RE: Querying V$ views within scripts
- From: "Johnson, George" <GJohnson@xxxxxxx>
- To: "'stephenbooth.uk@xxxxxxxxx'" <stephenbooth.uk@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 14 Dec 2004 13:09:08 -0000
Correct me if I'm wrong, but I don't think putting ALL the
tablespaces into backup mode at once, is a good idea. Depending on the
activity the redo would increase quite dramatically.
Rgds
-----Original Message-----
From: stephen booth [mailto:stephenbooth.uk@xxxxxxxxx]
Sent: 14 Dec 2004 12:26
To: oracle-l@xxxxxxxxxxxxx
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=dctm
export ORACLE_SID
ORAENV_ASK=NO
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;'
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.
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
--
//www.freelists.org/webpage/oracle-l
****************************************************************************
This message contains confidential information and is intended only
for the individual or entity named. If you are not the named addressee
you should not disseminate, distribute or copy this e-mail.
Please notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed, arrive
late or incomplete, or contain viruses. The sender therefore does not
accept liability for any errors or omissions in the contents of this
message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version.
This message is provided for informational purposes and should not
be construed as an invitation or offer to buy or sell any securities or
related financial instruments.
GAM operates in many jurisdictions and is
regulated or licensed in those jurisdictions as required.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
