Oracle Database Discussions

Hi,

I want to check how and when a table will be marked as STALE.

I have done following steps

---- Created a table --------------------

SQL> create table t1 (id number, name varchar2(100)) ;

Table created.

---- Inserted decent amount of data --------------

SQL> declare v1 number; begin for i in 1..25 loop
2 insert into t1 select object_id, object_name from all_objects ; commit; end loop;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select table_name, status, num_rows, last_analyzed, monitoring from user_tables where table_name='T1';

TABLE_NAME STATUS NUM_ROWS LAST_ANAL MON
------------------------------ -------- ---------- --------- ---
T1 VALID YES

---- Gather stats -----------------

SQL> exec dbms_stats.gather_table_stats( user, 'T1', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> select table_name, status, num_rows, last_analyzed, monitoring from user_tables where table_name='T1';

TABLE_NAME STATUS NUM_ROWS LAST_ANAL MON
------------------------------ -------- ---------- --------- ---
T1 VALID 115544 18-JAN-08 YES

---- Insert more data ---------------------------
SQL> declare v1 number; begin for i in 1..25 loop
2 insert into t1 select object_id, object_name from all_objects ; commit; end loop;
3 end;
4 /

PL/SQL procedure successfully completed.


Now, after I have analyzed the table and get the stats in, I loaded good amount of data. So, in theory, this table should be marked as STALE.

Where can I check if this table has been marked STALE? when ?

Please guide.

Thanks