DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How much data actually in the table?

  1. #1
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407

    How much data actually in the table?

    Hi,

    I am sure this question has been visited several times in this forum but couldn't find old thread.

    We have a 3rd party application that is growing the database quite rapidly. They ran their purge routine and I am not convinced if it actually did anything.
    I don't know what tables it purges so I can't query those tables directly.

    My fellow dba thinks that dba_free_space is telling us the current free space in the tablespace while I was telling him that it only tells us the free extents and not how densely populated they are (so even if it deleted 50% of the records, it will still show the same free space if the deletes were spanned across all the extents). I don't think we can find the actual data sapce without an export/import.

    However I was thinking if using average_row_size and number of rows will tell me what I want.

    Your suggestions please?

    Thanks,
    -Rajeev
    Rajeev Suri

  2. #2
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Not sure how using average_row_size and number of rows is going to help if you dont know the tables involved or dont have the stats from the tables before the purge?

  3. #3
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    You can run this script before and after your purge and you should be able to find what tables are getting hit. You could even customize it to hit a particular schema to narrow your search down.

    Code:
    select  substr(segment_name,1,15) NAME,substr(segment_type,1,7) TYPE,  
    substr(tablespace_name,1,10) AS TABLESPACE ,  
    round(sum(bytes)/1024/1024,2) Mb ,  
    sum(decode(extent_id, 0, bytes, 0))/1024 initial_ex,  
    sum(decode(extent_id, 1, bytes, 0))/1024 next_ex ,  
    max(extent_id)+1 extents ,  sum(bytes)/1024 ttlsize
      from dba_extents
     Where segment_type='TABLE'
    and extent_id>'30'
    group by  segment_name, segment_type,  tablespace_name
     order by 7 ASC;
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  4. #4
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Fraze,
    Thanks for pointing that...actually I know just a couple of big tables out of several hundred out there that I may go do a query now (I don't know how many records were there though before the purge)

    OracleDoc,
    I am not sure how this query will tell me anything useful?
    If the records were evenly spread then their is a fair chance that each of those extents will still be present after the purge.

    I am looking for something that takes into account the density of the extents and not the number.

    Thanks,
    -Rajeev
    Rajeev Suri

  5. #5
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    What good will that statement do? Just by deleting rows from a table you dont get any extents back. You can only free extents by truncating the table or using deallocate unused to free extents over the high water mark

  6. #6
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    my mistake guys sorry about that. For some reason I thought that if you ran that script before your purge you could look at the megs column and see how big the table was then run it again after to see the difference. But, such is not the case. Because as you said, it won't free up the extents.

    I'll go back under my rock now
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  7. #7
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    one thing I did found out is that there are lobs used in one of the table and looking at Quest's Schema Manager I see that that Blob (that resides inside the tablespace) is taking about 2GB

    So, I am guessing that the purge routine (written by webMethods application) is not cleaning the Lob on deletes.
    Another possibility is that the purge is deleting the records but Lob is not shrinking; any way I can compress the LOB?

    Thanks,
    -Rajeev
    Rajeev Suri

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