oracle怎样查察当前有哪些用户毗连到数据库

可以执行以下语句：
select username,serial#,sid from v$session; ---查询用户会话
alter system kill session ‘serial#,sid ‘;---删除相干用户会话

提议以靠山登岸删除用户会话
1、查询oracle的毗连数
select count(*) from v$session;
2、查询oracle的并发毗连数
select count(*) from v$session where status=‘ACTIVE‘;
3、查察差异用户的毗连数
select username,count(username) from v$session where username is not null group by username;
4、查察全部用户：
select * from all_users;
5、查察用户或脚色体系权限(直接赋值给用户或脚色的体系权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
6、查察脚色(只能查察登岸用户拥有的脚色)所包括的权限
select * from role_sys_privs;
7、查察用户工具权限：
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;
10、查察哪些用户有sysdba或sysoper体系权限(查询时必要响应权限)
select * from V$PWFILE_USERS;

修改数据库应承的最大毗连数：
alter system set processes = 300 scope = spfile;

查察游标数目
Select * from v$open_cursor Where user_name=‘‘

查询数据库应承的最大毗连数：
select value from v$parameter where name = ‘processes‘;
可能：show parameter processes;

查询数据库应承的最大游标数：
select value from v$parameter where name = ‘open_cursors‘

查察oracle版本
select banner from sys.v_$version;

按降序表现用户"SYSTEM"为每个会话打开的游标数
select o.sid,osuser,machine,count(*) num_curs from v$open_cursor o,v$session s where user_name = ‘SYSTEM‘ and o.sid=s.sid group by o.sid,machine order by num_curs desc;

以上转载自 https://www.cnblogs.com/caogang/p/4546072.html

保存两位小数以GB为单元表现数据文件巨细
SELECT ROUND(SUM(BYTES)/1024/1024/1024,2)||‘GB‘ FROM DBA_DATA_FILES;

以MB为单元表空间分组表现数据文件巨细
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 AS MB FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME;

表现数据库中姑且文件的巨细
SELECT SUM(BYTES)/1024/1024/1024 AS GB FROM DBA_TEMP_FILES;

表现数据库中日记文件的巨细
SELECT SUM(BYTES)/1024/1024/1024 AS GB FROM V$LOG;

表现数据库中数据文件、姑且文件、日记文件的总巨细
SELECT SUM(GB) AS GB
FROM(
SELECT SUM(BYTES)/1024/1024/1024 AS GB
FROM DBA_DATA_FILES
UNION ALL
SELECT SUM(BYTES)/1024/1024/1024
FROM DBA_TEMP_FILES
UNION ALL
SELECT SUM(BYTES)/1024/1024/1024
FROM V$LOG
);

SELECT ROUND(SUM(BYTES) / 1024 / 1024,2) "USED(M)"
FROM DBA_SEGMENTS
GROUP BY OWNER,TABLESPACE_NAME
ORDER BY SUM(BYTES) DESC;

--1、查察全部表空间及表空间巨细：
select tablespace_name,sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name;

--2、查察全部表空间对应的数据文件：
select tablespace_name,file_name from dba_data_files;

--3、修改数据文件巨细：
alter database datafile ‘H:ORACLEPRODUCT10.1.0ORADATAORACLEUSERS01.DBF‘ RESIZE 10240M;

select file_name,bytes/1024/1024 MB from dba_data_files;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!