Oracle进阶操作

## 一、数据库

下面的操作需要使用到`system/sys`用户或者拥有相同权限的用户。

#### 1、表空间

表空间（Tablespace）是Oracle数据库中用于**存储数据对象的逻辑容器**，它将数据逻辑上组织在一起，并映射到物理的数据文件（Data Files）。每个表空间可以包含一个或多个数据文件，但一个数据文件只能属于一个表空间。表空间是管理存储结构的重要单元，常见的类型包括：

- **系统表空间（SYSTEM、SYSAUX）**：存储数据字典、系统对象等核心信息。
- **用户表空间**：存储用户数据（如表、索引）。
- **临时表空间**：用于排序等临时操作。
- **撤销表空间**：管理事务回滚信息。

##### 创建表空间

**语法结构**

```sql
CREATE TABLESPACE 表空间名  
DATAFILE '数据文件路径' SIZE 大小  
[AUTOEXTEND ON|OFF]  
[EXTENT MANAGEMENT LOCAL|DICTIONARY]  
[SEGMENT SPACE MANAGEMENT AUTO|MANUAL];
```

**示例**

创建一个名为 `user_data` 的表空间，数据文件初始大小为100MB，自动扩展：

```sql
CREATE TABLESPACE user_data  
DATAFILE '/u01/oradata/user01.dbf' SIZE 100M  
AUTOEXTEND ON NEXT 10M MAXSIZE 1G  
EXTENT MANAGEMENT LOCAL  
SEGMENT SPACE MANAGEMENT AUTO;
```

##### 修改表空间

**添加数据文件**

```sql
ALTER TABLESPACE user_data  
ADD DATAFILE '/u01/oradata/user02.dbf' SIZE 50M;
```

**重命名表空间**（Oracle 12c+）

```sql
ALTER TABLESPACE user_data RENAME TO new_user_data;
```

**设置只读/读写**

```sql
ALTER TABLESPACE user_data READ ONLY;  -- 只读
ALTER TABLESPACE user_data READ WRITE; -- 恢复读写
```

##### 删除表空间

**语法**

```sql
DROP TABLESPACE 表空间名  
[INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]];
```

**注意**：`INCLUDING CONTENTS`表示同时删除其中的数据对象，`AND DATAFILES`表示同时删除物理数据文件。

**示例**

```sql
DROP TABLESPACE user_data  
INCLUDING CONTENTS AND DATAFILES;
```

##### 查询表空间信息

**查看所有表空间**

```sql
SELECT tablespace_name, status, contents FROM dba_tablespaces;
```

**查看表空间使用情况**

```sql
SELECT tablespace_name, 
       round(SUM(bytes) / 1024 / 1024, 2) "总大小(MB)",
       round(SUM(bytes - blocks * block_size) / 1024 / 1024, 2) "已用(MB)"
FROM dba_data_files 
GROUP BY tablespace_name;
```

##### 备份与恢复

**表空间备份模式**（需归档模式下）

```sql
ALTER TABLESPACE user_data BEGIN BACKUP; -- 开始备份
-- 手动复制数据文件后
ALTER TABLESPACE user_data END BACKUP;   -- 结束备份
```

#### 2、用户

**用户（User）** 是Oracle数据库中的身份标识，用于控制对数据库对象的访问权限。每个用户拥有：

- **唯一的用户名和密码**用于身份验证
- **默认表空间**：用户创建对象时默认存储的表空间
- **临时表空间**：用于排序等临时操作的存储空间
- **权限和角色**：控制用户可以执行的操作

**用户与模式（Schema）的关系**：

- 用户创建后会自动生成同名的模式
- 模式是用户拥有的数据库对象（表、视图、索引等）的集合

##### 创建用户

**语法**

```sql
CREATE USER 用户名 IDENTIFIED BY 密码
[DEFAULT TABLESPACE 默认表空间]
[TEMPORARY TABLESPACE 临时表空间]
[QUOTA 大小 ON 表空间]
[PROFILE 配置文件]
[ACCOUNT LOCK|UNLOCK];
```

**示例**

创建用户 `scott`，设置密码、表空间和配额：

```sql
CREATE USER scott IDENTIFIED BY tiger
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
QUOTA 50M ON example
ACCOUNT UNLOCK;
```

##### 修改用户

**修改密码**

```sql
ALTER USER scott IDENTIFIED BY new_password;
```

**修改表空间**

```sql
ALTER USER scott DEFAULT TABLESPACE new_users;
```

**设置空间配额**

```sql
ALTER USER scott QUOTA 200M ON users;
```

**锁定/解锁账户**

```sql
ALTER USER scott ACCOUNT LOCK;    -- 锁定
ALTER USER scott ACCOUNT UNLOCK;  -- 解锁
```

##### 删除用户

**语法**

```sql
DROP USER 用户名 [CASCADE];
```

**注意**：`CASCADE`表示同时删除用户拥有的所有对象

**示例**

```sql
DROP USER scott CASCADE;  -- 删除用户及其所有对象
```

##### 授予权限

**系统权限**

```sql
GRANT CREATE SESSION, CREATE TABLE TO scott;
```

**对象权限**

```sql
GRANT SELECT, INSERT ON hr.employees TO scott;
```

**角色权限**

```sql
GRANT CONNECT, RESOURCE TO scott;
```

##### 撤销权限

```sql
REVOKE CREATE TABLE FROM scott;              -- 撤销系统权限
REVOKE SELECT ON hr.employees FROM scott;    -- 撤销对象权限
REVOKE RESOURCE FROM scott;                  -- 撤销角色
```

##### 查询用户信息

**查看所有用户**

```sql
SELECT username, account_status, default_tablespace, temporary_tablespace
FROM dba_users;
```

**查看用户权限**

```sql
SELECT * FROM dba_sys_privs WHERE grantee = 'SCOTT';
```

**查看用户表空间配额**

```sql
SELECT tablespace_name, bytes, max_bytes
FROM dba_ts_quotas 
WHERE username = 'SCOTT';
```

##### 权限对照表

**系统权限**

| 权限名称              | 说明                                       |
| --------------------- | ------------------------------------------ |
| `CREATE SESSION`      | 允许用户连接到数据库。                     |
| `CREATE TABLE`        | 允许用户在其自己的模式中创建表。           |
| `CREATE ANY TABLE`    | 允许用户在任何模式中创建表（需谨慎授予）。 |
| `DROP ANY TABLE`      | 允许用户删除任何模式中的表。               |
| `CREATE VIEW`         | 允许用户创建视图。                         |
| `CREATE PROCEDURE`    | 允许用户创建存储过程、函数或包。           |
| `CREATE USER`         | 允许用户创建新数据库用户。                 |
| `DROP USER`           | 允许用户删除现有用户。                     |
| `ALTER DATABASE`      | 允许用户修改数据库结构（高级权限）。       |
| `GRANT ANY PRIVILEGE` | 允许用户授予任何系统权限（危险权限）。     |
| `SELECT ANY TABLE`    | 允许用户查询任何模式中的表。               |
| `INSERT ANY TABLE`    | 允许用户向任何表插入数据。                 |
| `UPDATE ANY TABLE`    | 允许用户更新任何表中的数据。               |
| `DELETE ANY TABLE`    | 允许用户删除任何表中的数据。               |

**对象权限**

| 权限名称     | 适用对象类型   | 说明                       |
| ------------ | -------------- | -------------------------- |
| `SELECT`     | 表、视图、序列 | 允许查询数据或使用序列。   |
| `INSERT`     | 表、视图       | 允许插入数据。             |
| `UPDATE`     | 表、视图       | 允许更新数据。             |
| `DELETE`     | 表、视图       | 允许删除数据。             |
| `ALTER`      | 表、序列       | 允许修改表结构或序列属性。 |
| `EXECUTE`    | 过程、函数、包 | 允许执行存储过程或函数。   |
| `REFERENCES` | 表             | 允许在外键约束中引用该表。 |
| `INDEX`      | 表             | 允许在表上创建索引。       |
| `ALL`        | 所有对象       | 授予所有可用的对象权限。   |

**预定义角色权限**

#### 3、数据类型

