Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments
COURSE of the MONTH
8 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
how to calculate disc space occupied by a table
Posted on 2004-08-02
what is the easy way to calculate the disc space occupied by a table in oracle
[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
2- +1
5 Comments
select segment_name as table_name, sum(bytes)
from user_segments
where segment_type = 'TABLE'
group by segment_name;
from user_segments
where segment_type = 'TABLE'
group by segment_name;
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???
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???
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 ...
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 ...
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
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
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('Segm
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,'
Segment Name= TABLE1
Free Blocks....................
Total Blocks....................
Total Bytes.....................
Total MBytes....................
Unused Blocks....................
Unused Bytes.....................
Last Used Ext FileId....................
Last Used Ext BlockId...................
Last Used Block.....................
PL/SQL procedure successfully completed.
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('Segm
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,'
Segment Name= TABLE1
Free Blocks....................
Total Blocks....................
Total Bytes.....................
Total MBytes....................
Unused Blocks....................
Unused Bytes.....................
Last Used Ext FileId....................
Last Used Ext BlockId...................
Last Used Block.....................
PL/SQL procedure successfully completed.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Configuring and using Oracle Database Gateway for ODBC
Introduction
First, a brief summary of what a Database Gateway is. A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
- Acronis
- Databases
- Oracle Database
- MongoDB
- Oracle
- *Backup Software
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month8 days, 21 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.