如何正確的刪除表空間資料檔案

lhrbest發表於2016-09-06

如何正確的刪除表空間資料檔案




應該使用如下的命令刪除:

ALTER TABLESPACE TEST DROP DATAFILE 3;

 

參考mos文章:

Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command (文件 ID 1050261.1)

違反下列任何一個條件,該datafile均不能被drop

1)必須為空,否則會報:ORA-03262: the file is non-empty。值得注意的是,non-empty的含義是有extent被分配給了table,而不是該table中有無rows此時若是使用drop table xxx是不行的,必須使用 drop table xxx purge;或者在已經使用了drop table xxx的情況下,再使用purge table “xxx表在回收站中的名稱purge該表,否則空間還是不釋放,datafile依然drop不掉。

2)不能是所屬表空間的第一個file

  以上兩者可以透過drop tablespace來達到目的。

3)不能在read-only表空間中。 ---經測試是可以的

4)不能被offline,否則會報:ORA-03264: cannot drop offline datafile of locally managed tablespace

 針對該報錯,解決方法為:

[oracle@rhel6 u01]$ oerr ora 3264  

03264, 00000, "cannot drop offline datafile of locally managed tablespace"  

// *Cause:  Trying to drop offline datafile in lmts  

// *Action: Try to drop file afetr making it online   

5Cannot be a datafile that is part of the system tablespace, even if it is not the first datafile of the system tablespace --該條來源於How to Drop a Datafile From a Tablespace (文件 ID 111316.1)

 

 

若使用alter database datafile 3 offline drop;並不會刪除資料檔案,這個時候可以先online後再用alter tablespace test drop datafile 3;刪除,若執行alter database  datafile 3 offline drop;後並OS級別刪除了資料檔案,那麼需要使用alter database create datafile 3 as '/u03/app/oracle/oradata/ora1024g/sysaux01.dbf';來新增一個資料檔案,然後再執行recoveronline後再用alter tablespace test drop datafile 3;命令刪除。

 







1. alter database datafile 'file_name' offline drop
該命令不會刪除資料檔案,只是將資料檔案的狀態更改為recover。 offline drop命令相當於把一個資料檔案至於離線狀態,並且需要恢復,並非刪除資料檔案。 資料檔案的相關資訊還會存在資料字典和控制檔案中。 
 
1.1 對於歸檔模式:
alter database datafile 'file_name' offline 和 offline drop 沒有什麼區別。 因為offline 之後多需要進行recover 才可以online。
 
如:
SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;


1.2 對於非歸檔模式:
如果是非歸檔模式,只能是offline drop. 因為非歸檔模式沒有歸檔檔案來進行recover操作,當然,如果offline 之後,速度足夠塊,online redo裡的資料還沒有被覆蓋掉,那麼這種情況下,還是可以進行recover的。
 
oracle 11g:
SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;
  
2. alter tablesapce ‘tablespace_name’ drop datafile 'datafile_name'
該語句會刪除磁碟上的檔案並更新控制檔案和資料字典中的資訊,刪除之後的原資料檔案序列號可以重用。
注意,該語句只能是datafile online的時候才可以使用。如果說對應的資料檔案已經是offline for drop,那麼僅針對 dictionary managed tablespaces 可用。


OFFLINE Specify OFFLINE to take the data file offline. If the database is open, then you must perform media recovery on the data file before bringing it back online, because a checkpoint is not performed on the data file before it is taken offline.

FOR DROP If the database is in NOARCHIVELOG mode, then you must specify FOR DROP clause to take a data file offline. However, this clause does not remove the data file from the database. To do that, you must use an operating system command or drop the tablespace in which the data file resides. Until you do so, the data file remains in the data dictionary with the status RECOVER or OFFLINE.

If the database is in ARCHIVELOG mode, then Oracle Database ignores the FOR DROP clause.


Bringing Data Files Online or Taking Offline in ARCHIVELOG Mode

