Question: I have
One undo Table space with 4 undo datafiles in my production
database Oracle 12c and standby database. Each data file
size is 30 gig but uses only 1 gig. Can I resize
(shrink) all undo data files without any downtime.
Answer: Yes, you can shrink UNDO
while online, but it is a bit tricky:
Please read:
Undo tablespace sizing
It's tricky because you
cannot shrink the current undo log, you can only shrink
those undo files that are not currently in-use.
You can shrink the datafile of the UNDO tablespace on the
primary database to 5G by using the following command:
alter database
datafile '/ou1/app/oracley/undo_ts01.dbf'
resize 2G;
You must Supply the correct
file name in the above command.
|
For ASM, you must
specify the correct ASM filename. The undo datafile on the
standby
If you get
this error:
ORA-03297: file contains used data beyond requested RESIZE
value
This ORA-03297 error says that only
space can be released if it is at the end of the datafile.
The undo segments may be scattered all over the UNDO
datafile.
In this case you have to do the following
on the database:
-- Create new undo tablespace with
smaller size.
SQL>
create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf'
size 2G;
-- Set the new tablespace as an undo_tablespace
SQL> alter
system set undo_tablespace=undo_rbs1;
-- Drop
the old tablespace.
SQL> drop
tablespace undo_rbs0 including contents.
-- Create new undo tablespace with
smaller size.
SQL>
create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf'
size 2G;
-- Set new tablespace as
undo_tablespace
SQL> alter system set
undo_tablespace=undo_rbs1;
-- Drop
the old tablespace.
SQL> drop
tablespace undo_rbs0 including contents.
Dropping the old tablespace may give the ORA-30013 : undo
tablespace '%s' is currently in use. This error indicates
you must wait for the undo tablespace to become unavailable.
In other words, you must wait for existing transaction to
commit or rollback.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
