Shrink undo tablespace: Difference between revisions
From WickyWiki
Created page with "At times you will run heavy SQL commands and your undo space will grow very big, it will never grow smaller automatically. You can do this manually by creating a new undo tablesp..." |
No edit summary |
||
| Line 3: | Line 3: | ||
<syntaxhighlight lang=sql> | <syntaxhighlight lang=sql> | ||
connect sys/pwd as sysdba | connect sys/pwd as sysdba | ||
CREATE UNDO TABLESPACE undotbs4 DATAFILE 'C:/oracle/product/10.2.0/oradata/orcl/UNDOTBS04.DBF' SIZE 100M REUSE AUTOEXTEND ON; | CREATE UNDO TABLESPACE undotbs4 | ||
DATAFILE 'C:/oracle/product/10.2.0/oradata/orcl/UNDOTBS04.DBF' | |||
SIZE 100M REUSE AUTOEXTEND ON; | |||
alter system set UNDO_TABLESPACE=UNDOTBS4; | alter system set UNDO_TABLESPACE=UNDOTBS4; | ||
drop tablespace UNDOTBS3 including contents and datafiles; | drop tablespace UNDOTBS3 including contents and datafiles; | ||
Revision as of 09:24, 11 January 2012
At times you will run heavy SQL commands and your undo space will grow very big, it will never grow smaller automatically. You can do this manually by creating a new undo tablespace and removing the old one.
connect sys/pwd as sysdba CREATE UNDO TABLESPACE undotbs4 DATAFILE 'C:/oracle/product/10.2.0/oradata/orcl/UNDOTBS04.DBF' SIZE 100M REUSE AUTOEXTEND ON; alter system set UNDO_TABLESPACE=UNDOTBS4; drop tablespace UNDOTBS3 including contents and datafiles;
Note: check and delete UNDOTBS1 manually on your system if needed
