Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Export Your Own Schema

By: FYIcenter.com

(Continued from previous topic...)

How To Export Your Own Schema?

If you have a non-system user account and you want to export all data objects in the schema associated with your account, you can use the "expdp" command with the SCHEMAS parameter. Running "expdp" command with a non-system user account requires a directory object granted to this user account. The following tutorial exercise shows you how to define a directory object and export a schema:

>mkdir \oraclexe\hr_dump

>cd \oraclexe\app\oracle\product\10.2.0\server\BIN

>sqlplus /nolog
SQL> connect SYSTEM/fyicenter

SQL> CREATE DIRECTORY hr_dump AS '\oraclexe\hr_dump';
Directory created.

SQL> GRANT READ ON DIRECTORY hr_dump TO hr;
Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY hr_dump TO hr;
Grant succeeded.

SQL> quit

>expdp hr/fyicenter SCHEMAS=hr 
  DIRECTORY=hr_dump DUMPFILE=schema.dmp LOGFILE=schema.log

Starting "HR"."SYS_EXPORT_SCHEMA_01":  
  hr/******** SCHEMAS=hr DIRECTORY=hr_dump
  DUMPFILE=schema.dmp 
  LOGFILE=schema.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 960 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCH
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
......
. . exported "HR"."COUNTRIES"          6.085 KB   25 rows
. . exported "HR"."DEPARTMENTS"        6.632 KB   27 rows
. . exported "HR"."EMPLOYEES"          15.76 KB  107 rows
. . exported "HR"."EMPLOYEES_TEMP"     15.86 KB  107 rows
......
Master table "HR"."SYS_EXPORT_SCHEMA_01" loaded/unloaded
*********************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
  C:\ORACLEXE\HR_DUMP\SCHEMA.DMP
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed

(Continued on next topic...)

  1. What Is the Simplest Tool to Run Commands on Oracle Servers?
  2. What Is the Quickest Way to Export a Table to a Flat File?
  3. How To Export Data with a Field Delimiter?
  4. What Is SQL*Loader?
  5. What Is a SQL*Loader Control File?
  6. How To Load Data with SQL*Loader?
  7. What Is an External Table?
  8. How To Load Data through External Tables?
  9. What Are the Restrictions on External Table Columns?
  10. What Is a Directory Object?
  11. How To Define an External Table in a Text File?
  12. How To Run Queries on External Tables?
  13. How To Load Data from External Tables to Regular Tables?
  14. What Is the Data Pump Export Utility?
  15. What Is the Data Pump Import Utility?
  16. How To Invoke the Data Pump Export Utility?
  17. How To Invoke the Data Pump Import Utility?
  18. What Are Data Pump Export and Import Modes?
  19. How To Estimate Disk Space Needed for an Export Job?
  20. How To Do a Full Database Export?
  21. Where Is the Export Dump File Located?
  22. How To Export Your Own Schema?
  23. How To Export Several Tables Together?
  24. What Happens If the Imported Table Already Exists?
  25. How To Import One Table Back from a Dump File?
  26. What Are the Original Export and Import Utilities?
  27. How To Invoke Original Export/Import Utilities?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...