tablespace question?? really mind bugging
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: tablespace question?? really mind bugging

  1. #1
    Join Date
    Aug 2000
    Location
    NY
    Posts
    37

    Post

    I have a table that I have partitioned into months(12 months). Each partition is in a separate tablespace. So I have 12 partitons and 12 tablespaces.

    I ran a query and the query tells me that 60% of the tablespace for march has been used.
    But when I select count(*) from march partiton, it did not return any rows(ZERO).Meaning there is no data in the march partition

    My question is, if the tablespace is supposed to contain only the partition for the month of march, why is it that the tablespace usage for march is 60% and when I select count(*) from march partitions it returns no rows.

    Can anyone explain.
    This is realy realy realy bugging my mind.

    THIS IS THE SCRIPT THAT TELLS ME THE TABLESPACE UTILIZATION:

    select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",round((f.free/a.total)*100) "% Free" from
    (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
    (select tablespace_name, round(sum(bytes/(1024*1024))) used from dba_extents group by tablespace_name) u,
    (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
    WHERE a.tablespace_name = f.tablespace_name
    and a.tablespace_name = u.tablespace_name


    can anybody explain what is going on here?



    regards,
    charity

  2. #2
    Join Date
    Oct 2000
    Posts
    80
    I couldn't decipher the query you posted, but offhand I'd suggest that data has been deleted from the tables in this tablespace. This would explain the behavior.
    Could you repost the query in more decipherable format?
    John Doyle

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    unless you truncate your march partition otherwise you will always see it has 60% used (if it was once occupied by 60% of data in tablespace)

  4. #4
    Join Date
    Nov 2000
    Posts
    344
    It is possible for a table to be empty, but the tablespace which contains it to be very full, even if there are no other objects in the tablespace.

    The reason is that when a table (partition, whatever, it doesn't matter) is created, space in the tablespace is allocated for it. It doesn't matter if you have rows yet or
    not. The amount of space allocated is defined by the INITIAL
    keyword in your CREATE TABLE sql stmt. When lots of rows
    are added and they finally take up all the room that has been allocated, then another chunck of space (called an EXTENT) will be added.

    The idea is that Oracle allocates space ahead of time for objects rather than take space as it needs it. This makes it much more likely that an object sits in one place on a disk, rather than scattered all over it.

  5. #5
    Join Date
    Jul 2000
    Posts
    296
    If you query USER_SEGMENTS you can see how many space the segment of the partition uses.

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