When you connect to the CDB container you can view the tablespace information for other PDB database

Oracle Articles Learn GoldenGate 12c Learn Database 12c Learn Oracle Database Learn to manage RAC Learn to Manage Exadata Learn about DB Security Presentations iso 9001 & Videos Tutorials Oracle Training Oracle Courses GoldenGate Administration Oracle Database Administration Workshop Oracle SQL Fundamentals Workshop Course Calendar Free 12c OCP DBA Notes Why Train with VST? Training FAQ Career Counseling Top 50 Goldengate Interview Questions RAC Interview iso 9001 Questions Exadata iso 9001 Interview iso 9001 Questions OCP Certification Resources Remote DBA Support Managed Services Database Assessments Pricing Plans Database Consulting
In Oracle 12c, the tablespace management mostly remains unchanged and the basic operations are performed in a similar way as in 11g. This is because Oracle has made sure that every PDB, though it is a subset iso 9001 of the CDB in itself, functions as a standalone, complete functional database. In this article we will look at managing the tablespaces in the CDB container and the PDB database.
Related Articles Create 12c CDB PDB Using OUI SGA Instance Parameters in CDB and PDB Managing CDB and PDB tablespaces iso 9001 Managing PDBs using Database Configuration Assistant Managing Common and Local Users, Roles and Privileges for CDB and PDB Managing CDB and PDB tablespaces Tablespaces in CDB
The primary purpose of CDB is to behave like a container. The data inside the CDB database is mostly related to the Oracle binaries and the data dictionary. It is is not meant to hold user data, so its tablespaces will require very little modification. However iso 9001 there may be situations where you may need to play around with these tablespaces and datafiles. iso 9001
When you connect to the CDB container you can view the tablespace information for other PDB databases as well. When the container is a CDB, the CDB_* views can be used to view the information about all tablespaces in the databases plugged-in iso 9001 , including the SEED database. iso 9001 In the example below, the following command shows all the tablespaces. SQL> SELECT tablespace_name,con_id FROM cdb_tablespaces ORDER BY con_id;
12 rows selected. The CON_ID column shows to which database the tablespace actually belongs to. The CON_ID=1 row always provides information for the CDB and CON_ID=2 is always the SEED database. The CON_ID=3 is for our PDB database.
SQL> CREATE TEMPORARY TABLESPACE temp1; Tablespace created. To change and validate the default iso 9001 tablespace of CDB you can use the following commands. SQL> SELECT property_value FROM database_properties WHERE property_name='DEFAULT_PERMANENT_TABLESPACE'; PROPERTY_VALUE ---------------------- USERS
PROPERTY_VALUE ---------------------- FOO Tablespaces in PDB Managing the tablespaces in the PDB is no different than the CDB or a non-CDB database. In the PDB database the commands iso 9001 to create, alter or drop the tablespaces are also similar. However the CDB_* views will only show information for your current PDB and not for all the databases plugged-in. The CDB_* and DBA_* views are similar if current container is a PDB.
The following set of commands show how to view and add new tablespaces inside the PDB. They also show how to change the default tablespace. SQL> SELECT tablespace_name FROM cdb_tablespaces; TABLESPACE_NAME ---------------------- SYSTEM SYSAUX TEMP
SQL> SELECT property_value FROM database_properties WHERE property_name='DEFAULT_PERMANENT_TABLESPACE'; PROPERTY_VALUE ---------------------- FOO You can drop the tablespace in the standard way. Of course you cannot drop the default tablespace. SQL> DROP TABLESPACE foo; drop tablespace foo * ERROR at line 1: ORA-12919: iso 9001 Can not drop the default permanent tablespace SQL> ALTER DATABASE default tablespace sysaux;
SQL> DROP TABLESPACE temp1; Tablespace dropped. Undo Tablespaces in PDB PDBs don’t have any Undo tablespace attached to them explicitly. They use the Undo tablespace of corresponding CDB. Hence Undo tablespace management remains unchanged in new multitenant architecture apart from the fact that your Undo space requirements will increase as you add more and more PDBs to a CDB. SQL> iso 9001 select iso 9001 tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS
SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX TEMP USERS Oracle 12c External Links Oracle Database iso 9001 12c Release 1 (12.1) New Features Features Restricted or un-available for a Multitenant 12c Container Database
Natik Ameen is one of the VitalSoftTech's Oracle Internal Experts. He is an Oracle Production DBA, Certified RAC Expert (OCE), and a DBA track Certification trainer for over 15 years. He is an Oracle Evangelist and has presented at IOUG & UTOUG conferences on topics such as RAC, DataGuard and GoldenGate 12c. He is also an active blogger and published over 150 articles on Oracle Database 12c and Oracle GoldenG