常用sql,建立臨時表語法,會話級臨時表,查看鎖表,解除鎖表,case,連接的會話數(shù) ，查詢耗資源的進程（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 定義了建立事務(wù)級臨時表的方法
CREATE GLOBAL TEMPORARY TABLE TABLE_NAME
-----(COUMNS …)
-----AS SELECT … FROM TABLE…
ON COMMIT DELETE ROWS;
當前session發(fā)出commit/rollback命令，則該事務(wù)周期發(fā)生的所有數(shù)據(jù)自動被Oracle刪除(Oracle truncate table)。但不影響任何其他session的數(shù)據(jù)。
B．ON COMMIT PRESERVE ROWS 定義了創(chuàng)建會話級臨時表的方法
CREATE GLOBAL TEMPORARY TABLE TABLE_NAME
-----(COUMNS …)
-----AS SELECT … FROM TABLE…
ON COMMIT PRESERVE ROWS;
當前session結(jié)束(用戶正常退出 / 用戶不正常退出 / Oracle實例崩潰)，Oracle對這個會話的中發(fā)生的數(shù)據(jù)進行刪除(Oracle truncate table)。但不影響任何其他session的數(shù)據(jù)。
2. 特點說明
A．臨時表數(shù)據(jù)自動清空后，但是臨時表的結(jié)構(gòu)以及元數(shù)據(jù)還存儲在用戶的數(shù)據(jù)字典中。表的定義對所有的會話可見
B．臨時表不需要DML鎖
C．可以索引臨時表和在臨時表基礎(chǔ)上建立視圖
D．在臨時表上的索引也是臨時的，也是只對當前會話或者事務(wù)有效
E．臨時表可以擁有觸發(fā)器
F．可以用export和import工具導入導出臨時表的定義，但是不能導出數(shù)據(jù)
3. 使用技巧
A．當某一個SQL語句關(guān)聯(lián)的表在2張及以上，并且和一些小表關(guān)聯(lián)。可以采用將大表進行分拆并且得到比較小的結(jié)果集合存放在臨時表中
B．程序執(zhí)行過程中可能需要存放一些臨時的數(shù)據(jù)，可以將這類數(shù)據(jù)放在臨時表里非常方便
C．存儲過程中用到臨時表:
1> 在建立臨時表前，應(yīng)先加上對表名的判斷
Select count(*) into v_count from user_tables where table_name = ‘XXX’;
If v_count=0 then
Create global temporary table …
在存儲過程結(jié)束處，應(yīng)該記得刪除表
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';
--以下幾個為相關(guān)表
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和執(zhí)行的語句
--比上面那段多出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.根據(jù)某一條件查出星期
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、在數(shù)據(jù)庫服務(wù)器上 查看 那臺機器連接到了數(shù)據(jù)庫，連接的會話數(shù)是多少
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;
監(jiān)控表空間的 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;
監(jiān)控當前數(shù)據(jù)庫誰在運行什么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