Practice #<number>: <topic title>

Practice: Export and Import Concepts and Structures.

In this practice you will use the parameters available for both export and import.

Instructions

1. Use the exp command and the help=y parameter to obtain a list of parameters available for export.

user45 ksh > exp help=y

Export: Release 9.0.1.1.1 - Production on Sun Mar 17 14:05:49 2002

You can let Export prompt you for parameters by entering the EXP command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed by various arguments. To specify parameters, you use keywords:

Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)

--------------------------------------------------------------------------

USERID username/password FULL export entire file (N)

BUFFER size of data buffer OWNER list of owner usernames

FILE output files (EXPDAT.DMP) TABLES list of table names

COMPRESS import into one extent (Y) RECORDLENGTH length of IO record

GRANTS export grants (Y) INCTYPE incremental export type

INDEXES export indexes (Y) RECORD track incr. export (Y)

DIRECT direct path (N) TRIGGERS export triggers (Y)

LOG log file of screen output STATISTICS analyze objects (ESTIMATE)

ROWS export data rows (Y) PARFILE parameter filename

CONSISTENT cross-table consistency CONSTRAINTS export constraints (Y)

FEEDBACK display progress every x rows (0)

FILESIZE maximum size of each dump file

FLASHBACK_SCN SCN used to set session snapshot back to

FLASHBACK_TIME time used to get the SCN closest to the specified time

QUERY select clause used to export a subset of a table

RESUMABLE suspend when a space related error is encountered(N)

RESUMABLE_NAME text string used to identify resumable statement

RESUMABLE_TIMEOUT wait time for RESUMABLE

TTS_FULL_CHECK perform full or partial dependency check for TTS

TABLESPACES list of tablespaces to export

TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

TEMPLATE template name which invokes iAS mode export

Export terminated successfully without warnings.

2. Find Use the imp command the help=y parameter to obtain a list of parameters available for import.

user45 ksh > imp help=y

Import: Release 9.0.1.1.1 - Production on Sun Mar 17 14:02:12 2002

You can let Import prompt you for parameters by entering the IMP

command followed by your username/password:

Example: IMP SCOTT/TIGER

Or, you can control how Import runs by entering the IMP command followed

by various arguments. To specify parameters, you use keywords:

Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)

--------------------------------------------------------------------------

USERID username/password FULL import entire file (N)

BUFFER size of data buffer FROMUSER list of owner usernames

FILE input files (EXPDAT.DMP) TOUSER list of usernames

SHOW just list file contents (N) TABLES list of table names

IGNORE ignore create errors (N) RECORDLENGTH length of IO record

GRANTS import grants (Y) INCTYPE incremental import type

INDEXES import indexes (Y) COMMIT commit array insert (N)

ROWS import data rows (Y) PARFILE parameter filename

LOG log file of screen output CONSTRAINTS import constraints (Y)

DESTROY overwrite tablespace data file (N)

INDEXFILE write table/index info to specified file

SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N)

FEEDBACK display progress every x rows(0)

TOID_NOVALIDATE skip validation of specified type ids

FILESIZE maximum size of each dump file

STATISTICS import precomputed statistics (always)

RESUMABLE suspend when a space related error is encountered(N)

RESUMABLE_NAME text string used to identify resumable statement

RESUMABLE_TIMEOUT wait time for RESUMABLE

COMPILE compile procedures, packages, and functions (Y)

The following keywords only apply to transportable tablespaces

TRANSPORT_TABLESPACE import transportable tablespace metadata (N)

TABLESPACES tablespaces to be transported into database

DATAFILES datafiles to be transported into database

TTS_OWNERS users that own data in the transportable tablespace set

Import terminated successfully without warnings.

This ends the practice.

Practice: Export and Import operations.

This practice covers using the import and export utilities.

Instructions:

1. Invoke the Export utility to export the EMPLOYEES and DEPARTMENTS tables in the HR schema.

user45 ksh > exp hr/hr file=$HOME/BACKUP/export.dmp tables=employees,department

Export: Release 9.0.1.0.0 - Production on Wed Mar 13 19:00:23 2002

Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production

With the Partitioning option

JServer Release 9.0.1.0.0 - Production

Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table EMPLOYEES 107 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table DEPARTMENTS 27 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

user45 ksh >

2. Connect as HR and drop the EMPLOYEES and DEPARTMENTS tables.

SQL> connect hr/hr

Connected.

SQL> drop table employees cascade constraints;

Table dropped.

SQL> drop table departments cascade constraints;

Table dropped.

SQL>

3. Restore the EMPLOYEES and DEPARTMENTS tables by using the import utility.

user45 ksh > imp hr/hr file=$HOME/BACKUP/export.dmp TABLES=employees,departments

Import: Release 9.0.1.0.0 - Production on Wed Mar 13 19:07:48 2002

Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production

With the Partitioning option

JServer Release 9.0.1.0.0 - Production

Export file created by EXPORT:V09.00.01 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set (possible charset conversion)

. importing HR's objects into HR

. . importing table "EMPLOYEES" 107 rows imported

. . importing table "DEPARTMENTS" 27 rows imported

About to enable constraints...

Import terminated successfully without warnings.

user45 ksh >

4. Query the EMPLOYEES and DEPARTMENTS tables to determine the number of rows in each of those tables.

user45 ksh > sqlplus hr/hr

SQL> select count(*) from employees;

COUNT(*)

----------

107

SQL> select count(*) from departments;

COUNT(*)

----------

SQL>

This ends this practice.