-
Hi all,
Oracle 8.1.7.3.0 on Win2000.
My TEMP tablespace is created as LMT, AUTOEXTEND ON.
Its size somehow reached to 30GB.
So, I decided to drop and recreate it.
I have issued the
drop tablespace TEMP including contents;
But it is taking long time to complete the command.
I heared that if it is a LMT, it drops real quick.
It also does not show anything in fet$ for TEMP tablespace.
What should I do?
Thanks in Adv.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
FYI LMT is locally managed so obviously you cant see anything in data dictionary such as fet$
-
The session that attempted to drop true temporary tablespace with active users waits for uncommited transactions that use the tablespace to finish. Other transactions that wish to use the tablespace will wait to the "drop tablespace" command and then fail.
Originally posted by samdba
What should I do?
You should create another tempfile first:
alter tablespace TEMP add tempfile '...' ...;
Then put the old tempfile offline:
alter database tempfile '...' offline;
This makes all temporary objects (tables, LOBs, sorts) unavailable. Active users may lose their work so it's good to check if there are any (v$sort_usage, v$temporary_lobs) before offlining the tempfile.
New temporary segments are created in the new tempfile.
Then you can try to drop the old tempfile:
alter database tempfile '...' drop;
If there are any uncommited transactions in the TEMP tablespace, "ORA-25152: TEMPFILE cannot be dropped at this time" occurs. Wait and try again. After dropping the tempfile you can remove it from disk.
Ales The whole difference between a little boy and an adult man is the price of toys
-
Hi,
After I disconnected/killed connected sessions, DROP tablespace TEMP was successful.
Thanks for your suggestions.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
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
|