| 类别 | 数据类型 | 语法/范围 | 存储大小 | 描述与用法 |
| ------------------ | ------------------------------------------- | ------------------------------------------------------------------------------------------------ | ------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **字符类型** | `CHAR(size)` | size: 1 ~ 2000**字节** | 固定长度，size字节 | 固定长度字符串。如果数据长度不足，会用空格填充至指定长度。适合存储长度固定的代码（如性别代码'M'/'F'）。 |
| | `VARCHAR2(size)` | size: 1 ~ 4000**字节** (标准) 或 32767 **字节** (扩展，需设置参数) | 可变长度，实际数据长度+1或2字节 | **最常用**的可变长度字符串。节省空间，性能好。**推荐始终使用VARCHAR2代替老的VARCHAR类型。** |
| | `NCHAR(size)` | size: 1 ~ 2000**字符** | 固定长度，size * 字符集字节数 | 固定长度的Unicode字符串。用于需要存储多国语言且长度固定的场景。 |
| | `NVARCHAR2(size)` | size: 1 ~ 4000**字符** | 可变长度，实际字符数 * 字符集字节数 | 可变长度的Unicode字符串。用于需要存储多国语言且长度可变的场景。 |
| **数值类型** | `NUMBER(p, s)` | p(精度): 1 ~ 38 s(小数位): -84 ~ 127 | 1~22字节（取决于精度） | **最常用**的数值类型。`p`是总位数（不含符号和小数点），`s`是小数点后的位数。 • `NUMBER(5)`：最大5位整数，如-99999 ~ 99999。 • `NUMBER(6,2)`：总位数6，小数位2，如1234.56。 |
| | `FLOAT(p)` | p: 1 ~ 126 (二进制精度) | 4或8字节 | 浮点数。`p`是以2为底的精度，近似于NUMBER类型的精度/2。为兼容IEEE754标准而存在。 |
| | `BINARY_FLOAT` | 32位单精度浮点数 | 4字节 | 遵循IEEE754标准的单精度浮点数。用于科学计算，性能比NUMBER高，但可能存在精度误差。 |
| | `BINARY_DOUBLE` | 64位双精度浮点数 | 8字节 | 遵循IEEE754标准的双精度浮点数。用途同BINARY_FLOAT，精度更高。 |
| | `INT` / `INTEGER` | — | 最大22字节（内部仍为NUMBER） | 实际上是`NUMBER(38)`的别名。用于存储整数。 |
| **日期时间类型** | `DATE` | 公元前4712年1月1日 ~ 公元9999年12月31日 | 7字节（世纪、年、月、日、时、分、秒） | 存储**日期和时间**（精确到秒）。是Oracle早期最主要的日期时间类型。 |
| | `TIMESTAMP(precision)` | precision: 0 ~ 9 (小数秒精度) | 7~11字节（取决于精度） | 比DATE更精确，可以存储**小数秒**（最高精确到纳秒）。 • `TIMESTAMP(3)`：精确到毫秒，如`2023-10-25 14:30:45.123`。 |
| | `TIMESTAMP(precision) WITH TIME ZONE` | 同TIMESTAMP，但包含时区信息 | 13字节 | 存储带时区信息的时间戳。用于需要明确时区的全球化应用。 |
| | `TIMESTAMP(precision) WITH LOCAL TIME ZONE` | 同TIMESTAMP，在数据库中以数据库时区标准化存储，返回时转换为会话时区 | 同TIMESTAMP | 存储时区信息，但显示时会自动转换为当前会话的时区。非常实用。 |
| | `INTERVAL YEAR TO MONTH` | `INTERVAL YEAR(precision) TO MONTH` precision: 0~9 (年位数) | 5字节 | 存储**年与月**的时间间隔。 • `INTERVAL '3-2' YEAR TO MONTH`：表示3年2个月。 |
| | `INTERVAL DAY TO SECOND` | `INTERVAL DAY(precision) TO SECOND(frac)` precision: 0~9 (天数位数) frac: 0~9 (小数秒精度) | 11字节 | 存储**天、时、分、秒**的时间间隔。 • `INTERVAL '5 04:30:10.123' DAY TO SECOND(3)`：表示5天4小时30分10.123秒。 |
| **大对象类型** | `CLOB` | 最大 128 TB (理论值) | 可变，存储位置不同 | 字符大对象。用于存储大量文本数据（如文章、日志）。 |
| | `NCLOB` | 同CLOB | 可变，存储位置不同 | 国家字符集大对象。用于存储大量Unicode文本数据。 |
| | `BLOB` | 最大 128 TB (理论值) | 可变，存储位置不同 | 二进制大对象。用于存储非结构化二进制数据（如图片、视频、PDF文件）。 |
| | `BFILE` | 最大 128 TB (理论值)，但受操作系统限制 | 在数据库外部的操作系统文件中 | 外部二进制文件。数据库中仅存储文件指针，实际文件存放在服务器文件系统上。 |
| **二进制与行标识** | `RAW(size)` | size: 1 ~ 2000**字节** | 可变长度 | 原始二进制数据。用于存储字节序列（如加密数据、哈希值），字符集转换不影响其内容。 |
| | `LONG RAW` | 最大 2 GB | 可变长度 | **已废弃**的原始二进制数据类型。仅为向后兼容，**新设计应使用BLOB**。 |
| | `LONG` | 最大 2 GB | 可变长度 | **已废弃**的文本数据类型。仅为向后兼容，**新设计应使用CLOB**。 |
| | `ROWID` | — | 10字节（扩展的ROWID） | 行地址的物理编码。表示数据行在磁盘上的物理位置。 |
| | `UROWID` | — | 最大 4000 字节 | 通用行标识符。可用于逻辑ROWID（如索引组织表）。 |

##### 类型选择建议

**字符串**：日常开发**首选 `VARCHAR2`**。只有在长度绝对固定且很短时（如固定长度的代码）才考虑`CHAR`。

**数字**：**首选 `NUMBER`**。它功能全面且精确。只有在进行大量科学计算且能接受精度损失时，才考虑`BINARY_FLOAT`/`BINARY_DOUBLE`以提升性能。

**日期时间**：

- 如果需要存储精确到秒的日期时间，且不关心小数秒，可使用`DATE`。
- **现代应用推荐使用 `TIMESTAMP`**，因为它更精确。
- 如果应用涉及多时区，**强烈推荐使用 `TIMESTAMP WITH LOCAL TIME ZONE`**。

**大文本/二进制数据**：**绝对不要使用已废弃的 `LONG` 或 `LONG RAW`**。始终使用 `CLOB`（文本） 和 `BLOB`（二进制）。

#### 4、数据库启动顺序

##### 阶段1：NOMOUNT（启动实例）

**作用**：启动数据库实例，分配内存结构，启动后台进程。

**执行命令**：

```sql
STARTUP NOMOUNT;
```

**具体过程**：

1. 读取参数文件（spfile或pfile）
2. 分配SGA（系统全局区）内存
3. 启动后台进程（PMON、SMON、DBWn、LGWR等）
4. 打开告警日志和跟踪文件

##### 阶段2：MOUNT（装载数据库）

**作用**：将数据库与控制文件关联，但不对数据文件进行验证。

**执行命令**：

```sql
ALTER DATABASE MOUNT;
```

**具体过程**：

1. 读取控制文件内容
2. 验证控制文件的完整性
3. 获取数据文件和重做日志文件的位置信息
4. 数据库处于关闭状态，但结构信息已加载

##### 阶段3：OPEN（打开数据库）

**作用**：打开所有数据文件和重做日志文件，使数据库可供用户访问。

**执行命令**：

```sql
ALTER DATABASE OPEN;
```

**具体过程**：

1. 检查所有数据文件和重做日志文件的存在性和一致性
2. 进行必要的实例恢复（如果上次异常关闭）
3. 打开所有在线数据文件
4. 数据库进入可读写状态

#### 5、数据导入/导出

Oracle数据库的导入导出主要通过**Data Pump**(数据泵)技术实现，这是Oracle 10g及以上版本推荐的工具。

##### 导出

```sql
expdp username/password 
  [参数1=值1] [参数2=值2] ...
```

**参数说明**

- `DIRECTORY`：指定转储文件和日志文件的目录对象
- `DUMPFILE`：导出文件名称
- `LOGFILE`：日志文件名称
- `TABLES`：导出特定表
- `SCHEMAS`：导出特定用户的所有对象
- `FULL`：全库导出（需要EXP_FULL_DATABASE权限）
- `CONTENT`：指定导出内容（DATA_ONLY, METADATA_ONLY, ALL）
- `PARALLEL`：并行度，提高导出速度

**示例**

导出整个用户

```sql
-- 创建目录对象（需要DBA权限）
CREATE OR REPLACE DIRECTORY export_dir AS '/u01/app/oracle/export';
GRANT READ, WRITE ON DIRECTORY export_dir TO scott;

-- 导出scott用户的所有对象
expdp scott/tiger 
  DIRECTORY=export_dir 
  DUMPFILE=scott_export.dmp 
  LOGFILE=scott_export.log 
  SCHEMAS=scott
```

按条件导出数据

```sql
expdp scott/tiger 
  DIRECTORY=export_dir 
  DUMPFILE=emp_filtered.dmp 
  TABLES=emp 
  QUERY=emp:"WHERE deptno = 10 AND sal > 2000"
```

##### 导入

```sql
impdp username/password 
  [参数1=值1] [参数2=值2] ...
```

**参数说明**

- `DIRECTORY`：转储文件所在目录
- `DUMPFILE`：导入文件名称
- `LOGFILE`：日志文件名称
- `SCHEMAS`：导入到特定用户
- `REMAP_SCHEMA`：将对象从一个用户映射到另一个用户
- `REMAP_TABLESPACE`：将表空间从一个映射到另一个
- `TABLE_EXISTS_ACTION`：表存在时的处理方式（SKIP, APPEND, TRUNCATE, REPLACE）
- `TRANSFORM`：转换对象属性

**示例**

导入到不同用户

```sql
impdp system/password 
  DIRECTORY=export_dir 
  DUMPFILE=scott_export.dmp 
  LOGFILE=scott_import.log 
  REMAP_SCHEMA=scott:new_user
```

导入特定表

```sql
impdp scott/tiger 
  DIRECTORY=export_dir 
  DUMPFILE=emp_dept_tables.dmp 
  LOGFILE=import_tables.log 
  TABLES=emp,dept 
  TABLE_EXISTS_ACTION=REPLACE
```

## 二、外部表

需要为数据库用户授予目录对象的读写权限

#### 1、概念

**外部表（External Table）** 是Oracle数据库中的一种特殊表，其数据实际存储在数据库外部的操作系统文件中，而不是数据库的数据文件中。外部表通过数据库的元数据定义来访问外部数据文件，实现数据的只读查询。

**主要特点**：

- **数据不存储在数据库中**：数据保留在外部文件（如CSV、TXT等）
- **只读访问**：不能通过DML操作（INSERT/UPDATE/DELETE）修改外部表数据
- **无需加载**：直接访问外部文件，避免数据加载的开销
- **多种数据源支持**：可访问文本文件、Excel、ORC、Parquet等格式

**适用场景**：数据仓库的ETL过程、日志文件分析、临时数据交换、大数据集成

#### 2、创建外部表

##### 语法

```sql
-- 首先创建目录对象
CREATE OR REPLACE DIRECTORY 目录对象名 AS '绝对路径';

CREATE TABLE 表名 (
  列1 数据类型,
  列2 数据类型,
  ...
)
ORGANIZATION EXTERNAL (
  TYPE [ORACLE_LOADER|ORACLE_DATAPUMP]
  DEFAULT DIRECTORY 目录对象/目录路径
  ACCESS PARAMETERS (
    [RECORDS DELIMITED BY NEWLINE]
    [FIELDS TERMINATED BY '分隔符']
    [MISSING FIELD VALUES ARE NULL]
  )
  LOCATION ('文件名')
)
[REJECT LIMIT UNLIMITED];
```

