oracle表空间的奇怪问题，你遇到过吗？

各位大虾：
    我在一个表空间里存放了两张表的索引，分别是A表索引和B表索引，其中A表是我需要的表，B表是个备份表。现在我做了如下操作：
1.truncate table b；发现表B数据被删除，但是查看表空间发现B表的索引还在，并占用一定空间。
2.drop table b；发现B表被删除，但是查看表空间发现B表的索引还在，并占用一定空间。
3.查看索引状态，发现B表的索引IDX_B存在，且状态正常，重建该索引报错：
 ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
4.无奈，直接删除该索引，报错：
 ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
5.查看锁表，因B表已被删除，所以查询不到B表锁表状况。

请教各位大虾这是什么情况？

25 个解决方案

#1

有没有高手知道怎么解决啊？不知道的表示一下关注我也感谢啦~~~

#2

truncate table b之后表分析一下呢

#3

如何分析一下？

#4

-- 估计是死锁了，查看当前有哪些死锁的会话，然后将其kill掉：
--- Oracle 查看死锁信息：
-- http://www.javaeye.com/wiki/topic/319593

SELECT SN.SID, 
       SN.SERIAL#, 
       SN.USERNAME, 
       SN.MACHINE, 
       SN.TERMINAL, 
       SN.PROGRAM, 
       SN.TYPE 
FROM V$SESSION SN 
WHERE SN.SID IN (SELECT LK.SID FROM V$LOCK LK); 

-------------------------------------------------------------------------------------------------------

--查询所有的死锁： 
SELECT * FROM V$LOCK; 


--查询所有的会话： 
SELECT * FROM V$SESSION; 


--查询所有的死锁的会话： 
SELECT SN.SID, 
       SN.SERIAL#, 
       SN.USERNAME, 
       SN.MACHINE, 
       SN.TERMINAL, 
       SN.PROGRAM, 
       SN.TYPE 
FROM V$SESSION SN 
WHERE SN.SID IN (SELECT LK.SID FROM V$LOCK LK); 

------------------------------------------------------------
SELECT 'ALTER SYSTEM KILL SESSION '''||SN.SID||','||SN.SERIAL#||''';'
FROM V$SESSION SN 
WHERE SN.SID IN (SELECT LK.SID FROM V$LOCK LK); 

ALTER SYSTEM KILL SESSION '1266,3834';
ALTER SYSTEM KILL SESSION '1282,2303';
ALTER SYSTEM KILL SESSION '1494,1';
ALTER SYSTEM KILL SESSION '1495,1';
ALTER SYSTEM KILL SESSION '1496,1';
ALTER SYSTEM KILL SESSION '1497,1';

--杀掉死锁会话： 
ALTER SYSTEM KILL SESSION 'SID,SERIAL#'; 

ALTER SYSTEM KILL SESSION '1323,13189';
ALTER SYSTEM KILL SESSION '1350,27497';
ALTER SYSTEM KILL SESSION '1367,13850';
ALTER SYSTEM KILL SESSION '1369,14223';
ALTER SYSTEM KILL SESSION '1440,6115';
ALTER SYSTEM KILL SESSION '1469,20601';
ALTER SYSTEM KILL SESSION '1478,21239';
ALTER SYSTEM KILL SESSION '1494,1';
ALTER SYSTEM KILL SESSION '1495,1';
ALTER SYSTEM KILL SESSION '1496,1';
ALTER SYSTEM KILL SESSION '1497,1';

#5

引用 4 楼 luoyoumou 的回复:

SQL code
-- 估计是死锁了，查看当前有哪些死锁的会话，然后将其kill掉：
--- Oracle 查看死锁信息：
-- http://www.javaeye.com/wiki/topic/319593

SELECT SN.SID, 
       SN.SERIAL#, 
       SN.USERNAME, 
       SN.MACHINE, 
       ……

兄弟，我的表B已经drop了，何来死锁？请看我的第五步操作，我已经考虑死锁了。

#6

这是一个很正常的问题，
你drop掉一个表，并没有drop其index,比如你可以创建一个表，建一个主键PK，那么PK就会自动创建一个index
当你drop掉表的时候，索引仍然存在，即存在这个索引对象，如何你想再建玲一个索引，名字是之前那个PK的index，就会爆对象已经存在。

