Home » RDBMS Server » Server Administration » Small table consume too much tablespace (Oracle RAC 10g, 10.2.0.5, Windows 2003 R2 Ent x64)
Small table consume too much tablespace [message #610021] Sun, 16 March 2014 10:33 Go to next message
quangnd
Messages: 2
Registered: March 2014
Junior Member
Hi,
I have a small table like that
create table TRANSACTION_DETAIL
(
  transaction_detail_seq NUMBER(10) not null,
  user_name              VARCHAR2(50),
  host_name              VARCHAR2(50),
  host_address           VARCHAR2(50),
  description            VARCHAR2(200) not null,
  transaction_id         NUMBER(10) not null,
  inserted_date          DATE default sysdate not null
)
tablespace USERS

In that table I have only 12630 rows but it consume 1342177280 bytes (1.3 Gig) in USERS tablespace.
Please help.
Re: Small table consume too much tablespace [message #610022 is a reply to message #610021] Sun, 16 March 2014 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

alter table ... enable row movement;
alter table ... shrink space;

Re: Small table consume too much tablespace [message #610023 is a reply to message #610021] Sun, 16 March 2014 10:38 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide, if you have nt done so already.

You may need to use the ALTER TABLE...SHRINK SPACE command, http://docs.oracle.com/cd/E16655_01/server.121/e17636/schema.htm#ADMIN10161
Re: Small table consume too much tablespace [message #610024 is a reply to message #610021] Sun, 16 March 2014 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this Forum & THANKS for reading the Posting Guidelines.

I suspect that at some point in the past, TRANSACTION_DETAIL table contained many more rows which were subsequently deleted.
Is above correct.
Will this table collect many more rows in the future?

>In that table I have only 12630 rows but it consume 1342177280 bytes (1.3 Gig) in USERS tablespace.
why do you consider above to require any action on your part?
Do you suffer from Compulsive Tuning Disorder?
Re: Small table consume too much tablespace [message #610029 is a reply to message #610024] Sun, 16 March 2014 23:36 Go to previous messageGo to next message
quangnd
Messages: 2
Registered: March 2014
Junior Member
Hi guys,
Finally I found the problem: I exported data from a huge system with compress extent = Y and import it into test bed. After that I delete all rows from the tables. But consumed tablespace was the same.
I run the following command and the problem resolved:
ALTER TABLE  TRANSACTION_DETAIL MOVE TABLESPACE USERS STORAGE (INITIAL 64K NEXT 1M);

Of cause, I have to do the same with related indexes after that Smile
Thanks all,
Re: Small table consume too much tablespace [message #610030 is a reply to message #610029] Sun, 16 March 2014 23:40 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
we have met the enemy, and they is me,
Previous Topic: Pre-upgrade warning (11.2.0.1 to 11.2.0.4)
Next Topic: ASM with immediate shutdown
Goto Forum:
  


Current Time: Thu Mar 28 17:51:03 CDT 2024