I have a cluster table. Which is growing unreasonably large. Even when I am deleting a significant number of records from the table, it is not freeing up space from the datafile. Only solution is Copying data to a temp table and truncate the same. But this is taking a hell lot of time.
So, I tried to move my CLUSTER table to a new tablespace. But oracle does not allow with ALTER TABLE move ..... command.
Can you suggest me any other way to do the same!!!
1. I am using a Index Cluster
2. Yes, might be you are right. The problem might be with the size. I am pasting the storage here. Pls advise me. The current size of the table is around half tera bytes. And perday inserts is around 4-5 GB.
initial extent: 1M
Next Extent: 1M
Min Extents: 1
Max extents 2147483645
3. Only one table in the cluster.
4. I can not reclaim the deleted space!! (I tried Alter cluster deallocate unused!! it is also not working!!)
It is a production problem and hence anxiously looking forward to your reply.
No, it is!! Is it good or has negative impact? If negative why?
It is hard to determine the SIZE value in the cluster unless you know the total number of rows.
Approx 2900 Million records - which is allmost 560GB actual segment size.
If you want still to use cluster, then create a temp table in a sorted order of the cluster key, then re-insert them into cluster agian with suitable SIZE vlaue.
presently, there is a back log of old data. First I would like to purge those. The issue is
1. Export is hanging!! (Any solution)
2. Trunacte is taking unreasonably long time. (Any suggestion to make it fast)
3. Doubt of fragmentation too.