Oracle managed files

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.

 

db_create_file_dest specifies the default location for oracle 

managed datafiles.this location is also used as default location 

for oracle managed control files and online redo logs if none of

the db_create_online_log_dest_n parameters are specified,if file

system location is specified as default directory then create the

directory structure and give permissions.

 

db_create_online_log_dest_n (n=1,2,3,4,5) specifies the default 

location for oracle managed control files and online redo logs.

if more than one location is specified then oracle managed control file and online redo log is multiplexed across the other db_

create_online_log_dest_n locations.

 

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

 

SQL> create tablespace srinu

  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 = 'SRINU'

SQL> /

 

FILE_NAME                                     BYTES

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

/mnt/redhat/o1_mf_srinu_7fh2qylt_.dbf     10485760

 

SQL>

 

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

 

SQL> alter tablespace srinu add datafile size 5m

  2  /

 

Tablespace altered.

 

SQL> select file_name, bytes from dba_data_files

  2  where tablespace_name = 'SRINU'

  3  /

 

FILE_NAME                                     BYTES

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

/mnt/redhat/o1_mf_srinu_7fh2qylt_.dbf     10485760

/mnt/redhat/o1_mf_srinu_7fh2zndg_.dbf      5242880

 

SQL>

 

Look at the files at the Linux level:

 

TEST9 > pwd

/mnt/redhat

TEST9 > ls -1

o1_mf_srinu_7fh2qylt_.dbf

o1_mf_srinu_7fh2zndg_.dbf

TEST9 >

 

Drop the tablespace:

 

SQL> drop tablespace srinu

  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 >