##### 全部参数

```sql
ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE           -- 记录分隔符
  CHARACTERSET UTF8                      -- 字符集
  BADFILE 'bad_%a_%p.bad'                -- 错误记录文件
  LOGFILE 'log_%a_%p.log'                -- 日志文件
  FIELDS TERMINATED BY ','               -- 字段分隔符
  OPTIONALLY ENCLOSED BY '"'             -- 可选引号包围
  MISSING FIELD VALUES ARE NULL          -- 缺失字段处理
  (
    sale_id    INTEGER EXTERNAL,
    product    CHAR(50),
    quantity   INTEGER EXTERNAL,
    sale_date  DATE "YYYY-MM-DD"         -- 日期格式
  )
)
```

##### 示例

创建基于文本文件的外部表

```sql
-- 首先创建目录对象
CREATE OR REPLACE DIRECTORY ext_dir AS '/u01/external_data';

-- 创建外部表
CREATE TABLE sales_external (
  sale_id    NUMBER,
  product    VARCHAR2(50),
  quantity   NUMBER,
  sale_date  DATE
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('sales_data.csv')
)
REJECT LIMIT UNLIMITED;
```

#### 3、查询外部表

查询方式与普通表相同

#### 4、修改外部表

外部表主要修改元数据定义

```sql
-- 修改访问参数
ALTER TABLE sales_external
ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE
  FIELDS TERMINATED BY '|'
  MISSING FIELD VALUES ARE NULL
);

-- 修改文件位置
ALTER TABLE sales_external
LOCATION ('new_sales_data.csv');
```

#### 5、删除外部表

```sql
DROP TABLE sales_external;
```

**注意**：删除外部表仅删除数据库中的元数据定义，不会删除实际的外部数据文件。

#### 6、获取外部表信息

```sql
-- 查看所有外部表
SELECT table_name, type_name, default_directory_name
FROM user_external_tables;

-- 查看外部表位置信息
SELECT * FROM user_external_locations
WHERE table_name = 'SALES_EXTERNAL';

-- 查看拒绝的记录（数据格式错误）
SELECT * FROM user_external_tables 
WHERE table_name = 'SALES_EXTERNAL';
```

## 三、内置函数

#### 1、字符串函数

操作VARCHAR2, CHAR等文本数据

| 函数名           | 语法                                       | 作用说明                 | 示例                                  |
| ---------------- | ------------------------------------------ | ------------------------ | ------------------------------------- |
| LENGTH           | LENGTH(string)                             | 返回字符串长度（字符数） | `LENGTH('Oracle')` → 6               |
| SUBSTR           | SUBSTR(string, start[, length])            | 提取子字符串             | `SUBSTR('Database', 2, 4)` → 'atab'  |
| INSTR            | INSTR(string, substring[, start])          | 返回子字符串位置         | `INSTR('Oracle', 'a')` → 3           |
| UPPER/LOWER      | UPPER(string)/LOWER(string)                | 转换为大写/小写          | `UPPER('hello')` → 'HELLO'           |
| INITCAP          | INITCAP(string)                            | 单词首字母大写           | `INITCAP('oracle db')` → 'Oracle Db' |
| TRIM/LTRIM/RTRIM | TRIM([LEADING\|TRAILING] char FROM string) | 去除首尾空格或指定字符   | `TRIM('  hello  ')` → 'hello'        |
| REPLACE          | REPLACE(string, old, new)                  | 替换字符串               | `REPLACE('ABC', 'B', 'X')` → 'AXC'   |
| CONCAT           | CONCAT(string1, string2)                   | 连接两个字符串           | `CONCAT('A', 'B')` → 'AB'            |
| LPAD/RPAD        | LPAD(string, length[, pad_string])         | 左/右填充字符串          | `LPAD('5', 3, '0')` → '005'          |
| ASCII/CHR        | ASCII(char)/CHR(n)                         | 字符与ASCII码转换        | `ASCII('A')` → 6                     |

#### 2、数值函数

操作NUMBER类型数据

| 函数名     | 语法                       | 作用说明           | 示例                          |
| ---------- | -------------------------- | ------------------ | ----------------------------- |
| ROUND      | ROUND(number[, decimals])  | 四舍五入           | `ROUND(123.456, 2)` → 123.46 |
| TRUNC      | TRUNC(number[, decimals])  | 截断小数位         | `TRUNC(123.456, 2)` → 123.45 |
| CEIL/FLOOR | CEIL(number)/FLOOR(number) | 向上/向下取整      | `CEIL(123.4)` → 124          |
| ABS        | ABS(number)                | 绝对值             | `ABS(-10)` → 10              |
| MOD        | MOD(dividend, divisor)     | 取余数             | `MOD(10, 3)` → 1             |
| POWER      | POWER(base, exponent)      | 幂运算             | `POWER(2, 3)` → 8            |
| SQRT       | SQRT(number)               | 平方根             | `SQRT(25)` → 5               |
| SIGN       | SIGN(number)               | 符号函数（-1,0,1） | `SIGN(-5)` → -1              |

#### 3、日期函数

操作DATE, TIMESTAMP类型数据

| 函数名            | 语法                         | 作用说明               | 示例                                                  |
| ----------------- | ---------------------------- | ---------------------- | ----------------------------------------------------- |
| SYSDATE           | SYSDATE                      | 返回当前系统日期和时间 | `SELECT SYSDATE FROM DUAL`                            |
| CURRENT_DATE      | CURRENT_DATE                 | 当前会话日期           | `SELECT CURRENT_DATE FROM DUAL`                       |
| EXTRACT           | EXTRACT(part FROM date)      | 提取日期部分           | `EXTRACT(YEAR FROM SYSDATE)`                          |
| ADD_MONTHS        | ADD_MONTHS(date, months)     | 添加月份               | `ADD_MONTHS(SYSDATE, 3)`                              |
| MONTHS_BETWEEN    | MONTHS_BETWEEN(date1, date2) | 月份差                 | `MONTHS_BETWEEN('2024-12-31', '2024-01-01')` → 11.97 |
| NEXT_DAY          | NEXT_DAY(date, '星期')       | 下一个指定星期几       | `NEXT_DAY(SYSDATE, 'MONDAY')`                         |
| LAST_DAY          | LAST_DAY(date)               | 当月最后一天           | `LAST_DAY(SYSDATE)`                                   |
| ROUND/TRUNC(date) | ROUND(date[, format])        | 日期四舍五入/截断      | `ROUND(SYSDATE, 'MM')` → 当月1日                     |
| TO_CHAR(date)     | TO_CHAR(date, 'format')      | 日期转字符串           | `TO_CHAR(SYSDATE, 'YYYY-MM-DD')`                      |

#### 4、转换函数

数据类型转换

| 函数名    | 语法                           | 作用说明         | 示例                                    |
| --------- | ------------------------------ | ---------------- | --------------------------------------- |
| TO_CHAR   | TO_CHAR(number/date, 'format') | 转换为字符串     | `TO_CHAR(123.45, '999.99')` → '123.45' |
| TO_NUMBER | TO_NUMBER(string, 'format')    | 字符串转数字     | `TO_NUMBER('123.45')` → 123.45         |
| TO_DATE   | TO_DATE(string, 'format')      | 字符串转日期     | `TO_DATE('2024-01-01', 'YYYY-MM-DD')`   |
| CAST      | CAST(expr AS type)             | 通用类型转换     | `CAST('123' AS NUMBER)` → 123          |
| NVL/NVL2  | NVL(expr1, expr2)              | 空值处理         | `NVL(NULL, '默认值')` → '默认值'       |
| COALESCE  | COALESCE(expr1, expr2, ...)    | 返回第一个非空值 | `COALESCE(NULL, NULL, 'A')` → 'A'      |

#### 5、聚合函数

多行数据统计

| 函数名  | 语法                     | 作用说明    | 示例                 |
| ------- | ------------------------ | ----------- | -------------------- |
| COUNT   | COUNT([DISTINCT] expr)   | 计数        | `COUNT(*)`           |
| SUM     | SUM([DISTINCT] expr)     | 求和        | `SUM(salary)`        |
| AVG     | AVG([DISTINCT] expr)     | 平均值      | `AVG(score)`         |
| MAX/MIN | MAX(expr)/MIN(expr)      | 最大/最小值 | `MAX(price)`         |
| LISTAGG | LISTAGG(expr, delimiter) | 行转列拼接  | `LISTAGG(name, ',')` |

#### 6、分析函数

利用窗口函数，分组计算

| 函数名                 | 语法                      | 作用说明   | 示例                                             |
| ---------------------- | ------------------------- | ---------- | ------------------------------------------------ |
| ROW_NUMBER             | ROW_NUMBER() OVER()       | 行号       | `ROW_NUMBER() OVER(ORDER BY id)`                 |
| RANK/DENSE_RANK        | RANK() OVER()             | 排名       | `RANK() OVER(PARTITION BY dept ORDER BY salary)` |
| LEAD/LAG               | LEAD(expr, offset) OVER() | 前后行数据 | `LEAD(salary, 1) OVER(ORDER BY id)`              |
| FIRST_VALUE/LAST_VALUE | FIRST_VALUE(expr) OVER()  | 首尾值     | `FIRST_VALUE(name) OVER()`                       |

#### 7、其他函数

特殊用途函数

