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:

Name of the database object you want to get its description.

[OPTIONAL] Type of the resultant object.

<save>

[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 可以查看历史命令

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' ?>

<ROW>

</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>

<title>Result Data</title>

<style>

* {

margin: 0;

padding: 0;

}

body {

font: 14px/1.4 Palatino, Serif;

}

ITPub博客

Oracle SQLcl介绍

最新文章