Excluding Oracle System Tables

Document ID : KB000026788
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

The Oracle Exchange has the ability to include system objects during the scan process. A checkbox on the GUI when clicked tells the scanner that system tables are to be included in the scan.

What is a System Table:

The current architecture of the scanner says a system table is a table that has a schema of SYSTEM, SYS, CTXSYS, MDSYS, and MMADMIN. There are many other objects supplied by Oracle that some customers consider to be system table such as WMSYS. However what one customer calls a system table may differ from what another customer calls a system table.

The following is a temporary solution until an enhancement to allow items to be excluded from the scan.

Solution:

Additional system tables can be excluded by modifying the file prextr_orc.cfg in the install folder.

To exclude additional tables, modify lines starting with VTABLE, VSCHEMA, VOBJECT, VSEQUENCE and VVIEW.

To exclude owner WMSYS and SYSMAN change line starting VTABLE. Insert 'OWNER NOT IN ('WMSYS','SYSMAN')' after DBA_ ALL _TABLES:

See below

VTABLE:TABLE:SYS:DBA_ ALL _TABLES:OWNER NOT IN ('WMSYS','SYSMAN'):

TABLE_NAME:@OWNER(30):*TABLE_NAME(30):TABLESPACE_NAME(30)

Object table types can be excluded by inserting 'TABLE_TYPE IS NULL' as shown below.

The following shows modifications needed to exclude owners

WMSYS and SYSMAN, and to exclude object table types.

Note:

line VSEQUENCE has SEQUENCE_OWNER not OWNER.

VTABLE:TABLE:SYS:DBA_ ALL _TABLES:TABLE_TYPE IS NULL AND OWNER NOT IN

('WMSYS','SYSMAN'):TABLE_NAME:@OWNER(30):*TABLE_NAME(30):TABLESPACE_NAME(30)

VSCHEMA:TABLE:SYS:DBA_ ALL _TABLES:TABLE_TYPE IS NULL AND OWNER NOT IN

('WMSYS','SYSMAN'):OWNER:DISTINCT:*OWNER(30)

VOBJECT:OBJECT:SYS:DBA_SOURCE:LINE = 1 AND TYPE IN ('PACKAGE',

'PACKAGE BODY','PROCEDURE','FUNCTION') AND OWNER NOT IN ('WMSYS',

'SYSMAN'):NAME:@OWNER(30):*NAME(30):!TYPE(30)

VSEQUENCE:SEQUENCE:SYS:DBA_SEQUENCES:SEQUENCE_OWNER NOT IN ('WMSYS',

'SYSMAN'):SEQUENCE_NAME:@SEQUENCE_OWNER(30):*SEQUENCE_NAME(30)

VVIEW:VIEW3:SYS:DBA_VIEWS:OWNER NOT IN ('WMSYS','SYSMAN'):VIEW_NAME:

@OWNER(30):*VIEW_NAME(30)