Drop Tablespace Hangs On Row Cache Enqueue
<DIV>今天接到一个临时任务，使用transportable tablespace 做数据库的迁移，从solaris到linux 。从10g到11g 。convert数据文件到ASM diskgroup的job正在执行，我要做的是等convert完成以后做metadata的导入。</DIV><DIV> </DIV>
<DIV>然后由于前期工作没有做细致，许多role没有建，当我导入的时候出现了很多报错：</DIV>
<DIV> </DIV>
<DIV><FONT face=Arial color=#808080 size=1>$cat imp_gwmtkpp_n.log | grep -i "ORA-" | sort |uniq<BR>ORA-01917: user or role 'APPL_OWNER_ROLE' does not exist<BR>ORA-01917: user or role 'APPL_ROLE' does not exist<BR>ORA-01917: user or role 'BATCH_ROLE' does not exist<BR>ORA-01917: user or role 'COGNOS_READ_ONLY_ROLE' does not exist<BR>ORA-01917: user or role 'COGNOS_ROLE' does not exist<BR>ORA-01917: user or role 'GWMETL_ROLE' does not exist<BR>ORA-01917: user or role 'MONITOR_READONLY_ROLE' does not exist<BR>ORA-01917: user or role 'MONITOR_ROLE' does not exist<BR>ORA-01917: user or role 'OFFLINE_ROLE' does not exist<BR>ORA-01917: user or role 'READONLY_ROLE' does not exist<BR>ORA-01917: user or role 'SUSTAIN_ROLE' does not exist<BR>ORA-01917: user or role 'SUSTAIN_ROLE_TEAMMEMBER' does not exist<BR>ORA-01917: user or role 'SUSTAIN_ROLE_TL' does not exist<BR>ORA-01917: user or role 'TESTING_MAINTENANCE_ROLE' does not exist<BR>ORA-01917: user or role 'UPDATE_SCH_JOB_PARAMETERS' does not exist<BR>ORA-02264: name already used by an existing constraint<BR>ORA-02270: no matching unique or primary key for this column-list<BR>ORA-06512: at line 1<BR>ORA-06512: at "SYS.DBMS_STATS", line 10234<BR>ORA-20001: Invalid or inconsistent input values</FONT></DIV>
<DIV> </DIV>
<DIV>这时候我必须将重新创建这些角色，删除表空间并保留数据文件，然后删除表空间时却hung住了。</DIV>
<DIV> </DIV>
<DIV><FONT face=Arial color=#808080 size=1>SQL> drop tablespace INDEX_DBO_32K INCLUDING CONTENTS keep datafiles;</FONT></DIV>
<DIV><FONT face=Arial color=#808080 size=1></FONT> </DIV>
<DIV><FONT face=Arial color=#808080 size=1>$ dbi sessions</FONT></DIV>
<DIV><FONT face=Arial color=#808080 size=1> SID SERIAL# SPID USERNAME STATUS SQL_HASH_VALUE EVENT<BR>------ ---------- -------- --------------- -------- -------------- ------------------------------------------------------------<BR> 961 2971 48374 SYS INACTIVE 0 SQL*Net message from client<BR> 1105 5 26110 SYS INACTIVE 0 SQL*Net message from client<BR> 985 163 47977 SYS ACTIVE 2529568628 row cache lock<BR>camcgmgssmdb005:gwmtkpp1:elcaro:$ dbi longops</FONT></DIV>
<DIV><FONT face=Arial color=#808080 size=1> SID SERIAL# USERNAME SOFAR TOTALWORK TIME_REMAINING MESSAGE<BR>---------- ---------- ---------------- ---------- ---------- -------------- --------------------------------------------------------------------------------<BR> 985 163 SYS 100 1390 1.6 hrs Index Fast Full Scan: SYS.OBJ$: 100 out of 1390 Blocks done</FONT></DIV>
<DIV> </DIV>
<DIV>尝试重启数据库，还是不行。metalink上查了一下，这是一个<A href="https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=6264045" target=_blank>Bug 6264045</A> (<A href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=833224.1" target=_blank>https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=833224.1</A>)</DIV>
<DIV> </DIV>
<DIV>看来只好等到表空间删除之后才能再接着做了。</DIV>
<DIV> </DIV>
页:
[1]