【应知应会】数据文件大小扩容或缩容必备技能

SYS@PROD4> !oerr ora 329703297, 00000, "file contains used data beyond requested RESIZE value"// *Cause:  Some portion of the file in the region to be trimmed is //          currently in use by a database object// *Action: Drop or move segments containing extents in this region prior to//          resizing the file, or choose a resize value such that only free//          space is in the trimmed.

--创建表空间create tablespace ywzd datafile '/u01/app/oracle/oradata/PROD4/ywzd.dbf' size 300m extent management local uniform size 1m;--查看数据文件大小column file_name format a45select file_name, round(bytes/1024/1024) SizeMB from dba_data_files where file_name like '%ywzd%';FILE_NAME                      SIZEMB--------------------------------------------- ----------/u01/app/oracle/oradata/PROD4/ywzd.dbf             300
SYS@PROD4> !ls -alt /u01/app/oracle/oradata/PROD4/ywzd.dbf-rw-r----- 1 oracle oinstall 314580992 Apr  3 14:50 /u01/app/oracle/oradata/PROD4/ywzd.dbf
-- 查看表空间使用情况set linesize 1000col file_name for a70SELECT UPPER(F.TABLESPACE_NAME) "TablespaceName",       D.TOT_GROOTTE_MB "TotalSize(M)",       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "UsedSize(M)",       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,                     2),               '990.99') "UsedPercent",       F.TOTAL_BYTES "FreeSize(M)"  FROM (SELECT TABLESPACE_NAME,               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES          FROM DBA_FREE_SPACE         GROUP BY TABLESPACE_NAME) F,       (SELECT DD.TABLESPACE_NAME,               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB          FROM DBA_DATA_FILES DD         GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME/

--扩容数据文件ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD4/ywzd.dbf' RESIZE 301m;--再次查看数据文件大小，最初创建大小是300M，当前大小是301Mcol name for a45set linesize 500SELECT FILE#, STATUS, ENABLED, CHECKPOINT_CHANGE#, round(CREATE_BYTES/1024/1024)create_mb, round(BYTES/1024/1024) current_MB, NAME FROM V$DATAFILE where name like '%ywzd%';     FILE# STATUS  ENABLED    CHECKPOINT_CHANGE#  CREATE_MB CURRENT_MB NAME---------- ------- ---------- ------------------ ---------- ---------- ---------------------------------------------     7 ONLINE  READ WRITE         2415445    300       301 /u01/app/oracle/oradata/PROD4/ywzd.dbf

--创建用户create user ywzd identified by ywzd;grant dba to ywzd;alter user ywzd default tablespace ywzd;connect ywzd/ywzd;--创建测试数据BEGIN   for i in 1..19 LOOP      execute immediate 'create table ' || 'TEST'||i ||' as select * from dba_objects';   end loop;end;/

--数据文件与段区块大小比较select bytes-ebytes from (select sum(bytes) ebytes from dba_extents where file_id=7), dba_data_files where file_id=7;BYTES-EBYTES------------    56623104
--查看剩余的空间大小select sum(bytes) from dba_free_space where file_id=7;SUM(BYTES)----------  5557452  set linesize 500SELECT tablespace_name,file_id,block_id,round(bytes/1024/1024)free_mb,blocks FROM DBA_FREE_SPACEWHERE TABLESPACE_NAME='YWZD'ORDER BY BLOCK_ID;TABLESPACE_NAME           FILE_ID   BLOCK_ID    FREE_MB     BLOCKS------------------------------ ---------- ---------- ---------- ----------YWZD                    7      31744         53       6784  数据块BLOCK_ID=31744包含6784个blocks，数据文件还剩余53M空间。

-- FINDEXT.SQLSET ECHO OFFSET PAGESIZ 25column file_name format a50select file_name, file_id from dba_data_files order by 2;
ttitle -center 'Segment Extent Summary' skip 2col ownr format a8 heading 'Owner' justify ccol type format a8 heading 'Type' justify c trunccol name format a30 heading 'Segment Name' justify ccol exid format 990 heading 'Extent#' justify ccol fiid format 9990 heading 'File#' justify ccol blid format 99990 heading 'Block#' justify ccol blks format 999,990 heading 'Blocks' justify c
select owner ownr, segment_name name, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blksfrom dba_extentswhere file_id = &file_idorder by block_id/

@FINDEXT.SQL Owner          Segment Name          Type     Extent# File# Block#  Blocks-------- ------------------------------ -------- ------- ----- ------ --------YWZD     TEST19             TABLE           6     7    30848       128YWZD     TEST19             TABLE           7     7    30976       128YWZD     TEST19             TABLE           8     7    31104       128YWZD     TEST19             TABLE           9     7    31232       128YWZD     TEST19             TABLE          10     7    31360       128YWZD     TEST19             TABLE          11     7    31488       128YWZD     TEST19             TABLE          12     7    31616       128
247 rows selected.

BEGIN   for i in 2..17 LOOP      execute immediate 'DROP table ' || 'TEST'||i;   end loop;   execute immediate 'DROP TABLE TEST19';end;/

=【Owner          Segment Name          Type     Extent# File# Block#  Blocks-------- ------------------------------ -------- ------- ----- ------ --------YWZD     TEST1                TABLE           0     7      128       128YWZD     TEST1                TABLE           1     7      256       128YWZD     TEST1                TABLE           2     7      384       128YWZD     TEST1                TABLE           3     7      512       128YWZD     TEST1                TABLE           4     7      640       128YWZD     TEST1                TABLE           5     7      768       128YWZD     TEST1                TABLE           6     7      896       128YWZD     TEST1                TABLE           7     7     1024       128YWZD     TEST1                TABLE           8     7     1152       128YWZD     TEST1                TABLE           9     7     1280       128YWZD     TEST1                TABLE          10     7     1408       128YWZD     TEST1                TABLE          11     7     1536       128YWZD     TEST1                TABLE          12     7     1664       128YWZD     TEST18             TABLE           0     7    28416       128YWZD     TEST18             TABLE           1     7    28544       128YWZD     TEST18             TABLE           2     7    28672       128YWZD     TEST18             TABLE           3     7    28800       128YWZD     TEST18             TABLE           4     7    28928       128YWZD     TEST18             TABLE           5     7    29056       128YWZD     TEST18             TABLE           6     7    29184       128YWZD     TEST18             TABLE           7     7    29312       128YWZD     TEST18             TABLE           8     7    29440       128YWZD     TEST18             TABLE           9     7    29568       128YWZD     TEST18             TABLE          10     7    29696       128YWZD     TEST18             TABLE          11     7    29824       128YWZD     TEST18             TABLE          12     7    29952       12826 rows selected.

SELECT ORIGINAL_NAME FROM DBA_RECYCLEBIN WHERE TS_NAME = 'YWZD';ORIGINAL_NAME--------------------------------------------------------------------------------------------------------------------------------TEST2TEST3TEST4TEST5TEST6TEST7TEST8TEST9TEST10TEST11TEST12TEST13TEST14TEST15TEST16TEST17TEST19
17 rows selected.

SELECT SUM(BYTES)+10*8192 FROM DBA_EXTENTS WHERE FILE_ID = 7;SUM(BYTES)+10*8192------------------      27344896

-- SHRINK_DATAFILE.SQLSET SERVEROUTPUT ONDECLARE     V_FILE_ID NUMBER;     V_BLOCK_SIZE NUMBER;     V_RESIZE_SIZE NUMBER;BEGIN     V_FILE_ID := &FILE_ID;     V_RESIZE_SIZE := &RESIZE_FILE_TO;
     SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID;
     DBMS_OUTPUT.PUT_LINE('.');     DBMS_OUTPUT.PUT_LINE('.');     DBMS_OUTPUT.PUT_LINE('.');     DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' BYTES');     DBMS_OUTPUT.PUT_LINE('===================================================================');     DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS');     DBMS_OUTPUT.PUT_LINE('===================================================================');
     for my_record in (          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME          FROM DBA_EXTENTS          WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE           AND FILE_ID = V_FILE_ID          AND SEGMENT_TYPE NOT LIKE '%PARTITION%'          ORDER BY 1) LOOP               DBMS_OUTPUT.PUT_LINE(my_record.ONAME);      END LOOP;
     DBMS_OUTPUT.PUT_LINE('===================================================================');     DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS');     DBMS_OUTPUT.PUT_LINE('===================================================================');
     for my_record in (          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME          FROM DBA_EXTENTS          WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE          AND FILE_ID = V_FILE_ID           AND SEGMENT_TYPE LIKE '%PARTITION%'          ORDER BY 1) LOOP                DBMS_OUTPUT.PUT_LINE(my_record.ONAME);     END LOOP;END;/

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD4/ywzd.dbf' RESIZE 27344896*ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE value