| 函数名         | 语法                                | 作用说明             | 示例                                     |
| -------------- | ----------------------------------- | -------------------- | ---------------------------------------- |
| DECODE         | DECODE(expr, search1, result1, ...) | 条件判断             | `DECODE(status, 1, '激活', 0, '未激活')` |
| CASE           | CASE WHEN condition THEN result END | 条件表达式           | `CASE WHEN score >= 60 THEN '及格' END`  |
| GREATEST/LEAST | GREATEST(expr1, expr2, ...)         | 最大/最小值          | `GREATEST(10, 20, 5)` → 20              |
| USER/UID       | USER/UID                            | 当前用户信息         | `SELECT USER FROM DUAL`                  |
| NVL/NULLIF     | NVL/NULLIF(expr1, expr2)            | 将空值定义一个默认值 | `NVL(column, 0)`                         |

## 四、合并(MERGE)操作

#### 1、概念

**MERGE**（合并）是Oracle数据库中的一种DML操作，它能够根据指定的条件**同时执行INSERT和UPDATE操作**。MERGE语句通过比较源表和目标表的数据，决定是插入新记录还是更新现有记录。

**主要特点**：

- **原子性操作**：INSERT和UPDATE在单个语句中完成
- **条件匹配**：根据ON子句的条件判断执行操作
- **高效性**：避免分别执行INSERT和UPDATE的开销
- **数据同步**：常用于数据仓库的增量加载和数据同步

**适用场景**：数据仓库的ETL过程、主从表数据同步、增量数据更新、缓存表维护

#### 2、语法结构

```sql
MERGE INTO 目标表 [别名]
USING (源表/子查询/视图) [别名]
ON (匹配条件)
WHEN MATCHED THEN
    UPDATE SET 列1 = 值1, 列2 = 值2, ...
    [WHERE 更新条件]
    [DELETE WHERE 删除条件]
WHEN NOT MATCHED THEN
    INSERT (列1, 列2, ...) VALUES (值1, 值2, ...)
    [WHERE 插入条件];
```

#### 3、示例

##### 基本MERGE操作

员工表数据同步，存在则更新工资，不存在则插入新记录

```sql
-- 创建示例表
CREATE TABLE employees_target (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    salary NUMBER,
    dept_id NUMBER,
    last_update DATE
);

CREATE TABLE employees_source (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    salary NUMBER,
    dept_id NUMBER
);

-- 插入测试数据
INSERT INTO employees_target VALUES (1, '张三', 5000, 10, SYSDATE);
INSERT INTO employees_target VALUES (2, '李四', 6000, 20, SYSDATE);

INSERT INTO employees_source VALUES (1, '张三', 5500, 10);  -- 更新
INSERT INTO employees_source VALUES (3, '王五', 7000, 30);  -- 插入

-- MERGE操作
MERGE INTO employees_target t
USING employees_source s
ON (t.emp_id = s.emp_id)
WHEN MATCHED THEN
    UPDATE SET 
        t.emp_name = s.emp_name,
        t.salary = s.salary,
        t.dept_id = s.dept_id,
        t.last_update = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (emp_id, emp_name, salary, dept_id, last_update)
    VALUES (s.emp_id, s.emp_name, s.salary, s.dept_id, SYSDATE);

-- 结果：ID=1的记录被更新，ID=3的记录被插入
```

##### 使用子查询作为源表

```sql
MERGE INTO employees_target t
USING (
 SELECT emp_id, emp_name, salary, dept_id 
 FROM employees_source 
 WHERE dept_id IN (10, 20, 30)
) s
ON (t.emp_id = s.emp_id)
WHEN MATCHED THEN
 UPDATE SET 
 t.salary = s.salary * 1.1, -- 涨薪10%
 t.last_update = SYSDATE
 WHERE t.salary < 8000 -- 只更新工资低于8000的员工
WHEN NOT MATCHED THEN
 INSERT (emp_id, emp_name, salary, dept_id, last_update)
 VALUES (s.emp_id, s.emp_name, s.salary, s.dept_id, SYSDATE)
 WHERE s.salary > 5000; -- 只插入工资高于5000的员工
```

##### 包含`DELETE`子句的MERGE

```sql
MERGE INTO employees_target t
USING employees_source s
ON (t.emp_id = s.emp_id)
WHEN MATCHED THEN
 UPDATE SET 
 t.salary = s.salary,
 t.last_update = SYSDATE
 DELETE WHERE t.salary < 3000 -- 删除工资低于3000的已匹配记录
WHEN NOT MATCHED THEN
 INSERT (emp_id, emp_name, salary, dept_id, last_update)
 VALUES (s.emp_id, s.emp_name, s.salary, s.dept_id, SYSDATE);
```

**注意**：`DELETE`只能与`UPDATE`一起使用，且删除的是目标表中已匹配的记录

##### 使用`DUAL`表进行单行操作

```sql
MERGE INTO employees_target t
USING (
    SELECT 4 as emp_id, '赵六' as emp_name, 6500 as salary, 40 as dept_id 
    FROM DUAL
) s
ON (t.emp_id = s.emp_id)
WHEN MATCHED THEN
    UPDATE SET 
        t.salary = s.salary,
        t.last_update = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (emp_id, emp_name, salary, dept_id, last_update)
    VALUES (s.emp_id, s.emp_name, s.salary, s.dept_id, SYSDATE);
```

#### 4、注意事项

- MERGE操作会触发目标表上的UPDATE和INSERT触发器
- 必须满足目标表的所有约束条件
- 主键冲突会导致操作失败

## 五、物化视图

#### 1、概念

**物化视图（Materialized View）** 是Oracle数据库中的一种数据库对象，它存储了查询结果的物理副本（实际数据），而不是像普通视图那样只保存查询定义。物化视图会定期或实时刷新以保持与基表数据的同步。

**核心特点**：

- **物理存储**：实际存储查询结果数据
- **数据冗余**：占用磁盘空间存储数据副本
- **自动刷新**：支持多种刷新机制保持数据同步
- **性能优化**：预先计算复杂查询结果，提高查询性能

#### 2、物化视图 vs 普通视图

| 特性           | 物化视图                         | 普通视图                     |
| -------------- | -------------------------------- | ---------------------------- |
| **数据存储**   | 物理存储实际数据                 | 只存储查询定义，不存储数据   |
| **性能**       | 查询速度快（直接访问预计算数据） | 查询时实时执行，性能依赖基表 |
| **数据实时性** | 数据可能不是最新的（需要刷新）   | 总是返回最新数据             |
| **存储空间**   | 占用磁盘空间                     | 不占用额外存储空间           |
| **维护成本**   | 需要维护刷新机制                 | 无需特殊维护                 |
| **使用场景**   | 数据仓库、报表系统、复杂查询优化 | 数据安全、简化查询、逻辑封装 |

#### 3、主要作用

**性能提升**

- 预先计算复杂聚合、连接操作
- 减少大型表查询的响应时间

**数据仓库应用**

- 支持星型 schema 优化
- 实现汇总表的自动维护

**分布式环境**

- 在分布式数据库中维护数据副本
- 减少网络传输开销

**报表系统**

- 为报表提供预计算数据
- 支持离线数据分析

#### 4、创建物化视图

##### 语法

##### 定时刷新

**时间单位示例**

- `SYSDATE + 1`：1天后
- `SYSDATE + 1/24`：1小时后
- `SYSDATE + 1/1440`：1分钟后
- `NEXT_DAY(TRUNC(SYSDATE), 'MONDAY')`：下周一

##### STORGAE参数

```sql
CREATE MATERIALIZED VIEW mv_storage
TABLESPACE users
STORAGE (
    INITIAL 100M    -- 初始扩展大小
    NEXT 50M        -- 下次扩展大小
    MAXEXTENTS UNLIMITED  -- 最大扩展数
    PCTINCREASE 0   -- 扩展增量百分比
)
REFRESH COMPLETE ON DEMAND
AS SELECT * FROM large_table;
```

##### LOGGING / NOLOGGING

```sql
CREATE MATERIALIZED VIEW mv_nolog
REFRESH COMPLETE ON DEMAND
NOLOGGING  -- 不生成重做日志，提高性能
AS SELECT * FROM temp_data;
```

##### CACHE / NOCACHE

```sql
CREATE MATERIALIZED VIEW mv_cached
REFRESH COMPLETE ON DEMAND
CACHE  -- 数据缓存在内存缓冲区
AS SELECT * FROM frequently_queried_data;
```

##### 示例

**创建基本物化视图**

```sql
-- 创建部门销售汇总物化视图
CREATE MATERIALIZED VIEW mv_dept_sales
REFRESH COMPLETE ON DEMAND
AS
SELECT 
    d.dept_id,
    d.dept_name,
    COUNT(e.emp_id) as emp_count,
    SUM(e.salary) as total_salary,
    AVG(e.salary) as avg_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;
```

**包含分区的物化视图**

```sql
CREATE MATERIALIZED VIEW mv_monthly_sales
PARTITION BY RANGE (sale_month)
(
    PARTITION p202401 VALUES LESS THAN ('2024-02'),
    PARTITION p202402 VALUES LESS THAN ('2024-03')
)
REFRESH COMPLETE ON DEMAND
AS
SELECT 
    product_id,
    TO_CHAR(sale_date, 'YYYY-MM') as sale_month,
    SUM(quantity) as total_quantity,
    SUM(amount) as total_amount
FROM sales
GROUP BY product_id, TO_CHAR(sale_date, 'YYYY-MM');
```

#### 5、刷新物化视图

##### 手动刷新

```sql
-- 完全刷新（重新执行查询）
EXEC DBMS_MVIEW.REFRESH('mv_dept_sales', 'C');

-- 快速刷新（只更新变化数据）
EXEC DBMS_MVIEW.REFRESH('mv_employee_stats', 'F');

-- 强制刷新（尝试快速，失败则完全）
EXEC DBMS_MVIEW.REFRESH('mv_dept_sales', 'A');

-- 刷新多个物化视图
EXEC DBMS_MVIEW.REFRESH('mv1,mv2,mv3', 'C');
```

##### 自动刷新配置

