[oracle@localhost ~]$ oerr ora 03297
03297, 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.
[oracle@Database-backup ~]$
SQL>
select 'alter database datafile ''' || file_name || ''' resize ' ||decode(MAX_GB, 0, '100M;', MAX_GB || 'G;') RESIZE命令,
a.TABLESPACE_NAME,
a.AUTOEXTENSIBLE,
a.TOTAL_GB,
a.MAX_GB
from (SELECT TABLESPACE_NAME,
FILE_NAME,
AUTOEXTENSIBLE,
CEIL(SUM(BYTES / 1024 / 1024 / 1024)) TOTAL_GB,
CEIL(SUM(case when MAX_BLOCK.FILE_ID is null then 0 else MAX_BLOCK.MAX_BLOCK end)) MAX_GB
--由于size大小需为整
--CEIL(N) 取大于等于数值N的最小整数
--FLOOR(N)取小于等于数值N的最大整数
FROM DBA_DATA_FILES
LEFT JOIN (SELECT MAX(BLOCK_ID) * 8 / 1024 / 1024 MAX_BLOCK,
FILE_ID
FROM DBA_EXTENTS
GROUP BY FILE_ID) MAX_BLOCK
ON MAX_BLOCK.FILE_ID = DBA_DATA_FILES.FILE_ID
GROUP BY TABLESPACE_NAME, /*MAX_BLOCK.FILE_ID,*/
FILE_NAME,
AUTOEXTENSIBLE
ORDER BY TABLESPACE_NAME) A
where TOTAL_GB != MAX_GB;
RESIZE命令 TABLESPACE_NAME AUTOEXTENSIBLE TOTAL_GB MAX_BLOCK_GB
---------------------------------------------------------------------------- -------- -------- -------- -------- -------- ----------------------------- ----------------- ------------- ------------
alter database datafile '/data/datafiles/ZB_LSP_001/sysaux01.dbf' resize 8G; SYSAUX YES 10 8
alter database datafile '/data/datafiles/ZBLSP_DATA_001.dbf' resize 27G; TBS_LMP YES 30 27
alter database datafile '/data/datafiles/ZB_LSP_001/tbs_scheduler.dbf'resize 10G; TBS_SCHEDULER YES 32 10
SQL>
