Oracle Interview Preparation Notes Part 11

Export – makes a logical copy of object definitions and data to a binary file (Logical backup).

Conventional (DIRECT=N) uses SQL select to extract data into buffer cache, then into evaluation buffer

Direct Path (DIRECT=Y) Data is read from disk into buffer cache – data in blocks is not reorganized.

COMPRESS=Y – initial extent resized as total current segment size

FEEDBACK=x – A dot is displayed in log file for every x records inserted.

FULL=Y or OWNER=user or TABLES=schema.table

CONSISTENT=Y – entire export as 1 read only transaction.

RECORD=Y – update the information in the SYS tables (INCxxx).

* INCTYPE=

COMPLETE – Export all objects and data, reinitialize dictionary views.

INCREMENTAL – All tables that have changed since last incremental, complete, or cumulative.

CUMULATIVE – All tables that have changed since last complete or cumulative.

SYS.INCFIL – Tracks all exports in current cycle.

SYS.INCEXP – Tracks which objects are in which export files.

SYS.INCVID – Tracks the last export information.

· Clusters are not exported with the Table mode, they are in User or Full mode.

· SYS owner objects (data dictionary) are never exported.

Import – Reads export files and copies object definitions and data into an Oracle database.

IGNORE=Y – Overlook object creation errors – causes rows to be imported into existing tables.

INDEXFILE=file – Index creation commands written to file (no actual import).

Order of operations: Type defs | Data | B-Tree indexes | Views, procedures, constraints | bitmap indexes.

Before import: Disable Ref Integrity before imports and increase buffer size for better performance

After import: Compile all invalid objects, reenable RI.