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;
/
