OraFAQ Forum: Server Administration » How to know the real size of a table (merged 7)

Home » RDBMS Server » Server Administration » How to know the real size of a table (merged 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to know the real size of a table (merged 7) [message #406708]

Fri, 05 June 2009 08:19

farenheiit
Messages: 31
Registered: June 2009
Location: Paris

Member

Hi,

To know the size of a table I execute the following query:

SELECT sum(bytes)/1024/1024/1024
FROM user_segments WHERE segment_name ='TOTO'

However, I'd want to know the size occupied by data without taking in account the free space on each block.

How can I do that?

[Updated on: Fri, 05 June 2009 08:44] by Moderator

Report message to a moderator

Re: How to know the real size of a table (merged 5) [message #406716 is a reply to message #406708]

Fri, 05 June 2009 08:35

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you have any problem when creating a topic, FIRST go to forum topic list to see if your topic and ONLY if it is not repost.

Regards
Michel

[Updated on: Fri, 05 June 2009 08:36]

Report message to a moderator

Re: How to know the real size of a table (merged 5) [message #406719 is a reply to message #406708]

Fri, 05 June 2009 08:40

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Have a look at DBMS_SPACE.UNUSED_SPACE procedure.

Regards
Michel

Report message to a moderator

Re: How to know the real size of a table (merged 5) [message #406720 is a reply to message #406708]

Fri, 05 June 2009 08:41

farenheiit
Messages: 31
Registered: June 2009
Location: Paris

Member

Sorry I have pushed only once on the button "create topic"

Report message to a moderator

Re: How to know the real size of a table (merged 5) [message #406725 is a reply to message #406719]

Fri, 05 June 2009 08:56

farenheiit
Messages: 31
Registered: June 2009
Location: Paris

Member

Michel Cadot wrote on Fri, 05 June 2009 08:40

Have a look at DBMS_SPACE.UNUSED_SPACE procedure.

Regards
Michel

OK this procedure give me the unused bytes above the HWM but not the unused bytes under in each block under the HWM

Report message to a moderator

Re: How to know the real size of a table (merged 5) [message #406726 is a reply to message #406725]

Fri, 05 June 2009 09:08

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Check SPACE_USAGE procedure just above.

Regards
Michel

Report message to a moderator

Re: How to know the real size of a table (merged 5) [message #406727 is a reply to message #406725]

Fri, 05 June 2009 09:08

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Or use dbms_stats to gather statistics and query user_tables.

Regards
Michel

Report message to a moderator

Re: How to know the real size of a table (merged 5) [message #406735 is a reply to message #406727]

Fri, 05 June 2009 09:52

farenheiit
Messages: 31
Registered: June 2009
Location: Paris

Member

Michel Cadot wrote on Fri, 05 June 2009 09:08

Or use dbms_stats to gather statistics and query user_tables.

Regards
Michel

How do you see that on user_tables ?

Report message to a moderator

Re: How to know the real size of a table (merged 7) [message #406736 is a reply to message #406708]

Fri, 05 June 2009 09:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> desc  user_tables
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME				   NOT NULL VARCHAR2(30)
 TABLESPACE_NAME				    VARCHAR2(30)
 CLUSTER_NAME					    VARCHAR2(30)
 IOT_NAME					    VARCHAR2(30)
 STATUS 					    VARCHAR2(8)
 PCT_FREE					    NUMBER
 PCT_USED					    NUMBER
 INI_TRANS					    NUMBER
 MAX_TRANS					    NUMBER
 INITIAL_EXTENT 				    NUMBER
 NEXT_EXTENT					    NUMBER
 MIN_EXTENTS					    NUMBER
 MAX_EXTENTS					    NUMBER
 PCT_INCREASE					    NUMBER
 FREELISTS					    NUMBER
 FREELIST_GROUPS				    NUMBER
 LOGGING					    VARCHAR2(3)
 BACKED_UP					    VARCHAR2(1)
 NUM_ROWS					    NUMBER
 BLOCKS 					    NUMBER
 EMPTY_BLOCKS					    NUMBER
 AVG_SPACE					    NUMBER
 CHAIN_CNT					    NUMBER
 AVG_ROW_LEN					    NUMBER
 AVG_SPACE_FREELIST_BLOCKS			    NUMBER
 NUM_FREELIST_BLOCKS				    NUMBER
 DEGREE 					    VARCHAR2(40)
 INSTANCES					    VARCHAR2(40)
 CACHE						    VARCHAR2(20)
 TABLE_LOCK					    VARCHAR2(8)
 SAMPLE_SIZE					    NUMBER
 LAST_ANALYZED					    DATE
 PARTITIONED					    VARCHAR2(3)
 IOT_TYPE					    VARCHAR2(12)
 TEMPORARY					    VARCHAR2(1)
 SECONDARY					    VARCHAR2(1)
 NESTED 					    VARCHAR2(3)
 BUFFER_POOL					    VARCHAR2(7)
 ROW_MOVEMENT					    VARCHAR2(8)
 GLOBAL_STATS					    VARCHAR2(3)
 USER_STATS					    VARCHAR2(3)
 DURATION					    VARCHAR2(15)
 SKIP_CORRUPT					    VARCHAR2(8)
 MONITORING					    VARCHAR2(3)
 CLUSTER_OWNER					    VARCHAR2(30)
 DEPENDENCIES					    VARCHAR2(8)
 COMPRESSION					    VARCHAR2(8)
 DROPPED					    VARCHAR2(3)

I wonder what is contained in EMPTY_BLOCKS field.

Report message to a moderator

Re: How to know the real size of a table (merged 7) [message #406744 is a reply to message #406736]

Fri, 05 June 2009 10:20

farenheiit
Messages: 31
Registered: June 2009
Location: Paris

Member

ok but how to know the space free in a block not empty ??

Report message to a moderator

Re: How to know the real size of a table (merged 7) [message #406746 is a reply to message #406744]

Fri, 05 June 2009 10:34

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

how to know the space free in a block not empty

Find all the rows in the block, sum the size, subtract it to the block size minus Oracle overhead.

Regards
Michel

Report message to a moderator

Previous Topic:	How to reduce the size of a database ? (merged 4)
Next Topic:	How to use a specific database on a server with multiple oracle databases - 11g OEL (merged 5)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 24 08:19:01 CST 2025