常用sql,建立臨時表語法,會話級臨時表,查看鎖表,解除鎖表,case,連接的會話數 ，查詢耗資源的進程（top session），查找object為哪些進程所用
查看所有SEQUENCES:
select sequence_name from USER_SEQUENCES;
修改表字段不允許為空
alter table table_name modify cloumn_name not null;
查詢序列
select seq_major_site_reg.nextval from dual;
添加主鍵
alter table table_name add constraint PK_SEEDSITE_ID primary key (ID);
Oracle Temporary Tables(Oracle 臨時表)
1. 建立臨時表語法
A．ON COMMIT DELETE ROWS 定義了建立事務級臨時表的方法
CREATE GLOBAL TEMPORARY TABLE TABLE_NAME
-----(COUMNS …)
-----AS SELECT … FROM TABLE…
ON COMMIT DELETE ROWS;
當前session發出commit/rollback命令，則該事務周期發生的所有數據自動被Oracle刪除(Oracle truncate table)。但不影響任何其他session的數據。
B．ON COMMIT PRESERVE ROWS 定義了創建會話級臨時表的方法
CREATE GLOBAL TEMPORARY TABLE TABLE_NAME
-----(COUMNS …)
-----AS SELECT … FROM TABLE…
ON COMMIT PRESERVE ROWS;
當前session結束(用戶正常退出 / 用戶不正常退出 / Oracle實例崩潰)，Oracle對這個會話的中發生的數據進行刪除(Oracle truncate table)。但不影響任何其他session的數據。
2. 特點說明
A．臨時表數據自動清空后，但是臨時表的結構以及元數據還存儲在用戶的數據字典中。表的定義對所有的會話可見
B．臨時表不需要DML鎖
C．可以索引臨時表和在臨時表基礎上建立視圖
D．在臨時表上的索引也是臨時的，也是只對當前會話或者事務有效
E．臨時表可以擁有觸發器
F．可以用export和import工具導入導出臨時表的定義，但是不能導出數據
3. 使用技巧
A．當某一個SQL語句關聯的表在2張及以上，并且和一些小表關聯。可以采用將大表進行分拆并且得到比較小的結果集合存放在臨時表中
B．程序執行過程中可能需要存放一些臨時的數據，可以將這類數據放在臨時表里非常方便
C．存儲過程中用到臨時表:
1> 在建立臨時表前，應先加上對表名的判斷
Select count(*) into v_count from user_tables where table_name = ‘XXX’;
If v_count=0 then
Create global temporary table …
在存儲過程結束處，應該記得刪除表
execute immediate 'drop table t_temp';
4、查看鎖表信息
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;
5、解除鎖表
alter system kill session 'sid,serial#';
例：alter system kill session '135,16';
--以下幾個為相關表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;
1.查出鎖定object的session的信息以及被鎖定的object名
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial# ;
2.查出鎖定表的session的sid, serial#,os_user_name, machine name, terminal和執行的語句
--比上面那段多出sql_text和action
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;
3.查出鎖定表的sid, serial#,os_user_name, machine_name, terminal，鎖的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
4.case語句:
SELECT count(*) cnt, field
FROM (SELECT case
when salary < 1000 then
'饑餓'
WHEN salary >= 1000 and salary <= 1500 THEN
'溫飽'
WHEN salary > 1500 and salary <= 2000 THEN
'富裕'
WHEN salary > 2000 and salary <= 2500 THEN
'小康'
ELSE
'太富了'
END field
FROM persion) a
GROUP by field;
5.根據某一條件查出星期
select to_char(to_date(' 2009-03-09 ', ' yyyy-mm-dd ') + rownum - 1, 'DAY') as 星期,
to_date(' 2009-03-09 ', ' yyyy-mm-dd ') + rownum - 1 as 日期
from user_objects
where rownum <= (to_date(' 2009-03-09 ', ' yyyy-mm-dd ') + 7 -
to_date(' 2009-03-09 ', ' yyyy-mm-dd '));
6、在數據庫服務器上 查看 那臺機器連接到了數據庫，連接的會話數是多少
select count(*),machine from v$session v group by machine;
找使用CPU多的用戶session
12是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
監控表空間的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
監控當前數據庫誰在運行什么SQL語句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
檢查被長時間鎖的對象
SQL>select a.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status from v$locked_object a,dba_objects b where a.object_id=b.object_id;
查找object為哪些進程所用
SELECT p.Spid, s.Sid, s.Serial# Serial_Num, s.Username User_Name,
a.TYPE Object_Type, s.Osuser Os_User_Name, a.Owner,
a.OBJECT Object_Name,
Decode(Sign(48 - Command), 1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,
p.Program Oracle_Process, s.Terminal Terminal, s.Program Program,
s.Status Session_Status
FROM V$session s, V$access a, V$process p
WHERE s.Paddr = p.Addr
AND s.TYPE = 'USER'
AND a.Sid = s.Sid
AND a.OBJECT = '&obj'
ORDER BY s.Username, s.Osuser
查詢耗資源的進程（top session）
SELECT s.Schemaname Schema_Name,
Decode(Sign(48 - Command),
1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,
Status Session_Status, s.Osuser Os_User_Name, s.Sid, p.Spid,
s.Serial# Serial_Num, Nvl(s.Username, '[Oracle process]') User_Name,
s.Terminal Terminal, s.Program Program, St.VALUE Criteria_Value
FROM V$sesstat St, V$session s, V$process p
WHERE St.Sid = s.Sid AND St.Statistic# = To_Number('38')
AND ('ALL' = 'ALL' OR s.Status = 'ALL')
AND p.Addr = s.Paddr
ORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC
查看鎖（lock）情況
SELECT /*+ RULE */
Ls.Osuser Os_User_Name, Ls.Username User_Name,
Decode(Ls.TYPE,
'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock',
'TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') Lock_Type,
o.Object_Name OBJECT,
Decode(Ls.Lmode,
1, NULL, 2, 'Row Share', 3, 'Row Exclusive',
4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', 6,NULL) Lock_Mode,
o.Owner, Ls.Sid, Ls.Serial# Serial_Num, Ls.Id1, Ls.Id2
FROM Sys.Dba_Objects o,
(SELECT s.Osuser, s.Username, l.TYPE, l.Lmode, s.Sid, s.Serial#, l.Id1,
l.Id2
FROM V$session s, V$lock l
WHERE s.Sid = l.Sid) Ls
WHERE o.Object_Id = Ls.Id1
AND o.Owner <> 'SYS'
ORDER BY o.Owner, o.Object_Name