常用SQL

最新推荐文章于 2024-08-15 16:05:29 发布

Sarielya

最新推荐文章于 2024-08-15 16:05:29 发布

阅读量98

点赞数

本文链接：https://blog.csdn.net/Sarielya/article/details/108361850

版权

常用SQL

查看会话

SELECT DATEDIFF(SS,LAST_SEND_TIME,SYSDATE),* 
FROM V$SESSIONS 
WHERE STATE = 'ACTIVE' 
ORDER BY 1 DESC;

SELECT * 
FROM V$SESSIONS 
WHERE STATE='ACTIVE' AND USER_NAME<>'SYSDBA' 
ORDER BY USER_NAME,SQL_TXT;

杀死会话

SP_CLOSE_SESSION(SESS_ID);

查看数据量

SELECT COUNT(*) FROM

查看有哪些数据库实例在运行

select NAME from V$INSTANCE;

查看数据库版本号

select * from v$version; 
select *from SYS.V$LICENSE;

查看系统信息

SELECT * FROM V$SYSTEMINFO

查看系统信息

SELECT * FROM V$SYSTEMINFO

查看内存信息

SELECT * FROM V$VPOOL;
SELECT * FROM V$VBUFFERPOLL
SELECT * FROM V$BUFFER

查看MAL 信息

SELECT * FROM V$MAL_INFO 
SELECT * FROM V$MAL_USING_LETTERS 
SELECT * FROM V$MAL_SYS

查询用户拥有的表权限

select grantee ,
     	  owner ,
     	  table_name,
     	  privilege ,
     	  grantable 
from dba_tab_privs 
where grantee=' ';

为指定的表的列添加注释

comment on column tablename.colname is '注释内容';

查询用户拥有的系统权限

select
	grantee,
	privilege,
	admin_option 
from dba_sys_privs 
where grantee='';

查询列信息

SELECT 
	T.SCHNAME,
	T.TVNAME,
	T.COLNAME,
	T.TYPE$,
	COM.COMMENT$,
	T.LENGTH$,
	T.NULLABEL  
FROM
(
    SELECT 
    	SCH.NAME,
    	TAB.NAME,
    	COL.NAME,
    	COL.TYPE$,
    	COL.LENGTH$,
    	COL.NULLABLE
    FROM SYSOBJECT TAB,
    		SYSOBJECT SCH,
    		SYSCOLUMNS COL
    WHERE TAB.ID=COL.ID AND SCH.ID=TAB.SCHID
)
T,
SYSCOLUMNCOMMENTS COM
WHERE
T.COLNAME=COM.COLNAME (+) 
 AND T.TVNAME=COM.TVNAME (+) 
 AND T.SCHNAME=COM.SCHNAME (+)  
 AND T.SCHNAME=' ' 
 AND T.TVNAME=' ';