RE: RE: wait/notify syntax for unix help please
There's no problem with waiting after the process has already finished,
you'll just get a non-zero return code the wait but evrything will still
work fine.
Chris
-Original Message-
Sent: 27 October 2003 18:54
To: Multiple recipients of list ORACLE-L
if you attemp to wait after the process is complete, will it cause a
problem? say the PID no longer exists when you issue wait?
From: Dunscombe, Chris [EMAIL PROTECTED]
Date: 2003/10/27 Mon AM 11:39:34 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: wait/notify syntax for unix help please
I don't know about Solaris but on HP-UX and AIX you can do:
run_sql_1
run_sql_2
wait
This will wait until both have finished.
Re a specific PID $! will return you PID of the last child process and
then
you can wait on that PID. Looks something like:
run_sql_1
run_sql_2
PID_WAIT=$!
wait ${PID_WAIT}
HTH
Chris Dunscombe
-Original Message-
Sent: 27 October 2003 16:09
To: Multiple recipients of list ORACLE-L
I need to parallelize some sql operations and Im running them from unix
scripts.
I want to spawn off a few in the background from a master script, then
have
the master script 'wait' for them to finish. Ive done this in Java and
with
dbms_alert, but I cant dig up the syntax to do this with korn shell on
solaris.
Also, if I want to wait for a specific PID, how do I get the PID of the
thread I want to wait for?
so I have
nohup run_sql
wait(on previous nohup)
then to use notify, I just use 'notify()' inside the script right?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dunscombe, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dunscombe, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: dba interview questions
How salty is the water? -Original Message- Bellow, Bambi Sent: 27 October 2003 17:20 To: Multiple recipients of list ORACLE-L Bill -- I appreciate your input to this very complex question. IMHO, however, she turned me into a newt! is, technically, an interview *statement* rather than an interview *question*. That being said, it can be used at the beginning or end of the interview to set tone, but lesser candidates might be inclined to send envelopes full of grass to thank you for taking the time to interview them. This may, or may not, be received favorably, and one must realize the risks in making bold statements when interviewing candidates who may be subpar. Along those lines, I admit that in all my years of interviewing, I have found that there are interview questions that it is simply better not to ask: when do you come around next? (leads interviewee to think that he has advanced to a second interview), and how do you know he's a king? (leads to answers about SCOTT and MILLER with CONNECT BYs. Not at all the intended response). However, I think you're on the right track, if a bit early in the scene. Perhaps you'd consider What also floats in water?. Of course, there are multiple answers (e.g., apples, very small stones, mud, churches) which could arguably be deemed to show an acceptable level of knowledge, an ability to think outside the box, and an inventive approach, even if the salient points might not be wholly accurate. Even so, the correct answer (a duck) is technically not correct as it does not float, but swims (new scientific has determined this with a fair amount of accuracy), and I'd hate to hire a DBA who was stuck in old paradigms of science and/or technology. A CICS programmer, sure, but not a DBA. Now, if you'll just bring me a shrubbery, I can get on with my database analysis. Bambi. -Original Message- Sent: Friday, October 24, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Cary Millsap scribbled on the wall in glitter crayon: Bambi, Be careful. Many will fall in love with you if you continue to submit such things as this to the list. how about: she turned me into a newt! a newt? i got better. -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] Behind an able man there are always other able men. - Chinese Proverb -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: anyone have opinions on the future of the 'grid'?
Well, they did not come straight out and say we invented the grid. They did say we can help you to use and manage grid computers and our software can use the grid. They will have software to add/remove computers from a grid, clone computers, propagate changes etc. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 27, 2003 6:39 PM I would imagine that the perspectice of grid computing offered at OOW would be somewhat Oracle centric. Grid computing does not require Oracle - it is, here comes an overused buzzword, a paradigm shift. There, I've said it. I think this is the first time I've ever used that term, and it may have even been properly used. That said, grid computing is the latest hype cycle, and time will tell if there's anything to it. Jared On Mon, 2003-10-27 at 00:59, Yechiel Adar wrote: Hello Tom From my limited understanding in OOW in Paris, the Grid is a BIG RAC, with options to add or remove servers as you go along. It can be used for web servers, applications servers, database servers etc. There is a lot more in 10g that can help you manage also separated databases. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 20, 2003 2:54 PM If this is true, then what is the difference between RAC (formerly Oracle Parallel Server) and Grid computing? Is this just another fine example of Oracle taking an existing product and renaming it yet again? they *like* doing this. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, October 17, 2003 3:35 PM To: Multiple recipients of list ORACLE-L Actually Dennis is quite correct. Part of the architectural framework is that you treat disk like a service. All disk storage is sharable across the enterprise grid you configure. If you have multiple grids in place, you would have multiple disk architecture frameworks in place. Alternately, you could share the disk architectures to create a common, scalable grid. Unfortunately, Oracle has been very lax in discussing the financial requirements for the grid. Forget Oracle pricing folks -- think more about the cost to reconfigure your entire data center. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (313) 227-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -Original Message- Sent: Friday, October 17, 2003 2:41 PM To: Multiple recipients of list ORACLE-L Subject: RE: anyone have opinions on the future of the 'grid'? But that would require Oracle software to be installed on all the grids, don't you think? I'm really curious how they are going to pull this off. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 16, 2003 6:30 PM To: Multiple recipients of list ORACLE-L I think the assumption must be that all the computers on the grid are attached to a SAN. Does that seem reasonable? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 16, 2003 5:04 PM To: Multiple recipients of list ORACLE-L For database type stuff, one must wonder how the data itself can be handled by CPU resources scattered hither and yon. The answer comes from the fairly recent knowledge that, in our universe, every particle has a matching particle; and changing one of the particles results in a change in the other particle. Well, the solution to trying to shove data all over the grid becomes obvious: One need only establish central management of the matching particles that make up the CPU and memory of all the computers involved. Initially, this would seem to be a daunting task ... until we recall that Larry is God. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego,
RE: dba interview questions
I ask things like tell me the thing you've done that you are most proud of and tell me your nightmare situation and how did you recover from it Ans: My worst nightmare, my date pick her nose infront of me, I call cab infront of her. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 8th Deadly Sin - Unkept Promises
The fact that they baited you with 10g to make you listen to sales pitch does not say anything about 10g. I was in OOW in Paris last week and 10g seems to have a lot of goodies in it. As we are now moving to 9.2 I do not think that we will use 10g but will jump, in 3-4 years, to 11g. Anyway I am going to move to OEM 10g, if they deliver on promises made during presentations. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 27, 2003 7:19 PM I went to Oracle technology day in Milwaukee the other day and was a bit put off by the marketing hype on 10g and the lack of 10g facts. The technology day was promoted as a grid day...10g this and grid that. What did we get? Marketing hype about collaboration suite. They used 10g to get people to come and listen to pitches for 9iRAC and collaboration suite. The demo on 9iRAC was on RAW partitions and was not useing OCFS. If 10g is a dissapointment when it finally ships I think I might start looking to learn another Db platform. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 8th Deadly Sin - Unkept Promises
As a tool provider, I'm interested - what promises did they make for OEM 10g? Cheers Mark -Original Message- Yechiel Adar Sent: 28 October 2003 09:54 To: Multiple recipients of list ORACLE-L The fact that they baited you with 10g to make you listen to sales pitch does not say anything about 10g. I was in OOW in Paris last week and 10g seems to have a lot of goodies in it. As we are now moving to 9.2 I do not think that we will use 10g but will jump, in 3-4 years, to 11g. Anyway I am going to move to OEM 10g, if they deliver on promises made during presentations. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 27, 2003 7:19 PM I went to Oracle technology day in Milwaukee the other day and was a bit put off by the marketing hype on 10g and the lack of 10g facts. The technology day was promoted as a grid day...10g this and grid that. What did we get? Marketing hype about collaboration suite. They used 10g to get people to come and listen to pitches for 9iRAC and collaboration suite. The demo on 9iRAC was on RAW partitions and was not useing OCFS. If 10g is a dissapointment when it finally ships I think I might start looking to learn another Db platform. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
MSSQL Link Server connection failure to Oracle 817
Hello, To my surprise I see there alot of us on this list, use MSSQL and Oracle in the same environment. So please forgive me for asking this but it is a huge issue here at my work. The problem is like this, we have a MSSQL 2000 box connecting to Oracle 817, via Linked servers using OLEDB. The jobs will run fine for awhile, but then suddenly fail with the following error Executed as user: TFMC\Administrator. OLE DB provider 'MSDASQL' reported an error. [SQLSTATE 42000] (Error 7399) Driver's SQLSetConnectAttr failed] [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed. OK, this is specifically the MS driver being used here, but when Oracle drivers are used, we have the same issues. Both Databases are on HP/Compaq servers, and the Windows 2000 platform. I have loaded the newest patches for OLEDB on the MSSQL for the Oracle Client, but nothing helps. Has anyone experienced this issue before? Regards Denham Eva Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denham Eva INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
dynamic sql problem
Hi Gurus, I have problem running Dynamic SQL through a package, though it runs fine in a unnamed block. This is the sample code DECLARE lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; / This runs fine. But as soon as I put this inside a package I get an error PROCEDURE test is lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at COMMADM.CT_REFRESH_PK, line 415 ORA-06512: at line 1 This line 415 is the execute immediate line. Any clues why this is acting strangely. Thanks in advance for your time in answering to my query With Warm Regards Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED]
Re: dba interview questions
Only two questions are required to ensure you get an appropriate person for the job (any job): 1) What do you think of David Bowie, is he brilliant or what ? and providing they answer the above question positively 2) Are you any good ? Works every time ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 7:44 PM I ask things like tell me the thing you've done that you are most proud of and tell me your nightmare situation and how did you recover from it Ans: My worst nightmare, my date pick her nose infront of me, I call cab infront of her. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dynamic sql problem
Title: Mensagem to use it inside packages u must have some priviledges given directly to u not to a role. Regards PG -Mensagem original-De: Siddharth Haldankar [mailto:[EMAIL PROTECTED] Enviada: terça-feira, 28 de Outubro de 2003 11:39Para: Multiple recipients of list ORACLE-LAssunto: dynamic sql problem Hi Gurus, I have problem running Dynamic SQL through a package, though it runs fine in a unnamed block. This is the sample code DECLARE lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; / This runs fine. But as soon as I put this inside a package I get an error PROCEDURE test is lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "COMMADM.CT_REFRESH_PK", line 415 ORA-06512: at line 1 This line 415 is the execute immediate line. Any clues why this is acting strangely. Thanks in advance for your time in answering to my query With Warm Regards Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED]
Re: 8th Deadly Sin - Unkept Promises
The one that I like was cloning and version control of the software. When you need a new server you can tell oracle to clone the software from existing server. Also you can install patch in one server and then move it to one or more servers. You can also clone databases this way. Next versions will allow you to clone whole computers, including the OS. All this is primarily done to create and manage servers in the grid but will work also on standalone servers. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:04 PM As a tool provider, I'm interested - what promises did they make for OEM 10g? Cheers Mark -Original Message- Yechiel Adar Sent: 28 October 2003 09:54 To: Multiple recipients of list ORACLE-L The fact that they baited you with 10g to make you listen to sales pitch does not say anything about 10g. I was in OOW in Paris last week and 10g seems to have a lot of goodies in it. As we are now moving to 9.2 I do not think that we will use 10g but will jump, in 3-4 years, to 11g. Anyway I am going to move to OEM 10g, if they deliver on promises made during presentations. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 27, 2003 7:19 PM I went to Oracle technology day in Milwaukee the other day and was a bit put off by the marketing hype on 10g and the lack of 10g facts. The technology day was promoted as a grid day...10g this and grid that. What did we get? Marketing hype about collaboration suite. They used 10g to get people to come and listen to pitches for 9iRAC and collaboration suite. The demo on 9iRAC was on RAW partitions and was not useing OCFS. If 10g is a dissapointment when it finally ships I think I might start looking to learn another Db platform. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dynamic sql problem
Your account probably has the create table privilege granted through the resource role, grant create table to your account and try again, privileges granted through a role are not active when running a procedure. -Original Message-From: Siddharth Haldankar [mailto:[EMAIL PROTECTED]Sent: 28 October 2003 11:39To: Multiple recipients of list ORACLE-LSubject: dynamic sql problem Hi Gurus, I have problem running Dynamic SQL through a package, though it runs fine in a unnamed block. This is the sample code DECLARE lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; / This runs fine. But as soon as I put this inside a package I get an error PROCEDURE test is lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "COMMADM.CT_REFRESH_PK", line 415 ORA-06512: at line 1 This line 415 is the execute immediate line. Any clues why this is acting strangely. Thanks in advance for your time in answering to my query With Warm Regards Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED]
RE: dynamic sql problem
Siddharth, All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer rights. The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer rights queries SESSION_ROLES, the query does not return any rows. Named PL/SQL blocks that execute with invoker rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. So the problem might be that you have been granted 'CREATE TABLE' through a role and not directly. Regards, Charu. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Siddharth HaldankarSent: 28 October 2003 17:09To: Multiple recipients of list ORACLE-LSubject: dynamic sql problem Hi Gurus, I have problem running Dynamic SQL through a package, though it runs fine in a unnamed block. This is the sample code DECLARE lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; / This runs fine. But as soon as I put this inside a package I get an error PROCEDURE test is lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "COMMADM.CT_REFRESH_PK", line 415 ORA-06512: at line 1 This line 415 is the execute immediate line. Any clues why this is acting strangely. Thanks in advance for your time in answering to my query With Warm Regards Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED] * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com
Re: dba interview questions
Only two questions are required to ensure you get an appropriate person for the job (any job): 1) What do you think of David Bowie, is he brilliant or what ? David who? Shall we sign the contracts now? ;) Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Execution Plan is good but HIGH CPU
It's 800 sec for 5617 exec/fetch calls. It looks like it's a sql inside a cursor loop or stored proc that gets called from some app. If you are unhappy, try to get rid of the cursor logic and get everything done in one sql call. Waleed -Original Message- Sent: Tuesday, October 28, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Hi, Execution plan looks good but the query is consuming 800 seconds CPU timewhy? SELECT sampleavail, sample_cost_amount, sample_sale_amount, discount_room, discount_case, discount_half_case, allow_cut, retail_cut_amount, cost_cut_amount, gp_room from tbljnwpbookvendortype t1, tbljnwpbookvendor t2 where t1.jnwpbvid = t2.jnwpbvid and t2.prsuid = :b3 and t2.wpbkid = :b2 and t1.wpptid = :b1 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 5618 0.63 0.58 0 0 0 0 Fetch 5617800.05 782.07 01409683 0 4187 --- -- -- -- -- -- -- total11236800.68 782.66 01409683 0 4187 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 109 (DDTBL) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDOR' 0 INDEX (UNIQUE SCAN) OF 'TBLJNWPBOOKVENDOR_UQ1' (UNIQUE) 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDORTYPE' 0 INDEX (RANGE SCAN) OF 'TBLJNWPBOOKVENDORTYPE_IDX1' (NON-UNIQUE) Muqthar Ahmed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: dba interview questions
On 10/28/2003 06:59:24 AM, Richard Foote wrote: 1) What do you think of David Bowie, is he brilliant or what ? Or what. and providing they answer the above question positively 2) Are you any good ? I'm ad to the one. Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Solved - RE: UTL_RAW and slowness
Thanks Vladimir ... your input has made me look at my code again ...
Here is relevant portion of profsum.sql output ...
profsum
Lines taking more than 1% of the total time, each run separate
RUNID HSECSPCT OWNER UNIT_NAME LINE# TEXT
- --- -- --- -- -- -
3 809.03 86.3 ST_DVDB2STWRITER_PKG_RAJ246 ntcpchar := ASCII(SUBSTR
(msg_text, i,1));
3 69.297.4 ST_DVDB2STWRITER_PKG_RAJ256 COMMIT;
3 13.621.5 ST_DVDB2STWRITER_PKG_RAJ248 nenctcpchar :=
TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),'');
3 10.131.1 ST_DVDB2STWRITER_PKG_RAJ247 r_chr :=
utl_raw.cast_to_raw(CHR(ntcpchar));
=
=
Most popular lines (more than 1%), summarize across all runs
HSECSPCT UNIT_OWNER UNIT_NAME LINE# TEXT
--- -- --- -- -
809.03 86.3 ST_DVDB2STWRITER_PKG_RAJ246 ntcpchar := ASCII(SUBSTR (msg_text,
i,1));
69.297.4 ST_DVDB2STWRITER_PKG_RAJ256 COMMIT;
13.621.5 ST_DVDB2STWRITER_PKG_RAJ248 nenctcpchar :=
TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),'');
10.131.1 ST_DVDB2STWRITER_PKG_RAJ247 r_chr =
utl_raw.cast_to_raw(CHR(ntcpchar));
/profsum
This shows that substr must have been the culprit ...
BTW I benchmarked your code, extended the strings to 2000 characters and ran each
conversion in a loop of 2000 and using utl_raw method turned out to be the fastest.
thanks again for your insight and sample code ... I never knew nor noticed other
utl_raw subprograms like utl_raw.copies ...
Now due to pipelining my code is very fast and to accomodate a 122 baud feed, I have
insert artificial delays in my code. 8:)
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
Sent: Monday, October 27, 2003 6:59 PM
To: Multiple recipients of list ORACLE-L
Raj,
Jamadagni, Rajendra wrote:
Read if you are interested ...
Finally I got some time and luckily the largest message to use with dbms_profiler.
And the results shocked me dbms_profiler showed me that instead of utl_raw,
substr() was the culprit. Remember my operation is character by character.
Could you please show dbms_profiler output data? I'd also suggest to remove
everything related to TCP/IP out from the code -- to get the clear picture.
Some questions/suggestions, if you do not mind
. I do not think that you need utl_raw to do byte by byte xor operation -- you
could do it using BITAND -- it should be faster.
. What's the point to do it char by char in general? Do you modify encryption
key making it dependent on each given char in the string? If not why not to
use something like the code below (see r1), hope I did not make any mistake:
VAR r1 VARCHAR2(256);
VAR r2 VARCHAR2(256);
VAR r3 VARCHAR2(256);
DECLARE
r_key RAW(1) := '41'; -- hex
r_key_n BINARY_INTEGER := 65; -- dec
l_n BINARY_INTEGER; -- ASCII of current char
-- string to be encrypted
l_string VARCHAR2(128) := 'AZBYCXDWEVFUGT';
-- its length
l_string_len BINARY_INTEGER := NVL(LENGTH(l_string), 0);
BEGIN
-- string
-- one can define utl_raw.copies(r_key, 128) as a constant, if it's possible.
:r1 :=
utl_raw.substr(utl_raw.bit_xor(utl_raw.cast_to_raw(l_string),
utl_raw.copies(r_key, 128)), 1, l_string_len);
-- char by char
:r2 := '';
FOR i IN 1..l_string_len
LOOP
:r2 := :r2 || utl_raw.bit_xor(utl_raw.cast_to_raw(SUBSTR(l_string, i, 1)), r_key);
END LOOP;
-- bitand
:r3 := '';
FOR i IN 1..l_string_len
LOOP
l_n := ASCII(SUBSTR(l_string, i, 1));
:r3 := :r3 || TO_CHAR(BITAND(-BITAND(-l_n - 1, -r_key_n - 1) - 1, -BITAND(l_n,
r_key_n) - 1), 'FM0X');
END LOOP;
END;
/
PRINT r1
PRINT r2
PRINT r3
BTW, you have double conversion to ASCII then back to CHR (lines 6 and 7) --
it's not dramatic but it can be eliminated.
HTH.
1 msglen := LENGTH (msg_text);
2 nCharsSent := 0;
3 p('Encrypting data...');
4 FOR i IN 1 .. msglen
5 LOOP
6 ntcpchar := ASCII (SUBSTR (msg_text, i, 1));
7 r_chr:= utl_raw.cast_to_raw(CHR(ntcpchar));
8 nenctcpchar := TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),'');
9 tcpmsglen := UTL_TCP.write_text (gv_tcp_conn, CHR(nenctcpchar), NULL);
10 nCharsSent := nCharssent + 1;
11 IF MOD(ncharssent,128) = 0 THEN
12p('Before Flush ...');
13UTL_TCP.FLUSH (gv_tcp_conn);
14p('Connection Flushed at ' || ncharssent);
15 END IF;
16 --
17 END LOOP; -- FOR i IN 1 .. msglen
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily
Re: dba interview questions
You're likely to get the kind of response my kids would give: Wasn't he with the Beatles or the Stones or some other *old* band like that? Hell, my son considers the Offspring to be over the hill and Linkin Park as starting to lose it... About 7 years ago, I interviewed someone who listed Phish as one of his interests. I asked him What is Phish? and then spelled it for him. The expressions that swept across his face in one second ran from frank astonishment, to disbelief, to pity, to a carefully-composed poker face as he answered, A musical group that I like. Food for thought: when I was a kid in the 70s, my father would play his big band records and my brothers and I would roll our eyes and leave the house. Such lame, ancient music! At the time, those recordings were 30-35 years old... Um... For a kid today, the Stones, the Beatles, Pink Floyd, Frank Zappa, Yes, and Bowie are far more ancient. Not just in years, but the years do add up... ..'scuse me, I think I hear a bottle of Metamucil calling... on 10/28/03 4:59 AM, Richard Foote at [EMAIL PROTECTED] wrote: Only two questions are required to ensure you get an appropriate person for the job (any job): 1) What do you think of David Bowie, is he brilliant or what ? and providing they answer the above question positively 2) Are you any good ? Works every time ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 7:44 PM I ask things like tell me the thing you've done that you are most proud of and tell me your nightmare situation and how did you recover from it Ans: My worst nightmare, my date pick her nose infront of me, I call cab infront of her. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Conflicting Java VM after Oracle 9.2 client install
We had a similar problem with a IE application using a particular version of Java - it broke once we did an Oracle 9i client install. Not sure if the same thing might help you. The workaround that I used was to export the registry for the Java Version (HKEY_LOCAL_MACHINE - JavaSoft - Java Plug-in - 1.3.1_08), renamed the existing key to 1.3.1_01 and re-imported the registry. End result was two directories (a 01 and an 08) and things started working again. It helped that I had an explorer error pop-up saying it could not locate 1.3.1_01, so I had a clue as to what to use as the new name. Babette Turner-Underwood -Original Message- Sent: 2003-10-27 8:44 AM To: Multiple recipients of list ORACLE-L Hi! Does anybody have any exprience with conflicting client JVMs? We are installing software though Microsoft SMS software packaging on the Clients (PC running XP). The deal is that another application (PVCS Dimensions) works fine if it is distributed on the systems without the Oracle 9.2 client. Because it can then use the Microsoft Virtual Machine that comes with Internet Explorer. But if we also install the Oracle Client 9.2, the Sun Java VM is installed as well and then the PVCS client doesn't work anymore. The question is: can I install the Oracle Client without having the Sun VM installed? Or is there a way that I can modify my Oracle installation that it doesn't change Internet Explorer settings to use the Sun VM? This is 9.2 client on Win XP. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Execution Plan is good but HIGH CPU
Have you run SQLTRACE on this query? The detail in the trace file will show where the cpu is being consumed. There is insufficient data in the summary to reach any conclusion. Jared On Mon, 2003-10-27 at 21:24, Muqthar Ahmed wrote: Hi, Execution plan looks good but the query is consuming 800 seconds CPU timewhy? SELECT sampleavail, sample_cost_amount, sample_sale_amount, discount_room, discount_case, discount_half_case, allow_cut, retail_cut_amount, cost_cut_amount, gp_room from tbljnwpbookvendortype t1, tbljnwpbookvendor t2 where t1.jnwpbvid = t2.jnwpbvid and t2.prsuid = :b3 and t2.wpbkid = :b2 and t1.wpptid = :b1 call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 5618 0.63 0.58 0 0 0 0 Fetch 5617800.05 782.07 01409683 04187 --- -- -- -- -- -- -- total11236800.68 782.66 01409683 04187 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 109 (DDTBL) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDOR' 0 INDEX (UNIQUE SCAN) OF 'TBLJNWPBOOKVENDOR_UQ1' (UNIQUE) 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDORTYPE' 0 INDEX (RANGE SCAN) OF 'TBLJNWPBOOKVENDORTYPE_IDX1' (NON-UNIQUE) Muqthar Ahmed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: performance issue on select count(*)
Tim, Thanks for your reply. The select count(*) is doing an index range scan on the column tid. No table access in the execution plan. The query you provided returned the following result: NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY LAST_ANAL - --- --- - 2326064.1 161201 1 3 28-OCT-03 I have tried local partition index on tid but the execution time was still around 10secs for the initial execution and 1sec subsequently. The table is partitioned on a date field. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. I monitored the wait events during both executions. They were all pretty low. It does not appear to be I/O bound either. tnsping from my PC to the database took about 30msec. Any other suggestions what I could check? Thanks. linda select * from v$session_event where sid=98; Initial run: SID EVENTTOTAL TOTAL TIME AVERAGE MAX WAITS TIMEOUTS WAITED WAIT WAIT --- - -- 98 latch free 115 681 .008695652 1 98 control file sequential read 300 0 0 98 refresh controlfile command 100 0 0 98 buffer busy waits 100 0 0 98 log file sync 101 1 1 98 db file sequential read 1968 0 827 .42022357710 98 file open 502 .4 1 98 SQL*Net message to client 305 00 0 0 98 SQL*Net message from client 3040 31819 104.667763 29911 Subsequent run: - SID EVENT TOTAL TOTAL TIME AVERAGE MAX WAITS TIMEOUTS WAITED WAIT WAIT --- - -- 99 latch free 162 93 3 .018518519 2 99 control file sequential read 3 0 0 0 0 99 refresh controlfile command1 0 0 0 0 99 buffer busy waits 1 00 0 0 99 log file sync 1 00 0 0 99 file open 3 01 .3 1 99 SQL*Net message to client 54 00 0 0 99 SQL*Net message from client53 02893 54.5849057 2698 From: Tim Gorman [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: performance issue on select count(*) Date: Mon, 27 Oct 2003 10:34:59 -0800 Linda, I am guessing that since your table is partitioned on an unspecified date column, that the index on TID is either LOCAL or non-partitioned (i.e. GLOBAL). If it is LOCAL (you would have had to specify the keyword, as it is not the default), then you will be performing indexed RANGE scans on each of the partitions in the index. Naturally, the more partitions there are, the longer this may take, but probably not a great deal longer than if the index was a GLOBAL non-partitioned index. But regardless of the number of RANGE scans and the type of index it is, the main question is whether TID is a good index to use in the first place. This is a matter of data, purely the nature of the data. You can diagnose this better using results from the following query: SELECT NUM_ROWS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, LAST_ANALYZED FROMDBA_INDEXES WHERE INDEX_NAME = 'name-of-index'; Of particular interest are the values in AVG_LEAF_BLOCKS_PER_KEY and AVG_DATA_BLOCKS_PER_KEY, as the cost-based optimizer uses these to calculate the cost of an index RANGE scan (assuming that column-level statistics or histograms have not been gathered). If the values of these two columns are high, then the CBO will be hesitant to use
RE: dba interview questions
I am shocked Tim you forgot Robbin Trower :-). -Original Message- Sent: Tuesday, October 28, 2003 7:09 AM To: Multiple recipients of list ORACLE-L You're likely to get the kind of response my kids would give: Wasn't he with the Beatles or the Stones or some other *old* band like that? Hell, my son considers the Offspring to be over the hill and Linkin Park as starting to lose it... About 7 years ago, I interviewed someone who listed Phish as one of his interests. I asked him What is Phish? and then spelled it for him. The expressions that swept across his face in one second ran from frank astonishment, to disbelief, to pity, to a carefully-composed poker face as he answered, A musical group that I like. Food for thought: when I was a kid in the 70s, my father would play his big band records and my brothers and I would roll our eyes and leave the house. Such lame, ancient music! At the time, those recordings were 30-35 years old... Um... For a kid today, the Stones, the Beatles, Pink Floyd, Frank Zappa, Yes, and Bowie are far more ancient. Not just in years, but the years do add up... ..'scuse me, I think I hear a bottle of Metamucil calling... on 10/28/03 4:59 AM, Richard Foote at [EMAIL PROTECTED] wrote: Only two questions are required to ensure you get an appropriate person for the job (any job): 1) What do you think of David Bowie, is he brilliant or what ? and providing they answer the above question positively 2) Are you any good ? Works every time ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 7:44 PM I ask things like tell me the thing you've done that you are most proud of and tell me your nightmare situation and how did you recover from it Ans: My worst nightmare, my date pick her nose infront of me, I call cab infront of her. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Conner, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dba interview questions
Give whisky, scotch and beer to Mladen and he'll be good -Original Message- Mladen Gogala Sent: 28 octobre, 2003 08:59 To: Multiple recipients of list ORACLE-L On 10/28/2003 06:59:24 AM, Richard Foote wrote: 1) What do you think of David Bowie, is he brilliant or what ? Or what. and providing they answer the above question positively 2) Are you any good ? I'm ad to the one. Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: anyone have opinions on the future of the 'grid'?
I know, I know, it's a bit of a stretch, but I think the idea's the same -- subsets of work divided between multiple machines. It's just that distcc is implemented at a much higher level than what's probably considered grid. The benefit is that it's much easier to implement. The downside is that it's usefulness is much more limited. But in this case, I think it works well, at least according to my co-workers who use it. Perhaps grid-wise Matt would care to comment? Gridfullessnessly yours, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 6:39 PM To: Multiple recipients of list ORACLE-L Subject: RE: anyone have opinions on the future of the 'grid'? Rich, That really isn't 'grid', but I think you know that. GC essentially makes a network look like one great big box, with the cross platform functionality included. In the immortal words of Scott McNeally: The network is the computer. :) Don't know if he said if first. John Brunner was likely the first to get the concept in print. Jared On Mon, 2003-10-27 at 10:09, Jesse, Rich wrote: Some of us here at work have been using grid computing to compile programs... http://www.gentoo.org/doc/en/distcc.xml Standard disclaimers apply (e.g. all machines must have same versions of copmiler, same architecture, etc. to avoid problems). Rich -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: anyone have opinions on the future of the 'grid'?
S Does it mean that a network with 100 computers of 1 cpu each is almost equivalent to 100 cpu giant computer ?? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:39 PM Rich, That really isn't 'grid', but I think you know that. GC essentially makes a network look like one great big box, with the cross platform functionality included. In the immortal words of Scott McNeally: The network is the computer. :) Don't know if he said if first. John Brunner was likely the first to get the concept in print. Jared On Mon, 2003-10-27 at 10:09, Jesse, Rich wrote: Some of us here at work have been using grid computing to compile programs... http://www.gentoo.org/doc/en/distcc.xml Standard disclaimers apply (e.g. all machines must have same versions of copmiler, same architecture, etc. to avoid problems). Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: anyone have opinions on the future of the 'grid'? Dick, Jared If you look at the big picture, 20 years ago the idea of a PC with the throughput of a mainframe was laughable. Not so laughable today. Large systems use many of the same components as PCs. Whether this means the grid is more than hype remains to be seen. But I suspect Larry would prefer you spent your money on Oracle licensing instead of hardware. And this may be an area where Oracle can keep ahead of the open-source folks. Dennis Williams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: anyone have opinions on the future of the 'grid'?
Title: RE: anyone have opinions on the future of the 'grid'? Oracle will probably think so with their per CPU pricing. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: AK [SMTP:[EMAIL PROTECTED] S Does it mean that a network with 100 computers of 1 cpu each is almost equivalent to 100 cpu giant computer ?? -ak
Re: performance issue on select count(*)
The symptom suggests caching is a big factor here - most likely block-buffers. Contrary to ?current? popular beliefs, BCHR is still a very relevant performance indicator - either being very high, or being too low - both of which gives a good indication of something that needs to be looked at. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: anyone have opinions on the future of the 'grid'?
I don't know... It's definitely an interesting concept though, isn't it? Check this out: http://www.informationweek.com/story/showArticle.jhtml?articleID=1560024 2 -Original Message- Sent: Tuesday, October 28, 2003 10:34 AM To: Multiple recipients of list ORACLE-L S Does it mean that a network with 100 computers of 1 cpu each is almost equivalent to 100 cpu giant computer ?? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:39 PM Rich, That really isn't 'grid', but I think you know that. GC essentially makes a network look like one great big box, with the cross platform functionality included. In the immortal words of Scott McNeally: The network is the computer. :) Don't know if he said if first. John Brunner was likely the first to get the concept in print. Jared On Mon, 2003-10-27 at 10:09, Jesse, Rich wrote: Some of us here at work have been using grid computing to compile programs... http://www.gentoo.org/doc/en/distcc.xml Standard disclaimers apply (e.g. all machines must have same versions of copmiler, same architecture, etc. to avoid problems). Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: anyone have opinions on the future of the 'grid'? Dick, Jared If you look at the big picture, 20 years ago the idea of a PC with the throughput of a mainframe was laughable. Not so laughable today. Large systems use many of the same components as PCs. Whether this means the grid is more than hype remains to be seen. But I suspect Larry would prefer you spent your money on Oracle licensing instead of hardware. And this may be an area where Oracle can keep ahead of the open-source folks. Dennis Williams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cunningham, Gerald INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Execution Plan is good but HIGH CPU
Jared, The output is from TKPROF. Muqthar -Original Message- Sent: Tuesday, October 28, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Have you run SQLTRACE on this query? The detail in the trace file will show where the cpu is being consumed. There is insufficient data in the summary to reach any conclusion. Jared On Mon, 2003-10-27 at 21:24, Muqthar Ahmed wrote: Hi, Execution plan looks good but the query is consuming 800 seconds CPU timewhy? SELECT sampleavail, sample_cost_amount, sample_sale_amount, discount_room, discount_case, discount_half_case, allow_cut, retail_cut_amount, cost_cut_amount, gp_room from tbljnwpbookvendortype t1, tbljnwpbookvendor t2 where t1.jnwpbvid = t2.jnwpbvid and t2.prsuid = :b3 and t2.wpbkid = :b2 and t1.wpptid = :b1 call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 5618 0.63 0.58 0 0 0 0 Fetch 5617800.05 782.07 01409683 04187 --- -- -- -- -- -- -- total11236800.68 782.66 01409683 04187 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 109 (DDTBL) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDOR' 0 INDEX (UNIQUE SCAN) OF 'TBLJNWPBOOKVENDOR_UQ1' (UNIQUE) 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDORTYPE' 0 INDEX (RANGE SCAN) OF 'TBLJNWPBOOKVENDORTYPE_IDX1' (NON-UNIQUE) Muqthar Ahmed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Execution Plan is good but HIGH CPU
just in case, if you have specified explain= on the command line to generate tkprof summary, remove it and just do tkprof filename ... which version of oracle? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, October 28, 2003 11:04 AM To: Multiple recipients of list ORACLE-L Jared, The output is from TKPROF. Muqthar -Original Message- Sent: Tuesday, October 28, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Have you run SQLTRACE on this query? The detail in the trace file will show where the cpu is being consumed. There is insufficient data in the summary to reach any conclusion. Jared On Mon, 2003-10-27 at 21:24, Muqthar Ahmed wrote: Hi, Execution plan looks good but the query is consuming 800 seconds CPU timewhy? SELECT sampleavail, sample_cost_amount, sample_sale_amount, discount_room, discount_case, discount_half_case, allow_cut, retail_cut_amount, cost_cut_amount, gp_room from tbljnwpbookvendortype t1, tbljnwpbookvendor t2 where t1.jnwpbvid = t2.jnwpbvid and t2.prsuid = :b3 and t2.wpbkid = :b2 and t1.wpptid = :b1 call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 5618 0.63 0.58 0 0 0 0 Fetch 5617800.05 782.07 01409683 04187 --- -- -- -- -- -- -- total11236800.68 782.66 01409683 04187 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 109 (DDTBL) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDOR' 0 INDEX (UNIQUE SCAN) OF 'TBLJNWPBOOKVENDOR_UQ1' (UNIQUE) 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDORTYPE' 0 INDEX (RANGE SCAN) OF 'TBLJNWPBOOKVENDORTYPE_IDX1' (NON-UNIQUE) Muqthar Ahmed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** 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
Re: RE: Execution Plan is good but HIGH CPU
check out asktom. its up for today. if you use an 8i tkprof on a 9i trace file, you get bad cpu results. are you doing that? From: Muqthar Ahmed [EMAIL PROTECTED] Date: 2003/10/28 Tue AM 11:04:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Execution Plan is good but HIGH CPU Jared, The output is from TKPROF. Muqthar -Original Message- Sent: Tuesday, October 28, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Have you run SQLTRACE on this query? The detail in the trace file will show where the cpu is being consumed. There is insufficient data in the summary to reach any conclusion. Jared On Mon, 2003-10-27 at 21:24, Muqthar Ahmed wrote: Hi, Execution plan looks good but the query is consuming 800 seconds CPU timewhy? SELECT sampleavail, sample_cost_amount, sample_sale_amount, discount_room, discount_case, discount_half_case, allow_cut, retail_cut_amount, cost_cut_amount, gp_room from tbljnwpbookvendortype t1, tbljnwpbookvendor t2 where t1.jnwpbvid = t2.jnwpbvid and t2.prsuid = :b3 and t2.wpbkid = :b2 and t1.wpptid = :b1 call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 5618 0.63 0.58 0 0 0 0 Fetch 5617800.05 782.07 01409683 04187 --- -- -- -- -- -- -- total11236800.68 782.66 01409683 04187 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 109 (DDTBL) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDOR' 0 INDEX (UNIQUE SCAN) OF 'TBLJNWPBOOKVENDOR_UQ1' (UNIQUE) 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDORTYPE' 0 INDEX (RANGE SCAN) OF 'TBLJNWPBOOKVENDORTYPE_IDX1' (NON-UNIQUE) Muqthar Ahmed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT
RE: Clone db 9.2 on AIX 5L
SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26 '/iu33/u02/oradata/spap/indexes03.dbf' 27 CHARACTER SET WE8ISO8859P1 28 ; CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-03114: not connected to ORACLE SQL -- RECOVER DATABASE SQL -- ALTER DATABASE OPEN; SQL -- ALTER TABLESPACE TEMP ADD TEMPFILE '/iu33/u02/oradata/spap/temp01.dbf' SIZE 3119M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE ; SQL The confusing thing is that I have instances already running on the machine. So I know the install is ok. I can start/shutdown the existing instances no problem. But when I try to clone and startup --- I get the results from above. -Original Message- Sent: Monday, October 27, 2003 5:24 PM To: [EMAIL PROTECTED] When are you getting the error? During startup? Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 03:04 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject RE: Clone db 9.2 on AIX 5L Sorry, AIX 5L to AIX 5L cold backup copies create backup controlfile to trace -- edited for the new file locations keeping the SID the same created init.ora from spfile startup nomount pfile=init.ora getting ora-3113 I have a TAR opened and figured I would check out here as well. -Original Message- [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:44 PM To: Multiple recipients of list ORACLE-L How about some more details? Are you cloning to a similar platform? Are you using a cold backup with controlfile recreation? RMAN backup or restore? RMAN duplicate? ... Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 02:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Clone db 9.2 on AIX 5L Just checking to see if anyone has been able to clone a 9.2 DB from one machine to another. I have never had a problem doing this prior to 9.2, and am just wondering if I have overlooked something peculiar to 9i. Thanks in adavance John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for
Unaccounted-for elapsed time
I've been a believer in response-time-based performance optimization for some time now, and reading Cary Millsap's book has reinforced my belief. That said, I'm looking for some help in identifying what I _think_ is unaccounted-for elapsed time. Both tkprof and trcanlzr show (sorry for the wrapped lines): callcount cpu elapsed diskquery current rowsmisses --- - - - - Parse 90 0.02 0.0100 00 1 Execute45 0.09 0.090 24 00 1 Fetch 45 4.11 28.20013210 0 45 0 --- - - - - total 180 4.22 28.30013234 0 45 2 And the waits Event Times Count Max. TotalBlocks waited on Waited Zero Time WaitWaited Accessed - - - - - - SQL*Net message from client (idle)... 225 0 0.01 0.56 SQL*Net message to client (idle). 225 0 0.00 0.00 - - - - - - total 450 0 0.01 0.57 0 non-idle waits... 0 0 0.00 0.00 0 idle waits... 450 0 0.01 0.57 Now, the SQL statement that produced this output makes a couple of function calls...does the elapsed time here include the waits that the function calls might cause? I have 24 seconds of elapsed that aren't accounted for. TIA, = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Refresh option for Materialized view , want to use it during refresh - for
Arup, I really appreciate your answer in great details. I got on prebuilt table work. Thanks a lot for your help. Here is another question: Do you see any advantage to use materialized view on prebuilt table for my data loading over just simple renaming tables as steps below: 1. create table t that is always accessed by applications 2. create table t1 that is a temp table for loading 3. load data into table t1 4. rename table t to table t2 5. rename table t1 to t 6. rename table t2 to t1 7. truncate table t1 for next day loading David From: Arup Nanda [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Refresh option for Materialized view , want to use it during refresh - for Date: Fri, 24 Oct 2003 18:04:33 -0800 David, Answers to your questions: (1) Without knowing your exact needs, I wil offer a few different scenarios. I am assuming that you are doing a complete refresh every time. The following pertain to that. Say, your name of the MV is MV1. Here are the steps the first time. 1. Create table MV1 2. Create MV MV1 on that table. When you want to refresh complete: 1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink (with NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp. 2. Drop MV MV1. This drops the MV but doesn't drop the table. 3. Drop table MV1. 4. Rename table MV1_TEMP to MV1. 5. Recreate MV MV1. 6. Allow users to proceed as usual. Note the time consumed between Steps 2 and 6 are in the order of a few seconds. And it's the only time the users will not have access to the MV, as opposed to a full refresh using dbms_mview.refresh approach., which will lock the MV for the entire duration and generate tons of redo and rollback. Even if you do a incremental refresh, this is still a better approach. In that case, you don't drop the table during the refresh. (2) Yes, the option is available in 8i, at least in 8.1.7. Make sure the syntax is correct. create materialized view MV1 on prebuilt table refresh fast as select ... from In the article I mentioned, you can find the complete syntax. www.proligence.com/downloads.html is the site. It also dscribes a step by step solution to the issue and compares the common solution with this new one. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The segment that used to be a table simply becomes an MV. (2) When you drop the MV, the MV is gone, but the table remains instact. (3) The table can be create by any means - export/import, SQL*Loader, INSERT APPEND, etc. (4) IT places less strain on the system comapred to the MV refresh option, simply because the MV refresh truncates the segment and then builds it. I presented a paper to the same effect at IOUG Live 2003. You can download a modified version of the same from my website www.proligence.com/downlaods.html, titled Painless Master Table Alter from the Presentations Section. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 21, 2003 3:59 AM refresh Hi Gurus, I have a materialized view, which is based on Oracle Apps tables and on remote database. The view refresh takes around ½ hour, during this time period I cannot see any records in the materialized view and therefore my application faces errors. The following is the view definition
Re: performance issue on select count(*)
So, what exactly is indicated by a high or low hit rate? What, exactly, is high and what do you consider low? What HR are you talking about? This would be the infamous BCHR: select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0))) / ( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) ) * 100 from v$sysstat What exactly should the number returned by this query tell me? On 10/28/2003 10:59:25 AM, Binley Lim wrote: The symptom suggests caching is a big factor here - most likely block-buffers. Contrary to ?current? popular beliefs, BCHR is still a very relevant performance indicator - either being very high, or being too low - both of which gives a good indication of something that needs to be looked at. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Using an index defined on members of a user defined type
Hi,
I am looking at Oracle schema for IBM's MQ Workflow and I have a table that
has an index on members of a user defined type. This index is not getting
used when it could help a delete statement. Using the 10053 trace I got this
snippet in the trace:
SINGLE TABLE ACCESS PATH
No statistics type defined for function TIMESTAMP_WF
No default selectivity defined for function TIMESTAMP_WF
Does anyone have any experience in setting up functions so that the database
will use indexes against them?
In this case the table looks like this:
CREATE TABLE AUDIT_TRAIL
(
CREATED TIMESTAMP_WFNOT NULL
, EVENT NUMBER(10,0)NOT NULL
, TEMPL_VALID_FROMTIMESTAMP_WF
, ACTIVITY_TYPE NUMBER(10,0)
, ACTIVITY_STATE NUMBER(10,0)
, ACTIVITY_RC NUMBER(10,0)
, CONTAINER_CONTENT BLOB
, PROCESS_NAMEVARCHAR2(63)NOT NULL
, PROCESS_ID VARCHAR2(64)NOT NULL
, TOP_LVL_PROC_NAME VARCHAR2(63)NOT NULL
, TOP_LVL_PROC_ID VARCHAR2(64)NOT NULL
, PARENT_PROC_NAMEVARCHAR2(63)
, PARENT_PROC_ID VARCHAR2(64)
, PROC_TEMPL_NAME VARCHAR2(32)NOT NULL
, BLOCK_NAMES VARCHAR2(254)
, USER_NAME VARCHAR2(32)
, SECOND_USER_NAMEVARCHAR2(32)
, ACTIVITY_NAME VARCHAR2(32)
, SECOND_ACT_NAME VARCHAR2(32)
, COMMAND_PARAMETERS VARCHAR2(1024)
, ASSOCIATED_OBJECT VARCHAR2(64)
, OBJECT_DESCRIPTION VARCHAR2(254)
, PROGRAM_NAMEVARCHAR2(32)
, EXTERNAL_CONTEXTVARCHAR2(254)
)
TABLESPACE ADTTRAIL;
and the index is:
CREATE INDEX AT_CREATED_PROCID
ON AUDIT_TRAIL
(
CREATED.D, CREATED.S
, PROCESS_ID
) TABLESPACE ADTTRAIL;
and here is the type
CREATE OR REPLACE TYPE TIMESTAMP_WF AS OBJECT(
D DATE,
S NUMBER(6),
STATIC FUNCTION CONSTRUCT( str VARCHAR2 ) RETURN TIMESTAMP_WF,
MEMBER FUNCTION AS_STRING RETURN VARCHAR2,
MEMBER FUNCTION AS_DATE_STRING RETURN VARCHAR2,
MEMBER FUNCTION ADD_SECONDS( sec INTEGER ) RETURN TIMESTAMP_WF,
MEMBER FUNCTION SUB_TIMESTAMP_WF(other_Timestamp IN TIMESTAMP_WF) RETURN
INTEGER
,
ORDER MEMBER FUNCTION COMPARE(other_Timestamp IN TIMESTAMP_WF) RETURN
INTEGER
);
/
CREATE OR REPLACE TYPE BODY TIMESTAMP_WF AS
STATIC FUNCTION CONSTRUCT( str VARCHAR2 ) RETURN TIMESTAMP_WF IS
BEGIN
IF str IS NULL THEN
RETURN NULL;
END IF;
IF LENGTH( str ) = 26 THEN
RETURN TIMESTAMP_WF( TO_DATE( SUBSTR( str, 1,19 ),
'-mm-dd-hh24.mi.s
s' ),
TO_NUMBER( SUBSTR( str, 21, 6 ) ));
END IF;
IF LENGTH( str ) = 19 THEN
RETURN TIMESTAMP_WF( TO_DATE( SUBSTR( str, 1,19 ),
'-mm-dd-hh24.mi.s
s'), 0 );
END IF;
RETURN TIMESTAMP_WF( TO_DATE('ERROR'), 0);
END;
MEMBER FUNCTION AS_STRING RETURN VARCHAR2 IS
BEGIN
RETURN
TO_CHAR(D,'-mm-dd-hh24.mi.ss')||'.'||SUBSTR(To_Char(S,'099'),
3);
END;
MEMBER FUNCTION AS_DATE_STRING RETURN VARCHAR2 IS
BEGIN
RETURN TO_CHAR(D,'-mm-dd-hh24.mi.ss');
END;
MEMBER FUNCTION ADD_SECONDS( sec INTEGER ) RETURN TIMESTAMP_WF IS
BEGIN
RETURN TIMESTAMP_WF( D + sec/86400, S );
END;
MEMBER FUNCTION SUB_TIMESTAMP_WF(other_Timestamp IN TIMESTAMP_WF) RETURN
INTEG
ER IS
BEGIN
RETURN ((D - other_TimeStamp.D)*86400);
END;
ORDER MEMBER FUNCTION COMPARE(other_Timestamp IN TIMESTAMP_WF) RETURN
INTEGER
IS
BEGIN
IFD other_Timestamp.D THEN RETURN 1;
ELSIF D other_Timestamp.D THEN RETURN -1;
END IF;
RETURN S - other_Timestamp.S;
END;
END;
/
Regards
Pete
__
The information contained in this email is confidential and
intended only for the use of the individual or entity named
above. If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly
prohibited. Thomson Scientific will accept no responsibility
or liability in respect to this email other than to the addressee.
If you have received this communication in error, please
notify us immediately via email: [EMAIL PROTECTED]
__
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hitchman, Peter
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Clone db 9.2 on AIX 5L
This is how ours look like ... STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE XXX NORESETLOGS ARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 192 MAXLOGMEMBERS 3 MAXDATAFILES 1024 MAXINSTANCES 32 MAXLOGHISTORY 3403 LOGFILE .. maybe it is that pfile line causing trouble ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, October 28, 2003 11:49 AM To: Multiple recipients of list ORACLE-L SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26 '/iu33/u02/oradata/spap/indexes03.dbf' 27 CHARACTER SET WE8ISO8859P1 28 ; CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-03114: not connected to ORACLE SQL -- RECOVER DATABASE SQL -- ALTER DATABASE OPEN; SQL -- ALTER TABLESPACE TEMP ADD TEMPFILE '/iu33/u02/oradata/spap/temp01.dbf' SIZE 3119M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE ; SQL The confusing thing is that I have instances already running on the machine. So I know the install is ok. I can start/shutdown the existing instances no problem. But when I try to clone and startup --- I get the results from above. -Original Message- Sent: Monday, October 27, 2003 5:24 PM To: [EMAIL PROTECTED] When are you getting the error? During startup? Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 03:04 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject RE: Clone db 9.2 on AIX 5L Sorry, AIX 5L to AIX 5L cold backup copies create backup controlfile to trace -- edited for the new file locations keeping the SID the same created init.ora from spfile startup nomount pfile=init.ora getting ora-3113 I have a TAR opened and figured I would check out here as well. -Original Message- [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:44 PM To: Multiple recipients of list ORACLE-L How about some more details? Are you cloning to a similar platform? Are you using a cold backup with controlfile recreation? RMAN backup or restore? RMAN duplicate? ... Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 02:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Clone db 9.2 on AIX 5L Just checking to see if anyone has been able to clone a 9.2 DB from one machine to another. I have never had a problem doing this prior to 9.2, and am just wondering if I have overlooked something peculiar to 9i. Thanks in adavance John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also
RE: Clone db 9.2 on AIX 5L
John, Did you see any messages in the alert log? And what is in the cr_spap script? the startup nomount is failing immediately, so everything else is worthless. Review the initspap.ora file and make sure that all of the directories exist. Also, is the ORACLE_SID evironmental set prior to running sqlplus? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, October 28, 2003 11:49 AM To: Multiple recipients of list ORACLE-L SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26 '/iu33/u02/oradata/spap/indexes03.dbf' 27 CHARACTER SET WE8ISO8859P1 28 ; CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-03114: not connected to ORACLE SQL -- RECOVER DATABASE SQL -- ALTER DATABASE OPEN; SQL -- ALTER TABLESPACE TEMP ADD TEMPFILE '/iu33/u02/oradata/spap/temp01.dbf' SIZE 3119M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE ; SQL The confusing thing is that I have instances already running on the machine. So I know the install is ok. I can start/shutdown the existing instances no problem. But when I try to clone and startup --- I get the results from above. -Original Message- Sent: Monday, October 27, 2003 5:24 PM To: [EMAIL PROTECTED] When are you getting the error? During startup? Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 03:04 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject RE: Clone db 9.2 on AIX 5L Sorry, AIX 5L to AIX 5L cold backup copies create backup controlfile to trace -- edited for the new file locations keeping the SID the same created init.ora from spfile startup nomount pfile=init.ora getting ora-3113 I have a TAR opened and figured I would check out here as well. -Original Message- [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:44 PM To: Multiple recipients of list ORACLE-L How about some more details? Are you cloning to a similar platform? Are you using a cold backup with controlfile recreation? RMAN backup or restore? RMAN duplicate? ... Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 02:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Clone db 9.2 on AIX 5L Just checking to see if anyone has been able to clone a 9.2 DB from one machine to another. I have never had a problem doing this prior to 9.2, and am just wondering if I have overlooked something peculiar to 9i. Thanks in adavance John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services
Re: Clone db 9.2 on AIX 5L
Check to your alert log for error messages. Maybe you haven't set your kernel parameters accordingly your new instance can't allocate enough resources it needs. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 6:49 PM SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26 '/iu33/u02/oradata/spap/indexes03.dbf' 27 CHARACTER SET WE8ISO8859P1 28 ; CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-03114: not connected to ORACLE SQL -- RECOVER DATABASE SQL -- ALTER DATABASE OPEN; SQL -- ALTER TABLESPACE TEMP ADD TEMPFILE '/iu33/u02/oradata/spap/temp01.dbf' SIZE 3119M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE ; SQL The confusing thing is that I have instances already running on the machine. So I know the install is ok. I can start/shutdown the existing instances no problem. But when I try to clone and startup --- I get the results from above. -Original Message- Sent: Monday, October 27, 2003 5:24 PM To: [EMAIL PROTECTED] When are you getting the error? During startup? Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 03:04 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject RE: Clone db 9.2 on AIX 5L Sorry, AIX 5L to AIX 5L cold backup copies create backup controlfile to trace -- edited for the new file locations keeping the SID the same created init.ora from spfile startup nomount pfile=init.ora getting ora-3113 I have a TAR opened and figured I would check out here as well. -Original Message- [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:44 PM To: Multiple recipients of list ORACLE-L How about some more details? Are you cloning to a similar platform? Are you using a cold backup with controlfile recreation? RMAN backup or restore? RMAN duplicate? ... Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 02:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Clone db 9.2 on AIX 5L Just checking to see if anyone has been able to clone a 9.2 DB from one machine to another. I have never had a problem doing this prior to 9.2, and am just wondering if I have overlooked something peculiar to 9i. Thanks in adavance John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com
Installing pro*cobol
Hi, We always copy the Oracle binaries rather than installing Oracle. Our standard Oracle copy does not contain Pro*cobol. After reading the doc I can summarize the installation of pro-cobol this way. (Cobol is already installed.) - Install pro*cobol 817 - Apply path 8172 for pro*cobol only. It seems I do not have to relink anything. Did I missed something ? The target server (aix433) has 28 instances with some important ones (user testing). Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: performance issue on select count(*)
yes bchr is only useful at extremes, but its based on interpretation. if you have a very high BCHR, you probably have alot of very bad sql. if you have a very low one AND are in a type of application where you should(namely OLTP) you may want to consider increasing your buffer cache. mladen is right. there is no 'exact' very high and very low. you have to interpret it. that is about it. Anyone who uses it for anymore than that is wrong. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 12:09:34 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: performance issue on select count(*) So, what exactly is indicated by a high or low hit rate? What, exactly, is high and what do you consider low? What HR are you talking about? This would be the infamous BCHR: select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0))) / ( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) ) * 100 from v$sysstat What exactly should the number returned by this query tell me? On 10/28/2003 10:59:25 AM, Binley Lim wrote: The symptom suggests caching is a big factor here - most likely block-buffers. Contrary to ?current? popular beliefs, BCHR is still a very relevant performance indicator - either being very high, or being too low - both of which gives a good indication of something that needs to be looked at. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Execution Plan is good but HIGH CPU
Hi, The developer was executing the SQL statement in the loop in procedure. After re-writing the code, now the performance is good. Thanks Muqthar Ahmed -Original Message- Sent: Tuesday, October 28, 2003 8:09 AM To: Multiple recipients of list ORACLE-L It's 800 sec for 5617 exec/fetch calls. It looks like it's a sql inside a cursor loop or stored proc that gets called from some app. If you are unhappy, try to get rid of the cursor logic and get everything done in one sql call. Waleed -Original Message- Sent: Tuesday, October 28, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Hi, Execution plan looks good but the query is consuming 800 seconds CPU timewhy? SELECT sampleavail, sample_cost_amount, sample_sale_amount, discount_room, discount_case, discount_half_case, allow_cut, retail_cut_amount, cost_cut_amount, gp_room from tbljnwpbookvendortype t1, tbljnwpbookvendor t2 where t1.jnwpbvid = t2.jnwpbvid and t2.prsuid = :b3 and t2.wpbkid = :b2 and t1.wpptid = :b1 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 5618 0.63 0.58 0 0 0 0 Fetch 5617800.05 782.07 01409683 0 4187 --- -- -- -- -- -- -- total11236800.68 782.66 01409683 0 4187 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 109 (DDTBL) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDOR' 0 INDEX (UNIQUE SCAN) OF 'TBLJNWPBOOKVENDOR_UQ1' (UNIQUE) 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDORTYPE' 0 INDEX (RANGE SCAN) OF 'TBLJNWPBOOKVENDORTYPE_IDX1' (NON-UNIQUE) Muqthar Ahmed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
rman backup
We take daily rman backup each nite at 10pm . Which
means in worst case we risk one day of work. Now suppose something wrong
goes before 10 pm . Then Can I recover my database till time using previous day
rman backup and currently available archived logs.
Or in nutshell is it possible to run { restore
databse; recover database } from rman and then apply remaing archived logs from
a separated disk ( not a rman backup ).
Thanks,
-ak
RE: dba interview questions
Oddly, I was thinking the same thing about Monty Python and the Holy Grail. And then I thought... NAAH. Grail is the only common denominator that I know of that all technical geeks have in common... but now that its some obscene number of years old, I've been on a quest to find another one... a non-Star Trek one, as Star Trek has so many adherents to various generations of it... Anyone? Bambi. -Original Message- Sent: Tuesday, October 28, 2003 8:09 AM To: Multiple recipients of list ORACLE-L You're likely to get the kind of response my kids would give: Wasn't he with the Beatles or the Stones or some other *old* band like that? Hell, my son considers the Offspring to be over the hill and Linkin Park as starting to lose it... About 7 years ago, I interviewed someone who listed Phish as one of his interests. I asked him What is Phish? and then spelled it for him. The expressions that swept across his face in one second ran from frank astonishment, to disbelief, to pity, to a carefully-composed poker face as he answered, A musical group that I like. Food for thought: when I was a kid in the 70s, my father would play his big band records and my brothers and I would roll our eyes and leave the house. Such lame, ancient music! At the time, those recordings were 30-35 years old... Um... For a kid today, the Stones, the Beatles, Pink Floyd, Frank Zappa, Yes, and Bowie are far more ancient. Not just in years, but the years do add up... ..'scuse me, I think I hear a bottle of Metamucil calling... on 10/28/03 4:59 AM, Richard Foote at [EMAIL PROTECTED] wrote: Only two questions are required to ensure you get an appropriate person for the job (any job): 1) What do you think of David Bowie, is he brilliant or what ? and providing they answer the above question positively 2) Are you any good ? Works every time ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 7:44 PM I ask things like tell me the thing you've done that you are most proud of and tell me your nightmare situation and how did you recover from it Ans: My worst nightmare, my date pick her nose infront of me, I call cab infront of her. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Clone db 9.2 on AIX 5L
Hi, You DO NOT have to CLONE the databases if you are upgrading the OS from 4.3.3 to 5L. You mentioned that the instances are already running. IBM has break the technology at AIX 5.1.0.0, meaning the AIX 4.3.3 and AIX 5.1.0.0 have different architecture. So if you are upgrading IBM AIX 4.3.3 to AIX 5.1.0.0, you also have to upgrade Oracle at the same time. Oracle 9.2 CDs are separate for IBM AIX 4.3.3 and IBM AIX 5.1.0.0. If you are in this situation, all you have to is shutdown all databases, install Oracle 9.2 with 5L CDs in new ORACEL HOME and setup your environment with new ORACLE HOME to start your existing databases. Muqthar Ahmed -Original Message- Sent: Tuesday, October 28, 2003 11:49 AM To: Multiple recipients of list ORACLE-L SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26 '/iu33/u02/oradata/spap/indexes03.dbf' 27 CHARACTER SET WE8ISO8859P1 28 ; CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-03114: not connected to ORACLE SQL -- RECOVER DATABASE SQL -- ALTER DATABASE OPEN; SQL -- ALTER TABLESPACE TEMP ADD TEMPFILE '/iu33/u02/oradata/spap/temp01.dbf' SIZE 3119M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE ; SQL The confusing thing is that I have instances already running on the machine. So I know the install is ok. I can start/shutdown the existing instances no problem. But when I try to clone and startup --- I get the results from above. -Original Message- Sent: Monday, October 27, 2003 5:24 PM To: [EMAIL PROTECTED] When are you getting the error? During startup? Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 03:04 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject RE: Clone db 9.2 on AIX 5L Sorry, AIX 5L to AIX 5L cold backup copies create backup controlfile to trace -- edited for the new file locations keeping the SID the same created init.ora from spfile startup nomount pfile=init.ora getting ora-3113 I have a TAR opened and figured I would check out here as well. -Original Message- [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:44 PM To: Multiple recipients of list ORACLE-L How about some more details? Are you cloning to a similar platform? Are you using a cold backup with controlfile recreation? RMAN backup or restore? RMAN duplicate? ... Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 02:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Clone db 9.2 on AIX 5L Just checking to see if anyone has been able to clone a 9.2 DB from one machine to another. I have never had a problem doing this prior to 9.2, and am just wondering if I have overlooked something peculiar to 9i. Thanks in adavance John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
was: AIX 9.2 refresh
Title: was: AIX 9.2 refresh Please ignore my last replay about control file ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! **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.**4
wither Designer documentation?
Where is the Designer 9i documentation? Not on OTN (http://otn.oracle.com/documentation/designer.html), not in download-east (http://download-east.oracle.com/docs/cd/A91773_01/ids902dl/index.htm ), not in tahiti... (http://tahiti.oracle.com ) Oracle Designer Generation seems to be the only book (Oracle Press). Oracle Designer Handbook by Pete Koletzke was published in 1998. Patrice -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dba interview questions
Bellow, Bambi scribbled on the wall in glitter crayon: Oddly, I was thinking the same thing about Monty Python and the Holy Grail. And then I thought... NAAH. Grail is the only common denominator that I know of that all technical geeks have in common... but now that its some obscene number of years old, I've been on a quest to find another one... a non-Star Trek one, as Star Trek has so many adherents to various generations of it... Anyone? dust puppy and the one true ping? -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] The superior man is distressed by the limitation of his ability; he is not distressed by the fact that men do not recognize the ability he has. - Confucius -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: wither Designer documentation?
I have had the same problem. I have contacted Oracle for a user guide with no response as of yet. I am literally printing off the help pages within Designer to address the need. It sucks 10/28/2003 10:14 AM PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Where is the Designer 9i documentation? Not on OTN (http://otn.oracle.com/documentation/designer.html), not in download-east (http://download-east.oracle.com/docs/cd/A91773_01/ids902dl/index.htm ), not in tahiti... (http://tahiti.oracle.com ) Oracle Designer Generation seems to be the only book (Oracle Press). Oracle Designer Handbook by Pete Koletzke was published in 1998. Patrice -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). American Express made the following annotations on 10/28/2003 11:27:28 AM -- ** This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you. ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Clone db 9.2 on AIX 5L
Hi John... Actually I do not use AIX, but I used to work with 3x and first 4x version. I do not know if your AIX version still manages AIX modules, but if it does, then you need to load the modules pointed in your root.sh (or rootpre.sh) file, I do not remember very well. Of course, you can run the command manually with the root account. I think this error is because you need to have your aix modules loaded and of course, this affects your kernel parameters, i.e. your new instance cannot allocate enough resources as is needed. HTH JL --- John Blake [EMAIL PROTECTED] wrote: SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26 '/iu33/u02/oradata/spap/indexes03.dbf' 27 CHARACTER SET WE8ISO8859P1 28 ; CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-03114: not connected to ORACLE SQL -- RECOVER DATABASE SQL -- ALTER DATABASE OPEN; SQL -- ALTER TABLESPACE TEMP ADD TEMPFILE '/iu33/u02/oradata/spap/temp01.dbf' SIZE 3119M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE ; SQL The confusing thing is that I have instances already running on the machine. So I know the install is ok. I can start/shutdown the existing instances no problem. But when I try to clone and startup --- I get the results from above. -Original Message- Sent: Monday, October 27, 2003 5:24 PM To: [EMAIL PROTECTED] When are you getting the error? During startup? Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 03:04 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject RE: Clone db 9.2 on AIX 5L Sorry, AIX 5L to AIX 5L cold backup copies create backup controlfile to trace -- edited for the new file locations keeping the SID the same created init.ora from spfile startup nomount pfile=init.ora getting ora-3113 I have a TAR opened and figured I would check out here as well. -Original Message- [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:44 PM To: Multiple recipients of list ORACLE-L How about some more details? Are you cloning to a similar platform? Are you using a cold backup with controlfile recreation? RMAN backup or restore? RMAN duplicate? ... Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 02:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Clone db 9.2 on AIX 5L Just checking to see if anyone has been able to clone a 9.2 DB from one machine to another. I have never had a problem doing this prior to 9.2, and am just wondering if I have overlooked something peculiar to 9i. Thanks in adavance John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
Re: dba interview questions
How about Life of Brian? That's even better then the Holy Grail. On 10/28/2003 01:19:25 PM, Thater, William wrote: Bellow, Bambi scribbled on the wall in glitter crayon: Oddly, I was thinking the same thing about Monty Python and the Holy Grail. And then I thought... NAAH. Grail is the only common denominator that I know of that all technical geeks have in common... but now that its some obscene number of years old, I've been on a quest to find another one... a non-Star Trek one, as Star Trek has so many adherents to various generations of it... Anyone? dust puppy and the one true ping? -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] The superior man is distressed by the limitation of his ability; he is not distressed by the fact that men do not recognize the ability he has. - Confucius -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: rman backup
AK - Unless you specify otherwise, RMAN will automatically apply archive
logs to bring the database up to the time of failure (your recover database
statement). This is why it is good to run disaster recovery tests on a
regular basis, to ensure everything is ready, and you can try different
recovery times. You will see that RMAN will not complete its recovery if the
archived logs are not available.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, October 28, 2003 11:44 AM
To: Multiple recipients of list ORACLE-L
We take daily rman backup each nite at 10pm . Which means in worst case we
risk one day of work. Now suppose something wrong goes before 10 pm . Then
Can I recover my database till time using previous day rman backup and
currently available archived logs.
Or in nutshell is it possible to run { restore databse; recover database }
from rman and then apply remaing archived logs from a separated disk ( not a
rman backup ).
Thanks,
-ak
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: rman backup
You can restore database and open it to the mount phase using RMAN.
Then you can get into sqlplus and type something like:
alter system set log_archive_dest_1=/directory/where/log/archives/reside
set autorecovery on
recover database auto until cancel
On 10/28/2003 12:44:24 PM, AK wrote:
We take daily rman backup each nite at 10pm . Which means in worst case we risk one
day of work. Now suppose something wrong goes before 10 pm . Then Can I recover my
database till time using previous day rman backup and currently available archived
logs.
Or in nutshell is it possible to run { restore databse; recover database } from rman
and then apply remaing archived logs from a separated disk ( not a rman backup ).
Thanks,
-ak
Mladen Gogala
Oracle DBA
Note:
This message is for the named person's use only. It may contain confidential,
proprietary or legally privileged information. No confidentiality or privilege is
waived or lost by any mistransmission. If you receive this message in error, please
immediately delete it and all copies of it from your system, destroy any hard copies
of it and notify the sender. You must not, directly or indirectly, use, disclose,
distribute, print, or copy any part of this message if you are not the intended
recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to
monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where
the message states otherwise and the sender is authorized to state them to be the
views of any such entity.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Clone db 9.2 on AIX 5L
Usually when I've seen that it's because of an invalid or missing password file. Anything in the alert log to tell you more? Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Blake Sent: Wednesday, October 29, 2003 3:49 AM To: Multiple recipients of list ORACLE-L SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26 '/iu33/u02/oradata/spap/indexes03.dbf' 27 CHARACTER SET WE8ISO8859P1 28 ; CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-03114: not connected to ORACLE SQL -- RECOVER DATABASE SQL -- ALTER DATABASE OPEN; SQL -- ALTER TABLESPACE TEMP ADD TEMPFILE '/iu33/u02/oradata/spap/temp01.dbf' SIZE 3119M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE ; SQL The confusing thing is that I have instances already running on the machine. So I know the install is ok. I can start/shutdown the existing instances no problem. But when I try to clone and startup --- I get the results from above. -Original Message- Sent: Monday, October 27, 2003 5:24 PM To: [EMAIL PROTECTED] When are you getting the error? During startup? Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 03:04 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject RE: Clone db 9.2 on AIX 5L Sorry, AIX 5L to AIX 5L cold backup copies create backup controlfile to trace -- edited for the new file locations keeping the SID the same created init.ora from spfile startup nomount pfile=init.ora getting ora-3113 I have a TAR opened and figured I would check out here as well. -Original Message- [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:44 PM To: Multiple recipients of list ORACLE-L How about some more details? Are you cloning to a similar platform? Are you using a cold backup with controlfile recreation? RMAN backup or restore? RMAN duplicate? ... Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 02:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Clone db 9.2 on AIX 5L Just checking to see if anyone has been able to clone a 9.2 DB from one machine to another. I have never had a problem doing this prior to 9.2, and am just wondering if I have overlooked something peculiar to 9i. Thanks in adavance John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego,
Re: rman backup
Thanks Dennis for Reply,
My confusion is , does RMAN sees only those archived logs which are backup
using rman or it can use current archived log as well stored in original
format at other disk ?
-ak
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 28, 2003 10:49 AM
AK - Unless you specify otherwise, RMAN will automatically apply archive
logs to bring the database up to the time of failure (your recover
database
statement). This is why it is good to run disaster recovery tests on a
regular basis, to ensure everything is ready, and you can try different
recovery times. You will see that RMAN will not complete its recovery if
the
archived logs are not available.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, October 28, 2003 11:44 AM
To: Multiple recipients of list ORACLE-L
We take daily rman backup each nite at 10pm . Which means in worst case we
risk one day of work. Now suppose something wrong goes before 10 pm .
Then
Can I recover my database till time using previous day rman backup and
currently available archived logs.
Or in nutshell is it possible to run { restore databse; recover database }
from rman and then apply remaing archived logs from a separated disk ( not
a
rman backup ).
Thanks,
-ak
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: AK
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Refresh option for Materialized view , want to use it during
This was my original suggestion. The only snag I can think of is if you have a bunch of dependencies like stored procedures and triggers. Even if somebody was selecting from the old table when you renamed it, the select would continue OK ... as long as you don't truncate it. You could even delete from the old table without hosing a running select ... as long as the rollback segment holds up. You could rename the tables, then truncate maybe an hour later. -Original Message- Arup, I really appreciate your answer in great details. I got on prebuilt table work. Thanks a lot for your help. Here is another question: Do you see any advantage to use materialized view on prebuilt table for my data loading over just simple renaming tables as steps below: 1. create table t that is always accessed by applications 2. create table t1 that is a temp table for loading 3. load data into table t1 4. rename table t to table t2 5. rename table t1 to t 6. rename table t2 to t1 7. truncate table t1 for next day loading David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Refresh option for Materialized view , want to use it during refresh - for
materialized views have the create statements in the database. you just have to refresh them. can you refersh a materialized view in parallel? if not than create can be faster... From: David Boyd [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 12:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Refresh option for Materialized view , want to use it during refresh - for Arup, I really appreciate your answer in great details. I got on prebuilt table work. Thanks a lot for your help. Here is another question: Do you see any advantage to use materialized view on prebuilt table for my data loading over just simple renaming tables as steps below: 1. create table t that is always accessed by applications 2. create table t1 that is a temp table for loading 3. load data into table t1 4. rename table t to table t2 5. rename table t1 to t 6. rename table t2 to t1 7. truncate table t1 for next day loading David From: Arup Nanda [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Refresh option for Materialized view , want to use it during refresh - for Date: Fri, 24 Oct 2003 18:04:33 -0800 David, Answers to your questions: (1) Without knowing your exact needs, I wil offer a few different scenarios. I am assuming that you are doing a complete refresh every time. The following pertain to that. Say, your name of the MV is MV1. Here are the steps the first time. 1. Create table MV1 2. Create MV MV1 on that table. When you want to refresh complete: 1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink (with NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp. 2. Drop MV MV1. This drops the MV but doesn't drop the table. 3. Drop table MV1. 4. Rename table MV1_TEMP to MV1. 5. Recreate MV MV1. 6. Allow users to proceed as usual. Note the time consumed between Steps 2 and 6 are in the order of a few seconds. And it's the only time the users will not have access to the MV, as opposed to a full refresh using dbms_mview.refresh approach., which will lock the MV for the entire duration and generate tons of redo and rollback. Even if you do a incremental refresh, this is still a better approach. In that case, you don't drop the table during the refresh. (2) Yes, the option is available in 8i, at least in 8.1.7. Make sure the syntax is correct. create materialized view MV1 on prebuilt table refresh fast as select ... from In the article I mentioned, you can find the complete syntax. www.proligence.com/downloads.html is the site. It also dscribes a step by step solution to the issue and compares the common solution with this new one. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The segment that used to be a table simply becomes an MV. (2) When you drop the MV, the MV is gone, but the table remains instact. (3) The table can be create by any means - export/import, SQL*Loader, INSERT APPEND, etc. (4) IT places less strain on the system comapred to the MV refresh option, simply because the MV refresh truncates the segment and then builds it. I presented a paper to the same effect at IOUG Live 2003. You can
RE: rman backup
As long as you have
the database in archivelog mode and have a level 0 backup as your starting point
then you can recover. Make sure that your level 0 includes the
controlfiles.
HTH,
Ruth
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of AKSent: Tuesday,
October 28, 2003 12:44 PMTo: Multiple recipients of list
ORACLE-LSubject: rman backup
We take daily rman backup each nite at 10pm .
Which means in worst case we risk one day of work. Now suppose something
wrong goes before 10 pm . Then Can I recover my database till time using
previous day rman backup and currently available archived logs.
Or in nutshell is it possible to run { restore
databse; recover database } from rman and then apply remaing archived logs
from a separated disk ( not a rman backup ).
Thanks,
-ak
ora-600 question
does toad or the oracle instance itself slip in rule hints? We got an ora-600 error off of a data dictionary read. i think it has to do with explain plan. ORA-00600: internal error code, arguments: [17182], [2325084336], [], [], [], [], [], [] Current SQL statement for this session: select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hiva l, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Noarchivelog == archivelog
Hi List, Could someone help me to figure out what is going on here? I am trying to change db from NOARCHIVELOG -- ARCHIVELOG. SQL archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination/u02/arch Oldest online log sequence 966 Current log sequence 968 SQL shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL startup mount ORACLE instance started. Total System Global Area 1409298592 bytes Fixed Size73888 bytes Variable Size 374722560 bytes Database Buffers 102400 bytes Redo Buffers 10502144 bytes Database mounted. SQL alter database archivelog; alter database archivelog * ERROR at line 1: ORA-01126: database must be mounted EXCLUSIVE and not open for this operation Thanks Sami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: wither Designer documentation?
none of the apps have documentation. not forsm, reports, or discoverer and noone is writing updated books on them since they dont sell enough copees. i dont understand oracle on this one. atleast they can write one and sell the damn thing... From: Tracy Rahmlow [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 01:29:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: wither Designer documentation? I have had the same problem. I have contacted Oracle for a user guide with no response as of yet. I am literally printing off the help pages within Designer to address the need. It sucks 10/28/2003 10:14 AM PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Where is the Designer 9i documentation? Not on OTN (http://otn.oracle.com/documentation/designer.html), not in download-east (http://download-east.oracle.com/docs/cd/A91773_01/ids902dl/index.htm ), not in tahiti... (http://tahiti.oracle.com ) Oracle Designer Generation seems to be the only book (Oracle Press). Oracle Designer Handbook by Pete Koletzke was published in 1998. Patrice -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). American Express made the following annotations on 10/28/2003 11:27:28 AM -- ** This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you. ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dba interview questions
Proud? landing a rock to fakie followed by a fakie 360 in near vertnot bad for a 41 yo Nightmare? Playing a particularly embarrasing gig with a manic dreppressive folk music ogre, 120 minutes of pure hell in front of an audience. I refused to play anymore gigs with him. Do I get the job...?? -Original Message- Sent: Tuesday, October 28, 2003 3:44 AM To: Multiple recipients of list ORACLE-L I ask things like tell me the thing you've done that you are most proud of and tell me your nightmare situation and how did you recover from it Ans: My worst nightmare, my date pick her nose infront of me, I call cab infront of her. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Clone db 9.2 on AIX 5L
Are you sure that init.ora has the same database name as the controlfile? Bambi. -Original Message- Blake Sent: Wednesday, October 29, 2003 3:49 AM To: Multiple recipients of list ORACLE-L SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26 '/iu33/u02/oradata/spap/indexes03.dbf' 27 CHARACTER SET WE8ISO8859P1 28 ; CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-03114: not connected to ORACLE SQL -- RECOVER DATABASE SQL -- ALTER DATABASE OPEN; SQL -- ALTER TABLESPACE TEMP ADD TEMPFILE '/iu33/u02/oradata/spap/temp01.dbf' SIZE 3119M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE ; SQL The confusing thing is that I have instances already running on the machine. So I know the install is ok. I can start/shutdown the existing instances no problem. But when I try to clone and startup --- I get the results from above. -Original Message- Sent: Monday, October 27, 2003 5:24 PM To: [EMAIL PROTECTED] When are you getting the error? During startup? Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 03:04 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject RE: Clone db 9.2 on AIX 5L Sorry, AIX 5L to AIX 5L cold backup copies create backup controlfile to trace -- edited for the new file locations keeping the SID the same created init.ora from spfile startup nomount pfile=init.ora getting ora-3113 I have a TAR opened and figured I would check out here as well. -Original Message- [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:44 PM To: Multiple recipients of list ORACLE-L How about some more details? Are you cloning to a similar platform? Are you using a cold backup with controlfile recreation? RMAN backup or restore? RMAN duplicate? ... Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 02:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Clone db 9.2 on AIX 5L Just checking to see if anyone has been able to clone a 9.2 DB from one machine to another. I have never had a problem doing this prior to 9.2, and am just wondering if I have overlooked something peculiar to 9i. Thanks in adavance John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED]
RE: dba interview questions
Ghostbusters? When someone asks if you're a god, you say 'YES'! ...and the flowers are still standing. Tell him about the Twinkie, Egon. Joe vs. The Volcano? Not a nice place you have here, Joe. I know he can get the job, but can he do the job? The Hunt for Red October? Be careful what you shoot at, Ryan. Most things in here don't react well to bullets. Next time, Jack, write a [gosh darn] memo. I said speak your mind, Jack, but geezus. One ping only. Come on, Big D, fly! Princess Bride? Have fun storming the castle! Did I make it clear that your job is at stake? Do you want me to send you back where you were -- unemployed in Greenland? Simpsons? Your manager says for you to shut up. And the weak and nerdy are admired for their computer programming ability. (OK, not a movie) Young Frankenstein? Throw the third switch! Not the THIRD switch! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: dba interview questions How about Life of Brian? That's even better then the Holy Grail. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Noarchivelog == archivelog
Startup nomount Alter system enable restricted session; Alter database mount exclusive; -Original Message- Sent: Tuesday, October 28, 2003 1:34 PM To: Multiple recipients of list ORACLE-L Hi List, Could someone help me to figure out what is going on here? I am trying to change db from NOARCHIVELOG -- ARCHIVELOG. SQL archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination/u02/arch Oldest online log sequence 966 Current log sequence 968 SQL shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL startup mount ORACLE instance started. Total System Global Area 1409298592 bytes Fixed Size73888 bytes Variable Size 374722560 bytes Database Buffers 102400 bytes Redo Buffers 10502144 bytes Database mounted. SQL alter database archivelog; alter database archivelog * ERROR at line 1: ORA-01126: database must be mounted EXCLUSIVE and not open for this operation Thanks Sami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Noarchivelog == archivelog
Sami Any possibility you are using RAC or Parallel Server? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, October 28, 2003 1:34 PM To: Multiple recipients of list ORACLE-L Hi List, Could someone help me to figure out what is going on here? I am trying to change db from NOARCHIVELOG -- ARCHIVELOG. SQL archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination/u02/arch Oldest online log sequence 966 Current log sequence 968 SQL shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL startup mount ORACLE instance started. Total System Global Area 1409298592 bytes Fixed Size73888 bytes Variable Size 374722560 bytes Database Buffers 102400 bytes Redo Buffers 10502144 bytes Database mounted. SQL alter database archivelog; alter database archivelog * ERROR at line 1: ORA-01126: database must be mounted EXCLUSIVE and not open for this operation Thanks Sami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Noarchivelog == archivelog
Hi The database must be mounted and in exclusive mode in order to turn archiving on. You need to do a: startup mount exclusive then: alter database archivelog then: alter database open Ben -Original Message- [EMAIL PROTECTED] Sent: October 28, 2003 2:34 PM To: Multiple recipients of list ORACLE-L Hi List, Could someone help me to figure out what is going on here? I am trying to change db from NOARCHIVELOG -- ARCHIVELOG. SQL archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination/u02/arch Oldest online log sequence 966 Current log sequence 968 SQL shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL startup mount ORACLE instance started. Total System Global Area 1409298592 bytes Fixed Size73888 bytes Variable Size 374722560 bytes Database Buffers 102400 bytes Redo Buffers 10502144 bytes Database mounted. SQL alter database archivelog; alter database archivelog * ERROR at line 1: ORA-01126: database must be mounted EXCLUSIVE and not open for this operation Thanks Sami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Noarchivelog == archivelog
On 10/28/2003 02:34:24 PM, [EMAIL PROTECTED] wrote: Hi List, Could someone help me to figure out what is going on here? I am trying to change db from NOARCHIVELOG -- ARCHIVELOG. Let me quote the mighty Oracle: Database must be mounted EXCLUSIVE and not open for this operation. Idis redibis nunquam in bello peribis. -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: rman backup
AK
When you have RMAN back up archived logs, IIRC, in a recovery RMAN first
restores those archived logs to the location that Oracle will expect them to
be, and I believe that is done as part of the RESTORE DATABASE command. In
my situation, I found no advantage from having RMAN store the archive logs,
so I have no experience there, just what I've read in the manual.
Only RMAN can perform the RESTORE DATABASE command, but once you
complete that command, you can complete the recovery using svrmgrl. From
everything I've seen, RMAN just issues the RECOVER DATABASE command to
svrmgrl or SQL*Plus.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, October 28, 2003 1:15 PM
To: Multiple recipients of list ORACLE-L
Thanks Dennis for Reply,
My confusion is , does RMAN sees only those archived logs which are backup
using rman or it can use current archived log as well stored in original
format at other disk ?
-ak
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 28, 2003 10:49 AM
AK - Unless you specify otherwise, RMAN will automatically apply archive
logs to bring the database up to the time of failure (your recover
database
statement). This is why it is good to run disaster recovery tests on a
regular basis, to ensure everything is ready, and you can try different
recovery times. You will see that RMAN will not complete its recovery if
the
archived logs are not available.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, October 28, 2003 11:44 AM
To: Multiple recipients of list ORACLE-L
We take daily rman backup each nite at 10pm . Which means in worst case we
risk one day of work. Now suppose something wrong goes before 10 pm .
Then
Can I recover my database till time using previous day rman backup and
currently available archived logs.
Or in nutshell is it possible to run { restore databse; recover database }
from rman and then apply remaing archived logs from a separated disk ( not
a
rman backup ).
Thanks,
-ak
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: AK
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: ora-600 question
TOAD puts a hint in. [EMAIL PROTECTED] 10/28/2003 1:29:25 PM does toad or the oracle instance itself slip in rule hints? We got an ora-600 error off of a data dictionary read. i think it has to do with explain plan. ORA-00600: internal error code, arguments: [17182], [2325084336], [], [], [], [], [], []Current SQL statement for this session:select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To 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).
tuning a co-related query howto
Hi All
Can somebody explain me how to tune this corealted
subquery.how do we convert the co-related subquery
into a inline if that helps
Select distinct PA.PersonAddress_IDX, AT.Name
AddressType,
A.Line1 Address1, A.Line2 Address2, A.City, A.State,
A.County, A.Country, A.PostalCode,
A.AllowPostalSoftYN, PA.ChangedBy,
PA.ChangedDT, PA.DeletedYN ,PA.Person_Key,
PA.Address_Key,
PA.AddressType_Key
FROMPersonAddress_h PA,Address_h A,AddressType_h AT
where PA.AddressType_Key IN (1,2,3) AND
AT.AddressType_IDX = PA.AddressType_Key
And A.Address_IDX = PA.Address_Key and PA.DeletedYN =
0
and PA.Person_KEY in (SELECT PERSON_KEY FROM
INSURED_h I where I.insured_idx=592374 )
and PA.CHANGEDDT=(select max(CHANGEDDT) from
PersonAddress_h
where PA.PERSON_KEY=Person_key and
AddressType_Key= PA.AddressType_Key
and Address_Key=PA.Address_Key)
and AT.CHANGEDDT=(select max(CHANGEDDT) from
AddressType_h
where AddressType_IDX = PA.AddressType_Key)
and A.CHANGEDDT= (Select max(CHANGEDDT) from
Address_h
where Address_IDX = PA.Address_Key and
(CHANGEDDT-to_date('10/22/2003
18:02:30','mm/dd/ hh24:mi:ss'))=0.001 )
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse1 0.30 0.30 0
0 0 0
Execute 1 0.00 0.00 0
0 0 0
Fetch1 13.46 31.73 27979
23786 31 0
--- -- -- --
-- -- --
total3 13.76 32.04 27979
23786 31 0
Rows Execution Plan
---
---
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (UNIQUE)
0HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF
'ADDRESS_H'
1 NESTED LOOPS
0 HASH JOIN
1100HASH JOIN
550 HASH JOIN
550 TABLE ACCESS GOAL: ANALYZED (BY
INDEX ROWID) OF
'PERSONADDRESS_H'
606 NESTED LOOPS
55TABLE ACCESS GOAL: ANALYZED (BY
INDEX ROWID)
OF 'INSURED_H'
55 INDEX (RANGE SCAN) OF
'INDX_INSURED_H_IDX_EDATE_CDATE' (NON-UNIQUE)
550INDEX (RANGE SCAN) OF
'INDX_PRSNADDR_PRSN_ADDR_H'
(NON-UNIQUE)
3 VIEW OF 'VW_SQ_2'
3 SORT (GROUP BY)
6INDEX (FAST FULL SCAN) OF
'CI_ADDRESSTYPE_H'
(NON-UNIQUE)
6 TABLE ACCESS GOAL: ANALYZED (FULL)
OF
'ADDRESSTYPE_H'
74421VIEW OF 'VW_SQ_3'
74421 SORT (GROUP BY)
462900 TABLE ACCESS GOAL: ANALYZED (FULL)
OF
'ADDRESS_H'
0 INDEX (RANGE SCAN) OF 'CI_ADDRESS_H'
(NON-UNIQUE)
0 VIEW OF 'VW_SQ_1'
0 SORT (GROUP BY)
0 INDEX (FULL SCAN) OF 'INDX_PRSNADDR_ALL'
(NON-UNIQUE)
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse1 0.30 0.30 0
0 0 0
Execute 2 0.00 0.01 0
0 0 0
Fetch1 13.46 31.73 27979
23786 31 0
--- -- -- --
-- -- --
total4 13.76 32.05 27979
23786 31 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
regards
Hrishy
Want to chat instantly with your online friends? Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?hrishy?=
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other
RE: Clone db 9.2 on AIX 5L
The alert log statest that the instance is starting NORMAL and that is all she wrote No trace files all my directories exist all owned by oracle UID The cr_spap script contains all create controlfile commands and yes since the STARTUP NOMOUNT is failing the balance is just from the echoed output .. Basically I think I'm just missing some obscure detail here, as there was someone in the list confirming that they are performing this on a similar environment AIX5.1 and ORACLE 9.2 That in mind I have gone through the init.ora checked for directory existance now researching listener config etc.. -Original Message- Sent: Tuesday, October 28, 2003 12:33 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' John, Did you see any messages in the alert log? And what is in the cr_spap script? the startup nomount is failing immediately, so everything else is worthless. Review the initspap.ora file and make sure that all of the directories exist. Also, is the ORACLE_SID evironmental set prior to running sqlplus? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, October 28, 2003 11:49 AM To: Multiple recipients of list ORACLE-L SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26 '/iu33/u02/oradata/spap/indexes03.dbf' 27 CHARACTER SET WE8ISO8859P1 28 ; CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-03114: not connected to ORACLE SQL -- RECOVER DATABASE SQL -- ALTER DATABASE OPEN; SQL -- ALTER TABLESPACE TEMP ADD TEMPFILE '/iu33/u02/oradata/spap/temp01.dbf' SIZE 3119M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE ; SQL The confusing thing is that I have instances already running on the machine. So I know the install is ok. I can start/shutdown the existing instances no problem. But when I try to clone and startup --- I get the results from above. -Original Message- Sent: Monday, October 27, 2003 5:24 PM To: [EMAIL PROTECTED] When are you getting the error? During startup? Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 03:04 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject RE: Clone db 9.2 on AIX 5L Sorry, AIX 5L to AIX 5L cold backup copies create backup controlfile to trace -- edited for the new file locations keeping the SID the same created init.ora from spfile startup nomount pfile=init.ora getting ora-3113 I have a TAR opened and figured I would check out here as well. -Original Message- [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:44 PM To: Multiple recipients of list ORACLE-L How about some more details? Are you cloning to a similar platform? Are you using a cold backup with controlfile recreation? RMAN backup or restore? RMAN duplicate? ... Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 02:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Clone db 9.2 on AIX 5L Just checking to see if anyone has been able to clone a 9.2 DB from one machine to another. I have never had a problem doing this prior to 9.2, and am just wondering if I have overlooked something peculiar to 9i. Thanks in adavance John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net --
** materialized view fast not working
Hi, I have a table tableA owned by userA. If I create a materialized view inanother schema/user fast and complete refresh set to refresh every 10 minutesthen onlycomplete refresh works and refreshes automatically every 10 minutes. However the fast refresh mv does not refresh automatically. It refreshes fine if the user executes DBMS_SNAPSHOT.REFRESH procedure. If I create a materialized view in the same user fast and complete refresh set to refreshevery 10 minutesboth work fine and refresh automatically every 10 minutes. Can some one help. Thank you Do you Yahoo!? The New Yahoo! Shopping - with improved product search
RE: Clone db 9.2 on AIX 5L
John Check your undo tablespace name in your CREATE CONTROLFILE statement and your init.ora file. I've received this error because the names are different. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, October 28, 2003 2:00 PM To: Multiple recipients of list ORACLE-L The alert log statest that the instance is starting NORMAL and that is all she wrote No trace files all my directories exist all owned by oracle UID The cr_spap script contains all create controlfile commands and yes since the STARTUP NOMOUNT is failing the balance is just from the echoed output .. Basically I think I'm just missing some obscure detail here, as there was someone in the list confirming that they are performing this on a similar environment AIX5.1 and ORACLE 9.2 That in mind I have gone through the init.ora checked for directory existance now researching listener config etc.. -Original Message- Sent: Tuesday, October 28, 2003 12:33 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' John, Did you see any messages in the alert log? And what is in the cr_spap script? the startup nomount is failing immediately, so everything else is worthless. Review the initspap.ora file and make sure that all of the directories exist. Also, is the ORACLE_SID evironmental set prior to running sqlplus? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, October 28, 2003 11:49 AM To: Multiple recipients of list ORACLE-L SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26 '/iu33/u02/oradata/spap/indexes03.dbf' 27 CHARACTER SET WE8ISO8859P1 28 ; CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-03114: not connected to ORACLE SQL -- RECOVER DATABASE SQL -- ALTER DATABASE OPEN; SQL -- ALTER TABLESPACE TEMP ADD TEMPFILE '/iu33/u02/oradata/spap/temp01.dbf' SIZE 3119M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE ; SQL The confusing thing is that I have instances already running on the machine. So I know the install is ok. I can start/shutdown the existing instances no problem. But when I try to clone and startup --- I get the results from above. -Original Message- Sent: Monday, October 27, 2003 5:24 PM To: [EMAIL PROTECTED] When are you getting the error? During startup? Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 03:04 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject RE: Clone db 9.2 on AIX 5L Sorry, AIX 5L to AIX 5L cold backup copies create backup controlfile to trace -- edited for the new file locations keeping the SID the same created init.ora from spfile startup nomount pfile=init.ora getting ora-3113 I have a TAR opened and figured I would check out here as well. -Original Message- [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:44 PM To: Multiple recipients of list ORACLE-L How about some more details? Are you cloning to a similar platform? Are you using a cold backup with controlfile recreation? RMAN backup or restore? RMAN duplicate? ... Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 02:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Clone db 9.2 on AIX 5L Just checking to see if anyone has been able to clone a 9.2 DB from one machine to another. I have never had a problem doing this prior to 9.2, and am just wondering if I have overlooked something peculiar to 9i. Thanks in adavance John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to:
RE: Clone db 9.2 on AIX 5L
what is your remote_login_passwordfile init.ora param set to? Change it to remote_login_passwordfile = none and see what happens. -Original Message- John Blake Sent: Tuesday, October 28, 2003 3:00 PM To: Multiple recipients of list ORACLE-L The alert log statest that the instance is starting NORMAL and that is all she wrote No trace files all my directories exist all owned by oracle UID The cr_spap script contains all create controlfile commands and yes since the STARTUP NOMOUNT is failing the balance is just from the echoed output .. Basically I think I'm just missing some obscure detail here, as there was someone in the list confirming that they are performing this on a similar environment AIX5.1 and ORACLE 9.2 That in mind I have gone through the init.ora checked for directory existance now researching listener config etc.. -Original Message- Sent: Tuesday, October 28, 2003 12:33 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' John, Did you see any messages in the alert log? And what is in the cr_spap script? the startup nomount is failing immediately, so everything else is worthless. Review the initspap.ora file and make sure that all of the directories exist. Also, is the ORACLE_SID evironmental set prior to running sqlplus? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, October 28, 2003 11:49 AM To: Multiple recipients of list ORACLE-L SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26 '/iu33/u02/oradata/spap/indexes03.dbf' 27 CHARACTER SET WE8ISO8859P1 28 ; CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-03114: not connected to ORACLE SQL -- RECOVER DATABASE SQL -- ALTER DATABASE OPEN; SQL -- ALTER TABLESPACE TEMP ADD TEMPFILE '/iu33/u02/oradata/spap/temp01.dbf' SIZE 3119M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE ; SQL The confusing thing is that I have instances already running on the machine. So I know the install is ok. I can start/shutdown the existing instances no problem. But when I try to clone and startup --- I get the results from above. -Original Message- Sent: Monday, October 27, 2003 5:24 PM To: [EMAIL PROTECTED] When are you getting the error? During startup? Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 03:04 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject RE: Clone db 9.2 on AIX 5L Sorry, AIX 5L to AIX 5L cold backup copies create backup controlfile to trace -- edited for the new file locations keeping the SID the same created init.ora from spfile startup nomount pfile=init.ora getting ora-3113 I have a TAR opened and figured I would check out here as well. -Original Message- [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:44 PM To: Multiple recipients of list ORACLE-L How about some more details? Are you cloning to a similar platform? Are you using a cold backup with controlfile recreation? RMAN backup or restore? RMAN duplicate? ... Adam John Blake [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/27/2003 02:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Clone db 9.2 on AIX 5L Just checking to see if anyone has been able to clone a 9.2 DB from one machine to another. I have never had a problem doing this prior to 9.2, and am just wondering if I have overlooked something peculiar to 9i. Thanks in adavance John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message
RE: Clone db 9.2 on AIX 5L
John, I'm not running AIX 5.1 or Oracle 9.2 (we're 8.1.6 on AIX 4.3.3). However, I have had some strange 3113 errors when trying to start a database. Did you have a false start and then ran the script again? The reason being we've seen problems due to memory segments hanging around after a crash. I found this note from our problems. --- Oracle error: ORA-03113 Error: End of Communication Channel - This error is caused by shared memory segments hanging due to the crashing of a database. This happened on prd3 and after a lot of research, it was found that doing a ipcs -ma command will show you all the processes using shared memory. Look for the ORACLE processes. On prd3, there were only 4 of them and each process ties back to a Oracle table by using the NATTCH field and grepping for the table name. For example, ps -ef | grep prod should give you the number of attached processes and that can be matched to the NATTCH field in the ipcs command, therefore that particular table can be associated with that database. Usually, the NATTCH field contains a 0 (zero), whenever the segment is hung and no longer associated with the database. In our case, the table involved was prod and the segment had a NATTCH field of 0 (zero). If you use the ipcrm -m (pid) to remove the hung segment, it will free it and allow the database to be started again. EXAMPLE: ipcs -am|grep oracle # Processes attached to this process m 655371 0xba08cf68 --rw-r- oracle dba oracle dba 82 1378840576 8642 67132 m 12 0x90edba50 --rw-r- oracle dba oracle dba 11 117329920 3896 52348 m 13 0x6b714a88 --rw-r- oracle dba oracle dba 18 131190784 2712 102820 m 262158 0xf7ef598c --rw-r- oracle dba oracle dba 21 107368448 11998 134330 Then do: ps -ef|grep SYS|wc -l 11 ps -ef|grep AUTO|wc -l 18 ps -ef|grep FAX|wc -l 22 ps -ef|grep PROD|wc -l 83 to verify that the count matches the counts from the ipcs -am. The counts are in the 9th column. Have the SA remove the memory segment that doesn't match. In fact, that one should have 0 as it's number of processes. -- You do need to do your homework with this, because you could drop a good database. But you should be ok if your SA knows what he's doing. Don't know if it will help, but thought I'd send it out. Durinda Jones Yellow Technologies -Original Message- Sent: Tuesday, October 28, 2003 2:00 PM To: Multiple recipients of list ORACLE-L The alert log statest that the instance is starting NORMAL and that is all she wrote No trace files all my directories exist all owned by oracle UID The cr_spap script contains all create controlfile commands and yes since the STARTUP NOMOUNT is failing the balance is just from the echoed output .. Basically I think I'm just missing some obscure detail here, as there was someone in the list confirming that they are performing this on a similar environment AIX5.1 and ORACLE 9.2 That in mind I have gone through the init.ora checked for directory existance now researching listener config etc.. -Original Message- Sent: Tuesday, October 28, 2003 12:33 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' John, Did you see any messages in the alert log? And what is in the cr_spap script? the startup nomount is failing immediately, so everything else is worthless. Review the initspap.ora file and make sure that all of the directories exist. Also, is the ORACLE_SID evironmental set prior to running sqlplus? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, October 28, 2003 11:49 AM To: Multiple recipients of list ORACLE-L SQL @cr_spap SQL STARTUP NOMOUNT pfile=/iu33/u01/app/oracle/admin/spap/pfile/initspap.ora ORA-03113: end-of-file on communication channel SQL CREATE CONTROLFILE REUSE DATABASE SPAP RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/iu33/u02/oradata/spap/redo01.log' SIZE 100M, 9 GROUP 2 '/iu33/u02/oradata/spap/redo02.log' SIZE 100M, 10 GROUP 3 '/iu33/u02/oradata/spap/redo03.log' SIZE 100M 11 DATAFILE 12 '/iu33/u02/oradata/spap/system01.dbf', 13 '/iu33/u02/oradata/spap/undotbs01.dbf', 14 '/iu33/u02/oradata/spap/drsys01.dbf', 15 '/iu33/u02/oradata/spap/example01.dbf', 16 '/iu33/u02/oradata/spap/odm01.dbf', 17 '/iu33/u02/oradata/spap/tools01.dbf', 18 '/iu33/u02/oradata/spap/users01.dbf', 19 '/iu33/u02/oradata/spap/xdb01.dbf', 20 '/iu33/u02/oradata/spap/users02.dbf', 21 '/iu33/u02/oradata/spap/users03.dbf', 22 '/iu33/u02/oradata/spap/users04.dbf', 23 '/iu33/u02/oradata/spap/users05.dbf', 24 '/iu33/u02/oradata/spap/indexes01.dbf', 25 '/iu33/u02/oradata/spap/indexes02.dbf', 26
Re: ** materialized view fast not working
Does the user have select access on the mlog? A Joshi wrote: Hi, I have a table tableA owned by userA. If I create a materialized view in another schema/user fast and complete refresh set to refresh every 10 minutes then only complete refresh works and refreshes automatically every 10 minutes. However the fast refresh mv does not refresh automatically. It refreshes fine if the user executes DBMS_SNAPSHOT.REFRESH procedure. If I create a materialized view in the same user fast and complete refresh set to refresh every 10 minutes both work fine and refresh automatically every 10 minutes. Can some one help. Thank you Do you Yahoo!? The New Yahoo! Shopping http://shopping.yahoo.com/?__yltc=s%3A15443%2Cd%3A22708228%2Cslk%3Atext%2Csec%3Amail - with improved product search -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Noarchivelog == archivelog
Well you got a number of responses, but the following have always worked for me: shutdown immediate; Startup mount; alter database archivelog; alter database open; Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, October 28, 2003 2:34 PM To: Multiple recipients of list ORACLE-L Hi List, Could someone help me to figure out what is going on here? I am trying to change db from NOARCHIVELOG -- ARCHIVELOG. SQL archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination/u02/arch Oldest online log sequence 966 Current log sequence 968 SQL shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL startup mount ORACLE instance started. Total System Global Area 1409298592 bytes Fixed Size73888 bytes Variable Size 374722560 bytes Database Buffers 102400 bytes Redo Buffers 10502144 bytes Database mounted. SQL alter database archivelog; alter database archivelog * ERROR at line 1: ORA-01126: database must be mounted EXCLUSIVE and not open for this operation Thanks Sami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dba interview questions
Ah. Some time ago when running a Youth Group, I discovered that there is only one infallible answer when faced with a 'popular beat music combo, m'lud' that one has never heard of. This consist of nodding ones head sagely and saying ' ah yes, but don't you think that they've gone a bit commercial'. Works every time - even with groups that have only released 1 record that sold 42 copies. Rumour has it even David Bowie fans are fooled by this particular line. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tim Gorman Sent: 28 October 2003 14:09 To: Multiple recipients of list ORACLE-L Subject: Re: dba interview questions You're likely to get the kind of response my kids would give: Wasn't he with the Beatles or the Stones or some other *old* band like that? Hell, my son considers the Offspring to be over the hill and Linkin Park as starting to lose it... About 7 years ago, I interviewed someone who listed Phish as one of his interests. I asked him What is Phish? and then spelled it for him. The expressions that swept across his face in one second ran from frank astonishment, to disbelief, to pity, to a carefully-composed poker face as he answered, A musical group that I like. Food for thought: when I was a kid in the 70s, my father would play his big band records and my brothers and I would roll our eyes and leave the house. Such lame, ancient music! At the time, those recordings were 30-35 years old... Um... For a kid today, the Stones, the Beatles, Pink Floyd, Frank Zappa, Yes, and Bowie are far more ancient. Not just in years, but the years do add up... ..'scuse me, I think I hear a bottle of Metamucil calling... on 10/28/03 4:59 AM, Richard Foote at [EMAIL PROTECTED] wrote: Only two questions are required to ensure you get an appropriate person for the job (any job): 1) What do you think of David Bowie, is he brilliant or what ? and providing they answer the above question positively 2) Are you any good ? Works every time ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 7:44 PM I ask things like tell me the thing you've done that you are most proud of and tell me your nightmare situation and how did you recover from it Ans: My worst nightmare, my date pick her nose infront of me, I call cab infront of her. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ora-600 question
What version of TOAD, Oracle server and Oracle client? See Metalink article 34779.1 for details on your bug, which may be fixed in releases 8.1.7.3, 9.0.1.2 and 9.2.0.1. BTW, TOAD can put in hints, but more importantly, it can do some conversion/translation of your SQL before sending it. You may want to checkout the official TOAD list at http://groups.yahoo.com/group/toad Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:29 PM To: Multiple recipients of list ORACLE-L Subject: ora-600 question does toad or the oracle instance itself slip in rule hints? We got an ora-600 error off of a data dictionary read. i think it has to do with explain plan. ORA-00600: internal error code, arguments: [17182], [2325084336], [], [], [], [], [], [] Current SQL statement for this session: select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hiva l, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
8i Support
List, Is any body know when Oracle stop supporting for 8i? Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: dba interview questions
How about Dogma? That's a new movie and it is hillarious. Salma Hayek character was very impresive. On 10/28/2003 02:49:32 PM, Jesse, Rich wrote: Ghostbusters? When someone asks if you're a god, you say 'YES'! ...and the flowers are still standing. Tell him about the Twinkie, Egon. Joe vs. The Volcano? Not a nice place you have here, Joe. I know he can get the job, but can he do the job? The Hunt for Red October? Be careful what you shoot at, Ryan. Most things in here don't react well to bullets. Next time, Jack, write a [gosh darn] memo. I said speak your mind, Jack, but geezus. One ping only. Come on, Big D, fly! Princess Bride? Have fun storming the castle! Did I make it clear that your job is at stake? Do you want me to send you back where you were -- unemployed in Greenland? Simpsons? Your manager says for you to shut up. And the weak and nerdy are admired for their computer programming ability. (OK, not a movie) Young Frankenstein? Throw the third switch! Not the THIRD switch! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: dba interview questions How about Life of Brian? That's even better then the Holy Grail. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ** materialized view fast not working
Mike : Yes the user has select access and is able to do a select on the MLOG table and the original table. Thanks for your help.Mike Spalinger [EMAIL PROTECTED] wrote: Does the user have select access on the mlog?A Joshi wrote: Hi, I have a table tableA owned by userA. If I create a materialized view in another schema/user fast and complete refresh set to refresh every 10 minutes then only complete refresh works and refreshes automatically every 10 minutes. However the fast refresh mv does not refresh automatically. It refreshes fine if the user executes DBMS_SNAPSHOT.REFRESH procedure. If I create a materialized view in the same user fast and complete refresh set to refresh every 10 minutes both work fine and refresh automatically every 10 minutes. Can some one help. Thank you Do you Yahoo!! ? The New Yahoo! Shopping - with improved product search-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mike SpalingerINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To 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). Do you Yahoo!? Exclusive Video Premiere - Britney Spears
Re: 8i Support
General support ends Dec 31, 2004, was Dec 31, 2003. [EMAIL PROTECTED] 10/28/03 03:44PM List,Is any body know when Oracle stop supporting for 8i?Thanks,Hamid AlaviOffice : 818-737-0526Cell phone : 818-416-5095-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Hamid Alavi INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To 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: Re: ora-600 question
The data dictionary should not have any statistics on them and thus will use rule as a rule so to speak. If you have run stats on the data dictionary you coul dbe running into some odd bugs. -Original Message- Sent: Tuesday, October 28, 2003 2:49 PM To: Multiple recipients of list ORACLE-L does the data dictionary still use rule by support? any idea why toad would bother slipping it in? From: John Shaw [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 02:59:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: ora-600 question TOAD puts a hint in. [EMAIL PROTECTED] 10/28/2003 1:29:25 PM does toad or the oracle instance itself slip in rule hints? We got an ora-600 error off of a data dictionary read. i think it has to do with explain plan. ORA-00600: internal error code, arguments: [17182], [2325084336], [], [], [], [], [], [] Current SQL statement for this session: select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hiva l, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: ora-600 question
By default it's set to 'choose' - but if try to use it for looking at locks (or most any ddl) it will take forever to come back -you have to go to the options menu and pick 'rule' for optimzer modeon ddl queries. [EMAIL PROTECTED] 10/28/2003 2:49:29 PM does the data dictionary still use rule by support? any idea why toad would bother slipping it in? From: "John Shaw" [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 02:59:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: ora-600 question TOAD puts a hint in.[EMAIL PROTECTED] 10/28/2003 1:29:25 PM does toad or the oracle instance itself slip in rule hints? We got an ora-600 error off of a data dictionary read. i think it has to do with explain plan. ORA-00600: internal error code, arguments: [17182], [2325084336], [], [], [], [], [], [] Current SQL statement for this session: select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hiva l, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Solved - RE: UTL_RAW and slowness
Raj I'm in :), so let's check what was the real issue, some more items here... Jamadagni, Rajendra wrote: Thanks Vladimir ... your input has made me look at my code again ... Here is relevant portion of profsum.sql output ... profsum Lines taking more than 1% of the total time, each run separate RUNID HSECSPCT OWNER UNIT_NAME LINE# TEXT - --- -- --- -- -- - 3 809.03 86.3 ST_DVDB2STWRITER_PKG_RAJ246 ntcpchar := ASCII(SUBSTR (msg_text, i,1)); 3 69.297.4 ST_DVDB2STWRITER_PKG_RAJ256 COMMIT; 3 13.621.5 ST_DVDB2STWRITER_PKG_RAJ248 nenctcpchar := TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),''); 3 10.131.1 ST_DVDB2STWRITER_PKG_RAJ247 r_chr := utl_raw.cast_to_raw(CHR(ntcpchar)); = = Most popular lines (more than 1%), summarize across all runs HSECSPCT UNIT_OWNER UNIT_NAME LINE# TEXT --- -- --- -- - 809.03 86.3 ST_DVDB2STWRITER_PKG_RAJ246 ntcpchar := ASCII(SUBSTR (msg_text, i,1)); 69.297.4 ST_DVDB2STWRITER_PKG_RAJ256 COMMIT; 13.621.5 ST_DVDB2STWRITER_PKG_RAJ248 nenctcpchar := TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),''); 10.131.1 ST_DVDB2STWRITER_PKG_RAJ247 r_chr = utl_raw.cast_to_raw(CHR(ntcpchar)); /profsum This shows that substr must have been the culprit ... I think, the profile *does not* show that. Moreover I'm not quite sure that the cause of the delays was SUBSTR(), but I would like to clarify some points here. Could you guess what's the difference between these two lines of code? l_n := ASCII(SUBSTR(l_s, j, 1)); l_n := ASCII(SUBSTR(l_s, j, 1)); That's ok if you could not. Nobody could. Because nobody knows that are the datatypes of l_n and l_s. And there is *significant* difference between datatypes in PL/SQL. Am I right assuming that msg_text could be CLOB and l_n could be NUMBER? Could it be like that? I think so. Could you please tell me what those datatypes are/were? BTW, why do you think it *was* OK to use SUBSTR() but not SUBSTRB() -- sure, you know the requirements better -- do you tranfer only US ASCII data? BTW I benchmarked your code, extended the strings to 2000 characters and ran each conversion in a loop of 2000 and using utl_raw method turned out to be the fastest. As I mentioned -- do it in 'bulk' if it's acceptable from security point. thanks again for your insight and sample code ... I never knew nor noticed other utl_raw subprograms like utl_raw.copies ... I would suggest to increase the length of the key at least up to 128 bytes. Now due to pipelining my code is very fast and to accomodate a 122 baud feed, I have insert artificial delays in my code. 8:) What's the point to pipeline it? Appreciate your feedback. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: ora-600 question
does the data dictionary still use rule by support? any idea why toad would bother slipping it in? From: John Shaw [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 02:59:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: ora-600 question TOAD puts a hint in. [EMAIL PROTECTED] 10/28/2003 1:29:25 PM does toad or the oracle instance itself slip in rule hints? We got an ora-600 error off of a data dictionary read. i think it has to do with explain plan. ORA-00600: internal error code, arguments: [17182], [2325084336], [], [], [], [], [], [] Current SQL statement for this session: select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hiva l, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). TOAD puts a hint in. [EMAIL PROTECTED] 10/28/2003 1:29:25 PM does toad or the oracle instance itself slip in rule hints? We got an ora-600 error off of a data dictionary read. i think it has to do with explain plan. ORA-00600: internal error code, arguments: [17182], [2325084336], [], [], [], [], [], []Current SQL statement for this session:select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To 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).
Foreign Key and Unique key on same columns
Hi, Is there any performance problem if two columns have FOREIGN KEY from different tables and both columns also have UNIQUE CONSTRAINT? CREATE TABLE table1 ( COL1NUMBER constraint table1_fk1 references table2(col1), COL2NUMBER constraint table1_fk2 references table3(col1)); CREATE UNIQUE INDEX table1_uq1 ON table1(COL1, COL2); Thanks Muqthar Ahmed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: wither Designer documentation?
Well then eventually no one will know how to use their products and move to other products that come with documentation sets. : ) Patrice -Original Message- Sent: Tuesday, October 28, 2003 3:14 PM To: Multiple recipients of list ORACLE-L none of the apps have documentation. not forsm, reports, or discoverer and noone is writing updated books on them since they dont sell enough copees. i dont understand oracle on this one. atleast they can write one and sell the damn thing... From: Tracy Rahmlow [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 01:29:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: wither Designer documentation? I have had the same problem. I have contacted Oracle for a user guide with no response as of yet. I am literally printing off the help pages within Designer to address the need. It sucks 10/28/2003 10:14 AM PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Where is the Designer 9i documentation? Not on OTN (http://otn.oracle.com/documentation/designer.html), not in download-east (http://download-east.oracle.com/docs/cd/A91773_01/ids902dl/index.htm ), not in tahiti... (http://tahiti.oracle.com ) Oracle Designer Generation seems to be the only book (Oracle Press). Oracle Designer Handbook by Pete Koletzke was published in 1998. Patrice -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). American Express made the following annotations on 10/28/2003 11:27:28 AM -- ** This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you. ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
Re: Refresh option for Materialized view , want to use it during refresh - for
David, Glad to be of help. Looking at your list of steps, I see that your questions is whether building an MV on the table T is beneficial. Tables and MVs, on prebuilt table or not, are stored as segments in the database; so space-sise there is no difference, nor there is any change in the way the MV/Table is accessed. There are a few situations where you may want to convert a table to MV. They are: * Building an MV enables Query Rewrite, where Oracle smartly decides to rewrite a user query to select from the MV instead of the main tables. This is not possible on a regular table. The user must explicitly select from it. * If you want to refresh FAST, then MVs are required. You can do a fast refresh on a table, but you have to write your own procedures for that. DBMS_MVIEW package does it for you on MVs. * Your designer software will recognize MV as one and will report it to all users, who are aware of the fact that it's an MV, useful for queries. A mere table will not be clear on that regard. Converting a table to MV does not cost any resource, as the change is done inside the data dictionary only. So, if you are in doubt, you may just convert the table to MV anyway. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:04 PM refresh - for Arup, I really appreciate your answer in great details. I got on prebuilt table work. Thanks a lot for your help. Here is another question: Do you see any advantage to use materialized view on prebuilt table for my data loading over just simple renaming tables as steps below: 1. create table t that is always accessed by applications 2. create table t1 that is a temp table for loading 3. load data into table t1 4. rename table t to table t2 5. rename table t1 to t 6. rename table t2 to t1 7. truncate table t1 for next day loading David From: Arup Nanda [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Refresh option for Materialized view , want to use it during refresh - for Date: Fri, 24 Oct 2003 18:04:33 -0800 David, Answers to your questions: (1) Without knowing your exact needs, I wil offer a few different scenarios. I am assuming that you are doing a complete refresh every time. The following pertain to that. Say, your name of the MV is MV1. Here are the steps the first time. 1. Create table MV1 2. Create MV MV1 on that table. When you want to refresh complete: 1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink (with NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp. 2. Drop MV MV1. This drops the MV but doesn't drop the table. 3. Drop table MV1. 4. Rename table MV1_TEMP to MV1. 5. Recreate MV MV1. 6. Allow users to proceed as usual. Note the time consumed between Steps 2 and 6 are in the order of a few seconds. And it's the only time the users will not have access to the MV, as opposed to a full refresh using dbms_mview.refresh approach., which will lock the MV for the entire duration and generate tons of redo and rollback. Even if you do a incremental refresh, this is still a better approach. In that case, you don't drop the table during the refresh. (2) Yes, the option is available in 8i, at least in 8.1.7. Make sure the syntax is correct. create materialized view MV1 on prebuilt table refresh fast as select ... from In the article I mentioned, you can find the complete syntax. www.proligence.com/downloads.html is the site. It also dscribes a step by step solution to the issue and compares the common solution with this new one. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW
RE: Solved - RE: UTL_RAW and slowness
Why do you think the profsum output is not right? at least it tells me that 83% of my time is spent on the line that does substr() ... right? msg_text is a clob, l_n is number you are right substr() was used because we transfer only US ASCII data. The point of pipelining was because in my previous version of code, I was experiencing delays due to (or may be due to) substr() operation ... There is no other easy way to split the CLOB and process it. I am comfortable with pipelining, I know it will work in this scenario so I used it, it worked. Got better ideas? BTW I on a second (9600 baud) feed I was feeding plain_text using utl_tcp.write_text and my colleagues were experiencing slight delays on the monitor even when I was sending 32k characters. So, I tested with sending 8k characters, convert to raw and use utl_tcp.write_raw, my colleagues are happy, they don't want to change it now. Thanks for your feedback, I appreciate it. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, October 28, 2003 4:15 PM To: Multiple recipients of list ORACLE-L Raj I'm in :), so let's check what was the real issue, some more items here... Jamadagni, Rajendra wrote: Thanks Vladimir ... your input has made me look at my code again ... Here is relevant portion of profsum.sql output ... profsum Lines taking more than 1% of the total time, each run separate RUNID HSECSPCT OWNER UNIT_NAME LINE# TEXT - --- -- --- -- -- - 3 809.03 86.3 ST_DVDB2STWRITER_PKG_RAJ246 ntcpchar := ASCII(SUBSTR (msg_text, i,1)); 3 69.297.4 ST_DVDB2STWRITER_PKG_RAJ256 COMMIT; 3 13.621.5 ST_DVDB2STWRITER_PKG_RAJ248 nenctcpchar := TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),''); 3 10.131.1 ST_DVDB2STWRITER_PKG_RAJ247 r_chr := utl_raw.cast_to_raw(CHR(ntcpchar)); = = Most popular lines (more than 1%), summarize across all runs HSECSPCT UNIT_OWNER UNIT_NAME LINE# TEXT --- -- --- -- - 809.03 86.3 ST_DVDB2STWRITER_PKG_RAJ246 ntcpchar := ASCII(SUBSTR (msg_text, i,1)); 69.297.4 ST_DVDB2STWRITER_PKG_RAJ256 COMMIT; 13.621.5 ST_DVDB2STWRITER_PKG_RAJ248 nenctcpchar := TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),''); 10.131.1 ST_DVDB2STWRITER_PKG_RAJ247 r_chr = utl_raw.cast_to_raw(CHR(ntcpchar)); /profsum This shows that substr must have been the culprit ... I think, the profile *does not* show that. Moreover I'm not quite sure that the cause of the delays was SUBSTR(), but I would like to clarify some points here. Could you guess what's the difference between these two lines of code? l_n := ASCII(SUBSTR(l_s, j, 1)); l_n := ASCII(SUBSTR(l_s, j, 1)); That's ok if you could not. Nobody could. Because nobody knows that are the datatypes of l_n and l_s. And there is *significant* difference between datatypes in PL/SQL. Am I right assuming that msg_text could be CLOB and l_n could be NUMBER? Could it be like that? I think so. Could you please tell me what those datatypes are/were? BTW, why do you think it *was* OK to use SUBSTR() but not SUBSTRB() -- sure, you know the requirements better -- do you tranfer only US ASCII data? BTW I benchmarked your code, extended the strings to 2000 characters and ran each conversion in a loop of 2000 and using utl_raw method turned out to be the fastest. As I mentioned -- do it in 'bulk' if it's acceptable from security point. thanks again for your insight and sample code ... I never knew nor noticed other utl_raw subprograms like utl_raw.copies ... I would suggest to increase the length of the key at least up to 128 bytes. Now due to pipelining my code is very fast and to accomodate a 122 baud feed, I have insert artificial delays in my code. 8:) What's the point to pipeline it? Appreciate your feedback. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. ** 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.
Re: Re: wither Designer documentation?
Documentation writing will likely be outsourced to Elbonia. So, wait until the little bearded men who live in the swamp write your documentation and everything will be OK. I bet you will not be satisfied even then. You'll make unreasonable requests and demand that documentation is actually meaningful. On 10/28/2003 02:59:38 PM, Boivin, Patrice J wrote: Well then eventually no one will know how to use their products and move to other products that come with documentation sets. : ) Patrice -Original Message- Sent: Tuesday, October 28, 2003 3:14 PM To: Multiple recipients of list ORACLE-L none of the apps have documentation. not forsm, reports, or discoverer and noone is writing updated books on them since they dont sell enough copees. i dont understand oracle on this one. atleast they can write one and sell the damn thing... From: Tracy Rahmlow [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 01:29:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: wither Designer documentation? I have had the same problem. I have contacted Oracle for a user guide with no response as of yet. I am literally printing off the help pages within Designer to address the need. It sucks 10/28/2003 10:14 AM PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Where is the Designer 9i documentation? Not on OTN (http://otn.oracle.com/documentation/designer.html), not in download-east (http://download-east.oracle.com/docs/cd/A91773_01/ids902dl/index.htm ), not in tahiti... (http://tahiti.oracle.com ) Oracle Designer Generation seems to be the only book (Oracle Press). Oracle Designer Handbook by Pete Koletzke was published in 1998. Patrice -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). American Express made the following annotations on 10/28/2003 11:27:28 AM -- ** This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you. ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services--
Re: dba interview questions
Bumper sticker sighted just the other day: My karma ran over your dogma How about Dogma? That's a new movie and it is hillarious. Salma Hayek character was very impresive. On 10/28/2003 02:49:32 PM, Jesse, Rich wrote: Ghostbusters? When someone asks if you're a god, you say 'YES'! ...and the flowers are still standing. Tell him about the Twinkie, Egon. Joe vs. The Volcano? Not a nice place you have here, Joe. I know he can get the job, but can he do the job? The Hunt for Red October? Be careful what you shoot at, Ryan. Most things in here don't react well to bullets. Next time, Jack, write a [gosh darn] memo. I said speak your mind, Jack, but geezus. One ping only. Come on, Big D, fly! Princess Bride? Have fun storming the castle! Did I make it clear that your job is at stake? Do you want me to send you back where you were -- unemployed in Greenland? Simpsons? Your manager says for you to shut up. And the weak and nerdy are admired for their computer programming ability. (OK, not a movie) Young Frankenstein? Throw the third switch! Not the THIRD switch! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: dba interview questions How about Life of Brian? That's even better then the Holy Grail. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: MSSQL Link Server connection failure to Oracle 817
-Original Message- From: Denham Eva [mailto:[EMAIL PROTECTED] Sent: Tuesday, 28 October 2003 22:09 To: Multiple recipients of list ORACLE-L Subject: MSSQL Link Server connection failure to Oracle 817 Hello, To my surprise I see there alot of us on this list, use MSSQL and Oracle in the same environment. Guilty. (I promise I won't mention DB2 as well) So please forgive me for asking this but it is a huge issue here at my work. The problem is like this, we have a MSSQL 2000 box connecting to Oracle 817, via Linked servers using OLEDB. The jobs will run fine for awhile, but then suddenly fail with the following error Executed as user: TFMC\Administrator. OLE DB provider 'MSDASQL' reported an error. [SQLSTATE 42000] (Error 7399) Driver's SQLSetConnectAttr failed] [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed. OK, this is specifically the MS driver being used here, but when Oracle drivers are used, we have the same issues. Both Databases are on HP/Compaq servers, and the Windows 2000 platform. I have loaded the newest patches for OLEDB on the MSSQL for the Oracle Client, but nothing helps. Has anyone experienced this issue before? Denham, From my experience, you're in for a torrid time. The 0x80004005 error is returned from the Win32 load library call (can't remember the exact method name, but basically it's the standard load this DLL call). 0x80004005 means ... wait for it ... FAILED. That's it. That's all MS wrote for this error. As you can see from your error dump, the ::Initialize method was being called, which implicitly loads the provider (and thus the DLL). The other thing I notice from your error is that you are NOT using the MS or Oracle native providers. You are using the OLEDB to ODBC bridge (MSDASQL ... instead of MSDAORA or OraOLEDB). This is a dog's breakfast. Try changing to whichever of the other two you haven't tried, and see if the error persists. Ciao Fuzzy :-) -- The contents of this post are my opinions only If swallowed seek medical advice -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
9I RAC corporate standard.
Hi, Has anyone started to implement 9I Rac as a corporate standard... IE. many or all the apps being deployed on 9I RAC clusters? We are looking at doing it and wanted to know what other people had as experience in doing it or on the way to attempting it. If so, what hardware platform are you using? HP Itanium or Linux boxes etc? Thanks for sharing experience... B / R / I / A / N S / P / E / A / R / S Anybody know what the above signature is about ? :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Spears, Brian INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dba interview questions
-Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Wednesday, 29 October 2003 05:44 To: Multiple recipients of list ORACLE-L Subject: Re: dba interview questions How about Life of Brian? That's even better then the Holy Grail. You're right, Mladen ... because we're all individuals. Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: anyone have opinions on the future of the 'grid'?
Title: RE: anyone have opinions on the future of the 'grid'? Oracle confirmed at the road show event I went to yesterday that "pricing will not change". Let's see how many people are happy to buy 100's of EE CPU licences :-) And my favourite bit? The new OEM is all singing, all dancing ... but must be hosted by 10gAS. (Though that's probably the case with EM under 9i .. just don't use it often enough to know). Ciao Fuzzy :-) --The contents of this post are my opinions only If swallowed seek medical advice -Original Message-From: Whittle Jerome Contr NCI [mailto:[EMAIL PROTECTED]Sent: Wednesday, 29 October 2003 03:00To: Multiple recipients of list ORACLE-LSubject: RE: anyone have opinions on the future of the 'grid'? Oracle will probably think so with their per CPU pricing. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: AK [SMTP:[EMAIL PROTECTED] S Does it mean that a network with 100 computers of 1 cpu each is almost equivalent to 100 cpu giant computer ?? -ak
Re: dba interview questions
Better still, sometimes 'X' takes the questions while 'Y' the candidate just stands next to him because 'X' can answer but Y does not. Tel.int means you (most probably) have not seen the guys face..correct?? By the time Visa gets approved (if overseas candidate), 'Y' ensures that he 'gains' some experience. GovindanK <-Original Message-> Sent: 10/24/2003 7:45:29 AM To: [EMAIL PROTECTED] Oh! Well. I have not seen Tom's book yet. But still, when the candidate is explaining this stuff to you, there are plenty of opps to question him/her to find out if he/she really knows fundamental things.. During one phone interview, we could clearly hear the paper shuffle in the background, while the candidate asked us to repeat the question (a couple of times) to 'make sure' he understood it correctly before answering (reading?)it :) We stopped phone interview process after this!! - Kirti --- [EMAIL PROTECTED] wrote: > that question is diagrammed and answered in tom kytes new book. :) im waiting to get asked it. > > there is a new ault book out on interview questions. I dont think they are very tough. I think > situational questions are better. Have a development DB set up with things for the applicant to > do. > > I find that most employers ask the same easy questions. Particularly developer questions > > > --- system manager <[EMAIL PROTECTED]> wrote: > Dear List,Can anyone send me a list of dba interview questions? > > Thanks, > > ___Get Your 10MB account for FREE at http://mail.arabia.com !Access MILLIONS of JOBS NOW!
Re: Re: ora-600 question
Hi! Just for the record, in 9.2 some views such dba_extents use ordered and use_nl hints, which force usage of CBO. If you don't have statistics calculated nor optimizer_dynamic_sampling set to at least 2, then you'll be using CBO with default statistics, which usually are quite misleading. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 11:09 PM The data dictionary should not have any statistics on them and thus will use rule as a rule so to speak. If you have run stats on the data dictionary you coul dbe running into some odd bugs. -Original Message- Sent: Tuesday, October 28, 2003 2:49 PM To: Multiple recipients of list ORACLE-L does the data dictionary still use rule by support? any idea why toad would bother slipping it in? From: John Shaw [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 02:59:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: ora-600 question TOAD puts a hint in. [EMAIL PROTECTED] 10/28/2003 1:29:25 PM does toad or the oracle instance itself slip in rule hints? We got an ora-600 error off of a data dictionary read. i think it has to do with explain plan. ORA-00600: internal error code, arguments: [17182], [2325084336], [], [], [], [], [], [] Current SQL statement for this session: select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hiva l, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
