Query Data Files and Temp Files in Oracle

Back to: Oracle DBA Tutorials

Query Data Files and Temp Files in Oracle

In this article, I am going to discuss Query Data Files and Temp Files in Oracle Database. Please read our previous article where we discussed Data Dictionary Differences inside a Pluggable Database in Oracle.

Note: Please make sure to start the Linux machine and log in as an oracle user and start the database before running the below queries.

Data Files in Oracle:

The data files always contain the actual user’s data, applications data, metadata like tables, rows indexes procedures, views, etc. If we lose the datafiles the database is lost. Open the SQL developer. Connect to the database as “sqlplus as sysdba” in the SQL developer. Once you connect to the database check the container name.

Command: show con_name

Make sure all the pluggable databases are open. Because we need to switch between the container database and pluggable database for better practice.

Query: select name,open_mode,con_id from v$pdbs;

We can there are two pluggable databases. Both are open in our case. We only log in to the JAYAPDB database. In case if the pluggable is not open you can use the below command to open the pluggable database.

Command: alter pluggable database JAYAPDB open;

Now, we have a dictionary cdb_data_files which shows all the data files in your system or database including the pluggable database.

Query: select file_name,file_id,tablespace_anme,con_id from cdb_data_files;

You can see that we have four data files having con_id as 1 and three data files having con_id as 3. These data files are created while creating the database. We will learn about how to create data files in further articles. For example, we have users01.dbf data file. The file_id for the data file users01.dbf is 7 and this data file is related to the tablespace USERS and the con_id is 1.

The four data files having con_id 1 belong to the container database CDB$ROOT. The four tablespaces are USERS, UNDOTBS1, SYSTEM, and SYSAUX belongs to the container database. To check the existence of the data files go to the location of the data files. Open File manager and go to the below location.

You are in the JAYADB folder. Below are the data files and temp files. We have system01.dbf, sysaux01.dbf, Undotbs01.dbf, users01.dbf. Don’t try to change these data files or edit these data files. You may lose the data.

Now, make the same query using the dba_data_files data dictionary. We already knew that these data dictionary displays only the data of the database that we have logged in.

If you notice a bit, we do not have a column called con_id because the data dictionary dba_data_files does not support the con_id. But in the cdb_data_files we can able to see the con_id. Now let us move to the pluggable database and try to check the data files in the database JAYAPDB.

If we use the same query that we have executed previously it only displays the data files present in the database that we have currently logged in.

Query: select file_name,file_id,tablespace_name from dba_data_files;

You can see the path of the data files is changed from JAYADB to JAYAPDB.

If we run the same query with the cdb_data_files data dictionary it shows the same output because it displays the content of the database that we have currently logged in.

Now we understood how to query the database based on the database that you have logged in. We have two data dictionaries for the data files cdb_data_files and dba_data_files which display the list of data files.

Note: We have discussed all_tables and all_users. But when it comes to data files there is no such data dictionary present like all_data_files. The reason behind not existing all_data_files is because any common user doesn’t require to know about and there is no reason to check the data files. Even if we try to use the data dictionary all_data_files we will get an output saying the table doesn’t exist.

Temp Files in Oracle Database:

Let us try to query the temp files. Temporary tablespaces are part of the oracle database. Temp files are used with temporary tablespaces. Temporary tablespaces are used for special operations particularly for sorting data results on disk and for hash join in SQL. For example, in SQL for millions of rows, the sort operation is too large for the RAM area and must occur on disk. This is the situation where we use a temporary tablespace and this temporary tablespace consists of TEMP files. Connect to the database as “sqlplus as sysdba” in the SQL Developer. Once you connect to the database check the container name.

Command: show con_name

To make sure all the pluggable databases are open we will query the database.

Query: select name,open_mode,con_id from v$pdbs;

Now let us try to list out the temp files in the database from CDB$ROOT.

Query: select file_name,file_id,tablespace_name,con_id from cdb_temp_files;

We can see there are two temp files temp01.dbf has con_id as 1 and 3. The temp files temp01.dbf is related to the temporary tablespace. We have another temp file that is present in the pluggable database. This indicates that only one temp file is enough for the entire database. We have one temp file in the root database and another temp file in the pluggable database. Now let us try to change the data dictionary from cdb_data_files to the dba_data_files. Let us see how the output will be.

Query: select file_name,file_id,tablespace_name from dba_temp_files;

We can see only one temp file which is present in the CDB$ROOT database. Let us try to move to the pluggable database and see what will be the output of the same query.

Now we are in the pluggable database. Let us try to run the same query to temp files and see how the result will be displayed.

Query: select file_name,file_id,tablespace_name from dba_temp_files;

We can see the path /uo1/app/oracle/JAYADB/jayapdb/temp01.dbf which indicates the temp file is in a pluggable database.

In the next article, I am going to discuss V$DATABASE/ V$INSTANCE in Oracle. Here, in this article, I try to explain Query Data Files and Temp Files in Oracle and I hope you enjoy this Query Data Files and Temp Files in Oracle Database article.