Re: 2 small tables - Query takes 3 hours
This view is part of the upgrade script provided by the Vendor (duh): I've been DBA'ing for 7 years in DB2 and Oracle and messing around with a SYS view is obviously not done- . I was looking for some proper advise as Ive never seen anything like it before. Sam - Original Message - Date: Sunday, July 27, 2003 11:39 am > People should not play with the SYS schema, period. No ifs, no buts, > it just isn't done. You are on your own, pal. If I were your employer, > you would have hit the road by now. Whatever application that Maximo > thing is, creating objects owned by SYS is simply not acceptable. > It's guys like you that give database administrators a bad name. > > > On 2003.07.27 01:29, [EMAIL PROTECTED] wrote: > > Unix Solaris 8 and Oracle 8.1.7.4 > > > > As part of an upgrade to the Maximo application I run a join on 2 > > tables: > > > > select count(*) from sys.syskeys s, maxsysindexes m where > > s.ixname=m.name; > > sys.syskeys is 705 rows and maxsysindexes is 443 rows. > > when I trace the statement I find it doing a hash join and it > estimates> 434 blocks and it does each block in 30 secs. > > > > Prior to running the query I create the sys.syskeys view as detailed > > below: > > The only way I get round the problem is to recreate the > > maxsysindexes table: And of course query then takes 1 second. I > > have disabled hash join and the query just goes down another path > > taking just as long. > > > > Any thoughts as this only happened once the first upgrade in 10 > > schemas, now its happening all the time. > > > > --schema owner > > > > DROP VIEW SYS.SYSKEYS; > > > > CREATE VIEW SYS.SYSKEYS > > (IXCREATOR, IXNAME, COLNAME, COLNO, COLSEQ, > > ORDERING, FUNCTION) > > AS > > SELECT IO.NAME, IDX.NAME, C.NAME, C.COL#, IC.POS#, 'A', '' > > FROM SYS.COL$ C, SYS.OBJ$ IDX, > > SYS.OBJ$ BASE, SYS.ICOL$ IC, > > SYS.USER$ IO, SYS.USER$ BO > > WHERE IO.NAME = 'ADWEA' AND BASE.OBJ# = C.OBJ# > > AND IC.COL# = C.COL# > > AND IC.BO# = BASE.OBJ# > > AND IO.USER# = IDX.OWNER# > > AND BO.USER# = BASE.OWNER# > > AND IC.OBJ# = IDX.OBJ# > > AND (IDX.OWNER# = UID OR > >BASE.OWNER# = UID > >OR > >BASE.OBJ# IN ( SELECT OBJ# > > from sys.objauth$ > > where grantee# in ( select kzsrorol > > from x$kzsro > >) > >) > >) > > ; > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services> - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE- L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > Mladen Gogala > Oracle DBA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > --- > -- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Antw: 8i ORA-1652 after upgrading from AIX 4.3.3 to 5.1
May not necessarily mean TEMP tablespace... Check all of your tablespaces for available free space, that is contiguous freespace. -Original Message- Guido Konsolke Sent: Sunday, July 27, 2003 3:04 AM To: Multiple recipients of list ORACLE-L Hi Saira, maybe temp tablespace isn't large enough. did you try what the doctor suggests? Did you enlarge the temp seg? 8-)) hth, Guido <<< [EMAIL PROTECTED] 27.07. 05.54 >>> Hi Gurus, After upgrading the OS from AIX 4.3.3 to 5.1, our Oracle 8i instance is dishing out plenty of ORA-1652 which means that our application cannot commit any transactions (or so I am finding in my tests). Help. The warehouse needs to be in production tomorrow! Thanks, Saira -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 8.1.7 instance on upgraded AIX machine - ORA-1652 all the time
There is a 64 bit version of Oracle 8.1.7 for AIX available. Check your $ORACLE_HOME/lib directory. If you have a lib64 or lib32 directory, then you have the 64bit version. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] ahoo.com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: 8.1.7 instance on upgraded AIX machine - ORA-1652 all ity.com the time 07/27/2003 01:24 AM Please respond to ORACLE-L We are using 32-bit on 5L. I don't think Oracle 8.1.7 is compatible with 64-bit AIX kernel. --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > Your 1652 error means the following: > > 01652, 0, "unable to extend temp segment by %s > in tablespace %s" > // *Cause: Failed to allocate an extent for temp > segment in tablespace. > // *Action: Use ALTER TABLESPACE ADD DATAFILE > statement to add one or more > // files to the tablespace indicated. > > If everything else is the same, I would say that the > new OS is using a > different OS filesystem block size. That would mess > up all your file sizes, > allocations and alike. You are probably using 64 bit > JFS on 5L, straight up > from 32-bit 4.3.3? > > > On 2003.07.27 00:39, Saira Somani wrote: > > Hi Gurus, > > > > After upgrading the OS from AIX 4.3.3 to 5.1, our > > Oracle 8i instance is dishing out plenty of > ORA-1652 > > which means that our application cannot commit any > > transactions (or so I am finding in my tests). > > > > Help. The warehouse needs to be in production > > tomorrow 8AM! > > > > Thanks, > > Saira > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: Saira Somani > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > San Diego, California-- Mailing list and > web hosting services > > > - > > To REMOVE yourself from this mailing list, send an > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > ORACLE-L > > (or the name of mailing list you want to be > removed from). You may > > also send the HELP command for other information > (like subscribing). > > > > -- > Mladen Gogala > Oracle DBA > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Mladen Gogala > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Maili
RE: 8i ORA-1652 after upgrading from AIX 4.3.3 to 5.1
As suggested, we checked all the tablespaces. TEMP space was increased from 2GB to 4GB and then to 6GB. We ended up bouncing the Oracle instance each time TEMP filled up because we really had nothing to lose. After doing this 3 maybe 4 times, we ran the same scenario in the application and this time (and a few times after that to be sure), TEMP did not fill up - only used 0.5%. Then we decreased TEMP to 2GB and everything still seemed to work. We had opened a severity level 1 TAR with Oracle and have now reduced it to level 2 because we don't have a case to present to them now. We don't really understand why this was happening or how the problem got fixed. Since today is a production day, we'll probably find out if it happens again. I know we weren't imagining the entire thing! Thank you all for your suggestions. They were extremely helpful. Saira --- John Blake <[EMAIL PROTECTED]> wrote: > May not necessarily mean TEMP tablespace... Check > all of your tablespaces > for available free space, that is contiguous > freespace. > > -Original Message- > Guido Konsolke > Sent: Sunday, July 27, 2003 3:04 AM > To: Multiple recipients of list ORACLE-L > > > Hi Saira, > > maybe temp tablespace isn't large enough. > did you try what the doctor suggests? > Did you enlarge the temp seg? > 8-)) > > hth, > Guido > > <<< [EMAIL PROTECTED] 27.07. 05.54 >>> > Hi Gurus, > > After upgrading the OS from AIX 4.3.3 to 5.1, our > Oracle 8i instance is dishing out plenty of ORA-1652 > which means that our application cannot commit any > transactions (or so I am finding in my tests). > > Help. The warehouse needs to be in production > tomorrow! > > Thanks, > Saira > -- > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Guido Konsolke > INET: > [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: John Blake > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 2 small tables - Query takes 3 hours
Sam Well, sometimes ya just gotta tell the vendor NO. I would start by explaining to the people in your organization and the vendor why this is a very bad idea, totally unsupported by Oracle. If due to the local politics this isn't feasible, make sure you keep this application on its own instance. Run the upgrade on a test instance first. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, July 27, 2003 4:29 AM To: Multiple recipients of list ORACLE-L This view is part of the upgrade script provided by the Vendor (duh): I've been DBA'ing for 7 years in DB2 and Oracle and messing around with a SYS view is obviously not done- . I was looking for some proper advise as Ive never seen anything like it before. Sam - Original Message - Date: Sunday, July 27, 2003 11:39 am > People should not play with the SYS schema, period. No ifs, no buts, > it just isn't done. You are on your own, pal. If I were your employer, > you would have hit the road by now. Whatever application that Maximo > thing is, creating objects owned by SYS is simply not acceptable. > It's guys like you that give database administrators a bad name. > > > On 2003.07.27 01:29, [EMAIL PROTECTED] wrote: > > Unix Solaris 8 and Oracle 8.1.7.4 > > > > As part of an upgrade to the Maximo application I run a join on 2 > > tables: > > > > select count(*) from sys.syskeys s, maxsysindexes m where > > s.ixname=m.name; > > sys.syskeys is 705 rows and maxsysindexes is 443 rows. > > when I trace the statement I find it doing a hash join and it > estimates> 434 blocks and it does each block in 30 secs. > > > > Prior to running the query I create the sys.syskeys view as detailed > > below: > > The only way I get round the problem is to recreate the > > maxsysindexes table: And of course query then takes 1 second. I > > have disabled hash join and the query just goes down another path > > taking just as long. > > > > Any thoughts as this only happened once the first upgrade in 10 > > schemas, now its happening all the time. > > > > --schema owner > > > > DROP VIEW SYS.SYSKEYS; > > > > CREATE VIEW SYS.SYSKEYS > > (IXCREATOR, IXNAME, COLNAME, COLNO, COLSEQ, > > ORDERING, FUNCTION) > > AS > > SELECT IO.NAME, IDX.NAME, C.NAME, C.COL#, IC.POS#, 'A', '' > > FROM SYS.COL$ C, SYS.OBJ$ IDX, > > SYS.OBJ$ BASE, SYS.ICOL$ IC, > > SYS.USER$ IO, SYS.USER$ BO > > WHERE IO.NAME = 'ADWEA' AND BASE.OBJ# = C.OBJ# > > AND IC.COL# = C.COL# > > AND IC.BO# = BASE.OBJ# > > AND IO.USER# = IDX.OWNER# > > AND BO.USER# = BASE.OWNER# > > AND IC.OBJ# = IDX.OBJ# > > AND (IDX.OWNER# = UID OR > >BASE.OWNER# = UID > >OR > >BASE.OBJ# IN ( SELECT OBJ# > > from sys.objauth$ > > where grantee# in ( select kzsrorol > > from x$kzsro > >) > >) > >) > > ; > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services> - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE- L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > Mladen Gogala > Oracle DBA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > --- > -- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list
Set Role in Trigger
Short form of my question: How can I enable a Role for a User within a database trigger (owned by another Schema) on a table owned by yet another Schema? - The M's I've RTF'd indicate that a trigger (and any procedure it calls) can never execute with Invoker's Rights. - I can't find a way to execute Set Role for a User as another User, say, System. - I'm stuck. - Environment: 8.1.7 on Win2k and HP-UX. Longer form of my question: I'm in the process of adding extra security features to our 3rd Party Student Information System, whose code I can't touch. I've successfully implemented FGAC to keep Users at a School from accessing info.at other schools. Now I need to limit which School Year's data they can update (Past, Current, Next). The Application grants Sel, Ins, Upd, Del on its tables via a Role, so I thought I'd just switch Roles when the User switched School Years (via updating her record in a Users table). Seemed like a good idea, but now I can't see how to implement it. SQL and PL/SQL commands like Set Role, Alter Session, DBMS_Session.Set_Role, etc. only apply to the current User, which would be the Trigger Owner. I've used DBMS_System.Set_SQL_Trace_In_Session, but can't find an equivalent procedure to Set Role for another User. BTW, the fact that there's no Invoker_Rights_Clause in the Create Trigger syntax and a section in the PL/SQL User's Guide and Reference (Ch. 7 Subprograms / Invoker Rights vs Definer Rights / Using Views and Database Triggers) are the basis for my being stuck. The only possible way I see to do this is to create the trigger as System, then use Dynamic SQL to issue the "Alter User ... Default Role " command. However, I don't know if that takes effect immediately (within the User's current Session) or would take effect at the User's next login. Before I spend a bunch of time setting up a test, I thought I'd get some opinions from this very knowledgeable List. Can I do it? How? TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED]
Re: Set Role in Trigger
Jack, Question - why do you want to use System triggers to change roles? I don't see how you can define an event for this trigger to be fired. If the event is the updating of record in USERS table, who updates it? Obviously not the user himeself; then the whole system becomes insecure to the core. Your choice of using a role to allow access to a specific yesr is sound design. I assume each year is stored in separate tables. If that is not correct, i.e. the record for all years are stored in a single table, then roles will not help you, you need to enable FGAC using something called application context. Scenario 1: Each year's data is stored in a seprate table. You would create roles, but not normal roles. The roles should be identified by a procedure. This procedure is owned by SYS or some other secued schema. Inside this procedure you would assign a value to the application context attribute called user_role, which is set via dbms_Session.set_context. All the users are revoked execute priv on dbms_session; so they will never be able to call this procedure directly. The only way they can do it is by calling the trusted procedure you have defined for that role. Scenario 2: All the tables have data for all the years. Inthis case you will have to use FGAC; but the FGAC policy will have to depend on the application context you defined earlier. You wil define another context attribute called school_year, whic is again set by the trusted procedure of the role. Since the user does not have the privilege to call dbms_Session, he will not be able to set the value of this attrbute to any other year at will. Summary: You will define several roles ideintified by procedure. All these roles are granted to the user but none is a default role. When a user logs in, all roles assigned to him are disabled, since none is a default role. Then he calls the procedure set_role(), no arguments. Inside the procedure set_role(), you will read the users table, see the role the user is supposed to have, enable this role via dbms_Session.set_role and then set the application context, if any. Since the user does not have execute privs on dbms_session, he will not be able to set the app context. Since the role is identified by a procedure, i.e. set_role(), the user will not be able to set the role himself using "SET ROLE" command in sql*plus. The only way he can do that is by calling the set_role procedure. If the user does not call the procedure, none of the roles are enabled; therefore he will not be able to do anything. In other words, you have a secured system. As an added bonus: you will have the application context attribute you can use any way you want to use. Right now you can use it for FGAC; but later when you are in 9i, you can use it in other cooler features such as Fine Grained Auditing. HTH. Arup Nanda www.proligence.com - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Sunday, July 27, 2003 11:59 AM Subject: Set Role in Trigger Short form of my question: How can I enable a Role for a User within a database trigger (owned by another Schema) on a table owned by yet another Schema? - The M's I've RTF'd indicate that a trigger (and any procedure it calls) can never execute with Invoker's Rights. - I can't find a way to execute Set Role for a User as another User, say, System. - I'm stuck. - Environment: 8.1.7 on Win2k and HP-UX. Longer form of my question: I'm in the process of adding extra security features to our 3rd Party Student Information System, whose code I can't touch. I've successfully implemented FGAC to keep Users at a School from accessing info.at other schools. Now I need to limit which School Year's data they can update (Past, Current, Next). The Application grants Sel, Ins, Upd, Del on its tables via a Role, so I thought I'd just switch Roles when the User switched School Years (via updating her record in a Users table). Seemed like a good idea, but now I can't see how to implement it. SQL and PL/SQL commands like Set Role, Alter Session, DBMS_Session.Set_Role, etc. only apply to the current User, which would be the Trigger Owner. I've used DBMS_System.Set_SQL_Trace_In_Session, but can't find an equivalent procedure to Set Role for another User. BTW, the fact that there's no Invoker_Rights_Clause in the Create Trigger syntax and a section in the PL/SQL User's Guide and Reference (Ch. 7 Subprograms / Invoker Rights vs Definer Rights / Using Views and Database Triggers) are the basis for my being stuck. The only possible way I see to do this is to create the trigger as System, then use Dynamic SQL to issue the "Alter User ... Default Role " command. However, I don't know if that takes effect immediately (within the User's current Session) or would take effect at the User's n
RE: Set Role in Trigger
This is probably too kludgy or simple-minded, or non-maintainable, but is it technically possible? 1) Create a series of views that subset the actual tables, according to the rules you've got about who the viewer is & what year(s) they've selected in the Users table. 2) Redefine the public synonyms so that they point to your views rather than the base tables. Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Sunday, July 27, 2003 11:24 AM To: Multiple recipients of list ORACLE-L Jack, Question - why do you want to use System triggers to change roles? I don't see how you can define an event for this trigger to be fired. If the event is the updating of record in USERS table, who updates it? Obviously not the user himeself; then the whole system becomes insecure to the core. Your choice of using a role to allow access to a specific yesr is sound design. I assume each year is stored in separate tables. If that is not correct, i.e. the record for all years are stored in a single table, then roles will not help you, you need to enable FGAC using something called application context. Scenario 1: Each year's data is stored in a seprate table. You would create roles, but not normal roles. The roles should be identified by a procedure. This procedure is owned by SYS or some other secued schema. Inside this procedure you would assign a value to the application context attribute called user_role, which is set via dbms_Session.set_context. All the users are revoked execute priv on dbms_session; so they will never be able to call this procedure directly. The only way they can do it is by calling the trusted procedure you have defined for that role. Scenario 2: All the tables have data for all the years. Inthis case you will have to use FGAC; but the FGAC policy will have to depend on the application context you defined earlier. You wil define another context attribute called school_year, whic is again set by the trusted procedure of the role. Since the user does not have the privilege to call dbms_Session, he will not be able to set the value of this attrbute to any other year at will. Summary: You will define several roles ideintified by procedure. All these roles are granted to the user but none is a default role. When a user logs in, all roles assigned to him are disabled, since none is a default role. Then he calls the procedure set_role(), no arguments. Inside the procedure set_role(), you will read the users table, see the role the user is supposed to have, enable this role via dbms_Session.set_role and then set the application context, if any. Since the user does not have execute privs on dbms_session, he will not be able to set the app context. Since the role is identified by a procedure, i.e. set_role(), the user will not be able to set the role himself using "SET ROLE" command in sql*plus. The only way he can do that is by calling the set_role procedure. If the user does not call the procedure, none of the roles are enabled; therefore he will not be able to do anything. In other words, you have a secured system. As an added bonus: you will have the application context attribute you can use any way you want to use. Right now you can use it for FGAC; but later when you are in 9i, you can use it in other cooler features such as Fine Grained Auditing. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L Sent: Sunday, July 27, 2003 11:59 AM Short form of my question: How can I enable a Role for a User within a database trigger (owned by another Schema) on a table owned by yet another Schema? - The M's I've RTF'd indicate that a trigger (and any procedure it calls) can never execute with Invoker's Rights. - I can't find a way to execute Set Role for a User as another User, say, System. - I'm stuck. - Environment: 8.1.7 on Win2k and HP-UX. Longer form of my question: I'm in the process of adding extra security features to our 3rd Party Student Information System, whose code I can't touch. I've successfully implemented FGAC to keep Users at a School from accessing info.at other schools. Now I need to limit which School Year's data they can update (Past, Current, Next). The Application grants Sel, Ins, Upd, Del on its tables via a Role, so I thought I'd just switch Roles when the User switched School Years (via updating her record in a Users table). Seemed like a good idea, but now I can't see how to implement it. SQL and PL/SQL commands like Set Role, Alter Session, DBMS_Session.Set_Role, etc. only apply to the current User, which would be the Trigger Owner. I've used DBMS_System.Set_SQL_Trace_In_Session, but can't find an equivalent procedure to Set Role for another User. BTW, the fact that there's no Invoker_Rights_Clause in the Create Trigger syntax and a section in the PL/SQL User'
Re: 2 small tables - Query takes 3 hours
If you have been a DBA for 7 years, then you know the procedure: Go to V$SESSION_WAIT and see what are you waiting for. You'll get file and block (P1 & P2), so you can locate the segment. Do explain plan and see how this segment is used. In my experience, it's usually an index used to retrieve the whole table. Then use the classic RBO methods to disable the index (||'',+0). What I object to is the casual fashion you used to talk about the data dictionary modifications. Sure, we've all done that but we've never bragged about it. It's like sex: that's how we all came in this world but it is rude to talk about that. Talking about dictionary modifications in "look mom, no hands" way makes us look like irresponsible hackers, which is not the image I'd like to project. Thus the rebuke. On 2003.07.27 05:29, [EMAIL PROTECTED] wrote: This view is part of the upgrade script provided by the Vendor (duh): I've been DBA'ing for 7 years in DB2 and Oracle and messing around with a SYS view is obviously not done- . I was looking for some proper advise as Ive never seen anything like it before. Sam - Original Message - Date: Sunday, July 27, 2003 11:39 am > People should not play with the SYS schema, period. No ifs, no buts, > it just isn't done. You are on your own, pal. If I were your employer, > you would have hit the road by now. Whatever application that Maximo > thing is, creating objects owned by SYS is simply not acceptable. > It's guys like you that give database administrators a bad name. > > > On 2003.07.27 01:29, [EMAIL PROTECTED] wrote: > > Unix Solaris 8 and Oracle 8.1.7.4 > > > > As part of an upgrade to the Maximo application I run a join on 2 > > tables: > > > > select count(*) from sys.syskeys s, maxsysindexes m where > > s.ixname=m.name; > > sys.syskeys is 705 rows and maxsysindexes is 443 rows. > > when I trace the statement I find it doing a hash join and it > estimates> 434 blocks and it does each block in 30 secs. > > > > Prior to running the query I create the sys.syskeys view as detailed > > below: > > The only way I get round the problem is to recreate the > > maxsysindexes table: And of course query then takes 1 second. I > > have disabled hash join and the query just goes down another path > > taking just as long. > > > > Any thoughts as this only happened once the first upgrade in 10 > > schemas, now its happening all the time. > > > > --schema owner > > > > DROP VIEW SYS.SYSKEYS; > > > > CREATE VIEW SYS.SYSKEYS > > (IXCREATOR, IXNAME, COLNAME, COLNO, COLSEQ, > > ORDERING, FUNCTION) > >AS > > SELECT IO.NAME, IDX.NAME, C.NAME, C.COL#, IC.POS#, 'A', '' > > FROM SYS.COL$ C, SYS.OBJ$ IDX, > > SYS.OBJ$ BASE, SYS.ICOL$ IC, > > SYS.USER$ IO, SYS.USER$ BO > > WHERE IO.NAME = 'ADWEA' AND BASE.OBJ# = C.OBJ# > > AND IC.COL# = C.COL# > > AND IC.BO# = BASE.OBJ# > > AND IO.USER# = IDX.OWNER# > > AND BO.USER# = BASE.OWNER# > > AND IC.OBJ# = IDX.OBJ# > > AND (IDX.OWNER# = UID OR > >BASE.OWNER# = UID > >OR > >BASE.OBJ# IN ( SELECT OBJ# > > from sys.objauth$ > > where grantee# in ( select kzsrorol > > from x$kzsro > >) > >) > >) > > ; > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services> - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE- L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > Mladen Gogala > Oracle DBA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > --- > -- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services ---
Re: RMAN/Veritas duplicate db article
Hmm... absolutely no responses. Perhaps a bit of clarification is in order. I'm interested in comments only on the content. This was a write as a you go paper and decidely not publishable as is. If it seems worthwhile, I will rewrite into something a little more acceptable than the very rough draft that it is. Thanks, Jared On Wednesday 23 July 2003 14:29, [EMAIL PROTECTED] wrote: > List, > > I've written a short ( compared to the length of time it took > to do this anyway ) article on how to create a duplicate database > on a remote server using RMAN and Veritas NetBackup. > > It can be found at: > >http://www.tinyurl.com/hu4r > > or at this one if you can't access tinyurl.com: > > > http://www.cybcon.com/~jkstill/Alternate%20Client%20Restore%20With%20Verita >s%20NetBackup%20and%20Oracle%20RMAN.doc > > It's kinda rough, so suggestions, comments, criticisms are welcome. > > Thanks, > > Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Question about the list
Thanks, I'll do that if I hit the problem again.. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, July 27, 2003 1:54 AM > > I own the list, that is, the logical entity. > > Bruce Bergman is owner of the ISP, fatcity.com, and > the sysadmin and mail admin. > > Don't know why some posts appear before others, could > be a simple explanation, could be a very complex one. > > Not being a mail expert, I don't know which it is. > > Best ask Bruce if you're curios - [EMAIL PROTECTED] > > Jared > > On Thursday 24 July 2003 15:29, Tanel Poder wrote: > > Hi! > > > > How does the list server here operate? > > I sent a reply to one message several hours ago (see below), but it never > > reached my mailbox again. Also, sometimes I get the reply first to my > > mailbox than the original post (I mean several minutes before, it couldn't > > be a SMTP latency issue). > > Is this list moderated somehow or what could be the cause? Or could it be > > related to timestamp of sent message (My morning arrives 7-9 hours earlier > > than in US). > > > > Kind of confused, > > Tanel. > > > > - Original Message - > > To: <[EMAIL PROTECTED]> > > Sent: Thursday, July 24, 2003 6:06 PM > > > > > Hi! > > > > > > About the second one, you see the first read started from 6041 and was 18 > > > blocks, the second one which was executed 7 minutes later, started from > > > > 6042 > > > > > and was 17 blocks. Probably the extent boundary is at block 6059. > > > > Multiblock > > > > > reads don't cross extent boundaries. > > > > > > So, probably your query acquired block 6041 with FTS and by the time it > > > needed next block, the blocks were already out of buffer cache (LRU list) > > > and another multiblock read was needed. Maybe you should look at keep > > > > buffer > > > > > pool, but since the time interval was long, it might not be necessary > > > anyway... > > > > > > Tanel. > > > - Original Message - > > > From: "Henry Poras" <[EMAIL PROTECTED]> > > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > > Sent: Thursday, July 24, 2003 6:04 PM > > > Subject: RE: Timestamps in trace files (and other trace file oddities) > > > > > > > (Tried sending this yesterday. I'll try again) > > > > > > > > Dan, > > > > I was running a 10046 (level 12) trace on an awful piece of PeopleSoft > > > > SQL > > > > > > today and got some really odd results in my trace file (8.1.7). > > > > > > > > *** 2003-07-23 15:40:59.149 > > > > WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6041 p3=18 > > > > *** 2003-07-23 15:46:06.340 > > > > WAIT #1: nam='latch free' ela= 1 p1=813986232 p2=66 p3=0 > > > > *** 2003-07-23 15:47:53.851 > > > > WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6042 p3=17 > > > > > > > > Two things struck me (three if it takes me too long to write this and I > > > > > > get > > > > > > > home late). First, the timestamps show an elapsed time of ~7 minutes, > > > > but > > > > > > the trace file has ela=1 (one onehundredth of a second). The 7 minutes > > > > is > > > > > > closer to reality. Huh??? > > > > > > > > Secondly, the first scattered read reads 18 blocks starting at 6041. > > > > Why does the next scattered read start at block# 6042? > > > > > > > > Any ideas? > > > > > > > > Henry > > > > > > > > > > > > > > > > -Original Message- > > > > Daniel Fink > > > > Sent: Wednesday, July 23, 2003 11:04 AM > > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > > > > Gudmundur, > > > > Where is this documented (so I can RTFM)? > > > > The one piece of this I don't quite understand is that the timestamp is > > > > > > not > > > > > > > emitted twice in a row. If the long time is the triggering event, why > > > > do > > > > I > > > > > > see a gap of 90 minutes (in another trace file)? > > > > > > > > Daniel > > > > > > > > Gudmundur Bjarni Josepsson wrote: > > > > > Daniel, > > > > > > > > > > Perhaps someone else can explain this better but the documentation > > > > I've > > > > > > > got on this says that the Oracle kernel emits timestamps when a long > > > > > time has elapsed since the last line was emitted to the trace file. > > > > > Long time is defined as tens of seconds. > > > > > > > > > > Gudmundur > > > > > > > > > > > -Original Message- > > > > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > > > > > > Behalf Of Daniel Fink > > > > > > Sent: 22. jślķ 2003 21:19 > > > > > > To: Multiple recipients of list ORACLE-L > > > > > > Subject: Timestamps in trace files > > > > > > > > > > > > > > > > > > I was perusing a 10046 trace file and I noticed that > > > > > > timestamps are written to the trace file. Sometimes they were > > > > > > very regular (3 minutes apart give or take 30 seconds) while > > > > > > other times they were hours apart. I have noticed that two > > > > > > timestamps are never written without any intervening > > > > > > activity. Anyone have any idea on the reasonin
Re: 8.1.7 instance on upgraded AIX machine - ORA-1652 all the time
Hi! I have a suggestion as well: Tempfiles are sparse files in unix, that is you can use alter tablespace command to add or resize the files even to 100G if you want, but they don't actually use much space on disk. When you start writing to this file, OS tries to allocate more real space to it and will fail if file system is full (depending on OS and your quota + rights you might actually see some free space, but it's unusable). You can see real size of a file in file system blocks using ls -ls (at least in Solaris & HP-UX it works...) Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, July 27, 2003 10:44 AM > Check the block sizes. That part of the argument might still stand. > > On 2003.07.27 02:24, Saira Somani wrote: > > We are using 32-bit on 5L. I don't think Oracle 8.1.7 > > is compatible with 64-bit AIX kernel. > > > > --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > > > Your 1652 error means the following: > > > > > > 01652, 0, "unable to extend temp segment by %s > > > in tablespace %s" > > > // *Cause: Failed to allocate an extent for temp > > > segment in tablespace. > > > // *Action: Use ALTER TABLESPACE ADD DATAFILE > > > statement to add one or more > > > // files to the tablespace indicated. > > > > > > If everything else is the same, I would say that the > > > new OS is using a > > > different OS filesystem block size. That would mess > > > up all your file sizes, > > > allocations and alike. You are probably using 64 bit > > > JFS on 5L, straight up > > > from 32-bit 4.3.3? > > > > > > > > > On 2003.07.27 00:39, Saira Somani wrote: > > > > Hi Gurus, > > > > > > > > After upgrading the OS from AIX 4.3.3 to 5.1, our > > > > Oracle 8i instance is dishing out plenty of > > > ORA-1652 > > > > which means that our application cannot commit any > > > > transactions (or so I am finding in my tests). > > > > > > > > Help. The warehouse needs to be in production > > > > tomorrow 8AM! > > > > > > > > Thanks, > > > > Saira > > > > -- > > > > Please see the official ORACLE-L FAQ: > > > http://www.orafaq.net > > > > -- > > > > Author: Saira Somani > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Services-- 858-538-5051 > > > http://www.fatcity.com > > > > San Diego, California-- Mailing list and > > > web hosting services > > > > > > > > > - > > > > To REMOVE yourself from this mailing list, send an > > > E-Mail message > > > > to: [EMAIL PROTECTED] (note EXACT spelling of > > > 'ListGuru') and in > > > > the message BODY, include a line containing: UNSUB > > > ORACLE-L > > > > (or the name of mailing list you want to be > > > removed from). You may > > > > also send the HELP command for other information > > > (like subscribing). > > > > > > > > > > -- > > > Mladen Gogala > > > Oracle DBA > > > -- > > > Please see the official ORACLE-L FAQ: > > > http://www.orafaq.net > > > -- > > > Author: Mladen Gogala > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > > > http://www.fatcity.com > > > San Diego, California-- Mailing list and web > > > hosting services > > > > > - > > > To REMOVE yourself from this mailing list, send an > > > E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of > > > 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB > > > ORACLE-L > > > (or the name of mailing list you want to be removed > > > from). You may > > > also send the HELP command for other information > > > (like subscribing). > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Saira Somani > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > Mladen Gogala > Oracle DBA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or
Re: 8i ORA-1652 after upgrading from AIX 4.3.3 to 5.1
Hi! How did you check that temp ts was filling up? From v$temp_space_header? Could it be that some execution plans have changed that huge sorts (of cartesian joins) are done instead of index scans etc.. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, July 27, 2003 6:34 PM > As suggested, we checked all the tablespaces. > > TEMP space was increased from 2GB to 4GB and then to > 6GB. > > We ended up bouncing the Oracle instance each time > TEMP filled up because we really had nothing to lose. > > After doing this 3 maybe 4 times, we ran the same > scenario in the application and this time (and a few > times after that to be sure), TEMP did not fill up - > only used 0.5%. Then we decreased TEMP to 2GB and > everything still seemed to work. > > We had opened a severity level 1 TAR with Oracle and > have now reduced it to level 2 because we don't have a > case to present to them now. > > We don't really understand why this was happening or > how the problem got fixed. Since today is a production > day, we'll probably find out if it happens again. I > know we weren't imagining the entire thing! > > Thank you all for your suggestions. They were > extremely helpful. > Saira > > --- John Blake <[EMAIL PROTECTED]> wrote: > > May not necessarily mean TEMP tablespace... Check > > all of your tablespaces > > for available free space, that is contiguous > > freespace. > > > > -Original Message- > > Guido Konsolke > > Sent: Sunday, July 27, 2003 3:04 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Hi Saira, > > > > maybe temp tablespace isn't large enough. > > did you try what the doctor suggests? > > Did you enlarge the temp seg? > > 8-)) > > > > hth, > > Guido > > > > <<< [EMAIL PROTECTED] 27.07. 05.54 >>> > > Hi Gurus, > > > > After upgrading the OS from AIX 4.3.3 to 5.1, our > > Oracle 8i instance is dishing out plenty of ORA-1652 > > which means that our application cannot commit any > > transactions (or so I am finding in my tests). > > > > Help. The warehouse needs to be in production > > tomorrow! > > > > Thanks, > > Saira > > -- > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > -- > > Author: Guido Konsolke > > INET: > > [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 > > http://www.fatcity.com > > San Diego, California-- Mailing list and web > > hosting services > > > - > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > (or the name of mailing list you want to be removed > > from). You may > > also send the HELP command for other information > > (like subscribing). > > > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > -- > > Author: John Blake > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 > > http://www.fatcity.com > > San Diego, California-- Mailing list and web > > hosting services > > > - > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > (or the name of mailing list you want to be removed > > from). You may > > also send the HELP command for other information > > (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Saira Somani > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Set Role in Trigger
Technically possible, but prpbably not practical. You have to create several views for each of the users; possible - but may become unmaintainable. FGAC may be a btter maintanable option, IMHO. Arup Nanda www.proligence.com - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, July 27, 2003 2:44 PM > This is probably too kludgy or simple-minded, or non-maintainable, but is it technically possible? > > 1) Create a series of views that subset > the actual tables, according to the rules > you've got about who the viewer is & what > year(s) they've selected in the Users table. > > 2) Redefine the public synonyms so that they > point to your views rather than the base > tables. > > Cheers, > > -Roy > > Roy Pardee > Programmer/Analyst/DBA > SWFPAC Lockheed Martin IT > Extension 8487 > -Original Message- > Sent: Sunday, July 27, 2003 11:24 AM > To: Multiple recipients of list ORACLE-L > > > Jack, > > Question - why do you want to use System triggers to change roles? I don't see how you can define an event for this trigger to be fired. If the event is the updating of record in USERS table, who updates it? Obviously not the user himeself; then the whole system becomes insecure to the core. > > Your choice of using a role to allow access to a specific yesr is sound design. I assume each year is stored in separate tables. If that is not correct, i.e. the record for all years are stored in a single table, then roles will not help you, you need to enable FGAC using something called application context. > > Scenario 1: Each year's data is stored in a seprate table. > > You would create roles, but not normal roles. The roles should be identified by a procedure. This procedure is owned by SYS or some other secued schema. Inside this procedure you would assign a value to the application context attribute called user_role, which is set via dbms_Session.set_context. All the users are revoked execute priv on dbms_session; so they will never be able to call this procedure directly. The only way they can do it is by calling the trusted procedure you have defined for that role. > > Scenario 2: All the tables have data for all the years. > > Inthis case you will have to use FGAC; but the FGAC policy will have to depend on the application context you defined earlier. You wil define another context attribute called school_year, whic is again set by the trusted procedure of the role. Since the user does not have the privilege to call dbms_Session, he will not be able to set the value of this attrbute to any other year at will. > > Summary: > > You will define several roles ideintified by procedure. All these roles are granted to the user but none is a default role. > > When a user logs in, all roles assigned to him are disabled, since none is a default role. Then he calls the procedure set_role(), no arguments. Inside the procedure set_role(), you will read the users table, see the role the user is supposed to have, enable this role via dbms_Session.set_role and then set the application context, if any. > > Since the user does not have execute privs on dbms_session, he will not be able to set the app context. > > Since the role is identified by a procedure, i.e. set_role(), the user will not be able to set the role himself using "SET ROLE" command in sql*plus. The only way he can do that is by calling the set_role procedure. > > If the user does not call the procedure, none of the roles are enabled; therefore he will not be able to do anything. > > In other words, you have a secured system. > > As an added bonus: you will have the application context attribute you can use any way you want to use. Right now you can use it for FGAC; but later when you are in 9i, you can use it in other cooler features such as Fine Grained Auditing. > > HTH. > > Arup Nanda > www.proligence.com > - Original Message - > To: Multiple recipients of list ORACLE-L > Sent: Sunday, July 27, 2003 11:59 AM > > > > Short form of my question: > How can I enable a Role for a User within a database trigger (owned by another Schema) on a table owned by yet another Schema? > - The M's I've RTF'd indicate that a trigger (and any procedure it calls) can never execute with Invoker's Rights. > - I can't find a way to execute Set Role for a User as another User, say, System. > - I'm stuck. > - Environment: 8.1.7 on Win2k and HP-UX. > > Longer form of my question: > I'm in the process of adding extra security features to our 3rd Party Student Information System, whose code I can't touch. I've successfully implemented FGAC to keep Users at a School from accessing info.at other schools. Now I need to limit which School Year's data they can update (Past, Current, Next). The Application grants Sel, Ins, Upd, Del on its tables via a Role, so I thought I'd just switch Roles when the User switched School Years (via updating her record in a Users table). Seemed like a good idea, b
Re: 2 small tables - Query takes 3 hours
Sam: The proper advice is not to do it. Also, warn the vendor that what they are doing will possibly void the support contract with Oracle for any of their customers that use their softwarre. Ask Oracle about it to get their opinion, but I'm sure they will tell you that if you alter the Data Dictionary all bets are off. You should admonish the vendor for taking liberties with the Data Dictionary and warn your employer that your Oracle support might be voided. As a rule, I always insist on looking at the vendors installation scripts and documentation. I never let them use the SYS account for installation unless it is absolutely necessary, and even then I try to determine if it will work with another userid. Any vendor that insists on using SYS's schema and altering SYS's objects obviously is not used to installing into Oracle and I would be wary of them. I encountered a vendor like that who was used to installing into Microsoft SQL Server. He wanted to use SYS under Oracle believing it was the correct counterpart to the Database Owner in SQL Server. I wouldn't allow them to use it and I wound up rewriting most of their installation routine for them for the Oracle database. I suggested that they give our company a break on the price since we had to modify their product for them. (I also told our legal and purchasing department , in case they wanted to arrange compensation or royalties from the vendor.) As the DBA you have to put your foot down and let everyone know what the consequences are for altering the Data Dictionary for one application. Naturally it will have to be in the instance by itself. If you use OEM or some other tool, there is the possiblity that altering the Data Dictionary might cause these tools to not work properly. There are some serious ramifications for doing what the vendor wanted. As for some practical advice, try updating the statistics with DBMS_STATS, and if possible, run the Oracle Expert, part of Oracle Enterprise Manager, against the instance while the query or report is running. Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] s.net.ae To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: 2 small tables - Query takes 3 hours ity.com 07/27/2003 04:29 AM Please respond to ORACLE-L This view is part of the upgrade script provided by the Vendor (duh): I've been DBA'ing for 7 years in DB2 and Oracle and messing around with a SYS view is obviously not done- . I was looking for some proper advise as Ive never seen anything like it before. Sam - Original Message - Date: Sunday, July 27, 2003 11:39 am > People should not play with the SYS schema, period. No ifs, no buts, > it just isn't done. You are on your own, pal. If I were your employer, > you would have hit the road by now. Whatever application that Maximo > thing is, creating objects owned by SYS is simply not acceptable. > It's guys like you that give database administrators a bad name. > > > On 2003.07.27 01:29, [EMAIL PROTECTED] wrote: > > Unix Solaris 8 and Oracle 8.1.7.4 > > > > As part of an upgrade to the Maximo application I run a join on 2 > > tables: > > > > select count(*) from sys.syske
Re: Set Role in Trigger
Hi! > This is probably too kludgy or simple-minded, or non-maintainable, but is it technically possible? > > 1) Create a series of views that subset > the actual tables, according to the rules > you've got about who the viewer is & what > year(s) they've selected in the Users table. > > 2) Redefine the public synonyms so that they > point to your views rather than the base > tables. Oracle Apps actually works that way, that a user gets assigned an organization id org_id when he logs on (not using trigger, from client side instead) and uses views which restrict queries & dml by org_id. This is based on session environment variables, I believe it's better in performance point of view, if we would have to scan a "privileges" table during every select on any table, it could become the bottleneck... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Top DW requirements needed
Hi DBAs, I am very new to data warehouse and I am looking for bullet list, URLs, etc. that list what are the essential data warehousing requirements that needs identifying. I know you obviously have to understand the business prior to identifying requirements. I assume some of the requirements are 1. Understand business process 2. Identify what information the Users need to better accomplish their job such as user objectives and challenges 3. Identify data experts, where data resides,etc. If anyone has a formal list of MUST or suggested requirements I would like to see them. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Set Role in Trigger
I am not an expert on Oracle Apps, but those "session environment variables" are probably application context attributes I mentioned earlier. They can also be implemented by a package global variable; but there is no security in that; the user will be able to set the variable in anyway he wants. Yes, it is better from the performance point, too. Arup Nanda - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, July 27, 2003 4:19 PM > Hi! > > This is probably too kludgy or simple-minded, or non-maintainable, but is > it technically possible? > > > > 1) Create a series of views that subset > > the actual tables, according to the rules > > you've got about who the viewer is & what > > year(s) they've selected in the Users table. > > > > 2) Redefine the public synonyms so that they > > point to your views rather than the base > > tables. > > Oracle Apps actually works that way, that a user gets assigned an > organization id org_id when he logs on (not using trigger, from client side > instead) and uses views which restrict queries & dml by org_id. This is > based on session environment variables, I believe it's better in performance > point of view, if we would have to scan a "privileges" table during every > select on any table, it could become the bottleneck... > > Tanel. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Set Role in Trigger
Hi! Yep, security wise this solution was not good, Apps uses acutally dbms_application_info.set_client_info procedure (which sets client_info column i v$session). That's quite old mechanism, but yeah, one could set anything for it's value (although IIRC, Apps user had to execute it through fnd_application_info package, which had some additional checks in it). Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, July 28, 2003 1:59 AM > I am not an expert on Oracle Apps, but those "session environment variables" > are probably application context attributes I mentioned earlier. They can > also be implemented by a package global variable; but there is no security > in that; the user will be able to set the variable in anyway he wants. Yes, > it is better from the performance point, too. > > Arup Nanda > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Sunday, July 27, 2003 4:19 PM > > > > Hi! > > > This is probably too kludgy or simple-minded, or non-maintainable, but > is > > it technically possible? > > > > > > 1) Create a series of views that subset > > > the actual tables, according to the rules > > > you've got about who the viewer is & what > > > year(s) they've selected in the Users table. > > > > > > 2) Redefine the public synonyms so that they > > > point to your views rather than the base > > > tables. > > > > Oracle Apps actually works that way, that a user gets assigned an > > organization id org_id when he logs on (not using trigger, from client > side > > instead) and uses views which restrict queries & dml by org_id. This is > > based on session environment variables, I believe it's better in > performance > > point of view, if we would have to scan a "privileges" table during every > > select on any table, it could become the bottleneck... > > > > Tanel. > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Tanel Poder > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Arup Nanda > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 8i ORA-1652 after upgrading from AIX 4.3.3 to 5.1
1)for temp usage check v$sort_usage while application/job running. It is some code which may be resulting in cartesian join using hash join. 2) check for parallel degree in tables or indexes (dba_tables or dba_indexes) 'select table_name,degree from dba_tables where degree > 1; or same for indexes. If degree is > 1 then lot of temp segments are required for sorting. 3-Same may result due use of parallel in hint. 4) In short it is application/code related and that is to be fixed first. Beside for DW house application temp space may require big space for large sorts may be 6-8GB. HTH, Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Sun, 27 Jul 2003 07:34:24 -0800 As suggested, we checked all the tablespaces. TEMP space was increased from 2GB to 4GB and then to 6GB. We ended up bouncing the Oracle instance each time TEMP filled up because we really had nothing to lose. After doing this 3 maybe 4 times, we ran the same scenario in the application and this time (and a few times after that to be sure), TEMP did not fill up - only used 0.5%. Then we decreased TEMP to 2GB and everything still seemed to work. We had opened a severity level 1 TAR with Oracle and have now reduced it to level 2 because we don't have a case to present to them now. We don't really understand why this was happening or how the problem got fixed. Since today is a production day, we'll probably find out if it happens again. I know we weren't imagining the entire thing! Thank you all for your suggestions. They were extremely helpful. Saira --- John Blake <[EMAIL PROTECTED]> wrote: > May not necessarily mean TEMP tablespace... Check > all of your tablespaces > for available free space, that is contiguous > freespace. > > -Original Message- > Guido Konsolke > Sent: Sunday, July 27, 2003 3:04 AM > To: Multiple recipients of list ORACLE-L > > > Hi Saira, > > maybe temp tablespace isn't large enough. > did you try what the doctor suggests? > Did you enlarge the temp seg? > 8-)) > > hth, > Guido > > <<< [EMAIL PROTECTED] 27.07. 05.54 >>> > Hi Gurus, > > After upgrading the OS from AIX 4.3.3 to 5.1, our > Oracle 8i instance is dishing out plenty of ORA-1652 > which means that our application cannot commit any > transactions (or so I am finding in my tests). > > Help. The warehouse needs to be in production > tomorrow! > > Thanks, > Saira > -- > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Guido Konsolke > INET: > [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: John Blake > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list
Re: Set Role in Trigger
Arup, Thanks for your reply. As I said in my memo, I really want the User's Role to be changed from within a Trigger on a table, not a System Trigger. Actually, it doesn't matter, since no database trigger can be defined with Invoker's Rights. That means that any trigger in which I issue execute DBMS_Session.Set_Role would fail, since Roles are disabled in any PL/SQL module executed with Definer's Rights - which is all triggers. Yes, this App. updates the User table when the User chooses to change to a different set of Schoolyear tables, but it creates no system insecurity. Why would it? Using an Applicatioin Context Variable won't work, since the App. Ctx. Var. User_Role is not really a Role to which I could grant object privileges. My situation is your Scenario #1. I understand Application Context and am using it in the current FGAC implementation I've created for this app. However, I can't see how I can substitute an App. Ctx. Var. for a "real" Role. Actually, you should review the docs on Application Context. No User can set his Application Context Variables by calling DBMS_Session.Set_Context, since each Application Context is "registered" to its specific package, which should be inaccessible to the User. Roy suggested views, which might be an option for a well-designed, low user-count app, but this app. (remember, 3rd Party, we didn't design it!) has over 50,000 tables. Also, we have several thousand teachers and administrators who access the system. Multiply the two and you can see that millions of views are not feasible. I've really got to find a way to change a User's "real" Role from within a Trigger. It can't be the After_Logon Trigger, since the User's Role must be changeable up to many times during a single Session. Also, remember that I can't make a single modification to the Application's code. Unless someone comes up with something else, I'll experiment with having the table Trigger owned by System and executing "Alter User... Default Role..." as a Dynamic SQL statement to see if that works to change the User's Role. Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] "Arup Nanda" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 07/27/2003 01:24 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: Set Role in Trigger Jack, Question - why do you want to use System triggers to change roles? I don't see how you can define an event for this trigger to be fired. If the event is the updating of record in USERS table, who updates it? Obviously not the user himeself; then the whole system becomes insecure to the core. Your choice of using a role to allow access to a specific yesr is sound design. I assume each year is stored in separate tables. If that is not correct, i.e. the record for all years are stored in a single table, then roles will not help you, you need to enable FGAC using something called application context. Scenario 1: Each year's data is stored in a seprate table. You would create roles, but not normal roles. The roles should be identified by a procedure. This procedure is owned by SYS or some other secued schema. Inside this procedure you would assign a value to the application context attribute called user_role, which is set via dbms_Session.set_context. All the users are revoked execute priv on dbms_session; so they will never be able to call this procedure directly. The only way they can do it is by calling the trusted procedure you have defined for that role. Scenario 2: All the tables have data for all the years. Inthis case you will have to use FGAC; but the FGAC policy will have to depend on the application context you defined earlier. You wil define another context attribute called school_year, whic is again set by the trusted procedure of the role. Since the user does not have the privilege to call dbms_Session, he will not be able to set the value of this attrbute to any other year at will. Summary: You will define several roles ideintified by procedure. All these roles are granted to the user but none is a default role. When a user logs in, all roles assigned to him are disabled, since none is a default role. Then he calls the procedure set_role(), no arguments. Inside the procedure set_role(), you will read the users table, see the role the user is supposed to have, enable this role via dbms_Session.set_role and then set the application context, if any. Since the user does not have execute privs on dbms_session, he will not be able to set the app context. Since the role is identified by a procedure, i.e. set_role(), the user will not be able to set the role himself using "SET ROLE" command in sql*plus. The only way he can do that is by calling the set_role procedure. If the user does not call the procedu
session_cached_cursors
Hello list , Performance tuning docs say that : "To enable the caching of session cursors you must set the initialization parameter set session_cached_cursors. You can also enable it dynamically with alter session set" But neither of the following works on 9.2.0.1.0 enterprise edition (win32) alter system set session_cached_cursors=10 scope=memory; alter system set session_cached_cursors=10 scope=spfile; alter system set session_cached_cursors=10 scope=both; However this works alter session set session_cached_cursors=10 ; Why ? ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Top DW requirements needed
Rick - Check out http://www.ralphkimball.com. He has written articles for years on this topic and has them posted on his web site. His books are also great. One thing that is different from the normal applications is the user participation. If you develop a payroll application, for example, the users may dislike it, but they have no choice whether to use it. A data warehouse on the other hand is a supplement to the users. If a marketing person finds your DW hard to use, they will just ignore it. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, July 27, 2003 4:09 PM To: Multiple recipients of list ORACLE-L Hi DBAs, I am very new to data warehouse and I am looking for bullet list, URLs, etc. that list what are the essential data warehousing requirements that needs identifying. I know you obviously have to understand the business prior to identifying requirements. I assume some of the requirements are 1. Understand business process 2. Identify what information the Users need to better accomplish their job such as user objectives and challenges 3. Identify data experts, where data resides,etc. If anyone has a formal list of MUST or suggested requirements I would like to see them. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Set Role in Trigger
Jack, After theclarification, my recommendation actually holds more ground - I hope you can see that in the explanation here. Creating views are not practical due to the number of users and year combinations involved. Since you are using FGAC, that is precisely you should do. Actually, you should review the docs on Application Context. No User can set his Application Context Variables by calling DBMS_Session.Set_Context, since each Application Context is "registered" to its specific package, which should be inaccessible to the User. I am aware of that; in fact that is what makes this process more secure. I am writing a book to be published in September that describes exactly how this is done; as a matter of fact, the example described mimics your case. Unfortunately as this stage I will violate the contract with the publisher if I divulge too much details; so I have to be succinct! I am assuming you have 10 years of data in table such as year1, year2, etc. When a user logins, he may be given a role year1, which has select privileges on year1 only. 1. create a procedure set_role (p_user_id). this procedure selects from some table that stores the username and role allocated. from the role allocated, it calls dbms_session.set_role (p_role_name); you can also specify dbms_session.set_context 2. create a role year1 identified by set_role. Note "identified by set_role"; this is important. Similarly create all roles year2, year3, etc. 3. grant select on year1 to year1, etc. 4. create the user myuser 5. grant year1, year2 to myuser. 6. alter user myuser default role none; 7. create a procedure validate_me (p_username, p_password) that returns only YES or NO based on the username/password combination. Inside this procedure call the set_role and set_context procs. 8. When the user calls this validate_me procedure, the application context as well as role can be set. I wish it were more descriptive; but .. Using an Application Context Variable won't work, since the App. Ctx. Var. User_Role is not really a Role to which I could grant object privileges the app ctx attr value is simply for the role name. The actual role setting is done by set_role procedure. The only thing I don't understand is why this has to be called from a DML trigger on table. Which table's trigger will trigger this role setting event; could you elaborate on that? I really HTH. Arup Nanda www.proligence.com - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Sunday, July 27, 2003 8:59 PM Subject: Re: Set Role in Trigger Arup, Thanks for your reply. As I said in my memo, I really want the User's Role to be changed from within a Trigger on a table, not a System Trigger. Actually, it doesn't matter, since no database trigger can be defined with Invoker's Rights. That means that any trigger in which I issue execute DBMS_Session.Set_Role would fail, since Roles are disabled in any PL/SQL module executed with Definer's Rights - which is all triggers. Yes, this App. updates the User table when the User chooses to change to a different set of Schoolyear tables, but it creates no system insecurity. Why would it? Using an Applicatioin Context Variable won't work, since the App. Ctx. Var. User_Role is not really a Role to which I could grant object privileges. My situation is your Scenario #1. I understand Application Context and am using it in the current FGAC implementation I've created for this app. However, I can't see how I can substitute an App. Ctx. Var. for a "real" Role. Actually, you should review the docs on Application Context. No User can set his Application Context Variables by calling DBMS_Session.Set_Context, since each Application Context is "registered" to its specific package, which should be inaccessible to the User. Roy suggested views, which might be an option for a well-designed, low user-count app, but this app. (remember, 3rd Party, we didn't design it!) has over 50,000 tables. Also, we have several thousand teachers and administrators who access the system. Multiply the two and you can see that millions of views are not feasible. I've really got to find a way to change a User's "real" Role from within a Trigger. It can't be the After_Logon Trigger, since the User's Role must be changeable up to many times during a single Session. Also, remember that I can't make a single modification to the Application's code. Unless someone comes up with something else, I'll experiment with having the table Trigger owned by System and executing "Alter User... Default Role..." as a Dynamic SQL statement to see if that works to change the User's Role. Thanks.Jack C. ApplewhiteDatabase AdministratorAustin Independent School DistrictAustin, Texas512.414.9715 (wk)512.935.5929 (pager)[EMAIL PROTECTED]
RE: Is there any data dictionary to check out the creation statem
Thanks, Chao Zhu. Since the "query" column in user_mviews is long data type, when I select query from user_mview, I can only see part of select statement. Even "set linesize" doesn't help. One more, what I want is to compare two select statements in "querie" column. But with "query" long data type, I could not do this. I just wondering why Oracle set this column as varchar2. Chuan -Original Message- Sent: Friday, 25 July 2003 18:19 To: Multiple recipients of list ORACLE-L statements SQL> select query from user_mviews where mview_name='MV_END_ART'; QUERY SELECT * FROM ARTICLESCATALOG WHERE END_DATE_DTM > SYSDATE AND END_DATE_DTM Sent: Friday, July 25, 2003 2:24 PM > Hi, All, > > Is there any data dictionary in Oracle to check out creation statements of > materialized view? Something like the user_source view about object > creation. I have checked user_mviews, user_mview_joins, etc, but I haven't > got one. > > TIA > > Chuan > Important: This transmission is intended only for the use of the addressee > and may contain confidential or legally privileged information. If you are > not the intended recipient, you are notified that any use or dissemination > of this communication is strictly prohibited. If you receive this > transmission in error please notify the author immediately by telephone and > delete all copies of this transmission together with any attachments. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index Usage ?!
Hi Tanel, thanks a lot for your wonderful explanation. sorry to pester u you again with my doubts on the first day of the week. My only doubt is: 1.After analyzing the index,CBO doesn't read the index because it is costlier in terms of I/O and other resources. 2.But without analyzing,CBO uses the index now,which is supposed to be coslty,and hence incurs the same I/O and other resources . 3.when the role of CBO is over,the h/w resources comes into play to read the index.even now it reads the full index and the response is faster.how is it so ? hope i am clear in telling u what i had understood ?! plz. excuse me if i'm sound silly and stupid. of the above 3 points,where am i wrong ? Regards, Jp. 25-7-2003 21:24:45, "Tanel Poder" <[EMAIL PROTECTED]> wrote: >Hi! > >Your original post shows that when index was not analyzed, optimizer used >default statistics and found out that index scan is quite cheap. Also, no >sorting had to be done, since descending index range scan could be used to >satisfy your order by clause. > >Whe index was analyzed, then CBO actually saw, that index isn't that good as >defaul values showed, the number of leaf blocks was higher and also the >clustering factor was probably much-mugh higher, meaning that for any >non-unique index key value it has to visit several different data blocks to >get all matching rows. That means lot's of IOs. Eventually CBO decided that >it's cheaper to do let say 1600 multiblock (plus one segment header + >possibly bitmap) reads directly and scan through the whole table than to >traverse through index branches, scan leaf blocks and visit every data block >individually. > >So, with not-analyzed index, CBO had nothing else to do, than to be >super-optimistic about the index (#LB, CLUF) thus using index in your case. >But when analyzed, CBO had accurate data, but did make bad decision because >too pessimistic values for optimizer_index_cost_adj and >optimizer_index_caching. You should set them at session level for testing to >let say 50 and 90, but read the document below for understanding those >parameters. > >http://www.evdbt.com/SearchIntelligenceCBO.doc > >Cheers, >Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: search archives
David, search at www.orafaq.net though late , hope i helped u. Jp. 26-07-2003 20:34:25, "Ehresmann, David" <[EMAIL PROTECTED]> wrote: >How do I search the archives of this list? >thanks, -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is there any data dictionary to check out the creation statem
use set long 4000 or whatever instead of set linesize and try. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Sun, 27 Jul 2003 18:19:24 -0800 Thanks, Chao Zhu. Since the "query" column in user_mviews is long data type, when I select query from user_mview, I can only see part of select statement. Even "set linesize" doesn't help. One more, what I want is to compare two select statements in "querie" column. But with "query" long data type, I could not do this. I just wondering why Oracle set this column as varchar2. Chuan -Original Message- Sent: Friday, 25 July 2003 18:19 To: Multiple recipients of list ORACLE-L statements SQL> select query from user_mviews where mview_name='MV_END_ART'; QUERY SELECT * FROM ARTICLESCATALOG WHERE END_DATE_DTM > SYSDATE AND END_DATE_DTM Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, July 25, 2003 2:24 PM > Hi, All, > > Is there any data dictionary in Oracle to check out creation statements of > materialized view? Something like the user_source view about object > creation. I have checked user_mviews, user_mview_joins, etc, but I haven't > got one. > > TIA > > Chuan > Important: This transmission is intended only for the use of the addressee > and may contain confidential or legally privileged information. If you are > not the intended recipient, you are notified that any use or dissemination > of this communication is strictly prohibited. If you receive this > transmission in error please notify the author immediately by telephone and > delete all copies of this transmission together with any attachments. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for oth
Problem with Autotrace ?
Guys, SET AUTOTRACE works fine on the Database server machine. the database server ENV is 9.2.0.3/Win2k AS - With SP3. SQL>connect cti/cti; SQL>set autot trace SQL> whereas when i try the same from a different machine,i get the error below. { this client machine has 9.2.0.2/Win2K server - with SP3 } SQL>connect cti/[EMAIL PROTECTED]; SQL>set autot trace SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report .seems to be unusual.isn't it ? had anyone faced the same problem ? can someone help me out. Regards, Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN/Veritas duplicate db article
Didn't get a change to read it yet, but if it makes you feel better, I REALLY DID bookmark it to read later, when I thought it might apply to me. Currently in the big blue world (Oracle on the mainframe) and Veritas is just a pleasant memory :-( Babette -Original Message- Jared Still Sent: Sunday, July 27, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Hmm... absolutely no responses. Perhaps a bit of clarification is in order. I'm interested in comments only on the content. This was a write as a you go paper and decidely not publishable as is. If it seems worthwhile, I will rewrite into something a little more acceptable than the very rough draft that it is. Thanks, Jared On Wednesday 23 July 2003 14:29, [EMAIL PROTECTED] wrote: > List, > > I've written a short ( compared to the length of time it took > to do this anyway ) article on how to create a duplicate database > on a remote server using RMAN and Veritas NetBackup. > > It can be found at: > >http://www.tinyurl.com/hu4r > > or at this one if you can't access tinyurl.com: > > > http://www.cybcon.com/~jkstill/Alternate%20Client%20Restore%20With%20Verita >s%20NetBackup%20and%20Oracle%20RMAN.doc > > It's kinda rough, so suggestions, comments, criticisms are welcome. > > Thanks, > > Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN/Veritas duplicate db article
Ok I will go one better and say I actually did have a look at it at first glance it looked a bit like my first effort of dealing with RMAN, and Veritas, I will have a better look now I am back in the office and see if i can add some useful comment. Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = "If people did not sometimes do silly things, nothing intelligent would ever get done." - Ludwig Wittgenstein = Mincom "The People, The Experience, The Vision" = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. "Babette Turner-Underwood" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 28-07-2003 01:54 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: RMAN/Veritas duplicate db article Didn't get a change to read it yet, but if it makes you feel better, I REALLY DID bookmark it to read later, when I thought it might apply to me. Currently in the big blue world (Oracle on the mainframe) and Veritas is just a pleasant memory :-( Babette -Original Message- Jared Still Sent: Sunday, July 27, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Hmm... absolutely no responses. Perhaps a bit of clarification is in order. I'm interested in comments only on the content. This was a write as a you go paper and decidely not publishable as is. If it seems worthwhile, I will rewrite into something a little more acceptable than the very rough draft that it is. Thanks, Jared On Wednesday 23 July 2003 14:29, [EMAIL PROTECTED] wrote: > List, > > I've written a short ( compared to the length of time it took > to do this anyway ) article on how to create a duplicate database > on a remote server using RMAN and Veritas NetBackup. > > It can be found at: > >http://www.tinyurl.com/hu4r > > or at this one if you can't access tinyurl.com: > > > http://www.cybcon.com/~jkstill/Alternate%20Client%20Restore%20With%20Verita >s%20NetBackup%20and%20Oracle%20RMAN.doc > > It's kinda rough, so suggestions, comments, criticisms are welcome. > > Thanks, > > Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RMAN/Veritas duplicate db article
I saved the article but not having too much experience with the database duplication using RMAN, I have no comment to make. I can send you the document in the PDF format, instead of DOC. OpenOffice 1.1 has that possibility On 2003.07.27 23:54, Babette Turner-Underwood wrote: Didn't get a change to read it yet, but if it makes you feel better, I REALLY DID bookmark it to read later, when I thought it might apply to me. Currently in the big blue world (Oracle on the mainframe) and Veritas is just a pleasant memory :-( Babette -Original Message- Jared Still Sent: Sunday, July 27, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Hmm... absolutely no responses. Perhaps a bit of clarification is in order. I'm interested in comments only on the content. This was a write as a you go paper and decidely not publishable as is. If it seems worthwhile, I will rewrite into something a little more acceptable than the very rough draft that it is. Thanks, Jared On Wednesday 23 July 2003 14:29, [EMAIL PROTECTED] wrote: > List, > > I've written a short ( compared to the length of time it took > to do this anyway ) article on how to create a duplicate database > on a remote server using RMAN and Veritas NetBackup. > > It can be found at: > >http://www.tinyurl.com/hu4r > > or at this one if you can't access tinyurl.com: > > > http://www.cybcon.com/~jkstill/Alternate%20Client%20Restore%20With%20Verita >s%20NetBackup%20and%20Oracle%20RMAN.doc > > It's kinda rough, so suggestions, comments, criticisms are welcome. > > Thanks, > > Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Problem with Autotrace - Is this a BUG ?
In continuation of my previous mail : just now i upgraded the cleint to 9.2.0.3 , and tried it once again. IT WORKS i simply wonder why ?! is this a bug in 9.2.0.2 ?*!#$#&* Thanks and Regards, Jp. 28-07-2003 12:49:24, Prem Khanna J <[EMAIL PROTECTED]> wrote: >Guys, > >SET AUTOTRACE works fine on the Database server machine. >the database server ENV is 9.2.0.3/Win2k AS - With SP3. > >SQL>connect cti/cti; >SQL>set autot trace >SQL> > >whereas when i try the same from a different machine,i get the error below. >{ this client machine has 9.2.0.2/Win2K server - with SP3 } > >SQL>connect cti/[EMAIL PROTECTED]; >SQL>set autot trace >SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled >SP2-0611: Error enabling STATISTICS report > >.seems to be unusual.isn't it ? >had anyone faced the same problem ? >can someone help me out. > >Regards, >Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
a simple xml output using stylsheet
dear list, can anyone please point me to a simple example of select * from emp; query and the output displayed in xml (using a simple stylesheet) ... we have not been able to locate this on the net... TIA -Rahul The information contained in this email and its attachments if any may contain privileged and confidential information intended only for the attention of the recipient(s) specified. If you are not a recipient , any forwarding , disclosure , photocopying , distribution or use of the information in any way is prohibited . If you have received this email in error , please email us immediately on [EMAIL PROTECTED] or contact us on (62 21) 522 8775.-
RE: a simple xml output using stylsheet
Hi Rahul, Have a look at http://otn.oracle.com/tech/xml and also 'Oracle Application Developers Guide - XML'. There is a utility called XSQL which does this for you. Here's an example of that utility cut from the above mentioned guide (for 8i): declare queryCtx DBMS_XMLquery.ctxType; result CLOB; begin -- set up the query context...! queryCtx := DBMS_XMLQuery.newContext('select * from emp'); -- get the result..! result := DBMS_XMLQuery.getXML(queryCtx); -- Now you can use the result to put it in tables/send as messages.. printClobOut(result); DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle.. end; / The printClobOut procedure is : /CREATE OR REPLACE PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is xmlstr varchar2(32767); line varchar2(2000); begin xmlstr := dbms_lob.SUBSTR(result,32767); loop exit when xmlstr is null; line := substr(xmlstr,1,instr(xmlstr,chr(10))-1); dbms_output.put_line('| '||line); xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1); end loop; end; / Hope that helps, Regards, Charu. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of rahul sharmaSent: Monday, July 28, 2003 10:51 AMTo: Multiple recipients of list ORACLE-LSubject: a simple xml output using stylsheet dear list, can anyone please point me to a simple example of select * from emp; query and the output displayed in xml (using a simple stylesheet) ... we have not been able to locate this on the net... TIA -Rahul The information contained in this email and its attachments if any may contain privileged and confidential information intended only for the attention of the recipient(s) specified. If you are not a recipient , any forwarding , disclosure , photocopying , distribution or use of the information in any way is prohibited . If you have received this email in error , please email us immediately on [EMAIL PROTECTED] or contact us on (62 21) 522 8775.-
Re: a simple xml output using stylsheet
Rahul, i read an example for the same in the last edition of Oramag. turning a few pages of it might help u. Jp. 28-07-2003 15:09:24, "rahul sharma" <[EMAIL PROTECTED]> wrote: > Date:Sun, 27 Jul 2003 22:09:24 -0800 > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > From:"rahul sharma" <[EMAIL PROTECTED]> > dear list, can anyone please point me to a simple example of select * from > emp; query > and the output displayed in xml (using a simple stylesheet) ... > we have not been able to locate this on the net... > TIA > -Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
2 table join taking 3hours - blah blah -roll on ibiza
I havent got original Subject e-mail as it is on my home PC. Firstly let me just say how I like to use this list. As I am completely different time zone to you guys i.e. I am Brit living in Middle East, also our weekends are thu/fri - by the time I read posted questions they are usually well answered. But I do like to follow the list and now and again I post a question for a number of reasons. In this case I am leaving for a months vacation to Ibiza on Wednesday, before that Im doing a database upgrade in test(on 10 schemas- each one takes 3-5 hours), a million other jobs and frankly Ive not faced a problem such as this for a long time. Now ,is not the time I have to investigate fully the problem myself, I have 2 more days and Im offsky to party,big style - aint gonna be worrying bout no sys.icol$ view. Anyhow I also have a work around as I explained by dropping a Maximo table (not from SYS schema if you look at original post carefully) but I still thought original problem may be interesting to some people out there or even another MAXIMO DBA who has had a similar experience.(we are a multi schema site- unique in Maximo world though). Many posts are unanswered and it was just a chance somebody would have a 'heads up' on this. If not no big deal. I must remind you that this a MANDATORY installation script provided by the vendor as part of the MAXIMO ERP implementation. All of a sudden a self proclaimed guru (MUPPET) starts accusing me of being a disgrace to DBA's then I get another mail also telling me how I should stop implementations of products if they touch SYS schema(whats all that IBM about - somebody please come to my rescue here). Is the world going mad or do I need this holiday more than I thought. (maybe I do) MAXIMO(MROI) is the leading vendor of MATERIALS MANAGEMENT software in the World (Its a USA company mate). Sorry BP/SHELL CEO we have to cancel 5 million dollar implementation because DBA says you cant create view in SYS schema(and its not Data Dictionary just cause its SYS ).Well SHELL/BP CEO says tell the DBA TO GO ** HIMSELF with his P45.Grow up please.Have you ever worked on a large ERP implementation. Have u ever been on a big job before? Well I took the abuse and then got accused of being to blase talking about SYS objects, and thats why I was rebuked (please- was that an apology ?). Well if you dont like the question or its not to ure liking the way I structured it well ignore it mate. Your reply was totally offensive ,rude,and also ignorant as you fully missed the point. By the way of course I tried wait events , your specialist subject must be ' the bleeding obvious'. I was looking for something a little more inspirational and imaginative. I know some people may find this reply also offensive and Im sorry for that, but enough is enough- I didnt realise there was a bizzy on the list and the rubuke did it for me. On a serious note though I am not afraid to ask questions and for that matter am not afraid to talk about sex (please feel free off line,girls only). Im also not afraid to take criticism, if I missed something obvious. Dont we all from time to time. IT people used to be fun and have a sense of humour. That includes laughing at yourself when you balls up. Next time I will take more care when structuring a question.Maybe I did rush it and it wasn't clear and was misleading. Anyhow I want to withdraw the original post, I cant deal with any other Numpty telling me woes and pitfalls and evil things that will happen to me if I mess with SYS. Roll on Ibiza - p.s. It was waiting all the time on TEMP tablespace- probably because Hash join. Temp was empty and well sized -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RMAN/Veritas duplicate db article
Thanks, but I think it will get rewritten soon anyway. Jared On Sunday 27 July 2003 22:44, Mladen Gogala wrote: > I saved the article but not having too much experience with the database > duplication using RMAN, I have no comment to make. I can send you the > document in the PDF format, instead of DOC. OpenOffice 1.1 has that > possibility > > On 2003.07.27 23:54, Babette Turner-Underwood wrote: > > Didn't get a change to read it yet, > > but if it makes you feel better, > > I REALLY DID bookmark it to read later, > > when I thought it might apply to me. > > > > Currently in the big blue world > > (Oracle on the mainframe) and Veritas is just > > a pleasant memory :-( > > > > Babette > > > > -Original Message- > > Jared Still > > Sent: Sunday, July 27, 2003 3:29 PM > > To: Multiple recipients of list ORACLE-L > > > > > > > > Hmm... absolutely no responses. > > > > Perhaps a bit of clarification is in order. > > > > I'm interested in comments only on the content. > > > > This was a write as a you go paper and decidely > > not publishable as is. If it seems worthwhile, I will > > rewrite into something a little more acceptable than > > the very rough draft that it is. > > > > Thanks, > > > > Jared > > > > On Wednesday 23 July 2003 14:29, [EMAIL PROTECTED] wrote: > > > List, > > > > > > I've written a short ( compared to the length of time it took > > > to do this anyway ) article on how to create a duplicate database > > > on a remote server using RMAN and Veritas NetBackup. > > > > > > It can be found at: > > > > > >http://www.tinyurl.com/hu4r > > > > > > or at this one if you can't access tinyurl.com: > > > > http://www.cybcon.com/~jkstill/Alternate%20Client%20Restore%20With%20Veri > >ta > > > > >s%20NetBackup%20and%20Oracle%20RMAN.doc > > > > > > It's kinda rough, so suggestions, comments, criticisms are welcome. > > > > > > Thanks, > > > > > > Jared > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jared Still > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Babette Turner-Underwood > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: a simple xml output using stylsheet
thanks Charu, i have used this example successfully... but nowhere it's tell me how to use/attach a stylesheet to format my XML output !!! - Original Message - From: Charu Joshi To: Multiple recipients of list ORACLE-L Sent: Monday, July 28, 2003 1:29 PM Subject: RE: a simple xml output using stylsheet Hi Rahul, Have a look at http://otn.oracle.com/tech/xml and also 'Oracle Application Developers Guide - XML'. There is a utility called XSQL which does this for you. Here's an example of that utility cut from the above mentioned guide (for 8i): declare queryCtx DBMS_XMLquery.ctxType; result CLOB; begin -- set up the query context...! queryCtx := DBMS_XMLQuery.newContext('select * from emp'); -- get the result..! result := DBMS_XMLQuery.getXML(queryCtx); -- Now you can use the result to put it in tables/send as messages.. printClobOut(result); DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle.. end; / The printClobOut procedure is : /CREATE OR REPLACE PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is xmlstr varchar2(32767); line varchar2(2000); begin xmlstr := dbms_lob.SUBSTR(result,32767); loop exit when xmlstr is null; line := substr(xmlstr,1,instr(xmlstr,chr(10))-1); dbms_output.put_line('| '||line); xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1); end loop; end; / Hope that helps, Regards, Charu. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of rahul sharmaSent: Monday, July 28, 2003 10:51 AMTo: Multiple recipients of list ORACLE-LSubject: a simple xml output using stylsheet dear list, can anyone please point me to a simple example of select * from emp; query and the output displayed in xml (using a simple stylesheet) ... we have not been able to locate this on the net... TIA -Rahul The information contained in this email and its attachments if any may contain privileged and confidential information intended only for the attention of the recipient(s) specified. If you are not a recipient , any forwarding , disclosure , photocopying , distribution or use of the information in any way is prohibited . If you have received this email in error , please email us immediately on [EMAIL PROTECTED] or contact us on (62 21) 522 8775.-