Export Options for Tables and Indexes with Data (SAP Library - Database Administration (Oracle) with SAPDBA)

Export Options for Tables and Indexes with Data

Procedure

1. Start the database system in Restricted Session mode (recommended), if this has not been already done. The R/3 System must be shut down.

2. Select the SAPDBA export option for a table with its data (Export tables and indexes including data ).

3. The following menu options are displayed (see SAPDBA: Export Menu):

4. Start the export. SAPDBA starts the ORACLE Export program.

Preliminary Result

The necessary scripts and the restart file restart.exd are created.

A log <timestamp>.exd is created.

The table definitions (and comments), the definitions of the table indexes, all constraints and grants, and the data are exported with Oracle Export or SAP unload.

If an error occurs, you can repeat the data export with the restart function (see Restarting the Export/Import).

5. If necessary, you can import the table(s) and their corresponding objects again - use the import function of SAPDBA Import tables and indexes including data to start the import scripts. You can also start structure imports (SQL scripts) singly (SAPDBA function Additional functions ® Execute SQL script).

Preliminary Result

Objects that exist in the database are not overwritten by an import. However, rows may be doubled if they already existed prior to the import and no UNIQUE index existed. Before you make the import you must make sure that the objects that are to be imported do not already exist, or that the tables are empty.

See Data Consistency and Importing Tables with Indexes and Data.

When you use SAP unload, SQL scripts are always created for indexes and grants, since in contrast to an Oracle Export, the SAP unload only exports the tables with their data.

See SAP unload/load, SQL*Loader.

Script Categories

During an export of a table with indexes and data, the following scripts and/or parameter files are used (if required) or are created for the later import or restart:

alt<TSP>.sql

exp<TSP>.sh ( exc<TSP>.sh if necessary) or unl<TSP>.sh

exp<TSP>.par (exc<TSP>.par if necessary) or ldr<TSP>.par

tab<TSP>.sql

imp<TSP>.sh (imc<TSP>.sh, imx<TSP>.sh if necessary) or ldr<TSP>.sh

ind<TSP>.sql (inx<TSP>.sql if necessary)

con<TSP>.sql

restart.exd (valid both for the export and for the possible import)

There are certain circumstances in which multiple scripts of the type *.sh and *.par are generated (see SAPDBA Scripts and Logs). The script alt<TSP>.sql is processed before tables with data are exported. It contains ALTER statements for those indexes that belong to a primary key. However, this applies only if one of the index parameters NEXT, PCTINCREASE, MAXEXTENTS, PCTFREE or FREELISTS has been changed (automatically or manually in the menu). For these indexes MINEXTENTS cannot be changed and INITIAL can only be changed using the option Compress extents yes.

When using Oracle Export the script ind<TSP>.sql is generated only if the index is exported without its table. When using SAP unload ind<TSP>.sql is always generated.

Grants are exported or imported with the Oracle programs export/import. Comments are always included in the script tab<TSP>.sql. SAPDBA creates the separate scripts grants.sql for grants when using the tools SAP unload/load, SQL*loader.

See Restart File for Export/Import and SAPDBA Scripts and Files.