Oracle Database 常用 SQL

发表于 Disqus：

Based on Oracle Database 11gR2

系统查询

查询系统视图或表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX开头的绝大部分都是视图
-- DBA_TABLES 意为 DBA 拥有的或可以访问的所有的关系表。
-- ALL_TABLES 意为某一用户拥有的或可以访问的所有的关系表。
-- USER_TABLES 意为某一用户所拥有的所有的关系表。
-- 当某一用户本身就为数据库 DBA 时，DBA_TABLES 与 ALL_TABLES 等价。
-- DBA_TABLES >= ALL_TABLES >= USER_TABLES
-- 需要注意的是在 ORACLE 数据库中大小写是敏感的，而此三表中数据默认都是大写的，所以在进行查询的时候注意小写的数据可能会造成数据无法查到。
SELECT * FROM DBA_VIEWS WHERE VIEW_NAME LIKE 'DBA%';
SELECT * FROM DBA_VIEWS WHERE VIEW_NAME LIKE 'ALL%';
SELECT * FROM DBA_VIEWS WHERE VIEW_NAME LIKE 'USER%';
SELECT * FROM DBA_VIEWS WHERE VIEW_NAME LIKE 'V_$%';        -- 针对某个实例的视图
SELECT * FROM DBA_VIEWS WHERE VIEW_NAME LIKE 'GV_$%';       -- 全局视图，针对多个实例环境
SELECT * FROM DBA_VIEWS WHERE VIEW_NAME LIKE 'SESSION%';
SELECT * FROM DBA_VIEWS WHERE VIEW_NAME LIKE 'INDEX%';

常用 DBA 开头的视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT * FROM DBA_USERS;            -- 数据库用户信息
SELECT * FROM DBA_ROLES;            -- 角色信息
SELECT * FROM DBA_SEGMENTS;         -- 表段信息
SELECT * FROM DBA_EXTENTS;          -- 数据区信息
SELECT * FROM DBA_OBJECTS;          -- 数据库对象信息
SELECT * FROM DBA_LOBS;             -- LOB 数据信息
SELECT * FROM DBA_TABLESPACES;      -- 数据库表空间信息
SELECT * FROM DBA_DATA_FILES;       -- 数据文件设置信息
SELECT * FROM DBA_TEMP_FILES;       -- 临时数据文件信息
SELECT * FROM DBA_ROLLBACK_SEGS;    -- 回滚段信息
SELECT * FROM DBA_TS_QUOTAS;        -- 用户表空间配额信息
SELECT * FROM DBA_FREE_SPACE;       -- 数据库空闲空间信息
SELECT * FROM DBA_PROFILES;         -- 数据库用户资源限制信息
SELECT * FROM DBA_SYS_PRIVS;        -- 用户的系统权限信息
SELECT * FROM DBA_TAB_PRIVS;        -- 用户具有的对象权限信息
SELECT * FROM DBA_COL_PRIVS;        -- 用户具有的列对象权限信息
SELECT * FROM DBA_ROLE_PRIVS;       -- 用户具有的角色信息
SELECT * FROM DBA_AUDIT_TRAIL;      -- 审计跟踪记录信息
SELECT * FROM DBA_STMT_AUDIT_OPTS;  -- 审计设置信息
SELECT * FROM DBA_AUDIT_OBJECT;     -- 对象审计结果信息
SELECT * FROM DBA_AUDIT_SESSION;    -- 会话审计结果信息
SELECT * FROM DBA_INDEXES;          -- 用户模式的索引信息

常用 ALL 开头的视图

1
2
3
4
5
6
7
8
SELECT * FROM ALL_USERS;            -- 数据库所有用户的信息
SELECT * FROM ALL_OBJECTS;          -- 数据库所有的对象的信息
SELECT * FROM ALL_DEF_AUDIT_OPTS;   -- 所有默认的审计设置信息
SELECT * FROM ALL_TABLES;           -- 所有的表对象信息
SELECT * FROM ALL_INDEXES;          -- 所有的数据库对象索引的信息
SELECT * FROM ALL_TAB_COMMENTS;     -- 查询所有用户的表，视图等
SELECT * FROM ALL_COL_COMMENTS;     -- 查询所有用户的表的列名和注释。
SELECT * FROM ALL_TAB_COLUMNS;      -- 查询所有用户的表的列名等信息 (详细但是没有备注)

