Oracle: how to clone profiles

Here is the simple script for cloning the Oracle profiles.
[cc lang=”oracle8″]
set serveroutput on

declare
cursor c_profiles is
select PROFILE, RESOURCE_NAME, LIMIT
from dba_profiles
order by profile, resource_name;

s_PROFILE dba_profiles.PROFILE%type ;
s_prev_PROFILE dba_profiles.PROFILE%type ;
s_RESOURCE_NAME dba_profiles.RESOURCE_NAME%type ;
s_LIMIT dba_profiles.LIMIT%type ;
begin

s_prev_PROFILE := ‘no_such_profile’ ;

dbms_output.enable(1000000);
open c_profiles;
loop
fetch c_profiles into s_PROFILE,s_RESOURCE_NAME,s_LIMIT ;
if ( s_prev_profile <> s_profile ) then
begin
dbms_output.put_line ( ‘–‘);
dbms_output.put_line ( ‘create profile “‘||s_profile||'” limit ‘ ||s_RESOURCE_NAME|| ‘ ‘ || s_LIMIT||’;’ ) ;
s_prev_profile := s_profile ;
end;
else
dbms_output.put_line ( ‘alter profile “‘||s_profile|| ‘” limit ‘ ||s_RESOURCE_NAME|| ‘ ‘ || s_LIMIT || ‘;’ ) ;
end if;
exit when c_profiles%NOTFOUND ;
end loop ;

close c_profiles ;

end;
/
[/cc]


Output example:

@clone_profile

create profile “DEFAULT” limit COMPOSITE_LIMIT UNLIMITED;
alter profile “DEFAULT” limit CONNECT_TIME UNLIMITED;
alter profile “DEFAULT” limit CPU_PER_CALL UNLIMITED;
alter profile “DEFAULT” limit CPU_PER_SESSION UNLIMITED;
alter profile “DEFAULT” limit FAILED_LOGIN_ATTEMPTS 10;
alter profile “DEFAULT” limit IDLE_TIME UNLIMITED;
alter profile “DEFAULT” limit LOGICAL_READS_PER_CALL UNLIMITED;
alter profile “DEFAULT” limit LOGICAL_READS_PER_SESSION UNLIMITED;
alter profile “DEFAULT” limit PASSWORD_GRACE_TIME 7;
alter profile “DEFAULT” limit PASSWORD_LIFE_TIME 180;
alter profile “DEFAULT” limit PASSWORD_LOCK_TIME 1;

create profile “MONITORING_PROFILE” limit COMPOSITE_LIMIT DEFAULT;
alter profile “MONITORING_PROFILE” limit FAILED_LOGIN_ATTEMPTS UNLIMITED;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.