Oracle SQL Developer 实战：免客户端连接、调试与导出避坑

现在大部分人第一次接触 Oracle装完数据库之后的第一件事就是找个能连上去的客户端。我见过太多人在这一步耗掉一整个下午下 PL/SQL Developer、装 Oracle 客户端、配 ORACLE_HOME、摆 tnsnames.ora、32 位和 64 位对不上、PATH 里加了半天还是报 OCI 找不到。而 Oracle SQL Developer 这个官方免费工具解压出来就能连上一套运行中的 Oracle 数据库不需要本地装任何客户端软件这一点在临时排查问题、只拿了一个 IP 和账号密码的场景下价值非常大。下面这些内容是我自己这几年用 SQL Developer 处理日常查询、数据导出、存储过程调试、对象维护时踩出来的东西从连接怎么建、工作表怎么用、导出身份证号为什么会变成科学计数法一直到分页写法和首选项里那几个必须改的开关都会一条条讲清楚。适合刚接手 Oracle 库、手头没有正版商用客户端的开发也适合用了很久但一直只点执行按钮、没深挖过这个工具的人。1. 为什么我的日常操作最终都落在 SQL Developer 上1.1 免客户端这一点决定了它在应急场景的地位SQL Developer 是纯 Java 写的工具底层走的是 JDBC thin 驱动。把它翻译成人话就是它压根不需要你本地装 Oracle 客户端不需要 ORACLE_HOME不需要 tnsnames.ora不需要纠结 instantclient 是 19 还是 21也不需要担心 32 位和 64 位错配。它靠一个 JDBC 连接串直接对数据库的 1521 端口发起网络连接跟你在浏览器里访问一个网页本质上没什么区别。这一点带来的差别极其具体。PL/SQL Developer 走的是 OCI 接口它对本地 Oracle 客户端是有硬依赖的你在一台刚装好的干净机器上光是把客户端和环境变量摆平就得折腾一阵子而 SQL Developer 只要 JDK 到位双击就能打开。官网上 Windows 版本还专门提供了把 JDK 一起打包进去的压缩包直接下那个版本连 JAVA_HOME 都不用配。这个细节很多人不知道一直在下那个纯 zip然后启动时报一堆找不到 Java 的错。注意SQL Developer 的版本和 JDK 版本是绑定的越新的版本对 JDK 要求越高。如果你的机器上只有一个非常老的运行时环境启动阶段就会直接失败连界面都出不来。这种情况别去怀疑数据库先把工具本身的运行环境确认清楚。1.2 它和 PL/SQL Developer、Navicat 的分工我是这么划的我从来不认为这三个工具是互相替代的关系。它们的强项完全不同混着用效率最高非要选一个当唯一主力反而处处别扭。使用场景SQL DeveloperPL/SQL DeveloperNavicat干净机器上临时连库直接可用无需客户端依赖 Oracle 客户端依赖 OCI 客户端写复杂存储过程、断点调试够用界面偏朴素强项体验最好偏弱表数据对比、结构同步一般一般强项大批量数据导出成 Excel支持 xlsx实用一般强项是否需要付费免费收费收费我的实际搭配是日常查数、改数、看执行计划、临时导个表给业务全在 SQL Developer 里做真正要写几百行的存储过程并且反复单步调试时才切到 PL/SQL Developer数据对比和跨库搬运交给 Navicat。SQL Developer 承担了大概七成的工作量因为它的启动速度、连接管理、工作表体验已经足够顺手。2. 建一个能用的连接1521 之后每一步都可能是坑2.1 新建连接对话框里真正需要填的只有几个字段点左上角的绿色加号弹出新建/选择数据库连接界面上一堆输入框看着唬人实际上决定成败的就六个名称、用户名、口令、主机名、端口、服务名或 SID。名称随便起中文也行它只是个标签。用户名和口令不用多说注意口令区分大小写而且 SQL Developer 默认把口令存进本地的密码箱换机器时要重新输。真正容易翻车的是连接类型那个下拉框。默认是基本这就是最常用的方式填主机名加端口加服务名即可。下面还有个高级和TNSTNS 方式需要你本地有 tnsnames.ora这就把 thin 驱动的免客户端优势抵消掉了我基本不用。自定义 JDBC URL是排查问题时的利器因为它能把最终拼出来的连接串暴露给你看。如果你不确定自己填的东西到底拼成了什么切到这个模式看一眼心里就有数了。主机名这一栏填 IP 和填主机名都行跨网段或者有 DNS 解析问题时建议直接填 IP少一层不确定性。端口绝大多数是 1521但生产环境为了安全考虑改成别的端口非常常见拿连接信息时一定要确认一下端口别默认就是 1521。2.2 SID 还是服务名这是最高频的一个错误连接类型选基本之后下面那个下拉框有两个选项SID 和服务名。这一步填错的人最多。简单说SID 是实例的名字服务名是数据库对外提供服务的名字。11g 及以前的单实例库很多 DBA 给的就是 SID而 RAC 环境和 12c 之后的多租户架构基本都必须用服务名尤其是连 PDB 的时候服务名几乎等同于 PDB 的名字。判断方法很简单你在 SQL Developer 里试着填服务名如果报 ORA-12514就换 SID 试如果报 ORA-12505就反过来换服务名试。这两个报错就是一对反向指示报哪个就换另一边比瞎猜快得多。下面那张表是我整理出来的常见报错和对应动作遇到问题直接查表比在网上翻帖子快。报错信息大概率原因先做哪一步ORA-12541: TNS:no listener端口不通或者监听服务根本没起来先 telnet 服务器 IP 端口确认端口可达ORA-12514: 监听不认识该服务服务名写错或实例没向监听注册在服务器上跑 lsnrctl status 看服务列表ORA-12505: SID 未注册SID 填错换成服务名再试一次ORA-28009: 连接 sys 必须带角色用 sys 登录但角色选了默认把角色改成 SYSDBAORA-28040: 无匹配的认证协议工具版本太老连不上新版本库换新版 SQL DeveloperORA-28547典型的是用了不一致的客户端组件确认自己走的是 thin 还是 OCI最后那个 ORA-28547 值得单独说一句。这个错误几乎不会在 thin 驱动下出现它出现的时候通常意味着你实际上在用某个 OCI 客户端去连而客户端和服务器版本对不上或者本地同时存在多套客户端导致加载了错误的那一套。用 SQL Developer 的场景下先确认自己填的是基本连接方式而不是 TNS 方式问题经常会自己消失。监听服务起不来也是个高频问题尤其是 Windows 上装完库之后重启机器服务没设成自动启动第二天连不上就开始慌。这种情况在服务器本机执行 lsnrctl status 一看就知道不用怀疑自己的连接配置。3. 工作表里那些能省下一半时间的操作3.1 F9 和 F5 是两条完全不同的执行路径刚用 SQL Developer 的人经常困惑为什么有时候点执行只跑出一条结果有时候又跑出一堆东西还有时候报错停在一半。原因是这个工具里有两条执行路径走哪条取决于你按的是哪个键。F9 或者 CtrlEnter 是执行语句它只跑光标当前所在的那一条 SQL或者你手动选中的那段。这个模式的特点是针对性强适合反复调整一条查询语句、快速看结果。你在一个工作表里写十段互不相关的查询用这个方法逐条验证非常舒服。F5 是运行脚本它把整个工作表当成一个脚本文件按分号切分从头到尾逐条执行结果出现在脚本输出窗口里格式是传统的文本表格样式而不是那种可以点列头排序的网格。写建表脚本、写一批数据修正语句、写需要按顺序执行的 DDL 加 DML必须用这个方式否则你按 F9 只会跑出第一条后面的全被当成注释或者干脆不执行。这两个键用错带来的典型现象是明明写了五条 update结果只有一条生效然后就以为自己语句写错了反复检查语法。实际上语句没错是执行方式选错了。这个坑我踩过也见过不少人踩。提示Ctrl空格在 SQL Developer 里是补全提示的快捷键但在 Windows 中文输入法下经常被输入法抢走导致补全弹不出来。如果一直用不了补全去首选项的快捷键设置里把这一项改掉不要一直以为是软件坏了。另外几个值得记住的CtrlShiftF 是格式化代码把一团乱麻的 SQL 排成整齐的样子Ctrl/ 是切换行注释CtrlE 或 F10 是查看执行计划。这几个键组合下来写 SQL 的手感会明显变好。3.2 结果网格里的排序是个陷阱很多人被它骗过这是我最想强调的一点。在结果网格里点击列头进行排序看起来很方便但这个排序是客户端行为只对已经抓取到本地的那些行做排序不是让数据库重新排序后返回全部数据。为什么这件事很危险因为 SQL Developer 默认一次只从服务器抓取 200 行你往下滚动才会继续取。假设表里有 100 万行你执行了一个没有 order by 的查询网格里现在有 200 行数据你点了金额列排序你看到的是这 200 行里的最大值但真正的全表最大值完全不在这 200 行里。你把这个最大值拿去汇报就是一条错误的数据。正确做法只有一条想要排序就把 order by 写进 SQL 里重新执行。点列头排序只适合那种我确定结果集全部取完了只是想换个顺序看的场景比如几十行的配置表、字典表。跟这个相关的还有一个设置项。在首选项的数据库高级设置里有一项 SQL 数组提取大小默认是 200。我一般会调到 500 或 1000减少滚动时的来回取数次数。但要注意调得太大而表又特别宽的时候内存占用会明显上去特别是在同时开着好几个连接的情况下。这个值没有标准答案根据自己机器的内存和常用表的宽度来定500 是比较稳的中间值。导出结果集的入口在结果网格上右键菜单里的导出支持 csv、xlsx、sql 插入语句、xml 等好几种格式。导出 sql 插入语句这个功能特别实用需要把一小批数据搬到另一个环境时直接导出成 insert 语句就能用不用写脚本。4. 导出身份证号变成科学计数法问题到底出在哪4.1 问题的根源不在 SQL Developer而在 Excel 的解析方式这个现象太经典了从数据库里导出客户信息身份证号那一列在数据库里是 18 位的正常字符导出成 csv 之后用 Excel 一打开全变成了 1.10101E17 这种样子更麻烦的是把格式改成文本之后末尾几位已经变成 0 了原数据已经丢了。要先想清楚一件事SQL Developer 导出的 csv 文件里身份证号大概率是完整的字符串一个字符都没少。是 Excel 在打开 csv 的时候看到这一列全是数字自作主张把它当数值类型解析了。Excel 对超过 15 位的数字有精度限制所以从第 16 位开始就全部变成 0这不是显示问题是数据真的被改掉了。理解了这个因果关系解法就清晰了要么让 Excel 别把它当数字要么换一种 Excel 不会自作主张的文件格式。从结果网格里直接 CtrlC 复制再粘到 Excel同样会中招而且粘贴过程里还会带上单元格的格式信息比导出文件更混乱所以别指望复制粘贴绕过去。4.2 三种解法按场景选第一种导成 xlsx 而不是 csv。在导出界面里把格式选成 Excel 2007 及以上这种格式在写入的时候列类型是明确的实测下来身份证号、银行卡号、长单号这类字段用 xlsx 导出基本不会出问题。如果你只是想把数据给业务同事看这是最省事的一条路。第二种导出 csv 之后不走双击打开而是走 Excel 的数据菜单选从文本/CSV在导入向导里走到最后一步把身份证号那一列的列数据格式从常规显式改成文本再点加载。这个流程稍微麻烦一点但它是唯一能保证万无一失的做法原始字符完全不动。第三种是在 SQL 层面动小心思。让你的查询直接把这一列拼成一个 Excel 认得出的文本形式select cust_name, || id_card || as id_card from customer_info where rownum 1000;拼接出来的结果长这样110101199001011234。Excel 打开 csv 时看到这种写法会把它当公式而公式的结果就是那个文本字符串正好是你想要的。这招简单粗暴代价是这个 csv 没法再原样导回数据库只能用于给 Excel 看的场景。还有一个容易被忽略的操作顺序问题。很多人发现科学计数法之后会先粘贴数据再把单元格格式改成文本然后发现没用就很困惑。原因是转换在粘贴的那一刻就已经发生了事后改格式只是改了显示方式底层存的还是那个已经丢精度的数字。正确顺序是先把目标列的格式设成文本再执行粘贴而且粘贴的时候尽量用选择性粘贴里的文本选项。顺序反了怎么试都没用。注意身份证、银行卡号、手机号、订单号、统一社会信用代码这几类字段只要长度超过 15 位且全是数字都会遇到同一个问题。养成习惯看到这类字段就提前用文本方式处理别等导出之后再返工。5. 存储过程调试断点能停下之前还有几件事要先确认5.1 断点不生效多半是权限或者编译信息的问题SQL Developer 自带的调试器不算华丽但用来追一个逻辑跑偏的存储过程完全够用。前提是要把几个前置条件摆平不然你会遇到断点打了但根本不停的情况然后开始怀疑软件。第一件事是权限。调试会话需要 DEBUG CONNECT SESSION 权限通常还需要 DEBUG ANY PROCEDURE。这两个权限普通开发账号一般没有需要 DBA 授权。遇到断点无效的时候先让 DBA 确认权限不要一上来就重装工具。第二件事是编译信息。PL/SQL 代码只有在编译时带上调试信息才能在里面下断点。SQL Developer 默认会在你点调试点的时候提示编译以进行调试让它自动重新编译一次就行。但如果是别人编译进环境的包或者从生产导出来直接部署的对象里面很可能没有调试信息这时候断点就是摆设。解决办法是自己在开发环境重新编译一遍。另外调试过程本身会占用数据库会话长时间停在断点上不继续会话有可能被清理掉调试窗口会断开。真遇到这种情况重新发起一次调试就好别以为是代码问题。5.2 DBMS_OUTPUT 缓冲区溢出是个几乎每个人都会撞的坑比起打断点我日常用得更多的其实是 DBMS_OUTPUT。在循环里打几行日志看看到底执行到哪一步、变量值是多少很多时候比单步调试快得多。在 SQL Developer 里对应的是视图菜单下的DBMS 输出面板打开之后点那个绿色的加号把它绑定到你当前的连接日志才会显示出来。这里的坑在于缓冲区大小。默认的缓冲区上限是 20000 字节一个稍微复杂点的过程循环里多打几行输出很快就会超过然后你会看到 ORU-10027: buffer overflow 这个报错而且报错之后前面的日志也看不到了非常影响判断。解决方式是在过程开头把缓冲区开大begin dbms_output.enable(1000000); -- 后续业务逻辑 dbms_output.put_line(开始处理共 || v_total || 条); end; /调到 100 万字节基本够日常使用。但也要有个意识DBMS_OUTPUT 会占用会话内存而且它是过程执行完之后才把整个缓冲区刷给客户端的输出量特别大的时候会拖慢执行。真正海量的日志场景还是应该写到一张日志表里用 DBMS_OUTPUT 只做关键节点的标记。6. 分页写法、执行计划和首选项里必须改的几项6.1 11g 和 12c 的分页写法完全不同写错了会全表扫分页查询是日常最高频的需求之一但 Oracle 的写法分水岭很明显。11g 及以前没有原生的 offset 语法只能靠 rownum 套子查询这里最容易犯的错误是把排序和 rownum 放在同一层-- 11g 及以前先排序再套一层加 rownum最后再过滤 select * from (select t.*, rownum as rn from (select order_id, amount, create_time from orders order by create_time desc) t where rownum 20) where rn 10;关键点是 rownum 的生成时机早于 order by所以必须先把排序结果当成一个子查询固定下来再在外面加行号顺序颠倒的话你拿到的就是随机顺序的前 20 行。12c 之后可以直接写可读性好很多select order_id, amount, create_time from orders order by create_time desc offset 10 rows fetch next 10 rows only;这里还有一个实战经验值得提深分页。当你要取第 5000 页的数据时不管用哪种写法数据库都得先把前面几万行算出来再丢掉越翻到后面越慢。真正跑数据同步或者后台批量处理的场景我更推荐键集分页也就是记下上一页最后一条记录的排序键下一页直接从它之后取select order_id, create_time from orders where create_time :last_create_time order by create_time desc fetch next 20 rows only;这种方式每一页的代价都差不多不会随着翻页变慢。6.2 执行计划要看但要知道哪些是估算哪些是真实CtrlE 弹出来的执行计划绝大多数情况下是估算值是优化器根据统计信息推演出来的不是真实跑一遍的结果。看估算计划的价值在于判断索引有没有被用上、有没有出现全表扫描、连接方式是不是嵌套循环。这些信息已经足够帮你判断一条 SQL 的方向对不对。真要拿到真实数据靠的是 set timing on 看总耗时再配合 autotrace 拿真实的逻辑读、物理读和行数。autotrace 需要先在库里创建 plustrace 角色并授权这一步需要 DBA 配合。拿到真实统计之后最典型的判断场景是估算计划上说返回 10 行实际返回了 50 万行那说明统计信息过期了该收集统计信息了。这种偏差光看估算计划是发现不了的。顺带说一个我常用的办法对一条慢 SQL先把它的执行计划截图存下来收集完统计信息再执行一次两张计划图对比着看比凭记忆判断靠谱得多。6.3 首选项里我必改的几项改完之后体感完全不同SQL Developer 的默认配置是为通用场景准备的真正长期用下来有几项必须动。编码这一项在环境设置里我一般固定设成 UTF-8。中文出现乱码时先别急着改这个设置正确做法是先查数据库本身的字符集select parameter, value from nls_database_parameters where parameter like %CHARACTERSET%;确认库是 ZHS16GBK 还是 AL32UTF8再决定客户端这边的编码怎么设。盲目乱改编码只会把原本能显示的中文变成乱码越改越乱。自动提交这一项我的建议是绝对不要开。开了之后你在网格里顺手改一个数、顺手删一行回车就生效没有任何反悔机会。手动敲 commit 虽然多一步但这一步救过命。与之配套的是在表数据编辑界面里改了数据要记得点提交按钮改完直接关窗口改动是不生效的很多人改了半天发现数据库里没变化就是这个原因。还有几项按个人习惯调编辑器的字体大小默认字体在 2K 屏上偏小工作表加载时的最大行数控制默认拉多少行回来连接树里的对象过滤把不关心的对象类型隐藏掉找表会快很多。这些设置都保存在用户目录下换机器可以整个目录拷过去省得重新配一遍。7. 对象浏览、失效对象清理和工作表片段管理7.1 用搜索代替一层层点开连接树在连接树里一层层展开去找一张表表多了之后是很折磨人的。正确姿势是 CtrlF 调出数据库对象查找功能支持百分号通配符比如输入%ORDER%所有名字里带 ORDER 的表、视图、序列全列出来点一下就直接到。找到对象之后右键菜单里有个查看打开的是一个多标签页的详情窗口列、约束、索引、触发器、依赖关系全在里面比翻数据字典视图方便得多。需要建表语句的时候用右键的快速 DDL或者生成 DDL能直接拿到 CREATE TABLE 脚本。这里要说清楚一个边界逆推出来的 DDL和当初真正执行的建表脚本未必完全一样。分区信息、索引组织表、某些存储参数、约束的命名逆推结果都可能和原始脚本有出入。拿它做参考没问题直接拿去另一个环境执行可能会踩到细节差异。我一般在原脚本丢失、需要快速重建一个结构参考的时候才用它。7.2 改完表结构之后别忘了那些变成 INVALID 的对象这是运维里非常典型的一幕给表加了一列或者改了某列的类型当时测试的几条 SQL 都正常过了两天业务报错说某个存储过程跑不了。原因是依赖这张表的存储过程、视图、函数在表结构变化后失效了状态变成了 INVALID第一次被调用时才重新编译如果编译不过就直接报错。把失效对象一次性找出来靠这一段就够了select object_name, object_type, status from user_objects where status INVALID order by object_type, object_name;排查完之后批量重编译一个 schema 下的所有对象用这个begin dbms_utility.compile_schema(user, false); end; /第二个参数传 false 表示不生成编译告警信息速度会快一些。重编译之后再跑一次上面的查询还剩下的 INVALID 就是真正有问题的对象需要逐个看它的编译错误select name, type, line, position, text from user_errors order by name, type, sequence;养成改完表结构就顺手跑一遍这两段 SQL 的习惯能省掉很多莫名其妙就报错的沟通成本。7.3 把高频 SQL 沉淀成片段别再靠脑记SQL Developer 有个片段面板可以把你常用的 SQL 存进去需要的时候拖到工作表里就能用。我在这里常备几段都是用一次省一次的按逗号把一列拆成多行处理那种用逗号拼接存储的标签字段时特别好用select regexp_substr(A,B,C,D, [^,], 1, level) as item from dual connect by level regexp_count(A,B,C,D, ,) 1;按照某个条件判断存在就更新、不存在就插入一条语句搞定merge into target_table t using (select :id as id, :name as name from dual) s on (t.id s.id) when matched then update set t.name s.name when not matched then insert (id, name) values (s.id, s.name);查当天数据时用来卡时间边界的select count(*) from orders where create_time trunc(sysdate) and create_time trunc(sysdate) 1;注意后半段用小于明天零点而不是用小于等于今天加 23 小时 59 分 59 秒。这张表上如果 create_time 是带毫秒的时间戳类型用小于等于的做法会漏掉最后那一秒里的部分数据这个细节在日志表、流水表上特别容易出问题。还有一个取整数字段的金额合计别忘了一起把空值处理掉select nvl(sum(amount), 0) as total_amount from orders where create_time trunc(sysdate, mm);trunc 带月份参数就是取当月一号配合 to_char 做月份分组统计的时候经常一起用。这些片段看起来都是基础语法但每次临时要写的时候从空白开始敲和从片段面板拖出来改两笔效率差距是实打实的。工具这类东西功能表上写的都差不多真正拉开差距的永远是那些默认配置之外的小开关和踩过才知道的坑。SQL Developer 的默认设置能让你跑通第一条查询但上面这些调整——数组提取大小、DBMS 输出的缓冲区、排序的真实含义、导出格式的选择、改完表结构后的重编译——才是让它从能用变成顺手的分界线。我自己的习惯是每换一台开发机花十分钟把这几个首选项和片段重新配一遍后面几个月都能省下零碎的时间。