I'm writing a simple procedure that will run nightly to gather some basic table information, I want to be able to answer questions like "How many record were in so and so table 2 months ago?".......I know this will have been done before but I'm learning and it never hurts to get your hands dirty
It inserts the information into a table and I'm currently collecting the following data:-
no of extents
Can you guys think of anything else (that relates to a table) that might be useful???
col owner form a8
col segment_type for a7
col segment_name form a25
col tablespace_name form a15
col req form 9999999990 heading "Req (Mb)"
col next_extent form 99990 heading "Req (Mb)"
col maxfree form 99990 heading "Free (Mb)"
set pause off
set pages 1000
set lines 80
compute sum of req on tablespace_name
break on tablespace_name skip 1
from dba_segments s, (select tablespace_name,max(fsu.bytes) max
from dba_free_space fsu
group by tablespace_name) maxtspace
where s.tablespace_name = maxtspace.tablespace_name
and segment_type IN ('TABLE','INDEX','CLUSTER','TABLE PARTITION','INDEX PARTITION')
and next_extent > maxtspace.max