my Siebel sandpit

This SQL query identifies custom columns added to the base Siebel data model.

SQL> select table_name, column_name
from all_tab_columns 
where owner='SIEBEL'
and table_name like 'S\_%' escape '\'
and column_name like 'X\_%' escape '\';

TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
S_CONTACT X_HIGH_VALUE_FLG

Most custom columns are (by definition) used in the Siebel application. Consequently, most are candidates for a custom index.

SQL> select index_name, column_name, table_name
from all_ind_columns
where table_name like 'S\_%' escape '\'
and column_name like 'X\_%' escape '\'
and table_owner = 'SIEBEL';

no rows selected

Similarly, to find custom tables

SQL> select table_name
from all_tables where 
owner='SIEBEL'
and table_name like 'CX\_%' escape '\';

TABLE_NAME
------------------------------
CX_EAI_LOG

Note: The ESCAPE clause is required and forces the underscore character to be interpreted literally instead of single character wildcard match.