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

1. alter database datafile 'file_name' offline drop

This command does not delete the data file, but simply changes the status of the data file to recover. The offline drop command is equivalent to taking a data file offline and requiring recovery, not deleting the data file. Information about data files is also stored in data dictionaries and control files.

1.1 For archive mode:

alter database datafile 'file_name' offline is no different from offline drop. After offline, you need to recover before you can go online.

For example:

SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

1.2 For non-archive mode:

If it's non-archive mode, it can only be offline drop. Because there is no archive file for recovery operation in non-archive mode, of course, if the speed is sufficient after offline, the data in online redo has not been overwritten, then in this case, recovery can still be performed.

oracle 11g:

SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;

2. alter tablesapce tablespace_name drop datafile 'datafile_name'

This statement deletes control files and files on disk, and the serial numbers of the original data files after deletion can be reused.

Note that this statement can only be used when datafile is online. If the corresponding data file is already offline for drop, it is only available for dictionary managed tablespaces.

3. example

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> select name from v$datafile;

NAME

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

+DATA/rac/datafile/system.256.746634087

+DATA/rac/datafile/undotbs1.258.746634089

+DATA/rac/datafile/sysaux.257.746634087

+DATA/rac/datafile/users.259.746634089

+DATA/rac/datafile/undotbs2.264.746634255

SQL> create tablespace dave datafile '+DATA/rac/datafile/dave01.dbf' size 10M;

Tablespace created.

SQL> alter tablespace dave add datafile '+DATA/rac/datafile/dave02.dbf' size 10M;

Tablespace altered.

SQL> select file#,status,name from v$datafile;

FILE# STATUS NAME

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

1 SYSTEM +DATA/rac/datafile/system.256.746634087

2 ONLINE +DATA/rac/datafile/undotbs1.258.746634089

3 ONLINE +DATA/rac/datafile/sysaux.257.746634087

4 ONLINE +DATA/rac/datafile/users.259.746634089

5 ONLINE +DATA/rac/datafile/undotbs2.264.746634255

6 ONLINE +DATA/rac/datafile/dave01.dbf

7 ONLINE +DATA/rac/datafile/dave02.dbf

SQL> alter database datafile '+DATA/rac/datafile/dave01.dbf' offline;

Database altered.

SQL> set wrap off;

SQL> select file#,status,name from v$datafile;

FILE# STATUS NAME

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

1 SYSTEM +DATA/rac/datafile/system.256.746634087

2 ONLINE +DATA/rac/datafile/undotbs1.258.746634089

3 ONLINE +DATA/rac/datafile/sysaux.257.746634087

4 ONLINE +DATA/rac/datafile/users.259.746634089

5 ONLINE +DATA/rac/datafile/undotbs2.264.746634255

6 RECOVER +DATA/rac/datafile/dave01.dbf

7 ONLINE +DATA/rac/datafile/dave02.dbf

7 rows selected.

SQL> alter tablespace dave drop datafile 6;

alter tablespace dave drop datafile 6

*

ERROR at line 1:

ORA-03263: cannot drop the first file of tablespace DAVE

This is incorrect, only online can be deleted. Let's delete dave02.dbf to see

SQL> alter tablespace dave drop datafile 7;

Tablespace altered.

--Delete successfully.

SQL> select file#,status,name from v$datafile;

FILE# STATUS NAME

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

1 SYSTEM +DATA/rac/datafile/system.256.746634087

2 ONLINE +DATA/rac/datafile/undotbs1.258.746634089

3 ONLINE +DATA/rac/datafile/sysaux.257.746634087

4 ONLINE +DATA/rac/datafile/users.259.746634089

5 ONLINE +DATA/rac/datafile/undotbs2.264.746634255

6 RECOVER +DATA/rac/datafile/dave01.dbf

6 rows selected.

--Go to ASM to see if the physical files have been deleted:

[oracle@rac1 ~]$ export ORACLE_SID=+ASM1

[oracle@rac1 ~]$ asmcmd

ASMCMD> ls

DATA/

FRA/

ASMCMD> cd DATA

ASMCMD> ls

DAVE/

DB_UNKNOWN/

RAC/

ASMCMD> cd RAC

ASMCMD> ls

CONTROLFILE/

DATAFILE/

TEMPFILE/

spfiledave.ora

spfilerac.ora

ASMCMD> cd DATAFILE

ASMCMD> ls

SYSAUX.257.746634087

SYSTEM.256.746634087

UNDOTBS1.258.746634089

UNDOTBS2.264.746634255

USERS.259.746634089

dave01.dbf

--The corresponding physical file dave02.dbf has been deleted

Let's take a look at Datafile 6 online:

SQL> alter database datafile 6 online;

alter database datafile 6 online

*

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '+DATA/rac/datafile/dave01.dbf'

- - That's why you need to file files.

SQL> recover datafile 6;

Media recovery complete.

SQL> alter database datafile 6 online;

Database altered.

SQL> select file#,status,name from v$datafile;

FILE# STATUS NAME

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

1 SYSTEM +DATA/rac/datafile/system.256.746634087

2 ONLINE +DATA/rac/datafile/undotbs1.258.746634089

3 ONLINE +DATA/rac/datafile/sysaux.257.746634087

4 ONLINE +DATA/rac/datafile/users.259.746634089

5 ONLINE +DATA/rac/datafile/undotbs2.264.746634255

6 ONLINE +DATA/rac/datafile/dave01.dbf

6 rows selected.

Finally, dave drop the entire table space:

SQL> drop tablespace dave including contents and datafiles;

Tablespace dropped.

SQL> select file#,status,name from v$datafile;

FILE# STATUS NAME

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

1 SYSTEM +DATA/rac/datafile/system.256.746634087

2 ONLINE +DATA/rac/datafile/undotbs1.258.746634089

3 ONLINE +DATA/rac/datafile/sysaux.257.746634087

4 ONLINE +DATA/rac/datafile/users.259.746634089

5 ONLINE +DATA/rac/datafile/undotbs2.264.746634255

Take a look at ASM:

ASMCMD> ls

SYSAUX.257.746634087

SYSTEM.256.746634087

UNDOTBS1.258.746634089

UNDOTBS2.264.746634255

USERS.259.746634089

The corresponding physical file is missing.

alter tablespace test drop datafile 8;

Command cannot drop non-empty data files. If you want to drop a data file, you need to remove the object first, and then move it back after the drop is completed.

SELECT owner ownr,

segment_name name,

segment_type TYPE,

extent_id exid,

file_id fiid,

block_id blid,

blocks blks

FROM dba_extents

WHERE file_id = 8

ORDER BY block_id;

alter table temp move tablespace test;

https://blog.csdn.net/tianlesoftware/article/details/6305600

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.