How to Use the Oracle Export Program exp

Oracle export Utility Program (Export utility) allows you to extract data from a database and write the data to an operating system file. The basic format used by Exp: exp [username [/password [@ Service]. The following example shows the common usage of Exp.

1. Get help
Exp help = y

2. Export a complete database
Exp system/manager file = bible_db log = dible_db full = y

3. Export Database definitions without exporting data
Exp system/manager file = bible_db log = dible_db full = y rows = N

4. Export all tables, indexes, and other objects to which one or more specified users belong.
Exp system/manager file = seapark log = seapark owner = seapark
Exp system/manager file = seapark log = seapark owner = (seapark, Amy, amyc, Harold)
Note: although you have obtained all objects of this user, you still cannot obtain any synonyms referenced by these objects. The solution is to use the following SQL * Plus command to create a script file. Run this script file to obtain an executable script that recreates all public synonyms of the object to which seapark belongs, then run the script on the target database to recreate the synonym.

Set linesize 132
Set pagesize 0
Set trimspool on
Spool C:/seapark. Syn
Select 'create public synonym' | synonym_name
| 'For' | table_owner | '.' | table_name | ';'
From dba_synonyms
Where table_owner = 'seapark' and owner = 'public ';
Spool off

5. Export one or more specified tables
Exp seapark/seapark file = Tank log = Tank tables = Tank
Exp system/manager file = Tank log = Tank tables = seapark. Tank
Exp system/manager file = Tank log = Tank tables = (seapark. Tank, Amy. Artist)

6. estimate the size of the exported file
Total number of bytes for all tables:
Select sum (bytes)
From dba_segments
Where segment_type = 'table ';

Total number of bytes of the table to which the seapark user belongs:
Select sum (bytes)
From dba_segments
Where owner = 'seapark'
And segment_type = 'table ';

The number of bytes in the aquatic_animal table under the seapark User:
Select sum (bytes)
From dba_segments
Where owner = 'seapark'
And segment_type = 'table'
And segment_name = 'aquatic _ animal ';

7. Export a subset of table data (Oracle8i or above)
NT System:

Exp system/manager query = 'where salad_type = 'fruit' 'tables = Amy. salad_type
File = fruit log = fruit
UNIX system:

Exp system/manager query =/"where salad_type =/'fruit/'/" tables = Amy. salad_type
File = fruit log = fruit

8. Separate an exported file with multiple files
Exp system/Manager
File = (paycheck_1, paycheck_2, paycheck_3, paycheck_4)
Log = paycheck, filesize = 1g tables = HR. paycheck

9. Use the parameter file
Exp system/manager parfile = bible_tables.par
Bible_tables.par parameter file:

# Export the sample tables used for the Oracle8i Database Administrator's le.
File = bible_tables
Log = bible_tables
Tables = (
Amy. Artist
Amy. Books
Seapark. Checkup
Seapark. Items
)

10. incremental Export
Full incremental Export (complete), that is, backing up the entire database
Exp system/manager inctype = complete file = 990702.dmp
"Incremental" incremental Export (incremental), that is, the data changed after the last backup
Exp system/manager inctype = incremental file = 990702.dmp
Cumulative: backs up the data changed after the last full export.
Exp system/manager inctype = cumulative file = 990702.dmp