-
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:-
instance name
table name
row count
date
no of extents
segment size
Can you guys think of anything else (that relates to a table) that might be useful???
LAST_ANALYZED maybe?
chris
-
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.
ALenz
-
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
select s.owner
,s.segment_type
,s.segment_name
,s.tablespace_name
,s.next_extent/1024/1024 req
,maxtspace.max/1024/1024 maxfree
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
/
-nagarjuna
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|