oracle 10g R2 drop empty datafile

在oracle 10g以前要是對某個表空間誤加了個資料檔案,想刪除掉這個時候是不行的,通常的做法是offline或者resize 到

一個很小的值,這對我們管理帶來很多不方便, 10g 就可以了,以下是測試驗證過程:

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 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create tablespace test;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q3D0H_.DBF

SQL> alter tablespace test add datafile;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q3D0H_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q4RY8_.DBF

6 rows selected.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q3D0H_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q4RY8_.DBF

6 rows selected.

SQL> alter tablespace test drop datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\
DATAFILE\O1_MF_TEST_422Q4RY8_.DBF';

Tablespace altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q3D0H_.DBF

SQL>

2.oracle9i 測試

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed May 7 15:56:30 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> create tablespace test datafile 'd:\test01.dbf' size 5M;

Tablespace created.

SQL> alter tablespace test add datafile 'd:\test02.dbf' size 5M;

Tablespace altered.

SQL> alter tablespace test drop datafile 'd:\test02.dbf';
alter tablespace test drop datafile 'd:\test02.dbf'
                      *
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option

SQL>