Import and Export in Oracle

Hey guys, in this blog I am going to explain to you about Import and Export in Oracle.

Fig.1 

On clota-labl08-

 

Step 1- To Check the Process in Oracle like pmon. 

[oracle@clota-labl08 ~]$ ps -ef | grep -i pmon

oracle      2207       1  0 Sep26 ?        00:00:05 ora_pmon_clotaDB

oracle     43671   37353  0 04:47 pts/4    00:00:00 grep --color=auto -i pmon

Step 2 – To Set the Environment for the Oracle

[oracle@clota-labl08 ~]$ . oraenv

ORACLE_SID = [DB1] ? clotaDB

The Oracle base remains unchanged with value /u01/app/oracle

 

Step 3 – Login as sys account.

[oracle@clota-labl08 ~]$ sqlplus / as sysdba

 

Step 4 – With Oracle Create an individual User.

SQL> create user i3 identified by Secure123;

 

Step 5 – Give grant to create session for particular User

SQL> grant create session to i3;

 

Step 6- Give grant to create table

SQL> grant create table to i3;

Step 7 – Give unlimited Quota for users’ tablespace for particular user.

SQL> alter user i3 quota unlimited on users;

 

Step 8 – Take DDL for a certain user.

SQL> select dbms_metadata.get_ddl('USER','I3') from dual;

SQL> set long 99999/

DBMS_METADATA.GET_DDL('USER','I3')

--------------------------------------------------------------------------------

 CREATE USER "I3" IDENTIFIED BY VALUES 'S:91874A071FD4EE320BFF622496A03D7E7BB7

6A0FDC31C23A983F69D13C4C;T:ECE3E745B3E5F79722F9CCD413F46776362EF1213562174E89157

69EDAF9BC4E139738BE861ECB759B00E33791BC1F902DDDA89155E18CEB0432150847688E14BEC50

72EAC4268D1043D089283DA49DD'

--------------------------------------------------------------------------------

Step 9- Change the default tablespace in Oracle.

SQL> alter user i3 default tablespace clota_data quota unlimited on clota_data;

 

Step 10- Once more, take the DDL for the specific Oracle user.

SQL> select dbms_metadata.get_ddl('USER','I3') from dual;

 

DBMS_METADATA.GET_DDL('USER','I3')

--------------------------------------------------------------------------------

CREATE USER "I3" IDENTIFIED BY VALUES 'S:91874A071FD4EE320BFF622496A03D7E7BB7

6A0FDC31C23A983F69D13C4C;T:ECE3E745B3E5F79722F9CCD413F46776362EF1213562174E89157

69EDAF9BC4E139738BE861ECB759B00E33791BC1F902DDDA89155E18CEB0432150847688E14BEC50

72EAC4268D1043D089283DA49DD'

      DEFAULT TABLESPACE "CLOTA_DATA"

      TEMPORARY TABLESPACE "TEMP"

--------------------------------------------------------------------------------

 

Step 11- In an Oracle instance, create a table. 

SQL> create table i3.emp(id int, name varchar2(20), address varchar2(50));

Table created.

SQL> create table i3.dpt(id number, name varchar2(20));

Table created.

Step 12- Insert some values in emp table.

SQL> insert into i3.emp values(1,'abcd','xyz');

SQL> set lines 300 pages 300/

 

        ID NAME                 ADDRESS

---------- -------------------- --------------------------------------------------

         1 abcd                 xyz

         2 abcd2                xyz2

         3 abcd3                xyz3

 

SQL> insert into i3.dpt(id, name) values( 10,'IT');

insert into i3.dpt(id, name) values( 11,'Admin');

1 row created.

 

Step 13- Describe dba directories

SQL> desc dba_directories;

SQL> set lines 300 pages 300

SQL> col OWNER for a20

SQL> col DIRECTORY_NAME for a20

SQL> col DIRECTORY_PATH for a20

SQL> /

OWNER                DIRECTORY_NAME       DIRECTORY_PATH

-------------------- -------------------- --------------------

SYS                  EXPORT_IMPORT        /u03/backup/

SYS                  SDO_DIR_WORK

SYS                  SDO_DIR_ADMIN        /u05/app/oracle/product/19.3.0.0/dbhome_1/md/admin

 SYS                  XMLDIR               /u05/app/oracle/product/19.3.0.0/dbhome_1/rdbms/xml

SYS                  XSDDIR               /u05/app/oracle/product/19.3.0.0/dbhome_1/rdbms/xml/schema

 

Step 14- Give a specific user permission to read and write to the directory.

SQL> grant read, write on directory EXPORT_IMPORT to i3;

Step 15- Consider the export of the Oracle-compatible Schemas.

[oracle@clota-labl08 ~]$ expdp directory=EXPORT_IMPORT dumpfile=i3_export.dmp logfile=i3_export.log schemas=I3

 

Step 16- To move the export file to a different server.

Scp i3_export* oracle@192.168.0.160:/u02/hari_dir/

 

On clota-labl03-

 

Step 17- To Check the Process in Oracle like pmon. 

[oracle@clota-labl03 ~]$ ps -ef | grep -i pmon

oracle      3911       1  0 Sep26 ?        00:00:04 ora_pmon_testdb

oracle    339154  338684  0 14:56 pts/2    00:00:00 grep --color=auto -i pmon

 

Step 18- To Set the Environment for the Oracle.

[oracle@clota-labl03 ~]$ . oraenv

ORACLE_SID = [cdb1] ? testdb

The Oracle base remains unchanged with value /u01/app/oracle

Step 19- Consider importing Oracle-compatible schemas and remapping the schema and tablespace on the Oracle instance. 

[oracle@clota-labl03 ~]$ impdp directory=hari_dir dumpfile=i3_export.dmp logfile=i3_import.log remap_schema=i3:i4 remap_tablespace=clota_data:users

 

Step 20- Select the data from the emp table. 

SQL> select * from emp;

ID        NAME         ADDRESS

 1           abcd                 xyz

2          abcd2                xyz2

3          abcd3                xyz3

 

Step 21- Drop the User.

SQL> drop user i4 cascade;

 

Step 22- In Secondary Server, create the user i4.

SQL> create user i4 identified by Secure123;

 

Step 23- Give grant to create session for particular User

SQL> grant create session, create table to i4;

Grant succeeded.

 

Step 24 – Give unlimited Quota for users’ tablespace for particular user.

SQL> alter user i4 quota unlimited on users;

User altered.

 

Step 25- Describe dba directories

SQL> select * from dba_directories;

 

Step 26- Give a specific user permission to read and write to the directory.

SQL> grant read,write on directory IMPORT_EXPORT to i4;

 

Step 27- Consider importing Oracle-compatible schemas and remapping the schema and tablespace on the Oracle instance.

[oracle@clota-labl03 ~]$ impdp directory=IMPORT_EXPORT dumpfile=i3_export.dmp logfile=i3_import2.log remap_schema=i3:i4 remap_tablespace=clota_data:users