clone a schema

[maswien]

How Can I copy all the objects under one schema to another schema? The constraints and primary key should aso copied.

Thanks

 

[carp]

Export the source schema and import into the target schema (using FROMUSER=source_schema and TOUSER=target_schema).

 

[maswien]

When I tried export the objects from that schema I got following error:

ORA-04068: existing state of packages has been discarded
ORA-04063: package body "XDB.DBMS_XDBUTIL_INT" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
EXP-00056: ORACLE error 4068 encountered
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "XDB.DBMS_XDBUTIL_INT" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully

 

[SantaMufasa]

Maswien,

You problem is not with Carp's methodology (which is the exact solution to your need)...the problem (as the error message implies) is with the packages themselves: their statuses are "INVALID".

To fix this problem, for the packages involved, you can say:

alter package <package_name> compile;

If the re-compilation is successful, then you are good to go. If, however, you receive the message:

[tt]Warning: Package altered with compilation errors.[/tt]

...then you must troubleshoot errors.

If the SQL*Plus command, "show errors" does not produce results, then you can use this script that I wrote for you (which I saved to a script names "showerr.sql"):

col a heading "Object" format a20
col b heading "Line|/Col" format a7
col c heading "Error Message" format a80 word_wrap
break on a
select name||chr(10)||'('||type||')' a, line||'/'||position b, text c
from user_errors
order by name,line,position
/

                     Line
Object               /Col    Error Message
-------------------- ------- --------------------------------------------------
VAC                  8/22    PL/SQL: SQL Statement ignored
(PACKAGE BODY)

                     8/36    PL/SQL: ORA-00942: table or view does not exist
                     9/17    PL/SQL: Statement ignored
                     9/20    PLS-00364: loop index variable 'R' use is invalid
********************************************************************************

Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[maswien]

Hi Mufasa,

compile were not successful, show errors doesn't show anything. I tried your script and return no rows. Any idea?
Thanks.

 

[carp]

maswien -
Please post a listing of what happens when you do the following:

ALTER PACKAGE XDB.DBMS_XDBUTIL_INT COMPILE BODY;

SHOW ERRORS

From the error message you posted earlier, it appears that there is a missing package/procedure that XDB.DBMS_XDBUTIL_INT references. If this is the case, then the package body will not compile until you get the missing code restored. But the results of the above two lines should at least let us check the basics and then move on from there.

 

[maswien]

Following is the error I got:

LINE/COL ERROR
-------- -----------------------------------------------------------------
33/7 PL/SQL: SQL Statement ignored
34/14 PL/SQL: ORA-00942: table or view does not exist

 

[carp]

OK, so there is a table or view that is either missing or XDB does not have privileges for.
Let's try this:

SELECT referenced_type, referenced_owner, referenced_name
FROM all_dependencies
WHERE name = 'DBMS_XDBUTIL_INT'
  AND owner = 'XDB'
  AND type = 'PACKAGE BODY'
ORDER BY 1,2,3;

This will hopefully flush out the deficiencies.

 

[maswien]

the output:

REFERENCED_TYPE   REFERENCED_OWNER       REFERENCED_NAME
----------------- ---------------------- ---------------
NON-EXISTENT      PUBLIC                         SYS
NON-EXISTENT      XDB                            ALL_POLICIES
NON-EXISTENT      XDB                            ALL_TRIGGERS
NON-EXISTENT      XDB                            ALL_USERS
NON-EXISTENT      XDB                            DBMS_SQL
NON-EXISTENT      XDB                            SYS
NON-EXISTENT      XDB                            XMLTYPE
PACKAGE           SYS                            DBMS_SQL
PACKAGE           SYS                            DBMS_SYS_SQL
PACKAGE           SYS                            STANDARD
PACKAGE           XDB                            DBMS_XDBUTIL_INT
PACKAGE           XDB                            DBMS_XDBZ0
SYNONYM           PUBLIC                         ALL_POLICIES
SYNONYM           PUBLIC                         ALL_TRIGGERS
SYNONYM           PUBLIC                         ALL_USERS
SYNONYM           PUBLIC                         DBMS_SQL
SYNONYM           PUBLIC                         XMLTYPE
TYPE              SYS                            XMLTYPE
VIEW              SYS                            ALL_POLICIES
VIEW              SYS                            ALL_TRIGGERS
VIEW              SYS                            ALL_USERS

21 rows selected.

 

[carp]

The seven objects at the top (the ones flagged as "nonexistent") are going to keep you from compiling your code. It appears to me that these objects actually belong to SYS; I suspect that you just need to change the references in the code to fully qualified names (e.g., sys.all_policies instead of just all_policies) or else reestablish the public synonyms that should be in place already.

 

[maswien]

I couldn't find the package code from oracle enterprise manager, how can I find it? Thanks.

 

[carp]

It should be under XDB's schema.