YWZD@PROD4> @SHRINK_DATAFILE.SQLEnter value for file_id: 7old   6:      V_FILE_ID := &FILE_ID;new   6:      V_FILE_ID := 7;Enter value for resize_file_to: 27344896old   7:      V_RESIZE_SIZE := &RESIZE_FILE_TO;new   7:      V_RESIZE_SIZE := 27344896;...OBJECTS IN FILE 7 THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO 27344896 BYTES===================================================================NON-PARTITIONED OBJECTS===================================================================YWZD.TEST18 - OBJECT TYPE = TABLE===================================================================PARTITIONED OBJECTS===================================================================PL/SQL procedure successfully completed.

--移动数据文件ALTER TABLE TEST18 MOVE TABLESPACE USERS;--再次执行缩容ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD4/ywzd.dbf' RESIZE 27344896*ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE value

purge tablespace YWZD;YWZD@PROD4> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD4/ywzd.dbf' RESIZE 27344896; 
Database altered. YWZD@PROD4> !ls -alt /u01/app/oracle/oradata/PROD4/ywzd.dbf-rw-r----- 1 oracle oinstall 27353088 Apr  3 15:27 /u01/app/oracle/oradata/PROD4/ywzd.dbf

-- 将TEST18测试表移回到YWZD表空间ALTER TABLE TEST18 MOVE TABLESPACE TEST;--清理掉测试环境CONNECT / AS SYSDBAdrop user ywzd cascade;drop tablespace ywzd including contents and datafiles cascade constraints;

