Home » RDBMS Server » Server Administration » Table is consuming un usual amount of space (Oracle 11g R1)
Table is consuming un usual amount of space [message #532226] Mon, 21 November 2011 10:40 Go to next message
shrinika
Messages: 231
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?




Re: Table is consuming un usual amount of space [message #532227 is a reply to message #532226] Mon, 21 November 2011 10:45 Go to previous messageGo to next message
cookiemonster
Messages: 10860
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?
Re: Table is consuming un usual amount of space [message #532228 is a reply to message #532226] Mon, 21 November 2011 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
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
Re: Table is consuming un usual amount of space [message #532229 is a reply to message #532226] Mon, 21 November 2011 10:49 Go to previous messageGo to next message
John Watson
Messages: 4414
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?

Re: Table is consuming un usual amount of space [message #532234 is a reply to message #532226] Mon, 21 November 2011 11:13 Go to previous message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Need White paper or asset on Oracle
Next Topic: ASM ISSUES
Goto Forum:
  


Current Time: Fri Aug 01 09:01:08 CDT 2014

Total time taken to generate the page: 0.09678 seconds