Solution: ora-02199: missing datafile / tempfile clause
OMF, the full name is Oracle Managed Files, that is, oracle file management. Using OMF can simplify the management of the administrator, without specifying the name, size, path of the file. Its name, size, path are automatically assigned by oracle. When deleting logs, data, and control files that are no longer used, OMF can also automatically delete their corresponding OS files.
OMF supports automatic management of the following files:
Tablespace Log files (online) Control file
Precondition: you need to set related parameters for these types of files.
1, OMF management of data files
Data file management parameter: DB "create" file "dest
DB create file dest: the default path when Oracle creates data files and temporary files without explicitly specifying the path. When DB create online log dest is not specified,
It also serves as the default path for online log files and control files.
Suppose you need to create tablespace s and data files
SQL> CREATE TABLESPACE s; /*Error message received*/
create tablespace s
*
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
SQL> show parameter db_create_file /*View the DB? Create? File? Dest parameter*/
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
--Set up db_create_file_dest parameter
SQL> ALTER SYSTEM SET db_create_file_dest = '/u01/app/oracle/oradata/orcl';
System altered.
--After setting parameters, creation succeeded
SQL> CREATE TABLESPACE s;
Tablespace created.
SQL> SELECT NAME FROM v$datafile; /*You can see that orcl / datafile / O1? MF? S? Vrl1t7h? DBF is a data file created automatically by Oralce*/
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/Test.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_s_5vrl1t7h_.dbf
/*View physical file, MB by default*/
SQL> ho ls -lh /u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_s_5vrl1t7h_.dbf
-rw------- 1 oracle oinstall 101M Apr 7 16:54 /u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_s_5vrl1t7h_.dbf
SQL> CREATE TABLESPACE t DATAFILE SIZE 10m; /*You can also specify a data file size of MB*/
Tablespace created.
SQL> SELECT NAME FROM v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/Test.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_s_5vrl1t7h_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_t_5vrlgqtl_.dbf
SQL> ho ls -lh /u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_t_5vrlgqtl_.dbf
-rw------- 1 oracle oinstall 11M Apr 7 17:01 /u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_t_5vrlgqtl_.dbf
--When a tablespace is deleted, the OMF The generated data file will be deleted along with the deletion of the tablespace
SQL> DROP TABLESPACE t;
Tablespace dropped.
--View physical file, no longer exists
SQL> ho ls -lh /u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_t_5vrlgqtl_.dbf
ls: /u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_t_5vrlgqtl_.dbf: No such file or directory
--For not used OMF After the tablespace is deleted, its data files are not deleted, v$datafile Deleted in view
--Here's unused OMF Created x Tablespaces and data files
SQL> CREATE TABLESPACE x DATAFILE '/u01/app/oracle/oradata/orcl/x.dbf' SIZE 10m;
Tablespace created.
SQL> SELECT NAME FROM v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/Test.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_s_5vrl1t7h_.dbf
/u01/app/oracle/oradata/orcl/x.dbf
SQL> DROP TABLESPACE x;
Tablespace dropped.
SQL> SELECT NAME FROM v$datafile; /*x.dbf data file no longer exists in view*/
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/Test.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_s_5vrl1t7h_.dbf
SQL> ho ls /u01/app/oracle/oradata/orcl/x.dbf /*Still exists in the operating system*/
/u01/app/oracle/oradata/orcl/x.dbf
--Note: use OMF The physical file will be deleted when using the
--Use the following method to rebuild x Tablespace, and then use including contents and datafiles Delete tablespaces and physical files completely
SQL> CREATE TABLESPACE X DATAFILE '/u01/app/oracle/oradata/orcl/x.dbf' REUSE;
Tablespace created.
SQL> DROP TABLESPACE x INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> ho ls /u01/app/oracle/oradata/orcl/x.dbf
ls: /u01/app/oracle/oradata/orcl/x.dbf: No such file or directory
Summary: ways to manage files using OMF
1. Use alter system set DB create file dest to set the path
2. View the just set show parameter DB "create" file "dest;
3. Create tablespace and data file
4. Create tablespace separately create tablespace < > datafile '' size < >;
5. You can also create undo and temporary tablespace. CREATE UNDO TABLESPACE tablespace_name ;CREATE TEMPORARY TABLESPACE tablespace_name;
6. Delete the tablespace drop tablespace tablespace_name; in the case of OMF, delete the physical file, which is equivalent to not using OMF to create and using INCLUDING CONTENTS AND DATAFILES to delete