oracle 10g R2 drop empty datafile

最新推荐文章于 2022-02-07 01:37:02 发布

cucanqi9387

最新推荐文章于 2022-02-07 01:37:02 发布

阅读量72

点赞数

在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
--------------------------------------------------------------------------------

6 rows selected.

SQL> select file_name from dba_data_files;

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

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
--------------------------------------------------------------------------------

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

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>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-262961/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-262961/