Extracting schemas using Oracle Data Pump utilities v4

Suggest edits

Migration Portal requires only the metadata in the SQL dump file to assess the extracted schemas.

Prerequisites

  • If you plan on exporting schemas that aren't your own, make sure that you're assigned the DATAPUMP_IMP_FULL_DATABASE role. Otherwise, you can export only your own schema.

  • Make sure that you have enough tablespace quota to create objects in the tablespace.

  • In SQL*Plus, create a directory object that points to a directory on your server file system. For example:

    On Linux:

    create directory DMPDIR as '/sample/file/path';

    On Windows:

    create directory DMPDIR as 'c:\sample\file\path\';

  • In SQL*Plus, grant read-write permissions to the user running the Data Pump utility.

    grant read,write on directory DMPDIR to <Username>;

Perform either of the following procedures:

Extract one or more schemas in a database

  1. Before running the expdp command, create a file with a .par extension (for example, export.par) on your server. Add attributes and values to the file: 

    CONTENT=metadata_only
INCLUDE=SYNONYM
INCLUDE=SEQUENCE
INCLUDE=TYPE
INCLUDE=TABLE
INCLUDE=CONSTRAINT
INCLUDE=PROCEDURE
INCLUDE=FUNCTION
INCLUDE=PACKAGE
INCLUDE=INDEX
INCLUDE=MATERIALIZED_VIEW
INCLUDE=VIEW
INCLUDE=TRIGGER
INCLUDE=DB_LINK
INCLUDE=USER
INCLUDE=GRANT
INCLUDE=SYSTEM_GRANT

    The attributes and values in this command specify the following options:

    • CONTENT=metadata_only extracts only the metadata of the schemas.
    • INCLUDE= specifies the database object types to include in the extraction. Extract only database object types that are supported by Migration Portal, see Supported object types.

  1. From the command line, run the export command to generate a schemas_metadata.dump file. For example, to extract metadata information for <Schema_1>, <Schema_2>, <Schema_3>, and so on, run:

    expdp <Username>@<ConnectIdentifier> DIRECTORY=DMPDIR SCHEMAS=<Schema_1>,<Schema_2>,<Schema_3> DUMPFILE=schemas_metadata.dump parfile=export.par

  2. To generate a SQL file from the dump file, run the import command.

    For example, to generate a YourSchemas.SQL file from the schemas_metadata.dump file, enter:

    impdp <Username>@<ConnectIdentifier> DIRECTORY=DMPDIR TRANSFORM=OID:n,SEGMENT_ATTRIBUTES:n EXCLUDE=STATISTICS SQLFILE=YourSchemas.sql DUMPFILE=schemas_metadata.dump

See file encoding for information about the file encoding format expected by the Migration Portal.

Extract all schemas in a database

Note

Don't perform this procedure from a user account that belongs to the excluded schemas list (see Unsupported schemas). The impdp command fails if the user account running the command is in the excluded list of schemas.

  1. Before running the expdp command, create a file with a .par extension (for example, export.par) on your server. Add attributes and values to the file: 

    CONTENT=metadata_only
FULL=Y
INCLUDE=SYNONYM
INCLUDE=SEQUENCE
INCLUDE=TYPE
INCLUDE=TABLE
INCLUDE=CONSTRAINT
INCLUDE=PROCEDURE
INCLUDE=FUNCTION
INCLUDE=PACKAGE
INCLUDE=INDEX
INCLUDE=MATERIALIZED_VIEW
INCLUDE=VIEW
INCLUDE=TRIGGER
INCLUDE=DB_LINK	
INCLUDE=USER
INCLUDE=PROFILE
INCLUDE=GRANT
INCLUDE=ROLE
INCLUDE=ROLE_GRANT
INCLUDE=SYSTEM_GRANT

    The attributes and values in this command specify the following options:

    • CONTENT=metadata_only extracts only the metadata of the schemas.
    • INCLUDE= specifies the database object types to include in the extraction. Extract only database object types that are supported by Migration Portal, see Supported object types.
    • FULL=Y extracts all schemas in the database.

  2. From the command line, run the export command to generate a schemas_metadata.dump file:

    expdp <Username>@<ConnectIdentifier> DIRECTORY=DMPDIR DUMPFILE=schemas_metadata.dump parfile=export.par

  3. Before running the impdp command, create a parameter file with a .par extension (for example, import.par) on your server. Add attributes and values to the file:

    EXCLUDE=SCHEMA:"IN(select username as schema_name from sys.dba_users where LOWER(username) LIKE 'pg_%' OR username IN
('ANONYMOUS','APEX_PUBLIC_USER','APEX_030200','APEX_040000','APEX_040200','APPQOSSYS','AUDSYS','CTXSYS','DMSYS','DBSNMP',
'DBSFWUSER','DEMO','DIP','DMSYS','DVF','DVSYS','EXFSYS','FLOWS_FILES','FLOWS_020100', 'FRANCK','GGSYS','GSMADMIN_INTERNAL',
'GSMCATUSER','GSMROOTUSER','GSMUSER','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OJVMSYS','OLAPSYS','ORDPLUGINS','ORDSYS',
'ORDDATA','OUTLN','ORACLE_OCM','OWBSYS','OWBYSS_AUDIT','PDBADMIN','RMAN','REMOTE_SCHEDULER_AGENT','SI_INFORMTN_SCHEMA',
'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SQLTXADMIN','SQLTXPLAIN','SYS$UMF','SYS','SYSBACKUP','SYSDG','SYSKM',
'SYSRAC','SYSTEM','SYSMAN','TSMSYS','WKPROXY','WKSYS','WK_TEST','WMSYS','XDB','XS$NULL'))"
EXCLUDE=STATISTICS

    The attributes and values in this command specify the following options:

    • EXCLUDE specifies the schemas to exclude. Append a list of the Unsupported schemas to ensure that the Data Pump utility doesn't extract any system schemas (including schemas beginning with pg_).
    • EXCLUDE=STATISTICS specifies not to include statistics-related information in the SQL output file. By not including them in the SQL file, the size of the file is significantly reduced without affecting Migration Portal's ability to assess the DDL.

  4. To generate a SQL file from the dump file, run the import command.

    For example, to generate YourSchemas.SQL file from the schemas_metadata.dump file, enter:

    impdp <Username>@<ConnectIdentifier> DIRECTORY=DMPDIR TRANSFORM=OID:n,SEGMENT_ATTRIBUTES:n SQLFILE=YourSchemas.sql DUMPFILE=schemas_metadata.dump parfile=import.par

    See file encoding for information about the file encoding format expected by the Migration Portal.

← Prev

Performing a schema extraction

Next →

Performing a schema assessment