Database Administration (MOSC)

MOSC Banner

how to recreate undo tablespace in 19c?

hi,


how to recreate undo tablespace in 19c, for my pdb?


Single tenant database, i.e. one pdb, Database 19c 19.4.0.0, one Linux 7


Local undo management is off I believe, see below

SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

no rows selected


I have three undo tablespaces, I'm assuming one of them is for cdb , one for seed and one for the pdb. I inherited this test environment (for context)

SQL>

CON_NAME
------------------------------
CDB$ROOT

SQL> set pagesize 1000
SQL> r
1 select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
2 from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
3 from dba_data_files a, dba_tablespaces b
4 where a.tablespace_name = b.tablespace_name
5 and b.contents = 'UNDO'
6 group by b.tablespace_name) a,
7 (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
8 from DBA_UNDO_EXTENTS c
9 where status <> 'EXPIRED'
10 group by c.tablespace_name) b
11* where a.tablespace_name = b.tablespace_name

TABLESPACE_NAME SIZEMB USAGEMB FREEMB
------------------------------ ---------- ---------- ----------
UNDOTBS2 12990 225.375 12764.625
UNDOTBS1 11655 262.3125 11392.6875
UNDOTBS3 17500 608.5625 16891.4375
Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center