Re: how to shrink undo tablespace on 10GR2?
- From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
- To: yong321@xxxxxxxxx
- Date: Wed, 20 Oct 2010 22:01:50 -0200
Hi Friend,
I got these instructions here in the forum in the past.
It solved my problem.
I guess it can help friends that need to re-create UNDOTBS.
This is a sample. Change script as you need.
conn sys/password as sysdba
Identify the current undo tablespace:
set long 1000
select dbms_metadata.get_ddl('TABLESPACE', 'UNDOTBS1') from dual;
DBMS_METADATA.GET_DDL('TABLESP
--------------------------------------------------------------------------------
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'C:\ORACLE\ORADATA\DB10G\UNDOTBS01.DBF' SIZE 26214400
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
1 row selected.
Use this information to build a new undo tablespace, remember to change both
the tablespace name and the datafile name. Also, don't forget to define a
smaller size or there isn't much point doing this:
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE
'C:\ORACLE\ORADATA\DB10G\UNDOTBS02.DBF' SIZE 26214400
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Tell the system your switching to another undo tablespace:
ALTER SYSTEM SET undo_tablespace=UNDOTBS2;
Drop the original undo tablespace:
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
Especially on Windows, you might find the datafiles associated with the undo
tablespace remain on disk. If so, restart the instance:
SHUTDOWN IMMEDIATE
STARTUP
Then manually remove the datafiles associated with the initial undo
tablespace. These should be listed by the query at the top.
You should now have a smaller undo tablespace.
Best Regards
Eriovaldo
On Wed, Oct 20, 2010 at 12:37 PM, Yong Huang <yong321@xxxxxxxxx> wrote:
> The recommended way to "shrink" an undo tablespace in AUM is as others
> said, create a new one and assign undo_tablespace to it.
>
> If you work with Oracle support, they may allow you to set
> "_smu_debug_mode" to 4 and either shrink an undo segment, or offline
> and drop it. Just another option to keep in mind, even though it's
> probably more work.
>
> Yong Huang
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
