Oracle管理文件OMF （oracle managed files）

简化dba的管理操作

1：启用 omf

23:16:04 SYS@orcl> show parameter DB_CREATE_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string
23:16:17 SYS@orcl> select distinct t1.name,t2.name datafile_name  ,t.autoextensible from v$tablespace t1,v$datafile t2    ,dba_data_files t where t1.name=t.tablespace_name   and t1.ts# = t2.ts# and t1.name like '%UNDO%'   order by t1.name asc;

NAME                           DATAFILE_NAME                                                                              AUT
------------------------------ ------------------------------------------------------------------------------------------ ---
TBS_29_UNDO                    /u01/app/oracle/oradata/orcl/tbs_29_undo.dbf                                               YES
TBS_30_UNDO                    /u01/app/oracle/oradata/orcl/tbs_30_undo.dbf                                               YES
UNDOTBS1                       /u01/app/oracle/oradata/orcl/undotbs01.dbf                                                 YES

23:16:52 SYS@orcl> alter system set db_create_file_dest='/u01/app/oracle/oradata/orcl/';

System altered.

23:17:40 SYS@orcl> show parameter DB_CREATE_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/app/oracle/oradata/orcl/
23:17:43 SYS@orcl> show parameter create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
db_create_file_dest                  string      /u01/app/oracle/oradata/orcl/
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
23:17:49 SYS@orcl> ho ls /u01/app/oracle/oradata/orcl/
compress_01.dbf  datafile5.dbf      redo04.log     tbs_02_02.dbf  tbs_08.dbf     tbs_14.dbf     tbs_17_wallet.dbf      tbsp_1.dbf   test02.dbf  undotbs01.dbf
compress_02.dbf  datafilebig_1.dfb  redo4.log      tbs_03.dbf     tbs_09.dbf     tbs_15.dbf     tbs_19_group_temp.dbf  temp_01.dbf  tp1.dbf     undotbs_1.dbf
control01.ctl    example01.dbf      sysaux01.dbf   tbs_04.dbf     tbs_10.dbf     tbs_16_01.dbf  tbs_20_group_temp.dbf  temp_1.dbf   tp2.dbf     undotbs_add.dbf
datafile1.dbf    redo01.log         system01.dbf   tbs_05.dbf     tbs_11.dbf     tbs_16_02.dbf  tbs_29_undo.dbf        temp_2.dbf   ts1.dbf     users01.dbf
datafile3.dbf    redo02.log         tbs_01.dbf     tbs_06.dbf     tbs_12_01.dbf  tbs_17_01.dbf  tbs_30_undo.dbf        temp.dbf     ts2.dbf
datafile4.dbf    redo03.log         tbs_02_01.dbf  tbs_07.dbf     tbs_13.dbf     tbs_17_02.dbf  tbs_example .dbf       test_01.dbf  ts3.dbf

23:19:56 SYS@orcl> create tablespace tbs_31;

Tablespace created.

23:20:27 SYS@orcl> ho ls /u01/app/oracle/oradata/orcl/
compress_01.dbf  datafile5.dbf      redo03.log    tbs_02_01.dbf  tbs_07.dbf     tbs_13.dbf     tbs_17_02.dbf          tbs_example .dbf  test_01.dbf  ts3.dbf
compress_02.dbf  datafilebig_1.dfb  redo04.log    tbs_02_02.dbf  tbs_08.dbf     tbs_14.dbf     tbs_17_wallet.dbf      tbsp_1.dbf        test02.dbf   undotbs01.dbf
control01.ctl    example01.dbf      redo4.log     tbs_03.dbf     tbs_09.dbf     tbs_15.dbf     tbs_19_group_temp.dbf  temp_01.dbf       tp1.dbf      undotbs_1.dbf
datafile1.dbf    ORCL               sysaux01.dbf  tbs_04.dbf     tbs_10.dbf     tbs_16_01.dbf  tbs_20_group_temp.dbf  temp_1.dbf        tp2.dbf      undotbs_add.dbf
datafile3.dbf    redo01.log         system01.dbf  tbs_05.dbf     tbs_11.dbf     tbs_16_02.dbf  tbs_29_undo.dbf        temp_2.dbf        ts1.dbf      users01.dbf
datafile4.dbf    redo02.log         tbs_01.dbf    tbs_06.dbf     tbs_12_01.dbf  tbs_17_01.dbf  tbs_30_undo.dbf        temp.dbf          ts2.dbf

23:20:30 SYS@orcl> select distinct t1.name,t2.name datafile_name  ,t.autoextensible from v$tablespace t1,v$datafile t2    ,dba_data_files t where t1.name=t.tablespace_name   and t1.ts# = t2.ts# and t1.name = 'TBS_31'   order by t1.name asc;

NAME                           DATAFILE_NAME                                                                              AUT
------------------------------ ------------------------------------------------------------------------------------------ ---
TBS_31                         /u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_tbs_31_fkhyxsmr_.dbf                      YES

23:21:00 SYS@orcl> ho ls /u01/app/oracle/oradata/orcl/ORCL
datafile

23:21:36 SYS@orcl> ho ls /u01/app/oracle/oradata/orcl/ORCL/datafile
o1_mf_tbs_31_fkhyxsmr_.dbf

23:21:46 SYS@orcl>

23:21:36 SYS@orcl> ho ls /u01/app/oracle/oradata/orcl/ORCL/datafile
o1_mf_tbs_31_fkhyxsmr_.dbf

23:21:46 SYS@orcl> create temporary tablespace tbs_32_temp ;

Tablespace created.

23:23:36 SYS@orcl> select distinct t1.name,t2.name datafile_name  ,t.autoextensible from v$tablespace t1,v$datafile t2    ,dba_data_files t where t1.name=t.tablespace_name   and t1.ts# = t2.ts# and t1.name = 'TBS_32_TEMP'   order by t1.name asc;

no rows selected

23:23:45 SYS@orcl> COL FILE_NAME FOR A60;
23:33:25 SYS@orcl>  COL TABLESPACE_NAME FOR A30;
23:33:26 SYS@orcl> SELECT TABLESPACE_NAME   AS TABLESPACE_NAME  ,FILE_NAME  AS FILE_NAME   ,STATUS  AS STATUS   ,AUTOEXTENSIBLE   AS AUTOEXTENSIBLE   FROM DBA_TEMP_FILES where TABLESPACE_NAME='tbs_32_temp';

no rows selected

23:33:28 SYS@orcl> SELECT TABLESPACE_NAME   AS TABLESPACE_NAME  ,FILE_NAME  AS FILE_NAME   ,STATUS  AS STATUS   ,AUTOEXTENSIBLE   AS AUTOEXTENSIBLE   FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_32_TEMP';

TABLESPACE_NAME                FILE_NAME                                                    STATUS  AUT
------------------------------ ------------------------------------------------------------ ------- ---
TBS_32_TEMP                    /u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_tbs_32_t_fk ONLINE  YES
                               hz3rdt_.tmp


23:33:44 SYS@orcl> ho ls /u01/app/oracle/oradata/orcl/ORCL/datafile
o1_mf_tbs_31_fkhyxsmr_.dbf  o1_mf_tbs_32_t_fkhz3rdt_.tmp

23:33:57 SYS@orcl>

——————————————————————————————————————————————————————————————————————————————————————————