Applies To

All Users

Summary

What is the proper way to drop a common user with objects in CDB & PDBs? I am finding info on CREATE USER, but not DROP USER.

In the CDB I tried the following command when logged in as SYSDBA, but it did not work:

drop user c##usrName cascade container=all;

Please provide proper syntax and steps. I am reading some documents where this can cause CDB and PDB to get out of sync and I want to avoid these type of issues.
 

Solution

To drop a common user connect to CDB then execute "drop user C##USERNAME cascade" statement. You cannot drop common users from PDBs.

Example:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show pdbs

  CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
  2 PDB$SEED READ ONLY NO
  3 PDB1 READ WRITE NO
SQL>
SQL> select username,common,con_id from cdb_users where username='C##TEST';

USERNAME             COM CON_ID
-------------------- --- ----------
C##TEST              YES  1
C##TEST              YES  3

SQL>

SQL> select object_name,object_type,owner,con_id from cdb_objects where owner='C##TEST';

OBJECT_NAME OBJECT_TYPE OWNER CON_ID
------------------------------ ------------------------------ -------------------- ----------
TABLE1 TABLE C##TEST  1
TABLE2 TABLE C##TEST  3

SQL>



SQL> drop user C##TEST cascade container=all;
drop user C##TEST cascade container=all
  *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> drop user C##TEST cascade;    <<<----------- cascade 强大

User dropped.

SQL>
SQL> select username,common from cdb_users where username='C##TEST';

no rows selected

SQL>

SQL> alter session set container=PDB1;

Session altered.

SQL> select username,common from dba_users where username='C##TEST';

no rows selected

SQL>