oracle笔记

background : 内蒙建行学习

 

insert into t select ..

总是写错，

create table ... as select ...

------------------------------------

SUM 和 COUNT

如果都为 NULL,SUM 之后不显示，为 NULL

如果都为 NULL,count 后显示 0 。

 

 

 

1 、 cast 函数：类型转换行数

2 、 with tempTable as() ：在执行 sql 之前预定义表。

    with tempTable as (select sysdate from dual),tempTable2 as (select 3 、 cast((select * from tempTable) as timestamp) + 1 from dual)

select * from tempTable2;

4 、 NVL 函数： NVL 为 NULL 值字符串替换函数。

      NVL 用途举例，在计算四则运算时， oracle 默认有 null 的操作结果是 NULL ，所以如果不涉及除法 , 都应该是 NVL(column, substitute ) 即当值是 NULL 时，取 substitue.

    NULLIF 举例，当作除法运算时，分母需要加 NULLIF(column,0); 当等于 0 时，取 NULL.

 

5 、 Case when 用法： Case when expr1 then xxx [when expr2 then xxx ... else xxx ] end

          如果没有 else expr3 ，且 expr1 为 false ，则返回 null.

 

6 、视图创建： create or replace view view_name as (…)

7 、 Trim 函数：去掉首尾的指定字符。首或尾或首尾。

8 、 substr: 字符串切分函数。基数为 1. 第二个参数为长度。

substr(str,pos,length) 如果 pos 为负数，如 -2 ，则从倒数第二位开始查，方向和 pos 为正是一样的。如果没有指定 length ，那么会一直到 str 结束。

 

9 、连接 join 分为： inner join,left outer join,right outer join,full outer join.

    可以把 left outer join 和 right outer join 视为一种 join ，所以总共三种 join.

在 outer join 中，有保留行表和替换 NULL 表之分：

• 保留行（ preserved row ） 表是为连接操作中不匹配的行保留行的表。
•   替换 NULL 的表是为连接中不匹配的行提供 NULL 行的表。

 

提示：保留行表即为 left outer join 中的左表， right outer join 中的右表

            替换 NULL 表为 left outer join 中的右表， right outer join 中的左表。

   

然而，应用于保留行表的 WHERE 子句谓词的最重要的属性是 DB2 可以在连接之前或之后应用谓词；应用于替换 NULL 的表的 WHERE 子句谓词的效果却非常不同，因为如果该谓词抵消了由外连接引入的 NULL ，那么它使 DB2 简化连接。

对于 oracle ，我认为情况也是一样的。至少在 explain plan 中可以看到，有的 outer join 已经直接被 oracle 简化为了 inner join 。

外连接详细解释可以看 IBM Develop Work 中的 Terry Purcell 谈外连接。

    对于 left outer join on 后边的子句，意思是如果在右表中找不到满足 on 后子句的记录，则使用 NULL 值替代这一行。当然子句包括 on 条件后的 and..

    对于外联是否被简化为内联，总结为：如果 where 中谓词分析出即使替换 NULL 总不会返回 TRUE ，那么外联将被简化为内联。即优化器会分析替换 NULL 是否有意义。

 

10 、 Oracle load method 解释：

 

11 、 union 、 union all 、 intersect 、 minus

    minus: 在第一个表中，但不在第二个表中

    intersect: 既在第一个表中，也在第二个表中

    union all: 第一个表和第二个表的合并，不去重

    union: 第一个表和第二个表合并，去重。

 

Minus 举例：

    查询在客户关系表中没有的关系的客户

    使用 not exists

SELECT cust_no FROM f_cust_info_his his WHERE end_dt= '29991231'

AND NOT EXISTS ( SELECT 1 FROM f_cm_cust_rela rel WHERE his.cust_no = rel.cust_no)

使用 minus

SELECT cust_no FROM f_cust_info_his his WHERE end_dt= '29991231' MINUS SELECT cust_no FROM f_cm_cust_rela rel

