Oracle 8.1.7 Client on OS/390 to Oracle 8.0.5 server on Solaris
Anyone using Oracle 8.1.7 Client and/or Oracle Access Manager for CICS on OS/390 connecting to an 8.0.5 database server (on Solaris in our case)? I'm planning to upgrade the OS/390 Client from version 7.3.4 and Access Manager from version 1.3 (which works fine against the 8.0.5 server) soon and want to head off any potential problems. I need to upgrade the IBM prior to my window for my server version upgrade. I just shipped off the 8.1.7 software to our datacenter to have it loaded on the IBM. I'm hoping to have a window in our development CICS region in the next couple of weeks. - Paul --Paul Troiano[EMAIL PROTECTED]
Oracle on Solaris Memory question
Hi all, I have 1gb physical memory and over 2gb swap space on my Solaris box (Solaris 8). My SGA is about 300mb. When I startup my instance (8.1.7) the amount of free memory (physical ram) dropped as expected but the swap usage also increased by 300mb. I am little puzzled over this as I don't expect the swap used to increase since my physical memory can accommodate the SGA. Below is a snapshot of TOP showing the memory: Database up == last pid: 23317; load averages: 0.25, 0.09, 0.09 23:14:25 74 processes: 73 sleeping, 1 on cpu CPU states: 97.6% idle, 1.4% user, 0.5% kernel, 0.5% iowait, 0.0% swap Memory: 1024M real, 341M free, 490M swap in use, 2319M swap free Bring down the database, and see the free memory goes up and swap used decreases. == last pid: 23317; load averages: 0.31, 0.12, 0.10 23:14:56 52 processes: 50 sleeping, 1 running, 1 on cpu CPU states: 84.1% idle, 4.9% user, 7.2% kernel, 3.8% iowait, 0.0% swap Memory: 1024M real, 728M free, 87M swap in use, 2722M swap free Startup the DB again, and see the free memory descreases and swap used increases. == last pid: 23339; load averages: 0.34, 0.14, 0.11 23:15:26 58 processes: 55 sleeping, 2 running, 1 on cpu CPU states: 62.1% idle, 10.9% user, 17.9% kernel, 9.2% iowait, 0.0% swap Memory: 1024M real, 353M free, 466M swap in use, 2344M swap free So with over 300MB of physical ram still being free, why is the swap in use so high? Thanks. Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard 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: Solaris 2.8
the '2.' prefix has been dropped in favor of just the '8'. This change was started with Solaris 7 but there is still a lot of mixed documentation around. The '2.' really served no purpose anyway. the '5.' prefix refers to the SunOS version underlying the specific Solaris '2.' version. - Paul - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 12:43 PM > David, > Solaris 2.8 (why they call it Version 8 and specify it as 2.8 I don't know) > is in use for production at a lot of sites. I am sure there are bugs and > problems with it the same as any other O/S but generally I think it is a > safe bet to install Oracle on Solaris 2.8 . > Just ensure you check Metalink for the correct/supported version of Oracle. > > John > > -Original Message- > Sent: 12 April 2002 18:53 > To: Multiple recipients of list ORACLE-L > > I have Oracle8 running on solaris8 for more than a year and don't see any > problem. > > > David > > -Original Message- > Sent: Friday, April 12, 2002 10:26 AM > To: Multiple recipients of list ORACLE-L > > > Hi friends and gurus, > > Anybody has had (or has heard from friends of) any bad experience with > Oracle 8 running on Solaris 8. I know that the first supported version on > Solaris 8 is 8.0.5. If you know of some I would appreciate f you could > share it, and specify which version and patch release. > > Thanks a lot. > > Djordje > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Djordje Jankovic > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Nguyen, David M > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: John Hallas > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Troiano 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: SQL statement with hints or without hints (LONG)
cool! - Original Message - From: Toepke, Kevin M To: Multiple recipients of list ORACLE-L Sent: Friday, April 12, 2002 1:28 PM Subject: RE: SQL statement with hints or without hints (LONG) > Using RBO is unnecessary if you are using Oracle8 v8.0 or above. The CBO> outperforms RBO in any situation except queries against the data dictionary> (because you cannot analyze the data dictionary). Never say never and Never say always. I have found the above statement to be true except in one case, and that involves a bug that was introduced somewhere in the 8.1.5 tree and _almost_ fixed in 8.1.7.1. It is particularly nasty in the 8.1.6 tree and it appears to be fixed in 9.0.1.2. I don't have the bug #, but the situation is follows: 1) You are joining multiple large tables together -- The more & larger the tables you are joining, the worse the effects 2) One or more of the join columns is in the SELECT list 3) You are ordering by > 1 of the join columns. -- this can be an ORDER BY, GROUP BY or DISTINCT clause or even a UNION, INTERSECT or MINUS! Anything that causes a sort to occur on the join column The CBO will choose to do SORT/MERGE joins (with full table scans) when any other join method is more efficient. Through normal hinting you CAN NOT get the CBO to use nested loops with index range scans (that's part of the bug). If you specify the INDEX_ASC() and USE_NL() hints, the CBO will do an FAST FULL SCAN on the index on the specified index. If you move the unsorted query into an inline view and sort outside the inline view, you can get a near-optimal execution path -- and hinting works properly. However, if you just use the /*+ RULE */ hint, you will get better performance than with the inline view method. How do you determine if you are running into this bug? There are several ways, but the best way is to run your query without your "sort" operation. If the query runs significantly faster without the sort than with, you may be hitting this bug. An example: SELECT e.empno, e.deptid, dept.name, d.dependent_name FROM emp e, dept, dependent d WHERE e.deptid = dept.id AND emp.empno = d.empno ORDER BY e.empno, e.deptid; Inline view method SELECT /*+ NO_MERGE(x) */ * FROM ( SELECT e.empno, e.deptid, dept.name, d.dependent_name FROM emp e, dept, dependent d WHERE e.deptid = dept.id AND emp.empno = d.empno) x ORDER BY empno, deptid; Rule Hint: SELECT /*+ RULE */ e.empno, e.deptid, dept.name, d.dependent_name FROM emp e, dept, dependent d WHERE e.deptid = dept.id AND emp.empno = d.empno ORDER BY e.empno, e.deptid; Even with this bug around, I would still highly recommend the CBO over the RBO. You just have to know the exceptions. Caver
Re: SQL statement with hints or without hints
I would suggest analyzing everything anyway. If any of the conditions occur which John K was so kind to document earlier (thanks John!), they'll be on CBO whether they like it or not, whether they know it or not. I think you can prove to them that they are using CBO by looking in V$SQLAREA where there is an OPTIMIZER or OPTIMIZER_MODE column (don't have a book or database to look at right now). That way, RBO won't get the credit for doing what CBO is doing... Convincing folks to let go of RBO, in spite of the terrible job it has been doing, is still very problematic... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 12:18 PM > Thanks Tim, > > At-least now I know I shouldn't tell them to start putting hints. This was > my problem to tell them or not to tell them. This still doesn't solve the > problem for me though, as everyone is still reluctant to move to CBO. And > this is exactly where it gets getting complicated. > > Raj > __ > Rajendra Jamadagni MIS, ESPN Inc. > Rajendra dot Jamadagni at ESPN dot com > Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. > > QOTD: Any clod can have facts, but having an opinion is an art! > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman 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: OCP Question
INDEX ORACLE-L
RE: OCP Question
It Helps ! -Original Message-From: Duk Lee [mailto:[EMAIL PROTECTED]]Sent: Friday, April 12, 2002 6:43 PMTo: Multiple recipients of list ORACLE-LSubject: OCP Question Does OCP really help? I am thinking about gettting OCP, yet still skeptical about it. I am a web engineer, and have very little dba "work" experience. I know PL/SQL and have worked with MS SQL 2000, mySQL, etc. And have been messing around with Oracle 9.02 on my linux box. also been studying with numerous oracle books. I am not really worried about getting OCP. What I am worrying about it that "will it really help me to get a job as Junior-level Oracle DBA?" Thanks.
RE: OCP Question
Title: RE: OCP Question Meaning ... been discussed millions of times already? :-| Should I just search the archieve? -Original Message- From: Joe Raube [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Subject: Re: OCP Question Here we go again :-) --- Duk Lee <[EMAIL PROTECTED]> wrote: > Does OCP really help? I am thinking about gettting OCP, yet still > skeptical > about it. I am a web engineer, and have very little dba "work" > experience. I > know PL/SQL and have worked with MS SQL 2000, mySQL, etc. And have > been > messing around with Oracle 9.02 on my linux box. also been studying > with > numerous oracle books. I am not really worried about getting OCP. > What I am > worrying about it that "will it really help me to get a job as > Junior-level > Oracle DBA?" > > Thanks. > __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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: OCP Question
If you play around with a database while studying for the OCP it can help in that way. It will most likely get you past some HR folks but the more folks that get out there that have an OCP but only got the tests from memorization will start to give the OCP a bad name (ok for some of you out there, worse name). Then it will not get you even through the door. Your PL/SQL and SQL 2000 experience will probably be the bigger help in landing a junior position though (assuming that is actual work experience). -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Duk LeeSent: Friday, April 12, 2002 4:43 PMTo: Multiple recipients of list ORACLE-LSubject: OCP Question Does OCP really help? I am thinking about gettting OCP, yet still skeptical about it. I am a web engineer, and have very little dba "work" experience. I know PL/SQL and have worked with MS SQL 2000, mySQL, etc. And have been messing around with Oracle 9.02 on my linux box. also been studying with numerous oracle books. I am not really worried about getting OCP. What I am worrying about it that "will it really help me to get a job as Junior-level Oracle DBA?" Thanks.
Re: OCP Question
Here we go again :-) --- Duk Lee <[EMAIL PROTECTED]> wrote: > Does OCP really help? I am thinking about gettting OCP, yet still > skeptical > about it. I am a web engineer, and have very little dba "work" > experience. I > know PL/SQL and have worked with MS SQL 2000, mySQL, etc. And have > been > messing around with Oracle 9.02 on my linux box. also been studying > with > numerous oracle books. I am not really worried about getting OCP. > What I am > worrying about it that "will it really help me to get a job as > Junior-level > Oracle DBA?" > > Thanks. > __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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).
OCP Question
Does OCP really help? I am thinking about gettting OCP, yet still skeptical about it. I am a web engineer, and have very little dba "work" experience. I know PL/SQL and have worked with MS SQL 2000, mySQL, etc. And have been messing around with Oracle 9.02 on my linux box. also been studying with numerous oracle books. I am not really worried about getting OCP. What I am worrying about it that "will it really help me to get a job as Junior-level Oracle DBA?" Thanks.
RE: 1Z0-007 Exam
Its not a Upgrade exam.. in fact its the first exam in Oracle 9i OCP core series -Original Message-From: John Hallas [mailto:[EMAIL PROTECTED]]Sent: Friday, April 12, 2002 12:58 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 1Z0-007 Exam Ken, If this is the 9i upgrade exam then there was a discussion on this recently (about 3-4 weeks ago) Search the archives for a post by Mike Hateley and responses from Robert Freeman. If is not the 9i upgrade which exam is it? John -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of KENNETH JANUSZSent: 12 April 2002 16:26To: Multiple recipients of list ORACLE-LSubject: 1Z0-007 Exam Has anyone taken this exam? If so, I would like your feedback. Thanks, Ken Janusz, CPIM
Re: Archival Freeze - Painful lessons learned
After reading the other postings I better add this one too;^) (If ya didn't learn something new today, it's because ya didn't do anything today). ORA-00257, "archiver error. Connect internal only, until freed." Brian P. MacLean Oracle DBA, OCP8i Brian P MacLean To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] 04/12/02 02:19 Subject: Re: Archival Freeze - Painful lessons learned(Document link: PM Brian P MacLean) As Daffy Duck says -> http://www.dailywav.com/0700/dcorrect.wav The archiver freaks first, and will keep trying to write/recover/looking for space. But the database will not hang until all the redo logs are full and none remain that have been archived (ie: all redo logs need archiving). The error in the alert.log is some combination of: ORA-00255, "error archiving log %s of thread %s, sequence # %s" ORA-00270, "error creating archive log %s" ORA-19504, "failed to create file \"%s\"" ORA-27040, "skgfrcre: create error, unable to create file" ARCH: Archival stopped, error occurred. Will continue retrying SVR4 Error: 5: I/O error Why do I have a list of the above errors you ask? Because I've been there (painful lessons learned), and these are just some of the errors I look for in my db status script. Brian P. MacLean Oracle DBA, OCP8i Rajesh.Rao@jpm chase.comTo: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Archival Freeze om 04/12/02 02:13 PM Please respond to ORACLE-L Hi Fellow DBAs, I and a fellow DBA are currently debating about how insufficient space in the archive destination freezes up the DB. He claims that as soon as the ARCH process is unable to write to the disk, the db freezes. I am of the opinion that it does not. It will only report an error (Any idea, what the error code is?). It will only freeze when it cycles thru the rest of the redolog groups, and then when it tries to switch log to the one which has not been archived. That's why the error message is "cannot allocate new log". Whats your say? He's also on this list. Raj -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author:
RE: SQL statement with hints or without hints
Brian, We are production on 9012, can't get 9013 compiled on Aix 64 bit though. We will be getting 9.2 (9i release 2) soon. But then it all depends on how you define 'soon'. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Friday, April 12, 2002 6:04 PM To: Multiple recipients of list ORACLE-L H, 9.2. Did you mean 9.0.2? As we all know 9i is really 9.0.1 I think we will see 9.1, if for no reason other than we had an 8.1. But I'm already seeing/hearing about 10i (aka "The Beast Master", "Wiz Bang, "All Knowing, All Seeing", "Ai", "Dilly Dally"). But 9.2, never, I take odd's on that. *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
Re: Archival Freeze - Painful lessons learned
As Daffy Duck says -> http://www.dailywav.com/0700/dcorrect.wav The archiver freaks first, and will keep trying to write/recover/looking for space. But the database will not hang until all the redo logs are full and none remain that have been archived (ie: all redo logs need archiving). The error in the alert.log is some combination of: ORA-00255, "error archiving log %s of thread %s, sequence # %s" ORA-00270, "error creating archive log %s" ORA-19504, "failed to create file \"%s\"" ORA-27040, "skgfrcre: create error, unable to create file" ARCH: Archival stopped, error occurred. Will continue retrying SVR4 Error: 5: I/O error Why do I have a list of the above errors you ask? Because I've been there (painful lessons learned), and these are just some of the errors I look for in my db status script. Brian P. MacLean Oracle DBA, OCP8i Rajesh.Rao@jpm chase.comTo: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Archival Freeze om 04/12/02 02:13 PM Please respond to ORACLE-L Hi Fellow DBAs, I and a fellow DBA are currently debating about how insufficient space in the archive destination freezes up the DB. He claims that as soon as the ARCH process is unable to write to the disk, the db freezes. I am of the opinion that it does not. It will only report an error (Any idea, what the error code is?). It will only freeze when it cycles thru the rest of the redolog groups, and then when it tries to switch log to the one which has not been archived. That's why the error message is "cannot allocate new log". Whats your say? He's also on this list. Raj -- 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). -- 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: SQL statement with hints or without hints
H, 9.2. Did you mean 9.0.2? As we all know 9i is really 9.0.1 I think we will see 9.1, if for no reason other than we had an 8.1. But I'm already seeing/hearing about 10i (aka "The Beast Master", "Wiz Bang, "All Knowing, All Seeing", "Ai", "Dilly Dally"). But 9.2, never, I take odd's on that. Brian P. MacLean Oracle DBA, OCP8i "Jamadagni, Rajendra" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: SQL statement with hints or without hints Sent by: [EMAIL PROTECTED] 04/12/02 01:38 PM Please respond to ORACLE-L Thanks Cherie and Rafiq, I am on 8061, but probably directly jump to 9.2 (whenever that becomes available that is, so far I have heard many dates). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! (See attached file: ESPN_Disclaimer.txt) ESPN_Disclaimer.txt Description: Binary data
RE: Archival Freeze
Raj, either way you are in deep doo doo until the problem is solved. The error code is ORA-257 - Archiver error. Connect internal only, until freed. >From metalink: ORA-00257: archiver error. Connect internal only, until freed. Cause: The archiver process received an error while trying to archive a log. Unless the problem is resolved soon, the database will stop executing transactions. Action: By far the most likely cause of the error is the archive destination device is out of space. Check archiver trace file for detail description of the problem. Have you tried it? Watch the timestamps on your redologs and that will probably answer your question. Lisa Koivu Oracle Database Tank Fairfield Resorts, Inc. 954-935-4117 > -Original Message- > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > Sent: Friday, April 12, 2002 5:13 PM > To: Multiple recipients of list ORACLE-L > Subject: Archival Freeze > > > Hi Fellow DBAs, > > I and a fellow DBA are currently debating about how insufficient space in > the archive destination freezes up the DB. He claims that as soon as the > ARCH process is unable to write to the disk, the db freezes. I am of the > opinion that it does not. It will only report an error (Any idea, what the > error code is?). It will only freeze when it cycles thru the rest of the > redolog groups, and then when it tries to switch log to the one which has > not been archived. That's why the error message is "cannot allocate new > log". > > Whats your say? He's also on this list. > > Raj > > > > > -- > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa 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: SQL statement with hints or without hints
Hi Jonathan, Yes - my phrasing was incorrect. The list was off the Tuning/Perf Guide, but I should have phrased it correctly. John > -Original Message- > From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] > Sent: Friday, April 12, 2002 2:18 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: SQL statement with hints or without hints > > > > I think you have to be a little careful how > you phrase your comment here. > > In your list there are some things which FORCE > the use of CBO, even if the optimizer_mode is > set to RULE; and there are some features which > are activated ONLY IF the cost based optimizer > is invoked. > > Off the top of my head, the following force CBO when > they appear in an SQL statement > > · Partitioned tables and indexes > · Index-organized tables > · SAMPLE clauses in a SELECT statement > · Parallel tables/indexes > > and the following are only recognised and used when > the CBO has kicked in > > · Reverse key indexes > · Function-based indexes > · Query rewrite (materialized views) > · Progress meter > · Hash joins > · Bitmap indexes and bitmap join indexes > · Star transformations > · Partition Views > · Index skip scans > > and on these two I pass: > · Star joins > · Extensible optimizer > > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Author of: > Practical Oracle 8i: Building Efficient Databases > > Next Seminar - Australia - July/August > http://www.jlcomp.demon.co.uk/seminar.html > > Host to The Co-Operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > -Original Message- > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: 12 April 2002 19:46 > > > Raj, > > Keep in mind that the CBO will be defaulted whenever the following is > present: > > · Partitioned tables and indexes > · Index-organized tables > · Reverse key indexes > · Function-based indexes > · SAMPLE clauses in a SELECT statement > · Parallel execution and parallel DML (i.e. presence of DEGREE on > Tables/Indexes) > · Star transformations > · Star joins > · Extensible optimizer > · Query rewrite (materialized views) > · Progress meter > · Hash joins > · Bitmap indexes and bitmap join indexes > · Partition Views > · Index skip scans > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jonathan Lewis > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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).
No DBAs needed on AS400
My view of the AS/400 will always be framed by one project I heard about. The Pepsi Bottling Group in the mid 80s, was looking for a solution that would tie all of their branch offices around the country to the Bottling Group headquarters in Purchase NY. IBM of course sold them an AS/400 solution telling them, as they grew and needed more CPU all they had to do was go was buy another AS/400 and plug it in next to the others. Also they wouldn't need any pricey systems programmers. Pepsi set up a few sites and discovered that the maximum (at the time) number of AS/400s that could sit side by side and function as one CPU was three. After that the overhead needed to keep them communicating and in sync was more than the added CPU. Although they didn't need any system programmers after the initial install, they discovered that they needed 50% more communications programmers (and more expensive than system programmers). And finally they found out that if they wanted to customize a system due to unique regional conditions, it was far more difficult than any other system they had ever used. They ended up dumping the whole AS/400 system and replacing it with HP minicomputers, which was successful enough for HP to feature that Pepsi system in marketing literature (no, they didn't mention the AS/400). I agree with most, it's time to look for a new job. This new boss will be nothing but headaches for you and by the time they realize what a mistake they made, you'll be long gone (and they'll find a way to blame you). Brad Weiner From: "Jay Hostetter" <[EMAIL PROTECTED]> Date: Thu, 11 Apr 2002 08:50:58 -0400 Subject: No DBAs needed on AS400We are going through a merger, and management is looking to eliminate =positions. Here is a brief summary of my discussion with the new director =of IT:Director: "Back when I we were using an AS400, we didn't need a DBA."Me: "Then you probably were just using files."Director: "No, it was a database."Me: "Could you issue SQL commands?"Director: "Yes. But we didn't need a DBA. I guess it was just one of =those mysteries of life."My thoughts are that he is using the term "database" in the generic sense =of the word (our "files" are our database), or he was using some proprietar=y database that doesn't even begin to compare to Oracle.For those of you who know AS400s, I would appreciate some insight that =would demonstrate why he needs to keep me as a DBA.Thanks,Jay
Re:Archival Freeze
Raj, Having frozen my db many a time I can say with complete conviction thet you've got it right. The alert log will have a message therein when the arch process originally gets stuck, due to insufficient space, but the db will continue normally until all online log files have been used at which time your users get the "ORA-00257: archiver stuck" error message. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 4/12/2002 1:13 PM Hi Fellow DBAs, I and a fellow DBA are currently debating about how insufficient space in the archive destination freezes up the DB. He claims that as soon as the ARCH process is unable to write to the disk, the db freezes. I am of the opinion that it does not. It will only report an error (Any idea, what the error code is?). It will only freeze when it cycles thru the rest of the redolog groups, and then when it tries to switch log to the one which has not been archived. That's why the error message is "cannot allocate new log". Whats your say? He's also on this list. Raj -- 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). -- 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: Solaris 2.8
Solaris2.8 or Solaris5.8 or Solaris8 is just same thing. I also run Oracle8.0, 8.1.5, 8.1.6, and 8i on solaris5.6 for couple years without seeing a major problem. David -Original Message- Sent: Friday, April 12, 2002 2:35 PM To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wrote: > David, > Solaris 2.8 (why they call it Version 8 and specify it as 2.8 I don't know) > is in use for production at a lot of sites. I am sure there are bugs and > problems with it the same as any other O/S but generally I think it is a > safe bet to install Oracle on Solaris 2.8 . > Just ensure you check Metalink for the correct/supported version of Oracle. well, i'm running 7.3.4, 8.1.5, 8.1.6 and 8.1.7 on it with no problems and have been for a while. -- -- Bill "Shrek" Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Error 13: Illegal brain function. Process terminated. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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: SQL statement with hints or without hints
I think you have to be a little careful how you phrase your comment here. In your list there are some things which FORCE the use of CBO, even if the optimizer_mode is set to RULE; and there are some features which are activated ONLY IF the cost based optimizer is invoked. Off the top of my head, the following force CBO when they appear in an SQL statement · Partitioned tables and indexes · Index-organized tables · SAMPLE clauses in a SELECT statement · Parallel tables/indexes and the following are only recognised and used when the CBO has kicked in · Reverse key indexes · Function-based indexes · Query rewrite (materialized views) · Progress meter · Hash joins · Bitmap indexes and bitmap join indexes · Star transformations · Partition Views · Index skip scans and on these two I pass: · Star joins · Extensible optimizer Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 12 April 2002 19:46 Raj, Keep in mind that the CBO will be defaulted whenever the following is present: · Partitioned tables and indexes · Index-organized tables · Reverse key indexes · Function-based indexes · SAMPLE clauses in a SELECT statement · Parallel execution and parallel DML (i.e. presence of DEGREE on Tables/Indexes) · Star transformations · Star joins · Extensible optimizer · Query rewrite (materialized views) · Progress meter · Hash joins · Bitmap indexes and bitmap join indexes · Partition Views · Index skip scans -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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).
Archival Freeze
Hi Fellow DBAs, I and a fellow DBA are currently debating about how insufficient space in the archive destination freezes up the DB. He claims that as soon as the ARCH process is unable to write to the disk, the db freezes. I am of the opinion that it does not. It will only report an error (Any idea, what the error code is?). It will only freeze when it cycles thru the rest of the redolog groups, and then when it tries to switch log to the one which has not been archived. That's why the error message is "cannot allocate new log". Whats your say? He's also on this list. Raj -- 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: SQL statement with hints or without hints
Thanks Cherie and Rafiq, I am on 8061, but probably directly jump to 9.2 (whenever that becomes available that is, so far I have heard many dates). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
Re: bytes per extent
On Fri, Apr 12, 2002 at 12:13:25PM -0800, Browett, Darren wrote: > I am assumming the table is contained within one extent. > > If you did an analyze table, wouldn't that show the amount of data (num_rows > * avg_row_len) > within the table (extent) which could be used to monitor growth. Yep, but I don't want an estimate, I want the byte count. great metalink notes I found: Note:116565.1 Note:116923.1 > -Original Message- > Sent: April 12, 2002 10:04 AM > To: Multiple recipients of list ORACLE-L > > > > > There are lots of versions of this around, but this reports > the sum of the allocated extents. My problem is that the > creater of this db made a huge extent and I don't seem to > have a way to track growth within the extent. > > > > > On Thu, Apr 11, 2002 at 07:13:31AM -0800, Ruth Gramolini wrote: > > Here is a script that I got from another lister. Hope it is what you're > > looking for. > > > > column tsname format a25 heading 'Tablespace Name' > > column tot format 99,999,999 heading 'Size (K)' > > column fsp Format 99,999,999 heading 'Free (K)' > > column csp Format 999,999 heading 'Free|Extents' > > column msp Format 9,999,999 heading 'Max Free|Ext (K)' > > column pctused Format 999.99 heading '% Used' > > > > column tsno noprint > > compute sum of tot fsp on report > > break on report > > > > select > > fi.tablespace_name "tsname", > > sum(fi.bytes)/1024 "tot", > > iv.free/1024 "fsp", > > ((SUM(fi.bytes)-iv.free)/SUM(fi.bytes))*100 "pctused", > > iv.no_of_exts "csp", > > iv.max/1024 "msp" > > from > > dba_data_files fi, > > ( > > select > > t.tablespace_name, > > NVL(MAX(f.bytes),0) max, > > NVL(sum(f.bytes),0) free, > > count(f.bytes) no_of_exts > > from > > sys.dba_free_space f, > > sys.dba_tablespaces t > > where > > t.tablespace_name=f.tablespace_name(+) > > and t.status != 'INVALID' > > group by > > t.tablespace_name > > ) iv > > where > > fi.tablespace_name = iv.tablespace_name > > GROUP BY > > fi.tablespace_name, > > iv.free, > > iv.no_of_exts, > > iv.max > > ORDER BY > > fi.tablespace_name > > / > > > > clear breaks > > clear columns > > clear computes > > > > > > Regards, > > Ruth > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Thursday, April 11, 2002 10:39 AM > > > > > > > Is there a query to get the number of bytes used and free > > > in an each extent? > > > === > > > Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Ray Stell > > > 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). > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Ruth Gramolini > > 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). > > -- > === > Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ray Stell > 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). > -- > Please see the official ORACLE-L FAQ: http://www.o
RE: empty string = null?
The promise/threat to treat empty strings differently from nulls has been in the documentaion since at least Oracle 7.3. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message-From: Khedr, Waleed [mailto:[EMAIL PROTECTED]]Sent: Thursday, April 11, 2002 6:33 PMTo: Multiple recipients of list ORACLE-LSubject: RE: empty string = null? I feel I had the same dream. Anyway this from Oracle 9i doc:Nulls"If a column in a row has no value, then the column is said to be null, or to contain a null. Nulls can appear in columns of any datatype that are not restricted by NOTNULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.Do not use null to represent a value of zero, because they are not equivalent. (Oracle currently treats a character value with a length of zero as null. However, thismay not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.) Any arithmetic expression containing anull always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand."Regards,Waleed-Original Message-From: Jonathan Gennick [mailto:[EMAIL PROTECTED]]Sent: Thursday, April 11, 2002 8:44 PMTo: Multiple recipients of list ORACLE-LSubject: empty string = null?Oracle has always treated empty strings (e.g.: '') as nulls.As I sit here and think about this, I can convince myselfthat I read somewhere that Oracle9i provided an option totreat empty strings not as nulls, but truly as emptystrings. Is this the case, or is my mind manufacturingmemories?Jonathan Gennick --- Brighten the corner where you aremailto:[EMAIL PROTECTED]http://Gennick.com * http://MichiganWaterfalls.com *http://ValleySpur.com--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Jonathan Gennick INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: SQL statement with hints or without hints
Tim, Have you ever found yourself needing to use the DBMS_STATS.SET_XXX_STATS routines to get your desired plans? And if so, did you do this on a trial and error basis, or did you use something like a 10053 trace to see inside the CBO's head to help you determine what values to use? Just curious. Something I've never done but am contemplating for a couple of cases. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- Sent: Friday, April 12, 2002 1:03 PM To: Multiple recipients of list ORACLE-L Instructing people to put hints in all of their code is the same as using CBO full-time, because CBO hints automatically enable the CBO. Using CBO full-time and not analyzing everything is asking for trouble... Using RBO is unnecessary if you are using Oracle8 v8.0 or above. The CBO outperforms RBO in any situation except queries against the data dictionary (because you cannot analyze the data dictionary). Please read my paper "Search for Intelligent Life in the CBO" (online at www.evdbt.com/library.htm) for an explanation as to why it is crucial to set OPTIMIZER_MODE=CHOOSE (not FIRST_ROWS or ALL_ROWS) and use the OPTIMIZER_INDEX_CACHING and (cautiously!) the OPTIMIZER_INDEX_COST_ADJ parameters. Especially in Oracle8i, the CBO chooses dramatically superior access plans over the RBO... Embedding hints in SQL will eventually cripple your system as conditions change, as will continued use of the RBO. Instructing people to embed hints on a wholesale basis is not the correct approach, in my opinion. Instead, I'd recommend setting the above-mentioned parameters appropriately (read the paper!), analyzing everything (use the GATHER_xxx_STATS procedures in the DBMS_STATS package if using Oracle8i or above), and trust the CBO. The myths about it "not working" or "not being trustworthy" have not been true for years. For situations where it doesn't choose an index where you think it should (or mistakenly chooses one that you think it should't), consider analyzing involved columns also to deal with any possible data skew problems by creating "histograms". As Dr. Evil says, "I'm the boss. I need the info". Give the CBO the info and it will choose the right plan. Like that analogy? I do... :-) I know the CBO works because I have made a living out of tuning Oracle-based applications for the past 6-7 years straight, working for Oracle and later as an independent. Lately (i.e. past 2 years or so), SQL tuning has mainly consisted of *removing* hints (both the old "pre-CBO" hints like "+0" and "||''" as well as CBO hints) and analyzing appropriately (i.e. "need the info!"). Nothing gets your attention like something that affects your wallet, so I've been betting my hourly income on these facts. I would have noticed if I was wrong by now... :-) "Would you like fries with that, ma'am?" Hints should be used only as they were originally intended: infrequent use to deal with the extremely rare circumstances when the CBO cannot choose the best path. Nothing is perfect, but the CBO in Oracle8i and above is the best there is... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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: bytes per extent
I am assumming the table is contained within one extent. If you did an analyze table, wouldn't that show the amount of data (num_rows * avg_row_len) within the table (extent) which could be used to monitor growth. -Original Message- Sent: April 12, 2002 10:04 AM To: Multiple recipients of list ORACLE-L There are lots of versions of this around, but this reports the sum of the allocated extents. My problem is that the creater of this db made a huge extent and I don't seem to have a way to track growth within the extent. On Thu, Apr 11, 2002 at 07:13:31AM -0800, Ruth Gramolini wrote: > Here is a script that I got from another lister. Hope it is what you're > looking for. > > column tsname format a25 heading 'Tablespace Name' > column tot format 99,999,999 heading 'Size (K)' > column fsp Format 99,999,999 heading 'Free (K)' > column csp Format 999,999 heading 'Free|Extents' > column msp Format 9,999,999 heading 'Max Free|Ext (K)' > column pctused Format 999.99 heading '% Used' > > column tsno noprint > compute sum of tot fsp on report > break on report > > select > fi.tablespace_name "tsname", > sum(fi.bytes)/1024 "tot", > iv.free/1024 "fsp", > ((SUM(fi.bytes)-iv.free)/SUM(fi.bytes))*100 "pctused", > iv.no_of_exts "csp", > iv.max/1024 "msp" > from > dba_data_files fi, > ( > select > t.tablespace_name, > NVL(MAX(f.bytes),0) max, > NVL(sum(f.bytes),0) free, > count(f.bytes) no_of_exts > from > sys.dba_free_space f, > sys.dba_tablespaces t > where > t.tablespace_name=f.tablespace_name(+) > and t.status != 'INVALID' > group by > t.tablespace_name > ) iv > where > fi.tablespace_name = iv.tablespace_name > GROUP BY > fi.tablespace_name, > iv.free, > iv.no_of_exts, > iv.max > ORDER BY > fi.tablespace_name > / > > clear breaks > clear columns > clear computes > > > Regards, > Ruth > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, April 11, 2002 10:39 AM > > > > Is there a query to get the number of bytes used and free > > in an each extent? > > === > > Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Ray Stell > > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ruth Gramolini > 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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
Re: SQL statement with hints or without hints
Raj, I shall comment on no 3. 1. Developers do not put hints in their SQL statements. 2. They implicitly reply on a set of indexes that have worked for them. 3. Due to reasons of 1 and 2, no new indexes can be created because it will make their queries run slower. I had gone through the same situation 1 year back after adding a new index on Oracle Financial Application to expedite or compliment a badly wirtten code and that code written fine but one of AR application using forms screen to add cash receipts become so slow (taking 10 minutes to move from a field instead of couple of seconds) and we have no option except to drop that index and things become normal but we have no option to change that form or put any hint. So under some situations we become helpless. Although I have been adding customized indexes to improve performance of certain Oracle Financials Reports like Accrual Rebuild REconcilation which was taking 30 hours and after adding 6 indexes the time went to 1.5 hours. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Fri, 12 Apr 2002 06:03:28 -0800 Hi all, We are running a kind of hybrid mode, mainly RBO with some tables analyzed (mainly for intermedia). Last week we had an interesting situation, when tuning a huge SQL, we created an index and the query worked fine, but later in the day developers complained that their queries are running slower. We finally took out the new index and things were fine after that, but this brought out few important issues, 1. Developers do not put hints in their SQL statements. 2. They implicitly reply on a set of indexes that have worked for them. 3. Due to reasons of 1 and 2, no new indexes can be created because it will make their queries run slower. 4. As an effect of 3, new queries that can't make use of these set indexes will always be slower. Some of these queries can't be rewritten to make use of the existing indexes due to business logic involved. My question is, do you, in your organization recommend putting hints in the SQLs all the time, some times or not at all ?? It doesn't really matter if you use RBO or CBO. When the developers questioned me, I told them 'Oracle optimizer is not an exact science, especially in a mixed RBO/CBO mode, so it is bound to make some wrong choices and that is precisely why Oracle calls these things as "hints", so we tell the optimizer to do the right thing." I am in a good mood to write a short note explaining developers why they (must) use hints in their SQL statements. Your input is greatly appreciated TIA Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! << ESPN_Disclaimer.txt >> MOHAMMAD RAFIQ _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq 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: IOUG Get-together
John Kanagaraj wrote: > > > If I have missed anyone - apologies, and please let me know! Charlie, can I > request you to think about a nice place to meet later? > Apologies accepted. If anybody is interested in how to make their applications search smartly information managed by a LDAP server (_NOT_ specifically OID which indeed I have never used) on a familiar example which I have OpenLdaped for the occasion, they are welcome on Tuesday at 4:15. Especially young attractive women. But don't feel put off if you don't fit the description :-). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: SQL statement with hints or without hints
Raj, I don't remember what version you said you were using but you might want to investigate stored outlines. If you have a full-size test environment, you could save away the current RBO-based execution plans, switch to CBO and gradually get rid of your RBO-based stored outlines by tuning statements one at a time. Theoretically, that should work. I haven't tried it myself, though. Theoretically, it's a way to be on cost-based but continue to use some of the the rule-based execution plans until your comfortable with the new stuff. Cherie Machler Oracle DBA Gelco Information Network "Jamadagni, Rajendra" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: SQL statement with hints or without hints Sent by: [EMAIL PROTECTED] 04/12/02 01:18 PM Please respond to ORACLE-L Thanks Tim, At-least now I know I shouldn't tell them to start putting hints. This was my problem to tell them or not to tell them. This still doesn't solve the problem for me though, as everyone is still reluctant to move to CBO. And this is exactly where it gets getting complicated. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! (See attached file: ESPN_Disclaimer.txt) ESPN_Disclaimer.txt Description: Binary data
Yet another rman question
Another (and hopefully final) Rman question for the community... If you decided NOT to use Rman and you opted for either another product, can you share with me why you decided not to use Rman. Was it the complexity, lack of documentation, or a really good software salesman? Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration Author Oracle9i New Features, Mastering Oracle8i The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert 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: Solaris 2.8
[EMAIL PROTECTED] wrote: > David, > Solaris 2.8 (why they call it Version 8 and specify it as 2.8 I don't know) > is in use for production at a lot of sites. I am sure there are bugs and > problems with it the same as any other O/S but generally I think it is a > safe bet to install Oracle on Solaris 2.8 . > Just ensure you check Metalink for the correct/supported version of Oracle. well, i'm running 7.3.4, 8.1.5, 8.1.6 and 8.1.7 on it with no problems and have been for a while. -- -- Bill "Shrek" Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Error 13: Illegal brain function. Process terminated. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater 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: UTL_FILE limitation
Kieran, I have to wonder... why are you using utl_file instead of sql*loader? Lisa Koivu Oracle Database Tank Fairfield Resorts, Inc. 954-935-4117 > -Original Message- > From: Kieran Murray [SMTP:[EMAIL PROTECTED]] > Sent: Friday, April 12, 2002 2:13 PM > To: Multiple recipients of list ORACLE-L > Subject: UTL_FILE limitation > > Hi, > I'm trying to load a table with 37 columns, from a flat file. I'm getting > ORA-06502 error after about 400 rows have been added to the file. When I > read read from other tables this works fine (although they don't have as > many columns). I've tried using the overloaded UTL_FILE.FOPEN procedure > (setting max_linesize to 32767) but to no avail. The strange thing is, if > I > run a wc -c on any of the lines created they average about 170 bytes, > which > doesn't even come close to the 1023 bytes limit. Would anyone know of a > workaround? > > Cheers, > Kieran Murray > Norkom Technologies, > 43 Upper Mount Street, > Dublin 2, Ireland > > P.S. relevant commands are : > fileid := utl_file.fopen(file_dir,file_name,'w',32767); > v_data := ; > utl_file.put_line(fileid, v_data); > utl_file.fclose(fileid); > > Table description is: > CREATE TABLE CDM_RESULTS_FILE ( > CUS_IDNUMBERNOT NULL, > BAN NUMBERNOT NULL, > CTN VARCHAR2 (12), > CNAME1VARCHAR2 (100), > CNAME2VARCHAR2 (100), > CMTITLE VARCHAR2 (50), > CMADD1VARCHAR2 (50), > CMADD2VARCHAR2 (50), > CMCNTYVARCHAR2 (50), > CMCITYVARCHAR2 (50), > CMZIP VARCHAR2 (50), > CMSTATE VARCHAR2 (50), > CATTN VARCHAR2 (50), > CWPH VARCHAR2 (50), > CWPHEXVARCHAR2 (50), > CHPH VARCHAR2 (50), > COTHPHON VARCHAR2 (50), > SCORE NUMBER, > SCORE10 NUMBER, > SCORE20 NUMBER, > SCORE50 NUMBER, > SCORE100 NUMBER, > REASON1 NUMBER, > REASON2 NUMBER, > REASON3 NUMBER, > REASON4 NUMBER, > REASON5 NUMBER, > REASON6 NUMBER, > REASON7 NUMBER, > REASON8 NUMBER, > REASON9 NUMBER, > REASON10 NUMBER, > MCABA NUMBER, > MSRATANUMBER, > MSRTA NUMBER, > MSRACANUMBER, > MROAM NUMBER) > > > > > The information contained in this e-mail transmission is confidential > and may be privileged. It is intended only for the addressee(s) stated > above. If you are not an addressee, any use, dissemination, distribution, > publication, or copying of the information contained in this e-mail is > strictly prohibited. If you have received this e-mail in error, please > immediately notify our IT Department by telephone at 353-1-6769333 > or e-mail [EMAIL PROTECTED] and delete the e-mail from your > system. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Kieran Murray > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa 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: Developer access in test database
Guess I should clarify... developers only get access to dev, and for test & prod they get nothing. I've encountered very few with a need/desire for select_catalog_role, and they have to ask first :) John Hallas wrote: > > Suzy, > You are much too generous. > I always have found that asking for a change control form delays things by a > few days!! > John > > -Original Message- > Sent: 12 April 2002 18:43 > To: Multiple recipients of list ORACLE-L > > I generally give developers access only to the schema's they are > developing for. In some cases, they might also get select_catalog_role. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Suzy Vordos > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: John Hallas > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos 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: SQL statement with hints or without hints (LONG)
> Using RBO is unnecessary if you are using Oracle8 v8.0 or above. The CBO> outperforms RBO in any situation except queries against the data dictionary> (because you cannot analyze the data dictionary). Never say never and Never say always. I have found the above statement to be true except in one case, and that involves a bug that was introduced somewhere in the 8.1.5 tree and _almost_ fixed in 8.1.7.1. It is particularly nasty in the 8.1.6 tree and it appears to be fixed in 9.0.1.2. I don't have the bug #, but the situation is follows: 1) You are joining multiple large tables together -- The more & larger the tables you are joining, the worse the effects 2) One or more of the join columns is in the SELECT list 3) You are ordering by > 1 of the join columns. -- this can be an ORDER BY, GROUP BY or DISTINCT clause or even a UNION, INTERSECT or MINUS! Anything that causes a sort to occur on the join column The CBO will choose to do SORT/MERGE joins (with full table scans) when any other join method is more efficient. Through normal hinting you CAN NOT get the CBO to use nested loops with index range scans (that's part of the bug). If you specify the INDEX_ASC() and USE_NL() hints, the CBO will do an FAST FULL SCAN on the index on the specified index. If you move the unsorted query into an inline view and sort outside the inline view, you can get a near-optimal execution path -- and hinting works properly. However, if you just use the /*+ RULE */ hint, you will get better performance than with the inline view method. How do you determine if you are running into this bug? There are several ways, but the best way is to run your query without your "sort" operation. If the query runs significantly faster without the sort than with, you may be hitting this bug. An example: SELECT e.empno, e.deptid, dept.name, d.dependent_name FROM emp e, dept, dependent d WHERE e.deptid = dept.id AND emp.empno = d.empno ORDER BY e.empno, e.deptid; Inline view method SELECT /*+ NO_MERGE(x) */ * FROM ( SELECT e.empno, e.deptid, dept.name, d.dependent_name FROM emp e, dept, dependent d WHERE e.deptid = dept.id AND emp.empno = d.empno) x ORDER BY empno, deptid; Rule Hint: SELECT /*+ RULE */ e.empno, e.deptid, dept.name, d.dependent_name FROM emp e, dept, dependent d WHERE e.deptid = dept.id AND emp.empno = d.empno ORDER BY e.empno, e.deptid; Even with this bug around, I would still highly recommend the CBO over the RBO. You just have to know the exceptions. Caver
RE: SQL statement with hints or without hints
Raj, Keep in mind that the CBO will be defaulted whenever the following is present: · Partitioned tables and indexes · Index-organized tables · Reverse key indexes · Function-based indexes · SAMPLE clauses in a SELECT statement · Parallel execution and parallel DML (i.e. presence of DEGREE on Tables/Indexes) · Star transformations · Star joins · Extensible optimizer · Query rewrite (materialized views) · Progress meter · Hash joins · Bitmap indexes and bitmap join indexes · Partition Views · Index skip scans *And*, when this now-invoked CBO detects objects without statistics, it 'guesstimeates' them based on some ridiculous defaults, leading to absolutely horrible paths. Methinks that is what you are observing Looking at the subject lines for papers at IOUG, this subject is going to be dealt with severely :) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** > -Original Message- > From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] > Sent: Friday, April 12, 2002 8:38 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL statement with hints or without hints > > > I am not sure, outlines do require CBO (me thinks) and we are > not there yet. > Also there is a general resistance around here for that. Now > that you have > asked me, I will probably take it up to my manager once we move to CBO > completely (sometime before Universe stops expanding judging > by current > support from within our group to migrate to CBO). > > Raj > __ > Rajendra JamadagniMIS, ESPN Inc. > Rajendra dot Jamadagni at ESPN dot com > Any opinion expressed here is personal and doesn't reflect > that of ESPN Inc. > > QOTD: Any clod can have facts, but having an opinion is an art! > > > -Original Message- > Sent: Friday, April 12, 2002 10:18 AM > To: 'Jamadagni, Rajendra'; '[EMAIL PROTECTED]' > > > Raj, out of curiosity: Why are outlines a no-no? Please > elaborate. I > haven't used them and would be interested in your opinion. > > LK > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: Rman, Duplicating without set untill.... & cloning with
I always rebuild the controlfiles for the clone and make it noarchivelog. I am still on 8.0.6.3 so I can't duplicate the database, I have to fool rman be renaming the clone to the orginal and restore. First I so a backup controlfile to trace on the clone, then I set name to the orginal and make it noarchivlog. I restore the to the renamed clone and then rename it back. I know it's confusing... Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 2:33 PM Ruth, When I startup nomount my clone, no files exist, except the init.ora. I then duplicate using: run { set until time "to_date('040120020214','mmddhh24mi')"; allocate auxiliary channel ch1 type disk; set newname for datafile 1 to '/u03/oradata/BSCSD/system01.dbf'; ...yadda yadda... duplicate target database to BSCSD logfile group 1 ('/u03/oradata/BSCSD/redo1a.log','/u04/oradata/BSCSD/redo1b.log') size 25m, group 2 ('/u03/oradata/BSCSD/redo2a.log','/u04/oradata/BSCSD/redo2b.log') size 25m, group 3 ('/u03/oradata/BSCSD/redo3a.log','/u04/oradata/BSCSD/redo3b.log') size 25m; } The manual states that duplicate creates the control files. Can you tell me more about creating the controlfiles in noarchivelog? Thanks, Jay >>> [EMAIL PROTECTED] 04/12/02 12:14PM >>> Make sure you build the controlfiles on the clone with noarchivelog. That will keep you from having the problem of set until time. Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 11:59 AM I think your theory is correct. I have the same experience, I have to use SET UNTIL, otherwise RMAN complains about the archivelogs. Oracle 8.1.7 on IBM AIX. Robin Jay Hostetter wrote: > > My experience has been that RMAN will try to restore the database right up to the current point-in-time of the production database. So if you are on a remote system, it will be looking for data in the current redo logs (or logs that were archived since the backup), which it can't access.Oracle claims that RMAN will restore the last backup, but I think it is trying to bring the duplicated DB in sync with production. > > I am on 8.1.7 on Tru64. I haven't investigated this enough to prove the theory, so I could be wrong. All I know is that if I don't use SET UNTIL when duplicating a database, RMAN complains about missing archivelogs. > > Jay > > >>> [EMAIL PROTECTED] 04/12/02 10:13AM >>> > I duplicate without at set until time using 8.0.6.3. I follow the > instructions from Metalink. I duplicate our production databases to another > box and change the names from prd to rpt. I do clone all of the > tablespaces. > > Ruth > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, April 12, 2002 5:58 AM > tablespaces > > Hi All, > > I'm currently in the middle of a tar with Oracle about duplicating without > set until time/scn According to the doc's it must be possible but when > trying I get an error message about an archive log file that it needs but > is not yet there. > Any of you duplicating without set untill time/scn etc.. w/o problems > > if so what version RMAN/Oracle What OS/version > What sequence of actions do you perform? > > Second I try to find the rman alternative to cloning just a part of the > database but can't find anything in the Doc's > Anybody > > TIA > > Jack > > === > De informatie verzonden in dit e-mailbericht is vertrouwelijk en is > uitsluitend bestemd voor de geadresseerde. Openbaarmaking, > vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan > derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & > Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en > volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch > voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een > verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten > worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. > > Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u > vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender > en het origineel en eventuele kopieën te verwijderen en te vernietigen. > > Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene > voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De > algemene voorwaarden worden u op verzoek kosteloos toegezonden. > = > The information contained in this communication is confidential and is > intended solely for the use of the individual or entity to whom it is > addressed. You should not copy, disclose or distribute this communication > without the authority of Ernst & Young. Ernst & Young is neither lia
UTL_FILE limitation
Hi, I'm trying to load a table with 37 columns, from a flat file. I'm getting ORA-06502 error after about 400 rows have been added to the file. When I read read from other tables this works fine (although they don't have as many columns). I've tried using the overloaded UTL_FILE.FOPEN procedure (setting max_linesize to 32767) but to no avail. The strange thing is, if I run a wc -c on any of the lines created they average about 170 bytes, which doesn't even come close to the 1023 bytes limit. Would anyone know of a workaround? Cheers, Kieran Murray Norkom Technologies, 43 Upper Mount Street, Dublin 2, Ireland P.S. relevant commands are : fileid := utl_file.fopen(file_dir,file_name,'w',32767); v_data := ; utl_file.put_line(fileid, v_data); utl_file.fclose(fileid); Table description is: CREATE TABLE CDM_RESULTS_FILE ( CUS_IDNUMBERNOT NULL, BAN NUMBERNOT NULL, CTN VARCHAR2 (12), CNAME1VARCHAR2 (100), CNAME2VARCHAR2 (100), CMTITLE VARCHAR2 (50), CMADD1VARCHAR2 (50), CMADD2VARCHAR2 (50), CMCNTYVARCHAR2 (50), CMCITYVARCHAR2 (50), CMZIP VARCHAR2 (50), CMSTATE VARCHAR2 (50), CATTN VARCHAR2 (50), CWPH VARCHAR2 (50), CWPHEXVARCHAR2 (50), CHPH VARCHAR2 (50), COTHPHON VARCHAR2 (50), SCORE NUMBER, SCORE10 NUMBER, SCORE20 NUMBER, SCORE50 NUMBER, SCORE100 NUMBER, REASON1 NUMBER, REASON2 NUMBER, REASON3 NUMBER, REASON4 NUMBER, REASON5 NUMBER, REASON6 NUMBER, REASON7 NUMBER, REASON8 NUMBER, REASON9 NUMBER, REASON10 NUMBER, MCABA NUMBER, MSRATANUMBER, MSRTA NUMBER, MSRACANUMBER, MROAM NUMBER) The information contained in this e-mail transmission is confidential and may be privileged. It is intended only for the addressee(s) stated above. If you are not an addressee, any use, dissemination, distribution, publication, or copying of the information contained in this e-mail is strictly prohibited. If you have received this e-mail in error, please immediately notify our IT Department by telephone at 353-1-6769333 or e-mail [EMAIL PROTECTED] and delete the e-mail from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kieran Murray 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).
consistent reads during inserts
Hi, all. Sorry to be such a pest, but I'd like to ask another question about my insert performance problem. An example of the trace output for the two machines is shown below. There are many other traces containing the same insert statements, and the values are very similar: Machine 1--acceptable performance INSERT INTO TLMPCSV ( SRT_SCH_SYS_NR,BCD_LBL_REF_TE,LD_REF_NR,SVC_TYP_CD, REC_INS_TS ) VALUES ( :b1,:b2,:b3,NVL(:b4,'000'),SYSDATE ) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse0 0.00 0.00 0 0 0 0 Execute 10975 11.37 15.88 1907 131437 10975 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total10975 11.37 15.88 1907 131437 10975 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 67 (APP730LM01) (recursive depth: 1) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: CHOOSE Machine 2--unacceptable performance INSERT INTO TLMPCSV ( SRT_SCH_SYS_NR,BCD_LBL_REF_TE,LD_REF_NR,SVC_TYP_CD, REC_INS_TS ) VALUES ( :b1,:b2,:b3,NVL(:b4,'000'),SYSDATE ) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse0 0.00 0.00 0 0 0 0 Execute 11010 12.40 55.78 0 3903 134549 11010 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total11010 12.40 55.78 0 3903 134549 11010 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 65 (APP730LM01) (recursive depth: 1) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: CHOOSE Notice that, in each case, approximately 11,000 executions of the insert statement used about 12 or so seconds of CPU time, got about 130,000 buffers in current mode, and caused little or no disk activity. The ELAPSED time, though, is 15 seconds vs. 55 seconds. The only other statistic that differs is "query", which is about 900 on the fast machine, and 4000 on the slow machine. "Query" is defined as number of buffers gotten for consistent read. So, I'm wondering why an insert needs buffers in consistent read mode, and, as a follow-up, if my assumption is correct that consistent read buffers are always obtained from a rollback segment. Finally, would any of you draw the conclusion that the difference in elapsed time between these two is due to the difference in number of consistent reads? Thanks! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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: Solaris 2.8
David, Solaris 2.8 (why they call it Version 8 and specify it as 2.8 I don't know) is in use for production at a lot of sites. I am sure there are bugs and problems with it the same as any other O/S but generally I think it is a safe bet to install Oracle on Solaris 2.8 . Just ensure you check Metalink for the correct/supported version of Oracle. John -Original Message- Sent: 12 April 2002 18:53 To: Multiple recipients of list ORACLE-L I have Oracle8 running on solaris8 for more than a year and don't see any problem. David -Original Message- Sent: Friday, April 12, 2002 10:26 AM To: Multiple recipients of list ORACLE-L Hi friends and gurus, Anybody has had (or has heard from friends of) any bad experience with Oracle 8 running on Solaris 8. I know that the first supported version on Solaris 8 is 8.0.5. If you know of some I would appreciate f you could share it, and specify which version and patch release. Thanks a lot. Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas 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: SQL statement with hints or without hints
Thanks Tim, At-least now I know I shouldn't tell them to start putting hints. This was my problem to tell them or not to tell them. This still doesn't solve the problem for me though, as everyone is still reluctant to move to CBO. And this is exactly where it gets getting complicated. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: SQL statement with hints or without hints
I worked at one consultancy/software house where you were not supposed to use hints under any circumstance. This was because they wanted to make the code truly portable (I believe SQL Server ignores Oracle hints as if they were comments anyway, correct me if I am wrong). I found this very frustrating and kept fixing code using hints to achieve good performance to prove that it was achievable. However I firmly believe that Oracle provided hints and therefore you should use them if they improve performance. I think it is harder to argue against them than it is to support their use. John -Original Message- Sent: 12 April 2002 16:05 To: Multiple recipients of list ORACLE-L Hi Raj, my two cents, fwiw: Interesting. I have worked places where hints were frowned upon ("why is Oracle not doing it's job? Hey, you, DBA, you fix it. No, I don't like your hint. Fix it") However I feel the opposite way. I have also worked in very liberal environments ("make it work! now!"). I truly believe that we, as developers, know the data better than the optimizer. Don't get me wrong, not all sql statements require hints, but there are statements that will benefit from having them. Determining which is which is the trick, based upon application behavior. I also think that a hybrid environment requires more "assistance" via hints than pure oltp or dss. Hybrids provide their own unique type of "challenge". omg did I just refer to myself as a developer... Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 954-935-4117 > -Original Message- > From: Jamadagni, Rajendra [SMTP:[EMAIL PROTECTED]] > Sent: Friday, April 12, 2002 10:03 AM > To: Multiple recipients of list ORACLE-L > Subject: SQL statement with hints or without hints > > Hi all, > > We are running a kind of hybrid mode, mainly RBO with some tables analyzed > (mainly for intermedia). Last week we had an interesting situation, when > tuning a huge SQL, we created an index and the query worked fine, but > later > in the day developers complained that their queries are running slower. > > We finally took out the new index and things were fine after that, but > this > brought out few important issues, > > 1. Developers do not put hints in their SQL statements. > 2. They implicitly reply on a set of indexes that have worked for them. > 3. Due to reasons of 1 and 2, no new indexes can be created because it > will > make their queries run slower. > 4. As an effect of 3, new queries that can't make use of these set indexes > will always be slower. Some of these queries can't be rewritten to make > use > of the existing indexes due to business logic involved. > > My question is, do you, in your organization recommend putting hints in > the > SQLs all the time, some times or not at all ?? It doesn't really matter > if > you use RBO or CBO. > > When the developers questioned me, I told them 'Oracle optimizer is not an > exact science, especially in a mixed RBO/CBO mode, so it is bound to make > some wrong choices and that is precisely why Oracle calls these things as > "hints", so we tell the optimizer to do the right thing." > > I am in a good mood to write a short note explaining developers why they > (must) use hints in their SQL statements. > > Your input is greatly appreciated > TIA > Raj > __ > Rajendra JamadagniMIS, ESPN Inc. > Rajendra dot Jamadagni at ESPN dot com > Any opinion expressed here is personal and doesn't reflect that of ESPN > Inc. > > QOTD: Any clod can have facts, but having an opinion is an art! > << File: ESPN_Disclaimer.txt >> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas 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: Developer access in test database
I generally give developers access only to the schema's they are developing for. In some cases, they might also get select_catalog_role. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos 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: Developer access in test database
Suzy, You are much too generous. I always have found that asking for a change control form delays things by a few days!! John -Original Message- Sent: 12 April 2002 18:43 To: Multiple recipients of list ORACLE-L I generally give developers access only to the schema's they are developing for. In some cases, they might also get select_catalog_role. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas 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: Rman, Duplicating without set untill.... & cloning with
Ruth, When I startup nomount my clone, no files exist, except the init.ora. I then duplicate using: run { set until time "to_date('040120020214','mmddhh24mi')"; allocate auxiliary channel ch1 type disk; set newname for datafile 1 to '/u03/oradata/BSCSD/system01.dbf'; ...yadda yadda... duplicate target database to BSCSD logfile group 1 ('/u03/oradata/BSCSD/redo1a.log','/u04/oradata/BSCSD/redo1b.log') size 25m, group 2 ('/u03/oradata/BSCSD/redo2a.log','/u04/oradata/BSCSD/redo2b.log') size 25m, group 3 ('/u03/oradata/BSCSD/redo3a.log','/u04/oradata/BSCSD/redo3b.log') size 25m; } The manual states that duplicate creates the control files. Can you tell me more about creating the controlfiles in noarchivelog? Thanks, Jay >>> [EMAIL PROTECTED] 04/12/02 12:14PM >>> Make sure you build the controlfiles on the clone with noarchivelog. That will keep you from having the problem of set until time. Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 11:59 AM I think your theory is correct. I have the same experience, I have to use SET UNTIL, otherwise RMAN complains about the archivelogs. Oracle 8.1.7 on IBM AIX. Robin Jay Hostetter wrote: > > My experience has been that RMAN will try to restore the database right up to the current point-in-time of the production database. So if you are on a remote system, it will be looking for data in the current redo logs (or logs that were archived since the backup), which it can't access.Oracle claims that RMAN will restore the last backup, but I think it is trying to bring the duplicated DB in sync with production. > > I am on 8.1.7 on Tru64. I haven't investigated this enough to prove the theory, so I could be wrong. All I know is that if I don't use SET UNTIL when duplicating a database, RMAN complains about missing archivelogs. > > Jay > > >>> [EMAIL PROTECTED] 04/12/02 10:13AM >>> > I duplicate without at set until time using 8.0.6.3. I follow the > instructions from Metalink. I duplicate our production databases to another > box and change the names from prd to rpt. I do clone all of the > tablespaces. > > Ruth > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, April 12, 2002 5:58 AM > tablespaces > > Hi All, > > I'm currently in the middle of a tar with Oracle about duplicating without > set until time/scn According to the doc's it must be possible but when > trying I get an error message about an archive log file that it needs but > is not yet there. > Any of you duplicating without set untill time/scn etc.. w/o problems > > if so what version RMAN/Oracle What OS/version > What sequence of actions do you perform? > > Second I try to find the rman alternative to cloning just a part of the > database but can't find anything in the Doc's > Anybody > > TIA > > Jack > > === > De informatie verzonden in dit e-mailbericht is vertrouwelijk en is > uitsluitend bestemd voor de geadresseerde. Openbaarmaking, > vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan > derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & > Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en > volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch > voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een > verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten > worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. > > Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u > vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender > en het origineel en eventuele kopieën te verwijderen en te vernietigen. > > Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene > voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De > algemene voorwaarden worden u op verzoek kosteloos toegezonden. > = > The information contained in this communication is confidential and is > intended solely for the use of the individual or entity to whom it is > addressed. You should not copy, disclose or distribute this communication > without the authority of Ernst & Young. Ernst & Young is neither liable for > the proper and complete transmission of the information contained in this > communication nor for any delay in its receipt. Ernst & Young does not > guarantee that the integrity of this communication has been maintained nor > that the communication is free of viruses, interceptions or interference. > > If you are not the intended recipient of this communication please return > the communication to the sender and delete and destroy all copies. > > In carrying out its engagements, Ernst & Young applies general terms and >
Re: OEM Error On Unix
Check Metalink Note:114959.1. Also check if all of the OEM components were installed by running the OUI, sounds like something is missing because on Unix you should have OEM Client, Console, and OMS. "Reddy, Madhusudana" wrote: > > No I was trying to start the OEM console , which requires OMS, but I do not > have it on HP Unix server. > So I have installed OEM on Windows and now able to work with console from my > m/c. > > Thanks for suggestions , but not much help > Thanks > Madhu > > -Original Message- > Sent: Thursday, April 11, 2002 5:57 PM > To: Multiple recipients of list ORACLE-L > > Wouldn't it be: oemapp dbastudio & > > "Reddy, Madhusudana" wrote: > > > > $ /usr/local/bin/sudo find / -name *oem* -print > > > /usr/local/oracle8i/disk1/stage/Components/oracle.sysman.emcommon/2.2.0.0.0/ > > 1/DataFiles/Expanded/Scripts/oemapp > > /db01/app/oracle/product/8.1.7/bin/oemapp > > $ > > > > The above will confirm that , I do not have oemctrl on my Oracle Client on > > HP Unix machine. > > > > Any idea how to get that stuff , do I need to install anything else here > > like OMS , if so where can I get it > > > > Thanks, > > Madhu > > > > -Original Message- > > Sent: Thursday, April 11, 2002 3:13 PM > > To: Multiple recipients of list ORACLE-L > > > > Try to do > > > > $which oemctrl > > > > or > > > > $find / -name *oem* -print > > > > Someone on the distro might add in comments > > > > David > > > > -Original Message- > > Sent: Thursday, April 11, 2002 1:04 PM > > To: Multiple recipients of list ORACLE-L > > > > I could not see this on HP-UNIX client ( $OH/bin ) where I have installed > > all the Oracle Client ( Administration ), > > Would you let me know the location , where I can find this on UNIX > > > > --Madhu > > > > -Original Message- > > Sent: Thursday, April 11, 2002 12:29 PM > > To: Multiple recipients of list ORACLE-L > > > > Try > > > > oemctrl start oms > > > > -Original Message- > > Sent: Thursday, April 11, 2002 11:26 AM > > To: Multiple recipients of list ORACLE-L > > > > Hell All, > > I am seeing the following error , when I am trying to start OEM console on > > HP Unix. Any Idea , what I am missing and would like to know , what is > this > > Management server and how to start it on HP Unix .. Thanks in advance > > > > <<...OLE_Obj...>> > > > > Thanks, > > Madhu V Reddy > > Database Support Services > > (952) 324-0392 ( work ) > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Reddy, Madhusudana > > 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). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Nguyen, David M > > 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). > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Reddy, Madhusudana > > 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). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Nguyen, David M > > 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
Re: SQL statement with hints or without hints
Instructing people to put hints in all of their code is the same as usingCBO full-time, because CBO hints automatically enable the CBO. Using CBOfull-time and not analyzing everything is asking for trouble...Using RBO is unnecessary if you are using Oracle8 v8.0 or above. The CBOoutperforms RBO in any situation except queries against the data dictionary(because you cannot analyze the data dictionary). Please read my paper"Search for Intelligent Life in the CBO" (online atwww.evdbt.com/library.htm) for an explanation as to why it is crucial to setOPTIMIZER_MODE=CHOOSE (not FIRST_ROWS or ALL_ROWS) and use theOPTIMIZER_INDEX_CACHING and (cautiously!) the OPTIMIZER_INDEX_COST_ADJparameters. Especially in Oracle8i, the CBO chooses dramatically superioraccess plans over the RBO...Embedding hints in SQL will eventually cripple your system as conditionschange, as will continued use of the RBO. Instructing people to embed hintson a wholesale basis is not the correct approach, in my opinion. Instead,I'd recommend setting the above-mentioned parameters appropriately (read thepaper!), analyzing everything (use the GATHER_xxx_STATS procedures in theDBMS_STATS package if using Oracle8i or above), and trust the CBO. Themyths about it "not working" or "not being trustworthy" have not been truefor years. For situations where it doesn't choose an index where you thinkit should (or mistakenly chooses one that you think it should't), consideranalyzing involved columns also to deal with any possible data skew problemsby creating "histograms".As Dr. Evil says, "I'm the boss. I need the info". Give the CBO the infoand it will choose the right plan. Like that analogy? I do... :-)I know the CBO works because I have made a living out of tuning Oracle-basedapplications for the past 6-7 years straight, working for Oracle and lateras an independent. Lately (i.e. past 2 years or so), SQL tuning has mainlyconsisted of *removing* hints (both the old "pre-CBO" hints like "+0" and"||''" as well as CBO hints) and analyzing appropriately (i.e. "need theinfo!"). Nothing gets your attention like something that affects yourwallet, so I've been betting my hourly income on these facts. I would havenoticed if I was wrong by now... :-) "Would you like fries with that,ma'am?"Hints should be used only as they were originally intended: infrequent useto deal with the extremely rare circumstances when the CBO cannot choose thebest path. Nothing is perfect, but the CBO in Oracle8i and above is thebest there is...- Original Message -From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>Sent: Friday, April 12, 2002 8:03 AMSubject: SQL statement with hints or without hints> Hi all,>> We are running a kind of hybrid mode, mainly RBO with some tables analyzed> (mainly for intermedia). Last week we had an interesting situation, when> tuning a huge SQL, we created an index and the query worked fine, butlater> in the day developers complained that their queries are running slower.>> We finally took out the new index and things were fine after that, butthis> brought out few important issues,>> 1. Developers do not put hints in their SQL statements.> 2. They implicitly reply on a set of indexes that have worked for them.> 3. Due to reasons of 1 and 2, no new indexes can be created because itwill> make their queries run slower.> 4. As an effect of 3, new queries that can't make use of these set indexes> will always be slower. Some of these queries can't be rewritten to makeuse> of the existing indexes due to business logic involved.>> My question is, do you, in your organization recommend putting hints inthe> SQLs all the time, some times or not at all ?? It doesn't really matterif> you use RBO or CBO.>> When the developers questioned me, I told them 'Oracle optimizer is not an> exact science, especially in a mixed RBO/CBO mode, so it is bound to make> some wrong choices and that is precisely why Oracle calls these things as> "hints", so we tell the optimizer to do the right thing.">> I am in a good mood to write a short note explaining developers why they> (must) use hints in their SQL statements.>> Your input is greatly appreciated> TIA> Raj> __> Rajendra Jamadagni MIS, ESPN Inc.> Rajendra dot Jamadagni at ESPN dot com> Any opinion expressed here is personal and doesn't reflect that of ESPNInc.>> QOTD: Any clod can have facts, but having an opinion is an art!>>
RE: 1Z0-007 Exam
Ken, If this is the 9i upgrade exam then there was a discussion on this recently (about 3-4 weeks ago) Search the archives for a post by Mike Hateley and responses from Robert Freeman. If is not the 9i upgrade which exam is it? John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of KENNETH JANUSZ Sent: 12 April 2002 16:26 To: Multiple recipients of list ORACLE-L Subject: 1Z0-007 Exam Has anyone taken this exam? If so, I would like your feedback. Thanks, Ken Janusz, CPIM
RE: Solaris 2.8
I have Oracle8 running on solaris8 for more than a year and don't see any problem. David -Original Message- Sent: Friday, April 12, 2002 10:26 AM To: Multiple recipients of list ORACLE-L Hi friends and gurus, Anybody has had (or has heard from friends of) any bad experience with Oracle 8 running on Solaris 8. I know that the first supported version on Solaris 8 is 8.0.5. If you know of some I would appreciate f you could share it, and specify which version and patch release. Thanks a lot. Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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: protocol.ora
If you trace the listener you will see that it is looking for .protocol.ora with a leading period. This is a documented bug. Just copy protocol.ora to .protocol.ora. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Fri, 12 Apr 2002, Sajid Iqbal wrote: > We used to use the protocol.ora file to restrict access to the database, > on oracle 8.0.5 > > However since moving to Oracle 8.1.6.3 this doesn't work. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Rule based engine
Not really required. This is clearly explained in both Oracle 8i and Orace 9i manuals: Oracle8i Designing and Tuning for Performance - Chapter 4 The optimizer Oracle9i Database Performance Guide and Reference - Chapter 8 Using the Rule-Based Optimizer John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- Sent: Thursday, April 11, 2002 11:33 PM To: Multiple recipients of list ORACLE-L Check Oracle 7.3 documentation -Original Message- Sent: Friday, April 12, 2002 1:58 AM To: Multiple recipients of list ORACLE-L Does anybody know what algorithms a rule based engine uses.? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: bytes per extent
There are lots of versions of this around, but this reports the sum of the allocated extents. My problem is that the creater of this db made a huge extent and I don't seem to have a way to track growth within the extent. On Thu, Apr 11, 2002 at 07:13:31AM -0800, Ruth Gramolini wrote: > Here is a script that I got from another lister. Hope it is what you're > looking for. > > column tsname format a25 heading 'Tablespace Name' > column tot format 99,999,999 heading 'Size (K)' > column fsp Format 99,999,999 heading 'Free (K)' > column csp Format 999,999 heading 'Free|Extents' > column msp Format 9,999,999 heading 'Max Free|Ext (K)' > column pctused Format 999.99 heading '% Used' > > column tsno noprint > compute sum of tot fsp on report > break on report > > select > fi.tablespace_name "tsname", > sum(fi.bytes)/1024 "tot", > iv.free/1024 "fsp", > ((SUM(fi.bytes)-iv.free)/SUM(fi.bytes))*100 "pctused", > iv.no_of_exts "csp", > iv.max/1024 "msp" > from > dba_data_files fi, > ( > select > t.tablespace_name, > NVL(MAX(f.bytes),0) max, > NVL(sum(f.bytes),0) free, > count(f.bytes) no_of_exts > from > sys.dba_free_space f, > sys.dba_tablespaces t > where > t.tablespace_name=f.tablespace_name(+) > and t.status != 'INVALID' > group by > t.tablespace_name > ) iv > where > fi.tablespace_name = iv.tablespace_name > GROUP BY > fi.tablespace_name, > iv.free, > iv.no_of_exts, > iv.max > ORDER BY > fi.tablespace_name > / > > clear breaks > clear columns > clear computes > > > Regards, > Ruth > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, April 11, 2002 10:39 AM > > > > Is there a query to get the number of bytes used and free > > in an each extent? > > === > > Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Ray Stell > > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ruth Gramolini > 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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: insert performance
No. CPU utilitization averages 40-60% across 4 CPUs. --- Mohammed Shakir <[EMAIL PROTECTED]> wrote: > No enough inserts to bog down the CPUs? > > --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > > Thanks, Mohammed and Anjo, for your replies. > > > > Now my question is this: given that the table structures > (freelists, > > etc.) are identical on the two machines, the init.ora parameters > are > > identical for the two instances, and the machines themselves are > > nearly > > identical (one has 6 CPUs, one 4, but in neither case are the CPUs > > anywhere near maxed out), what could be causing the discrepancy? > > > > PB > > > > --- Mohammed Shakir <[EMAIL PROTECTED]> wrote: > > > data block waits is the indicator of freelists contention. I have > > > never > > > seen a freelists contention, even though I have been running 11 > > > processing doing inserts using 11 CPUs. I have seen library > cache, > > > Shared pool and cache buffer chains waits. I took care of buffer > > busy > > > waits and db file sequential reads by increasing freelists, > > initrans > > > to > > > 11 and by partitioning the disks. In my case most of the data was > > > writting to the same block by as many as 9 processes. I am > working > > on > > > a > > > > > > Sparc 4500 Solaris 2.1.6 with Oracle 8.1.6.0 and 8.1.7.2 > > > > > > Library Shared Pool latch contention was found due to Literal or > > > non-shared SQL. Check SQL_text in SQLarea to find literal SQL. > > Shared > > > Pool contention seems to be due to a very large Shared pool. I > > found > > > a > > > bug report that indicates that cache buffer chain problem was > fixed > > > in > > > 8.1.3.4 and 9.0.1.3 > > > > > > Cache buffer chain is also an indicator of high physical and > > logical > > > I/O. You can check on that as well. > > > > > > Hope this helps. > > > > > > Shakir > > > > > > --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > > > > Greetings! > > > > > > > > I am trying to diagnose a performance difference between two > > > > databases > > > > running the same test. They are similarly configured (same SGA > > > size, > > > > etc.), and the servers are identical except for the number of > > CPUs > > > > (server A has 4, server B has 6). > > > > > > > > On database A, INSERT performance is about 190 rows/second. > > > > > > > > On database B, INSERT performance is over 500 rows/second. > > > > > > > > I saw some cache buffers chains, buffer busy, and library cache > > > latch > > > > waits on database A while the test was running, as well as redo > > log > > > > sync waits. The waits didn't seem excessive, though. I > checked > > > for > > > > "checkpoint not complete" redo allocation messages in database > > A's > > > > alert log and found none. The db_block_lru_latches parameter > is > > > set > > > > to > > > > one-half the number of CPUs in both machines. > > > > > > > > I'd much appreciate any suggestions as to what else to check. > I > > > know > > > > that freelists can be an issue (there are multiple sessions > > > > performing > > > > inserts); how can I check to see if there's freelist > contention? > > > > Anything else I should investigate? > > > > > > > > Many TIA, > > > > > > > > > > > > > > > > > > > > > > > > = > > > > Paul Baumgartel, Adept Computer Associates, Inc. > > > > [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > > > > > > > __ > > > > Do You Yahoo!? > > > > Yahoo! Tax Center - online filing with TurboTax > > > > http://taxes.yahoo.com/ > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > > -- > > > > Author: Paul Baumgartel > > > > 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). > > > > > > > > > = > > > Mohammed Shakir > > > CompuSoft, Inc. > > > 11 Heather Way > > > East Brunswick, NJ 08816-2825 > > > (732) 672-0464 (Cell) > > > (732) 257-6001 (Home) > > > > > > __ > > > Do You Yahoo!? > > > Yahoo! Tax Center - online filing with TurboTax > > > http://taxes.yahoo.com/ > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Mohammed Shakir > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) > 538-5051 > > > San Diego, California-- Public
Re: Rman, Duplicating without set untill.... & cloning with
Make sure you build the controlfiles on the clone with noarchivelog. That will keep you from having the problem of set until time. Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 11:59 AM I think your theory is correct. I have the same experience, I have to use SET UNTIL, otherwise RMAN complains about the archivelogs. Oracle 8.1.7 on IBM AIX. Robin Jay Hostetter wrote: > > My experience has been that RMAN will try to restore the database right up to the current point-in-time of the production database. So if you are on a remote system, it will be looking for data in the current redo logs (or logs that were archived since the backup), which it can't access.Oracle claims that RMAN will restore the last backup, but I think it is trying to bring the duplicated DB in sync with production. > > I am on 8.1.7 on Tru64. I haven't investigated this enough to prove the theory, so I could be wrong. All I know is that if I don't use SET UNTIL when duplicating a database, RMAN complains about missing archivelogs. > > Jay > > >>> [EMAIL PROTECTED] 04/12/02 10:13AM >>> > I duplicate without at set until time using 8.0.6.3. I follow the > instructions from Metalink. I duplicate our production databases to another > box and change the names from prd to rpt. I do clone all of the > tablespaces. > > Ruth > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, April 12, 2002 5:58 AM > tablespaces > > Hi All, > > I'm currently in the middle of a tar with Oracle about duplicating without > set until time/scn According to the doc's it must be possible but when > trying I get an error message about an archive log file that it needs but > is not yet there. > Any of you duplicating without set untill time/scn etc.. w/o problems > > if so what version RMAN/Oracle What OS/version > What sequence of actions do you perform? > > Second I try to find the rman alternative to cloning just a part of the > database but can't find anything in the Doc's > Anybody > > TIA > > Jack > > === > De informatie verzonden in dit e-mailbericht is vertrouwelijk en is > uitsluitend bestemd voor de geadresseerde. Openbaarmaking, > vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan > derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & > Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en > volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch > voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een > verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten > worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. > > Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u > vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender > en het origineel en eventuele kopieën te verwijderen en te vernietigen. > > Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene > voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De > algemene voorwaarden worden u op verzoek kosteloos toegezonden. > = > The information contained in this communication is confidential and is > intended solely for the use of the individual or entity to whom it is > addressed. You should not copy, disclose or distribute this communication > without the authority of Ernst & Young. Ernst & Young is neither liable for > the proper and complete transmission of the information contained in this > communication nor for any delay in its receipt. Ernst & Young does not > guarantee that the integrity of this communication has been maintained nor > that the communication is free of viruses, interceptions or interference. > > If you are not the intended recipient of this communication please return > the communication to the sender and delete and destroy all copies. > > In carrying out its engagements, Ernst & Young applies general terms and > conditions, which contain a clause that limits its liability. A copy of > these terms and conditions is available on request free of charge. > === > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jack van Zanen > 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
Re: Exchanging partition takes a lot of times
Thanks for replying. I've seen your post on a similar question on metalink. I've already tested with the constraints enabled novalidate using the default exchange mode (with validation). The exchange is taking 3 seconds. We'll go this way since we're replacing completely the target tables with the staging ones and we're doing all the test on the staging tables before switching. Regards --- Jonathan Lewis <[EMAIL PROTECTED]> a écrit : > > > See the book - chapter 12, page 250. > It's normal behaviour. There is one bizarre > SQL run if you do the exchange > "without validation" and another (usually cheaper) > if you do it "with validation". > > This relates to checking primary and unique > keys, rather than the partitioning constraint. > > The solution/workaround is to set the constraints > to a RELY ENABLE NOVALIDATE. But the last > time I checked you still got problems with > partitioned > tables in involved in parent/child relationships. > > > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Host to The Co-Operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > Author of: > Practical Oracle 8i: Building Efficient Databases > > > -Original Message- > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > Date: 11 April 2002 17:17 > > > I'm testing the exchange partition and it's taking > 90 > seconds to exchange a table containing 700 000 rows > with a partition containing also 700 000 rows. > > I've noticed that SYS is doing a crazy select to > check > on the PK of the tables even if I used whitout > validation in the exchange statement. > > I this normal behavior ? > > > = > Stéphane Paquette > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jonathan Lewis > 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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: Rman, Duplicating without set untill.... & cloning with
I think your theory is correct. I have the same experience, I have to use SET UNTIL, otherwise RMAN complains about the archivelogs. Oracle 8.1.7 on IBM AIX. Robin Jay Hostetter wrote: > > My experience has been that RMAN will try to restore the database right up to the >current point-in-time of the production database. So if you are on a remote system, >it will be looking for data in the current redo logs (or logs that were archived >since the backup), which it can't access.Oracle claims that RMAN will restore the >last backup, but I think it is trying to bring the duplicated DB in sync with >production. > > I am on 8.1.7 on Tru64. I haven't investigated this enough to prove the theory, so >I could be wrong. All I know is that if I don't use SET UNTIL when duplicating a >database, RMAN complains about missing archivelogs. > > Jay > > >>> [EMAIL PROTECTED] 04/12/02 10:13AM >>> > I duplicate without at set until time using 8.0.6.3. I follow the > instructions from Metalink. I duplicate our production databases to another > box and change the names from prd to rpt. I do clone all of the > tablespaces. > > Ruth > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, April 12, 2002 5:58 AM > tablespaces > > Hi All, > > I'm currently in the middle of a tar with Oracle about duplicating without > set until time/scn According to the doc's it must be possible but when > trying I get an error message about an archive log file that it needs but > is not yet there. > Any of you duplicating without set untill time/scn etc.. w/o problems > > if so what version RMAN/Oracle What OS/version > What sequence of actions do you perform? > > Second I try to find the rman alternative to cloning just a part of the > database but can't find anything in the Doc's > Anybody > > TIA > > Jack > > === > De informatie verzonden in dit e-mailbericht is vertrouwelijk en is > uitsluitend bestemd voor de geadresseerde. Openbaarmaking, > vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan > derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & > Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en > volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch > voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een > verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten > worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. > > Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u > vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender > en het origineel en eventuele kopieën te verwijderen en te vernietigen. > > Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene > voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De > algemene voorwaarden worden u op verzoek kosteloos toegezonden. > = > The information contained in this communication is confidential and is > intended solely for the use of the individual or entity to whom it is > addressed. You should not copy, disclose or distribute this communication > without the authority of Ernst & Young. Ernst & Young is neither liable for > the proper and complete transmission of the information contained in this > communication nor for any delay in its receipt. Ernst & Young does not > guarantee that the integrity of this communication has been maintained nor > that the communication is free of viruses, interceptions or interference. > > If you are not the intended recipient of this communication please return > the communication to the sender and delete and destroy all copies. > > In carrying out its engagements, Ernst & Young applies general terms and > conditions, which contain a clause that limits its liability. A copy of > these terms and conditions is available on request free of charge. > === > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jack van Zanen > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ruth Gramolini > INET: [EMAIL PROTECTED] >
RE: SQL statement with hints or without hints
I am not sure, outlines do require CBO (me thinks) and we are not there yet. Also there is a general resistance around here for that. Now that you have asked me, I will probably take it up to my manager once we move to CBO completely (sometime before Universe stops expanding judging by current support from within our group to migrate to CBO). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Friday, April 12, 2002 10:18 AM To: 'Jamadagni, Rajendra'; '[EMAIL PROTECTED]' Raj, out of curiosity: Why are outlines a no-no? Please elaborate. I haven't used them and would be interested in your opinion. LK ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
protocol.ora
Hi We used to use the protocol.ora file to restrict access to the database, on oracle 8.0.5 However since moving to Oracle 8.1.6.3 this doesn't work. Am I missing something or is there something wrong. TIA -- Sajid Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal 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: Rman, Duplicating without set untill.... & cloning with
My experience has been that RMAN will try to restore the database right up to the current point-in-time of the production database. So if you are on a remote system, it will be looking for data in the current redo logs (or logs that were archived since the backup), which it can't access.Oracle claims that RMAN will restore the last backup, but I think it is trying to bring the duplicated DB in sync with production. I am on 8.1.7 on Tru64. I haven't investigated this enough to prove the theory, so I could be wrong. All I know is that if I don't use SET UNTIL when duplicating a database, RMAN complains about missing archivelogs. Jay >>> [EMAIL PROTECTED] 04/12/02 10:13AM >>> I duplicate without at set until time using 8.0.6.3. I follow the instructions from Metalink. I duplicate our production databases to another box and change the names from prd to rpt. I do clone all of the tablespaces. Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 5:58 AM tablespaces Hi All, I'm currently in the middle of a tar with Oracle about duplicating without set until time/scn According to the doc's it must be possible but when trying I get an error message about an archive log file that it needs but is not yet there. Any of you duplicating without set untill time/scn etc.. w/o problems if so what version RMAN/Oracle What OS/version What sequence of actions do you perform? Second I try to find the rman alternative to cloning just a part of the database but can't find anything in the Doc's Anybody TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst & Young. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst & Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst & Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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 l
1Z0-007 Exam
Has anyone taken this exam? If so, I would like your feedback. Thanks, Ken Janusz, CPIM
Solaris 2.8
Hi friends and gurus, Anybody has had (or has heard from friends of) any bad experience with Oracle 8 running on Solaris 8. I know that the first supported version on Solaris 8 is 8.0.5. If you know of some I would appreciate f you could share it, and specify which version and patch release. Thanks a lot. Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic 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: Rule based engine
Got to www.google.com and search for "Oracle 7.3 documentation". Scott Shafer San Antonio, TX 210-581-6217 > -Original Message- > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > Sent: Friday, April 12, 2002 2:03 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Rule based engine > > > Could you give me more information on this. I do not have the Oracle 7.3 > doc's -- 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: Controlling access to tables
Create a role that has the suid privs, assign the role to the user who needs it, BUT DO NOT MAKE IT DEFAULT. Within the application enable this role for the user as soon as they log in, so they get all access for that session. Outside of this, as the role is not default, they won't have access. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
Is it possible to remove default (TOAD users)
8.1.6 NT TOAD I have a developer assign a default value to a column, and they don't want it anymore. I can reset the default to be NULL (the default default so to speak) but they are able to see the fact that there is a default in TOAD on that column and of course the other columns don't have a default of NULL on them so they would like it removed - it makes them nervous. Is there any way to just get rid of the default showing up without deleting and re-adding the column? Should I tell them not to meddle anymore? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 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: SQL statement with hints or without hints
Hi Raj, my two cents, fwiw: Interesting. I have worked places where hints were frowned upon ("why is Oracle not doing it's job? Hey, you, DBA, you fix it. No, I don't like your hint. Fix it") However I feel the opposite way. I have also worked in very liberal environments ("make it work! now!"). I truly believe that we, as developers, know the data better than the optimizer. Don't get me wrong, not all sql statements require hints, but there are statements that will benefit from having them. Determining which is which is the trick, based upon application behavior. I also think that a hybrid environment requires more "assistance" via hints than pure oltp or dss. Hybrids provide their own unique type of "challenge". omg did I just refer to myself as a developer... Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 954-935-4117 > -Original Message- > From: Jamadagni, Rajendra [SMTP:[EMAIL PROTECTED]] > Sent: Friday, April 12, 2002 10:03 AM > To: Multiple recipients of list ORACLE-L > Subject: SQL statement with hints or without hints > > Hi all, > > We are running a kind of hybrid mode, mainly RBO with some tables analyzed > (mainly for intermedia). Last week we had an interesting situation, when > tuning a huge SQL, we created an index and the query worked fine, but > later > in the day developers complained that their queries are running slower. > > We finally took out the new index and things were fine after that, but > this > brought out few important issues, > > 1. Developers do not put hints in their SQL statements. > 2. They implicitly reply on a set of indexes that have worked for them. > 3. Due to reasons of 1 and 2, no new indexes can be created because it > will > make their queries run slower. > 4. As an effect of 3, new queries that can't make use of these set indexes > will always be slower. Some of these queries can't be rewritten to make > use > of the existing indexes due to business logic involved. > > My question is, do you, in your organization recommend putting hints in > the > SQLs all the time, some times or not at all ?? It doesn't really matter > if > you use RBO or CBO. > > When the developers questioned me, I told them 'Oracle optimizer is not an > exact science, especially in a mixed RBO/CBO mode, so it is bound to make > some wrong choices and that is precisely why Oracle calls these things as > "hints", so we tell the optimizer to do the right thing." > > I am in a good mood to write a short note explaining developers why they > (must) use hints in their SQL statements. > > Your input is greatly appreciated > TIA > Raj > __ > Rajendra JamadagniMIS, ESPN Inc. > Rajendra dot Jamadagni at ESPN dot com > Any opinion expressed here is personal and doesn't reflect that of ESPN > Inc. > > QOTD: Any clod can have facts, but having an opinion is an art! > << File: ESPN_Disclaimer.txt >> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa 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: No DBAs needed on AS400
That was pretty good Brian,:) I think that all of us have had an "Earl Fisher" that we worked for in the past! Thanks, Frank -Original Message- [EMAIL PROTECTED] Sent: Thursday, April 11, 2002 10:29 AM To: Multiple recipients of list ORACLE-L In the late 80's and early 90's I worked on an AS/400. We also had NO DBA, and the box looked and ran like we had NO DBA. Back then IBM pushed AS/400's as a "put it in and forget it/don't worry about it" box. And as a result our "database" looked and ran like a POS. I remember submitting jobs, and after 1 hour of elapsed run time the job had 1 second of cpu. That's when I made my decision to switch to Oracle and Unix. I'm sure AS/400's are fine quality machines, and I'm not trying to bash them here (everything has it's place in the market). If your looking for justification, find out if IBM offers training for AS/400 DBA's. Use the course description as ammunition as to why he should have had a DBA or ask him who carried out the tasks the class described. Also check out books on Amazon for same. Then describe the tasks that a Oracle DBA performs and ask him who he expects to perform them. He's just rattling your cage, time to rattle his. (This email is dedicated to Earl Fisher at Famous Footwear and all the irreparable damage he caused me) Brian P. MacLean Oracle DBA, OCP8i "Jay Hostetter" tions.com>cc: Sent by: Subject: No DBAs needed on AS400 [EMAIL PROTECTED] 04/11/02 06:54 AM Please respond to ORACLE-L We are going through a merger, and management is looking to eliminate positions. Here is a brief summary of my discussion with the new director of IT: Director: "Back when I we were using an AS400, we didn't need a DBA." Me: "Then you probably were just using files." Director: "No, it was a database." Me: "Could you issue SQL commands?" Director: "Yes. But we didn't need a DBA. I guess it was just one of those mysteries of life." My thoughts are that he is using the term "database" in the generic sense of the word (our "files" are our database), or he was using some proprietary database that doesn't even begin to compare to Oracle. For those of you who know AS400s, I would appreciate some insight that would demonstrate why he needs to keep me as a DBA. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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). -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Frank Pettinato 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: RE: Developer access in test database
Dick, Boy or boy or boy, do I ever agree with you! And when you talk with these nut-cases, it is obvious that they have no valid reason for doing this other than their lack of experience. We as professionals are told to put nothing in the SYS account, and then these packages are bought and delivered and we're stuck with'em. really sucks. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, April 12, 2002 9:02 AM To: Mercadante; Thomas F; Multiple recipients of list ORACLE-L Tom, That's all well & good. I believe in keeping people be they developers or end users out of matters that they cannot do anything about or just plain have no reason to be mucking about in, like SYS. Now would someone please tell all of those third party apps folks the same thing. I've got another third party application, called Maximo from MRO to install & guess where they want to go first? You got it, SYS. Why, becuase they want their own views buiilt on all of those deep down tables that SYS owns. I'm geting pretty p&^%ed at these clowns who are constantly telling me that they have tons of experience & know what their doing. It figures though, this one supports MicroSlop Sql too. GOD, I really hope that federal court decides to break up Gates & co. Dick Goulet Reply Separator Author: "Mercadante; Thomas F" <[EMAIL PROTECTED]> Date: 4/12/2002 5:33 AM Jeff, Developers should only have access to stuff they need. This does not include SYS stuff. Basically, do you want to be restoring the development database just because somebody was playing with something? Further, who will be blamed for the developers not being able to do their job while you are restoring the db - the developer who screwed it up, or you? I'm all for giving developers tons of privs in the dev database - up to a point. You need to protect yourself from them ruining your day. To answer your question, I grant s,i,u,d to the schema tables directly to the user to that they can create stored procs if need be. I then migrate stored procs to the dev schema as need be. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, April 12, 2002 8:38 AM To: Multiple recipients of list ORACLE-L In our test databases, I have always granted select,insert,update,delete any table to the developers. While yesterday, one updated a "sys" table. Be non-production, I always felt if it is destroyed can always be recreated. What table privileges do you grant in "test". Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 Jeff, Developers should only have access to stuff they need. This does not include SYS stuff. Basically, do you want to be restoring the development database just because somebody was playing with something? Further, who will be blamed for the developers not being able to do their job while you are restoring the db - the developer who screwed it up, or you? I'm all for giving developers tons of privs in the dev database - up to a point. You need to protect yourself from them ruining your day. To answer your question, I grant s,i,u,d to the schema tables directly to the user to that they can create stored procs if need be. I then migrate stored procs to the dev schema as need be. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]]Sent: Friday, April 12, 2002 8:38 AMTo: Multiple recipients of list ORACLE-LSubject: Developer access in test database In our test databases, I have always granted select,insert,update,delete any table to the developers. While yesterday, one updated a "sys" table. Be non-production, I always felt if it is destroyed can always be recreated. What table privileges do you grant in "test". Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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).
Controlling access to tables
Hi, My co-worker is looking for suggestions on how to accomplish the following. A user has SUID access on a set of tables while in a application. Outside the application we only want that user to have select privs only when using sql*plus. They could have the application changed to reset privs but are there any other alternatives. To summarize in the app have one set a privs everywhere else another set of privs. Thanks Rick -- 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: Brainbench Oracle certifications
...guess I should have added the emoticon. I meant it as a response, not a question... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 8:03 AM > I vote we skip the debate. There is really not much to add to it as pretty > much everyone > voiced their opinion on that one already:-) > > -Original Message- > Sent: Friday, April 12, 2002 6:23 AM > To: Multiple recipients of list ORACLE-L > > > [EMAIL PROTECTED] wrote: > > > is there any value to OCP? > > > here we go again.;-) > > > > -- > -- > Bill "Shrek" Thater ORACLE DBA > [EMAIL PROTECTED] > > You gotta program like you don't need the money, > You gotta compile like you'll never get hurt, > You gotta run like there's nobody watching, > It's gotta come from the heart if you want it to work. > > Terminal glare: A look that kills... > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: bill thater > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Kimberly Smith > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman 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:RE: No DBAs needed on AS400
Tony, Image may have been a network database, but when HP Turbo'd it they made the change to the hierarchical model. Sorry to correct you, but it says so right in the manual. In the "What is TurboImage" section they do acknowledge the background in Image, though there is no mention of TOTAL or that the predecessors where network based. That's a new one on me, so I guess there is one more item I've learned. HP also wrapped their ImageSQL stuff around TurboImage so that one could issue SQL statements against it. That is how the Oracle Gateway does it's thing. Better but not the best. With a little luck our install of TurboImage will see the end of day in a month. Can't wait for some reason. Dick Goulet Reply Separator Author: Tony Johnson <[EMAIL PROTECTED]> Date: 4/11/2002 3:40 PM Going to show my age here ... Actually IMAGE is a network database and not a hierarchical one. It is patterned after a database called TOTAL that back in the days preceding Oracle, Sybase,etc. was installed almost everywhere. While you didnt need a DBA it was good to have people that were knowledgable in normalization and many of the same principles in use today to create your databases. I cut my 'DBA' teeth on TOTAL and did some work with IMAGE many moons ago. A great tool in its time but would be seen as a dinosaur today. -Original Message- Sent: Thursday, April 11, 2002 7:58 AM To: Multiple recipients of list ORACLE-L Jay, We still have one of those dinosaurs running here called HP's TurboImage database. It also "does not need a DBA", actually it does not understand what a DBA is. The "database" is hierachtical with the constraints set during creation. I constantly have fun with the older ManMan developers as we move them into PeopleSoft. They have no idea of what's going on under the covers. SQL is a foreign language to them, their all use to TurboImage intrinsics and a SQL*Plus look alike tool called Quiz. It's kind of fun, you have to "use" a dataset (they call them databases), report out columns, and then set conditions. Kind of like writing SQL with the from clause first. This type of structure has to be carried into the application programs as well, namely you've got to call the dbopen intrinsic before you can use a dataset. BTW, that's in C syntax 'dbopen("MANDB.MDATABAS.MMV090")' (the HP3000 MPEi/x directory structure is kind of strange). There is no such thing as rollback or read consistent view and recovery consists of going back to the last backup, all of which are cold, and having everyone re-enter their transactions. OH, yes, there is no such thing as a user. If you have the ability to loggon to the HP3000, you can use the database and everything is wide open. No ideas of security. Problem with TurboImage is that to "modify a database" you have to rebuild it using an HP utility and then you have to rebuild all of your application programs, or else they crash. Developers do that task as needed and when they mess up, well all hell can and does break loose. Also you need to run these third party utilities each night so that there is room for the dataset to grow and you have to fix broken chains all the time. There is no concept of an instance either. Your application program directly accesses the data files/datasets, so 'impeded' sessions are a common occurance and if an application messes up it can require a system reboot to clear the problems. Sure you don't need a DBA, but you sure as heck need an operator. Problem is that most operators don't get paid as well as a DBA. If your new CIO is in that mindset I'd recommend polishing your resume, cause your gonna need it. Dick Goulet Reply Separator Author: "Jay Hostetter" <[EMAIL PROTECTED]> Date: 4/11/2002 5:54 AM We are going through a merger, and management is looking to eliminate positions. Here is a brief summary of my discussion with the new director of IT: Director: "Back when I we were using an AS400, we didn't need a DBA." Me: "Then you probably were just using files." Director: "No, it was a database." Me: "Could you issue SQL commands?" Director: "Yes. But we didn't need a DBA. I guess it was just one of those mysteries of life." My thoughts are that he is using the term "database" in the generic sense of the word (our "files" are our database), or he was using some proprietary database that doesn't even begin to compare to Oracle. For those of you who know AS400s, I would appreciate some insight that would demonstrate why he needs to keep me as a DBA. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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
RE: Developer access in test database
I grant them full access to their own schema. The production schema owner in TEST databases is still controlled by DBAs. I do grant them selected '... any ...' privileges as needed, in the TEST databases excluding 'select any table'. No one gets it. Instead I grant them select_catalog_role to view SYS owned views etc. In addition, I have created a TOAD_USER role for them to use TOAD fully without running into any problems. These things have cut down a lot of very basic questions and calls from Developers. However, one size does not fit all. This is all dependent on how developement work is done in your particular environment. In my previous job, no one bothered to change sys and system passwords and it was a true 'open systems' environment when it came to TEST/DEV work !! - Kirti -Original Message-From: Kimberly Smith [mailto:[EMAIL PROTECTED]]Sent: Friday, April 12, 2002 8:58 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Developer access in test database As little as they need. I, as a general rule, only grant to the objects they are actually using. One way to look at it is you will always know which grants to move over to test and prod. Now, the group I work with grant select on any table which I have mixed feelings about. With the amount of tables we are dealing with it sure makes life easier. Most of them do not need to even update the application tables as batch loads take care of that (for the most part). -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jeffrey BeckstromSent: Friday, April 12, 2002 5:38 AMTo: Multiple recipients of list ORACLE-LSubject: Developer access in test database In our test databases, I have always granted select,insert,update,delete any table to the developers. While yesterday, one updated a "sys" table. Be non-production, I always felt if it is destroyed can always be recreated. What table privileges do you grant in "test". Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204
RE: V$SESSION
1. The user has connected, but not issued a SQL statement. 2. The user has been connected a while, issued a statement some time ago, but that statement has been flushed via the buffer under the LRU algorithm. Any more? HTH Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -Original Message- Mengler Sent: 11 April 2002 21:29 To: Multiple recipients of list ORACLE-L If there is an entry in V$SESSION, does it imply that user has a current session within the instance? If not, how do I determine which users have logged out of the DB? Under what conditions would I not be able to obtain any SQL from V$SQLAREA for any given SID? 1 select username, osuser, sql_text 2 from v$session ss, v$sqlarea sa 3 where ss.sid = 861 4 and ss.sql_address = sa.address 5* and ss.sql_hash_value = sa.hash_value [EMAIL PROTECTED]> / no rows selected [EMAIL PROTECTED]> select * from v$session where sid = 861; SADDR SIDSERIAL# AUDSID PADDR USER# USERNAME COMMAND TADDR -- -- -- -- -- -- LOCKWAIT STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS - -- -- -- - - MACHINE TERMINAL - PROGRAM TYPE SQL_ADDR SQL_HASH_VALUE PREV_SQL -- --- --- PREV_HASH_VALUE MODULE MODULE_HASH --- --- ACTION ACTION_HASH --- CLIENT_INFO FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# --- - - ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET -- --- - - AF4518E4861680 47669205 AF289B1C 2494 OPS$RCAMPBEL 0 INACTIVE DEDICATED 2494 OPS$RCAMPBEL rcampbel25645 titanpts/200 runform30@titan (TNS interface) USER 00 0 A54EFF6C -2.129E+09 frmula_inq1635528872 from menu_driver 306861083 683581-1 14 13785328 11-APR-02 117 -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 Daylight Savings Time means that everybody gets up 1 hour earlier. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith 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: Rman, Duplicating without set untill.... & cloning with not all tablespaces
I duplicate without at set until time using 8.0.6.3. I follow the instructions from Metalink. I duplicate our production databases to another box and change the names from prd to rpt. I do clone all of the tablespaces. Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 5:58 AM tablespaces Hi All, I'm currently in the middle of a tar with Oracle about duplicating without set until time/scn According to the doc's it must be possible but when trying I get an error message about an archive log file that it needs but is not yet there. Any of you duplicating without set untill time/scn etc.. w/o problems if so what version RMAN/Oracle What OS/version What sequence of actions do you perform? Second I try to find the rman alternative to cloning just a part of the database but can't find anything in the Doc's Anybody TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst & Young. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst & Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst & Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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).
SQL statement with hints or without hints
Hi all, We are running a kind of hybrid mode, mainly RBO with some tables analyzed (mainly for intermedia). Last week we had an interesting situation, when tuning a huge SQL, we created an index and the query worked fine, but later in the day developers complained that their queries are running slower. We finally took out the new index and things were fine after that, but this brought out few important issues, 1. Developers do not put hints in their SQL statements. 2. They implicitly reply on a set of indexes that have worked for them. 3. Due to reasons of 1 and 2, no new indexes can be created because it will make their queries run slower. 4. As an effect of 3, new queries that can't make use of these set indexes will always be slower. Some of these queries can't be rewritten to make use of the existing indexes due to business logic involved. My question is, do you, in your organization recommend putting hints in the SQLs all the time, some times or not at all ?? It doesn't really matter if you use RBO or CBO. When the developers questioned me, I told them 'Oracle optimizer is not an exact science, especially in a mixed RBO/CBO mode, so it is bound to make some wrong choices and that is precisely why Oracle calls these things as "hints", so we tell the optimizer to do the right thing." I am in a good mood to write a short note explaining developers why they (must) use hints in their SQL statements. Your input is greatly appreciated TIA Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: Brainbench Oracle certifications
I vote we skip the debate. There is really not much to add to it as pretty much everyone voiced their opinion on that one already:-) -Original Message- Sent: Friday, April 12, 2002 6:23 AM To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wrote: > is there any value to OCP? here we go again.;-) -- -- Bill "Shrek" Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Terminal glare: A look that kills... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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:RE: Developer access in test database
Tom, That's all well & good. I believe in keeping people be they developers or end users out of matters that they cannot do anything about or just plain have no reason to be mucking about in, like SYS. Now would someone please tell all of those third party apps folks the same thing. I've got another third party application, called Maximo from MRO to install & guess where they want to go first? You got it, SYS. Why, becuase they want their own views buiilt on all of those deep down tables that SYS owns. I'm geting pretty p&^%ed at these clowns who are constantly telling me that they have tons of experience & know what their doing. It figures though, this one supports MicroSlop Sql too. GOD, I really hope that federal court decides to break up Gates & co. Dick Goulet Reply Separator Author: "Mercadante; Thomas F" <[EMAIL PROTECTED]> Date: 4/12/2002 5:33 AM Jeff, Developers should only have access to stuff they need. This does not include SYS stuff. Basically, do you want to be restoring the development database just because somebody was playing with something? Further, who will be blamed for the developers not being able to do their job while you are restoring the db - the developer who screwed it up, or you? I'm all for giving developers tons of privs in the dev database - up to a point. You need to protect yourself from them ruining your day. To answer your question, I grant s,i,u,d to the schema tables directly to the user to that they can create stored procs if need be. I then migrate stored procs to the dev schema as need be. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, April 12, 2002 8:38 AM To: Multiple recipients of list ORACLE-L In our test databases, I have always granted select,insert,update,delete any table to the developers. While yesterday, one updated a "sys" table. Be non-production, I always felt if it is destroyed can always be recreated. What table privileges do you grant in "test". Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 Jeff, Developers should only have access to stuff they need. This does not include SYS stuff. Basically, do you want to be restoring the development database just because somebody was playing with something? Further, who will be blamed for the developers not being able to do their job while you are restoring the db - the developer who screwed it up, or you? I'm all for giving developers tons of privs in the dev database - up to a point. You need to protect yourself from them ruining your day. To answer your question, I grant s,i,u,d to the schema tables directly to the user to that they can create stored procs if need be. I then migrate stored procs to the dev schema as need be. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]]Sent: Friday, April 12, 2002 8:38 AMTo: Multiple recipients of list ORACLE-LSubject: Developer access in test database In our test databases, I have always granted select,insert,update,delete any table to the developers. While yesterday, one updated a "sys" table. Be non-production, I always felt if it is destroyed can always be recreated. What table privileges do you grant in "test". Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204 -- 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: Developer access in test database
As little as they need. I, as a general rule, only grant to the objects they are actually using. One way to look at it is you will always know which grants to move over to test and prod. Now, the group I work with grant select on any table which I have mixed feelings about. With the amount of tables we are dealing with it sure makes life easier. Most of them do not need to even update the application tables as batch loads take care of that (for the most part). -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jeffrey BeckstromSent: Friday, April 12, 2002 5:38 AMTo: Multiple recipients of list ORACLE-LSubject: Developer access in test database In our test databases, I have always granted select,insert,update,delete any table to the developers. While yesterday, one updated a "sys" table. Be non-production, I always felt if it is destroyed can always be recreated. What table privileges do you grant in "test". Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204
RE: Developer access in test database
Jeff, I agree with Raj. Further to what I said before, I have four database - dev, user test, training and production. Training and Prod are always at the same application release level. Developers have total access in dev; and query-only access in the rest. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, April 12, 2002 9:13 AM To: Multiple recipients of list ORACLE-L Jeff, We have dba privileges granted to schema owners in devl databases, test database mimics the prod, so they just have appropriate roles. In prod, no schema level access, everyone goes in with their own id, and no one has those ANY privileges. In next couple of months, we are cutting off developer access to prod. For fixing bugs and debugging, we create a database (named DAYOLD) that is same as production database at 5am that day. It's is all automatic, works fine and all developers are getting adjusted to the fact that prod access is going away. Also we have a benefit of this dayold database, we can run and test all weekly releases on the dayold database, the day before they are applied to production, so we can catch all errors and get them fixed by developers before the code release, this is imp because when we do the code release, most of the developers are probably still in bed that early in the am. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Friday, April 12, 2002 8:38 AM To: Multiple recipients of list ORACLE-L In our test databases, I have always granted select,insert,update,delete any table to the developers. While yesterday, one updated a "sys" table. Be non-production, I always felt if it is destroyed can always be recreated. What table privileges do you grant in "test". Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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: Get the Latest Date
Gavin, We have a similar situation. We simply maintain two tables - one to maintain login-history and another to record the latest-login. The latest-login table would be updated by a trigger on the history-table. A procedure checks both tables and returns approppriate info. -Madhu >From: "Gavin D'Mello" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Get the Latest Date >Date: Fri, 12 Apr 2002 02:43:25 -0800 > >Hi, > I have read up quite a lot before posting this message so please >bear with me if this question is trivial. > I have table which stores session information of users. I have to develop >a >report which gives me the number of times users have logged in ( which is >straightforward ) as well as their last access time. > Since every user has multiple records in the table, I was trying >to >find a way to get me just one row per user which returns the latest date, >rather than checking for the latest date in the client logic. > Is there any function in Oracle which would return the latest date >? > >Thank You, > >Gavin > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Gavin D'Mello > 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath 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: Developer access in test database
Like I said this is test. In prod, they only have select access. However, since in test have "any" rights, were able to get a sys object.>>> [EMAIL PROTECTED] 4/12/02 9:13:19 AM >>>Jeff,We have dba privileges granted to schema owners in devl databases, testdatabase mimics the prod, so they just have appropriate roles. In prod, noschema level access, everyone goes in with their own id, and no one hasthose ANY privileges.In next couple of months, we are cutting off developer access to prod. Forfixing bugs and debugging, we create a database (named DAYOLD) that is sameas production database at 5am that day. It's is all automatic, works fineand all developers are getting adjusted to the fact that prod access isgoing away.Also we have a benefit of this dayold database, we can run and test allweekly releases on the dayold database, the day before they are applied toproduction, so we can catch all errors and get them fixed by developersbefore the code release, this is imp because when we do the code release,most of the developers are probably still in bed that early in the am.Raj__Rajendra Jamadagni MIS, ESPN Inc.Rajendra dot Jamadagni at ESPN dot comAny opinion expressed here is personal and doesn't reflect that of ESPN Inc.QOTD: Any clod can have facts, but having an opinion is an art!-Original Message-Sent: Friday, April 12, 2002 8:38 AMTo: Multiple recipients of list ORACLE-LIn our test databases, I have always granted select,insert,update,delete anytable to the developers.While yesterday, one updated a "sys" table.Be non-production, I always felt if it is destroyed can always be recreated.What table privileges do you grant in "test".Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204
RE: Developer access in test database
Jeff, Developers should only have access to stuff they need. This does not include SYS stuff. Basically, do you want to be restoring the development database just because somebody was playing with something? Further, who will be blamed for the developers not being able to do their job while you are restoring the db - the developer who screwed it up, or you? I'm all for giving developers tons of privs in the dev database - up to a point. You need to protect yourself from them ruining your day. To answer your question, I grant s,i,u,d to the schema tables directly to the user to that they can create stored procs if need be. I then migrate stored procs to the dev schema as need be. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]]Sent: Friday, April 12, 2002 8:38 AMTo: Multiple recipients of list ORACLE-LSubject: Developer access in test database In our test databases, I have always granted select,insert,update,delete any table to the developers. While yesterday, one updated a "sys" table. Be non-production, I always felt if it is destroyed can always be recreated. What table privileges do you grant in "test". Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204
Re: Brainbench Oracle certifications
[EMAIL PROTECTED] wrote: > is there any value to OCP? here we go again.;-) -- -- Bill "Shrek" Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Terminal glare: A look that kills... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater 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: empty string = null?
Jonathan, This behavior is partly because ANSI standard states that treatment of NULL is implementation specific. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, April 11, 2002 11:53 PM To: Multiple recipients of list ORACLE-L You're kidding! The above is really in the Oracle docs? Oracle treats zero-length strings as nulls and yet recommends against us doing likewise? How, pray tell, are we to avoid treating empty strings as nulls, since that's the way the Oracle software works? *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: Developer access in test database
Jeff, We have dba privileges granted to schema owners in devl databases, test database mimics the prod, so they just have appropriate roles. In prod, no schema level access, everyone goes in with their own id, and no one has those ANY privileges. In next couple of months, we are cutting off developer access to prod. For fixing bugs and debugging, we create a database (named DAYOLD) that is same as production database at 5am that day. It's is all automatic, works fine and all developers are getting adjusted to the fact that prod access is going away. Also we have a benefit of this dayold database, we can run and test all weekly releases on the dayold database, the day before they are applied to production, so we can catch all errors and get them fixed by developers before the code release, this is imp because when we do the code release, most of the developers are probably still in bed that early in the am. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Friday, April 12, 2002 8:38 AM To: Multiple recipients of list ORACLE-L In our test databases, I have always granted select,insert,update,delete any table to the developers. While yesterday, one updated a "sys" table. Be non-production, I always felt if it is destroyed can always be recreated. What table privileges do you grant in "test". Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: RE: No DBAs needed on AS400
Wow, this is scary thinking. Look only at expenses, not at the need that needs to be filled. Here's another example of that: http://www.vnunet.com/News/1130760 Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Thursday, April 11, 2002 6:13 PM To: Multiple recipients of list ORACLE-L Subject:Re:RE: No DBAs needed on AS400 Hey, just wait around till the Oracle support bill shows up. No, better yet, leave before then. If he/she is making decisions based strickly on expense you might as well be one of the early rats, namely leave the ship before it sinks! Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 4/11/2002 11:13 AM I would ask myself the question, "Do I want to be working for someone this technically clueless?" I foresee a long education process via-a-vis this guy (I'm assuming that it's a guy) or a never-ending struggle just to get the basic tools that you need to do your job. "Mercadante, Thomas F"To: Multiple recipients of list ORACLE-L @labor.state.cc: ny.us> Subject: RE: No DBAs needed on AS400 Sent by: root 04/11/2002 10:18 AM Please respond to ORACLE-L Jay, I would provide a list of functions you perform daily and what would happen if they are not completed in a timely manner. I would also update my resume. Sounds like a pretty naive director of IT. Either he/she is making a play to move you out and get a prior buddy in place, or is a complete idiot, thinking of short-term money savings but is completely unaware of long-term result. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, April 11, 2002 9:54 AM To: Multiple recipients of list ORACLE-L We are going through a merger, and management is looking to eliminate positions. Here is a brief summary of my discussion with the new director of IT: Director: "Back when I we were using an AS400, we didn't need a DBA." Me: "Then you probably were just using files." Director: "No, it was a database." Me: "Could you issue SQL commands?" Director: "Yes. But we didn't need a DBA. I guess it was just one of those mysteries of life." My thoughts are that he is using the term "database" in the generic sense of the word (our "files" are our database), or he was using some proprietary database that doesn't even begin to compare to Oracle. For those of you who know AS400s, I would appreciate some insight that would demonstrate why he needs to keep me as a DBA. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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). -- 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 spell
RE: Brainbench Oracle certifications
I've got a few BB certifications, purely for my own benefit. It doesn't appear as if many employees (in the UK or Scotland anyway) appear to take them seriously. Kev. -Original Message- Sent: 12 April 2002 12:58 To: Multiple recipients of list ORACLE-L Never even heard of it. If it's a valid test (as in - if it measures what it purports to measure) then it's interesting, but if employers have never heard of it it's useless in that regard. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Thursday, April 11, 2002 7:58 PM To: Multiple recipients of list ORACLE-L Subject:Re: Brainbench Oracle certifications One certain company (to remain nameless) is asking employees to obtain at least 4 Brainbench Certifications this year. And that company also uses Brainbench assessment tests for pre-employment screening. So there are certain employers taking advantage of Brainbench. But otherwise, I haven't found my Brainbench certifications to have much meaning to potential employers. Andrey Bronfin wrote: > > Dear gurus ! > May i ask for your opinions regarding the value of the Brainbench Oracle 8i > Administration certifications. > I understand that it can not be compared t othe OCP, but is there any value > at all for it ? > Thanks a lot in advance. > Andrey. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin 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: Get the Latest Date
Thanks alot Iain - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 4:58 PM > Gavin, > > select username, max(access_time) last_access_time, count(*) no_of_logins > from session_info_table > group by username > > should do it > > Iain Nicoll > > -Original Message- > Sent: Friday, April 12, 2002 11:43 AM > To: Multiple recipients of list ORACLE-L > > > Hi, > I have read up quite a lot before posting this message so please > bear with me if this question is trivial. > I have table which stores session information of users. I have to develop a > report which gives me the number of times users have logged in ( which is > straightforward ) as well as their last access time. > Since every user has multiple records in the table, I was trying to > find a way to get me just one row per user which returns the latest date, > rather than checking for the latest date in the client logic. > Is there any function in Oracle which would return the latest date ? > > Thank You, > > Gavin > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Gavin D'Mello > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Nicoll, Iain (Calanais) > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gavin D'Mello 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).
Developer access in test database
In our test databases, I have always granted select,insert,update,delete any table to the developers. While yesterday, one updated a "sys" table. Be non-production, I always felt if it is destroyed can always be recreated. What table privileges do you grant in "test". Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204
Re: Get the Latest Date
SELECT user_id, COUNT(user_id), MAX(last_connect_time) FROM user_ses GROUP BY user_id; JP On Fri 12. April 2002 12:43, you wrote: > Hi, > I have read up quite a lot before posting this message so please > bear with me if this question is trivial. > I have table which stores session information of users. I have to develop > a report which gives me the number of times users have logged in ( which is > straightforward ) as well as their last access time. > Since every user has multiple records in the table, I was trying to > find a way to get me just one row per user which returns the latest date, > rather than checking for the latest date in the client logic. > Is there any function in Oracle which would return the latest date > ? > > Thank You, > > Gavin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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: Brainbench Oracle certifications
Never even heard of it. If it's a valid test (as in - if it measures what it purports to measure) then it's interesting, but if employers have never heard of it it's useless in that regard. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Thursday, April 11, 2002 7:58 PM To: Multiple recipients of list ORACLE-L Subject:Re: Brainbench Oracle certifications One certain company (to remain nameless) is asking employees to obtain at least 4 Brainbench Certifications this year. And that company also uses Brainbench assessment tests for pre-employment screening. So there are certain employers taking advantage of Brainbench. But otherwise, I haven't found my Brainbench certifications to have much meaning to potential employers. Andrey Bronfin wrote: > > Dear gurus ! > May i ask for your opinions regarding the value of the Brainbench Oracle 8i > Administration certifications. > I understand that it can not be compared t othe OCP, but is there any value > at all for it ? > Thanks a lot in advance. > Andrey. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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: Get the Latest Date
Try the max function to pick the latest timestamp. I assume the column is of type date John -Original Message- Sent: 12 April 2002 11:43 To: Multiple recipients of list ORACLE-L Hi, I have read up quite a lot before posting this message so please bear with me if this question is trivial. I have table which stores session information of users. I have to develop a report which gives me the number of times users have logged in ( which is straightforward ) as well as their last access time. Since every user has multiple records in the table, I was trying to find a way to get me just one row per user which returns the latest date, rather than checking for the latest date in the client logic. Is there any function in Oracle which would return the latest date ? Thank You, Gavin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gavin D'Mello 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas 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: Get the Latest Date
Gavin, select username, max(access_time) last_access_time, count(*) no_of_logins from session_info_table group by username should do it Iain Nicoll -Original Message- Sent: Friday, April 12, 2002 11:43 AM To: Multiple recipients of list ORACLE-L Hi, I have read up quite a lot before posting this message so please bear with me if this question is trivial. I have table which stores session information of users. I have to develop a report which gives me the number of times users have logged in ( which is straightforward ) as well as their last access time. Since every user has multiple records in the table, I was trying to find a way to get me just one row per user which returns the latest date, rather than checking for the latest date in the client logic. Is there any function in Oracle which would return the latest date ? Thank You, Gavin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gavin D'Mello 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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).
Get the Latest Date
Hi, I have read up quite a lot before posting this message so please bear with me if this question is trivial. I have table which stores session information of users. I have to develop a report which gives me the number of times users have logged in ( which is straightforward ) as well as their last access time. Since every user has multiple records in the table, I was trying to find a way to get me just one row per user which returns the latest date, rather than checking for the latest date in the client logic. Is there any function in Oracle which would return the latest date ? Thank You, Gavin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gavin D'Mello 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: Rman, Duplicating without set untill.... & cloning with not a
Jack, Of course you can duplicate a db using RMAN without using the 'set until time' feature. Are u sure all the archived logs which have been backed up by RMAN are present i.e. none of the logs have been accidentally deleted ?? RMAN will use the archived logs to apply the changes and open the database with the 'resetlogs' option. Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 April 2002 10:58 To: Multiple recipients of list ORACLE-L tablespaces Hi All, I'm currently in the middle of a tar with Oracle about duplicating without set until time/scn According to the doc's it must be possible but when trying I get an error message about an archive log file that it needs but is not yet there. Any of you duplicating without set untill time/scn etc.. w/o problems if so what version RMAN/Oracle What OS/version What sequence of actions do you perform? Second I try to find the rman alternative to cloning just a part of the database but can't find anything in the Doc's Anybody TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst & Young. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst & Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst & Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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). ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego,
Re: Phoee, Find views with column name
xxx_DEPENDENCIES would probably be a better starting point. hth connor --- [EMAIL PROTECTED] wrote: > Darn E-mail package. Every once in a while I get a > message as I'm deleting one > or more I don't want & one I do want goes as well. > Well, I guess that's > IBM/Lotus for you. > > Anyway, someone asked how to find all the views that > include a specific column. > Try the following: > > select view_name > from user_views, user_tab_columns > where view_name = table_name > and column_name = ''; > > Dick Goulet > > > -- > 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
Rman, Duplicating without set untill.... & cloning with not all tablespaces
Hi All, I'm currently in the middle of a tar with Oracle about duplicating without set until time/scn According to the doc's it must be possible but when trying I get an error message about an archive log file that it needs but is not yet there. Any of you duplicating without set untill time/scn etc.. w/o problems if so what version RMAN/Oracle What OS/version What sequence of actions do you perform? Second I try to find the rman alternative to cloning just a part of the database but can't find anything in the Doc's Anybody TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst & Young. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst & Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst & Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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: PX Deq: Table Q qref
You could try setting event 10391, levels 64, 512, and 2048 are likely to be the most informative for you problem. Add the values together to run all three at once. The level is a bit flag that goes up to a total of 8191 - but when you set 8191 it looks as if a couple of the flags may disable each other. The trace file is usually a little way behind real time - you can try: oradebug setospid {slave o/s pid} oradebug flush to get the last bit out. The "Execute Reply" is a slave that has sent all it's data and is waiting for new instructions from the co-ordinator - typically a 'die' command. You are dumping a lot of data to a spool file - you haven't exceeded the file size that SQL*Plus can managed have you ? Perhaps something is going wrong there. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 11 April 2002 23:25 |All | |I have some more information on this. I turned on event 10046 on this. From |this I was able to note the following... | |1. I start the sql script and spool the results | |2. It waits for less than a second on "process startup" and "PX Deq: Join |ACK". I think these are waits for starting up the parallel query slaves and |then they acknowledging the startup. | |3. Then it starts waiting on "PX Deq Credit: need buffer" and "PX Deq |Credit: send blkd". I think these are when PQ actually returns data to the |QC | |4. Then it starts witing on "PX Deq: Execute Reply". I see numerous waits on |this. I am not sure on what this wait means. | |5.After two hours, it starts waiting on "PX Deq: Table Q qref" and "SQL*Net |message to client". By this time, it has finished writing to the spool file. |I verified this from the unix timestamp. The trace also shows "FETCH |#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=57419669" | |6. After about 3-4 hours later the SQL prompt returns to me. During this |time it has not written to the spool file. So I am not sure what is being |done in this time... | |What is it doing during the Step5 desciribed above? Also why does it show |"Fetch" when I have already gotten the data.. | | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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: ORACLE 9i doesn't start on Linux Solved
Hi, Thank you very much. It solved my problem. Zsolt Csillag Hungary At 12:38 2002.04.11. -0800, you wrote: >Hi Zsolt , >check /etc/oratab, you may need to replace N by Y >hth >Vadim > >-Original Message- >Sent: Thursday, April 11, 2002 4:04 PM >To: Multiple recipients of list ORACLE-L > > > >Hi, > > >I've installed Oracle 9i on Suse Linux 7.1. > >The lsnrctl starts nicely, however when I type "dbstart" then >it does absolutelly nothing. >No error message, but the database won't start. > >Any ideas? > > >Thank you in advance > >Zsolt Csillag, >Hungary >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Zsolt Csillag > 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). >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Vadim Gorbounov > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Csillag Zsolt 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).