To bring an individual data file online, issue the ALTER DATABASE statement and include the DATAFILE clause. The following statement brings the specified data file online:

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

To take the same file offline, issue the following statement:

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

Note:

To use this form of the ALTER DATABASE statement, the database must be in ARCHIVELOG mode. This requirement prevents you from accidentally losing the data file, since taking the data file offline while in NOARCHIVELOG mode is likely to result in losing the file.

Taking Data Files Offline in NOARCHIVELOG Mode

To take a data file offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE FOR DROP clauses.

  • The OFFLINE keyword causes the database to mark the data file OFFLINE, whether or not it is corrupted, so that you can open the database.

  • The FOR DROP keywords mark the data file for subsequent dropping. Such a data file can no longer be brought back online.

    Note:

    This operation does not actually drop the data file. It remains in the data dictionary, and you must drop it yourself using one of the following methods:
    • An ALTER TABLESPACE ... DROP DATAFILE statement.

      After an OFFLINE FOR DROP, this method works for dictionary managed tablespaces only.

    • A DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES statement

    • If the preceding methods fail, an operating system command to delete the data file. This is the least desirable method, as it leaves references to the data file in the data dictionary and control files.

The following statement takes the specified data file offline and marks it to be dropped:

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






點選(此處)摺疊或開啟

  1. SYS@ora10g> create tablespace ts_dd_lhr datafile '/tmp/ts_dd_lhr01.dbf' size 10M;

  2. Tablespace created.

  3. SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';
  4. alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'
  5. *
  6. ERROR at line 1:
  7. ORA-03261: the tablespace TS_DD_LHR has only one file


  8. SYS@ora10g> alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;

  9. Tablespace altered.

  10. SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';
  11. alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'
  12. *
  13. ERROR at line 1:
  14. ORA-03263: cannot drop the first file of tablespace TS_DD_LHR


  15. SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';

  16. Tablespace altered.

  17. SYS@ora10g> ! ls -l /tmp/ts_dd_lhr0*
  18. -rw-r----- 1 oracle oinstall 10493952 Jun 29 14:58 /tmp/ts_dd_lhr01.dbf


  19. --------------------------------------------------

  20. SYS@ora10g> alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;

  21. Tablespace altered.

  22. SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' offline drop;

  23. Database altered.

  24. SYS@ora10g> ! ls -l /tmp/ts_dd_lhr02.dbf
  25. -rw-r----- 1 oracle oinstall 10493952 Jun 29 15:17 /tmp/ts_dd_lhr02.dbf


  26. SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
  27. alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'
  28. *
  29. ERROR at line 1:
  30. ORA-03264: cannot drop offline datafile of locally managed tablespace


  31. SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' online;
  32. alter database datafile '/tmp/ts_dd_lhr02.dbf' online
  33. *
  34. ERROR at line 1:
  35. ORA-01113: file 9 needs media recovery
  36. ORA-01110: data file 9: '/tmp/ts_dd_lhr02.dbf'


  37. SYS@ora10g> recover datafile 9;
  38. Media recovery complete.
  39. SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' online;

  40. Database altered.

  41. SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';

  42. Tablespace altered.

  43. SYS@ora10g> ! ls -l /tmp/ts_dd_lhr02.dbf
  44. ls: cannot access /tmp/ts_dd_lhr02.dbf: No such file or directory





  45. SYS@orclasm > create table t_ts_dd_lhr tablespace ts_dd_lhr as select * from dual;

  46. Table created.

  47. SYS@orclasm > truncate table t_ts_dd_lhr;

  48. Table truncated.

  49. SYS@orclasm >
  50. SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
  51. alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'
  52. *
  53. ERROR at line 1:
  54. ORA-03262: the file is non-empty


  55. SYS@orclasm > drop table t_ts_dd_lhr;

  56. Table dropped.

  57. SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
  58. alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'
  59. *
  60. ERROR at line 1:
  61. ORA-03262: the file is non-empty


  62. SYS@orclasm > purge recyclebin;

  63. Recyclebin purged.

  64. SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';

  65. Tablespace altered.







  66. SYS@ora10g> create tablespace ts_dd_lhr datafile '/tmp/ts_dd_lhr01.dbf' size 10M;


  67. alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;

  68. Tablespace created.

  69. SYS@ora10g> SYS@ora10g> SYS@ora10g>
  70. Tablespace altered.

  71. SYS@ora10g>
  72. SYS@ora10g> alter tablespace ts_dd_lhr read only;

  73. Tablespace altered.

  74. SYS@ora10g> select * from dba_tablespaces;

  75. TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
  76. ------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
  77. SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
  78. UNDOTBS1 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
  79. SYSAUX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  80. TEMP 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO
  81. USERS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  82. EXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANENT NOLOGGING NO LOCAL SYSTEM YES AUTO DISABLED NOT APPLY NO
  83. TS10GTEST 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  84. HHRIS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  85. TS_DD_LHR 8192 65536 1 2147483645 65536 READ ONLY PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO

  86. 9 rows selected.

  87. SYS@ora10g>
  88. SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';
  89. alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'
  90. *
  91. ERROR at line 1:
  92. ORA-03263: cannot drop the first file of tablespace TS_DD_LHR


  93. SYS@ora10g>
  94. SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';

  95. Tablespace altered.

 







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 test datafile '+DATA/rac/datafile/test01.dbf' size 10M;
Tablespace created. 
SQL> alter tablespace test add datafile '+DATA/rac/datafile/test02.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/test01.dbf
         7 ONLINE  +DATA/rac/datafile/test02.dbf
 
