Drop Tablespace Hangs On Row Cache Enqueue

54xw

今天接到一个临时任务，使用transportable tablespace 做数据库的迁移，从solaris到linux 。从10g到11g 。convert数据文件到ASM diskgroup的job正在执行，我要做的是等convert完成以后做metadata的导入。

 

然后由于前期工作没有做细致，许多role没有建，当我导入的时候出现了很多报错：

 

$cat imp_gwmtkpp_n.log | grep -i "ORA-" | sort |uniq
ORA-01917: user or role 'APPL_OWNER_ROLE' does not exist
ORA-01917: user or role 'APPL_ROLE' does not exist
ORA-01917: user or role 'BATCH_ROLE' does not exist
ORA-01917: user or role 'COGNOS_READ_ONLY_ROLE' does not exist
ORA-01917: user or role 'COGNOS_ROLE' does not exist
ORA-01917: user or role 'GWMETL_ROLE' does not exist
ORA-01917: user or role 'MONITOR_READONLY_ROLE' does not exist
ORA-01917: user or role 'MONITOR_ROLE' does not exist
ORA-01917: user or role 'OFFLINE_ROLE' does not exist
ORA-01917: user or role 'READONLY_ROLE' does not exist
ORA-01917: user or role 'SUSTAIN_ROLE' does not exist
ORA-01917: user or role 'SUSTAIN_ROLE_TEAMMEMBER' does not exist
ORA-01917: user or role 'SUSTAIN_ROLE_TL' does not exist
ORA-01917: user or role 'TESTING_MAINTENANCE_ROLE' does not exist
ORA-01917: user or role 'UPDATE_SCH_JOB_PARAMETERS' does not exist
ORA-02264: name already used by an existing constraint
ORA-02270: no matching unique or primary key for this column-list
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_STATS", line 10234
ORA-20001: Invalid or inconsistent input values

 

这时候我必须将重新创建这些角色，删除表空间并保留数据文件，然后删除表空间时却hung住了。

 

SQL> drop tablespace INDEX_DBO_32K INCLUDING CONTENTS keep datafiles;

 

$ dbi sessions

   SID    SERIAL# SPID     USERNAME        STATUS   SQL_HASH_VALUE EVENT
------ ---------- -------- --------------- -------- -------------- ------------------------------------------------------------
   961       2971 48374    SYS             INACTIVE              0 SQL*Net message from client
  1105          5 26110    SYS             INACTIVE              0 SQL*Net message from client
   985        163 47977    SYS             ACTIVE       2529568628 row cache lock
camcgmgssmdb005:gwmtkpp1:elcaro:$ dbi longops

       SID    SERIAL# USERNAME              SOFAR  TOTALWORK TIME_REMAINING MESSAGE
---------- ---------- ---------------- ---------- ---------- -------------- --------------------------------------------------------------------------------
       985        163 SYS                     100       1390 1.6 hrs        Index Fast Full Scan:  SYS.OBJ$: 100 out of 1390 Blocks done

 

尝试重启数据库，还是不行。metalink上查了一下，这是一个Bug 6264045 (https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=833224.1)

 

看来只好等到表空间删除之后才能再接着做了。