1. Check
--Hiển thị dung lượng trống của tablespace UNDOTBS1 UNDOTBS2
SELECT a.tablespace_name,100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Usage",
ROUND (a.bytes_alloc / 1024 / 1024) "Size MB",
ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Used MB",
ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Free MB",
--ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Free",
ROUND (maxbytes / 1048576) "Max MB",
round(maxbytes/1048576-(ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024)),0) "Free_MB_Max",
ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/ ROUND (maxbytes / 1048576) * 100) "%Used of Max"
FROM (SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc, SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
FROM dba_data_files f
GROUP BY tablespace_name) a,
(SELECT f.tablespace_name, SUM (f.bytes) bytes_free FROM dba_free_space f GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+) and (a.tablespace_name in ('UNDOTBS1','UNDOTBS1'))
order by "%Used of Max" desc;
SELECT a.tablespace_name,100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Usage",
ROUND (a.bytes_alloc / 1024 / 1024) "Size MB",
ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Used MB",
ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Free MB",
--ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Free",
ROUND (maxbytes / 1048576) "Max MB",
round(maxbytes/1048576-(ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024)),0) "Free_MB_Max",
ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/ ROUND (maxbytes / 1048576) * 100) "%Used of Max"
FROM (SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc, SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
FROM dba_data_files f
GROUP BY tablespace_name) a,
(SELECT f.tablespace_name, SUM (f.bytes) bytes_free FROM dba_free_space f GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+) and (a.tablespace_name in ('UNDOTBS1','UNDOTBS1'))
order by "%Used of Max" desc;
--Kiểm tra session nào đang sử dụng:
SELECT s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;
select * from dba_data_Files where tablespace_name in ('UNDOTBS1','UNDOTBS21');
--/data/oradata/dbaviet/undotbs01.dbf 2 UNDOTBS1 25674383360 --> Node 1
--/data/oradata/dbaviet/undotbs22_1.dbf 62 UNDOTBS21 6442450944 --> Node 2
--/data/oradata/dbaviet/undotbs22_002.dbf 66 UNDOTBS21 2215641088
--/data/oradata/dbaviet/undotbs22_003.dbf 87 UNDOTBS21 2064646144
2.Tạo undo tablespace
create undo tablespace UNDOTBS11 datafile '/u02/oradata/dbaviet/undotbs01.dbf' size 1g autoextend on next 100m;
create undo tablespace UNDOTBS22 datafile '/u02/oradata/dbaviet/undotbs02.dbf' size 1g autoextend on next 100m;
3.Set undo về instance 1, instance 2
select * From gv$instance;
alter system set undo_tablespace=UNDOTBS11 sid='dbaviet1';
alter system set undo_tablespace=UNDOTBS22 sid='dbaviet2';
-- Check lai
select * from gv$parameter where name like '%undo%'
4.RESTARTTẠI TỪNG INSTANCE
SQL> shutdown immediate
SQL> startup
5.Drop undo
DROP TABLESPACE UNDOTBS1 including contents and datafiles;
DROP TABLESPACE UNDOTBS2 including contents and datafiles;
