误删 drop recyclebin$ 后又重建，不能删除表空间tablespace

yangang_q

drop recyclebin$ 后又根据其他库相同版本recyclebin$ 结构进行了 重建，然后就不能删除其中一个表空间了，该表空间中有已经删除的对象，需要怎么处理

Maclean Liu(刘相兵

提问的 基本 要素  ：版本、日志 、报错信息

yangang_q

Thu May 10 16:04:57 2012
DROP TABLESPACE TBS_CRM_TS_S1  including contents
performing DML/DDL operation over object in bin.
ORA-604 signalled during: DROP TABLESPACE TBS_CRM_TS_S1  including contents
...
Thu May 10 16:10:56 2012
DROP TABLESPACE TBS_CRM_TS_S1  including contents
performing DML/DDL operation over object in bin.
ORA-604 signalled during: DROP TABLESPACE TBS_CRM_TS_S1  including contents
...
Thu May 10 16:14:25 2012
alter tablespace TBS_CRM_TS_S1 online
Completed: alter tablespace TBS_CRM_TS_S1 online
Thu May 10 16:14:57 2012
drop tablespace TBS_CRM_TS_S1 including contents
performing DML/DDL operation over object in bin.
ORA-604 signalled during: drop tablespace TBS_CRM_TS_S1 including contents
...
Thu May 10 16:15:34 2012
performing DML/DDL operation over object in bin.
Thu May 10 16:19:04 2012
drop tablespace TBS_CRM_TS_S1 including contents
performing DML/DDL operation over object in bin.
ORA-604 signalled during: drop tablespace TBS_CRM_TS_S1 including contents
...
Thu May 10 16:36:35 2012
performing DML/DDL operation over object in bin.
Thu May 10 16:37:30 2012
performing DML/DDL operation over object in bin.
Thu May 10 16:40:03 2012
drop tablespace TBS_CRM_TS_S1 including contents
performing DML/DDL operation over object in bin.
ORA-604 signalled during: drop tablespace TBS_CRM_TS_S1 including contents
...
以上是部分 alter.log  日志

Trace file /oracle/diag/rdbms/bonctest/bonctest/trace/bonctest_ora_36895.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2.0.4
System name:        Linux
Node name:        nmtest.bonc
Release:        2.6.32-71.el6.x86_64
Version:        #1 SMP Wed Sep 1 01:33:01 EDT 2010
Machine:        x86_64
Instance name: bonctest
Redo thread mounted by this instance: 1
Oracle process number: 52
Unix process pid: 36895, image: oracle@nmtest.bonc


*** 2012-05-10 16:14:25.401
*** SESSION ID:(961.241) 2012-05-10 16:14:25.401
*** CLIENT ID:() 2012-05-10 16:14:25.401
*** SERVICE NAME:(bonctest) 2012-05-10 16:14:25.401
*** MODULE NAME:(PL/SQL Developer) 2012-05-10 16:14:25.401
*** ACTION NAME:(SQL Window - SELECT OWNER, SEGME) 2012-05-10 16:14:25.401

Datafile header for locally managed datafile 7 upgraded to 10.2
以上是相关trc文件

yangang_q

一下附件是相关的错误

Maclean Liu(刘相兵

oradebug setmypid;
oradebug unlimit;
oradebug event  38301  trace name errorstack level 4;

RUN YOUR STATEMENT:
drop tablespace TBS_CRM_TS_S1  including contents;

oradebug tracefile_name;


上传 压缩后的TRACE文件

yangang_q

刘大，已经按照你的吩咐 上传了相关文件，希望你尽快处理一下，非常感谢。

Maclean Liu(刘相兵

ODM DATA :

10.2.0.4 + Linux x86_64

ORA-38301: 无法对回收站中的对象执行 DDL/DML
----- Current SQL Statement for this session (sql_id=7p26qq2w3j2u2) -----
drop table "NEW_SRC"."BIN$v6e/f8I1Fx/gQAwKBQBMtw==$0" cascade constraints purge



stack call => dtbdrv=> kkdlLockTblOptViaEV =>kkdllk0 => ksesec0 => error




action plan:

执行以下命令：
conn / as sysdba
purge recyclebin;
alter system set recyclebin=off scope=both;
drop table "NEW_SRC"."BIN$v6e/f8I1Fx/gQAwKBQBMtw==$0" cascade constraints purge
drop tablespace TBS_CRM_TS_S1  including contents;

yangang_q

recyclebin 初始化参数不能修改

Maclean Liu(刘相兵

ODM TEST:


SQL> select  * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production



SQL> conn maclean/maclean

Connected.
SQL> SQL>
SQL>
SQL>
SQL> create table drop_recycelbin as select * From dba_tables;

Table created.


SQL> drop table drop_recycelbin;

Table dropped.


SQL> select * from sys.recyclebin$;

      OBJ#     OWNER# ORIGINAL_NAME                     OPERATION      TYPE#
---------- ---------- -------------------------------- ---------- ----------
       TS#      FILE#     BLOCK# DROPTIME     DROPSCN
---------- ---------- ---------- --------- ----------
PARTITION_NAME                        FLAGS    RELATED         BO   PURGEOBJ
-------------------------------- ---------- ---------- ---------- ----------
  BASE_TS# BASE_OWNER#      SPACE       CON#     SPARE1     SPARE2     SPARE3
---------- ----------- ---------- ---------- ---------- ---------- ----------
     81362         90 DROP_RECYCELBIN                           0          1
         4          4     140426 10-MAY-12    4380607
                                         30      81362      81362      81362
                              112          0

                                                          
                                                          
SQL> select * from dba_recyclebin;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME                    OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME                        CREATETIME          DROPTIME
------------------------------ ------------------- -------------------
   DROPSCN PARTITION_NAME                   CAN CAN    RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT      SPACE
------------ ----------
MACLEAN                        BIN$v7IAIdjrR2XgQ7IBqMBP6Q==$0
DROP_RECYCELBIN                  DROP      TABLE
USERS                          2012-05-10:12:19:09 2012-05-10:12:20:39
   4380607                                  YES YES      81362       81362
       81362        112





SQL> create table dup_recycle as select * from sys.recyclebin$;

Table created.


SQL> drop table recyclebin$;

Table dropped.


11.2 开始  recyclebin$ 的DDL 在dsqlddl.bsq 中


rem table used to store the dropped objects which are still not purged
create table recyclebin$
(
  obj#                  number not null,           /* original object number */
  owner#                number not null,                /* owner user number */
  original_name         varchar2(32),                /* Original Object Name */
  operation             number not null,            /* Operation carried out */
                                                                /* 0 -> DROP */
                                            /* 1 -> TRUNCATE (not supported) */
  type#                 number not null,          /* object type (see KQD.H) */
  ts#                   number,                         /* tablespace number */
  file#                 number,                /* segment header file number */
  block#                number,               /* segment header block number */
  droptime              date,                /* time when object was dropped */
  dropscn               number,           /* SCN of Tx which caused the drop */
  partition_name        varchar2(32),       /* Name of the partition dropped */
                                                           /* NULL otherwise */
  flags                 number,               /* flags for undrop processing */
  related               number not null,    /* obj one level up in heirarchy */
  bo                    number not null,                      /* base object */
  purgeobj              number not null,   /* obj to purge when purging this */
  base_ts#              number,            /* Base objects Tablespace number */
  base_owner#           number,                 /* Base objects owner number */
  space                 number,       /* number of blocks used by the object */
  con#                  number,       /* con#, if index is due to constraint */
  spare1                number,
  spare2                number,
  spare3                number
)
/
create index recyclebin$_obj on recyclebin$(obj#)
/
create index recyclebin$_ts on recyclebin$(ts#)
/
create index recyclebin$_owner on recyclebin$(owner#)
/          


conn / as sysdba

SQL> rem table used to store the dropped objects which are still not purged
SQL> create table recyclebin$
  2  (
  3    obj#                  number not null,           /* original object number */
  4    owner#                number not null,                /* owner user number */
  5    original_name         varchar2(32),                /* Original Object Name */
  6    operation             number not null,            /* Operation carried out */
  7                                                                  /* 0 -> DROP */
  8                                              /* 1 -> TRUNCATE (not supported) */
  9    type#                 number not null,          /* object type (see KQD.H) */
10    ts#                   number,                         /* tablespace number */
11    file#                 number,                /* segment header file number */
12    block#                number,               /* segment header block number */
13    droptime              date,                /* time when object was dropped */
14    dropscn               number,           /* SCN of Tx which caused the drop */
15    partition_name        varchar2(32),       /* Name of the partition dropped */
16                                                             /* NULL otherwise */
17    flags                 number,               /* flags for undrop processing */
18    related               number not null,    /* obj one level up in heirarchy */
19    bo                    number not null,                      /* base object */
20    purgeobj              number not null,   /* obj to purge when purging this */
21    base_ts#              number,            /* Base objects Tablespace number */
22    base_owner#           number,                 /* Base objects owner number */
23    space                 number,       /* number of blocks used by the object */
24    con#                  number,       /* con#, if index is due to constraint */
25    spare1                number,
26    spare2                number,
27    spare3                number
28  )
29  /
create index recyclebin$_obj on recyclebin$(obj#)
/
create index recyclebin$_ts on recyclebin$(ts#)

Table created.

SQL>   2  /
create index recyclebin$_owner on recyclebin$(owner#)
/         
Index created.

SQL>   2  
Index created.

SQL>   2  

Index created.






SQL> select * from sys.recyclebin$;

no rows selected



SQL> drop table MACLEAN."BIN$v7IAIdjrR2XgQ7IBqMBP6Q==$0" purge;
drop table MACLEAN."BIN$v7IAIdjrR2XgQ7IBqMBP6Q==$0" purge
                   *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin


rename "BIN$v7IAIdjrR2XgQ7IBqMBP6Q==$0" to  abc;
rename "BIN$v7IAIdjrR2XgQ7IBqMBP6Q==$0" to  abc
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

SQL> ALTER SYSTEM SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF
                                *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option



SQL> ALTER SYSTEM SET recyclebin = OFF scope=spfile;

System altered.


11gR2 中 recyclebin 参数 不在动态可修改， 需要重启实例


SQL> startup force;
ORACLE instance started.

Total System Global Area  939495424 bytes
Fixed Size                  2233960 bytes
Variable Size             708839832 bytes
Database Buffers          222298112 bytes
Redo Buffers                6123520 bytes
Database mounted.
Database opened.



SQL>  drop table MACLEAN."BIN$v7IAIdjrR2XgQ7IBqMBP6Q==$0" purge;

Table dropped.




Solution 1:

ALTER SYSTEM SET recyclebin = OFF scope=spfile;

reboot instance

  drop table  in recyclebin

yangang_q

conn / as sysdba
purge recyclebin;
alter system set recyclebin=off scope=both;---both 会报错，不能修改 （spfile+memory）

--禁用回收站功能
alter system set recyclebin=off scope=spfile;--可以正常修改

这样修改后需重启数据库才能生效 ？？？ （重启后生效）

SELECT * FROM V$SYSTEM_PARAMETER WHERE NAME LIKE '%RECYCLEBIN%';

shutdown immediate
startup mount
alter database open;

--删除该用户下已经在回收站的表，因为'STAGE', 'NEW_SRC' 默认表空间是 TBS_CRM_TS_S1，

SELECT * FROM DBA_SEGMENTS WHERE OWNER IN ('STAGE', 'NEW_SRC');
drop table "NEW_SRC"."BIN$v6e/f8IyFx/gQAwKBQBMtw==$0" cascade constraints purge
drop table "NEW_SRC"."BIN$v6e/f8IzFx/gQAwKBQBMtw==$0" cascade constraints purge
drop table "NEW_SRC"."BIN$v6e/f8IxFx/gQAwKBQBMtw==$0" cascade constraints purge

drop tablespace TBS_CRM_TS_S1  including contents;--删除成功