---
title: "Oracle 基础"
date: 2019-10-30T11:55:46+08:00
lastmod: 2019-10-30T11:55:46+08:00
tags: ["oracle"]
categories: ["database"]
---

## 字符型
类型 | 大小 | 描述
---- | ---- | ----
varchar2 | 0~4000 | 可变长度字符串
nvarchar2 | 0~1000 | Unicode字符串的可变长字符型数据
char | 0~2000 | 定长字符型数据
nchar | 0~1000 | Unicode字符集定长字符型数据
long | 0~2GB | 变长字符串

## 数字型
类型 | 进制 | 描述
---- | ---- | ----
number(p,s) | 十进制 | p最大精度38位，s小数位数
float | 二进制 | 126位整数

## 日期
类型 | 大小 | 描述
---- | ---- | ----
date | 公元前4712-1-1~9999-12-31 | 存储日期和时间
timestamp | 公元前4712-1-1~9999-12-31 | 精确到小数秒，显示上下午

## 其他数据类型
类型 | 大小 | 描述
---- | ---- | ----
blob | 4GB | 二进制
clob | 4GB | 字符串
bfile | 视操作系统 | 存储非结构化数据到数据库外的文件中

## 创建表
    ```sql
    CREATE TABLE table_name
    (
    column_name datatype [NULL|NOT NULL],
    ...,
    PRIMARY KEY(),
    CONSTRAINT table_name constraint_name FOREIGN KEY (column_name) REFERENCE table_name(column_name) ON DELETE CASCADE,
    CONSTRAINT constraint_name CHECK(condition),
    CONSTRAINT constraint_name UNIQUE(column_name)
    )
    |AS SELECT column_name1,column_name2,...FROM source_table;
    DROP TABLE table_name;
    ```

## 删除表
    ```bash
    DROP TABLE table_name;
    # 执行最快，删除数据、结构、索引、约束、触发器和索引，存储过程和索引invalid状态，直接生效，不可回滚，不释放空间
    TRUNCATE TABLE table_name;
    # 执行较快，只删除数据，直接生效，不可回滚，释放空间
    DELETE FROM table_name [WHERE condition];
    # 执行最慢，只删除数据，commit 后生效，可回滚，不释放空间
    ```

## 操作表列
    ```sql
    ALTER TABLE table_name
    ADD column_name datatype [NULL|NOT NULL]
    |MODIFY column_name new_datatype|NULL|NOT NULL
    |DROP COLUMN column_name;
    -- 删除列时通常追加 CASCADE CONSTRAINTS ，以删除于该列有关的约束
    ```

## 操作主键
    ```sql
    ALTER TABLE table_name
    ADD CONSTRAINTS constraint_name PRIMARY KEY(column_name)
    |DROP CONSTRAINTS constraint_name;
    ```

## 操作外键
    ```sql
    ALTER TABLE table_name
    ADD CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCE table_name(column_name) ON DELETE CASCADE
    |DROP CONSTRAINT constraint_name;
    ```

## 操作CHECK约束
    ```sql
    ALTER TABLE table_name
    ADD CONSTRAINT constraint_name CHECK(condition)
    |DROP CONSTRAINT constraint_name;
    ```

## 操作UNIQUE约束
    ```sql
    ALTER TABLE table_name
    ADD CONSTRAINT constraint_name UNIQUE(column_name)
    |DROP CONSTRAINT constraint_name;
    ```

## 添加数据
    ```sql
    INSERT INTO table_name(column_name1,column_name2,...)
    VALUES(data1,data2,...)
    |SELECT column_name1,column_name2...FROM table_name2;
    ```

## 修改数据
    ```sql
    UPDATE table_name SET column_name1=data1,column_name2=data2,...[WHERE condition];
    ```

## 删除数据
    ```sql
    DELETE FROM table_name [WHERE condition];
    TRUNCATE TABLE table_name;
    ```

## 查询数据
    ```sql
    SELECT column_name1,column_name2,...FROM table_name [WHERE condition];
    ```

## MERGE语句
    ```sql
    MERGE INTO table_name1 USING table_name2 ON(condition) WHEN MATCHED THEN ... WHEN NOT MATCHED THEN ...;
    ```

## SELECT 语句
    ```sql
    SELECT [DISTINCT|ALL] select_list FROM table_list [WHERE ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...];
    ```

## select_list
    ```sql
    *|[schema.] {table|view} .*|expr[	[AS ]c_alias]
    ```

## expr
    ```sql
    "||" 连接的字符串 | 函数
    ```