使用 Left out join

SELECT his.cust_no FROM f_cust_info_his his LEFT OUTER JOIN f_cm_cust_rela rel ON his.cust_no = rel.cust_no  WHERE end_dt= '29991231' AND rel.cust_no IS NULL ;

 

 

12 、 row_number() 函数用法：

    SELECT department_id, last_name, employee_id, ROW_NUMBER()

     OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id

     FROM employees;

 

DEPARTMENT_ID LAST_NAME                 EMPLOYEE_ID     EMP_ID

------------- ------------------------- ----------- ----------

           10 Whalen                            200          1

           20 Hartstein                         201          1

           20 Fay                               202          2

           30 Raphaely                          114          1

           30 Khoo                              115          2

            30 Baida                             116          3

           30 Tobias                            117          4

           30 Himuro                            118          5

           30 Colmenares                        119          6

            40 Mavris                            203          1

.

.

.

          100 Popp                              113          6

          110 Higgins                           205          1

          110 Gietz                             206          2

 

 

13 、 first_value() 函数用法：

 

14 、 oracle 中 trim(NULL) 不会报错，仍然为 NULL

 

15 、 trunc(date) 函数 和 round(date) 函数

    这两个函数都是用来处理时间的函数。 Tranc 是截断 ,round 会考虑舍与入

    Select trunc(sysdate,’mon’) from dual 

       解释：当前 fmt 是月， trunc 会将当前时间截到本月。

    Select round(sysdate,’mon’) from dual

       解释：当前 fmt 是月， round 会根据当前的是某月的多少号，判断出是舍还是入。

 

16 、 last_day(date) 函数：

      对给定 date 进行操作：日期为 date 所在月份的最后一天。

                       时间为 date 所在时间。

17 、 SQL 技巧：

       1) 将列数据转换行数据。 1)Decode. 2)group by + 聚集函数

        2) 将一行数据转换为 N 行。 Decode + N 行的记录 inner join 多行表 On where 1=1

 

例子如下：

 

创建表：

Create Table temp

(

num varchar2(15 Char),

name varchar2(20 Char),

sex varchar2(2 Char),

classes varchar2(30 Char),

course_name varchar2(50 Char)

);

 

插入：

insert   into    temp(num,name,sex,classes,course_name)  values ('206211',' 王艺 ',' 男 ','06-1',' 保险学 ');

insert into temp(num,name,sex,classes,course_name) values ('206212',' 肖薇 ',' 女 ','06-2',' 保险学 ');

insert into temp(num,name,sex,classes,course_name) values ('206212',' 肖薇 ',' 女 ','06-2',' 财务管理 ');

insert into temp(num,name,sex,classes,course_name) values ('206212',' 肖薇 ',' 女 ','06-2',' 财务会计 ');

insert into temp(num,name,sex,classes,course_name) values ('206213',' 陈雅诗 ',' 女 ','06-2',' 电子商务 ');

insert into temp(num,name,sex,classes,course_name) values ('206213',' 陈雅诗 ',' 女 ','06-2',' 公共经济学 ');

insert into temp(num,name,sex,classes,course_name) values ('206213',' 陈雅诗 ',' 女 ','06-2',' 公司理财 ');

insert into temp(num,name,sex,classes,course_name) values ('206213',' 陈雅诗 ',' 女 ','06-2',' 管理学原理 ');

insert into temp(num,name,sex,classes,course_name) values ('206213',' 陈雅诗 ',' 女 ','06-2',' 保险学 ');

insert into temp(num,name,sex,classes,course_name) values ('206214',' 李丹阳 ',' 男 ','06-1',' 保险学 ');

insert into temp(num,name,sex,classes,course_name) values ('206214',' 李丹阳 ',' 男 ','06-1',' 财务管理 ');

insert into temp(num,name,sex,classes,course_name) values ('206214',' 李丹阳 ',' 男 ','06-1',' 财务会计 ');

