Exp/imp under Oracle

At ordinary times, when you do Oracle backup, you usually do logical hot backup, while you generally use exp/imp to do logical hot backup. Let's talk about it briefly here.

Use exp to export data imp import data as follows:

Exp username/password owner=backup file=backup.dmpimp username/password file=backup.dmp ignore=y full=y

Explain here that ignore=y means to ignore the creation error and continue with the following operations. This parameter in full=y means that all imports include table constraints, which is more important when importing.

However, it is important to note that a new feature has been added after the Oracle 11g version begins. When the table has no data, no segment is allocated to save space. This feature is enabled by default and can be viewed in the following ways:

Sql > show parameter deferred_segment_creation

Therefore, you still need to check when you want to export using exp after 11g version. Of course, because of this feature, empty tables will not be exported if you encounter empty tables when exporting using exp in 11g version. There are many solutions to this problem. Of course, the preferred method for individuals is to disable the deferred_segment_creation feature before export, check all empty tables, and then allocate space for all empty tables found.

Sql > alter system set deferred_segment_creation=false;sql > select table_name from user_tables where NUM_ROWS=0;sql > select 'alter table' | | table_name | 'allocate extent;' from user_tables where num_rows=0

You can export with exp at this time.