Tablespaces in Oracle

Back to: Oracle DBA Tutorials

Tablespaces in Oracle with Examples

In this article, I am going to discuss cdb_tablespaces and v$tablespace in Oracle Database with Examples. Please read our previous article where we discussed V$datafile in Oracle.

cdb_tablespaces and v$tablespace in Oracle

In this article, we will try to query tablespaces. Mainly we have two data dictionaries regarding tablespaces cdb_tablespaces and v$tablespace.

Until now we have seen many data dictionaries and many tables. There might be a question, how can we remember all these data dictionaries and the tables? The answer is no need to remember because we have understood the concept of each and every data dictionary. For example, we forgot a table name for some tablespace. But we remember the subject called tablespace and the subject called control file and temp file. But we do not remember the tables. We can use the tablespace data dictionary to extract the table name and the other details of the table. By practicing again and again we can remember most of the tables and the data dictionaries. But if you are preparing for the Oracle certification you need to remember hard.

Note: Open the Linux machine and start the database. Once the database is up and running open the pluggable database as well. If all the previous steps are done then open the SQL developer and login as sysdba.

Let us check whether we are in a container database or a pluggable database.

We are in the container database. Make sure to open the container database as well because we will be switching between the ROOT database and the pluggable database.

Command: alter pluggable database JAYAPDB open

We have a data dictionary cdb_tablespaces. Let us try to run a query with this data dictionary and analyze the output.

Query: select tablespace_name,block_size,status,contents,con_id from cdb_tablespaces;

From the output we can see all the tablespaces from the JAYADB with con_id as 1 and tablespaces from JAYAPDB with con_id as 3. We have block size as 8192 means 8kb and the status of tablespaces are online and the contents of the tablespaces are PERMANENT except for the tables TEMP has TEMPORARY and the UNDO tablespace has content UNDO.

Now let us try to run the same query with the data dictionary v$tablespaces. We already knew that V$tablespaces displays a lot of information even if the database is hidden or closed.

We can see a lot of tablespaces which show tablespaces having con_id as 1 are present in CDB$ROOT database and con_id as 3 are present in JAYAPDB pluggable database and tablespaces having con_id as 2 are present in PDB$SEED database. Let us move to a pluggable database from the CDB$ROOT database.

Command: alter session set container=JAYAPDB;

We are in the pluggable database. Let us try to run the tablespace query using the data dictionary cdb_tablespaces.

Query: select tablespace_name,block_size,status,contents,con_id from cdb_tablespaces;

So, the output displays only the tablespaces present inside the pluggable database JAYAPDB. Let us try to run using the other data dictionary dba_tablespaces.

Query: select tablespace_name,block_size,contents,status from dba_tablespaces;

We can see the 4 tablespaces and the same output as the data dictionary cdb_tablespaces. Let’s try to check with the data dictionary V$tablespsaces. This also shows only 4 tablespaces because currently, we are in the pluggable database.

So, we have learned that when we are in the container database it takes all the pluggable databases into consideration and displays the output when we use the data dictionary v$tablespace and if we are in any pluggable database, it only displays data present in the pluggable database.

In the next article, I am going to discuss V$Controlfile/ V$Logfile in Oracle Database with Examples. Here, in this article, I try to explain cdb_tablespaces and v$tablespace in Oracle Database with Examples and I hope you enjoy this cdb_tablespaces and v$tablespace in the Oracle article.