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 [] [SAVE .sql]
Usage:
ddl {PARAMETERS}
Parameters:
Name of the database object you want to get its description.
[OPTIONAL] Type of the resultant object.
[OPTIONAL] Save the content of the buffer to a SQL script.
[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';
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';
Result Data
* {
margin: 0;
padding: 0;
}
body {
font: 14px/1.4 Palatino, Serif;
}
