DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: actual blocks used on TS

  1. #1
    Join Date
    Oct 2007
    Posts
    114

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  3. #3
    Join Date
    Oct 2007
    Posts
    114
    thanks

    you forgot the scripts too

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote 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.

  5. #5
    Join Date
    Oct 2007
    Posts
    114
    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

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  7. #7
    Join Date
    Oct 2007
    Posts
    114
    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.

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Nope.
    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.

  9. #9
    Join Date
    Oct 2007
    Posts
    114
    Thanks

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


    Thanks

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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
  •  


Click Here to Expand Forum to Full Width