Page 309
Type of Object | with FULL=Y option | with OWNER option |
with TABLE option |
Refresh groups and children | All objects | Just for owner | |
User history table |
All objects | ||
Default and system auditing options | All objects |
CAUTION |
ROWIDs are re-assigned during an import. If a table has a column with ROWID information, the data will become obsolete. Also, ROWID snapshots become obsolete after an import and must be refreshed with the COMPLETE option before they can be automatically refreshed again. In addition, some REF attributes may contain ROWID information, so the REFs may become obsolete during an Import session. To re-create the REF information with proper ROWIDs after importing the data, issue the ANALYZE TABLE owner.table_name VALIDATE REF UPDATE; command. |
The Import and Export utilitieswith the availability of dozens of parameters that can be passedare extremely flexible. Also, there are two methods of running Import and Export: Interactive mode and Non-interactive mode. With the Interactive mode, you are stepped through a series of prompts to enter the basic information for an import and export session. This method is less flexible; you are prompted for only a few of the dozens of available parameters.
To export with the Interactive mode, just enter exp80 at the command line. You are then prompted for a username and password.
CAUTION |
To export the entire database, the user account that you export with must have been granted the EXP_FULL_DATABASE system privilege. For accounts with DBA privileges, such as SYSTEM, this privilege is implicitly granted to the user. Otherwise, the export will fail. |
Next, you receive a prompt for the array fetch buffer size. This is the size of the memory buffer through which rows are exported. This should be larger than the size of the largest record multiplied by the number of rows that you wish to fit within the buffer, and is operating-system dependent.
Page 310
You then receive a prompt for the name of the export file. By default, it is expdat.dmp. Next, a small menu with three options appears: (1)E(ntire database), (2)U(sers), or (3)T(ables).
If you select option 1, you receive a prompt to enter values for grants (Y/N), table data (Y/N), and compress extents (Y/N).
If you select option 2, you receive a prompt to enter values for grants (Y/N), table data (Y/N), compress extents (Y/N), and user(s) to be exported (keep entering until done, and then enter a single period on the line to finish).
If you select option 3, you receive a prompt to enter values for export table data (Y/N), compress extents (Y/N), and Table (T) or Partition (T:P) to be exported (keep entering until done, and then enter a single period on the line to finish).
To export in Non-interactive mode, you can either pass all parameters at the command line or through a parameter file. For all possible parameters of the export command, type exp80 help=y in Windows NT, as shown in Figure 13.1.
FIG. 13.1
Sample result of exp80
help=y, in a Windows NT
environment.
You can use 23 parameters during an export session. You can either specify them in the command line or any parameter file that is specified. Table 13.2 describes all the export parameters.
Table 13.2Description of Parameters for the Export Utility
Parameter | Default Value | Description | BUFFER |
OS-Dependent | The size of BUFFER (in bytes) determines the memory buffer through which rows are exported. This should be larger than the size of the largest record multiplied by the number of rows that you wish to fit within the buffer. |
Page 311
Parameter | Default Value | Description |
COMPRESS | Y | If COMPRESS=Y, the INITIAL storage parameter will be set to the total size of all extents allocated for the object. The change takes effect only when the object is imported. |
CONSISTENT | N | Setting CONSISTENT=Y exports all tables and references in a consistent state. This slows the export because rollback space is used. If CONSISTENT=N, which is the default, and a record is modified during the export, the data becomes inconsistent. |
CONSTRAINTS | N | Specifies whether table constraints are exported. |
DIRECT | N | If DIRECT=Y, Oracle bypasses the SQL command processing layer, improving the speed of the export. Unfortunately, the new object types endemic to Oracle8, such as LOBs, will not get exported. |
FEEDBACK | 0 | Oracle displays a period for each group of records inserted. The size of the group is defined by FEEDBACK. By setting FEEDBACK=1000, for example, a period displays for every 1000 records imported. This parameter is useful for tracking the progress of large imports. |
FILE | expdat.dmp | By default, expdat.dmp (stands for EXPort DATa.DuMP) will be the name of the file. For a more meaningful file name, change the FILE parameter. |
FULL | N | The entire database will be exported if FULL=Y, including tablespace definitions. |
GRANTS | Y | Specifies whether all grant definitions will be exported for the objects being exported. |
HELP | N | No other parameters are needed if you specify HELP=Y. A basic help screen is displayed. |
continues
Page 312
Table 13.2Continued
Parameter | Default Value | Description |
INCTYPE |
The valid options for the INCTYPE parameter are
COMPLETE, CUMULATIVE, and INCREMENTAL. A COMPLETE
export lays down a full export for which the other two options rely on for
restores of the database. CUMULATIVE exports all tables and other objects that
have changed since the last CUMULATIVE or COMPLETE export was taken. If
one record in a table has been altered, the entire table is exported.
INCREMENTAL exports all tables and objects that have changed since the last INCREMENTAL, CUMULATIVE, or COMPLETE export. | |
INDEXES | Y | Specifies whether user-defined indexes are exported. System indexes created with constraints (primary key, unique key) and OID indexes are automatically exported, regardless of the value of the INDEXES parameter. |
LOG | The LOG parameter specifies the name of the file to spool the feedback from the export session. Unless otherwise specified, Oracle appends a .LOG extension to the file. | |
PARFILE | Instead of entering all parameters on the command line, some or all may be kept in a parameter file. The PARFILE parameter specifies which file to use, if desired. This parameter is especially useful for non-interactive import sessions. | |
POINT_IN_TIME_RECOVER | N |
Exports information for a
Point-in-time Recovery for the tablespace listed with the
TABLESPACES parameter. |