Home » RDBMS Server » Server Administration » altering undo tablepace (oracle 10g)
altering undo tablepace [message #406611] Thu, 04 June 2009 23:56 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,

i am having two instances in my machine orcl and orcl1.i am creating most of the databases in orcl.i have noticed that in my orcl instance my undo tablespace UNDOTBS01.DBF IS OF 5.5GB size and
undo tablespace in instance orcl1 is of 170MB.

hoe can i reduce the size of undo tablespace in orcl.

it is in the location

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF.

can anyone suggest me i can do so in achieving this.
Re: altering undo tablepace [message #406614 is a reply to message #406611] Fri, 05 June 2009 00:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create a new one, switch to it and drop the old one.

Regards
Michel
Re: altering undo tablepace [message #406616 is a reply to message #406614] Fri, 05 June 2009 00:05 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i am getting exception while doing so
like


SQL>CREATE UNDO TABLESPACE UNDOTBS02 DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS02.DBF' SIZE 100M reuse AUTOEXTEND ON ;

EXCEPTION:

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS02.DBF'
-----------INVALID CHARACTER AT D.

can you give the sequence of steps in doing so
Re: altering undo tablepace [message #406626 is a reply to message #406616] Fri, 05 June 2009 00:56 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I do not have the same behaviour that you.
Use SQL*Plus and copy and paste your session.

Before, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Maximum session exceeded
Next Topic: How to reduce the size of a database ? (merged 4)
Goto Forum:
  


Current Time: Fri Dec 09 00:09:51 CST 2016

Total time taken to generate the page: 0.14988 seconds