3 Replies Latest reply: Jul 31, 2013 12:32 PM by EdStevens RSS

    DBA_FREE_SPACE reference in stored procedure

    andy_schnelle

      Hi all,

       

      I tried googling around and could not find a solution.  I am referencing the DBA_FREE_SPACE system view in a stored procedure.  I received an error for insuffiient privileges for that particular piece of the stored proc.  I then explicity granted SELECT on the system view to the user schema which holds the stored proc.  I still am getting the insufficient privileges error and can not figure out why.  Has anyone experienced this or do you have any ideas I can try?

       

      Thanks in advance.

        • 1. Re: DBA_FREE_SPACE reference in stored procedure
          Suri

          Hi,

           

          Please execute SELECT * FROM dba_free_space  ? Are you able to select the data ?

           

          Cheers,

          Suri

          • 2. Re: DBA_FREE_SPACE reference in stored procedure
            EdStevens

            andy_schnelle wrote:

             

            Hi all,

             

            I tried googling around and could not find a solution.  I am referencing the DBA_FREE_SPACE system view in a stored procedure.  I received an error for insuffiient privileges for that particular piece of the stored proc.  I then explicity granted SELECT on the system view to the user schema which holds the stored proc.  I still am getting the insufficient privileges error and can not figure out why.  Has anyone experienced this or do you have any ideas I can try?

             

            Thanks in advance.

             

            Maybe we should see your code and the exact error message, not your interpretation of the error.  Typically, not having SELECT privilge on a table or view will return 'ORA-00942: table or view does not exist'. 

             

             

            SQL> conn scott/tiger

            ERROR:

            ORA-28001: the password has expired

             

             

            Changing password for scott

            New password:

            Retype new password:

            Password changed

            Connected.

            SQL> select * from dba_free_space;

            select * from dba_free_space

                          *

            ERROR at line 1:

            ORA-00942: table or view does not exist

             

             

            SQL> conn / as sysdba

            Connected.

            SQL> grant select on dba_free_space to scott;

             

            Grant succeeded.

             

            SQL> conn scott/lion

            Connected.

            SQL> select * from dba_free_space;

             

            TABLESPACE_NAME   FILE_ID   BLOCK_ID  BYTES     BLOCKS

            ------------------------------ ---------- ---------- ---------- ----------

            RELATIVE_FNO

            ------------

            • 3. Re: DBA_FREE_SPACE reference in stored procedure
              andy_schnelle

              Hi,

               

              Thanks for your help, I was able to figure this out.  The particular user name needed additional system privileges granted.