RE: sizeiof table!!!

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 19 Sep 2001 07:47:09 -0700
Message-ID: <F001.00392A84.20010919080032@fatcity.com>

Rick & Lex,

Have you tried the:

DBMS_SPACE.unused_space package?

It gives used and unused number of bytes and blocks for an item. Below is the syntax:

  PROCEDURE unused_space(segment_owner IN VARCHAR2, 
                         segment_name IN VARCHAR2,
                         segment_type IN VARCHAR2,
                         total_blocks OUT NUMBER,
                         total_bytes OUT NUMBER,
                         unused_blocks OUT NUMBER,
                         unused_bytes OUT NUMBER,
                         last_used_extent_file_id OUT NUMBER,
                         last_used_extent_block_id OUT NUMBER,
                         last_used_block OUT NUMBER,
                         partition_name IN VARCHAR2 DEFAULT NULL
                         );

PRAGMA RESTRICT_REFERENCES(unused_space,WNDS);

Returns information about unused space in an object (table, index,
or cluster).
Input arguments:
segment_owner
schema name of the segment to be analyzed
segment_name
object name of the segment to be analyzed
partition_name
partition name of the segment to be analyzed
segment_type
type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
Output arguments:
total_blocks
total number of blocks in the segment
total_bytes
the same as above, expressed in bytes
unused_blocks
number of blocks which are not used
unused_bytes
the same as above, expressed in bytes
last_used_extent_file_id
the file ID of the last extent which contains data
last_used_extent_block_id
the block ID of the last extent which contains data
last_used_block
the last block within this extent which contains data

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, September 19, 2001 11:05 AM To: Multiple recipients of list ORACLE-L

Does anyone have a script that gives actual space used instead of size of extents allocated?

Thanks
Rick

-----Original Message-----
Sent: Wednesday, September 19, 2001 10:05 AM To: Multiple recipients of list ORACLE-L

Select sum(bytes) / 1048576 "Size in Mb" From dba_extents
Where owner = upper('&owner')
and segment_name = upper('&tablename');

"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863

-----Original Message-----
Sent: Tuesday, September 18, 2001 1:05 PM To: Multiple recipients of list ORACLE-L

Hi gurus,
I need get the size in bytes of any table!!!

thanks!!!

@lex

Lic. Alexander Ordóñez Arroyo

  Caja Costarricense del Seguro Social                             
  Soporte Técnico - División de Informática                     
  Telefono: 295-2004, San José, Costa Rica                    
  Aordonez_at_ccss.sa.cr        Icq# 30173325

------------------------------------------------------------

The true is out there in WWW

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alexander Ordonez
  INET: aordonez_at_ccss.sa.cr

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cale, Rick T (Richard)
  INET: RICHARD.T.CALE_at_saic.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Sep 19 2001 - 09:47:09 CDT