DM常用查询SQL命令–系统信息篇
包括数据库版本、实例、统计信息、资源限制信息、进程信息、全局索引 IID 信息、事件信息。
1、查询实例信息
select '版本',SVR_VERSION from v$instance union all
select '数据库名', NAME from v$database union all
select '实例名',INSTANCE_NAME from v$instance union all
select '系统状态',DECODE(STATUS$,'OPEN','打开','MOUNT','配置','SUSPEND','挂起') from v$instance union all
select '实例模式',DECODE(MODE$,'NORMAL','普通模式','PRIMARY','主机模式','STANDBY','备机模式') from v$instance union all
select '是否启用归档',DECODE(ARCH_MODE,'Y','是','N','否') from v$database union all
select '数据文件总大小', TOTAL_SIZE*PAGE/1024/1024||'MB' from v$database union all
select '数据库打开次数', OPEN_COUNT||'' from v$database union all
select '创建时间', cast(CREATE_TIME as varchar(50)) from v$database union all
select '启动时间',START_TIME||'' from v$instance union all
select '最后检查点时间', cast(LAST_CKPT_TIME as varchar(50)) from v$database;
2、授权信息
select '许可证版本' as 名称,'v'||LIC_VERSION as 信息 from v$license union all
select '序列号',SERIES_NO from v$license union all
select '校验码',CHECK_CODE from v$license union all
select '制作日期',cast(DATE_GEN as varchar(30)) from v$license union all
select '最终用户',AUTHORIZED_CUSTOMER from v$license union all
select '项目名称',PROJECT_NAME from v$license union all
select '产品名称',PRODUCT_TYPE||'('||DECODE(SERVER_SERIES,'P','个人版','S','标准版','E','企业版','A','安全版','D','开发版')||')' from v$license union all
select '产品类型',DECODE(SERVER_TYPE,'1','正式版','2','测试版','3','试用版') from v$license union all
select '有效日期',cast(EXPIRED_DATE as varchar(30)) from v$license union all
select '授权系统',OS_TYPE||' '||DECODE(CPU_TYPE,'X86','x86','X64','x64') from v$license union all
select '授权用户数',cast(AUTHORIZED_USER_NUMBER as varchar(30)) from v$license union all
select '授权并发数',cast(CONCURRENCY_USER_NUMBER as varchar(30)) from v$license union all
select '授权CPU个数',cast(MAX_CPU_NUM as varchar(30)) from v$license union all
select '授权CPU核数',cast(MAX_CORE_NUM as varchar(30)) from v$license union all
select '授权集群',DECODE(CLUSTER_TYPE,'0000','无','0001','RAC','0010','读写分离','0011','读写分离、RAC','0100','MPP','0101','MPP、RAC','0110','MPP、读写分离','0111','MPP、读写分离、RAC','1000','主备','1001','主备、RAC','1010','主备、读写分离','1011','主备、读写分离、RAC','1100','主备、MPP','1101','主备、MPP、RAC','1110','主备、MPP、读写分离','1111','主备、MPP、读写分离、RAC') from v$license union all
select '未激活状态截止日期',cast(NOACTIVE_DEADLINE as varchar(30)) from v$license;
3、获取数据库初始化参数
--获取数据库名
SELECT CUR_DATABASE();
--获得系统建库时指定的簇大小
SELECT SF_GET_EXTENT_SIZE ();
--获得系统建库时指定的页大小
SELECT SF_GET_PAGE_SIZE ();--SELECT PAGE();
--获得大小写敏感信息
SELECT SF_GET_CASE_SENSITIVE_FLAG();
--获得系统建库时指定字符集
SELECT SF_GET_UNICODE_FLAG ();
--0 表示 GB18030,1 表示 UTF-8,2 表示 EUC-KR
–dm.ini关键参数
SELECT NAME as 名称,DECODE(TYPE,'READ ONLY','手动','IN FILE','静态','SYS','系统级动态','SESSION','会话级动态')as 类型,VALUE as 当前会话值,SYS_VALUE as 系统值,FILE_VALUE as ini文件值,DESCRIPTION as 参数描述 FROM V$PARAMETER WHERE NAME IN ('INSTANCE_NAME','MAX_OS_MEMORY','MEMORY_POOL','BUFFER','BUFFER_POOLS','MAX_BUFFER','RECYCLE','RECYCLE_POOLS','HJ_BUF_GLOBAL_SIZE','HJ_BUF_SIZE','DICT_BUF_SIZE','TEMP_SIZE','VM_POOL_SIZE','SESS_POOL_SIZE','CACHE_POOL_SIZE','WORKER_THREADS','TASK_THREADS','MAX_SESSION_STATEMENT','OPTIMIZER_MODE','VIEW_PULLUP_FLAG','COMPATIBLE_MODE','SVR_LOG','MAX_SESSIONS','USE_PLN_POOL','ENABLE_MONITOR','OLAP_FLAG','ARCH_INI');
4、获取数据库唯一标志 sguid
SELECT SF_GET_SGUID();
5、获取数据库永久魔数
SELECT PERMANENT_MAGIC();
6、获取一个唯一编码串。
select GUID () ;
7、查看数据字典信息
select
type 字典对象类型 ,
name 对象名 ,
schid 对象所属模式,
id 对象ID ,
pid 父ID
from
v$dict_cache_item
where
type='TRIGGER'
order by
name;
/*注:TYPE 列对应类型如下:
DB、TABLE、VIEW、INDEX、USER、ROLE、PROC、TRIGGER、CONSTRAINT、SECHEMA、SEQUENCE、
DBLINK、SYSROLE、PACKAGE、OBJECT、SYNOM、CRYPT、CIPHER、IDENTITY*/
8、显示系统统计信息
select
ID 统计对象ID ,
CLASSID 统计对象类别ID,
NAME 统计对象名 ,
STAT_VAL 统计值
from
V$SYSSTAT
where
id=11
order by
2;
/*
统计对象所属类别 ID。1:字典信息;2:SQL;3:事务;4:检查点;5:RLOG;6:UNDO;7:IO;8:B 树;9:
网络;10:文件;11:内存;12:CPU;13:OS;14:
缓冲区;15:限流控制;20:其它
*/
select
ID ,
CLASSID,
NAME ,
STAT_VAL
from
V$SYSSTAT
where
id in(11, 12, 13)
order by
2;
9、查询的对象的依赖关系
select
OWNER 对象的模式名 ,
NAME 对象名 ,
TYPE 对象类型 ,
REFERENCED_OWNER 被引用对象的模式名,
REFERENCED_NAME 被引用对象名 ,
REFERENCED_TYPE 被引用对象类型
from
"DBA_DEPENDENCIES"
where
owner='GAORUI';
10、服务器信息
--CPU信息
select N_CPU from V$SYSTEMINFO;
--内存信息
select TOTAL_PHY_SIZE/1024/1024/1024||'G' TOTAL_PHY,FREE_PHY_SIZE/1024/1024/1024||'G' FREE_PHY,TOTAL_VIR_SIZE/1024/1024/1024||'G' TOTAL_VIR,FREE_VIR_SIZE/1024/1024/1024||'G' FREE_VIR from V$SYSTEMINFO;
--磁盘信息
select TOTLE_DISK_SIZE/1024/1024/1024||'G' TOTAL,FREE_DISK_SIZE/1024/1024/1024||'G' FREE from V$SYSTEMINFO;
--驱动器信息
select DRIVER_NAME,DRIVER_TOTAL_SIZE,DRIVER_FREE_SIZE from V$SYSTEMINFO;
--负载信息
select LOAD_ONE_AVERAGE,LOAD_FIVE_AVERAGE,LOAD_FIFTEEN_AVERAGE from V$SYSTEMINFO;
注:
LOAD_ONE_AVERAGE FLOAT 每分钟平均负载
LOAD_FIVE_AVERAGE FLOAT 每五分钟平均负载
LOAD_FIFTEEN_AVERAGE FLOAT 每十五分钟平均负载
--CPU使用率
select CPU_USER_RATE,CPU_SYSTEM_RATE,CPU_IDLE_RATE from V$SYSTEMINFO;
注:
CPU_USER_RATE FLOAT 用户级的 CPU 使用率
CPU_SYSTEM_RATE FLOAT 用户级的 CPU 使用率
CPU_IDLE_RATE FLOAT 用户级的 CPU 使用率
--网络信息
select SEND_BYTES_TOTAL/1024/1024/1024||'G' as SEND_TOTAL,RECEIVE_BYTES_TOTAL/1024/1024/1024||'G' as RECEIVE_TOTAL,SEND_BYTES_PER_SECOND/1024||'K' as SEND_PER_SECOND,RECEIVE_BYTES_PER_SECOND/1024||'K' as RECEIVE_PER_SECOND,SEND_PACKAGES_PER_SECOND,RECEIVE_PACKAGES_PER_SECOND
from V$SYSTEMINFO;