Hi Team,
Im mystified as to why the cur_rec.block_size cursor cannot be used within the decode section. Maybe it just cant be used as part of the decode. However the other identifiers are valid, ie b.maxextend and A.BYTES/1024/1024, b.maxextend etc.
The error I’m getting is ORA-00904: "CUR_REC"."BLOCK_SIZE": invalid identifier
Any idea why that error might happen?
Thanks for any insight.
john
Thanks for helping, the script is as follows:
CREATE OR REPLACE PROCEDURE tspinfo5
IS
l_sql varchar(2000);
v_sqlerrm varchar2(500);
BEGIN
delete from ORADBA.GXS_TABLESPACE_USAGE;
FOR cur_rec IN (SELECT DB_NAME,DB_LINK,BLOCK_SIZE
from MESSAGING_PROD_LIST
WHERE STATUS = 'ACTIVE'
ORDER BY 1)
LOOP
BEGIN
l_sql := 'INSERT INTO ORADBA.GXS_TABLESPACE_USAGE (TABLESPACE_NAME, CUR_MB, MAX_MB, TOTAL_USED, TOTAL_FREE, PERCENT_FREE, INSERT_TIME, DB_NAME)
(SELECT * FROM
(SELECT a.tablespace_name,
SUM(a.bytes)/1024/1024,
SUM(decode(b.maxextend, null, A.BYTES/1024/1024,b.maxextend*cur_rec.BLOCK_SIZE/1024/1024)), <-- ERROR HAPPENS DUE TO cur_rec_block_size not used.
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)),
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*cur_rec.BLOCK_SIZE/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))),round(100 - (100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*cur_rec.BLOCK_SIZE/1024/1024))))),
SYSDATE,
d.name FROM dba_data_files@' || cur_rec.db_link || ' a, sys.filext$@' || cur_rec.db_link || ' b, v$DATABASE@' || cur_rec.db_link || ' d,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free"
FROM dba_tablespaces@' || cur_rec.db_link || ' d,DBA_FREE_SPACE@' || cur_rec.db_link || ' c
WHERE d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
WHERE a.file_id = b.file#(+)
AND a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024,d.name
HAVING round(100 - (100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*cur_rec.BLOCK_SIZE/1024/1024))))) < 10
ORDER by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*cur_rec.BLOCK_SIZE/1024/1024)))) desc
))
';
EXECUTE IMMEDIATE l_sql;
EXCEPTION
WHEN OTHERS THEN
v_sqlerrm := substr(SQLERRM,1,90);
INSERT INTO oradba.GXS_TABLESPACE_USAGE (DB_NAME,ERROR_MESSAGE)
values (cur_rec.db_name, v_sqlerrm);
END;
COMMIT;
END LOOP;
END;
/