そんなときは glogin.sql を使いましょう (結論)

はじめに

pages(ize) は SQL*Plus の SETシステム変数のひとつで、ページ※の行数を設定することができます。
デフォルト値は 14 です。

※見出しから次の見出しまでの行数

SQL> sho pages
pagesize 14
SQL> select employee_id, department_id from hr.employees;

EMPLOYEE_ID DEPARTMENT_ID      ★1行
----------- -------------      ★2行
        100            90      ★3行
        101            90      ★4行
        102            90      ★5行
        103            60      ★6行
        104            60      ★7行
        105            60      ★8行
        106            60      ★9行
        107            60      ★10行
        108           100      ★11行
        109           100      ★12行
        110           100      ★13行
                               ★14行
EMPLOYEE_ID DEPARTMENT_ID
(snip)

SQL> set pages 17
SQL> r
  1* select employee_id, department_id from hr.employees

EMPLOYEE_ID DEPARTMENT_ID      ★1行
----------- -------------      ★2行
        100            90      ★3行
        101            90      ★4行
        102            90      ★5行
        103            60      ★6行
        104            60      ★7行
        105            60      ★8行
        106            60      ★9行
        107            60      ★10行
        108           100      ★11行
        109           100      ★12行
        110           100      ★13行
        111           100      ★14行
        112           100      ★15行
        113           100      ★16行
                               ★17行
EMPLOYEE_ID DEPARTMENT_ID
(snip)

SELECT結果件数次第では何度も見出しが表示されて煩わしいので
set pages XXで大きな値を設定しますが、SQL*Plus を起動する度に入力する必要があり面倒です。
そんな時に有効なのがglogin.sqlです。

glogin.sql とは

glogin.sqlは SQL*Plus 起動時に自動実行される SQLファイルです (全ユーザに適用されます)

[oracle@handson-vm01 ~]$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
[oracle@handson-vm01 ~]$

glogin.sqlにset pages XXと書いておけば、SQL*Plus を起動する度に入力する必要がなくなります。

[oracle@handson-vm01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 7 14:31:32 2025
Version 19.28.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0

SQL>
SQL> sho pages
pagesize 14       ★
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[oracle@handson-vm01 ~]$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql
[oracle@handson-vm01 ~]$
[oracle@handson-vm01 ~]$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set pages 50000           ★
[oracle@handson-vm01 ~]$
[oracle@handson-vm01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 7 14:32:38 2025
Version 19.28.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0

SQL>
SQL> sho pages
pagesize 50000       ★
SQL>

トラブル時などにターミナルログを見返すときのために、日時などを表示するようにしておくのも有効かもしれません。

[oracle@handson-vm01 ~]$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql
[oracle@handson-vm01 ~]$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set pages 50000
select systimestamp from dual;       ★

[oracle@handson-vm01 ~]$
[oracle@handson-vm01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 7 14:53:02 2025
Version 19.28.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0


SYSTIMESTAMP
-------------------------------------
07-AUG-25 02.53.02.141734 PM +09:00    ★

SQL>

注意点

ALTER SESSIONなどでPDBを切り替えた時には実行されないので、この点は注意しましょう。(CONNECT時は実行されます)

[oracle@handson-vm01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 7 14:56:00 2025
Version 19.28.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0


SYSTIMESTAMP
---------------------------------------------------------------------------
07-AUG-25 02.56.00.245880 PM +09:00

SQL> conn hr/password@//localhost:1521/orclpdb1    ★
Connected.

SYSTIMESTAMP
-------------------------------------
07-AUG-25 02.56.26.922482 PM +09:00    ★

SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[oracle@handson-vm01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 7 14:56:36 2025
Version 19.28.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0


SYSTIMESTAMP
---------------------------------------------------------------------------
07-AUG-25 02.56.36.702689 PM +09:00

SQL>
SQL> alter session set container=orclpdb1;    ★

Session altered.

SQL>

また、接続ユーザに参照権限のないテーブルなどを SELECT するとエラーとなります。(接続エラーにはなりません)

[oracle@handson-vm01 ~]$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql
[oracle@handson-vm01 ~]$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set pages 50000
select systimestamp from dual;
select instance_name from v$instance;    ★

[oracle@handson-vm01 ~]$
[oracle@handson-vm01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 7 15:04:27 2025
Version 19.28.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0


SYSTIMESTAMP
---------------------------------------------------------------------------
07-AUG-25 03.04.27.297999 PM +09:00


INSTANCE_NAME
----------------
ORCLCDB          ★

SQL>
SQL> conn hr/password@//localhost:1521/orclpdb1
Connected.

SYSTIMESTAMP
---------------------------------------------------------------------------
07-AUG-25 03.04.43.834129 PM +09:00

select instance_name from v$instance
                          *
ERROR at line 1:
ORA-00942: table or view does not exist    ★


SQL>
SQL> sho con_name

CON_NAME
------------------------------
ORCLPDB1
SQL>
SQL> sho user
USER is "HR"
SQL>