web analytics

Oracle: how to clone profiles

Here is the simple script for cloning the Oracle profiles.

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


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

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.