Shulou(Shulou.com)06/01 Report--

How to understand the reuse attributes in Oracle data files, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

Oracle tablespace creation parameters

When we add a data file to the tablespace, we have a reuse attribute. The 10g 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 the original size.

If file already exists and file size is specified at the time of creation, the original file is reused and the new size is applied, and if no file size is specified, the original size is retained.

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

-- if file does not exist, oracle ignores this parameter.

Restriction on the REUSE Clause

You cannot specify REUSE unless you have specified filename.

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

If Oracle uses an existing file, then all data in the previous file will be lost.

No relevant information was found in the official documents of Oracle 11g. Because you don't have an 11g library on hand, it's not easy to test. The test in this blog is based on the Oracle 10g environment.

Let's do some tests:

1. Create a tablespace Dave

SQL > show user

USER is "SYS"

SQL > create tablespace dave datafile'/ u01 size

Tablespace created.

two。 Create the table anqing and specify the storage tablespace 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 tablespace dave and use reuse

SQL > alter tablespace dave add datafile'/ u01 reuse

Alter tablespace dave add datafile'/ u01 reuse

*

ERROR at line 1:

ORA-01119: error in creating database file'/ u01max appActionorAcleUniplicoradataUnip dave2max dave02.dbf'

ORA-17610: file'/ u01 does not exist and no size specified ORA-27037 does not exist and no size specified ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3

In this case, if the file exists, the size of the original file is used. But dave02.dbf does not exist, and we do not specify a file size, so it cannot be created. We can create it by specifying size.

SQL > alter tablespace dave add datafile'/ u01 size reuse

Tablespace altered.

SQL >

4. Keep the tablespace in state, and then use reuse to add data files

SQL > alter tablespace dave add datafile'/ u01 size reuse

Alter tablespace dave add datafile'/ u01 size size 50m reuse

*

ERROR at line 1:

ORA-01537: cannot add file'/ u01 file already part of database

An error is reported, so even if we need to use reuse, the premise is that the tablespace no longer exists in the data file.

5. First datafile offline drop, in reuse

Offline drop doesn't drop datafile, it just marks datafile as offline, and we can recover back after online. Specific reference:

The difference between alter database datafile offline drop and alter tablespace drop datafile

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

SQL > alter database datafile'/ u01 offline drop

Database altered.

SQL > alter tablespace dave add datafile'/ u01 size reuse

Alter tablespace dave add datafile'/ u01 size size 50m reuse

*

ERROR at line 1:

ORA-01537: cannot add file'/ u01 file already part of database

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

-- restore the data file

SQL > alter database datafile'/ u01 online

Alter database datafile'/ u01 online

*

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6:'/ u01 *

SQL > recover datafile 6

Media recovery complete.

6. Use the alter tablespace dave drop datafile command

This command deletes control files and physical files, so there is no meaning available.

SQL > alter tablespace dave drop datafile'/ u01ameApplains orave2max dave02.dbf'

Tablespace altered.

[oracle@db2 dave2] $pwd

/ u01/app/oracle/oradata/dave2

[oracle@db2 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

-- the file no longer exists

7. After deleting the tablespace, in reuse

The command is as follows:

SQL > drop tablespace dave including contents and datafiles

This command can also specify that the physical file be deleted at the same time, but then our test cannot be completed, so we do not delete datafile, only the tablespace from the control file.

SQL > drop tablespace dave including contents

Tablespace dropped.

SQL > create tablespace dave2 datafile'/ u01 size reuse

Tablespace created.

-- successful reuse

Take a look at the data file size:

[oracle@db2 dave2] $ll-h dave01.dbf

-rw-r- 1 oracle oinstall 51m Jun 3 04:31 dave01.dbf

We used to be 100m, but now we are 50m.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.