常用 USER 开头的视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT * FROM USER_OBJECTS;                     -- 用户对象信息
SELECT * FROM USER_SOURCE;                      -- 数据库用户的所有资源对象信息
SELECT * FROM USER_SEGMENTS;                    -- 用户的表段信息
SELECT * FROM USER_TABLES;                      -- 用户的表对象信息
SELECT * FROM USER_TAB_COLUMNS;                 -- 用户的表列信息
SELECT * FROM USER_CONSTRAINTS;                 -- 用户的对象约束信息
SELECT * FROM USER_SYS_PRIVS;                   -- 当前用户的系统权限信息
SELECT * FROM USER_TAB_PRIVS;                   -- 当前用户的对象权限信息
SELECT * FROM USER_COL_PRIVS;                   -- 当前用户的表列权限信息
SELECT * FROM USER_COL_COMMENTS;                -- 查询本用户的表的列名和注释
SELECT * FROM USER_ROLE_PRIVS;                  -- 当前用户的角色权限信息
SELECT * FROM USER_INDEXES;                     -- 用户的索引信息
SELECT * FROM USER_IND_COLUMNS;                 -- 用户的索引对应的表列信息
SELECT * FROM USER_CONS_COLUMNS;                -- 用户的约束对应的表列信息
SELECT * FROM USER_CLUSTERS;                    -- 用户的所有簇信息
SELECT * FROM USER_CLU_COLUMNS;                 -- 用户的簇所包含的内容信息
SELECT * FROM USER_CLUSTER_HASH_EXPRESSIONS;    -- 散列簇的信息

常用 V$开头的视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SELECT * FROM V$VERSION;                        -- 版本信息
SELECT * FROM V$DATABASE;                       -- 数据库信息
SELECT * FROM V$DATAFILE;                       -- 数据文件信息
SELECT * FROM V$CONTROLFILE;                    -- 控制文件信息
SELECT * FROM V$LOGFILE;                        -- 重做日志信息
SELECT * FROM V$INSTANCE;                       -- 数据库实例信息
SELECT * FROM V$LOG;                            -- 日志组信息
SELECT * FROM V$LOGHIST;                        -- 日志历史信息
SELECT * FROM V$SGA;                            -- 数据库 SGA 信息
SELECT * FROM V$PARAMETER;                      -- 初始化参数信息
SELECT * FROM V$PROCESS;                        -- 数据库服务器进程信息
SELECT * FROM V$BGPROCESS;                      -- 数据库后台进程信息
SELECT * FROM V$CONTROLFILE_RECORD_SECTION;     -- 控制文件记载的各部分信息
SELECT * FROM V$THREAD;                         -- 线程信息
SELECT * FROM V$DATAFILE_HEADER;                -- 数据文件头所记载的信息
SELECT * FROM V$ARCHIVED_LOG;                   -- 归档日志信息
SELECT * FROM V$ARCHIVE_DEST;                   -- 归档日志的设置信息
SELECT * FROM V$LOGMNR_CONTENTS;                -- 归档日志分析的 DML DDL 结果信息
SELECT * FROM V$LOGMNR_DICTIONARY;              -- 日志分析的字典文件信息
SELECT * FROM V$LOGMNR_LOGS;                    -- 日志分析的日志列表信息
SELECT * FROM V$TABLESPACE;                     -- 表空间信息
SELECT * FROM V$TEMPFILE;                       -- 临时文件信息
SELECT * FROM V$FILESTAT;                       -- 数据文件的 I/O 统计信息
SELECT * FROM V$UNDOSTAT;                       -- UNDO 数据信息
SELECT * FROM V$ROLLNAME;                       -- 在线回滚段信息
SELECT * FROM V$SESSION;                        -- 会话信息
SELECT * FROM V$TRANSACTION;                    -- 事务信息
SELECT * FROM V$ROLLSTAT;                       -- 回滚段统计信息
SELECT * FROM V$PWFILE_USERS;                   -- 特权用户信息
SELECT * FROM V$SQLAREA;                        -- 当前查询过的 SQL 语句访问过的资源及相关的信息
SELECT * FROM V$SQL;                            -- 与 V$SQLAREA 基本相同的相关信息
SELECT * FROM V$SYSSTAT;                        -- 数据库系统状态信息

