May 22, 2022
Datafiles- Files
Datafiles, along with redo log files, are the most important type of files in the database. This is where all of your data will ultimately be stored. Every database has at least one datafile associated with it and typically has many more than one.
You can view the datafiles for your database by querying the data dictionary. The names of the datafiles will vary depending on whether you’re using RAC (with datafiles on ASM disk) and/ or Oracle Managed Files (OMF).
For example, here are the datafiles in a RAC container database, using ASM disks, and the OMF naming convention:
SQL> select name from v$datafile;
NAME
+DATA/CDB/DATAFILE/system.257.1064287113
+DATA/CDB/DATAFILE/sysaux.258.1064287137
+DATA/CDB/DATAFILE/undotbs1.259.1064287153
+DATA/CDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.265.1064287787
+DATA/CDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.266.1064287787
+DATA/CDB/DATAFILE/users.260.1064287153
+DATA/CDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.267.1064287787
+DATA/CDB/DATAFILE/undotbs2.269.1064288035
+DATA/CDB/BB1C6AAC137C6A4DE0536638A8C06678/DATAFILE/system.274.1064288517
+DATA/CDB/BB1C6AAC137C6A4DE0536638A8C06678/DATAFILE/sysaux.275.1064288517
The prior output shows three SYSTEM datafiles (where the Oracle data dictionary is stored). One is for the root container database, and the other two are associated with pluggable databases.
The long random string in the directory path is the GUID (unique identifier) associated with the pluggable databases when using OMF files.
Listed next are the datafiles in a single instance container database (not using OMF):
SQL> select name from v$datafile;
NAME
/opt/oracle/oradata/CDB/system01.dbf
/opt/oracle/oradata/CDB/sysaux01.dbf
/opt/oracle/oradata/CDB/undotbs01.dbf
/opt/oracle/oradata/CDB/pdbseed/system01.dbf
/opt/oracle/oradata/CDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/CDB/users01.dbf /opt/oracle
/oradata/CDB/pdbseed/undotbs01.dbf /opt/oracle
/oradata/CDB/PDB1/system01.dbf
/opt/oracle/oradata/CDB/PDB1/sysaux01.dbf
/opt/oracle/oradata/CDB/PDB1/undotbs01.dbf
/opt/oracle/oradata/CDB/PDB1/users01.dbf
/opt/oracle/oradata/CDB/PDB2/system01.dbf
/opt/oracle/oradata/CDB/PDB2/sysaux01.dbf
/opt/oracle/oradata/CDB/PDB2/undotbs01.dbf
/opt/oracle/oradata/CDB/PDB2/users01.db
The datafiles in this directory /opt/oracle/oradata/CDB belong to the root container.
The datafiles in subdirectories belong to pluggable databases. Each database will usually (minimally) have the following datafiles:
•\ SYSTEM: Stores the Oracle data dictionary
•\ SYSAUX: Contains other non-dictionary objects
•\ UNDO: Stores the undo segments used for rollback operations
•\ USERS: A default tablespace to be used for application data (if no other tablespace is designated)
After a brief review of file system types, we’ll discuss how Oracle organizes these files and how data is organized within them. To understand this, you need to know what tablespaces, segments, extents, and blocks are. These are the units of allocation that Oracle uses to hold objects in the database, and I describe them in detail shortly.
More Details