Schema Level Export data pump and Import data pump

Description:–

Performing the import of Schemas of your database is as simple as exporting them. Similar to expdp Schema export, we use SCHEMAS parameter to perform the import of the schema. SCHEMAS parameter specifies that user wants to perform Schema-Mode import. Also using Schema parameter helps you in choosing a particular schema from the multiple schema export to import.

Step1:Create the new user and table:

SQL> conn remo/remo

Connected.

SQL> select count(*) from emp;

COUNT(*)

———-

SQL> host

Step 2:And Create Directory and grant permission:

SQL>create directory dir as ‘/u01/exportfile’;

SQL>grant exp_full_database,imp_full_database to sys,system,remo;

SQL> grant read,write on directory dir to sys,system,remo;

Grant succeeded.

Step3:Export the Dumpfile:

[oracle@trichy u01]$ expdp directory=dir dumpfile=bkp_remo.dmp log=bkp_remo.log owner=’REMO’

Export: Release 12.2.0.1.0 - Production on Tue Nov 10 00:35:48 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

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "log=bkp_remo.log" Location: Command Line, Replaced with: "logfile=bkp_remo.log"

Legacy Mode Parameter: "owner=REMO" Location: Command Line, Replaced with: "schemas=REMO"

Legacy Mode has set reuse_dumpfiles=true parameter.

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=dir dumpfile=bkp_remo.dmp logfile=bkp_remo.log schemas=REMO reuse_dumpfiles=true

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/INDEX/INDEX

. . exported "REMO"."EMP"                                5.539 KB       7 rows

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

  /u01/backup/bkp_remo.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Nov 10 00:36:59 2020 elapsed 0 00:00:47

[oracle@trichydoyen ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 10 00:41:33 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> conn remo/remo

Connected.

SQL> select count(*) from emp;

COUNT(*)

———-

Step4:To Drop the User:

SQL> conn sys/dba as sysdba

Connected.

SQL> drop user remo cascade;

drop user remo cascade

*ERROR at line 1:

ORA-01940: cannot drop a user that is currently connected

SQL> shut abort

ORACLE instance shut down.

SQL> startup force

ORACLE instance started.

Total System Global Area 834666496 bytes

Fixed Size 8626192 bytes

Variable Size 583012336 bytes

Database Buffers 239075328 bytes

Redo Buffers 3952640 bytes

Database mounted.

Database opened.

SQL> drop user remo cascade;

User dropped.

SQL> conn remo/remo

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn sys/dba as sysdba

Connected.

Step 5: Now Import the schema file after to check the dropped user.

SQL> host

[oracle@trichydoyen ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 10 00:41:33 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> host

[oracle@trichy u01]$ impdp directory=dir dumpfile=bkp_remo.dmp log=bkp_remo.log remap_schema=REMO:SYSTEM;

Import: Release 12.2.0.1.0 - Production on Tue Nov 10 00:40:51 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

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "log=bkp_remo.log" Location: Command Line, Replaced with: "logfile=bkp_remo.log"

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=dir dumpfile=bkp_remo.dmp logfile=bkp_remo.log remap_schema=REMO:SYSTEM

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 "SYSTEM"."EMP"                              5.539 KB       7 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

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 Nov 10 00:41:33 2020 elapsed 0 00:00:21

[oracle@trichy u01]$

[oracle@trichydoyen ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 10 00:43:33 6 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> conn remo/remo

Connected.

SQL> select count(*) from emp;

COUNT(*)

———-

Inbaraj Asirvatham