進化したのはサーバだけじゃない！

〜 DBA の毎日をもっと豊かにする

ユーティリティのすすめ〜

2017.03.08 - JPOUG in 15 minutesOracle Database Connect 2017

Michitoshi Yoshida

@miyosh0008

Oracle DB に Connect するとき、どのユーテリティを一番使っていますか？

突然ですが、みなさんに質問です：

1. SQL*Plus2. SQL Developer3. その他

ver. ５の時代から君臨しているらしい言わずと知れた定番中の定番

GUI が大好きな方でも何度かはお世話になっているはず

最近 12.2 になってヒストリ機能がついたとか

1. SQL*Plus

ここ数年で機能が追加されまくっているすごいやつ

SQL, PL/SQL 開発するなら使いましょう！最近は DBA 用の機能もあります。

2. SQL Developer

参考 : SQL Developerって必要ですか？

SQL*Plus だけだとやっぱりツライ。。どこでも rlwrap が使えるわけじゃないし。。

でも GUI で DB の管理作業するのもちょっと気が引ける。。

どこかに良いコマンドライン・ツールはないものか。。

ありそうな？悩み

SQLcl なんてどうでしょう？

そんなあなたに

SQLcl ってなあに？

→ Oracle の SQL Developer 開発チーム謹製 コマンドライン・ツール（ Java 実装）

→ SQL*Plus と互換性あり。 DB 12.2 にも同梱。 ※ 同梱されているバージョンは最新ではないので注意！

→ タブでの補完やヒストリ機能はもちろん、 さまざまな便利機能が追加されている（後述）

→ 必要なものは JRE 1.8 以上のみ Oracle Client のインストールは不要 → 最新版のダウンロードは こちら から

$ export JAVA_HOME=/usr/java/jdk1.8.0_66$ export ORACLE_SID=bisrc1 $ ./sql / as sysdba

SQLcl: 土 3 04 16:12:57 2017 のリリース 4.2.0 Production

Copyright (c) 1982, 2017, Oracle. All rights reserved.

接続先 :Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, ... 略 ...

SQL> show userUSER は "SYS“ です

さっそくインストールから OS 認証まで

sqlcl/bin にはパスを通しておきましょう！

$ unzip sqlcl-4.2.0.16.355.0402-no-jre.zip$ cd sqlcl/bin

$ sql /@jdbc:oracle:oci8:@pocvpc02

SQLcl: 土 3 04 20:32:59 2017 のリリース 4.2.0 Production

Copyright (c) 1982, 2017, Oracle. All rights reserved.

接続先 :Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters and Automatic Storage Management options

20:33:00 POCVPC02(jdbc:oracle:oci8:@pocvpc02)>

nolog はもちろん、 Wallet での接続も OK ！

Wallet での接続例：

$ cat login.sqlset time onset sqlprompt ‘&_USER(&_CONNECT_IDENTIFIER)> ’

SQL* Plus でおなじみの login.sql で プロンプトをカスタマイズ！

$ export SQLPATH=/home/oracle/tools/sql$ sql tpch@dwh

SQLcl: 土 3 04 16:25:12 2017 のリリース 4.2.0 Production

Copyright (c) 1982, 2017, Oracle. All rights reserved.

パスワード (**********?) ****接続先 :Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, ... 略 ...

16:25:14 TPCH(dwh)>

SQLcl の便利な機能たち

全部は無理なので一部だけ

$ sql tpch@dwh

SQLcl: 土 3 04 16:20:40 2017 のリリース 4.2.0 Production

Copyright (c) 1982, 2017, Oracle. All rights reserved.

パスワード (**********?) ****接続先 :Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production... 略 ...

SQL> show userUSER は "TPCH" です

net - お気に入りの接続先を登録するSQL> net dwh=ex05c1-xscan:1521/bisrc;SQL> net list dwhdwh---ex05c1-xscan:1521/bisrc

SYS(bisrc)> usersUSERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABL ..SYS OPEN 21-AUG-17 SYSTEM ..AUDSYS EXPIRED & LOCKED 22-FEB-17 22-FEB-17 USERS ..SYSTEM OPEN 21-AUG-17 SYSTEM ..OUTLN EXPIRED & LOCKED 22-FEB-17 22-FEB-17 SYSTEM .. SYSKM EXPIRED & LOCKED 22-FEB-17 22-FEB-17 USERSSYSDG EXPIRED & LOCKED 22-FEB-17 22-FEB-17 USERS SYSBACKUP EXPIRED & LOCKED 22-FEB-17 22-FEB-17 USERS GSMUSER EXPIRED & LOCKED 22-FEB-17 22-FEB-17 USERS GSMADMIN_INTERNAL EXPIRED & LOCKED 22-FEB-17 22-FEB-17 SYSAUX DIP EXPIRED & LOCKED 22-FEB-17 22-FEB-17 USERS XS$NULL EXPIRED & LOCKED 22-FEB-17 22-FEB-17 USERS ORACLE_OCM EXPIRED & LOCKED 22-FEB-17 22-FEB-17 USERS...

alias - お気に入りの SQL を登録する

SYS(bisrc)> aliaslockssessionstablestables2users

SYS(bisrc)> alias users=select 　username,account_status,lock_date,expiry_date,default_tablespace,temporary_tablespace,created,profile,last_login from dba_users order by created;

TPCH(dwh)> info H_ORDERTABLE: H_ORDER

LAST ANALYZED:2017-02-27 22:00:49.0 ROWS :3000000 SAMPLE SIZE :3000000 INMEMORY :DISABLED COMMENTS :

ColumnsNAME DATA TYPE NULL DEFAULT COMMENTS*O_ORDERKEY NUMBER(10,0) No O_CUSTKEY NUMBER(10,0) No O_ORDERSTATUS CHAR(1 BYTE) No O_TOTALPRICE NUMBER No O_ORDERDATE DATE No O_ORDERPRIORITY VARCHAR2(15 BYTE) No O_CLERK VARCHAR2(15 BYTE) No O_SHIPPRIORITY NUMBER(38,0) No O_COMMENT VARCHAR2(79 BYTE) No

IndexesINDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNSTPCH.ORDERS_PK UNIQUE VALID O_ORDERKEY

ReferencesTABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE ..H_LINEITEM LINEITEM_ORDER_FK NO ACTION ENABLED NOT DEFERRABLE ..

info - テーブル定義・構成情報を確認する

テーブル情報ROWS, LAST_ANALYZED, COMMENTS..

列定義

索引・参照制約

SYS(dwh)> info dba_usersColumnsNAME DATA TYPE NULL ... COMMENTSUSERNAME VARCHAR2(128 BYTE) No Name of the userUSER_ID NUMBER No 　　 ID number of the userPASSWORD VARCHAR2(4000 BYTE) Yes Deprecated from 11.2 .ACCOUNT_STATUS VARCHAR2(32 BYTE) NoLOCK_DATE DATE YesEXPIRY_DATE DATE YesDEFAULT_TABLESPACE VARCHAR2(30 BYTE) No Default tablespace ..TEMPORARY_TABLESPACE VARCHAR2(30 BYTE) No Default tablespace ..CREATED DATE No User creation datePROFILE VARCHAR2(128 BYTE) No User resource profile..INITIAL_RSRC_CONSUME.. VARCHAR2(128 BYTE) Yes User's initial consum..EXTERNAL_NAME VARCHAR2(4000 BYTE) Yes User external namePASSWORD_VERSIONS VARCHAR2(12 BYTE) Yes List of versions of .....

info にまつわる小ネタ その１

データ・ディクショナリ（ dba_* 等）に使うと、列情報がコメント付きで表示されて便利！注： 動的パフォーマンスビューにはコメント付きません

TPCH(dwh)> info+ H_ORDERTABLE: H_ORDER

LAST ANALYZED:2017-02-27 22:00:49.0 ROWS :3000000 SAMPLE SIZE :3000000 INMEMORY :DISABLED COMMENTS :

