ORACLE数据库中常用的统计语句信息【持续更新】

在日常使用ORACLE的过程中，我们需要经常关注的ORALCE最大连接数、并发连接数、当前连接数、用户系统权限等等，涉及的统计很多很多，但是有时候确实很难记住，因此在这里做一个备注。

1、查询oracle的连接数

SELECT COUNT(*) FROM V$SESSION;

2、查询oracle的并发连接数

SELECT COUNT(*) FROM V$SESSION WHERE STATUS='ACTIVE';

3、 查看当前的连接数

SELECT COUNT(*) FROM V$PROCESS;

4、数据库允许的最大连接数

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'processes'; 

5、查看不同用户的连接数

SELECT USERNAME,COUNT(USERNAME) FROM V$SESSION WHERE USERNAME IS NOT NULL GROUP BY USERNAME;

6、查看当前哪些用户在哪个机器上操作数据库

SELECT USERNAME,OSUSER,STATUS,SCHEMANAME,MACHINE FROM V$SESSION 
WHERE USERNAME IS NOT NULL AND USERNAME = 'MOBIL' ORDER BY USERNAME,OSUSER;

7、查看所有用户：

SELECT * FROM ALL_USERS;

8、查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;

9、查看角色(只能查看登陆用户拥有的角色)所包含的权限

SELECT * FROM ROLE_SYS_PRIVS;

10、查看用户对象权限：

SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;

11、查看所有角色：

SELECT * FROM DBA_ROLES;

12、查看用户或角色所拥有的角色：

SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

13、查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

SELECT * FROM V$PWFILE_USERS;

14、查看哪些用户、机器、正在操作哪些SQL语句

SELECT b.SID, b.SERIAL#, SPID, b.USERNAME, PADDR, SQL_TEXT, b.MACHINE, LOGON_TIME
FROM V$PROCESS a, V$SESSION b, V$SQLAREA c
WHERE a.ADDR = b.PADDR AND b.SQL_HASH_VALUE = c.HASH_VALUE
ORDER BY b.SID,b.SERIAL#,SPID

15、杀死占用ORACLE资源的进程

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

16、查询数据库表和表字段相关信息

-- 查询当前用户下所有表 --
SELECT * FROM USER_TABLES;

-- 查询用户有所表下字段信息 --
SELECT * FROM USER_TAB_COLUMNS;

-- 查询用户所有字段信息的注释 --
SELECT * FROM USER_COL_COMMENTS;

-- 查询用户字段约束和字段对应关系 --
SELECT * FROM USER_CONS_COLUMNS;

-- 查询用户所有约束信息 --
SELECT * FROM USER_CONSTRAINTS;

-- 查询全部表和对应表结构 --
SELECT
	T.DATABASE_NAME AS DATABASE_NAME,
	T.TABLE_NAME AS TABLE_NAME,
	T.COLUMN_NAME AS COLUMN_NAME,
	T.COLUMN_TYPE AS COLUMN_TYPE,
	T.DATA_LENGTH AS DATA_LENGTH,
	T.COLUMN_COMMENT AS COLUMN_COMMENT,
	B.CONSTRAINT_TYPE AS CONSTRAINT_TYPE 
FROM
	(
	SELECT
		UB.TABLESPACE_NAME AS DATABASE_NAME,
		UTC.TABLE_NAME AS TABLE_NAME,
		UTC.COLUMN_NAME AS COLUMN_NAME,
		UTC.DATA_TYPE AS COLUMN_TYPE,
		UTC.DATA_LENGTH AS DATA_LENGTH,
		UCC.COMMENTS AS COLUMN_COMMENT 
	FROM
		USER_TABLES UB
		LEFT JOIN USER_TAB_COLUMNS UTC ON UB.TABLE_NAME = UTC.TABLE_NAME
		LEFT JOIN USER_COL_COMMENTS UCC ON UTC.COLUMN_NAME = UCC.COLUMN_NAME 
		AND UTC.TABLE_NAME = UCC.TABLE_NAME 
	) T
	LEFT JOIN (
	SELECT
		UCC.TABLE_NAME AS TABLE_NAME,
		UCC.COLUMN_NAME AS COLUMN_NAME,
		WM_CONCAT ( UC.CONSTRAINT_TYPE ) AS CONSTRAINT_TYPE 
	FROM
		USER_CONS_COLUMNS UCC
		LEFT JOIN USER_CONSTRAINTS UC ON UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME 
	GROUP BY
		UCC.TABLE_NAME,
		UCC.COLUMN_NAME 
	) B ON T.TABLE_NAME = B.TABLE_NAME 
	AND T.COLUMN_NAME = B.COLUMN_NAME

如有不当之处，请及时指出，谢谢！