Oracle学习笔记整理手册

柔光的暖阳◎ 2021-09-16 06:36 507阅读 0赞

### 文章目录 ###

*   *  第一章、Oracle SQL技巧
     *   *  (1)Oracle正则匹配使用
         *  (2)sql筛选出记录数大于2的记录
         *  (3)oracle同义词
         *  (4)oracle内外连接
         *  (5)oracle去重关键字
         *  (6)Oracle不等于符号
         *  (7)1=1和1=0
         *  (8)Oracle in关键字和等于符号
         *  (9)union
         *  (10)exist和in
     *  第二章、Oracle数据库操作
     *   *  (1)创建表空间
         *  (2)oracle数据库还原
         *  (3)重导dmp文件
         *  (4)批量写入大量数据
         *  (5)col \[字段名\] format a\[字符数\]
         *  (6)查询某张表约束信息
         *  (7)强制删除表
         *  (8)建表并加主键
         *  (9)Oracle表数据回滚语句
         *  (10)Oracle修改有数据的数据字段类型
     *  第三章、Oracle函数用法
     *   *  (1)NVL函数和NVL2函数
         *  (2)递归查询
         *  (3)条件sql
         *  (4)vm\_concat函数
         *  (5)replace函数
         *  (6)instr函数
         *  (7)decode函数
         *  (8)upper和lower函数
         *  (9)oracle开窗函数
         *  (10)oracle rpad函数
         *  (11)ceil函数和floor函数
     *  第四章、Oracle调优指南
     *   *  (1)SQL资源使用情况分析
         *  (2)with...as用法
         *  (34)查看数据库锁情况
         *  (35)查询表索引情况

PS：本博客收录自己工作中遇到学到的一些Oracle技能，有时间就更新整理一下

## 第一章、Oracle SQL技巧 ##

### (1)Oracle正则匹配使用 ###

PS:这条SQL可以通过正则匹对查询一下，表A的字段a是否有非数字的数据，有时候数据表的一些字段是varchar类型的，如果sql里用to\_number的话，就肯定会报无效数字的错误

select * from 表A where not regexp_like(字段a,'^[[:digit:]]+$');

### (2)sql筛选出记录数大于2的记录 ###

select id,count(*) from A a
    where a.test like '%'
    group by id
    having count(*)>=2

### (3)oracle同义词 ###

创建同义词：
    create public synonym tablename for user.tablename;
    synonyms

### (4)oracle内外连接 ###

Oracle左右连接可以使用(+)，+号在左表示右外连接，在右表示左外连接

### (5)oracle去重关键字 ###

Oracle关键字：  
distinct：过滤掉多余的重复记录只保留一条

### (6)Oracle不等于符号 ###

oracle不等于有下面三种表示：  
!=、^=、<>  
<>可移植性好

### (7)1=1和1=0 ###

select \* from t where 1=1  
1=1条件始终为true，是为了避免负责的条件查询出现错误  
1=0条件始终未false，结果不会返回任何数据，所以可以用于建表，create table newtable as select \* from oldtable where 1=0;

### (8)Oracle in关键字和等于符号 ###

oracle in 和 =的区别  
in可以有多条记录，=只有一条记录  
=性能更好，因为使用=符合能更好地使用索引

### (9)union ###

union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;

union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;

### (10)exist和in ###

select a.id from A a where a.id in(select b.id from B b)
    
    select a.id from A a where exists(select b.id from B b where a.id = b.id)

## 第二章、Oracle数据库操作 ##

### (1)创建表空间 ###

–创建表空间  
CREATE SMALLFILE  
TABLESPACE “WORKFLOW01”  
LOGGING  
DATAFILE  
‘D:\\app\\Administrator\\oradata\\oanet\\WORKFLOW01.ora’ SIZE  
245M EXTENT MANAGEMENT LOCAL SEGMENT SPACE Management AUTO

### (2)oracle数据库还原 ###

1、将TESTDB.bmp放置在dpdump目录，如下路径：  
E:\\app\\Administrator\\admin\\orcl\\dpdump

2、创建用户  
create user testdb65 identified by “testdb65”  
default tablespace users temporary tablespace temp;  
用户授权  
grant connect,dba to testdb65;

3、创建表空间  
CREATE TABLESPACE HR\_DATA01 DATAFILE ‘D:\\nhsj\\hr\_data01.dbf’ SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ; 【创建表空间】

4、导入 bgyhr2是来源库  
impdp testdb65/testdb65@orcl schema=testdb65 dumpfile=TESTDB65.dmp

导出 scott 是要导出的库  
expdp lfwtrain/a@orcl schema=当前用户 dumpfile=expdptest.dmp

### (3)重导dmp文件 ###

Oracle保存数据时一般以DMP文件的形式进行保存  
本博客记录，DMP文件的导入 先打开CMD  
`sqlplus /nolog`  
以sysdba连接  
`conn /as sysdba`  
这里创建表空间，因为表空间进行存储的话，可以提供系统性能  
`CREATE TABLESPACE DATA01 DATAFILE 'D:\tablespace\data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;`  
创建用户  
`CREATE USER job IDENTIFIED BY job DEFAULT TABLESPACE DATA01 TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON DATA01`  
授权  
`grant connect,dba to job;`  
复制dmp文件到oracle安装目录里的app\\admin\\orce\\dpdump里，然后用以下代码导入dmp文件，要顺便导入logfile的话，加上logfile=文件名  
`impdp job/job@orcl schemas=job dumpfile=EXPDP160810GZ.dmp`

### (4)批量写入大量数据 ###

SQL> begin
      2  for i in 1..5000 loop
      3  insert into employee values('EMPNO',i);
      4  end loop;
      5  commit;
      6  end;
      7  /
    
    PL/SQL 过程已成功完成。

### (5)col \[字段名\] format a\[字符数\] ###

sqlplus查询时有时候有些数据返回太长了，这里可以用col \[字段名\] format a\[字符数\]的格式定义数据返回格式  
eg：sys\_user表的name字段查询时返回20个字符

SQL> col name format a20;
    SQL>select name from sys_user where username='admin';

### (6)查询某张表约束信息 ###

select * from user_constraints t where t.table_name = '表名'

### (7)强制删除表 ###

即使有表关联也强删，不太建议，不过有些情况可以这样做  
DROP TABLE 表名 CASCADE CONSTRAINTS;

### (8)建表并加主键 ###

例子

create table T_TEST
    (
       UUID                 VARCHAR2(255)        not null,
       CODE                 VARCHAR2(255)        not null,
       constraint PK_T_TEST primary key (UUID)
    );

### (9)Oracle表数据回滚语句 ###

【表数据恢复回N分钟之前】
    
    alter table 表名 enable row movement; 
    flashback table 表名 to timestamp systimestamp - interval 'N' minute;

### (10)Oracle修改有数据的数据字段类型 ###

有些时候，我们是要修改一些数据字段的类型的，mysql是可以支持的，不过Oracle对一些有数据的数据字段是不予许修改的，这时候，可以通过下面sql进行修改，大概思路就是创建一个临时的，负责转运数据，然后再切换

alter table 表A add new_temp varchar2(50);
    update 表A set new_temp = a;
    commit;
    update 表A set a = '';
    commit;
    alter table 表A modify a varchar2(50);
    update 表A set a = new_temp;
    commit;
    alter table 表A drop column new_temp;

## 第三章、Oracle函数用法 ##

### (1)NVL函数和NVL2函数 ###

NVL函数的格式如下：NVL(expr1,expr2)  
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

NVL2函数的格式如下：NVL2(expr1,expr2, expr3)  
含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。SQL> select ename,NVL2(comm,-1,1) from emp;

NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。  
格式如下：  
Coalesce(expr1, expr2, expr3…… exprn)  
表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。  
例子，对于字段b查询出来为空的情况，就返回’’，否就返回vm\_concat(a||’(’||b||’)’)，具体可以参考我的博客: https://blog.csdn.net/u014427391/article/details/84981114

