sorting during large index create

[khussain]

TRU64 UNIX, Oracle 8.1.5

sort_area_size=8M
TEMP tablespace = 7 Gig

I have a create index (local partition) running on a table with 60 partitions (about 1.5Gig) each.

TEMP space is filling up to approx. 5.5Gig

I know I can alter system to change the sort_area_size deferred...but that will be for all future connections.

Is there anyway I can increase the sort_area_size of the session creating the index?

Oh, BTW, I am creating the index using PARALLEL (4 sessions).

[pando]

alter session set sort_area_size=25M

[marist89]

Using PARALLEL 4 will require 4 times as much TEMP space as a normal rebuild.

[marist89]

http://otn.oracle.com/docs/products/...paral.htm#8399

[khussain]

I wanted to be able to assign more sort_area_size memory to a process that was already running...I don't this that is possible. Because in alter session you cannot specify a session id, it has to be the current session only.

Also, for the same create index, I was getting ora-01555 snapshot errors on the create index; even though no one is updating the rows in the table. When I took the PARALLEL word out from the create index...it runs fine.

I have a TAR open.

[pando]

you can set parameters for other session but it is not supported

there is a dirty script

-- Set initSID.ora parameters in remote sessions,
--
-- Must be connected as SYS
--
-- EXEC DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(SID=>10,-
--                                           SERIAL#=>40,-
--                                           PARNAM=>'SORT_AREA_SIZE',-
--                                           INTVAL=>10000000)
--
-- EXEC DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(SID=>10,-
--                                            SERIAL#=>40,-
--                                            PARNAM=>'HASH_JOIN_ENABLED',-
--                                            BVAL=>FALSE)
--

set verify off

prompt There are two types of parameter whose values can be set remotely, Boolean and Integer
prompt Specify parameter types, BOL for boolean and INT for integer
prompt If specifying for boolean parameter type insert anything for INITVAL and vice versa for integer parameter type
prompt Specify remote SID, SERIAL#, PARAMETER NAME and PARAMETER VALUE

pause

DECLARE
  l_partype varchar2(3) := upper('&parameter_type');
BEGIN
  IF l_partype = 'INT'
  THEN
  DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(SID => &&SID,
                                       SERIAL# => &&SERIAL,
                                       PARNAM => '&&PARAMETER_NAME',
                                       INTVAL => &INTEGER_VALUE);
  ELSE
  DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(SID => &&SID,
                                        SERIAL# => &&SERIAL,
                                        PARNAM => '&&PARAMETER_NAME',
                                        BVAL => &BOOLEAN_VALUE;
  END IF;
END;
/

undefine SID SERIAL PARAMETER_NAME