Step1:To create user and table:
SQL> conn remo/remo
Connected.
SQL> select count(*) from emp;
COUNT(*)
———-
4
SQL> host
Step2: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
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 remo/remo
Connected.
SQL> select count(*) from emp;
COUNT(*)
———-
4
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.
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
Copyright (c) 1982, 2016, Oracle. All rights reserved.
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
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 remo/remo
Connected.
SQL> select count(*) from emp;
COUNT(*)
———-
4