RE: Life without a correlated subquery
How about using Minus? (I'm a set operator groupie.) It usually performs well for me, though I've done no detailed analysis. delete from theTable where rowid in ( select rowid from theTable where X is not null minus select min(rowid) from theTable where X is not null group by X ) ; Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager, [EMAIL PROTECTED]) [EMAIL PROTECTED] Bellow, Bambi [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ech.com cc: Sent by: Subject: RE: Life without a correlated subquery [EMAIL PROTECTED] .com 11/10/2003 12:24 PM Please respond to ORACLE-L Thanks Folks! The inline query, indeed, beat the correlated subquery. Bambi. -Original Message- From: Daniel Fink [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 11:34 AM To: Multiple recipients of list ORACLE-L Subject: Re: Life without a correlated subquery It is a little convoluted, but you can use an inline query. It is not a correlated subquery, it may be more efficient, your mileage may vary, contents under pressure... Here is an example of the select using the old, reliable emp table that I populated with duplicates. SQL l 1 select e1.rowid, 2 e1.empno, 3 e1.ename 4* from emp e1 SQL / ROWID EMPNO ENAME -- -- -- AAABb5AAEAAAUIiAAA 7369 SMITH AAABb5AAEAAAUIiAAB 7499 ALLEN AAABb5AAEAAAUIiAAC 7521 WARD AAABb5AAEAAAUIiAAD 7566 JONES AAABb5AAEAAAUIiAAE 7654 MARTIN AAABb5AAEAAAUIiAAF 7698 BLAKE AAABb5AAEAAAUIiAAG 7782 CLARK AAABb5AAEAAAUIiAAH 7788 SCOTT AAABb5AAEAAAUIiAAI 7839 KING AAABb5AAEAAAUIiAAJ 7844 TURNER AAABb5AAEAAAUIiAAK 7876 ADAMS AAABb5AAEAAAUIiAAL 7900 JAMES AAABb5AAEAAAUIiAAM 7902 FORD AAABb5AAEAAAUIiAAN 7934 MILLER AAABb5AAEAAAUIiAAO 7369 SMITH AAABb5AAEAAAUIiAAP 7499 ALLEN AAABb5AAEAAAUIiAAQ 7521 WARD AAABb5AAEAAAUIiAAR 7566 JONES AAABb5AAEAAAUIiAAS 7654 MARTIN AAABb5AAEAAAUIiAAT 7698 BLAKE AAABb5AAEAAAUIiAAU 7782 CLARK AAABb5AAEAAAUIiAAV 7788 SCOTT AAABb5AAEAAAUIiAAW 7839 KING AAABb5AAEAAAUIiAAX 7844 TURNER AAABb5AAEAAAUIiAAY 7876 ADAMS AAABb5AAEAAAUIiAAZ 7900 JAMES AAABb5AAEAAAUIiAAa 7902 FORD AAABb5AAEAAAUIiAAb 7934 MILLER 1 select e1.rowid, 2 e1.empno, 3 e1.ename 4 from emp e1, 5 (select empno, min(rowid) min_rowid 6from emp 7group by empno) e2 8 where e1.empno = e2.empno 9* and e1.rowid != e2.min_rowid SQL / ROWID EMPNO ENAME -- -- -- AAABb5AAEAAAUIiAAO 7369 SMITH AAABb5AAEAAAUIiAAP 7499 ALLEN AAABb5AAEAAAUIiAAQ 7521 WARD AAABb5AAEAAAUIiAAR 7566 JONES AAABb5AAEAAAUIiAAS 7654
Re: Boolean dates...
Jose, In my experience - long ago - managing dates rarely involved Boolean logic, which is Two Valued Logic, True or False. Instead, they involved MVL (Multi-Valued Logic) with the most common return value being one of a bewildering assortment of INDETERMINATE variations, followed by frequent FALSE return values, and extremely rare occurrances of the TRUE return value. Perhaps an example query and its results will illustrate. Me: So Pam, can we make it a date Saturday night? Say, dinner and a movie? Possible responses, with actual MVL result for Will there be a Date?: Pam: Oh gosh, I'd love to, but not this weekend. - INDETERMINATE Pam: Let me check my calendar and I'll get back to you. - INDETERMINATE Pam: I'll have to wait and see how the week goes. - INDETERMINATE Pam: Sorry, I've got other plans that night.- FALSE Pam: HA HA HA HA HA... (hysterical laughter)- FALSE Pam: Sure, pick me up at 8:00. - TRUE (very rare) Those observations with the usual caveat - YMMV.;-) Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager, [EMAIL PROTECTED]) [EMAIL PROTECTED] Jose Luis Delgado joseluis_delgadoTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @yahoo.com cc: Sent by: Subject: Boolean dates... [EMAIL PROTECTED] .com 10/22/2003 10:19 AM Please respond to ORACLE-L Hi to all! We have an old app that manages something that my boss calls: boolean dates. He told me that exists an algorithm that manages dates as a boolean format. We have several tables in this form: Note: The following table: PAAM has the field BDATE defined as NUMBER. sql select bdate from paam sql where rownum 6 BDATE -- 728464 728434 728403 728495 728283 now, I need to convert that format to an 'understandable' format to get the old data and old dates. I'm looking (google-ing) for that subject but, without luck. any ideas? help?, pls... Thanks in advance Regards! JL -- 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: Boolean dates...
The 15th of March. From http://www.infoplease.com/spot/ides1.html : Kalends (1st day of the month) Nones (the 7th day in March, May, July, and October; the 5th in the other months) Ides (the 15th day in March, May, July, and October; the 13th in the other months Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager, [EMAIL PROTECTED]) [EMAIL PROTECTED] Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/22/2003 04:09 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Boolean dates... When, exactly, were the Ides of March? ..
Oracle Client for Macintosh
I've searched TechNet and MetaLink but can't find a downloadable 8i, 9i, anything Oracle Client for Apple Macintosh - OS 9 or OS X. What am I missing? Is it not available? Some docs on MetaLink mention it, but the versions seem related to SQL*Net, not the RDBMS. I'm clueless when it comes to Macs, so I probably don't know the right keywords. Could someone point me in the right direction? Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager, [EMAIL PROTECTED]) [EMAIL PROTECTED]
RE: DBA needed in Austin, TX
Raj, It's Mongolian Barbeque and still there. Now there's even a Mongolian BBQ North. Tom, You're forgetting that: - In the summer it's hot. - When it's hot, people wear less clothes to keep cool, sometimes a LOT less. - Austin is home to the University of Texas (almost 50,000 students at peak, half of which are female). Are you getting a visual at all? Jack From-Behind-His-Sunglasses-Shy-Admirer Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager, [EMAIL PROTECTED]) [EMAIL PROTECTED] Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Rajendra.Jamadagncc: [EMAIL PROTECTED] Subject: RE: DBA needed in Austin, TX Sent by: [EMAIL PROTECTED] com 09/26/2003 09:14 AM Please respond to ORACLE-L I still remember the dinner I had in an Mongolian restaurant near the I35, great food (considering that I don't eat meat) ... it has been 4-5 years, don't remember the name though. Raj -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 4:15 PM To: Multiple recipients of list ORACLE-L Subject: RE: DBA needed in Austin, TX ..note to selfdon't visit Austin during the months of May thru August. -- 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: DBA needed in Austin, TX
100 deg. F and raining - Happens all over Texas during an afternoon summer thunderstorm, though the rain usually cools things down a bit. The wimps in Dallas can't complain about humidity at all. Folks in Houston certainly can. Unfortunately, my wife won't let me keep even ONE of the Dixie Chicks. I'm trying for Sandra Bullock now. ;-) Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager, [EMAIL PROTECTED]) [EMAIL PROTECTED] Mercadante, Thomas FTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: te.ny.usSubject: RE: DBA needed in Austin, TX Sent by: [EMAIL PROTECTED] .com 09/25/2003 10:44 AM Please respond to ORACLE-L ummm.. if it's 100% humidity, doesn't that mean it's raining .. or misting .. or something? I can't imagine it being 100degrees and raining. -Original Message- From: Loughmiller, Greg [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 11:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBA needed in Austin, TX dry heat?? Not sure about Austin, but I have some co-workers in Dallas where they say it's 100x100: 100 degrees with 100% humidity... greg -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 10:05 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBA needed in Austin, TX I really like that no DBA left behind program of yours, but TX is too hot for me. I know it's a dry heat but nevertheless, I do prefer colder climate. You can keep the Dixie Chicks. ... -- 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: DBA needed in Austin, TX
David forgot to mention a couple of things about working for AISD: - We don't wear neckties (well, David does sometimes). - We get 23 paid Holidays (how does 2 weeks off for Christmas and a week off for Spring Break sound?). - We have an unlimited site license for Oracle EE. I slap databases out there as needed without a second thought. - Before he became MIS Director, David was an Oracle DBA and a UNIX Sys Admin before that, so he really understands the technology and the challenges. - Did I mention that we don't wear neckties? AISD's infrastructure is truly awesome. The fiber WAN allows our widely-scattered users to connect dependably to our databases and me to push tons of data around quickly and reliably. All the UNIX and other servers (not to mention our Users' PCs) are either brand new or about 3 years or less old. The oldest Oracle databases we have are 8.1.7.0.0 and we've already started upgrading to 9.2.0.4.0. We'll be moving our only Win2k database to HP-UX in the next few months. All the other DBs are already on either HP-UX or Linux. We've got a couple of less-than-perfect 3rd Party COTS applications for Financials and Student Information, but we're rapidly applying Oracle features to improve them and add functionality their producers never even thought of, as well as providing Web-enabled access to our thousands of AISD users and the Community at large. Our Developers have hardly a Duh(veloper) among them and their supervisor is top-notch, too. An all-round great bunch of folks to work with District-wide. I've been with AISD for 15 months - my first Public Sector gig ever - and I'm loving it. Also, with the Economy the way it is (and will be for a while to come), this Public Sector organization is very competitive in salary and benefits with the Private Sector. I know, I shopped around and got other offers before coming here. Oh, and Austin being the Live Music Capital of the World doesn't hurt, either. And I certainly don't want to forget the part about not having to wear a necktie! Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager, [EMAIL PROTECTED]) [EMAIL PROTECTED] [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/23/2003 03:39 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:DBA needed in Austin, TX With permission from Jared: 'Needed' is kind of strong. We've got a position open, but we're not real needy folks. We're a school district. The 37th (depending on the day of the week) largest in the United States. If we were a business, it'd be pretty good-sized. Our annual operating budget is 730 million dollars plus (of which MIS gets a really small, tiny, little itty bit), we have 15,000 employees, 130 branch offices, our own police force and post office. We fix 150,000 meals a day and need to collect cash or validate for every person through the cafeterias. We're moving from a mainframe environment to client-server computing with an Oracle back-end running on HPUX and Linux for both our student data and ERP systems. It's a pretty dynamic shop. We've got 40 or so people (depending on the day of the week) split into three areas, business systems, student systems and programming. We don't do the network piece, but that's solid - switched gigabit ethernet running on over 300 miles of fiber with 100MB to the desktop. I don't hire or set salaries. HR does that. I only recommend. The salary for this position will be somewhere between $60K and $80K depending on experience (and whatever bizarre decisions are reached by the compensation analyst). You've got to be REAL strong in backup and recovery. And curious. We're planning to go where no school district (or very few) has gone before. Your assistance will be vital in making the investment Austin has made in technology pay off. If you've got any questions, please send me an email. If you'd like to apply, you can do so on-line at http://www.austin.isd.tenet.edu/about/hr/search/index.phtml Under 'classified' positions, see if you can find the DBA position. If it isn't there (depending on the day of the week), and you can't possibly wait, apply for either the Programmer/Analyst or UNIX Admin position. It should be there sometime after 4pm CDT, or maybe tomorrow, or maybe not. [We don't do the Internet piece - yet]. David A. Barbour Director, Management Information Services AISD -- Author: INET: [EMAIL PROTECTED]
Re: Any known problems with installing new MS patch on Oracle server
If MS means Micros**t, our Techs have been applying patches like crazy to all our Win2k servers. Our production Student Info. database lives on one of those - it's EE 8.1.7.4 - and I've seen no ill effects. We just had to find a time to reboot the server so the patches would take effect. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] lth.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Any known problems with installing new MS patch on Oracle server .com 09/12/2003 07:44 AM Please respond to ORACLE-L Hi All, Has anyone heard of any known gotchas with installing the latest MS patch on Oracle server? Thanks Rick -- Author: INET: [EMAIL PROTECTED] -- 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).
Recover 8.1.7 DB with _allow_resetlogs_corruption
8.1.7.0 on HP-UX. Another DBA (really, it wasn't me) forgot which server he was on and deleted the RBS tablespace datafile and all the archived redo logs - on different mount points - of our Production Financials database. No time for the whys of that story or why we don't mirror our archived redo logs (which we will do starting today!). We dug up references to these undocumented parameters: _allow_resetlogs_corruption _corrupted_rollback_segments _offline_rollback_segments We have all the current database datafiles (plus the hot backup RBS datafile from last night), online redo logs, control files, etc. and have included those undocumented parameters in our init.ora. A message in the alert log looks hopeful when we attempt to Open Resetlogs: RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 4806846187 However, all our attempts to open the database have failed with various errors: ORA-00600: internal error code, arguments: [4000], [25], [], [], [], [], [], [] ORA-704 signalled during: alter database open resetlogs ORA-1139 signalled during: alter database open resetlogs We also tried to recreate the control files from a trace coltrolfile, but get this error in the alert log. On the screen it's a snapshot too old error, referring to a rollback segment with no number or name. ORA-604 signalled during: ALTER DATABASE OPEN ResetLogs Are we hopelessly hosed? We backed up the database, unfortunately only _after_ we tried a couple of recovery attempts. Have we messed around with these datafiles too much and need to restore from our backup? Did our messing with the database before we backed it up eliminate the possibility of any kind of recovery, even using those undocumented parameters? Remember, ALL our archived redo logs between last night's hot backup and today's fiasco are gone, and there were enough log switches to have cycled through the online redo logs several times. We're logging a tar with Oracle Support, but any advice would be helpful. Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED]
Re: Row level security and latch waits
I got the 9.2 docs and this is in the Concepts manual: Dynamic Predicates The function or package that implements the security policy you create returns a predicate (a WHERE condition). This predicate controls access as set out by the policy. Rewritten queries are fully optimized and shareable. Fully optimized and shareable sure sounds like the queries are parsed after the predicate is added. I couldn't find any init parameter similar to what you mentioned. Does anyone have a concrete reference regarding parsing of FGAC-modified queries? Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Connor McDonald [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] uk cc: Sent by: Subject: Re: Re: Row level security and latch waits [EMAIL PROTECTED] .com 08/20/2003 10:21 AM Please respond to ORACLE-L My understanding was that the rls predicate was added at parse time (hence the importance of the contexts and avoiding things like 'sysdate') But also if I remember correctly, this behaviour was changed in v9 to process the security function with each execution (and hence probably increase the amount of parsing going on). You might want to have a play the the _dynamic_policies parameter (or something like that) which can be used to revert the 8i behaviour (which should reduce parsing to a degree) hth connor ... -- 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: Row level security and latch waits
Raj, Thanks. A mention of which FM would've saved me some searching, but I found it under Introduction to Fine-Grained Access Control / Automatic Reparse in the Oracle9i Application Developer's Guide - Fundamentals. I'll have to do more investigation, since those paragraphs don't clearly explain (at least, to me) what is meant by the same predicate. Does that mean that predicates with Context Variables included as bind variables will not be reparsed if their Policy Function was created with Static_Policy=True, and yet different sessions can reuse the parsed SQL with their own Context values? ...or not? Also, any idea where init parameters such as _dynamic_rls_policies and _app_ctx_vers are fully documented? A search reveals that they're not in any of the 9.2 docs that I've got, except for the brief mention they get in the above-mentioned section of the Developer's Guide. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Rajendra.Jamadagncc: [EMAIL PROTECTED] Subject: RE: Row level security and latch waits Sent by: [EMAIL PROTECTED] com 08/20/2003 04:09 PM Please respond to ORACLE-L Jack, This is from TFM ... manual Starting from 9i, queries against Fine Grained Access enabled objects always execute the policy function to make sure most up to date predicate is used for each policy. For example, in case of the time based policy function, in which queries are only allowed between 8am-5pm, an execute of a cursor that is parsed at noon would result in an execution of the policy function at the execution time to make sure policy function is consulted again for the query. There are only two exceptions to this rule. One is to specify STATIC_POLICY=TRUE when adding the policy to indicate that the policy function always returns the same predicate. Another one is for users whose security policies do not return different predicate within a database session, the init.ora parameter _dynamic_rls_policies can be set to FALSE to reduce the execution overhead. /manual Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ... -- 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: Row level security and latch waits
RLS doesn't use bind variables? How then does Oracle treat the Application Context variables that you include in the predicates generated by the Security Policy functions? If those aren't bind variables then I guess I don't know what bind variables are. Please refer me to the documentation on which your assertion is based. Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Row level security and latch waits .com 08/19/2003 10:44 AM Please respond to ORACLE-L row level security doesnt use bind variables. dont know if there is a way to get it to use them. thats probably your problem. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/08/19 Tue AM 11:19:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com -- 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: Re: Row level security and latch waits
OK, I went and looked in the 8i Concepts manual. It seems pretty clear that Application Context variables are used as bind variables. It may have changed for 9i, but I can't see how or why. Application Context Application context facilitates the implementation of fine-grained access control. It allows you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus). Application contexts permit flexible, parameter-based access control, based on attributes of interest to an application. For example, context attributes for a human resources application could include position, organizational unit, and country while attributes for an order-entry control might be customer number and sales region. You can: - Base predicates on context values - Use context values within predicates, as bind variables NOTE THIS LINE. - Set user attributes - Access user attributes Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Re: Row level security and latch waits .com 08/19/2003 01:39 PM Please respond to ORACLE-L its just appending a where clause. its not binding it. im not familiar with contexts. never worked with them. someone correct me if im wrong here? Could have sworn i read that somewhere. i looked up application contexts. they appear to be handled differently. am i wrong? From: [EMAIL PROTECTED] Date: 2003/08/19 Tue PM 02:14:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Row level security and latch waits RLS doesn't use bind variables? How then does Oracle treat the Application Context variables that you include in the predicates generated by the Security Policy functions? If those aren't bind variables then I guess I don't know what bind variables are. Please refer me to the documentation on which your assertion is based. Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Row level security and latch waits .com 08/19/2003 10:44 AM Please respond to ORACLE-L row level security doesnt use bind variables. dont know if there is a way to get it to use them. thats probably your problem. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/08/19 Tue AM 11:19:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most
Re: Row level security and latch waits
Application Context is used to implement FGAC (aka RLS). You need to read those sections in the Concepts doc. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Re: Row level security and latch waits .com 08/19/2003 02:19 PM Please respond to ORACLE-L i wasny referring to application contexts. I was referring to just using a policy based on dbms_rls. thats different isnt it? From: [EMAIL PROTECTED] Date: 2003/08/19 Tue PM 03:14:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Re: Row level security and latch waits OK, I went and looked in the 8i Concepts manual. It seems pretty clear that Application Context variables are used as bind variables. It may have changed for 9i, but I can't see how or why. Application Context Application context facilitates the implementation of fine-grained access control. It allows you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus). Application contexts permit flexible, parameter-based access control, based on attributes of interest to an application. For example, context attributes for a human resources application could include position, organizational unit, and country while attributes for an order-entry control might be customer number and sales region. You can: - Base predicates on context values - Use context values within predicates, as bind variables NOTE THIS LINE. - Set user attributes - Access user attributes Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Re: Row level security and latch waits .com 08/19/2003 01:39 PM Please respond to ORACLE-L its just appending a where clause. its not binding it. im not familiar with contexts. never worked with them. someone correct me if im wrong here? Could have sworn i read that somewhere. i looked up application contexts. they appear to be handled differently. am i wrong? From: [EMAIL PROTECTED] Date: 2003/08/19 Tue PM 02:14:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Row level security and latch waits RLS doesn't use bind variables? How then does Oracle treat the Application Context variables that you include in the predicates generated by the Security Policy functions? If those aren't bind variables then I guess I don't know what bind variables are. Please refer me to the documentation on which your assertion is based. Thanks. Jack C. Applewhite Database Administrator Austin Independent
Re: Implementing different document types with different attributes
Tim, As soon as I sent this I saw my mistake. There's got to be one more table. Doc_Types and Doc_Attributes stay, but Documents gets changed and Document_Attributes gets created. Actually, if you wanted you could make a single integer PK column for Doc_Type_Attributes and use that as the FK in Document_Attributes. Table Documents Doc_ID NumberPK ,Doc_DescrVarChar2(100) ,Doc_Body BLOB(store this segment out-of-line somewhere) (other Document columns) Table Document_Attributes Doc_ID NumberPK and FK to Documents ,Doc_Type_IDNumberPK and part of FK to Doc_Type_Attributes ,Doc_Attrib_ID NumberPK and part of FK to Doc_Type_Attributes Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jack Applewhite To: [EMAIL PROTECTED] 08/07/2003 01:16 cc: PM Subject: Re: Implementing different document types with different attributes (Document link: Jack Applewhite) Tim, How about this? Table Doc_Types Doc_Type_IDNumberPK ,Doc_Type_Descr VarChar2(100) Table Doc_Attributes Doc_Attrib_ID NumberPK ,Doc_Attrib_Descr VarChar2(100) Table Doc_Type_Attributes (Intersect table of the above two) Doc_Type_IDNumberPK and FK to Doc_Types ,Doc_Attrib_ID NumterPK and FK to Doc_Attributes Table Documents Doc_ID NumberPK ,Doc_Type_IDNumberpart of FK to Doc_Type_Attributes ,Doc_Attrib_ID Numberpart of FK to Doc_Type_Attributes ,Doc_DescrVarChar2(100) ,Doc_Body BLOB(store this segment out-of-line somewhere) This way you can have as many Doc Types and Attributes as you want and never have to modify table designs if you add or remove some. Also, no blank columns. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Vernaillen Tim [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] gs.be cc: Sent by: Subject: Implementing different document types with different attributes [EMAIL PROTECTED] .com 08/07/2003 08:34 AM Please respond to ORACLE-L Hello I've an installation/implementation question! We've to analyse 50 document types, in total those 50 has 70 different attributes. We
Re: Implementing different document types with different attributes
Tim, How about this? Table Doc_Types Doc_Type_IDNumberPK ,Doc_Type_Descr VarChar2(100) Table Doc_Attributes Doc_Attrib_ID NumberPK ,Doc_Attrib_Descr VarChar2(100) Table Doc_Type_Attributes (Intersect table of the above two) Doc_Type_IDNumberPK and FK to Doc_Types ,Doc_Attrib_ID NumterPK and FK to Doc_Attributes Table Documents Doc_ID NumberPK ,Doc_Type_IDNumberpart of FK to Doc_Type_Attributes ,Doc_Attrib_ID Numberpart of FK to Doc_Type_Attributes ,Doc_DescrVarChar2(100) ,Doc_Body BLOB(store this segment out-of-line somewhere) This way you can have as many Doc Types and Attributes as you want and never have to modify table designs if you add or remove some. Also, no blank columns. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Vernaillen Tim [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] gs.be cc: Sent by: Subject: Implementing different document types with different attributes [EMAIL PROTECTED] .com 08/07/2003 08:34 AM Please respond to ORACLE-L Hello I've an installation/implementation question! We've to analyse 50 document types, in total those 50 has 70 different attributes. We don't want to put all those document types into one table, because more than the half (35) of the attributes are not always used for each document type. This will have to much disk space for each record, if most of the fields are just blank. Has anyone suggestions how to build our table-structure? I've heart something about FlexFields, what are they? Takes every field diskspace, even if it's blank (null)? Thanks in advance for the response! Tim -- 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
Jared, Yeah, I'm sure our Developers complain to their boss That dang DBA's Jacking with my application again. ;-) Actually, it would only take as long to generate the 50k triggers as it would to loop through the list of tables in a PL/SQL procedure and generate/execute the Dynamic SQL Create Trigger commands for those tables. The Before Statement Triggers would all have the same simple call to a security package that checked the User's UserClass and allowed/failed the DML. Since tables are created by each Campus from time to time, I could have an After Create Trigger on the App Schema and add that generic security trigger to each new table. As opposed to Triggers, I'm really excited about Granting/Revoking System Privileges to control each User's access to the various sets of tables they hit for different Schoolyears. That way there's just a couple of triggers for me to maintain, not 50,000, and the App's native security (incomplete as it is) controls most of the access. I'm just applying an extra, transparent, layer. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jared Still [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: Sent by: Subject: Re: Set Role in Trigger [EMAIL PROTECTED] .com 07/31/2003 12:34 AM Please respond to ORACLE-L Incredible amount of work you've had to do here Jack. Now I can say I know Jack about roles in triggers. Yeah, ok, sorry. ;) Jack, how long do you think it will take to create 50k triggers? Jared On Wed, 2003-07-30 at 12:54, [EMAIL PROTECTED] wrote: OK, here's what I've found so far. There is no way at all in Oracle8i (don't know about 9i or 10g) to enable/disable/affect a User's Role from within a Trigger. Role processing is automatically disabled in any Definer Rights PL/SQL module. All Triggers can only be created to execute with Definer Rights. So it doesn't matter if the User owns the Trigger or any Invoker Rights procedure it calls - Role processing is disabled, period. (Thanks to Roy Pardee for pointing me to MetaLink Note 106140.1, which lays it out pretty clearly.) FGAC and/or Application Context is also of no help in enabling/disabling Roles from within a Trigger. Notice I said from within a Trigger - that's my requirement. I can't add any code to call an Invoker Rights Stored Procedure to switch Roles from within the (3rd Party, remember) App itself - that would have been easy. I tried to do an Alter User User Default Role TheRoleIWantToEnable from the Trigger. First of all, it's got to be an Autonomous Transaction Trigger, since Alter User issues an implicit Commit. I did that, but even when the Trigger was owned by System, an Insufficient Privileges error was generated. If the Trigger was owned by Sys, however, it fired successfully and the User's Default Roles were changed (as evidenced by the User querying User_Role_Privs). However, the User's ENABLED Roles were unaffected for that session, which is just what the docs indicate. The User's new Default Roles would be enabled at the next Login. No help there. I thought about setting up different Schemas with only synonyms and different privileges on the
Re: Set Role in Trigger
OK, here's what I've found so far. There is no way at all in Oracle8i (don't know about 9i or 10g) to enable/disable/affect a User's Role from within a Trigger. Role processing is automatically disabled in any Definer Rights PL/SQL module. All Triggers can only be created to execute with Definer Rights. So it doesn't matter if the User owns the Trigger or any Invoker Rights procedure it calls - Role processing is disabled, period. (Thanks to Roy Pardee for pointing me to MetaLink Note 106140.1, which lays it out pretty clearly.) FGAC and/or Application Context is also of no help in enabling/disabling Roles from within a Trigger. Notice I said from within a Trigger - that's my requirement. I can't add any code to call an Invoker Rights Stored Procedure to switch Roles from within the (3rd Party, remember) App itself - that would have been easy. I tried to do an Alter User User Default Role TheRoleIWantToEnable from the Trigger. First of all, it's got to be an Autonomous Transaction Trigger, since Alter User issues an implicit Commit. I did that, but even when the Trigger was owned by System, an Insufficient Privileges error was generated. If the Trigger was owned by Sys, however, it fired successfully and the User's Default Roles were changed (as evidenced by the User querying User_Role_Privs). However, the User's ENABLED Roles were unaffected for that session, which is just what the docs indicate. The User's new Default Roles would be enabled at the next Login. No help there. I thought about setting up different Schemas with only synonyms and different privileges on the main App Schemas tables, then switching Schemas via Alter Session Set Current_Schema = DifferentSchema. However, I quickly realized that was a stupid idea since I'd have to grant the User Ins/Upd/Del on one of those Schemas, which wouldn't go away when I switched Schemas. The only way this would work would be to put each SchoolYear's actual tables in a separate Schema with different Privileges granted to Users (via Roles). Then switching Schemas would really switch Privileges as well. This is not feasible, since the App would not be expecting it and would probably barf. I might be able to fool it with synonyms and grants, but it's a lot of work and too iffy. The solution I'm pursuing now is to Grant and Revoke System Privileges (which take effect immediately) to each User from within an After Logon Trigger and the After Row Trigger that really needs to drive this part of the Security layer I'm implementing. The App already grants Select Any Table, Insert Any Table, Update Any Table, and Delete Any Table to the Role assigned to all App Users. I'll simply use the Application Context variables I'm setting up to support the FGAC piece I've implemented to determine which of those System Privileges to Grant at Logon and which ones to Grant/Revoke when the User switches SchoolYears (from within the After Row Trigger). Before y'all respond with howls of protest at my granting Sel/Ins/Upd/Del Any Table to Users, rest assured that: 1. The App does that anyway and now I'll be exercising even more control over them. 2. This is the only App in this database and DB access is strictly controlled by the App. 3. Because of a Double Logon mechanism, Users only know their App Login, not the Oracle Login by which they actually create a session in the database. Also, the way the Oracle Logins are created - passwords are created behind the scenes and stored encrypted - not even the App Administrator knows anyone's password, much less the Users. Also, since the App has over 50,000 tables, I can't easily Grant/Revoke Object Privileges on that many tables each time a User switches SchoolYears. I'm confident that this will work. However, my final fallback solution would be to write a PL/SQL procedure that would create 50,000 triggers that would call a security package to check the User's UserClass and either fail or let the DML succeed. It would be pretty simple, but I don't want to mess with that if I don't have to. BTW, I did try switching the User's UserClass (this App's equivalent of a Role) when a SchoolYear change was made, but it only reads UserClass at Login (like Oracle and Default Roles), so the switch didn't help for the current Session. Thanks for all your suggestions. I'll let you know the final outcome. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] d.tenet.edu To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc:
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
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 procedure, none of the roles are enabled; therefore he will not be able to
Re: Union quries: INTERSECT, MINUS, etc
Jonathon, I've got a query for you that uses all 3 set operators at once! I wrote it to compare two different versions of our 3rd Party Student Information System (SASI) in two different databases. We were getting ready to upgrade Production, having already upgraded a Test instance. The query hit the local schema, as well as the remote schema across a DB Link. The results of this query and a couple of others that showed brand-new tables/columns and dropped tables/columns helped our programmers figure out which of their reports, etc. needed modifications. I was impressed at performance, considering it queried across a DB Link, but mainly because this horrendous mess of an application has over 50,000 tables (User_Tab_Columns has over 1.4 million rows!). One of the DBs is on HP-UX, but the Production DB is on Win2k. Anyway, hope this is interesting enough. ;-) BTW, if you can find a way to improve it, please let me know. I sort of threw it together, knowing it would be a one-time thing, so it could probably be made better with some expert critique. /* Get a list of columns that have changed from SASI 4.5 to 5.0 for tables that are present in both versions only for the current school year. List only the first 4 characters of the table names, since all campuses will be the same. */ Spool SASI_45_50_Table_Compare.txt ( Select Substr(TABLE_NAME,1,4) Table -- New 5.0 Columns ,COLUMN_NAME Column ,'5.0'Ver ,DATA_TYPEDType ,DATA_LENGTH DLn ,DATA_PRECISION DPr ,DATA_SCALE DSc ,NULLABLE N? From User_Tab_Columns Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' AndTable_Name In ( Select Table_Name --...for Tables in both 4.5 and 5.0 From User_Tables Intersect Select Table_Name From [EMAIL PROTECTED] ) Minus --...remove unchanged columns ( Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'5.0' -- Constant allows Minus to work ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From User_Tab_Columns Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' Intersect Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'5.0' -- Constant allows Minus to work ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From [EMAIL PROTECTED] Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' ) ) Union ( Select Substr(TABLE_NAME,1,4) Table-- Old 4.5 columns... ,COLUMN_NAME Column ,'4.5'Ver ,DATA_TYPEDType ,DATA_LENGTH DLn ,DATA_PRECISION DPr ,DATA_SCALE DSc ,NULLABLE N? From [EMAIL PROTECTED] Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' AndTable_Name In ( Select Table_Name From User_Tables Intersect Select Table_Name From [EMAIL PROTECTED] ) Minus ( Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'4.5' ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From User_Tab_Columns Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' Intersect Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'4.5' ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From [EMAIL PROTECTED] Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' ) ) / Spool Off Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jonathan Gennick [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: Union quries: INTERSECT, MINUS, etc [EMAIL PROTECTED] .com
RE: should you seperate indexes from tables in seperate datafiles?
Yes, complicated with as simple a combination as tables and their indexes. Consider even more variables introduced by using LOBs and interMedia Text. In a past job, I used to make myself crazy analyzing and placing all the segments involved with interMedia-indexed CLOBs. Here's what I came up with: - the main table on its own spindle - the out-of-line CLOB segments on a 2nd spindle - the regular indexes for the main table on a 3rd spindle - the I table part of the interMedia index on a 4th spindle - the K, R and N tables and I index segments on a 5th spindle This was all on 22 JBODs (36GB each) under 8.1.7 / Win2k. The 13 million CLOB documents that were online and indexed made the database about 200GB. It was beautiful (to this nerd) to watch the disk I/O on the Win2k Performance Monitor as different queries would hit different parts of those segments at different times. Though it probably wasn't, the PerfMon graphs looked like there was a lot of parallel work going on across all those spindles. That database was used by only one or a very vew users at a time, doing similar queries, so I think performance benefitted from that segment separation. I would definitely agree, though, that with dozens or hundreds of concurrent users, I would have had to closely monitor tablespace / datafile I/O and shuffle datafiles around to better distribute load across available spindles. That would especially hold true if user activity coincided with the nightly loading and reindexing of up to 250,000 new CLOBs. No particular point here, but I/O distribution was a consideration for segment-to-tablespace mapping, even though an equal (or greater) consideration was differing extent sizes - 1MB for tables and regular indexes 100MB for the CLOB segments....all on LMTs, of course. Here at AISD, almost all our 8.1.7 databases live on the 14 RAID-5 LUNs presented by our HP XP-512 array (that's just something we've gotta live with, though I'm definitely a BAARFist). Most of the DBs are on 3 HP-UX boxes, but one is on Win2k. I've not yet had a chance to map all the pieces of all the DBs to see which pieces share which physical drives, but I/O hasn't seemed to hurt DB performance. Bad performance continues to be more than adequately addressed by the horribly-inefficient SQL produced by our two 3rd Party apps (Student Info. System and Financials System). I guess that goes to show that segment distribution - even on RAID-5 - is an insignificant factor when compared to bad SQL (producing tens of millions of unnecessary logical I/Os, in this case). Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Cary Millsap [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sos.com cc: Sent by: Subject: RE: should you seperate indexes from tables in seperate datafiles? [EMAIL PROTECTED] .com 07/15/2003 12:19 PM Please respond to ORACLE-L The thing that occurred to me a few years ago (as a result of a test designed by Craig Shallahamer) is that what disks do gets very, very complicated when you add users. On any system busy enough to
Re: security without using different usernames
Ryan, To make a particular schema the focus for a session: Alter Session Set Current_Schema = TheSchema ; Better yet, instead of proliferating the same schema for each client, convert the tables to partitioned tables in a single schema, with each partiion being for a specific client. That way you can add and remove clients by adding and dropping partitions. Each client's partitions could even be in tablespace(s) on separate drives to isolate their I/O from others. If all the schemas will always be identical, then partitioned tables are the way to go. If you'll customize the app and data structures for some clients, then you'll have to stick with individual schemas. If you use partitioned tables, use FGAC (Fine-Grained Access Control, AKA Row-Level Security) and Application Context to control security - see the Concepts doc and Application Developers Guide for details. Using those features of Oracle, you could limit each client to only their own partitions. I've used it in the past and It works very well. In fact, I'm in the last stages of designing and implementing an FGAC solution by which the 162 Campus-level Student Information databases in our District will be consolidated into a single Oracle database, with users at each Campus only seeing the tables for their School. We already have all 53,000 tables and 75,000 indexes (those are not typos!) of this 3rd Party App in an Oracle database. I'm just adding the security piece to keep them out of each other's business (or, bidness, as we say in Texas). ;-) Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/15/2003 05:29 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:security without using different usernames I know this is terrible design, but the GUI was created by a software engineering group that is seperate from the database group. Its not scalable. So Im trying to come up with a more scalable method. I have no power to change their gui. It rides on the database. I have to live with it. This is not a high enough transaction database to warrant seperate instances. We have a variety of customers. Each of them has their own versions of data. However, the schema is exactly the same. These tables can get huge, so we dont want to throw them all into the same schema. Right now, due to the fact that the GUI has a series of logins that are the same across clients, each client has its own instance. This isnt very scalable as we get more business. We have to create another instance and ingest data to it. Id like to find a way to get all the clients in the same instance with just different schemas and tablespaces. One thing I may have control over would be to slightly rename the executable. If you check v$session, in a client-server application the name of the product connecting to the database is recording. I can handle security based off of that. My question is what would be the best way? Cant do synonyms for this since its the same login. I think I saw somewhere that there is a session based 'set' command where you can say use this schema. I think it was on asktom and in reference to a question about public synonyms. I cant find it. Anyone know it? Also is it viable to base a context off of what is in v$sesion with a logon trigger? How would I 'redirect' all queries to a specific schema? To stress, I cant change the application. Different group with different skillsets. Any suggestions?
Re: security without using different usernames
Ryan, Segment Exchange is a very easy, instantaneous, method of moving data back and forth between standalone tables and partitioned tables. Transportable Tablespaces can ingest the data into your DB, but then it can be comfortably digested into partitions via Segment Exchange. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/15/2003 06:09 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: security without using different usernames management has handled it. namely my manager and the answer is no. Partitioning cant be done. we are ingesting data to this application via transportable tablespaces. So each schema has to be self-contained. - Original Message - From: Goulet, Dick To: Multiple recipients of list ORACLE-L Sent: Tuesday, July 15, 2003 6:59 PM Subject: RE: security without using different usernames Ryan, What would be much better is to create the single schema and partition the tables so that each customer's data lands into it's own partition. As for this other group, make some friends. It's a lot easier to get your problems and concerns addressed if the people your talking to are on a friendly basis with you. You can also bring up the problems of scaling to your management in terms of dollars needed for additional servers, memory, hard disk, and software. For some reason that is something pointy headed managers seem to understand, especially when you start talking about Oracle licenses at $40K per CPU. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- From: Ryan [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 6:29 PM To: Multiple recipients of list ORACLE-L Subject: security without using different usernames I know this is terrible design, but the GUI was created by a software engineering group that is seperate from the database group. Its not scalable. So Im trying to come up with a more scalable method. I have no power to change their gui. It rides on the database. I have to live with it. This is not a high enough transaction database to warrant seperate instances. We have a variety of customers. Each of them has their own versions of data. However, the schema is exactly the same. These tables can get huge, so we dont want to throw them all into the same schema. Right now, due to the fact that the GUI has a series of logins that are the same across clients, each client has its own instance. This isnt very scalable as we get more business. We have to create another instance and ingest data to it. Id like to find a way to get all the clients in the same instance with just different schemas and tablespaces. One thing I may have control over would be to slightly rename the executable. If you check v$session, in a client-server application the name of the product connecting to the database is recording. I can handle security based off of that. My question is what would be the best way? Cant do synonyms for this since its the same login. I think I saw somewhere that there is a session based 'set' command where you can say use this schema. I think it was on asktom and in reference to a question about public synonyms. I cant find it. Anyone know it? Also is it viable to base a context off of what is in v$sesion with a logon trigger? How would I 'redirect' all queries to a specific schema? To stress, I cant change the application. Different group with different skillsets. Any suggestions?
Re: How to get user's IP address?
Anne,
I use database triggers to capture system events and send me email. I use
the Event Attributes, though I've not captured IP address. My 8.1.7 docs
say that you should use ora_client_ip_address, though it looks like that
Attribute is only available in an After Logon Event, so you'd need to
capture it in a PL/SQL packaged variable or a Global Temporary Table for
the duration of the session.
You can find info. on the Event Attributes in Chapter 13, Working with
System Events, in Oracle8i Application Developer's Guide - Fundamentals.
Hope this helps.
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Anne Yu
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L
.us [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: How to get user's IP address?
.com
06/24/2003 04:25
PM
Please respond to
ORACLE-L
I want log changes when a user makes a change to a view, proc, func,
package, etc.
Basically, I have a trigger to capture the date, schema name and computer
name or IP address. However, I could not get the IP address or host name
out of Oracle.Can anyone help?
E.g. Query:
Select sys_context('USERENV','IP_ADDRESS') from v_$session;
Return NOTHING :(
Many thanks
--
Author: Anne Yu
INET: [EMAIL PROTECTED]
--
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: anyone have any soundex scripts?
You might consider an interMedia Text index on the column(s) of interest. The interMedia Fuzzy Search and scoring functionality (and others) may prove more flexible than Soundex. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent by: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: .com Subject: anyone have any soundex scripts? 06/09/2003 07:49 AM Please respond to ORACLE-L I was on a project a few years ago where we used a soundex algorithm to determine and eliminte duplicate data. For example we would have: 301 Fairfield Lane 301 Faerfield Lane Notice the typo? The soundex algorithm caught it. Unfortunately I forgot to grab a copy before I left. Everytime I do a google search on soundex I get a theory website explaining the math behind it. anyone have one written? or know where I can find one? It is incredibly useful. -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] -- 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: Autoallocate vs Uniform extent performance
Rachel, At a recent past job, under 8.1.6 on Win2k we had tables with out-of-line CLOB segments of 30,000 extents (1MB each). Every month we dropped one to make room for another (6 months of CLOB documents online). It always just took a few seconds for the drop. These were in DMTs. Later we switched servers and I changed to LMTs of 100MB Uniform Extents for the CLOB segments. Going from 30,000 to 300 extents for those hulks made no noticeable difference in query or interMedia indexing performance, nor did it noticeably change the time it took to drop the tables. Here at AISD, our student information database (SASI, for those in Education who know this 3rd party app) has over 47,000 tables and 70,000 indexes (typical abysmal design for a 3rd party app, eh?), many of them empty or with very few rows. A few months ago I rebuilt it under 8.1.7.4.6 (Win2k - it was previously at 8.1.7.0.0) with LMTs of 8KB Uniform Extents to save space. Surprisingly, only 40 or so segments have over 1000 extents. One, a consolidated Student table, has a little over 10,000 extents. We've noticed no problem at all with performance, etc. I've not been concerned about extent counts for several years now, and I've seen nothing convincing that I should be. Maybe I've just not hit the situation where it matters. That is not to say that extents don't matter, but it's only if they obey the stupid directives of uninformed duhvelopers, such as those of our 3rd party Financials system, where they used PctIncrease of 50. Like children and dogs, there are no bad extents, just bad designers.;-) Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Rachel Carmichael [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L o.com[EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: Autoallocate vs Uniform extent performance 04/04/2003 07:01 AM Please respond to ORACLE-L rumor hath it (as I've never actually had an object hit that high a number) that when you exceed 4K extents it's time to resize. This came from one of the instructors in Oracle University, one who is well-known to actually have more than a clue. He said this at the Data Internals class, before 9i was released. I have not seen his test results but I do know that tests done with DMTs have shown that large numbers of extents (I believe Kevin Loney tested with 60K extents, and I vaguely remember a conversation with Cary where he said he had also tested large numbers) are a problem during operations that empty a lot of extents (think large deletes) because of thrashing on FET$ and UET$. Since an LMT doesn't access those tables by design, I would think that that problem goes away. -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] -- 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: create interMedia index problem
Guang,
Is your RESINDEX tablespace a Locally-Managed Tablespace with Uniform
Extents of less than 3 database blocks? If so, that's your problem.
InterMedia indexes consist of some LOB segments and those require extents
of at least 3 database blocks - at least in 8i.
If not, I don't know.
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Guang Mei
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L
Sent by: [EMAIL PROTECTED]
[EMAIL PROTECTED] cc:
Subject: create interMedia index
problem
03/27/2003 10:53
PM
Please respond to
ORACLE-L
Hi:
I have this problem on our production server and I don't know too much
about InterMedia stuff. We have oracle 8173 on Sun Solaris 2.8.
[EMAIL PROTECTED] desc DRUGDATA;
Name Null?Type
-
IDNOT NULL NUMBER
DRUGREPID NOT NULL NUMBER
FIELD NOT NULL NUMBER
TEXT VARCHAR2(4000)
DRUGTERMID NUMBER
DATATYPEIDNOT NULL NUMBER
ADD_FILE NOT NULL VARCHAR2(32)
ADD_DATE NOT NULL DATE
DEL_FILE VARCHAR2(32)
DEL_DATE DATE
STATUSNOT NULL CHAR(1)
ORDERBY NOT NULL NUMBER
[EMAIL PROTECTED] select count(*) from DRUGDATA;
COUNT(*)
--
0
TABLESPACE_NAME USED-Kb ALLOC-KbUSED% SEGS EXT NEXTEXT
--- --- -- - -
DATA 3,000 1,048,576 .3 1 1 504
INDEXES 10,664,424 14,680,064 72.6 48011 693,080
PERFSTAT 105,728 2,097,1525.056 150 128
PROTEOME 164,872 1,048,576 15.75219 25,600
RBS5,131,360 8,388,608 61.2 6 8002,048
RESCTX43,832 6,803,456 .66628 504
RESDATA 34,470,408 46,137,344 74.7 361 2319
RESINDEX 41,046,376 69,206,016 59.3 300 1063 512,000
SYSTEM74,320 153,600 48.4 401 1151,120
TEMP 10,224,960 10,240,000 99.9 1 20455,000
YPD 12,769,224 16,777,216 76.1 249 # 292,976
TABLESPACE_NAME USED-Kb ALLOC-KbUSED% SEGS EXT NEXTEXT
--- --- -- - -
YPDCUST0 1,048,576 .0 0 00
Then I ran
create index DRUGDATAINDEX_TEXT on DRUGDATA (TEXT)
indextype is ctxsys.context
parameters ('LEXER ctxsys.ISILEX WORDLIST ctxsys.ISIWORDLIST
STOPLIST ctxsys.ISISTOP storage isistore memory 50M');
I got
create index DRUGDATAINDEX_TEXT on DRUGDATA (TEXT)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-2: interMedia Text error:
DRG-50857: oracle error in drixtab.create_index_tables
ORA-01658: unable to create INITIAL extent for segment in tablespace
RESINDEX
ORA-06512: at CTXSYS.DRUE, line 126
ORA-06512: at CTXSYS.TEXTINDEXMETHODS, line 78
ORA-06512: at line 1
and I got from alert_log file:
ORA-1652: unable to extend temp segment by 128000 in tablespace
RESINDEX
I did coalesce on all tablespace and added another 2G datafile on RESINDEX
tablespace, I still got the same
Perl Use Net8 Encryption?
A Perl client app on Linux can successfully access an 8.1.7.4 DB via a listener on a non-standard port through a firewall. The Perl developer figured out where he has to configure the non-standard port# to connect to the DB. However, we want this client to access the DB using native Net8 encryption, which works just fine using SQL*Plus from the client. We can't force the DB to require encryption, since there are lots of clients inside our firewall that don't have Net8 encryption configured. Since the Perl connection is specified outside of tnsnames.ora and sqlnet.ora, how can I ensure that the Perl connection will use Net8 encryption? BTW, I don't speak Perl and he doesn't speak Oracle. TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] -- 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: DBV
Jeremiah, Perhaps the fact that my experience was with Personal Oracle 7.3.2.3 on Win95 explains the hosed database - Win95 not being the best platform, to say the least. I'll definitely try it again - on a test database, of course. BTW, I can't find any way to tell DBV to verify more than one datafile at a time. Is there? Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jeremiah Wilton [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L y.net[EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: DBV 02/26/2003 06:34 PM Please respond to ORACLE-L ... So unless Jack had an OS that writes when you ask it to read, there's no way that DBV could have done anything bad to your database. ... -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- 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: DBV
Dan, That must be new for 9i, since my 8.1.7 docs say it is an offline utility. I remember hosing an entire 7.3 test database years ago when I first ran DBV on it while it was up and open - can't remember the errors, but the DB was unusable. One of the (too) many times I learned to read the docs more carefully. :-( Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Daniel W. Fink [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L .com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: DBV 02/26/2003 01:54 PM Please respond to ORACLE-L Yes, you can. It may report that a block is influx if the block is being written while dbv is looking at it. If you encounter this error, run it again. If it does not report the same block, you are in the clear. As with any i/o intensive process, run it off-hours. Dan Fink Breno A. K. Magnago wrote: Hi, Can I use the program DBV on a production datafile (read and write), without take offline the tablespace ? -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] -- 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:
Ken, Do all the following as a way to minimize MTTR (Mean Time To Recovery) for a variety of scenarios and, by the way, they happen to use a lot of disk: ;-) 1. Mirror your online and archived redo logs to multiple drives 2. Put backup copies of drive A' datafiles on drive B (or C) and vice versa. Do this on multiple drives as well to guard against multi-disk failure. 3. Do frequent exports to support object-level recovery. All that chews up disk and, actually, comes in pretty handy when you need them. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Ken Heng [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L m[EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: 02/18/2003 05:23 PM Please respond to ORACLE-L Hi, I was wondering how DBAs are coping with these new large disks that are availableyou can purchase 36gb, 72gb, etc. You can fit a whole database on one of these. But with all the performance and redundancy considerations, you wouldn'tso what do you do with the free space? Or how do you tell your bean counter that out of that 72gb you are only going to use 10gb so you need a couple of these? Rgds, Ken Heng -- Author: Ken Heng INET: [EMAIL PROTECTED] -- 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: Service startup on Windows 2000 fails
Lisa, What error do you get when you try lsnrctl start from the command line? Have you tried oradim from the command line to start the DB service? Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Koivu, Lisa Lisa.Koivu@efairTo: Multiple recipients of list ORACLE-L field.com[EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Service startup on Windows 2000 fails 02/19/2003 08:08 AM Please respond to ORACLE-L Good morning everyone, Windows 2000 sp3, ver 8.1.7.2 I have NO IDEA what is going on here. I can't start any of the services associated with Oracle, not with my logon, not with local admin, not with a local account with appropriate permissions, nothing. Here's what I've tried: Followed note on metalink stating to add local security policies: Act as part of operating system, increase quotas, log on as batch job, replace a process level token, and rebooted. All logons are part of the administrator and the ORA_DBA group. I am able to log in as each of these local accounts, the accounts are not locked or messed up. I have double-checked the password in the properties tab. Local system account also fails with same error. Here's the error I receive when I try to manually start the listener service: Could not start the OracleOraHome81TNSListener service on Local Computer. The service did not return an error This could be an internal Windows error or an internal service error. If the problem persists, contact your system administrator. I am clueless and so is the sysadmin. I am just thankful this is a test system instead of production... I know next to nothing about windows. Any suggestions would be helpful. Thanks Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 Office: 954-935-4117 Fax:954-935-3639 Cell:954-683-4459 -- 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: Skipping a table on import
Craig, I had to smile when you stated you had 400 tables and were reluctant to list them in the export or import parfile. Our 3rd party Student Information system has over 47,000 tables. I frequently do exports and/or imports with parfiles listing several thousand tables. It's not a problem at all. Spool the following to a text file and incorporate it (with minor editing to add the parens and remove the first comma) into your parfile... Select ',' || Table_Name From User_Tables Where Table_Name 'theTableYouDoNotWant' ; Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Craig Healey [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L om [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Skipping a table on import 02/07/2003 04:04 AM Please respond to ORACLE-L Hi, I'm trying to import the live schema into a test schema, but 1 or 2 tables have a lot of data and it's taking hours. Is there a way to skip certain tables during an import? We're using 8.1.7 We have 400 tables, so I'd like to avoid having to list all the tables in the export statement. I have to pre-create the tables to put them in the correct tablespace, so could I alter the table definitions to force import to skip particular ones? Any help is appreciated. Craig Healey -- Author: Craig Healey INET: [EMAIL PROTECTED] -- 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: Skipping a table on import
John, A quick check turned up a medium parfile for me - only 1201 tables listed. The file size is 14KB, so I guess it's not an issue for 8i. Even on larger parfiles I've never gotten an error - for neither HP-UX nor Win2k. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] John.Hallas@voda fone.co.uk To: Multiple recipients of list ORACLE-L Sent by: [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject: RE: Skipping a table on import 02/07/2003 07:38 AM Please respond to ORACLE-L Jack, I always knew there was a limit on the size of a parfile which I had assumed was a maximum no of tables allowed. However I recently found out that it is a byte limit on the overall file parameter. (It was 8K in 7.x ) Do you not hit this limit with several thousand table names (presumably not, otherwise you would not do it ) ?? John -Original Message- [mailto:[EMAIL PROTECTED]] Sent: 07 February 2003 13:04 To: Multiple recipients of list ORACLE-L Craig, I had to smile when you stated you had 400 tables and were reluctant to list them in the export or import parfile. Our 3rd party Student Information system has over 47,000 tables. I frequently do exports and/or imports with parfiles listing several thousand tables. It's not a problem at all. Spool the following to a text file and incorporate it (with minor editing to add the parens and remove the first comma) into your parfile... Select ',' || Table_Name From User_Tables Where Table_Name 'theTableYouDoNotWant' ; Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Craig Healey [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L om [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Skipping a table on import 02/07/2003 04:04 AM Please respond to ORACLE-L Hi, I'm trying to import the live schema into a test schema, but 1 or 2 tables have a lot of data and it's taking hours. Is there a way to skip certain tables during an import? We're using 8.1.7 We have 400 tables, so I'd like to avoid having to list all the tables in the export statement. I have to pre-create the tables to put them in the correct tablespace, so could I alter the table definitions to force import to skip particular ones? Any help is appreciated. Craig Healey -- Author: Craig Healey INET: [EMAIL PROTECTED] -- Author: INET: [EMAIL PROTECTED] -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] -- 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: Size of a Long Field
Ed, Perhaps you could use the DBMS_SQL Define_Column_Long and Column_Value_Long procedures to manipulate the Long? Converting the Long to a LOB using To_LOB function, then manipulating it with the DBMS_LOB package might do, too. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Ed Bittel grep_oracle@hotmTo: Multiple recipients of list ORACLE-L ail.com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Size of a Long Field 02/05/2003 08:53 AM Please respond to ORACLE-L How do you determine the size, in bytes, of a long field? Please. No tape measure jokes. Ed -- 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: Size of a Long Field
Dennis, Using VSize on a Long results in: ORA-00997: illegal use of LONG datatype Just tried in on Trigger_Body in DBA_Triggers. (8.1.7.0.0 on HP-UX). Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] DENNIS WILLIAMS DWILLIAMS@LIFETOTo: Multiple recipients of list ORACLE-L UCH.COM [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Size of a Long Field 02/05/2003 01:29 PM Please respond to ORACLE-L Ed - Look up the function VSIZE in the documentation. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 05, 2003 8:53 AM To: Multiple recipients of list ORACLE-L How do you determine the size, in bytes, of a long field? Please. No tape measure jokes. Ed -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] -- 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: why these tables can not be seen from user_segments?
Guang Mei, All the DR$ objects are interMedia Index segments - some are tables, some are indexes, some are IOTs. Check out OTN and/or MetaLink for docs on how each segment type contributes to the interMedia indexing process. It's quite interesting. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] gmei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L Sent by: [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject: why these tables can not be seen from user_segments? 02/04/2003 09:24 AM Please respond to ORACLE-L Hi: Oracle 8173 on Sun Box. SQL select count(*) from user_tables; COUNT(*) -- 326 SQL select count(*) from user_segments where segment_type='TABLE'; COUNT(*) -- 318 SQL select segment_name from user_segments 2 where segment_type='TABLE' 3 and segment_name like 'DR$%' 4 order by segment_name; SEGMENT_NAME DR$ABSTRACT_TEXT$I DR$ABSTRACT_TEXT$R DR$COREREF_ABSTRACT$I DR$COREREF_ABSTRACT$R DR$COREREF_TITLE$I DR$COREREF_TITLE$R DR$TEST_CONTEXT$I DR$TEST_CONTEXT$R 8 rows selected. SQL select table_name from user_tables 2 where table_name like 'DR$%' 3 order by table_name; TABLE_NAME -- DR$ABSTRACT_TEXT$I DR$ABSTRACT_TEXT$K DR$ABSTRACT_TEXT$N DR$ABSTRACT_TEXT$R DR$COREREF_ABSTRACT$I DR$COREREF_ABSTRACT$K DR$COREREF_ABSTRACT$N DR$COREREF_ABSTRACT$R DR$COREREF_TITLE$I DR$COREREF_TITLE$K DR$COREREF_TITLE$N TABLE_NAME -- DR$COREREF_TITLE$R DR$TEST_CONTEXT$I DR$TEST_CONTEXT$K DR$TEST_CONTEXT$N DR$TEST_CONTEXT$R 16 rows selected. Why tables like DR$ABSTRACT_TEXT$K, DR$ABSTRACT_TEXT$N do not have their corresponding segments? What are they and their functions? TIA. Guang Mei -- Author: gmei INET: [EMAIL PROTECTED] -- 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: Case of the Missing Rows
Rick, Might there be a Scheduled Task or DBMS_Job that runs shortly after the load and deletes rows? Just a thought - such automagic processes have bitten me in the past. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Weiss, Rick [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L us [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Case of the Missing Rows 01/27/2003 05:53 PM Please respond to ORACLE-L I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss -- 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:RE: dbms_job - running jobs every 15 minutes
Actually, Interval is evaluated at the beginning of the job according to the docs. I've not seen anyone mention the real cause behind DBMS_Job creep. That is the setting of Job_Queue_Interval which, by default, is 60 seconds. So your jobs will run 1 minute later each time unless you set Interval to evaluate to an absolute. If someone's set Job_Queue_Interval longer, the creep will be longer as well. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] Sent by: To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject: Re:RE: dbms_job - running jobs every 15 minutes 01/22/2003 10:34 AM Please respond to ORACLE-L One potential problem with DBMS_JOBS as is being discussed here is that Oracle computes the next_date at the end of the job. They do that so that if a job runs longer than it's schedule interval the two invocations will not run into each other. Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. Dick Goulet -- Author: INET: [EMAIL PROTECTED] -- 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: primary key, local partition index - prefixed/non-prefixed
Sean,
In your case the PK would need to be on (Last, ID) in order for the index
to be Local. Otherwise, it's gotta be Global or else you need to change
your partition key.
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
From
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L
heavy.com[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: primary key, local partition
index -
prefixed/non-prefixed
01/10/2003 02:18
AM
Please respond to
ORACLE-L
Hi all...
I'm messing around with partitioning, and trying to create a primary key
index which is locally partition to match the underlying table. Table
looks something like this:
create table mytable (
id number,
last date)
tablespace data
storage (initial 64k next 64k pctincrease 0)
partition by range (last)
(partition lastq1_03 values less than(to_date
('01-APR-2003','DD-MON-')),
partition lastq2_03 values less than(to_date
('01-JUL-2003','DD-MON-')),
partition lastq3_03 values less than(to_date
('01-OCT-2003','DD-MON-')),
partition lastq4_03 values less than(to_date
('01-JAN-2004','DD-MON-')))
enable row movement;
SQL alter table mytable add primary key (id) using index
tablespace INDX local enable;
alter table mytable add primary key (id) using index
tablespace INDX local enable
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a
UNIQUE index
Basically what Oracle is saying is hey, your table is partitioned on last,
and you want to create a local index on id, no can do. So how *DO* I do
that, and for that matter, how do I manage with a foreign key constraint?
I've looked at the docs, but I really need a good example. If anyone can
point me in the right direction, I'd appreciate it.
Thanks,
Sean
--
Author: From
INET: [EMAIL PROTECTED]
--
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: Conversion from CLOB to RAW
Bill, I agree with Michael. You've already got the data in a suitable datatype. Why move it to a cumbersome, soon-to-be-obsolete datatype? You can use DBMS_LOB functionality on LOBs, not on Raw. I'd be so happy if the couple dozen tables in our 3rd party Student Information system that have Long or Long Raw columns had CLOB or BLOB columns instead. It would make converting them to partitioned tables much easier. I definitely vote to keep your CLOBs. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Michael Fontana [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L et [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: Conversion from CLOB to RAW 01/10/2003 02:15 PM Please respond to ORACLE-L At 11:30 AM 1/10/2003 -0800, Burke, William F (Bill) wrote: Here's where I get to ask the most likely simple question. I've inherited a database where it was built using a CLOB to hold XML data but we have now determined that was total overkill and want to move it to a RAW column or other suitable datatype. Looking for conversion issues or other alternatives. Since Oracle is moving us away from LONG and RAW datatypes, I assume you want to convert from CLOB to BLOB? BLOB is probably more storage-efficient, but since XML is made up of character data, I don't really understand the issue with keeping it a CLOB. What do you mean by overkill? -- 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: ORA-04031
David, What OS and Oracle version? At least in 8.1.7.0.0 on HPUX that error message could be the result of a bug - we get it every few weeks on our production financials database and have to bounce the DB. Flushing the Shared Pool every night doesn't prevent it. Eventually we'll patch to 8.1.7.4 where it's supposedly fixed. This from Metalink (didn't copy the ID of the doc I found this in): If you are receiving ORA-4031 errors which reference BAMIMA: Bam Buffer, please apply the 8.1.7.4 patchset. It is possible that you are hitting bug 2104071 which is fixed in that patchset. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Nguyen, David M david.m.nguyen@xTo: Multiple recipients of list ORACLE-L o.com[EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: ORA-04031 12/20/2002 12:48 AM Please respond to ORACLE-L I received ORA-04031 while exporting database. Can someone explain what causes it and how to fix it? . exporting post-schema procedural objects and actions EXP-8: ORACLE error 4031 encountered ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN :1 := SYS.DBMS_REFR...,PL/SQL MPCODE,BAMIMA: Bam Buffer) EXP-00083: The previous problem occurred when calling SYS.DBMS_REFRESH_EXP_SITES.schema_info_exp . exporting statistics Export terminated successfully with warnings. Thanks, David -- Author: Nguyen, David M INET: [EMAIL PROTECTED] -- 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: Re[2]: Happy Holidays!!
Well... I've got to take off here in a bit for our two week Winter Break (can't call it Chrismas Holiday - gotta be denominationally neutral), though I *will* get to come in the weekend of the 28th to rebuild our Student Information database. A five day Thanksgiving Holiday was hard to get used to. I'll also have to adjust to having the week of Spring Break off. Man, I didn't know working in the Public Sector could be such a transition! ;-) Everybody have a wonderful Winter Break! Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] Sent by: To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject: Re[2]: Happy Holidays!! 12/20/2002 12:31 PM Please respond to ORACLE-L Some people have all the luck!! Enjoy Dick Goulet Reply Separator Author: Thomas Day [EMAIL PROTECTED] Date: 12/20/2002 8:09 AM Happy holidays to all! Working for a large manufacturing company in the past, I got in the habit of taking the week between Christmas and New Years off. Since I work mostly in development databases I've been able to cultivate that habit. -- Author: Thomas Day INET: [EMAIL PROTECTED] -- Author: INET: [EMAIL PROTECTED] -- 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: SQL question avoiding 2 views and not in
Lisa, A couple of years ago, when I was a consultant, I implemented Application Context and Fine-Grained Access Control, AKA Row Level Security for a client. Since it causes a predicate to be appended to the Where clause of every SQL statement issued against the tables having a Security Policy, I guess performance could be impacted if you didn't index the columns referenced by the appended predicates. We never noticed a bit of degradation in our testing, but we were careful about the indexing. I left that project before it went into production, so I don't know the ultimate outcome. However, I'd sure use FGAC again, if the need arises, it works very well. Actually, I probably *will* use it on a couple of our 3rd Party apps here which don't enforce security to the degree that we require. I'll let y'all know how it performs. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Koivu, Lisa Lisa.Koivu@efairTo: Multiple recipients of list ORACLE-L field.com[EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: SQL question avoiding 2 views and not in 12/13/2002 03:38 PM Please respond to ORACLE-L Has anyone used context and fine-grained security? I seem to remember the performance hit was not minimal when using this functionality. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: Purging Managed Standby Database Archive Logs
Steve, It's pretty simple. No need to record anything - see the Order By Descending in the SQL below. Here are a batch file and a SQL script I used on an 8.1.7 Standby DB under Win2k. It ran reliably for months as a Scheduled Task once every hour and got rid of all applied logs, assuming that there would never be any more than 400 archived redo logs sent over from the main DB in a single hour. You could easily change it to shell scripts under UNIX, as well as the formatting of your archived redo log names. Delete_Applied_Archived_Redo_Logs_main.bat --- Set ORACLE_SID=MySID SQLPlus internal @C: \Oracle\Admin\MySID\Delete_Applied_Archived_Redo_Logs_sub.sql C:\Oracle\Admin\MySID\Delete_Applied_Archived_Redo_Logs_Delete.bat --- Delete_Applied_Archived_Redo_Logs_sub.sql --- Set FeedBack Off Set LineSize 200 Set PageSize 0 Set TrimSpool On Spool C:\Oracle\Admin\MySID\Delete_Applied_Archived_Redo_Logs_delete.bat Select 'Del U:\Oracle\OraData\MySID\Archive\ARC' || Trim(v.Seq) || '.LOG' From ( Select To_Char(Sequence#,'0') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum 401 ; Spool Off Exit --- Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Orr, Steve [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L m[EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Purging Managed Standby Database Archive Logs 12/12/2002 10:04 AM Please respond to ORACLE-L Any one have a ready-made routine to purge the unneeded archives which have been automagically applied to a managed standby database? I figure it needs to: 1. Query v$archived_log and v$log_history to get a list of the archive logs (v$archived_log.name) where sequence# [the max number you purged the last time]; 2. Cycle through the above list and remove the files; 3. Record the max(sequence#) from v$log_history for the next purge. Any other ideas/suggestions? AtDhVaAnNkCsE!!! Steve Orr Standing by in Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: Any way to script or document jobs defined to the NT/MS2000 Task
Ron, Saw your memo and fooled around with Task Scheduler a bit. All it is is Windows Explorer pointed at the Scheduled Tasks folder in Control Panel. To back up your Scheduled Tasks, simply single click on one or more of them (or use the Edit / Select All menu item, then de-select Add Scheduled Task), then choose the Edit / Copy To Folder menu item. A single *.job file is created for each Scheduled Task in the target folder. You can then copy those files into another Server's or PC's Scheduled Tasks folder in Control Panel. Those copied-in Scheduled Tasks then appear in the target's Task Scheduler with all their info. Glad you brought this up, since we have a Win2k server with LOTS of Scheduled Tasks. Now I know how to back them up. I just now followed the above procedure and now have all the Scheduled Tasks from that server in my own Task Scheduler on my PC. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Smith, Ron L. To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED][EMAIL PROTECTED] om cc: Sent by: Subject: Any way to script or document jobs [EMAIL PROTECTED]defined to the NT/MS2000 Task om 12/05/2002 08:24 AM Please respond to ORACLE-L We have started using the NT/MS2000 Task Scheduler instead of the 'AT' command to schedule jobs. I am concerned that if we lose the server we would lose the schedule and have to figure out how and When all the batch jobs were scheduled. Does anyone know a way to script or otherwise document jobs defined to the Task Scheduler? R. Smith If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: To_Number
Laura,
Are those really zeros in $34,000.05 or are they letter Os? If so use
Replace. (Beware of letter l being used instead of numeral 1 as well.)
Any leading or trailing spaces? If so use Trim(unit_cost).
Just a couple of quick suggestions.
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Burton, Laura
L. To: Multiple recipients of list ORACLE-L
BurtonL@prism[EMAIL PROTECTED]
plus.comcc:
Sent by: Subject: To_Number
[EMAIL PROTECTED]
om
12/05/2002
01:24 PM
Please respond
to ORACLE-L
I have a table which contains a Unit_Cost varchar2(16) which contains
$34,000.05. I can enter select to_number('$34,990.08','$999,999,999.99')
from dual; and the results is 34990.08. However when I enter select
to_number(unit_cost,'$999,999,999.99') from elas.qdr I get ora-01722:
invalid number.
Is there any other way to do this? I am trying to add a varchar2 field
that contains $ and commas. I thought the to_number function would convert
the data to a number field.
Thanks,
Laura
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
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: To_Number
Rachel,
We have two Third Party apps here for Finance and Student Information that
do ridiculous stuff like this so often, I just immediately jumped into fix
it mode without even questioning. Don't even ask why is our motto.;
-)
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Rachel
Carmichael To: Multiple recipients of list ORACLE-L
wisernet100@Y[EMAIL PROTECTED]
AHOO.COMcc:
Sent by: Subject: Re: To_Number
[EMAIL PROTECTED]
om
12/05/2002
03:49 PM
Please respond
to ORACLE-L
Am I the only one wondering why an obviously numeric field
(unit_cost???) is being stored as varchar?
--- [EMAIL PROTECTED] wrote:
Laura,
Are those really zeros in $34,000.05 or are they letter Os? If so
use
Replace. (Beware of letter l being used instead of numeral 1 as
well.)
Any leading or trailing spaces? If so use Trim(unit_cost).
Just a couple of quick suggestions.
Jack C. Applewhite
Burton, Laura
I have a table which contains a Unit_Cost varchar2(16) which contains
$34,000.05. I can enter select
to_number('$34,990.08','$999,999,999.99')
from dual; and the results is 34990.08. However when I enter select
to_number(unit_cost,'$999,999,999.99') from elas.qdr I get ora-01722:
invalid number.
Is there any other way to do this? I am trying to add a varchar2
field
that contains $ and commas. I thought the to_number function would
convert
the data to a number field.
Thanks,
Laura
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
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: image storage confusion ??
FWIW ... in my last job we stored 13 million CLOB documents (max size of 5KB, stored out-of-line in their own CLOB segments) in an 8.1.7.3 DB under Win2k (2 CPU HP NetServer with 4GB RAM and twenty-three 36GB drives; JBOD). The CLOB documents were all interMedia Text indexed. Contains queries returned results in a second or less, unless the query was not very specific and hit thousands of documents. Java processes loaded up to 250,000 new documents every night and the interMedia index was refreshed shortly thereafter - took up to a couple of hours. The front-end was Cold Fusion, so that's not applicable to your situation. This ~200GB database had a Standby DB on a sister machine. Everything worked very well. RMan level 0 backups took a couple of hours. I vote for out-of-line BLOBs in their own tablespace(s) on as many spindles as you can afford. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Stephane Paquette stephane_paquette@ To: Multiple recipients of list ORACLE-L YAHOO.COM [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: image storage confusion ?? 12/03/2002 08:32 AM Please respond to ORACLE-L You should/must do a benchmark. If not, how can you justify your decision ? If your management do not ask for a benchmack then you have bad management (and that's no good for you either...) Anyway, in a previous life, we did a benchmarck with files of similar size and it was faster on the OS. The environment was Oracle 816/Sun with Vignette Story Server. HTH --- oraora oraora [EMAIL PROTECTED] a écrit : Guys , i posted this already and this time my question is a bit different . I have to store 20,000,000 images of 5k each either in DB ( on win2k) or linux o/s file system. the images are to be displayed over mobile phones.so the time to retrieve the images should be minimum. for this to be achieved , i am confused , whether the images should be stored in o/s file system or in the DB ? --selecting a file from linux o/s file system --- or --querying it from oracle DB ( on win2k) --- which of the above will be faster ? all these will be done with java . this being condition , i would like to know ur suggestion guys. my DB is oracle 8.1.6 on Win2k. TIA -- Author: oraora oraora INET: [EMAIL PROTECTED] = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: Dynamic views
David, How about Dynamic SQL in an Instead Of trigger? Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] david hill david.hill@lec To: Multiple recipients of list ORACLE-L hateau.ca [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Dynamic views m 11/20/2002 12:48 PM Please respond to ORACLE-L Hi Guys I'm trying to see if I can get around paying oracle $50,000 for partitioning I have a huge table and I want to partition it on date so I created 12 tables _JAN _FEB And so on But I want to create a view that will be used for inserting so it always Points to the current month without having to recreate the view every month. Is there anyway anybody can think of doing this? Thanks David Hill DBA Le Chateau -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Slow Inserts
Oracle 8.1.7.0.0 on Win2k (4 CPU, 4GB RAM) A 3rd Party app. is experiencing very slow performance on one of our databases. I think I've nailed it down to slow, row-at-a-time inserts. The same app. performs very fast on another DB with LMTs. After switching the tables and indexes in the slow DB to LMT, we still have slow performance. The extract from the SQL_Trace below is the slow statement. It actually takes about an hour to insert a few hundred rows. You can watch the trace file slowly grow with executions of this statement. There is only one User hitting this table (with its single index). The table is initially empty, so it's not extending. Anybody have any ideas as to the cause of this slow Insert activity? BTW, I ran BStat and EStat during this time and nothing jumps out at me. Also, we ran it with CHOOSE (and fresh statistics) - same slowness. INSERT INTO SASI.W_ENROLL (SCHOOLNUMBER ,STULINK ,FROMDATE , TODATE ,GRADE ,TRACK ,PEIMSTRACK ,PEIMSSCHOOLNUMBER , ADAELIGIBILITYCODE ,ISENTERDATE ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2735 5.88 30.00 0 0 0 0 Execute 2735 1.16 1.24 3 2779 8571 2735 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 5470 7.04 31.24 3 2779 8571 2735 Misses in library cache during parse: 0 Optimizer goal: RULE Parsing user id: 399 (TXSRC) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: RULE TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: Convert TEMP tablespace from datafiles to tempfiles
Rich, If you've got the disk space, do it while the DB is up. Much simpler. Create a new Temporary LMT - call it NewTemp - with the appropriate tempfiles. Switch everyone to NewTemp by spooling and executing this and running the spooled file: Select 'Alter User ' || UserName || ' Temporary Tablespace NewTemp ;' From DBA_Users; After a day or two, or when you can determine that no one is using the old Temp tablespace, offline and drop Temp. If you've just got to have the Temp tablespace named Temp, repeat the above steps to create a new Temp LMT. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jesse, Rich Rich.Jesse@qti To: Multiple recipients of list ORACLE-L world.com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Convert TEMP tablespace from m datafiles to tempfiles 11/06/2002 09:56 AM Please respond to ORACLE-L Hey all, I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to take the opportunity to convert the datafiles of the TEMP tablespace to tempfiles. My reason for this change is primarily to get the TEMP tablespace LMT, but also to shrink our hotbacks w/o modifying the working script. I've been trying to reason out this task in my head, as I can't find much on MetaLink, and here's what I've got so far: 1) Shutdown DB. 2) Backup DB. 3) Startup restricted. 4) Offline tablespace TEMP. 5) Drop tablespace TEMP. 6) Create new temporary TEMP LMT. 7) Bounce instance. I don't yet have an arena to try this in. Will users whose assigned TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments on the procedure? TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: LOCALLY MANAGED TABLESPACE
Seema, Our production Student Information database (8.1.7 under Win2k) has 40,000 tables and 60,000 indexes. It's a third party app designed for dBaseIV - go ahead and laugh, we do all the time (when we're not crying). Anyhow, we have to regularly clone out the data to a couple of other databases, both on HPUX. The different OS means we have to use export/import, not restore from hot backup or transportable tablespaces, to move the data. Both of the recipient databases (with dictionary-managed tablespaces) started out needing about 2 hours to drop all the tables and indexes (tables change, so we can't truncate) and about 6 hours to import the full dataset. After several refreshes the time requirement grew to almost 30 hours for each DB. I think the data dictionary tables that record info. about tables, indexes, and extents (someone else on this list could probably name the very ones) got totally mucked up (a techical term) after so many massive drops and creates. After I recreated the recipient tablespaces as locally-managed, drop and import times returned to 2 and 6 hours, respectively, and have remained there through numerous subsequent refreshes. Needless to say, we are *very* happy with LMTs. BTW, our Student Info. system is clunky (we're going to redesign it into a couple hundred partitioned tables with 40,000 views and 120,000 Instead-Of Triggers, but that's another story) but several thousand teachers and administrators basically like the way it manages our 80,000 students. How's that for a client base? Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 [EMAIL PROTECTED] Seema Singh oracledbam@ho To: Multiple recipients of list ORACLE-L tmail.com[EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: LOCALLY MANAGED TABLESPACE om 10/24/2002 01:49 PM Please respond to ORACLE-L Hi I am thinking to change our few dictinary manages tablespace to locally managed tablespace.Can any one experienced any issues with locally managed tablespace? Do any one experience what gain after changing to locally managed tablespace? Thx -Seema -- Author: Seema Singh INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: System Tablespace and Autoextend
Sam, Autoextend caused tablespace corruption for me once, but it was over 5 years ago with Personal Oracle 7.3.2.3 on Win95 - not the most reliable OS that Oracle has ever ported to. ;-) We have several 8.1.7 databases here, on both Win2k and HPUX. Autoextend, even on System, has caused no problems that I'm aware of. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 [EMAIL PROTECTED] Sam Bootsma [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: System Tablespace and Autoextend om 10/24/2002 01:26 PM Please respond to ORACLE-L Hello All, I have heard several times that if the SYSTEM tablespace runs out of space and needs to autoextend (assuming autoextend is turned on for the data file), then you run the risk of the database crashing and of data dictionary corruption. I have never personally encountered this problem, so I have no experience on what actually does happen. I looked in metalink for documents on this, but turned up nothing. Does anybody have experience on the dangers of allowing the SYSTEM tablespace to autoextend and also any documents on Metalink or OTN that describe this problem? We are running Oracle versions 7.3.4, 8.0.5, 8.1.7, and 9.2. All our Oracle versions are running on Windows NT (or Windows 2000). Thanks for any feedback. Sam Bootsma, OCP [EMAIL PROTECTED] -- Author: Sam Bootsma INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: No Nulls? (was: Warehouse design: snowflake vs star schemas)
Rich, Several years ago in the old Database Programming and Design magazine (a really useful publication, IMHO - too bad it's gone), C. J. Date and another database guru (I can't remember his name) carried on a debate that lasted several months about the badness (Date) vs goodness (the other guy) of Three Valued Logic in general and Nulls in particular. Date even wrote an article showing how to design a database with all Not Null columns. The issues are many, but, for me, the bottom line is that it's easier to live with the evils of Nulls - including the extra coding you have to do to make sure they're handled appropriately for each SQL statement - than to do all the upfront work required to eliminate them. Theoretically speaking, I think Date is totally correct. Practically speaking, I'm too lazy to implement his ideas. ;-) Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 [EMAIL PROTECTED] Jesse, Rich Rich.Jesse@qti To: Multiple recipients of list ORACLE-L world.com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: No Nulls? (was: Warehouse design: m snowflake vs star schemas) 10/14/2002 12:33 PM Please respond to ORACLE-L On the link below is this quote from C.J.Date: I don't want you to think that my SQL solution to your problem means I advocate the use of nulls. Nulls are a disaster. Of course, he doesn't expound upon it (probably not a need except for dummies like me). Anyone care to comment? (On the quote, not on my dumminess...) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Robson, Peter [mailto:[EMAIL PROTECTED]] Sent: Monday, October 14, 2002 4:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: Warehouse design: snowflake vs star schemas Just for the record (and perhaps to confirm that there are always two sides to a story). Readers may like to see the article Chris Date wrote to Ralph Kemball on the subject of business rules and integrity constraints: http://www.dbdebunk.com/kimball1.htm -- Author: Jesse, Rich INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: No Nulls? (was: Warehouse design: snowflake vs star schemas)
Rich, I piqued my own curiosity and looked at Database Programming and Design On-Line. Below is a link to a lengthy response (1995) by Date and others to Tom Johnston's article defending MVL (Multi-Valued Logic). Plenty to chew on. http://www.dbpd.com/vault/dateresp.htm Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 [EMAIL PROTECTED] Jack Applewhite To: [EMAIL PROTECTED] cc: 10/14/2002 Subject: Re: No Nulls? (was: Warehouse 01:32 PM design: snowflake vs star schemas)(Document link: Jack Applewhite) Rich, Several years ago in the old Database Programming and Design magazine (a really useful publication, IMHO - too bad it's gone), C. J. Date and another database guru (I can't remember his name) carried on a debate that lasted several months about the badness (Date) vs goodness (the other guy) of Three Valued Logic in general and Nulls in particular. Date even wrote an article showing how to design a database with all Not Null columns. The issues are many, but, for me, the bottom line is that it's easier to live with the evils of Nulls - including the extra coding you have to do to make sure they're handled appropriately for each SQL statement - than to do all the upfront work required to eliminate them. Theoretically speaking, I think Date is totally correct. Practically speaking, I'm too lazy to implement his ideas. ;-) Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 [EMAIL PROTECTED] Jesse, Rich Rich.Jesse@qti To: Multiple recipients of list ORACLE-L world.com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: No Nulls? (was: Warehouse design: m snowflake vs star schemas) 10/14/2002 12:33 PM Please respond to ORACLE-L On the link below is this quote from C.J.Date: I don't want you to think that my SQL solution to your problem means I advocate the use of nulls. Nulls are a disaster. Of course, he doesn't expound upon it (probably not a need except for dummies like me). Anyone care to comment? (On the quote, not on my dumminess...) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Robson, Peter [mailto:[EMAIL PROTECTED]] Sent: Monday, October 14, 2002 4:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: Warehouse design: snowflake vs star schemas Just for the record (and perhaps to confirm that there are always two sides to a story). Readers may like to see the article Chris Date wrote to Ralph Kemball on the subject of business rules and integrity constraints: http://www.dbdebunk.com/kimball1.htm -- Author: Jesse, Rich INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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
Re: count(*)
If you can modify the application code, how about incrementing a sequence for each row inserted? Not to hamper performance with a Select MySequence.NextVal From Dual you could select from - I vaguely remember this from a thread a few weeks ago - x$dual (or something like that - I can't remember how to set this up, maybe you could check the archives). Then you could simply query User_Sequences.Last_Number every 10 minutes to see how many rows you've inserted. If you can't modify the application code, how about an After Insert Row trigger on the table that would increment the sequence? It might work for you. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 [EMAIL PROTECTED] Rishi.Jain@verizonwi reless.com To: Multiple recipients of list ORACLE-L Sent by:[EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject: count(*) 09/19/2002 01:28 PM Please respond to ORACLE-L Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions or something similar). Actually this app is being ported from Informix. Informix can somehow keep a trak of the count(*) of a table in its header somewhere. And yes I have tries count(1) , count(indexed_column) etc. Thanks In Advance. R.h -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: dbms_space_admin.tablespace_migrate_to_local
John, Here's the syntax from the Supplied PL/SQL Packages doc - refer to it for details: TABLESPACE_MIGRATE_TO_LOCAL(tablespace_name, allocation_unit, relative_fno) Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 [EMAIL PROTECTED] John Dunn john.dunn@sef To: Multiple recipients of list ORACLE-L as.co.uk [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: omdbms_space_admin.tablespace_migrate_to_local 07/03/2002 06:43 AM Please respond to ORACLE-L Can this be used to specify the allocation, autoallocate or uniform? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Adding processor to Oracle server
Dave, Your Oracle instance won't have a problem with it. Your Oracle Sales Rep. will be absolutely delighted, because you will owe them more $$$ for the extra CPU license! Better check with your CIO (or equivalent) to see if your organization really wants to add that CPU. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 [EMAIL PROTECTED] Farnsworth, Dave DFarnsworth@Ashleyfurn To: Multiple recipients of list ORACLE-L iture.com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Adding processor to Oracle server 05/30/2002 08:54 AM Please respond to ORACLE-L I have a Windoze server that has an instance of 8.0.5 Oracle running on it. I just got an email from the network badmins that they are adding a processor to this server today. Glad to be in the loop!! This is a new experience for me. Is this something that may cause problems when Oracle starts up with this new processor?? Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Script for deleting old archive logs from NT
Arun, Here are a couple of files (a .bat and .sql) that let me maintain a constant number of Archived Redo Logs online. The first batch file executes SQL*Plus to produce two other batch files to delete the excess logs and move some others, maintaining, in this case about 450 logs. It ran every hour - smooth as silk. Hope it helps. Archived_Redo_Logs_MoveDelete_main.bat --- Set ORACLE_SID=orcl SQLPlus internal @C: \Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_sub.sql Call C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_delete.bat Call C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_move.bat Archived_Redo_Logs_MoveDelete_sub.sql Set FeedBack Off Set LineSize 200 Set PageSize 0 Set TrimSpool On -- Delete backup archived redo logs if there are over 450 total arc`d logs. Spool C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_delete.bat Select 'Del T:\Oracle\OraData\orcl\Archive_Backup\ARC' || Trim(v.Seq) || '.LOG' From ( Select To_Char(Sequence#,'0') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum 601 Minus Select 'Del T:\Oracle\OraData\orcl\Archive_Backup\ARC' || Trim(v.Seq) || '.LOG' From ( Select To_Char(Sequence#,'0') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum 451 ; Spool Off -- Move some arc`d redo logs to backup if there are over 200 in Archive directory. Spool C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_move.bat Select 'Move U:\Oracle\OraData\orcl\Archive\ARC' || Trim(v.Seq) || '.LOG T: \Oracle\OraData\orcl\Archive_Backup' From ( Select To_Char(Sequence#,'0') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum 401 Minus Select 'Move U:\Oracle\OraData\orcl\Archive\ARC' || Trim(v.Seq) || '.LOG T: \Oracle\OraData\orcl\Archive_Backup' From ( Select To_Char(Sequence#,'0') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum 201 ; Spool Off Exit Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 [EMAIL PROTECTED] Arun Chakrapani To: Multiple recipients of list ORACLE-L ArunC@1800FLO[EMAIL PROTECTED] WERS.comcc: Sent by: Subject: Script for deleting old archive logs [EMAIL PROTECTED]from NT om 05/15/2002 04:43 PM Please respond to ORACLE-L Does anybody has script to delete old archive logs on NT when the disk reaches certain percentage. Please let me know Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arun Chakrapani INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
