RE: ORA-00600 [19004]

From: Sonja Šehović <sonja.sehovic_at_pbz.hr>
Date: Fri, 26 Nov 2004 15:57:05 +0100
Message-ID: <B3772712305A314CAB52855AF8F2739613BA96D3@ksdcexc01.sit.pbz.hr>

It works, thanks!!

Regards,
Sonja

-----Original Message-----
From: Steve Jelfs [mailto:steve_at_trolltec.co.uk] Sent: Friday, November 26, 2004 3:26 PM
To: Sonja =A9ehovi=E6
Cc: oracle-l_at_freelists.org
Subject: Re: ORA-00600 [19004]

BEGIN DBMS_JOB.CHANGE
(job =3D> 21,
 next_date =3D> to_date('27-stu-2004 01:00:00 AM','dd-Mon-yyyy HH:MI:SS = =3D
AM'),
 interval =3D> 'TRUNC(SYSDATE+1)+1/24',
 what =3D> 'begin

            execute immediate ''alter session set NLS_SORT=3Dbinary'';
              DBMS_STATS.GATHER_DATABASE_STATS (NULL, FALSE,''FOR ALL =

=3D
COLUMNS SIZE AUTO'',4, ''DEFAULT'',TRUE,NULL,NULL,''GATHER'');             end;'
);

END;
/

Steve

Sonja =A9ehovi=E6 wrote:

>Hi all!
>
>I's Oracle 9.2.0.4 on AIX 5.2
>The problem is with gathering statistics.
>As workaround Oracle suggested to set NLS_SORT=3D3Dbinary in the =
session =3D
>before collecting statistics.
>
>I wanted to put it in my daily job and tried this:
>
>BEGIN DBMS_JOB.CHANGE
>(job =3D3D> 21,
> next_date =3D3D> to_date('27-stu-2004 01:00:00 AM','dd-Mon-yyyy =
HH:MI:SS =3D
>AM'),
> interval =3D3D> 'TRUNC(SYSDATE+1)+1/24',
> what =3D3D> 'alter session set NLS_SORT=3D3Dbinary;
> DBMS_STATS.GATHER_DATABASE_STATS (NULL, FALSE,''FOR ALL =
=3D
>COLUMNS SIZE AUTO'',4, ''DEFAULT'',TRUE,NULL,NULL,''GATHER'');'
>);
>END;
>/
>
>BEGIN DBMS_JOB.CHANGE
>*
>ERROR at line 1:
>ORA-06550: line 1, column 93:
>PLS-00103: Encountered the symbol "ALTER" when expecting one of the =3D
>following:
>begin case declare exit for goto if loop mod null pragma
>raise return select update while with <an identifier>
><a double-quoted delimited-identifier> <a bind variable> <<
>close current delete fetch lock insert open rollback
>savepoint set sql execute commit forall merge
><a single-quoted SQL string> pipe
>The symbol "update was inserted before "ALTER" to continue.
>ORA-06512: at "SYS.DBMS_JOB", line 79
>ORA-06512: at "SYS.DBMS_JOB", line 205
>ORA-06512: at "SYS.DBMS_JOB", line 185
>ORA-06512: at line 1
>
>I also tried with exec immediate but with no luck.
>Can someone tell me how to do this?
>
>Regards,
>Sonja
>
>--
>http://www.freelists.org/webpage/oracle-l
> =20
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 26 2004 - 08:54:52 CST