当前位置: 首页 > EVENT > 正文

FGA导致在线重定义报ORA-42008,ORA-12015错误

      下面是在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/2015/05/11/fga%e5%af%bc%e8%87%b4%e5%9c%a8%e7%ba%bf%e9%87%8d%e5%ae%9a%e4%b9%89%e6%8a%a5ora-42008%ef%bc%8cora-12015%e9%94%99%e8%af%af.html | 认真就输

该日志由 huangtingzhong 于2015年05月11日发表在 EVENT 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: FGA导致在线重定义报ORA-42008,ORA-12015错误 | 认真就输
关键字: