达梦DM-查看数据库状态信息SQL命令合集

1,查询数据库实例详情

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);

2,授权信息

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;

3,数据库基本信息

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;

4,数据库实例信息

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;

5,表空间信息

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;

6,数据文件使用情况

select"GROUP_ID" "所属的表空间ID" ,
--"ID" "数据库文件 ID" ,
"PATH" "数据库文件路径" ,
"CREATE_TIME" "创建时间" ,
--"STATUS$" "状态" ,
CASE WHEN "RW_STATUS"='1' THEN '读' WHEN "RW_STATUS"='2' THEN '写' end
"读写状态",
"LAST_CKPT_TIME" "最后一次检查点时间" ,
"MODIFY_TIME" "文件修改时间" ,
"MODIFY_TRX" "修改事务" ,
"TOTAL_SIZE"*page/1024/1024
||'M' "总大小",
"FREE_SIZE"*page/1024/1024
||'M' "空闲大小",
"PAGE_SIZE"/1024
||'k' "页大小" ,
CASE WHEN "AUTO_EXTEND"='1' THEN '支持' WHEN "AUTO_EXTEND"='0' THEN
'不支持' end "是否支持自动扩展",
"MAX_SIZE"
||'M' "文件最大大小"
--"NEXT_SIZE"
--||'M' "文件每次扩展大小"
--"MIRROR_PATH" "镜像文件路径"
from
V$DATAFILE；

7,在线重做日志

SELECT
A.FILE_ID "文件ID",
A.PATH "文件路径" ,A.RLOG_SIZE/1024/1024
||'M' "文件大小",
/* B.FREE_SPACE/1024/1024
||'M' "目前可用的日志空间", */
/* B.TOTAL_SPACE/1024/1024
||'M' "日志总空间", */
B.CUR_FILE "记录刷文件前当前文件的ID"
from
(
select * from V$RLOGFILE where true
)
A,
(
select * from V$RLOG where true
)
B;

8,部分参数配置信息

SELECT
"PARA_NAME" "参数名称" ,
"PARA_VALUE" "系统参数值" ,
"MIN_VALUE" "最小值" ,
"MAX_VALUE" "最大值" ,
"MPP_CHK" "是否检查 MPP 节点间参数一致性",
"SESS_VALUE" "会话参数值" ,
"FILE_VALUE" "INI 文件中参数值" ,
"DESCRIPTION" "参数描述" ,
"PARA_TYPE" "参数级别"
FROM
V$DM_INIWHERE
PARA_NAME IN ( 'MAX_OS_MEMORY','MEMORY_POOL', 'MEMORY_TARGET',
'BUFFER', 'BUFFER_POOLS', 'RECYCLE', 'RECYCLE_POOLS', 'DICT_BUF_SIZE',
'WORKER_THREADS', 'TASK_THREADS', 'MAX_SESSIONS',
'TEMP_SPACE_LIMIT', 'ENABLE_MONITOR', 'FAST_COMMIT', 'SVR_LOG',
'HA_INST_CHECK_IP', 'HA_INST_CHECK_PORT', 'RLOG_BUF_SIZE',
'RLOG_POOL_SIZE', 'BUFFER_POOLS', 'FAST_POOL_PAGES',
'FAST_ROLL_PAGES', 'CKPT_RLOG_SIZE', 'CKPT_INTERVAL',
'CKPT_FLUSH_RATE', 'CKPT_FLUSH_PAGES', 'BDTA_SIZE', 'OLAP_FLAG',
'UNDO_RETENTION');

9,归档信息

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;

10,SVR_LOG

SELECT
"PARA_NAME" "参数名称" ,
CASE WHEN "PARA_VALUE"='1' THEN 'SQLLOG已开启' ELSE THEN 'SQLLOG未开
启'END "系统参数值"
-- "MIN_VALUE" "最小值" ,
-- "MAX_VALUE" "最大值" ,
-- "MPP_CHK" "是否检查 MPP 节点间参数一致性",
-- "SESS_VALUE" "会话参数值" ,
-- "FILE_VALUE" "INI 文件中参数值" ,
-- "DESCRIPTION" "参数描述" ,
-- "PARA_TYPE" "参数级别"
FROM
V$DM_INI
WHERE
PARA_NAME = 'SVR_LOG';

11,用户信息

