首页 » ORACLE 9i-23ai » Script: 查看datafile 的HWM,估算resize 最小size
Script: 查看datafile 的HWM,估算resize 最小size
数据文件如果有2g，先create一个大表占了1G,后来建了个几M小表，再把大表drop 掉加purge选项，向把数据文件resize到100M，往往会失败，在磁盘紧张的情况下，想知道数据文件最小值是多少？就要计算datafile HWM,意义和segment的HWM是一样的。
测试一下,分享收集的script
sys@ANBOB>create tablespace tt datafile '/u01/app/oracle/oradata/anbob/tt01.dbf' size 81k;
Tablespace created.
sys@ANBOB>select bytes/1024 from dba_data_files where file_name='/u01/app/oracle/oradata/anbob/tt01.dbf';
BYTES/1024
----------
88
db version:10201
block size :8192
File header: min 1 block
Bitmap: min 64k
Segment header: min 1 block
Segment data block: min 1 block: 8k
Total: 88k
sys@ANBOB>alter database datafile '/u01/app/oracle/oradata/anbob/tt01.dbf' resize 10m;
Database altered.
sys@ANBOB>select bytes/1024 from dba_data_files where file_name='/u01/app/oracle/oradata/anbob/tt01.dbf'
2 ;
BYTES/1024
----------
10240
sys@ANBOB>create table test tablespace tt as select * from dba_objects ;
Table created.
sys@ANBOB>select bytes/1024 from dba_segments where segment_name='TEST' AND owner='SYS';
BYTES/1024
----------
7168
sys@ANBOB>insert into test select * from test;
insert into test select * from test
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TEST by 128 in tablespace TT
sys@ANBOB>select bytes/1024 from dba_segments where segment_name='TEST' AND owner='SYS';
BYTES/1024
----------
9216
sys@ANBOB>select max(block_id+blocks-1)*8192/1024 from dba_extents where segment_name='TEST' AND owner='SYS';
MAX(BLOCK_ID+BLOCKS-1)*8192/1024
--------------------------------
9240
sys@ANBOB>create table test1(id int) tablespace tt;
Table created.
sys@ANBOB>insert into test1 values(1);
1 row created.
sys@ANBOB>commit;
sys@ANBOB>select bytes/1024 from dba_segments where segment_name='TEST1' AND owner='SYS';
BYTES/1024
----------
64
sys@ANBOB>drop table test purge;
Table dropped.
sys@ANBOB>alter database datafile '/u01/app/oracle/oradata/anbob/tt01.dbf' resize 9m;
alter database datafile '/u01/app/oracle/oradata/anbob/tt01.dbf' resize 9m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
sys@ANBOB>col tablespace_name for a18
sys@ANBOB>col file_name for a60
sys@ANBOB>SELECT a.tablespace_name,
file_name,
c.VALUE / 1024 "Blk. size(Kb)",
CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "smallest(Mb) - HWM",
CEIL (blocks * c.VALUE / 1024 / 1024) "currsize(Mb)",
CEIL (blocks * c.VALUE / 1024 / 1024) - CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "savings(Mb)"
FROM dba_data_files a,
(SELECT file_id, MAX (block_id + blocks - 1) hwm
FROM dba_extents
GROUP BY file_id) b,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size') c
WHERE a.file_id = b.file_id(+)
and a.status !='INVALID';
TABLESPACE_NAME FILE_NAME Blk. size(Kb) smallest(Mb) - HWM currsize(Mb) savings(Mb)
------------------ ------------------------------------------------------------ ------------- ------------------ ------------ -----------
TT /u01/app/oracle/oradata/anbob/tt01.dbf 8 10 10 0
MGMT_ECM_DEPOT_TS /u01/app/oracle/oradata/anbob/mgmt_ecm_depot1.dbf 8 10 10 0
SYSAUX /u01/app/oracle/oradata/anbob/sysaux01.dbf 8 303 310 7
TBSAPP /u01/app/oracle/oradata/anbob/tbsapp1.dbf 8 1 20 19
EXAMPLE /u01/app/oracle/oradata/anbob/example01.dbf 8 69 69 0
USERS /u01/app/oracle/oradata/anbob/users01.dbf 8 19 19 0
UNDOTBS1 /u01/app/oracle/oradata/anbob/undotbs01.dbf 8 104 120 16
MGMT_TABLESPACE /u01/app/oracle/oradata/anbob/mgmt.dbf 8 438 440 2
SYSTEM /u01/app/oracle/oradata/anbob/system01.dbf 8 539 540 1
sys@ANBOB>drop table test1 purge;
Table dropped.
sys@ANBOB> SELECT a.tablespace_name,
2 file_name,
3 c.VALUE / 1024 "Blk. size(Kb)",
4 CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "smallest(Mb) - HWM",
5 CEIL (blocks * c.VALUE / 1024 / 1024) "currsize(Mb)",
6 CEIL (blocks * c.VALUE / 1024 / 1024) - CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "savings(Mb)"
7 FROM dba_data_files a,
8 (SELECT file_id, MAX (block_id + blocks - 1) hwm
9 FROM dba_extents
10 GROUP BY file_id) b,
11 (SELECT VALUE
12 FROM v$parameter
13 WHERE NAME = 'db_block_size') c
14 WHERE a.file_id = b.file_id(+)
15 and a.status !='INVALID';
TABLESPACE_NAME FILE_NAME Blk. size(Kb) smallest(Mb) - HWM currsize(Mb) savings(Mb)
------------------ ------------------------------------------------------------ ------------- ------------------ ------------ -----------
TT /u01/app/oracle/oradata/anbob/tt01.dbf 8 1 10 9
MGMT_ECM_DEPOT_TS /u01/app/oracle/oradata/anbob/mgmt_ecm_depot1.dbf 8 10 10 0
SYSAUX /u01/app/oracle/oradata/anbob/sysaux01.dbf 8 303 310 7
TBSAPP /u01/app/oracle/oradata/anbob/tbsapp1.dbf 8 1 20 19
EXAMPLE /u01/app/oracle/oradata/anbob/example01.dbf 8 69 69 0
USERS /u01/app/oracle/oradata/anbob/users01.dbf 8 19 19 0
UNDOTBS1 /u01/app/oracle/oradata/anbob/undotbs01.dbf 8 104 120 16
MGMT_TABLESPACE /u01/app/oracle/oradata/anbob/mgmt.dbf 8 438 440 2
SYSTEM /u01/app/oracle/oradata/anbob/system01.dbf 8 539 540 1
9 rows selected.
NOTE:
任何查询DBA_EXTENTS的代价都是非常昂贵的,在本地管理的表空间里是用x$ktfbue stucture 表现已用的extents情况,在v$lock 视图中能看到在查询dba_extents时会加TT,TO lock type,(TT:Serializes DDL operations on tablespaces;TO:Serializes DDL operations on tablespaces)， 会对tablespace上的每个seg$ 进行递归查询
相关
http://www.anbob.com/?p=1259
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。