Oracle 数据库常用命令速查手册

约 3066 字大约 10 分钟

oracle数据库

2020-05-06

本文整理了 Oracle 数据库的常用操作命令，涵盖 DDL、DML、DQL、DCL 以及存储过程、函数、定时任务等高级功能，适合作为日常参考手册使用。

背景与适用场景

Oracle 数据库是企业级应用中最常用的关系型数据库之一。本文按 SQL 类型分类整理常用命令，帮助开发者快速查阅语法，解决日常开发中的常见问题。

适用读者：有一定 SQL 基础的开发者或 DBA。

卸载与清理

在 Windows 环境下彻底卸载 Oracle，需按以下步骤操作：

停止 Oracle 所有相关服务。
打开 Oracle Universal Installer，在卸载产品页面展开目录，删除除 OraDb11g_home1 外的所有目录。
删除注册表信息：

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services 下的所有 ORA 开头的键
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application 下的所有 ORA 开头的键
HKEY_CLASSES_ROOT 下所有 ORA、ORCL 等相关的键
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\MenuOrder\Start Menu\Programs 下 ORA 开头的键
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI 中除 Microsoft ODBC for Oracle 外的所有含 Oracle 的键

删除环境变量中有关 Oracle 的设定。
删除所有与 Oracle 相关的目录（若删不掉，重启计算机后再删）：

C:\Program file\Oracle 目录
ORACLE_BASE 目录（Oracle 安装目录）
C:\WINDOWS\system32\config\systemprofile\Oracle 目录
C:\Users\Administrator\Oracle 或 C:\Documents and Settings\Administrator\Oracle 目录
C:\WINDOWS 下的 ORACLE.INI、oradim73.INI、oradim80.INI、oraodbc.ini 等文件
C:\WINDOWS\WIN.INI 文件中 [ORACLE] 段

DDL：数据定义语言

表空间管理

创建临时表空间：

CREATE TEMPORARY TABLESPACE tablespace_name
TEMPFILE 'C:\path\to\temp01.dbf' SIZE 50M
AUTOEXTEND ON NEXT 50M MAXSIZE 20480M
EXTENT MANAGEMENT LOCAL;

创建数据表空间：

CREATE TABLESPACE tablespace_name
DATAFILE 'C:\path\to\data01.dbf' SIZE 50M
AUTOEXTEND ON NEXT 50M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

其他操作：

-- 删除表空间（包含内容和数据文件）
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

-- 重命名表空间
ALTER TABLESPACE tablespace_name1 RENAME TO tablespace_name2;

用户管理

创建用户并指定表空间：

CREATE USER user_name IDENTIFIED BY password
DEFAULT TABLESPACE JC_DATA
TEMPORARY TABLESPACE JC_TEMP;

创建与 SYSTEM 相同表空间的用户：

CREATE USER bqft IDENTIFIED BY crmoracle
DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;

其他操作：

-- 删除用户（cascade 级联删除所有对象）
DROP USER user_name [CASCADE];

-- 解锁用户
ALTER USER user_name ACCOUNT UNLOCK;

-- 修改密码
ALTER USER user_name IDENTIFIED BY new_password;

-- 修改默认表空间
ALTER USER user_name DEFAULT TABLESPACE tablespace_name;

-- 设置表空间配额
ALTER USER user_name QUOTA UNLIMITED ON tablespace_name;

角色管理

-- 创建角色
CREATE ROLE role_name;

-- 删除角色
DROP ROLE role_name;

表管理

创建表：

CREATE TABLE table_name (
  id INT PRIMARY KEY NOT NULL,
  name VARCHAR(32)
);

修改表结构：

-- 重命名表
ALTER TABLE table_old RENAME TO table_new;

-- 添加字段
ALTER TABLE table_name ADD column_name column_type;

-- 修改字段名
ALTER TABLE table_name RENAME COLUMN column_name TO column_name_new;

-- 修改字段类型
ALTER TABLE table_name MODIFY column_name column_type;

-- 删除字段
ALTER TABLE table_name DROP COLUMN column_name;

-- 删除表
DROP TABLE table_name;

添加注释：

-- 为表添加注释
COMMENT ON TABLE table_name IS '表注释说明';

-- 为字段添加注释
COMMENT ON COLUMN table_name.column_name IS '字段注释说明';

查看表结构：

-- 查询表结构
DESC table_name;

-- 查询用户下的所有表
SELECT * FROM user_tables;

-- 查询指定用户的表
SELECT * FROM dba_tables WHERE owner = 'owner_name';

约束管理

外键约束（内部写法）：

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(id)
);

外键约束（外部写法）：

ALTER TABLE table_name1
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name1)
REFERENCES table_name2(column_name2);

添加主键约束：

ALTER TABLE ECC_FND.RESOURCE_TYPE_CFG
ADD CONSTRAINT P_RESOURCE_TYPE_CFG PRIMARY KEY (
  RESOURCE_CODE,
  RSRC_LOOKUP_TYPE,
  RSRC_LOOKUP_CODE,
  REGISTER_ID
)
USING INDEX TABLESPACE ECC_FND
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
  INITIAL 448K
  NEXT 128K
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0
);

删除约束：

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

同义词

-- 创建同义词
CREATE SYNONYM T_YCPORDER_FLOW FOR TELECOM.T_YCPORDER_FLOW;

视图

创建普通视图：

CREATE [OR REPLACE] VIEW view_name (a, b, c) AS
SELECT ...;

创建物化视图：

CREATE MATERIALIZED VIEW mv_name
REFRESH FORCE ON DEMAND
START WITH SYSDATE
NEXT TO_DATE(
  CONCAT(TO_CHAR(SYSDATE + 1, 'yyyy-mm-dd'), '10:25:00'),
  'yyyy-mm-dd hh24:mi:ss'
)
AS SELECT * FROM table_name;

删除视图：

DROP VIEW view_name;

序列

创建序列：

CREATE SEQUENCE sequence_name
MINVALUE 1
MAXVALUE 9999999
INCREMENT BY 1
START WITH 1
NOCACHE NOORDER NOCYCLE;

使用和修改序列：

-- 获取下一个值
SELECT sequence_name.nextval FROM dual;

-- 修改增量
ALTER SEQUENCE sequence_name INCREMENT BY 80;

-- 再次获取值
SELECT sequence_name.nextval FROM dual;

-- 还原增量
ALTER SEQUENCE sequence_name INCREMENT BY 1;

-- 删除序列
DROP SEQUENCE sequence_name;

索引

索引的用途：

提高表的 SELECT 速度
降低 INSERT、UPDATE、DELETE 速度
对有关联的字段取值进行检查
索引字段可以为空

创建索引：

CREATE INDEX IDX_TZMS_CD ON T_ZNFX_MRZJ_SNAP(CREATE_DATE);

修改索引：

ALTER [UNIQUE] INDEX index_name
[INITRANS n] [MAXTRANS n]
REBUILD [STORAGE n];

删除索引：

DROP INDEX index_name;

目录对象

CREATE DIRECTORY directory_name AS 'file_path';

DML：数据操作语言

基本操作

-- 插入数据
INSERT INTO table_name (col1, col2, col3) VALUES (val1, val2, val3);

-- 更新数据
UPDATE table_name SET column = 'value' WHERE condition;

-- 删除数据
DELETE FROM table_name WHERE condition;

-- 清空表（保留结构）
TRUNCATE TABLE table_name;

MERGE 语句

MERGE 语句用于根据条件执行插入或更新操作：

MERGE INTO table_name t1
USING table_name t2 ON (t1.id = t2.id)
WHEN MATCHED THEN
  UPDATE SET
    t1.column1 = t2.column1,
    t1.column2 = t2.column2
WHEN NOT MATCHED THEN
  INSERT (t1.column1, t1.column2)
  VALUES (t2.column1, t2.column2);

DQL：数据查询语言

基本查询

-- 基础查询
SELECT * FROM table_name;

-- 去重
SELECT DISTINCT column_name FROM table_name;

-- 条件过滤
SELECT column_name FROM table_name
WHERE column = '' AND column2 = '';

-- 排序（DESC 降序）
SELECT column_name FROM table_name
ORDER BY column_name [DESC];

-- 限制返回行数
SELECT column_name FROM table_name
WHERE rownum <= 10;

模糊查询

-- 通配符说明：% 任意多个字符，_ 单个字符
SELECT * FROM table_name
WHERE column_name LIKE 'a%';

递归查询

Oracle 使用 CONNECT BY 实现递归查询：

SELECT t.subid, t.parentid
FROM table_name t
START WITH t.subid = '1'
CONNECT BY PRIOR t.subid = t.parentid;

多表关联

-- 使用别名
SELECT t1.column, t2.column
FROM table_name1 t1
JOIN table_name2 t2 ON t1.id = t2.id;

-- 支持的 JOIN 类型：LEFT JOIN | RIGHT JOIN | FULL JOIN | INNER JOIN

合并结果集

SELECT * FROM table_name1 WHERE condition
UNION ALL
SELECT * FROM table_name2 WHERE condition;

查询约束

SELECT TABLE_NAME
FROM all_constraints
WHERE CONSTRAINT_NAME = 'Reference_460';

元数据查询

查询用户下的索引：

SELECT * FROM user_indexes;

SELECT * FROM dba_indexes WHERE owner = 'owner_name';

查询用户下的视图：

SELECT * FROM user_views;

SELECT * FROM dba_views WHERE owner = 'owner_name';

查询用户下的约束：

SELECT * FROM user_constraints;

DCL：数据控制语言

常用权限列表：

权限	说明
`CREATE SESSION`	登录权限
`UNLIMITED TABLESPACE`	使用表空间
`CREATE TABLE`	创建表
`DROP TABLE`	删除表
`INSERT TABLE`	插入数据
`UPDATE TABLE`	更新数据
`ALL`	所有权限

权限授予与撤销：

-- 授予角色给用户
GRANT role_name TO user_name;

-- 授予权限给用户或角色
GRANT create session TO user_name;

-- 授予特定表的查询权限
GRANT select ON table_name TO user_name;

-- 撤销权限
REVOKE create session FROM user_name;

存储过程

基本输出

-- 启用输出打印
SET SERVEROUTPUT ON;

-- 输出语句
DBMS_OUTPUT.PUT_LINE('Hello World');

创建存储过程

无参数存储过程：

CREATE OR REPLACE PROCEDURE test_procedure IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('测试存储过程');
END test_procedure;

带参数的存储过程：

CREATE OR REPLACE PROCEDURE procedure_name (
  param1 IN type,
  param2 OUT type
) AS
  vs_msg VARCHAR2(512);
  vs_ym_begin CHAR(6);
BEGIN
  -- 存储过程逻辑
END procedure_name;

条件判断

CREATE OR REPLACE PROCEDURE test(x IN NUMBER) IS
BEGIN
  IF x > 0 THEN
    x := 0 - x;
  END IF;

  IF x = 0 THEN
    x := 1;
  END IF;
END test;

循环结构

FOR 循环遍历游标：

CREATE OR REPLACE PROCEDURE test AS
  CURSOR cursor IS SELECT name FROM student;
BEGIN
  FOR name IN cursor LOOP
    DBMS_OUTPUT.PUT_LINE(name);
  END LOOP;
END test;

FOR 循环遍历数组：

CREATE OR REPLACE PROCEDURE test(varArray IN myPackage.TestArray) AS
BEGIN
  FOR i IN 1..varArray.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('The No.' || i || ' record: ' || varArray(i));
  END LOOP;
END test;

WHILE 循环：

CREATE OR REPLACE PROCEDURE test(i IN NUMBER) AS
BEGIN
  WHILE i < 0 LOOP
    i := i + 1;
  END LOOP;
END test;

数组类型

Oracle 自带数组类型：

CREATE OR REPLACE PROCEDURE test(y OUT array) IS
  x array;
BEGIN
  x := NEW array();
  y := x;
END test;

自定义数组类型：

CREATE OR REPLACE PACKAGE myPackage IS
  TYPE info IS RECORD (
    name VARCHAR(20),
    y NUMBER
  );

  TYPE TestArray IS TABLE OF info INDEX BY BINARY_INTEGER;
END myPackage;

使用数组：

DECLARE
  TYPE V_ARRY_TYPE IS VARRAY(2) OF VARCHAR2(10);
  V_ARRY_NAME V_ARRY_TYPE;
BEGIN
  V_ARRY_NAME := V_ARRY_TYPE('tom', 'jim', 'tim');
  DBMS_OUTPUT.PUT_LINE(V_ARRY_NAME(1));
  DBMS_OUTPUT.PUT_LINE(V_ARRY_NAME(2));
END;

游标操作

DECLARE
  -- 静态游标
  CURSOR c_test IS SELECT id, name FROM t_user t WHERE t.id = :id;
  c_t c_test%ROWTYPE;

  -- 动态游标
  TYPE my_cur_type IS REF CURSOR;
  my_cur my_cur_type;
  my_obj my_cur%ROWTYPE;
BEGIN
  -- FOR 循环方式
  FOR c_t IN c_test LOOP
    DBMS_OUTPUT.PUT_LINE(c_t.id || '-' || c_t.name);
  END LOOP;

  -- WHILE 循环方式
  OPEN c_test;
  FETCH c_test INTO c_t;
  WHILE c_test%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(c_t.id || '-' || c_t.name);
    FETCH c_test INTO c_t;
  END LOOP;
  CLOSE c_test;

  -- FETCH 循环方式
  OPEN c_test;
  LOOP
    FETCH c_test INTO c_t;
    EXIT WHEN c_test%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(c_t.id || '-' || c_t.name);
  END LOOP;
  CLOSE c_test;
END;

管理存储过程

-- 执行存储过程
EXECUTE test_procedure;

-- 删除存储过程
DROP PROCEDURE test_procedure;

-- 查询所有存储过程
SELECT name FROM user_source WHERE type = 'PROCEDURE';

-- 查询存储过程内容
SELECT text FROM user_source WHERE name = 'PROCEDURE_NAME';

常用函数

-- 四舍五入保留精度
ROUND(1234.3565, 2);

-- 截取
TRUNC(value);

-- 转字符串
TO_CHAR(value);

-- 去除空格
TRIM(string);

-- 存在性判断
EXISTS(subquery);
NOT EXISTS(subquery);

-- 条件解码（类似 CASE WHEN）
DECODE(value, 'a', 'b', 'c', value);

-- 窗口函数
OVER(ORDER BY salary);           -- 按薪资排序
OVER(PARTITION BY deptno);        -- 按部门分区

-- 分组编号
ROW_NUMBER() OVER(PARTITION BY fldphy ORDER BY flddate DESC) AS row_flg;

定时任务

查询定时任务

SELECT * FROM ALL_JOBS
WHERE LOWER(WHAT) LIKE '%PRO_USER_ACCESS%';

创建定时任务

DECLARE
  JOB_USER_ACCESS NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    JOB_USER_ACCESS,
    'PRO_USER_ACCESS;',
    SYSDATE + 14/24,
    'TRUNC(SYSDATE + 1)'
  );
  COMMIT;
END;

删除定时任务

BEGIN
  DBMS_JOB.REMOVE(696391);
  COMMIT;
END;

常见问题处理

DBLink 配置

在 tnsnames.ora 中配置数据库连接，然后查询：

SELECT * FROM ALL_DB_LINKS;
SELECT * FROM dba_db_links;

闪回查询

闪回查询可用于恢复误操作的数据。

更新/删除操作的闪回查询：

SELECT * FROM ecc_oc.order_header
AS OF TIMESTAMP TO_TIMESTAMP('2017-08-14 16:41:00', 'yyyy-mm-dd hh24:mi:ss')
MINUS
SELECT * FROM ecc_oc.order_header;

闪回恢复数据：

MERGE INTO tab a
USING (
  SELECT * FROM tab
  AS OF TIMESTAMP TO_TIMESTAMP('time_point', 'yyyy-mm-dd hh24:mi:ss')
  MINUS
  SELECT * FROM tab
) b
ON (a.unique_id = b.unique_id)
WHEN MATCHED THEN
  UPDATE SET a.col1 = b.col1, a.col2 = b.col2
WHEN NOT MATCHED THEN
  INSERT VALUES (b.unique_id, b.col1, b.col2);

用户锁死处理

查询锁定信息：

SELECT
  vs.USERNAME,
  vs.LOCKWAIT,
  vs.STATUS,
  vs.MACHINE,
  vs.PROGRAM
FROM v$session vs
WHERE vs.SID IN (
  SELECT session_id FROM v$locked_object
);

查询锁死语句：

SELECT vsql.SQL_TEXT
FROM v$sql vsql
WHERE vsql.HASH_VALUE IN (
  SELECT vsession.SQL_HASH_VALUE
  FROM v$session vsession
  WHERE vsession.SID IN (
    SELECT vl.SESSION_ID FROM v$locked_object vl
  )
);

解锁用户：

ALTER USER user_name ACCOUNT UNLOCK;

修改失败登录次数限制：

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 30;

表锁死处理

查看被锁的表：

SELECT b.owner, b.object_name, a.session_id, a.locked_mode
FROM v$locked_object a, dba_objects b
WHERE b.object_id = a.object_id;

查看造成锁表的用户或进程：

SELECT b.username, b.sid, b.serial#, logon_time
FROM v$locked_object a, v$session b
WHERE a.session_id = b.sid
ORDER BY b.logon_time;

杀死进程：

ALTER SYSTEM KILL SESSION '651,11757';

实用查询技巧

分组取每组第一条：

SELECT *
FROM (
  SELECT
    ROW_NUMBER() OVER(PARTITION BY account_no ORDER BY fldid DESC) rn,
    fi.*
  FROM t_fix_importaccount fi
  WHERE fi.account_no IS NOT NULL
)
WHERE rn = 1;

去除重复数据：

DELETE FROM t_fix_communication_gzltjinfo
WHERE (RID) IN (
  SELECT RID FROM t_fix_communication_gzltjinfo
  WHERE FLDYEAR = '2019' AND FLDMONTH = '01'
  GROUP BY RID HAVING COUNT(RID) > 1
)
AND ROWID NOT IN (
  SELECT MIN(ROWID) FROM t_fix_communication_gzltjinfo
  WHERE FLDYEAR = '2019' AND FLDMONTH = '01'
  GROUP BY RID HAVING COUNT(*) > 1
);

并行查询

使用并行提示加速查询：

SELECT /*+parallel(T,8)*/ * FROM large_table T;

常用自定义函数

MD5 加密函数：

CREATE OR REPLACE FUNCTION md5(in_src IN VARCHAR2) RETURN VARCHAR2 IS
  retval VARCHAR2(128);
BEGIN
  retval := CONVERT(in_src, 'ZHS16GBK');
  retval := CONVERT(retval, 'UTF8');
  retval := UTL_RAW.CAST_TO_RAW(
    SYS.DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => retval)
  );
  RETURN UPPER(retval);
END md5;

BLOB 字符串转换

-- 字符串转 BLOB
TO_BLOB(UTL_RAW.CAST_TO_RAW('string'));

-- BLOB 转字符串
UTL_RAW.CAST_TO_VARCHAR2(blob_value);

-- 十六进制转换
HEXTORAW(hex_string);
RAWTOHEX(raw_value);

XMLTYPE 字段查询

SELECT EXTRACTVALUE(VALUE(I), '/string') AS GLPSZ
FROM I_TKJT_XXJS_XMGLBGS_ITXMJXPFB T1,
     TABLE(XMLSEQUENCE(EXTRACT(T1.GLPSZ, '/ArrayOfString/string'))) I
WHERE T1.OBJECTID = :objectId;

表空间管理

查看表空间使用情况：

SELECT
  B.FILE_ID AS 文件ID,
  B.TABLESPACE_NAME AS 表空间名,
  B.BYTES AS 字节数,
  (B.BYTES - SUM(NVL(A.BYTES, 0))) AS 已使用,
  SUM(NVL(A.BYTES, 0)) AS 剩余空间,
  SUM(NVL(A.BYTES, 0)) / B.BYTES * 100 AS 剩余百分比
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID
GROUP BY B.TABLESPACE_NAME, B.FILE_ID, B.BYTES
ORDER BY B.FILE_ID;

扩展表空间：

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 4048M;

连接数管理

查询最大连接数：

SELECT value FROM v$parameter WHERE name = 'processes';

查看当前连接数：

SELECT COUNT(*) FROM v$process;

查看连接消耗：

SELECT B.MACHINE, B.PROGRAM, B.USERNAME, COUNT(*)
FROM v$process A, v$session B
WHERE A.ADDR = B.PADDR AND B.USERNAME IS NOT NULL
GROUP BY B.MACHINE, B.PROGRAM, B.USERNAME
ORDER BY COUNT(*) DESC;

总结

本文按 SQL 类型系统整理了 Oracle 数据库的常用命令，涵盖：

DDL：表空间、用户、角色、表、约束、索引等对象管理
DML：数据增删改及 MERGE 操作
DQL：各种查询技巧，包括递归、多表关联、分组聚合
DCL：权限授予与撤销
高级功能：存储过程、函数、定时任务
运维技巧：锁处理、闪回、并行查询、表空间管理

建议将本文作为日常参考手册，遇到具体问题时查阅相关章节。