Gather statistics on table and schema

SELECT * FROM dba_tab_modifications


SELECT * FROM user_tab_modifications


CREATE OR REPLACE PROCEDURE HALIM_GATHER_STAT_SCHEMA
IS
BEGIN
begin
dbms_stats.unlock_schema_stats(ownname=> '"TEST"');
end;

dbms_stats.gather_schema_stats (
ownname => 'TEST',
options => 'GATHER',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade => true,
degree => 5
);

begin
dbms_stats.lock_schema_stats( ownname=> '"TEST"' );
end;

END;



EXEC dbms_stats.gather_schema_stats (
ownname => 'MYBANK',
options => 'GATHER',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade => true,
degree => 5
);



SELECT a.owner, a.table_name, a.avg_row_len, a.blocks, a.last_analyzed,
a.num_rows, a.tablespace_name, a.row_movement, a.sample_size
FROM all_tables a
WHERE last_analyzed IS NOT NULL AND owner = 'TEST'
ORDER BY a.last_analyzed DESC


SELECT a.owner, a.table_name, a.index_name, a.last_analyzed, a.num_rows,
a.tablespace_name, a.sample_size
FROM all_indexes a
WHERE last_analyzed IS NOT NULL AND owner = 'TEST'
ORDER BY a.last_analyzed DESC