Schema Export

Oracle schema to export

The Oracle database export can be limited to a specific Schema or Namespace; this may be mandatory depending on the database connection user.

• SCHEMA

  This directive is used to set the schema name to use during export. For example:

SCHEMA  APPS

will extract objects associated with the APPS schema.

When no schema name is provided and EXPORT_SCHEMA is enabled, Ora2Pg will export all objects from all schemas of the Oracle instance with their names prefixed with the schema name.

• EXPORT_SCHEMA

  By default, the Oracle schema is not exported into the PostgreSQL database and all objects are created under the default Pg namespace. If you want to also export this schema and create all objects under this namespace, set the EXPORT_SCHEMA directive to 1. This will set the schema search_path at the top of the export SQL file to the schema name set in the SCHEMA directive with the default pg_catalog schema. If you want to change this path, use the directive PG_SCHEMA.

• CREATE_SCHEMA

  Enable/disable the CREATE SCHEMA SQL order at the start of the output file. It is enabled by default and concerns the TABLE export type.

• COMPILE_SCHEMA

  By default, Ora2Pg will only export valid PL/SQL code. You can force Oracle to compile again the invalidated code to get a chance to have it obtain the valid status and then be able to export it.

  Enable this directive to force Oracle to compile schema before exporting code. When this directive is enabled and SCHEMA is set to a specific schema name, only invalid objects in this schema will be recompiled. If SCHEMA is not set then all schema will be recompiled. To force recompilation of invalid object in a specific schema, set COMPILE_SCHEMA to the schema name you want to recompile.

  This will ask Oracle to validate the PL/SQL that could have been invalidated after an export/import for example. The 'VALID' or 'INVALID' status applies to functions, procedures, packages and user defined types. It also concerns disabled triggers.

• EXPORT_INVALID

  If the above configuration directive is not enough to validate your PL/SQL code, enable this configuration directive to allow export of all PL/SQL code even if it is marked as invalid. The 'VALID' or 'INVALID' status applies to functions, procedures, packages, triggers and user defined types.

• PG_SCHEMA

  Allows you to define/force the PostgreSQL schema to use. By default, if you set EXPORT_SCHEMA to 1, the PostgreSQL search_path will be set to the schema name exported set as value of the SCHEMA directive.

  The value can be a comma-delimited list of schema names but not when using TABLE export type because in this case it will generate the CREATE SCHEMA statement and it doesn't support multiple schema names. For example, if you set PG_SCHEMA to something like "user_schema, public", the search path will be set like this:

SET search_path = user_schema, public;

forcing the use of an other schema (here user_schema) than the one from Oracle schema set in the SCHEMA directive.

You can also set the default search_path for the PostgreSQL user you are using to connect to the destination database by using:

ALTER ROLE username SET search_path TO user_schema, public;

in this case you don't have to set PG_SCHEMA.

• SYSUSERS

  Without explicit schema, Ora2Pg will export all objects that do not belong to system schemas or roles:

SYSTEM,CTXSYS,DBSNMP,EXFSYS,LBACSYS,MDSYS,MGMT_VIEW,
OLAPSYS,ORDDATA,OWBSYS,ORDPLUGINS,ORDSYS,OUTLN,
SI_INFORMTN_SCHEMA,SYS,SYSMAN,WK_TEST,WKSYS,WKPROXY,
WMSYS,XDB,APEX_PUBLIC_USER,DIP,FLOWS_020100,FLOWS_030000,
FLOWS_040100,FLOWS_010600,FLOWS_FILES,MDDATA,ORACLE_OCM,
SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR,XS$NULL,PERFSTAT,
SQLTXPLAIN,DMSYS,TSMSYS,WKSYS,APEX_040000,APEX_040200,
DVSYS,OJVMSYS,GSMADMIN_INTERNAL,APPQOSSYS,DVSYS,DVF,
AUDSYS,APEX_030200,MGMT_VIEW,ODM,ODM_MTR,TRACESRV,MTMSYS,
OWBSYS_AUDIT,WEBSYS,WK_PROXY,OSE$HTTP$ADMIN,
AURORA$JIS$UTILITY$,AURORA$ORB$UNAUTHENTICATED,
DBMS_PRIVILEGE_CAPTURE,CSMIG,MGDSYS,SDE,DBSFWUSER

Depending on your Oracle installation, you may have several other system roles defined. To append these users to the schema exclusion list, just set the SYSUSERS configuration directive to a comma-separated list of system users to exclude. For example:

SYSUSERS        INTERNAL,SYSDBA,BI,HR,IX,OE,PM,SH

will add users INTERNAL and SYSDBA to the schema exclusion list.

• FORCE_OWNER

  By default, the owner of the database objects is the one you're using to connect to PostgreSQL using the psql command. If you use an other user (postgres for example), you can force Ora2Pg to set the object owner to be the one used in the Oracle database by setting the directive to 1, or to a completely different username by setting the directive value to that username.

• FORCE_SECURITY_INVOKER

  Ora2Pg uses the function's security privileges set in Oracle and it is often defined as SECURITY DEFINER. If you want to override those security privileges for all functions and use SECURITY DEFINER instead, enable this directive.

• USE_TABLESPACE

  When enabled this directive forces ora2pg to export all tables and indexes using the tablespace name defined in Oracle database. This works only with tablespaces that are not TEMP, USERS or SYSTEM.

• WITH_OID

  Activating this directive will force Ora2Pg to add WITH (OIDS) when creating tables or views as tables. Default is same as PostgreSQL, disabled.

• LOOK_FORWARD_FUNCTION

  List of schemas to get functions/procedures meta information that are used in the current schema export. When replacing calls to functions with OUT parameters, if a function is declared in an other package, then the function call rewriting can not be done because Ora2Pg only knows about functions declared in the current schema. By setting a comma-separated list of schemas as value of this directive, Ora2Pg will look forward in these packages for all functions/procedures/packages declarations before proceeding to current schema export.

• NO_FUNCTION_METADATA

  Forces Ora2Pg to not look for function declarations. Note that this will prevent Ora2Pg from rewriting function replacement calls if needed. Do not enable it unless looking forward at functions breaks other exports.