tom answered
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 28 10:58:14 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYSTEM@VKS_SI0BOS126> ----------- maxshrink.sql ----------------------------------
SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> set verify off
SYSTEM@VKS_SI0BOS126> column file_name format a50 word_wrapped
SYSTEM@VKS_SI0BOS126> column smallest format 999,990 heading "Smallest|Size|Poss."
SYSTEM@VKS_SI0BOS126> column currsize format 999,990 heading "Current|Size"
SYSTEM@VKS_SI0BOS126> column savings format 999,990 heading "Poss.|Savings"
SYSTEM@VKS_SI0BOS126> break on report
SYSTEM@VKS_SI0BOS126> compute sum of savings on report
SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> column value new_val blksize
SYSTEM@VKS_SI0BOS126> select value from v$parameter where name = 'db_block_size'
2 /
VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8192
SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> select file_name,
2 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
3 ceil( blocks*&&blksize/1024/1024) currsize,
4 ceil( blocks*&&blksize/1024/1024) -
5 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
6 from dba_data_files a,
7 ( select file_id, max(block_id+blocks-1) hwm
8 from dba_extents
9 group by file_id ) b
10 where a.file_id = b.file_id(+)
11 /
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
D:\ORACLE\ORADATA\VKS\SYSTEM01.DBF 2,712 2,712 0
D:\ORACLE\ORADATA\VKS\TS2014.DBF 2 2 0
D:\ORACLE\ORADATA\VKS\SAP01.DBF 4,096 4,096 0
D:\ORACLE\ORADATA\VKS\TS1998.DBF 8 8 0
D:\ORACLE\ORADATA\VKS\TS2003.DBF 249 249 0
D:\ORACLE\ORADATA\VKS\TS2005.DBF 576 576 0
D:\ORACLE\ORADATA\VKS\USERS02.DBF 4,096 4,096 0
D:\ORACLE\ORADATA\VKS\TS2006.DBF 1,508 1,508 0
D:\ORACLE\ORADATA\VKS\TS2012.DBF 2 2 0
D:\ORACLE\ORADATA\VKS\TS2013.DBF 2 2 0
D:\ORACLE\ORADATA\VKS\SAP02.DBF 2,136 2,136 0
D:\ORACLE\ORADATA\VKS\USERS01.DBF 8,966 25,600 16,634
D:\ORACLE\ORADATA\VKS\TS1997.DBF 2 2 0
F:\ORACLE\ORADATA\VKS\UNDODFLT.DBF 655 655 0
D:\ORACLE\ORADATA\VKS\TS2000.DBF 114 114 0
D:\ORACLE\ORADATA\VKS\TS2009.DBF 4,083 4,083 0
D:\ORACLE\ORADATA\VKS\TS2015.DBF 2 2 0
D:\ORACLE\ORADATA\VKS\SYSAUX01.DBF 1,123 1,123 0
D:\ORACLE\ORADATA\VKS\TS1999.DBF 49 49 0
D:\ORACLE\ORADATA\VKS\TS2010.DBF 2,234 2,234 0
D:\ORACLE\ORADATA\VKS\TS2001.DBF 140 140 0
D:\ORACLE\ORADATA\VKS\TS2002.DBF 164 164 0
D:\ORACLE\ORADATA\VKS\TS2004.DBF 521 521 0
D:\ORACLE\ORADATA\VKS\TS2007.DBF 4,752 4,752 0
D:\ORACLE\ORADATA\VKS\TS2008.DBF 3,737 3,737 0
D:\ORACLE\ORADATA\VKS\TS2011.DBF 2 2 0
--------
sum 16,634
26 rows selected.
SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> column cmd format a75 word_wrapped
SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> select 'alter database datafile '''||file_name||''' resize ' ||
2 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
3 from dba_data_files a,
4 ( select file_id, max(block_id+blocks-1) hwm
5 from dba_extents
6 group by file_id ) b
7 where a.file_id = b.file_id(+)
8 and ceil( blocks*&&blksize/1024/1024) -
9 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
10 /
CMD
---------------------------------------------------------------------------
alter database datafile 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' resize 8966m;
SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> alter database datafile 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' resize 8966m;
alter database datafile 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' resize 8966m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
SYSTEM@VKS_SI0BOS126> alter database datafile 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' resize 20000m;
alter database datafile 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' resize 20000m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
SYSTEM@VKS_SI0BOS126> select *
2 from (
3 select owner, segment_name,
4 segment_type, block_id
5 from dba_extents
6 where file_id =
7 ( select file_id
8 from dba_data_files
9 where file_name = 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' )
10 order by block_id desc
11 )
12 where rownum <= 5
13 ;
OWNER SEGMENT_NAME SEGMENT_TYPE BLOCK_ID
------------------------------ --------------------------------------------------------------------------------- ------------------ ----------
DIL_DATA SYS_LOB0000201574C00003$$ LOBSEGMENT 1147593
DIL_DATA SYS_LOB0000201574C00003$$ LOBSEGMENT 1147585
DIL_DATA OBJECTS TABLE 1147577
ENTW EVAIN_I_QUEUE INDEX 1147569
ENTW EVAIN_I_STATEVAINKEN INDEX 1147553
SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> select *
2 from dba_data_files
3 where file_name = 'D:\ORACLE\ORADATA\VKS\USERS01.DBF';
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
-------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
D:\ORACLE\ORADATA\VKS\USERS01.DBF 4 USERS 2.6844E+10 3276800 AVAILABLE 4 YES 3.4360E+10 4194302 160 2.6843E+10 3276784 ONLINE
SYSTEM@VKS_SI0BOS126>
Hi Tom,
I think that I have same problem as matthias on 10gR2 here it is:
select 'alter database datafile '''||FILE_NAME||''' resize ' ||
ceil( (nvl(hwm,1)*8192)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.FILE_ID = B.FILE_ID(+)
and CEIL( blocks*8192/1024/1024) -
CEIL( (NVL(HWM,1)*8192)/1024/1024 ) > 0
and a.file_id = 14;
CMD
-------------------------------------------------------------------------------------------------------------
alter database datafile '+DATA/crp4/datafile/apps_ts_interface.341.722527955' resize 2092m;
Error starting at line 13 in command:
alter database datafile '+DATA/crp4/datafile/apps_ts_interface.341.722527955' resize 2092m
Error report:
SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
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.
select * from (
select tablespace_name,BLOCK_ID START_BLOCK, BLOCK_ID+blocks END_BLOCK
from DBA_FREE_SPACE where FILE_ID = 14 order by BLOCK_ID desc
) where rownum <= 5;
TABLESPACE_NAME START_BLOCK END_BLOCK
------------------------------ ---------------------- ----------------------
APPS_TS_INTERFACE 1615593 1620473
APPS_TS_INTERFACE 1615577 1615593
APPS_TS_INTERFACE 1615561 1615577
APPS_TS_INTERFACE 1615545 1615561
APPS_TS_INTERFACE 1615529 1615545
as you can see there are two free extents at the end but they are not coalesced (LMTS) don't know why
this is the tablespace:
select * from dba_tablespaces where tablespace_name = 'APPS_TS_INTERFACE'; TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION BIGFILE ------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- --------- --------- --------- ------------- ----------------- --------------- ---------- ------------------------ ------------------- ----------- ------- APPS_TS_INTERFACE 8192 131072 131072 1 2147483645 0
select BLOCK_ID START_BLOCK, BLOCK_ID+blocks END_BLOCK, ROUND(BYTES/1024/1024,2) FREE_MB
from dba_free_space where file_id = 14 order by block_id desc ;
START_BLOCK END_BLOCK FREE_MB
---------------------- ---------------------- ----------------------
1627001 1628153 9
1626985 1627001 0,13
1626969 1626985 0,13
1626953 1626969 0,13
1626937 1626953 0,13
1626921 1626937 0,13
1626905 1626921 0,13
1626889 1626905 0,13
1626873 1626889 0,13
1625705 1626873 9,13
1625689 1625705 0,13
1625673 1625689 0,13
1625657 1625673 0,13
1625609 1625657 0,38
1625593 1625609 0,13
1625577 1625593 0,13
1625561 1625577 0,13
1625545 1625561 0,13
1625513 1625545 0,25
1625497 1625513 0,13
1015817 1625497 4763,13
1010265 1015817 43,38
select 'segments', sum(bytes)/1024/1024 mbytes
from dba_segments
where tablespace_name = 'MADHRP_SUM_INDEX' and RELATIVE_FNO=474
enable row movement
as
select rownum id, rpad('*',1000,'*') data,object_name,object_type
from all_objects;
dbms_stats.gather_table_stats(user,'T');
delete from t where mod(id,4) = 0;
Commit;
dbms_stats.gather_table_stats(user,'T');
select blocks,empty_blocks from user_tables where table_name = 'T';
Original -->7428 ------- 0
Shrink ---->4754 ------- 0
Move ---->5576 ------- 0
select sum(bytes)/(1024*1024) size_in_MB,sum(extents) extent_num from user_segments where segment_name = 'T';
Original -->59 ------ 74
Shrink ---->37.8 ---- 53
Move ---->44 ------ 59select sum(bytes)/(1024*1024) size_in_MB,sum(extents) extent_num from user_segments where segment_name = 'T';
select blocks,empty_blocks from user_tables where table_name = 'T';
exec dbms_stats.gather_table_stats(user,'T');
sys@ORCL> create tablespace TEST
2 logging
3 datafile 'C:\ORADATA\TEST01.DBF'
4 size 100m
5 extent management local uniform size 1M
6 segment space management auto;
Tablespace created.
sys@ORCL> drop tablespace TEST;
Tablespace dropped.
sys@ORCL> create tablespace TEST
2 logging
3 datafile 'C:\ORADATA\ORCL\TEST01.DBF'
4 size 100m
5 extent management local uniform size 1M
6 segment space management auto;
Tablespace created.
sys@ORCL> set verify off
sys@ORCL> column file_name format a50 word_wrapped
sys@ORCL> column smallest format 999,990 heading "Smallest|Size|Poss."
sys@ORCL> column currsize format 999,990 heading "Current|Size"
sys@ORCL> column savings format 999,990 heading "Poss.|Savings"
sys@ORCL> break on report
sys@ORCL> compute sum of savings on report
sys@ORCL>
sys@ORCL> column value new_val blksize
sys@ORCL> select value from v$parameter where name = 'db_block_size'
2 /
VALUE
---------------------------------------------------------------------------------------
8192
sys@ORCL>
sys@ORCL> select file_name,
2 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
3 ceil( blocks*&&blksize/1024/1024) currsize,
4 ceil( blocks*&&blksize/1024/1024) -
5 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
6 from dba_data_files a,
7 ( select file_id, max(block_id+blocks-1) hwm
8 from dba_extents
9 group by file_id ) b
10 where a.file_id = b.file_id(+)
11 /
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
C:\ORADATA\ORCL\SYSTEM01.DBF 484 1,000 516
C:\ORADATA\ORCL\UNDOTBS01.DBF 20 25 5
C:\ORADATA\ORCL\USERS01.DBF 1 5 4
C:\ORADATA\ORCL\SYSAUX01.DBF 274 280 6
C:\ORADATA\ORCL\TEST01.DBF 1 100 99
--------
sum 630
sys@ORCL>
sys@ORCL> column cmd format a75 word_wrapped
sys@ORCL>
sys@ORCL> select 'alter database datafile '''||file_name||''' resize ' ||
2 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
3 from dba_data_files a,
4 ( select file_id, max(block_id+blocks-1) hwm
5 from dba_extents
6 group by file_id ) b
7 where a.file_id = b.file_id(+)
8 and ceil( blocks*&&blksize/1024/1024) -
9 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
10 /
CMD
---------------------------------------------------------------------------
alter database datafile 'C:\ORADATA\ORCL\SYSTEM01.DBF' resize 484m;
alter database datafile 'C:\ORADATA\ORCL\UNDOTBS01.DBF' resize 20m;
alter database datafile 'C:\ORADATA\ORCL\USERS01.DBF' resize 1m;
alter database datafile 'C:\ORADATA\ORCL\SYSAUX01.DBF' resize 274m;
alter database datafile 'C:\ORADATA\ORCL\TEST01.DBF' resize 1m;
sys@ORCL> alter database datafile 'C:\ORADATA\ORCL\TEST01.DBF' resize 1m;
alter database datafile 'C:\ORADATA\ORCL\TEST01.DBF' resize 1m
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
sys@ORCL> alter database datafile 'C:\ORADATA\ORCL\TEST01.DBF' resize 1088k;
Database altered.
sys@ORCL>
