Stored procedure seems to fail for only SYSTEM
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Stored procedure seems to fail for only SYSTEM

  1. #1
    Join Date
    Aug 2008
    Posts
    3

    Stored procedure seems to fail for only SYSTEM

    Hi Guys Iam trying to create a stored procedure which will create datafiles whenever the freespace in a tablespace is below an agreed threshold. The procedure gets created without any issues under sysman and sysdba but seems to have problems when I create under SYSTEM. I just don't seem to understand why. The error that I get is:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    32/9 PL/SQL: SQL Statement ignored
    39/17 PL/SQL: ORA-00903: invalid table name

    But when I execute the same SQL statement that I used for the cursor, it works fine. I don't understand why it is not able to select out of the tables. I even tried putting sys infront of the table and creating private synonyms under system for these tables. I have attached the script below, let me know if I am missing anything obvious.

    create or replace procedure
    cr8_data_file (p_ts_free_threshold NUMBER, --Input the threshold for freespace in a tablespace.
    p_init_file_size NUMBER, --Input the size for initial file size.
    p_next_ext_size NUMBER, --Input the size for the next extent.
    p_max_file_size NUMBER) --Input the size for the maximum file size.
    AUTHID DEFINER --So that users with execute privileges can create datafiles.
    AS
    /**************************************************************************************************** ****
    * Description: This procedure can be used to create a datafiles in permanent tablespaces whenever a *
    * freespace threshold is breached. The procedure assumes that the initialization parameter*
    * "db_create_file_dest" has been set appropriately and there is enough free space. *
    * *
    * Author: Bethi *
    * Date: 18/August/2008 *
    * Version: 1.0 *
    **************************************************************************************************** *****
    * Change History *
    **************************************************************************************************** *****
    * Who * What * When * Version *
    **************************************************************************************************** *****
    * * * * *
    **************************************************************************************************** ****/
    v_ts_free_threshold NUMBER;
    v_tbs VARCHAR2(500); --For tablespace Name.
    v_init_file_size VARCHAR2(50); --Input the size for initial file size.
    v_next_ext_size VARCHAR2(50); --Input the size for the next extent.
    v_max_file_size VARCHAR2(50); --Input the size for the maximum file size.
    --
    --Cursor to get all tablespaces whose free space is less than the supplied threshold value.
    --
    CURSOR c_tbs IS
    SELECT f.tsn
    FROM (SELECT ddf.tablespace_name as tsn,
    ((SUM(DECODE(ddf.maxbytes,0,ddf.bytes,ddf.maxbytes))/1024)/1024)-
    ((SUM(ddf.bytes-dfs.bytes)/1024)/1024) AS fmb
    FROM dba_data_files ddf,
    dba_free_space dfs,
    dba_tablespaces dts
    WHERE ddf.tablespace_name = dfs.tablespace_name
    AND ddf.tablespace_name = dts.tablespace_name
    AND dts.contents = 'PERMANENT'
    AND ddf.file_id = dfs.file_id
    GROUP BY ddf.tablespace_name
    ORDER BY ddf.tablespace_name) f
    WHERE f.fmb < v_ts_free_threshold;

    BEGIN
    v_ts_free_threshold:= p_ts_free_threshold;
    v_init_file_size:=p_init_file_size||'M';
    v_next_ext_size:=p_next_ext_size||'M';
    v_max_file_size:=p_max_file_size||'M';

    OPEN c_tbs;
    LOOP
    FETCH c_tbs INTO v_tbs;
    EXIT WHEN c_tbs%NOTFOUND;
    --
    --Create datafiles in all the tablespaces whose free space is less than the free space threshold.
    --
    EXECUTE IMMEDIATE 'ALTER TABLESPACE '||v_tbs
    ||' ADD DATAFILE SIZE '||v_init_file_size
    ||' AUTOEXTEND ON NEXT '||v_next_ext_size
    ||' MAXSIZE '||v_max_file_size;
    END LOOP;
    CLOSE c_tbs;
    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    because system doesnt have access to the tables direectly, only via a role - you need to grant access directly but i wouldnt use sys or system becuase they are owned by the database, create your own user which the right privleges

  3. #3
    Join Date
    Aug 2008
    Posts
    3
    Thanks Dave.

    What I still dont understand is, how can I execute the same select statement from SQLPLUS logged in as system? But it doesn't work within a stored procedure? And what role are you reffering to?

    Please advice.

    Regards

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    roles are ignored in stored procedures, you need direct priveleges on the objects (the role that system has is dba)

  5. #5
    Join Date
    Aug 2008
    Posts
    3
    Thanks Dave

    That explains why i had trouble creating the procedure.

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width