Shulou(Shulou.com)05/31 Report--

The editor will share with you how to use the OMF feature of Oracle. I hope you will get something after reading this article. Let's discuss it together.

1. Confirm that the OMF feature is enabled on the system

Whether or not the OMF feature is enabled in the Oracle database can be obtained by looking at the DB_CREATE_FILE_DEST parameter.

When the DB_CREATE_FILE_DEST parameter value is empty, the OMF feature is not enabled.

Sys@ora10g > show parameter db_create_file_dest

NAME TYPE VALUE

Db_create_file_dest string

two。 Enable the OMF feature

Make it effective by modifying the database parameters. There are many ways to adjust according to personal preferences (pfile adjustment method, alter system adjustment parameters).

Sys@ora10g > alter system set db_create_file_dest='/oracle/oradata'

System altered.

Sys@ora10g > show parameter db_create_file_dest

NAME TYPE VALUE

Db_create_file_dest string / oracle/oradata

3. Create tablespaces using the OMF feature

Sys@ora10g > create tablespace tbs_secooler

Tablespace created.

4. Location of data files

In this case, Oracle will create a directory under the / oracle/oradata directory with the uppercase name of the database instance (in this case, ORA10G), and the data files corresponding to the table space will be stored in the datafile directory.

Sys@ora10g >! ls-l / oracle/oradata/ORA10G/datafile

Total 102512

-rw-r- 1 oracle oinstall 104865792 Jul 15 05:59 o1_mf_tbs_seco_63x8xzvw_.dbf

5. Default properties for tablespaces

Sys@ora10g > select dbms_metadata.get_ddl ('TABLESPACE','TBS_SECOOLER') from dual

DBMS_METADATA.GET_DDL ('TABLESPACE','TBS_SECOOLER')

CREATE TABLESPACE "TBS_SECOOLER" DATAFILE

'/ oracle/oradata/ORA10G/datafile/o1_mf_tbs_seco_63x8xzvw_.dbf' SIZE 104857600

AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

It can be seen that tablespaces created by OMF default to 100m in size and are automatically extended.

6. Add data file

You can simply add a data file to the tablespace tbs_secooler with the following command

Sys@ora10g > alter tablespace tbs_secooler add datafile

Tablespace altered.

Sys@ora10g > select dbms_metadata.get_ddl ('TABLESPACE','TBS_SECOOLER') from dual

DBMS_METADATA.GET_DDL ('TABLESPACE','TBS_SECOOLER')

CREATE TABLESPACE "TBS_SECOOLER" DATAFILE

'/ oracle/oradata/ORA10G/datafile/o1_mf_tbs_seco_63x8xzvw_.dbf' SIZE 104857600

AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M

'/ oracle/oradata/ORA10G/datafile/o1_mf_tbs_seco_63x9scb5_.dbf' SIZE 104857600

AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

Sys@ora10g >! ls-l / oracle/oradata/ORA10G/datafile

Total 205024

-rw-r- 1 oracle oinstall 104865792 Jul 15 05:59 o1_mf_tbs_seco_63x8xzvw_.dbf

-rw-r- 1 oracle oinstall 104865792 Jul 15 06:14 o1_mf_tbs_seco_63x9scb5_.dbf

7. Delete tablespace data files will be deleted

Sys@ora10g > drop tablespace tbs_secooler

Tablespace dropped.

Sys@ora10g >! ls-l / oracle/oradata/ORA10G/datafile

Total 0

After reading this article, I believe you have a certain understanding of "how to use the OMF features of Oracle". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.