```sql
-- 创建按计划刷新的物化视图
CREATE MATERIALIZED VIEW mv_daily_sales
REFRESH COMPLETE 
START WITH SYSDATE NEXT SYSDATE + 1  -- 每天刷新
AS
SELECT 
    sale_date,
    SUM(amount) as daily_total
FROM sales
WHERE sale_date >= TRUNC(SYSDATE) - 30
GROUP BY sale_date;

-- 提交时刷新（基表数据变更时立即刷新）
CREATE MATERIALIZED VIEW mv_realtime_orders
REFRESH FAST ON COMMIT
AS
SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent
FROM orders
GROUP BY customer_id;
```

#### 6、管理物化视图

##### 查看物化视图信息

```sql
-- 查看所有物化视图
SELECT mview_name, refresh_mode, refresh_method, last_refresh_date
FROM user_mviews;

-- 查看物化视图依赖的基表
SELECT * FROM user_mview_dependencies
WHERE mview_name = 'MV_DEPT_SALES';

-- 查看物化视图日志（快速刷新需要）
SELECT * FROM user_mview_logs;
```

##### 修改物化视图

```sql
-- 修改刷新方式
ALTER MATERIALIZED VIEW mv_dept_sales
REFRESH FAST ON DEMAND;

-- 启用/禁用查询重写
ALTER MATERIALIZED VIEW mv_employee_stats
ENABLE QUERY REWRITE;

ALTER MATERIALIZED VIEW mv_employee_stats
DISABLE QUERY REWRITE;
```

##### 删除物化视图

```sql
DROP MATERIALIZED VIEW mv_dept_sales;
```

## 六、同义词

#### 1、概念

**同义词（Synonym）** 是Oracle数据库中的一个数据库对象，它为另一个数据库对象（表、视图、序列、存储过程、函数、包、物化视图，甚至另一个同义词）提供别名或替代名称。

**核心特点**：

- **别名功能**：为对象提供简化的访问名称
- **位置透明性**：隐藏对象的实际位置和名称
- **安全控制**：避免直接暴露底层对象
- **简化访问**：简化复杂对象名的引用

#### 2、类型

##### 私有同义词

```sql
-- 在当前用户模式下创建，仅当前用户可用
CREATE SYNONYM emp FOR hr.employees;
```

##### 公有同义词

```sql
-- 在公共模式下创建，所有用户都可访问
CREATE PUBLIC SYNONYM employees FOR hr.employees;
```

#### 3、主要作用

**简化对象引用**

```sql
-- 原始：需要完整模式名
SELECT * FROM hr.employees@remote_db;

-- 使用同义词：简化访问
SELECT * FROM emp;
```

**位置透明性**

```sql
-- 同义词可以指向本地或远程对象
CREATE SYNONYM remote_emp FOR hr.employees@remote_db;
```

**安全增强**

```sql
-- 用户通过同义词访问，不直接接触底层对象
GRANT SELECT ON emp_synonym TO user1;
```

**应用程序可移植性**

```sql
-- 应用程序使用同义词，底层对象变更不影响代码
CREATE SYNONYM app_data FOR prod_schema.data_table;
```

#### 4、创建同义词

##### 语法

```sql
CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym_name 
FOR [schema.]object_name [@dblink];
```

##### 示例

```sql
-- 为当前用户的表创建同义词
CREATE SYNONYM emp FOR employees;

-- 为其他用户的表创建同义词
CREATE SYNONYM hr_emp FOR hr.employees;

-- 为视图创建同义词
CREATE SYNONYM emp_view FOR hr.v_employee_details;

-- 为序列创建同义词
CREATE SYNONYM emp_seq FOR hr.employee_id_seq;

-- 为存储过程创建同义词
CREATE SYNONYM calc_salary FOR hr.calculate_salary;

-- 为远程数据库对象创建同义词
CREATE SYNONYM remote_sales FOR sales.sales_data@remote_db;

-- 公有同义词（所有用户可访问）
CREATE PUBLIC SYNONYM all_emps FOR hr.employees;

-- 公有同义词指向远程对象
CREATE PUBLIC SYNONYM company_data FOR corp.hr_data@headquarters;

-- 为常用函数创建公有同义词
CREATE PUBLIC SYNONYM current_date FOR sysdate;

-- 如果同义词已存在则替换
CREATE OR REPLACE SYNONYM emp FOR hr.employees_new;
CREATE OR REPLACE PUBLIC SYNONYM app_config FOR config.system_settings;
```

#### 5、查看同义词信息

##### 查看所有可访问的同义词

```sql
-- 查看当前用户可访问的所有同义词（包括公有）
SELECT synonym_name, table_owner, table_name, db_link
FROM all_synonyms
WHERE owner IN (USER, 'PUBLIC')
ORDER BY owner, synonym_name;
```

##### 查看同义词依赖关系

```sql
-- 查看同义词指向的对象信息
SELECT s.synonym_name, 
       s.table_owner as referenced_owner,
       s.table_name as referenced_object,
       s.db_link,
       o.object_type
FROM user_synonyms s
LEFT JOIN all_objects o ON (s.table_owner = o.owner AND s.table_name = o.object_name)
WHERE s.synonym_name = 'EMP';
```

#### 6、使用同义词

##### 在SQL语句中使用

```sql
-- DQL：数据查询
SELECT * FROM emp;  -- 代替 SELECT * FROM hr.employees;

-- DML：数据操作
INSERT INTO emp (emp_id, emp_name) VALUES (1001, '张三');
UPDATE emp SET salary = salary * 1.1 WHERE dept_id = 10;
DELETE FROM emp WHERE emp_id = 1001;

-- DDL：需要直接对象名，不能使用同义词
-- CREATE INDEX idx_emp ON emp(emp_id);  -- 错误！
CREATE INDEX idx_emp ON hr.employees(emp_id);  -- 正确
```

##### 在PL/SQL中使用

```sql
DECLARE
    v_count NUMBER;
    v_emp_name VARCHAR2(100);
BEGIN
    -- 使用同义词查询
    SELECT COUNT(*) INTO v_count FROM emp;
  
    -- 使用同义词调用存储过程
    calc_salary(1001, 5000);
  
    DBMS_OUTPUT.PUT_LINE('员工数量: ' || v_count);
END;
/
```

##### 序列同义词使用

```sql
-- 创建序列同义词
CREATE SYNONYM emp_id_seq FOR hr.employee_id_seq;

-- 使用序列同义词
INSERT INTO emp (emp_id, emp_name) 
VALUES (emp_id_seq.NEXTVAL, '李四');

SELECT emp_id_seq.CURRVAL FROM DUAL;
```

#### 7、修改同义词

##### 使用 OR REPLACE 修改

```sql
-- 修改同义词指向新对象
CREATE OR REPLACE SYNONYM emp FOR hr.employees_new;

-- 修改为指向远程对象
CREATE OR REPLACE SYNONYM emp FOR hr.employees@remote_db;

-- 修改公有同义词
CREATE OR REPLACE PUBLIC SYNONYM app_users FOR new_schema.user_table;

-- 注意
-- 同义词本身不能直接修改，只能重建
-- 错误示例：
-- ALTER SYNONYM emp ... (不支持)
-- 正确做法：使用 CREATE OR REPLACE
CREATE OR REPLACE SYNONYM emp FOR hr.employees_v2;
```

#### 8、删除同义词

##### 普通删除

```sql
-- 删除当前用户的同义词
DROP SYNONYM emp;

-- 删除其他用户的同义词（需要权限）
DROP SYNONYM hr.emp_synonym;

-- 删除公有同义词
DROP PUBLIC SYNONYM all_employees;

-- 如果同义词不存在时避免错误
DROP PUBLIC SYNONYM IF EXISTS old_synonym;
```

##### 批量删除

```sql
-- 删除所有以TEMP开头的同义词
BEGIN
    FOR rec IN (SELECT synonym_name FROM user_synonyms 
                WHERE synonym_name LIKE 'TEMP%') 
    LOOP
        EXECUTE IMMEDIATE 'DROP SYNONYM ' || rec.synonym_name;
    END LOOP;
END;
/
```

#### 9、同义词权限管理

##### 授予同义词权限

```sql
-- 授予同义词的SELECT权限
GRANT SELECT ON emp TO user1, user2;

-- 授予同义词的DML权限
GRANT INSERT, UPDATE, DELETE ON emp TO user1;

-- 授予所有权限
GRANT ALL ON emp TO user1;
```

##### 查看同义词权限

```sql
-- 查看同义词上的权限
SELECT grantee, privilege, grantable
FROM user_tab_privs
WHERE table_name = 'EMP';
```

##### 撤销同义词权限

```sql
-- 撤销SELECT权限
REVOKE SELECT ON emp FROM user1;

-- 撤销所有权限
REVOKE ALL ON emp FROM user1;
```

## 七、DBLink

创建 DBLink 需具备 `CREATE DATABASE LINK` 权限；操作远程表时需在目标库有对应权限。

#### 1、概念

**DBLink**（数据库链接）是 Oracle 数据库中的一个对象，用于在不同数据库实例之间建立连接，允许用户在一个数据库中访问另一个数据库的对象（如表、视图等）。DBLink 常用于**分布式数据库**环境中，实现跨数据库的数据查询或操作。

#### 2、创建DBLink

##### 语法

```sql
CREATE [SHARED] [PUBLIC] DATABASE LINK 连接名
  CONNECT TO 连接账户 IDENTIFIED BY 连接密码
  USING '网络地址:端口/方式';
```

**可选参数说明**：

- `SHARED`：指定共享数据库链接（多个会话可复用连接，节省资源）。
- `PUBLIC`：创建公共链接，所有用户均可访问（需有 `CREATE PUBLIC DATABASE LINK` 权限）。
- `link_name`：自定义的链接名称。
- `target_username`/`target_password`：目标数据库的登录凭据。
- `connect_string`：目标数据库的连接描述符（如 TNS 名称或 Easy Connect 字符串）。

##### 示例

创建一个名为 `remote_db` 的私有链接，连接到 IP 为 `192.168.1.100` 的数据库。