--查看临时数据文件大小set linesize 200col file_name for a80col tablespace_name for a20select TABLESPACE_NAME,FILE_NAME,AUTOEXTENSIBLE,BYTES/1024/1024 Size_MB from dba_temp_files order by 1,2;
TABLESPACE_NAME      FILE_NAME                                          AUT    SIZE_MB-------------------- -------------------------------------------------------------------------------- --- ----------TEMP             /u01/app/oracle/oradata/PROD4/temp01.dbf                          YES     197TEMP             /u01/app/oracle/oradata/PROD4/temp02.dbf                          NO     500

alter tablespace temp shrink space keep 1g;alter tablespace temp shrink space keep 1g*ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE value
select sum(blocks*8/1024) size_mb from v$tempseg_usage where tablespace='TEMP';
   SIZE_MB----------       217

select inst_id,       sid,       serial#,       status,       "USERNAME",       machine,       program,       sql_id,       event,       'alter system kill session ''' || sid || ',' || serial# || ',@' ||       inst_id || ''' immediate;'  from gv$session where saddr in (select session_addr from gv$tempseg_usage) order by 1;

select TABLESPACE_NAME,bytes/1024/1024 from dba_temp_files;alter tablespace temp shrink space keep 10g;---从原32G调整至20Galter tablespace temp shrink tempfile '/u01/app/oracle/oradata/PROD4/temp02.dbf' keep 100M; ---从原32G调整至20G

