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

Quản trị datafile trong Oracle Database

-- Dung lu?ng DB
select round(sum(bytes)/1024/1024/1024,2) "GB" from dba_data_files;

-- Online data file
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' ONLINE;
-- Offline data file
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;
-- Resize datafile
select ' ALTER DATABASE DATAFILE '''||a.name||' '' RESIZE '|| bytes*3 ||';' 
from v$datafile a,v$tablespace b 
where a.ts#=b.ts#
and b.name like '%TRANS%';

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'   RESIZE 100M;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'   RESIZE 128K;  -- 128K la min size datafile
ALTER DATABASE DATAFILE '/index01/crmdb/big_idx1_6.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 14G;

--Resize datafile
declare
   cursor c1 is select tablespace_name,file_id,max(block_id) mbid from dba_extents 
   --where tablespace_name in ( select distinct(tablespace_name) from dba_data_files where file_name like '%/s02/%')
   group by tablespace_name,file_id;
   fs number;
   fn varchar2(100);
   ts number;
   x number;
   gtot number:=0;
   tffs number:=0;
   ffrags number:= 0;
   begin
   for v1 in c1 loop
      select sum(bytes)/1024/1024 into fs from dba_free_space where tablespace_name = v1.tablespace_name and
           file_id = v1.file_id and block_id>v1.mbid;
           select file_name,bytes/1024/1024 into fn,ts from dba_data_files where file_id=v1.file_id and tablespace_name=v1.tablespace_name;
           select sum(bytes/1024/1024) into tffs from
           (select a.bytes/1024/1024 as bytes from dba_free_space a where file_id=v1.file_id and tablespace_name=v1.tablespace_name
            union all
            select 0.000001 as bytes from dual);
   x:=(ts-fs)+64;
   if ts>x then
   dbms_output.put_line('alter database datafile ' || '''' || fn || '''' || ' resize ' ||  round(x) || 'm;' || '--total size' || round(ts) || 'm;' );
 
   gtot :=  gtot + (ts-x);
   end if;
 
   ffrags := nvl(tffs - fs,0);
   dbms_output.put_line('--alter database datafile ' || '''' || fn || '''' || ' resize ' ||  round(x) || 'm;' || '--frag size' || round(ffrags) || 'm;');
 
   end loop;
 
   dbms_output.put_line('Total space reclaimation:' || gtot || 'MB');
  end;



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