User Creation using Network Link

------------Create profile

BEGIN

FOR cur_rec IN (SELECT distinct profile,Resource_name,limit

FROM dba_profiles@lnk_previousyearbalance where profile!='DEFAULT') LOOP

BEGIN

EXECUTE IMMEDIATE 'Create profile '|| cur_rec.profile ||' Limit '|| cur_rec.Resource_name ||' '|| cur_rec.Limit ;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/

------------Alter profile

BEGIN

FOR cur_rec IN (SELECT distinct profile,Resource_name,limit

FROM dba_profiles@lnk_previousyearbalance where profile!='DEFAULT' and Limit!='DEFAULT') LOOP

BEGIN

EXECUTE IMMEDIATE 'Alter profile '|| cur_rec.profile ||' Limit '|| cur_rec.Resource_name ||' '|| cur_rec.Limit ;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/

------------Create User

BEGIN

FOR cur_rec IN (SELECT username,password,default_tablespace,temporary_tablespace,profile

FROM dba_users@lnk_previousyearbalance where username!='SYSTEM' and Username!='SYS' and Username!='DBSNMP' and Username!='REPADMIN') LOOP

BEGIN

EXECUTE IMMEDIATE 'create user ' || cur_rec.username || ' identified by values ' || '''' || cur_rec.password || '''' || ' default tablespace ' || cur_rec.default_tablespace || ' temporary tablespace ' || cur_rec.temporary_tablespace || ' profile ' || cur_rec.profile;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/