[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DURATION || NVL(TEMPORARY,'N') || NVL(PCT_FREE, 0)
|| NVL(PCT_USED, 0) || NVL(INI_TRANS, 0) ||
NVL(MAX_TRANS, 0) || NVL(INITIAL_EXTENT, 0) ||
NVL(NEXT_EXTENT, 0) || NVL(MIN_EXTENTS, 0) ||
NVL(MAX_EXTENTS, 0) || NVL(PCT_INCREASE, 0) ||
NVL(PARTITIONED, 'NO') || NVL(FREELISTS, 0) ||
NVL(FREELIST_GROUPS, 0) ||
NVL(DECODE(LTRIM(DEGREE), 'DEFAULT', 65536, DEGREE) , 0) ||
LOGGING|| CACHE|| IOT_TYPE || ROW_MOVEMENT into tmp_str
FROM ALL_TABLES WHERE TABLE_NAME=p_tablename AND OWNER=p_owner;
for x in ( select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE,
DATA_PRECISION, DATA_SCALE, DATA_DEFAULT from all_tab_columns where
table_name =p_tablename and owner = p_owner order by column_name)
loop
l_hashVal := dbms_utility.get_hash_value(x.COLUMN_NAME || x.DATA_TYPE || x.DATA_LENGTH || x.NULLABLE || x.DATA_PRECISION || x.DATA_SCALE || x.DATA_DEFAULT, 1, 999999999);
for x in ( SELECT PARTITION_NAME, HIGH_VALUE, SUBPARTITION_COUNT
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = p_tablename
and TABLE_OWNER = p_owner ORDER BY PARTITION_POSITION)
loop
l_hashVal := dbms_utility.get_hash_value(x.PARTITION_NAME || x.HIGH_VALUE || x.SUBPARTITION_COUNT,1,999999999);
for x in ( SELECT COLUMN_NAME FROM DBA_PART_KEY_COLUMNS
WHERE NAME = p_tablename AND OWNER =p_owner ORDER BY COLUMN_POSITION)
loop
l_hashVal := dbms_utility.get_hash_value(x.COLUMN_NAME,1,999999999);
SELECT TYPE || OBJECT_TYPE || SORT_ORDER || RECIPIENTS ||
MESSAGE_GROUPING || PRIMARY_INSTANCE || SECONDARY_INSTANCE ||
USER_COMMENT INTO l_hashstr
FROM ALL_QUEUE_TABLES
WHERE QUEUE_TABLE=p_qtablename AND OWNER= p_owner;
SELECT name || queue_table || max_retries || enqueue_enabled ||
dequeue_enabled || retry_delay || retention || user_comment
INTO l_hashstr
FROM ALL_QUEUES
WHERE OWNER=p_owner AND NAME = p_queuename;
SELECT COLUMN_NAME
FROM all_ind_columns
WHERE INDEX_NAME = p_indexname AND INDEX_OWNER = p_owner
ORDER BY COLUMN_POSITION;
SELECT INDEX_TYPE || UNIQUENESS || NVL(INI_TRANS, 0) || NVL(MAX_TRANS, 0) ||
NVL(INITIAL_EXTENT, 0) || NVL(NEXT_EXTENT, 0 ) || NVL(MIN_EXTENTS, 0) || NVL(MAX_EXTENTS, 0) ||
NVL(PCT_INCREASE, 0) || NVL(FREELISTS, 0) || NVL(FREELIST_GROUPS, 0) || NVL(pct_free, 0) ||
NVL(DECODE(LTRIM(DEGREE), 'DEFAULT', 65536, DEGREE),0) || NVL(PARTITIONED, 'NO') ||
NVL(funcidx_status, 'DISABLED') || TABLE_NAME ||
TABLE_TYPE || NVL(ITYP_OWNER, '-1') || NVL(ITYP_NAME,'-1') || NVL(PARAMETERS, '-1') || NVL(COMPRESSION, 'DISABLED')
INTO l_hashstr
FROM ALL_INDEXES
WHERE index_name = p_indexname AND OWNER = p_owner;
SELECT COLUMN_NAME
FROM all_ind_columns
WHERE INDEX_NAME = p_indexname AND INDEX_OWNER = p_owner
ORDER BY COLUMN_POSITION;
selQry := 'SELECT INDEX_TYPE || UNIQUENESS || NVL(INI_TRANS, 0) || NVL(MAX_TRANS, 0) || '
||'NVL(INITIAL_EXTENT, 0) || NVL(NEXT_EXTENT, 0 ) || NVL(MIN_EXTENTS, 0) || NVL(MAX_EXTENTS, 0) || '
||'NVL(PCT_INCREASE, 0) || NVL(FREELISTS, 0) || NVL(FREELIST_GROUPS, 0) || NVL(pct_free, 0) || '
||'NVL(DECODE(LTRIM(DEGREE), ''DEFAULT'', 65536, DEGREE),0) || NVL(PARTITIONED, ''NO'') || '
||'NVL(funcidx_status, ''DISABLED'') || TABLE_NAME || NVL(tablespace_name, '' '') || TABLE_OWNER || '
||'TABLE_TYPE || NVL(ITYP_OWNER, ''-1'') || NVL(ITYP_NAME,''-1'') || NVL(PARAMETERS, ''-1'') || NVL(COMPRESSION, ''DISABLED'') hashstr,'
||'index_name '
||'FROM ALL_INDEXES , TABLE(FND_XDF_TABLE_OF_VARCHAR2_30(' || l_indexListstr ||')) FND_TAB '
||'WHERE index_name = FND_TAB.COLUMN_VALUE AND OWNER = ''' || p_owner || ''''
||' UNION ALL '
||'SELECT ''NULL'', C.COLUMN_VALUE FROM TABLE(FND_XDF_TABLE_OF_VARCHAR2_30('|| l_indexListstr || ')) C '
||'WHERE C.COLUMN_VALUE NOT IN '
||'(SELECT B.INDEX_NAME FROM ALL_INDEXES B WHERE OWNER = '''|| p_owner || ''' AND C.COLUMN_VALUE = B.INDEX_NAME) ';
SELECT COLUMN_NAME
FROM all_ind_columns
WHERE INDEX_NAME = p_indexname AND INDEX_OWNER = p_owner
ORDER BY COLUMN_POSITION;
selQry := 'SELECT INDEX_TYPE || UNIQUENESS || NVL(INI_TRANS, 0) || NVL(MAX_TRANS, 0) || '
||'NVL(INITIAL_EXTENT, 0) || NVL(NEXT_EXTENT, 0 ) || NVL(MIN_EXTENTS, 0) || NVL(MAX_EXTENTS, 0) ||'
||'NVL(PCT_INCREASE, 0) || NVL(FREELISTS, 0) || NVL(FREELIST_GROUPS, 0) || NVL(pct_free, 0) ||'
||'NVL(DECODE(LTRIM(DEGREE), ''DEFAULT'', 65536, DEGREE),0) || NVL(PARTITIONED, ''NO'') ||'
||'NVL(funcidx_status, ''DISABLED'') || TABLE_NAME || '
||'TABLE_TYPE || NVL(ITYP_OWNER, ''-1'') || NVL(ITYP_NAME,''-1'') || NVL(PARAMETERS, ''-1'') || NVL(COMPRESSION, ''DISABLED'') hashstr'
||' FROM ALL_INDEXES '
||' WHERE OWNER = '''|| p_owner ||''' AND INDEX_NAME = :p_indexname ';
for x in ( select /*+ first_rows */ name,
owner,
type
from dba_dependencies
where referenced_owner = p_owner
and referenced_type = p_type
and type in ('TABLE','TYPE','MATERIALIZED VIEW','VIEW','INDEX')
and referenced_name = p_name )
loop
l_data.extend;