Home » RDBMS Server » Server Administration » Unable to release space from table
Unable to release space from table [message #234328] Mon, 30 April 2007 21:08 Go to next message
nneog
Messages: 6
Registered: April 2007
Location: Duliajan
Junior Member
Hi all,

We are unable to release space from a table called TST03 even after deletion of records.

Followings informations are for you.
Database : 9.2
Table Name : TST03
Tablespace : LOCALY MANAGED.
Previously there were lots of rows.
Now only 9 No of Rows are there.
Space allocated : 41 GB
PCT_INCREASE : Null
One of the column is of LONG RAW type.
In fact this is SAP Table.

Since the table is in LMTS, we were expecting that the space allocated will be released automaticaly after deletion of records.

Now, what are the option left with us to release 41GB of space ?
a. Is there any effect of "Drop storage" option of 'Truncate table' command ?
b. If yes , can i copy all the 9 rows to a new table, then use "Truncate table TST03 drop storage", check if space is released and then copy back all the 9 rows to this table.
c. Do you have any other easy solution apart from export/import ?

Hope I could give you clear picture.

Thanks .
Naba J Neog
Re: Unable to release space from table [message #234329 is a reply to message #234328] Mon, 30 April 2007 21:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Search the forum for HWM.
Delete will not release space.
Truncate will.
do an
alter table mytable move tablespace same_tablespace_as_it_was_before;

Rebuild your indexes and dependents.
Re: Unable to release space from table [message #234330 is a reply to message #234328] Mon, 30 April 2007 21:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Meanwhile, I re-read your question.
>>Now, what are the option left with us to release 41GB of space ?
You mean shrinking the datafile/tablespace?
Re: Unable to release space from table [message #234331 is a reply to message #234330] Mon, 30 April 2007 21:30 Go to previous messageGo to next message
nneog
Messages: 6
Registered: April 2007
Location: Duliajan
Junior Member
Yes .
What are the option left to get back 41GB space.

Thanks.
Naba
Re: Unable to release space from table [message #234332 is a reply to message #234331] Mon, 30 April 2007 22:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
10g might have some interesting options for you.
In 9i, YOu need to resize the datafile.
http://orafaq.com/faqdba.htm#FILERESIZE
Re: Unable to release space from table [message #234337 is a reply to message #234332] Mon, 30 April 2007 23:39 Go to previous messageGo to next message
nneog
Messages: 6
Registered: April 2007
Location: Duliajan
Junior Member
Hi Mr. Mahesh Rajendran,

I am sorry I could not explain my problem properly.

I would like to reclaim space of this table(below HWM) to the tablespace.

Will Resizing of datafile help ?

Thanks & Regards.

Naba J Neog.
Re: Unable to release space from table [message #234347 is a reply to message #234337] Tue, 01 May 2007 00:44 Go to previous messageGo to next message
nneog
Messages: 6
Registered: April 2007
Location: Duliajan
Junior Member
Hi Mr. Mahesh Rajendran,

I am sorry I could not explain my problem properly.

My query was
1. How can I reclaim space of this table(below HWM) to the tablespace, so that other growing table can make use of it.

2. Will the command :-
alter table move same_tablespace_as_it_was_before
help reclaim space. Also please note that this table has LONG RAW coloumn.

Thanks & Regards.

Naba J Neog.
Re: Unable to release space from table [message #234351 is a reply to message #234347] Tue, 01 May 2007 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you use ASSM: alter table shrink space
If you use MSSM: alter table move

Regards
Michel
Re: Unable to release space from table [message #234358 is a reply to message #234351] Tue, 01 May 2007 02:36 Go to previous messageGo to next message
nneog
Messages: 6
Registered: April 2007
Location: Duliajan
Junior Member
Hi Michel Cadot,

Thanks for the reply.

Command "Alter table TST03 move" is not possible for this table because it contains a LONG RAW column.

Would please specify exactly how you wanted to do.

Naba J Neog.
Re: Unable to release space from table [message #234360 is a reply to message #234358] Tue, 01 May 2007 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You choose to keep LONG RAW.
You're stick with old possible actions.
You can't do anything but export/truncate/import or ctas/truncate/insert back.

Regards
Michel
Re: Unable to release space from table [message #234361 is a reply to message #234360] Tue, 01 May 2007 03:09 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

hi,
try this..


Suppose your table name in ori_table.
1) Create table test_table as select * from ori_table;

2) Drop that original table ori_table

3) Rename test_table to ori_table using alter table command..

Regards...

Dipali
Re: Unable to release space from table [message #234365 is a reply to message #234361] Tue, 01 May 2007 03:15 Go to previous messageGo to next message
nneog
Messages: 6
Registered: April 2007
Location: Duliajan
Junior Member
Hi Dipali,

Create table test_table as select * from ori_table
is not possible since the table contains LONG RAW column.

Thanks.

Naba J Neog
Re: Unable to release space from table [message #234374 is a reply to message #234365] Tue, 01 May 2007 05:03 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So: You can't do anything but export/truncate/import

Regard
Michel
Previous Topic: LONG to CLOB
Next Topic: Database crash
Goto Forum:
  


Current Time: Sat Dec 10 10:45:11 CST 2016

Total time taken to generate the page: 0.15318 seconds