SOFTELメモ Developer's blog

会社概要ブログ調査依頼採用情報 ...

技術者募集中

【Oracle】SHOW PROCESSLIST っぽいもの

Category software 2015/06/13

問題

Oracleで接続数や接続状況などを見るには？

（MySQLのSHOW PROCESSLIST みたいな機能は？）

答え

以下のSQLで、現在の接続数などがある程度分かる。

select * from v$license;
select * from v$session;

SESSIONS_MAX	SESSIONS_WARNING	SESSIONS_CURRENT	SESSIONS_HIGHWATER	USERS_MAX	CPU_COUNT_CURRENT	CPU_CORE_COUNT_CURRENT	CPU_SOCKET_COUNT_CURRENT	CPU_COUNT_HIGHWATER	CPU_CORE_COUNT_HIGHWATER	CPU_SOCKET_COUNT_HIGHWATER
0	0	21	24	0	16	2	NULL	16	2	NULL

SADDR	SID	SERIAL#	AUDSID	PADDR	USER#	USERNAME	COMMAND	OWNERID	TADDR	LOCKWAIT	STATUS	SERVER	SCHEMA#	SCHEMANAME	OSUSER	PROCESS	MACHINE	TERMINAL	PROGRAM	TYPE	SQL_ADDRESS	SQL_HASH_VALUE	SQL_ID	SQL_CHILD_NUMBER	PREV_SQL_ADDR	PREV_HASH_VALUE	PREV_SQL_ID	PREV_CHILD_NUMBER	MODULE	MODULE_HASH	ACTION	ACTION_HASH	CLIENT_INFO	FIXED_TABLE_SEQUENCE	ROW_WAIT_OBJ#	ROW_WAIT_FILE#	ROW_WAIT_BLOCK#	ROW_WAIT_ROW#	LOGON_TIME	LAST_CALL_ET	PDML_ENABLED	FAILOVER_TYPE	FAILOVER_METHOD	FAILED_OVER	RESOURCE_CONSUMER_GROUP	PDML_STATUS	PDDL_STATUS	PQ_STATUS	CURRENT_QUEUE_DURATION	CLIENT_IDENTIFIER	BLOCKING_SESSION_STATUS	BLOCKING_INSTANCE	BLOCKING_SESSION	SEQ#	EVENT#	EVENT	P1TEXT	P1	P1RAW	P2TEXT	P2	P2RAW	P3TEXT	P3	P3RAW	WAIT_CLASS_ID	WAIT_CLASS#	WAIT_CLASS	WAIT_TIME	SECONDS_IN_WAIT	STATE	SERVICE_NAME	SQL_TRACE	SQL_TRACE_WAITS	SQL_TRACE_BINDS
	342	87	8683819		44	XXX_M	-67	2147483644	1F666B34	NULL	ACTIVE	DEDICATED	44	XXX_M	XXXSV01\Administrator	5588:6064	WORKGROUP\XXXSV01	XXXSV01	XXX.exe	USER		3549843768	4d4ry8m9tcn9s	0		3549843768	4d4ry8m9tcn9s	0	XXX.exe	0	NULL	0	NULL	187925	114088	4	366101	0	16-02-06	19	NO	NONE	NONE	NO	NULL	DISABLED	DISABLED	DISABLED	0	NULL	NO HOLDER	NULL	NULL	28579	116	db file scattered read	file#	4		block#	366101		blocks	32		1740759767	8	User I/O	0	0	WAITING	XXX	DISABLED	FALSE	FALSE
!U8	345	1891	0	k(	0	NULL	0	2147483644	NULL	NULL	ACTIVE	DEDICATED	0	SYS	SYSTEM	5376	XXXSV01	XXXSV01	XXX.exe (CJQ0)	BACKGROUND		0	NULL	0		0	NULL	0	NULL	0	NULL	0	NULL	167456	5102	1	6313	0	16-02-05	18900	NO	NONE	NONE	NO	NULL	DISABLED	DISABLED	DISABLED	0	NULL	NO HOLDER	NULL	NULL	441	2	rdbms ipc message	timeout	500		NULL	0		NULL	0		2723168908	6	Idle	0	206	WAITING	SYS$BACKGROUND	DISABLED	FALSE	FALSE
!h	346	614	8681812		25	XXXSYS_M	0	2147483644	NULL	NULL	INACTIVE	DEDICATED	25	XXXSYS_M	NT AUTHORITY\NETWORK SERVICE	4820:4016	WORKGROUP\XXXSV01	XXXSV01	XXX.exe	USER		0	NULL	NULL		2291225165	9qsbyg2492nkd	0	XXX.exe	0	NULL	0	NULL	154376	10093	5	452156	0	16-02-05	38740	NO	NONE	NONE	NO	NULL	DISABLED	DISABLED	DISABLED	0	NULL	NO HOLDER	NULL	NULL	38	256	SQL*Net message from client	driver id	1413697536	TCP	#bytes	1		NULL	0		2723168908	6	Idle	0	38740	WAITING	XXX	DISABLED	FALSE	FALSE
	347	1271	8678262		44	XXX_M	-67	2147483644	1F62C5AC	NULL	ACTIVE	DEDICATED	44	XXX_M	XXXSV01\Administrator	5428:5156	WORKGROUP\XXXSV01	XXXSV01	XXX.exe	USER		3864123378	g52wnabm53pzk	0		3864123378	g52wnabm53pzk	0	XXX.exe	0	NULL	0	NULL	187901	114094	4	1052990	0	16-02-05	157	NO	NONE	NONE	NO	NULL	DISABLED	DISABLED	DISABLED	0	NULL	NO HOLDER	NULL	NULL	32325	116	db file scattered read	file#	4		block#	1052990	>	blocks	5		1740759767	8	User I/O	0	0	WAITING	XXX	DISABLED	FALSE	FALSE
	348	2906	8683575		44	XXX_M	-67	2147483644	1F64CA04	NULL	ACTIVE	DEDICATED	44	XXX_M	XXXSV01\Administrator	1196:5136	WORKGROUP\XXXSV01	XXXSV01	XXX.exe	USER		3066339887	3g07d8avc98jg	0		3066339887	3g07d8avc98jg	0	XXX.exe	0	NULL	0	NULL	187928	114094	4	399762	0	16-02-06	4	NO	NONE	NONE	NO	NULL	DISABLED	DISABLED	DISABLED	0	NULL	NO HOLDER	NULL	NULL	64032	115	db file sequential read	file#	4		block#	399762		blocks	1		1740759767	8	User I/O	0	0	WAITING	XXX	DISABLED	FALSE	FALSE

（以下略）

関連するメモ

コメント