Adding Large Numbers of Tables/Schemas in the Setup Wizard

Problem Description

If you have large numbers of tables or schemas these can be written to their own file. This is then able to be passed as input via @(filename) to the setup wizard. This saves having to type out these details one by one, which would be impractical in such cases.

Solution

For example, we can create a text file in my replication home directory with the names of the tables I want to include in the replication. Each table name is written to a new line:

oracle@dbvlin801[/home/oracle/RepOneWay]: cat scott_tables.txt
scott.test1
scott.test2
scott.test3
scott.test4
scott.test5
scott.test6
scott.test7
scott.test8
scott.test9
scott.test10

Using the SPOOL command of SQLPlus can also be a useful option in helping to generate these files, for example:

sqlplus "/as sysdba"
SPOOL scott_tables2.txt;
SET heading off;
SET feedback off;
SET serveroutput off;
select 'SCOTT.' || table_name from dba_tables where owner='SCOTT';
SPOOL off;

Then, in Step 3 of the setup wizard process, we pass this file into the wizard itself with the @ command:

Please enter list of all individual tables to be replicated.
Enter schema name(s) only to replicate all tables in that schema.
Use comma or space to delimit the entries.

Enter the tables and schemas: [] @scott_tables.txt
Selected tables:
SCOTT.TEST1,SCOTT.TEST10,SCOTT.TEST2,SCOTT.TEST3,SCOTT.TEST4,SCOTT.TEST5,SCOTT.TEST6,SCOTT.TEST7,SCOTT.
TEST8,SCOTT.TEST9

Add more tables or schemas? (YES/NO) [NO]

The same approach holds for adding in large numbers of schema names. To do so, simply create a text file in the replication home directory with the names of the schemas to include in the replication. Write each schema name on a new line. For example, I can create a text file called rep_schemas.txt with the following:

This text file can then be invoked in the setup wizard as follows:

To reiterate, you can use SQLPlus (or similar tools) to help produce these files.

Other options for adding many schemas can be found here.

Mike Donovan February 02, 2015 15:58