## ORDER BY ...
    ```sql
    {expr|positon|c_alias} {ASC|DESC} {NULLS FIRST|NULLS LAST}[ {expr|positon|c_alias} {ASC|DESC} {NULLS FIRST|NULLS LAST},...]
    ```

## 模糊查询关键字like
    ```sql
    '_'替代一个字符，'%'替代多个字符
    ```

## 从给定值中选取查询
    ```sql
    IN(data1,data2,...)
    ```

## 连接
    ```sql
    -- 连接，只能查询匹配记录
    SELECT select_list FROM table_name1 INNER JOIN table_name2 ON condition;
    -- 左外连接
    SELECT select_list FROM table_name1 LEFT JOIN table_name2 ON condition;
    -- 右外连接
    SELECT select_list FROM table_name1 RIGHT JOIN table_name2 ON condition;
    -- 全外连接
    SELECT select_list FROM table_name1 FULL JOIN table_name2 ON condition;
    ```

## 内置函数
    ```sql
    ABS(n)    -- n绝对值
    MOD(n2,n1)    -- n2对n1取余
    SIGN(n)    -- n的符号
    CEIL(n)    -- 大于等于n的最小整数
    FLOOR(n)    -- 小于等于n的最大整数
    SQRT(n)    -- n的平方根
    POWER(n2,n1)    -- n2的n1次幂
    EXP(n)    -- e的n次幂
    LOG(n1,n2)    -- n1为底n2的对数
    LN(n)    -- n的自然对数
    ROUND(n2,n1)    -- n2小数部分四舍五入至n1位
    TRUNC(n2,n1)    -- n2截取至n1位
    CHR(n)    -- 把n根据ASCII转换成字符
    ASCII(char)    -- 参数首字母的ASCII值
    LENGTH(char)    -- 字符串长度
    SUBSTR(char,position[,substring_length])    -- 截取字符串
    CONCAT(char1,char2)    -- 连接字符串
    INSTR(string,substring[,position[,occurrence]])    -- 查找字符串
    UPPER(char)    -- 转换成大写
    LOWER(char)    -- 转换成小些
    INITCAP(char)    -- 单词首字母大写
    NLSSORT(char[,nslparam])    -- 按指定方式排序'NLS_SORT=SCHINESE_PINYIN_M'
    REPLACE(char,search_string[,replacement_string])    -- 字符串替换，默认删除
    RPAD(expr1,n[,expr2])    -- 用expr2右填充expr1至长度为n，默认空格
    LPAD(expr1,n[,expr2])    -- 用expr2左填充expr1至长度为n，默认空格
    TRIM([LEADING|TRAILING|BOTH] [trim_character FROM] trim_source)    -- 删除字符串首尾指定字符
    SYSDATE    -- 获取系统当前日期
    SYSTIMESTAMP    -- 获取系统当前时间
    DBTIMEZONE    -- 获取数据库当前时区
    ADD_MONTHS(date,integer)    -- 指定日期增加指定月份数
    SESSIONTIMEZONE    -- 获取当前会话的时区
    LAST_DAY(date)    -- 获取指定日期对应月份的最后一天
    NEXT_DAY(date,char)    -- 获取下周char的日期
    CURRENT_DATE    -- 获取会话时区的当前日期
    EXTRACT(datetime)    -- 从指定时间中获取指定部分
    MONTHS_BETWEEN(date1,date2)    -- 获取两个时间之间的月份数
    NET_TIME(date,timezone1,timezone2)    -- 获取时区1中的时间转换到时区2后的时间
    TO_CHAR(n[,fmt])    -- 转换为字符类型
    TO_DATE(n[,fmt])    -- 转换为时间类型
    TO_NUMBER(n[,fmt])    -- 转换为数字类型
    LNNVL(condition)    -- 排除指定条件函数
    NVL(expr1,expr2)    -- expr1为空时返回expr2
    NVL2(expr1,expr2,expr3)    -- expr1为空时返回expr3，不为空返回expr2
    AVG([DISTINCT|ALL ]expr)    -- 获取平均值
    COUNT(*|[DISTINCT|ALL ]expr)    -- 获取数量
    SUM([DISTINCT|ALL ]expr)    -- 获取和
    SELECT USER FROM DUAL;    -- 返回当前会话的登录名
    USERENV(param)    -- 返回当前会话的信息
    SYS_CONTEXT(namespace,param)    -- 返回oracle已创建的context
    DECODE(expr,search,result[,search1,result1...])    -- expr结果是search返回result
    ```