```sql
CREATE DATABASE LINK remote_db
  CONNECT TO scott IDENTIFIED BY tiger
  USING '192.168.1.100:1521/ORCL';
```

#### 3、使用DBLink

查询远程数据库中的 `employees` 表

```sql
SELECT * FROM employees@remote_db;
```

向远程表的 `departments` 插入数据

```sql
INSERT INTO departments@remote_db (dept_id, dept_name) 
VALUES (101, 'HR');
```

#### 4、删除DBLink

删除名为 `remote_db` 的链接

```sql
DROP DATABASE LINK remote_db;
```

#### 5、查看已创建的 DBLink

查询当前用户下的所有 DBLink

```sql
SELECT db_link, username, host FROM user_db_links;
```

#### 6、修改`tnsnames.ora`配置创建DBLink

##### 文件位置

- Linux/Unix: `$ORACLE_HOME/network/admin/tnsnames.ora`
- Windows: `%ORACLE_HOME%\network\admin\tnsnames.ora`

##### 示例

```sql
# 在 tnsnames.ora 中新增以下内容
REMOTE_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )
```

**参数说明**：

- `REMOTE_DB`：自定义的 TNS 连接名称（后续在 DBLink 中直接引用）。
- `HOST`/`PORT`：远程数据库的 IP 和端口。
- `SERVICE_NAME`：远程数据库的服务名（或使用 `SID` 替代）。

## 八、序列

#### 1、概念

**序列**是 Oracle 数据库中的一种数据库对象，用于生成唯一的、按一定规律递增或递减的数值序列。序列不依赖于具体表，独立存在，通常用于为主键列提供唯一的数值。

**主要作用**

- 为主键字段自动生成唯一值
- 实现自增字段功能
- 生成订单号、流水号等业务编号
- 避免主键冲突，提高数据完整性

#### 2、创建序列

##### 语法

```sql
CREATE SEQUENCE 序列名
  [START WITH n]           -- 起始值，默认1
  [INCREMENT BY n]         -- 步长（正数递增，负数递减），默认1
  [MINVALUE n | NOMINVALUE] -- 最小值，默认NOMINVALUE
  [MAXVALUE n | NOMAXVALUE] -- 最大值，默认NOMAXVALUE
  [CYCLE | NOCYCLE]        -- 是否循环，默认NOCYCLE
  [CACHE n | NOCACHE]      -- 缓存大小，默认20
  [ORDER | NOORDER];       -- 是否保证顺序，默认NOORDER
```

**参数注释**：

- `START WITH`：序列起始值（可选）
- `INCREMENT BY`：每次增加的值（可选）
- `MINVALUE/MAXVALUE`：序列的最小/最大值（可选）
- `CYCLE`：达到极值后是否重新循环（可选）
- `CACHE`：预分配缓存的序列值数量（提高性能）
- `ORDER`：是否保证序列值按请求顺序生成（RAC环境有用）

##### 示例

**创建带缓存的循环序列**

```sql
CREATE SEQUENCE seq_order_no
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 9999
  CACHE 50
  CYCLE;
```

#### 3、使用序列

##### 获取下一个值（递增）

```sql
SELECT seq_employee_id.NEXTVAL FROM dual;
-- 结果：1000（首次调用）
```

##### 获取当前值（不递增）

```sql
SELECT seq_employee_id.CURRVAL FROM dual;
-- 结果：1000（需先调用NEXTVAL）
```

##### 插入数据时使用

```sql
INSERT INTO employees (emp_id, emp_name, hire_date)
VALUES (seq_employee_id.NEXTVAL, '张三', SYSDATE);
```

##### 创建表时设置默认值

```sql
CREATE TABLE orders (
  order_id NUMBER DEFAULT seq_order_no.NEXTVAL,
  order_date DATE,
  amount NUMBER
);
```

#### 4、修改序列

##### 修改序列的增量值

```sql
ALTER SEQUENCE seq_employee_id
  INCREMENT BY 2
  MAXVALUE 10000;
```

#### 5、删除序列

```sql
DROP SEQUENCE seq_employee_id;
```

#### 6、查看序列信息

```sql
-- 查看用户拥有的序列
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;

-- 查看序列的详细定义
SELECT * FROM user_sequences WHERE sequence_name = 'SEQ_EMPLOYEE_ID';
```

#### 7、注意事项

- **事务无关性**：序列值的生成独立于事务，即使事务回滚，序列值也不会回退
- **缓存丢失**：使用CACHE时，数据库重启可能导致缓存序列值丢失
- **并发安全**：序列是线程安全的，多个会话同时调用不会产生重复值
- **GAP问题**：由于缓存、回滚等原因，序列值可能不连续（这通常是正常现象）

## 九、数据块

#### 1、概念

**数据块**是 Oracle 数据库中最小的存储单位，也是 I/O 操作的基本单元。它是物理存储层面上的概念，对应操作系统中的数据块，但大小可以不同。

**主要作用**：

- 数据库存储的基本单位
- I/O 操作的最小单元
- 管理空间分配和回收
- 提高数据访问效率

#### 2、物理结构

#### 3、查看数据块信息

```sql
-- 查看数据库标准块大小
SELECT name, value FROM v$parameter WHERE name = 'db_block_size';

-- 查看表空间使用的块大小
SELECT tablespace_name, block_size FROM dba_tablespaces;

-- 查看段（表、索引）使用的数据块信息
SELECT segment_name, blocks, bytes, blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') AS actual_bytes
FROM user_segments
WHERE segment_name = 'EMPLOYEES';

-- 查看表中实际使用的数据块数量
SELECT table_name, blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name = 'EMPLOYEES';
```

#### 4、创建不同块大小的表空间

```sql
-- 创建非标准块大小的表空间（需要先配置DB_CACHE_SIZE）
CREATE TABLESPACE ts_large_blocks
  DATAFILE '/u01/oradata/large_ts.dbf' SIZE 100M
  BLOCKSIZE 16K;  -- 16KB块大小
```

```

## 一、数据库

下面的操作需要使用到`system/sys`用户或者拥有相同权限的用户。

#### 1、表空间

##### 创建表空间

**语法结构**

```sql
CREATE TABLESPACE 表空间名  
DATAFILE '数据文件路径' SIZE 大小  
[AUTOEXTEND ON|OFF]  
[EXTENT MANAGEMENT LOCAL|DICTIONARY]  
[SEGMENT SPACE MANAGEMENT AUTO|MANUAL];
```

**示例**

创建一个名为 `user_data` 的表空间，数据文件初始大小为100MB，自动扩展：

```sql
CREATE TABLESPACE user_data  
DATAFILE '/u01/oradata/user01.dbf' SIZE 100M  
AUTOEXTEND ON NEXT 10M MAXSIZE 1G  
EXTENT MANAGEMENT LOCAL  
SEGMENT SPACE MANAGEMENT AUTO;
```

##### 修改表空间

**添加数据文件**

```sql
ALTER TABLESPACE user_data  
ADD DATAFILE '/u01/oradata/user02.dbf' SIZE 50M;
```

**重命名表空间**（Oracle 12c+）

```sql
ALTER TABLESPACE user_data RENAME TO new_user_data;
```

**设置只读/读写**

```sql
ALTER TABLESPACE user_data READ ONLY;  -- 只读
ALTER TABLESPACE user_data READ WRITE; -- 恢复读写
```

##### 删除表空间

**语法**

```sql
DROP TABLESPACE 表空间名  
[INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]];
```

**注意**：`INCLUDING CONTENTS`表示同时删除其中的数据对象，`AND DATAFILES`表示同时删除物理数据文件。

**示例**

```sql
DROP TABLESPACE user_data  
INCLUDING CONTENTS AND DATAFILES;
```

##### 查询表空间信息

**查看所有表空间**

```sql
SELECT tablespace_name, status, contents FROM dba_tablespaces;
```

**查看表空间使用情况**

##### 备份与恢复

**表空间备份模式**（需归档模式下）

```sql
ALTER TABLESPACE user_data BEGIN BACKUP; -- 开始备份
-- 手动复制数据文件后
ALTER TABLESPACE user_data END BACKUP;   -- 结束备份
```

#### 2、用户

**用户（User）** 是Oracle数据库中的身份标识，用于控制对数据库对象的访问权限。每个用户拥有：

**用户与模式（Schema）的关系**：

- 用户创建后会自动生成同名的模式
- 模式是用户拥有的数据库对象（表、视图、索引等）的集合

##### 创建用户

**语法**

**示例**

创建用户 `scott`，设置密码、表空间和配额：

```sql
CREATE USER scott IDENTIFIED BY tiger
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
QUOTA 50M ON example
ACCOUNT UNLOCK;
```

##### 修改用户

**修改密码**

```sql
ALTER USER scott IDENTIFIED BY new_password;
```

**修改表空间**

```sql
ALTER USER scott DEFAULT TABLESPACE new_users;
```

**设置空间配额**

```sql
ALTER USER scott QUOTA 200M ON users;
```

**锁定/解锁账户**

```sql
ALTER USER scott ACCOUNT LOCK;    -- 锁定
ALTER USER scott ACCOUNT UNLOCK;  -- 解锁
```

##### 删除用户

**语法**

```sql
DROP USER 用户名 [CASCADE];
```

**注意**：`CASCADE`表示同时删除用户拥有的所有对象

**示例**

```sql
DROP USER scott CASCADE;  -- 删除用户及其所有对象
```

##### 授予权限

**系统权限**

```sql
GRANT CREATE SESSION, CREATE TABLE TO scott;
```

**对象权限**

```sql
GRANT SELECT, INSERT ON hr.employees TO scott;
```

**角色权限**

```sql
GRANT CONNECT, RESOURCE TO scott;
```

##### 撤销权限

##### 查询用户信息

**查看所有用户**

```sql
SELECT username, account_status, default_tablespace, temporary_tablespace
FROM dba_users;
```

**查看用户权限**

```sql
SELECT * FROM dba_sys_privs WHERE grantee = 'SCOTT';
```

**查看用户表空间配额**

```sql
SELECT tablespace_name, bytes, max_bytes
FROM dba_ts_quotas 
WHERE username = 'SCOTT';
```

##### 权限对照表

**系统权限**

**对象权限**

**预定义角色权限**

#### 3、数据类型

##### 类型选择建议

**字符串**：日常开发**首选 `VARCHAR2`**。只有在长度绝对固定且很短时（如固定长度的代码）才考虑`CHAR`。

**数字**：**首选 `NUMBER`**。它功能全面且精确。只有在进行大量科学计算且能接受精度损失时，才考虑`BINARY_FLOAT`/`BINARY_DOUBLE`以提升性能。

**日期时间**：

**大文本/二进制数据**：**绝对不要使用已废弃的 `LONG` 或 `LONG RAW`**。始终使用 `CLOB`（文本） 和 `BLOB`（二进制）。

#### 4、数据库启动顺序

##### 阶段1：NOMOUNT（启动实例）

**作用**：启动数据库实例，分配内存结构，启动后台进程。

**执行命令**：

```sql
STARTUP NOMOUNT;
```

**具体过程**：

1. 读取参数文件（spfile或pfile）
2. 分配SGA（系统全局区）内存
3. 启动后台进程（PMON、SMON、DBWn、LGWR等）
4. 打开告警日志和跟踪文件

##### 阶段2：MOUNT（装载数据库）

**作用**：将数据库与控制文件关联，但不对数据文件进行验证。

**执行命令**：

```sql
ALTER DATABASE MOUNT;
```

**具体过程**：

1. 读取控制文件内容
2. 验证控制文件的完整性
3. 获取数据文件和重做日志文件的位置信息
4. 数据库处于关闭状态，但结构信息已加载

##### 阶段3：OPEN（打开数据库）

**作用**：打开所有数据文件和重做日志文件，使数据库可供用户访问。

**执行命令**：

```sql
ALTER DATABASE OPEN;
```

**具体过程**：

1. 检查所有数据文件和重做日志文件的存在性和一致性
2. 进行必要的实例恢复（如果上次异常关闭）
3. 打开所有在线数据文件
4. 数据库进入可读写状态

#### 5、数据导入/导出

Oracle数据库的导入导出主要通过**Data Pump**(数据泵)技术实现，这是Oracle 10g及以上版本推荐的工具。

##### 导出

```sql
expdp username/password 
  [参数1=值1] [参数2=值2] ...
