Kamran Agayev's Oracle Blog

Oracle Certified Master

V$TEMPFILE view in a procedure gives error even having DBA role

Posted by Kamran Agayev A. on February 1st, 2011

An interesting issue I’ve seen today in OTN forum, where OP was asking why it is not possible to query V$TEMPFILE view in a procedure when he was able to query it outside a procedure. I’ve made a little demonstration and would like to get your opinion as well

[code]

SQL> grant dba to us1 identified by us1;
 
Grant succeeded.
 
SQL> conn us1/us1
Connected.
SQL> desc v$tempfile;
 Name                                      Null?    Type
 —————————————– ——– —————————-
 FILE#                                              NUMBER
 CREATION_CHANGE#                                   NUMBER
 CREATION_TIME                                      DATE
 TS#                                                NUMBER
 RFILE#                                             NUMBER
 STATUS                                             VARCHAR2(7)
 ENABLED                                            VARCHAR2(10)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 CREATE_BYTES                                       NUMBER
 BLOCK_SIZE                                         NUMBER
 NAME                                               VARCHAR2(513)
 
SQL> create or replace procedure test
  2  is
  3  v_id number;
  4  begin
  5  select file# into v_id from v$tempfile;
  6  end;
  7  /
 
Warning: Procedure created with compilation errors.
 
SQL> show error
Errors for PROCEDURE TEST:
 
LINE/COL ERROR
——– —————————————————————–
5/1      PL/SQL: SQL Statement ignored
5/29     PL/SQL: ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL>
SQL> grant select on v$tempfile to us1;
grant select on v$tempfile to us1
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
 
 
SQL> grant select on v_$tempfile to us1;
 
Grant succeeded.
 
SQL> conn us1/us1 
Connected.
SQL>
SQL> create or replace procedure test
  2  is
  3  v_id number;
  4  begin
  5  select file# into v_id from sys.v_$tempfile;
  6  end;
  7  /
 
Procedure created.
 
SQL> show error
No errors.
SQL>

 [/code]

The reason why I got an error ORA-02030 and why I’ve granted SELECT privilege to not V$TEMPFILE, but V_$TEMPFILE, is because V$TEMPFILE is a synonym created on the base view V_$TEMPFILE. Here’s the proof:

[code]

SQL> select object_type from dba_objects where object_NAME=’V$TEMPFILE’;

OBJECT_TYPE
——————-
SYNONYM

SQL> select table_name from dba_synonyms where synonym_name=’V$TEMPFILE’;

TABLE_NAME
——————————
V_$TEMPFILE

SQL>

[/code]

For more information, check the following MOS:

ORA-02030 WHEN GRANTING SELECT ON V$ VIEW [ID 1061103.6]

3 Responses to “V$TEMPFILE view in a procedure gives error even having DBA role”

  1. Aman.... Says:

    Its because that from within the stored programs, the privs that have come via a role doesn’t work. This is what you have done as well by granting the user DBA role. So when the user tried to access the object within the stored programs, it didn’t work.

    Aman….

  2. mohsin Says:

    so u mean that outside procedure whatever privs u have does not work inside proc. So can we grant that dba role or privs to the user inside the proc and then use the select …. does that work?

  3. Kamran Agayev A. Says:

    You can enable the DBA role implicitily in the procedure

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>