Home » RDBMS Server » Server Administration » How to know the real size of a table (merged 7)
How to know the real size of a table (merged 7) [message #406708] Fri, 05 June 2009 08:19 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at DBMS_SPACE.UNUSED_SPACE procedure.

Regards
Michel
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 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
Sorry I have pushed only once on the button "create topic"
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check SPACE_USAGE procedure just above.

Regards
Michel
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or use dbms_stats to gather statistics and query user_tables.

Regards
Michel
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 Go to previous messageGo to next message
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 ?
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 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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.
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 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
ok but how to know the space free in a block not empty ??
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 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
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:
  


Current Time: Thu Dec 08 00:04:25 CST 2016

Total time taken to generate the page: 0.05556 seconds