insert into temp(num,name,sex,classes,course_name) values ('206214',' 李丹阳 ',' 男 ','06-1',' 电子商务 ');

insert into temp(num,name,sex,classes,course_name) values ('206214',' 李丹阳 ',' 男 ','06-1',' 公共经济学 ');

insert into temp(num,name,sex,classes,course_name) values ('206215',' 杨伊琳 ',' 女 ','06-3',' 环境管理学 ');

insert into temp(num,name,sex,classes,course_name) values ('206215',' 杨伊琳 ',' 女 ','06-3',' 管理学原理 ');

insert into temp(num,name,sex,classes,course_name) values ('206215',' 杨伊琳 ',' 女 ','06-3',' 商务谈判 ');

insert into temp(num,name,sex,classes,course_name) values ('206216',' 李佳琪 ',' 男 ','06-2',' 土地估计 ');

Commit;

 

 

------------ 查询包含最多课程的人的课程数。

SELECT MAX(COUNT(COURSE_NAME)) FROM TEMP GROUP BY NUM, NAME, SEX, CLASSES;

-- 结果：

 

----------- 分析函数 row_number()

SELECT   NUM

,NAME

,SEX

,CLASSES

,COURSE_NAME

,ROW_NUMBER() OVER(PARTITION BY NUM, NAME, SEX, CLASSES ORDER BY COURSE_NAME) RN

FROM TEMP

-- 结果：（这里数据很有序和分析函数有关，而不是因为插入有序）

 

 

--decode 函数

SELECT NUM ,

       NAME ,

       SEX,

       CLASSES,

       DECODE (RN, 1 , COURSE_NAME, NULL ) COURSE_NAME_1,

       DECODE (RN, 2 , COURSE_NAME, NULL ) COURSE_NAME_2,

       DECODE (RN, 3 , COURSE_NAME, NULL ) COURSE_NAME_3,

       DECODE (RN, 4 , COURSE_NAME, NULL ) COURSE_NAME_4,

       DECODE (RN, 5 , COURSE_NAME, NULL ) COURSE_NAME_5

  FROM ( SELECT NUM ,

               NAME ,

               SEX,

               CLASSES,

               COURSE_NAME,

               ROW_NUMBER() OVER ( PARTITION BY NUM , NAME , SEX, CLASSES ORDER BY COURSE_NAME) RN

          FROM TEMP)

 

-- 结果：

 

--group by 分组合并 + 聚合函数

SELECT NUM ,

       NAME ,

       SEX,

       CLASSES,

       MAX ( DECODE (RN, 1 , COURSE_NAME, NULL )) COURSE_NAME_1,

       MAX ( DECODE (RN, 2 , COURSE_NAME, NULL )) COURSE_NAME_2,

       MAX ( DECODE (RN, 3 , COURSE_NAME, NULL )) COURSE_NAME_3,

       MAX ( DECODE (RN, 4 , COURSE_NAME, NULL )) COURSE_NAME_4,

       MAX ( DECODE (RN, 5 , COURSE_NAME, NULL )) COURSE_NAME_5

  FROM ( SELECT NUM ,

               NAME ,

               SEX,

               CLASSES,

               COURSE_NAME,

               ROW_NUMBER() OVER ( PARTITION BY NUM , NAME , SEX, CLASSES ORDER BY COURSE_NAME) RN

          FROM TEMP)

  GROUP BY NUM , NAME , SEX, CLASSES;

 

 

 

 

 

 

18 、 greatest() 函数 : 计算最大值

 

19 、 least() 函数：计算最小值。

 

    计算客户经理一天登录次数 : 登录总次数

                              有效登录数 ( 每小时只计一次 , 每日最多 4 次 ))

   

      SELECT am_no -- 客户经理编号 ,

                login_dt,

                COUNT(login_tm) login_num -- 登陆总次数 ,

          least(4, COUNT(DISTINCT SUBSTR(login_tm, 1, 2)))

eff_login_num -- 有效登陆总数

       FROM f_cm_am_login WHERE login_dt = '#par_date#'

   GROUP BY am_no, login_dt

 

20 、 NULLIF(expr1,expr2) 函数：

       NULLIF 函数比较 expr1 和 expr2 ，如果 expr1 = expr2 ，则返回 NULL

                                   如果 expr1 != expr2 , 返回 expr1.

 

 

21 、 count(expr1) 返回的是 expr1 不为 NULL 的数量。

 

22 、取在 A 表中存在，但是在 B 表中不存在的客户：

       除了 not in,not exists ，还可以用 left outer join

    Select A.id from A left outer join B on A.id = B.id where B.is is NULL.

    该语句在 where 条件中使用了替换 NULL 行表，判断条件是 is NULL ，所以优化器不会对其进行内联优化。

 

23 、索引组织表，不可以在 pl/sql 中， for update 手动更新，只能通过 update 语句更新？

 

 

24 、比较聪明的处理方式：

       要求：从客户客户经理关系历史 , 找出本月所有客户对应的最早客户经理 , 用于汇总 .

      

select his.cust_no,

       CAST(min(his.am_no) KEEP(dense_rank FIRST ORDER BY his.start_dt ASC) AS

            VARCHAR2(20)) am_no,

       min(his.start_dt) dt

  FROM f_cm_cust_rela_his his

  INNER JOIN f_cm_cust_rela cur -- 只取出正常关系的客户

    ON cur.cust_no = his.cust_no

   AND cur.eff_flg = '1'

  WHERE

-- 开始日期不大于月底 , 结束日期大于月初

  his.start_dt <=

  to_char(last_day(to_date('20110425', 'yyyymmdd')), 'yyyymmdd')

  and his.end_Dt > SUBSTR('20110425', 1, 6) || '01'

  GROUP BY his.cust_no;

 

 

 

 

 

 

 

25 、 oracle 中 between and 是个闭区间。两边都包括。

 

 

26 、 group by… 之后，用 having 过滤组，如果 having 有多个条件，同样是 and 。

 

 

 

27 、 Oralce 执行计划提示：

       解释：告诉 oracle ，使用什么方式进行表之间的连接。

      

       方法：在 select 关键字后，紧随 /*+ link_method(a,b) */

               link_method 是指定的连接方式，如 use_hash 。 a,b 分别为表名

例如 select /*+ use_hash(vst,grd) */

 

 

28 、分析函数小例

Sql 脚本：

 

create table TEST_MIN_OVER

(

  SEX  VARCHAR2(1),

  AGE  VARCHAR2(3),

  NAME VARCHAR2(10)

)

 

delete from TEST_MIN_OVER;

commit;

 

insert into TEST_MIN_OVER (SEX, AGE, NAME)

values ('b', '18', 'john18');

insert into TEST_MIN_OVER (SEX, AGE, NAME)

values ('b', '19', 'john19');

insert into TEST_MIN_OVER (SEX, AGE, NAME)

values ('b', '20', 'john20');

insert into TEST_MIN_OVER (SEX, AGE, NAME)

values ('g', '17', 'kate17');

insert into TEST_MIN_OVER (SEX, AGE, NAME)

values ('g', '18', 'kate18');

insert into TEST_MIN_OVER (SEX, AGE, NAME)

values ('g', '19', 'kate19');

insert into TEST_MIN_OVER (SEX, AGE, NAME)

values ('b', '18', 'tate');

commit;

 

测试 sql:

--me

SELECT SEX, MIN ( NAME ) KEEP (DENSE_RANK LAST ORDER BY AGE ASC ), MAX (AGE)

  FROM TEST_MIN_OVER

  GROUP BY SEX;

SELECT SEX, MIN ( NAME ) KEEP (DENSE_RANK FIRST ORDER BY AGE ASC ), MAX (AGE)

  FROM TEST_MIN_OVER

  GROUP BY SEX;

--he

