Solved

select from the table v$sesstat in a stored procedure

Posted on 2004-04-30
3
1,090 Views
Last Modified: 2012-05-04
Hi!

I want to make a stored procedure that select values from the table v$sesstat.

I'm logged into sqlplus as System.

The problem is that i get the error:
_________________________________________________

Errors for PROCEDURE SP_MEMORY2:

LINE/COL ERROR
-------- -------------------------------------------------------------
12/2     PL/SQL: SQL Statement ignored
13/7     PL/SQL: ORA-00942: table or view does not exist

___________________________________________________

this is the procedure I try to use:
____________________________________

create or replace procedure sp_memory2
as

      memsum number(12) := 0;
      thetime date;
      
      
      
begin

      thetime := sysdate;
      select sum(value) into memsum
      from v$sesstat;
                  
      dbms_output.put_line('The size is: ' || memsum || ' and the date and time is: ' || thetime);
      
end;
/
__________________________________

Can anyone tell me why this doesn't work and what I need to do to get to use this table in a stored procedure?

Greetings,

Tom-Erik
0
Comment
Question by:tomerikv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 10965099
Can anyone tell me why this doesn't work and what I need to do to get to use this table in a stored procedure?

1. because system account does not have access to this view v$sesstat;

2. log in as SYSDBA,

SQL>grant select on v$sesstat to system;


then try your procs
0
 
LVL 3

Accepted Solution

by:
dbms_chu earned 95 total points
ID: 10965134
Try logging in as sysdba then:

sql> grant select on sys.v_$sesstat to system;

0
 
LVL 1

Author Comment

by:tomerikv
ID: 10966696
Hi!

The anwer from seazodiac gave an error because of the lack of the underscore before $, but thanks to both of you!

: )

greetings,

Tom-Erik




0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question