原标题:EXP导出aud$报错EXP-00008,ORA-00904 解决
前一日在天斗皇家学院,那三位教委联手对抗毒斗罗的时候,明显是和他认识的,或许那三位教委会知道独孤博的下落,如果他们也不知道,说不得就只有去寻找那位雪星亲王了。 李庆安是在十天前返回安西,他的妻子暂住龟兹城,一回到安西,李庆安便着手备战对吐蕃战役,在他的近二十万军队中,一大半都部署在岭西,而在安西北庭只有四万新军,这显然是远远不够,为此,他必须将碎叶的精锐部队换防到安西,在这次对吐蕃的战役中,陇右的哥舒翰是主力,他只须从侧翼配合哥舒翰,因此李庆安只准备出兵五万,这样就不会影响到军队屯田。 这是为了维护唐朝的礼制,保护大妇的权力,这也是当初李庆安不能娶裴婉儿的原因,当时明月还在安西,如果明月不点头,那裴婉儿就进不了李庆安的门。 主题:EXP导出aud$报错EXP-00008,ORA-00904 解决 导出sys用户下的表,依据经验使用system用户导出: 按照这个方法,exp导出时报错,具体报错信息如下: 使用oradebug跟踪对应的exp进程: 2.1 exp 交互式 2.2 定位exp进程的SPID: 结果如下: 2.3 oradebug跟踪spid: 实际过程记录: 2.4 查看10046的跟踪文件: 也可以选择tkprof美化trc文件: 美化后的文档可以更清晰的看到904错误的原因: 根据报错和trc文件的内容,匹配到MOS文档:Errors EXP-8 ORA-904 During Export (文档 ID 1091927.1) APPLIES TO: EXP-00008: ORACLE error 904 encountered When performing a trace on the export, we see that the ORA-904 error is caused by the following statement: PARSE ERROR #9:len=302 dep=0 uid=5 oct=3 lid=5 tim=778463694169 err=904 Privileges to the DBMS_JAVA package are needed for the export to succeed. However, as there is concern about security issues due to privileges on the DBMS_JAVA many have revoked the execute privilege to this package to keep the database secure. SOLUTION SQL> grant execute on DBMS_JAVA to dba; This will give members of the DBA role the rights to perform the export while keeping the DBMS_JAVA package out of the PUBLIC role. MOS解决方案赋权DBMS_JAVA给DBA角色: 再次尝试exp导出成功。 至此,问题完美解决,该案例的定位,主要用到了oradebug的跟踪技能。
高仙芝的眉头皱成一团,如果没有包抄围堵的话,连云堡外围的吐蕃军就可以从南边逃走了。
环境:Oracle 11.2.0.4
问题:在自己的测试环境,导出sys用户下的aud$表报错。
1.故障现场
[oracle@jyrac1 ~]$ exp system/oracle file=audit.dmp log=audit.log tables=sys.aud$
Export: Release 11.2.0.4.0 - Production on Wed Jan 17 17:16:30 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table AUD$
EXP-00008: ORACLE error 904 encountered
ORA-00904: : invalid identifier
Export terminated successfully with warnings.
2.跟踪处理
[oracle@jyrac1 ~]$ exp system/oracle
Export: Release 11.2.0.4.0 - Production on Wed Jan 17 17:20:17 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Enter array fetch buffer size: 4096 >
SELECT p.spid,
p.addr,
p.pid,
s.sid,
SUBSTR(s.username, 1, 15) "USERNAME",
SUBSTR(s.program, 1, 15) "PROGRAM"
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND addr =
(SELECT paddr FROM v$session WHERE UPPER(program) LIKE "EXP%");
SYS@jyzhao1 >SELECT p.spid,
2 p.addr,
3 p.pid,
4 s.sid,
5 SUBSTR(s.username, 1, 15) "USERNAME",
6 SUBSTR(s.program, 1, 15) "PROGRAM"
7 FROM v$process p, v$session s
8 WHERE s.paddr = p.addr
9 AND addr =
10 (SELECT paddr FROM v$session WHERE UPPER(program) LIKE "EXP%");
SPID ADDR PID SID USERNAME PROGRAM
------------------------ ---------------- ---------- ---------- ------------------------------ ------------------------------
15552 000000007F4CA268 33 141 SYSTEM exp@jyrac1 (TNS
SQL> oradebug setospid 15552
SQL> oradebug unlimit
SQL> oradebug tracefile_name
SQL> oradebug Event 10046 trace name context forever, level 12
SYS@jyzhao1 >oradebug setospid 15552
Oracle pid: 33, Unix process pid: 15552, image: oracle@jyrac1 (TNS V1-V3)
SYS@jyzhao1 >oradebug unlimit
Statement processed.
SYS@jyzhao1 >oradebug tracefile_name
/opt/app/oracle/diag/rdbms/jyzhao/jyzhao1/trace/jyzhao1_ora_15552.trc
SYS@jyzhao1 >oradebug Event 10046 trace name context forever, level 12
Statement processed.
我搜索904这个错误,可以匹配到err=904
的部分=====================
PARSE ERROR #139705015595160:len=301 dep=0 uid=5 oct=3 lid=5 tim=1516181414835913 err=904
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID, TABOWNID, SYNOBJNO FROM SYS.EXU9PTS WHERE SYNOBJNO IN ( SELECT SYNOBJNO FROM SYS.EXU9TYPT WHERE TABOBJNO = :1 ) ORDER BY SYNTIME
WAIT #139705015595160: nam="SQL*Net message to client" ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181414836191
WAIT #139705015595160: nam="SQL*Net message from client" ela= 127 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181414836355
WAIT #0: nam="SQL*Net message to client" ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181414836399
*** 2018-01-17 17:30:19.545
WAIT #0: nam="SQL*Net message from client" ela= 4709518 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181419545928
WAIT #0: nam="SQL*Net message to client" ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181419546119
WAIT #0: nam="SQL*Net message from client" ela= 65 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181419546230
PARSE #139705014733696:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1516181419547202
BINDS #139705014733696:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=7f0f9bca8510 bln=16 avl=16 flg=05
value=00002451.0000.0001
EXEC #139705014733696:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1516181419547371
FETCH #139705014733696:c=0,e=59,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1516181419547468
CLOSE #139705014733696:c=0,e=29,dep=1,type=3,tim=1516181419547548
PARSE #139705014733696:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1516181419548823
BINDS #139705014733696:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=7f0f9bca8510 bln=16 avl=16 flg=05
value=0000243C.0007.0001
EXEC #139705014733696:c=0,e=112,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1516181419549015
FETCH #139705014733696:c=1000,e=116,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1516181419549155
CLOSE #139705014733696:c=0,e=9,dep=1,type=3,tim=1516181419549188
=====================
[root@jyrac1 ~]# tkprof /opt/app/oracle/diag/rdbms/jyzhao/jyzhao1/trace/jyzhao1_ora_15552.trc /tmp/jyzhao1_ora_15552.out waits=y sort=exeela
TKPROF: Release 11.2.0.4.0 - Development on Wed Jan 17 17:33:44 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
********************************************************************************
The following statement encountered a error during parse:
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID, TABOWNID, SYNOBJNO FROM SYS.EXU9PTS WHERE SYNOBJNO IN ( SELECT SYNOBJNO FROM SYS.EXU9TYPT WHERE TABOBJNO = :1 ) ORDER BY SYNTIME
Error encountered: ORA-00904
********************************************************************************
3.匹配MOS文章
Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
Checked for relevance on 16-Dec-2014
SYMPTOMS
You are attempting to perform an export as the SYSTEM user or a DBA. During export, the following errors occurred:
ORA-00904: : invalid identifier
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID, TABOWNID, SYNOBJNO FROM SYS.EXU9PTS WHERE SYNOBJNO IN ( SELECT SYNOBJNO FROM SYS.EXU9TYPT WHERE TABOBJNO = :1 ) ORDER BY SYNTIME
CAUSE
The PUBLIC privilege has been revoked from the DBMS_JAVA package.
Explicitly grant EXECUTE on the DBMS_JAVA package to the DBA role:4.使用MOS解决方案
SYS@jyzhao1 >grant execute on DBMS_JAVA to dba;
Grant succeeded.
[oracle@jyrac1 ~]$ exp system/oracle file=audit.dmp log=audit.log tables=sys.aud$
Export: Release 11.2.0.4.0 - Production on Wed Jan 17 17:39:07 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table AUD$ 25 rows exported
Export terminated successfully without warnings.
编辑:扁乙密
发布时间:2019-02-21 16:08:36
当前文章:http://www.radiokey.biz/list/kau4u.html
打拖拉机扑克的技巧 成事于德 沈阳盛京棋牌 不思议客服电话 可以打牌赚钱的游戏 房卡手机棋牌 千炮捕鱼棋牌 上海欢乐互娱
73653 88193 70233 81249 92685 4057250902 30877 25366
责任编辑:公密北