MySQLやPostgreSQLを知っていると、OracleのSQL*Plusは列の幅調整等、SQLの結果表示は少し面倒に感じるかもしれません。
今回はこれだけは知っておきたいSQL*Plusのコマンドを紹介します。
ページ毎の行表示数
デフォルトの場合、10行毎にヘッダーが表示されるため、検索結果が多いと無駄な表示が増えます。
その場合は set pages <行数> で変更します。
変更前
SQL> select username from dba_users;
USERNAME
--------------------------------------------------------------------------------
SYS
SYSTEM
XS$NULL
OJVMSYS
LBACSYS
OUTLN
SYS$UMF
DBSNMP
APPQOSSYS
DBSFWUSER
GGSYS
USERNAME
--------------------------------------------------------------------------------
ANONYMOUS
CTXSYS
DVSYS
DVF
PERFSTAT
GSMADMIN_INTERNAL
MDSYS
OLAPSYS
XDB
WMSYS
GSMCATUSER
USERNAME
--------------------------------------------------------------------------------
MDDATA
SYSBACKUP
REMOTE_SCHEDULER_AGENT
GSMUSER
SYSRAC
GSMROOTUSER
SI_INFORMTN_SCHEMA
AUDSYS
DIP
ORDPLUGINS
SYSKM
USERNAME
--------------------------------------------------------------------------------
ORDDATA
ORACLE_OCM
SYSDG
ORDSYS
37 rows selected.
変更後
SQL> select username from dba_users;
USERNAME
--------------------------------------------------------------------------------
SYS
SYSTEM
XS$NULL
OJVMSYS
LBACSYS
OUTLN
SYS$UMF
DBSNMP
APPQOSSYS
DBSFWUSER
GGSYS
ANONYMOUS
CTXSYS
DVSYS
DVF
PERFSTAT
GSMADMIN_INTERNAL
MDSYS
OLAPSYS
XDB
WMSYS
GSMCATUSER
MDDATA
SYSBACKUP
REMOTE_SCHEDULER_AGENT
GSMUSER
SYSRAC
GSMROOTUSER
SI_INFORMTN_SCHEMA
AUDSYS
DIP
ORDPLUGINS
SYSKM
ORDDATA
ORACLE_OCM
SYSDG
ORDSYS
37 rows selected.
列の幅調整
デフォルトの場合、列の幅が長すぎて無駄な改行が入ることがあります。
その場合は col <列名> for a<列幅> で列幅を調整します。
変更前
SQL> select username, account_status from dba_users;
USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
SYS
OPEN
SYSTEM
OPEN
XS$NULL
EXPIRED & LOCKED
OJVMSYS
LOCKED
LBACSYS
LOCKED
OUTLN
LOCKED
SYS$UMF
LOCKED
DBSNMP
LOCKED
APPQOSSYS
LOCKED
DBSFWUSER
LOCKED
GGSYS
LOCKED
ANONYMOUS
EXPIRED & LOCKED
CTXSYS
EXPIRED & LOCKED
DVSYS
LOCKED
DVF
LOCKED
USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
PERFSTAT
OPEN
GSMADMIN_INTERNAL
LOCKED
MDSYS
LOCKED
OLAPSYS
LOCKED
XDB
LOCKED
WMSYS
LOCKED
GSMCATUSER
LOCKED
MDDATA
LOCKED
SYSBACKUP
LOCKED
REMOTE_SCHEDULER_AGENT
LOCKED
GSMUSER
LOCKED
SYSRAC
LOCKED
GSMROOTUSER
LOCKED
SI_INFORMTN_SCHEMA
LOCKED
AUDSYS
LOCKED
USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
DIP
LOCKED
ORDPLUGINS
LOCKED
SYSKM
LOCKED
ORDDATA
LOCKED
ORACLE_OCM
LOCKED
SYSDG
LOCKED
ORDSYS
LOCKED
37 rows selected.
変更後
SQL> col username for a30
SQL> select username, account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SYS OPEN
SYSTEM OPEN
XS$NULL EXPIRED & LOCKED
OJVMSYS LOCKED
LBACSYS LOCKED
OUTLN LOCKED
SYS$UMF LOCKED
DBSNMP LOCKED
APPQOSSYS LOCKED
DBSFWUSER LOCKED
GGSYS LOCKED
ANONYMOUS EXPIRED & LOCKED
CTXSYS EXPIRED & LOCKED
DVSYS LOCKED
DVF LOCKED
PERFSTAT OPEN
GSMADMIN_INTERNAL LOCKED
MDSYS LOCKED
OLAPSYS LOCKED
XDB LOCKED
WMSYS LOCKED
GSMCATUSER LOCKED
MDDATA LOCKED
SYSBACKUP LOCKED
REMOTE_SCHEDULER_AGENT LOCKED
GSMUSER LOCKED
SYSRAC LOCKED
GSMROOTUSER LOCKED
SI_INFORMTN_SCHEMA LOCKED
AUDSYS LOCKED
DIP LOCKED
ORDPLUGINS LOCKED
SYSKM LOCKED
ORDDATA LOCKED
ORACLE_OCM LOCKED
SYSDG LOCKED
ORDSYS LOCKED
37 rows selected.
時刻表示形式の変更
SYSDATE等はデフォルトでは時刻は表示されないので、以下のALTER SESSIONコマンドにて時刻表示を変更します。
alter session set nls_date_format=’YYYY/MM/DD HH24:MI:SS’;
変更前
SQL> select sysdate from dual;
SYSDATE
------------------
23-MAY-21
変更後
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2021/05/23 15:16:40
横幅の調整
横幅が短いと検索結果が無駄に改行されてしまう場合があります。
その場合は SET LIN <横幅> で1行のサイズを調整します。
変更前
SQL> select username, account_status, created from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
CREATED
-------------------
SYS OPEN
2019/04/17 00:56:32
SYSTEM OPEN
2019/04/17 00:56:33
XS$NULL EXPIRED & LOCKED
2019/04/17 01:02:44
OJVMSYS LOCKED
2019/04/17 01:42:57
LBACSYS LOCKED
2019/04/17 02:02:15
OUTLN LOCKED
2019/04/17 00:56:39
SYS$UMF LOCKED
2019/04/17 01:10:05
DBSNMP LOCKED
2019/04/17 01:14:35
APPQOSSYS LOCKED
2019/04/17 01:14:37
DBSFWUSER LOCKED
2019/04/17 01:02:55
GGSYS LOCKED
2019/04/17 01:15:01
ANONYMOUS EXPIRED & LOCKED
2019/04/17 01:16:53
CTXSYS EXPIRED & LOCKED
2019/04/17 01:46:56
DVSYS LOCKED
2019/04/17 02:02:47
DVF LOCKED
2019/04/17 02:02:47
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
CREATED
-------------------
PERFSTAT OPEN
2021/05/23 11:08:21
GSMADMIN_INTERNAL LOCKED
2019/04/17 01:02:02
MDSYS LOCKED
2019/04/17 01:48:37
OLAPSYS LOCKED
2019/04/17 01:52:56
XDB LOCKED
2019/04/17 01:16:53
WMSYS LOCKED
2019/04/17 01:40:04
GSMCATUSER LOCKED
2019/04/17 01:14:57
MDDATA LOCKED
2019/04/17 01:53:39
SYSBACKUP LOCKED
2019/04/17 00:56:33
REMOTE_SCHEDULER_AGENT LOCKED
2019/04/17 01:02:54
GSMUSER LOCKED
2019/04/17 01:02:02
SYSRAC LOCKED
2019/04/17 00:56:33
GSMROOTUSER LOCKED
2019/04/17 01:02:03
SI_INFORMTN_SCHEMA LOCKED
2019/04/17 01:48:37
AUDSYS LOCKED
2019/04/17 00:56:33
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
CREATED
-------------------
DIP LOCKED
2019/04/17 01:02:17
ORDPLUGINS LOCKED
2019/04/17 01:48:37
SYSKM LOCKED
2019/04/17 00:56:33
ORDDATA LOCKED
2019/04/17 01:48:37
ORACLE_OCM LOCKED
2019/04/17 01:04:04
SYSDG LOCKED
2019/04/17 00:56:33
ORDSYS LOCKED
2019/04/17 01:48:36
37 rows selected.
変更後
SQL> set lin 200
SQL> select username, account_status, created from dba_users;
USERNAME ACCOUNT_STATUS CREATED
------------------------------ -------------------------------- -------------------
SYS OPEN 2019/04/17 00:56:32
SYSTEM OPEN 2019/04/17 00:56:33
XS$NULL EXPIRED & LOCKED 2019/04/17 01:02:44
OJVMSYS LOCKED 2019/04/17 01:42:57
LBACSYS LOCKED 2019/04/17 02:02:15
OUTLN LOCKED 2019/04/17 00:56:39
SYS$UMF LOCKED 2019/04/17 01:10:05
DBSNMP LOCKED 2019/04/17 01:14:35
APPQOSSYS LOCKED 2019/04/17 01:14:37
DBSFWUSER LOCKED 2019/04/17 01:02:55
GGSYS LOCKED 2019/04/17 01:15:01
ANONYMOUS EXPIRED & LOCKED 2019/04/17 01:16:53
CTXSYS EXPIRED & LOCKED 2019/04/17 01:46:56
DVSYS LOCKED 2019/04/17 02:02:47
DVF LOCKED 2019/04/17 02:02:47
PERFSTAT OPEN 2021/05/23 11:08:21
GSMADMIN_INTERNAL LOCKED 2019/04/17 01:02:02
MDSYS LOCKED 2019/04/17 01:48:37
OLAPSYS LOCKED 2019/04/17 01:52:56
XDB LOCKED 2019/04/17 01:16:53
WMSYS LOCKED 2019/04/17 01:40:04
GSMCATUSER LOCKED 2019/04/17 01:14:57
MDDATA LOCKED 2019/04/17 01:53:39
SYSBACKUP LOCKED 2019/04/17 00:56:33
REMOTE_SCHEDULER_AGENT LOCKED 2019/04/17 01:02:54
GSMUSER LOCKED 2019/04/17 01:02:02
SYSRAC LOCKED 2019/04/17 00:56:33
GSMROOTUSER LOCKED 2019/04/17 01:02:03
SI_INFORMTN_SCHEMA LOCKED 2019/04/17 01:48:37
AUDSYS LOCKED 2019/04/17 00:56:33
DIP LOCKED 2019/04/17 01:02:17
ORDPLUGINS LOCKED 2019/04/17 01:48:37
SYSKM LOCKED 2019/04/17 00:56:33
ORDDATA LOCKED 2019/04/17 01:48:37
ORACLE_OCM LOCKED 2019/04/17 01:04:04
SYSDG LOCKED 2019/04/17 00:56:33
ORDSYS LOCKED 2019/04/17 01:48:36
37 rows selected.
私がよく使うものを紹介しましたが、毎回セットせずにデフォルトで表示を変更したい場合は glogin.sql ファイルを変更します。
変更の方法は以下の記事を参照してください。
