I'd like to know the size occupied by each table and its details, such as the size of the index for each table, the size allocated, and the free space for each table of my database in Oracle.
The "size" of a table can mean many things. I'll cover the many ways that people use to determine the size of an object.
To see how much space is allocated for an object, issue the following query:
SELECT block,bytes FROM dba_segments WHERE owner='SCOTT' and segment_name='EMP';
In all of these examples, I'll use the SCOTT.EMP table, but you can easily rewrite these examples for your specific needs.
If you calculate statistics on the table, then you can use the average row length and the total number of rows as an estimate of the total space used.
ANALYZE TABLE scott.emp COMPUTE STATISTICS;
AS used_space FROM all_tables WHERE owner='SCOTT' and table_name='EMP';
You can use similar queries for indexes.
The DBMS_SPACE package can compute how much space is being used, particularly the SPACE_USAGE procedure of this package. For instance, to see the space used by one of your tables, you might used code like the following in SQL*Plus:
variable unf number; --unformatted blocks variable unfb number; --unformatted bytes variable fs1 number; --number of blocks with 0-25% free space variable fs1b number; --number of bytes with 0-25% free space variable fs2 number; --number of blocks with 25-50% free space variable fs2b number; --number of bytes with 25-50% free space variable fs3 number; --number of blocks with 50-75% free space variable fs3b number; --number of bytes with 25-50% free space variable fs4 number; --number of blocks with 75-100% free space variable fs4b number; --number of bytes with 25-50% free space variable full number; --number of full blocks variable fullb number; --number of full bytes begin dbms_space.space_usage('SCOTT','EMP','TABLE',
:unf, :unfb, :fs1, :fs1b, :fs2, :fs2b,
:fs3, :fs3b, :fs4, :fs4b,
:full, :fullb); end; / print unf ; print unfb ;
print fs4 ; print fs4b; print fs3 ; print fs3b; print fs2 ; print fs2b;
print fs1 ; print fs1b; print full; print fullb;
I've put comment besides the variable names to show their meaning.
It is important to note that the SPACE_USAGE procedure details the space used below the table's High Water Mark. Blocks above the HWM are not analyzed in this process.
The DBMS_SPACE.UNUSED_SPACE can be used to determine how space is not being used in the segment. An example of this procedure can be seen below:
variable totalblk number; --number of total blocks variable totalbyt number; --number of total bytes variable unusedblk number; --number of unused blocks variable unusedbyt number; --number of unused bytes variable lastextentfile number; --last used extent file id variable lastextentblk number; --last used exent block number variable lastusedblk number; --last used block begin dbms_space.unused_space('SCOTT','EMP','TABLE', :totalblk, :totalbyt, :unusedblk, :unusedbyt, :lastextentfile, :lastextentblk, :lastusedblk); end; / print totalblk; print totalbyt; print unusedblk; print unusedbyt; print lastextentfile; print lastextentblk; print lastusedblk;
By taking the total number of blocks and subtracting the unused blocks, you can get a good idea of how many blocks are being used. If you want to know the usage of those blocks, you'll have the use the SPACE_USAGE procedure discussed above.