SELECT SEX,

       MAX (AGE) AGE,

       MIN ( NAME ) KEEP (DENSE_RANK FIRST ORDER BY AGE DESC NULLS LAST ) NAME

  FROM TEST_MIN_OVER

  GROUP BY SEX;

 

SELECT SEX,

       AGE,

       NAME ,

       MAX (AGE) OVER ( PARTITION BY SEX ORDER BY AGE DESC ) AGE

  FROM TEST_MIN_OVER

 

 

 

29 、分析函数之 row_number() ———为给行在组中编号 :

 

例子 1 ：

select num,name,sex,classes,course_name,row_number() OVER ( PARTITION BY num,name,sex,classes ORDER BY course_name) from temp

学生的课程顺序编号

 

例子 2 ： 返回每个员工再在每个部门中按员工号排序后的顺序号。

SELECT department_id, last_name, employee_id, ROW_NUMBER()

OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id

FROM employees

WHERE department_id < 50;

 

 

30 、 PL/SQL

1) 包括三个部分： declare section,execute section,exception section

           execute is mandatory .

 

                        

 

 

DECLARE

   Declaration statements

BEGIN

   Executable statements

EXCEPTION

   Exception-handling statements

END;

 

              2)  sqlplus 执行包： execute package_name.procedure_name(args)

 

 

 

 

31 、压缩表空间：

       alter TABLE F_PROM_CUST_LOSS shrink space compact CASCADE ;

 

32 、更改表 row movement 属性，使之可以移动。

ALTER TABLE F_PROM_CUST_LOSS ENABLE ROW MOVEMENT

 

33 、 sql 查看表属性

       SELECT * from dba_tables t where t.TABLE_NAME = 'xxx' ;

    如，是不是分区表，是不是可以 row movement 等。

 

34 、任何值和 NULL 比较都是 false. NULL<>1 也是 false, NULL = NULL 同样是 false

 

35 、 dba_tables,dba_views,dba_procedures, dba_db_links , dba_users , dba_sequences.. 基本上都是视图

 

36 、 merge 语句： update+insert

 

 

37 、 select * from user_tab_cols where table_name = ‘xxxx’

    该视图可以查看某个表中个字段的信息，如字段长度、字段类型、是否为空等信息。

 

38 、 rpad 函数用法：右填充函数

    格式： RPAD ( char1 , n [, char2] )

    例子：

       SELECT RPAD ( 'sun' , 9 , 0 ) from dual;

       解释：结果是 sun000000

       SELECT RPAD ( 'sun' , 2 ) from dual;

       解释：结果是 su

 

39 、 sequence 的下个值：

    select sequence_name.nextval from dual

 

 

40 、 oracle 随机函数：

    1 ）随机 0,1 这两个数。

       SELECT floor (dbms_random.value( 0 , 2 )) from dual;

 

SELECT ROUND (dbms_random.value( 0 , 1 )) FROM dual;

    2)

SELECT ROWNUM , SUBSTR (t.org4_id, 1 , 9 ),to_char( 20110301 + round (dbms_random.value( 0 , 30 ))) ,t.org4_nm, round (dbms_random.value( 0 , 1 )) FROM   epemdata.view_b_f_cm_org2_code_nm t

 

 

41 、 for 举例

DECLARE

BEGIN

  FOR i IN 1 .. 10

    LOOP

    dbms_output.put_line(i);

    END LOOP;

END;

/

 

 

42 、 oralce 获取嵌套表内容

select * from table(nested_table);

 

 

43 、正则替换 regexp_replace(‘2011- 5 -11’,’-| ’,’’);

 

 

 

44 、精度 precision: 精度是指整数位数 + 小数位数

    在定义 Number 类型时，格式为 number(precision [,scale]);

                       参数： precision 指定整数位数 + 小数位数的总个数。

                            scale 指定了小数部分位数，可选部分。

    当赋值时， precision-scale 必须大于整数长度， scale 如果小于小数长度，会自动四舍五入。

    一种特殊的情况就是指定了小数部分保留 n 位数，但是小数部分 n+1 进上去导致没有了小数。

   

 

 

