spool dynprofile.out
set serveroutput on size 1000000
DECLARE
CURSOR get_profilename IS SELECT distinct profile FROM dba_profiles
WHERE profile NOT IN ('DEFAULT');
CURSOR get_profilelimits (p_profilelimit VARCHAR2) IS
SELECT * from dba_profiles
WHERE profile = p_profilelimit and limit not in ('UNLIMITED','DEFAULT');
l_str VARCHAR2(10);
BEGIN
FOR profile_rec IN get_profilename LOOP
dbms_output.put_line ('CREATE PROFILE '||profile_rec.profile);
-- For each profile loop through the limits
FOR limit_rec IN get_profilelimits (profile_rec.profile)
LOOP
IF get_profilelimits%ROWCOUNT = 1 THEN
l_str := 'LIMIT';
ELSE
l_str := ' ';
END IF;
dbms_output.put_line (l_str||' '
||limit_rec.resource_name||' '||limit_rec.limit);
END LOOP;
dbms_output.put_line (';');
dbms_output.put_line (' ');
dbms_output.new_line;
END LOOP;
END;
/
spool
spool off
Tags:
Oracle