查询版本号

SELECT	BANNERFROM	V$VERSIONWHERE	BANNER LIKE 'DM Database%'

查询实例信息

select '版本' as 名称,SVR_VERSION as 信息 from v$instance union allselect '数据库名' as 名称,CUR_DATABASE() union allselect '实例名',INSTANCE_NAME from v$instance union allselect '永久魔术值',to_char(PERMANENT_MAGIC()) union allselect '簇大小',SF_GET_EXTENT_SIZE ()||'页' union allselect '页大小',PAGE()/1024||'K' union allselect '字符集',DECODE(UNICODE,'0','GB18030','1','UTF-8','2','EUC-KR') union allselect '大小写',DECODE(SF_GET_CASE_SENSITIVE_FLAG,'0','不敏感','1','敏感') union allselect '空格填充',DECODE(BLANK_PAD_MODE,'0','否','1','是') union allselect '日志大小',MAX(RLOG_SIZE/1024/1024)||'MB' from v$rlogfile union allselect '归档状态',DECODE(ARCH_MODE,'Y','启用','N','未启用') from v$database union allselect '创建时间', cast(CREATE_TIME as varchar(50)) from v$database;

查询当前会话 id

SELECT SESSID ();

查询当前登录用户 id

SELECT UID();

查询会话信息

SELECT	SESS_ID AS "会话ID",	DECODE(STATE,	'CREATE',	'创建',	'STARTUP',	'启动',	'IDLE',	'空闲',	'ACTIVE',	'活动',	'WAIT',	'等待',	'UNKNOWN',	'未知') AS "会话状态",	CREATE_TIME AS "会话创建时间",	TRX_ID AS "事务ID",	SQL_TEXT AS "SQL",	USER_NAME AS "当前用户",	CURR_SCH AS "当前模式",	CLNT_TYPE AS "连接类型",	DECODE(AUTO_CMT,	'Y',	'是',	'N',	'否') AS "是否自动提交",	DECODE(DDL_AUTOCMT,	'Y',	'是',	'N',	'否') AS "DDL是否自动提交",	APPNAME AS "连接程序名",	CLNT_IP AS "客户机IP",	CLNT_HOST AS "客户机名",	OSNAME AS "客户机系统"FROM	V$SESSIONS;

杀掉会话

--根据SESS_ID杀掉会话SP_CLOSE_SESSION(157792223); 

统计会话信息

SELECT	STATE AS 状态,	CLNT_IP AS 连接IP,	COUNT(*)AS 数量FROM	V$SESSIONSGROUP BY	STATE,	CLNT_IP

查询锁信息

SELECT	ADDR AS "锁地址",	TRX_ID AS "所属事务ID",	LTYPE AS "锁类型",	LMODE AS "锁模式",	DECODE(BLOCKED,	'1',	'是',	'0',	'否') AS "是否阻塞",	TABLE_ID AS "对应表锁ID",	ROW_IDX AS "TID锁事务ID"FROM	V$LOCK;

查询发生死锁的历史记录

SELECT	SESS_ID AS "会话ID",	TRX_ID AS "事务ID",	SQL_TEXT AS "产生死锁的SQL",	HAPPEN_TIME AS "死锁发生时间"FROM	V$DEADLOCK_HISTORY;

查询用户列表

SELECT	TABLESPACE_NAME,	TABLE_NAMEFROM	USER_TABLES

查询系统所有表

SELECT	OWNER,	TABLE_NAMEFROM	ALL_TABLES;

查询是否开启归档日志

SELECT	ARCH_MODEFROM	V$DATABASE;

查询有效归档日志中最小的更改编号

