select file_name,tablespace_name,bytes/1024,AUTOEXTENSIBLE,MAXBLOCKS,MAXBYTES,status from dba_data_files order by tablespace_name;
select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;
select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files;
ALTER DATABASE DATAFILE ‘/uo1/oracle/oatrngdata/a_txn_data03.dbf’ AUTOEXTEND ON maxsize unlimited;
select
‘alter database datafile ‘||
file_name||
‘ ‘||
‘ autoextend on maxsize unlimited;’
from
dba_data_files;
EXEC dbms_stats.gather_database_stats;
EXEC DBMS_STATS.gather_system_stats;
EXEC DBMS_STATS.gather_fixed_objects_stats;
EXPLAIN PLAN FOR SELECT * FROM subject;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
alter table subject enable row movement;
alter table subject shrink space;
–check progress
SELECT *
FROM v$session_longops
WHERE opname LIKE ‘%Gather%’ AND time_remaining != 0
ORDER BY SID;
–check stats update status view
DBA_TAB_STATS_HISTORY
USER_TAB_STATS_HISTORY
ALL_TAB_STATS_HISTORY
set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
— get highest block id from each datafiles ( from x$ktfbue as we don’t need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
— join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
— join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks(bytes/blocks),510241024) hwm_bytes,bytes,autoextensible,maxbytes from hwmts right join dba_data_files using(tablespace_name,relative_fno) ) select case when autoextensible=’YES’ and maxbytes>=bytes then — we generate resize statements only if autoextensible can grow back to current size ‘/ reclaim ‘||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||’M from ‘||to_char(ceil(bytes/1024/1024),999999)||’M / ‘ ||’alter database datafile ”’||file_name||”’ resize ‘||ceil(hwm_bytes/1024/1024)||’M;’ else — generate only a comment when autoextensible is off ‘/ reclaim ‘||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||’M from ‘||to_char(ceil(bytes/1024/1024),999999)
||’M after setting autoextensible maxsize higher than current size for file ‘
|| file_name||’ /’ end SQL from hwmdf where bytes-hwm_bytes>10241024 — resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/
/* reclaim 4995M from 5000M after setting autoextensible maxsize higher than current size for file /Data/app/oracle/shiredb/SIEBEL_APP_DATA02.dbf / / reclaim 2356M from 10000M / alter database datafile ‘/Data/app/oracle/shiredb/users01.dbf’ resize 7645M; / reclaim 2088M from 4440M / alter database datafile ‘/Data/app/oracle/shiredb/undotbs01.dbf’ resize 2353M; / reclaim 1995M from 2000M after setting autoextensible maxsize higher than current size for file /Data/app/oracle/shiredb/SIEBEL_APP_INDEX02.dbf / / reclaim 1477M from 30315M / alter database datafile ‘/Data/app/oracle/shiredb/SIEBEL_APP_DATA.dbf’ resize 28839M; / reclaim 1448M from 3000M / alter database datafile ‘/Data/app/oracle/shiredb/system01.dbf’ resize 1553M; / reclaim 838M from 2000M / alter database datafile ‘/Data/app/oracle/shiredb/sysaux01.dbf’ resize 1163M; / reclaim 498M from 500M / alter database datafile ‘/Data/app/oracle/shiredb/UNDOTBS2.dbf’ resize 3M; / reclaim 113M from 1746M */ alter database datafile ‘/Data/app/oracle/shiredb/SIEBEL_APP_INDEX.dbf’ resize 1634M;
EXPLAIN PLAN FOR SELECT * FROM subject;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3975859072
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 46728 | 16M| 421 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| SUBJECT | 46728 | 16M| 421 (2)| 00:00:01 |
