-
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
-
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?
-
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)
-
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.
-
If you query USER_SEGMENTS you can see how many space the segment of the partition uses.
