Oracle数据库状态

--数据库最大连接数
SELECT VALUE FROM v$parameter WHERE NAME='processes';

SELECT * FROM v$process WHERE program='oracle.exe(SHAD)';

--查看锁
select object_name as 对象名称,s.SID,s.SERIAL#,p.SPID as 进程号,l.OS_USER_NAME
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.OBJECT_ID = o.OBJECT_ID
and l.SESSION_ID=s.SID and s.PADDR = p.ADDR;
alter system kill session '63,28371';

--查询数据库元数据
SELECT * FROM User_Tab_Columns tt WHERE 1=1 AND tt.TABLE_NAME='WFI_WORKLIST_TODO';
SELECT * FROM User_Tab_Comments tt WHERE 1=1 AND tt.TABLE_NAME='WFI_WORKLIST_TODO';
SELECT * FROM User_Tab_Cols tt WHERE 1=1 AND tt.TABLE_NAME='WFI_WORKLIST_TODO';

--ORACLE中的NLS:
SELECT * FROM V$nls_Parameters;

--常用的Oracle系统表：
--dba开头...
SELECT * FROM dba_users;--数据库用户信息表 
SELECT * FROM Dba_Segments;--表段信息
SELECT * FROM dba_extents;--数据区信息
SELECT * FROM dba_objects;--数据库对象信息
SELECT * FROM Dba_Tablespaces;--数据库表空间信息
SELECT * FROM Dba_Free_Space;--数据库空闲空间信息
SELECT * FROM user_tables;--用户的表对象信息
SELECT * FROM user_segments;--用户的表段信息

SELECT * FROM v$database;--数据库信息
SELECT * FROM v$process;--数据库服务器进程信息

SELECT * FROM v$bgprocess;--数据库后台进程信息
SELECT * FROM v$session;--会话信息
SELECT * FROM v$sqlarea;--当前查询过的sql语句访问过的资源及相关的信息

select * from v$locked_object;--查询锁死的对象

SELECT * 
FROM EMP --(基础表) 
WHERE EMPNO > 0 
AND 
EXISTS (SELECT 'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')
--高效应该是这样的 in 是把外表和内表作hash 连接，
--而exists是对外表作loop循环，每次loop循环再对内表进行查询。
--一直以来认为exists比in效率高的说法是不准确的。
--如果查询的两个表大小相当，那么用in和exists差别不大。 
--如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in

SELECT RPAD('gao',10,'#') FROM dual;
SELECT lPAD('gao',10,'#') FROM dual;
SELECT coalesce(NULL,NULL,100,2) AS aa FROM dual;
SELECT greatest(5,1,7,9,6) AS mm FROM dual;
SELECT least(5,1,7,9,6) AS gg FROM dual;
SELECT case Nullif(1,1) IS NULL THEN '相等' ELSE '不等' END bj FROM dual;

SELECT NVL2(NULL,'aa','bb') AS NAME FROM dual;--不是null 返回aa,是null返回bb
SELECT sys_guid()FROM dual;

SELECT XMLELEMENT("DATE",SYSDATE) FROM DUAL;--生成一个xml的element

SELECT UID FROM DUAL;

SELECT to_char(current_timestamp,'yyyy-MM-dd hh:mm:ss') FROM dual;

SELECT extract(YEAR FROM Sysdate) YEAR FROM dual;
SELECT months_between(SYSDATE,'31-7月-2016') kk_date FROM dual;
SELECT greatest(1,2,3,4,5,4,8,5) aa FROM dual;
SELECT trim(to_char(-0.55,'L999,999,990.99')) money FROM dual;

SELECT INSTR('syranmo', 's') FROM DUAL; -- 返回 1 
SELECT INSTR('syranmo', 'ra') FROM DUAL; -- 返回 3 
SELECT INSTR('syran mo', 'a', 1, 2) FROM DUAL; -- 返回 0

SELECT DECODE(SIGN(3 - 5), 1, 3, 5) FROM DUAL;

SELECT * FROM v$bgprocess WHERE paddr <> '00';