OraFAQ Forum: Server Administration » Small table consume too much tablespace

Home » RDBMS Server » Server Administration » Small table consume too much tablespace (Oracle RAC 10g, 10.2.0.5, Windows 2003 R2 Ent x64)

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

Small table consume too much tablespace [message #610021]

Sun, 16 March 2014 10:33

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.

Report message to a moderator

Re: Small table consume too much tablespace [message #610022 is a reply to message #610021]

Sun, 16 March 2014 10:38

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

Senior Member
Account Moderator

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

Report message to a moderator

Re: Small table consume too much tablespace [message #610023 is a reply to message #610021]

Sun, 16 March 2014 10:38

John Watson
Messages: 8960
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

Report message to a moderator

Re: Small table consume too much tablespace [message #610024 is a reply to message #610021]

Sun, 16 March 2014 10:40

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?

Report message to a moderator

Re: Small table consume too much tablespace [message #610029 is a reply to message #610024]

Sun, 16 March 2014 23:36

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,

Report message to a moderator

Re: Small table consume too much tablespace [message #610030 is a reply to message #610029]

Sun, 16 March 2014 23:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

we have met the enemy, and they is me,

Report message to a moderator

Previous Topic:	Pre-upgrade warning (11.2.0.1 to 11.2.0.4)
Next Topic:	ASM with immediate shutdown

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 21:40:39 CST 2024