Sunday, November 25, 2012

0 Oracle Management Files (OMF)

Oracle Management Files is the feature allow oracle instance manage the database file, such as the tablespace file. It can name the files base on the OFA rule in the predefine location and delete the file when the file is no longer require in the even such as drop tablespace. It is new feature available after Oracle 9i.

DB_CREATE_FILE_DEST Require Data File, Temporary File, Block Change Tracking File. When no DB_CREATE_ONLINE_LOG_DEST, it can be use by control file and log file
DB_CREATE_ONLINE_LOG_DEST_n(1~5) Optional Control File, Log File.

DB_RECOVERY_FILE_DEST

Optional Default location for Archive log and RMAN backup files

When DB_CREATE_FILE_DEST is not set, the OMF is not enable.

-- If no db_create_file_dest is set, the OMF is not enable
show parameter db_create
 
show parameter db_recovery

image

Enable the OMF

-- If OMF is not enable, below "create tablespace" would fail
Create tablespace mytbs;

image

alter system set db_create_file_dest='/home/oracle/app/oracle/oradata/orcl';
-- Re create the tablespace
Create tablespace mytbs;
-- Query the tablespace
col tablespace_name format a21
col "FILE_NAME" format a20
select DT.TABLESPACE_NAME,DT.contents,DT.STATUS,DT.SEGMENT_SPACE_MANAGEMENT, DT2.FILE_NAME,DT2.BYTES
from DBA_TABLESPACES DT, DBA_DATA_FILES  DT2
where DT.TABLESPACE_NAME= DT2.TABLESPACE_NAME and DT.TABLESPACE_NAME='MYTBS';
 
-- drop tablespace
drop tablespace MYTBS;

image

 

Reference

http://docs.oracle.com/cd/B10501_01/server.920/a96521/omf.htm

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates