OraFAQ Forum: Server Administration » Table is consuming un usual amount of space

Home » RDBMS Server » Server Administration » Table is consuming un usual amount of space (Oracle 11g R1)

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

Table is consuming un usual amount of space [message #532226]

Mon, 21 November 2011 10:40

shrinika
Messages: 306
Registered: April 2008

Senior Member

I have table which has 240 columns.

Here is the list of column data type.

 VARCHAR2(50)
 TIMESTAMP(6)
 VARCHAR2(25)
 VARCHAR2(25)
 NUMBER(15,2)
 VARCHAR2(50)
 NUMBER(1)
 NUMBER(1)
 NUMBER(1)
 NUMBER(1)
 NUMBER(1)
 NUMBER(2)
 NUMBER(1)
 NUMBER(1)
 VARCHAR2(25)
 VARCHAR2(50)
 NUMBER(9)
 NUMBER(1)
 VARCHAR2(20)
 TIMESTAMP(6)
 VARCHAR2(25)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(25)
 VARCHAR2(50)
 VARCHAR2(25)
 VARCHAR2(25)
 VARCHAR2(25)
 NUMBER(15,2)
 NUMBER(15,2)
 VARCHAR2(25)
 VARCHAR2(25)
 VARCHAR2(25)
 VARCHAR2(10)
 VARCHAR2(50)
 NUMBER(15,2)
 DATE
 DATE
 NUMBER(1)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(25)
 NUMBER(1)
 NUMBER(15,2)
 NUMBER(15,2)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(4000)
 NUMBER(1)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 DATE
 DATE
 NUMBER(15,2)
 NUMBER(15,2)
 NUMBER(15,2)
 NUMBER(15,2)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 DATE
 DATE
 NUMBER(15,2)
 NUMBER(15,2)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 DATE
 DATE
 NUMBER(15,2)
 NUMBER(15,2)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 DATE
 DATE
 NUMBER(15,2)
 NUMBER(15,2)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 DATE
 DATE
 NUMBER(15,2)
 NUMBER(15,2)
 NUMBER(15,2)
 NUMBER(15,2)
 VARCHAR2(255)
 VARCHAR2(255)
 VARCHAR2(255)
 VARCHAR2(255)
 VARCHAR2(255)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 DATE
 NUMBER(15,2)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(50)
 NUMBER(2)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(10)
 NUMBER(15,2)
 VARCHAR2(50)
 VARCHAR2(50)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(12)
 VARCHAR2(4000)
 NUMBER(8)
 VARCHAR2(4000)
 VARCHAR2(4000)
 VARCHAR2(4000)
 VARCHAR2(4000)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(10)
 VARCHAR2(50)
 TIMESTAMP(6)
 VARCHAR2(4000)
 CHAR(1)
 VARCHAR2(900)
 VARCHAR2(50)
 NUMBER(8)
 VARCHAR2(4000)
 CHAR(1)
 VARCHAR2(50)
 VARCHAR2(50)
 NUMBER(1)
 NUMBER(1)
 NUMBER(1)
 NUMBER(1)
 DATE

The table has 64 partition..
When i count the table(select count(*) from table),
i see close to 22 million records.

SQL> select (num_rows*avg_row_len)/1024/1024/1024 GB,num_rows,avg_row_len
2 from dba_tables
3 where table_name='TRX_TAB';

GB NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------
74.9393936 21871585 3679

SQL>

It is supposed to take 75 GB.
But this table is consuming 135 GB now. It is
occuping 8 GB per day.

What could be the reason? any clue?

Report message to a moderator

Re: Table is consuming un usual amount of space [message #532227 is a reply to message #532226]

Mon, 21 November 2011 10:45

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

1) You know how to use code tags, so use them - the query and result should be in them.
2) How are you measuring how much space the table is taking?

Report message to a moderator

Re: Table is consuming un usual amount of space [message #532228 is a reply to message #532226]

Mon, 21 November 2011 10:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

select bytes,(1024*1024*1024) TOTAL,LAST_ANALYZED, (num_rows*avg_row_len)/1024/1024/1024 GB,num_rows,avg_row_len
from dba_tables
where table_name='TRX_TAB';

Post results from above

Report message to a moderator

Re: Table is consuming un usual amount of space [message #532229 is a reply to message #532226]

Mon, 21 November 2011 10:49

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

I would guess that the table blocks are not full (you'll see this if you look at DBA_TABLES.AVG_SPACE, assuming the statistics are up to date) and that the tablespace is managed with free lists, not with Automatic Segment Space Management.
Do you know how to check that?

Report message to a moderator

Re: Table is consuming un usual amount of space [message #532234 is a reply to message #532226]

Mon, 21 November 2011 11:13

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

Senior Member
Account Moderator

Quote:

Here is the list of column data type.

A DISTINCT and ORDER BY on the column type should be welcome.

Use DBMS_SPACE_ADMIN to estimate the efficiency your table storage.

Regards
Michel

Report message to a moderator

Previous Topic:	Need White paper or asset on Oracle
Next Topic:	ASM ISSUES

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 17:24:53 CST 2025