ColumnsNAME DATA TYPE .. LOW_VALUE HIGH_VALUE NUM_DISTINCT HIST.*O_ORDERKEY NUMBER(10,0) 1 12000000 3000000 NONE O_CUSTKEY NUMBER(10,0) 1 299999 200560 NONE O_ORDERSTATUS CHAR(1 BYTE) 3 NONE O_TOTALPRICE NUMBER 853.84 543948.47 2867456 NONE O_ORDERDATE DATE 1992.01.01. 1998.08.02. 2406 NONE O_ORDERPRIORITY VARCHAR2(15 BYTE) 1-URGENT 5-LOW 5 NONE O_CLERK VARCHAR2(15 BYTE) Clerk#00001 Clerk#02000 2000 NONE O_SHIPPRIORITY NUMBER(38,0) 0 0 1 NONE O_COMMENT VARCHAR2(79 BYTE) Tiresias.. zzle? Fur.. 2885120 NONE

IndexesINDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNSTPCH.ORDERS_PK UNIQUE VALID O_ORDERKEY..

info にまつわる小ネタ その２

info+ を使うと列統計まで確認できます！LOW_VALUE, HIGH_VALUE, NUM_DISTINCT, HISTOGRAM

TPCH(dwh)> ddl H_ORDER CREATE TABLE “TPCH”.“H_ORDER” ( “O_ORDERKEY” NUMBER(10,0) NOT NULL ENABLE,

“O_CUSTKEY” NUMBER(10,0) NOT NULL ENABLE,“O_ORDERSTATUS” CHAR(1) NOT NULL ENABLE,“O_TOTALPRICE” NUMBER NOT NULL ENABLE,“O_ORDERDATE” DATE NOT NULL ENABLE,“O_ORDERPRIORITY” VARCHAR2(15) NOT NULL ENABLE,“O_CLERK” VARCHAR2(15) NOT NULL ENABLE,“O_SHIPPRIORITY” NUMBER(*,0) NOT NULL ENABLE,“O_COMMENT” VARCHAR2(79) NOT NULL ENABLE, CONSTRAINT “ORDERS_PK” PRIMARY KEY (“O_ORDERKEY”)

... 略 ... TABLESPACE "TPCH_TS" PARALLEL 2 ;

ddl - オブジェクト再作成用の DDL を生成

出力形式は set ddl コマンドで変更可能です。STORAGE 句の出力有無、制約の出力有無、等指定可能なオプションは show ddl で確認してください。

HR(pdb1)> set sqlformat ansiconsoleHR(pdb1)> select * from employees fetch first 10 rows only;

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY ..

100 Steven King SKING 515.123.4567 03-06-17 AD_PRES 24000101 Neena Kochhar NKOCHHAR 515.123.4568 05-09-21 AD_VP 17000102 Lex De Haan LDEHAAN 515.123.4569 01-01-13 AD_VP 17000103 Alexander Hunold AHUNOLD 590.423.4567 06-01-03 IT_PROG 9000104 Bruce Ernst BERNST 590.423.4568 07-05-21 IT_PROG 6000105 David Austin DAUSTIN 590.423.4569 05-06-25 IT_PROG 4800106 Valli Pataballa VPATABAL 590.423.4560 06-02-05 IT_PROG 4800107 Diana Lorentz DLORENTZ 590.423.5567 07-02-07 IT_PROG 4200108 Nancy Greenberg NGREENBE 515.124.4569 02-08-17 FI_MGR 12008109 Daniel Faviet DFAVIET 515.124.4169 02-08-16 FI_ACCOUNT 9000

sqlformat - 問い合わせ結果の出力形式を変更

ansiconsole → カラムや行の幅を自動的に最適化した形で 出力してくれます（ SQL*Plus の col, linesize ）

sqlformat - 問い合わせ結果の出力形式を変更

csv → ヘッダ付きの csv として結果を出力してくれます

HR(pdb1)> set sqlformat csvHR(pdb1)> select * from employees fetch first 10 rows only;

”EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID"100,"Steven","King","SKING","515.123.4567",03-06-17,"AD_PRES",24000,,,90101,"Neena","Kochhar","NKOCHHAR","515.123.4568",05-09-21,"AD_VP",17000,,100,90102,"Lex","De Haan","LDEHAAN","515.123.4569",01-01-13,"AD_VP",17000,,100,90103,"Alexander","Hunold","AHUNOLD","590.423.4567",06-01-03,"IT_PROG",9000,,102,60104,"Bruce","Ernst","BERNST","590.423.4568",07-05-21,"IT_PROG",6000,,103,60105,"David","Austin","DAUSTIN","590.423.4569",05-06-25,"IT_PROG",4800,,103,60106,"Valli","Pataballa","VPATABAL","590.423.4560",06-02-05,"IT_PROG",4800,,103,60107,"Diana","Lorentz","DLORENTZ","590.423.5567",07-02-07,"IT_PROG",4200,,103,60108,"Nancy","Greenberg","NGREENBE","515.124.4569",02-08-17,"FI_MGR",12008,,101,100109,"Daniel","Faviet","DFAVIET","515.124.4169",02-08-16,"FI_ACCOUNT",9000,,108,100

他にも html や xml, json 等様々な出力形式があります。help set sqlformat で確認してみましょう！

それだけじゃないんです。この子、拡張できるんです！！

おまけ

SYS(CDB$ROOT)> help scriptSQLcl スクリプトは Java の JSR-223 に基づいており、スクリプト言語を Java VM から実行できます。 Java に含まれている Nashorn Javascript エンジンにプラグインできる言語は多数あります。

クライアント側のスクリプトを追加することで、 SQL スクリプト自体のフローを制御できます。また、ファイル・アクセス、ホスト・コマンドの詳細な制御、様々なJavascript ライブラリの利用、 Java の利用などが可能になります。

スクリプトを実行するには、単に script < スクリプト名> と入力します。ファイル拡張子が渡されない場合、 .jsであるとみなされます。

例 : https://github.com/oracle/oracle-db-tools/tree/master/sqlcl/examples

最新版のヘルプ・ドキュメントより

ざっくり言うと Javascript 等で複雑な SQL スクリプト作ったり

SQLcl 自体を拡張したりできるのです

SYS(bisrc)> select * from v$database;DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROLFILE_TYPE CONTROLFILE_CREATED CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CONTROLFILE_TIME OPEN_RESETLOGS VERSION_TIME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL REMOTE_ARCHIVE ACTIVATION# SWITCHOVER# DATABASE_ROLE ARCHIVELOG_CHANGE# ARCHIVELOG_COMPRESSION SWITCHOVER_STATUS DATAGUARD_BROKER GUARD_STATUS SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI FORCE_LOGGING PLATFORM_ID PLATFORM_NAME RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN FLASHBACK_ON SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL DB_UNIQUE_NAME STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAILOVER_OBSERVER_PRESENT FS_FAILOVER_OBSERVER_HOST CONTROLFILE_CONVERTED PRIMARY_DB_UNIQUE_NAME SUPPLEMENTAL_LOG_DATA_PL MIN_REQUIRED_CAPTURE_CHANGE# CDB CON_ID PENDING_ROLE_CHANGE_TASKS CON_DBID FORCE_FULL_DB_CACHING2208787650 BISRC 22-FEB-17 1 22-FEB-17 0 ARCHIVELOG 1630835 1603130 CURRENT 22-FEB-17 7093 1652900 04-MAR-17 NOT ALLOWED 22-FEB-17 READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE ENABLED 2208769218 2208769218 PRIMARY 1645270 DISABLED NOT ALLOWED DISABLED NONE NO NO NO NO 13 Linux x86 64-bit 1 1 1652944 NO NO NO bisrc 0 DISABLED 0 NO NO NO 0 NOT APPLICABLE 2208787650 NO

たとえば、こんなのを・・

SYS(bisrc)> script transpose.jsSYS(bisrc)> transpose select * from v$database;COLUMN VALUE--------------- ---------------DBID 2208787650NAME BISRCCREATED 2017-02-22 13:32:50.0RESETLOGS_CHANGE# 1RESETLOGS_TIME 2017-02-22 13:32:50.0PRIOR_RESETLOGS_CHANGE# 0PRIOR_RESETLOGS_TIME nullLOG_MODE ARCHIVELOGCHECKPOINT_CHANGE# 1630835ARCHIVE_CHANGE# 1603130CONTROLFILE_TYPE CURRENTCONTROLFILE_CREATED 2017-02-22 13:32:51.0 ... 略 ...

行・列入れ替えて見やすく - TRANSPOSE

スクリプトは こちら。 SQLPATH に置いて実行してください。※ 利用は自己責任でお願いします

ご清聴ありがとうございました