## 查看所有默认表空间
    ```sql
    SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
    ```

## 查看指定用户默认表空间
    ```sql
    SELECT DEFAULT_STAPCE,USERNAME FROM DBA_USERS WHERE USERNAME='username';
    ```

## 创建表空间
    ```sql
    CREATE TABLESPACE tablespace_name
    DATAFILE filename
    SIZE size
    [AUTOEXTEND [ON NEXT size|OFF]]
    [MAXSIZE size]
    [PERMANENT|TEMPORARY]     永久/临时表空间，默认永久
    [EXTENT MANAGEMENT [DICTIONARY|LOCAL     字典/本地管理方式，默认本地
    [AUTOALLOCATE|UNIFORM. [SIZE integer[K|M]]]];
    ```

## 重命名表空间
    ```sql
    ALTER TABLESPACE oldname RENAME TO newname;
    ```

## 修改表空间大小
    ```sql
    ALTER DATABASE DATAFILE filename RESIZE size;
    ```

## 增加表空间大小
    ```sql
    ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size;
    ```

## 设置表空间读写状态
    ```sql
    ALTER TABLESPACE tablespace_name READ {ONLY|WRITE};
    ```

## 设置表空间可用状态
    ```sql
    ALTER TABLESPACE tablespace_name {ONLINE|OFFLINE [NORMAL|TEMPORARY|IMMEDIATE]};
    ```

## 创建大文件表空间
    ```sql
    CREATE BIGFILE TABLESPACE tablespace_name DATAFILE filename SIZE size;
    ```

## 删除表空间
    ```sql
    DROP TABLESPACE tablespace_name
    [INCLUDING CONTENTS AND DATAFILES]     数据文件删除
    [CASCADE CONSTRAINTS];    完整性删除
    ```

## 查看表空间大小
    ```sql
    SELECT TABLESPACE_NAME,FILE_NAME,BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=tablespace_name;
    ```

## 查看表空间剩余 空间
    ```sql
    SELECT TABLESPACE_NAME,BYTES FROM DBA_FREE_SPACES;
    ```

## 创建/修改用户
    ```sql
    CREATE|ALTER
    USER user_name
    IDENTIFIED BY password
    [DEFAULT TABLESPACE tablespace_name ]
    [QUOTA size|UNLIMITED ON tablespace_name ]    用户使用表空间的最大值
    [PROFILE profile ]    概要文件
    [PASSWORD EXPIRE ]    用户密码过期
    [ACCOUNT LOCK|UNLOCK];    默认锁定状态
    ```

## 删除用户
    ```sql
    DROP USER user_name CASCADE;
    ```

## 授予系统权限
    ```sql
    GRANT
    system_privileges|ALL PRIVILEGES     权限
    TO {user IDENTIFIED BY password|role }    用户/角色
    [WITH ADMIN OPTION];    授予其他用户或角色系统权限
    ```

## 授予对象权限
    ```sql
    GRANT
    object_privilege|ALL     权限
    ON schema.object     对象
    TO user_name|role_name     用户/角色
    [WITH ADMIN OPTION ]    授予其他用户或角色系统权限
    [WITH THE GRANT ANY OBJECT];    授予其他用户或角色对象权限
    ```

## 撤销系统权限
    ```sql
    REVOKE system_privilege FROM
    user|role;
    ```

## 撤销对象权限
    ```sql
    REVOKE
    object_privilege |ALL
    ON schema.object FROM
    user_name|role_name
    [CASCADE CONSTRAINTS];
    ```

## 数据字典
数据 | 字典
---- | ----
系统权限 | DBA_SYS_PRIVS
对象权限 | DBA_TAB_PRIVS
用户角色 | DBA_ROLE_PRIVS

## 创建角色
    ```sql
    CREATE|ALTER     //创建/修改
    ROLE role_name
    [NOT IDENTIFIED|IDENDIFIED BY [password]];
    GRANT     //填充权限
    system_privilege|ALL PRIVILEGES
    TO role_name
    [WITH ADMIN OPTION];
    -- 角色创建完成后不能直接使用，需将角色赋予用户才能使用
    GRANT role_name TO user_name;
    SET ROLE role_name    -- 设置角色生效
    SET ROLE ALL    -- 设置所有角色生效
    SET ROLE ALL EXCEPT role_name    -- 设置只有role_name失效
    SET ROLE NONE    -- 设置所有角色失效
    ```

## 删除角色
    ```sql
    DROP ROLE role_name;
    ```

## 脱机备份（冷备份）/恢复
    ```sql
    关闭数据库服务后直接复制需要的文件，包括数据文件和控制文件
    ```

