Hello everyone.
We are using oracle 11r1 RAC.
I have an issue with dictionary views. I am able to select, explain plan, and execute pl/sql blocks using v$views. But at the same time I can not create stored procedure/view using the views. The following code runs fine:
BEGIN
FOR x IN (SELECT
0 snapid
,'STAT...' || sn.name
,ss.VALUE
FROM
v$statname sn
,v$sesstat ss
WHERE
sn.statistic# = ss.statistic#
AND ss.sid = 123)
LOOP
NULL;
END LOOP;
END;
I can explain plan for a select statement with these view, thus I should have access to underlying objects:
EXPLAIN PLAN
FOR
SELECT
0 snapid
,'STAT...' || sn.name
,ss.VALUE
FROM
v$statname sn
,v$sesstat ss
WHERE
sn.statistic# = ss.statistic#
AND ss.sid = 123;
But I can not create a stored procedure:
CREATE OR REPLACE PROCEDURE init IS
BEGIN
FOR x IN (SELECT
0 snapid
,'STAT...' || sn.name
,ss.VALUE
FROM
v$statname sn
,v$sesstat ss
WHERE
sn.statistic# = ss.statistic#
AND ss.sid = 123)
LOOP
NULL;
END LOOP;
END;
It returns ORA-00942: table or view does not exist.
Neither can I create a view. I have privileges to create views and procedures.
My question is why?
Thank you in advance.