Video Tutorial – Installing ASM on Linux and Windows

In this video tutorial I show how to install ASM on Linux and Windows operating systems

To download the .mp4 version of this file, use the following link

http://www.kamranagayev.com/Video_Tutorials/Installing_ASM_on_Linux_and_Windows.mp4

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

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

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>

 

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:

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>

For more information, check the following MOS:

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