Pages

Friday, February 8, 2019

Export or import with advantages?

Export or import with advantages?


What happens when we export data from a database?
Before going into the topic, you should note some things. Export means using select statement from source(backup). And import means using the insert statement (restore).

The export utility will simply prepare a set of sql statements and execute them against the target database.
The data that is returned by the select operation(definition and rows of the objects) will be written to a file known as the dump file. The extension of the dump file is “.dmp”.
This dump file is platform independent and can be carried to any other oracle site.
DBA can import the objects from the dump file into database when required.
Import file reads the definition from dump file and creates the objects there after.
It executes insert statements from the dump file against the database and loads the data back into the database objects that were created from the datafile.

Advantages of export and import:

We can backup and restore a particular object(table).
These are used to copy objects from one oracle schema to another.
For performing an oracle database from one operating system to another(like windows to linux), then logical backups are the only way to do that using dump file.
We can export(backup) and import(restore) objects without shutting down the database.
Whenever we upgrade the oracl le versions like 8i to 9i, oracle will automatically upgrade the database. But, still we need to export full database before we begin the upgrade process.  This is because, if upgrade fails then exp will be helpful.
Export helps in performing a database re-org. After using database for a while, it gets fragmented with some segments relating to so many extents. This will result in poor I/O performance. For this, we follow some steps.

1. Full Database Export.
2. Drop the Database.
3. Create a Brand New Database.
4. Full Database Import

At the end of the import, all the segments will be back within single huge extents and we will get rid of all fragmentation.
Exports can be done at table level, schema level, tablespace level or full database.
While exporting a table we can mention the query option from 8i onwards, example export the emp table where the deptno=10
If the database is too large the dump file can run into GB and this much space may not be available. In a single file system, thus we can export the data to multiple files spanning across multiple files.
The size of an export dump file is usually 6 to 10 times smaller than the size of the database.

why??????????

1.  Control file, redolog file are not exported
2. Export does not carry any base tables(objects owned by sys user)
3. The temporary segments are ignored.
4. Undo and rollback segments are ignored as we are interested only in committed data.
5. Only table data goes into the dump file along with the table definitions

Time taken for exp:imp is usually 1:6 ratio as export is only a select operation where as import is a combination of DML+DDL operations and each line goes through the undo or rollback segments. And also goes to logfiles.
Import operation will cause a lot of redo activity and hence log switching will occur.

No comments:

Post a Comment