TRANSFORM

Default: There is no default

Purpose

Enables you to alter object creation DDL for objects being imported.

Syntax and Description

TRANSFORM = transform_name:value[:object_type]

The transform_name specifies the name of the transform. The possible options are as follows, in alphabetical order:

  • DISABLE_ARCHIVE_LOGGING:[Y | N]

    If set to Y, then the logging attributes for the specified object types (TABLE and/or INDEX) are disabled before the data is imported. If set to N (the default), then archive logging is not disabled during import. After the data has been loaded, the logging attributes for the objects are restored to their original settings. If no object type is specified, then the DISABLE_ARCHIVE_LOGGING behavior is applied to both TABLE and INDEX object types. This transform works for both file mode imports and network mode imports. It does not apply to transportable tablespace imports.

    Note:

    If the database is in FORCE LOGGING mode, then the DISABLE_ARCHIVE_LOGGING option will not disable logging when indexes and tables are created.

  • INMEMORY:[Y | N]

    The INMEMORY transform is related to the In-Memory Column Store (IM column store). The IM column store is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects. In the IM column store, data is populated by column rather than row as it is in other parts of the SGA, and data is optimized for rapid scans. The IM column store does not replace the buffer cache, but acts as a supplement so that both memory areas can store the same data in different formats. The IM column store is included with the Oracle Database In-Memory option.

    If Y (the default value) is specified on import, then Data Pump keeps the IM column store clause for all objects that have one. When those objects are recreated at import time, Data Pump generates the IM column store clause that matches the setting for those objects at export time.

    If N is specified on import, then Data Pump drops the IM column store clause from all objects that have one. If there is no IM column store clause for an object that is stored in a tablespace, then the object inherits the IM column store clause from the tablespace. So if you are migrating a database and want the new database to use IM column store features, you could pre-create the tablespaces with the appropriate IM column store clause and then use TRANSFORM=INMEMORY:N on the import command. The object would then inherit the IM column store clause from the new pre-created tablespace.

    If you do not use the INMEMORY transform, then you must individually alter every object to add the appropriate IM column store clause.

    Note:

    The INMEMORY transform is available only in Oracle Database 12c Release 1 (12.1.0.2) or later.

    See Also:

  • INMEMORY_CLAUSE:"string with a valid in-memory parameter"

    The INMEMORY_CLAUSE transform is related to the In-Memory Column Store (IM column store). The IM column store is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects. In the IM column store, data is populated by column rather than row as it is in other parts of the SGA, and data is optimized for rapid scans. The IM column store does not replace the buffer cache, but acts as a supplement so that both memory areas can store the same data in different formats. The IM column store is included with the Oracle Database In-Memory option.

    When you specify this transform, Data Pump uses the contents of the string as the INMEMORY_CLAUSE for all objects being imported that have an IM column store clause in their DDL. This transform is useful when you want to override the IM column store clause for an object in the dump file.

    Note:

    The INMEMORY_CLAUSE transform is available only in Oracle Database 12c Release 1 (12.1.0.2) or later.

    See Also:

  • LOB_STORAGE:[SECUREFILE | BASICFILE | DEFAULT | NO_CHANGE]

    LOB segments are created with the specified storage, either SECUREFILE or BASICFILE. If the value is NO_CHANGE (the default), the LOB segments are created with the same storage they had in the source database. If the value is DEFAULT, then the keyword (SECUREFILE or BASICFILE) is omitted and the LOB segment is created with the default storage.

    Specifying this transform changes LOB storage for all tables in the job, including tables that provide storage for materialized views.

    The LOB_STORAGE transform is not valid in transportable import jobs.

  • OID:[Y | N]

    If Y (the default value) is specified on import, then the exported OIDs are assigned to new object tables and types. Data Pump also performs OID checking when looking for an existing matching type on the target database.

    If N is specified on import, then:

    • The assignment of the exported OID during the creation of new object tables and types is inhibited. Instead, a new OID is assigned. This can be useful for cloning schemas, but does not affect referenced objects.

    • Prior to loading data for a table associated with a type, Data Pump skips normal type OID checking when looking for an existing matching type on the target database. Other checks using a type's hash code, version number, and type name are still performed.

  • PCTSPACE:some_number_greater_than_zero

    The value supplied for this transform must be a number greater than zero. It represents the percentage multiplier used to alter extent allocations and the size of data files.

    Note that you can use the PCTSPACE transform with the Data Pump Export SAMPLE parameter so that the size of storage allocations matches the sampled data subset. (See "SAMPLE".)

  • SEGMENT_ATTRIBUTES:[Y | N]

    If the value is specified as Y, then segment attributes (physical attributes, storage attributes, tablespaces, and logging) are included, with appropriate DDL. The default is Y.

  • SEGMENT_CREATION:[Y | N]

    If set to Y (the default), then this transform causes the SQL SEGMENT CREATION clause to be added to the CREATE TABLE statement. That is, the CREATE TABLE statement will explicitly say either SEGMENT CREATION DEFERRED or SEGMENT CREATION IMMEDIATE. If the value is N, then the SEGMENT CREATION clause is omitted from the CREATE TABLE statement. Set this parameter to N to use the default segment creation attributes for the table(s) being loaded. (This functionality is available starting with Oracle Database 11g release 2 (11.2.0.2).)

  • STORAGE:[Y | N]

    If the value is specified as Y, then the storage clauses are included, with appropriate DDL. The default is Y. This parameter is ignored if SEGMENT_ATTRIBUTES=N.

  • TABLE_COMPRESSION_CLAUSE:[NONE | compression_clause]

    If NONE is specified, the table compression clause is omitted (and the table gets the default compression for the tablespace). Otherwise the value is a valid table compression clause (for example, NOCOMPRESS, COMPRESS BASIC, and so on). Tables are created with the specified compression. See Oracle Database SQL Language Reference for information about valid table compression syntax.

    If the table compression clause is more than one word, it must be contained in single or double quotation marks.

    Specifying this transform changes the type of compression for all tables in the job, including tables that provide storage for materialized views.

