-
Table not giving up space problem
9.2.0.4
After computing statistics, a table has
19,538 "BLOCKS"
0 "NUM_ROWS"
70 "EMPTY_BLOCKS"
I tried "alter tablespace aq_1 coalesce", reanalyze .. no love.
I've got 1 3gb datafile and I want to shrink it, there's no data in it.
Any advice would be greatly apprciated.
-
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Since you want to shrink the size of your datafile, I would:
1. alter table move to a new ts
2. resize your existing ts
3. alter table move back to original ts
Jeff Hunter
-
If you do not want to move table. We can try EXP/IMP that table
Raghu
-
Excellent suggestions. Thank you all.
-
created tablespace aq_2 to move tables from aq_1
alter table el_webqueue move tablespace aq_2
ORA-00054: resource busy and acquire with NOWAIT specified
I know it's busy and I can't do it online (like an index)
What's keeping it from deallocating that space?
-
Do you tried .. It may be easy ..Create new table in existing TS or new TS
Create table new_tab as select * from old_tab nologging;
Drop old table
Rename new table to old
Raghu
-
Originally posted by KenEwald
created tablespace aq_2 to move tables from aq_1
alter table el_webqueue move tablespace aq_2
ORA-00054: resource busy and acquire with NOWAIT specified
I know it's busy and I can't do it online (like an index)
What's keeping it from deallocating that space?
Does a process have the table locked?
Is there a large uncomitted dml transaction on that table?
As you know either commiting or rolling back that session should unlock the table.
-
It's an advanced queue - queue table.
Messages are being consumed constantly from that queue table.
Looks like I need to stop the subscribers to the queue then make the move.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|