Extracting schemas using Oracle Data Pump utilities v4
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
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.
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
To generate a SQL file from the dump file, run the import command.
For example, to generate a
YourSchemas.SQL
file from theschemas_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.
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.
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
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 withpg_
).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.
To generate a SQL file from the dump file, run the import command.
For example, to generate
YourSchemas.SQL
file from theschemas_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.