Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Querying V$ views within scripts

RE: Querying V$ views within scripts

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Tue, 14 Dec 2004 08:33:00 -0700
Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B439196@fiji.arraybp.com>


You need to escape the '$' (i.e. select * from v\$backup).



Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of stephen booth Sent: Tuesday, December 14, 2004 5:26 AM To: oracle-l_at_freelists.org
Subject: Querying V$ views within scripts

Recently I was asked to produce a couple of scripts that could be run from the UNIX prompt (or cron) to put all of the tablespaces in a database into backup mode then then put them back into non-backup mode (the plan being to run the first script to put them into backup mode, run the backup at filesystem level then run the second script to put them back). Below is my 'first stab':

-------------------------------------------------------------------------=


#!/usr/bin/sh
ORACLE_SID=3Ddctm
export ORACLE_SID
ORAENV_ASK=3DNO
export ORAENV_ASK
. oraenv
sqlplus /nolog <<EOF
connect / as sysdba
set echo off
set head off
set verify off
set feedback off
set pagesize 1024
alter system checkpoint;
spool alter_tbs_to_backup.sql
select 'alter tablespace '||tablespace_name||' begin backup;'=20 from dba_tablespaces
where contents not in ('TEMPORARY')
/
spool off
@@alter_tbs_to_backup.sql
alter system archive log current;
EOF
exit

-------------------------------------------------------------------------=


it seems to work fine and do what it's supposed to do. The problem is that if I try to query a v$view at anytime in the script (e.g. select * from v$backup; to confirm that the tablespaces are in backup mode) it reports that the view does not exist. If I run exactly the same commands interactively then the select from the v$ view works perfectly.

I've tried specifying, for example, sys.v$backup but still get the same = error.

Presumably there's something blatantly obvious that I'm missing.=20 Anyone come accross this or have any thoughts on what it might be?

I've tried a search of Metalink but nothing useful came up.

I'm using Oracle 9.2 on Solaris 9 (patched to 09/04).

Thanks

Stephen

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

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender  of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 14 2004 - 09:35:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US