Basic Oracle Concepts and Programming Question:

Download Job Interview Questions and Answers PDF

How To Export Your Own Schema?

Oracle Database Interview Question
Oracle Database Interview Question

Answers:

Answer #1
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 oraclexehr_dump

>cd oraclexeapporacleproduct10.2.0serverBIN

>sqlplus /nolog
SQL> connect SYSTEM/globalguideline

SQL> CREATE DIRECTORY hr_dump AS 'oraclexehr_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/globalguideline SCHEMAS=hr
DIRECTORY=hr_dump DUMPFILE=schema.dmp LOGFILE=schema.log


Answer #2
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
<pre>......
. . 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
......</pre>
Master table "HR"."SYS_EXPORT_SCHEMA_01" loaded/unloaded
*********************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
C:ORACLEXEHR_DUMPSCHEMA.DMP
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed


Download Oracle Database Interview Questions And Answers PDF

Previous QuestionNext Question
Where Is the Export Dump File Located?How To Export Several Tables Together?