Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Permission problem on v$datafile, v$logfile, v$controlfile
A copy of this was sent to "Gerry West" <gwest_at_skyconnect.com>
(if that email address didn't require changing)
On Fri, 19 Feb 1999 11:17:30 -0700, you wrote:
>Hi All,
>
>I am having problems accessing the v$ tables from a unix Bourne shell
>script.
>Obviously this is a permissions problem.
no its not, its a shell problem....
>Oracle Tech support has not been a great help on this.
>
they aren't necessary sh programmers :)
>So far I have:
>
>grant select on v_$datafile to system;
>grant select on v_$logfile to system;
>grant select on v_$controlfile to system;
>
>I have tried executing my script as SYS and SYSTEM from the oracle8 unix id
>and
>I get the Oracle error: ORA-00942 table or view does not exist.
>
thats cause you are trying to select from V, not from v$datafile, etc.... $ = sh special character...
do this:
$ORACLE_HOME/bin/sqlplus $1/$2 << ENDIT
set echo off
set heading off
select 'cp '|| name || '/destination_dir' from v\$datafile; select 'cp '|| member || '/destination_dir' from v\$logfile; select 'cp '|| name || '/destination_dir' from v\$controlfile;
ENDIT
exit
see the \$controlfile.... that escapes the $ otherwise the sh is looking for an environment variable $datafile, doesn't find it and just returns V -- you get the 942 on select * from v...
>If I select from the above v$ tables via a stored procedure or sqlplus it
>works,
>the v$ tables are found.
>
>Below is this simple script that creates another script to perform part of a
>cold backup.
>$1 and $2 being sys/password or system/password.
>
>#!/bin/sh
>
>ORACLE_HOME=.usr/oracle8/product
>ORACLE_SID=nvod
>export ORACLE_HOME
>export ORACLE_SID
>
>$ORACLE_HOME/bin/sqlplus $1/$2 << ENDIT > run_cold_backup.sql
>set echo off
>set heading off
>
>select 'cp '|| name || '/destination_dir' from v$datafile;
>select 'cp '|| member || '/destination_dir' from v$logfile;
>select 'cp '|| name || '/destination_dir' from v$controlfile;
>
>ENDIT
>exit
>
>Thanks in advance,
>Gerry
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities