Oracle Managed Files (Part 1)

Oracle managed files were introduced in version 9. You can implement them using initialisation parameters. These can be set:

1. In the init.ora or server parameter file.
2. In an alter session or alter system statement.

The parameters specify directories which Oracle should use for datafiles in subsequent DDL statements such as create tablespace etc. You can see what I mean in the example below, which I ran on Oracle 9.2.0.4.0:

First, specify the directory where files should be created using the db_create_file_dest parameter:

SQL> alter session set db_create_file_dest = '/mnt/redhat';

 

Session altered.

 

SQL>

 

Now create a tablespace. Oracle is managing the creation of datafiles so no filename is required:

 

SQL> create tablespace andrew

  2  datafile size 10m

  3  /

 

Tablespace created.

 

SQL>

 

Check the name(s) of the datafile(s) in the tablespace. There is only one and Oracle has created it in the location specified by the db_create_file_dest parameter:

 

SQL> l

  1  select file_name, bytes from dba_data_files

  2* where tablespace_name = 'ANDREW'

SQL> /

 

FILE_NAME                                     BYTES

---------------------------------------- ----------

/mnt/redhat/o1_mf_andrew_7fh2qylt_.dbf     10485760

 

SQL>

 

Add a datafile to the tablespace and check the name(s) of the datafile(s) again:

 

SQL> alter tablespace andrew add datafile size 5m

  2  /

 

Tablespace altered.

 

SQL> select file_name, bytes from dba_data_files

  2  where tablespace_name = 'ANDREW'

  3  /

 

FILE_NAME                                     BYTES

---------------------------------------- ----------

/mnt/redhat/o1_mf_andrew_7fh2qylt_.dbf     10485760

/mnt/redhat/o1_mf_andrew_7fh2zndg_.dbf      5242880

 

SQL>

 

Look at the files at the Linux level:

 

TEST9 > pwd

/mnt/redhat

TEST9 > ls -1

o1_mf_andrew_7fh2qylt_.dbf

o1_mf_andrew_7fh2zndg_.dbf

TEST9 >

 

Drop the tablespace:

 

SQL> drop tablespace andrew

  2  /

 

Tablespace dropped.

 

SQL>

 

Oracle deletes managed files once they are no longer required.Check that the files have gone at the Linux level:

 

TEST9 > pwd

/mnt/redhat

TEST9 > ls -l

total 0

TEST9 >