Oracle SQLcl介绍

来源：这里教程网时间：2026-03-03 20:04:01 作者：

Oracle SQLcl（SQL 开发人员命令行）是基于 Java 的命令行 Oracle 数据库的接口。使用 SQLcl，可以执行 SQL 和 PL/SQL 语句以交互方式或作为批处理文件。 SQLcl 提供内联编辑、语句补全、命令调用，并且还支持现有的 SQL*Plus 脚本。简单来说sqlcl和sqlplus差不多但是功能要强大的多。下载地址 https://www.oracle.com/database/technologies/appdev/sqlcl.html 我们可以使用set或者 export设置 Oracle Java 虚拟机（JVM）选项。 export JAVA_TOOL_OPTIONS= -Xmx800m [root@test 19c]# export JAVA_TOOL_OPTIONS=-Xmx800m [root@test 19c]# sql sys/oracle@192.168.75.2:1521/orcl as sysdba Picked up JAVA_TOOL_OPTIONS: -Xmx800m 如果想要查看当前配置：show all 介绍几个比较特别的功能，其他功能可以看官方文档： 1 补齐功能，tab键可以补齐 SQL> select * from dba_tab DBA_TABLES DBA_TAB_COLS_V$ DBA_TAB_IDENTITY_COLS DBA_TAB_STATS_HISTORY DBA_TABLESPACES DBA_TAB_COLUMNS DBA_TAB_MODIFICATIONS DBA_TAB_STAT_PREFS DBA_TABLESPACE_GROUPS DBA_TAB_COL_STATISTICS DBA_TAB_PARTITIONS DBA_TAB_SUBPARTITIONS DBA_TABLESPACE_THRESHOLDS DBA_TAB_COMMENTS DBA_TAB_PENDING_STATS DBA_TABLESPACE_USAGE_METRICS DBA_TAB_HISTGRM_PENDING_STATS DBA_TAB_PRIVS DBA_TAB_COLS DBA_TAB_HISTOGRAMS DBA_TAB_STATISTICS 2 支持左右箭头可以通过箭头方面的到达命令的某个位置进行修改 SQL> desc zc.t; 3 可以通过命令很方便的查看表的定义语句，和通过dbms_metadata打印的基本是一致的 DDL [object_name [type] [SAVE filename]] SQL> ddl zc.t CREATE TABLE "ZC"."T" ( "OWNER" VARCHAR2(128), SQL> help ddl Generates the code to reconstruct the selected object. Use the result type for materialized views. Use the save parameter to save the DDL in a file. DDL <object_name> [<object_type>] [SAVE <file_name>.sql] Usage: ddl {PARAMETERS} Parameters: <object> Name of the database object you want to get its description. <objecttype> [OPTIONAL] Type of the resultant object. <save> [OPTIONAL] Save the content of the buffer to a SQL script. <filename> [OPTIONAL - SAVE required] File name where the content is saved. More help topics: DDL EXAMPLES DDL SYNTAX 4 可以查看历史命令 HISTORY [index | FULL | USAGE | SCRIPT | TIME | CLEAR (SESSION)?] SQL> history --》可以查看历史命令容量是100个，循环复写，上下箭头操作，可以显示完整的指令 History: 1 select * from v$database; 2 !pwd 3 select * from v$instance; 4 ddl t1; SQL> history time; --》可以查看命令的执行时间 1 (00.352) select * from v$database; 2 ( ) !pwd 3 (00.012) select * from v$instance; SQL> history usage; --》可以查看命令的执行次数 1 (1) select * from v$database; 2 (1) !pwd 3 (1) select * from v$instance; SQL> history full; --》隔行显示 1 select * from v$database; 2 !pwd 3 select * from v$instance; 5 info命令打印出对象的信息还是比较有用的: SQL> info INFORMATION -------- This command is like describe but with more details about the objects requested. INFO[RMATION] {[schema.]object[@connect_identifier]} INFO+ will show column statistics SQL> info zc.t TABLE: T LAST ANALYZED:2024-06-06 00:05:23.0 ROWS :72467 SAMPLE SIZE :72467 INMEMORY :DISABLED COMMENTS : Columns NAME DATA TYPE NULL DEFAULT COMMENTS OWNER VARCHAR2(128 BYTE) Yes OBJECT_NAME VARCHAR2(128 BYTE) Yes SUBOBJECT_NAME VARCHAR2(128 BYTE) Yes OBJECT_ID NUMBER Yes DATA_OBJECT_ID NUMBER Yes OBJECT_TYPE VARCHAR2(23 BYTE) Yes CREATED DATE Yes LAST_DDL_TIME DATE Yes TIMESTAMP VARCHAR2(19 BYTE) Yes STATUS VARCHAR2(7 BYTE) Yes TEMPORARY VARCHAR2(1 BYTE) Yes GENERATED VARCHAR2(1 BYTE) Yes SECONDARY VARCHAR2(1 BYTE) Yes NAMESPACE NUMBER Yes EDITION_NAME VARCHAR2(128 BYTE) Yes SHARING VARCHAR2(18 BYTE) Yes EDITIONABLE VARCHAR2(1 BYTE) Yes ORACLE_MAINTAINED VARCHAR2(1 BYTE) Yes APPLICATION VARCHAR2(1 BYTE) Yes DEFAULT_COLLATION VARCHAR2(100 BYTE) Yes DUPLICATED VARCHAR2(1 BYTE) Yes SHARDED VARCHAR2(1 BYTE) Yes CREATED_APPID NUMBER Yes CREATED_VSNID NUMBER Yes MODIFIED_APPID NUMBER Yes MODIFIED_VSNID NUMBER Yes Indexes INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS _____________ _____________ _________ _________________ ____________ ZC.TT NONUNIQUE VALID OBJECT_ID SQL> info+ zc.t TABLE: T LAST ANALYZED:2024-06-06 00:05:23.0 ROWS :72467 SAMPLE SIZE :72467 INMEMORY :DISABLED COMMENTS : Columns NAME DATA TYPE NULL DEFAULT LOW_VALUE HIGH_VALUE NUM_DISTINCT HISTOGRAM OWNER VARCHAR2(128 BYTE) Yes APPQOSSYS XDB 24 NONE OBJECT_NAME VARCHAR2(128 BYTE) Yes ABSPATH sun/util/xml/PlatformXmlProperti(锟?2z+2 60868 NONE SUBOBJECT_NAME VARCHAR2(128 BYTE) Yes $VSN_1 WRR_REPLAY_THREAD_P1 310 NONE OBJECT_ID NUMBER Yes 2 73763 72466 NONE DATA_OBJECT_ID NUMBER Yes 2 73763 5892 NONE OBJECT_TYPE VARCHAR2(23 BYTE) Yes CLUSTER XML SCHEMA 47 NONE CREATED DATE Yes 2019.04.17.00.56.11 2024.06.06.00.02.19 1007 NONE LAST_DDL_TIME DATE Yes 2001.12.06.13.00.00 2024.06.06.00.02.19 1203 NONE TIMESTAMP VARCHAR2(19 BYTE) Yes 1990-08-26:11:25:00 2024-06-06:00:02:19 1195 NONE STATUS VARCHAR2(7 BYTE) Yes VALID VALID 1 NONE TEMPORARY VARCHAR2(1 BYTE) Yes N Y 2 NONE GENERATED VARCHAR2(1 BYTE) Yes N Y 2 NONE SECONDARY VARCHAR2(1 BYTE) Yes N N 1 NONE NAMESPACE NUMBER Yes 1 132 23 NONE EDITION_NAME VARCHAR2(128 BYTE) Yes 0 NONE SHARING VARCHAR2(18 BYTE) Yes DATA LINK NONE 4 NONE EDITIONABLE VARCHAR2(1 BYTE) Yes N N 1 NONE ORACLE_MAINTAINED VARCHAR2(1 BYTE) Yes N Y 2 NONE APPLICATION VARCHAR2(1 BYTE) Yes N N 1 NONE DEFAULT_COLLATION VARCHAR2(100 BYTE) Yes USING_NLS_COMP USING_NLS_COMP 1 NONE DUPLICATED VARCHAR2(1 BYTE) Yes N N 1 NONE SHARDED VARCHAR2(1 BYTE) Yes N N 1 NONE CREATED_APPID NUMBER Yes 0 NONE CREATED_VSNID NUMBER Yes 0 NONE MODIFIED_APPID NUMBER Yes 0 NONE MODIFIED_VSNID NUMBER Yes 0 NONE Indexes INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS _____________ _____________ _________ _________________ ____________ ZC.TT NONUNIQUE VALID OBJECT_ID 查看索引信息 SQL> info LOGMNR_I1SHARD_TS CREATE INDEX "SYSTEM"."LOGMNR_I1SHARD_TS" ON "SYSTEM"."LOGMNR_SHARD_TS" ("LOGMNR_UID", "TABLESPACE_NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSAUX" LOCAL (PARTITION "P_LESSTHAN100" NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSAUX" ) 还可以查看包的信息 SQL> info dbms_stats.gather_table_stats; Package /* Package SYS.DBMS_STATS */ /* PROCEDURE SYS.DBMS_STATS.GATHER_TABLE_STATS */ SYS.DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => p_IN_param0 /* VARCHAR2 */, TABNAME => p_IN_param1 /* VARCHAR2 */, PARTNAME => p_IN_param2 /* VARCHAR2 */, ESTIMATE_PERCENT => p_IN_param3 /* NUMBER */, BLOCK_SAMPLE => p_IN_param4 /* PL/SQL BOOLEAN */, METHOD_OPT => p_IN_param5 /* VARCHAR2 */, DEGREE => p_IN_param6 /* NUMBER */, 6 格式化输出相关信息 SET SQLFORMAT {csv | html | xml | json | ansiconsole | insert | loader | fixed | default} SQL> SET SQLFORMAT csv SQL> select owner,object_name from dba_objects where object_type='INDEX' and owner='SYSTEM'; "OWNER","OBJECT_NAME" "SYSTEM","LOGMNR_SESSION_EVOLVE$_PK" SQL> SET SQLFORMAT xml SQL> select owner,object_name from dba_objects where object_type='INDEX' and owner='SYSTEM'; <?xml version='1.0' encoding='UTF-8' ?> <RESULTS> <ROW> <COLUMN NAME="OWNER"><![CDATA[SYSTEM]]></COLUMN> <COLUMN NAME="OBJECT_NAME"><![CDATA[LOGMNR_SESSION_EVOLVE$_PK]]></COLUMN> </ROW> SQL> SET SQLFORMAT json SQL> select owner,object_name from dba_objects where object_type='INDEX' and owner='SYSTEM'; {"results":[{"columns":[{"name":"OWNER","type":"VARCHAR2"},{"name":"OBJECT_NAME","type":"VARCHAR2"}],"items": [ {"owner":"SYSTEM","object_name":"LOGMNR_SESSION_EVOLVE$_PK"} ,{"owner":"SYSTEM","object_name":"LOGMNR_PDB_INFO$_PK"} ,{"owner":"SYSTEM","object_name":"LOGMNR_DID$_PK"} ,{"owner":"SYSTEM","object_name":"LOGMNR_UID$_PK"} SQL> SET SQLFORMAT html SQL> select owner,object_name from dba_objects where object_type='INDEX' and owner='SYSTEM'; <!DOCTYPE html> <html> <head> <meta charset='UTF-8'> <title>Result Data</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <style> * { margin: 0; padding: 0; } body { font: 14px/1.4 Palatino, Serif; }

编辑推荐：

返回列表

Oracle SQLcl介绍

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推