SELECT	MIN(FIRST_CHANGE#) FROM	V$ARCHIVED_LOGWHERE	ARCHIVED = 'YES'	AND DELETED = 'NO'	AND STATUS = 'A'

查询有效归档日志中最大的更改编号

SELECT	MAX(NEXT_CHANGE#)FROM	V$ARCHIVED_LOGWHERE	NAME IS NOT NULL	AND ARCHIVED = 'YES'	AND STATUS = 'A'

查询所有可用的归档日志文件

SELECT	A.NAME AS FILE_NAME,	A.FIRST_CHANGE# FIRST_CHANGE,	A.NEXT_CHANGE# NEXT_CHANGE,	'YES',	NULL,	'ARCHIVED',	A.SEQUENCE# AS SEQ,	A.DICTIONARY_BEGIN,	A.DICTIONARY_ENDFROM	V$ARCHIVED_LOG AWHERE	A.NAME IS NOT NULL	AND A.ARCHIVED = 'YES'	AND A.STATUS = 'A'	AND A.NEXT_CHANGE# > 618083584ORDER BY	7

这边sql 中过滤掉了 SCN 小于的 618083584 的记录

删除归档日志

--保留30天归档SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE - 30);

查询归档日志详情

SELECT	*FROM	SYS."V$ARCHIVED_LOG";

查询下一个redo日志序列号

SELECT	NEXT_SEQFROM	V$RLOG；

查询当前redo 日志序列号

SELECT	NEXT_SEQ -1FROM	V$RLOG；

如何判断 redo 日志发生切换

两次查询当前的 redo日志序列号,如果两次结果不一样，则说明redo日志发生了切换。

查询redo文件信息

SELECT	CLIENT_PATH AS "日志名",	PATH AS "路径",	(RLOG_SIZE / 1024 / 1024) AS "文件大小M",	CREATE_TIME AS "创建时间"FROM	V$RLOGFILE;

查询redo日志详情

SELECT	CKPT_LSN,	FILE_LSN,	FLUSH_LSN,	CUR_LSN,	NEXT_SEQ,	N_MAGIC,	DB_MAGIC,	FLUSH_PAGES,	FLUSHING_PAGES,	CUR_FILE,	CUR_OFFSET,	CKPT_FILE,	CKPT_OFFSET,	FREE_SPACE,	TOTAL_SPACE,	SUSPEND_TIME,	UPD_CTL_LSN,	N_RESERVE_WAIT,	TOTAL_FLUSH_PAGES,	TOTAL_FLUSH_TIMES,	TOTAL_ECPR_FLUSH_PAGES,	GLOBAL_NEXT_SEQ,	N_PRIMAY_EP,	PRIMARY_DB_MAGIC,	CKPT_N_PRIMAY_EP,	CKPT_PRIMARY_DB_MAGIC,	MIN_EXEC_VER,	MIN_DCT_VERFROM	V$RLOG

查询所有表行数

SELECT	TABLE_NAME AS 表名,	OWNER AS 所属用户,	TABLESPACE_NAME AS 所属表空间,	NUM_ROWS AS 行数FROM	DBA_TABLESWHERE	OWNER NOT IN ('SYS', 'SYSTEM', 'SYSAUDITOR', 'SYSJOB', 'SYSSSO', 'CTISYS')	AND TABLESPACE_NAME != 'TEMP';

查询模式下所有表行数

SELECT	OWNER,	TABLE_NAME,	NUM_ROWS,	TABLESPACE_NAMEFROM	DBA_TABLES TWHERE	T.OWNER = UPPER('TEST');

统计各个模式下表数据量

SELECT	OWNER,	SUM(NUM_ROWS)FROM	DBA_TABLESGROUP BY	OWNER;

查询模式下各个表索引

SELECT	TABLE_OWNER AS "表归属",	TABLE_NAME AS "表名",	OWNER AS "索引归属",	INDEX_NAME AS "索引名",	INDEX_TYPE AS "索引类型",	TABLESPACE_NAME AS "索引所在表空间",	JOIN_INDEX AS "是否组合索引",	DECODE(VISIBILITY,	'VISIBLE',	'可见',	'INVISIBLE',	'不可见') AS "索引是否可见"FROM	DBA_INDEXESWHERE	OWNER = 'TEST'

其中 TEST是具体的模式名称

查询模式下有哪些表

SELECT	NAME AS "表名"FROM	SYSOBJECTSWHERE	SUBTYPE$ = 'UTAB'	AND SCHID IN (	SELECT		A.ID	FROM		SYSOBJECTS A,		ALL_USERS B	WHERE		A.TYPE$ = 'SCH'		AND A.PID = B.USER_ID		AND A.NAME = 'TEST');

其中 TEST是具体的模式名称

查询模式下表的总数量

SELECT	count(*)FROM	all_all_tablesWHERE	owner = 'TEST';

其中 TEST是具体的模式名称

查询当前用户下有哪些模式

SELECT	A.NAME AS "模式名",	A.ID AS "模式ID"FROM	SYSOBJECTS A,	ALL_USERS BWHERE	A.TYPE$ = 'SCH'	AND A.PID = B.USER_ID;

查询所有用户信息

SELECT	USER_ID AS "用户ID",	USERNAME AS "用户名",	ACCOUNT_STATUS AS "状态",	DEFAULT_TABLESPACE AS "默认表空间",	CREATED AS "创建时间",	LOCK_DATE AS "锁定开始时间",	EXPIRY_DATE AS "密码有效期截止"FROM	DBA_USERS;

查询表结构信息

-- 查看TEST模式下TEST_TABLE表的结构SELECT	TABLEDEF('TEST', 'TEST_TABLE')FROM	DUAL;

查询数据库实例信息

SELECT	B.SVR_VERSION AS "数据库版本",	C.DB_MAGIC AS "数据库MAGIC",	A.NAME AS "数据库名",	B.INSTANCE_NAME AS "实例名",	B.STATUS$ AS "系统状态",	B.MODE$ AS "模式",	DECODE(A.ARCH_MODE,	'Y',	'是',	'N',	'否') AS "是否归档",	B.START_TIME AS "服务启动时间"FROM	V$DATABASE A,	V$INSTANCE B,	V$RLOG C;

查询表空间信息

SELECT	NAME AS 名称,	DECODE(TYPE$,	'1',	'DB类型',	'2',	'临时表空间')AS 类型,	DECODE(STATUS$,	'0',	'联机',	'1',	'脱机',	'2',	'RES_OFFLINE',	'3',	'CORRUPT')AS 状态,	TOTAL_SIZE * PAGE / 1024 / 1024 AS 总大小MB,	FILE_NUM AS 包含文件数FROM	V$TABLESPACE;

查询数据文件信息

SELECT	PATH AS 文件路径,	(TOTAL_SIZE * PAGE / 1024 / 1024)AS 文件大小MB,	(FREE_SIZE * PAGE / 1024 / 1024)AS 剩余大小MB,	(CAST((TOTAL_SIZE-FREE_SIZE)* 100 / TOTAL_SIZE AS NUMERIC(2,	0))|| '%') AS 使用比例,	DECODE(AUTO_EXTEND,	'0',	'关闭',	'1',	'打开') AS 自动扩展,	NEXT_SIZE AS 扩充尺寸MB,	MAX_SIZE AS 扩充上限MB,	CREATE_TIME AS 创建时间,	MODIFY_TIME AS 修改时间FROM	V$DATAFILE;

查询阻塞信息dm8

WITH TRX_TAB AS(SELECT	DISTINCT O1.NAME,	L1.TRX_IDFROM	V$LOCK L1,	SYSOBJECTS O1WHERE	L1.TABLE_ID = O1.ID	AND O1.ID <> 0),TRX_SESS AS (SELECT	L.TRX_ID WT_TRXID,	L.TID 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(SS, S1.LAST_SEND_TIME, SYSDATE) SSFROM	V$LOCK L,	V$SESSIONS S1,	V$SESSIONS S2WHERE	L.TRX_ID = S1.TRX_ID	AND L.TID = S2.TRX_ID)SELECT	SYSDATE STATTIME,	*FROM	TRX_SESSWHERE	BLOCKED = 1;

dm7

WITH LOCKS AS(SELECT	O.NAME,	L.*,	S.SESS_ID,	S.SQL_TEXT,	S.CLNT_IP,	S.LAST_SEND_TIMEFROM	V$LOCK L,	SYSOBJECTS O,	V$SESSIONS SWHERE	L.TABLE_ID = O.ID	AND L.TRX_ID = S.TRX_ID),LOCK_TR AS(SELECT	TRX_ID WT_TRXID,	ROW_IDX BLK_TRXIDFROM	LOCKSWHERE	BLOCKED = 1),RES AS(SELECT	SYSDATE STATTIME,	T1.NAME,	T1.SESS_ID WT_SESSID,	S.WT_TRXID,	T2.SESS_ID BLK_SESSID,	S.BLK_TRXID,	T2.CLNT_IP,	SF_GET_SESSION_SQL(T1.SESS_ID)FULSQL,	DATEDIFF(SS, T1.LAST_SEND_TIME, SYSDATE)SS,	T1.SQL_TEXT WT_SQLFROM	LOCK_TR S,	LOCKS T1,	LOCKS T2WHERE	T1.LTYPE = 'OBJECT'	AND T1.TABLE_ID <> 0	AND T2.LTYPE = 'OBJECT'	AND T2.TABLE_ID <> 0	AND S.WT_TRXID = T1.TRX_ID	AND S.BLK_TRXID = T2.TRX_ID)SELECT	DISTINCT WT_SQL,	CLNT_IP,	SS,	WT_TRXID,	BLK_TRXIDFROM	RES;

查询等待事件

SELECT	THREAD_ID AS "线程ID",	TRX_ID AS "事务ID",	WAIT_CLASS AS "等待类型号",	WAIT_OBJECT AS "等待对象",	WAIT_START AS "等待开始时间",	TRUNC(WAIT_TIME, 6)/ 1000000 AS "等待时间"FROM	V$WAIT_HISTORY;

查询历史错误信息

SELECT	SESS_ID AS "会话ID",	TRX_ID AS "事务ID",	DECODE(SU_FLAG,	'U',	'用户异常',	'S',	'系统异常',	'P',	'语法异常') AS "错误类型",	SQL_TEXT AS "错误的SQL",	ECPT_CODE AS "错误提示",	ERR_TIME AS "产生时间"FROM	V$RUNTIME_ERR_HISTORY;

清除当日之前的空闲会话

DECLARE  vsessid varchar(50);venddate varchar(8);isessioncnt int;CURSOR c1;BEGIN	vsessid := '';SELECT	to_char(sysdate , 'yyyymmdd')INTO	venddateFROM	dual;--获取指定日期  SELECT	count(1)INTO	isessioncntFROM	v$sessionsWHERE	state = 'IDLE'	AND to_char(last_recv_time, 'yyyymmdd') < venddate;--查看会话数  OPEN c1 FORSELECT	sess_idFROM	v$sessionsWHERE	state = 'IDLE'	AND to_char(create_time, 'yyyymmdd') < venddate;--打开游标loop--循环获取会话ID      IF c1%NOTFOUND THEN EXIT;END IF;FETCH c1INTO	vsessid;sp_close_session(vsessid);--删除会话END loop;--关闭游标  CLOSE c1;END;

清除大于1800的空闲会话

DECLARE  vsessid varchar(50);venddate varchar(8);isessioncnt int;CURSOR c1;BEGIN	vsessid := '';SELECT	to_char(sysdate + 1 , 'yyyymmdd')INTO	venddateFROM	dual;--获取指定日期  SELECT	count(1)INTO	isessioncntFROM	v$sessionsWHERE	state = 'IDLE'	AND to_char(last_recv_time, 'yyyymmdd') < venddate;--查看会话数  IF isessioncnt > 1800 THEN 	      OPEN c1 FORSELECT	sess_idFROM	v$sessionsWHERE	state = 'IDLE'	AND to_char(create_time, 'yyyymmdd') < venddate;--打开游标loop--循环获取会话ID        IF c1%NOTFOUND THEN EXIT;END IF;FETCH c1INTO	vsessid;sp_close_session(vsessid);--删除会话END loop;CLOSE c1;--关闭游标END IF;END;

缩小TEMP表空间

--缩小TEMP表空间大小为32MSP_TRUNC_TS_FILE(3,0,32);

清理执行计划缓存

SP_CLEAR_PLAN_CACHE();

查询备份文件类型

SELECT	DECODE(SF_BAK_GET_TYPE('/opt/bak/all.bak'),	'0',	'全量',	'1',	'增量',	'2',	'B树');

查询备份文件方式

SELECT	DECODE(SF_BAK_GET_LEVEL('/opt/bak/all.bak'),	'0',	'联机备份',	'1',	'脱机备份');

查询备份文件时间

SELECT	SF_BAK_GET_TIME('/opt/bak/all.bak');

查询备份文件簇大小

SELECT	SF_BAK_GET_EXTENT_SIZE('/opt/bak/all.bak')|| '页';

查询备份文件页大小

SELECT	SF_BAK_GET_PAGE_SIZE('/opt/bak/all.bak')/ 1024 || 'K';

查询备份文件大小写

SELECT	DECODE(SF_BAK_GET_CASE_SENSITIVE('/opt/bak/all.bak'),	'0',	'不敏感',	'1',	'敏感');

查询备份文件数据库版本

SELECT	SF_BAK_GET_GLOBAL_VERSION('/opt/bak/all.bak');

查询备份文件是否有归档

SELECT	DECODE(SF_BAK_GET_ARCH_FLAG('/opt/bak/all.bak',	'0',	'未归档',	'1',	'归档');

查询备份文件是否加密

SELECT	DECODE(SF_BAK_GET_ENCRYPT_TYPE ('/opt/bak/all.bak'),	'0',	'未加密',	'1',	'加密');

查询备份文件是压缩

SELECT	DECODE(SF_BAK_GET_COMPRESSED('/opt/bak/all.bak'),	'0',	'未压缩',	'1',	'压缩');

监控数据库内存

--创建基础表CREATE TABLE MEM_POOL("CONTTIME" TIMESTAMP(0),"SUM(ORG)" VARCHAR2(20),"SUM(TOTAL)" VARCHAR2(20));--插入当前内存信息INSERT	INTO	MEM_POOLVALUES (SYSDATE(),(SELECT	sum(ORG_SIZE)/ 1024 / 1024FROM	v$mem_pool),(SELECT	sum(TOTAL_SIZE)/ 1024 / 1024FROM	v$mem_pool));COMMIT;

监控指定索引是否使用

CREATE TABLE T1("C1" CHAR(10)); --创建测试表CREATE INDEX IDX_T1_C1 ON T1("C1" ASC); --创建IDX_T1_C1索引ALTER INDEX SYSDBA."IDX_T1_C1" MONITORING USAGE; --监控IDX_T1_C1索引SELECT * FROM T1 WHERE T1.C1 = '1'; --使用IDX_T1_C1索引ALTER INDEX SYSDBA."IDX_T1_C1" NOMONITORING USAGE; --取消监控INDEX_T1_C1索引SELECT SCH_NAME AS "所属模式",TABLE_NAME AS "所属表",INDEX_NAME AS "索引名称", MONITORING AS "是否监控",USED AS "是否使用",START_MONITORING AS "开始监控时间",END_MONITORING AS "停止监控时间" FROM V$OBJECT_USAGE; --查看监控结果

监控指定模式下索引是否使用监控SYSDBA模式下的索引

SELECT	'ALTER INDEX ' || OWNER || '."' || OBJECT_NAME || '" ' || 'MONITORING USAGE;'FROM	DBA_OBJECTSWHERE	OBJECT_TYPE = 'INDEX'	AND GENERATED = 'N'	AND OWNER = 'SYSDBA';

取消监控SYSDBA模式下的索引

SELECT	'ALTER INDEX ' || OWNER || '."' || OBJECT_NAME || '" ' || 'NOMONITORING USAGE;'FROM	DBA_OBJECTSWHERE	OBJECT_TYPE = 'INDEX'	AND GENERATED = 'N'	AND OWNER = 'SYSDBA'; 

查看监控结果

SELECT	SCH_NAME AS "所属模式",	TABLE_NAME AS "所属表",	INDEX_NAME AS "索引名称",	MONITORING AS "是否监控",	USED AS "是否使用",	START_MONITORING AS "开始监控时间",	END_MONITORING AS "停止监控时间"FROM	V$OBJECT_USAGE;

参考文章：