学习内容关键字:
时间日期函数、碎片管理
1.达梦关于时间日期函数总结
--查询当前日期
select TRUNC(SYSDATE) from dual;
--查询前一天或N天
select TRUNC(SYSDATE - 1) from dual;
select TRUNC(SYSDATE - n) from dual;
--查询本周周一
SELECT trunc(sysdate,'iw') FROM dual;
SELECT trunc(sysdate,'w') FROM dual;
--查询当前日期是当年第几天
select to_char(sysdate,'ddd') from dual;
--查询本年年初
select trunc(sysdate,'yyyy') from dual;
select trunc(sysdate,'year') from dual;
-- 查询本周是当年的第几周
select to_char(sysdate,'iw') from dual;
--查询几年前日期
select to_char(trunc((sysdate - interval '3' year ),'year'),'yyyy-mm-dd') from dual; --3年前1月1日
select trunc((sysdate - interval '3' year ),'yyyy') from dual; --3年前1月1日
SELECT add_months(SYSDATE, -3) FROM dual; --3月前的今天
select ADD_MONTHS(trunc(sysdate,'yyyy') ,-12) from dual ; --去年第一天
select ADD_MONTHS(sysdate,-12) from dual; --去年的今天
--关于w、ww、iw的用法和区别:
ww:按每年的1月1日为第一周第一天,1月8日为第二周第一天
w:与ww功能类似,不过它是按每月的1日为每周第一天,例如1日为当月第一周第一天,8日为第二周第一天
iw:自然周,为星期一至星期日算一周,且每年的第一个星期一为第一周
select to_char(sysdate,‘ww’) from dual; – 查询今年第几周,每天第一天为第一周第一天
select to_char(sysdate,‘iw’) from dual; – 查询今年第几周,自然周
select to_char(sysdate,‘w’) from dual; --查询本月第几周
select trunc(日期,‘d’) from dual; --意周日为一周起始日期 查询当周第一天
select trunc(日期,‘iw’) from dual;–意周日为一周结束日期 查询当周第一天
2.碎片管理探究
概述:数据库的空间碎片存在于表空间、表和索引中,其形成根本原因是数据库相类似的存储设计原理,直接原因是系统中对表和表数据的删除,即便是Oracle的ASSM也不能避免空间碎片问题,降低碎片率,我们可以更充分利用存储空间,一定程度上提高数据库的响应效率,还能避免因空间资源紧缺导致的异常。
碎片评估方法
方法 | 特点 | 指标解读 | 前提 |
FSFI | 空闲空间碎片指数,优点:快速得出评估结果略,结果指标易读;缺点:结果相对粗略 | FSFI值低于30%,则表明碎片较多 | DBA_FREE_SPACE、DBA_TABLESPACES的查询权限 |
TFSTSFRM | 表空间碎片算法,优点:结果相对精准;缺点:计算相对较慢,需要相对更高的权限(写),指标相对不易读懂 | 空闲碎块越多且空闲总空间越大,则说明碎片较多 | CREATE TABLE权限,DBA_FREE_SPACE查询权限 |
2.1方法1-FSFI
SELECT a.TABLESPACE_NAME,
SQRT(MAX(a.BLOCKS) / SUM(a.BLOCKS)) * (100 / SQRT(SQRT(COUNT(a.BLOCKS)))) FSFI
FROM DBA_FREE_SPACE a, DBA_TABLESPACES b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME
AND b.CONTENTS NOT IN ('TEMPORARY','UNDO')
GROUP BY a.TABLESPACE_NAME
ORDER BY FSFI;
2.2 方法2-TFSTSFRM(适用于disql)
REM NAME:TFSTSFRM.SQL
REM USAGE:"start path/tfstsfgm.sql"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT ON DBA_FREE_SPACE , CREATE TABLE
REM ------------------------------------------------------------------------
REM Main text of script follows:
create table SPACE_TEMP (
TABLESPACE_NAME CHAR(30),
CONTIGUOUS_BYTES NUMBER)
/
declare
cursor query is select *
from dba_free_space
order by tablespace_name, block_id;
this_row query%rowtype;
previous_row query%rowtype;
total number;
begin
open query;
fetch query into this_row;
previous_row := this_row;
total := previous_row.bytes;
loop
fetch query into this_row;
exit when query%notfound;
if this_row.block_id = previous_row.block_id + previous_row.blocks then
total := total + this_row.bytes;
insert into SPACE_TEMP (tablespace_name) values (previous_row.tablespace_name);
else
insert into SPACE_TEMP values (previous_row.tablespace_name, total);
total := this_row.bytes;
end if;
previous_row := this_row;
end loop;
insert into SPACE_TEMP values (previous_row.tablespace_name, total);
commit;
end;
/
set pagesize 60
set newpage 0
set echo off
spool /tmp/contig_free_space.lis
select TABLESPACE_NAME "TABLESPACE NAME",
CONTIGUOUS_BYTES "CONTIGUOUS BYTES"
from SPACE_TEMP
where CONTIGUOUS_BYTES is not null
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;
select tablespace_name, count(*) "# OF EXTENTS", sum(contiguous_bytes) "TOTAL BYTES"
from space_temp
group by tablespace_name;
spool off
drop table SPACE_TEMP
/
2.3 碎片整理方法
方法 | 是否支持在线操作 | 适用碎片 | DM对应方法 |
表空间压缩: alter tablespace <tps_name> coalesce | 是 | 表空间 | 不支持,考虑表空间重建、备份恢复和表空间数据导出导入 |
数据exp/imp及Data Pump | 否 | 表空间、表、索引 | dexp/dimp |
CTAS,即重建表 | 否 | 表 | 支持 |
Move Tablespace,即转移表空间 | 否 | 表 | 不支持,考虑dexp/dimp或重建表 |
Shrink,即空间压缩 | 是 | 表、索引 | 不支持,考虑dexp/dimp或重建表 |
Online Redefinition,即在线重定义 | 是 | 表 | 不支持,考虑dexp/dimp或重建表 |
索引重建: alter index <index_name> rebuild online; | 是 | 索引 | 支持 |
索引压缩: alter index <index_name> coalesce | 是 | 索引 | 不支持,考虑重建索引 |