The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ddl_object
,ddl_stmt
,ddl_type
FROM hri_utl_dynmc_ddl_infrmtn
WHERE table_name = p_table_name
AND table_owner = p_table_owner
AND ddl_type IN ('INDEX', 'INDEX LOGGING')
ORDER BY DECODE(ddl_type, 'INDEX', 1, 2);
DELETE FROM hri_utl_dynmc_ddl_infrmtn
WHERE table_name = p_table_name
AND table_owner = p_table_owner
AND ddl_object = index_rec.ddl_object
AND ddl_type = index_rec.ddl_type;
SELECT index_name index_name
,DECODE(uniqueness, 'UNIQUE', 'UNIQUE ', null) uniqueness
,DECODE(INDEX_TYPE,'NORMAL',null,INDEX_TYPE) index_type
,NVL(LOGGING, 'NO') logging
,DECODE(PARTITIONED,'YES','LOCAL ',' ') ||
DECODE(NVL(TABLESPACE_NAME,'###'),'###',null,'TABLESPACE ' ||TABLESPACE_NAME) ||
' NOLOGGING' ||
' STORAGE (INITIAL ' || NVL(to_char(initial_extent), '4K') ||
' NEXT ' || NVL(to_char(next_extent), '40K') ||
' MINEXTENTS ' || NVL(to_char(min_extents), '1') ||
' MAXEXTENTS ' || NVL(to_char(max_extents), 'UNLIMITED') ||
' PCTINCREASE ' || NVL(to_char(pct_increase), '0') ||
' FREELIST GROUPS ' || NVL(to_char(freelist_groups), '4') ||
' FREELISTS ' || NVL(to_char(freelists), '4') || ')' ||
' PCTFREE ' || NVL(to_char(pct_free), '10') ||
' INITRANS ' || NVL(to_char(ini_trans), '11') ||
' MAXTRANS ' || NVL(to_char(max_trans), '255') ||
' PARALLEL ' storage_clause
FROM dba_indexes
WHERE table_name = p_table_name
AND table_owner = p_table_owner
AND owner = p_table_owner
--
-- for bug 3738009, filter out the system created indexes on the materialized
-- views since these are not to be dropped and recreated
--
AND index_name NOT LIKE 'I_SNAP$%';
SELECT column_name
FROM dba_ind_columns
WHERE index_owner = p_table_owner
AND table_owner = p_table_owner
AND table_name = p_table_name
AND index_name = v_index_name
ORDER BY column_position;
INSERT INTO hri_utl_dynmc_ddl_infrmtn
(table_name
,table_owner
,ddl_object
,ddl_type
,ddl_stmt)
VALUES
(p_table_name
,p_table_owner
,index_rec.index_name
,'INDEX'
,l_create_index_stmt);
UPDATE hri_utl_dynmc_ddl_infrmtn
SET ddl_stmt = l_create_index_stmt
WHERE table_name = p_table_name
AND table_owner = p_table_owner
AND ddl_object = index_rec.index_name
AND ddl_type = 'INDEX';
INSERT INTO hri_utl_dynmc_ddl_infrmtn
(table_name
,table_owner
,ddl_object
,ddl_type
,ddl_stmt)
VALUES
(p_table_name
,p_table_owner
,index_rec.index_name
,'INDEX LOGGING'
,l_alter_index_stmt);
UPDATE hri_utl_dynmc_ddl_infrmtn
SET ddl_stmt = l_alter_index_stmt
WHERE table_name = p_table_name
AND table_owner = p_table_owner
AND ddl_object = index_rec.index_name
AND ddl_type = 'INDEX LOGGING';
SELECT partition_count
FROM all_part_tables
WHERE table_name = p_table_name
AND owner = p_table_owner;