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，转载请标明出处。