一、常用的快捷键方式与设置
1、开启自动替换
step1:设置自动替换文本：manager工具-窗口-选项-查询分析器-自动替换（ps：记得点击应用确认）
例子：sf = select * from
step2:查询自动替换快捷键（可按照习惯设置）：manager工具-窗口-选项-常规设置-快捷键-自动替换（ps：记得点击应用确认）
step3:如何使用：sql查询窗内输入 sf ，然乎按ctrl+shift+=
2、开启sql查询窗口行序号
step1：manager工具-窗口-选项-查询分析器-编辑器-勾选显示行号（ps：记得点击应用确认）
step2:预览效果
3、使用sql美化器
step1：编写一段凌乱的sql
step2:快捷方法：ctrl+shift+f 或者选取需要美化的sql鼠标右击-SQL脚本-格式化
step3:效果预览
二、常用的数据库信息sql查询
1、数据库相关信息
a、查询实例
SELECT '实例名称' 数据库选项,INSTANCE_NAME 数据库集群相关参数值 FROM v$instance
union all
SELECT '数据库授权码',(SELECT SERIES_NO FROM V$LICENSE)
UNION ALL
SELECT '数据库有效期',cast((SELECT EXPIRED_DATE FROM V$LICENSE)as VARCHAR)
UNION ALL
SELECT '授权客户',(SELECT AUTHORIZED_CUSTOMER FROM V$LICENSE)
UNION ALL
SELECT '数据库版本',substr(svr_version,instr(svr_version,'(')) FROM v$instance
union all
SELECT '数据库版本小号',(SELECT id_code) FROM v$instance
union all
SELECT '数据库实例路径',(SELECT PARA_VALUE from v$dm_ini where para_name like'%SYSTEM_PATH%')
FROM v$instance
union all
SELECT '字符集',
CASE SF_GET_UNICODE_FLAG() WHEN '0' THEN 'GBK18030' WHEN '1' then 'UTF-8' when '2' then 'EUC-KR' end
union all
SELECT '页大小',cast(PAGE()/1024 as varchar)
union all
SELECT '簇大小',cast(SF_GET_EXTENT_SIZE() as varchar)
union all
SELECT '大小写敏感',cast(SF_GET_CASE_SENSITIVE_FLAG() as varchar)
union all
SELECT '数据库模式',MODE$ from v$instance
union all
SELECT '唯一魔数',cast(permanent_magic as varchar)
union all
SELECT 'LSN',cast(cur_lsn as varchar) from v$rlog
union all
SELECT 'BLANK_PAD_MODE',cast ( BLANK_PAD_MODE() as varchar);例子预览：
b、查询授权信息
SELECT LIC_VERSION "许可证版本号",
SERIES_NO "序列号",
CASE WHEN "SERVER_SERIES"='P' THEN '个人版'
WHEN "SERVER_SERIES"='S' THEN '标准版'
WHEN "SERVER_SERIES"='E' THEN '企业版'
WHEN "SERVER_SERIES"='A' THEN '安全版'
WHEN "SERVER_SERIES"='C' THEN '云版本'
WHEN "SERVER_SERIES"='D' THEN '开发版'
END "服务器颁布类型",
CASE WHEN "SERVER_TYPE" ='1' THEN '正式版'
WHEN "SERVER_TYPE"='2' THEN '测试版'
WHEN "SERVER_TYPE"='3' THEN '试用版'
END "服务器发布类型",
SERVER_VER "服务器版本号",
EXPIRED_DATE "有效日期",
AUTHORIZED_CUSTOMER "用户名称",
AUTHORIZED_USER_NUMBER "授权用户数",
CONCURRENCY_USER_NUMBER "并发连接数",
MAX_CPU_NUM "最大CPU数目",
NOACTIVE_DEADLINE "未激活状态截止日期",
PRODUCT_TYPE "产品类型",
PROJECT_NAME "项目名称",
CPU_TYPE "授权运行的 CPU类型",
OS_TYPE "授权运行的操作系统",
MAX_CORE_NUM "授权最大CPU核个数",
CASE WHEN "CLUSTER_TYPE"='1' THEN '主备'
WHEN "CLUSTER_TYPE"='2' THEN 'MPP'
WHEN "CLUSTER_TYPE"='NULL' THEN '无集群'
END "授权使用的集群类型",
DATE_GEN "KEY的生成日期"
FROM V$LICENSE;c、数据库基本信息
SELECT "NAME" "数据库名称" ,
"CREATE_TIME" "数据库创建时间" ,
case when "ARCH_MODE"='Y' then '归档' else '不归档' end "归档模式" ,
"LAST_CKPT_TIME" "最后一次检查点时间" ,
case when "STATUS$" ='1' then '启动'
when "STATUS$"='2' then '启动，redo 完成'
when "STATUS$"='3' then 'MOUNT'
when "STATUS$"='4' then '打开'
when "STATUS$"='5' then '挂起'
end "库状态" ,
case when "ROLE$" ='0' then '普通库'
when "ROLE$"='1' then '主库'
when "ROLE$"='2' then '备库'
end "角色" ,
"TOTAL_SIZE"*page/1024/1024||'M' "总大小" ,
-- "RAC_NODES" "DSC集群系统中的实例总数" ,
"OPEN_COUNT" "数据库open次数" ,
"STARTUP_COUNT" "数据库启动次数" ,
"LAST_STARTUP_TIME" "数据库最近一次启动时间" ,
page/1024 ||'K' "页大小" ,
SF_GET_EXTENT_SIZE() "簇大小" ,
case when SF_GET_CASE_SENSITIVE_FLAG()='1' then '敏感'
when SF_GET_CASE_SENSITIVE_FLAG()='0' then '不敏感'
end "标识符大小写敏感",
case when SF_GET_UNICODE_FLAG() ='0' then 'GB18030'
when SF_GET_UNICODE_FLAG() ='1' then 'UTF-8'
when SF_GET_UNICODE_FLAG() ='2'then 'EUC-KR'
end "数据库字符集"
FROM V$DATABASE;d、数据库实例信息
SELECT "NAME" "实例名称" ,
"INSTANCE_NUMBER" "实例ID",
"HOST_NAME" "主机名称" ,
"SVR_VERSION" "服务器版本" ,
"DB_VERSION" "数据库版本" ,
"START_TIME" "服务器启动时间" ,
"STATUS$" "系统状态" ,
"MODE$" "数据库模式" ,
"OGUID" "控制文件的 OGUID",
--"RAC_SEQNO" "DSC 序号"
--"RAC_ROLE" "DSC系统角色"
SF_GET_PARA_VALUE(2,'PORT_NUM') "端口号"
FROM V$INSTANCE;e、表空间信息
select C."ID" "表空间 ID",
C."NAME" "表空间名称",
--C."CACHE" "CACHE名" ,
CASE WHEN C."TYPE$"='1' THEN 'DB 类型'
WHEN C."TYPE$"='2' THEN '临时表空间'
END "表空间类型",
CASE WHEN C."STATUS$"='0' THEN 'ONLINE'
WHEN C."STATUS$"='1' THEN 'OFFLINE'
WHEN C."STATUS$"='2' THEN 'RES_OFFLINE'
WHEN C."STATUS$"='3' THEN 'CORRUPT'
END "状态",
C."TOTAL_SIZE"*page/1024/1024||'M' "总大小",
C."FILE_NUM" "包含的文件数",
C."ENCRYPT_NAME" "加密算法名",
C."ENCRYPTED_KEY" "加密密钥",
D.used_per ||'%' "表空间使用率"
from V$TABLESPACE c
join ( SELECT a.id,
100-(sum(b.free_size)*100/sum(b.total_size)) used_per
FROM V$TABLESPACE a,
V$DATAFILE b
where a.id=b.GROUP_ID
group by a.id ) d
on c.id=d.id
order by c.id;f、数据库实例会话信息
SELECT STATE "会话状态" ,
CLNT_IP "客户端IP",
CLNT_TYPE "连接类型",
CURR_SCH "当前模式",
USER_NAME "当前用户",
COUNT(*) "会话数"
FROM V$SESSIONS
GROUP BY STATE ,
CLNT_IP ,
CLNT_TYPE,
CURR_SCH ,
USER_NAME
ORDER BY STATE;2、备份与归档信息
a、归档信息
SELECT "ARCH_NAME" "归档名称",
"ARCH_TYPE" "归档类型",
"ARCH_DEST" "归档目标",
"ARCH_FILE_SIZE" "单个归档文件大小",
"ARCH_SPACE_LIMIT" "归档大小上限",
"ARCH_TIMER_NAME" "定时器名称",
"ARCH_IS_VALID" "归档状态",
"ARCH_WAIT_APPLY" "性能模式",
"ARCH_INCOMING_PATH" "远程归档保存在本地的目录",
case arch_space_limit when 0 then '无限制归档上限，归档总大小为'||
(SELECT SUM(LEN)/(1024*1024) FROM V$ARCH_FILE)||'M'
else
( SELECT SUM(LEN)/(1024*1024)*100 FROM V$ARCH_FILE )/arch_space_limit||'%'
END "已使用归档空间百分比"
FROM V$DM_ARCH_INI;b、备份集信息
-- 会校验每个备份集，占用IO，慎用
SELECT "DEVICE_TYPE" "存储介质类型" ,
"BACKUP_ID" "备份ID" ,
"BACKUP_NAME" "备份名" ,
"BACKUP_PATH" "备份路径" ,
CASE WHEN "TYPE" ='0' THEN '基备份' WHEN "TYPE"='1' THEN '增量备份' WHEN "TYPE"='2' THEN '表备份' WHEN "TYPE"='3' THEN '归档备份' END "备份类型" ,
CASE WHEN "LEVEL" ='0' THEN '联机备份' WHEN "LEVEL"='1' THEN '脱机备份' END "是否脱机备份",
CASE WHEN "RANGE#"='1' THEN '库备份' WHEN "RANGE#"='2' THEN '表空间备份' WHEN "RANGE#"='3' THEN '表级备份' WHEN "RANGE#"='4' THEN '归档备份' END "备份类型" ,
"BASE_NAME" "基备份名" ,
"BACKUP_TIME" "备份时间" ,
"BEGIN_LSN" "备份的起始 LSN值" ,
"END_LSN" "结束备份的 LSN值" ,
"BKP_NUM" "备份片个数" ,
CASE WHEN ( SELECT SF_BAKSET_CHECK(DEVICE_TYPE, BACKUP_PATH) ) ='1' THEN '有效' WHEN ( SELECT SF_BAKSET_CHECK(DEVICE_TYPE, BACKUP_PATH) ) =0 THEN '无效' END "备份校验",
'SELECT SF_BAKSET_CHECK(' ||DEVICE_TYPE ||',' ||BACKUP_PATH||');' "校验命令"
FROM V$BACKUPSET
WHERE BACKUP_TIME>(SYSDATE-7);3、用户相关信息
a、用户信息
select "USERNAME" "用户名",
"ACCOUNT_STATUS" "账号状态",
"LOCK_DATE" "锁定开始的时间",
"EXPIRY_DATE" "密码过期时间",
"DEFAULT_TABLESPACE" "默认表空间",
"TEMPORARY_TABLESPACE" "默认临时表空间",
"CREATED" "创建时间",
"PROFILE" "表空间所在路径",
"EDITIONS_ENABLED" "是否可读" ,
"AUTHENTICATION_TYPE" "用户登陆验证类型" ,
"NOWDATE" "当前日期时刻"
from DBA_USERS;b、用户会话设置信息
select b.name "用户名" ,
a.SESS_PER_USER "最大会话数" ,
a.CONN_IDLE_TIME "最大空闲时间(分钟)",
a.FAILED_NUM "登录失败次数" ,
a.LIFE_TIME "口令有效期(天)" ,
a.REUSE_TIME "口令等待期(天)" ,
a.REUSE_MAX "口令变更次数" ,
a.LOCK_TIME "口令锁定期(分钟)" ,
a.GRACE_TIME "口令宽限期(天)" ,
CASE WHEN a.LOCKED_STATUS='1' THEN 'LOCKED'
ELSE THEN 'OPEN'
END "用户状态",
a.LASTEST_LOCKED "最后一次的锁定时间" ,
a.PWD_POLICY "口令策略" ,
--a.RN_FLAG "是否只读" ,
a.ALLOW_ADDR "允许的 IP 地址" ,
a.NOT_ALLOW_ADDR "不允许的 IP 地址",
a.ALLOW_DT "允许登录的时间段" ,
a.NOT_ALLOW_DT "不允许登录的时间段" ,
a.LAST_LOGIN_DTID "上次登录时间" ,
a.LAST_LOGIN_IP "上次登录 IP 地址" ,
a.FAILED_ATTEMPS "即将被锁定的连续登录失败的次数"
from SYSUSERS a,
SYS.SYSOBJECTS b
where a.id=b.id;4、死锁与阻塞信息
a、死锁查询
select "SEQNO" "编号" ,
"TRX_ID" "事务ID" ,
"SESS_ID" "会话ID" ,
"SESS_SEQ" "会话序列号" ,
"SQL_TEXT" "产生死锁的SQL语句",
"HAPPEN_TIME" "死锁发生的时间"
from V$DEADLOCK_HISTORY;b、事务阻塞信息
WITH TRX_TAB AS
(
SELECT OB1.NAME,
L1.TRX_ID FROM V$LOCK L1,
SYSOBJECTS OB1 WHERE L1.TABLE_ID=OB1.ID
AND OB1.ID <>0
)
,
TRX_SESS AS
( SELECT L.TRX_ID WT_TRXID ,
L.ROW_IDX BLK_TRXID,
L.BLOCKED , ( SELECT NAME TABLE_NAME FROM TRX_TAB A WHERE A.TRX_ID=L.TRX_ID ) WT_TABLE ,
S1.SESS_ID WT_SESS ,
S2.SESS_ID BLK_SESS ,
S1.USER_NAME WT_USER_NAME ,
S2.USER_NAME BLK_USER_NAME,
S1.SQL_TEXT ,
S1.CLNT_IP ,
DATEDIFF(S, S1.LAST_SEND_TIME, SYSDATE) SS
FROM V$LOCK L ,
V$SESSIONS S1,
V$SESSIONS S2
WHERE L.TRX_ID =S1.TRX_ID
AND L.ROW_IDX=S2.TRX_ID
)
SELECT SYSDATE "当前时间" ,
WT_TRXID "所属事务ID" ,
BLK_TRXID "TID锁对象事务ID",
CASE WHEN BLOCKED='1' THEN '是' ELSE THEN '否' END "是否阻塞" ,
WT_TABLE "被阻塞表" ,
WT_SESS "被阻塞的会话ID" ,
BLK_SESS "阻塞的会话ID",
WT_USER_NAME "被阻塞用户" ,
BLK_USER_NAME "阻塞用户" ,
SF_GET_SESSION_SQL(WT_SESS) "被阻塞的SQL" ,
CLNT_IP "客户端IP" ,
SS||'秒' "阻塞时间"
FROM TRX_SESS
WHERE BLOCKED=1;5、作业调度信息
a、作业信息
SELECT A.ID "作业ID号" ,
A.NAME "作业名称" ,
CASE WHEN A."ENABLE"='1' THEN '启用'
WHEN A."ENABLE"='0' THEN '不启用'
END "是否被启用",
A.USERNAME "创建者名称" ,
A.CREATETIME "创建时间" ,
A.MODIFYTIME "最后一次被修改的时间" ,
A.DESCRIBE "作业描述" ,
B.LAST_DATE ||' ' ||B.LAST_SEC "最后一次运行时间",
B.NEXT_DATE ||' ' ||B.NEXT_SEC "下一次运行时间",
B.WHAT "执行任务的PL/SQL块"
FROM SYSJOB.SYSJOBS A,
SYSJOB.USER_JOBS B
WHERE A.ID=B.JOB;b、调度查询
SELECT "ID" "调度ID号" ,
"NAME" "调度的名称" ,
"JOBID" "作业ID号" ,
CASE WHEN "ENABLE"='1' THEN '启用'
WHEN "ENABLE"='0' THEN '不启用'
END "是否启用",
CASE WHEN "TYPE" ='0' THEN '只执行一次'
WHEN "TYPE"='1' THEN '按天执行'
WHEN "TYPE"='2' THEN '按周执行'
WHEN "TYPE"='3' THEN '某个月某一天执行'
WHEN "TYPE"='4' THEN '一个月的第一周第几天执行'
WHEN "TYPE"='5' THEN '一个月的第二周的第几天执行'
WHEN "TYPE"='6' THEN '一个月的第三周的第几天执行'
WHEN "TYPE"='7' THEN '一个月的第四周的第几天执行'
WHEN "TYPE"='8' THEN '一个月的最后一周的第几天执行'
END "调度类型",
CASE WHEN "TYPE" ='0' THEN '只执行一次'
WHEN "TYPE"='1' THEN '每隔' ||FREQ_INTERVAL ||'天执行'
WHEN "TYPE"='2' THEN '每' ||FREQ_INTERVAL ||'周执行'
WHEN "TYPE"='3' THEN '每隔' ||FREQ_INTERVAL ||'个月执行'
ELSE THEN '每隔' ||FREQ_INTERVAL ||'个月执行'
END "执行的频率",
CASE WHEN "TYPE" ='0' THEN '只执行一次'
WHEN "TYPE"='1' THEN '每天执行'
WHEN "TYPE"='2' THEN "FREQ_SUB_INTERVAL" ||'(10进制)'
else THEN '第' ||"FREQ_SUB_INTERVAL" ||'天执行'
END "具体执行的频率" ,
"FREQ_MINUTE_INTERVAL" "一天内每隔多少分钟执行一次",
"STARTTIME" "调度的起始时间" ,
"ENDTIME" "调度结束时间" ,
--"SCHNAME" "驱动触发器所属的数据库模式名" ,--"TRIGNAME" "触发器名" ,
"VALID" "是否合法"
--"DESCRIBE" "注释信息"
FROM SYSJOB.SYSJOBSCHEDULES;c、作业步骤查询
SELECT "EXEC_ID" "作业执行的ID号" ,
"NAME" "作业名" ,
"STEPNAME" "步骤名" ,
"START_TIME" "步骤开始的时间",
"END_TIME" "步骤结束的时间" ,
--"ERRTYPE" ,
"ERRCODE" "错误码" ,
"ERRINFO" "错误描述信息",
"RETRY_ATTEMPTS" "当前重试次数"
FROM SYSJOB.SYSSTEPHISTORIES2 A
WHERE ( SELECT COUNT(*)
FROM SYSJOB.SYSSTEPHISTORIES2 B
WHERE B.NAME = A.NAME
AND B.EXEC_ID >= A.EXEC_ID ) <= 10
ORDER BY A.START_TIME DESC,
A.NAME;6、SQL查询相关信息
a、实例启动以来长sql
SELECT top 20 "SQL_TEXT" "SQL文本",
"EXEC_TIME"/1000 ||'秒' "执行时间" ,
"FINISH_TIME" "执行结束时间",
"N_RUNS" "执行次数"
FROM V$SYSTEM_LONG_EXEC_SQLS
ORDER BY EXEC_TIME DESC;b、占用内存大的sql
select top 20 "SESS_ID" "SESSION的ID",
--"SQL_ID" "语句的SQLID" ,
"SQL_TEXT" "SQL文本" ,
"MEM_USED_BY_K"/1024||'M' "使用的内存",
"FINISH_TIME" "执行结束时间" ,
"N_RUNS" "执行次数"
--"SEQNO" "编号" ,--"TRX_ID" "事务号" ,
--"SESS_SEQ" "会话序列号"
from V$SYSTEM_LARGE_MEM_SQLS
order by mem_used_by_k desc;7、其他
a、共享池相关
select "ADDR" "内存结构地址" ,
"NAME" "内存池名称" ,
"IS_SHARED" "是否共享" ,
"CHK_MAGIC" "是否打开内存校验" ,
"CHK_LEAK" "是否打开泄漏检查" ,
"IS_OVERFLOW" "是否已经触发BAK_POOL的分配",
--"IS_DSA_ITEM" ,
"ORG_SIZE"/1024/1024 ||'M' "初始大小",
"TOTAL_SIZE"/1024/1024 ||'M' "当前总大小",
"RESERVED_SIZE"/1024/1024 ||'M' "已经分配大小",
"DATA_SIZE"/1024/1024 ||'M' "分配的数据占用大小",
"EXTEND_SIZE"/1024/1024 ||'M' "每次扩展的块大小",
"TARGET_SIZE"/1024/1024 ||'M' "扩展的目标大小" ,
"EXTEND_LEN" "扩展链长度" ,
"N_ALLOC" "累计分配次数" ,
"N_EXTEND_NORMAL" "TARGET范围内累计扩展次数" ,
"N_EXTEND_EXCLUSIVE" "超出TARGET累计扩展次数",
"N_FREE" "累计释放次数" ,
"MAX_EXTEND_SIZE"/1024/1024||'M' "最大的扩展块",
"MIN_EXTEND_SIZE"/1024/1024 ||'M' "最小的扩展块" ,
"FILE_NAME" "源文件名" ,
"FILE_LINE" "所在的代码行",
"CREATOR" "创建者线程号"
from V$MEM_POOL;
6937
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
