LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@jri_narita(成田 裕一郎)

SQL*Plus で毎回 set pages XX って打つの面倒くさくないですか？

Last updated at Posted at 2025-08-25

そんなときは 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>
0
0
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
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?