1.查看当前数据库建立的进程情况:
select count(*) from vKaTeX parse error: Expected 'EOF', got '#' at position 27: …lect sid,serial#̲,username,progr…session;
select program,username,count(1) from v$session group by program,username order by count(1) desc;
----会话连接数
select count(1) from v$session;
------会话连接数最大值
select value from v$parameter where name=‘sessions’;
2.查询数据库当前进程的连接数
select b.MACHINE, b.PROGRAM , count() from v
p
r
o
c
e
s
s
a
,
v
process a, v
processa,vsession b where a.ADDR = b.PADDR and b.USERNAME is not null group by b.MACHINE , b.PROGRAM order by count() desc;
3.查询数据库允许的最大连接数
select value from v
p
a
r
a
m
e
t
e
r
w
h
e
r
e
n
a
m
e
=
′
p
r
o
c
e
s
s
e
s
′
;
或者:
s
h
o
w
p
a
r
a
m
e
t
e
r
p
r
o
c
e
s
s
e
s
;
4.
修改数据库允许的最大连接数
a
l
t
e
r
s
y
s
t
e
m
s
e
t
p
r
o
c
e
s
s
e
s
=
300
s
c
o
p
e
=
s
p
f
i
l
e
;
5.
重启数据库
s
h
u
t
d
o
w
n
i
m
m
e
d
i
a
t
e
;
s
t
a
r
t
u
p
;
6.
查看连接
o
r
a
c
l
e
的所有机器的连接数和状态
s
e
l
e
c
t
m
a
c
h
i
n
e
,
s
t
a
t
u
s
,
c
o
u
n
t
(
∗
)
f
r
o
m
v
parameter where name = 'processes'; 或者:show parameter processes; 4.修改数据库允许的最大连接数 alter system set processes = 300 scope = spfile; 5.重启数据库 shutdown immediate; startup; 6.查看连接oracle的所有机器的连接数和状态 select machine,status,count(*)from v
parameterwherename=′processes′;或者:showparameterprocesses;4.修改数据库允许的最大连接数altersystemsetprocesses=300scope=spfile;5.重启数据库shutdownimmediate;startup;6.查看连接oracle的所有机器的连接数和状态selectmachine,status,count(∗)fromvsessiongroup by machine,statusorder by status;
7.查看oracle进程的使用
SELECt PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM FROM V
P
R
O
C
E
S
S
;
8.
查看不同用户的连接数
s
e
l
e
c
t
u
s
e
r
n
a
m
e
,
c
o
u
n
t
(
u
s
e
r
n
a
m
e
)
f
r
o
m
v
PROCESS; 8.查看不同用户的连接数 select username,count(username) from v
PROCESS;8.查看不同用户的连接数selectusername,count(username)fromvsession where username is not null group by username;
9.查看所有用户
select * from all_users;
10.修改数据库允许的最大连接数
alter system set processes = 300 scope = spfile;
查看游标数量
Select * from v
o
p
e
n
c
u
r
s
o
r
W
h
e
r
e
u
s
e
r
n
a
m
e
=
′
′
查询数据库允许的最大连接数:
s
e
l
e
c
t
v
a
l
u
e
f
r
o
m
v
open_cursor Where user_name='' 查询数据库允许的最大连接数: select value from v
opencursorWhereusername=′′查询数据库允许的最大连接数:selectvaluefromvparameter where name = ‘processes’;
或者:show parameter processes;
查询数据库允许的最大游标数:
select value from vKaTeX parse error: Expected group after '_' at position 74: …nner from sys.v_̲version;
按降序显示用户"SYSTEM"为每个会话打开的游标数
select o.sid, osuser, machine, count(*) num_curs from v
o
p
e
n
c
u
r
s
o
r
o
,
v
open_cursor o, v
opencursoro,vsession s where user_name = ‘SYSTEM’ and o.sid=s.sid group by o.sid, osuser, machine order by num_curs desc;
11.设置数据库密码永不过期
ALTER profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED