LoginSignup
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@plusultra(Noriyoshi Shinoda)

SQLcl独自の機能を試す（１）

Last updated at Posted at 2021-07-06

Oracle Databaseのコマンドライン・インターフェースにはSQLPlus（コマンドsqlplus）と、SQLcl（コマンドsql）があります。古くから使われているツールはSQLPlusですが、最近はSQLclの方に機能が追加される傾向があります。ここではSQLclにしか存在しない機能を紹介します。環境はSQLcl 21.2 (Production Build: 21.2.0.174.2245) とOracle Database 19c (19.11)です。

CTAS

CTASコマンドは既存のテーブルからCREATE TABLE AS SELECT文を使って新しいテーブルを自動生成する機能です。「CTAS 既存テーブル 新規テーブル」の構文で実行します。

SQL> CTAS data1 data2
CREATE TABLE "SCOTT"."DATA2"
   (    "C1",
        "C2",
         PRIMARY KEY ("C1")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  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 "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS 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 "USERS"
 as
select * from DATA1
SQL> /

Table "SCOTT"."DATA2" created.

コマンドを実行するとバッファにDDLが出力されるため、/ コマンドを入力すると実行されます。既存テーブルはALL_TABLESビューから検索しているようで、ビュー名を指定するとエラーになります。同様の理由でマテリアライズド・ビューを指定する場合には成功します。生成されるDDLには制約や表領域の指定まで同じテーブルが作成されますが、インデックスは生成されません。

SQL> CTAS view1 data2
Create Table As Select command failed to get DDL for table "VIEW1".

DDL

既存のオブジェクトからDDLを生成することができます。「DDL {スキーマ名.}オブジェクト名 [オブジェクト種別] [SAVE ファイル名]」の構文で実行します。ワイルドカードは指定できません。

SQL> DDL data1 TABLE

  CREATE TABLE "SCOTT"."DATA1"
   (    "C1" NUMBER,
        "C2" VARCHAR2(10),
         PRIMARY KEY ("C1")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  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 "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS 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 "USERS" ;
SQL>

テーブルだけでなく他のオブジェクトも指定できます。以下はマテリアライズド・ビューの例です。

SQL> DDL mview1 MATERIALIZED VIEW

  CREATE MATERIALIZED VIEW "SCOTT"."MVIEW1" ("CNT")
  SEGMENT CREATION IMMEDIATE
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS 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 "USERS"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE
  AS select count(*) cnt from data1;

   COMMENT ON MATERIALIZED VIEW "SCOTT"."MVIEW1"  IS 'snapshot table for snapshot SCOTT.MVIEW1';

しかしDATABASE LINKのDDL生成は失敗するようです。

SQL> SELECT COUNT(*) FROM data1@link1;

  COUNT(*)
----------
         1

SQL> DDL link1 DATABASE LINK
Object DATABASE LINK link1 not found
SQL>

生成できるオブジェクトはスキーマ・オブジェクトに限ります。DBA_OBJECTSビューに含まれない表領域やユーザーの情報からはDDLは生成できないようです。

INFORMATION

INFORMATIONコマンド（INFOに省略できます）はSQL*PlusのDESCRIBEコマンドの代替です。
DESCRIBEコマンドは列名、NULL制約、データ型しか出力されませんが、INFORMATIONコマンドは統計情報やインデックスの指定等、追加情報が出力されます。

SQL> DESC data1
Name Null?    Type
---- -------- ------------
C1   NOT NULL NUMBER
C2            VARCHAR2(10)
SQL>
SQL> INFO data1
TABLE: DATA1
         LAST ANALYZED:2021-07-05 17:29:39.0
         ROWS         :1
         SAMPLE SIZE  :1
         INMEMORY     :DISABLED
         COMMENTS     :

Columns
NAME         DATA TYPE           NULL  DEFAULT    COMMENTS
------------ ------------------- ----- ---------- --------
*C1          NUMBER              No
 C2          VARCHAR2(10 BYTE)   Yes

Indexes
          INDEX_NAME    UNIQUENESS    STATUS    FUNCIDX_STATUS    COLUMNS
-------------------- ------------- --------- ----------------- ----------
SCOTT.SYS_C007705    UNIQUE        VALID                       C1

INFO+コマンドを実行すると更に列の統計情報が追加されます。

SQL> INFO+ data1
TABLE: DATA1
         LAST ANALYZED:2021-07-05 17:29:39.0
         ROWS         :1
         SAMPLE SIZE  :1
         INMEMORY     :DISABLED
         COMMENTS     :

Columns
NAME         DATA TYPE           NULL  DEFAULT    LOW_VALUE   HIGH_VALUE   NUM_DISTINCT   HISTOGRAM
------------ ------------------- ---------------- --------- -------------- ------------ ------------
*C1          NUMBER              No                   100         100          1              NONE
 C2          VARCHAR2(10 BYTE)   Yes                  data1       data1        1              NONE

Indexes
          INDEX_NAME    UNIQUENESS    STATUS    FUNCIDX_STATUS    COLUMNS
-------------------- ------------- --------- ----------------- ----------
SCOTT.SYS_C007705    UNIQUE        VALID                       C1

OERR

OERRは、Oracle Databaseソフトウェアに含まれるOERRコマンド（${ORACLE_HOME}/bin/oerr）をSQLcl上で実行するコマンドです。使い方はOSコマンドと同じ「OERR カテゴリー 番号」です。発生したエラーの詳細情報を知りたい時に便利です。

SQL> OERR ORA 600

00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause:    This is the generic internal error number for Oracle program
           exceptions. It indicates that a process has encountered a low-level,
           unexpected condition. The first argument is the internal message
           number. This argument and the database version number are critical in
           identifying the root cause and the potential impact to your system.
SQL>

TNSPING

OERRと同様にOSコマンドTNSPINGコマンドをSQLcl上で実行できます。tnsnames.oraファイルの識別子や接続文字列を指定できます。

SQL> TNSPING pdb1
 ping:77ms
SQL> TNSPING localhost:1521/O19A
 ping:11ms
SQL>

SQLcl 21.2 (Production Build 21.2.0.174.2245) のTNSPINGコマンドはエラーが発生して実行できないようです。

SQL> TNSPING pdb1
Exception in thread "main" java.lang.NoSuchMethodError: oracle.net.ns.NSProtocol.connect(Ljava/lang/String;Ljava/util/Properties;Loracle/jdbc/driver/DMSFactory$DMSNoun;)V
        at oracle.dbtools.raptor.newscriptrunner.commands.PingCmd.ping(PingCmd.java:110)
        at oracle.dbtools.raptor.newscriptrunner.commands.PingCmd.handleEvent(PingCmd.java:58)
        at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:346)
        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:226)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:341)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1049)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:369)
$
2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?