Re: Adding or Altering the TOAD datapump EXCLUDE/INCLUDE Filter List

Toad for Oracle
1

Hi Mark,

Indeed it’s not listed in those views. Well, I know it works as I use it
all the time. In my scenario I have domain indexes which are controlled by
third-party software. I wish to datapump a replacement table with all the normal
indexes intact BUT skip the domain indexes as they can often take much longer to
load than the table data itself. I have to rebuild them all anyhow on the target
database because the third-party software renames them to an instance-specific
naming scenario. I can indeed use TABLE/INDEX to skip ALL the indexes but would
rather not have to do that work.

So is there some way to convince the TOAD team that TABLE/INDEX/DOMAIN_INDEX is
a valid parameter? I can show three import logs for a sample table:

NO FILTER, gives me the whole kit and caboodle:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table “RAD_NHD”.“SYS_IMPORT_FULL_03” successfully loaded/unloaded

Starting “RAD_NHD”.“SYS_IMPORT_FULL_03”: rad_nhd/********@kraken_waters10
DUMPFILE=sample.dmp LOGFILE=full_import.log DIRECTORY=LOADING_DOCK
TABLE_EXISTS_ACTION=REPLACE

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported “RAD_NHD”.“RAD_FSHTD_L_OS” 2.272 MB 3830 rows

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX

Job “RAD_NHD”.“SYS_IMPORT_FULL_03” successfully completed at 10:57:29

FILTER using TABLE/INDEX, gives me no indexes at all

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table “RAD_NHD”.“SYS_IMPORT_FULL_03” successfully loaded/unloaded

Starting “RAD_NHD”.“SYS_IMPORT_FULL_03”: rad_nhd/********@kraken_waters10
DUMPFILE=sample.dmp LOGFILE=exclude_tableindex_import.log DIRECTORY=LOADING_DOCK
TABLE_EXISTS_ACTION=REPLACE EXCLUDE=TABLE/INDEX

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported “RAD_NHD”.“RAD_FSHTD_L_OS” 2.272 MB 3830 rows

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job “RAD_NHD”.“SYS_IMPORT_FULL_03” successfully completed at 10:59:55

FILTER USING TABLE/INDEX/DOMAIN_INDEX, gives me all indexes (and index stats)
BUT no domain indexes

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table “RAD_NHD”.“SYS_IMPORT_FULL_03” successfully loaded/unloaded

Starting “RAD_NHD”.“SYS_IMPORT_FULL_03”: rad_nhd/********@kraken_waters10
DUMPFILE=sample.dmp LOGFILE=exclude_tableindexdoindex_import.log
DIRECTORY=LOADING_DOCK TABLE_EXISTS_ACTION=REPLACE
EXCLUDE=TABLE/INDEX/DOMAIN_INDEX

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported “RAD_NHD”.“RAD_FSHTD_L_OS” 2.272 MB 3830 rows

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job “RAD_NHD”.“SYS_IMPORT_FULL_03” successfully completed at 11:02:27

If I cannot convince you with this evidence, how about an option to add a custom
filter or somehow tack DOMAIN_INDEX on the backend of the existing TABLE/INDEX
option?

Thanks for responding to this issue so quickly.

Cheers,

Paul

2

You’ve convinced me Paul, you’ve convinced me. : )

I’ll either just add that one to the list or make it so you can add additional
values onto that list in case there are other valid values. It would be nice to
pull them from the database somewhere though.

Because we’re preparing the release of Toad 11 the change won’t be commercially
available until 11.1, hopefully in the spring. And in the beta, which we’ll
start up in late Sept/early Oct.

3

DATABASE_EXPORT_OBJECTS , SCHEMA_EXPORT_OBJECTS , and TABLE_EXPORT_OBJECTS

4

not sure what that means Bert

5

It would be nice to pull them from the database somewhere though.

DATABASE_EXPORT_OBJECTS , SCHEMA_EXPORT_OBJECTS , and TABLE_EXPORT_OBJECTS

Those are the answers to your wish, you can select from them.

6

Did you guys actually read the thread?

7

Yes – I thought someone said would be nice to be able to select from list
so I sent the views with the choices. What did I miss?

8

Hey Mark, I was looking at the underlying objects of these views.

If you do this query, you’ll find what Paul mentions.

select * from sys . metanametrans$

where name like ‘%DOMAIN%’

It looks like an oracle bug that some other data is missing from sys.metafilter$
which is preventing everything from showing up in the views you are using.

9

cool, you’re right, thanks John. I’d never have imagined the view itself to be
wrong, but that explains it. Presumably anyone who can query the view will be
able to query SYS.METAFILTER$, right? I’m much happier about pulling the domain
from the database

10

No, that’s a table owned by SYS. Selecting from it shouldn’t be a
problem for DBA type users, but most others won’t have the privileges to
select from it.

11

This is fixed for Toad 11.1 Paul, and will be in the upcoming Beta if you want
to get it there first. Thanks for bringing it to our attention.