Home » RDBMS Server » Server Administration » Tablespace not reclaiming space after deletion
Tablespace not reclaiming space after deletion [message #56844] Wed, 30 April 2003 04:32 Go to next message
Anu
Messages: 82
Registered: May 2000
Member
I have a huge table containing 643000 records. This table and another 2 tables are in same tablespace. Now the tablespace is 99% full. I deleted around 200000 records from this table. After deletion also this tablespace shows 99% full. How can I claim the deleted records space in this tablespace

Thanks in advance

Anu
Re: Tablespace not reclaiming space after deletion [message #56846 is a reply to message #56844] Wed, 30 April 2003 05:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
deleting the rows will not reset the highwater mark.
u have to truncate the table
or
move it ( within the same tablespace)

mag@itloaner1_local > select table_Name,tablespace_name from user_tables where table_name='EMP';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            SYSTEM

mag@itloaner1_local > alter table emp move tablespace system;

Table altered.

----------------------------------------------------------------------

mag@itloaner1_local > create table backup_emp as select * from emp;

Table created.

mag@itloaner1_local > truncate table emp;

Table truncated.

mag@itloaner1_local > ed
Wrote file afiedt.buf

  1* insert into emp (select * from backup_emp)
mag@itloaner1_local > /

14 rows created.

mag@itloaner1_local > commit;

Commit complete.

mag@itloaner1_local > 

Re: Tablespace not reclaiming space after deletion [message #56865 is a reply to message #56846] Thu, 01 May 2003 03:06 Go to previous messageGo to next message
Anu
Messages: 82
Registered: May 2000
Member
Thanks mahesh for your reply.

I am using oracle 7.3.3. There is no alter table move option inthis version. What about the following steps.

1. Export the table
2. truncate the table
3. import the dump file

will it work?

what about alter tablespace <> colasce option.

waiting for your reply.

Thanks in advance

Regards
Anu
Re: Tablespace not reclaiming space after deletion [message #56868 is a reply to message #56865] Thu, 01 May 2003 06:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
exporting and importing again with compress=y will help.
if you can afford some space in the same or any other tablespace, the easier method would
1. create table_new as select * from table_old;
2. truncate table_old;
3. insert into table_old ( select * from table_new);
4. drop table table_new;

Re: Tablespace not reclaiming space after deletion [message #56896 is a reply to message #56868] Sat, 03 May 2003 23:41 Go to previous message
Anu
Messages: 82
Registered: May 2000
Member
Thanks Mahesh,

I will try and let you know.

Regards,

Anu
Previous Topic: Upgrade from 8.1.7.2 to 8.1.7.4
Next Topic: recover
Goto Forum:
  


Current Time: Fri Sep 20 09:22:30 CDT 2024