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
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>
