Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2003-03-16
8
Medium Priority
?
2,880 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
  • 5
7 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
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
Suggested Courses

564 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