Selecting objects for Oracle migration

As part of the migration creation, you can specify objects to include or exclude for Oracle Migrations. Alternatively, you can also perform the act of inclusion or exclusion of objects after a migration has been created, using the Selected Objects menu option.

When creating a migration, specify rules for selecting objects in the Advanced Settings on the Selected Objects tab.

Select the Use advanced editor toggle to add the objects you want to include or exclude in bulk as follows:

schema_name1,object_name1,TABLE,EXCLUDE
schema_name2,object_name2,TABLE,EXCLUDE
schema_name3,object_name3,TABLE,EXCLUDE

Add all the objects to include or exclude by listing the Object Owner, Object Name, Object Type , and Action (Include or Exclude), as shown in the above format (comma separated).

To exclude a table from replication, enter information in the following comma separated format:

schema_name1,object_name1,TABLE,EXCLUDE,EXCLUDEFROMREPLICATION

Note:

In the advanced editor:

• Use a comma separator character (,) to separate each item for every inclusion/exclusion definition.

• Use the escape character (\ if your schema or object name has a comma (,) character as part of its name.

• You can add multibyte character (Unicode) names for schema or object names. For example, ƹ ƿschema,DŽobject,TABLE,EXCLUDE.

• The maximum input size is 500 KB. __COMMENT_6__

Special Characters and Reserved Words

To avoid issues, do not use special characters such as ( & @ * $ | % ~ -) in object names, except for underscore (_). Also do not use Oracle SQL or PL/SQL reserved words in object names, especially the Oracle name.

For more information for excluding the table objects with special characters, see Database Object Naming Rules.

For information about Reserved words, characters and nonquoted identifiers, see Oracle SQL Reserved Words.

For the latest information, you can generate a list of all keywords and reserved words with the V$RESERVED_WORDS view, described here.

Alternatively, you can choose either Include or Exclude from the Action list to specify if a rule should include or exclude the specified database objects in the migration. You can either include or exclude objects in a migration, but you cannot do both.

If no rule is defined, all schemas and objects of the source database will be migrated, with exceptions explained in Objects and Schemas Excluded by Default below.

If you specify Include rules, the migration will only move the specified objects and their dependent objects; all other objects are automatically excluded.

When specifying Exclude rules, the migration will exclude the specified objects and their dependent objects; all other objects are included in the migration.

To create a rule, enter values for each of the following fields:

• Object Owner specifies the owner of the selected database objects. When using Include rules, all rules must be for the same owner, and wild characters are not allowed.

• Object Name specifies the name of selected database objects

• Object Type specifies the type of selected database objects. You can select ALL to select objects of all types.

• Replication only: You can select this toggle when you want to exclude the tables from replication. This option is enabled when the action is Exclude and the Object Type is TABLE. This ensures that object types such as ROWID columns, unsupported by Oracle GoldenGate, are not replicated during online migration.

Note: Although this excludes the tables from replication; these tables are migrated to the target database twice. Once during the initial load and another during the switchover phase. Data Pump export and import is performed again (reload) on these tables that were not part of replication to bring these tables on the target up to date with the source.

You can filter Object Owner and Object Name fields using any valid pattern in Java class Pattern. For example, you can enter .* in the Object Name field to select objects of any name.

The objects included in a migration are also influenced by the Job Mode of the initial load, as explained in Creating Oracle migrations.

Please note the following restrictions:

• When excluding an object in a specified schema, and an object of the same name exists in a different schema that is also part of the migration, the objects will not be excluded (that is, the rule is ignored). The exclusion can be accomplished by migrating the schemas in separate migrations.

• When creating Include rules in Full job mode, only schema-level rules (Object Name is .* and Object Type is ALL) are allowed.

• If an Include rule has .* in Object Name, no other rule for the same Object Type is allowed. If the rule has ALL as Object Type, no other rule for any type is allowed.

• The Object type ALL is only allowed for schema-level rules (Object Name is .*).

• If you define a rule with an Object owner pattern other than .* and the Object Name is .* then the Object type TABLE is not allowed.

• Object-level rules (Object Name is any pattern other than .) can only be used for the following object types: DIRECTORY, FUNCTION, JOB, MATERIALIZED_VIEW, PACKAGE, PROCEDURE, TRIGGER, SEQUENCE, TABLE. All other object types must be either included or excluded using the . pattern in Object Name, and in addition for exclude, the owner should be .*

Examples

Example 1: Include all objects of schema MySchema

Action = Include

Object Owner	Object Name	Object Type
MySchema	.*	ALL

Example 2: Include all tables starting with PROD and procedure MYPROC of schema MySchema, including all dependent objects.

Action = Include

Object Owner	Object Name	Object Type
MySchema	PROD.*	TABLE
MySchema	MYPROC	PROCEDURE

Example 3: Exclude schemas starting with Experimental, the table MySchema.OldTable (also excluding all dependent objects) and all objects of type DB_LINK.

Note that MySchema.OldTable will not be excluded if a table called OldTable is present in a different schema that is also migrated.

Action = Exclude

Object Owner	Object Name	Object Type
Experimental.*	.*	ALL
MySchema	OldTable	TABLE
.*	.*	DB_LINK

Objects and schemas excluded by default

The following object types are always excluded:

• GoldenGate administrators: identified in DBA_GOLDENGATE_PRIVILEGES, including ggadmin and c##ggadmin users

• If target is Autonomous Data Warehouse Shared Infrastructure: CLUSTER, DB_LINK, INDEXTYPE, STATISTICS

• If target is Autonomous Data Warehouse Dedicated Infrastructure, Autonomous Transaction Processing Shared or Dedicated Infrastructure: CLUSTER, DB_LINK, STATISTICS

• All other targets: STATISTICS

The following schemas are excluded by default:

• Schema is marked as ORACLE_MAINTAINED in SYS.DBA_USERS on the source or target database

• Schema is marked as excluded from export in SYS.KU_NOEXP_VIEW on the source database

• Schema GGADMIN and C##GGADMIN