-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
thanks
you forgot the scripts too
-
Originally Posted by reeshe
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
Last edited by reeshe; 05-27-2008 at 03:59 AM.
-
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks
If i analyze all the tables, which columns tells me the density of the table?
is it BLOCKS+EMPTY_BLOCKS?
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.
Last edited by PAVB; 05-27-2008 at 06:23 AM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|