1 、查詢oracle的連接數(shù)
select count ( * ) from v$session;
2 、查詢oracle的并發(fā)連接數(shù)
select count ( * ) from v$session where status = 'ACTIVE' ;
3 、查看不同用戶的連接數(shù)
select username, count (username) from v$session where username is not null group by username ;
4 、查看所有用戶：
select * from all_users;
5 、查看用戶或角色系統(tǒng)權(quán)限(直接賦值給用戶或角色的系統(tǒng)權(quán)限)：
select * from dba_sys_privs;
select * from user_sys_privs;
6 、查看角色(只能查看登陸用戶擁有的角色)所包含的權(quán)限
select * from role_sys_privs;
7 、查看用戶對(duì)象權(quán)限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
8 、查看所有角色：
select * from dba_roles;
9 、查看用戶或角色所擁有的角色：
select * from dba_role_privs;
select * from user_role_privs;
select * from V$PWFILE_USERS;
select count(*) from v$process --當(dāng)前的連接數(shù)
select value from v$parameter where name = 'processes' --數(shù)據(jù)庫(kù)允許的最大連接數(shù)
修改最大連接數(shù):
alter system set processes = 300 scope = spfile;
重啟數(shù)據(jù)庫(kù):
shutdown immediate;
startup;
--查看當(dāng)前有哪些用戶正在使用數(shù)據(jù)
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
select count(*) from v$session #連接數(shù)
select count(*) from v$session where status='ACTIVE' #并發(fā)連接數(shù)
show parameter processes #最大連接
alter system set processes = value scope = spfile;重啟數(shù)據(jù)庫(kù) #修改連接
SQL> Select count(*) from v$session where status='ACTIVE' ;
COUNT(*)
----------
20
SQL> Select count(*) from v$session;
COUNT(*)
----------
187
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ----------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 450
SQL>
并發(fā)指active,I SEE
SQL> select count(*) from v$session #連接數(shù)
SQL> Select count(*) from v$session where status='ACTIVE' #并發(fā)連接數(shù)
SQL> show parameter processes #最大連接
SQL> alter system set processes = value scope = spfile;重啟數(shù)據(jù)庫(kù) #修改連接
unix 1個(gè)用戶session 對(duì)應(yīng)一個(gè)操作系統(tǒng) process
而 windows體現(xiàn)在線程
DBA要定時(shí)對(duì)數(shù)據(jù)庫(kù)的連接情況進(jìn)行檢查，看與數(shù)據(jù)庫(kù)建立的會(huì)話數(shù)目是不是正常，如果建立了過(guò)多的連接，會(huì)消耗數(shù)據(jù)庫(kù)的資源。同時(shí)，對(duì)一些“掛死”的連接，可能會(huì)需要DBA手工進(jìn)行清理。
以下的SQL語(yǔ)句列出當(dāng)前數(shù)據(jù)庫(kù)建立的會(huì)話情況:
select sid,serial#,username,program,machine,status
from v$session;
輸出結(jié)果為:
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---- ------- ---------- ----------- --------------- --------
1 1 ORACLE.EXE WORK3 ACTIVE
2 1 ORACLE.EXE WORK3 ACTIVE
3 1 ORACLE.EXE WORK3 ACTIVE
4 1 ORACLE.EXE WORK3 ACTIVE
5 3 ORACLE.EXE WORK3 ACTIVE
6 1 ORACLE.EXE WORK3 ACTIVE
7 1 ORACLE.EXE WORK3 ACTIVE
8 27 SYS SQLPLUS.EXE WORKGROUP\\WORK3 ACTIVE
11 5 DBSNMP dbsnmp.exe WORKGROUP\\WORK3 INACTIVE
其中，
SID 會(huì)話(session)的ID號(hào);
SERIAL# 會(huì)話的序列號(hào)，和SID一起用來(lái)唯一標(biāo)識(shí)一個(gè)會(huì)話;
USERNAME 建立該會(huì)話的用戶名;
PROGRAM 這個(gè)會(huì)話是用什么工具連接到數(shù)據(jù)庫(kù)的;
STATUS 當(dāng)前這個(gè)會(huì)話的狀態(tài)，ACTIVE表示會(huì)話正在執(zhí)行某些任務(wù)，INACTIVE表示當(dāng)前會(huì)話沒(méi)有執(zhí)行任何操作;
如果DBA要手工斷開某個(gè)會(huì)話，則執(zhí)行:
alter system kill session \'SID,SERIAL#\'
sql語(yǔ)句
SQL語(yǔ)句如下：
SELECT username, machine, program, status, COUNT (machine) AS
連接數(shù)量
FROM v$session
GROUP BY username, machine, program, status
ORDER BY machine;
顯示結(jié)果（每個(gè)人的機(jī)器上會(huì)不同）
SCHNEIDER|WORKGROUD\WANGZHENG|TOAD.exe|ACTIVE|1
SCHNEIDER|WORKGROUP\597728AA514F49D|sqlplusw.exe|INACTIVE|1
|WWW-Q6ZMR2OIU9V|ORACLE.EXE|ACTIVE|8
PUBLIC|||INACTIVE|0
按主機(jī)名查詢
SELECT COUNT(*) FROM V$SESSION WHERE MACHINE = 'DXMH'; 'DXMH'為主機(jī)名
數(shù)據(jù)恢復(fù)語(yǔ)句
create table informationlaw_bak
as
select * from informationlaw as of TIMESTAMP to_timestamp('20121126 103435','yyyymmdd hh24miss');
//按機(jī)器名分組查
select username,machine,count(username) from v$session where username is not null group by username,machine;
本文為張軍原創(chuàng)文章,轉(zhuǎn)載無(wú)需和我聯(lián)系,但請(qǐng)注明來(lái)自張軍的軍軍小站,個(gè)人博客http://m.sfpk123.com
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
