User Log-Machine Name
Hello A user log is maintained for a table.I want to log all updations made to this table. I have written a database trigger (After Update) to log the changes to another table.My requirement is to find the machine name from which the updation has occurred and log the machine name also . How to get the currently working session ID so that machine name can be retrieved fromV$session view or any other method to retrive the machine name uniquely. Is it that one Oracle user can have multiple active sessions on different machines? Eg. User Name Status Module Session Machine XXACTIVESQL*Plus 10 AA XXACTIVESQL*Plus 19 BB Regards, Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Unkillable Background process SHUTDOWN ABORT LEAVES UNKILLAB
Hi Stephen, Sun Clustering basically quite simple (I think) :) an Oracle user dedicated to this software use to monitor (do query for certain system tables) the existence of the db if anything wrong than this so call fault monitoring will do shutdown abort and release the mount points for second node to do startup pfile= the problem is shutdown abort successful but the mounted points cannot be unmount because of those UNKILLED background processes. I think sun clustering shuold add this if cannot release mount point then do STOP A follow by power off, right ? Sinardy -Original Message- Sent: 01 November 2002 23:03 To: Multiple recipients of list ORACLE-L UNKILLAB -Original Message- when I try to sync the session hung (init will sync automatically therefore it hung too) I have to POWER OFF and POWER ON my SUN BOX to release the mount point My sun clustering fail to fail over because of this there is jargon in unix call zombie process (what is this ?) What is tampered process, and when it happen ? A zombie process is a process that was started by a parent process in such a way that it is expected that the child process will exit and the parent will acknowledge the exit; but the parent never did. You used to see this all the time on a Unix box in the past. Now days, the init process usually becomes the foster parent so you don't have zombies piling up as time goes on. There was never anything terribly wrong with zombies (as long as it wasn't some ridiculous amount) since the only resource they tied up (at least in theory) was a process ID number; and it made for a messy, disconcerting ps output. I know absolutely nothing about Sun clustering; but I am wondering if you are having trouble with network file system reads and writes. NFS problems can cause hanging. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- 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: Relation Between Oracle Sessions
Hussain, This is just from the top of my head and therefore requires careful checking, but I presume that if you are not using MTS then the 2nd generation session must be initiated by your shadow process (hope you are under Unix!). V$SESSION holds the OS process id of the program on the 'client side' (eq sqlplus), and V$PROCESS contains the details about the shadow processes (SADDR lets you link with V$SESSION). This is where I would have a look. If my theory is right, the 'middle generation' process id should be both in V$SESSION and V$PROCESS. HTH Stephane Faroult - Original Message - From: Hussain Ahmed Qadri [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 04 Nov 2002 21:08:21 Hi all, A question related to Developer but involves forming sessions at the backend, so would appreciate any help or ideas. Whenever a form is opened, it opens an Oracle session (can be seen from v$session). Now when we call another form from this opened form, (call form property of forms), it will open another session. If we consider the main form as the parent form and the form called from within it as its child form, then, IS there a way to know, from some view or else, that session of this newly opened form is the child of which parent session, their relationship basically? Thanks in advance Hussain Ahmed Qadri DBA SKMCHRC Pakistan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: Relation Between Oracle Sessions
Title: RE: Relation Between Oracle Sessions Hi Justin, We have our application in the form of a menu, with all the different modules written on top in the form of menus. Now with call-form property (which we use now in our application), only one form can be opened as the menu is disappeared from the top and is only made visible when that particular form is closed and this results in only one initial session. (Although by passing a parameter to call-form property of form, we can still tell Oracle to open this form in another session, other than the initial session). But when we use the Open-form property in forms, then every form that is opened from with in the application is opened in another session. Menu is there at the top and even if one form is open, you can use the menu and open another form or module. So in my opinion that's not a bug but an Oracle property. And it's this newly opened session (if we use the open-form property) from with in the main application, whose relation to the session of the main application I wanted to know. You might be absolutely right that Oracle doesn't keep track of the so-called parent-child relation ship of sessions, so I wanted to know if there was any indirect method, like from some views or something that can help me track that relationship. If there is something that is confusing, please let me know. Thank you for your time. Regards, Hussain Ahmed Qadri DBA SKMCHRC -Original Message- From: Justin Cave [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 11:03 AM To: Multiple recipients of list ORACLE-L Subject: Re: Relation Between Oracle Sessions At 10:08 PM 11/4/2002, you wrote: Whenever a form is opened, it opens an Oracle session (can be seen from v$session). Now when we call another form from this opened form, (call form property of forms), it will open another session. If we consider the main form as the parent form and the form called from within it as its child form, then, IS there a way to know, from some view or else, that session of this newly opened form is the child of which parent session, their relationship basically? I believe the simple answer here is no. If your application is opening multiple sessions, each session is logically independent of the other-- Oracle has no concept of parent or child sessions. I would generally take this sort of behavior to indicate a bug in your application-- generally one wants to reuse connections across multiple forms so that each form doesn't have the overhead of opening a new connection to the database. Justin Cave Distributed Database Consulting -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Justin Cave 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).
ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE
Hello All Oracle 8.1.6.3.4 on NT. I got ora-19502 - can not write archive log, and users could not logged on. I can logon only as internal. We had the same problem a few days ago. The technical support people checked the raid 5 disks and did not find any I/O errors. There is a lot of free space on the drive. Has anybody encountered this problem? Do you know what can cause it? What I need to do in order to prevent this from happening again? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: User Log-Machine Name
Hei...
Inside trigger block, using userenv('terminal') you can identify the machine
name.
One oracle user can have multiple active sessions.
(Here in my team, 5 people working on the same oracle username with
different machines.
The privileges are set for them at application level.)
HTH.
Nirmal.,
-Original Message-
[mailto:PK_Deepa/VGIL;vguard.satyam.net.in]
Sent: Tuesday, November 05, 2002 11:23 AM
To: Multiple recipients of list ORACLE-L
Hello
A user log is maintained for a table.I want to log all updations made to
this table.
I have written a database trigger (After Update) to log the changes to
another table.My requirement is to find the machine name from which the
updation has occurred and log the machine name also .
How to get the currently working session ID so that machine name can be
retrieved fromV$session view or any other method to retrive the machine
name uniquely.
Is it that one Oracle user can have multiple active sessions on different
machines? Eg.
User Name Status Module Session Machine
XXACTIVESQL*Plus 10 AA
XXACTIVESQL*Plus 19 BB
Regards,
Deepa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nirmal Kumar Muthu Kumaran
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: User Log-Machine Name
audsid = SYS_CONTEXT('USERENV', 'SESSIONID')
You could have guessed it :-).
- Original Message -
From: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Tue, 05 Nov 2002 00:23:28
Hello
A user log is maintained for a table.I want to log
all updations made to
this table.
I have written a database trigger (After Update) to
log the changes to
another table.My requirement is to find the
machine name from which the
updation has occurred and log the machine name also
.
How to get the currently working session ID so that
machine name can be
retrieved fromV$session view or any other method to
retrive the machine
name uniquely.
Is it that one Oracle user can have multiple active
sessions on different
machines? Eg.
User Name Status Module Session
Machine
XXACTIVESQL*Plus 10 AA
XXACTIVESQL*Plus 19 BB
Regards,
Deepa
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
http://www.fatcity.com
San Diego, California-- Mailing list and
web hosting services
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
---
--
Regards,
Stephane Faroult
Oriole
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroul
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).
OCP Exams - What to study out of?
Title: OCP Exams - What to study out of? Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly Insect Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
suggestion w/c platforms to choose from...
hi, can anyone suggestion w/c platform should i used to run oracle? wat are the things to consider in choosing platform? thanks Best regards, Grace Lim MIS Department Suy Sing Comm'l Corp. T- (632)-2474134 F- (632)-2474160 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: grace 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).
Read Only Login with Source Visibility
Hi all, I am trying to create a read-only login, RO_USER which can do the following: 1. See all tables, views, constraints etc. in one *specific* other schema, APP. 2. See all the source for PL/SQL objects in APP. The first bit is easy: GRANT SELECT on tables, views to RO_USER. Not sure about the second bit. I have tried granting CREATE ANY PROCEDURE and CREATE ANY TRIGGER to RO_USER and using a DDL trigger to prevent these privileges actually being used to create procedures/triggers. This works but does not restrict the source visibility to APP. I have other schemas where I do not want RO_USER to see the source. My other concern is granting SELECT on sequences as this means that they can select nextval from them and hence increment the numbers (not quite read-only!) Any suggestions for fixing these problems would be much appreciated! Thanks - Bill. PS. This is on 8i. -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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: suggestion w/c platforms to choose from...
As a rule buy the biggest, meanest, fault tolerance, with gigabytes of memory and terabytes of disk storage that you can buy. If you will provide more data about: 1) The size of the database 2) How many users 3) How critical is the system 4) The use of the system - data warehouse, OLTP etc then you will probably get a more specified answer. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 2:13 PM hi, can anyone suggestion w/c platform should i used to run oracle? wat are the things to consider in choosing platform? thanks Best regards, Grace Lim MIS Department Suy Sing Comm'l Corp. T- (632)-2474134 F- (632)-2474160 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: grace 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.com -- 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: suggestion w/c platforms to choose from...
How about considering what OS skills your systems admins already have? Do they have experience with HP/UX, or Solaris, or NT/2000 type systems etc.? How big is the database? How many users will it have? What will the database be used for? How much money do you have to spend? Mark -Original Message- Sent: 05 November 2002 12:14 To: Multiple recipients of list ORACLE-L hi, can anyone suggestion w/c platform should i used to run oracle? wat are the things to consider in choosing platform? thanks Best regards, Grace Lim MIS Department Suy Sing Comm'l Corp. T- (632)-2474134 F- (632)-2474160 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: grace 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.com -- 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: OCP Exams - What to study out of?
Title: OCP Exams - What to study out of? I only studied the Coriolius books and done the self-exams (which are harder then the real one) and passed with 85+% each time. I think the course materials were not as concise as the Cor. books. I believe you can get some of those books still on Amazon. That publisher is out of business. Someone recently posted which publisher is taking over for them. Good luck. -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: Tuesday, November 05, 2002 7:09 AMTo: Multiple recipients of list ORACLE-LSubject: OCP Exams - What to study out of? Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly "Insect" Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity." Dennis Ritchie. DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
ORA-02050
ORA-02050: transaction 8.82.26033 rolled back, some remote DBs may be in-doubt The transaction seems to have completely rolled back. My question is, is there any way to relate transaction id 8.82.26033 to an application or table row or something at a higher layer? Thanks. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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: Read Only Login with Source Visibility
How about redefining copies of the relevant data dictionary views for RO_USER to suit your purposes? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 5:48 AM Hi all, I am trying to create a read-only login, RO_USER which can do the following: 1. See all tables, views, constraints etc. in one *specific* other schema, APP. 2. See all the source for PL/SQL objects in APP. The first bit is easy: GRANT SELECT on tables, views to RO_USER. Not sure about the second bit. I have tried granting CREATE ANY PROCEDURE and CREATE ANY TRIGGER to RO_USER and using a DDL trigger to prevent these privileges actually being used to create procedures/triggers. This works but does not restrict the source visibility to APP. I have other schemas where I do not want RO_USER to see the source. My other concern is granting SELECT on sequences as this means that they can select nextval from them and hence increment the numbers (not quite read-only!) Any suggestions for fixing these problems would be much appreciated! Thanks - Bill. PS. This is on 8i. -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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.com -- 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: OCP Exams - What to study out of?
Denham, I have just passed my 8i upgrade exam and added some notes to a page about getting OCP certification. It can be accessed on http://www.hcresources.co.uk/ocp.htm http://www.hcresources.co.uk/ocp.htm Hope you find it interesting John -Original Message- Sent: 05 November 2002 12:09 To: Multiple recipients of list ORACLE-L Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly Insect Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RTIF Interface
What is an RTIF interface? I looked on Acronym Finder and it didn't find it in it's database. Thanks, Ken Janusz, CPIM
RE: ORA-02050
Ray, I think you need to check DBA_2PC_PENDING on both the local and remote database to see if anything is still in there. That gives the osuser, terminal and host name so you may be able to get something from that. Of course if there is nothing in that table then the transaction has been rolled back already John -Original Message- Sent: 05 November 2002 13:43 To: Multiple recipients of list ORACLE-L ORA-02050: transaction 8.82.26033 rolled back, some remote DBs may be in-doubt The transaction seems to have completely rolled back. My question is, is there any way to relate transaction id 8.82.26033 to an application or table row or something at a higher layer? Thanks. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Read Only Login with Source Visibility
Hi Bill I am trying to create a read-only login, RO_USER which can do the following: 1. See all tables, views, constraints etc. in one *specific* other schema, 2. See all the source for PL/SQL objects in APP. Is what you really want is a way for users to be able to see the DDL and PL/SQL without actually being able to run or modify it? If so, as a suggestion, why don't you try using the DDL to HTML documentation generator in the freeware DBATool. You could easily cut a browsable tree with all (or part) of a schemas DDL. It also does nice things like cross reference related entities - for example for a table you can click on links to the associated indexes, foreign keys, grants, synonyms etc. Password protection and security can easily be implemented by moving the HTML tree onto a password protected drive. An example tree can be found at the link below http://www.databee.com/dt_htmltree/db=TESTDB1+sc=DT_TEST.htm The DBATool is freeware http://www.DataBee.com Regards Dale Edgar Net 2000 Ltd. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: MTS process HIGH CPU
That process has an oracle session associated with it. Before killing it, it would be nice to see what that session is doing. You could do it by joining v$process and v$session. The SPID column in v$process is System PID and ADDR column corresponds to the PADDR in V$SESSION table. -Original Message- From: Seema Singh [mailto:oracledbam;hotmail.com] Sent: Monday, November 04, 2002 2:09 PM To: Multiple recipients of list ORACLE-L Subject: MTS process HIGH CPU Hi I am using MTS in 8.1.6.I am wondering one of MTS process is locked 2 table since morning and this process is taking more CPU.Can I kill that process like ora_s000_prod by using alter system and then unix kill command? Thx -Seema _ Choose an Internet access plan right for you -- try MSN! http://resourcecenter.msn.com/access/plans/default.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh 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.com -- Author: Gogala, Mladen 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: Unkillable Background process SHUTDOWN ABORT LEAVES UNKILLAB
I once used a product like this called First Watch, but I called it automatic failover rather than clustering. The product provided a redundant heart beat monitoring; but the user had to set up the configuration files and write and maintain any scripts that controlled the failover. What we discovered was that the Sun OS and hardware were so reliable that the most likely point of failure was in the human management of the configuration files and scripts. That is, the hardware was more reliable than people. So we decided to disable the automatic failover and use manual failover, where we would manually unplug the cables to the storage arrays from one box, plug them into the failover box, then boot the failover box by hand. We never had to use it. What continues to amaze me is how often people implement these complex and difficult to manage redundancy setups (sometimes referred to NOT as a cluster, but a cluster f**k) when, if they would just choose a reliable platform, they would be better off. -Original Message- if anything wrong than this so call fault monitoring will do shutdown abort and release the mount points for second node to do startup pfile= -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Clone Production Server to Stand by Server on 8.1.7 on Win 2k
Hello We currently have two Identical servers (identical in terms of both Hardware and SW). We run Oracle 7.3.4.0 (Workgroup server) on Win NT 4 on both the servers. We call them as Production Server and Stand by server. The Stand by server is passive in nature (i.e. does not do anything). Every night a batch process shuts down Oracle instance on both the machines and copies over all the files (Data, log, ctl etc) from Production Server to the Stand by server (Drive to drive, directory to directory...) In case the Production Server fails, we simply switch over the users (with a different alias to the stand by server) and they are back in business. Now, we are thinking of migrating to 8.1.7, however while trying to install this version, one needs to specify a Global name which I believe has to be unique on the network. So will the same process that I used to run (i.e. copy all files over from production to stand by ) work??? I guess both my servers will now have to have separate/unique Global Names. Is there any other approach that any of you can suggest??? TIA Arif -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arif Khan (GWL) 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: OCP Exams - What to study out of?
Title: RE: OCP Exams - What to study out of? Guys, anyone know about an application called Cloverleaf? Basically, my organization is pursuing using Cloverleaf as the data integration sole application of choice. To me Unix/SQL*Loader and native tools for the various RDBMS usually are more straightforward and provide simplication and less proprietary coding and it is easier for an organization to find others who have those type of skills. Any ideas guys? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 9:18 AM To: Multiple recipients of list ORACLE-L Subject: RE: OCP Exams - What to study out of? Denham, I have just passed my 8i upgrade exam and added some notes to a page about getting OCP certification. It can be accessed on http://www.hcresources.co.uk/ocp.htm http://www.hcresources.co.uk/ocp.htm Hope you find it interesting John -Original Message- Sent: 05 November 2002 12:09 To: Multiple recipients of list ORACLE-L Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly Insect Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-02050
On Tue, Nov 05, 2002 at 06:33:44AM -0800, [EMAIL PROTECTED] wrote: Ray, I think you need to check DBA_2PC_PENDING on both the local and remote database to see if anything is still in there. That gives the osuser, terminal and host name so you may be able to get something from that. Of course if there is nothing in that table then the transaction has been rolled back already Right, nothing there, so I was wondering if there was a way to chase the transaction id to app, such as archivelog. 8.1.7.4 here. Thanks. John -Original Message- Sent: 05 November 2002 13:43 To: Multiple recipients of list ORACLE-L ORA-02050: transaction 8.82.26033 rolled back, some remote DBs may be in-doubt The transaction seems to have completely rolled back. My question is, is there any way to relate transaction id 8.82.26033 to an application or table row or something at a higher layer? Thanks. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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 Production Server to Stand by Server on 8.1.7 on Win 2k
Hello You did not write if you bring both databases on every night or the backup is down until needed. If it is down you have no problem. If you bring both up you can change the global name : Alter database rename global_name to backupdb; BTW - I am not sure that 8.1.7 need global_name=true. We are on 8.1.6 on NT and we do not use global names = true. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 4:53 PM Hello We currently have two Identical servers (identical in terms of both Hardware and SW). We run Oracle 7.3.4.0 (Workgroup server) on Win NT 4 on both the servers. We call them as Production Server and Stand by server. The Stand by server is passive in nature (i.e. does not do anything). Every night a batch process shuts down Oracle instance on both the machines and copies over all the files (Data, log, ctl etc) from Production Server to the Stand by server (Drive to drive, directory to directory...) In case the Production Server fails, we simply switch over the users (with a different alias to the stand by server) and they are back in business. Now, we are thinking of migrating to 8.1.7, however while trying to install this version, one needs to specify a Global name which I believe has to be unique on the network. So will the same process that I used to run (i.e. copy all files over from production to stand by ) work??? I guess both my servers will now have to have separate/unique Global Names. Is there any other approach that any of you can suggest??? TIA Arif -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arif Khan (GWL) 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.com -- 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: OCP Exams - What to study out of?
Eva - Everything I've heard says that all the exam questions come out of the Oracle University class Student Guides. To put it bluntly, your goal is to pass the exam. You could study the Oracle manuals, but there is a lot of material to cover and what strikes you as important might not be covered on the exam, and vice-versa. Essentially you are getting two levels of filtering. Somebody read the manuals and used that to prepare student guides, then a group of Oracle instructors read the student guide and prepared questions. I think that most of the exam guides were prepared from the Oracle Student Guides and the author has at least taken the exam, so they have a general idea of how the test is approached. Everyone has their favorite exam preparation book and I consider this a worthwhile investment, I bought Couchman - http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL0 H http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL 0Hsourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=007 2133414 sourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=00721 33414 Two issues: Which exam track are you planning to take? 9i? 8i? The advice I received is don't take the SQL exam as your first exam. Take the DBA exam first. The SQL exam is reported to be quite hard because it covers all the odd SQL functions and can really catch you unawares. As a practicing DBA you should find the DBA exam pretty easy. I didn't personally take that path for my own reasons, but I still consider it good advice. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 05, 2002 6:09 AM To: Multiple recipients of list ORACLE-L Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly Insect Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Enterprise Manager Console - Export problem
Hello I run Oracle 8.1.6 on Win 2k (Both standard ed.). I am trying to run the export wizard through the Enterprise Manager Console by doing the following: Under Database folder, I right click the Global Name (say ORCL) - Data Management - Export ... and it comes back with the error Either Preferred Credentials are not set or the username and/or password are invalid for this database and node. You must set the Preferred Credentials for the database and node in the Oracle Enterprise Manager Console OK I have logged in to EM-Console with SYSMAN user. Under System - Preferences - Preferred Credential, for ORCL, I have tried using SYSMAN, SYSTEM and SYS with Role as NORMAL, SYSDBA SYSOPER. I have no credentials setup for the Node (say ORATEST). I am pretty new to Oracle 8i and hence may not be taking the right steps required. TIA Arif -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arif Khan (GWL) 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-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE
Yechiel, What appears in the archive log? There is very likely to be some OS errors included. Jared Yechiel Adar [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 12:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE Hello All Oracle 8.1.6.3.4 on NT. I got ora-19502 - can not write archive log, and users could not logged on. I can logon only as internal. We had the same problem a few days ago. The technical support people checked the raid 5 disks and did not find any I/O errors. There is a lot of free space on the drive. Has anybody encountered this problem? Do you know what can cause it? What I need to do in order to prevent this from happening again? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE
Here is parts of the alert log: Current log# 6 seq# 4153 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1356.ORA Sun Nov 03 11:03:34 2002 ARC0: Beginning to archive log# 5 seq# 4152 ARC0: Completed archiving log# 5 seq# 4152 Sun Nov 03 11:10:33 2002 Thread 1 advanced to log sequence 4154 Current log# 7 seq# 4154 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1357.ORA Sun Nov 03 11:10:33 2002 ARC2: Beginning to archive log# 6 seq# 4153 ARC2: Completed archiving log# 6 seq# 4153 Sun Nov 03 11:20:50 2002 Thread 1 advanced to log sequence 4155 Current log# 8 seq# 4155 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1358.ORA Sun Nov 03 11:20:50 2002 ARC1: Beginning to archive log# 7 seq# 4154 ARC1: I/O error 19502 archiving log 7 to 'E:\ORACLE\ORADATA\MUSK135\DATABASE\ARCHIVE\M135T001S04154.ARC' ARC1: Archiving not possible: error count exceeded ARC1: Failed to archive log# 7 seq# 4154 ARCH: Archival stopped, error occurred. Will continue retrying ARCH: ORA-16038: log 7 sequence# 4154 cannot be archived ORA-19502: write error on file , blockno (blocksize=) ORA-00312: online log 7 thread 1: 'D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1357.ORA' Sun Nov 03 11:28:06 2002 Thread 1 advanced to log sequence 4156 Current log# 3 seq# 4156 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1353.ORA Sun Nov 03 11:28:06 2002 ARC2: Beginning to archive log# 7 seq# 4154 ARC2: Archiving not possible: No primary destinations ARC2: Failed to archive log# 7 seq# 4154 ARCH: Archival stopped, error occurred. Will continue retrying ARCH: ORA-16014: log 7 sequence# 4154 not archived, no available destinations ORA-00312: online log 7 thread 1: 'D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1357.ORA' Sun Nov 03 11:38:54 2002 Thread 1 advanced to log sequence 4157 Current log# 9 seq# 4157 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOG9.ORA Sun Nov 03 11:38:55 2002 ARC0: Beginning to archive log# 7 seq# 4154 ARC0: Archiving not possible: No primary destinations ARC0: Failed to archive log# 7 seq# 4154 Sun Nov 03 11:39:07 2002 Thread 1 advanced to log sequence 4158 Current log# 10 seq# 4158 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOG10.ORA Sun Nov 03 11:39:07 2002 ARC1: Beginning to archive log# 7 seq# 4154 ARC1: Archiving not possible: No primary destinations ARC1: Failed to archive log# 7 seq# 4154 Yechiel Adar Mehish - Original Message - To: ORACLE-L [EMAIL PROTECTED] Cc: Yechiel Adar [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 7:31 PM Yechiel, What about those alert log entries? Jared Yechiel Adar [EMAIL PROTECTED] 11/05/2002 10:38 AM To: ORACLE-L [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:Re: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE Hello Charlie and Jared Thanks for the reply. As I mentioned in my mail there is a lot of free space on the disk and the database is with auto archive. Our problem is that the ARCH process gives an error message (ora 19502) and stop. There are messages on the console about error trying to write the archive log, like when the disk is full. Then the redo log will fill up and the instance will stop. After I connect as internal and do: archive log stop; archive log all; archive log start; everything is back to normal. Yechiel Adar Mehish - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 4:48 PM Subject: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE My first guess is that the instance has archiving enable, but the ARCHIVER is/was not started. The DB will run OK until all the redo logfiles have been filled. At this point the instance will simply cease to process any additional transactions until it has space to which to write additional redo logfile information. There should be an entry in the alert_SID.log about what is happening why. - Forwarded by Charlie Mengler/THD on 11/05/2002 06:45 AM - Yechiel Adar [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .il cc: Sent by: Subject: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE [EMAIL PROTECTED] 11/05/2002 12:58 AM Please respond to ORACLE-L Hello All Oracle 8.1.6.3.4 on NT. I got ora-19502 - can not write archive log, and users could not logged on. I can logon only as internal. We had the same problem a few days ago. The technical support people checked the raid 5 disks and did not find any I/O errors. There is a lot of free space on the drive. Has anybody encountered this problem? Do you know what can cause it? What I need to do in order to prevent this from happening
SQL Brain Teaser Challenge
Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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: suggestion w/c platforms to choose from...
How about a 400 CPU nCube running Oracle 9i RAC? If they still support nCube that is. ( Larry owns nCube ) Seriously though, does your app maintain HR data for 500 people, or is it a 20TB Data Warehouse? These apps may require different HW configurations. Jared grace [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 04:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:suggestion w/c platforms to choose from... hi, can anyone suggestion w/c platform should i used to run oracle? wat are the things to consider in choosing platform? thanks Best regards, Grace Lim MIS Department Suy Sing Comm'l Corp. T- (632)-2474134 F- (632)-2474160 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: grace 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE
Hello Charlie and Jared Thanks for the reply. As I mentioned in my mail there is a lot of free space on the disk and the database is with auto archive. Our problem is that the ARCH process gives an error message (ora 19502) and stop. There are messages on the console about error trying to write the archive log, like when the disk is full. Then the redo log will fill up and the instance will stop. After I connect as internal and do: archive log stop; archive log all; archive log start; everything is back to normal. Yechiel Adar Mehish - Original Message - To: [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 4:48 PM My first guess is that the instance has archiving enable, but the ARCHIVER is/was not started. The DB will run OK until all the redo logfiles have been filled. At this point the instance will simply cease to process any additional transactions until it has space to which to write additional redo logfile information. There should be an entry in the alert_SID.log about what is happening why. - Forwarded by Charlie Mengler/THD on 11/05/2002 06:45 AM - Yechiel Adar [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .il cc: Sent by: Subject: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE [EMAIL PROTECTED] 11/05/2002 12:58 AM Please respond to ORACLE-L Hello All Oracle 8.1.6.3.4 on NT. I got ora-19502 - can not write archive log, and users could not logged on. I can logon only as internal. We had the same problem a few days ago. The technical support people checked the raid 5 disks and did not find any I/O errors. There is a lot of free space on the drive. Has anybody encountered this problem? Do you know what can cause it? What I need to do in order to prevent this from happening again? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge SELECT LEVEL, treenode.* FROM treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid ORDER SIBLINGS BY PARENTid , nodeorder Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Subject: SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentid number not null, nodeorder number not null, description varchar2(20) null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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 are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Clone Production Server to Stand by Server on 8.1.7 on Win 2k
First off, your process is doing a lot of unnecessary work. Standby databases are available in 7.3.4. I believe that would be somewhat simpler than your current procedure. I haven't tried it though, so I could be wrong. Even with your current procedure, you don't need to copy all of the files, most of the time anyway. Build your standby database, put it in recovery mode, and just keep applying archive log files to it. You may need to rebuild when a datafile is added to a tablespace, not sure. In 8i, why not just use standby database? Jared Arif Khan (GWL) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 06:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Clone Production Server to Stand by Server on 8.1.7 on Win 2k Hello We currently have two Identical servers (identical in terms of both Hardware and SW). We run Oracle 7.3.4.0 (Workgroup server) on Win NT 4 on both the servers. We call them as Production Server and Stand by server. The Stand by server is passive in nature (i.e. does not do anything). Every night a batch process shuts down Oracle instance on both the machines and copies over all the files (Data, log, ctl etc) from Production Server to the Stand by server (Drive to drive, directory to directory...) In case the Production Server fails, we simply switch over the users (with a different alias to the stand by server) and they are back in business. Now, we are thinking of migrating to 8.1.7, however while trying to install this version, one needs to specify a Global name which I believe has to be unique on the network. So will the same process that I used to run (i.e. copy all files over from production to stand by ) work??? I guess both my servers will now have to have separate/unique Global Names. Is there any other approach that any of you can suggest??? TIA Arif -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arif Khan (GWL) 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Read Only Login with Source Visibility
Rewrite the all_source view, and I believe the all_objects view. It's a pain in the rear, but it can be done. It also tends to break when you upgrade. I did this once on Oracle 7, there may be some other workaround now on 8i. Jared Bill Buchan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 04:48 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Read Only Login with Source Visibility Hi all, I am trying to create a read-only login, RO_USER which can do the following: 1. See all tables, views, constraints etc. in one *specific* other schema, APP. 2. See all the source for PL/SQL objects in APP. The first bit is easy: GRANT SELECT on tables, views to RO_USER. Not sure about the second bit. I have tried granting CREATE ANY PROCEDURE and CREATE ANY TRIGGER to RO_USER and using a DDL trigger to prevent these privileges actually being used to create procedures/triggers. This works but does not restrict the source visibility to APP. I have other schemas where I do not want RO_USER to see the source. My other concern is granting SELECT on sequences as this means that they can select nextval from them and hence increment the numbers (not quite read-only!) Any suggestions for fixing these problems would be much appreciated! Thanks - Bill. PS. This is on 8i. -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
BMC's dtoarchmon goes to lunch
Hi. We are running Oracle 8172 on Dynix 452 and backing it up via BMC's obacktrac 3.0.0.6. Since we upgraded the OS and obakctrack dtoarchmon started dying. The process either disappears altogether or doesn't wake up to start an archive backup when it should. The dtoachmon -ping shows that everything is OK. Has anyone experienced this and has any insight to a possible solution? thanks Gene __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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).
dispatcher timer virtual circuit status top of wait events/no M
Hi All, Above two events are at the top of my wait events in one of my databases. Does anybody have an idea what would be the reason? Oracle 8.1.6.0.0 Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE
Yechiel, No OS errors here. You may want to look at the LGWR trace file in the BDUMP directory. 2 things 1) Have the SA check for device errors. Something in the IO system is not working. 2) Open a TAR. If you have another location to redirect arch logs to, you may want to do that. The following 2 notes you will find useful for redirecting archive logs to another location without bouncing the database. 135866.1 74324.1 HTH Jared Yechiel Adar [EMAIL PROTECTED] 11/05/2002 10:56 AM To: ORACLE-L [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:Re: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE Here is parts of the alert log: Current log# 6 seq# 4153 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1356.ORA Sun Nov 03 11:03:34 2002 ARC0: Beginning to archive log# 5 seq# 4152 ARC0: Completed archiving log# 5 seq# 4152 Sun Nov 03 11:10:33 2002 Thread 1 advanced to log sequence 4154 Current log# 7 seq# 4154 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1357.ORA Sun Nov 03 11:10:33 2002 ARC2: Beginning to archive log# 6 seq# 4153 ARC2: Completed archiving log# 6 seq# 4153 Sun Nov 03 11:20:50 2002 Thread 1 advanced to log sequence 4155 Current log# 8 seq# 4155 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1358.ORA Sun Nov 03 11:20:50 2002 ARC1: Beginning to archive log# 7 seq# 4154 ARC1: I/O error 19502 archiving log 7 to 'E:\ORACLE\ORADATA\MUSK135\DATABASE\ARCHIVE\M135T001S04154.ARC' ARC1: Archiving not possible: error count exceeded ARC1: Failed to archive log# 7 seq# 4154 ARCH: Archival stopped, error occurred. Will continue retrying ARCH: ORA-16038: log 7 sequence# 4154 cannot be archived ORA-19502: write error on file , blockno (blocksize=) ORA-00312: online log 7 thread 1: 'D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1357.ORA' Sun Nov 03 11:28:06 2002 Thread 1 advanced to log sequence 4156 Current log# 3 seq# 4156 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1353.ORA Sun Nov 03 11:28:06 2002 ARC2: Beginning to archive log# 7 seq# 4154 ARC2: Archiving not possible: No primary destinations ARC2: Failed to archive log# 7 seq# 4154 ARCH: Archival stopped, error occurred. Will continue retrying ARCH: ORA-16014: log 7 sequence# 4154 not archived, no available destinations ORA-00312: online log 7 thread 1: 'D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1357.ORA' Sun Nov 03 11:38:54 2002 Thread 1 advanced to log sequence 4157 Current log# 9 seq# 4157 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOG9.ORA Sun Nov 03 11:38:55 2002 ARC0: Beginning to archive log# 7 seq# 4154 ARC0: Archiving not possible: No primary destinations ARC0: Failed to archive log# 7 seq# 4154 Sun Nov 03 11:39:07 2002 Thread 1 advanced to log sequence 4158 Current log# 10 seq# 4158 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOG10.ORA Sun Nov 03 11:39:07 2002 ARC1: Beginning to archive log# 7 seq# 4154 ARC1: Archiving not possible: No primary destinations ARC1: Failed to archive log# 7 seq# 4154 Yechiel Adar Mehish - Original Message - To: ORACLE-L [EMAIL PROTECTED] Cc: Yechiel Adar [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 7:31 PM Yechiel, What about those alert log entries? Jared Yechiel Adar [EMAIL PROTECTED] 11/05/2002 10:38 AM To: ORACLE-L [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:Re: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE Hello Charlie and Jared Thanks for the reply. As I mentioned in my mail there is a lot of free space on the disk and the database is with auto archive. Our problem is that the ARCH process gives an error message (ora 19502) and stop. There are messages on the console about error trying to write the archive log, like when the disk is full. Then the redo log will fill up and the instance will stop. After I connect as internal and do: archive log stop; archive log all; archive log start; everything is back to normal. Yechiel Adar Mehish - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 4:48 PM Subject: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE My first guess is that the instance has archiving enable, but the ARCHIVER is/was not started. The DB will run OK until all the redo logfiles have been filled. At this point the instance will simply cease to process any additional transactions until it has space to which to write additional redo logfile information. There should be an entry in the alert_SID.log about what is happening why. - Forwarded by Charlie Mengler/THD on 11/05/2002 06:45 AM - Yechiel Adar [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .il cc: Sent by: Subject: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE
RE: SQL Brain Teaser Challenge
I get an error on 8.1.7.2. Is siblings new? SQL l 1 SELECT LEVEL, treenode.* 2FROM treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* ORDER SIBLINGS BY PARENTid , nodeorder SQL / ORDER SIBLINGS BY PARENTid , nodeorder * ERROR at line 5: ORA-00924: missing BY keyword -Original Message- Sent: Tuesday, November 05, 2002 11:02 AM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT LEVEL, treenode.* FROM treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid ORDER SIBLINGS BY PARENTid , nodeorder Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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.com -- Author: Orr, Steve 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-02050
There is another view, DBA_2PC_NEIGHBORS. Maybe there is some info there. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 5:58 PM On Tue, Nov 05, 2002 at 06:33:44AM -0800, [EMAIL PROTECTED] wrote: Ray, I think you need to check DBA_2PC_PENDING on both the local and remote database to see if anything is still in there. That gives the osuser, terminal and host name so you may be able to get something from that. Of course if there is nothing in that table then the transaction has been rolled back already Right, nothing there, so I was wondering if there was a way to chase the transaction id to app, such as archivelog. 8.1.7.4 here. Thanks. John -Original Message- Sent: 05 November 2002 13:43 To: Multiple recipients of list ORACLE-L ORA-02050: transaction 8.82.26033 rolled back, some remote DBs may be in-doubt The transaction seems to have completely rolled back. My question is, is there any way to relate transaction id 8.82.26033 to an application or table row or something at a higher layer? Thanks. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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.com -- 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: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE
Yechiel, What about those alert log entries? Jared Yechiel Adar [EMAIL PROTECTED] 11/05/2002 10:38 AM To: ORACLE-L [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:Re: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE Hello Charlie and Jared Thanks for the reply. As I mentioned in my mail there is a lot of free space on the disk and the database is with auto archive. Our problem is that the ARCH process gives an error message (ora 19502) and stop. There are messages on the console about error trying to write the archive log, like when the disk is full. Then the redo log will fill up and the instance will stop. After I connect as internal and do: archive log stop; archive log all; archive log start; everything is back to normal. Yechiel Adar Mehish - Original Message - To: [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 4:48 PM My first guess is that the instance has archiving enable, but the ARCHIVER is/was not started. The DB will run OK until all the redo logfiles have been filled. At this point the instance will simply cease to process any additional transactions until it has space to which to write additional redo logfile information. There should be an entry in the alert_SID.log about what is happening why. - Forwarded by Charlie Mengler/THD on 11/05/2002 06:45 AM - Yechiel Adar [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .il cc: Sent by: Subject: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE [EMAIL PROTECTED] 11/05/2002 12:58 AM Please respond to ORACLE-L Hello All Oracle 8.1.6.3.4 on NT. I got ora-19502 - can not write archive log, and users could not logged on. I can logon only as internal. We had the same problem a few days ago. The technical support people checked the raid 5 disks and did not find any I/O errors. There is a lot of free space on the drive. Has anybody encountered this problem? Do you know what can cause it? What I need to do in order to prevent this from happening again? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE
Thanks Jared. I forgot to mention that the SA checked for device errors. None found. A tar has been opened is already on it's way to higher support level. This problem occurred again today, third time in two weeks. Also, the database is up, only the ARCH process is dead. I started the ARCH (archive log stop; archive log all; archive log start;) and the database continued to work. Yechiel Adar Mehish - Original Message - To: ORACLE-L [EMAIL PROTECTED] Cc: Yechiel Adar [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 8:17 PM Yechiel, No OS errors here. You may want to look at the LGWR trace file in the BDUMP directory. 2 things 1) Have the SA check for device errors. Something in the IO system is not working. 2) Open a TAR. If you have another location to redirect arch logs to, you may want to do that. The following 2 notes you will find useful for redirecting archive logs to another location without bouncing the database. 135866.1 74324.1 HTH Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: SQL Brain Teaser Challenge
Does an in-line view do the trick? select * from (select id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid) order by nodeorder; Jay [EMAIL PROTECTED] 11/05/02 12:24PM Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Read Only Login with Source Visibility
better yet - create a local copy of it, and place a private synonym in their account to point to it. really sneaky. -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Rewrite the all_source view, and I believe the all_objects view. It's a pain in the rear, but it can be done. It also tends to break when you upgrade. I did this once on Oracle 7, there may be some other workaround now on 8i. Jared Bill Buchan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 04:48 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Read Only Login with Source Visibility Hi all, I am trying to create a read-only login, RO_USER which can do the following: 1. See all tables, views, constraints etc. in one *specific* other schema, APP. 2. See all the source for PL/SQL objects in APP. The first bit is easy: GRANT SELECT on tables, views to RO_USER. Not sure about the second bit. I have tried granting CREATE ANY PROCEDURE and CREATE ANY TRIGGER to RO_USER and using a DDL trigger to prevent these privileges actually being used to create procedures/triggers. This works but does not restrict the source visibility to APP. I have other schemas where I do not want RO_USER to see the source. My other concern is granting SELECT on sequences as this means that they can select nextval from them and hence increment the numbers (not quite read-only!) Any suggestions for fixing these problems would be much appreciated! Thanks - Bill. PS. This is on 8i. -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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: OCP Exams - What to study out of?
Title: RE: OCP Exams - What to study out of? I took the SQL exam first and had no problems although I have heard others have. I think it has to do with how I learned - I forced myself to use SQL before getting hooked into a GUI tool of any kind. In the long-run that is a good approach as you will always have that but might not always have the GUI. -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 12:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: OCP Exams - What to study out of? Eva - Everything I've heard says that all the exam questions come out of the Oracle University class Student Guides. To put it bluntly, your goal is to pass the exam. You could study the Oracle manuals, but there is a lot of material to cover and what strikes you as important might not be covered on the exam, and vice-versa. Essentially you are getting two levels of filtering. Somebody read the manuals and used that to prepare student guides, then a group of Oracle instructors read the student guide and prepared questions. I think that most of the exam guides were prepared from the Oracle Student Guides and the author has at least taken the exam, so they have a general idea of how the test is approached. Everyone has their favorite exam preparation book and I consider this a worthwhile investment, I bought Couchman - http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL0 H http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL 0Hsourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=007 2133414 sourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=00721 33414 Two issues: Which exam track are you planning to take? 9i? 8i? The advice I received is don't take the SQL exam as your first exam. Take the DBA exam first. The SQL exam is reported to be quite hard because it covers all the odd SQL functions and can really catch you unawares. As a practicing DBA you should find the DBA exam pretty easy. I didn't personally take that path for my own reasons, but I still consider it good advice. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 05, 2002 6:09 AM To: Multiple recipients of list ORACLE-L Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly Insect Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: SQL Brain Teaser Challenge
Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge Steve, I'm busted! You caught me cheating. Still you must admit it was an interesting use of TRANSLATE given the data in the DESCRIPTION field. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Orr, Steve [SMTP:[EMAIL PROTECTED]] Well it worked but it doesn't follow the rules. The description can change so it should not be sorted on. Consider this: update treenode set description='2nd item, 3rd folder' where id=8; select * from treenode order by translate(description || 0, '0123456789abcdefghijklmnopqrstuvwxyz ', '0123456789'); ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 8 7 1 2nd item, 3rd folder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 9 rows selected. -Original Message- From: Whittle Jerome Contr NCI [mailto:[EMAIL PROTECTED]] select * from treenode order by translate(description || 0, '0123456789abcdefghijklmnopqrstuvwxyz ', '0123456789') Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Orr, Steve [SMTP:[EMAIL PROTECTED]] Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentid number not null, nodeorder number not null, description varchar2(20) null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana
Re: SQL Brain Teaser Challenge
Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE
Did I say LGWR trace file? I meant ARCH trace file, sorry. jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 10:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE Yechiel, No OS errors here. You may want to look at the LGWR trace file in the BDUMP directory. 2 things 1) Have the SA check for device errors. Something in the IO system is not working. 2) Open a TAR. If you have another location to redirect arch logs to, you may want to do that. The following 2 notes you will find useful for redirecting archive logs to another location without bouncing the database. 135866.1 74324.1 HTH Jared Yechiel Adar [EMAIL PROTECTED] 11/05/2002 10:56 AM To: ORACLE-L [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:Re: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE Here is parts of the alert log: Current log# 6 seq# 4153 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1356.ORA Sun Nov 03 11:03:34 2002 ARC0: Beginning to archive log# 5 seq# 4152 ARC0: Completed archiving log# 5 seq# 4152 Sun Nov 03 11:10:33 2002 Thread 1 advanced to log sequence 4154 Current log# 7 seq# 4154 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1357.ORA Sun Nov 03 11:10:33 2002 ARC2: Beginning to archive log# 6 seq# 4153 ARC2: Completed archiving log# 6 seq# 4153 Sun Nov 03 11:20:50 2002 Thread 1 advanced to log sequence 4155 Current log# 8 seq# 4155 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1358.ORA Sun Nov 03 11:20:50 2002 ARC1: Beginning to archive log# 7 seq# 4154 ARC1: I/O error 19502 archiving log 7 to 'E:\ORACLE\ORADATA\MUSK135\DATABASE\ARCHIVE\M135T001S04154.ARC' ARC1: Archiving not possible: error count exceeded ARC1: Failed to archive log# 7 seq# 4154 ARCH: Archival stopped, error occurred. Will continue retrying ARCH: ORA-16038: log 7 sequence# 4154 cannot be archived ORA-19502: write error on file , blockno (blocksize=) ORA-00312: online log 7 thread 1: 'D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1357.ORA' Sun Nov 03 11:28:06 2002 Thread 1 advanced to log sequence 4156 Current log# 3 seq# 4156 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1353.ORA Sun Nov 03 11:28:06 2002 ARC2: Beginning to archive log# 7 seq# 4154 ARC2: Archiving not possible: No primary destinations ARC2: Failed to archive log# 7 seq# 4154 ARCH: Archival stopped, error occurred. Will continue retrying ARCH: ORA-16014: log 7 sequence# 4154 not archived, no available destinations ORA-00312: online log 7 thread 1: 'D:\ORACLE\ORADATA\MUSK135\DATABASE\LOGM1357.ORA' Sun Nov 03 11:38:54 2002 Thread 1 advanced to log sequence 4157 Current log# 9 seq# 4157 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOG9.ORA Sun Nov 03 11:38:55 2002 ARC0: Beginning to archive log# 7 seq# 4154 ARC0: Archiving not possible: No primary destinations ARC0: Failed to archive log# 7 seq# 4154 Sun Nov 03 11:39:07 2002 Thread 1 advanced to log sequence 4158 Current log# 10 seq# 4158 mem# 0: D:\ORACLE\ORADATA\MUSK135\DATABASE\LOG10.ORA Sun Nov 03 11:39:07 2002 ARC1: Beginning to archive log# 7 seq# 4154 ARC1: Archiving not possible: No primary destinations ARC1: Failed to archive log# 7 seq# 4154 Yechiel Adar Mehish - Original Message - To: ORACLE-L [EMAIL PROTECTED] Cc: Yechiel Adar [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 7:31 PM Yechiel, What about those alert log entries? Jared Yechiel Adar [EMAIL PROTECTED] 11/05/2002 10:38 AM To: ORACLE-L [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:Re: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE Hello Charlie and Jared Thanks for the reply. As I mentioned in my mail there is a lot of free space on the disk and the database is with auto archive. Our problem is that the ARCH process gives an error message (ora 19502) and stop. There are messages on the console about error trying to write the archive log, like when the disk is full. Then the redo log will fill up and the instance will stop. After I connect as internal and do: archive log stop; archive log all; archive log start; everything is back to normal. Yechiel Adar Mehish - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 4:48 PM Subject: ORA-19502 ON RAID 5 DISKS WITH ENOUGH FREE SPACE My first guess is that the instance has archiving enable, but the ARCHIVER is/was not started. The DB will run OK until all the redo logfiles have been filled. At this point the instance will simply cease to process any additional transactions until it has space to which to write additional redo logfile information. There should be an entry in the alert_SID.log about what is happening why. - Forwarded by Charlie Mengler/THD on 11/05/2002
RE: SQL Brain Teaser Challenge
I love a good challenge. Since you cannot sort on a hierarchical query, you have to use an inline query... select id, parentid, nodeorder, description from (select id, parentid, nodeorder, description from treenod start with parentid=0 connect by prior id = parentid) order by parentid, nodeorder; ID PARENTID NODEORDER DESCRIPTION - -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 7 1 2 3rd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 Dan Fink -Original Message- Sent: Tuesday, November 05, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge select * from treenode order by translate(description || 0, '0123456789abcdefghijklmnopqrstuvwxyz ', '0123456789') Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Orr, Steve [SMTP:[EMAIL PROTECTED]] Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentid number not null, nodeorder number not null, description varchar2(20) null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana
RE: SQL Brain Teaser Challenge
How about... SELECT t.id , t.parentid , t.nodeorder , t.description FROM treenode t CONNECT BY t.parentid = PRIOR t.id START WITH t.description = 'top folder' ORDER BY NVL ( TRIM ( TRANSLATE ( LOWER (t.description) , 'abcdefghijklmnopqrstuvwxyz' , ' ' ) ) , 0 ); Regards! Greg -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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.com -- Author: Mirsky, Greg INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge Steve, I did try it ... oraclei@rhea-ACPT1 sys SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 5 14:18:02 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected. SQL conn system Enter password: Connected. SQL SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 2:15 PM To: Jamadagni, Rajendra; '[EMAIL PROTECTED]' Subject: RE: SQL Brain Teaser Challenge Nope... SQL l 1 SELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4* CONNECT BY PRIOR ID = parentid SQL / FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) * ERROR at line 2: ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc. I supplied the DDL/DML so you could test on your own systems. -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 12:07 PM To: '[EMAIL PROTECTED]'; Orr, Steve Subject: RE: SQL Brain Teaser Challenge SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid Does this work? Also see http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:921234804 9 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge Whoa... A query that works on 9i but fails on 8i !!! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Tuesday, November 05, 2002 12:19 PMTo: Orr, Steve; '[EMAIL PROTECTED]'Subject: RE: SQL Brain Teaser Challenge Steve, I did try it ... oraclei@rhea-ACPT1 sys SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 5 14:18:02 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected. SQL conn system Enter password: Connected. SQL SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 2:15 PM To: Jamadagni, Rajendra; '[EMAIL PROTECTED]' Subject: RE: SQL Brain Teaser Challenge Nope... SQL l 1 SELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4* CONNECT BY PRIOR ID = parentid SQL / FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) * ERROR at line 2: ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc. I supplied the DDL/DML so you could test on your own systems. -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 12:07 PM To: '[EMAIL PROTECTED]'; Orr, Steve Subject: RE: SQL Brain Teaser Challenge SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid Does this work? Also see http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:921234804 9 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
RE: OCP Exams - What to study out of?
The Cloverleaf product I know is from Quovadx. One of its usages it provides an interface between Healthcare systems and Oracle(many other DBMS). For ex. we use to interface with hospitals sending data in standard HL7 format and Cloverleaf interprets HL7 format and data is loaded directly into Oracle for further processing. See http://www.quovadx.com/ for more info Rick [EMAIL PROTECTED] tate.fl.usTo: Multiple recipients of list ORACLE-L Sent by: [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject: RE: OCP Exams - What to study out of? 11/05/2002 10:23 AM Please respond to ORACLE-L Guys, anyone know about an application called Cloverleaf? Basically, my organization is pursuing using Cloverleaf as the data integration sole application of choice. To me Unix/SQL*Loader and native tools for the various RDBMS usually are more straightforward and provide simplication and less proprietary coding and it is easier for an organization to find others who have those type of skills. Any ideas guys? -Original Message- Sent: Tuesday, November 05, 2002 9:18 AM To: Multiple recipients of list ORACLE-L Denham, I have just passed my 8i upgrade exam and added some notes to a page about getting OCP certification. It can be accessed on http://www.hcresources.co.uk/ocp.htm http://www.hcresources.co.uk/ocp.htm Hope you find it interesting John -Original Message- Sent: 05 November 2002 12:09 To: Multiple recipients of list ORACLE-L Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly Insect Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City
RE: SQL Brain Teaser Challenge
You still haven't answered the challenge... your results are in a different order than the desired results I gave at the bottom. The idea is to have the children appearing immediately after the parents in the correct order. -Original Message- Sent: Tuesday, November 05, 2002 11:57 AM To: '[EMAIL PROTECTED]'; Orr, Steve I love a good challenge. Since you cannot sort on a hierarchical query, you have to use an inline query... select id, parentid, nodeorder, description from (select id, parentid, nodeorder, description from treenod start with parentid=0 connect by prior id = parentid) order by parentid, nodeorder; ID PARENTID NODEORDER DESCRIPTION - -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 7 1 2 3rd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 Dan Fink -Original Message- Sent: Tuesday, November 05, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Well it worked but it doesn't follow the rules. The description can change so it should not be sorted on. Consider this: update treenode set description='2nd item, 3rd folder' where id=8; select * from treenode order by translate(description || 0, '0123456789abcdefghijklmnopqrstuvwxyz ', '0123456789'); ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 8 7 1 2nd item, 3rd folder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 9 rows selected. -Original Message- Sent: Tuesday, November 05, 2002 11:52 AM To: [EMAIL PROTECTED] Cc: Orr, Steve select * from treenode order by translate(description || 0, '0123456789abcdefghijklmnopqrstuvwxyz ', '0123456789') Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid Does this work? Also see http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:9212348049 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *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.*1
RE: SQL Brain Teaser Challenge
Nope... (select id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid) order by nodeorder; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 5 7 0 folder 3 item 1 8 7 1 2nd item, 3rd folder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 7 1 2 3rd subfolder 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 -Original Message- Sent: Tuesday, November 05, 2002 11:52 AM To: [EMAIL PROTECTED]; Orr, Steve Does an in-line view do the trick? select * from (select id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid) order by nodeorder; Jay [EMAIL PROTECTED] 11/05/02 12:24PM Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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
RE: SQL Brain Teaser Challenge
Nope... SQL l 1 SELECT * 2FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4* CONNECT BY PRIOR ID = parentid SQL / FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) * ERROR at line 2: ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc. I supplied the DDL/DML so you could test on your own systems. -Original Message- Sent: Tuesday, November 05, 2002 12:07 PM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid Does this work? Also see http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:921234804 9 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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-02050
On Tue, Nov 05, 2002 at 09:24:04AM -0800, Yechiel Adar wrote: There is another view, DBA_2PC_NEIGHBORS. Maybe there is some info there. No, the transaction was rolled back completely, or perhaps it resynced when the remote db recovered and thus covered the tracks. This is a good thing. I am wondering if I can find the app that was hit. I'm just curious, not trying to recover. Otherwise, flames would be coming from my hair. ;) Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 5:58 PM On Tue, Nov 05, 2002 at 06:33:44AM -0800, [EMAIL PROTECTED] wrote: Ray, I think you need to check DBA_2PC_PENDING on both the local and remote database to see if anything is still in there. That gives the osuser, terminal and host name so you may be able to get something from that. Of course if there is nothing in that table then the transaction has been rolled back already Right, nothing there, so I was wondering if there was a way to chase the transaction id to app, such as archivelog. 8.1.7.4 here. Thanks. John -Original Message- Sent: 05 November 2002 13:43 To: Multiple recipients of list ORACLE-L ORA-02050: transaction 8.82.26033 rolled back, some remote DBs may be in-doubt The transaction seems to have completely rolled back. My question is, is there any way to relate transaction id 8.82.26033 to an application or table row or something at a higher layer? Thanks. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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.com -- 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: OCP Exams - What to study out of?
I agree that the SQL exam is very difficult. I've taken it twice and missed passing by just a couple of points. But I will persist. Interestingly enough when I got my APICS CPIM certification I took six exams and passed them all on the first try. The passing grade is 90%. Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 11:03 AM Eva - Everything I've heard says that all the exam questions come out of the Oracle University class Student Guides. To put it bluntly, your goal is to pass the exam. You could study the Oracle manuals, but there is a lot of material to cover and what strikes you as important might not be covered on the exam, and vice-versa. Essentially you are getting two levels of filtering. Somebody read the manuals and used that to prepare student guides, then a group of Oracle instructors read the student guide and prepared questions. I think that most of the exam guides were prepared from the Oracle Student Guides and the author has at least taken the exam, so they have a general idea of how the test is approached. Everyone has their favorite exam preparation book and I consider this a worthwhile investment, I bought Couchman - http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL0 H http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL 0Hsourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=007 2133414 sourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=00721 33414 Two issues: Which exam track are you planning to take? 9i? 8i? The advice I received is don't take the SQL exam as your first exam. Take the DBA exam first. The SQL exam is reported to be quite hard because it covers all the odd SQL functions and can really catch you unawares. As a practicing DBA you should find the DBA exam pretty easy. I didn't personally take that path for my own reasons, but I still consider it good advice. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 05, 2002 6:09 AM To: Multiple recipients of list ORACLE-L Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly Insect Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: KENNETH JANUSZ 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
RE: SQL Brain Teaser Challenge
Hey Raj, Ever paranoid of a ruse I had to see for myself that your query worked on O9i and not O8i. I cranked up my O9i test server and confirmed it does indeed work on 9i and not 8i. (See below.) I'm still looking for a way to get this to work on O8i. In the meantime I'll submit a TAR but I hate it when the solution is to upgrade to the next version. Steve --- SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 5 04:58:14 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production you are logged into RAC1. RAC1.SYS.SQLSELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid ; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. -Original Message- Sent: Tuesday, November 05, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Steve, I did try it ... oraclei@rhea-ACPT1 sys SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 5 14:18:02 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected. SQL conn system Enter password: Connected. SQL SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, November 05, 2002 2:15 PM To: Jamadagni, Rajendra; '[EMAIL PROTECTED]' Nope... SQL l 1 SELECT * 2FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4* CONNECT BY PRIOR ID = parentid SQL / FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) * ERROR at line 2: ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc. I supplied the DDL/DML so you could test on your own systems. -Original Message- Sent: Tuesday, November 05, 2002 12:07 PM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid Does this work? Also see http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:921234804 9 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Well it works but your query assumes knowledge of the tree- that it will always only have 3 levels. Consider when I add the following 2 rows: insert into treenode values(10,3,1,'nested folder2.2.1'); insert into treenode values(11,10,2,'nested folder2.2.2'); Now it fails. SORRY I wasn't clear on this part of the rules/spec. :-( We should be able to add nodes and levels. We should also be able update nodes to different parents and their children and children's children (etc) should automatically following them around on the tree. Any other ideas? Steve -Original Message- Sent: Tuesday, November 05, 2002 12:20 PM To: [EMAIL PROTECTED] Cc: Orr, Steve Importance: High Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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.com --
RE: OCP Exams - What to study out of?
Paula - You're losing me on the connection between SQL and GUI. I can see a connection between command-line DBA actions and GUI DBA tools. And some GUI report writing tools create SQL for you, but usually we DBAs are called when it creates bad SQL code and we must straighten it out. So have you passed all 5 OCP modules? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 05, 2002 12:29 PM To: Multiple recipients of list ORACLE-L I took the SQL exam first and had no problems although I have heard others have. I think it has to do with how I learned - I forced myself to use SQL before getting hooked into a GUI tool of any kind. In the long-run that is a good approach as you will always have that but might not always have the GUI. -Original Message- mailto:DWILLIAMS;LIFETOUCH.COM ] Sent: Tuesday, November 05, 2002 12:04 PM To: Multiple recipients of list ORACLE-L Eva - Everything I've heard says that all the exam questions come out of the Oracle University class Student Guides. To put it bluntly, your goal is to pass the exam. You could study the Oracle manuals, but there is a lot of material to cover and what strikes you as important might not be covered on the exam, and vice-versa. Essentially you are getting two levels of filtering. Somebody read the manuals and used that to prepare student guides, then a group of Oracle instructors read the student guide and prepared questions. I think that most of the exam guides were prepared from the Oracle Student Guides and the author has at least taken the exam, so they have a general idea of how the test is approached. Everyone has their favorite exam preparation book and I consider this a worthwhile investment, I bought Couchman - http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL0 http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL 0 H http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL 0Hsourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=007 2133414 sourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=00721 33414 Two issues: Which exam track are you planning to take? 9i? 8i? The advice I received is don't take the SQL exam as your first exam. Take the DBA exam first. The SQL exam is reported to be quite hard because it covers all the odd SQL functions and can really catch you unawares. As a practicing DBA you should find the DBA exam pretty easy. I didn't personally take that path for my own reasons, but I still consider it good advice. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 05, 2002 6:09 AM To: Multiple recipients of list ORACLE-L Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly Insect Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: SQL Brain Teaser Challenge
and i got this to work: SQL select * from treenode; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. /cheating with the order of the insert statements ;-) i had always thought the parentid was the way to go when representing trees in the database, but Celko describes the Nested-Set Model of Trees alternative in this ancient article: http://www.dbmsmag.com/9603d06.html makes your problem trivial. anyway, thanks for the post Steve. learned something today - i'm going home. -Original Message- Sent: Tuesday, November 05, 2002 1:15 PM To: Multiple recipients of list ORACLE-L Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To
RE: SQL Brain Teaser Challenge
I was also able to confirm this works on O9i. -Original Message- Sent: Tuesday, November 05, 2002 11:14 AM To: Multiple recipients of list ORACLE-L I get an error on 8.1.7.2. Is siblings new? SQL l 1 SELECT LEVEL, treenode.* 2FROM treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* ORDER SIBLINGS BY PARENTid , nodeorder SQL / ORDER SIBLINGS BY PARENTid , nodeorder * ERROR at line 5: ORA-00924: missing BY keyword -Original Message- Sent: Tuesday, November 05, 2002 11:02 AM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT LEVEL, treenode.* FROM treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid ORDER SIBLINGS BY PARENTid , nodeorder Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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.com -- Author: Orr, Steve 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.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services
RE: SQL Brain Teaser Challenge
Okay, my answer was almost correct (almost correct = wrong). Jared's answer is right on, given the current data set. What happens when the data is changed? Does ID have meaning or is it the sequence in which the row was added? NODEORDER is 'sequential', but the starting values vary within the parent node (sometimes start with 1, others with 0). Old college trick, instead of answering the question, you challenge the validity of the question. Can you use sql only or assume sql*plus is available? -Original Message- Sent: Tuesday, November 05, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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.com -- Author: Fink, Dan 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 Production Server to Stand by Server on 8.1.7 on Win 2k
Hello Jared If I run the database in Standby mode, once my production server fails, can the standby database be opened for users to make changes (i.e. read an write) and how do I accomplish that. Also, once my production server is back on line, how do you suggest I should synchronize the production server with he standby server database? Thanks Arif -Original Message- Sent: Tuesday, November 05, 2002 12:29 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] 2k Importance: High First off, your process is doing a lot of unnecessary work. Standby databases are available in 7.3.4. I believe that would be somewhat simpler than your current procedure. I haven't tried it though, so I could be wrong. Even with your current procedure, you don't need to copy all of the files, most of the time anyway. Build your standby database, put it in recovery mode, and just keep applying archive log files to it. You may need to rebuild when a datafile is added to a tablespace, not sure. In 8i, why not just use standby database? Jared Arif Khan (GWL) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 06:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Clone Production Server to Stand by Server on 8.1.7 on Win 2k Hello We currently have two Identical servers (identical in terms of both Hardware and SW). We run Oracle 7.3.4.0 (Workgroup server) on Win NT 4 on both the servers. We call them as Production Server and Stand by server. The Stand by server is passive in nature (i.e. does not do anything). Every night a batch process shuts down Oracle instance on both the machines and copies over all the files (Data, log, ctl etc) from Production Server to the Stand by server (Drive to drive, directory to directory...) In case the Production Server fails, we simply switch over the users (with a different alias to the stand by server) and they are back in business. Now, we are thinking of migrating to 8.1.7, however while trying to install this version, one needs to specify a Global name which I believe has to be unique on the network. So will the same process that I used to run (i.e. copy all files over from production to stand by ) work??? I guess both my servers will now have to have separate/unique Global Names. Is there any other approach that any of you can suggest??? TIA Arif -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arif Khan (GWL) 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.com -- Author: Arif Khan (GWL) 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: Read Only Login with Source Visibility
Depending on the tool, that may not work. It wouldn't work with SQL Navigator, or MS Access. Jared Mercadante, Thomas F [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 10:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Read Only Login with Source Visibility better yet - create a local copy of it, and place a private synonym in their account to point to it. really sneaky. -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Rewrite the all_source view, and I believe the all_objects view. It's a pain in the rear, but it can be done. It also tends to break when you upgrade. I did this once on Oracle 7, there may be some other workaround now on 8i. Jared Bill Buchan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 04:48 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Read Only Login with Source Visibility Hi all, I am trying to create a read-only login, RO_USER which can do the following: 1. See all tables, views, constraints etc. in one *specific* other schema, APP. 2. See all the source for PL/SQL objects in APP. The first bit is easy: GRANT SELECT on tables, views to RO_USER. Not sure about the second bit. I have tried granting CREATE ANY PROCEDURE and CREATE ANY TRIGGER to RO_USER and using a DDL trigger to prevent these privileges actually being used to create procedures/triggers. This works but does not restrict the source visibility to APP. I have other schemas where I do not want RO_USER to see the source. My other concern is granting SELECT on sequences as this means that they can select nextval from them and hence increment the numbers (not quite read-only!) Any suggestions for fixing these problems would be much appreciated! Thanks - Bill. PS. This is on 8i. -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Other posts should answer most of your questions. Jared's answer assumes only three levels and doesn't work when we add levels or branches to the tree. ID is a system generated key and has no meaning. SQL only, no SQL*Plus stuff. NODEORDER only refers to the sort within a node because you should not have to resequence all the data just to add a node, change the sort within a node or move a node to another parent. -Original Message- Sent: Tuesday, November 05, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Okay, my answer was almost correct (almost correct = wrong). Jared's answer is right on, given the current data set. What happens when the data is changed? Does ID have meaning or is it the sequence in which the row was added? NODEORDER is 'sequential', but the starting values vary within the parent node (sometimes start with 1, others with 0). Old college trick, instead of answering the question, you challenge the validity of the question. Can you use sql only or assume sql*plus is available? -Original Message- Sent: Tuesday, November 05, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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.com -- Author: Fink, Dan 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.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: SQL Brain Teaser Challenge
I wondered if someone would try that. :-) The link you provided is interesting but I don't think it fits the needs as regards being able to add and rearrange nodes. (Maybe I got confused with the little worms.) This is because the right column has to be twice the number of rows and this number would need to be updated every time you added or removed a row. -Original Message- Sent: Tuesday, November 05, 2002 12:45 PM To: Multiple recipients of list ORACLE-L and i got this to work: SQL select * from treenode; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. /cheating with the order of the insert statements ;-) i had always thought the parentid was the way to go when representing trees in the database, but Celko describes the Nested-Set Model of Trees alternative in this ancient article: http://www.dbmsmag.com/9603d06.html makes your problem trivial. anyway, thanks for the post Steve. learned something today - i'm going home. -Original Message- Sent: Tuesday, November 05, 2002 1:15 PM To: Multiple recipients of list ORACLE-L Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3
RE: Clone Production Server to Stand by Server on 8.1.7 on Win 2k
Hi Arif, Please read the article Note: 76373.1 on Metalink about Standby database. Regards, Quamrul Polash From: "Arif Khan (GWL)" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: Clone Production Server to Stand by Server on 8.1.7 on Win 2k Date: Tue, 05 Nov 2002 12:28:36 -0800 Hello Jared If I run the database in Standby mode, once my production server fails, can the standby database be opened for users to make changes (i.e. read an write) and how do I accomplish that. Also, once my production server is back on line, how do you suggest I should synchronize the production server with he standby server database? Thanks Arif -Original Message- Sent: Tuesday, November 05, 2002 12:29 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] 2k Importance: High First off, your process is doing a lot of unnecessary work. Standby databases are available in 7.3.4. I believe that would be somewhat simpler than your current procedure. I haven't tried it though, so I could be wrong. Even with your current procedure, you don't need to copy all of the files, most of the time anyway. Build your standby database, put it in recovery mode, and just keep applying archive log files to it. You may need to rebuild when a datafile is added to a tablespace, not sure. In 8i, why not just use standby database? Jared "Arif Khan (GWL)" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 11/05/2002 06:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Clone Production Server to Stand by Server on 8.1.7 on Win 2k Hello We currently have two Identical servers (identical in terms of both Hardware and SW). We run Oracle 7.3.4.0 (Workgroup server) on Win NT 4 on both the servers. We call them as Production Server and Stand by server. The Stand by server is passive in nature (i.e. does not do anything). Every night a batch process shuts down Oracle instance on both the machines and copies over all the files (Data, log, ctl etc) from Production Server to the Stand by server (Drive to drive, directory to directory...) In case the Production Server fails, we simply switch over the users (with a different alias to the stand by server) and they are back in business. Now, we are thinking of migrating to 8.1.7, however while trying to install this version, one needs to specify a Global name which I believe has to be unique on the network. So will the same process that I used to run (i.e. copy all files over from production to stand by ) work??? I guess both my servers will now have to have separate/unique Global Names. Is there any other approach that any of you can suggest??? TIA Arif -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arif Khan (GWL) 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.com -- Author: Arif Khan (GWL) 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). Unlimited Internet access for only $21.95/month. Try MSN! Click Here -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Quamrul Polash 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: OCP Exams - What to study out of?
Ken - Wow, I'm impressed. I took and passed one APICS module years and years ago and I can testify that they aren't easy. And I took the module related to my daily job. Can you share any tips on how to study for the SQL exam? I find I learn MUCH more from someone that was forced to reassess their strategy than from people that say oh, I took the manual home two nights and breezed through it. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 05, 2002 2:04 PM To: Multiple recipients of list ORACLE-L I agree that the SQL exam is very difficult. I've taken it twice and missed passing by just a couple of points. But I will persist. Interestingly enough when I got my APICS CPIM certification I took six exams and passed them all on the first try. The passing grade is 90%. Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 11:03 AM Eva - Everything I've heard says that all the exam questions come out of the Oracle University class Student Guides. To put it bluntly, your goal is to pass the exam. You could study the Oracle manuals, but there is a lot of material to cover and what strikes you as important might not be covered on the exam, and vice-versa. Essentially you are getting two levels of filtering. Somebody read the manuals and used that to prepare student guides, then a group of Oracle instructors read the student guide and prepared questions. I think that most of the exam guides were prepared from the Oracle Student Guides and the author has at least taken the exam, so they have a general idea of how the test is approached. Everyone has their favorite exam preparation book and I consider this a worthwhile investment, I bought Couchman - http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL0 H http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL 0Hsourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=007 2133414 sourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=00721 33414 Two issues: Which exam track are you planning to take? 9i? 8i? The advice I received is don't take the SQL exam as your first exam. Take the DBA exam first. The SQL exam is reported to be quite hard because it covers all the odd SQL functions and can really catch you unawares. As a practicing DBA you should find the DBA exam pretty easy. I didn't personally take that path for my own reasons, but I still consider it good advice. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 05, 2002 6:09 AM To: Multiple recipients of list ORACLE-L Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly Insect Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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:
RE: SQL Brain Teaser Challenge
Steve, I'm not sure why as of yet, but I had some success by creating two segmented indexes. One on PARENTID, NODEORDER and the second on NODEORDER, PARENTID: ID PARENTID NODEORDER DESCRIPTION 1 00 top folder 9 10 1st subfolder 2 11 2nd subfolder 4 21 folder 2 item 1 3 22 folder 2 item 2 6 23 folder 2 item 3 7 12 3rd subfolder 5 70 folder 3 item 1 8 71 folder 3 item 2 Seems strange, though, and I don't have the time to research it. We had the exact same problem for our BOM structures and ended up writing a recursive PL/SQL procedure and cursors to do it. Not nice. Also, I'm using CBO on 8.1.7.4 and have analyzed the tables and indexes w/o histograms using DBMS_STATS. BTW, the explain plan is a helluva lot better with the indexes... :) HTH! GL! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Orr, Steve [mailto:sorr;rightnow.com] Sent: Tuesday, November 05, 2002 11:24 AM To: Multiple recipients of list ORACLE-L Subject: SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: SQL Brain Teaser Challenge
actually - i think you are correct. the Nested-Set Model of Trees as Celko calls it would require more maintenance than the traditional parentid column when the tree changes... but the advantages on the query side are interesting. that nodeOrder column takes you on a ride. looks to me like your stuck with a lack of features (or a bug?) to do what you want in 8i. you could hardcode the levels like Jared did, or move to 9i and using Raj's suggestion. did you try it with a cursor? -Original Message- Sent: Tuesday, November 05, 2002 3:15 PM To: Multiple recipients of list ORACLE-L I wondered if someone would try that. :-) The link you provided is interesting but I don't think it fits the needs as regards being able to add and rearrange nodes. (Maybe I got confused with the little worms.) This is because the right column has to be twice the number of rows and this number would need to be updated every time you added or removed a row. -Original Message- Sent: Tuesday, November 05, 2002 12:45 PM To: Multiple recipients of list ORACLE-L and i got this to work: SQL select * from treenode; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. /cheating with the order of the insert statements ;-) i had always thought the parentid was the way to go when representing trees in the database, but Celko describes the Nested-Set Model of Trees alternative in this ancient article: http://www.dbmsmag.com/9603d06.html makes your problem trivial. anyway, thanks for the post Steve. learned something today - i'm going home. -Original Message- Sent: Tuesday, November 05, 2002 1:15 PM To: Multiple recipients of list ORACLE-L Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1
RE: SQL Brain Teaser Challenge
Steve, I've answered this one before (not on this list) for an Oracle 8i database... I guess the truth is that you really can't guarantee it, but it can be tricked with a hint. The trick is to access the table in correct sibling order. Create an index on the nodeorder column and then use an index hint in the query SQL create index m on treenode(nodeorder); SQL select /*+ index(t m) */ * 2 from treenode t 3 start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. How does this work? Well, when using connect by prior type SQL the rows are returned in the order in which they are input, with the except being the hierarchical sorting. By accessing the table via the index all rows with nodeorder 0 will be returned first, then nodeorder 1 second, etc. The connect-by then does it's stuff and creates the hierarchy, but by a stroke of luck you get the result you want. The danger with this? Well, I always say hints are exactly what they call themselves - hints. Oracle could choose to use a different index, or no index based on the query, number of rows, etc. If Oracle doesn't obey your hint then it won't work. Someone commented that a subquery with an order by isn't allowed - using the hint like this effectively does the same and overcomes that limitation. What do I win? Regards, Mark. PS: Since I don't have access to 9i I haven't heard of order by siblings... But it sounds like it fixes the problem correctly. Orr, Steve sorr@rightnow To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 06:49 Please respond to ORACLE-L Well it works but your query assumes knowledge of the tree- that it will always only have 3 levels. Consider when I add the following 2 rows: insert into treenode values(10,3,1,'nested folder2.2.1'); insert into treenode values(11,10,2,'nested folder2.2.2'); Now it fails. SORRY I wasn't clear on this part of the rules/spec. :-( We should be able to add nodes and levels. We should also be able update nodes to different parents and their children and children's children (etc) should automatically following them around on the tree. Any other ideas? Steve -Original Message- Sent: Tuesday, November 05, 2002 12:20 PM To: [EMAIL PROTECTED] Cc: Orr, Steve Importance: High Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode
RE: SQL Brain Teaser Challenge
Very good Mark. It worked even with the new subfolders added. I have no idea WHY it worked ... but it did. ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 -Original Message- Sent: Tuesday, November 05, 2002 3:41 PM To: Multiple recipients of list ORACLE-L Steve, I've answered this one before (not on this list) for an Oracle 8i database... I guess the truth is that you really can't guarantee it, but it can be tricked with a hint. The trick is to access the table in correct sibling order. Create an index on the nodeorder column and then use an index hint in the query SQL create index m on treenode(nodeorder); SQL select /*+ index(t m) */ * 2 from treenode t 3 start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. How does this work? Well, when using connect by prior type SQL the rows are returned in the order in which they are input, with the except being the hierarchical sorting. By accessing the table via the index all rows with nodeorder 0 will be returned first, then nodeorder 1 second, etc. The connect-by then does it's stuff and creates the hierarchy, but by a stroke of luck you get the result you want. The danger with this? Well, I always say hints are exactly what they call themselves - hints. Oracle could choose to use a different index, or no index based on the query, number of rows, etc. If Oracle doesn't obey your hint then it won't work. Someone commented that a subquery with an order by isn't allowed - using the hint like this effectively does the same and overcomes that limitation. What do I win? Regards, Mark. PS: Since I don't have access to 9i I haven't heard of order by siblings... But it sounds like it fixes the problem correctly. Orr, Steve sorr@rightnow To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 06:49 Please respond to ORACLE-L Well it works but your query assumes knowledge of the tree- that it will always only have 3 levels. Consider when I add the following 2 rows: insert into treenode values(10,3,1,'nested folder2.2.1'); insert into treenode values(11,10,2,'nested folder2.2.2'); Now it fails. SORRY I wasn't clear on this part of the rules/spec. :-( We should be able to add nodes and levels. We should also be able update nodes to different parents and their children and children's children (etc) should automatically following them around on the tree. Any other ideas? Steve -Original Message- Sent: Tuesday, November 05, 2002 12:20 PM To: [EMAIL PROTECTED] Cc: Orr, Steve Importance: High Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
Re: Partition Question
I agree... What are you trying to accomplish with partitioning? Partitioning by year / month / day / whatever can make it easy to truncate / archive old data. The only trick is to create new partitions before they are required. Another goal of partitioning may be query execution. You might partition a table by a certain column what is frequently stored in a where clause. This might restrict the query to a partition rather than the entire table and (depending on the query) could give a performance gain. If you are lucky partitioning will achieve both, if you are unlucky partitioning will just introduce a maintenance hassle. Think about why you want to partition the table and what you expect to gain by doing it. Whatever you do, don't partition simply because you can. Regards, Mark. Don Jerman [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ate.nc.uscc: Sent by: Subject: Re: Partition Question [EMAIL PROTECTED] m 05/11/2002 05:54 Please respond to ORACLE-L It depends on your reason for partitioning -- if you mean to drop a partition in the future (to roll off the 1999 data or whatever) then the ID range is potentially a valid approach, as long as ID is serial. If you just want to put chunks on different disk volumes, you could use the type or even a hash partitioning scheme. It's down to what you're trying to accomplish, and what is good for one partition key is probably bad or neutral for the other. Hamid Alavi wrote: Hi List, I have a question regarding partitioning: If I want to partition a table which strategy is better, like do i have to use a value which from first day of using this table all those partion is using or just using first partion, then second etc. E.G: If I do partion tableA based on ID range 1000, so for few month the only first partion of this table will be used then second partion, but if I partion it on Type (1,2,3,4,5) any record can be any of these type and from first day all of the partions will be used. Just want to check with you guys which way is better for performance? THanks for HELP Hamid Alavi Office 818 737-0526 Cell818 416-5095 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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
RE: SQL Brain Teaser Challenge
WOW. This actually works quite well!!! All this business is for generating an HTML tree navigation object using SQL, Perl-CGI, and javascript (from www.treeview.net) and it's critical that everything appear in the right order or the javascript will fail miserably. I hate to be dependent on indexes for sorts. If an index goes south or another is added that changes the results the whole app could come crashing down. But it's not the first time I'd be stuck with a fragile solution that works and I don't like it anyway. Thanks, Steve -Original Message- Sent: Tuesday, November 05, 2002 2:33 PM To: '[EMAIL PROTECTED]' Cc: Orr, Steve Steve, I'm not sure why as of yet, but I had some success by creating two segmented indexes. One on PARENTID, NODEORDER and the second on NODEORDER, PARENTID: ID PARENTID NODEORDER DESCRIPTION 1 00 top folder 9 10 1st subfolder 2 11 2nd subfolder 4 21 folder 2 item 1 3 22 folder 2 item 2 6 23 folder 2 item 3 7 12 3rd subfolder 5 70 folder 3 item 1 8 71 folder 3 item 2 Seems strange, though, and I don't have the time to research it. We had the exact same problem for our BOM structures and ended up writing a recursive PL/SQL procedure and cursors to do it. Not nice. Also, I'm using CBO on 8.1.7.4 and have analyzed the tables and indexes w/o histograms using DBMS_STATS. BTW, the explain plan is a helluva lot better with the indexes... :) HTH! GL! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Orr, Steve [mailto:sorr;rightnow.com] Sent: Tuesday, November 05, 2002 11:24 AM To: Multiple recipients of list ORACLE-L Subject: SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
What do I win? This was stated in the very first post... kudos. :-) At the moment you and Rich Jesse are tied but I'm still not very pleased with the solution. But unless somebody comes up with something better I'll box you up some kudos for shipping. (I afraid to ask but what are kudos anyway?) Thanks. Tentatively yours, Steve -Original Message- Sent: Tuesday, November 05, 2002 2:41 PM To: Multiple recipients of list ORACLE-L Steve, I've answered this one before (not on this list) for an Oracle 8i database... I guess the truth is that you really can't guarantee it, but it can be tricked with a hint. The trick is to access the table in correct sibling order. Create an index on the nodeorder column and then use an index hint in the query SQL create index m on treenode(nodeorder); SQL select /*+ index(t m) */ * 2 from treenode t 3 start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. How does this work? Well, when using connect by prior type SQL the rows are returned in the order in which they are input, with the except being the hierarchical sorting. By accessing the table via the index all rows with nodeorder 0 will be returned first, then nodeorder 1 second, etc. The connect-by then does it's stuff and creates the hierarchy, but by a stroke of luck you get the result you want. The danger with this? Well, I always say hints are exactly what they call themselves - hints. Oracle could choose to use a different index, or no index based on the query, number of rows, etc. If Oracle doesn't obey your hint then it won't work. Someone commented that a subquery with an order by isn't allowed - using the hint like this effectively does the same and overcomes that limitation. What do I win? Regards, Mark. PS: Since I don't have access to 9i I haven't heard of order by siblings... But it sounds like it fixes the problem correctly. Orr, Steve sorr@rightnow To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 06:49 Please respond to ORACLE-L Well it works but your query assumes knowledge of the tree- that it will always only have 3 levels. Consider when I add the following 2 rows: insert into treenode values(10,3,1,'nested folder2.2.1'); insert into treenode values(11,10,2,'nested folder2.2.2'); Now it fails. SORRY I wasn't clear on this part of the rules/spec. :-( We should be able to add nodes and levels. We should also be able update nodes to different parents and their children and children's children (etc) should automatically following them around on the tree. Any other ideas? Steve -Original Message- Sent: Tuesday, November 05, 2002 12:20 PM To: [EMAIL PROTECTED] Cc: Orr, Steve Importance: High Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not
RE: SQL Brain Teaser Challenge
Kevin, Like I said - as long as the table access is via the index then all rows are returned in nodeorder (sorted globally). The connect-by clause then orders the result set hierarchically. When searching for children of a parent it will find them in correct nodeorder order. It works for an infinite number of depths, although I believe connect-by only works to 256 levels anyway. The most likely problem to encounter relates to adding indexes to the id and parentid columns - necessary if the tree of going to be much more than (maybe) 1000 rows. In this instance a concatenated index of parentid,nodeorder would probably be best, although I've never really tested that out. As a side note: The first person I solved this for wanted all children of the parent sorted alphabetically - this was achieved by indexing the equivalent of the description column - I can imagine there are several applications of this query. Kevin Lange [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 09:08 Please respond to ORACLE-L Very good Mark. It worked even with the new subfolders added. I have no idea WHY it worked ... but it did. ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 -Original Message- Sent: Tuesday, November 05, 2002 3:41 PM To: Multiple recipients of list ORACLE-L Steve, I've answered this one before (not on this list) for an Oracle 8i database... I guess the truth is that you really can't guarantee it, but it can be tricked with a hint. The trick is to access the table in correct sibling order. Create an index on the nodeorder column and then use an index hint in the query SQL create index m on treenode(nodeorder); SQL select /*+ index(t m) */ * 2 from treenode t 3 start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. How does this work? Well, when using connect by prior type SQL the rows are returned in the order in which they are input, with the except being the hierarchical sorting. By accessing the table via the index all rows with nodeorder 0 will be returned first, then nodeorder 1 second, etc. The connect-by then does it's stuff and creates the hierarchy, but by a stroke of luck you get the result you want. The danger with
RE: SQL Brain Teaser Challenge
Steve, I thought kudos was the codename for the latest and greatest Palm Pilot. : -) As I said - it's not a nice solution, it has it's limits, but it works on Oracle 8i (and should work on other versions as well) Regards, Mark. PS: For the sake of completeness - the parentid of node 11 should be 3 judging by the description. Orr, Steve sorr@rightnow To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 09:23 Please respond to ORACLE-L What do I win? This was stated in the very first post... kudos. :-) At the moment you and Rich Jesse are tied but I'm still not very pleased with the solution. But unless somebody comes up with something better I'll box you up some kudos for shipping. (I afraid to ask but what are kudos anyway?) Thanks. Tentatively yours, Steve -Original Message- Sent: Tuesday, November 05, 2002 2:41 PM To: Multiple recipients of list ORACLE-L Steve, I've answered this one before (not on this list) for an Oracle 8i database... I guess the truth is that you really can't guarantee it, but it can be tricked with a hint. The trick is to access the table in correct sibling order. Create an index on the nodeorder column and then use an index hint in the query SQL create index m on treenode(nodeorder); SQL select /*+ index(t m) */ * 2 from treenode t 3 start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. How does this work? Well, when using connect by prior type SQL the rows are returned in the order in which they are input, with the except being the hierarchical sorting. By accessing the table via the index all rows with nodeorder 0 will be returned first, then nodeorder 1 second, etc. The connect-by then does it's stuff and creates the hierarchy, but by a stroke of luck you get the result you want. The danger with this? Well, I always say hints are exactly what they call themselves - hints. Oracle could choose to use a different index, or no index based on the query, number of rows, etc. If Oracle doesn't obey your hint then it won't work. Someone commented that a subquery with an order by isn't allowed - using the hint like this effectively does the same and overcomes that limitation. What do I win? Regards, Mark. PS: Since I don't have access to 9i I haven't heard of order by siblings... But it sounds like it fixes the problem correctly. Orr, Steve sorr@rightnow To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 06:49 Please respond to
Oracle iSQL*Plus buffer overflow vulnerability (#NISR04112002)
Alert available at: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOTp_id=216775.1 - Forwarded by Jared Still/Radisys_Corporation/US on 11/05/2002 03:11 PM - NGSSoftware Insight Security Research [EMAIL PROTECTED] 11/04/2002 09:48 AM To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:Oracle iSQL*Plus buffer overflow vulnerability (#NISR04112002) NGSSoftware Insight Security Research Advisory Name: Oracle iSQL*Plus buffer overflow Systems: Oracle Database 9i R1,2 on all operating systems Severity: High Risk Vendor URL: http://www.oracle.com/ Author: David Litchfield ([EMAIL PROTECTED]) Advisory URL: http://www.ngssoftware.com/advisories/ora-isqlplus.txt Date: 4th November 2002 Advisory number: #NISR04112002 Description *** Oracle iSQL*Plus is a web based application that allows users to query the database. It is installed with Oracle 9 database server and runs on top of apache. The iSQL*Plus module is vulnerable to a classic buffer overflow vulnerability. Details *** The iSQL*Plus web application requires users to log in. After accessing the default url, /isqlplus a user is presented with a log in screen. By sending the web server an overly long user ID parameter, an internal buffer is overflow on the stack and the saved return address is overwritten. This can allow an attacker to run arbitrary code in the security context of the web server. On most systems this will be the oracle user and on Windows the SYSTEM user. Once the web server has been compromised attackers may then use it as a staging platform to launch attacks against the database server itself. Fix Information *** NGSSoftware alerted Oracle to this problem on the 18th of October and Oracle, last week, issued an alert. The Oracle bug number assigned to this issue is 2581911. Patches can be downloaded from the Oracle Metalink site http://metalink.oracle.com/. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle9iAS Web Cache Denial of Service (a102802-1)
FYI - Forwarded by Jared Still/Radisys_Corporation/US on 11/05/2002 03:12 PM - @stake advisories advisories 10/28/2002 11:05 AM To: [EMAIL PROTECTED] cc: Subject:Oracle9iAS Web Cache Denial of Service (a102802-1) -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 @stake, Inc. www.atstake.com Security Advisory Advisory Name: Oracle9iAS Web Cache Denial of Service Release Date: 10-28-2002 Application: Oracle9iAS Web Cache 9.0.2.0.0 Platform: Windows NT/2000/XP Severity: Remote anonymous DoS Author: Andreas Junestam ([EMAIL PROTECTED]) Vendor Status: Oracle has released a bulletin CVE Candidate: CAN-2002-0386 Reference: www.atstake.com/research/advisories/2002/a102802-1.txt Overview: Oracle Web Cache is a part of the Oracle Application Server suite. The Web Cache server is designed to be implemented in front of the Oracle Web server and act as a caching reverse proxy server. There exists two different denial of service scenarios, which will cause the Web Cache service to fail. The denial of service conditions can be exploited by simple HTTP requests to the Web Cache service. Detailed Description: There exists two different denial of service situations in Oracle Web Cache 9.0.2.0.0. The first one is triggered by issuing a HTTP GET request containing at least one dot-dot-slash contained in the URI: GET /../ HTTP/1.0 Host: whatever [CRLF] [CRLF] The second denial of service is triggered by issuing an malformed GET request: GET / HTTP/1.0 Host: whatever Transfer-Encoding: chunked [CRLF] [CRLF] Both will create an exception and the service will fail. Vendor Response: Vendor was first contacted by @stake: 08-28-2002. Vendor released a bulletin: 10-04-2002 Oracle has released a bulletin describing a solution to this issue. Recommendation: Follow the vendor's instructions detailed in the security bulletin for this issue. - - From the Oracle bulletin: Customers should follow best security practices for protecting the administration process from unauthorized users and requests. As such, Oracle strongly encourages customers to take both of the following protective measures: 1. Use firewall techniques to restrict access to the Web Cache administration port. 2. Use the Secure Subnets feature of the Web Cache Manager tool to provide access only to administrators connecting from a list of permitted IP addresses or subnets. The potential security vulnerability is being tracked internally at Oracle and will be fixed by default in the 9.0.4 release of Oracle9i Application Server. For more information, see: http://otn.oracle.com/deploy/security/pdf/2002alert43rev1.pdf Common Vulnerabilities and Exposures (CVE) Information: CAN-2002-0386 Oracle9iAS Web Cache Denial of Service @stake Vulnerability Reporting Policy: http://www.atstake.com/research/policy/ @stake Advisory Archive: http://www.atstake.com/research/advisories/ PGP Key: http://www.atstake.com/research/pgp_key.asc Copyright 2002 @stake, Inc. All rights reserved. -BEGIN PGP SIGNATURE- Version: PGPfreeware 7.0.3 for non-commercial use http://www.pgp.com iQA/AwUBPb2J9Ee9kNIfAm4yEQLSFQCg7dL0gNKF5XxKlGK6KMXPKqd8ngEAnj1Q nqWXYFAipK5RbSYzYmRAgoP+ =5sSn -END PGP SIGNATURE- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Brain Teaser Challenge
Orr, Steve wrote: What do I win? This was stated in the very first post... kudos. :-) At the moment you and Rich Jesse are tied but I'm still not very pleased with the solution. But unless somebody comes up with something better I'll box you up some kudos for shipping. (I afraid to ask but what are kudos anyway?) Thanks. Tentatively yours, Steve Steve, I have a solution which doesn't rely on hints, but I am not very satisfied with it either. It relies on a function, and performance will be likely to be dismal if your tree grows big. Here is the function : create or replace function tree_rank(p_id in number) return number is n_rank number; begin select sum(nodeorder * power(10, -1 * level)) into n_rank from treenode where id in (select id from treenode connect by id = prior parentid start with id = p_id) connect by parentid = prior id start with id = 1; return n_rank; end; / (double 'CONNECT BY', ouch). Note that if you expect more than 10 items per level, you should use somthing bigger than 10 in the power function. However : SQL select * from treenode 2 order by tree_rank(id); ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
I have a solution which doesn't rely on hints, but I am not very satisfied with it either. Innovative nonetheless. Another cool way to skin this cat. Thanks! Steve -Original Message- Sent: Tuesday, November 05, 2002 5:14 PM To: Multiple recipients of list ORACLE-L Importance: High Orr, Steve wrote: What do I win? This was stated in the very first post... kudos. :-) At the moment you and Rich Jesse are tied but I'm still not very pleased with the solution. But unless somebody comes up with something better I'll box you up some kudos for shipping. (I afraid to ask but what are kudos anyway?) Thanks. Tentatively yours, Steve Steve, I have a solution which doesn't rely on hints, but I am not very satisfied with it either. It relies on a function, and performance will be likely to be dismal if your tree grows big. Here is the function : create or replace function tree_rank(p_id in number) return number is n_rank number; begin select sum(nodeorder * power(10, -1 * level)) into n_rank from treenode where id in (select id from treenode connect by id = prior parentid start with id = p_id) connect by parentid = prior id start with id = 1; return n_rank; end; / (double 'CONNECT BY', ouch). Note that if you expect more than 10 items per level, you should use somthing bigger than 10 in the power function. However : SQL select * from treenode 2 order by tree_rank(id); ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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.com -- Author: Orr, Steve 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: suggestion w/c platforms to choose from...
database will be used for oltp and data mart # of users -- 200 very critical, since order taking , purchase order and accounting module,hr will run on it.. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 9:03 PM As a rule buy the biggest, meanest, fault tolerance, with gigabytes of memory and terabytes of disk storage that you can buy. If you will provide more data about: 1) The size of the database 2) How many users 3) How critical is the system 4) The use of the system - data warehouse, OLTP etc then you will probably get a more specified answer. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 2:13 PM hi, can anyone suggestion w/c platform should i used to run oracle? wat are the things to consider in choosing platform? thanks Best regards, Grace Lim MIS Department Suy Sing Comm'l Corp. T- (632)-2474134 F- (632)-2474160 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: grace 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.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: grace INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Steve: Unfortunately, I didn't have time to experiment with this Teaser, but I was glad (real glad) to see that several great minds made the attempt. I could use all of you to help teach my students how to use their minds to solve problems like this. Ok. Now back to work folks. See you at OracleWorld Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Tuesday, November 05, 2002 7:43 PM To: Multiple recipients of list ORACLE-L Subject:RE: SQL Brain Teaser Challenge I have a solution which doesn't rely on hints, but I am not very satisfied with it either. Innovative nonetheless. Another cool way to skin this cat. Thanks! Steve -Original Message- Sent: Tuesday, November 05, 2002 5:14 PM To: Multiple recipients of list ORACLE-L Importance: High Orr, Steve wrote: What do I win? This was stated in the very first post... kudos. :-) At the moment you and Rich Jesse are tied but I'm still not very pleased with the solution. But unless somebody comes up with something better I'll box you up some kudos for shipping. (I afraid to ask but what are kudos anyway?) Thanks. Tentatively yours, Steve Steve, I have a solution which doesn't rely on hints, but I am not very satisfied with it either. It relies on a function, and performance will be likely to be dismal if your tree grows big. Here is the function : create or replace function tree_rank(p_id in number) return number is n_rank number; begin select sum(nodeorder * power(10, -1 * level)) into n_rank from treenode where id in (select id from treenode connect by id = prior parentid start with id = p_id) connect by parentid = prior id start with id = 1; return n_rank; end; / (double 'CONNECT BY', ouch). Note that if you expect more than 10 items per level, you should use somthing bigger than 10 in the power function. However : SQL select * from treenode 2 order by tree_rank(id); ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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.com -- Author: Orr, Steve 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). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen 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
/opt/oracle/product/jre/1.1.8/bin/jre
Hi all, Can I set this file to r-xr-xr-x ? rwxrwxrwx /opt/oracle/product/jre/1.1.8/bin/jre Thanks Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: OCP Exams - What to study out of?
Title: OCP Exams - What to study out of? Thanks to everyone for their input. I am considering the 8i track. a. Because I work mainly with the 8i DB. b. I hear that for the 9i a attendance of a course is a prerequisite. Regards Denham -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: Tuesday, November 05, 2002 2:09 PMTo: Multiple recipients of list ORACLE-LSubject: OCP Exams - What to study out of? Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly "Insect" Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity." Dennis Ritchie. DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com DISCLAIMER 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. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage 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 views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
