tag:blogger.com,1999:blog-11462313.post112180894735444659..comments2023-05-25T05:50:48.080-07:00Comments on Mike Ault's Blog: Tailoring Parameters in Oracle By UserMikehttp://www.blogger.com/profile/06863836374532649517noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-11462313.post-1124342044914035252005-08-17T22:14:00.000-07:002005-08-17T22:14:00.000-07:00OK, got it tested. The drill is:1) Create the res...OK, got it tested. The drill is:<BR/><BR/>1) Create the resource groups and assign them to whatever plan you want.<BR/><BR/>2) Create a user, set its default resource group to whatever you want with <BR/>exec DBMS_RESOURCE_MANAGER.<BR/>SET_INITIAL_CONSUMER_GROUP<BR/>('USER_YOU_WANT','GROUP_YOU_WANT');<BR/><BR/>3) Add a login trigger to the user to set current_schema=WHATEVER_SCHEMA;<BR/><BR/>And that's it. The user will login as USER_YOU_WANT, then the default schema will be WHATEVER_SCHEMA (you still have to grant USER_YOU_WANT access to WHATEVER_SCHEMA objects!) and the resource plan associated with GROUP_YOU_WANT will take effect.Noonshttps://www.blogger.com/profile/07694829378563989648noreply@blogger.comtag:blogger.com,1999:blog-11462313.post-1123753262850891422005-08-11T02:41:00.000-07:002005-08-11T02:41:00.000-07:00but you see what the problem is, don't you?The thi...but you see what the problem is, don't you?<BR/>The thing is: we often hear that to avoid excessive use of synonyms/grants, we can use the set curent_schema thing. <BR/>Yes we can. But aren't we shooting ourselves on the foot by doing that if we ever need to apply resource-manager?<BR/><BR/>I'm going to test this in a few days (just as soon as I finish putting out the current fires). Will report here what I find. Thanks for your comment.Noonshttps://www.blogger.com/profile/07694829378563989648noreply@blogger.comtag:blogger.com,1999:blog-11462313.post-1123694089929160452005-08-10T10:14:00.000-07:002005-08-10T10:14:00.000-07:00Nuno,I am not sure. If you "become" the schema the...Nuno,<BR/><BR/>I am not sure. If you "become" the schema then I would say yes, but if it is merely acting on their behalf probably not. I would have to experiment to see what happens.<BR/><BR/>MikeMikehttps://www.blogger.com/profile/06863836374532649517noreply@blogger.comtag:blogger.com,1999:blog-11462313.post-1123393423069120222005-08-06T22:43:00.000-07:002005-08-06T22:43:00.000-07:00Hey Tim: do you know if setting the current schema...Hey Tim: do you know if setting the current schema changes any resource manager allocations? Like, if I logon as user A which is part of resource group X and my logon trigger changes schema to B. Do I now now inherit B's resource group Y, or do I continue using X? Mike, do you know?<BR/>Sorry, I don't have a system on hand to test.Noonshttps://www.blogger.com/profile/07694829378563989648noreply@blogger.comtag:blogger.com,1999:blog-11462313.post-1122625046879050482005-07-29T01:17:00.000-07:002005-07-29T01:17:00.000-07:00The AFTER LOGON triggers are really useful. Like y...The AFTER LOGON triggers are really useful. Like you, I use them for setting the cursor_sharing parameter for 3rd part application connection.<BR/><BR/>I also find them useful for setting the current_schema parameter. Saves using loads of public or private synonyms.<BR/><BR/>Cheers<BR/><BR/>Tim...Tim...https://www.blogger.com/profile/17721555946005999179noreply@blogger.comtag:blogger.com,1999:blog-11462313.post-1122583820958574012005-07-28T13:50:00.000-07:002005-07-28T13:50:00.000-07:00You are corect! I edited this example to remove a ...You are corect! I edited this example to remove a second line that was there and forgot to remove this call...I'll fix it...<BR/><BR/>MikeMikehttps://www.blogger.com/profile/06863836374532649517noreply@blogger.comtag:blogger.com,1999:blog-11462313.post-1121904975135461022005-07-20T17:16:00.000-07:002005-07-20T17:16:00.000-07:00In the casre you mention, it would have whichever ...In the casre you mention, it would have whichever one it pulled from the table first. You are probably correct. I will correct the primary key.<BR/><BR/>Like I said at the bottom, this is just a barebones example, more error checking and such need to be added. I agree a call to see if the parameter is session modifiable is a good idea.<BR/><BR/><BR/>Thanks!<BR/><BR/>MikeMikehttps://www.blogger.com/profile/06863836374532649517noreply@blogger.comtag:blogger.com,1999:blog-11462313.post-1121894453457530912005-07-20T14:20:00.000-07:002005-07-20T14:20:00.000-07:00Ajay,Not sure what you mean. It creates multiple s...Ajay,<BR/><BR/>Not sure what you mean. It creates multiple settings for each user. You can have more than one parameter setting. It is designed for when multiple parameters are needed for one or more users.<BR/><BR/>It works.<BR/><BR/>MikeMikehttps://www.blogger.com/profile/06863836374532649517noreply@blogger.com