oracle 把datafile的size用resize方法变成最小
来源:互联网 发布:java磁条读卡器 编辑:程序博客网 时间:2024/06/05 21:56
把datafile的size用resize方法变成最小
SQL> 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 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
1)Oracle block的大小
SQL>select value from v$parameter where name = 'db_block_size';
SQL>select value from v$parameter where name = 'db_block_size';
VALUE
--------------------------------------------------------------------------------
8192
--------------------------------------------------------------------------------
8192
2)算出每个文件的信息
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(+)
/
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/oracle/oradata/orcl/system01.dbf 705 710 5
/oracle/product/10.2.0/db_1/dbs/tivoliorts.dbf 2 250 248
/oracle/oradata/orcl/sysaux01.dbf 764 810 46
/oracle/oradata/orcl/users01.dbf 2 5 3
/oracle/oradata/orcl/undotbs01.dbf 87 100 13
/oracle/ts_cus/ts_cus.dbf 2 100 98
/oracle/oradata/orcl/dyx 1
/oracle/oradata/orcl/ddd.ora 1
/oracle/oradata/orcl/testtb.dbf 1
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(+)
/
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/oracle/oradata/orcl/system01.dbf 705 710 5
/oracle/product/10.2.0/db_1/dbs/tivoliorts.dbf 2 250 248
/oracle/oradata/orcl/sysaux01.dbf 764 810 46
/oracle/oradata/orcl/users01.dbf 2 5 3
/oracle/oradata/orcl/undotbs01.dbf 87 100 13
/oracle/ts_cus/ts_cus.dbf 2 100 98
/oracle/oradata/orcl/dyx 1
/oracle/oradata/orcl/ddd.ora 1
/oracle/oradata/orcl/testtb.dbf 1
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
--------
sum 413
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
--------
sum 413
3)动态生成resizesql语句
column cmd format a75 word_wrapped
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
/
SQL>
CMD
---------------------------------------------------------------------------
alter database datafile '/oracle/oradata/orcl/system01.dbf' resize 705m;
alter database datafile '/oracle/product/10.2.0/db_1/dbs/tivoliorts.dbf' resize 2m;
alter database datafile '/oracle/oradata/orcl/sysaux01.dbf' resize 764m;
alter database datafile '/oracle/oradata/orcl/users01.dbf' resize 2m;
alter database datafile '/oracle/oradata/orcl/undotbs01.dbf' resize 87m;
alter database datafile '/oracle/ts_cus/ts_cus.dbf' resize 2m;
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
/
SQL>
CMD
---------------------------------------------------------------------------
alter database datafile '/oracle/oradata/orcl/system01.dbf' resize 705m;
alter database datafile '/oracle/product/10.2.0/db_1/dbs/tivoliorts.dbf' resize 2m;
alter database datafile '/oracle/oradata/orcl/sysaux01.dbf' resize 764m;
alter database datafile '/oracle/oradata/orcl/users01.dbf' resize 2m;
alter database datafile '/oracle/oradata/orcl/undotbs01.dbf' resize 87m;
alter database datafile '/oracle/ts_cus/ts_cus.dbf' resize 2m;
6 rows selected.
0 0
- oracle 把datafile的size用resize方法变成最小
- 把datafile的size用resize方法变成最小
- 查看datafile的HWM,估算resize最小size脚本
- Resize Oracle Datafile的脚本
- Oracle 9i resize datafile
- DATAFILE的extents的回收resize
- 计算datafile可以resize收缩的空间.
- 计算datafile可以resize收缩的空间.
- Oracle 11g asm中不同au size下datafile的au分布初探
- 用c语言把屏幕变成坐标系的方法:
- 把sqldatareader变成datatable的方法
- EDIUS把单声道变成立体声的方法
- 扩展欧几里得板子题(poj2142)注意把一个数变成最小的符合的正数的方法
- Resize Datafile时ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据
- 修改ORACLE的DATAFILE文件名
- Oracle移动datafile的位置
- Oracle的DataFile大小问题
- Vector的使用:resize、size、reserve、capacity
- oracle中=>符号的意义
- 百度语音识别技术负责人李先刚:如何利用Deep CNN大幅提升识别准确率?
- java中遍历实体类属性和类型,属性值
- 闰年判断
- C与指针 六 一些字符函数
- oracle 把datafile的size用resize方法变成最小
- js案例-4-经典tab栏切换
- POJ 3320 Jessica's Reading Problem 尺取法
- opensips安装配置
- C语言初级小例题矩阵对角线求和
- CentOS7 启用root用户
- 01字串
- iOS二维码限制扫描区域的问题记录
- 14.多态