MOVE降低高水位 HWM
MOVE降低高水位 HWM
--创建实验表空间
SQL> create tablespace andy03 datafile '/home/oracle/app/oradata/orcl/andy03.dbf' size 10M autoextend on next 30M;
--创建实验数据
SQL> create table andy03 tablespace andy03 as select * from dba_objects ;
SQL> insert into andy03 select * from dba_objects ;
SQL> commit;
SQL> select BYTES/1024/1024,MAXBYTES/1024/1024 from Dba_Data_Files where tablespace_name ='ANDY03';
-- 模拟系列高水位变化
SQL>col name for a30
SQL>
SELECT UPPER(F.TABLESPACE_NAME) " name",
D.TOT_GROOTTE_MB " size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES " used(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "percent",
F.TOTAL_BYTES "current_free_size(M)",
(SELECT free_space_mb+free_allocate_mb FROM dba_tablespace_free a where a.tablespace_name= f.tablespace_name) "total_ FREE_size",
D.TOT_GROOTTE_MB - F.MAX_BYTES "HWM(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and f.tablespace_name='ANDY03'
ORDER BY 3 desc;
name size(M) used(M) percent current_free_size(M) total_ FREE_size HWM(M)
------------------------------ ---------- ---------- -------- -------------------- ---------------- ----------
ANDY03 40 22 55.00% 18 32745.9844 22
SQL> create table andy04 tablespace andy03 as select * from dba_objects;
SQL>
SELECT UPPER(F.TABLESPACE_NAME) " name",
D.TOT_GROOTTE_MB " size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES " used(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "percent",
F.TOTAL_BYTES "current_free_size(M)",
(SELECT free_space_mb+free_allocate_mb FROM dba_tablespace_free a where a.tablespace_name= f.tablespace_name) "total_ FREE_size",
D.TOT_GROOTTE_MB - F.MAX_BYTES "HWM(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and f.tablespace_name='ANDY03'
ORDER BY 3 desc;
name size(M) used(M) percent current_free_size(M) total_ FREE_size HWM(M)
------------------------------ ---------- ---------- -------- -------------------- ---------------- ----------
ANDY03 40 33 82.50% 7 32734.9844 33
SQL> truncate table andy03;
SQL>
SELECT UPPER(F.TABLESPACE_NAME) " name",
D.TOT_GROOTTE_MB " size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES " used(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "percent",
F.TOTAL_BYTES "current_free_size(M)",
(SELECT free_space_mb+free_allocate_mb FROM dba_tablespace_free a where a.tablespace_name= f.tablespace_name) "total_ FREE_size",
D.TOT_GROOTTE_MB - F.MAX_BYTES "HWM(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and f.tablespace_name='ANDY03'
ORDER BY 3 desc;
name size(M) used(M) percent current_free_size(M) total_ FREE_size HWM(M)
------------------------------ ---------- ---------- -------- -------------------- ---------------- ----------
ANDY03 40 12.06 30.15% 27.94 32755.9244 19.06
-- 说明truncate table后,统计信息发生了变化,查询的 HWM 不是真实的(此时 HWM 值 = 表空间大小 - 表空间对象实际物理空间总和)。
SQL> alter database datafile 10 resize 20M;
alter database datafile 10 resize 20M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
-- 执行成功说明 真实的 HWM 为历史上实际最大值(33 M 为上面操作查询的值)。
SQL> alter database datafile 10 resize 33M;
Database altered.
-- 查询指定表空间内对象的块号
SQL> select segment_name,block_id,blocks FROM dba_extents where Tablespace_name ='ANDY03' order by block_id desc;
-- 查询表空间内所有对象
SQL> col SEGMENT_NAME for a25
SQL>
SELECT t.owner, t.segment_name,SUM(bytes)/1024/1024 "SIZE(M)", t.SEGMENT_TYPE From dba_segments t
WHERE t.tablespace_name = 'ANDY03'
GROUP BY t.owner,t.segment_name,t.SEGMENT_TYPE
ORDER BY SUM(bytes) desc;
OWNER SEGMENT_NAME SIZE(M) SEGMENT_TYPE
------------------------------ ------------------------- ---------- ------------------
SYS ANDY04 11 TABLE
SYS ANDY03 .0625 TABLE
-- 一一开启所有对象 move 。 (按对象 block_id 由小到大 依次 move)
SQL> alter table andy04 move;
说明:
table在进行move操作时,我们只能对它进行select的操作。反过来说,当我们的一个session对table进行DML操作且没有commit时,
在另一个session中是不能对这个table进行move操作的,否则oracle会返回这样的错误信息:ORA-00054
SQL>
SELECT UPPER(F.TABLESPACE_NAME) " name",
D.TOT_GROOTTE_MB " size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES " used(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "percent",
F.TOTAL_BYTES "current_free_size(M)",
(SELECT free_space_mb+free_allocate_mb FROM dba_tablespace_free a where a.tablespace_name= f.tablespace_name) "total_ FREE_size",
D.TOT_GROOTTE_MB - F.MAX_BYTES "HWM(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and f.tablespace_name='ANDY03'
ORDER BY 3 desc;
name size(M) used(M) percent current_free_size(M) total_ FREE_size HWM(M)
------------------------------ ---------- ---------- -------- -------------------- ---------------- ----------
ANDY03 33 12.06 36.55% 20.94 32755.9244 13
-- move后,可resize 。 (原因 block_id 发生了变化)
SQL> alter database datafile 10 resize 13M;
Database altered.
SQL>
SELECT UPPER(F.TABLESPACE_NAME) " name",
D.TOT_GROOTTE_MB " size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES " used(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "percent",
F.TOTAL_BYTES "current_free_size(M)",
(SELECT free_space_mb+free_allocate_mb FROM dba_tablespace_free a where a.tablespace_name= f.tablespace_name) "total_ FREE_size",
D.TOT_GROOTTE_MB - F.MAX_BYTES "HWM(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and f.tablespace_name='ANDY03'
ORDER BY 3 desc;
name size(M) used(M) percent current_free_size(M) total_ FREE_size HWM(M)
------------------------------ ---------- ---------- -------- -------------------- ---------------- ----------
ANDY03 13 12.06 92.77% .94 32755.9244 12.06
最后,HWM 的确下降了。 OK,转载请标明出处。
最新文章
- XSS 前端防火墙 —— 无懈可击的钩子
- Azure Service Febric 笔记:Web API应用
- ArcMap自定义脚本工具制作
- 【转】Android.mk文件语法规范(Android.mk File)
- 【HDOJ】2795 Billboard
- libcurl编译
- 为什么说Neutron不是SDN?
- jq-实战之表格筛选
- Fastify 系列教程二 (中间件、钩子函数和装饰器)
- C++ list forward_list
- TortoiseSVN使用
- html5+hbuilder+夜神模拟器+webview
- Mathematica 代码
- 获取当前TestStep发送的request信息
- Wood Chipping Text Animation
- [work]Spring_Jdbc
- 07_组件三大属性(1)_state
- bzoj1072排列
- 用java做操作系统内核:软盘读写
- HTML标签img--改变图片尺寸
热门文章
- 看完这篇还不会自定义 View ,我跪搓衣板
- 重新编译Nginx指导手册【修复静态编译Openssl的Nginx漏洞 】(转)
- 用户对变量或寄存器进行位操作 、“|=”和“&;=~”操作
- 【BZOJ1786】[Ahoi2008]Pair 配对 DP
- 我们计划为EasyDSS定制开发一款超低延时的EasyPlayer Flash播放器
- iOS SQLite使用
- TCP/UDP server
- The template root requires exactly one element
- Hadoop实战-Flume之Sink Load-balancing(十七)
- 二维码图片流转base64