How do you move an index organized table?

The following index needs to be moved from tablespace DATA1 to tablespace DATA2:

select unique segment_name,segment_type,tablespace_name 
from dba_segments 
where owner='SCOTT'
and tablespace_name = 'DATA1'
;

Result:

SEGMENT_NAME	SEGMENT_TYPE	TABLESPACE_NAME
COUNTRY_ID_PK	INDEX	DATA1

Turns out, the index belongs to an IOT:

select i.owner,i.table_name,i.tablespace_name,i.status, t.iot_type
from dba_indexes i join dba_tables t
on (i.table_name = t.table_name)
and t.table_name ='COUNTRIES'
and t.owner=i.owner
where i.index_name='COUNTRY_ID_PK'
and i.owner='SCOTT';

Result in:

OWNER	TABLE_NAME	TABLESPACE_NAME	STATUS	IOT_TYPE
SCOTT	COUNTRIES	DATA1	VALID	IOT

If you try to move the index COUNTR_ID_PK with the usual "alter index ... rebuild" clause, it will fail with

ORA-28650: Primary index on an IOT cannot be rebuilt

Instead, move the table. Since it's an IOT, the index will be moved automatically with it:

alter table SCOTT.COUNTRIES move tablespace DATA2 ONLINE;

Verify that the IOT was moved by executing the same query as previously:

OWNER	TABLE_NAME	TABLESPACE_NAME	STATUS	IOT_TYPE
SCOTT	COUNTRIES	DATA2	VALID	IOT

A function that shows schema size in PostgreSQL

Thanks to Emanuel Calvo for publishing this procedure.

I put it in a script:

SET search_path TO public;
SHOW search_path;
CREATE OR REPLACE FUNCTION pg_schema_size(text) RETURNS BIGINT AS $$
SELECT SUM(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::BIGINT FROM pg_tables WHERE schemaname = $1
$$ LANGUAGE SQL;

and then executed it:

]$ psql
psql (11.7)
Type "help" for help.


postgres=# \i cre_funk.sql
SET
 search_path
-------------
 public
(1 row)

CREATE FUNCTION

When this is done, it can be executed in any database part of the postgres instance, as long as you connect to the right one first:

postgres=# \connect sales
You are now connected to database "sales" as user "postgres".
sales=# select pg_size_pretty(pg_schema_size('sales_archive'));
 pg_size_pretty
----------------
 28 MB
(1 row)

Av en eller annen grunn får vi nå: HINT: No function matches the given name and argument types. You might need to add explicit type casts. når vi kjører den i postgres versjoner > 11

Comparing users, schemas, instances and databases between Oracle and PostgreSQL

If you have to deal with both Oracle and PostgreSQL databases in your daily work, it can be confusing to deal with terms such as users, schemas and instances, since they mean different things in each software installation.

After discussing the matter with an experienced coworker, I have made the following matrix which may clarify the differences and the similarities between the two:

Oracle	PostgreSQL
One database may contain many schemas	One database may contain many schemas
A user equals a schema	A user equals a role (with login)
One instance supports one database *	One instance may support many databases
One user belongs to one database	One user belongs to the entire instance
Users belong in a global namespace together with roles and public synonyms	Users does not belong in any namespace
By default, users can only access objects in their own schema	By default, users can only access objects in the schemas that they own
For a user to access objects in a different schema, object privileges or the ANY system privilege is required	For a user to access objects in a different schema, in must be granted USAGE on the schema
A schema has a password	A schema does not have a password
A schema cannot be owned by a (another) user	A schema is owned by a user, by default the user that created the schema
A schema may contain up until 3 namespaces: one for indexes, another one for constraints and a final one for tables, views, sequences etc.	A schema is in itself a namespace. This namespace may in turn contain tables, views, indexes etc

* Exceptions to this rule are 1) Oracle RAC, where multiple instances support one database and 2) Oracle Multitenant, where one instance supports many pluggable databasess

A good tutorial regarding schemas can be found here

How to display a run-time parameter in a postgreSQL instance

Like with Oracle, PostgreSQL has an easy way to display run-time parameters:

[postgres@oric-pg01~]$ psql
psql (11.7)
Type "help" for help.

postgres=# show log_min_duration_statement;
 log_min_duration_statement
----------------------------
 -1
(1 row)

the log_min_duration_statement parameter is documented here

How to specifiy default compression for a tablespace

CREATE BIGFILE TABLESPACE star_data DATAFILE
'/oradata/proddb01/star_data.dbf' SIZE 1024M AUTOEXTEND ON NEXT 128M MAXSIZE 12T
LOGGING
DEFAULT
TABLE COMPRESS FOR OLTP
INDEX COMPRESS ADVANCED LOW
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Note that the syntax used for tablespaces

TABLE COMPRESS FOR OLTP

is equivalent to the ROW STORE COMPRESS ADVANCED clause of CREATE TABLE.

Read more about tablespaces with default compression attributes in the 12.2 documentation

How to compress an existing table online

The ONLINE keyword makes it simple to compress an existing table online:

alter TABLE scott.emp
move new_tablespace
row store compress advanced
online;

If you omit the new_tablespace clause, Oracle will move it to the same tablespace as it is already residing in and the table will be reorganized and compressed.

How to modify a unified auditing policy to make exceptions based on login information

The audit policy had been created as follows:

create audit policy all_select_policy actions select;

Here is how you can alter an existing policy so that the policy will make an exception for session created by user "DBAADMIN"

alter audit policy ALL_SELECT_POLICY 
condition 'SYS_CONTEXT (''USERENV'',''CURRENT_USER'') NOT IN (''DBAADMIN'')' 
evaluate per Session;

Documentation can be found here
The oracle-supplied policy ORA_LOGIN_FAILURES automatically audits all failed login attempts. You can alter it to exclude certain uninteresting connections, such as for example DBSNMP, like this:

alter audit policy ORA_LOGON_FAILURES
condition 'SYS_CONTEXT (''USERENV'',''CURRENT_USER'') NOT IN (''DBSNMP'')'
evaluate per session;

For more information about the SYS_CONTEXT function, check the official 12.2 documentation.