simple question on growth monitoring
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???
would be good to know:
1. number of max extents allowed for this table to make sure table can extend and the table is not close to reaching max extents.
2. make sure next extent can fit in the tablespace.
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
Click Here to Expand Forum to Full Width