Re: How to determine size(storage) of a table with indexes?

Use something like the following to get the table with some sample rows in it then multiple it up to your requirements. It's best to do this empirically then use pencil and paper.

SET SERVEROUTPUT ON SIZE 10000 DECLARE

  totalBlocks             NUMBER;
  totalBytes              NUMBER;
  unusedBlocks            NUMBER;
  unusedBytes             NUMBER;
  lastUsedExtentFileID    NUMBER;
  lastUsedExtentBlockID   NUMBER;
  LastUsedBlock           NUMBER;

BEGIN
-- Calculating free space for an object

   dbms_space.unused_space(<SCHEMA>,

                           <TABLE NAME>,
                           'TABLE',
                           totalBlocks,
                           totalBytes,
                           unusedBlocks,
                           unusedBytes,
                           lastUsedExtentFileID,
                           lastUsedExtentBlockID,
                           lastUsedBlock);

• Output DBMS_OUTPUT.PUT_LINE('totalBlocks= '||TO_CHAR(totalBlocks) ); DBMS_OUTPUT.PUT_LINE('totalBytes= '||TO_CHAR(totalBytes) ); DBMS_OUTPUT.PUT_LINE('unusedBlocks= '||TO_CHAR(unusedBlocks) ); DBMS_OUTPUT.PUT_LINE('unusedBytes= '||TO_CHAR(unusedBytes) ); DBMS_OUTPUT.PUT_LINE('lastUsedExtentFileID= '||TO_CHAR(lastUsedExtentFileID) ); DBMS_OUTPUT.PUT_LINE('lastUsedExtentBlockID= '||TO_CHAR(lastUsedExtentBlockID) ); DBMS_OUTPUT.PUT_LINE('LastUsedBlock= '||TO_CHAR(LastUsedBlock) );

  DBMS_OUTPUT.PUT_LINE('Unused Mb=
'||TO_CHAR(unusedBytes/(1024*1024)));

  DBMS_OUTPUT.PUT_LINE('Used Mb= '||TO_CHAR( (totalBytes - unusedBytes)/(1024*1024)));

END;
/

Daniel.

On 6 Jun 2003 15:42:16 -0400, yijin_at_wam.umd.edu (Yi Jin) wrote:

>
>How do one determine the storage requirement of a table,
>together with the associated indexes?
>
>Thanks.
>
>YJ
Received on Mon Jun 09 2003 - 15:45:20 CDT