常用 SESSION 开头的视图

1
2
SELECT * FROM SESSION_ROLES;    -- 会话的角色信息
SELECT * FROM SESSION_PRIVS;    -- 会话的权限信息

常用 INDEX 开头的视图

1
SELECT * FROM INDEX_STATS;      --索引的设置和存储信息

系统参数

数据库名

  • 查看状态：SHOW PARAMETER GLOBAL_NAME;
  • 查值：SELECT * FROM GLOBAL_NAME;
  • 改值： 
    1
    2
    ALTER SYSTEM SET GLOBAL_NAMES = true;
    ALTER DATABASE RENAME GLOBAL_NAME TO orcl;

节省空间的参数

deferred_segment_creation是11.2.0.4版本的 Oracle 数据库新增参数，默认是 true。这会导致在 exp 时，没有 segment 的对象不会导出。即空表不会导出。

  • 查看状态：SHOW PARAMETER DEFERRED_SEGMENT_CREATION;
  • 改值：ALTER system SET deferred_segment_creation = false;，关闭该功能，只对之后导入的表有效。

TABLESPACE 表空间

  • 查询表空间存储路径：SELECT * FROM dba_data_files;

Oracle 表空间详解

创建表空间

1
2
3
CREATE TEMPORARY TABLESPACE TEST_TEMP TEMPFILE 'C:\APP\USERNAME\ORADATA\LOCAL\TEST_TEMP01.DBF' SIZE 1024M AUTOEXTEND ON NEXT 32M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE TEST_DATA LOGGING DATAFILE 'C:\APP\USERNAME\ORADATA\LOCAL\TEST_DATA01.DBF' SIZE 1024M AUTOEXTEND ON NEXT 32M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE TEST_SORT_DATA LOGGING DATAFILE 'C:\APP\USERNAME\ORADATA\LOCAL\TEST_SORT_DATA01.DBF' SIZE 1024M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL;
  • TEMPORARY，表示临时表空间，而且使用数据文件类型需设置为TEMPFILE。
  • TEMPFILE | DATAFILE，表示存储的数据文件的类型。
  • SIZE，表示数据文件的初始大小
  • AUTOEXTEND ON NEXT 32M，表示自增长，当初始大小不够时，会自增长 32M
  • MAXSIZE [unlimited | 2048M] ，表示自增长到不限制或限制为 2048M

修改表空间

1
ALTER USER username defalut|temporary TABLESPACE tablespace_name;

删除表空间

1
2
3
4
5
6
7
8
9
10
-- 删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
-- 删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
-- 删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
-- 删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
-- 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上 CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

USER&ROLE 用户&赋权

创建用户

1
2
3
4
5
6
7
8
9
10
11
-- Create the user 
create user test
  identified by test
  default tablespace TEST_DATA
  temporary tablespace TEST_TEMP
  profile DEFAULT;
-- Grant/Revoke role privileges 
grant connect to test;
grant dba to test;
-- Grant/Revoke system privileges 
grant unlimited tablespace to test;

删除用户

1
drop user test cascade
1
2
3
select owner,object_name from dba_objects where object_type='DATABASE LINK';
-- 或者
select * from dba_db_links;
  • 查看用户是否有权限
