SET SERVEROUTPUT ON
DECLARE
cur SYS_REFCURSOR;
r INTEGER;
BEGIN
FOR rec_parts IN (
SELECT TABLE_NAME,subpartition_name,HIGH_VALUE
FROM user_tab_subpartitions
ORDER BY TABLE_NAME,SUBPARTITION_POSITION
)
LOOP
IF rec_parts.HIGH_VALUE NOT IN ('DEFAULT','MAXVALUE') THEN
OPEN cur FOR 'SELECT ROWNUM FROM '||rec_parts.TABLE_NAME||' SUBPARTITION ('||rec_parts.subpartition_name||') WHERE ROWNUM <= 1';
FETCH cur INTO r;
IF cur%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE ('No records found for '||rec_parts.TABLE_NAME||' SUBPARTITION ('||rec_parts.subpartition_name||')');
END IF;
CLOSE cur;
END IF;
END LOOP;
END;
/
DECLARE
cur SYS_REFCURSOR;
r INTEGER;
BEGIN
FOR rec_parts IN (
SELECT TABLE_NAME,subpartition_name,HIGH_VALUE
FROM user_tab_subpartitions
ORDER BY TABLE_NAME,SUBPARTITION_POSITION
)
LOOP
IF rec_parts.HIGH_VALUE NOT IN ('DEFAULT','MAXVALUE') THEN
OPEN cur FOR 'SELECT ROWNUM FROM '||rec_parts.TABLE_NAME||' SUBPARTITION ('||rec_parts.subpartition_name||') WHERE ROWNUM <= 1';
FETCH cur INTO r;
IF cur%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE ('No records found for '||rec_parts.TABLE_NAME||' SUBPARTITION ('||rec_parts.subpartition_name||')');
END IF;
CLOSE cur;
END IF;
END LOOP;
END;
/
No comments:
Post a Comment