actual blocks used on TS

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

You can query DBA_DATA_FILES, DBA_TABLESPACES, DBA_EXTENTS

thanks :)

you forgot the scripts too:p

Quote:

---

Originally Posted by reeshe

you forgot the scripts too:p

---

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

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

Thanks anyway for the clue ;)

But I think it is not the one i wanted:D ..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

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.

Hi..thanks, that is clever of u:D 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

Nope.

Thanks :)

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


Thanks

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.

dbms_space

Quote:

---

Originally Posted by pando

dbms_space

---

...as far as I remember it doesn't tells you how much free space you have in used blocks. Please correct me if I'm wrong.

well he wants to know how much space is allocated i.e HWM of a table so this should be enough

there isnt a correct way to find the free space inside a used block, things gets worse with ASSM

Code:

---


example with a 350000 rows table

BEFORE DELETE:
NOM_TABLA: LSC$SRC
-----------------------------
Free Blocks.............................              0
Total Blocks............................          14,976
Total Bytes.............................    122,683,392
Total MBytes............................            117
Unused Blocks...........................              5
Unused Bytes............................          40,960
Last Used Ext FileId....................              8
Last Used Ext BlockId...................          15,625
Last Used Block.........................            123
-----------------------------

AFTER DELETE:
NOM_TABLA: LSC$SRC
-----------------------------
Free Blocks.............................          14,970
Total Blocks............................          14,976
Total Bytes.............................    122,683,392
Total MBytes............................            117
Unused Blocks...........................              5
Unused Bytes............................          40,960
Last Used Ext FileId....................              8
Last Used Ext BlockId...................          15,625
Last Used Block.........................            123
-----------------------------

---

Quote:

---

Originally Posted by pando

well he wants to know how much space is allocated i.e HWM of a table so this should be enough

---

Mhhnnnn... my reading of the post is slightly different, poster is not looking for HWM, poster is looking for actual used space

Ok whatever dear ...i dont need a perfect output :D just an estimate/approximate one.
My boss is just asking plain black n white report:

"FREE" SPACE vs "USED" SPACE

When column/stats in the dba_tables are near representative of these two?:rolleyes:

By the way pando, how do u get that output? Why is there before delete/ after delete? When I am not yet deleting rows in my table



Thanks

Hi frenz,

I got this link that explains a little bit clearly:D

http://forums.oracle.com/forums/thre...484299&#484299

Quote:

---

Originally Posted by reeshe

I got this link that explains a little bit clearly:D

---

Thanks God! did you got it now?

Yeah:) I'ts just that your's and pando's explanation is quite ....hmmnn...what do u call it?....high-level:D i need the dummy-level :o


Thanks