kk10072019
1/11/2017 - 2:07 AM

DBA codes for database

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' );