Specifying an object_type is optional. If supplied, it designates the object type to which the transform will be applied. If no object type is specified, then the transform applies to all valid object types. Table 3-1 indicates which object types are valid for each transform.

Table 3-1 Valid Object Types for the Data Pump Import TRANSFORM Parameter

- CLUSTER CONSTRAINT INC_TYPE INDEX ROLLBACK_SEGMENT TABLE TABLESPACE TYPE

DISABLE_ARCHIVE_LOGGING

No

No

No

Yes

No

Yes

No

No

INMEMORY_

No

No

No

No

No

Yes

Yes

No

INMEMORY_CLAUSE

No

No

No

No

No

Yes

Yes

No

LOB_STORAGE

No

No

No

No

No

Yes

No

No

OID

No

No

Yes

No

No

Yes

No

Yes

PCTSPACE

Yes

Yes

No

Yes

Yes

Yes

Yes

No

SEGMENT_ATTRIBUTES

Yes

Yes

No

Yes

Yes

Yes

Yes

No

SEGMENT_CREATION

No

No

No

No

No

Yes

No

No

STORAGE

Yes

Yes

No

Yes

Yes

Yes

No

No

TABLE_COMPRESSION_CLAUSE

No

No

No

No

No

Yes

No

No

Example

For the following example, assume that you have exported the employees table in the hr schema. The SQL CREATE TABLE statement that results when you then import the table is similar to the following:

CREATE TABLE "HR"."EMPLOYEES" 
   ( "EMPLOYEE_ID" NUMBER(6,0), 
     "FIRST_NAME" VARCHAR2(20), 
     "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, 
     "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, 
     "PHONE_NUMBER" VARCHAR2(20), 
     "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, 
     "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, 
     "SALARY" NUMBER(8,2), 
     "COMMISSION_PCT" NUMBER(2,2), 
     "MANAGER_ID" NUMBER(6,0), 
     "DEPARTMENT_ID" NUMBER(4,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 10240 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 121
  PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM" ;

If you do not want to retain the STORAGE clause or TABLESPACE clause, then you can remove them from the CREATE STATEMENT by using the Import TRANSFORM parameter. Specify the value of SEGMENT_ATTRIBUTES as N. This results in the exclusion of segment attributes (both storage and tablespace) from the table.

> impdp hr TABLES=hr.employees DIRECTORY=dpump_dir1 DUMPFILE=hr_emp.dmp
  TRANSFORM=SEGMENT_ATTRIBUTES:N:table

The resulting CREATE TABLE statement for the employees table would then look similar to the following. It does not contain a STORAGE or TABLESPACE clause; the attributes for the default tablespace for the HR schema will be used instead.

CREATE TABLE "HR"."EMPLOYEES" 
   ( "EMPLOYEE_ID" NUMBER(6,0), 
     "FIRST_NAME" VARCHAR2(20), 
     "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, 
     "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, 
     "PHONE_NUMBER" VARCHAR2(20), 
     "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, 
     "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, 
     "SALARY" NUMBER(8,2), 
     "COMMISSION_PCT" NUMBER(2,2), 
     "MANAGER_ID" NUMBER(6,0), 
     "DEPARTMENT_ID" NUMBER(4,0)
   );

As shown in the previous example, the SEGMENT_ATTRIBUTES transform applies to both storage and tablespace attributes. To omit only the STORAGE clause and retain the TABLESPACE clause, you can use the STORAGE transform, as follows:

> impdp hr TABLES=hr.employees DIRECTORY=dpump_dir1 DUMPFILE=hr_emp.dmp
  TRANSFORM=STORAGE:N:table

The SEGMENT_ATTRIBUTES and STORAGE transforms can be applied to all applicable table and index objects by not specifying the object type on the TRANSFORM parameter, as shown in the following command:

> impdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=SEGMENT_ATTRIBUTES:N