Your multitenant container database (CDB) contains multiple pluggable databases (PDBs).
You execute the command to create a common user:
SQL> CREATE USER c##a_admin
IDENTIFIED BY password
DEFAULT TABLESPACE users
QUOTA I00M ON users
TEMPORARY TABLESPACE temp;
Which statement is true about the execution of the command?
A.
The common user is created in the CDB and all the PDBs, and uses the users and temp
tablespaces of the CDB to store schema objects.
B.
The command succeeds only if all the PDBs have the users and temp tablespaces.
C.
The command gives an error because the container=all clause is missing.
D.
The command succeeds and sets the default permanent tablespace of a PDB as the
default tablespace for the c##a_admin user if the users tabiespace does not exist in that
PDB.
B for me:
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
———- —————————— — — — — ———-
1 SYSAUX YES NO YES 1
0 SYSTEM YES NO YES 1
2 UNDOTBS1 YES NO YES 1
4 USERS YES NO YES 1
3 TEMP NO NO YES 1
0 SYSTEM YES NO YES 2
1 SYSAUX YES NO YES 2
2 TEMP NO NO YES 2
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
2 TEMP NO NO YES 3
11 rows selected.
SQL> CREATE USER c##a_admin IDENTIFIED BY oracle1 DEFAULT TABLESPACE USERS QUOTA 100M ON USERS TEMPORARY TABLESPACE TEMP;
CREATE USER c##a_admin IDENTIFIED BY oracle1 DEFAULT TABLESPACE USERS QUOTA 100M ON USERS TEMPORARY TABLESPACE TEMP
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database PDBMAX
ORA-00959: tablespace ‘USERS’ does not exist
B
From the SG:
When creating a common user, any tablespace, tablespace group or profile specified in the CREATE command must exist in every PDB. If none of these are specified, the default TABLESPACE, TEMPORARY TABLESPACE, and PROFILE for the PDB will be used.
Thank Steve! B.
When creating a common user, any tablespace, tablespace group or profile specified in the CREATE command must exist in every PDB. If none of these are specified, the default TABLESPACE, TEMPORARY TABLESPACE, and PROFILE for the PDB will be used.
Thanks Puntano and Steve
B
B)
SQL> create tablespace x datafile size 10m;
Tablespace created.
SQL> create user c##x1 identified by x default tablespace x temporary tablespace temp;
create user c##x1 identified by x default tablespace x temporary tablespace temp
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database PDB11
ORA-00959: tablespace ‘X’ does not exist
SQL>
As said by others, B is correct one, because, as mentioned below,
When creating a common user the following requirements must all be met.
-You must be connected to a common user with the CREATE USER privilege.
-The current container must be the root container.
-The username for the common user must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
-The username must be unique across all containers.
-The DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA and PROFILE must all reference objects that exist in all containers.(WE ARE NOT MEETING THIS, HENCE IT WILL FAIL)
-You can either specify the CONTAINER=ALL clause, or omit it, as this is the default setting when the current container is the root.
A Is correct!
Considering the fact that the question does not tell us clearly whether the users and temp tablespaces exist in the ‘multiple pluggable databases’ then its good to assume that they exist in the pdbs which will allow for the common user to be created across all the pdbs and that will make A the best answer
Sorry I meant B!
Read everything about creating common and loocal users here:- https://oracle-base.com/articles/12c/multitenant-manage-users-and-privileges-for-cdb-and-pdb-12cr1
B
https://docs.oracle.com/cd/E49329_01/network.121/b71285/users.htm#CIHGGGJC
B