f



ORA-00942: table or view does not exist for v$session.

Hi all,

Not as dramatic as the subject heading suggests fortunately!

I have a function within a shell script 'kill_session' (below) which
queries the v$session table. However, when I run the script I get this
error message:

Connected.
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' from v
where schemaname = upper('film')
 
*
ERROR at line 1:
ORA-00942: table or view does not exist


function kill_session
{
/opt/ora/oracle/product/9.2.0/db_1/bin/sqlplus -s /nolog<<EOF
@/home/oracle/system_conn.sql
set head off
set feed off
set verify off
set define off
set scan off
set serveroutput on
spool ${WK_DIR}kill_${schema}_sessions.sql
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' from v
$session where schemaname = upper('${schema}');
spool off
exit
EOF
}

I would be ever so grateful if someone could help me out with this one
please.

Many thanks
tb3101

0
trub3101 (40)
7/8/2010 1:12:11 PM
comp.databases.oracle.server 22978 articles. 1 followers. Post Follow

9 Replies
10918 Views

Similar Articles

[PageSpeed] 1

"trub3101" <trub3101@sky.com> a �crit dans le message de news: 8a604fd7-8ed9-4f93-b242-a5fda52caa98@e5g2000yqn.googlegroups.com...
| Hi all,
|
| Not as dramatic as the subject heading suggests fortunately!
|
| I have a function within a shell script 'kill_session' (below) which
| queries the v$session table. However, when I run the script I get this
| error message:
|
| Connected.
| select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' from v
| where schemaname = upper('film')
|
| *
| ERROR at line 1:
| ORA-00942: table or view does not exist
|
|
| function kill_session
| {
| /opt/ora/oracle/product/9.2.0/db_1/bin/sqlplus -s /nolog<<EOF
| @/home/oracle/system_conn.sql
| set head off
| set feed off
| set verify off
| set define off
| set scan off
| set serveroutput on
| spool ${WK_DIR}kill_${schema}_sessions.sql
| select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' from v
| $session where schemaname = upper('${schema}');
| spool off
| exit
| EOF
| }
|
| I would be ever so grateful if someone could help me out with this one
| please.
|
| Many thanks
| tb3101
|

v\$session

Regards
Michel


0
Michel
7/8/2010 1:40:46 PM
Probably just a "privilege" issue. V$SESSION is usually a Public
Synonym for the SYS.V_$SESSION table. Have your DBA grant an explicit
SELECT privilege on the SYS.V_$SESSION table and see if that works.

ORA-00942 table or view does not exist

Cause: The table or view entered does not exist, a synonym that is not
allowed here was used, or a view was referenced where a table is
required. Existing user tables and views can be listed by querying the
data dictionary. Certain privileges may be required to access the
table. If an application returned this message, the table the
application tried to access does not exist in the database, or the
application does not have access to it.

Action: Check each of the following:

the spelling of the table or view name.
that a view is not specified where a table is required.
that an existing table or view name exists.
Contact the database administrator if the table needs to be created or
if user or application privileges are required to access the table.

Also, if attempting to access a table or view in another schema, make
certain the correct schema is referenced and that access to the object
is granted.

0
Eugene
7/8/2010 1:41:45 PM
On 8 July, 14:41, Eugene Pokopac <epoko...@gmail.com> wrote:
> Probably just a "privilege" issue. V$SESSION is usually a Public
> Synonym for the SYS.V_$SESSION table. Have your DBA grant an explicit
> SELECT privilege on the SYS.V_$SESSION table and see if that works.
>
> ORA-00942 table or view does not exist
>
> Cause: The table or view entered does not exist, a synonym that is not
> allowed here was used, or a view was referenced where a table is
> required. Existing user tables and views can be listed by querying the
> data dictionary. Certain privileges may be required to access the
> table. If an application returned this message, the table the
> application tried to access does not exist in the database, or the
> application does not have access to it.
>
> Action: Check each of the following:
>
> the spelling of the table or view name.
> that a view is not specified where a table is required.
> that an existing table or view name exists.
> Contact the database administrator if the table needs to be created or
> if user or application privileges are required to access the table.
>
> Also, if attempting to access a table or view in another schema, make
> certain the correct schema is referenced and that access to the object
> is granted.

Thanks for your quick replies Michel and Eugene!

Top stuff! The script is now works!

Thanks again
tb3101


0
trub3101
7/8/2010 2:03:38 PM
trub3101 <trub3101@sky.com> writes:

