IMPORT AND EXPORT
UTILITY
How does one backup a
database using Import / Export Utility?
Oracle exports are
"logical" database backups (not physical) as they extract data and
logical definitions from the database into a file.
Usage of Export or Import
Utility:-
expdp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
expdp scott/tiger file=emp.dmp tables=(emp,dept)
impdp scott/tiger file=emp.dmp full=yes
impdp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept
Exporting a subset of table:- exp scott/tiger tables=emp query="where deptno=10"
Can One Import tables to a
different tablespace?
1)
Pre-create
the table(s) in the correct tablespace:
- Import the dump file using the INDEXFILE= option
- Edit the indexfile. Remove remarks and specify the correct tablespaces.
- Run this indexfile against your database, this will create the required tables in the appropriate tablespaces
- Import the table(s) with the IGNORE=Y option.
2)
Change
the default tablespace for the user:
- Revoke the "UNLIMITED TABLESPACE" privilege from the user
- Import Table
What are the common Import/ Export problems?
- ORA-00001: Unique constraint (...) violated
You
are importing duplicate rows. Use IGNORE=YES to skip tables that already exist
(imp will give an error if the object is re-created).
- ORA-01555: Snapshot too old
Ask
your users to STOP working while you are exporting or try using parameter
CONSISTENT=NO
- ORA-01562: Failed to extend rollback segment
Create
bigger rollback segments or set parameter COMMIT=Y while importing
- IMP-00015: Statement failed ... object already exists...
Use
the IGNORE=Y import parameter to ignore these errors, but be careful as you
might end up with duplicate rows.
No comments:
Post a Comment