| How to reclaim the space from a 25 GB table [message #60685] |
Wed, 25 February 2004 15:49  |
kumarpk1008
Messages: 1
Registered: February 2004
|
Junior Member |
|
|
Hello everyone,
We are running Oracle 9.2 on AIX and I have the strange situation.
I have a table which normally will have only 3-4GB worth of data and due to problem this table has grown to a size of 25GB. We have managed to find the culprit program and removed the actual data from the table. But our issue is the 20GB space is locked to that table and we want to reclaim the space and make it available for the tablespace so that the other growing tables can make use of it.
I can hear you saying "Why can't you reorg the table or export table, recreate the objects and import the table back"
We can do but the problem is getting downtime on this system. With Oracle 9>, is there any new feature or facility which help us to do the same in somewhat easier way, without downttime ?? Some way of resetting the highwater mark??
Any clues, ideas, suggestions. Any help will be greatly appreciated.
Thanks and Regards Kumar
|
|
|
|
|
|
|
|
|
|
| Re: How to reclaim the space from a 25 GB table [message #60723 is a reply to message #60700] |
Sun, 29 February 2004 14:42  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Online keyword can only be used for non-partitioned index organized table , not for regular heap tables.
thiru@9.2.0:SQL>create table t3 (x int);
Table created.
thiru@9.2.0:SQL>alter table t3 move online;
alter table t3 move online
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
thiru@9.2.0:SQL>drop table t3;
Table dropped.
thiru@9.2.0:SQL>create table t3(x int primary key) organization index;
Table created.
thiru@9.2.0:SQL>alter table t3 move online;
Table altered.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