select
"USERNAME" "用户名",
"ACCOUNT_STATUS" "账号状态",
"LOCK_DATE" "锁定开始的时间",
"EXPIRY_DATE" "密码过期时间",
"DEFAULT_TABLESPACE" "默认表空间",
--"TEMPORARY_TABLESPACE" "默认临时表空间",
"CREATED" "创建时间",
"PROFILE" "表空间所在路径",
--"EDITIONS_ENABLED" "是否可读" ,
--"AUTHENTICATION_TYPE" "用户登陆验证类型" ,"NOWDATE" "当前日期时刻"
from
DBA_USERS;

12,用户授权信息

SELECT
"GRANTEE" "被授权用户名" ,
"PRIVILEGE" "权限名称" ,
--"PRIVILEGE_TYPE" "权限类型",
"ADMIN_OPTION" "是否可转授"
FROM
(
SELECT
GRANTEE ,
GRANTED_ROLE PRIVILEGE ,
'ROLE_PRIVS' PRIVILEGE_TYPE,
CASE ADMIN_OPTION WHEN 'Y' THEN 'YES' ELSE 'NO' END ADMIN_OPTION
FROM
DBA_ROLE_PRIVS
UNION
SELECT
GRANTEE ,
PRIVILEGE ,
'SYS_PRIVS' PRIVILEGE_TYPE,
ADMIN_OPTION
FROM
DBA_SYS_PRIVS
UNION
SELECT
GRANTEE,PRIVILEGE
||' ON '
||OWNER
||'.'
||TABLE_NAME PRIVILEGE ,
'TABLE_PRIVS' PRIVILEGE_TYPE,
GRANTABLE
FROM
DBA_TAB_PRIVS
)
WHERE
GRANTEE IN
(
SELECT
USERNAME
FROM
ALL_USERS
WHERE
USERNAME NOT IN ('SYS', 'SYSDBA', 'SYSSSO', 'SYSAUDITOR')
)
ORDER BY
GRANTEE ,
PRIVILEGE_TYPE,
PRIVILEGE;

13,用户会话信息

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;

14,会话信息

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;

15,死锁

select
"SEQNO" "编号" ,
"TRX_ID" "事务ID" ,
"SESS_ID" "会话ID" ,
"SESS_SEQ" "会话序列号" ,
"SQL_TEXT" "产生死锁的SQL语句",
"HAPPEN_TIME" "死锁发生的时间"
from
V$DEADLOCK_HISTORY;

16,事务阻塞信息

WITH
TRX_TAB AS
(
SELECTO1.NAME,
L1.TRX_ID
FROM
V$LOCK L1,
SYSOBJECTS O1
WHERE
L1.TABLE_ID=O1.ID
AND O1.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;

17,备份集信息

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);
会校验每个备份集，占用IO，慎用
作业信息
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;

18,作业调度信息

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;

19,作业步骤

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;

20,作业错误信息

SELECT
EXEC_ID "作业ID" ,
NAME "作业名" ,
STEPNAME "步骤名" ,
START_TIME "步骤开始的时间",
END_TIME "步骤结束的时间" ,
ERRCODE "错误码" ,
ERRINFO "错误描述信息"
FROM
(
SELECT
EXEC_ID ,
NAME ,
STEPNAME ,
START_TIME,
END_TIME ,
ERRTYPE ,
ERRCODE ,
ERRINFO ,
ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY EXEC_ID DESC) RN
FROM
SYSJOB.SYSSTEPHISTORIES2
)
WHEREERRCODE <>0
and RN <=10;

21,共享池相关

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;

22,实例启动以来的长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;

23,使用内存较多的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;

24,查看作业调度步骤命令

select
        b.name "作业名称",
        a.name "步骤名" ,
        a.command "执行语句",
        case when a."TYPE"=0 then '执行为SQL语句'
            when a."TYPE"=1 then '执行基于 V1.0 版本的备份还原（没有 WITHOUT 
LOG 和 PARALLEL 选项）'
            when a."TYPE"=2 then '重组数据库'
            when a."TYPE"=3 then '更新数据库的统计信息'
            when a."TYPE"=4 then '执行 DTS'
            when a."TYPE"=5 then '执行基于 V1.0 版本的备份还原（有 WITHOUT 
LOG 和 PARALLEL 选项）'
            when a."TYPE"=6 then '执行基于 V2.0 版本的备份还原' end "步骤类型",
            a.dbname "数据库名"
from
        SYSJOB.SYSJOBSTEPS a,
        SYSJOB.sysjobs b
where
        a.jobid=b.id