Kamran Agayev's Oracle Blog

Oracle Certified Master

Archive for February, 2011

Video Tutorial – Installing ASM on Linux and Windows

Posted by Kamran Agayev A. on 7th February 2011

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

Posted in Administration | 26 Comments »

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

Posted by Kamran Agayev A. on 1st February 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]

Posted in Administration | 3 Comments »