转自:
http://ask.orasos.com/thread-11-1-1.html
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
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(+)
/
--上面的语句是查询datafile的现状,本行汉字是我自己加的。惜总的sql没有本行汉字。
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/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*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
--上面的语句是生成 datafile的resize语句。本行汉字是我自己加的。惜总的sql没有本行汉字。
下面来试验一下:
[oracle@rhel63single ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 22 18:44:43 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set pages 200
SQL> set verify off
SQL> column file_name format a50 word_wrapped
SQL> column smallest format 999,990 heading "Smallest|Size|Poss."
SQL> column currsize format 999,990 heading "Current|Size"
SQL> column savings format 999,990 heading "Poss.|Savings"
SQL> break on report
SQL> compute sum of savings on report
SQL>
SQL> column value new_val blksize
SQL> select value from v$parameter where name = 'db_block_size'
2 /
VALUE
--------------------------------------------------------------------------------
8192
SQL> SQL> 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
-------------------------------------------------- -------- -------- --------
/u01/app/oracle/oradata/test/system01.dbf 801 810 9
/u01/app/oracle/oradata/test/tb_test_01.dbf 5 5 0
/u01/app/oracle/oradata/test/test_uni_sz_1m_01.dbf 13 200 187
/u01/app/oracle/oradata/test/sysaux01.dbf 3,219 3,310 91
/u01/app/oracle/oradata/test/users01.dbf 348 365 17
/u01/app/oracle/oradata/test/ten01.dbf 20 50 30
/u01/app/oracle/oradata/test/ts2.dbf 201 500 299
/u01/app/oracle/oradata/test/undotbs01.dbf 69 240 171
/u01/app/oracle/oradata/test/test01.dbf 7 50 43
/u01/app/oracle/oradata/test/test_uni_sz_2m_01.dbf 7 50 43
/u01/app/oracle/oradata/test/test.dbf 1
/u01/app/oracle/oradata/test/ts1.dbf 1 500 499
--------
sum 1,389
12 rows selected.
SQL> column cmd format a75 word_wrapped
SQL>
SQL> 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 '/u01/app/oracle/oradata/test/system01.dbf' resize
801m;
alter database datafile
'/u01/app/oracle/oradata/test/test_uni_sz_1m_01.dbf' resize 13m;
alter database datafile '/u01/app/oracle/oradata/test/sysaux01.dbf' resize
3219m;
alter database datafile '/u01/app/oracle/oradata/test/users01.dbf' resize
348m;
alter database datafile '/u01/app/oracle/oradata/test/ten01.dbf' resize
20m;
alter database datafile '/u01/app/oracle/oradata/test/ts2.dbf' resize 201m;
alter database datafile '/u01/app/oracle/oradata/test/undotbs01.dbf' resize
69m;
alter database datafile '/u01/app/oracle/oradata/test/test01.dbf' resize
7m;
alter database datafile
'/u01/app/oracle/oradata/test/test_uni_sz_2m_01.dbf' resize 7m;
alter database datafile '/u01/app/oracle/oradata/test/ts1.dbf' resize 1m;
10 rows selected.
SQL>
一共12个datafile,为啥resize的时候返回10行呢?因为生成resize的sql是有条件的:
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0