Oracle data File Reuse Property Description (reprint)

Oracle tablespace Creation Parameter description

Http://blog.csdn.net/tianlesoftware/archive/2011/01/27/6166928.aspx

When we add a data file to a tablespace, there is a reuse property. 10g's official website describes this parameter as follows:

Reuse

Specify reuse to allow Oracle to reuse an existing file.

(1) If the file already exists, then Oracle reuses the filename and applies the new size (If you specify size) or retains t He original size.

--if file already exists and the file size is specified when it is created, then the original file is reused and the new size is applied, and if the file size is not specified, the original is preserved.

(2) If The file does not exist, then Oracle ignores this clause and creates the file.

--If file does not exist, Oracle ignores the parameter.

Restriction on the Reuse Clause

You cannot specify reuse unless the specified filename.

Whenever Oracle uses an existing file, the previous contents of the file is lost.

-If Oracle uses a file that already exists, the data in the previous file will be lost.

From:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses004.htm#SQLRF01602

No information was found in the official documentation for Oracle 11g. Because there is no 11g library at hand, it is not a good test. This blog is tested based on Oracle 10g environments.

Let's do some tests:

1. Create a table space Dave

Sql> Show user;

USER is "SYS"

Sql> Create tablespace Dave datafile '/u01/app/oracle/oradata/dave2/dave01.dbf ' size 100M;

Tablespace created.

2. Create the table anqing and specify the storage table space Dave

Sql> CREATE TABLE Anqing tablespace Dave as select * from Dba_objects;

Table created.

Sql> commit;

Commit complete.

Sql> Select COUNT (*) from Anqing;

COUNT (*)

----------

50391

Sql> set Wrap off;

Sql> Select Owner,table_name,tablespace_name from dba_tables where table_name= ' anqing ';

OWNER table_name Tablespace_name

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

SYS Anqing DAVE

3. Add a new data file to the table space Dave and use the reuse

Sql> alter tablespace Dave add datafile '/u01/app/oracle/oradata/dave2/dave02.dbf ' reuse;

Alter tablespace Dave add datafile '/u01/app/oracle/oradata/dave2/dave02.dbf ' reuse

*

ERROR at line 1:

Ora-01119:error in creating database file '/u01/app/oracle/oradata/dave2/dave02.dbf '

Ora-17610:file '/u01/app/oracle/oradata/dave2/dave02.dbf ' does not exist and no size specified ora-27037:unable to Obtai N File status Linux error:2: No such file or directory Additional Information:3

--In this case, if the file exists, the original file size will be used. However, DAVE02.DBF does not exist, and we do not specify a file size, so we cannot create it. We can create a size by specifying it.

Sql> alter tablespace Dave add datafile '/u01/app/oracle/oradata/dave2/dave02.dbf ' size 50M reuse;

Tablespace altered.

Sql>

4. Maintain the status of the tablespace and then use reuse to add the data file

Sql> alter tablespace Dave add datafile '/u01/app/oracle/oradata/dave2/dave01.dbf ' size 50M reuse;

Alter tablespace Dave add datafile '/u01/app/oracle/oradata/dave2/dave01.dbf ' size 50M reuse

*

ERROR at line 1:

Ora-01537:cannot add file '/u01/app/oracle/oradata/dave2/dave01.dbf '-file already part of database

--Error, so even if we need to use reuse, the table space is no longer available for the data file.

5. Drop datafile offline first, in reuse

Offline drop will not drop datafile, just mark datafile as offline, we can recover back after online. Specific reference:

ALTER DATABASE datafile offline drop with alter tablespace drop datafile Difference

Http://blog.csdn.net/tianlesoftware/archive/2011/04/06/6305600.aspx

sql> ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/DAVE2/DAVE01.DBF ' offline drop;

Database altered.

Sql> alter tablespace Dave add datafile '/u01/app/oracle/oradata/dave2/dave01.dbf ' size 50M reuse;

Alter tablespace Dave add datafile '/u01/app/oracle/oradata/dave2/dave01.dbf ' size 50M reuse

*

ERROR at line 1:

Ora-01537:cannot add file '/u01/app/oracle/oradata/dave2/dave01.dbf '-file already part of database

--still error, because the data file dave01.dbf information is also recorded in the data dictionary.

--Restore the data file back

sql> ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/DAVE2/DAVE01.DBF ' online;

ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/DAVE2/DAVE01.DBF ' online

*

ERROR at line 1:

Ora-01113:file 6 Needs Media recovery

Ora-01110:data file 6: '/U01/APP/ORACLE/ORADATA/DAVE2/DAVE01.DBF '

sql> recover datafile 6;

Media recovery complete.

6. Use the ALTER tablespace Dave Drop datafile Command

This command removes the control file and the physical file, so there is no usable meaning.

Sql> alter tablespace Dave drop datafile '/u01/app/oracle/oradata/dave2/dave02.dbf ';

Tablespace altered.

[Email protected] dave2]$ pwd

/u01/app/oracle/oradata/dave2

[[email protected] dave2]$ ls

Control01.ctl control03.ctl example01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf

Control02.ctl dave01.dbf huaining01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

--File no longer exists

7. After the table space is deleted, the reuse

The command is as follows:

Sql>drop tablespace dave including contents and datafiles;

The command can also specify that physical files be deleted at the same time, but then our tests are not done, so we do not delete datafile, only the table space is removed from the control file.

Sql> drop tablespace dave including contents;

Tablespace dropped.

sql> Create tablespace dave2 datafile '/u01/app/oracle/oradata/dave2/dave01.dbf ' size 50M reuse;

Tablespace created.

--Reuse success

Look at the data file size:

[Email protected] dave2]$ ll-h dave01.dbf

-RW-R-----1 Oracle Oinstall 51M June 3 04:31 dave01.dbf

We were 100M and now we're 50M.

Oracle data File Reuse Property Description (reprint)