V$TEMPFILE View in a Procedure Gives Error Even With the User Having a DBA Role 

01 SQL> grant dba to us1 identified by us1; 
02    
03 Grant succeeded. 
04    
05 SQL> conn us1/us1 
06 Connected. 
07 SQL> desc v$tempfile; 
08  Name                                      Null?    Type 
09  ----------------------------------------- -------- ---------------------------- 
10  FILE#                                              NUMBER 
11  CREATION_CHANGE#                                   NUMBER 
12  CREATION_TIME                                      DATE 
13  TS#                                                NUMBER 
14  RFILE#                                             NUMBER 
15  STATUS                                             VARCHAR2(7) 
16  ENABLED                                            VARCHAR2(10) 
17  BYTES                                              NUMBER 
18  BLOCKS                                             NUMBER 
19  CREATE_BYTES                                       NUMBER 
20  BLOCK_SIZE                                         NUMBER 
21  NAME                                               VARCHAR2(513) 
22    
23 SQL> create or replace procedure test 
24   2 is 
25   3 v_id number; 
26   4 begin 
27   5 select file# into v_id from v$tempfile; 
28   6 end; 
29   7 / 
30    
31 Warning: Procedure created with compilation errors. 
32    
33 SQL> show error 
34 Errors for PROCEDURE TEST: 
35    
36 LINE/COL ERROR 
37 -------- ----------------------------------------------------------------- 
38 5/1 PL/SQL: SQL Statement ignored 
39 5/29 PL/SQL: ORA-00942: table or view does not exist 
40 SQL> conn / as sysdba 
41 Connected. 
42 SQL> 
43 SQL> grant select on v$tempfile to us1; 
44 grant select on v$tempfile to us1 
45                 * 
46 ERROR at line 1: 
47 ORA-02030: can only select from fixed tables/views 
48    
49    
50 SQL> grant select on v_$tempfile to us1; 
51    
52 Grant succeeded. 
53    
54 SQL> conn us1/us1 
55 Connected. 
56 SQL> 
57 SQL> create or replace procedure test 
58   2 is 
59   3 v_id number; 
60   4 begin 
61   5 select file# into v_id from sys.v_$tempfile; 
62   6 end; 
63   7 / 
64    
65 Procedure created. 
66    
67 SQL> show error 
68 No errors. 
69 SQL> 
70     
        

The reason for the ORA-02030 errir 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. The following queries confirm:

01 SQL> select object_type from dba_objects where object_NAME='V$TEMPFILE'; 
02   
03 OBJECT_TYPE 
04 ------------------- 
05 SYNONYM 
06   
07 SQL> select table_name from dba_synonyms where synonym_name='V$TEMPFILE'; 
08   
09 TABLE_NAME 
10 ------------------------------ 
11 V_$TEMPFILE 
12   
13 SQL>