EXPORT DP AND IMPORT DP IN SCHEMAS PARAMETER USING PARFILE
In this tutorial we are going to learn about how to do export and import datapump using schemas parameter
DESCRIPTION:
A schema export is specified using the schemas parameter. This is the default export mode. If you have the DATAPUMP_EXP_FULL_DATABASE role, then you can specify a list of schemas, optionally including the schema definitions themselves and also system privilege grants to those schemas. If you do not have the DATAPUMP_EXP_FULL_DATABASE role, then you can export only your own schema.
The sys schema cannot be used as a source schema for export jobs.
[oracle@oracle ~]$ expdp directory=parexp dumpfile=schema.dmp logfile=schema.log schemas=lockdownExport: Release 12.2.0.1.0 – Production on Tue Sep 22 08:08:55 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: lockdown/dba1
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “LOCKDOWN”.”SYS_EXPORT_SCHEMA_04″: lockdown/******** directory=parexp dumpfile=schema.dmp logfile=schema.log schemas=lockdown
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported “LOCKDOWN”.”SYS_EXPORT_SCHEMA_01″ 0 KB 0 rows
. . exported “LOCKDOWN”.”SYS_EXPORT_SCHEMA_02″ 0 KB 0 rows
. . exported “LOCKDOWN”.”SYS_EXPORT_SCHEMA_03″ 0 KB 0 rows
. . exported “LOCKDOWN”.”PANDEMIC” 6.484 KB 4 rows
. . exported “LOCKDOWN”.”EXPORTB” 0 KB 0 rows
. . exported “LOCKDOWN”.”TB1″ 0 KB 0 rows
. . exported “LOCKDOWN”.”SAM1″ 0 KB 0 rows
Master table “LOCKDOWN”.”SYS_EXPORT_SCHEMA_04″ successfully loaded/unloaded
******************************************************************************
Dump file set for LOCKDOWN.SYS_EXPORT_SCHEMA_04 is:
/u01/exppar/schema.dmp
Job “LOCKDOWN”.”SYS_EXPORT_SCHEMA_04″ successfully completed at Tue Sep 22 08:09:41 2020 elapsed 0 00:00:39
[oracle@oracle ~]$ !sqsqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 22 08:10:03 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> conn lockdown/dba1
Connected.
SQL> conn sys/dba as sysdba
Connected.
SQL> drop user lockdown cascade;
drop user lockdown cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 8620080 bytes
Variable Size 436209616 bytes
Database Buffers 754974720 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> drop user lockdown cascade;
User dropped.
SQL> conn lockdown/dba1
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> !
[oracle@oracle ~]$ impdp directory=parexp dumpfile=schema.dmp logfile=impdpschema.logImport: Release 12.2.0.1.0 – Production on Tue Sep 22 08:13:54 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″: /******** AS SYSDBA directory=parexp dumpfile=schema.dmp logfile=impdpschema.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “LOCKDOWN”.”SYS_EXPORT_SCHEMA_01″ 0 KB 0 rows
. . imported “LOCKDOWN”.”SYS_EXPORT_SCHEMA_02″ 0 KB 0 rows
. . imported “LOCKDOWN”.”SYS_EXPORT_SCHEMA_03″ 0 KB 0 rows
. . imported “LOCKDOWN”.”PANDEMIC” 6.484 KB 4 rows
. . imported “LOCKDOWN”.”EXPORTB” 0 KB 0 rows
. . imported “LOCKDOWN”.”TB1″ 0 KB 0 rows
. . imported “LOCKDOWN”.”SAM1″ 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “SYS”.”SYS_IMPORT_FULL_01″ successfully completed at Tue Sep 22 08:14:28 2020 elapsed 0 00:00:21
[oracle@oracle ~]$ !sqsqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 22 08:44:35 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> conn lockdown/dba1
Connected.
SQL> select * from pandemic;
ID NAME CITY PHONE_NO
———- ——————– ——————– ———-
1 shankar coimbatore 7906438930
2 prasanth selam 843873793
3 richred erode 8484949403
4 sana coimbatore 9057959574
