Tablespace usage pattern
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Tablespace usage pattern

  1. #1
    Join Date
    Jul 2005
    Posts
    87

    Tablespace usage pattern

    Gurus,
    what i need to find out is which tablespace tends to demand the most space,showing the growth pattern for each TS over the last week or so. determine the TS thatís grown the most and by how much over the last couple of weeks.How can i generate such a report?

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    as far as I know there is no such reports exists. you need to maintain spread sheet with growth rates every week.

    However, if you want to find the growth trend for a particular object based on the current load you can use the below procedure.

    select * from table(dbms_space.OBJECT_GROWTH_TREND ('SCHEMA','OBJECT NAME','OBJECT TYPE'));
    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    How accurate is this. I have a table defined like this (see below).

    Today at 4PM, it had 10 million rows in it. I deleted approx to 91,000 rows and ran the report again and I still get the same numbers for 8/2 and 8/3 after the delete. At the minimum I would suspect that the space_usage should have decreased.

    What am I missing?

    TIMEPOINT
    ---------------------------------------------------------------------------
    SPACE_USAGE SPACE_ALLOC QUALITY
    ----------- ----------- --------------------
    03-AUG-10 04.19.36.858955 PM
    469388 3145728 INTERPOLATED

    04-AUG-10 04.19.36.858955 PM
    469388 3145728 PROJECTED


    CREATE TABLE aaa
    (a NUMBER(4),
    b NUMBER(4),
    c DATE)
    PARTITION BY RANGE(c)
    (
    PARTITION S2001 VALUES LESS THAN(TO_DATE('01/01/2001','DD/MM/YYYY')),
    PARTITION S2002 VALUES LESS THAN(TO_DATE('01/01/2002','DD/MM/YYYY')),
    PARTITION S2003 VALUES LESS THAN(TO_DATE('01/01/2003','DD/MM/YYYY')),
    PARTITION S2004 VALUES LESS THAN(TO_DATE('01/01/2004','DD/MM/YYYY')),
    PARTITION S2005 VALUES LESS THAN(TO_DATE('01/01/2005','DD/MM/YYYY')),
    PARTITION S2006 VALUES LESS THAN(TO_DATE('01/01/2006','DD/MM/YYYY')),
    PARTITION S2007 VALUES LESS THAN(TO_DATE('01/01/2007','DD/MM/YYYY')),
    PARTITION S2008 VALUES LESS THAN(TO_DATE('01/01/2008','DD/MM/YYYY')),
    PARTITION S2009 VALUES LESS THAN(TO_DATE('01/01/2009','DD/MM/YYYY')),
    PARTITION S2010 VALUES LESS THAN(TO_DATE('01/01/2010','DD/MM/YYYY')),
    PARTITION S2011 VALUES LESS THAN(TO_DATE('01/01/2011','DD/MM/YYYY')))

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    agree with you. did you run this immediately after delete?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width