45 、 CHR 函数的使用：

       在 oracle 中 & 代表了提示输入符号

       比如 select ‘&nbsp;’ from dual; 将不会输出 &nbsp ，而会提示要输入的内容，然后将 nbsp; 替换为输入的字符。

       可以使用 CHR 函数完成： select CHR(38)||’nbsp;’ from dual

 

 

 

 

46 、创建表空间：

       CREATE TABLESPACE dmt

        DATAFILE 'c:/1.dbf' SIZE 2m

        EXTENT MANAGEMENT dictionary; -- 区段管理方式（字典管理 outdated ）

    虽然这是例子一般情况下会提示错误，但是也是告诉了我们如何创建表空间。

 

47 、授权 sql:

    grant select on v_$mystat to scott;

    revoke select on v_$mystat from scott;

   

 

48 、分析函数：

    分组求和，总和

WITH t AS (

    SELECT 1 val, 'a' col FROM dual

    UNION

    SELECT 2 , 'a' FROM dual

    UNION

    SELECT 3 , 'a' FROM dual

    UNION

    SELECT 4 , 'a' FROM dual

    UNION

    SELECT 5 , 'b' FROM dual

    UNION

    SELECT 6 , 'b' FROM dual

    UNION

    SELECT 7 , 'b' FROM dual

)

, x AS (

  SELECT col, SUM (val) AS val

    FROM t

    GROUP BY col

)

 

SELECT col,val, SUM (val) OVER (),val/ SUM (val) OVER ()

FROM x

 

 

 

 

 

oracle 中常用表说明：

v$session ：可以查看当前所有连接到 oracle 的 sid( 会话 id) ，等信息。

           

v$mystat ：查看当前 session id, statistic# ,value

v$sesstat ：全部用户的 mystat

v$statname: 字典视图，用于查看 statistic# 对应的名字

eg: 可以查看 v$statname 中 statistic#=5 的 name 是 user rollbacks

     从当前 v$mystat 中查看当前 session 的回滚次数

         select * from v$mystat where statistic#=5;

v$lock: 系统中锁信息，其中 ID1 对应系统中对象的 ID ，有的 ID 并不在 dba_objects 里

TYPE 为锁的类型 (v$lock_type)

all_objects: 系统中所有的对象

CLUSTER    CONSUMER GROUP    EVALUATION CONTEXT   FUNCTION   INDEX  INDEX PARTITION   INDEXTYPE    JAVA CLASS JAVA RESOURCE JOB CLASS  LIBRARY    OPERATOR   PACKAGE    PACKAGE BODY  PROCEDURE    PROGRAM    SCHEDULE   SEQUENCE   SYNONYM    TABLE  TABLE PARTITION   TABLE SUBPARTITION    TYPE   VIEW   WINDOW WINDOW GROUP  XML SCHEMA 都是对象。

 

------------------------ 输出 oracle 的表对象 ---------------------

DECLARE

       v_count   NUMBER ; -- 列数

       v_dysql   VARCHAR2 ( 4000 ); -- 动态 sql 字符串

       TYPE weak_cur IS REF CURSOR ; -- 弱引用类型

       cur weak_cur;

       v_temp VARCHAR2 ( 1000 ); -- 输出结果

       v_dysql2 VARCHAR2 ( 1000 ); -- 动态 sql 字符串

