EBS中如何后台批量更改Profiles的值

来源：互联网发布：four loko 淘宝编辑：程序博客网时间：2024/05/16 11:03

自己写来管理自己负责维护的EBS instance，经常有人设置了一些调试用的profile忘记关闭，结果导致服务器性能变差，可用空间变小，所以写了这个脚本。

1.如果要增加修改的profile, 需要同时在l_profNames 增加变量名，在 l_profNewVal增加变量值。

2.l_profSpeUser，如果只更改一个用户的profile值，就需要设置这个用户名，必须大写，而且这个值拥有最高优先级。

3.l_profSpeResp，如果只更改一个职责的profile值，就需要设置这个职责名，而且这个值拥有第二高优先级。

4.l_profSpeApp，如果只更改一个应用的profile值，就需要设置这个用户名，必须大写，而且这个值拥有第三最高优先级。

5.如果即不指明用户，职责和应用，那就对所有层次更新所有层次的profiles。

6.l_profExceptUser, 如果对所有层次更新profiles，那么这里指定的用户会被忽略，不会被更新，用户名要大写。

7.l_profIncludeSite，如果对所有层次更新profiles的时候，这个变量指明会不会更新站点层次的值。'N'是不更新,'Y'是更新。

/***  Batch update profile values from back end. Mainly used to*  enable/disable debug profiles on dev instance*  Author:tavor*  Date:2014-01-08*  Revision:1*********************************************************************************************** IF YOU ARE NOT INSTANCE OWNER/ADMINISTRATOR, PLEASE BE CAREFUL TO RUN THIS SCRIPT***** *******************************************************************************************    *  1.If need to add more profile, need to specify profile name in l_profNames*    and new value in l_profNewVal.*  2.l_profSpeUser is the user we just update profile for him/her, and this has highest priority.*  3.l_profSpeResp is the resp we just update profile for it, and this has less higher priority.*  4.l_profSpeApp is the App we just update profile for it, and this has less the third high priority.*  5.If l_profSpeUser, l_profSpeResp and l_profSpeApp are all null, then we will update profiles for *    all resp, user and app level.*  6.l_profExceptUser, If we update profiles for all levels, we will not update profile values *    for this user.*  7.l_profIncludeSite, when we update profiels for all levels, this variable indidate if we also*    update profile values at site level. 'N', will not udpate for site for level, 'Y' for the opposite.**/DECLARE--l_profCount NUMBER :=3;TYPE HWDebugType IS VARRAY(10) OF VARCHAR2(240);l_profNames HWDebugType;l_profIDs HWDebugType;l_profNewVal HWDebugType;l_profIncSite HWDebugType;l_profAllLevel HWDebugType;l_profSpeUser VARCHAR2(20) := 'HONWEI1';--this should be in upper casel_profSpeUserID NUMBER := NULL;l_profSpeResp VARCHAR2(20) := NULL;l_profSpeRespID NUMBER := NULL;l_profSpeApp VARCHAR2(20) := NULL;l_profSpeAppID NUMBER := NULL;l_profExceptUser VARCHAR2(20) := NULL;--this should be in upper casel_profExceptUserID NUMBER :=NULL;l_profIncludeSite VARCHAR2(5) := 'N';l_userLevel NUMBER := 10004;l_respLevel NUMBER := 10003;l_appLevel  NUMBER := 10002;l_siteLevel NUMBER := 10001;l_profileId NUMBER := NULL;l_space     VARCHAR2(20)  :='   ';CURSOR CURRENT_PROFILE_VALUES (p_profileName VARCHAR2) IS SELECT  a.profile_option_id,t.user_profile_option_name cProfileOption,         decode(a.level_id,     10001,     'Site',                                10002,     'Application',                                10003,     'Responsibility',                                10004,     'User') cLevel,         decode(a.level_id,     10001,     'Site',                                10002,     b.application_short_name,                                10003,     c.responsibility_key,                                10004,     d.user_name) cLevelValue,         a.profile_option_value cProfileValueFROM fnd_profile_option_values a,      fnd_application b,      fnd_responsibility c,      fnd_user d,      fnd_profile_options e,      fnd_profile_options_tl t WHERE a.profile_option_id = e.profile_option_id   AND e.profile_option_name = p_profileName   AND a.level_value = b.application_id(+)   AND a.level_value = c.responsibility_id(+)   AND a.level_value = d.user_id(+)   AND t.profile_option_name = e.profile_option_name   AND t.LANGUAGE = 'US'ORDER BY a.profile_option_id,e.profile_option_name, a.level_id DESC;l_curProfileValues CURRENT_PROFILE_VALUES%ROWTYPE;BEGIN  l_profNames := HWDebugType( 'INV_DEBUG_TRACE'    --                          --,'AFLOG_ENABLED'     --                          --,'ICX_FORMS_LAUNCHER'--                          );  l_profNewVal := HWDebugType( '2'    --                              --   ,'N'     --                              --   ,NULL--                                );  l_profIncSite := HWDebugType('Y'    --                                 ,'Y'     --                                 ,'N'--                                );  l_profAllLevel := HWDebugType('Y'    --                                  ,'Y'     --                                  ,'Y'--                                );  IF l_profSpeUser IS NOT NULL AND l_profSpeUserID IS NULL THEN     SELECT USER_ID     INTO   l_profSpeUserID     FROM   FND_USER     WHERE  user_name = l_profSpeUser;  END IF;  Dbms_Output.put_line('Specified User:'||l_profSpeUser||', ID:'||l_profSpeUserID);  IF l_profExceptUser IS NOT NULL AND l_profExceptUserID IS NULL THEN     SELECT USER_ID     INTO   l_profExceptUserID     FROM   FND_USER     WHERE  user_name = l_profExceptUser;  END IF;  Dbms_Output.put_line('Except for User:'||l_profExceptUser||', ID:'||l_profExceptUserID);  IF l_profSpeResp IS NOT NULL AND l_profSpeRespID IS NULL THEN     SELECT responsibility_id     INTO   l_profSpeRespID     FROM   FND_RESPONSIBILITY_VL     WHERE  responsibility_name =l_profSpeResp;  END IF;  Dbms_Output.put_line('specified Responsibility:'||l_profSpeResp||', ID:'||l_profSpeRespID);  IF l_profSpeApp IS NOT NULL AND l_profSpeAppID IS NULL THEN     SELECT APPLICATION_ID     INTO   l_profSpeAppID     FROM   FND_APPLICATION_VL     WHERE  APPLICATION_NAME = l_profSpeApp;  END IF;  Dbms_Output.put_line('specified Application:'||l_profSpeApp||', ID:'||l_profSpeAppID);  FOR i IN 1..l_profNames.count loop    Dbms_Output.put_line('profile name:'||l_profNames(i));    Dbms_Output.put_line('Profile Option'||l_space||'Level'||l_space||'Level Value'||l_space||'Profile Value');    FOR currProf_rec IN CURRENT_PROFILE_VALUES(l_profNames(i)) LOOP        Dbms_Output.put_line(currProf_rec.cProfileOption||l_space||currProf_rec.cLevel||l_space||currProf_rec.cLevelValue||l_space||currProf_rec.cProfileValue);    END LOOP;    SELECT profile_option_id    INTO   l_profileId    FROM   fnd_profile_options    WHERE  profile_option_name = l_profNames(i);    if l_profSpeUserID is NOT null THEN    UPDATE fnd_profile_option_values    SET    profile_option_value = l_profNewVal(i) -- profile value    WHERE  profile_option_id = l_profileId        -- profile id    AND    level_id    = l_userLevel              -- level  id    AND    level_value = l_profSpeUserID          -- level value    AND    profile_option_value <> l_profNewVal(i);    Dbms_Output.put_line('Have set profiles at user level for userID:'||l_profSpeUserID);    elsif l_profSpeRespID is NOT null THEN    UPDATE fnd_profile_option_values    SET    profile_option_value = l_profNewVal(i) -- profile value    WHERE  profile_option_id = l_profileId        -- profile id    AND    level_id    = l_respLevel              -- level  id    AND    level_value = l_profSpeRespID          -- level value    AND    profile_option_value <> l_profNewVal(i);    Dbms_Output.put_line('Have set profiles at resp level for respID:'||l_profSpeRespID);    elsif l_profSpeAppID is NOT null THEN    UPDATE fnd_profile_option_values    SET    profile_option_value = l_profNewVal(i) -- profile value    WHERE  profile_option_id = l_profileId        -- profile id    AND    level_id    = l_appLevel               -- level  id    AND    level_value = l_profSpeAppID           -- level value    AND    profile_option_value <> l_profNewVal(i);    Dbms_Output.put_line('Have set profiles at app level for appID:'||l_profSpeAppID);    ELSE          UPDATE fnd_profile_option_values          SET    profile_option_value = l_profNewVal(i) -- profile value          WHERE  profile_option_id = l_profileId        -- profile id          AND    profile_option_value <> l_profNewVal(i)          AND    level_id <> Decode(l_profIncludeSite, 'Y', -1, l_siteLevel)          AND    (  (l_profExceptUserID IS NOT NULL AND (    (level_value <> l_profExceptUserID AND level_id = l_userLevel)                                                          OR (level_id <> l_userLevel)))                  OR l_profExceptUserID IS NULL);          Dbms_Output.put_line('Have set profiles for all level, l_profIncludeSite:'||l_profIncludeSite||',l_profExceptUserID:'||l_profExceptUserID);    end if;    Dbms_Output.put_line('After update profile value for profile name:'||l_profNames(i));    Dbms_Output.put_line('Profile Option'||l_space||'Level'||l_space||'Level Value'||l_space||'Profile Value');    FOR currProf_rec IN CURRENT_PROFILE_VALUES(l_profNames(i)) LOOP        Dbms_Output.put_line(currProf_rec.cProfileOption||l_space||currProf_rec.cLevel||l_space||currProf_rec.cLevelValue||l_space||currProf_rec.cProfileValue);    END LOOP;  END LOOP;EXCEPTION    WHEN OTHERS THEN    Dbms_Output.put_line('SQLCODE:'||SQLCODE||', SQLERRM:'||SQLERRM);END;

0 0