Oracle常用sql总结

常用sql语句

查看oracle的版本

select * from v$version;

查看oracle的产品信息

select * from product_component_version;

查看oracle相关信息

select 
 sys_context('USERENV', 'SERVER_HOST') SERVER_HOST,
 sys_context('USERENV', 'DB_NAME') DB_NAME,
 sys_context('USERENV', 'DB_DOMAIN') DB_DOMAIN, 
 sys_context('USERENV', 'INSTANCE') INSTANCE, 
 sys_context('USERENV', 'HOST') CLIENT_HOST, 
 sys_context('USERENV', 'IP_ADDRESS') CLIENT_IP_ADDRESS, 
 sys_context('USERENV', 'OS_USER') CLIENT_OS_USER, 
 sys_context('USERENV', 'CLIENT_IDENTIFIER') CLIENT_IDENTIFIER, 
 sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA, 
 sys_context('USERENV', 'LANG') LANG, 
 sys_context('USERENV', 'LANGUAGE') LANGUAGE,
 sys_context('USERENV', 'NLS_CALENDAR') NLS_CALENDAR, 
 sys_context('USERENV', 'NLS_CURRENCY') NLS_CURRENCY, 
 sys_context('USERENV', 'NLS_DATE_FORMAT') NLS_DATE_FORMAT, 
 sys_context('USERENV', 'NLS_DATE_LANGUAGE') NLS_DATE_LANGUAGE, 
 sys_context('USERENV', 'NLS_SORT') NLS_SORT, 
 sys_context('USERENV', 'NLS_TERRITORY') NLS_TERRITORY
FROM DUAL;

检查正在运行的session

select inst_id, sid, serial# from gv$session;

查出正在运行的session的相关信息

SELECT S.USERNAME,
S.SID,
S.SERTAL#,
S.INST_ID 
FROM GV$SESSION S, GV$SQLAREA SQ 
WHERE S.STATUS = 'ACTIVE'
AND S.USERNAME IS NOT NULL
AND S.SQL_ID = SQ.SQL_ID;

杀掉正在运行的session

alter system kill session '指定SID, 指定INST_ID, @1';

获取指定表的建表语句

select dbms_metadata.get_DDL('TABLE', '表名', '表空间名') from DUAL;
--其中'表名', '表空间名'，根据自己的实际情况替换。

获取所有的视图

select owner, object_type, object_name, status from all_objects
where object_type = 'VIEW';

获取所有的存储过程和函数

select owner, object_type, object_name, status from all_objects
where object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER');

检查数据库对象是否被任何存储过程引用

select * from all_source
where type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER');