DBA codes for database
-- Run gather stats on a table to remove the stale data
BEGIN
dbms_stats.gather_table_stats ('HCLSTG3'
,'BEN_CONV_TARGET'
,cascade => TRUE);
END;
-- List all indexes with INDEX CREATE statement for a given table
SELECT i.table_name
,i.index_name
,i.uniqueness
,c.column_name
,i.status
,dbms_lob.substr (dbms_metadata.get_ddl ('INDEX'
,i.index_name)
,4000
,1) ddl_val
FROM user_indexes i
,user_ind_columns c
WHERE i.index_name = c.index_name
and i.table_name = 'DATA_COMPARE_RESULT_464'
ORDER BY i.table_name
,c.index_name
,c.column_position
execute dbms_stats.gather_schema_stats
(ownname => 'HCLSTG3'
,degree => 16
,estimate_percent => 5
,cascade => TRUE
,method_opt => 'for all indexed columns size skewonly' );