Chủ Nhật, 29 tháng 11, 2020

Quản trị Undo trong Oracle Database

select a.tablespace_name,   a.TOTAL_GB total_GB,   b.Used_GB Used_GB,   round(((b.Used_GB/a.TOTAL_GB)*100),2) Percent_USED   
    from
        (select tablespace_name,  sum(bytes)/1024/1024/1024 TOTAL_GB from dba_data_files   group by tablespace_name) a,
        (select tablespace_name, sum(bytes)/1024/1024/1024 Used_GB from DBA_UNDO_EXTENTS group by tablespace_name) b
    where a.tablespace_name=b.tablespace_name 
order by percent_used desc
--1/Create undo tablespace

CREATE SMALLFILE UNDO TABLESPACE "TUNDOTBS1" DATAFILE 
'/u03/oracle/oradata/DBAViet/tundo1_01.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo1_02.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo1_03.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo1_04.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo1_05.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo1_06.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo1_07.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo1_08.dbf' SIZE 10G REUSE  


CREATE SMALLFILE UNDO TABLESPACE "TUNDOTBS2" DATAFILE 
'/u03/oracle/oradata/DBAViet/tundo2_01.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo2_02.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo2_03.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo2_04.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo2_05.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo2_06.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo2_07.dbf' SIZE 10G REUSE , 
'/u03/oracle/oradata/DBAViet/tundo2_08.dbf' SIZE 10G REUSE 

-- 2/ Set new undo tablespace for each intance

ALTER SYSTEM SET undo_tablespace = TUNDOTBS1 SID='DBAViet1'

ALTER SYSTEM SET undo_tablespace = TUNDOTBS2 SID='DBAViet2'


-- 3/ DROP TABLESPACE UNDOTBS1_old including contents;

DROP TABLESPACE UNDOTBS1 including contents and datafiles;

DROP TABLESPACE UNDOTBS2 including contents and datafiles;

KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH: 📧 Mail: binhoracle@gmail.com ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: https://www.facebook.com/BinhOracleMaster 👨 Inbox Messenger: https://m.me/101036604657441 (profile) 👨 Fanpage: https://www.facebook.com/tranvanbinh.vn 👨 Inbox Fanpage: https://m.me/tranvanbinh.vn 👨👩 Group FB: https://www.facebook.com/groups/OracleDBAVietNam 👨 Website: http://www.tranvanbinh.vn 👨 Blogger: https://tranvanbinhmaster.blogspot.com 🎬 Youtube: http://bit.ly/ytb_binhoraclemaster 👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi 👨 Linkin: https://www.linkedin.com/in/binhoracle 👨 Twitter: https://twitter.com/binhoracle 👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master