select to_char(nvl2(b,vm_concat(a||'('||b||')'), '') from A group by id

### (2)递归查询 ###

start with 子句：遍历起始条件，有个小技巧，如果要查父结点，这里可以用子结点的列，反之亦然。

connect by 子句：连接条件。关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历；prior跟子结点列subid放在一起，则往叶子结点方向遍历

https://www.cnblogs.com/benbenduo/p/4588612.html  
ps:例子，递归查询一般用于递归查单位，地区信息等等什么的。start with后可以加一些开始条件，没有就写1=1或者不写也可以的。id和parent\_id根据业务需要替换。

select * from 表格1 start with 1=1 connect by id = prior parent_id

### (3)条件sql ###

case when then else end 可以理解为流程控制语句或条件控制语句。可以实现资料获取的时候，可以更多的条件和自定义逻辑  
列子：

case when (select count(1) from t_unit_info 
    					where para_unit_code = unitInfo.unit_code) > 0 
    					then 'closed' else 'open' end as state

> select 1 from mytable;与select anycol(目的表集合中的任意一行） from mytable;与select \* from mytable 作用上来说是没有差别的，都是查看是否有记录，一般是作条件用的。select 1 from 中的1是一常量，查到的所有行的值都是它，但从效率上来说，1>anycol>\*，因为不用查字典表。

### (4)vm\_concat函数 ###

函数wm\_concat(列名)，该函数可以把列值以","号分隔起来,并显示成一行  
**ps:这个列转行函数vm\_comcat()在oracle10比较稳定，到了oracle11就会出现一些不兼容的情况，oracle12版本就没这个函数了**

具体可以参考我的博客：https://blog.csdn.net/u014427391/article/details/84981114

### (5)replace函数 ###

REPLACE(char, search\_string \[, replacement\_string \] )

char : 等待替换的字符串

search\_string : 搜索需要替换的字符串

replacement\_string : 替换字符串

如果replacement\_string缺省或者为null,那么所有char中出现的search\_string 都将被移除

如果search\_string为null,那么结果就是char  
例子：过滤某个字段的换行符

select replace(a, chr(10)) from tableA

### (6)instr函数 ###

格式一：instr( string1, string2 ) / instr(源字符串, 目标字符串)

格式二：instr( string1, string2 \[, start\_position \[, nth\_appearance \] \] ) / instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start\_position给出的数值（即：位置）开始在string1检索，检索第nth\_appearance（几）次出现string2。

例子：查询某个数据字段是否包含换行符

select * from tableA where instr(a,chr(10))>0

### (7)decode函数 ###

含义解释：  
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

该函数的含义如下：  
IF 条件=值1 THEN  
　　　　RETURN(翻译值1)  
ELSIF 条件=值2 THEN  
　　　　RETURN(翻译值2)  
　　　　…  
ELSIF 条件=值n THEN  
　　　　RETURN(翻译值n)  
ELSE  
　　　　RETURN(缺省值)  
END IF

例子：下面的sql就可以加在where后面，sql意思就是t.project\_Name为空的情况\#\{projectName\}=\#\{projectName\}，也即1=1，就是不做任何操作；不为空的情况，t.project\_Name=\#\{projectName\}

decode(t.project_Name,null,#{projectName},t.project_Name) = #{projectName}

### (8)upper和lower函数 ###

upper函数是将字段转成大写，lower相反，是将字段转成小写  
例子：  
upper将 user表里的user\_name字段信息中含有字母的全部转成大写的方法：

update user　set 　user\_name=Upper(user\_name)

将 user表里的user\_name字段信息中含有字母的全部转成小写的方法：

update user　set 　user\_name=lower(user\_name)

### (9)oracle开窗函数 ###

> 分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

> 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化

上面是开窗函数over(…)的简单介绍。

oracle开窗函数使用的话一般是和order、partition by、row\_number()、rank()、dense\_rank()几个函数一起使用

例子：成绩表的例子，

数据表为t\_score，字段分别为stuId，stuName，classId ，score

**over函数和row\_number一起使用：**  
开窗函数和row\_number函数一起使用的话，就是返回一行，不过这里其实不适合用来统计，因为统计成绩的话，可以存在分数一样的两条数据，而row\_number只返回一条数据。

select * from                                                                      
        (                                                                           
        select stuName,row_number()over(partition by classId order by score desc) rnfrom t_score
        )                                                                           
        where rn=1；

**over函数和rank一起使用：**  
rank函数功能也是排序，这里的话，假如有分数一样的两条数据的情况，是会返回两条的，但是假如第二名，就是从3开始计算了

select stuName,rank()over(partition by classId order by score desc) mm from t_score

**over函数和dense\_rank一起使用：**  
dense\_rank函数是rank函数的补充，假如有分数一样的两条数据，是可以按照正常排序，第二名的情况，还是显示2

select stuName,dense_rank()over(partition by classId order by score desc) mm from t_score

### (10)oracle rpad函数 ###

Oracle提供了rpad函数，rpad函数从右边对字符串使用指定的字符进行填充 　　  
rpad(string,padded\_length,\[pad\_string\]) 　　  
string ：表示被填充的字符串 　　  
padded\_length ：表示字符的长度 　　  
pad\_string 是个可选参数，字符串是要粘贴到string的右边，如果这个参数没有写，lpad函数将会在string的右边粘贴空格。

### (11)ceil函数和floor函数 ###

ceil(n) 取大于等于数值n的最小整数；  
floor(n)取小于等于数值n的最大整数

## 第四章、Oracle调优指南 ##

### (1)SQL资源使用情况分析 ###

SELECT SQL_TEXT,
           SQL_FULLTEXT,
           ELAPSED_TIME,
           DISK_READS,
           BUFFER_GETS,
           EXECUTIONS,
           Round(ELAPSED_TIME / EXECUTIONS ,2),
           ROUND(DISK_READS / EXECUTIONS, 2),
           ROUND(BUFFER_GETS / EXECUTIONS , 2),
           ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) 
      FROM V$SQLAREA 
     WHERE EXECUTIONS > 0
       AND BUFFER_GETS > 0
       AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8
       ORDER BY Round(ELAPSED_TIME / EXECUTIONS ,2) desc;

### (2)with…as用法 ###

oracle提供了with…as用法，with as有点像视图，with as短语相当于子查询部分，其语法大概为:

> with 临时表名tmp as (子查询SQL) select \* from 表格 where id in (select \* from  
> tmp)

给个例子，可以scott登录一下，然后给个子查询相关的sql

查出部门名称为SALES的职员信息：

select * from emp where deptno in (select deptno from dept where dname='SALES');

用with as进行改写，看起来貌似with as更麻烦一点，然后为什么一些DBA调优时候喜欢用with as？因为with as是有带缓存的，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中，所以以后查询都是查全局temp表，子查询比较耗性能时候，可以用with as进行改造调优

with tmp as (select deptno from dept where dname='SALES')
    select * from emp where deptno in (select * from tmp);

### (34)查看数据库锁情况 ###

WITH blockers_and_blockees AS
     (SELECT ROWNUM rn, a.*
        FROM gv$session a
       WHERE blocking_session_status = 'VALID'
          OR (inst_id, sid) IN
             (SELECT blocking_instance, blocking_session
                FROM gv$session
               WHERE blocking_session_status = 'VALID'))
    SELECT LPAD(' ', 3 * (LEVEL - 1)) || sid ||
           DECODE(LEVEL, 1, ' root blocker') blocked_session,
           inst_id,
           event,
           TO_CHAR(FLOOR(seconds_in_wait / 3600), 'fm9900') || ':' ||
           TO_CHAR(FLOOR(MOD(seconds_in_wait, 3600) / 60), 'fm00') || ':' ||
           TO_CHAR(MOD(seconds_in_wait, 60), 'fm00') time_in_wait,
           username,
           osuser,
           machine,
           (SELECT owner || '.' || object_name
              FROM dba_objects
             WHERE object_id = b.row_wait_obj#) waiting_on_object,
           CASE
             WHEN row_wait_obj# > 0 THEN
              DBMS_ROWID.rowid_create(1,
                                      row_wait_obj#,
                                      row_wait_file#,
                                      row_wait_block#,
                                      row_wait_row#)
           END waiting_on_rowid,
           (SELECT sql_text
              FROM gv$sql s
             WHERE s.sql_id = b.sql_id
               AND s.inst_id = b.inst_id
               AND s.child_number = b.sql_child_number) current_sql,
           status,
           serial#,
           (SELECT spid
              FROM gv$process p
             WHERE p.addr = b.paddr
               AND p.inst_id = b.inst_id) os_process_id
      FROM blockers_and_blockees b
    CONNECT BY PRIOR sid = blocking_session
           AND PRIOR inst_id = blocking_instance
     START WITH blocking_session IS NULL;

### (35)查询表索引情况 ###

*  查询表索引情况

select table_name,
            index_name,
            last_analyzed,
            blevel,
            num_rows,
            leaf_blocks,
            distinct_keys,
            status
       from user_indexes
      where table_name = 'LIST_PART_TAB';

*  查询索引在哪些列上

select index_name, column_name, column_position
      from user_ind_columns
     where table_name = 'LIST_PART_TAB';

*  查询普通表失效的索引

select ind.index_name,
           ind.table_name,
           ind.blevel,
           ind.num_rows,
           ind.leaf_blocks,
           ind.distinct_keys
      from user_indexes ind
     where status = 'INVALID';

*  查询分区表失效的索引

select a.blevel,
           a.leaf_blocks,
           a.index_name,
           b.table_name,
           a.partition_name,
           a.status
      from user_ind_partitions a, user_indexes b
     where a.index_name = b.index_name
       and a.status = 'UNUSABLE';