数据文件被删除如何删除表空间

LDean

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

一个数据文件被人删除，已经恢复不回来了，使用offline drop了表空间，但因为应用还要用这个用户，就想删掉表空间重建，但是删除不掉，网上找到一篇blog直接从controlfile里删掉，尝试了一下，结果现在变成这样
SQL> select file_name,file_id,status from dba_data_files;

FILE_NAME                                                       FILE_ID STATUS
------------------------------------------------------------ ---------- ---------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\APP.DBF                       6 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\ORI.DBF                       5 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\USERS01.DBF                   4 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\SYSAUX01.DBF                  3 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\UNDOTBS01.DBF                 2 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\SYSTEM01.DBF                  1 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00007                   7 AVAILABLE
后来又加了一个数据文件进去，可以加入，现在可以先建表，但是以前存在的表重建会提示已存在


SQL> select file_name,file_id,status from dba_data_files;

FILE_NAME                                                       FILE_ID STATUS
------------------------------------------------------------ ---------- ---------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\APP.DBF                       6 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\ORI.DBF                       5 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\USERS01.DBF                   4 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\SYSAUX01.DBF                  3 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\UNDOTBS01.DBF                 2 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\AKBHDB\SYSTEM01.DBF                  1 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00007                   7 AVAILABLE
D:\SDE1.DBF                                                           8 AVAILABLE

现在能想到最坏的办法就准备备份删库重建了，想问问有其他办法嘛
现在D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00007是不存在的，我也曾尝试从controlfile里再次删除D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00007这条，但是启动后还是会有，有没有办法把这个表空间删掉。
谢谢

psufnxk2000

SQL> startup
ORACLE 例程已经启动。

Total System Global Area  603979776 bytes
Fixed Size                  1250404 bytes
Variable Size             197135260 bytes
Database Buffers          402653184 bytes
Redo Buffers                2940928 bytes
数据库装载完毕。
ORA-01157: 无法标识/锁定数据文件 7 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 7: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\SONG\PP.DB

SQL> alter database datafile 7 offline ;

数据库已更改。

SQL> alter database open;

数据库已更改。


SQL> drop tablespace pp including contents;

表空间已删除。

SQL>

Maclean Liu(刘相兵

1.

drop tablespace XX including datafiles and contents 的时候报什么错？

LDean

SQL> drop tablespace sde including contents and datafiles;
drop tablespace sde including contents and datafiles
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace

我重新拿冷备恢复了一遍，冷备里就少这个数据文件没法子，offline后删除报这个错

Maclean Liu(刘相兵

[oracle@vrh8 ~]$ oerr ora 29857   
29857, 00000, "domain indexes and/or secondary objects exist in the tablespace"
// *Cause: An attempt was made to drop a tablespace which contains secondary
//         objects and/or domain indexes.
// *Action: Drop the domain indexes in his tablespace. Also, find the domain
//          indexes which created secondary objects in this tablespace and
//          drop them. Then try dropping the tablespace.

表空间上有域索引或2级对象， 跑一下这个脚本

1. set heading off
   
2. set feedback off
   
3. set pagesize 0
   
4. spool dis_cons.sql
   
5. select 'ALTER TABLE '||c.owner||'.'||c.table_name||
   
6. ' disable constraint '||c.constraint_name||' cascade;'
   
7. from dba_constraints c, dba_indexes i
   
8. where c.constraint_name=i.index_name
   
9. and i.tablespace_name='SDE'
   
10. and i.owner = c.owner;
    
11. spool off
    
12. set heading on
    
13. set feedback on
    
14. set pagesize 100
    
15. /

复制代码

用以上生成的语句 把contstraint 约束都禁用掉， 再drop tablespace

LDean

SQL> ALTER TABLE SDECLIENT.S482_IDX$ disable constraint S482$_IX1 cascade;
ALTER TABLE SDECLIENT.S482_IDX$ disable constraint S482$_IX1 cascade
*
第 1 行出现错误:
ORA-25188: 对于索引表或排序散列簇, 无法删除/禁用/延迟主键约束条件

能禁用一部分，还有一部分报错，IOT不能禁用啊
尝试了truncate


SQL> truncate TABLE SDECLIENT.S482_IDX$;
truncate TABLE SDECLIENT.S482_IDX$
                         *
第 1 行出现错误:
ORA-00376: 此时无法读取文件 7
ORA-01110: 数据文件 7: 'D:\SDE.DBF'

ORA-25188 cannot drop/disable/defer the primary key constraint for index-organized tables

Cause: An attempt was made to DROP, DISABLE, or DEFER the primary key constraint for an index-only table. This is not allowed.

Action: Do not DROP, DISABLE, or DEFER the primary key constraint for an index-only table.

LDean

表可以删除

SQL> drop TABLE SDECLIENT.S483_IDX$;

表已删除。

SQL> drop tablespace sde including contents;
drop tablespace sde including contents
*
第 1 行出现错误:
ORA-29857: 表空间中存在域索引和/或次级对象
但还是报错，跑了下脚本已经没有行了


SQL> set heading off
SQL> set feedback off
SQL> set pagesize 0
SQL> spool dis_cons.sql
SQL> select 'ALTER TABLE '||c.owner||'.'||c.table_name||
  2  ' disable constraint '||c.constraint_name||' cascade;'
  3  from dba_constraints c, dba_indexes i
  4  where c.constraint_name=i.index_name
  5  and i.tablespace_name='SDE'
  6  and i.owner = c.owner;
SQL> spool off
SQL> set heading on
SQL> set feedback on
SQL> set pagesize 100
SQL> /

未选定行

Maclean Liu(刘相兵

再查一下 表空间上存在哪些 不常见的对象。

总之这个问题和 datafile offline drop关联不大。

LDean

查了下，现在所有的东西能删的都删掉了，剩下的删除禁用都报错
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00376: 此时无法读取文件 7
ORA-01110: 数据文件 7: 'D:\SDE.DBF'
ORA-06512: 在 line 1

麻烦再帮我看下，谢谢

bibosea

先drop user，然后 drop tablespace呢？

Maclean Liu(刘相兵

drop tablespace sde including contents and datafiles;

现在 报什么错 ？

LDean

SQL> drop user sde cascade;
drop user sde cascade
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-21700: 对象不存在或标记为删除


SQL> drop tablespace sde including contents and datafiles;
drop tablespace sde including contents and datafiles
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-21700: 对象不存在或标记为删除

teapot

查一下表空间ts#号，
到sys.seg$中查一下ts#里，有没有什么东西。
有可能是回收站的东西

不了峰

drop时做个trace，可能可以发现原因