```

**参数说明**

**示例**

导出整个用户

```sql
-- 创建目录对象（需要DBA权限）
CREATE OR REPLACE DIRECTORY export_dir AS '/u01/app/oracle/export';
GRANT READ, WRITE ON DIRECTORY export_dir TO scott;

-- 导出scott用户的所有对象
expdp scott/tiger 
  DIRECTORY=export_dir 
  DUMPFILE=scott_export.dmp 
  LOGFILE=scott_export.log 
  SCHEMAS=scott
```

按条件导出数据

```sql
expdp scott/tiger 
  DIRECTORY=export_dir 
  DUMPFILE=emp_filtered.dmp 
  TABLES=emp 
  QUERY=emp:"WHERE deptno = 10 AND sal > 2000"
```

##### 导入

```sql
impdp username/password 
  [参数1=值1] [参数2=值2] ...
```

**参数说明**

**示例**

导入到不同用户

```sql
impdp system/password 
  DIRECTORY=export_dir 
  DUMPFILE=scott_export.dmp 
  LOGFILE=scott_import.log 
  REMAP_SCHEMA=scott:new_user
```

导入特定表

```sql
impdp scott/tiger 
  DIRECTORY=export_dir 
  DUMPFILE=emp_dept_tables.dmp 
  LOGFILE=import_tables.log 
  TABLES=emp,dept 
  TABLE_EXISTS_ACTION=REPLACE
```

## 二、外部表

需要为数据库用户授予目录对象的读写权限

#### 1、概念

**主要特点**：

**适用场景**：数据仓库的ETL过程、日志文件分析、临时数据交换、大数据集成

#### 2、创建外部表

##### 语法

```sql
-- 首先创建目录对象
CREATE OR REPLACE DIRECTORY 目录对象名 AS '绝对路径';

##### 全部参数

##### 示例

创建基于文本文件的外部表

```sql
-- 首先创建目录对象
CREATE OR REPLACE DIRECTORY ext_dir AS '/u01/external_data';

#### 3、查询外部表

查询方式与普通表相同

#### 4、修改外部表

外部表主要修改元数据定义

```sql
-- 修改访问参数
ALTER TABLE sales_external
ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE
  FIELDS TERMINATED BY '|'
  MISSING FIELD VALUES ARE NULL
);

-- 修改文件位置
ALTER TABLE sales_external
LOCATION ('new_sales_data.csv');
```

#### 5、删除外部表

```sql
DROP TABLE sales_external;
```

**注意**：删除外部表仅删除数据库中的元数据定义，不会删除实际的外部数据文件。

#### 6、获取外部表信息

```sql
-- 查看所有外部表
SELECT table_name, type_name, default_directory_name
FROM user_external_tables;

-- 查看外部表位置信息
SELECT * FROM user_external_locations
WHERE table_name = 'SALES_EXTERNAL';

-- 查看拒绝的记录（数据格式错误）
SELECT * FROM user_external_tables 
WHERE table_name = 'SALES_EXTERNAL';
```

## 三、内置函数

#### 1、字符串函数

操作VARCHAR2, CHAR等文本数据

#### 2、数值函数

操作NUMBER类型数据

#### 3、日期函数

操作DATE, TIMESTAMP类型数据

#### 4、转换函数

数据类型转换

#### 5、聚合函数

多行数据统计

#### 6、分析函数

利用窗口函数，分组计算

#### 7、其他函数

特殊用途函数

## 四、合并(MERGE)操作

#### 1、概念

**主要特点**：

**适用场景**：数据仓库的ETL过程、主从表数据同步、增量数据更新、缓存表维护

#### 2、语法结构

#### 3、示例

##### 基本MERGE操作

员工表数据同步，存在则更新工资，不存在则插入新记录

```sql
-- 创建示例表
CREATE TABLE employees_target (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    salary NUMBER,
    dept_id NUMBER,
    last_update DATE
);

CREATE TABLE employees_source (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    salary NUMBER,
    dept_id NUMBER
);

-- 插入测试数据
INSERT INTO employees_target VALUES (1, '张三', 5000, 10, SYSDATE);
INSERT INTO employees_target VALUES (2, '李四', 6000, 20, SYSDATE);

INSERT INTO employees_source VALUES (1, '张三', 5500, 10);  -- 更新
INSERT INTO employees_source VALUES (3, '王五', 7000, 30);  -- 插入

-- 结果：ID=1的记录被更新，ID=3的记录被插入
```

##### 使用子查询作为源表

##### 包含`DELETE`子句的MERGE

**注意**：`DELETE`只能与`UPDATE`一起使用，且删除的是目标表中已匹配的记录

##### 使用`DUAL`表进行单行操作

#### 4、注意事项

- MERGE操作会触发目标表上的UPDATE和INSERT触发器
- 必须满足目标表的所有约束条件
- 主键冲突会导致操作失败

## 五、物化视图

#### 1、概念

**核心特点**：

#### 2、物化视图 vs 普通视图

#### 3、主要作用

**性能提升**

- 预先计算复杂聚合、连接操作
- 减少大型表查询的响应时间

**数据仓库应用**

- 支持星型 schema 优化
- 实现汇总表的自动维护

**分布式环境**

- 在分布式数据库中维护数据副本
- 减少网络传输开销

**报表系统**

- 为报表提供预计算数据
- 支持离线数据分析

#### 4、创建物化视图

##### 语法

##### 定时刷新

**时间单位示例**

- `SYSDATE + 1`：1天后
- `SYSDATE + 1/24`：1小时后
- `SYSDATE + 1/1440`：1分钟后
- `NEXT_DAY(TRUNC(SYSDATE), 'MONDAY')`：下周一

##### STORGAE参数

##### LOGGING / NOLOGGING

```sql
CREATE MATERIALIZED VIEW mv_nolog
REFRESH COMPLETE ON DEMAND
NOLOGGING  -- 不生成重做日志，提高性能
AS SELECT * FROM temp_data;
```

##### CACHE / NOCACHE

```sql
CREATE MATERIALIZED VIEW mv_cached
REFRESH COMPLETE ON DEMAND
CACHE  -- 数据缓存在内存缓冲区
AS SELECT * FROM frequently_queried_data;
```

##### 示例

**创建基本物化视图**

**包含分区的物化视图**

#### 5、刷新物化视图

##### 手动刷新

```sql
-- 完全刷新（重新执行查询）
EXEC DBMS_MVIEW.REFRESH('mv_dept_sales', 'C');

-- 快速刷新（只更新变化数据）
EXEC DBMS_MVIEW.REFRESH('mv_employee_stats', 'F');

-- 强制刷新（尝试快速，失败则完全）
EXEC DBMS_MVIEW.REFRESH('mv_dept_sales', 'A');

-- 刷新多个物化视图
EXEC DBMS_MVIEW.REFRESH('mv1,mv2,mv3', 'C');
```

##### 自动刷新配置

#### 6、管理物化视图

##### 查看物化视图信息

```sql
-- 查看所有物化视图
SELECT mview_name, refresh_mode, refresh_method, last_refresh_date
FROM user_mviews;

-- 查看物化视图依赖的基表
SELECT * FROM user_mview_dependencies
WHERE mview_name = 'MV_DEPT_SALES';

-- 查看物化视图日志（快速刷新需要）
SELECT * FROM user_mview_logs;
```

##### 修改物化视图

```sql
-- 修改刷新方式
ALTER MATERIALIZED VIEW mv_dept_sales
REFRESH FAST ON DEMAND;