SQL> alter database datafile '+DATA/rac/datafile/test01.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/test01.dbf
         7 ONLINE  +DATA/rac/datafile/test02.dbf
 
7 rows selected. 
SQL> alter tablespace test drop datafile 6;
alter tablespace test drop datafile 6
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST
 
這裡報錯了,因為datafile 6是test表空間第一個資料檔案不讓刪(這種情況只能刪表空間了)。 我們刪除test02.dbf 看看
SQL> alter tablespace test drop datafile 7;
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 RECOVER +DATA/rac/datafile/test01.dbf
 
6 rows selected.
 
去ASM 裡看下物理檔案是否刪除掉了:
[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[oracle@rac1 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
ASMCMD> cd DATA
ASMCMD> ls
TEST/
DB_UNKNOWN/
RAC/
ASMCMD> cd RAC
ASMCMD> ls
CONTROLFILE/
DATAFILE/
TEMPFILE/
spfiletest.ora
spfilerac.ora
ASMCMD> cd DATAFILE
ASMCMD> ls
SYSAUX.257.746634087
SYSTEM.256.746634087
UNDOTBS1.258.746634089
UNDOTBS2.264.746634255
USERS.259.746634089
test01.dbf
--對應的物理檔案test02.dbf 已經被刪除了 
我們將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/test01.dbf'
--提示需要recover。 這也就是需要歸檔檔案的原因。 
 
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/test01.dbf
 
6 rows selected. 
最後把整個表空間test drop 掉: 
SQL> drop tablespace test 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
 
去ASM裡看一下:
ASMCMD> ls
SYSAUX.257.746634087
SYSTEM.256.746634087
UNDOTBS1.258.746634089
UNDOTBS2.264.746634255
USERS.259.746634089
 
對應的物理檔案沒有了。 
SQL>alter tablespace test drop datafile 8; 
不能drop 非空的資料檔案, 如果要drop 某個資料檔案,需要先把物件移除走。
  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 test2; 重建索引。
 


Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command (文件 ID 1050261.1)


In this Document

Symptoms
Changes
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.1.0 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 01-Nov-2011***
***Checked for relevance on 27-Aug-2014***

SYMPTOMS

Attempting to drop a datafile from a tablespace using the 10.2 and higher feature:
 

alter tablespace ... drop datafile ... ;

fails. The errors reported may differ depending on the actual situation. 

ORA-3262: the file is non-empty
ORA-3263: cannot drop the first file of tablespace
ORA-3264: cannot drop offline datafile of locally managed tablespace

ORA-60


Some errors are rather straightforward. However some errors are not, like the ORA-60. While the answer to the most common errors are self explanatory, this note focuses on the ORA-60 self-deadlock error received..

CHANGES

Datafile was lost at the Operating System level, causing the datafile to be in an OFFLINE status. Because the datafile contained some temporary segments and extents, these became invalid or stray. This causes an ORA-60 when trying to drop the datafile from the tablespace.

The datafile may be listed as MISSING in the datafile name, the reason for this is explained in:

Note 1050268.1: Explanation of MISSING keyword in datafile name

CAUSE

To drop a data file or temp file, it:

- Must be empty. 
- Cannot be the first file that was created in the tablespace. 
    In such cases, drop the tablespace instead.
- Cannot be in a read-only tablespace.
- Cannot be offline.

SOLUTION

The missing datafile has left this tablespace in an indeterminate status. The tablespace itself can actually still be used. But when an object located in the missing datafile is accessed, the statement will error-out with:

ORA-376: file 7 cannot be read at this time

The tablespace will need to be dropped as well. The 10gR2 feature to drop a single datafile from a tablespace cannot be use in this situation.. 

The following notes can be used to recover the data:
Note 216683.1: How to Recover Data from a Tablespace When One or Several Datafiles are Lost. 
Note 286355.1: How to Recover OFFLINE Dropped Datafile in ARCHIVELOG MODE

REFERENCES

NOTE:1050268.1 - Explanation of MISSING keyword in datafile name


NOTE:286355.1 - HOW TO RECOVER OFFLINE DROPPED DATAFILE IN ARCHIVELOG MODE




How to Drop a Datafile From a Tablespace (文件 ID 111316.1)


PURPOSE
  This note explains how a datafile can be removed from a database.  

  Since there can be confusion as to how a datafile can be dropped because of 
  the ALTER DATABASE DATAFILE OFFLINE DROP command, this note explains the 
  steps needed to delete a datafile and, in contrast, when the OFFLINE DROP 
  command is used.
 

SCOPE & APPLICATION
  There are two situations where people may want to 'remove' a datafile from a 
  tablespace:

  1.  You have just mistakenly added a file to a tablespace, or perhaps you 
      made the file much larger than intended and now want to remove it.

  2.  You are involved in a recovery scenario and the database will not start 
      because a datafile is missing.

  This article is meant to discuss situation 1 above.  There are other 
  articles that discuss recovery scenarios where a database cannot be brought 
  online due to missing datafiles.  Please see the 'Related Documents' section 
  at the bottom of this article.

Restrictions on Dropping Datafiles:

 - Datafile Must be empty.
 - Cannot be the first file in the tablespace. In such cases, drop the tablespace instead.
 - Cannot be a datafile that is part of the system tablespace, even if it is not the first datafile of the system tablespace.
 - Cannot be in a read-only tablespace.
 - The datafile cannot be offline.




How to 'DROP' a Datafile from a Tablespace:
===========================================

Version 9.2 and earlier

Before we start with detailed explanations of the process involved, please note
that Oracle does not provide an interface for dropping datafiles in the same 
way that you could drop a schema object such as a table, a view, a user, etc.  
Once you make a datafile part of a tablespace, the datafile CANNOT be removed, 
although we can use some workarounds.
 
Before performing certain operations such as taking tablespaces/datafiles 
offline, and trying to drop them, ensure you have a full backup.

If the datafile you wish to remove is the only datafile in that tablespace, 
simply drop the entire tablespace using:

    DROP TABLESPACE  INCLUDING CONTENTS;

You can confirm how many datafiles make up a tablespace by running the 
following query:

    select file_name, tablespace_name 
    from dba_data_files 
    where tablespace_name ='';

The DROP TABLESPACE command removes the tablespace, the datafile, and the 
tablespace's contents from the data dictionary.  Oracle will no longer have 
access to ANY object that was contained in this tablespace.  The physical 
datafile must then be removed using an operating system command (Oracle NEVER 
physically removes any datafiles).  Depending on which platform you try this 
on, you may not be able to physically delete the datafile until Oracle is 
completely shut down. (For example, on Windows NT, you may have to shutdown 
Oracle AND stop the associated service before the operating system will allow 
you to delete the file - in some cases, file locks are still held by Oracle.) 

If you have more than one datafile in the tablespace, and you do NOT need the 
information contained in that tablespace, or if you can easily recreate the 
information in this tablespace, then use the same command as above:

    DROP TABLESPACE  INCLUDING CONTENTS;

Again, this will remove the tablespace, the datafiles, and the tablespace's 
contents from the data dictionary.  Oracle will no longer have access to ANY 
object that was contained in this tablespace.  You can then use CREATE 
TABLESPACE and re-import the appropriate objects back into the tablespace.

If you have more than one datafile in the tablespace and you wish to keep the 
objects that reside in the other datafile(s) which are part of this tablespace,
then you must export all the objects  inside the affected tablespace.  Gather 
information on the current datafiles within the tablespace by running this 
query:

    select file_name, tablespace_name 
    from dba_data_files 
    where tablespace_name ='';

Make sure you specify the tablespace name in capital letters.

In order to allow you to identify which objects are inside the affected 
tablespace for the purposes of running your export, use the following query:

    select owner,segment_name,segment_type 
    from dba_segments 
    where tablespace_name=''

Now, export all the objects that you wish to keep.

Once the export is done, issue the DROP TABLESPACE tablespace INCLUDING 
CONTENTS.  

Note that this PERMANENTLY removes all objects in this tablespace. Delete the 
datafiles belonging to this tablespace using the operating system. (See the 
comment above about possible problems in doing this.) Recreate the tablespace 
with the datafile(s) desired, then import the objects into that tablespace.  
(This may have to be done at the table level, depending on how the tablespace 
was organized.)  

NOTE:
The ALTER DATABASE DATAFILE  OFFLINE DROP command, is not meant 
to allow you to remove a datafile. What the command really means is that you 
are offlining the datafile with the intention of dropping the tablespace.

If you are running in archivelog mode, you can also use:

    ALTER DATABASE DATAFILE  OFFLINE; 

instead of OFFLINE DROP.  Once the datafile is offline, Oracle no longer 
attempts to access it, but it is still considered part of that tablespace. This
datafile is marked only as offline in the controlfile and there is no SCN 
comparison done between the controlfile and the datafile during startup (This 
also allows you to startup a database with a non-critical datafile missing).  
The entry for that datafile is not deleted from the controlfile to give us the
opportunity to recover that datafile.

New functionality was added with the release of version 10.1 and higher

You can now specify drop tablespace inlcluding contents AND DATAFILES
Refer to Oracle? Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01
Chapter 8 managing tablespaces for more detailed explination


Starting with version 10.2 and higher 

You can now alter tablespace drop datafile (except first datafile
of a tablespace)

Refer to the following Oracle Documentation for more details regarding this operation:

For Oracle 10g Release 2:
     Oracle? Database Administrator's Guide 10g Release 2 (10.2)Part Number B14231-02 Chapter 9: Dropping Datafiles.

For Oracle 11g:
     Oracle? Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-04 Chapter 12:  Dropping Datafiles.

If you do not wish to follow any of these procedures, there are other things 
that can be done besides dropping the tablespace.

- If the reason you wanted to drop the file is because you mistakenly created 
  the file of the wrong size, then consider using the RESIZE command.  
  See 'Related Documents' below.

- If you really added the datafile by mistake, and Oracle has not yet allocated
  any space within this datafile, then you can use ALTER DATABASE DATAFILE 
   RESIZE; command to make the file smaller than 5 Oracle blocks. If 
  the datafile is resized to smaller than 5 oracle blocks, then it will never 
  be considered for extent allocation. At some later date, the tablespace can 
  be rebuilt to exclude the incorrect datafile.


RELATED DOCUMENTS
----------------- Note 30910.1 -  Recreating database objects Note 1013221.6 - Recovering from a lost datafile in a ROLLBACK tablespace Note 198640.1 - How to Recover from a Lost Datafile with Different Scenarios Note 1060605.6 - Recover A Lost Datafile With No Backup Note 1029252.6 - How to resize a datafile


HOW TO RECOVER OFFLINE DROPPED DATAFILE IN ARCHIVELOG MODE (文件 ID 286355.1)

The information in this document applies to: 

Symptoms

You have offline dropped a datafile in archivelog mode
You know that once you drop a datafile you need to recreate the tablespace containing that datafile
You can not do that as this is a Big tablespace 
You want that datafile to be again part of the database
even though you do not want that datafile to contain any objects
 
Changes

You can recover the offline datafile and then make it online
further as you do not want any objects to be allocated to that datafile 
you can resize it to a very small size ( remember it can only be done if the datafile is empty .........
you can not resize a datafile below it's high water mark)


Example
==========


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /h01/app/oracle/product/9.2.0/dbs/arch
Oldest online log sequence     207
Next log sequence to archive   209
Current log sequence           209

=======================
shows DB is in archivelog mode
=======================

SQL> alter database datafile 44 offline drop;

Database altered.


SQL> select file#,status from v$datafile where file#=44;

     FILE# STATUS
---------- -------
        44 RECOVER  ======> status in controlfile is recover

SQL> c/datafile/datafile_header
  1* select file#,status from v$datafile_header where file#=44
SQL> /

     FILE# STATUS
---------- -------
        44 OFFLINE  ========>  status in file_header is offline


switch some log file
sql> alter system switch logfile ;
system altered

.
.
.
apply the log ( it will only ask for the log/ corresponding archivelog  which was 
online at that time
It wont ask you to apply any other archivelog

SQL> recover datafile 44;
ORA-00279: change 8252199007514 generated at 10/18/2004 14:21:47 needed for thread 1
ORA-00289: suggestion : /h01/app/oracle/product/9.2.0/dbs/arch1_216.dbf
ORA-00280: change 8252199007514 for thread 1 is in sequence #216


Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS      FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ----------- ------------- ---------
         1          1        248  104857600          1 YES INACTIVE       8.2522E+12 18-OCT-04
         2          1        250  104857600          1 NO  CURRENT        8.2522E+12 18-OCT-04
         3          1        249  104857600          1 YES ACTIVE         8.2522E+12 18-OCT-04



SQL> select file#,status from v$datafile where file#=44;

     FILE# STATUS
---------- -------
        44 OFFLINE

SQL> select file#,status from v$datafile_header where file#=44;

     FILE# STATUS
---------- -------
        44 OFFLINE

SQL> alter database datafile 44 online;

Database altered.

SQL> select file#,status from v$datafile_header where file#=44;

     FILE# STATUS
---------- -------
        44 ONLINE

SQL> select file#,status from v$datafile where file#=44;

     FILE# STATUS
---------- -------
        44 ONLINE


SO the datafile is online

The only case in which the offline dropped datafile can not be online is 
when you have added to many datafiles in the database after offline drop 






Cause

From Documentation
====================

If the database is in NOARCHIVELOG mode, you must specify the DROP clause to take a datafile
 offline. However, the DROP clause does not remove the datafile from the database.
To do that, you must drop the tablespace in which the datafile resides. Until you
 do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE.
    
 If the database is in ARCHIVELOG mode, Oracle ignores the DROP keyword.







來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2124605/,如需轉載,請註明出處,否則將追究法律責任。

相關文章