> Hi all,
>
> Not as dramatic as the subject heading suggests fortunately!
>
> I have a function within a shell script 'kill_session' (below) which
> queries the v$session table. However, when I run the script I get this
> error message:
>
> Connected.
> select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' from v
> where schemaname = upper('film')
>  
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
>
>
> function kill_session
> {
> /opt/ora/oracle/product/9.2.0/db_1/bin/sqlplus -s /nolog<<EOF
> @/home/oracle/system_conn.sql
> set head off
> set feed off
> set verify off
> set define off
> set scan off
> set serveroutput on
> spool ${WK_DIR}kill_${schema}_sessions.sql
> select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' from v
> $session where schemaname = upper('${schema}');
> spool off
> exit
> EOF
> }
>
> I would be ever so grateful if someone could help me out with this one
> please.
>
> Many thanks
> tb3101
>

My guess is that your shell is interpreting $ in v$session as a variable
$session and expands it so that your table name ens up just being 'v'.
Quote the $ and see if that fixes it.

Tim

-- 
tcross (at) rapttech dot com dot au
0
Tim
7/8/2010 10:37:02 PM
Hi Eugene,

while i am running "SELECT COUNT(*) cnt,status FROM v$session GROUP BY status"
it is working fine, fetching the records

but same thing iam using in a procedure like below
 FOR i IN
    ( SELECT COUNT(*) cnt,status FROM SYS.v$session GROUP BY status 
    )
    LOOP
  ---
    END

but here i am getting the Same error Table does not Exist

can you please reply on this plese

and is there any possiblities to use this query resultset in my procedure?
0
ramana910140
2/12/2016 9:57:32 AM
On 2016-02-12, ramana910140@gmail.com <ramana910140@gmail.com> wrote:
> Hi Eugene,
>
> while i am running "SELECT COUNT(*) cnt,status FROM v$session GROUP BY status"
> it is working fine, fetching the records
>
> but same thing iam using in a procedure like below
>  FOR i IN
>     ( SELECT COUNT(*) cnt,status FROM SYS.v$session GROUP BY status 
>     )
>     LOOP
>   ---
>     END
>
> but here i am getting the Same error Table does not Exist
>
> can you please reply on this plese
>
> and is there any possiblities to use this query resultset in my procedure?

Well, you are responding to a five-and-a-half year old message, which
rather reduces you chance of getting a response from Eugene!

Anyway, the solution is already in his message:

    Have your DBA grant an explicit SELECT privilege on the SYS.V_$SESSION
    table and see if that works.

This is because when it works for you interactively your user presumably
has some roles granted which allow you to see V$SESSION, but inside a
procedure roles are not enabled, so you can't see it, hence the need for
an explicit grant. BTW, note the underscore in the name for the grant -
this is _necessary_.

One more thing, putting something like "can you please reply on this
plese" in message makes you sound impatient no matter how many different
ways you spell "please", and discourages many people from responding.

Eric
-- 
ms fnd in a lbry
0
Eric
2/12/2016 3:11:15 PM
Try dropping "SYS." from before the v$session synonym in your procedure.

Results via plain old SQL*Plus:

SYSTEM@SGDV>SELECT COUNT(*) cnt,status FROM SYS.v$session GROUP BY status ;
SELECT COUNT(*) cnt,status FROM SYS.v$session GROUP BY status
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SYSTEM@SGDV>SELECT COUNT(*) cnt,status FROM v$session GROUP BY status ;

       CNT STATUS
---------- --------
        29 ACTIVE
        19 INACTIVE


The devil is in the details. Public Synonyms are owned by the PUBLIC owner.
0
epokopac
2/12/2016 3:39:56 PM
On Fri, 12 Feb 2016 16:11:15 +0100, Eric wrote:


>     Have your DBA grant an explicit SELECT privilege on the
>     SYS.V_$SESSION table and see if that works.

Or he can have his DBA grant him the SYSDBA role, which would enable him 
to accomplish his task. And do some other things, too. I wonder what's the 
joke behind this bozos responding to years old messages.


-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
0
Mladen
2/20/2016 4:40:07 AM
On 2016-02-20, Mladen Gogala <gogala.mladen@gmail.com> wrote:
> On Fri, 12 Feb 2016 16:11:15 +0100, Eric wrote:
>
>> Anyway, the solution is already in his message:
>>
>>     Have your DBA grant an explicit SELECT privilege on the
>>     SYS.V_$SESSION table and see if that works.
>
> Or he can have his DBA grant him the SYSDBA role, which would enable him 
> to accomplish his task. And do some other things, too. I wonder what's the 
> joke behind this bozos responding to years old messages.

Correct quoting restored, the point being that I didn't say that, I just
referred to it. And yes, I might have done that if asked nicely with
reasons, except that he would have got permission on a view owned by a
special user rather than the real thing.

As for the "joke", it's just one of:

   not paying attention,
or ignorance,
or deliberate trolling.

Eric
-- 
ms fnd in a lbry
0
Eric
2/20/2016 2:54:39 PM
Reply: