| pikun |
Posted: Mar 01, 2008 04:51:13 AM |
Total Post: 17
Joined: Jan, 2008
|
I have set my undo-space in auto-extend mode. but in the end when its capturing huge capacity,, than .. I am not able to drop this Undo-tablespace even after restarting database as well as server.
To drop the old undo1 I am creating Undo-2(new Undo tablespace) after restarting database.
then also it is not permitting me to drop the undo-tablespace1. so i restarted my server.. but still no result..
the Procedure i attempted is given below--------
1)CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/kol/dbs/dbf/acestkol/undotbs05.dbf' SIZE 50M AUTOEXTEND OFF;
2)ALTER SYSTEM SET undo_tablespace = UNDOTBS2 ;
3)DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;
4)CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/kol/dbs/dbf/acestkol/undotbs01.dbf' SIZE 1024M AUTOEXTEND ON;
5)ALTER SYSTEM SET undo_tablespace = UNDOTBS1;
6)DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES ;
friends.!!!!! the 3rd query is not working by giving error like--
"the datafile currently in use" ( I repeat even after restarting server, its not working)
need help please give solution.!!!!!!!!
|
|
|
 pikun |
| Posted: Mar 01, 2008 06:10:18 AM |  |
|
Total Post: 17
Joined: Jan, 2008
|
making it offling... will it make any difference?
|
|
|
|
|
| Mohammed Taj |
| Posted: Mar 01, 2008 07:34:45 AM | |
|
Total Post: 746
Joined: Jul, 2007
|
You have to wait for sometimes becuase some transaction still using "undotbs2". but you said you restart database and server can show us exact sqlplus output.
|
|
|
|
|
| pikun |
| Posted: Mar 02, 2008 01:27:05 AM | |
|
Total Post: 17
Joined: Jan, 2008
|
hello Taj,
as I told u tht i was using the above ueries..
So after restarting Server or database whenever i execute those queries then .. after running 3rd query it is giving exactly error like below..
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
|
|
|
|
|
| Mohammed Taj |
| Posted: Mar 02, 2008 01:41:00 AM | |
|
Total Post: 746
Joined: Jul, 2007
|
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- -------------------------undo_tablespace string UNDOTBS1
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
the above something happen with you...
first show us what is your "undo_tablespace" parameter is set ?
|
|
|
|
|
| pikun |
| Posted: Mar 02, 2008 05:00:58 AM | |
|
Total Post: 17
Joined: Jan, 2008
|
as per as ur advice following thing is happening.....
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ----------------
undo_tablespace string UNDOTBS1
|
|
|
|
|
| pikun |
| Posted: Mar 02, 2008 05:03:54 AM | |
|
Total Post: 17
Joined: Jan, 2008
|
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------- -------- ---------
undo_tablespace string UNDOTBS1
|
|
|
|
|
| pikun |
| Posted: Mar 02, 2008 05:18:00 AM | |
|
Total Post: 17
Joined: Jan, 2008
|
hello taj,
looks like the problem is solved. one of our data process was not completed. thats why this process was holding the current undoplace. i traced the machine running the process followed by completing the process.
Now the database is permitting to drop the tablespace
wat u say??? i think its ok.. reply
|
|
|
|
|
| Mohammed Taj |
| Posted: Mar 02, 2008 07:19:33 AM | |
|
Total Post: 746
Joined: Jul, 2007
|
I don't know it is completed or not.
just give me below command output.
show parameter undo_tablespace
if you drop UNDOTBS1 then above command must be show something different name for undo tablespace.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
