There may be an error in the database import log similar to this:
ORA-39083: Object type TABLE:"AVUSER"."T_AV_STMT_COMP_RESULTS" failed to create with error: ORA-14223: Deferred segment creation is not supported for this table Failing sql is: CREATE TABLE "AVUSER"."T_AV_STMT_COMP_RESULTS" ("ID" NUMBER NOT NULL ENABLE, "COMPONENT_ID" NUMBER NOT NULL ENABLE, "JRXML" CLOB, "RR_XML" CLOB, "PRINT_OBJ" BLOB, "RR_NT" "AVUSER"."RES_ROWS_T" ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "DATA_1M" NESTED TABLE "RR_NT" STOR ORA-39083: Object type TABLE:"AVUSER"."T_AV_DATA_ACCESS_COLLECTORS" failed to create with error: ORA-14223: Deferred segment creation is not supported for this table Failing sql is: CREATE TABLE "AVUSER"."T_AV_DATA_ACCESS_COLLECTORS" ("ID" NUMBER(*,0) NOT NULL ENABLE, "APP_RESOLUTION_RULES" CLOB NOT NULL ENABLE, "USER_NORM_RULE_SPEC" "XMLTYPE") SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "DATA_50M" LOB ("APP_RESOLUTION_RULES") STORE AS BASICFILE ( ORA-39083: Object type TABLE:"AVUSER"."T_AV_DADC_VERSIONS" failed to create with error: ORA-14223: Deferred segment creation is not supported for this table;
These errors may not appear as failures in the database import.log when importing the database dmp file, but may be noticed later, as an error during migration of the database. These would appear in the User Interface Migration screen output as well as in the migrate.log file.
An example of the post-import, migration error is shown here:
begin Report_Views_Pkg.create_av_report_views; end; java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist ORA-06512: at "AVUSER.REPORT_VIEWS_PKG", line 328, ORA-06512: at line 1
Oracle 11g R2 Enterprise Edition contains an Oracle bug that appears on importing of a dump because of the default way a table could be created with deferred segments. (This bug has been fixed in Oracle Enterprise versions greater than 11g R2.)
This affects only:
Pre-7.x RSA Identity Governance & Lifecycle installations since Oracle 12 is required for 7.x.
Non-appliance implementations only (the appliance and soft appliance ship with Oracle 11g Standard edition so they are not affected. This affects Enterprise Edition only.)
This bug has been fixed in Oracle Enterprise versions greater than 11g R2. Upgrade to a supported version of RSA Identity Governance & Lifecycle (7.x) which requires Oracle 12.
It is recommended that you create your database instance with the deferred segment turned off before creating your schema. This will prevent the issue from occurring. Run the following SQL command as sysdba:
SQL> ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION = FALSE
However, if you have database dumps which were created on a system that has this issue (i.e. deferred_segment_creation=TRUE) , a different process is needed to prevent the error from occurring. This process is detailed below and recreates the tables prior to the import. It must be followed BEFORE importing a .dmp file.
Recreate the AVUSER:
Note that this step will drop any existing data, but this should not be a problem, as the intent is to import an existing database dump file.
If AVUSER has any active connections, the drop command will fail. In this case, the database instance should be restarted to ensure there are no active AVUSER connections, and then the drop command should be re-issued.
$ acm stopdb $ acm startdb $ sqlplus / as sysdba SQL> DROP USER AVUSER CASCADE; SQL> CREATE USER AVUSER identified by <password> profile ACMPROFILE; SQL> ALTER USER AVUSER default tablespace DATA_1M temporary TABLESPACE TEMP;
Run the normal impdp command with these additional options: