简介一份面向Oracle初学者、开发人员及数据库管理员的原创整理PDF系统梳理了Oracle数据库的基础知识体系重点涵盖表空间、事务、索引、触发器、视图、PL/SQL编程等核心主题并介绍了OCP认证、主流数据库对比等背景内容。资源为单个PDF文档大小1.55MB内容按目录分层组织从安装启动、用户权限管理到SQL查询技巧、数据完整性维护与备份恢复均有清晰讲解适合作为系统自学或日常查阅的手册。已有1172人学习使用内容覆盖范围较广对初学者与有经验者都有参考价值。文档结合实践整理了大量可操作要点例如创建和维护表空间、使用SQL函数、编写分页过程与触发器、处理常见例外、管理权限和角色等能够帮助读者快速建立Oracle知识框架提升企业级数据库管理与开发实战能力。1. 为什么老系统里总有一台 Oracle 在扛着你刚接手一套运行了七八年的业务系统查一张订单表要等几十秒打开 AWR 报告一看SQL 执行计划里全是全表扫描和笛卡尔积。这种场景在企业里太常见了Oracle 常常以“老、稳、贵”的形象存在但真正接手后你会发现它的表空间、数据字典、事务隔离和 PL/SQL 能力和 MySQL 是完全不同的另一套思维。这份原创整理的 Oracle 基础资料从安装启动到用户权限从表管理到触发器基本覆盖了一条从“能连上数据库”到“能写存储过程”的完整路径。适合刚转 Oracle 的 DBA、被派去维护旧系统的 Java 工程师以及准备考 OCP 认证的入门者。2. 表空间与数据字典Oracle 物理与逻辑结构的起点2.1 先建表空间再建表顺序不能乱Oracle 的数据组织分两层。逻辑上一个数据库由若干表空间组成表空间里放段段包含区区由连续的数据块构成物理上表空间最终落地到数据文件。这条层级关系决定了你在创建业务表之前必须先规划好表空间。直接把业务表建到 SYSTEM 表空间是新手最容易犯的错System 表空间是数据字典的存放地一旦写满整个实例都会卡死。我一般会为业务数据单独建表空间这样做还有一个好处IO 可以分流。常见做法是把用户表、索引、临时排序分别放到不同表空间再让它们落在不同的磁盘或 ASM 磁盘组上减少磁头争抢。这个习惯即使到了 19c 也依然适用。2.2 创建表空间的参数细节创建用户表空间的 SQL 写法如下CREATE TABLESPACE app_data DATAFILE /u01/app/oracle/oradata/ORCL/app_data01.dbf SIZE 512M AUTOEXTEND ON NEXT 64M MAXSIZE 4G LOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;这段 SQL 有五个关键点。DATAFILE指定数据文件的物理路径和文件名SIZE 512M是初始大小AUTOEXTEND ON表示允许自动扩展NEXT 64M是每次扩展的增量MAXSIZE 4G是上限生产环境建议设置上限而不是无限增长否则文件系统会被慢慢写满。LOGGING表示数据文件记录重做日志默认保留即可。EXTENT MANAGEMENT LOCAL使用本地管理表空间位图记录区状态避免字典管理表的递归空间分配问题。SEGMENT SPACE MANAGEMENT AUTO使用自动段空间管理段内空闲空间用位图跟踪不需要手动设置 PCTUSED。除了用户表空间还有两个必须存在临时表空间和撤销表空间。临时表空间用于排序和哈希连接排序数据量超过内存就会写入临时表空间建独立临时表空间可以避免和业务数据争抢 IOCREATE TEMPORARY TABLESPACE temp1 TEMPFILE /u01/app/oracle/oradata/ORCL/temp01.dbf SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE 8G;2.3 用数据字典看库的家底Oracle 把元数据统一放在数据字典里日常排查问题全靠三类视图DBA_开头视图显示所有对象需要 DBA 权限ALL_视图显示当前用户有权访问的对象USER_视图只显示当前用户拥有的对象。动态性能视图以V$开头比如V$SESSION、V$SQL实时反映实例运行状态。查表空间使用率是 DBA 每天必做的操作SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS SIZE_MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;再看每个表空间的剩余空间SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS FREE_MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;两张表结合就能算出使用率。当剩余空间不足时优先看AUTOEXTEND是否关闭再看MAXSIZE是否触顶。错误的典型报错是 ORA-01653提示表空间无法扩展这个错一般就是上面两个原因之一。2.4 连接问题从监听到客户端连接不上 Oracle 时先分清是服务器问题还是客户端问题。在服务器上执行lsnrctl status检查监听状态如果监听服务无法启动优先排查端口 1521 是否被占用以及listener.ora里的主机名配置。客户端报 ORA-28547 时问题通常出在 Oracle Net 层客户端和服务器端的 SQL*Net 版本或配置不一致先交换检查sqlnet.ora和tnsnames.ora再确认客户端 OCI 版本。这类问题在 Navicat、DBeaver 上表现更明显很多时候是驱动版本和数据库版本不匹配换一个对应版本的官方 JDBC 或 OCI 驱动就恢复了。3. 用户、权限与角色用最少的 GRANT 管好一个库3.1 从 CREATE USER 开始的完整路径Oracle 的用户管理比 MySQL 复杂核心原因是权限体系细。创建一个用户的完整 SQLCREATE USER itblog IDENTIFIED BY your_password DEFAULT TABLESPACE app_data TEMPORARY TABLESPACE temp1 QUOTA UNLIMITED ON app_data;IDENTIFIED BY是登录口令DEFAULT TABLESPACE指定该用户创建对象时的默认表空间TEMPORARY TABLESPACE指定临时表空间QUOTA控制用户在这个表空间中最多能用多少空间UNLIMITED表示不限制。没有配额时用户即使有 CREATE TABLE 权限也建不了表报错 ORA-01950。这一步很多人忽略需要在授权时一起规划。新用户此时连数据库都登不进去因为缺少CREATE SESSION权限。口令策略可以用 profile 管理CREATE PROFILE app_user_profile LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1 PASSWORD_LIFE_TIME 90; ALTER USER itblog PROFILE app_user_profile;FAILED_LOGIN_ATTEMPTS是连续登录失败次数上限超过后锁定PASSWORD_LOCK_TIME是锁定天数PASSWORD_LIFE_TIME是密码有效期到 90 天强制改密。这是原资料里 profile 管理用户口令的推荐配置。3.2 系统权限、对象权限与角色的差别Oracle 把权限分为两类系统权限控制用户能不能做某类操作比如CREATE SESSION、CREATE TABLE、CREATE PROCEDURE对象权限控制用户对某个具体对象能不能增删改查比如SELECT ON scott.emp。常用权限参考下表权限类型权限名作用范围系统权限CREATE SESSION允许登录数据库系统权限CREATE TABLE允许在自己的 schema 中建表系统权限CREATE ANY TABLE允许在任意 schema 中建表系统权限DROP ANY TABLE删除任意用户的表对象权限SELECT / INSERT / UPDATE / DELETE对指定表的增删改查对象权限ALTER修改指定表结构一个个权限去授太慢角色就是权限的集合。原资料里有个形象比喻connect 角色像给账号开通进门权限resource 角色是给账号发工具dba 角色则是一整栋楼的门禁卡。实际授权可以这样组合GRANT CONNECT, RESOURCE TO itblog; GRANT SELECT, INSERT, UPDATE ON scott.emp TO itblog;CONNECT角色在 11g 之后只包含CREATE SESSIONRESOURCE包含CREATE TABLE、CREATE SEQUENCE、CREATE PROCEDURE等基础对象权限。应用账号通常只给这两个角色就够了dba 角色不要随便给业务账号。3.3 权限传递的两种方式权限传递是权限管理的进阶点原资料里单独列了权限传递一节。系统权限和对象权限的传递机制不同GRANT CREATE ANY TABLE TO hr WITH ADMIN OPTION; GRANT SELECT ON scott.emp TO hr WITH GRANT OPTION;WITH ADMIN OPTION用于系统权限接收者可以把这个权限再授予其他人而且回收 hr 的权限时hr 授出去的权限链不断。WITH GRANT OPTION用于对象权限接收者同样可以传递权限但回收是级联的从 scott 收回 hr 的 SELECT 权限后hr 授给别人的 SELECT 也会一起失效。生产环境中WITH ADMIN OPTION适合授予负责建库建表的 DBA 助手WITH GRANT OPTION适合需要在业务团队内部分发报表查询权限的人。3.4 表和约束数据完整性的第一道闸有了权限下一步就是建表。建表时要顺手把约束定义进去否则事后补非常被动CREATE TABLE emp ( id NUMBER(8) NOT NULL, emp_no VARCHAR2(20) CONSTRAINT uk_emp_no UNIQUE, name VARCHAR2(50) NOT NULL, sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal 0), dept_id NUMBER(8), CONSTRAINT pk_emp PRIMARY KEY (id), CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id) );PRIMARY KEY约束主键UNIQUE约束工号唯一CHECK约束工资不能为负FOREIGN KEY约束部门必须存在于 dept 表。约束定义在列后面叫列级定义定义在表后面叫表级定义。复合主键、复合外键必须用表级定义。生产环境改表结构时约束一般不随建表语句写在列定义里而是单独添加方便控制索引表空间ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (id) USING INDEX TABLESPACE idx_data;USING INDEX TABLESPACE指定约束对应的索引放在独立索引表空间避免和数据文件抢 IO。如果建表是在线业务高峰期加约束会导致表级锁建议低峰期操作或者先建索引再 ADD CONSTRAINT。4. 复杂查询、事务与索引从能查到查得快4.1 多表连接与子查询的取舍Oracle 的查询语法和标准 SQL 差异不大难点在于写法和性能的取舍。多表连接优先用 ANSI 92 之后的显式 JOINSELECT e.emp_no, e.name, d.dept_name FROM emp e LEFT JOIN dept d ON e.dept_id d.id WHERE d.id IS NULL;这段 SQL 查的是“没有归属部门的员工”用LEFT JOIN后判断右表主键为空来过滤逻辑上比 NOT IN 子查询更直观而且不会遇到 NULL 值陷阱WHERE dept_id NOT IN (SELECT id FROM dept)在子查询结果出现 NULL 时会返回空集很多人在这里踩过坑。NOT EXISTS也可以实现同样的语义在子表数据量大时性能更稳定SELECT e.emp_no, e.name FROM emp e WHERE NOT EXISTS (SELECT 1 FROM dept d WHERE d.id e.dept_id);子查询和 JOIN 怎么选小结果集子查询可以用 IN大表关联直接 JOINEXISTS 适合“主表小、子表大”的场景。执行计划里留意NESTED LOOP和HASH JOIN前者适合小表驱动后者适合大表全量关联。关于查询输出有一个高频真实场景身份证号导出到 Excel 后变成科学计数法。这不是数据库的问题而是超过 15 位的数字被 Excel 转成了浮点显示。正确的做法是在 SQL 里直接转成字符串输出SELECT TO_CHAR(id_card) AS id_card FROM emp WHERE emp_no 10001;TO_CHAR显式转换比隐式转换更可控开发同学和数据分析同学在写报表 SQL 时要注意这一点。4.2 ROWNUM 分页与 FETCH FIRSTOracle 11g 没有 MySQL 的 LIMIT分页必须用 ROWNUM 两层嵌套。先按工资倒序排取前 40 条再截取第 21 到第 40 条SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT emp_no, name, sal FROM emp ORDER BY sal DESC ) t WHERE ROWNUM 40 ) WHERE rn 20;三层结构每层都有用。最内层先做排序中层给排好序的结果编号同时限制 ROWNUM 上限外层再过滤页码区间。如果直接在外层写ROWNUM 20结果是空集因为 ROWNUM 在行被取出时就编号无法先编号再跳过。没有内层 ORDER BY 的分页是不稳定的每次翻页顺序都可能变。12c 及以上可以直接用FETCH FIRST 20 ROWS ONLY但如果系统还跑在 11g 上上面的三层嵌套还是要手写熟练。4.3 事务控制与锁Oracle 的事务从第一条 DML 开始到 COMMIT 或 ROLLBACK 结束。看下面这段INSERT INTO emp (id, emp_no, name, sal) VALUES (1, E001, 张三, 8000); SAVEPOINT sp1; UPDATE emp SET sal 9000 WHERE emp_no E001; ROLLBACK TO sp1; COMMIT;SAVEPOINT在事务里设置一个回退点ROLLBACK TO sp1只回退到该点之后的操作INSERT 保留UPDATE 回滚。但如果已经执行了 COMMIT整个事务就结束了之前的 SAVEPOINT 全部失效无法再回退。Oracle 默认隔离级别是读已提交。一个会话执行UPDATE未提交时另一个会话更新同一行会被阻塞直到第一个会话 COMMIT 或 ROLLBACK这就是行锁。排查阻塞常用 V$LOCK 和 V$SESSIONSELECT sid, type, id1, block FROM V$LOCK WHERE block 1;block 1表示这个会话持有阻塞别人的锁。事务不要开得过长长事务会占用回滚段极端情况下报 ORA-01555 快照过旧。在 Java 和 Navicat、DBeaver 这类客户端里还要确认是否开了自动提交很多“改了数据没生效”的诡异问题其实是客户端默认没提交。4.4 索引在哪建、什么时候拆索引不是越多越好但建对地方查询性能能提升几个数量级。Oracle 常用的索引类型索引类型适用场景特点B-tree 索引高基数列如主键、唯一编号默认索引范围查询和等值查询都适用位图索引低基数列如状态、性别适合组合查询和数仓场景但更新代价大函数索引查询条件对列做了函数处理比如 UPPER(name)函数索引直接命中创建索引的写法CREATE INDEX idx_emp_sal ON emp(sal) TABLESPACE idx_data; CREATE BITMAP INDEX idx_emp_status ON emp(status) TABLESPACE idx_data; CREATE INDEX idx_emp_upper_name ON emp(UPPER(name));idx_emp_sal是普通 B-tree 索引加速工资范围查询idx_emp_status是位图索引适合卡片机这种低基数、频繁组合查询的列idx_emp_upper_name建在大写函数上查询时也要写成WHERE UPPER(name) ZHANG SAN才能命中索引。索引的缺点同样明显每次 INSERT 和 UPDATE 都要同步维护索引树写入变慢索引段占用额外空间优化器可能选择不带你建的索引全表扫描反而更快。生产环境做大表批量导入时常见做法是先禁用索引ALTER INDEX idx_emp_sal UNUSABLE;导入完成后再重建ALTER INDEX idx_emp_sal REBUILD;UNUSABLE状态下的索引不会被维护也不参与执行计划选择但查询会失效所以这个操作只能在特定批量任务窗口内使用。判断索引有没有被用上可以看执行计划EXPLAIN PLAN FOR加查询语句后查DBMS_XPLAN.DISPLAY执行计划里的INDEX RANGE SCAN或INDEX FULL SCAN就是走索引的标记。5. PL/SQL 与触发器把规则下推到数据库层5.1 匿名块的骨架PL/SQL 是 Oracle 的过程式扩展语言最基本的形态是匿名块。结构固定为三部分声明区、执行区、异常区。DECLARE v_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE emp_no E001; v_sal : v_sal * 1.1; UPDATE emp SET sal v_sal WHERE emp_no E001; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(员工不存在); END;emp.sal%TYPE是常用的便捷写法自动跟随表字段类型表结构变更时不需要改代码。SELECT INTO必须返回恰好一行返回值多了报TOO_MANY_ROWS少了报NO_DATA_FOUND异常区分别处理。在 SQL*Plus 或 PL/SQL Developer 里执行匿名块要先SET SERVEROUTPUT ON才能看到DBMS_OUTPUT的输出。5.2 过程与函数参数模式决定行为过程和函数的区别在返回值函数必须有 RETURN过程没有。写一个分页过程是原资料练习里的重点核心参数有三种模式参数模式方向说明IN传入调用时给值过程中的修改不返回IN OUT传入传出调用时给值过程结束后带出新值OUT传出过程中赋值调用方获取结果创建存储过程的例子CREATE OR REPLACE PROCEDURE update_sal ( p_emp_no IN VARCHAR2, p_ratio IN NUMBER, p_new_sal OUT NUMBER ) IS BEGIN UPDATE emp SET sal sal * (1 p_ratio) WHERE emp_no p_emp_no RETURNING sal INTO p_new_sal; COMMIT; END;RETURNING子句把更新后的工资直接赋给p_new_sal出参省了一次回查。调用时 OUT 参数需要先用变量接住DECLARE v_new_sal NUMBER; BEGIN update_sal(E001, 0.1, v_new_sal); DBMS_OUTPUT.PUT_LINE(v_new_sal); END;函数则适合做单值计算比如自定义格式化身份证号或工单编号的拼接规则可以在 SQL 里直接调用但要注意函数默认不能修改数据库状态。5.3 触发器是最后一道闸触发器是自动执行的 PL/SQL 逻辑适合做审计、默认值填充、防止无效修改。写一个行级触发器监控工资变更CREATE OR REPLACE TRIGGER trg_emp_sal_audit BEFORE UPDATE OF sal ON emp FOR EACH ROW BEGIN IF :NEW.sal :OLD.sal THEN RAISE_APPLICATION_ERROR(-20001, 工资不允许下调); END IF; END;:OLD和:NEW是行级触发器特有的伪记录分别代表修改前后的行值。BEFORE UPDATE OF sal表示只在 SAL 列被更新时触发。RAISE_APPLICATION_ERROR主动报错事务会进入异常状态等待回滚。行级触发器对性能有影响每一行都要执行一次批量作业扫千万行时开销非常大所以生产环境不要过度堆触发器。5.4 最后别忘了数据泵PL/SQL 解决业务逻辑DBA 日常还要面对备份与迁移。逻辑备份用数据泵就足够导出和导入参数基本对称expdp itblog/your_passwordORCL DIRECTORYDATA_PUMP_DIR DUMPFILEitblog_full.dmp FULLY LOGFILEitblog_full.log impdp itblog/your_passwordORCL DIRECTORYDATA_PUMP_DIR DUMPFILEitblog_full.dmp SCHEMASitblogFULLY导全库SCHEMAS导指定模式生产环境大库建议按表或按方案导出避免数据泵文件过大。DG 主备切换时留意 archived log gapV$ARCHIVE_GAP能查主备日志缺口先补齐 gap 再切数据才不会丢。本文还有配套的精品资源点击获取 
长河君 · 2026/9/17 10:31:31 · 阅读 1.2k Oracle 基础全解析：从表空间到触发器
简介一份面向Oracle初学者、开发人员及数据库管理员的原创整理PDF系统梳理了Oracle数据库的基础知识体系重点涵盖表空间、事务、索引、触发器、视图、PL/SQL编程等核心主题并介绍了OCP认证、主流数据库对比等背景内容。资源为单个PDF文档大小1.55MB内容按目录分层组织从安装启动、用户权限管理到SQL查询技巧、数据完整性维护与备份恢复均有清晰讲解适合作为系统自学或日常查阅的手册。已有1172人学习使用内容覆盖范围较广对初学者与有经验者都有参考价值。文档结合实践整理了大量可操作要点例如创建和维护表空间、使用SQL函数、编写分页过程与触发器、处理常见例外、管理权限和角色等能够帮助读者快速建立Oracle知识框架提升企业级数据库管理与开发实战能力。1. 为什么老系统里总有一台 Oracle 在扛着你刚接手一套运行了七八年的业务系统查一张订单表要等几十秒打开 AWR 报告一看SQL 执行计划里全是全表扫描和笛卡尔积。这种场景在企业里太常见了Oracle 常常以“老、稳、贵”的形象存在但真正接手后你会发现它的表空间、数据字典、事务隔离和 PL/SQL 能力和 MySQL 是完全不同的另一套思维。这份原创整理的 Oracle 基础资料从安装启动到用户权限从表管理到触发器基本覆盖了一条从“能连上数据库”到“能写存储过程”的完整路径。适合刚转 Oracle 的 DBA、被派去维护旧系统的 Java 工程师以及准备考 OCP 认证的入门者。2. 表空间与数据字典Oracle 物理与逻辑结构的起点2.1 先建表空间再建表顺序不能乱Oracle 的数据组织分两层。逻辑上一个数据库由若干表空间组成表空间里放段段包含区区由连续的数据块构成物理上表空间最终落地到数据文件。这条层级关系决定了你在创建业务表之前必须先规划好表空间。直接把业务表建到 SYSTEM 表空间是新手最容易犯的错System 表空间是数据字典的存放地一旦写满整个实例都会卡死。我一般会为业务数据单独建表空间这样做还有一个好处IO 可以分流。常见做法是把用户表、索引、临时排序分别放到不同表空间再让它们落在不同的磁盘或 ASM 磁盘组上减少磁头争抢。这个习惯即使到了 19c 也依然适用。2.2 创建表空间的参数细节创建用户表空间的 SQL 写法如下CREATE TABLESPACE app_data DATAFILE /u01/app/oracle/oradata/ORCL/app_data01.dbf SIZE 512M AUTOEXTEND ON NEXT 64M MAXSIZE 4G LOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;这段 SQL 有五个关键点。DATAFILE指定数据文件的物理路径和文件名SIZE 512M是初始大小AUTOEXTEND ON表示允许自动扩展NEXT 64M是每次扩展的增量MAXSIZE 4G是上限生产环境建议设置上限而不是无限增长否则文件系统会被慢慢写满。LOGGING表示数据文件记录重做日志默认保留即可。EXTENT MANAGEMENT LOCAL使用本地管理表空间位图记录区状态避免字典管理表的递归空间分配问题。SEGMENT SPACE MANAGEMENT AUTO使用自动段空间管理段内空闲空间用位图跟踪不需要手动设置 PCTUSED。除了用户表空间还有两个必须存在临时表空间和撤销表空间。临时表空间用于排序和哈希连接排序数据量超过内存就会写入临时表空间建独立临时表空间可以避免和业务数据争抢 IOCREATE TEMPORARY TABLESPACE temp1 TEMPFILE /u01/app/oracle/oradata/ORCL/temp01.dbf SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE 8G;2.3 用数据字典看库的家底Oracle 把元数据统一放在数据字典里日常排查问题全靠三类视图DBA_开头视图显示所有对象需要 DBA 权限ALL_视图显示当前用户有权访问的对象USER_视图只显示当前用户拥有的对象。动态性能视图以V$开头比如V$SESSION、V$SQL实时反映实例运行状态。查表空间使用率是 DBA 每天必做的操作SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS SIZE_MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;再看每个表空间的剩余空间SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS FREE_MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;两张表结合就能算出使用率。当剩余空间不足时优先看AUTOEXTEND是否关闭再看MAXSIZE是否触顶。错误的典型报错是 ORA-01653提示表空间无法扩展这个错一般就是上面两个原因之一。2.4 连接问题从监听到客户端连接不上 Oracle 时先分清是服务器问题还是客户端问题。在服务器上执行lsnrctl status检查监听状态如果监听服务无法启动优先排查端口 1521 是否被占用以及listener.ora里的主机名配置。客户端报 ORA-28547 时问题通常出在 Oracle Net 层客户端和服务器端的 SQL*Net 版本或配置不一致先交换检查sqlnet.ora和tnsnames.ora再确认客户端 OCI 版本。这类问题在 Navicat、DBeaver 上表现更明显很多时候是驱动版本和数据库版本不匹配换一个对应版本的官方 JDBC 或 OCI 驱动就恢复了。3. 用户、权限与角色用最少的 GRANT 管好一个库3.1 从 CREATE USER 开始的完整路径Oracle 的用户管理比 MySQL 复杂核心原因是权限体系细。创建一个用户的完整 SQLCREATE USER itblog IDENTIFIED BY your_password DEFAULT TABLESPACE app_data TEMPORARY TABLESPACE temp1 QUOTA UNLIMITED ON app_data;IDENTIFIED BY是登录口令DEFAULT TABLESPACE指定该用户创建对象时的默认表空间TEMPORARY TABLESPACE指定临时表空间QUOTA控制用户在这个表空间中最多能用多少空间UNLIMITED表示不限制。没有配额时用户即使有 CREATE TABLE 权限也建不了表报错 ORA-01950。这一步很多人忽略需要在授权时一起规划。新用户此时连数据库都登不进去因为缺少CREATE SESSION权限。口令策略可以用 profile 管理CREATE PROFILE app_user_profile LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1 PASSWORD_LIFE_TIME 90; ALTER USER itblog PROFILE app_user_profile;FAILED_LOGIN_ATTEMPTS是连续登录失败次数上限超过后锁定PASSWORD_LOCK_TIME是锁定天数PASSWORD_LIFE_TIME是密码有效期到 90 天强制改密。这是原资料里 profile 管理用户口令的推荐配置。3.2 系统权限、对象权限与角色的差别Oracle 把权限分为两类系统权限控制用户能不能做某类操作比如CREATE SESSION、CREATE TABLE、CREATE PROCEDURE对象权限控制用户对某个具体对象能不能增删改查比如SELECT ON scott.emp。常用权限参考下表权限类型权限名作用范围系统权限CREATE SESSION允许登录数据库系统权限CREATE TABLE允许在自己的 schema 中建表系统权限CREATE ANY TABLE允许在任意 schema 中建表系统权限DROP ANY TABLE删除任意用户的表对象权限SELECT / INSERT / UPDATE / DELETE对指定表的增删改查对象权限ALTER修改指定表结构一个个权限去授太慢角色就是权限的集合。原资料里有个形象比喻connect 角色像给账号开通进门权限resource 角色是给账号发工具dba 角色则是一整栋楼的门禁卡。实际授权可以这样组合GRANT CONNECT, RESOURCE TO itblog; GRANT SELECT, INSERT, UPDATE ON scott.emp TO itblog;CONNECT角色在 11g 之后只包含CREATE SESSIONRESOURCE包含CREATE TABLE、CREATE SEQUENCE、CREATE PROCEDURE等基础对象权限。应用账号通常只给这两个角色就够了dba 角色不要随便给业务账号。3.3 权限传递的两种方式权限传递是权限管理的进阶点原资料里单独列了权限传递一节。系统权限和对象权限的传递机制不同GRANT CREATE ANY TABLE TO hr WITH ADMIN OPTION; GRANT SELECT ON scott.emp TO hr WITH GRANT OPTION;WITH ADMIN OPTION用于系统权限接收者可以把这个权限再授予其他人而且回收 hr 的权限时hr 授出去的权限链不断。WITH GRANT OPTION用于对象权限接收者同样可以传递权限但回收是级联的从 scott 收回 hr 的 SELECT 权限后hr 授给别人的 SELECT 也会一起失效。生产环境中WITH ADMIN OPTION适合授予负责建库建表的 DBA 助手WITH GRANT OPTION适合需要在业务团队内部分发报表查询权限的人。3.4 表和约束数据完整性的第一道闸有了权限下一步就是建表。建表时要顺手把约束定义进去否则事后补非常被动CREATE TABLE emp ( id NUMBER(8) NOT NULL, emp_no VARCHAR2(20) CONSTRAINT uk_emp_no UNIQUE, name VARCHAR2(50) NOT NULL, sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal 0), dept_id NUMBER(8), CONSTRAINT pk_emp PRIMARY KEY (id), CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id) );PRIMARY KEY约束主键UNIQUE约束工号唯一CHECK约束工资不能为负FOREIGN KEY约束部门必须存在于 dept 表。约束定义在列后面叫列级定义定义在表后面叫表级定义。复合主键、复合外键必须用表级定义。生产环境改表结构时约束一般不随建表语句写在列定义里而是单独添加方便控制索引表空间ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (id) USING INDEX TABLESPACE idx_data;USING INDEX TABLESPACE指定约束对应的索引放在独立索引表空间避免和数据文件抢 IO。如果建表是在线业务高峰期加约束会导致表级锁建议低峰期操作或者先建索引再 ADD CONSTRAINT。4. 复杂查询、事务与索引从能查到查得快4.1 多表连接与子查询的取舍Oracle 的查询语法和标准 SQL 差异不大难点在于写法和性能的取舍。多表连接优先用 ANSI 92 之后的显式 JOINSELECT e.emp_no, e.name, d.dept_name FROM emp e LEFT JOIN dept d ON e.dept_id d.id WHERE d.id IS NULL;这段 SQL 查的是“没有归属部门的员工”用LEFT JOIN后判断右表主键为空来过滤逻辑上比 NOT IN 子查询更直观而且不会遇到 NULL 值陷阱WHERE dept_id NOT IN (SELECT id FROM dept)在子查询结果出现 NULL 时会返回空集很多人在这里踩过坑。NOT EXISTS也可以实现同样的语义在子表数据量大时性能更稳定SELECT e.emp_no, e.name FROM emp e WHERE NOT EXISTS (SELECT 1 FROM dept d WHERE d.id e.dept_id);子查询和 JOIN 怎么选小结果集子查询可以用 IN大表关联直接 JOINEXISTS 适合“主表小、子表大”的场景。执行计划里留意NESTED LOOP和HASH JOIN前者适合小表驱动后者适合大表全量关联。关于查询输出有一个高频真实场景身份证号导出到 Excel 后变成科学计数法。这不是数据库的问题而是超过 15 位的数字被 Excel 转成了浮点显示。正确的做法是在 SQL 里直接转成字符串输出SELECT TO_CHAR(id_card) AS id_card FROM emp WHERE emp_no 10001;TO_CHAR显式转换比隐式转换更可控开发同学和数据分析同学在写报表 SQL 时要注意这一点。4.2 ROWNUM 分页与 FETCH FIRSTOracle 11g 没有 MySQL 的 LIMIT分页必须用 ROWNUM 两层嵌套。先按工资倒序排取前 40 条再截取第 21 到第 40 条SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT emp_no, name, sal FROM emp ORDER BY sal DESC ) t WHERE ROWNUM 40 ) WHERE rn 20;三层结构每层都有用。最内层先做排序中层给排好序的结果编号同时限制 ROWNUM 上限外层再过滤页码区间。如果直接在外层写ROWNUM 20结果是空集因为 ROWNUM 在行被取出时就编号无法先编号再跳过。没有内层 ORDER BY 的分页是不稳定的每次翻页顺序都可能变。12c 及以上可以直接用FETCH FIRST 20 ROWS ONLY但如果系统还跑在 11g 上上面的三层嵌套还是要手写熟练。4.3 事务控制与锁Oracle 的事务从第一条 DML 开始到 COMMIT 或 ROLLBACK 结束。看下面这段INSERT INTO emp (id, emp_no, name, sal) VALUES (1, E001, 张三, 8000); SAVEPOINT sp1; UPDATE emp SET sal 9000 WHERE emp_no E001; ROLLBACK TO sp1; COMMIT;SAVEPOINT在事务里设置一个回退点ROLLBACK TO sp1只回退到该点之后的操作INSERT 保留UPDATE 回滚。但如果已经执行了 COMMIT整个事务就结束了之前的 SAVEPOINT 全部失效无法再回退。Oracle 默认隔离级别是读已提交。一个会话执行UPDATE未提交时另一个会话更新同一行会被阻塞直到第一个会话 COMMIT 或 ROLLBACK这就是行锁。排查阻塞常用 V$LOCK 和 V$SESSIONSELECT sid, type, id1, block FROM V$LOCK WHERE block 1;block 1表示这个会话持有阻塞别人的锁。事务不要开得过长长事务会占用回滚段极端情况下报 ORA-01555 快照过旧。在 Java 和 Navicat、DBeaver 这类客户端里还要确认是否开了自动提交很多“改了数据没生效”的诡异问题其实是客户端默认没提交。4.4 索引在哪建、什么时候拆索引不是越多越好但建对地方查询性能能提升几个数量级。Oracle 常用的索引类型索引类型适用场景特点B-tree 索引高基数列如主键、唯一编号默认索引范围查询和等值查询都适用位图索引低基数列如状态、性别适合组合查询和数仓场景但更新代价大函数索引查询条件对列做了函数处理比如 UPPER(name)函数索引直接命中创建索引的写法CREATE INDEX idx_emp_sal ON emp(sal) TABLESPACE idx_data; CREATE BITMAP INDEX idx_emp_status ON emp(status) TABLESPACE idx_data; CREATE INDEX idx_emp_upper_name ON emp(UPPER(name));idx_emp_sal是普通 B-tree 索引加速工资范围查询idx_emp_status是位图索引适合卡片机这种低基数、频繁组合查询的列idx_emp_upper_name建在大写函数上查询时也要写成WHERE UPPER(name) ZHANG SAN才能命中索引。索引的缺点同样明显每次 INSERT 和 UPDATE 都要同步维护索引树写入变慢索引段占用额外空间优化器可能选择不带你建的索引全表扫描反而更快。生产环境做大表批量导入时常见做法是先禁用索引ALTER INDEX idx_emp_sal UNUSABLE;导入完成后再重建ALTER INDEX idx_emp_sal REBUILD;UNUSABLE状态下的索引不会被维护也不参与执行计划选择但查询会失效所以这个操作只能在特定批量任务窗口内使用。判断索引有没有被用上可以看执行计划EXPLAIN PLAN FOR加查询语句后查DBMS_XPLAN.DISPLAY执行计划里的INDEX RANGE SCAN或INDEX FULL SCAN就是走索引的标记。5. PL/SQL 与触发器把规则下推到数据库层5.1 匿名块的骨架PL/SQL 是 Oracle 的过程式扩展语言最基本的形态是匿名块。结构固定为三部分声明区、执行区、异常区。DECLARE v_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE emp_no E001; v_sal : v_sal * 1.1; UPDATE emp SET sal v_sal WHERE emp_no E001; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(员工不存在); END;emp.sal%TYPE是常用的便捷写法自动跟随表字段类型表结构变更时不需要改代码。SELECT INTO必须返回恰好一行返回值多了报TOO_MANY_ROWS少了报NO_DATA_FOUND异常区分别处理。在 SQL*Plus 或 PL/SQL Developer 里执行匿名块要先SET SERVEROUTPUT ON才能看到DBMS_OUTPUT的输出。5.2 过程与函数参数模式决定行为过程和函数的区别在返回值函数必须有 RETURN过程没有。写一个分页过程是原资料练习里的重点核心参数有三种模式参数模式方向说明IN传入调用时给值过程中的修改不返回IN OUT传入传出调用时给值过程结束后带出新值OUT传出过程中赋值调用方获取结果创建存储过程的例子CREATE OR REPLACE PROCEDURE update_sal ( p_emp_no IN VARCHAR2, p_ratio IN NUMBER, p_new_sal OUT NUMBER ) IS BEGIN UPDATE emp SET sal sal * (1 p_ratio) WHERE emp_no p_emp_no RETURNING sal INTO p_new_sal; COMMIT; END;RETURNING子句把更新后的工资直接赋给p_new_sal出参省了一次回查。调用时 OUT 参数需要先用变量接住DECLARE v_new_sal NUMBER; BEGIN update_sal(E001, 0.1, v_new_sal); DBMS_OUTPUT.PUT_LINE(v_new_sal); END;函数则适合做单值计算比如自定义格式化身份证号或工单编号的拼接规则可以在 SQL 里直接调用但要注意函数默认不能修改数据库状态。5.3 触发器是最后一道闸触发器是自动执行的 PL/SQL 逻辑适合做审计、默认值填充、防止无效修改。写一个行级触发器监控工资变更CREATE OR REPLACE TRIGGER trg_emp_sal_audit BEFORE UPDATE OF sal ON emp FOR EACH ROW BEGIN IF :NEW.sal :OLD.sal THEN RAISE_APPLICATION_ERROR(-20001, 工资不允许下调); END IF; END;:OLD和:NEW是行级触发器特有的伪记录分别代表修改前后的行值。BEFORE UPDATE OF sal表示只在 SAL 列被更新时触发。RAISE_APPLICATION_ERROR主动报错事务会进入异常状态等待回滚。行级触发器对性能有影响每一行都要执行一次批量作业扫千万行时开销非常大所以生产环境不要过度堆触发器。5.4 最后别忘了数据泵PL/SQL 解决业务逻辑DBA 日常还要面对备份与迁移。逻辑备份用数据泵就足够导出和导入参数基本对称expdp itblog/your_passwordORCL DIRECTORYDATA_PUMP_DIR DUMPFILEitblog_full.dmp FULLY LOGFILEitblog_full.log impdp itblog/your_passwordORCL DIRECTORYDATA_PUMP_DIR DUMPFILEitblog_full.dmp SCHEMASitblogFULLY导全库SCHEMAS导指定模式生产环境大库建议按表或按方案导出避免数据泵文件过大。DG 主备切换时留意 archived log gapV$ARCHIVE_GAP能查主备日志缺口先补齐 gap 再切数据才不会丢。本文还有配套的精品资源点击获取