Oracle常用命令维护总结

Oracle 数据库与 MySQL 在架构、SQL 语法和运维命令上有显著区别。Oracle 更强调实例与数据库的分离、表空间管理以及PL/SQL 的强大功能。

以下是 Oracle 数据库从安装配置、用户管理、日常维护到核心 SQL 操作的全流程指南。

第一阶段：环境准备与连接 (Oracle 特色)#

Oracle 不像 MySQL 那样直接 mysql -u root -p。你需要先配置环境变量，并理解 SID (实例名) 或 Service Name (服务名)。

1. 切换用户与配置环境 (Linux)#

Oracle 通常安装在 oracle 用户下。

# 切换到 oracle 用户
su - oracle

# 配置环境变量 (如果未自动加载)
# 设置 SID (实例名，例如 ORCL)
export ORACLE_SID=ORCL
# 或者设置服务名路径 (通常由 netca 配置好)
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

2. 登录数据库#

# 方式 A: 操作系统认证 (免密，用于本地运维)
sqlplus / as sysdba

# 方式 B: 账号密码登录
sqlplus username/password@localhost:1521/ORCL

# 方式 C: 静默登录 (用于脚本)
sqlplus -S username/password@ORCL

注：as sysdba 是最高权限，类似 MySQL 的 root，但权限更大，可启动/关闭实例。

第二阶段：用户、表空间与权限 (DBA 核心)#

Oracle 没有“Database”的概念（实例启动后就有一个库），逻辑隔离靠 Schema (用户) 和 Tablespace (表空间)。

1. 创建表空间 (类似 MySQL 的 Database 存储层)#

-- 创建数据文件 (大小自动扩展)
CREATE TABLESPACE my_ts 
DATAFILE '/u01/app/oracle/oradata/ORCL/my_ts01.dbf' 
SIZE 100M 
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

2. 创建用户并绑定表空间#

-- 创建用户，指定默认表空间和临时表空间
CREATE USER my_user IDENTIFIED BY "StrongPassword123"
DEFAULT TABLESPACE my_ts
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON my_ts; -- 赋予该表空间无限使用权

3. 授权 (权限体系比 MySQL 更细)#

-- 赋予基础连接和资源权限 (最常用)
GRANT CONNECT, RESOURCE TO my_user;

-- 赋予 DBA 权限 (慎用，相当于超级管理员)
GRANT DBA TO my_user;

-- 赋予特定表权限
GRANT SELECT, INSERT, UPDATE ON scott.emp TO my_user;

-- 赋予执行存储过程权限
GRANT EXECUTE ON some_package TO my_user;

-- 回收权限
REVOKE DELETE ON scott.emp FROM my_user;

4. 修改密码#

-- 修改自己密码
ALTER USER my_user IDENTIFIED BY "NewPassword123";

-- 管理员修改他人密码
ALTER USER my_user IDENTIFIED BY "NewPassword123";

5. 查看用户与表空间#

-- 查看所有用户
SELECT username, default_tablespace, created FROM dba_users;

-- 查看表空间使用情况 (高频监控)
SELECT 
    t.tablespace_name,
    ROUND(SUM(d.bytes) / 1024 / 1024, 2) AS total_mb,
    ROUND(SUM(NVL(f.bytes, 0)) / 1024 / 1024, 2) AS free_mb,
    ROUND((SUM(d.bytes) - SUM(NVL(f.bytes, 0))) / 1024 / 1024, 2) AS used_mb,
    ROUND((1 - SUM(NVL(f.bytes, 0)) / SUM(d.bytes)) * 100, 2) AS used_percent
FROM dba_tablespaces t
JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
LEFT JOIN dba_free_space f ON t.tablespace_name = f.tablespace_name
GROUP BY t.tablespace_name;

第三阶段：核心 SQL 操作 (DML & DDL)#

Oracle SQL 符合 ANSI 标准，但在分页、函数和自增列上有独特语法。

1. 表操作 (DDL)#

-- 创建表 (Oracle 没有 AUTO_INCREMENT，需用 Sequence 或 Identity 12c+)
CREATE TABLE employees (
    emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 12c+ 自增写法
    emp_name VARCHAR2(50) NOT NULL, -- 注意是 VARCHAR2 不是 VARCHAR
    salary NUMBER(10, 2),
    hire_date DATE DEFAULT SYSDATE, -- 默认当前时间
    is_active CHAR(1) DEFAULT 'Y'
) TABLESPACE my_ts;

-- 查看表结构
DESC employees; 
-- 或查看完整建表语句
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'MY_USER') FROM DUAL;

-- 添加列
ALTER TABLE employees ADD (phone VARCHAR2(20));

-- 修改列类型
ALTER TABLE employees MODIFY (phone VARCHAR2(30));

-- 重命名表
RENAME employees TO staff;

-- 截断表 (DDL 操作，不可回滚，速度快)
TRUNCATE TABLE staff;

2. 增删改 (DML)#

注意：Oracle 默认开启事务，修改后必须 COMMIT 才会生效！

-- 增
INSERT INTO employees (emp_name, salary) VALUES ('ZhangSan', 5000.50);
INSERT INTO employees (emp_name, salary) SELECT emp_name, salary FROM old_employees WHERE status = 1;

-- 改
UPDATE employees SET salary = salary * 1.1 WHERE emp_name = 'ZhangSan';
-- ⚠️ 记得提交
COMMIT;

-- 删
DELETE FROM employees WHERE emp_id = 101;
COMMIT;

-- Oracle 特有：MERGE INTO (存在则更新，不存在则插入)
MERGE INTO employees e
USING (SELECT 102 AS id, 'LiSi' AS name FROM DUAL) src
ON (e.emp_id = src.id)
WHEN MATCHED THEN
  UPDATE SET e.emp_name = src.name
WHEN NOT MATCHED THEN
  INSERT (emp_id, emp_name) VALUES (src.id, src.name);

3. 查 (Select) - 重点差异#

A. 分页 (Oracle 12c 之前 vs 之后) Oracle 没有 LIMIT 关键字。

老版本 (11g 及以前) - 使用 ROWNUM 嵌套:

SELECT * FROM (
    SELECT t.*, ROWNUM rn 
    FROM (
        SELECT * FROM employees ORDER BY hire_date DESC
    ) t
    WHERE ROWNUM <= 20  -- 取前 20 条
)
WHERE rn > 10; -- 排除前 10 条 (即第 11-20 条)

新版本 (12c+) - 支持 ANSI 标准:

SELECT * FROM employees 
ORDER BY hire_date DESC 
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

B. 常用函数差异

功能	MySQL	Oracle
当前时间	`NOW()`, `SYSDATE()`	`SYSDATE` (含时分秒), `SYSTIMESTAMP`
空值处理	`IFNULL(a, b)`	`NVL(a, b)`
字符串拼接	`CONCAT(a, b)` 或 `a
子串	`SUBSTR(str, start, len)`	`SUBSTR(str, start, len)` (一致)
日期格式化	`DATE_FORMAT(dt, '%Y-%m-%d')`	`TO_CHAR(dt, 'YYYY-MM-DD')`
字符串转日期	`STR_TO_DATE(str, ...)`	`TO_DATE(str, 'YYYY-MM-DD')`
自增列	`AUTO_INCREMENT`	`SEQUENCE` 或 `IDENTITY`
伪表	无	`DUAL` (必须用 SELECT … FROM DUAL)

C. 典型查询示例

-- 1. 基础查询 (必须 FROM DUAL 如果没有表)
SELECT SYSDATE, USER FROM DUAL;

-- 2. 空值处理
SELECT emp_name, NVL(salary, 0) AS real_salary FROM employees;

-- 3. 日期格式化
SELECT emp_name, TO_CHAR(hire_date, 'YYYY-MM-DD HH24:MI:SS') AS hire_time FROM employees;

-- 4. 分组统计
SELECT department_id, COUNT(*) as cnt, AVG(salary) as avg_sal 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 5;

-- 5. 层次查询 (Oracle 神器：递归查询树形结构)
-- 假设 emp 表有 mgr (上级 ID) 字段
SELECT LEVEL, emp_name, mgr 
FROM employees 
START WITH emp_id = 100 -- 根节点
CONNECT BY PRIOR emp_id = mgr; -- 连接条件

第四阶段：日常维护与监控 (DBA 必备)#

1. 会话与锁监控 (排障核心)#

-- 查看当前所有活跃会话
SELECT sid, serial#, username, status, program, logon_time 
FROM v$session 
WHERE status = 'ACTIVE' AND username IS NOT NULL;

-- 查看锁等待情况 (谁锁住了谁)
SELECT 
    blocking_sess.sid AS blocker_sid,
    waiting_sess.sid AS waiter_sid,
    waiting_sess.serial#,
    waiting_sess.username,
    waiting_sess.event
FROM v$session waiting_sess
JOIN v$session blocking_sess 
  ON waiting_sess.blocking_session = blocking_sess.sid
WHERE waiting_sess.blocking_session IS NOT NULL;

-- 杀掉会话 (类似 MySQL KILL)
-- 格式：ALTER SYSTEM KILL SESSION 'sid,serial#';
ALTER SYSTEM KILL SESSION '123,4567'; 

-- 如果杀不掉 (状态 KILLED)，需在 OS 层杀进程
SELECT spid FROM v$process WHERE addr = (SELECT paddr FROM v$session WHERE sid = 123);
-- 然后在 Linux 执行: kill -9 <spid>

2. 性能监控#

-- 查看最耗资源的 SQL (Top SQL)
SELECT sql_id, executions, elapsed_time/1000000 as elapsed_sec, sql_text 
FROM v$sql 
ORDER BY elapsed_time DESC 
FETCH FIRST 10 ROWS ONLY;

-- 查看执行计划 (解释 SQL 怎么走索引)
EXPLAIN PLAN FOR SELECT * FROM employees WHERE emp_id = 100;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 查看表空间剩余空间 (简化版)
SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb 
FROM dba_free_space 
GROUP BY tablespace_name;

3. 监听与服务管理 (Linux 命令行)#

这些命令在 sqlplus 之外运行：

# 查看监听状态
lsnrctl status

# 启动/重启/停止监听
lsnrctl start
lsnrctl stop
lsnrctl reload -- 重载配置不中断连接

# 启动/关闭数据库实例 (需在 sqlplus / as sysdba 中)
-- 启动到 OPEN 状态
STARTUP; 
-- 仅启动到 MOUNT (用于恢复)
STARTUP MOUNT;
-- 强制关闭 (类似 kill -9)
SHUTDOWN ABORT; 
-- 正常关闭 (等待事务结束)
SHUTDOWN IMMEDIATE;

4. 备份与恢复 (RMAN)#

Oracle 推荐使用 RMAN (Recovery Manager) 进行物理备份，比 expdp 更可靠。

# 进入 RMAN
rman target /

# 全库备份
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

# 备份特定表空间
RMAN> BACKUP TABLESPACE my_ts;

# 查看备份集
RMAN> LIST BACKUP;

# 恢复数据库 (示例)
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

逻辑备份 (Expdp/Impdp):

# 导出用户
expdp system/password DIRECTORY=dp_dir DUMPFILE=my_user.dmp SCHEMAS=my_user

# 导入用户
impdp system/password DIRECTORY=dp_dir DUMPFILE=my_user.dmp REMAP_SCHEMA=my_user:new_user

💡 Oracle vs MySQL 核心差异速查#

特性	MySQL	Oracle
事务提交	默认自动提交 (`autocommit=1`)	默认不自动提交，必须手动 `COMMIT`
自增列	`AUTO_INCREMENT`	`SEQUENCE` 或 `IDENTITY` (12c+)
分页	`LIMIT offset, count`	`ROWNUM` 嵌套或 `OFFSET ... FETCH` (12c+)
空值排序	`NULL` 视为最小 (排在前面)	`NULL` 视为最大 (排在后面，除非指定 `NULLS FIRST`)
引号	字符串用单/双引号，表名用反引号 `	字符串必须单引号 `'`，表名/列名大小写敏感时用双引号 `"`
伪表	无	查询常量必须 `FROM DUAL`
存储过程	相对简单	PL/SQL 功能极其强大，是企业级核心
并发控制	锁机制相对简单	多版本并发控制 (MVCC) 非常成熟，锁粒度细

⚠️ 新手避坑指南#

忘记 Commit：在 SQLPlus 或 Java/JDBC (默认自动提交除外) 中执行 UPDATE/DELETE/INSERT 后，如果不 COMMIT，其他会话看不到数据，且重启后会回滚。
大小写敏感：Oracle 默认将未加引号的标识符转为大写。如果建表时用了双引号 "MyTable"，查询时也必须用 "MyTable"，否则报错“表不存在”。建议全程大写或不使用双引号。
空字符串与 NULL：在 Oracle 中，空字符串 '' 等同于 NULL。这与 MySQL 不同 (MySQL 中空字符串是值)。
日期类型：Oracle 的 DATE 类型包含时分秒，没有单独的 DATETIME。
序列 (Sequence)：在 12c 之前，主键自增需要手动创建 Sequence 并在 Insert 时调用 seq_name.NEXTVAL。

掌握以上命令和差异，你就能从容应对 Oracle 数据库的开发与基础运维工作。