How to avoid ERROR: role cannot be dropped because some objects depend on it in PostgreSQL

When you try to drop a user in a PostgreSQL database, you may encounter this error:

postgres=# drop role scott;
ERROR:  role "scott" cannot be dropped because some objects depend on it
DETAIL:  privileges for database mydb01

Solution: Drop the user's objects first:

postgres=# drop owned by scott;
DROP OWNED

Dropping can now proceed:

postgres=# drop role scott;
DROP ROLE

If you get errors like:

postgres=# drop role scott;
ERROR:  role "scott" cannot be dropped because some objects depend on it
DETAIL:  privileges for database mydb02
25 objects in database mydb02

You need to open a connection to mydb02 and revoke the privileges, like this:

postgres=# \connect mydb02
You are now connected to database "mydb02" as user "postgres".

Try dropping the role now, and you'll get a list of objects with dependencies (the list is abbriviated):

mydb02=# drop role scott;
ERROR:  role "scott" cannot be dropped because some objects depend on it
DETAIL:  privileges for sequence my_seq1
privileges for sequence my_seq2
privileges for sequence my_seq3
privileges for table mytable1
privileges for table mytable2
privileges for table mytable3
privileges for schema public

Revoke access to all the public tables:

mydb02=# revoke all on all tables in schema public from scott;
REVOKE

When you attempt to drop the role now, you can see that all the tables previously listed are gone:

mydb02=# drop role scott;
ERROR:  role "scott" cannot be dropped because some objects depend on it
DETAIL:  privileges for sequence my_seq1
privileges for sequence my_seq2
privileges for sequence my_seq3
privileges for schema public

Privileges to sequences and a schema remains. Revoke access to the sequences:

mydb02=# revoke all on all sequences in schema public from scott;
REVOKE
mydb02=# drop role scott;
ERROR:  role "scott" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema public

Revoke the final privilege:

mydb02=# revoke usage on schema public from scott;
REVOKE

Finally, we can drop the role:

mydb02=# drop role scott;
DROP ROLE

How to work around hung rman process when duplicating from active database

Applicable for Oracle 12.1.

During a "clone from active database" operation, I had a seemingly stuck restore process. It used a lot of time on restoring the controlfiles.

My clonescript was simple enough:

connect target /@mysrcdb
connect auxiliary /@myauxdb
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
debug io;
DUPLICATE TARGET DATABASE TO myauxdb
FROM ACTIVE DATABASE
;
debug off;
}
exit

I noticed that the logfile wouldn't move beoynd this point:

DBGANY:     No untilscn in effect [17:44:29.012] (krmkicat)
RMAN-08016: channel aux1: starting datafile backup set restore
RMAN-08169: channel aux1: using network backup set from service mysrcdb
RMAN-08021: channel aux1: restoring control file

Solution:
On the axiliary server, set the parameter

DISABLE_OOB=on

in your $TNS_ADMIN/sqlnet.ora, and reexecute the duplicate-command again.

This is caused by a bug mentioned in Doc ID 2073604.1: "RMAN active duplicate hanging on restore control file" found on support.oracle.com

What is the difference between real-time apply and real-time query?

Real-time apply is the process of applying the redo in the current standby redo log file as it is being filled, in contrast to waiting for the standby redo log to be filled up. By default, apply services will wait for a standby redo log file to be archived, then apply the redo that it contains.


Real-time query is the process of applying redo redo while the physical standby database is open
This allows for read-only usage of the data which is identical to the primary database.
Note: Real-time query is licenced as the "Active Data Guard" option.


Real-time apply is documented here

Real-time query is documented here

How to list index subpartitions and their sizes

SELECT IDXSUBPART.INDEX_OWNER, IDXSUBPART.INDEX_NAME, IDXSUBPART.PARTITION_NAME, IDXSUBPART.SUBPARTITION_NAME,IDXSUBPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='SALES'
AND    IDXSUBPART.TABLESPACE_NAME='SH'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION'
GROUP BY IDXSUBPART.INDEX_OWNER, IDXSUBPART.INDEX_NAME, IDXSUBPART.PARTITION_NAME, IDXSUBPART.SUBPARTITION_NAME,IDXSUBPART.TABLESPACE_NAME;

Output:

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	SUBPARTITION_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX_SK1	SYS_P18153	SYS_SUBP14709	DATA1	1
SH	SALES_IDX_SK1	SYS_P18157	SYS_SUBP14831	DATA1	1
SH	SALES_IDX_SK1	SYS_P18158	SYS_SUBP14862	DATA2	25
SH	SALES_IDX_SK1	SYS_P18157	SYS_SUBP14832	DATA1	0
SH	SALES_IDX_SK1	SYS_P18142	SYS_SUBP12409	DATA2	32
SH	SALES_IDX_SK1	SYS_P18139	SYS_SUBP12414	DATA1	1

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