Re: running sqlplus scripts under AT on NT(oracle 8)

Hi,

I have a batch (*.bat) that work fine, the problem will be fixed with the -SILENT option:

rem
set ORACLE_SID=bzdl
set PATH=%PATH%;d:\orant\bin
set NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1

sqlplus -SILENT sys/password_of_sys @d:\blnadm\Create_coalesce_TBS.sql sqlplus -SILENT sys/password_of_sys @d:\blnadm\coalesce_TBS.sql

Create_coalesce_TBS.sql:

set pagesize 0
set feedback off
spool d:\blnadm\coalesce_TBS.sql
select 'spool d:\blnadm\coalesce_TBS.log' from dual; select 'alter tablespace '||tablespace_name||' coalesce;' from dba_tablespaces;
select 'spool off' from dual;
select 'exit' from dual;
spool off
exit

coalesce_TBS.sql:

spool
d:\blnadm\coalesce_TBS.log alter tablespace SYSTEM coalesce;
spool
off exit
David Spaisman wrote:
>
> Hello:
>
> I have trying to run a batch(also tried as a cmd) file with sqlplus
> scripts run by the start command. Please note that there is a bat file
> attached.
> This is only 1 of 21 similar such sqlplus scripts called within the
> batch file.
>
> When I run the bat file on the command line, either directly on the
> server
> or using rcmd (nt resource kit) from my client machine, the batch files
>
> run. I get the output I am looking for--buffcache.lst as per below..
> When
> I schedule it under AT, I get nothing but the empty lst file used as a
>
> pipe. I have tried running it as a bat file, and a cmd file using
> start. I
> have also used call in a bat file and as a cmd file under AT and I do
> not
> get the sqlplus output I am looking for -- buffcache.lst in this
> example.
>
> ---------------------------------------------------------------------------
>
> A part of the bat file is:
>
> SET ORACLE_SID=INOB
> SET > D:\orant\utility\inob\batch\setinstout0120b.txt
> CALL E:\ORANT\BIN\PLUS80 system/manager_at_INOB
> @d:\orant\utility\inob\batch\batch1.sql >
> d:\orant\utility\inob\\batch\batch1.lst
>
> REM START E:\ORANT\BIN\PLUS80 system/manager_at_INOB
> @d:\orant\utility\inob\batch\batch2.sql >
> d:\orant\utility\inob\\batch\batch2.lst
>
> REM START E:\ORANT\BIN\PLUS80 system/manager_at_INOB
> @d:\orant\utility\inob\batch\batch3.sql >
> d:\orant\utility\inob\\batch\batch3.lst
>
> -----------------------------------------------------------------
>
> The batch file(or cmd file) has, under its permissions, has the system
> account with full control. I ran it from my machine and we ran the same
>
> batch file from the network administrator's machine. Both times, the
> result, under WINAT, was the same -- no buffcache output file.
>
> A sample of my batch file text is batch1.sql below:
> ----------------------------------------------------------------------
> -buffcache
> --
> -- buffer cache
> --
> -- if hit ratio = 1 -(physical reads / (db block gets + consistent gets)
> ) = less than
> -- 60/70 %, then add more DB_BLOCK_BUFFERS.
> --
> set termout off
> set feedback off
> set pause off
> set linesize 200
> spool buffcache.lst
>
> select name,value
> from v$sysstat
> where name in ('db block gets', 'consistent gets', 'physical
> reads');
>
> spool off;
> -----------------------------------------------------------------------
> The output file received when the batch file runs on the command line
> is:
>
> ------------------------------------------------------------------------------
>
> << File: BUFFCACHE.LST >>
>
> NAME
> VALUE
> ----------------------------------------------------------------
> ---------
> db block gets
> 421
> consistent gets
> 5949
> physical reads
> 322
>
> ----------------------------------------------------------------------------
>
> Please let me know if you have any suggestions on how I may basically
> run
> a sqlplus script in batch (scheduled nightly) under NT using the AT
> scheduler(actually WINAT). Does AT not recognize sqplus commands? I run
> it as system. so I am baffled why its doesn't produce sqlplus output? I
> tried the batch file with call. Still the same result? Any ideas? I may
> try to look for a third-party scheduler or get a vb exe. Thanks.
>
> David Spaisman
Received on Thu Jan 21 1999 - 01:32:30 CST