Tuesday, May 29, 2012

Global sqlplus setting in oracle database & client

I had a situation to change the global array size setting and i found that there is a wonderful option available to change the default value of sqlplus setting on the client & database side.

go to $ORACLE_HOME/sqlplus/admin folder
you will find a file called "glogin.sql" or "login.sql" depends on the oracle version.
you can add/modify the default value to the new value.


SQL> show array
arraysize 15

After adding it in the glogin.sql file

$ more glogin.sql

-- Copyright (c) 1988, 2004, Oracle Corporation.  All Rights Reserved.
--   glogin.sql
--   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
--   This script is automatically run
set arraysize 2500

$ sqlplus / as sysdba

SQL*Plus: Release - Production on Tue May 29 14:10:49 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show array
arraysize 2500