?
Solved

How to find out the size in byte of a specific table

Posted on 2003-03-16
8
Medium Priority
?
2,875 Views
Last Modified: 2012-06-27
Hi i am looking for SQLplus command/s to find out for a specific table it's size in byte ,and it's remaining size.
0
Comment
Question by:nirma
[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
8 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 8146794
As most simple solution You can use smth like the following example from
http://www.oracle-base.com/DBA/DBAScript.asp?Monitoring\Show_Space.sql:
here's the code:
CREATE OR REPLACE
PROCEDURE show_space
( p_segname IN VARCHAR2,
  p_owner   IN VARCHAR2 DEFAULT user,
  p_type    IN VARCHAR2 DEFAULT 'TABLE' )
AS
  l_free_blks                 NUMBER;
  l_total_blocks              NUMBER;
  l_total_bytes               NUMBER;
  l_unused_blocks             NUMBER;
  l_unused_bytes              NUMBER;
  l_last_used_ext_file_id     NUMBER;
  l_last_used_ext_block_id        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_last_used_ext_file_id,
    last_used_extent_block_id => l_last_used_ext_block_id,
    last_used_block           => l_last_used_block );
 
  p( 'Free Blocks', l_free_blks );
  p( 'Total Blocks', l_total_blocks );
  p( 'Total Bytes', l_total_bytes );
  p( 'Unused Blocks', l_unused_blocks );
  p( 'Unused Bytes', l_unused_bytes );
  p( 'Last Used Ext FileId', l_last_used_ext_file_id );
  p( 'Last Used Ext BlockId', l_last_used_ext_block_id );
  p( 'Last Used Block', l_LAST_USED_BLOCK );
END;
/




To try something for  a specific schema schema :
SELECT t.table_name AS "Table Name",
       t.num_rows AS "Rows",
       t.avg_row_len AS "Avg Row Len",
       Trunc((t.blocks * p.value)/1024) AS "Size KB",
       t.last_analyzed AS "Last Analyzed"
FROM   dba_tables t,
       v$parameter p
WHERE t.owner = Decode(Upper('&1'), 'ALL', t.owner, Upper('&1'))
AND   p.name = 'db_block_size'
ORDER by t.table_name; with &1 - Your schema name

Hope it helpfull,the mostly is the site,use it!!!
0
 
LVL 21

Accepted Solution

by:
oleggold earned 1000 total points
ID: 8149743
I had adopted this to handle simple partitions also, this looks like this:
PROCEDURE show_space
( p_segname IN VARCHAR2,
  p_owner   IN VARCHAR2 DEFAULT user,
  p_type    IN VARCHAR2 DEFAULT 'TABLE' )
AS
  l_free_blks                 NUMBER;
  l_total_blocks              NUMBER;
  l_total_bytes               NUMBER;
  l_unused_blocks             NUMBER;
  l_unused_bytes              NUMBER;
  l_last_used_ext_file_id     NUMBER;
  l_last_used_ext_block_id        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
FOR I IN (SELECT DISTINCT PARTITION_NAME FROM DBA_PART_COL_STATISTICS WHERE TABLE_NAME=p_segname)
LOOP
  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,-- ! PARTITIONS ONLY
    PARTITION_NAME =>I.PARTITION_NAME );
  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_last_used_ext_file_id,
    last_used_extent_block_id => l_last_used_ext_block_id,
    last_used_block           => l_last_used_block ,
    PARTITION_NAME =>I.PARTITION_NAME );
  p( 'For PARTITION NAME'||I.PARTITION_NAME, l_free_blks );
  p( 'Free Blocks', l_free_blks );
  p( 'Total Blocks', l_total_blocks );
  p( 'Total Bytes', l_total_bytes );
  p( 'Unused Blocks', l_unused_blocks );
  p( 'Unused Bytes', l_unused_bytes );
  p( 'Last Used Ext FileId', l_last_used_ext_file_id );
  p( 'Last Used Ext BlockId', l_last_used_ext_block_id );
  p( 'Last Used Block', l_LAST_USED_BLOCK );
END LOOP;
END;
0
 
LVL 9

Expert Comment

by:konektor
ID: 8149921
select * from  sys.dba_segments where segment_name = '<table name>' - there u can see space occupied by table - not filled with data, but allocated. u can select * from  sys.dba_segments where segment_name = '<index name of your table>' to see how much space are occupied by table's indexes
if u want to see free space of table - select * from dba_tables - if there are calculated statisitcs on table all data are there.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:oleggold
ID: 8149944
I had adopted this to handle simple partitions also, this looks like this:

PROCEDURE show_space
( p_segname IN VARCHAR2,
  p_owner   IN VARCHAR2 DEFAULT user,
  p_type    IN VARCHAR2 DEFAULT 'TABLE' )
AS
  l_free_blks                 NUMBER;
  l_total_blocks              NUMBER;
  l_total_bytes               NUMBER;
  l_unused_blocks             NUMBER;
  l_unused_bytes              NUMBER;
  l_last_used_ext_file_id     NUMBER;
  l_last_used_ext_block_id        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
FOR I IN (SELECT DISTINCT PARTITION_NAME FROM DBA_PART_COL_STATISTICS WHERE TABLE_NAME=p_segname)
LOOP
  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,-- ! PARTITIONS ONLY
    PARTITION_NAME =>I.PARTITION_NAME );
  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_last_used_ext_file_id,
    last_used_extent_block_id => l_last_used_ext_block_id,
    last_used_block           => l_last_used_block ,
    PARTITION_NAME =>I.PARTITION_NAME );
  p( 'For PARTITION NAME  '||I.PARTITION_NAME||' (KB)', l_total_bytes/1024 );
  p( 'Free Blocks', l_free_blks );
  p( 'Total Blocks', l_total_blocks );
  p( 'Total Bytes', l_total_bytes );
  p( 'Unused Blocks', l_unused_blocks );
  p( 'Unused Bytes', l_unused_bytes );
  p( 'Last Used Ext FileId', l_last_used_ext_file_id );
  p( 'Last Used Ext BlockId', l_last_used_ext_block_id );
  p( 'Last Used Block', l_LAST_USED_BLOCK );
END LOOP;
END;
0
 
LVL 21

Expert Comment

by:oleggold
ID: 8149953
The last one was for displaying the partition size in KBs
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8152616
Konector gave you the simple answer to your first question:
"find out for a specific table it's size in bytes":

select bytes from sys.dba_segments where segment_name = '<table name>';

Or, if you are logged in as the schema owner:

select bytes from user_segments where segment_name = '<table name>';


What do you mean by "remaining size"?

Do you mean free space in allocated blocks for that table that have no data in them yet?

Do you mean free space in allocated blocks that had data, but now have space for more because some data has been deleted?

Or do you mean free space in the tablespace not yet allocated to this (or any) table, but available for this (or any) table?
0
 
LVL 21

Expert Comment

by:oleggold
ID: 10604249
In the procedure I supplied use p_segname  as a name for the particular table:

PROCEDURE show_space
( p_segname IN VARCHAR2,
  p_owner   IN VARCHAR2 DEFAULT user,
  p_type    IN VARCHAR2 DEFAULT 'TABLE' )
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup
Suggested Courses

770 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