1.CHECK
-- Kiểm tra trạng thái:
--187 11167039014814 1/20/2014 10:57:50 AM 595 187 RECOVER READ WRITE 13503656762637 5/5/2022 2:53:37 PM 13391571876700 12/16/2021 9:07:13 PM 13408963390301 13408988992234 1/4/2022 3:15:15 PM 19373490176 2364928 10737418240 8192 0 8192 NONE 0 --> Mặc dù read write nhưng là RECOVER nên phải recover datafile này
select * from v$datafile where file#=187;
-- 187 DATA AVAILABLE 187 RECOVER -> Mặc dù AVAILABLE nhưng là RECOVER nên phải recover datafile này
select * from dba_data_files where file_id=187;
select * from v$datafile where status='RECOVER';
-- Dung luợng DB tính mỗi datafile (ngoài ra còn redo log, archived log, backup, flashback log,...)
select round(sum(bytes)/1024/1024/1024,2) "GB" from dba_data_files;
2.THAY ĐỔI
-- 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;
-- Script off autoextend vào 1 phân vùng /u02 do đầy 100% hoặc 99%
select 'alter database datafile ' || file# ||' autoextend off;' from v$datafile where name like '%/u02%';
-- Script 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;
3. Drop datafile
ALTER TABLESPACE example DROP DATAFILE '+DATA/dbaviet/example_df3.f';
ALTER TABLESPACE example DROP DATAFILE '/oradata/dbaviet/example_df3.f';
-------
