V$datafile in Oracle

Back to: Oracle DBA Tutorials

V$datafile in Oracle Database with Examples

In this article, I am going to discuss V$datafile in Oracle Database with Examples. Please read our previous article where we discussed V$Containers/cdb_pdbs in Oracle.

V$datafile in Oracle

In this article, we will try to understand the differences between data dictionary v$datafile and dba_data_files. Let us see the differences with an example and set how these both have differed.

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 ROOT database. Let’s check if all the pluggable databases are up and running or not.

The pluggable databases are up and running fine. If the pluggable database is not open, please try to open the pluggable database using the command “alter pluggable database JAYAPDB open. Let us run a query to check the list of database files in the container database using the cdb_data_files data dictionary.

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

We have already used this query to check the data files present in the database.

We can see the first four data files have con_id as 1 which is part of the container database and the rest three data files having con_id as 3 are part of the pluggable database JAYAPDB. Let us just try the other data dictionary for the data files. The data dictionary is v$datafile.

Query: select file#,name,ts#,con_id from v$datafile order by con_id;

Here file# indicates file name and ts# indicates tablespace name.

We might think that it also retrieves data files for the container database JAYADB and the pluggable database JAYAPDB. But we have 11 data files because the results also display the data files present in the seed database PDB$SEED having con_id as 2. So, this result indicates that v$database will display the database even if the database is open and in read-only mode. So, the Oracle Enterprise Manager always depends on these types of views. We will try to understand the Oracle Enterprise Manager in further sessions.

So, we understood that the data dictionary v$database will display even if the database is not open. Let us try to close the pluggable database and try to run the query.

Now, the pluggable database is closed. Let us try to run the same query again and check the output.

Query: select file#,name,ts#,con_id from v$datafile order by con_id;

We can see all the data files using the data dictionary v$datafile. Let us try to check the data dictionary cdb_data_files and see the output.

Here is the output and this shows only datafiles with con_id as 1 for the database JAYADB. We cannot see the data files in the pluggable database JAYAPDB because the pluggable database is closed and the data dictionary cdb_data_files cannot check the data files if the database is closed.

Note: The dictionary cannot see any data if the database is closed but the V$ dictionary can see the data.

Let us move from the container database to the pluggable database. Even if the database is down, we can move from the pluggable database to the container database.

Let us try to run the same query we ran before with the data dictionary $datafile and see the output.

Query: select file#,name,ts#,con_id from v$datafile order by con_id;

We can see only 3 datafiles having con_id as 3 means the datafiles present inside the pluggable database. So, even if the database is closed, we can see the data. Let us try to check the same with other data dictionaries.

Query: select file_name,file_id,tablespace_name from dba_data_files;

We can see the error displaying database is not open. So, if the database is closed and we try to execute with data dictionary cdb_ or dba_ we will get these errors.

In this article, we have learned about the differences between the V$datafile and the dba_data_files and cdb_data_files.

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