In Depth Oracle Import and Export

Hi guys, in this blog I am going to explain you about In-Depth Oracle Import and Export.

 

Export In 0racle-

In the context of databases, "export" refers to the process of extracting data from a database and saving it to an external file or another storage format. This process allows you to create a snapshot of the data in a form that can be easily transported or backed up. In Oracle databases, the export process is typically performed using utilities like EXP (export) or EXPDP (Data Pump Export).

Here are some common use cases for exporting data in Oracle:

 

Backup and Recovery-

Creating a backup of the database by exporting its contents allows for recovery in case of data loss or database corruption. The exported data can be later used to restore the database to a specific point in time.

 

Data Migration: -

When moving data from one Oracle database to another, exporting data from the source database and then importing it into the target database is a common practice. This is useful during database upgrades, system migrations, or when consolidating data from multiple sources.

 

Data Archiving-

Archiving historical data is often done by exporting older records to separate files. This helps in managing the size of the active database and allows for more efficient data retrieval.

 

Sharing Data-

Exporting data to a portable format makes it easy to share datasets with others or transfer data between different systems and environments.

 

Database Cloning-

Exporting and then importing a database or specific schemas allows for creating a copy (clone) of a database. This is useful for creating test environments that mimic the structure and data of the production database.

 

Create Data pump Directory-

The creation of an OS level directory, which Oracle will utilize for exports and imports, is the first step in using Oracle Data Pump. At the OS level, create a directory.

mkdir -p /u05/do_exp_dir

 

Create directory inside the database-

create directory datapump as '/u02/dp_exp_dir';

Grant permissions on director-

grant read,write on directory datapump to scott;

 

View directory information

select * from dba_directories;

 

Import in Oracle-

The process of loading data into a database from an external source is referred to as "import" in the context of databases. It is the inverse of "export," which is the process of removing data from a database and saving it in an external file or another storage type.

The import process is commonly used for the following purposes:

 

Restoring Database Backups-  

If you previously exported a database or specific data, you can use the import method to restore the data from the exported files. This is critical for recovery and backup plans.

 

Migrating Data-

When migrating data from one database system to another or moving data across instances, the import process can be used to get data into the new environment.

 

Importing Data-

Importing data is frequently part of the initial setup of a database. Instead of entering enormous datasets manually, you can prepare them in an external file and then import them into the database.

 

Data Warehousing-

Import is used in data warehousing to load data into the data warehouse from numerous source systems.

In Oracle databases, the import process is typically performed using utilities like IMP (import) or IMPDP (Data Pump Import). These tools allow you to bring data from dump files, which are generated during the export process, back into the database.

 

Table Level Import and Export-

 

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 Abhi10 identified by Abhi10;

 

Step 5 – Give grant to create session for particular User

SQL> grant create session to Abhi10;

 

Step 6- Give grant to create table

SQL> grant create table to Abhi10;

 

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

SQL> alter user Abhi10 quota unlimited on users;

 

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

SQL> create table Abhi10.emp (id NUMBER PRIMARY KEY, name VARCHAR2(50), age NUMBER);

Table created.

SQL> create table Abhi10.emp (emp_id NUMBER PRIMARY KEY, department VARCHAR2(50), salary NUMBER);

Table created.

 

Step 9- Insert some values in Abhi.10emp table.

SQL> INSERT INTO Abhi10.emp (id, name, age) VALUES (1, 'John', 25);

SQL> set lines 300 pages 300/

SQL> Select * from Abhi10.emp;

 

        ID NAME                                       AGE

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

         1 John                                                 25

         2 Alice                                                30

         3 Ali                                                    38

         4 Jv                                                     35

         5 Glen                                                 32

 

Step 10- 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 11- Consider the export of the Oracle-compatible Schemas.

[oracle@clota-labl08 ~]$ expdp directory=EXPORT_IMPORT dumpfile=Abhi10_export.dmp logfile=Abhi10_export.log tables='Abhi10.emp,Abhi10.emp1' 

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

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

 

On clota-labl03-

 

Step 13- 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 14- 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 15- With Oracle Create an individual Use

SQL> Create user Abhi11 identified by Abhi11;

User created.

Step 16- Give grant to create session and create table for particular User

SQL> grant create session, create table to Abhi11;

Grant succeeded.

Step 17 - Give unlimited Quota for users’ tablespace for particular user.

SQL> alter user Abhi11 quota unlimited on users;

User altered.

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

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

Grant succeeded.

 

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=Abhi10_export.dmp logfile=Abhi10_import.log remap_schema=Abhi10:Abhi11 remap_tablespace=users: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 20- Select the data from the emp table. 

SQL> select * from Abhi11.emp;

 

        ID NAME                                               AGE

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

         1 John                                                        25

         2 Alice                                                       30

         3 Ali                                                            38

         4 Jv                                                              35

         5 Glen                                                        32