Thanks. The "alter database backup controlfile to trace AS '...' " bit is what I originally seek
for. Also thanks Sybrand for the way locating spid.
"Jim Blazek" <xxx_jblazek_at_a.com> wrote in message news:3FC21DF4.EFC3E374_at_a.com...
> This is inside of a package that is called by an Oracle job. Then you can run it once a day. I
> have a directory called ddump to keep output that I create. And with the exception handling, the
> job does not break.
>
> Jim
>
> PROCEDURE system_trace
> IS
> cursor_name INTEGER;
> ret INTEGER;
> s_sql varchar(100);
> Begin
>
> cursor_name := dbms_sql.open_cursor;
>
> s_sql := 'alter database backup controlfile to trace as ''$ORACLE_ADMIN/ddump/$ORACLE_SID.'||
> to_char(sysdate, 'yyyy-mm-dd') ||'.trc'' reuse';
> dbms_sql.parse(cursor_name, s_sql, dbms_sql.native);
> ret := dbms_sql.execute(cursor_name);
>
> s_sql := 'create pfile=''$ORACLE_ADMIN/ddump/pfile$ORACLE_SID.'|| to_char(sysdate,
> 'yyyy-mm-dd') ||'.ora'' from spfile';
> dbms_sql.parse(cursor_name, s_sql, dbms_sql.native);
> ret := dbms_sql.execute(cursor_name);
>
> DBMS_SQL.close_cursor(cursor_name);
>
> EXCEPTION
> WHEN OTHERS THEN
> fxs_da_mail.send_err_mail(
> header => 'FAILURE of system_trace',
> message => 'system_trace' || crlf || sqlerrm );
>
> End system_trace;
>
>
>
> Jack Wang wrote:
>
> > All,
> >
> > I have a job 'alter database backup controlfile to trace'. How can I automate the process of
> > locating the trace file and copying to somewhere else? Thanks.
> >
> > W2K + 9.2.0.3
> >
> > - Jack
>
Received on Mon Nov 24 2003 - 17:01:55 CST