## 联机备份（热备份）
    ```sql
    ARCHIVE LOG LIST                                        查看本机数据库的日志状态
    ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;   设置日志模式为归档
    SHUTDOWN IMMEDIATE;                                     关闭数据库
    STARTUP MOUNT;                                          启动mount实例
    ALTER DATABASE ARCHIVELOG;                              更改数据库为归档日志模式
    ALTER DATABASE OPEN;                                    更改数据库状态为打开模式
    ALTER TABLESPACE tablespace_name BEGIN BACKUP;          开始备份数据库
    复制文件到其他目录
    ALTER TABLESPACE tablespace_name END BACKUP;            结束备份操作
    ```

## 恢复
    ```sql
    ALTER SYSTEM ARCHIVE LOG CURRENT;                       归档当前日志
    ALTER SYSTEM SWITCH LOGFILE;                            切换日志文件
    SELECT * FROM v$RECOVER_FILE                            获取文件编号
    ALTER DATABASE DATAFILE file_id OFFLINE DROP;           把要恢复的数据文件脱机
    ALTER DATABASE OPEN;                                    更改数据库状态为打开模式
    RECOVER DATAFILE file_id;                               恢复数据文件
    ALTER DATABASE DATAFILE file_id ONLINE;                 设置数据文件联机
    ```

## EXP工具导出数据
    ```bash
    exp db_user/password                                  登陆数据库的用户名和密码，非SYS
    ```

## EXP工具直接导出表
    ```bash
    exp db_user/password file="filename.dmp" tables="table_name,..."
    ```

## EXP工具导出表空间
    ```bash
    exp db_user/password file="filename.dmp" tablespaces="tablespaces_name"
    ```

## EXPDP导出数据
    ```sql
    CREATE DIRECTORY directory_name AS 'file_name';         目录名称 文件名称
    GRANT READ,WRITE ON DIRECTORY directory_name TO db_user;    授权用户使用该目录
    #expdp db_user/password directory=directory_name dumpfile=file_name tables=table_name;
    ```

## IMP导入数据
    ```sql
    imp db_user/password
    ```

## IMP直接导入表
    ```sql
    imp db_user/password file="filename.dmp" tables="table_name,..."
    ```

## IMPDP导入数据
    ```sql
    impdp db_user_password
    ```

## IMPDP直接导入表
    ```bash
    impdp db_user/password directory=dir dumpfile=filename.dmp tables=table_name;
    ```

## RMAN工具配置
    ```sql
    CONN /AS SYSDBA;    连接恢复目录数据库
    CREATE USER rman_user IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name;    创建恢复用户
    GRANT RECOVERY_CATALOG_OWNER TO rman_user;    为新创建的用户授权
    #rman
    CONN CATALOG rman_user/password;    连接新创建的用户
    CREATE CATALOG;    创建恢复目录
    ```

## RMAN工具使用
    ```sql
    #rman target db_user/password@servicename catalog rman_user/password    连接恢复目录数据库
    CONNECT TARGET db_user/password@servicename;    连接目标数据库
    CONNECT CATALOG rman_user/password@servicename;    连接恢复目录数据库
    REGISTER database;    在恢复目录数据库中注册数据库
    ```

## 手动分配通道
    ```sql
    关闭目标数据库，启动到mount状态，运行：
    run
    {
        ALLOCATE CHANNEL channel_name1 DEVICE TYPE {sbt|disk};
        ...
        BACKUP [level] [backup type] [option]
    }
    ```

## 自动分配通道
    ```sql
    CONFIGURE DEVICE TYPE {sbt|disk} PARALLELISM n;    指定通道类型和名称
    CONFIGURE DEFAULT DEVICE TYPE {sbt|disk};    指定默认设备类型
    BACKUP [level] [backup type] [option];
    ```

## BACKUP 参数
    ```sql
    level           备份增量，1、2、3、4或者FULL（全备份）
    backup type     对象类型，database、datafile、tablespace、controlfilecopy、archivelog all
    option          channel备份使用的通道 maxsetsize定义备份集的最大值
    ```

## RESTORE还原
    ```sql
    RESTORE database_object;
    database_object:    DATABASE(mount),TABLESPACE(open),DATAFILE,CONTROLFILE(mount),ARCHIVELOG,SPFILE(mount)
    ```

## RECOVER同步恢复
    ```sql
    RECOVER database_object;
    database_object:    DATABASE(mount),TABLESPACE(open),DATAFILE
    ```