1
select * from user_sys_privs t where t.privilege like upper('%link%');
  • 所创建的 dblink 只能是创建者能使用，别的用户使用不了
    SYS CREATE DATABASE LINK NO
  • 删除权限
    SYS DROP PUBLIC DATABASE LINK NO
  • public 表示所创建的 dblink 所有用户都可以使用
    SYS CREATE PUBLIC DATABASE LINK NO
  • 如没有权限 
    1
    2
    -- sys 用户下
    grant CREATE PUBLIC DATABASE LINK，DROP PUBLIC DATABASE LINK to test;
  • 创建 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    -- 没有配置 tnsnames.ora
    create public database link dblinkname 
    connect to username 
    identified by password
       using '(DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME =servicename)
    )
    )';
    1
    2
    3
    4
    5
    6
    -- 已配置 tnsnames.ora
    create public database link dblinktest 
    connect to username 
    identified by password 
    using 'test';
    -- `test`即为 tnsnames.ora 中的 test = (DESCRIPTION = .....
1
drop public database link dblinkname;

常用查询

ROWNUM

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- ROWNUM 小于某个数时可以直接作为查询条件（注意 ORACLE 不支持 SELECT TOP）
SELECT * FROM table WHERE ROWNUM < 3;

-- 查询 ROWNUM 大于某个数值，需要使用子查询，并且 ROWNUM 需要有别名
SELECT * FROM (SELECT ROWNUM RN ,ID,NAME FROM table) WHERE RN > 2;
SELECT * FROM (SELECT ROWNUM RN, table.* FROM table) WHERE RN > 3;

-- 区间查询
SELECT * FROM (SELECT ROWNUM RN, table.* FROM table) WHERE RN > 3 AND RN < 6;

-- 排序 + 前 N 条
SELECT * FROM (SELECT ROWNUM RN, T.* FROM ( SELECT D.* FROM table D ORDER BY column) T ) P WHERE P.RN < 10;

-- 排序 + 区间查询 1（效率低）
SELECT * FROM (SELECT ROWNUM RN, T.* FROM ( SELECT D.* FROM table D ORDER BY table_column)T ) P WHERE P.RN < 9 AND P.RN > 6;

-- 排序 + 区间查询 2（效率高）
SELECT * FROM (SELECT ROWNUM RN, T.* FROM ( SELECT D.* FROM table D ORDER BY table_column) T WHERE ROWNUM < 9 ) P WHERE P.RN > 6;

分页

不排序

1
2
3
4
5
6
-- 效率低
SELECT * FROM (SELECT ROWNUM RN, D.* FROM table D ) P WHERE P.RN <= 20 AND P.RN >= 10;
SELECT * FROM (SELECT ROWNUM RN, D.* FROM table D ) P WHERE P.RN BETWEEN 10 AND 20;

-- 效率高
SELECT * FROM (SELECT ROWNUM RN, D.* FROM table D WHERE ROWNUM <= 20 ) P WHERE P.RN >= 10;

排序

1
2
3
4
5
6
--排序 + 区间查询 1（效率低）
SELECT * FROM (SELECT ROWNUM RN, T.* FROM ( SELECT D.* FROM table D ORDER BY table_column) T ) P WHERE P.RN <= 20 AND P.RN >= 10;
SELECT * FROM (SELECT ROWNUM RN, T.* FROM ( SELECT D.* FROM table D ORDER BY table_column) T ) P WHERE P.RN BETWEEN 10 AND 20;

--排序 + 区间查询 2（效率高）
SELECT * FROM (SELECT ROWNUM RN, T.* FROM ( SELECT D.* FROM table D ORDER BY table_column) T WHERE ROWNUM <= 20 ) P WHERE P.RN >= 10;

时间

TO_CHAR

1
2
3
4
5
6
7
8
9
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') CURRENTTIME,
       TO_CHAR(SYSDATE, 'YYYY') YEAR,
       TO_CHAR(SYSDATE, 'MM') MONTH,
       TO_CHAR(SYSDATE, 'DD') DAY,
       TO_CHAR(SYSDATE, 'DAY') WEEK,
       TO_CHAR(SYSDATE, 'HH24') HOUR,
       TO_CHAR(SYSDATE, 'MI') MINUTE,
       TO_CHAR(SYSDATE, 'SS') SECOND
  FROM DUAL;

TO_DATE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT TO_DATE('2009-07-04 05:02:01', 'YYYY-MM-DD HH24:MI:SS') CURRENTTIME,
       TO_CHAR(TO_DATE('2009-07-04 05:02:01', 'YYYY-MM-DD HH24:MI:SS'),
               'YYYY') YEAR,
       TO_CHAR(TO_DATE('2009-07-04 05:02:01', 'YYYY-MM-DD HH24:MI:SS'),
               'MM') MONTH,
       TO_CHAR(TO_DATE('2009-07-04 05:02:01', 'YYYY-MM-DD HH24:MI:SS'),
               'DD') DAY,
       TO_CHAR(TO_DATE('2009-07-04 05:02:01', 'YYYY-MM-DD HH24:MI:SS'),
               'DAY') WEEK,
       TO_CHAR(TO_DATE('2009-07-04 05:02:01', 'YYYY-MM-DD HH24:MI:SS'),
               'DAY',
               'NLS_DATE_LANGUAGE=AMERICAN') WEEK,
       TO_CHAR(TO_DATE('2009-07-04 05:02:01', 'YYYY-MM-DD HH24:MI:SS'),
               'HH24') HOUR,
       TO_CHAR(TO_DATE('2009-07-04 05:02:01', 'YYYY-MM-DD HH24:MI:SS'),
               'MI') MINUTE,
       TO_CHAR(TO_DATE('2009-07-04 05:02:01', 'YYYY-MM-DD HH24:MI:SS'),
               'SS') SECOND
  FROM DUAL;

MONTHS_BETWEEN

1
2
3
SELECT MONTHS_BETWEEN(TO_DATE('03-31-2014', 'MM-DD-YYYY'),
                      TO_DATE('12-31-2013', 'MM-DD-YYYY')) "MONTHS"
  FROM DUAL;

NEXT_DAY

1
SELECT SYSDATE TODAY, NEXT_DAY(SYSDATE, 6) NEXTWEEK FROM DUAL;

INTERVAL

1
2
3
4
5
6
7
8
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') CURRENTTIME,
       TO_CHAR(SYSDATE - INTERVAL '7' YEAR, 'YYYY-MM-DD HH24:MI:SS') INTERVALYEAR,
       TO_CHAR(SYSDATE - INTERVAL '7' MONTH, 'YYYY-MM-DD HH24:MI:SS') INTERVALMONTH,
       TO_CHAR(SYSDATE - INTERVAL '7' DAY, 'YYYY-MM-DD HH24:MI:SS') INTERVALDAY,
       TO_CHAR(SYSDATE - INTERVAL '7' HOUR, 'YYYY-MM-DD HH24:MI:SS') INTERVALHOUR,
       TO_CHAR(SYSDATE - INTERVAL '7' MINUTE, 'YYYY-MM-DD HH24:MI:SS') INTERVALMINUTE,
       TO_CHAR(SYSDATE - INTERVAL '7' SECOND, 'YYYY-MM-DD HH24:MI:SS') INTERVALSECOND
  FROM DUAL;

BETWEEN AND

1
2
3
4
5
SELECT id, date
  FROM table
 WHERE TO_DATE(date, 'YYYY-MM-DD HH24:MI:SS') BETWEEN
       TO_DATE('2014-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
       TO_DATE('2014-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

字符相关

长度

1
2
SELECT LENGTH('ABCDEFG') LEN    -- LENGTH 
  FROM DUAL;

截取、查找、连接、替换

1
2
3
4
5
6
SELECT SUBSTR('ABCDEFG', 1, 5) SUBSTR,              -- 字符串截取
       INSTR('ABCDEFG', 'BC') INSTR,                -- 查找子串
       'HELLO' || 'WORLD' CONCAT,                   -- 连接
       CONCAT('11', '22') CONCAT,　　　　　　　　      -- 连接
       REPLACE('WISH1', '1', 'YOUHAPPY') REPLACE    -- 替换
  FROM DUAL;

去空格

1
2
3
4
SELECT TRIM('  WISH  ') TRIM,   -- 去前后空格
       RTRIM('WISH  ') RTRIM,   -- 去后面空格
       LTRIM('  WISH') LTRIM    -- 去前面空格
  FROM DUAL;

去前后缀

1
2
3
4
SELECT TRIM('W' FROM 'WISH') TRIM1,
       TRIM(LEADING 'W' FROM 'WISH') DELETEPREFIX,      -- 去前缀
       TRIM(TRAILING 'H' FROM 'WISH') DELETETRAILING    -- 去后缀
  FROM DUAL;

转码

1
2
3
4
5
SELECT ASCII('A') A1,
       ASCII('A') A2,   -- ASCII(转换为对应的十进制数)
       CHR(65) C1,
       CHR(97) C2       -- CHR(十进制转对应字符)
  FROM DUAL;

大小写转换

1
2
3
4
SELECT LOWER('WISH') LOWER,
       UPPER('WISH') UPPER,
       INITCAP('WISH') INITCAP
  FROM DUAL;

转换

1
2
3
SELECT TRANSLATE('WISH1', '1', 'Y') TRANSLATE,      -- 转换，对应一位 (前面的位数大于等于后面的位数)
       TRANSLATE('WISH1', 'SH1', 'HY') TRANSLATE1
  FROM DUAL;

参考文献