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.


Example:





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.
--
-- 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 arraysize 2500


$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - 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 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show array
arraysize 2500
SQL>