Tuesday, June 14, 2011

Exporting schemas with invalid objects

Let's say that you want to copy one schema from some Oracle database to other Oracle database; you first export that schema with exp without warnings:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links

...

. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully without warnings.

Then you import it in your target database, again without warnings:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning option

Export file created by EXPORT:V10.02.01 via direct path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
. importing SOMEUSER's objects into SOMEUSER
. . importing table "TABLE_ONE"
0 rows imported
. . importing table "TABLE_TWO"
0 rows imported
. . importing table "TABLE_THREE"
22 rows imported

...

. . importing table "TABLE_SIXTY_ONE"
0 rows imported
. . importing table "TABLE_SIXTY_TWO"
1 rows imported
. . importing table "TABLE_SIXTY_THREE"
0 rows imported
Import terminated successfully without warnings.

Just to be sure, you count the number of objects of that schema in both databases, just to find out that there are less objects in your target database:

SQL> select OWNER, count(*) from dba_objects where OWNER in ('SOMEUSER')
group by OWNER order by OWNER;

OWNER COUNT(*)
------------------------------ ----------
SOMEUSER 241

=====================================================

SQL> select OWNER, count(*) from dba_objects where OWNER in ('SOMEUSER')
group by OWNER order by OWNER;

OWNER COUNT(*)
------------------------------ ----------
SOMEUSER 205

This is a good time to check and recompile invalid objects so you run the utlrp script in your source database and query the dba_objects table again:

@?/rdbms/admin/utlrp.sql;

...

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
2

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
1


PL/SQL procedure successfully completed.

SQL> column OBJECT_NAME format a50
SQL> select OWNER, OBJECT_TYPE, OBJECT_NAME from dba_objects where STATUS='INVALID'
order by OWNER, OBJECT_TYPE, OBJECT_NAME;

OWNER OBJECT_TYPE OBJECT_NAME
------------------------------ ------------------- -----------------------------
SOMEUSER PROCEDURE SOME_PROCEDURE
PERFSTAT PACKAGE BODY STATSPACK
SYS PACKAGE BODY DBMS_SWRF_REPORT_INTERNAL

Now you know why there are less objects in your target database, the invalid objects where not exported. You might want to check the procedure's errors and fix them before doing the export/import operation again.

No comments:

Post a Comment