actual blocks used on TS

[reeshe]

Hi friends,

I see scripts posted here before, which computes the actual blocks/bytes used per tablespace or table. I can not find it anymore because I forgot the key-word to search?
For example: I have USERS tablespace with size 2GB and it is already 100% used
but the tables inside it is not really filled-up. How to i get the actual used space and the unused one?

Can you give me the link to it pls.

Thanks a lot

[PAVB]

You can query DBA_DATA_FILES, DBA_TABLESPACES, DBA_EXTENTS

[reeshe]

thanks

you forgot the scripts too

[PAVB]

you forgot the scripts too

Nope, I didn't... I'm just not doing your job.

Don't be lazy, even my parrot can do it.

[reeshe]

Thanks anyway for the clue

But I think it is not the one i wanted ..it is like this:

Say, i have USERS tablepace 2Gb size, then I create a table with pre-allocated space of 2Gb.
so the TS is 100% used, but the table has no data yet. It will report something like 0 blocks used and 100% blocks free?
or near to that....something like that

Any other clue dear?

Thanks

[PAVB]

So you asked about the breed of your dog when you actually wanted to know the color of your cat?

If what you really want is to learn how dense a table is, gather fresh stats on your offending table and query DBA_TABLES.

[reeshe]

Hi..thanks, that is clever of u im just testing you if u know

I think this is easier?

Say I have a 100Gb database and when i exported it, it is only 10Gb.
Can I approx. say only 10% space is actually used and 90% is free? ( less the indexes , temp, redo)


Thanks

[PAVB]

Nope.

[reeshe]

Thanks

If i analyze all the tables, which columns tells me the density of the table?
is it BLOCKS+EMPTY_BLOCKS?


Thanks

[PAVB]

BLOCKS tells you how many blocks are allocated for the specific table.
EMPTY_BLOCKS tells you how many full-blocks you have empty.

Please note neither one of these columns is telling you how much free space you have on the used blocks.

NUM_ROWS * AVG_ROW_LEN tells you how much space your raw data is using but you may want to take into consideration PCT_USED/PCT_FREE values to get a more realistic figure.

Last but not least, for partitioned tables you may want to query DBA_TAB_PARTITIONS instead of DBA_TABLES.