FGA导致在线重定义报ORA-42008,ORA-12015错误
May112015
下面是在11.2.0.4遇到在线重定义报错,信息如下:
1,报错代码 |
exec DBMS_REDEFINITION.START_REDEF_TABLE('HTZ','HTZ','HTZ_RED'); ERROR ----------------------- ORA-42008: error occurred while instantiating the redefinition ORA-12015: cannot create a fast refresh materialized view from a complex query ORA-6512: at "SYS.DBMS_REDEFINITION", line 52 ORA-6512: at "SYS.DBMS_REDEFINITION", line 1646 ORA-6512: at line 2
这里看到START_REDEF_TABLE报错,但是在CAN_REDEF_TABLE都没有报任何的错误。
www.htz.pw > EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('HTZ','HTZ',DBMS_REDEFINITION.CONS_USE_PK); PL/SQL procedure successfully completed.
2,配置EVENT 10046,ERRORSTACK |
oradebug setmypid oradebug event 10046 trace name context forever,level 12; oradebug event 12015 trace name errorstack level 12;
3,分析trace文件 |
*** 2015-05-05 22:58:52.183 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=12, mask=0x0) ----- Error Stack Dump ----- ORA-12015: cannot create a fast refresh materialized view from a complex query ----- Current SQL Statement for this session (sql_id=7fq15yav7sx45) ----- create snapshot "HPAY"."USER_KEY_MID" on prebuilt table with reduced precision refresh fast with primary key as select * from "HPAY"."USER_KEY" "USER_KEY" ----- PL/SQL Stack ----- ----- PL/SQL Call Stack ----- object line object handle number name 0x3a3433408 56 package body SYS.DBMS_REDEFINITION 0x3a3433408 1498 package body SYS.DBMS_REDEFINITION 0x3d05a9f88 1 anonymous block ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- skdstdst()+41 call kgdsdst() 000000000 ? 000000000 ? 7FFF77A069C0 ? 7FFF77A06A98 ? 7FFF77A0B540 ? 000000002 ? ksedst1()+103 call skdstdst() 000000000 ? 000000000 ? 7FFF77A069C0 ? 7FFF77A06A98 ? 7FFF77A0B540 ? 000000002 ? ksedst()+39 call ksedst1() 000000000 ? 000000001 ? 7FFF77A069C0 ? 7FFF77A06A98 ? 7FFF77A0B540 ? 000000002 ? dbkedDefDump()+2746 call ksedst() 000000000 ? 000000001 ? 7FFF77A069C0 ? 7FFF77A06A98 ? 7FFF77A0B540 ? 000000002 ? ksedmp()+41 call dbkedDefDump() 00000000C ? 000000000 ? 7FFF77A069C0 ? 7FFF77A06A98 ? 7FFF77A0B540 ? 000000002 ? dbkdaKsdActDriver() call ksedmp() 00000000C ? 000000000 ? +1960 7FFF77A069C0 ? 7FFF77A06A98 ? 7FFF77A0B540 ? 000000002 ? dbgdaExecuteAction( call dbkdaKsdActDriver() 7F993BD28730 ? 7FFF77A0DC40 ? )+1065 7FFF77A069C0 ? 7FFF77A06A98 ? 7FFF77A0B540 ? 000000002 ? dbgdaRunAction()+81 call dbgdaExecuteAction( 7F993BD28730 ? 00A600760 ? 5 ) 0020C0003 ? 7FFF77A0DC40 ? 000000001 ? 000000002 ? dbgdRunActions()+64 call dbgdaRunAction() 7F993BD28730 ? 000000005 ? 0020C0003 ? 7FFF77A0DC40 ? 000000001 ? 000000002 ? dbgdProcessEventAct call dbgdRunActions() 7F993BD28730 ? 000000005 ? ions()+656 0020C0003 ? 7FFF77A0DC40 ? 000000001 ? 000000002 ? dbgdChkEventKgErr() call dbgdProcessEventAct 7F993BD28730 ? 00C0CC9E0 ? +1658 ions() 7F993B399AC0 ? 7FFF77A0DC40 ? 000000001 ? 000000002 ? dbkdChkEventRdbmsEr call dbgdChkEventKgErr() 7F993BD28730 ? 00C0CC9E0 ? r()+61 7F993B399AC0 ? 7FFF77A0DC40 ? 000000001 ? 000000002 ? ksfpec()+66 call dbkdChkEventRdbmsEr 7F993BD28730 ? 00C0CC9E0 ? r() 7F993B399AC0 ? 7FFF77A0DC40 ? 000000001 ? 000000002 ? dbgePostErrorKGE()+ call ksfpec() 7F993BD28730 ? 00C0CC9E0 ? 1137 7F993B399AC0 ? 7FFF77A0DC40 ? 000000001 ? 000000002 ? dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 00C0CC9E0 ? 7F993BD35BD0 ? 71 000002EEF ? 7FFF77A0DC40 ? 000000001 ? 000000002 ? kgeselv()+276 call dbkePostKGE_kgsf() 00C0CC9E0 ? 7F993BD35BD0 ? 000002EEF ? 7FFF77A0DC40 ? 000000001 ? 000000002 ? ksesecl0()+162 call kgeselv() 00C0CC9E0 ? 7F993BD35BD0 ? 000002EEF ? 7FFF77A0DC40 ? 000000001 ? 000000000 ? kkzsval()+745 call ksesecl0() 00C0CC9E0 ? 7F993BD35BD0 ? 000002EEF ? 000000000 ? 000000000 ? 1000000000000000 ? kkzcsn()+1415 call kkzsval() 3A16ADE38 ? 7FFF77A0F050 ? 7FFF77A0F1A8 ? 7FFF77A0F030 ? 7FFF77A0F1A4 ? 7FFF77A0F19C ? opiexe()+21292 call kkzcsn() 3A16ADE38 ? 7FFF77A0F050 ? 7FFF77A0F1A8 ? 7FFF77A0F030 ? 7FFF77A0F1A4 ? 7FFF77A0F19C ? opiosq0()+3932 call opiexe() 000000004 ? 000000000 ? 7FFF77A0F1A8 ? 7FFF77A0F030 ? 7FFF77A0F1A4 ? 7FFF77A0F19C ? opiall0()+4834 call opiosq0() 000000003 ? 00000000E ? 7FFF77A10AF0 ? 000000020 ? 7FFF77A0F1A4 ? 7FFF77A0F19C ? opikpr()+524 call opiall0() 000000065 ? 000000022 ? 7FFF77A11278 ? 000000000 ? 7FFF77A0F1A4 ? 7FFF77A0F19C ? opiodr()+917 call opikpr() 000000065 ? 00000001C ? 7FFF77A129A8 ? 000000000 ? 7FFF77A0F1A4 ? 7FFF77A0F19C ? rpidrus()+211 call opiodr() 000000065 ? 00000001C ? 7FFF77A129A8 ? 000000000 ? 7FFF77A0F1A4 ? 7FFF77A0F19C ? skgmstack()+148 call rpidrus() 7FFF77A12588 ? 00000001C ? 7FFF77A129A8 ? 000000000 ? 7FFF77A0F1A4 ? 7FFF77A0F19C ? rpiswu2()+690 call skgmstack() 7FFF77A12560 ? 00C0CC600 ? 00000F618 ? 009698B1C ? 7FFF77A12588 ? 7FFF77A0F19C ? kprball()+1232 call rpiswu2() 3CED42700 ? 000000000 ? 7FFF77A128B0 ? 000000002 ? 7FFF77A12588 ? 7FFF77A0F19C ? kkzucpmv()+1747 call kprball() 7FFF77A129A8 ? 000000100 ? 7FFF77A128B0 ? 000000002 ? 7FFF77A12588 ? 7FFF77A0F19C ? kkzusord()+8098 call kkzucpmv() 7F993B4D8B54 ? 000000004 ? 7F993B4D89DC ? 000000002 ? 7FFF77A12588 ? 7FFF77A0F19C ? kkzisord()+862 call kkzusord() 000000001 ? 7F993B7FFCA8 ? 7F993B7FFCB0 ? 7F993B7FFCB8 ? 7FFF77A12588 ? 7FFF77A0F19C ? spefcmpa()+874 call kkzisord() 000000001 ? 7F993B7FFCA8 ? 7F993B7FFCB0 ? 7F993B7FFCB8 ? 7FFF77A12588 ? 7F993B4D8298 ? spefmccallstd()+235 call spefcmpa() 7FFF77A17C40 ? 00000000E ? 7F993BB17FD8 ? 7F993B7FFCB8 ? 7FFF77A12588 ? 7F993B4D8298 ? pextproc()+41 call spefmccallstd() 7FFF77A18040 ? 7FFF77A17CE8 ? 7FFF77A17A00 ? 7FFF77A17C40 ? 000000000 ? 7F993B4D8298 ? peftrusted()+150 call pextproc() 7FFF77A18040 ? 7FFF77A17CE8 ? 7FFF77A17A00 ? 7FFF77A17C40 ? 000000000 ? 7F993B4D8298 ? psdexsp()+255 call peftrusted() 7FFF77A18040 ? 7FFF77A17CE8 ? 7FFF77A17A00 ? 7FFF77A17C40 ? 000000000 ? 7F993B4D8298 ? rpiswu2()+1776 call psdexsp() 7FFF77A17820 ? 7FFF77A17CE8 ? 7FFF77A17A00 ? 7FFF77A17C40 ? 000000000 ? 7F993B4D8298 ? psdextp()+700 call rpiswu2() 3CED0E820 ? 000000000 ? 3CED0E8A4 ? 000000002 ? 000000000 ? 7F993B4D8298 ? pefccal()+726 call psdextp() 7FFF77A19080 ? 7FFF77A17CE8 ? 7FFF77A17A00 ? 000000000 ? 000020003 ? 7FFF77A18040 ? pefcal()+224 call pefccal() 7FFF77A19080 ? 7FFF77A17CE8 ? 7FFF77A17A00 ? 000000000 ? 000020003 ? 7FFF77A18040 ? pevm_FCAL()+169 call pefcal() 7FFF77A18040 ? 7FFF77A17CE8 ? 7FFF77A17A00 ? 000000000 ? 000020003 ? 7FFF77A18040 ? pfrinstr_FCAL()+75 call pevm_FCAL() 7F993B849720 ? 39E917000 ? 7FFF77A17A00 ? 39E916E00 ? 000020003 ? 7FFF77A18040 ? pfrrun_no_tool()+63 call pfrinstr_FCAL() 7F993B849720 ? 39E91BD12 ? 7F993B849790 ? 39E916E00 ? 000020003 ? 7FFF77A18040 ? pfrrun()+627 call pfrrun_no_tool() 7F993B849720 ? 39E91BD12 ? 7F993B849790 ? 39E916E00 ? 000020003 ? 7FFF77A18040 ? plsql_run()+649 call pfrrun() 7F993B849720 ? 39E91BD12 ? 7F993B849790 ? 7FFF77A19080 ? 000020003 ? 7FFF77A18040 ? peicnt()+302 call plsql_run() 7F993B849720 ? 39E91BD12 ? 7F993B849790 ? 7FFF77A19080 ? 000020003 ? 7FFF77A18040 ? kkxexe()+525 call peicnt() 7FFF77A19080 ? 7F993B849720 ? 7F993B849790 ? 7FFF77A19080 ? 000020003 ? 000000000 ? opiexe()+18001 call kkxexe() 7F993B8280F0 ? 7F993B849720 ? 7F993B849790 ? 7FFF77A19080 ? 000020003 ? 000000000 ? kpoal8()+2118 call opiexe() 000000049 ? 000000003 ? 7F993B849790 ? 7FFF77A19080 ? 000020003 ? 000000000 ? opiodr()+917 call kpoal8() 00000005E ? 000000003 ? 7F993B849790 ? 7FFF77A19080 ? 000020003 ? 000000000 ? *** 2015-05-05 22:58:52.787 ttcpip()+2183 call opiodr() 00000005E ? 00000001C ? 7FFF77A1D8B0 ? 7FFF77A19080 ? 000020003 ? 000000000 ? opitsk()+1710 call ttcpip() 00C0EA630 ? 00999BB10 ? 7FFF77A1D8B0 ? 000000000 ? 7FFF77A1D308 ? 7FFF77A1D8AC ? opiino()+969 call opitsk() 00C0EA638 ? 000000000 ? 7FFF77A1D8B0 ? 000000000 ? 7FFF77A1D308 ? 7FFF77A1D8AC ? opiodr()+917 call opiino() 00000003C ? 000000004 ? 7FFF77A1F0A8 ? 000000000 ? 7FFF77A1D308 ? 7FFF77A1D8AC ? opidrv()+570 call opiodr() 00000003C ? 000000004 ? 7FFF77A1F0A8 ? 000000000 ? 7FFF77A1D308 ? 7FFF77A1D8AC ? sou2o()+103 call opidrv() 00000003C ? 000000004 ? 7FFF77A1F0A8 ? 000000000 ? 7FFF77A1D308 ? 7FFF77A1D8AC ? opimai_real()+133 call sou2o() 7FFF77A1F080 ? 00000003C ? 000000004 ? 7FFF77A1F0A8 ? 7FFF77A1D308 ? 7FFF77A1D8AC ? ssthrdmain()+265 call opimai_real() 000000002 ? 7FFF77A1F270 ? 000000004 ? 7FFF77A1F0A8 ? 7FFF77A1D308 ? 7FFF77A1D8AC ? main()+201 call ssthrdmain() 000000002 ? 7FFF77A1F270 ? 000000001 ? 000000000 ? 7FFF77A1D308 ? 7FFF77A1D8AC ? __libc_start_main() call main() 000000002 ? 7FFF77A1F418 ? +253 000000001 ? 000000000 ? 7FFF77A1D308 ? 7FFF77A1D8AC ? _start()+41 call __libc_start_main() 000A29134 ? 000000002 ? 7FFF77A1F408 ? 000000000 ? 7FFF77A1D308 ? 7FFF77A1D8AC ?
4 故障处理 |
exec dbms_fga.disable_policy(object_schema=>'HTZ', object_name=> 'HTZ', policy_name=> 'HTZ');
取消表上面的FGA后,再次执行START正常。
下面来看一下FGA的内容
OBJECT_SCHEMA OBJECT_NAME POLICY_OWNER POLICY_NAME POLICY_TEXT POLICY_COLUMN PF_SCHEMA PF_PACKAGE PF_FUNCTION ENABLED SEL INS UPD DEL AUDIT_TRAIL POLICY_COLUMN_OPTIONS 1 HTZ HTZ HTZ HTZ "SYS_CONTEXT('USERENV','CURRENT_USER') not in ('RA','RCONTROL','SMS','HPAY','LOG','APM','JERRY','BITEL')" YES YES NO NO NO DB+EXTENDED ANY_COLUMNS
本文固定链接: http://www.htz.pw/?p=973 | 认真就输
最活跃的读者