Data Pump error

Ron Schuster

unread,

Sep 28, 2006, 1:13:10 PM9/28/06

to

I'm getting the following error trying to do a data pump export in 10g
Express. I'm basically just trying to follow the example in the help.

C:\Documents and Settings\eheard>expdp SYSTEM/dbo SCHEMAS=hr
DIRECTORY=dmpdir DUMPFILE=schema.dmp LOGFILE=expschema.log

Export: Release 10.2.0.1.0 - Production on Thursday, 28 September, 2006
11:27:58

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 -
Production
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20060928112759" and "" for
Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1555
ORA-04063: package body "SYS.DBMS_AQADM_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.DBMS_AQADM_SYS"

Vladimir M. Zakharychev

unread,

Oct 2, 2006, 2:36:02 AM10/2/06

to

This one is particularly alerting: ORA-04063: package body
"SYS.DBMS_AQADM_SYS" has errors. Looks like something's not right with
your database. Try connecting AS SYSDBA and re-running
@?/rdbms/admin/catqueue.sql to redo AQ initialization. If you will hit
any errors during this script execution and won't be able to fix them
yourself, post them here. If there will be no errors, try your export
again and see if the original problem persists.

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Ron Schuster

unread,

Oct 2, 2006, 9:37:38 AM10/2/06

to

If I just installed Oracle Express, what is the password for SYSDBA?

DA Morgan

unread,

Oct 2, 2006, 11:08:06 AM10/2/06

to

SYSDBA doesn't have a password ... SYS does. SYSDBA is a role.
What precisely are you using to log in ... all of it ... as you
are typing it into the prompts?
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Vladimir M. Zakharychev

unread,

Oct 3, 2006, 12:01:29 PM10/3/06

to

You should be able to login without password from the host where Oracle
runs using this sequence:

at the OS command prompt type

sqlplus /nolog

then at the SQL*Plus prompt

connect / as sysdba

This should connect you to the instance as SYS with SYSDBA role using
operating system authentication (so you must be member of ORA_DBA group
on OS level.) Then you can run suggested script (just paste or type the
command I gave in my previous post in SQL*Plus.) You may want to spool
its output to a file for further review. To do this, type

spool path/to/the/filename.txt

at SQL*Plus prompt before actually running the script. When the script
is done, type

spool off

to turn off spooling. Then review the filename.txt file looking for any
errors and try to fix them, or, if you can't, post them here.

Reading through the 2 Day DBA guide will surely be helpful, too. Pay
close attention to sections describing how to manage database using SQL
commands - some things can't be done from the GUI and (I think) running
database creation/upgrade scripts is one of them, so you should really
get yourself familiar with SQL*Plus.

Regards,

Ron Schuster

unread,

Oct 5, 2006, 1:00:48 PM10/5/06

to

The script runs with errors. This appears multiple times in the log:

Vladimir M. Zakharychev

unread,

Oct 6, 2006, 11:26:10 AM10/6/06

to

Strange. In SQL*Plus, type

SHOW ERRORS PACKAGE BODY SYS.DBMS_AQADM_SYS

and see why it doesn't want to compile (or post the errors here.)

Ron Schuster

unread,

Oct 6, 2006, 12:13:13 PM10/6/06

to

SQL> show errors package body sys.dbms_aqadm_sys
Errors for PACKAGE BODY SYS.DBMS_AQADM_SYS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1415/20 PL/SQL: Item ignored
1415/27 PLS-00302: component 'AQ$_QUEUES' must be declared
1418/5 PL/SQL: SQL Statement ignored
1418/27 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

1418/33 PL/SQL: ORA-00904: : invalid identifier
1426/5 PL/SQL: SQL Statement ignored
1428/50 PL/SQL: ORA-00904: "OBJNO": invalid identifier
1428/50 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

LINE/COL ERROR
-------- -----------------------------------------------------------------

1436/6 PL/SQL: SQL Statement ignored
1438/15 PL/SQL: ORA-00904: "OBJNO": invalid identifier
1438/15 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

1448/9 PL/SQL: Statement ignored
1449/13 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

1881/21 PL/SQL: Item ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
1881/28 PLS-00302: component 'AQ$_QUEUE_TABLES' must be declared
1882/21 PL/SQL: Item ignored
1882/28 PLS-00302: component 'AQ$_QUEUES' must be declared
1883/21 PL/SQL: Item ignored
1883/28 PLS-00302: component 'AQ$_QUEUES' must be declared
1888/28 PLS-00302: component 'AQ$_QUEUES' must be declared
SQL>

Vladimir M. Zakharychev

unread,

Oct 9, 2006, 2:11:39 AM10/9/06

to

Next step is to figure out why catqueue.sql didn't create these tables.
They should've been created with SYSTEM being the owner. You can verify
their existence, for example, with this SQL:

select owner, table_name from dba_tables where table_name like
'AQ$\_QUEUE%';

Check if they do exist and the owner is correct. If they are not there,
try creating them manually using the corresponding CREATE TABLE
commands in the rdbms/admin/catqueue.sql script (the path is relative
to your Oracle home directory,) and see if there are any errors
preventing them from being created. If they are owned by wrong user,
you will need to drop them and re-run catqueue.sql script again, logged
in as SYSDBA.

Ron Schuster

unread,

Oct 9, 2006, 2:52:07 PM10/9/06

to

They are there and SYSTEM is the owner. Next idea?

SQL> select owner, table_name from dba_tables where table_name like

'AQ$_QUEUE%'
;

OWNER TABLE_NAME
------------------------------ ------------------------------
SYSTEM AQ$_QUEUE_TABLES
SYSTEM AQ$_QUEUES
SYS AQ$_QUEUE_TABLE_AFFINITIES
SYS AQ$_QUEUE_STATISTICS

SQL>

Vladimir M. Zakharychev

unread,

Oct 10, 2006, 2:34:09 AM10/10/06

to

Hmm... Then verify that DBMS_AQADM_SYS package and its body have the
right owner (SYS). If it is so, then I'm at loss. Don't have an XE
instance handy so I can't check myself if this is a generic XE issue or
just something with your installation. Did you try official XE support
forum?

Robbert van der Hoorn

unread,

Oct 10, 2006, 4:38:34 AM10/10/06

to

"Vladimir M. Zakharychev" <vladimir.z...@gmail.com> schreef in
bericht news:1160462048.9...@i3g2000cwc.googlegroups.com...

Ron, Vladimir,

this is NOT a generic XE issue. The posted script works fine on my instance.

Robbert van der Hoorn
OSA it BV
The Netherlands