You create a new pluggable database, HR_PDB, from the seed database. Which three
tablespaces are created by default in HR_PDB?
A.
SYSTEM
B.
SYSAUX
C.
EXAMPLE
D.
UNDO
E.
TEMP
F.
USERS
Explanation:
* A PDB would have its SYSTEM, SYSAUX, TEMP tablespaces.It can also contains
other user created tablespaces in it.
*
* Oracle Database creates both the SYSTEM and SYSAUX tablespaces as part of every
database.* tablespace_datafile_clauses
Use these clauses to specify attributes for all data files comprising the SYSTEM and SYSAUX
tablespaces in the seed PDB.
Incorrect:
Not D: a PDB can not have an undo tablespace. Instead, it uses the undo tablespace belonging to
the CDB.
Note:
* Example:
CONN pdb_admin@pdb1
SELECT tablespace_name FROM dba_tablespaces;
TABLESPACE_NAME
——————————SYSTEM
SYSAUX
TEMP
USERS
SQL>
A and B. Not sure about C EXAMPLE. It may created if used default option.
ABE
ACE
Sorry ACE
ABE
ABE – OK
To demonstrate a PDB sharing the temporary tablespace of CDB, the first step is to create a PDB without a temporary tablespace. By default when a PDB is created from seed, it is created with its local temporary tablespace TEMP and it cannot be dropped as it is the default temporary tablespace of the PDB.
http://oracleinaction.com/share-cdb-temp/
ABE
ABE
Ref: Oracle 12c by Sam Alapati
The “CREATE PLUGGABLE DATABASE PDB1 ” statement does the following:
(1) It creates the pluggable database by copying the data files from the READ ONLY seed PDB to the target dir.
(2) It creates the SYSTEM and SYSAUX tablespaces.
(3) It creates a new default service for the PDB.
(4) It creates default schemas SYS, SYSTEM, who can administer the new PDB.
(5) It creates a local user and PDB administrator (PDBA) named pdb1adm.
(6) It grants the local PDB_DBA role to pdb1adm.
Either Oracle Managed Files is enabled for the CDB, or the PDB_FILE_NAME_CONVERT initialization parameter is set. The files associated with the seed will be copied to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.
•
There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.
Ref: Oracle 12c New Features – 12c Class Notes from Oracle Corp.
The creation of a new PDB from the seed is nearly instantaneous. The operation copies the data files from the READ ONLY seed PDB to the target directory defined in the CREATE PLUGGABLE DATABASE statement.
It creates tablespaces such as SYSTEM to store a full catalog including metadata pointing to Oracle-supplied objects, SYSAUX for local auxiliary data.
It creates default schemas and common users that exist in seed PDB, SYS who continues to have all superuser privileges and SYSTEM who can administer the PDB.
It creates a local user (the PDBA) granted a local PDB_DBA role. Until the PDB SYS user grants privileges to the local PDB_DBA role, the new PDBA cannot perform any other operation than connecting to the PDB.
A new default service is also created for the PDB.
Nowhere is it stated that a ‘TEMP” default tablespace is created. A default service is inded created, but not TEMP tablespace. Again, default schemas, SYS and SYSTEM are created by default, but not EXAMPLE schema. I think, in the exam the question will be asked differently. Who knows? Definitely, SYSTEM and SYSAUX tablespaces are created by default. The other default component is a service. If the exam has a service choice, you have to pick SYSTEM, SYSAUX and a default service.
ABE
SQL> CREATE PLUGGABLE DATABASE pdb_london ADMIN USER pdb_london IDENTIFIED BY oracle_4U ROLES=(CONNECT) CREATE_FILE_DEST=’/u01/app/oracle/oradata/cdb2/pdb_london’;
Pluggable database created.
SQL> alter session set container=pdb_london;
SQL> show con_name
CON_NAME
——————————
PDB_LONDON
SQL> alter pluggable database pdb_london open;
Pluggable database altered.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
——————————
SYSTEM
SYSAUX
TEMP
ABE
A,B,E are correct
Tested: A,B,E
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDBTST READ WRITE NO
4 PDBEXPL READ WRITE NO
SQL>
SQL>
SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1;
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDBTST READ WRITE NO
4 PDBEXPL READ WRITE NO
5 PDB2 MOUNTED
SQL> alter pluggable database pdb2 open read write;
Pluggable database altered.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDBTST READ WRITE NO
4 PDBEXPL READ WRITE NO
5 PDB2 READ WRITE NO
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDBTST READ WRITE NO
4 PDBEXPL READ WRITE NO
5 PDB2 READ WRITE NO
SQL> conn sys/Oraadmin12@PDB2 as sysdba
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Warning: You are no longer connected to ORACLE.
SQL>
> oracle@ora12cdbhost1(ORA-SID=CDBTST):/oracle/product/dbase/12102/CDBTST/network/admin oracle@ora12cdbhost1(ORA-SID=CDBTST):/oracle/product/dbase/12102/CDBTST/network/admin oracle@ora12cdbhost1(ORA-SID=CDBTST):/oracle/product/dbase/12102/CDBTST/network/admin conn sys/Oraadmin12@PDB2 as sysdba
Connected.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
5 PDB2 READ WRITE NO
SQL>
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
———- —————————— — — — — ———-
2 UNDOTBS1 YES NO YES 0
0 SYSTEM YES NO YES 5
1 SYSAUX YES NO YES 5
2 TEMP NO NO YES 5
SQL>