Resize UNDO TABLESPACE (2)

Masih melanjutkan bagaimana caranya me-resize file UNDO TABLESPACE (biar lebi singkat, TABLESPACE saya ganti TBS) yang tadi pagi belum kelar, tadi sehabis sholat dzuhur tiba-tiba kepikiran caranya, yaitu membuat file UNDO TBS kedua untuk transisi aja, dan ternyata berhasil :)

1. Saya coba lihat FILENAME dan TABLESPACE serta ukuranya dari SQLPLUS.
col file_name format a50
col tablespace_name format a10
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files ORDER BY file_name;
2. Bikin UNDO TBS kedua
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
‘/data/app/oracle/oradata/zeus/undotbs2.dbf’
SIZE 50 M AUTOEXTEND OFF;
3. Set UNDo TBS kedua menjadi Default UNDO
ALTER SYSTEM SET undo_tablespace = UNDOTBS2;
4. Hapus UNDO TBS pertama
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
5. Bukin UNDO TBS pertama dengan limit 1 GB
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE
‘/data/app/oracle/oradata/zeus/undotbs1.dbf’
SIZE 500 M AUTOEXTEND ON NEXT 5 M MAXSIZE 1000M;
6. Set UNDO TBS pertama menjadi Defulat UNDO
ALTER SYSTEM SET undo_tablespace = UNDOTBS1;
7. Hapus UNDO TBS kedua
DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES;
8. Ganti Parameter UNDO_RETENTION
ALTER SYSTEM SET UNDO_RETENTION=600;
perintah (command) SQLPLUS dapat dilihat di psoug