2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试
Oracle错误:ORA-39125,ORA-31642,DBMS_CDC_EXPDP.SCHEMA_CALLOUT一例
有网友提问在使用expdp时出现了ORA-39125、ORA-31642错误，具体错误信息如下：
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "EXPALL"."SYS_EXPORT_FULL_04": expall/******** directory=expdp_dir full=y dumpfile=exp_tposdb_full.dmp logfile=exp_tposdb_ full.log Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB [] ORA-31642: the following SQL statement fails: BEGIN "SYS"."DBMS_CDC_EXPDP".SCHEMA_CALLOUT(:1,0,1,'10.02.00.04.00'); END; ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_METADATA", line 907 ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPW$WORKER", line 6307 ----- PL/SQL Call Stack ----- object line object handle number name 0xa19cbae8 15032 package body SYS.KUPW$WORKER 0xa19cbae8 6372 package body SYS.KUPW$WORKER 0xa19cbae8 9206 package body SYS.KUPW$WORKER 0xa19cbae8 1936 package body SYS.KUPW$WORKER 0xa19cbae8 6944 package body SYS.KUPW$WORKER 0xa19cbae8 1314 package body SYS.KUPW$WORKER 0xa15c0d34 2 anonymous block Job "EXPALL"."SYS_EXPORT_FULL_04" stopped due to fatal error at 14:06:05针对该错误可以参考Mos Note <DataPump Export Utility Fails With Errors ORA-39125 ORA-31642 PLS-201 on Package SYS.DBMS_CDC_EXPDP [ID 825680.1]>和<Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges) [ID 351598.1]>， 现引用如下文:
DataPump Export Utility Fails With Errors ORA-39125 ORA-31642 PLS-201 on Package SYS.DBMS_CDC_EXPDP
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.
Symptoms
You try to export a schema from database using DataPump export utility. This ends with errors:
#> expdp system/password directory=dpu dumpfile=test.dmp schemas=test
Export: Release 10.2.0.4.0 - 64bit Production on Friday, 15 May, 2009 8:49:29
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bitProduction
With the Partitioning, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dpu dumpfile=test.dmp schemas=test
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling
DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_CDC_EXPDP".SCHEMA_CALLOUT(:1,0,1,'10.02.00.04.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 907
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_CDC_EXPDP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6307
----- PL/SQL Call Stack -----
object line object
handle number name
0x7e8998d8 15032 package body SYS.KUPW$WORKER
0x7e8998d8 6372 package body SYS.KUPW$WORKER
0x7e8998d8 9206 package body SYS.KUPW$WORKER
0x7e8998d8 1936 package body SYS.KUPW$WORKER
0x7e8998d8 6944 package body SYS.KUPW$WORKER
0x7e8998d8 1314 package body SYS.KUPW$WORKER
0x76337f10 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" stopped due to fatal error at 08:50:02
Cause
These errors are raised, when either the EXECUTE privilege on package SYS.DBMS_CDC_EXPDP was revoked, or,
when the package package SYS.DBMS_CDC_EXPDP was accidentally dropped from data dictionary. Verify this with:
connect / as sysdba
select grantee, privilege
from dba_tab_privs
where table_name = 'DBMS_CDC_EXPDP';
select owner, object_name, object_type
from dba_objects
where object_name = 'DBMS_CDC_EXPDP';
The found objects should be these:
SQL> select grantee, privilege from dba_tab_privs where table_name = 'DBMS_CDC_EXPDP';
GRANTEE PRIVILEGE
--------------- ------------
PUBLIC EXECUTE
SQL> select owner, object_name, object_type from dba_objects where object_name = 'DBMS_CDC_EXPDP';
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- -------------------- -------------------
SYS DBMS_CDC_EXPDP PACKAGE
SYS DBMS_CDC_EXPDP PACKAGE BODY
PUBLIC DBMS_CDC_EXPDP SYNONYM
The package SYS.DBMS_CDC_EXPDP is registered in data dictionary table EXPPKGACT$,
which is automatically called during expdp process. Because the package SYS.DBMS_CDC_EXPDP
doesn't exist or the user doesn't have the privilege to run the package, the error PLS-201 is raised.
Solution
To correct this, follow the next steps:
1. If only the EXECUTE privilege is missing, then perform via SQL*Plus:
connect / as sysdba
grant execute on dbms_cdc_expdp to public;
=> Then restart expdp.
2. If the package DBMS_CDC_EXPDP is missing, then you have 2 possibilities:
A. De-register the package DBMS_CDC_EXPDP from table EXPPKGACT$ (so it's not called during expdp)
or:
B. Re-create the package DBMS_CDC_EXPDP so that expdp will find it.
If you want to follow the way A, then perform in SQL*Plus:
connect / as sysdba
-- save the actual entries from exppkgact$
create table exppkgact$_bck as select * from exppkgact$;
-- delete the entries related to package DBMS_CDC_EXPDP
delete from exppkgact$ where package = 'DBMS_CDC_EXPDP';
commit;
=> Then restart expdp
If you decide for way B, then perform in SQL*Plus:
-- first change the OS local directory to:
#> cd $ORACLE_HOME/rdbms/admin
connect / as sysdba
-- create the missing package
@prvtcdpe.plb
=> Then restart expdp.
Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges) [ID 351598.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.6 - Release: 10.1 to 11.1
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.6 [Release: 10.1 to 11.1]
Oracle Server - Personal Edition - Version: 10.1.0.2 to 11.1.0.6 [Release: 10.1 to 11.1]
Oracle Server - Standard Edition - Version: 10.1.0.2 to 11.1.0.6 [Release: 10.1 to 11.1]
Enterprise Manager for RDBMS - Version: 10.1.0.2 to 11.1 [Release: 10.1 to ]
Information in this document applies to any platform.
***Checked for relevance on 7-Feb-2011***
Goal
In Oracle10g, the new database export and import utilities Export DataPump (expdp) and Import DataPump (impdp)
were introduced. This document describes the minimum requirements to run an Export DataPump or Import DataPump job.
Solution
1. Minimum Requirements.
The minimum requirements to run an Export DataPump or Import DataPump job on an Oracle10g or any
higher release database are:
- system privilege CREATE SESSION (or the old role: CONNECT)
- system privilege CREATE TABLE
- object privileges READ and WRITE on an valid directory object (or the CREATE DIRECTORY
privilege with which a valid directory object was created)
- sufficient tablespace quota on the user's default tablespace (needed to create the
so-called Master Table that is used by the DataPump job).
In addition, the role EXP_FULL_DATABASE is needed:
- to run a full database Export DataPump job or
- to run a transport_tablespace Export DataPump job or
- to run an Export DataPump job with the TRACE parameter or
- to run an Export DataPump job that exports a different schema.
In addition, the role IMP_FULL_DATABASE is needed:
- to run a full database Import DataPump job or
- to run a transport_datafiles Import DataPump job or
- to run an Import DataPump job with the TRACE parameter or
- to run an Import DataPump job that imports a different schema.
Note that these requirements apply to the user that connects to the database when running the
Export DataPump or Import DataPump job, not to the user that is exported/imported.
2. How to grant privileges.
The privileges can be granted explicitly or via a role. E.g.:
CONNECT system/manager
CREATE DIRECTORY my_dir AS 'full_pre_existing_directory_path_here';
GRANT create session, create table TO scott IDENTIFIED BY tiger;
GRANT read, write ON DIRECTORY my_dir TO scott;
ALTER USER scott QUOTA unlimited ON users;
or:
CONNECT system/manager
CREATE DIRECTORY my_dir AS 'full_pre_existing_directory_path_here>>';
CREATE ROLE expdp_role;
GRANT create session, create table TO expdp_role;
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
GRANT expdp_role TO scott;
ALTER USER scott DEFAULT ROLE all;
ALTER USER scott QUOTA unlimited ON users;
3. Privileged User.
A privileged user is a user that has the EXP_FULL_DATABASE role for an
Export DataPump and the IMP_FULL_DATABASE role for an Import DataPump job,
or the DBA role that contains both these roles:
SET lines 80
COL privilege FOR a40
SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY 1,2;
GRANTEE GRANTED_ROLE DEF
-------------------- ------------------------------ ---
DBA EXP_FULL_DATABASE YES
DBA IMP_FULL_DATABASE YES
SCOTT EXP_FULL_DATABASE YES
SCOTT IMP_FULL_DATABASE YES
SYS DBA YES
SYS EXP_FULL_DATABASE YES
SYS IMP_FULL_DATABASE YES
SYSMAN DBA YES
SYSTEM DBA YES
4. Useful queries.
-- check for granted system privileges CREATE SESSION and CREATE TABLE
-- (replace SCOTT with the username that connects to the database when
-- running the DataPump job):
SET lines 80 pages 50
COL privilege FOR a40
SELECT grantee, privilege
FROM dba_sys_privs
WHERE (grantee IN ('SCOTT', 'PUBLIC')
OR grantee IN (SELECT granted_role FROM dba_role_privs
WHERE grantee IN ('SCOTT', 'PUBLIC')))
AND privilege IN ('CREATE SESSION', 'CREATE TABLE')
ORDER BY 1,2;
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
SCOTT CREATE SESSION
SCOTT CREATE TABLE
-- check for granted roles and the default role (replace SCOTT with the
-- username that connects to the database when running the DataPump job):
SET lines 80
SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE grantee IN ('SCOTT', 'PUBLIC') ORDER BY 1,2;
GRANTEE GRANTED_ROLE DEF
------------------------------ ------------------------------ ---
SCOTT EXP_FULL_DATABASE YES
SCOTT IMP_FULL_DATABASE YES
-- check for granted privileges on directory objects (replace SCOTT with
-- the username that connects to the database when running the DataPump job):
SET lines 100
COL privilege FOR a10
COL grantee FOR a20
COL owner FOR a20
SELECT p.grantee, p.privilege, p.owner, d.directory_name
FROM dba_tab_privs p, dba_directories d
WHERE p.table_name=d.directory_name
AND (grantee IN ('SCOTT', 'PUBLIC')
OR grantee IN (SELECT granted_role FROM dba_role_privs
WHERE grantee IN ('SCOTT', 'PUBLIC')))
ORDER BY 4,3,2;
GRANTEE PRIVILEGE OWNER DIRECTORY_NAME
-------------------- ---------- -------------------- ----------------------
IMP_FULL_DATABASE READ SYS DATA_PUMP_DIR
EXP_FULL_DATABASE READ SYS DATA_PUMP_DIR
EXP_FULL_DATABASE WRITE SYS DATA_PUMP_DIR
IMP_FULL_DATABASE WRITE SYS DATA_PUMP_DIR
SCOTT READ SYS MY_DIR
SCOTT WRITE SYS MY_DIR
-- check for the default tablespace (replace SCOTT with the username that
-- connects to the database when running the DataPump job):
SET lines 80
SELECT username, default_tablespace
FROM dba_users WHERE username IN ('SCOTT');
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT USERS
-- check for the tablespace quota (replace SCOTT with the username that
-- connects to the database when running the DataPump job):
SET lines 100 numwidth 12
SELECT q.username, q.tablespace_name, q.bytes, q.max_bytes
FROM dba_ts_quotas q, dba_users u
WHERE q.username=u.username AND q.username in ('SCOTT');
USERNAME TABLESPACE_NAME BYTES MAX_BYTES
-------------------- -------------------- ------------ ------------
SCOTT USERS 393216 -1
5. Possible errors.
Possible errors if user SCOTT tries to run an Export DataPump job with:
> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=exp_s.dmp \
LOGFILE=exp_s.log SCHEMAS=scott
5.1. Session related.
UDE-00008: operation generated ORACLE error 1045
ORA-01045: user SCOTT lacks CREATE SESSION privilege; logon denied
Solution: grant the CREATE SESSION privilege to the user that runs the
export (scott) or grant it to the role (expdp_role) that is granted to the user that runs the export, e.g.:
GRANT create session TO scott;
-- or:
GRANT create session TO expdp_role;
Alternatively, run the export with a different privileged user that has the
'CREATE SESSION' privilege, or the old 'CONNECT' role (which has the CREATE SESSION privilege),
e.g.: run the export while connecting with the SYSTEM schema.
5.2. Master table related.
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCOTT.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01031: insufficient privileges
Solution: grant the CREATE TABLE privilege to the user that runs the export (scott) or
grant it to the role (expdp_role) that is granted to the user that runs the export, e.g.:
GRANT create table TO scott;
-- or:
GRANT create table TO expdp_role;
Alternatively, run the export with a different privileged user that has the 'CREATE TABLE'
privilege, e.g.: run the export while connecting with the SYSTEM schema.
5.3. Directory related.
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name MY_DIR is invalid
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39087: directory name MY_DIR is invalid
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-31631: privileges are required
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31631: privileges are required
Solution: grant the READ and WRITE privilege on the DIRECTORY object to the user that
runs the export (scott) or grant it to the role (expdp_role) that is granted
to the user that runs the export, e.g.:
GRANT read, write ON DIRECTORY my_dir TO scott;
-- or:
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
Alternatively, run the export with a different privileged user that has the 'READ' and 'WRITE'
privilege on the directory object, e.g.: run the export while connecting with the user that
created the directory object.
Note: If the errors reproduce after (re-)granting the privileges, then the directory on
disk may have been created after the directory object was created. In that case:
- drop the directory object in the database;
- ensure that the directory exists on the filesystem on the server where the
Oracle database is installed, and that the Oracle user has access to it;
- re-create the directory object in the database;
- grant the read and write privileges on the directory object to the user(s) who
connect to the database when starting the expdp / impdp job.
5.4. Tablespace quota related.
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCOTT.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCOTT.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'
Solution: grant tablespace quota on the default tablespace of the user that runs the export (scott),
or change the default tablespace of the user that runs the export to a tablespace on which
this user has sufficient quota, e.g.:
ALTER USER scott QUOTA unlimited ON users;
-- or:
ALTER USER scott DEFAULT TABLESPACE scott_tbsp;
Alternatively, run the export with a different privileged user that has sufficient quota on their default tablespace.
5.5. Privileged User related.
> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log TRANSPORT_TABLESPACES=users
Export: Release 10.2.0.3.0 - Production on Monday, 30 July, 2007 10:03:59
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31631: privileges are required
ORA-39162: Transportable tablespace job require privileges
> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log TABLES=hugo.emp
Export: Release 10.2.0.3.0 - Production on Monday, 30 July, 2007 11:51:25
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas
Solution: grant the DBA role or the EXP_FULL_DATABASE role to the user that runs the export
(scott) or grant it to the role (expdp_role) that is granted to the user that runs the export, e.g.:
GRANT exp_full_database TO scott;
-- or:
GRANT dba TO expdp_role;
Alternatively, run the export with a privileged user (i.e. a user who has the DBA or the EXP_FULL_DATABASE role).
5.6. Database link related.
If a privileged user (e.g. SYSTEM) is exporting or importing data with the NETWORK_LINK parameter
and is using a (public) database link that connects to a remote database
as a non-privileged user (e.g. SCOTT), then the Import DataPump or Export DataPump job will fail with:
> impdp system/manager NOLOGFILE=y NETWORK_LINK=scott_pub_dblink \
SCHEMAS=scott REMAP_SCHEMA=scott:hugo
Import: Release 10.2.0.3.0 - Production on Thursday, 23 August, 2007 11:49:30
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user
Solution: ensure that the user in the remote database with which the database link connects to,
has the DBA role or the EXP_FULL_DATABASE role, or grant it to the role (expdp_role)
that is granted to the user that the database link connects to, e.g.:
-- in remote database:
GRANT exp_full_database TO scott;
-- or in remote database:
GRANT dba TO expdp_role;
Alternatively, have the privileged user that runs the job create a private database link of its own,
that connects as another privileged user to the remote database.
-- in local database:
CONNECT system/manager
CREATE DATABASE LINK system_to_remote
CONNECT TO system IDENTIFIED BY manager
USING 'remote_db.oracle.com';
> impdp system/manager NOLOGFILE=y NETWORK_LINK=system_to_remote \
SCHEMAS=scott REMAP_SCHEMA=scott:hugo
5.7. Trace file related.
If a non-privileged user is exporting or importing data with the TRACE parameter, then
the Export DataPump or Import DataPump job will fail with:
> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log TABLES=emp TRACE=480300
Export: Release 10.2.0.3.0 - Production on Monday, 30 July, 2007 12:44:30
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31631: privileges are required
Solution: when the Data Pump TRACE parameter is used in Oracle10g Release 2 or higher, then the
user who connects to the database and runs the export DataPump job needs to have the DBA role or the
EXP_FULL_DATABASE role (the same applies to Import DataPump with TRACE parameter: DBA or IMP_FULL_DATABASE
role required), e.g.:
GRANT exp_full_database TO scott;
-- or:
GRANT dba TO expdp_role;
Alternatively, run the export with TRACE parameter with a privileged user (i.e. a user who has
the DBA or the EXP_FULL_DATABASE role).
Reference:
Bug:5152186 "EXPDP/IMPDP JOB WITH TRACE AND STARTED WITH NON-DBA USER FAILS: ORA-31631"
5.8. Flashback query related.
If a user with the EXP_FULL_DATABASE role is exporting a table that is owned by a different
schema and that table has as SYS.XMLTYPE column, then the Export DataPump job will fail with:
File: expdp_s.par
-----------------
DIRECTORY = my_dir
DUMPFILE = expdp_s.dmp
LOGFILE = expdp_s.log
FLASHBACK_TIME = "to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')"
> expdp scott/tiger SCHEMAS=hugo parfile=expdp_s.par
...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-31693: Table data object "HUGO"."MYXMLTAB" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
...
Solution: explicitly grant the FLASHBACK privilege to the schema that runs the
Export DataPump job. The privilege can be granted on specific objects (privilege: FLASHBACK),
or on all tables (privilege: FLASHBACK ANY TABLE), e.g.:
GRANT flashback ON hugo.myxmltab TO scott;
-- or:
GRANT flashback any table TO expdp_role;
Alternatively, grant the DBA role to the schema that runs the export DataPump job or
run the export with a schema that has the DBA role.
5.9. Change Data Capture (CDC) related.
If a user with the EXP_FULL_DATABASE role performs a full database export, and
the database has a change table (SELECT * FROM change_tables), then the Export DataPump job may fail with:
...
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS
while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_CDC_EXPDP".SCHEMA_CALLOUT(:1,0,1,'10.02.00.03.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 907
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6249
...
...
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS
while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_CDC_EXPDP".SCHEMA_CALLOUT(:1,0,1,'10.02.00.03.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 907
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6249
...
Reference: Bug:6078613 "EXPORT DATAPUMP AFTER INSTALLING CDC FAILS WITH ORA-942"
Workaround: explicitly grant the following privileges to the schema that runs the
full database Export DataPump job, e.g.:
GRANT create any table TO scott;
GRANT insert any table TO scott;
GRANT drop any table TO scott;
GRANT select on cdc_change_sources$ TO scott;
GRANT select on cdc_change_sets$ TO scott;
GRANT select on cdc_change_tables$ TO scott;
GRANT select on cdc_subscribers$ TO scott;
Alternatively, grant the DBA role to the schema that runs the export DataPump job or
run the export with a schema that has the DBA role.
5.10. Exported schema privileges related.
If the schema that is being exported (i.e. not the user that started the Export DataPump job) has
scheduler jobs that need to be exported, and that user does not have (enough) tablespace quota,
then the Export DataPump job will fail with:
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling
DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_SCHED_EXPORT_CALLOUTS".SCHEMA_CALLOUT(:1,0,1,'10.02.00.03.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 907
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6249
----- PL/SQL Call Stack -----
object line object
handle number name
2DFB4638 14938 package body SYS.KUPW$WORKER
2DFB4638 6314 package body SYS.KUPW$WORKER
2DFB4638 9129 package body SYS.KUPW$WORKER
2DFB4638 1882 package body SYS.KUPW$WORKER
2DFB4638 6875 package body SYS.KUPW$WORKER
2DFB4638 1260 package body SYS.KUPW$WORKER
2DC76BE4 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" stopped due to fatal error at 14:38:18
Solution: grant tablespace quota to the default tablespace of the schema that needs to be exported, or
change the default tablespace of the schema that needs to be exported to a tablespace on which this schema has
sufficient quota, e.g.:
ALTER USER scott QUOTA unlimited ON users;
-- or:
ALTER USER scott DEFAULT TABLESPACE scott_tbsp;
posted on 2013-03-19 00:48 Oracle和MySQL 阅读(1668) 评论(0) 编辑 收藏 举报