#7

1、truncate表，索引也会truncate，但不会删除索引，也不会改变索引的状态，
也就是说索引状态仍然有效。
2、如果删除表，索引也会随之删除。
3、查询一下v$locked_object视图，是否有锁定对象。

#8

引用 6 楼 gelyon 的回复:

这是一个很正常的问题，
你drop掉一个表，并没有drop其index,比如你可以创建一个表，建一个主键PK，那么PK就会自动创建一个index
当你drop掉表的时候，索引仍然存在，即存在这个索引对象，如何你想再建玲一个索引，名字是之前那个PK的index，就会爆对象已经存在。

drop 一个表，这个表的索引不随之一起删除吗？
假如这个索引还存在，那么我drop 该索引的时候为什么不行呢？
这问题如何解决？

#9

引用 7 楼 tangren 的回复:

1、truncate表，索引也会truncate，但不会删除索引，也不会改变索引的状态，
也就是说索引状态仍然有效。
2、如果删除表，索引也会随之删除。
3、查询一下v$locked_object视图，是否有锁定对象。

我查询了v$locked_object视图，因为这个表已经被drop，索引没有该表的锁表信息。

#10

引用 8 楼 a0o00o0a 的回复:

引用 6 楼 gelyon 的回复:
这是一个很正常的问题，
你drop掉一个表，并没有drop其index,比如你可以创建一个表，建一个主键PK，那么PK就会自动创建一个index
当你drop掉表的时候，索引仍然存在，即存在这个索引对象，如何你想再建玲一个索引，名字是之前那个PK的index，就会爆对象已经存在。

drop 一个表，这个表的索引不随之一起删除吗？
假如这个索引还存……

sorry,我想错了

#11

参考：
1.将你的那个表空间里面的数据全部备份，
2.然后：Alter tablespace  表空间名称   offline;
3.手动删除表空间
4.建立一个相同的表空间，在将备份的数据加进去

#12

引用 11 楼 bobo12082119 的回复:

参考：
1.将你的那个表空间里面的数据全部备份，
2.然后：Alter tablespace 表空间名称 offline;
3.手动删除表空间
4.建立一个相同的表空间，在将备份的数据加进去

这倒是个解决思路，不过没有查出原因所在啊

#13

我也遇到了，期待解决方法

#14

大家来看看啊

#15

讨论了半天，你数据库版本多少？

#16

http://linux.chinaitlab.com/special/linuxcom/Index.html

没有

#17

资源正忙，可以先杀死这个session，然后再做其他操作。
alter system kill session   '140,417';
 truncate和 delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.
2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.
   truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动,显然drop语句将表所占用的空间全部释放
truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage;   truncate会将高水线复位(回到最开始).

#18

我怎么看着 好像还是没有解决啊。

#19

分析一下表吧,我刚才做了一下这个测试.
drop table b之后索引也不在了.
分析表之后,被占用的表空间也得到释放.

语句如下：
analyze table tablename compute statistics 

希望对你有用

#20

是否是正在进行删除索引？。

#21

没遇到过这种情况，不好说。
建议LZ可以把用sql plus步骤展示一下。而非文字说明。

另外我的想法，drop表b时，oracle负责把idx_b也删除。并且如果drop表B成功，在逻辑上idx_b也被删除了。那是不是立刻就表现在相关的数据字段view中，或者是否立即收缩空间，我觉得是oracle的事情，或许他还有一下其他的事情要等待(比如锁)，但是逻辑上索引已经删除，不可访问。这是没有问题的。
所以我感觉你可以不用故意去访问的而造成相关的怪上加怪的问题。

#22

没见过这个问题，但是却又经常发生，因为删表很常见，期待解决方案

#23

最近比较忙，等我有空分析分析在结贴哈！

#24

可能是数据库的问题，之前这里有一套RAC环境，由于死锁(library cache lock)，导致任何一个实例执行任何语句都没有报错，但是都没有执行成功，夯在那里了，一些情况和LZ描述的情况一致，shutdown immediate都不起作用，最后kill pmon进程后，一个实例重启后，RAC正常。。。

#25

该问题已解决。我的数据库做的rac，有一个节点经常宕机，初步认定为unix操作系统的原因，请ibm的人升级后，重启服务器和数据库，问题解决。