Home » SQL & PL/SQL » SQL & PL/SQL » MY$STATS TABLE PROBLEM
MY$STATS TABLE PROBLEM [message #290663] Mon, 31 December 2007 01:21 Go to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Hi,

I have the below problem:

It works in anynoymous block but it will not work in function or procedure.
Any idea what i should do.

SQL> set serveroutput on
SQL>  declare
  2     l_used_memory  NUMBER(10);
  3    BEGIN
  4    SELECT value 
  5     INTO   l_used_memory
  6    FROM   v$mystat,
  7    v$statname
  8     WHERE v$mystat.statistic# = v$statname.statistic#
  9    AND     name = 'session pga memory';
 10  dbms_output.put_line( l_used_memory);
 11   end;
 12  /
1957460

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE PROCEDURE PR_get_used_memory (V_VALUE OUT NUMBER)
  2  AS
  3   l_used_memory  NUMBER(10);
  4  BEGIN
  5   SELECT ms.value 
  6   INTO   l_used_memory
  7   FROM   v$mystat ms,
  8          v$statname sn
  9   WHERE  ms.statistic# = sn.statistic#
 10   AND     sn.name = 'session pga memory';
 11  -- RETURN l_used_memory;
 12  END PR_get_used_memory;
 13  /

Warning: Procedure created with compilation errors.

SQL> SHOW ERR
Errors for PROCEDURE PR_GET_USED_MEMORY:

LINE/COL
--------
ERROR
------------------------------
5/2
PL/SQL: SQL Statement ignored

8/9
PL/SQL: ORA-00942: table or
view does not exist



Thaks,
Srinivas


[Mod-edit: Frank changed closing code-tag]

[Updated on: Mon, 31 December 2007 02:32] by Moderator

Report message to a moderator

Re: MY$STATS TABLE PROBLEM [message #290666 is a reply to message #290663] Mon, 31 December 2007 01:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Ensure that your schema is having adequate garnts/ previllege to access v$mystat .

Thumbs Up
Rajuvan.
Re: MY$STATS TABLE PROBLEM [message #290669 is a reply to message #290663] Mon, 31 December 2007 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Grant select on v$mystat to public.

Regards
Michel
Re: MY$STATS TABLE PROBLEM [message #290673 is a reply to message #290663] Mon, 31 December 2007 01:36 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Hi,

When i run that i am getting the following privilege error,
Should i ask my DBA to give privilege
or where to find the default user name and password like SYS/DBA or
SYSTEM/MANAGER so that i can give privelge myself

SQL> Grant select on v$mystat to public;
Grant select on v$mystat to public
                *
ERROR at line 1:
ORA-01031: insufficient privileges
Re: MY$STATS TABLE PROBLEM [message #290679 is a reply to message #290673] Mon, 31 December 2007 01:45 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ask your DBA.
Re: MY$STATS TABLE PROBLEM [message #290683 is a reply to message #290673] Mon, 31 December 2007 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FYI, you have to be connected as SYS and use:
grant select on v_$mystat to public;

Regards
Michel

Re: MY$STATS TABLE PROBLEM [message #290687 is a reply to message #290663] Mon, 31 December 2007 01:58 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


where to find the default user name and password like SYS/DBA or
SYSTEM/MANAGER so that i can give privelge myself


If are trying this in your company , why do you want to take the risk of invading into the DBA secrets ? Better you check your DBA.

Thumbs Up
Rajuvan
Re: MY$STATS TABLE PROBLEM [message #290689 is a reply to message #290683] Mon, 31 December 2007 01:58 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But, he doesn't have the required credentials; I believe he'll have to ask DBA to do it for him.
Quote:

Where to find the default user name and password like SYS/DBA or SYSTEM/MANAGER so that i can give privelge myself?
Re: MY$STATS TABLE PROBLEM [message #290694 is a reply to message #290673] Mon, 31 December 2007 02:18 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

where to find the default user name and password like SYS/DBA or SYSTEM/MANAGER so that i can give privelge myself

If your DBA is a DBA then default passwords are useless.
If you don't know the password then you are not allowed to use the account.
In many companies if you do this then you are fired and prosecuted.

Regards
Michel
Previous Topic: NUmeric format
Next Topic: Usage of Cluster
Goto Forum:
  


Current Time: Fri Dec 02 20:56:01 CST 2016

Total time taken to generate the page: 0.10416 seconds