Solved

how to calculate disc space occupied by a table

Posted on 2004-08-02
5
2,100 Views
Last Modified: 2008-02-01
what is the easy way to calculate the disc space occupied by a table in oracle
0
Comment
Question by:oraram9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 15

Expert Comment

by:ishando
ID: 11699327
select segment_name as table_name, sum(bytes)
from user_segments
where segment_type = 'TABLE'
group by segment_name;
0
 

Author Comment

by:oraram9
ID: 11699509
ishando,
do the above query retrieve  the space allocated to that table or space occupied by the table data on the disc at this point of time???
0
 
LVL 15

Expert Comment

by:ishando
ID: 11700156
It is the space allocated to the table.

Not sure that you can easily see how much space is free in the allocated space.
I believe you can you dba|user_free_space to find the blocks/bytes that are above the high water mark, but there isn't a way to see how much space there is below it.
Though its been a while since I looked at this and I don't have the scripts I came up with with me ...
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 11700571
hi

the query given by ishando is the space occupied by the table in the tablespace which is there in ur hard disk. so try to get the total space occupied in a given segment and then minus it from the total tablespace size. that should give u the free space and also the space occupied in a tablespace.

select sum(bytes)
from user_segments
where tablespace_name = <tablespace name>;

get the size of the tablespace by searching for the dbf file
subtract the value ie total tablespace size - sum(bytes) will give u the free space and sum(bytes) gives u the space occupied in the tablespace

regards
annamalai
0
 
LVL 13

Accepted Solution

by:
riazpk earned 50 total points
ID: 11702280
My preferred way to do this:


CREATE OR REPLACE  PROCEDURE SHOW_SPACE      
    ( p_segname in varchar2,
      p_owner   in varchar2 default user,
      p_type    in varchar2 default 'INDEX' )
    as
        l_free_blks                 number;
        l_total_blocks              number;
       l_total_bytes               number;
       l_unused_blocks             number;
       l_unused_bytes              number;
       l_LastUsedExtFileId         number;
       l_LastUsedExtBlockId        number;
       l_LAST_USED_BLOCK           number;
       procedure p( p_label in varchar2, p_num in number )
       is
       begin
           dbms_output.put_line( rpad(p_label,40,'.') ||
                                 p_num );
         
       end;
   begin
       dbms_space.free_blocks
       ( segment_owner     => p_owner,
         segment_name      => p_segname,
         segment_type      => p_type,
         freelist_group_id => 0,
         free_blks         => l_free_blks );
       dbms_space.unused_space
       ( segment_owner     => p_owner,
         segment_name      => p_segname,
         segment_type      => p_type,
         total_blocks      => l_total_blocks,
         total_bytes       => l_total_bytes,
         unused_blocks     => l_unused_blocks,
         unused_bytes      => l_unused_bytes,
         LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
         LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
         LAST_USED_BLOCK => l_LAST_USED_BLOCK );
       dbms_output.put_line('Segment Name=  '||p_segname);
       p( 'Free Blocks', l_free_blks );
       p( 'Total Blocks', l_total_blocks );
       p( 'Total Bytes', l_total_bytes );
       p( 'Total MBytes', l_total_bytes/1024/1024);
       p( 'Unused Blocks', l_unused_blocks );
       p( 'Unused Bytes', l_unused_bytes );
       p( 'Last Used Ext FileId', l_LastUsedExtFileId );
       p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
       p( 'Last Used Block', l_LAST_USED_BLOCK );
   end;
/

SQL> set serveroutput on
SQL> execute show_space('TABLE1',user,'TABLE');
Segment Name=  TABLE1
Free Blocks.............................4566
Total Blocks............................167808
Total Bytes.............................1374683136
Total MBytes............................1311
Unused Blocks...........................4962
Unused Bytes............................40648704
Last Used Ext FileId....................12
Last Used Ext BlockId...................138377
Last Used Block.........................3230

PL/SQL procedure successfully completed.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question