oracle 创建 undo tablespace

os: centos 7.4
db: oracle 12.1.0.2

A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (2的32次方) blocks. The minimum size of the single data file or temp file is 12 megabytes (MB) for a tablespace with 32K blocks and 7MB for a tablespace with 8K blocks. The maximum size of the single data file or temp file is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.

A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million (2的22次方) blocks.

bigfile tablespace 只能包含一个文件,不可以再添加.block 是 2的32次方(4G) 寻址
smallfile tablespace 是可以添加至1022个数据文件.block 是 2的22次方(4M) 寻址

create undo smallfile tablespace

CREATE UNDO TABLESPACE peiyb_undo
DATAFILE '/data/orcl/pdborcl/peiyb_undo01.dbf' 
SIZE 1G 
AUTOEXTEND ON NEXT 1G 
MAXSIZE UNLIMITED
ONLINE
RETENTION GUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

create undo bigfile tablespace

CREATE BIGFILE UNDO TABLESPACE peiyb_undo 
DATAFILE '/data/orcl/pdborcl/peiyb_undo01.dbf' 
SIZE 1G 
AUTOEXTEND ON NEXT 1G 
MAXSIZE UNLIMITED
ONLINE
RETENTION GUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

add undo smallfile

ALTER TABLESPACE peiyb_undo
  ADD DATAFILE '/data/orcl/pdborcl/peiyb_undo02.dbf'
  SIZE 1G
  AUTOEXTEND ON NEXT 1G
  MAXSIZE UNLIMITED;

resize undo smallfile

select 'alter database datafile '''||dt.file_name||''' autoextend on next 1g maxsize 30g; ', 
       dt.*,
       dts.*
from dba_data_files dt,
     dba_tablespaces dts
where 1=1
  and dt.tablespace_name = dts.tablespace_name
  and dts.contents='UNDO'
  and dts.bigfile = 'NO'
;

ALTER DATABASE DATAFILE '/data/orcl/pdborcl/peiyb_undo02.dbf'
RESIZE 10G;
 
ALTER DATABASE DATAFILE '/data/orcl/pdborcl/peiyb_undo02.dbf'
AUTOEXTEND ON NEXT 1G 
MAXSIZE UNLIMITED;

undo_retention & retention guarantee

SQL> alter system set undo_retention=3600;
SQL> alter tablespace UNDOTBS1 retention guarantee; 

Switching Undo Tablespaces

SQL>  alter system set undo_tablespace = undotbs_02;

参考:
https://docs.oracle.com/database/121/SQLRF/statements_7003.htm#SQLRF01403
https://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN11356
https://docs.oracle.com/database/121/SQLRF/statements_3002.htm#SQLRF01002

<< Managing Undo >>
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-undo.html#GUID-2C865CF9-A8B5-4BF1-A451-E8C08D3611F0