-- 启用/禁用查询重写
ALTER MATERIALIZED VIEW mv_employee_stats
ENABLE QUERY REWRITE;

ALTER MATERIALIZED VIEW mv_employee_stats
DISABLE QUERY REWRITE;
```

##### 删除物化视图

```sql
DROP MATERIALIZED VIEW mv_dept_sales;
```

## 六、同义词

#### 1、概念

**核心特点**：

#### 2、类型

##### 私有同义词

```sql
-- 在当前用户模式下创建，仅当前用户可用
CREATE SYNONYM emp FOR hr.employees;
```

##### 公有同义词

```sql
-- 在公共模式下创建，所有用户都可访问
CREATE PUBLIC SYNONYM employees FOR hr.employees;
```

#### 3、主要作用

**简化对象引用**

```sql
-- 原始：需要完整模式名
SELECT * FROM hr.employees@remote_db;

-- 使用同义词：简化访问
SELECT * FROM emp;
```

**位置透明性**

```sql
-- 同义词可以指向本地或远程对象
CREATE SYNONYM remote_emp FOR hr.employees@remote_db;
```

**安全增强**

```sql
-- 用户通过同义词访问，不直接接触底层对象
GRANT SELECT ON emp_synonym TO user1;
```

**应用程序可移植性**

```sql
-- 应用程序使用同义词，底层对象变更不影响代码
CREATE SYNONYM app_data FOR prod_schema.data_table;
```

#### 4、创建同义词

##### 语法

```sql
CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym_name 
FOR [schema.]object_name [@dblink];
```

##### 示例

```sql
-- 为当前用户的表创建同义词
CREATE SYNONYM emp FOR employees;

-- 为其他用户的表创建同义词
CREATE SYNONYM hr_emp FOR hr.employees;

-- 为视图创建同义词
CREATE SYNONYM emp_view FOR hr.v_employee_details;

-- 为序列创建同义词
CREATE SYNONYM emp_seq FOR hr.employee_id_seq;

-- 为存储过程创建同义词
CREATE SYNONYM calc_salary FOR hr.calculate_salary;

-- 为远程数据库对象创建同义词
CREATE SYNONYM remote_sales FOR sales.sales_data@remote_db;

-- 公有同义词（所有用户可访问）
CREATE PUBLIC SYNONYM all_emps FOR hr.employees;

-- 公有同义词指向远程对象
CREATE PUBLIC SYNONYM company_data FOR corp.hr_data@headquarters;

-- 为常用函数创建公有同义词
CREATE PUBLIC SYNONYM current_date FOR sysdate;

-- 如果同义词已存在则替换
CREATE OR REPLACE SYNONYM emp FOR hr.employees_new;
CREATE OR REPLACE PUBLIC SYNONYM app_config FOR config.system_settings;
```

#### 5、查看同义词信息

##### 查看所有可访问的同义词

##### 查看同义词依赖关系

#### 6、使用同义词

##### 在SQL语句中使用

```sql
-- DQL：数据查询
SELECT * FROM emp;  -- 代替 SELECT * FROM hr.employees;

-- DML：数据操作
INSERT INTO emp (emp_id, emp_name) VALUES (1001, '张三');
UPDATE emp SET salary = salary * 1.1 WHERE dept_id = 10;
DELETE FROM emp WHERE emp_id = 1001;

-- DDL：需要直接对象名，不能使用同义词
-- CREATE INDEX idx_emp ON emp(emp_id);  -- 错误！
CREATE INDEX idx_emp ON hr.employees(emp_id);  -- 正确
```

##### 在PL/SQL中使用

##### 序列同义词使用

```sql
-- 创建序列同义词
CREATE SYNONYM emp_id_seq FOR hr.employee_id_seq;

-- 使用序列同义词
INSERT INTO emp (emp_id, emp_name) 
VALUES (emp_id_seq.NEXTVAL, '李四');

SELECT emp_id_seq.CURRVAL FROM DUAL;
```

#### 7、修改同义词

##### 使用 OR REPLACE 修改

```sql
-- 修改同义词指向新对象
CREATE OR REPLACE SYNONYM emp FOR hr.employees_new;

-- 修改为指向远程对象
CREATE OR REPLACE SYNONYM emp FOR hr.employees@remote_db;

-- 修改公有同义词
CREATE OR REPLACE PUBLIC SYNONYM app_users FOR new_schema.user_table;

#### 8、删除同义词

##### 普通删除

```sql
-- 删除当前用户的同义词
DROP SYNONYM emp;

-- 删除其他用户的同义词（需要权限）
DROP SYNONYM hr.emp_synonym;

-- 删除公有同义词
DROP PUBLIC SYNONYM all_employees;

-- 如果同义词不存在时避免错误
DROP PUBLIC SYNONYM IF EXISTS old_synonym;
```

##### 批量删除

#### 9、同义词权限管理

##### 授予同义词权限

```sql
-- 授予同义词的SELECT权限
GRANT SELECT ON emp TO user1, user2;

-- 授予同义词的DML权限
GRANT INSERT, UPDATE, DELETE ON emp TO user1;

-- 授予所有权限
GRANT ALL ON emp TO user1;
```

##### 查看同义词权限

```sql
-- 查看同义词上的权限
SELECT grantee, privilege, grantable
FROM user_tab_privs
WHERE table_name = 'EMP';
```

##### 撤销同义词权限

```sql
-- 撤销SELECT权限
REVOKE SELECT ON emp FROM user1;

-- 撤销所有权限
REVOKE ALL ON emp FROM user1;
```

## 七、DBLink

创建 DBLink 需具备 `CREATE DATABASE LINK` 权限；操作远程表时需在目标库有对应权限。

#### 1、概念

#### 2、创建DBLink

##### 语法

```sql
CREATE [SHARED] [PUBLIC] DATABASE LINK 连接名
  CONNECT TO 连接账户 IDENTIFIED BY 连接密码
  USING '网络地址:端口/方式';
```

**可选参数说明**：

##### 示例

创建一个名为 `remote_db` 的私有链接，连接到 IP 为 `192.168.1.100` 的数据库。

```sql
CREATE DATABASE LINK remote_db
  CONNECT TO scott IDENTIFIED BY tiger
  USING '192.168.1.100:1521/ORCL';
```

#### 3、使用DBLink

查询远程数据库中的 `employees` 表

```sql
SELECT * FROM employees@remote_db;
```

向远程表的 `departments` 插入数据

```sql
INSERT INTO departments@remote_db (dept_id, dept_name) 
VALUES (101, 'HR');
```

#### 4、删除DBLink

删除名为 `remote_db` 的链接

```sql
DROP DATABASE LINK remote_db;
```

#### 5、查看已创建的 DBLink

查询当前用户下的所有 DBLink

```sql
SELECT db_link, username, host FROM user_db_links;
```

#### 6、修改`tnsnames.ora`配置创建DBLink

##### 文件位置

- Linux/Unix: `$ORACLE_HOME/network/admin/tnsnames.ora`
- Windows: `%ORACLE_HOME%\network\admin\tnsnames.ora`

##### 示例

**参数说明**：

## 八、序列

#### 1、概念

**主要作用**

- 为主键字段自动生成唯一值
- 实现自增字段功能
- 生成订单号、流水号等业务编号
- 避免主键冲突，提高数据完整性

#### 2、创建序列

##### 语法

**参数注释**：

##### 示例

**创建带缓存的循环序列**

```sql
CREATE SEQUENCE seq_order_no
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 9999
  CACHE 50
  CYCLE;
```

#### 3、使用序列

##### 获取下一个值（递增）

```sql
SELECT seq_employee_id.NEXTVAL FROM dual;
-- 结果：1000（首次调用）
```

##### 获取当前值（不递增）

```sql
SELECT seq_employee_id.CURRVAL FROM dual;
-- 结果：1000（需先调用NEXTVAL）
```

##### 插入数据时使用

```sql
INSERT INTO employees (emp_id, emp_name, hire_date)
VALUES (seq_employee_id.NEXTVAL, '张三', SYSDATE);
```

##### 创建表时设置默认值

```sql
CREATE TABLE orders (
  order_id NUMBER DEFAULT seq_order_no.NEXTVAL,
  order_date DATE,
  amount NUMBER
);
```

#### 4、修改序列

##### 修改序列的增量值

```sql
ALTER SEQUENCE seq_employee_id
  INCREMENT BY 2
  MAXVALUE 10000;
```

#### 5、删除序列

```sql
DROP SEQUENCE seq_employee_id;
```

#### 6、查看序列信息

```sql
-- 查看用户拥有的序列
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;

-- 查看序列的详细定义
SELECT * FROM user_sequences WHERE sequence_name = 'SEQ_EMPLOYEE_ID';
```

#### 7、注意事项

## 九、数据块

#### 1、概念

**数据块**是 Oracle 数据库中最小的存储单位，也是 I/O 操作的基本单元。它是物理存储层面上的概念，对应操作系统中的数据块，但大小可以不同。

**主要作用**：

- 数据库存储的基本单位
- I/O 操作的最小单元
- 管理空间分配和回收
- 提高数据访问效率

#### 2、物理结构

#### 3、查看数据块信息

```sql
-- 查看数据库标准块大小
SELECT name, value FROM v$parameter WHERE name = 'db_block_size';

-- 查看表空间使用的块大小
SELECT tablespace_name, block_size FROM dba_tablespaces;

-- 查看表中实际使用的数据块数量
SELECT table_name, blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name = 'EMPLOYEES';
```

#### 4、创建不同块大小的表空间

```

最后修改：2025 年 12 月 04 日

👆请简单粗暴地爱我。

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Spring框架依赖注入

MySQL知识扩展

Day12 - JDBC驱动安装及其简单使用方法

Java常用注解表

Electron的简介

Oracle异常捕获与动态SQL

[工具类] Collections类常用的方法

Vue3可复用解决方案

MyBatis框架基础

可视化Linux操作：宝塔面板

Oracle进阶操作

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Oracle进阶操作

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款