“Learn Oracle database programming with hundreds of Interview Questions and Answers and examples and get a better job as an Oracle DBA with this basic and advance Oracle Database Interview Questions and Answers guide”
An external table is a table defined in the database with data stored outside the database. Data of an external table is stored in files on the operating systems. Accessing data of external tables are done through data access drivers. Currently, Oracle supports two data access drivers: ORACLE_LOADER and ORACLE_DATAPUMP.
External tables can be used to load data from external files into database, or unload data from database to external files.
If you have data stored in external files, you can load it to database through an external table by the steps below:
► Create an external table with columns matching data fields in the external file.
► Create a regular table with the same columns.
► Run an INSERT INTO ... SELECT statement to load data from the external file to the regular table through the external table.
When creating external table columns, you need to watch out some restrictions:
► "PRIMARY KEY" is not allowed.
► "NOT NULL" is not allowed.
► "DEFAULT value" is not allowed.
A directory object is a logical alias for a physical directory path name on the operating system. Directory objects can be created, dropped, and granted access permissions to different users. The following tutorial exercise shows you some good examples:
>sqlplus /nolog
SQL> connect SYSTEM/globalguideline
SQL> CREATE DIRECTORY test_dir AS '/oraclexe/test';
Directory created.
SQL> GRANT READ ON DIRECTORY test_dir TO hr;
Grant succeeded.
SQL> GRANT WRITE ON DIRECTORY test_dir TO hr;
Grant succeeded.
SQL> CREATE DIRECTORY temp_dir AS '/oraclexe/temp';
Directory created.
SQL> DROP DIRECTORY temp_dir;
Directory dropped.
You can use the CREATE TABLE statement to create external tables. But you need to use ORGANIZATION EXTERNAL clause to specify the external file location and the data access driver. The tutorial exercise below shows you how to define an external table as a text file:
>sqlplus /nolog
SQL> connect HR/globalguideline
SQL> CREATE TABLE ext_ggl_links (
id NUMBER(4),
url VARCHAR2(16),
notes VARCHAR2(16),
counts NUMBER(4),
created DATE
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY test_dir
LOCATION ('ext_ggl_links.txt')
);
Table created.
SQL> SELECT table_name, tablespace_name, num_rows
FROM USER_TABLES;
TABLE_NAME TABLESPACE_NAME NUM_ROWS--------------------- ---------------------- ----------
REGIONS USERS 4
LOCATIONS USERS 23
DEPARTMENTS USERS 27
JOBS USERS 19
EMPLOYEES USERS 107
JOB_HISTORY USERS 10
ggl_LINKS USERS 2
EXT_ggl_LINKS
COUNTRIES 25
Once you have your data entered in a text file, and an external table defined to this text file, you can easily load data from this text file to a regular table. The following tutorial exercise shows you how to load data from the text file and the external table defined in the previous exercises to a regular table:
SQL> CREATE TABLE ggl_links (
id NUMBER(4) PRIMARY KEY,
url VARCHAR2(16) NOT NULL,
notes VARCHAR2(16),
counts NUMBER(4),
created DATE DEFAULT (sysdate)
);
SQL> INSERT INTO ggl_links SELECT * FROM ext_ggl_links;
2 rows created.
SQL> SELECT * FROM ggl_links;
ID URL NOTES COUNTS CREATED--------- ---------------- ----------- -------- ---------
101 rendc.org Session 1 07-MAY-06
110 centerggl.com Session 1 07-MAY-06
1101 www.globalguideline Link #1 88 07-MAY-06
1110 www.globalguideline Link #2 88 07-MAY-06
Oracle Data Pump Export utility is a standalone programs that allows you to export data objects from Oracle database to operating system files called dump file set, which can be imported back to Oracle database only by Oracle Data Pump Import utility.
The dump file set can be imported on the same system or it can be moved to another system and loaded there.
The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set. Because the dump files are written by the server, rather than by the client, the data base administrator (DBA) must create directory objects.
If you have an external table defined as a text file with the ORACLE_LOADER driver, you can add data to the text file, and query the text file through the external table. By default, data fields in the text file should be terminated by ','. The tutorial exercise below shows you how add data to the external table defined in the previous exercise:
>edit /oraclexe/test/ext_ggl_links.txt
1101,www.globalguideline,Link #1,88,07-MAY-06
1110,www.globalguideline,Link #2,88,07-MAY-06
>sqlplus /nolog
SQL> connect HR/globalguideline
SQL> SELECT * FROM ext_ggl_links;
ID URL NOTES COUNTS CREATED--------- ---------------- ----------- -------- ---------
1101 www.globalguideline Link #1 88 07-MAY-06
1110 www.globalguideline Link #2 88 07-MAY-06
Oracle Data Pump Import utility is a standalone programs that allows you to import data objects from an Oracle dump file set into Oracle database. Oracle dump file set is written in a proprietary binary format by the Data Pump Export utility.
Import can also be used to load a target database directly from a source database with no intervening dump files. This allows export and import operations to run concurrently, minimizing total elapsed time. This is known as a network import. Data Pump Import enables you to specify whether a job should move a subset of the data and metadata from the dump file set or the source database (in the case of a network import), as determined by the import mode. This is done using data filters and metadata filters, which are implemented through Import commands.
The Data Pump Export utility is distributed as executable file called "expdp.exe". To invoke this utility, you should go to the "bin" directory of your Oracle server installation and run the "expdp" command. Here is tutorial exercise on how to invoke the export utility:
>cd oraclexeapporacleproduct10.2.0serverBIN
>expdp help=y
Export: Release 10.2.0.1.0 -
The Data Pump export utility provides a mechanism for
transferring data objects between Oracle databases. The
utility is invoked with the following command:
Example: expdp scott/tiger DIRECTORY=dmpdir
DUMPFILE=scott.dmp
You can control how Export runs by entering the 'expdp'
command followed by various parameters:
Format: expdp KEYWORD=value or
KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp
DIRECTORY=dmpdir
SCHEMAS=scott or TABLES=(T1:P1,T1:P2)
The Data Pump Import utility is distributed as executable file called "impdp.exe". To invoke this utility, you should go to the "bin" directory of your Oracle server installation and run the "impdp" command. Here is tutorial exercise on how to invoke the import utility:
>cd oraclexeapporacleproduct10.2.0serverBIN
>impdp help=y
Import: Release 10.2.0.1.0 -
The Data Pump Import utility provides a mechanism for
transferring data objects between Oracle databases. The
utility is invoked with the following command:
Example: impdp scott/tiger DIRECTORY=dmpdir
DUMPFILE=scott.dmp
You can control how Import runs by entering the 'impdp'
command followed by various parameters.
Format: impdp KEYWORD=value or
KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir
DUMPFILE=scott.dmp
Data pump export and import modes are used to determine the type and portions of database to be exported and imported. Oracle 10g supports 5 export and import modes:
► Full: Exports and imports a full database. Use the FULL parameter to specify this mode.
► Schema: Enables you to export and import all objects that belong to a schema. Use the SCHEMAS parameter to specify this mode. This is the default mode.
► Table: Enables you to export and import specific tables and partitions. Use the TABLES parameter to specify this mode.
► Tablespace: Enables a privileged user to move a set of tablespaces from one Oracle database to another. Use the TABLESPACES parameter to specify this mode.
► Tablespace: Enables a privileged user to move metadata from the tables within a specified set of tablespaces from one Oracle database to another. Use the TRANSPORT_TABLESPACES parameter to specify this mode.
If you just want to know how much disk space for the dump without actually exporting any data, you can use the ESTIMATE_ONLY=y parameter on the expdp command. The following tutorial exercise shows you how a system user wants to see the disk space estimates on a full database export:
>cd oraclexeapporacleproduct10.2.0serverBIN
>expdp SYSTEM/globalguideline FULL=y ESTIMATE_ONLY=y
Starting "SYSTEM"."SYS_EXPORT_FULL_01": SYSTEM/**** FULL=y
ESTIMATE_ONLY=y
Estimate in progress using BLOCKS method...Processing object DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. estimated "FLOWS_020100"."WWV_FLOW_PAGE_PLUGS" 42 MB
. estimated "FLOWS_020100"."WWV_FLOW_STEP_ITEMS" 27 MB
. estimated "FLOWS_020100"."WWV_FLOW_STEP_PROCESSING" 16 MB
......
. estimated "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB
. estimated "TSMSYS"."SRS$" 0 KB
Total estimation using BLOCKS method: 169.8 MB
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed
Now you know that you need 170 MB disk space to export the entire data base.
Oracle also records the screen output in a log file called export.log at oraclexeapporacleadminXEdpdump.
If you are ready to do a full database export, you can use the FULL=y parameter on the expdp command, as shown in the following tutorial exercise:
>expdp SYSTEM/globalguideline FULL=y ESTIMATE_ONLY=y
Starting "SYSTEM"."SYS_EXPORT_FULL_01": SYSTEM/**** FULL=y
Estimate in progress using BLOCKS method...
Processing object DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 169.8 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
......
. . exported FLOWS_020100.WWV_FLOW_PAGE_PLUGS 32.51MB
. . exported FLOWS_020100.WWV_FLOW_STEP_ITEMS 21.68MB
. . exported FLOWS_020100.WWV_FLOW_STEP_PROCESSING 11.17MB
......
Master table "SYSTEM"."SYS_EXPORT_FULL_01" unloaded
**********************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
C:ORACLEXEAPPORACLEADMINXEDPDUMPEXPDAT.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed
If you are not specifying the dump directory and file name, the dump file will be stored in the default dump directory with the default file name. The tutorial exercise below tells you find what is your default dump directory and locate the dump file.
>sqlplus /nolog
SQL> connect SYSTEM/globalguideline
SQL> COL owner FORMAT A8;
SQL> COL directory_name FORMAT A16;
SQL> COL directory_path FORMAT A40;
SQL> SELECT * FROM dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH----- -------------- -------------------------------------
SYS DATA_PUMP_DIR oraclexeapporacleadminXEdpdump
SYS TEST_DIR /oraclexe/test
SYS ORACLECLRDIR oraclexeapporacleproduct10.2.0
serverinclr
Obviously, the default dump directory is directory object defined to oraclexeapporacleadminXEdpdump. If you go to that directory, you will find the full database dump file is called "expdat.dmp".
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
If you don't want to export the entire schema and only want to export several tables only, you can use the "expdp" command with the "TABLES" parameter as shown in the following tutorial exercise:
>cd oraclexeapporacleproduct10.2.0serverBIN
>expdp hr/globalguideline TABLES=employees,departments
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********
TABLES=employees,departments DIRECTORY=hr_dump
DUMPFILE=tables.dmp
LOGFILE=tables.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CON...
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTI...
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF...
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TAB...
. . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.76 KB 107 rows
Master table "HR".
If the import process tries to import a table that already exists, the Data Pump Import utility will return an error and skip this table. The following exercise shows you a good example:
>cd oraclexeapporacleproduct10.2.0serverBIN
>impdp hr/globalguideline TABLES=employees
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log
Master table "HR"."SYS_IMPORT_TABLE_01" loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": hr/** TABLES=employees
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."EMPLOYEES" exists. All dependent
metadata and data will be skipped due to table_exists_action
of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
......
If you only want to import one table back to the database, you can use a dump file that was created by full export, schema export or a table export. The following tutorial exercise shows you how to import the "ggl_links" table from a dump file created by a schema export:
>cd oraclexeapporacleproduct10.2.0serverBIN
>sqlplus /nolog
SQL> connect HR/globalguideline
SQL> DROP TABLE ggl_links;
Table dropped.
SQL> exit;
>impdp hr/globalguideline TABLES=ggl_links DIRECTORY=hr_dump
DUMPFILE=schema.dmp LOGFILE=tables.log
Master table "HR"."SYS_IMPORT_TABLE_01" loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": hr/** TABLES=ggl_links
DIRECTORY=hr_dump DUMPFILE=schema.dmp LOGFILE=tables.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."ggl_LINKS" 6.375 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CON...
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTI...
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TAB...
Job "HR"."SYS_IMPORT_TABLE_01" successfully completed.
Oracle original Export and Import utilities are standalone programs that provide you a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.
When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file.
An export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on systems not connected through a network. The files can also be used as backups in addition to normal backup procedures.
Export and Import utilities are now being replaced by Data Pump Export and Import utilities in Oracle 10g. But you can still use them.
rendc.org
Whether it is technological solutions or user-oriented innovations, We have put together for you the best sources of HR Interview Questions and Answers, Tech Ideas, and Programming Tutorials."
Get all the latest information on rendc.org
© 2022 | All rights reserved.
RENDC
welcome to www.rendc.org