BEGIN

      SELECT count ( DISTINCT t.OBJECT_TYPE) INTO v_count FROM all_objects t;

       v_dysql := 'WITH object_type AS (

                        SELECT DISTINCT object_type tp_nm FROM all_objects

                   )

                    , det AS (

                        SELECT tp.tp_nm,RANK() OVER(ORDER BY tp.tp_nm) no FROM object_type tp

                   )

                   , t as (SELECT ' ;

 

       FOR i IN 1 .. v_count LOOP

           IF i <> 1 THEN

               v_dysql := v_dysql|| ' , ' ;

               v_dysql2 := v_dysql2|| ' || ' ;

           END IF ;

           v_dysql := v_dysql || ' MAX(DECODE(no,' || i || ',tp_nm,null))||chr(9) ' || 'type' || i; --chr(9) 为 tab 键编码

           v_dysql2 := v_dysql2 || ' type' || i;

       END LOOP ;

       v_dysql := v_dysql || ' FROM det)

       select ' || v_dysql2 || ' from t' ;

       --DBMS_OUTPUT.put_line(v_dysql);

       OPEN cur FOR v_dysql;

      

         LOOP

              FETCH cur INTO v_temp;

              EXIT WHEN cur% NOTFOUND ;

       END LOOP ;

       DBMS_OUTPUT.put_line(v_temp);

      -- 关闭游标

END ;

---------------------- 输出 oracle 中的对象 -------------------------------

 

查看数据库中的锁：

-- 系统中的锁

  SELECT

          lk.sid  AS 会话 ID

        , sess.SCHEMANAME AS 模式名

        , sess.MACHINE AS 网络机器

        , lk.TYPE AS 锁类型

        , tp.name AS 锁名字

        , lk.id1

        , lk.id2

        , obj.OBJECT_NAME AS 对象名字

        , sess.USERNAME

  FROM v$lock lk

  INNER JOIN v$session sess

  ON lk.sid = sess.SID

  INNER JOIN v$lock_type tp

  ON tp.type = lk.TYPE

  LEFT JOIN dba_objects obj

  ON obj.OBJECT_ID  = lk.ID1

 

-- 系统中的被锁对象

  SELECT

          obj.OBJECT_NAME AS 被锁对象名

        , obj.OBJECT_TYPE AS 被锁对象类型

        , obj.OWNER AS 被锁对象所属

        , lk_obj.OS_USER_NAME AS 操作系统用户名

        , lk_obj.ORACLE_USERNAME AS oracle 登录名

        , sess.sid AS 会话 ID

        , sess.SERIAL# AS 会话 ID_SERIAL

        , sess.SCHEMANAME AS 会话模式名

        , sess.MACHINE AS 会话网络机器

        , sess.USERNAME AS 会话用户名

  FROM v$locked_object lk_obj

  INNER JOIN dba_objects obj

  ON obj.OBJECT_ID = lk_obj.OBJECT_ID

  INNER JOIN v$session sess

  ON lk_obj.SESSION_ID = sess.SID

 

-- 杀死某个锁住对象的会话： ( 需要有足够的权限 )

alter system kill session 'sid,serial ';

 

 

 

 

49 、分析索引：

    analyze index sys_iot_top_129273 validate structure;

select * from index_stats

 

50 、列字符串合并：

SELECT wmsys.wm_concat( DISTINCT object_type) b FROM all_objects

 

51 、创建视图：

---------------------------------------------------

CREATE OR REPLACE TRIGGER T_TRIGGER

    BEFORE INSERT OR DELETE ON T

    FOR EACH ROW

 

BEGIN

    IF (INSERTING) THEN

        UPDATE T2 SET CNT = CNT + 1;

    ELSE

        UPDATE T2 SET CNT = CNT - 1;

    END IF;

    DBMS_OUTPUT.PUT_LINE('I fired and updated ' || SQL%ROWCOUNT || ' rows');

END;

---------------------------------------------------

 

 

52 、 oracle 睡眠：

    dbms_lock.sleep(n);n 的单位是秒

 

DECLARE

  v_count NUMBER ;

BEGIN

    SELECT COUNT(*) INTO v_count FROM scott.t; -- 睡眠前读到有 3 条记录

    dbms_output.put_line(v_count);

    dbms_lock.sleep(10);-- 睡眠时，在其他 session 中删除表中记录

    SELECT COUNT(*) INTO v_count FROM scott.t;-- 睡眠后读到的记录是 0 条。

    dbms_output.put_line(v_count);

END;

 

 

 

 

 

 

 

53 、 oracle 授权 / 回收（ grant/revoke ）：

    如 %oracle_home%/sqlplus/admin 下的 plustrce.sql 文件中：

    创建角色：

       drop role plustrace;

       create role  plustrace;

       grant select on v_$sesstat to plustrace;

grant select on v_$statname to plustrace;

grant select on v_$mystat to plustrace;

grant plustrace to dba with admin option; 将 plustrace 角色赋予 dba 角色

       grant plustrace to public; 将 plustrace 角色赋予所有 user;

 

54 、 oracle 创建同义词 synonym

    create or replace [PUBLIC] synonym plan_table for sys.plan_table;

    [public] 是指定 owner?

GRANT ALL ON PLAN_TABLE TO PUBLIC 。 -- 所有人都可以使用 plan_table;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

55 、脏读：

    将数据插入表中，但是不提交。会不会导致物理文件结构变化？即是不是真插入到数据库中，答案是！

    怎么可以看出来？

A) 在 insert 结束后，但是不提交，可以查看表的物理结构变化。

