删除某个表空间中的某个数据文件 还没有数据在这个datafile上的话: 1) shutdown (abort) 2) startup mount 3) alter database datafile 'fullpath_of_removed_DF' offline drop 4) alter database open SQL> conn internal 已連接 SQL> shutdown abort 已關閉 ORACLE 執行項次. SQL> startup mount 已啟動 ORACLE 執行項次. Total System Global Area 40159260 bytes Fixed Size 75804 bytes Variable Size 26898432 bytes Database Buffers 13107200 bytes Redo Buffers 77824 bytes 資料庫已掛載. SQL> alter database datafile 'e:\oracle\oradata\ADM_DATA1..ORA' offline drop; 資料庫已被更改 SQL> alter database open; 資料庫已被更改 SQL> SELECT * FROM DBA_DATA_FILES; FILE_NAME --------------------------------------------------------- E:\ORACLE\ORADATA\ORCL\USERS01.DBF E:\ORACLE\ORADATA\ORCL\DR01.DBF E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF E:\ORACLE\ORADATA\ORCL\INDX01.DBF E:\ORACLE\ORADATA\ORCL\RBS01.DBF E:\ORACLE\ORADATA\ORCL\TEMP01.DBF E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF E:\ORACLE\ORADATA\ORCL\TEST.ORA E:\ORACLE\ORADATA\ORCL\ADM_DATA.ORA E:\ORACLE\ORADATA\ORCL\ADM_IDX.ORA E:\ORACLE\ORADATA\ADM_DATA1..ORA FILE_NAME --------------------------------------------------------- E:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY.ORAE:\ORACLE\ORADATA\ADM_DATA1..ORA 这个数据文件只存在数据字典中,但确实是没有内容,而且应该不占空间。 你用: select sum(bytes) from dba_data_files where file_name='E:\ORACLE\ORADATA\ADM_DATA1..ORA '; 方法只是不用这个数据文件 这个数据文件的信息还是存放在控制文件之中的。 最好的办法就是重新创建表空间 用exp/imp的方法就可以的 不一定要用导出表空间方式,导出上面的表即可Another method: (from Tom) --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 <filename> 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.
drop tablespace 表空间名;
以dba连入
sql>ALTER DATABASE DATAFILE 'd:\his\his021.dat' OFFLINE DROP;
1) shutdown (abort)
2) startup mount
3) alter database datafile 'fullpath_of_removed_DF' offline drop
4) alter database open SQL> conn internal
已連接
SQL> shutdown abort
已關閉 ORACLE 執行項次.
SQL> startup mount
已啟動 ORACLE 執行項次. Total System Global Area 40159260 bytes
Fixed Size 75804 bytes
Variable Size 26898432 bytes
Database Buffers 13107200 bytes
Redo Buffers 77824 bytes
資料庫已掛載.
SQL> alter database datafile 'e:\oracle\oradata\ADM_DATA1..ORA' offline drop; 資料庫已被更改 SQL> alter database open; 資料庫已被更改
SQL> SELECT * FROM DBA_DATA_FILES; FILE_NAME
---------------------------------------------------------
E:\ORACLE\ORADATA\ORCL\USERS01.DBF
E:\ORACLE\ORADATA\ORCL\DR01.DBF
E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
E:\ORACLE\ORADATA\ORCL\INDX01.DBF
E:\ORACLE\ORADATA\ORCL\RBS01.DBF
E:\ORACLE\ORADATA\ORCL\TEMP01.DBF
E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
E:\ORACLE\ORADATA\ORCL\TEST.ORA
E:\ORACLE\ORADATA\ORCL\ADM_DATA.ORA
E:\ORACLE\ORADATA\ORCL\ADM_IDX.ORA
E:\ORACLE\ORADATA\ADM_DATA1..ORA FILE_NAME
---------------------------------------------------------
E:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY.ORAE:\ORACLE\ORADATA\ADM_DATA1..ORA
这个数据文件只存在数据字典中,但确实是没有内容,而且应该不占空间。
你用:
select sum(bytes) from dba_data_files
where file_name='E:\ORACLE\ORADATA\ADM_DATA1..ORA '; 方法只是不用这个数据文件
这个数据文件的信息还是存放在控制文件之中的。
最好的办法就是重新创建表空间
用exp/imp的方法就可以的
不一定要用导出表空间方式,导出上面的表即可Another method: (from Tom) --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
<filename> 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.