常用sql,建立临时表语法,会话级临时表,查看锁表,解除锁表,case,连接的会话数 ，查询耗资源的

常用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