Main problem is how to temporary disable unnecessary users a large number of users and still leave fully enable application for chosen one. This is real situation in a case of big roll outs or migration events, when some real APPS users need to have 100% of application usage to continue their normal work in APPS (as testing user for an example). And the best of all you do not have to restart any part of APPS...all is going online!
Our situation was even worse, because we have APPS based on several org_id's (represented here with set_of_books_id and we had to take care of those users as well. So you may find this approach pretty universal. This example was fully tested on Oracle EBS 11.5.10.2 for more then dozen times with 100% success.
Requirements
In my praxis I have very often need to temporary save some data. For that I use two kinds of pure Oracle tables defined as:create table xxhy_table_ch (c1 varchar2(4000) ); create table xxhy_table_num (n1 number);As you can see this tables has just one column (VARCHAR2 and NUMBER). Create them in "XX" schema and grant all to APPS user, as you do in usual cases.
Before any work I need to cleanup them:
truncate table xxhy_table_ch drop storage; truncate table xxhy_table_num drop storage;
For some less experienced DBA let me express that all scripts must be run as APPS user for what you need certain level of security.
Regardless I wrote here these two steps as required, both of them can be avoid for core functioning but I find it suitable and shown them as mine approach.
Initialization
In this example I'll use mine list2table function, which can be found on of my previous post Treat lists as table. This function will allow me to save initial list of users in plain Oracle table, leaving that all other scripts has static values. Static scripts could be executed by any Oracle related person...not APPS DBA only, what reduce single point of failure in such a situations...Next initialization step is to insert user names, which should stay enabled, in xxhy_table_ch table. This is the only part which must be changed because not all users are the same for every event so manual editing is needed.
insert into xxhy_table_ch
SELECT * FROM TABLE(list2table('SCOTTT,JOHNYB,TOMP2,ALENG,DJINAZ,CARLM,ROBERTD'));
As you can see, parameter for list2table function are user names that should stay enabled so change them as you need.For the end of initialization it is very important to save(remember) the number of enabled users before we have modify their status. For saving this value I use my second xxhy_table_num table:
INSERT INTO xxhy_table_num SELECT count(*) from HR.PER_ALL_ASSIGNMENTS_F P, FND_USER f where upper(user_name) not in (select c1 from xxhy_table_ch) and user_id >= 1110 and assignment_id=employee_id and (set_of_books_id = '1001' OR set_of_books_id = '1002' OR set_of_books_id = '1003') and (EFFECTIVE_END_DATE > SYSDATE); COMMIT;As you can see, in my case I had 3 different org_id's (3 set_of_books_id).
EBS system users
Users whose user_id is bellow 1110 are system ones and they should remain as they were. Really you do not want to mess with them!SQL> select user_id, user_name 2 from fnd_user 3 where user_id <= 1110; USER_ID USER_NAME ---------- ------------------------------ -1 ANONYMOUS 0 SYSADMIN 1 AUTOINSTALL 2 INITIAL SETUP 3 FEEDER SYSTEM 4 CONCURRENT MANAGER 5 APPSMGR 6 GUEST 7 WIZARD 1003 IEXADMIN 1004 IBE_GUEST 1005 OP_SYSADMIN 1006 OP_CUST_CARE_ADMIN 1007 ASGUEST 1008 IBE_ADMIN 1009 IBEGUEST 1010 ASGADM 1011 MOBILEADM 1012 IRC_EXT_GUEST 1013 IRC_EMP_GUEST 1030 PORTAL30 1031 PORTAL30_SSO 1050 XML_USER 1070 AME_INVALID_APPROVER 1090 MOBADM 1091 MOBDEV 26 rows selected. SQL>For those who want to be more safe, manual writing number is also a good way...
;-)
Disable users
This is script which will disable all users except mentioned one:update fnd_user set end_date = SYSDATE-1 where user_id in (select user_id from HR.PER_ALL_ASSIGNMENTS_F P, fnd_user f where upper(user_name) not in (select c1 from xxhy_table_ch) and user_id >= 1110 and assignment_id=employee_id and (set_of_books_id = '1001' OR set_of_books_id = '1002' OR set_of_books_id = '1003') and (EFFECTIVE_END_DATE > SYSDATE) ) ; COMMIT;It is also a good idea to save this number as well but I was not doing this. How ever slightly modify script (add returning clause which you have to save in xxhy_table_num table).
Now chosen users can work(test) with no worry that some other uninvited user may start unexpected concurrent or action... When all is done reverse process can be started....
Enable users
This is reverse script as one in the beginning.update FND_USER set end_date = null where user_id in (select user_id from HR.PER_ALL_ASSIGNMENTS_F P, fnd_user f where upper(user_name) not in (select c1 from xxhy_table_ch) and user_id >= 1110 and assignment_id=employee_id and (set_of_books_id = '1001' OR set_of_books_id = '1002' OR set_of_books_id = '1003') and (EFFECTIVE_END_DATE > SYSDATE) ); COMMIT;After this check number of enabled user which was stored in xxhy_table_num first step. For that repeat SELECT count(*) part from the beginning, which is not covered here because it's simplicity.
"Synchronize WF LOCAL tables" concurrent
For the end, last required step is to run Synchronize WF LOCAL tables concurrent program as sysadmin with System Administrator responsibility, which will synchronize WF tables according some recent changes we have made before.While you are disabling users you have some running session and want to terminate them, you can use scripts explained in my topic "Kill session on EBS environment".
Kill active APPS sessions
If you have need to kill all running APPS session on my blog Kill session on EBS environment you may find fully qualified information for that.Cheers!
