Andrew Fraser DBA

To see when tables (or other objects) were created, first change your date format to display time:

alter session set nls_date_format='Dy DD-Mon-YYYY HH24:MI:SS';

Then in Oracle SQL Developer, right click on the table or object in question, select ‘open‘, and look at the ‘details‘ tab.

Or alternatively still in SQL, look at the created column:

select object_name, created from user_objects order by 2;

That is for objects owned by you. For objects owned by others:

select object_name, created from all_objects order by 2;

Note that the column ‘last_ddl_time’ is not as useful as it sounds, since even operations like GRANTs on the table update the ‘last_ddl_time’.

Replace SYSTEM with the name of the schema you are interested in:

set serverout on size 999999
declare
  cnt number ;
begin
  for c1 in (select owner, table_name from all_tables where owner = 'SYSTEM')
  loop
    execute immediate 'select count(1) from '||c1.owner||'.'||c1.table_name into cnt ;
    dbms_output.put_line(c1.table_name||','||cnt) ;
  end loop ;
end ;
/

check it is on with:

select state, last_start_date from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB' ;

Switch it on and off with:

exec dbms_scheduler.disable('GATHER_STATS_JOB')
exec dbms_scheduler.enable('GATHER_STATS_JOB')