B) 创建一个空表，不加索引，插入 10000000 条数据，回滚。再次查询，发现查询时间很长。 truncate 之后，再次查询，发现时间很短。

  解释：全表扫描时， oracle 会读取表段中所有的数据块，在其中查询是否存在满足条件的数据。插入，但是不提交，真的会将数据插入到数据文件中，导致文件结构变化 。 truncate 命令之执行后，会将表达 HWM 高水位线置零。

 

既然不提交，会插入到数据库中，那么数据库要完成避免脏读，一般来说就需要加锁，在更新数据库时，将会阻塞读。也就是 oracle 常说的几乎其他的数据库，写都会阻塞读。但是 oracle 提供的是多版本读取，物化出多个版本，写是不会阻塞读到。

 

脏读的好处是并发读取，写不会阻塞读。 但是这个好处 oracle 使用多版本读取实现了，而且不会出现脏读现象。因为脏读这种好处是要不得的！

oracle 中写不会阻塞读，而且不会出现脏读。

 

扩展概念： oracle 中多版本，如何实现并发读取，而且没有脏读

       a) 时间点版本。

在 SA 读取表数据（未读完）， SB 更新表数据了， SA 继续读取，但是 SA 是按照开始时间点读的，和 SB 更新的数据不发生关系。

       b) v$trasaction 中好象可以看到当前数据库中更新单位提交的事务，我想 oracle 是不是根据这个时间，来取时间点。 v$transaction 中记录了系统中正运行的事务？

                  SB 更新表，但是未提交， SA 读取表，我想是查看了 v$transaction 中是否存在表和当前 SA 要读取的表一样，如果存在，则取上一个时间点的？

 

 

问题：为什么不提交，要更新数据文件呢？

    为什么更新未提交会导致数据真的插入到了数据文件中，为什么不放在 session 中，等待提交后，在写数据呢？我说的情况会避免脏读和满足写不阻塞读吗？

    首先，如果只更新，未提交的确前，其他 session 无法读取到脏读数据！但是提交期间，真的不会满足，非脏读？如果 session A 提交期间， session B 读取了数据，完成了 session B 的操作，但是断电了， session A 的事务下次数据库启动后，必然要重做！这个重做是回到之间状态还是重新完成操作呢 ? 假设是回到之间状态，因为断电时，数据库尚未对操作结果给出回应，客户一定以为是失败了。这时 session B 读取应该就是脏读了吧。假设重启后，数据库继续完成了 session A 的操作 , 那么也许 session B 读取的就不是脏读了。那么 session B 读取的是不是说未来一定存在的数据呢？

    其次，将事务提交累计到一个点，是不是意味着，所有操作都集中在一个时间，是不是不太好。

（书上说的意思好像是掉电后， redo 段用来恢复事务，那岂不是重新做事务！那提交再更新数据文件有什么不好呢？）

    瞎分析了，谁知道为什么给我讲讲了。