WARNING: Rants ahead.
Simple request migrate schema from one database to another, right?
Create new database, perform schema export and import; this only works if objects are self contained.
The following objects are missing from schema export to name a few.
DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Here’s what was done and hopefully did not missed anything. TBS was pre-created.
$ cat impdp_full_public.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_public.log
include=PASSWORD_VERIFY_FUNCTION
include=PROFILE
include=ROLE
include=PUBLIC_SYNONYM/SYNONYM
$ cat impdp_full_schema.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_schema.log
include=SCHEMA:"IN ('DEMO')"
DEMO
$ cat expdp_schema.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
flashback_time="TO_TIMESTAMP(TO_CHAR(systimestamp,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')"
metrics=Y
reuse_dumpfiles=Y
dumpfile=schema.dmp
logfile=exp_schema.log
SCHEMAS=ggs_admin,demo
$ expdp parfile=expdp_schema.par
Export: Release 11.2.0.4.0 - Production on Sat Feb 11 10:47:22 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA parfile=expdp_schema.par
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimated 12 TABLE_DATA objects in 0 seconds
Total estimation using BLOCKS method: 19 MB
Processing object type SCHEMA_EXPORT/USER
Completed 2 USER objects in 0 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Completed 14 SYSTEM_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Completed 5 ROLE_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Completed 2 DEFAULT_ROLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Completed 1 TABLESPACE_QUOTA objects in 0 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Completed 3 PROCACT_SCHEMA objects in 1 seconds
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Completed 2 SYNONYM objects in 0 seconds
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Completed 2 TYPE objects in 0 seconds
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Completed 1 SEQUENCE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Completed 14 PROCACT_INSTANCE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Completed 12 TABLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Completed 1 FUNCTION objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Completed 2 PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Completed 1 ALTER_FUNCTION objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Completed 2 ALTER_PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Completed 1 INDEX objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Completed 4 CONSTRAINT objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Completed 7 INDEX_STATISTICS objects in 0 seconds
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Completed 5 VIEW objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Completed 12 TABLE_STATISTICS objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Completed 8 PROCACT_INSTANCE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
Completed 8 PROCDEPOBJ objects in 1 seconds
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Completed 11 PROCOBJ objects in 0 seconds
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Completed 1 PROCACT_SCHEMA objects in 0 seconds
. . exported "DEMO"."T" 6.087 MB 68540 rows
. . exported "GGS_ADMIN"."OGG$Q_TAB_E_HAWK" 16.98 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_C" 5.804 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_G" 13.16 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_H" 9.039 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_I" 9.007 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_L" 7.828 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_S" 11.31 KB 3 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_T" 6.218 KB 0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_SEED" 11.81 KB 1 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT" 0 KB 0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_HISTORY" 0 KB 0 rows
Completed 12 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 1 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/schema.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Feb 11 10:47:29 2017 elapsed 0 00:00:07
$ cat expdp_full.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
flashback_time=systimestamp
metrics=Y
exclude=statistics
reuse_dumpfiles=Y
dumpfile=full.dmp
#PARALLEL=2
#DUMPFILE=full%U.dmp
logfile=expdp_full.log
FULL=Y
$ expdp parfile=expdp_full.par
Export: Release 11.2.0.4.0 - Production on Sat Feb 11 10:59:36 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_FULL_01": /******** AS SYSDBA parfile=expdp_full.par
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Estimated 69 TABLE_DATA objects in 3 seconds
Total estimation using BLOCKS method: 23.31 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Completed 4 TABLESPACE objects in 1 seconds
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
Completed 4 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/ROLE
Completed 16 ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Completed 4 PROC_SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Completed 48 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Completed 49 ROLE_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Completed 4 DEFAULT_ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Completed 1 TABLESPACE_QUOTA objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Completed 18 SEQUENCE objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Completed 1 DIRECTORY objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 4 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/CONTEXT
Completed 3 CONTEXT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Completed 6 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Completed 10 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Completed 11 TYPE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Completed 3 PROCACT_SYSTEM objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Completed 17 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Completed 4 PROCACT_SYSTEM objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Completed 7 PROCACT_SCHEMA objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE
Completed 14 PROCACT_INSTANCE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Completed 72 TABLE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Completed 6 PRE_TABLE_ACTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 24 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Completed 424 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Completed 1 PACKAGE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Completed 5 FUNCTION objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 4 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Completed 4 PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Completed 5 ALTER_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Completed 4 ALTER_PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Completed 106 INDEX objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Completed 89 CONSTRAINT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Completed 17 VIEW objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 3 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Completed 7 COMMENT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Completed 1 PACKAGE_BODY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Completed 36 REF_CONSTRAINT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
Completed 4 POST_TABLE_ACTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Completed 2 TRIGGER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Completed 8 PROCACT_INSTANCE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Completed 8 PROCDEPOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Completed 11 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Completed 4 PROCACT_SCHEMA objects in 0 seconds
Processing object type DATABASE_EXPORT/AUDIT
Completed 29 AUDIT objects in 1 seconds
. . exported "DEMO"."T" 6.087 MB 68540 rows
. . exported "GGS_ADMIN"."OGG$Q_TAB_E_HAWK" 16.98 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_C" 5.804 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_G" 13.16 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_H" 9.039 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_I" 9.007 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_L" 7.828 KB 0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_S" 11.31 KB 3 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_T" 6.218 KB 0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_SEED" 11.81 KB 1 rows
. . exported "SYSTEM"."DEF$_LOB" 6.664 KB 0 rows
. . exported "OUTLN"."OL$HINTS" 13.12 KB 0 rows
. . exported "SYSTEM"."DEF$_AQCALL" 14.73 KB 0 rows
. . exported "SYSTEM"."DEF$_AQERROR" 14.73 KB 0 rows
. . exported "SYSTEM"."REPCAT$_EXCEPTIONS" 7.843 KB 0 rows
. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL" 6.25 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS" 5.859 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS" 10.72 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS" 7.085 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 6.257 KB 0 rows
. . exported "OUTLN"."OL$" 10.17 KB 0 rows
. . exported "OUTLN"."OL$NODES" 7.781 KB 0 rows
. . exported "SYSTEM"."DEF$_CALLDEST" 7 KB 0 rows
. . exported "SYSTEM"."DEF$_DEFAULTDEST" 5.007 KB 0 rows
. . exported "SYSTEM"."DEF$_DESTINATION" 13.50 KB 0 rows
. . exported "SYSTEM"."DEF$_ERROR" 8.210 KB 0 rows
. . exported "SYSTEM"."DEF$_ORIGIN" 7.390 KB 0 rows
. . exported "SYSTEM"."DEF$_PROPAGATOR" 5.796 KB 0 rows
. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS" 6.218 KB 0 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE" 6.328 KB 2 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN" 7.843 KB 0 rows
. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP" 6.210 KB 0 rows
. . exported "SYSTEM"."REPCAT$_CONFLICT" 6.226 KB 0 rows
. . exported "SYSTEM"."REPCAT$_DDL" 7.406 KB 0 rows
. . exported "SYSTEM"."REPCAT$_EXTENSION" 9.890 KB 0 rows
. . exported "SYSTEM"."REPCAT$_FLAVORS" 7.390 KB 0 rows
. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS" 8.187 KB 0 rows
. . exported "SYSTEM"."REPCAT$_GENERATED" 8.640 KB 0 rows
. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN" 6.601 KB 0 rows
. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS" 6.203 KB 0 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS" 5.429 KB 0 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES" 6.882 KB 28 rows
. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN" 8.679 KB 0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY" 9.070 KB 0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP" 6.656 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES" 10.69 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCAT" 7.398 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCATLOG" 13.09 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCOLUMN" 13.79 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS" 7.390 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPOBJECT" 10.67 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPPROP" 8.226 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPSCHEMA" 8.609 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION" 8.25 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD" 5.835 KB 19 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS" 8.265 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL" 7.835 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITES_NEW" 7.015 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS" 6.210 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP" 7 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 7.046 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES" 9.062 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS" 5.484 KB 3 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 6.648 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES" 6.289 KB 2 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 5.828 KB 0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 7.867 KB 0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT" 0 KB 0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_HISTORY" 0 KB 0 rows
Completed 69 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 2 seconds
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/full.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Sat Feb 11 10:59:53 2017 elapsed 0 00:00:17
$ cat impdp_full_sql.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_syn.log
include=PASSWORD_VERIFY_FUNCTION
include=PROFILE
include=ROLE
include=PUBLIC_SYNONYM/SYNONYM
sqlfile=impdp_full.sql
$ impdp parfile=impdp_full_sql.par
Import: Release 11.2.0.4.0 - Production on Sat Feb 11 11:09:06 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
Startup took 0 seconds
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": /******** AS SYSDBA parfile=impdp_full_sql.par
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/ROLE
Completed 16 ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Completed 6 SYNONYM objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Sat Feb 11 11:09:09 2017 elapsed 0 00:00:02
$ cat $ORACLE_HOME/rdbms/log/impdp_full.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
CREATE FUNCTION verify_function_11G
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
db_name varchar2(40);
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
i_char varchar2(10);
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
-- Check for the minimum length of the password
IF length(password) < 8 THEN
raise_application_error(-20001, 'Password length less than 8');
END IF;
-- Check if the password is same as the username or username(1-100)
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20002, 'Password same as or similar to user');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to user name ');
END IF;
END LOOP;
-- Check if the password is same as the username reversed
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
raise_application_error(-20003, 'Password same as username reversed');
END IF;
-- Check if the password is the same as server name and or servername(1-100)
select name into db_name from sys.v$database;
if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
raise_application_error(-20004, 'Password same as or similar to server name');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to server name ');
END IF;
END LOOP;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password is the same as oracle (1-100)
simple_password := 'oracle';
FOR i IN 1..100 LOOP
i_char := to_char(i);
if simple_password || i_char = NLS_LOWER(password) THEN
raise_application_error(-20007, 'Password too simple ');
END IF;
END LOOP;
-- Check if the password contains at least one letter, one digit
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20008, 'Password must contain at least one digit, one character');
END IF;
-- 2. Check for the character
<>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20009, 'Password must contain at least one \
digit, and one character');
END IF;
<>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
differ := abs(differ);
IF differ < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20011, 'Password should differ from the \
old password by at least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/
-- new object type path: DATABASE_EXPORT/PROFILE
ALTER PROFILE "DEFAULT"
LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 15552000/86400
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION "VERIFY_FUNCTION_11G"
PASSWORD_LOCK_TIME 86400/86400
PASSWORD_GRACE_TIME 604800/86400 ;
-- new object type path: DATABASE_EXPORT/ROLE
CREATE ROLE "SELECT_CATALOG_ROLE";
REVOKE "SELECT_CATALOG_ROLE" FROM SYS;
CREATE ROLE "EXECUTE_CATALOG_ROLE";
REVOKE "EXECUTE_CATALOG_ROLE" FROM SYS;
CREATE ROLE "DELETE_CATALOG_ROLE";
REVOKE "DELETE_CATALOG_ROLE" FROM SYS;
CREATE ROLE "DBFS_ROLE";
REVOKE "DBFS_ROLE" FROM SYS;
CREATE ROLE "AQ_ADMINISTRATOR_ROLE";
REVOKE "AQ_ADMINISTRATOR_ROLE" FROM SYS;
CREATE ROLE "AQ_USER_ROLE";
REVOKE "AQ_USER_ROLE" FROM SYS;
CREATE ROLE "ADM_PARALLEL_EXECUTE_TASK";
REVOKE "ADM_PARALLEL_EXECUTE_TASK" FROM SYS;
CREATE ROLE "GATHER_SYSTEM_STATISTICS";
REVOKE "GATHER_SYSTEM_STATISTICS" FROM SYS;
CREATE ROLE "RECOVERY_CATALOG_OWNER";
REVOKE "RECOVERY_CATALOG_OWNER" FROM SYS;
CREATE ROLE "SCHEDULER_ADMIN";
REVOKE "SCHEDULER_ADMIN" FROM SYS;
CREATE ROLE "HS_ADMIN_SELECT_ROLE";
REVOKE "HS_ADMIN_SELECT_ROLE" FROM SYS;
CREATE ROLE "HS_ADMIN_EXECUTE_ROLE";
REVOKE "HS_ADMIN_EXECUTE_ROLE" FROM SYS;
CREATE ROLE "HS_ADMIN_ROLE";
REVOKE "HS_ADMIN_ROLE" FROM SYS;
CREATE ROLE "GLOBAL_AQ_USER_ROLE" IDENTIFIED GLOBALLY;
CREATE ROLE "OEM_ADVISOR";
REVOKE "OEM_ADVISOR" FROM SYS;
CREATE ROLE "OEM_MONITOR";
REVOKE "OEM_MONITOR" FROM SYS;
-- new object type path: DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
CREATE PUBLIC SYNONYM "OL$" FOR "SYSTEM"."OL$";
CREATE PUBLIC SYNONYM "OL$HINTS" FOR "SYSTEM"."OL$HINTS";
CREATE PUBLIC SYNONYM "OL$NODES" FOR "SYSTEM"."OL$NODES";
CREATE PUBLIC SYNONYM "PRODUCT_PROFILE" FOR "SYSTEM"."PRODUCT_PRIVS";
CREATE PUBLIC SYNONYM "PRODUCT_USER_PROFILE" FOR "SYSTEM"."PRODUCT_PRIVS";
CREATE PUBLIC SYNONYM "PUBLIC_HAWK" FOR "GGS_ADMIN"."OGG$Q_TAB_E_HAWK";
Thinking Out Loud 