REM Script is meant for Oracle version 9 and higherREM -----------------------------------------------
set serveroutput onexec dbms_output.enable(1000000);
declare
cursor c_dbfile isselect f.tablespace_name,f.file_name,f.file_id,f.blocks,t.block_size,decode(t.allocation_type,'UNIFORM',t.initial_extent/t.block_size,0) uni_extent,decode(t.allocation_type,'UNIFORM',(128+(t.initial_extent/t.block_size)),128) file_min_sizefrom dba_data_files f,dba_tablespaces twhere f.tablespace_name = t.tablespace_nameand t.status = 'ONLINE'order by f.tablespace_name,f.file_id;
cursor c_freespace(v_file_id in number) isselect block_id, block_id+blocks max_blockfrom dba_free_spacewhere file_id = v_file_idorder by block_id desc;
/* variables to check settings/values */dummy number;checkval varchar2(10);block_correction1 number;block_correction2 number;
/* running variable to show (possible) end-of-file */file_min_block number;
/* variables to check if recycle_bin is on and if extent as checked is in ... */recycle_bin boolean:=false;extent_in_recycle_bin boolean;
/* exception handler needed for non-existing tables note:344940.1 */sqlstr varchar2(100);table_does_not_exist exception;pragma exception_init(table_does_not_exist,-942);
/* variable to spot space wastage in datafile of uniform tablespace */space_wastage number;
begin
/* recyclebin is present in Oracle 10.2 and higher and might contain extent as checked */beginselect value into checkval from v$parameter where name = 'recyclebin';if checkval = 'on'thenrecycle_bin := true;end if;exceptionwhen no_data_foundthenrecycle_bin := false;end;
/* main loop */for c_file in c_dbfileloop/* initialization of loop variables */dummy :=0;extent_in_recycle_bin := false;file_min_block := c_file.blocks;
begin
space_wastage:=0; /* reset for every file check */
<<check_free>>
for c_free in c_freespace(c_file.file_id)loop/* if blocks is an uneven value there is a need to correctwith -1 to compare with end-of-file which is even */block_correction1 := (0-mod(c_free.max_block,2));block_correction2 := (0-mod(c_file.blocks,2));if file_min_block+block_correction2 = c_free.max_block+block_correction1then
/* free extent is at end so file can be resized */file_min_block := c_free.block_id;
/* Uniform sized tablespace check if space at end of fileis less then uniform extent size */elsif (c_file.uni_extent !=0) and ((c_file.blocks - c_free.max_block) < c_file.uni_extent)then
/* uniform tablespace which has a wastage of space in datafiledue to fact that space at end of file is smaller than uniform extent size */
space_wastage:=c_file.blocks - c_free.max_block;file_min_block := c_free.block_id;
else/* no more free extent at end of file, file cannot be further resized */exit check_free;end if;end loop;end;
/* check if file can be resized, minimal size of file 128 {+ initial_extent} blocks */if (file_min_block = c_file.blocks) or (c_file.blocks <= c_file.file_min_size)then
dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);dbms_output.put_line('cannot be resized no free extents found');dbms_output.put_line('Note: for some cases, dba_free_spaces data is not accurate, and this script does not work for such cases. You may want to manually check if the datafile is feasible to be resized');dbms_output.put_line('.');
else
/* file needs minimal no of blocks which does vary over versions,using safe value of 128 {+ initial_extent} */if file_min_block < c_file.file_min_sizethenfile_min_block := c_file.file_min_size;end if;

dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);dbms_output.put_line('current size: '||(c_file.blocks*c_file.block_size)/1024||'K'||' can be resized to: '||round((file_min_block*c_file.block_size)/1024)||'K (reduction of: '||round(((c_file.blocks-file_min_block)/c_file.blocks)*100,2)||' %)');

/* below is only true if recyclebin is on */if recycle_binthenbeginsqlstr:='select distinct 1 from recyclebin$ where file#='||c_file.file_id;execute immediate sqlstr into dummy;
if dummy > 0then
dbms_output.put_line('Extents found in recyclebin for above file/tablespace');dbms_output.put_line('Implying that purge of recyclebin might be needed in order to resize');dbms_output.put_line('SQL> purge tablespace '||c_file.tablespace_name||';');end if;exceptionwhen no_data_foundthen null;when table_does_not_existthen null;end;end if;dbms_output.put_line('SQL> alter database datafile '''||c_file.file_name||''' resize '||round((file_min_block*c_file.block_size)/1024)||'K;');
if space_wastage!=0thendbms_output.put_line('Datafile belongs to uniform sized tablespace and is not optimally sized.');dbms_output.put_line('Size of datafile is not a multiple of NN*uniform_extent_size + overhead');dbms_output.put_line('Space that cannot be used (space wastage): '||round((space_wastage*c_file.block_size)/1024)||'K');dbms_output.put_line('For optimal usage of space in file either resize OR increase to: '||round(((c_file.blocks+(c_file.uni_extent-space_wastage))*c_file.block_size)/1024)||'K');end if;
dbms_output.put_line('.');
end if;
end loop;
end;/

Tablespace: EXAMPLE Datafile: /u01/app/oracle/oradata/PROD4/example01.dbfcurrent size: 1273600K can be resized to: 1248320K (reduction of: 1.98 %)SQL> alter database datafile '/u01/app/oracle/oradata/PROD4/example01.dbf'resize 1248320K;.Tablespace: SYSAUX Datafile: /u01/app/oracle/oradata/PROD4/sysaux01.dbfcurrent size: 747520K can be resized to: 702464K (reduction of: 6.03 %)SQL> alter database datafile '/u01/app/oracle/oradata/PROD4/sysaux01.dbf' resize702464K;.