Re: oracle 9 IAS
Thanks Dennis , But I am looking something for oracle application server . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 10:14 AM AK - My favorite for people that are new to Oracle, or even databases for that point, is: Oracle9i DBA 101 http://www.amazon.com/exec/obidos/tg/detail/-/0072224746/qid=1060186608/sr=8 -2/ref=sr_8_2/103-5914235-9099062?v=glance http://www.amazon.com/exec/obidos/tg/detail/-/0072224746/qid=1060186608/sr= 8-2/ref=sr_8_2/103-5914235-9099062?v=glances=booksn=507846 s=booksn=507846 Take a look at this and let us know if that isn't what you had in mind. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 06, 2003 12:04 PM To: Multiple recipients of list ORACLE-L Any good book on oracle 9iAS for beginners which covers installation , configurations etc . There is too much in oracle docs . -Ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Partition Pruning and Hash Joins in 8i
Listers, 8.1.7.4. Anyone use the _subquery_pruning_cost_factor and _subquery_pruning_reduction hidden parameters to make the CBO a bit more aggressive about using recursive SQL and the TBL$OR$IDX$PART$NUM internal function to get partition pruning on hash joins (for example, when the value(s) used for partition elimination are coming from a dimension, and the fact is partitioned, and a hash join is used)? Metalink Note 179518.1 talks a bit about this (for those that haven't seen the behavior). We get the behavior on a handful of hash joins, where he will go ahead and do the recursive SQL on a dimension to get the distinct partition key values for the fact table. But we have many cases where the CBO doesn't, where the cost of that recursive SQL wouldn't be that bad and we would like to see it happen. So I've been experimenting with those parameters. There's not a lot out there on those parameters. Anyway, my main question is, for those that may have used those parameters, have you seen any ill side effects or bugs, partition pruning related or not? Do they impact anything else besides the pruning capabilities during hash joins? I intend to test quite thoroughly but am always a bit leery of using undocumented parameters. And yes, we will get support involved before we would even think of using them in a production DB, but you know how support is about providing info on undocumented parameters. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: oracle 9 IAS
Oops, my bad. I didn't notice the AS. Ignore that last message. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 06, 2003 12:14 PM To: Multiple recipients of list ORACLE-L AK - My favorite for people that are new to Oracle, or even databases for that point, is: Oracle9i DBA 101 http://www.amazon.com/exec/obidos/tg/detail/-/0072224746/qid=1060186608/sr=8 -2/ref=sr_8_2/103-5914235-9099062?v=glance http://www.amazon.com/exec/obidos/tg/detail/-/0072224746/qid=1060186608/sr= 8-2/ref=sr_8_2/103-5914235-9099062?v=glances=booksn=507846 s=booksn=507846 Take a look at this and let us know if that isn't what you had in mind. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 06, 2003 12:04 PM To: Multiple recipients of list ORACLE-L Any good book on oracle 9iAS for beginners which covers installation , configurations etc . There is too much in oracle docs . -Ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: After Logon Trigger and Import
Prasad, You can use SKIP_UNUSABLE_INDEXES=Y as an import parameter. But then again, why do that? Why not just impirt with INDEXES=N and then rebuild the indexes in parallel and with NOLOGGING? HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 1:19 PM Sorry, I forgot to mention the OS and Oracle Version. It is Hp-UX v11 and Oracle 8.1.7.4 Thanks. Best Regards, Prasad 860 843 8377 Prasada R Gunda To: [EMAIL PROTECTED] 08/05/2003 12:21 cc: PM Subject: After Logon Trigger and Import(Document link: Prasada R Gunda) Hi, I put the 'alter session set skip_unusable_indexes=true' in the logon trigger of a particular user and tested it in the sql*plus session. It is working fine there. I tested it by making an index unusable and inserting the data into the table. But, when I tried to import (using the same user) the data into that table, It gives an error saying that 'Index is in unusable state'. Does logon trigger fire for the Import? Is there any way to verify that the skip_unusable_indexes is set to 'true' for a particular session. Thanks in advance for your help. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Change based recovery
Lisa,
Shut
down your production database and FTP the control files over also. This
will make your control files waaayyy ahead of your data files, thus forcing a
recovery.
Right
now, your database does not need recovery - everything is in synch. Your
control files don't know anything about the archive logs because they are set
back to last Friday - thus they cannot move beyond that.
Good
Luck!
Tom Mercadante Oracle Certified Professional
-Original Message-From: Dobson, Lisa
[mailto:[EMAIL PROTECTED]Sent: Thursday, August 07,
2003 8:21 AMTo: Multiple recipients of list
ORACLE-LSubject: Change based recovery
Hi Guys and Gals,
We are currently doing
some testing to enable us to move our production database from one unix box to
another.
We are running a 7.3.4 db
in archivelog mode. The approach that management want to use is to
restore the database on the new server from a backup and then roll it forward
using the archived redo logs.
I have a full cold back
up from last Friday. I have restored the datafiles, controlfiles and redo logs
onto our test server from the backup tape, and then ftp'd the archived logs
over.
I then do -
SVRMGR startup
mountORACLE instance started.Total System Global
Area 258304260 bytesFixed
Size
45092 bytesVariable
Size
126925024 bytesDatabase
Buffers
131072000 bytesRedo
Buffers
262144 bytesDatabase mounted.SVRMGR recover database until change
10349;Media recovery complete.
I would have expected it
to display the names of the logs, butit doesn't, and when I check the alert log it
shows 'No Media Recovery required'.
Where am I going wrong? I can't understand why it
won't apply the archived logs. (Too hot today and brain not working
properly!)
TIA.
Lisa Dobson Database Analyst Home Group Ltd
This
message is intended only for the use of the person(s) ("Intended Recipient")
to whom it is addressed. It may contain information, which is privileged and
confidential. Accordingly any dissemination, distribution, copying or other
use of this message or any of its content by any person other than the
Intended Recipient may constitute a breach of civil or criminal law and is
strictly prohibited. If you are not the Intended Recipient, please contact the
sender as soon as possible.
This
e-mail has been scanned for all viruses by Star Internet. Theservice is
powered by MessageLabs. For more information on a proactiveanti-virus
service working around the clock, around the globe, visit:http://www.star.net.uk
RE: Implementing different document types with different attributes
attribute is a specific part of an index-file, but those attributes are different for all document types -Original Message- Sent: donderdag 7 augustus 2003 16:15 To: Multiple recipients of list ORACLE-L attributes What do you have as an example of an attribute? Vernaillen Tim tim.vernaillen To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @xgs.be cc: Sent by: Subject: Implementing different document types with different attributes ml-errors 08/07/2003 09:34 AM Please respond to ORACLE-L Hello I've an installation/implementation question! We've to analyse 50 document types, in total those 50 has 70 different attributes. We don't want to put all those document types into one table, because more than the half (35) of the attributes are not always used for each document type. This will have to much disk space for each record, if most of the fields are just blank. Has anyone suggestions how to build our table-structure? I've heart something about FlexFields, what are they? Takes every field diskspace, even if it's blank (null)? Thanks in advance for the response! Tim -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vernaillen Tim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Implementing different document types with different attribut
how about 2 tables w/ a 1:many relationship? document_types each with many document_type_attributes. read up on data modeling. i think several good books were mentioned on this list recently. -Original Message- Sent: Thursday, August 07, 2003 8:34 AM To: Multiple recipients of list ORACLE-L Hello I've an installation/implementation question! We've to analyse 50 document types, in total those 50 has 70 different attributes. We don't want to put all those document types into one table, because more than the half (35) of the attributes are not always used for each document type. This will have to much disk space for each record, if most of the fields are just blank. Has anyone suggestions how to build our table-structure? I've heart something about FlexFields, what are they? Takes every field diskspace, even if it's blank (null)? Thanks in advance for the response! Tim -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Q. To RAC or go vertical
While fault tolerance is certainly one of the features of RAC, it isn't correct to say that it is not also for scalability. Buy a bigger box? That works fine until you're in the biggest box you can get, then what? I realize that it's a small market segment that requires that kind of hardware, but it still exists. Sun has been testing a cluster of 15k servers with RAC, ostensibly for scalability. Some nodes are populated with 78 CPU's and 288 Gig of RAM. ( yes, that is correct ). Jared On Tue, 2003-08-05 at 07:54, Stephen Lee wrote: I think the point of RAC is fault tolerance, not scalability. If it's performance you want then you want a bigger box, not more boxes. 8 CPUs is not big. You sure don't need the expensive hardware if all you want to run is 8 CPUs. It would be better to go with a smaller frame and use the money you save to get more CPUs and additional I/O capacity. For example, instead of E12K with 8 CPUs, get 4810 with 12 CPUs -- unless you have definite plans to push the E12K out to its limits in the future. Don't forget to consider the backup requirements of a 5 - 10 TByte database. Another consideration, I think, is that those big, fancy boxes require additional sys admin skills. -Original Message- Hi All I would like to ask for your thoughts on whether to RAC or just go vertical (more cpu) Background Txn - OLTP like txn during day but batch extracts at night and very big batch extract periodically Data Volume - 5-10 TByte Data volatility - 99 % of data is very much like a ware house (unchanged) other 1% is read/update/delete/insert Options 1. Say a very large server like a HP Superdome or SUN E12000 with 8 CPUs Server already exist so cost is in obtaining additional CPU/Blades ie Traditional Server using plain old vanilla Oracle EE - can still increase head room. - batch programs can utilise all 8 CPUs - storage system need not cater for clustering 2, Same large server like a HP Superdome or SUN E12000 but partitioned into two. Each with 4 CPU. Oracle RDBMS + RAC option - storage server need to cater for cluster config - max performance for batch is with 4 CPUs only Which would you prefer and why. I am not convinced with the RAC option. Now if I was going with cheaper Intel servers like Dell servers with 4 CPUS each, and purchase say 4 nodes of 4 cpus each, that would be a different story. In this case I have the equipment and ability to grow vertically. ta tony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-3113
..sounds like a IPC problem. Check ora network and listener config. relink client relink utilities -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 11:49 AM To: Multiple recipients of list ORACLE-L No, There is no log generated. Below is the only message I am getting. hp204:NAMES :/home/oracle svrmgrl Oracle Server Manager Release 3.1.6.0.0 - Production Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved. ORA-03113: end-of-file on communication channel SVRMGR -Original Message- Sent: Wednesday, August 06, 2003 9:39 AM To: Multiple recipients of list ORACLE-L Any log file output? Mike -Original Message- [mailto:[EMAIL PROTECTED] Sent: 06 August 2003 16:29 To: Multiple recipients of list ORACLE-L Applied 250 OS patches on HP-UX 11.11 server and after that getting ORA-3113 error message every time I do svrmgrl or sqlplus /nolog connect /as sysdba. This database holds RMAN catalog for more than 30 databases and really very critical. Please share your ideas. I already tried to relink and rebooting the server. Database is 816 64bit on HP-UX 11.11 DISCLAIMER: This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Take an Oracle 8.1 export file back to an Oracle 7.1 database.
Title: Message I'm very interested in finding out how will you import LOB's, Java classes, function based indexes, IOTand ADT objects into V7 database, especially if it is V7.1 and not 7.3.4 or 7.3.5. Please, keep us posted. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bartolo, DavidSent: Thursday, August 07, 2003 1:15 PMTo: Multiple recipients of list ORACLE-LSubject: Take an Oracle 8.1 export file back to an Oracle 7.1 database. Hi all Is it possible to take an Oracle Version 8.1 database export file and load it into an Oracle Version 7.1 database? The export file of the 8.1 is on a different machine than the 7.1 database. If I can not is there some other method I can use to get the data from the 8.1 export file to the 7.1 database. Thanks David Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error,please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient.Wang Trading LLCand any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
cursor_sharing=SIMILAR and 9i
A quick update. Having multiple ORA-600 errors thrown at me randomly when I try to do a select from any of the V$ tables. Not always. 3 out of 5 attempts spew out ORA-600's and while this is in progress, the database freezes for a few seconds. Oracle Support says its coz of the cursor_sharing=similar. True, for when we comment it out, the 600's stop. Oracle Version 9.2.0.3. Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Change based recovery
Hi, When you are restoring from a cold backup you dont have to recover and your database will be old. If you want to bring it to current time then recreate the control file and recover it using RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; this will ask you for archive logs and then you have to supply them. Create the control file from a trace file generated on the running database using the following command: ALTER DATABASE BACKUP CONTROLFILE TO TRACE; Cheers! Venu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dobson, Lisa Sent: Thursday, August 07, 2003 5:51 PM To: Multiple recipients of list ORACLE-L Subject: Change based recovery Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs. I have a full cold back up from last Friday. I have restored the datafiles, controlfiles and redo logs onto our test server from the backup tape, and then ftp'd the archived logs over. I then do - SVRMGR startup mount ORACLE instance started. Total System Global Area 258304260 bytes Fixed Size 45092 bytes Variable Size 126925024 bytes Database Buffers 131072000 bytes Redo Buffers 262144 bytes Database mounted. SVRMGR recover database until change 10349; Media recovery complete. I would have expected it to display the names of the logs, butit doesn't, and when I check the alert log it shows 'No Media Recovery required'. Where am I going wrong? I can't understand why it won't apply the archived logs. (Too hot today and brain not working properly!) TIA. Lisa Dobson Database Analyst Home Group Ltd This message is intended only for the use of the person(s) (Intended Recipient) to whom it is addressed. It may contain information, which is privileged and confidential. Accordingly any dissemination, distribution, copying or other use of this message or any of its content by any person other than the Intended Recipient may constitute a breach of civil or criminal law and is strictly prohibited. If you are not the Intended Recipient, please contact the sender as soon as possible. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
RE: RMAN Backup piece being placed in wrong directory
name=D:\ORACLE\EWFMS\NOTF_INDX.DBFinput datafile fno=9 name=D:\ORACLE\EWFMS\TCS_DATA01.DBFinput datafile fno=6 name=D:\ORACLE\EWFMS\TOOLS01.DBFchannel ORA_DISK_2: starting piece 1 at 07-AUG-03channel ORA_DISK_3: starting full datafile backupsetchannel ORA_DISK_3: specifying datafile(s) in backupsetinput datafile fno=1 name=D:\ORACLE\EWFMS\SYSTEM01.DBFinput datafile fno=4 name=D:\ORACLE\EWFMS\DRSYS01.DBFinput datafile fno=5 name=D:\ORACLE\EWFMS\INDX01.DBFinput datafile fno=7 name=D:\ORACLE\EWFMS\USERS01.DBFchannel ORA_DISK_3: starting piece 1 at 07-AUG-03channel ORA_DISK_2: finished piece 1 at 07-AUG-03piece handle=D:\BACKUP\ORACLE\HOTBACKUP\RMAN_DEWFMS_T501408013_U1FEU5O8D_1_1.BAK comment=NONEchannel ORA_DISK_2: backup set complete, elapsed time: 00:00:46channel ORA_DISK_1: finished piece 1 at 07-AUG-03piece handle=D:\BACKUP\ORACLE\HOTBACKUP\RMAN_DEWFMS_T501408013_U1EEU5O8D_1_1.BAK comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:41channel ORA_DISK_3: finished piece 1 at 07-AUG-03piece handle=D:\BACKUP\ORACLE\HOTBACKUP\RMAN_DEWFMS_T501408013_U1GEU5O8D_1_1.BAK comment=NONEchannel ORA_DISK_3: backup set complete, elapsed time: 00:01:41Finished backup at 07-AUG-03 Starting backup at 07-AUG-03current log archivedusing channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=20 recid=17 stamp=501408115channel ORA_DISK_1: starting piece 1 at 07-AUG-03channel ORA_DISK_1: finished piece 1 at 07-AUG-03piece handle=C:\ORACLE\ORACLE\9.2.0\DATABASE\1HEU5OBJ_1_1 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 07-AUG-03 Starting Control File and SPFILE Autobackup at 07-AUG-03piece handle=D:\BACKUP\ORACLE\HOTBACKUP\CF_C-1770609602-20030807-02.BAK comment=NONEFinished Control File and SPFILE Autobackup at 07-AUG-03 RMAN Thanks in Advance, Scott Stefick MILPDS OCP Oracle DBA [EMAIL PROTECTED]
RE: OT : Learning curve
Announcing my new book... How to Gain Professional Wisdom and Become an Oracle Sage in Just 24 Hours. -Original Message- Sent: Wednesday, August 06, 2003 4:59 AM To: Multiple recipients of list ORACLE-L I stumbled by chance into the following article http://www.norvig.com/21-days.html which, without being in anyway related to Oracle, will probably ring familiar bells - and what it says about programming could probably be said about administration as well. By the way (and still more OT) I came to this site because of the PowerPoint 'Gettysburgh address' and I was brought there by a reference on Edward Tufte's site (http://www.edwardtufte.com), all of them worth a look if you have imports or installations to run. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- 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-600 when analyzing IOT
Title: RE: ora-600 when analyzing IOT Only missing data in *_TABLES is avg_row_len which, probably, can be used for estimating hash area for hash join. Also, columns statistics will not be generated, probably more important. Alex. -Original Message- Does it mean, that if I don't have overflow segment in my IOT, I don't have to analyze table, just analyze PK? Igor Neyman, OCP DBA [EMAIL PROTECTED]
Re: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
Hi! This is troubleshooting, not performance tuning ;) As far as Ixora states, v$sesstat opened cursors current shows PL/SQL cached cursors as well, even when they are explicitly closed (assuming that session_cached_cursors is set). But v$open_cursor doesn't show the number of cursor structures in UGA, but it shows you (some kind of count) of parse locks which get created in SGA for a SQL statement or PL/SQL block. They provide a link from schema objects to shared SQL area, so the cursors can be invalidated when doing DDL on referenced objects. They are called breakable parse locks in Concepts Manual. As the name says breakable - even though you got a lock on schema object, you still can alter it, the lock is just broken, the cursor is invalidated (but remains cached in UGA) and has to be reloaded on next execution. Here we can have a situation, when as result of DDL, we don't have any breakable parse locks on SGA anymore, but we still have open cursors in UGA. Thus the difference between v$sesstat and v$open_cursor. (this story is based on Concepts manual and Ixora) But for ORA-1000 errors, you should maybe set event for ORA-1000 and dump errorstack. Or sample v$sesstat quite often (once per few seconds), to catch the point where number of open cursors jumps to ceiling (as I understand, it's fairly low normally). And for TAR's - I recommend you to do few Oracle Applications upgrades, especially to fresh versions of major new releases, such 11.5.1 or 2, then you start even seeing severity 1 TARs in your dreams ;) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 07, 2003 11:14 PM No takers on this from the perf tuning gurus? Please don't make me open a TAR. Think of the children... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Jesse, Rich Sent: Thursday, July 24, 2003 3:39 PM To: Multiple recipients of list ORACLE-L Subject: V$SESSSTAT stat 3 vs V$OPEN_CURSOR Hey all, We recently had a problem with a 3rd-party app getting ORA-1000 max open cursors exceeded on their 8.1.7.4 DB. Since OPEN_CURSORS is set to 500 in the init.ora -- should be more than generous for a tiny app on a tiny DB -- we started looking into how many cursors the app actually has open at any given time. While investigating this, I see that the number of rows in V$OPEN_CURSORS isn't consistent with the value of stat 3 (opened cursors current) of V$SESSSTAT. Of the two processes I looked at, each had a V$SESSSTAT value of 3 for stat 3, while the first had a single entry in V$OPEN_CURSOR and the second had four. Should these values match? I looked on Metalink but was unable to find any reference to the relationship between these, other than a forum article where the OraSupport person was extraordinarily unhelpful. TIA, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
There were discussions about this before. You can search the archive for it. v$open_cursor and the statistic opened cursor current doesn't always agree. v$open_cursor show cursors parsed and not closed, while the opened cursor current shows cursors opened and not closed. v$open_cursor doesn't track for unparsed dynamic cursors so it some times doesn't show all cursors that count toward the max open cursor parameter. Richard Ji -Original Message- Sent: Thursday, August 07, 2003 4:14 PM To: Multiple recipients of list ORACLE-L No takers on this from the perf tuning gurus? Please don't make me open a TAR. Think of the children... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Jesse, Rich Sent: Thursday, July 24, 2003 3:39 PM To: Multiple recipients of list ORACLE-L Subject: V$SESSSTAT stat 3 vs V$OPEN_CURSOR Hey all, We recently had a problem with a 3rd-party app getting ORA-1000 max open cursors exceeded on their 8.1.7.4 DB. Since OPEN_CURSORS is set to 500 in the init.ora -- should be more than generous for a tiny app on a tiny DB -- we started looking into how many cursors the app actually has open at any given time. While investigating this, I see that the number of rows in V$OPEN_CURSORS isn't consistent with the value of stat 3 (opened cursors current) of V$SESSSTAT. Of the two processes I looked at, each had a V$SESSSTAT value of 3 for stat 3, while the first had a single entry in V$OPEN_CURSOR and the second had four. Should these values match? I looked on Metalink but was unable to find any reference to the relationship between these, other than a forum article where the OraSupport person was extraordinarily unhelpful. TIA, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
Ryan, Yes it is, as long you limit the results based on SID. The same goes for v$open_cursor. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 08, 2003 1:54 AM really? I thought v$sesstat was session based statistics? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 07, 2003 5:39 PM I should have known to check Steve's site first (is he still lurking around here?). One thing I noticed is that one of the cursors listed in V$OPEN_CURSOR is an INSERT into AUD$. This would account for the discrepancy and could match the description you provided. So, in summary, the true number of open cursors is correct in V$SESSTAT and is *not necessarily* the count of rows in the corresponding V$OPEN_CURSOR view. And my apologies to the sticklers of the list who have noticed my V$SESSSTAT and V$OPEN_CURSORS... sigh Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Tanel Poder [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 4:20 PM To: Multiple recipients of list ORACLE-L Subject: Re: V$SESSSTAT stat 3 vs V$OPEN_CURSOR Hi! This is troubleshooting, not performance tuning ;) As far as Ixora states, v$sesstat opened cursors current shows PL/SQL cached cursors as well, even when they are explicitly closed (assuming that session_cached_cursors is set). But v$open_cursor doesn't show the number of cursor structures in UGA, but it shows you (some kind of count) of parse locks which get created in SGA for a SQL statement or PL/SQL block. They provide a link from schema objects to shared SQL area, so the cursors can be invalidated when doing DDL on referenced objects. They are called breakable parse locks in Concepts Manual. As the name says breakable - even though you got a lock on schema object, you still can alter it, the lock is just broken, the cursor is invalidated (but remains cached in UGA) and has to be reloaded on next execution. Here we can have a situation, when as result of DDL, we don't have any breakable parse locks on SGA anymore, but we still have open cursors in UGA. Thus the difference between v$sesstat and v$open_cursor. (this story is based on Concepts manual and Ixora) But for ORA-1000 errors, you should maybe set event for ORA-1000 and dump errorstack. Or sample v$sesstat quite often (once per few seconds), to catch the point where number of open cursors jumps to ceiling (as I understand, it's fairly low normally). And for TAR's - I recommend you to do few Oracle Applications upgrades, especially to fresh versions of major new releases, such 11.5.1 or 2, then you start even seeing severity 1 TARs in your dreams ;) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 07, 2003 11:14 PM No takers on this from the perf tuning gurus? Please don't make me open a TAR. Think of the children... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Jesse, Rich Sent: Thursday, July 24, 2003 3:39 PM To: Multiple recipients of list ORACLE-L Subject: V$SESSSTAT stat 3 vs V$OPEN_CURSOR Hey all, We recently had a problem with a 3rd-party app getting ORA-1000 max open cursors exceeded on their 8.1.7.4 DB. Since OPEN_CURSORS is set to 500 in the init.ora -- should be more than generous for a tiny app on a tiny DB -- we started looking into how many cursors the app actually has open at any given time. While investigating this, I see that the number of rows in V$OPEN_CURSORS isn't consistent with the value of stat 3 (opened cursors current) of V$SESSSTAT. Of the two processes I looked at, each had a V$SESSSTAT value of 3 for stat 3, while the first had a single entry in V$OPEN_CURSOR and the second had four. Should these values match? I looked on Metalink but was unable to find any reference to the relationship between these, other than a forum article where the OraSupport person was extraordinarily unhelpful. TIA, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: ** is there PL/SQL for case
Title: RE: ** is there PL/SQL for case You are wrong ... SQL is NOT a subset of PL/SQL ... declare szVar varchar2(50); begin SELECT CASE WHEN state = 'CA' THEN 'Almost Bankrupt' WHEN state = 'CT' THEN 'Ridiculously High Tax Rates for no particular reason' WHEN state = 'PA' THEN 'We never stop fixing our roads' WHEN state = 'NJ' THEN 'We have the best drivers' END into szVar FROM (SELECT 'CA' state FROM dual); Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: A Joshi [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 07, 2003 4:55 PM To: Multiple recipients of list ORACLE-L Subject: RE: ** is there PL/SQL for case Guang, Thanks for your help. Do you have an example you can send me. I thought whatever one can do in sql one can do in pl/sql. meaning sql is a subset of pl/sql. Correct me if I am wrong. Thank You. Guang Mei [EMAIL PROTECTED] wrote: I am not sure in 9i. But in 8i I think you can use case in sql but not in pl/sql. You have to use if elsif in pl/sql. Guang -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of A Joshi Sent: Thursday, August 07, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: ** is there PL/SQL for case Hi, Is there a statement in pl/SQL like case or is if elsif the only way. Meaning if I need to transalate state depending on input number as follows : 1 - CA 2 - OR 3 - WA 4 - AR Can I have one statement like case 'state# : 1: state := 'CA' 2: state := 'OR' etc. or do i have to do : IF state# = 1 THEN state := 'CA'; ELSIF state# = 2 THEN state := 'OR'; etc Thank You. Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software *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
IMP-00020: long column too large for column buffer size (22)
Hi All, One of our developer ftp'ed the export (dmp) file from UNIX to NT in ASCII mode. He didn't know that it supposed to be done in BINARY mode. Now, he ftp'ed the dmp file back to UNIX in ASCII mode and tried doing the import. Looks like dmp file has been corrupted and Import is giving the following error. IMP-00020: long column too large for column buffer size (22) IMP-00028: partial import of previous table rolled back: 16380 rows rolled back/ Is there any way to fix the dmp file. Any ideas? I searched the metalink and looks like there is no way to correct it. It is on HP-UX and oracle 8.1.7.4 I appreciate your help. Thanks. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ** is there PL/SQL for case
Below
is an example (with Oracle 8173),"case" works in sql, not in
pl/sql.
Guang
[EMAIL PROTECTED] create table
t1 (name varchar2(30), salary number );Table
created.[EMAIL PROTECTED] insert into t1
(name,salary) values ('Bill', 1000);1 row
created.[EMAIL PROTECTED] insert into t1
(name,salary) values ('George', 2000);1 row
created.[EMAIL PROTECTED] insert into t1
(name,salary) values ('Gore', 3000);1 row
created.[EMAIL PROTECTED] insert into t1
(name,salary) values ('Dick', 4000);1 row
created.[EMAIL PROTECTED]
commit;Commit complete.[EMAIL PROTECTED] select * from
t1;NAME
SALARY--
--Bill
1000George
2000Gore
3000Dick
4000[EMAIL PROTECTED] select count(case
when salary 2000 then 1 else null end)
poor, count(case when salary between
2000 and 3000 then 1 else null end)
middle_class, count(case when salary
3000 then 1 else null end) rich from
t1; POOR
MIDDLE_CLASS RICH--
--
1
2 1[EMAIL PROTECTED] declare
c1 number; c2 number; c3 number;begin select
count(case when salary 2000 then 1 else null end)
poor, count(case when salary
between 2000 and 3000 then 1 else null end)
middle_class, count(case
when salary 3000 then 1 else null end) rich into
c1,c2,c3 from
t1;end;/
select count(case when
salary 2000 then 1 else null end)
poor,
*ERROR at line 6:ORA-06550: line 6, column 16:PLS-00103: Encountered
the symbol "CASE" when expecting one of the following:( * - + all mod null
an identifiera double-quoted delimited-identifier a bind
variable avgcount current distinct max min prior sql stddev sum
uniquevariance execute forall time timestamp interval datea string
literal with character set specificationa number a
single-quoted SQL string
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of A JoshiSent:
Thursday, August 07, 2003 4:55 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: ** is there PL/SQL for
case
Guang,
Thanks for your help. Do you have an example you can send me. I
thought whatever one can do in sql one can do in pl/sql. meaning sql is a
subset of pl/sql. Correct me if i am wrong. Thank You.Guang Mei
[EMAIL PROTECTED] wrote:
I
am not sure in 9i. But in 8i I think you can use "case" in sql but not in
pl/sql. You have to use if elsif in pl/sql.
Guang
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of A JoshiSent:
Thursday, August 07, 2003 4:24 PMTo: Multiple recipients of
list ORACLE-LSubject: ** is there PL/SQL for
case
Hi,
Is there a statement in pl/SQL like case or is if elsif the
only way. Meaning if I need to transalate state depending on input number
asfollows :
1 - CA
2 - OR
3 - WA
4 - AR
Can I have one statement like case 'state# :
1: state := 'CA'
2: state := 'OR'
etc.
or do i have to do :
IF state# = 1 THEN
state := 'CA';
ELSIF state# = 2 THEN
state := 'OR';
etc
Thank You.
Do you Yahoo!?Yahoo!
SiteBuilder - Free, easy-to-use web site design
software
Do you Yahoo!?Yahoo!
SiteBuilder - Free, easy-to-use web site design
software
RE: possible Bug in Oracle 9.2.0.2
Title: Message Tell them ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Browett, Darren [mailto:[EMAIL PROTECTED]Sent: Thursday, August 07, 2003 2:49 PMTo: Multiple recipients of list ORACLE-LSubject: RE: possible Bug in Oracle 9.2.0.2 That fixed it, thank you. I still haven't heard from oracle support yet. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 4:44 AMTo: Multiple recipients of list ORACLE-LSubject: RE: possible Bug in Oracle 9.2.0.2 You it is a bug alter session|system set "_unnest_subquery"=false / Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Browett, Darren [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 06, 2003 7:34 PM To: Multiple recipients of list ORACLE-L Subject: possible Bug in Oracle 9.2.0.2 While I am waiting for oracle support to respond to my tar update (2nd callback) I am just wondering if anybody has found this problem. We have the following select query (from a peoplesoft implementation) SELECT a.emplid, a.effdt FROM PS_JOB A WHERE A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT = SYSDATE) AND A.EFFSEQ = (SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A.EMPLID = A2.EMPLID AND A.EMPL_RCD = A2.EMPL_RCD AND A.EFFDT = A2.EFFDT) AND A.EMPL_STATUS = 'A' and a.emplid='3442' when we run the query we get one row back, but when we replace the field names with count(*), the resulting answer back is "2". We have tested it in 8.0.5.1.1 and we get the correct results, 1 row, and a count of 1. Darren -- Darren Browett P.Eng This message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
really? I thought v$sesstat was session based statistics? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 07, 2003 5:39 PM I should have known to check Steve's site first (is he still lurking around here?). One thing I noticed is that one of the cursors listed in V$OPEN_CURSOR is an INSERT into AUD$. This would account for the discrepancy and could match the description you provided. So, in summary, the true number of open cursors is correct in V$SESSTAT and is *not necessarily* the count of rows in the corresponding V$OPEN_CURSOR view. And my apologies to the sticklers of the list who have noticed my V$SESSSTAT and V$OPEN_CURSORS... sigh Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Tanel Poder [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 4:20 PM To: Multiple recipients of list ORACLE-L Subject: Re: V$SESSSTAT stat 3 vs V$OPEN_CURSOR Hi! This is troubleshooting, not performance tuning ;) As far as Ixora states, v$sesstat opened cursors current shows PL/SQL cached cursors as well, even when they are explicitly closed (assuming that session_cached_cursors is set). But v$open_cursor doesn't show the number of cursor structures in UGA, but it shows you (some kind of count) of parse locks which get created in SGA for a SQL statement or PL/SQL block. They provide a link from schema objects to shared SQL area, so the cursors can be invalidated when doing DDL on referenced objects. They are called breakable parse locks in Concepts Manual. As the name says breakable - even though you got a lock on schema object, you still can alter it, the lock is just broken, the cursor is invalidated (but remains cached in UGA) and has to be reloaded on next execution. Here we can have a situation, when as result of DDL, we don't have any breakable parse locks on SGA anymore, but we still have open cursors in UGA. Thus the difference between v$sesstat and v$open_cursor. (this story is based on Concepts manual and Ixora) But for ORA-1000 errors, you should maybe set event for ORA-1000 and dump errorstack. Or sample v$sesstat quite often (once per few seconds), to catch the point where number of open cursors jumps to ceiling (as I understand, it's fairly low normally). And for TAR's - I recommend you to do few Oracle Applications upgrades, especially to fresh versions of major new releases, such 11.5.1 or 2, then you start even seeing severity 1 TARs in your dreams ;) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 07, 2003 11:14 PM No takers on this from the perf tuning gurus? Please don't make me open a TAR. Think of the children... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Jesse, Rich Sent: Thursday, July 24, 2003 3:39 PM To: Multiple recipients of list ORACLE-L Subject: V$SESSSTAT stat 3 vs V$OPEN_CURSOR Hey all, We recently had a problem with a 3rd-party app getting ORA-1000 max open cursors exceeded on their 8.1.7.4 DB. Since OPEN_CURSORS is set to 500 in the init.ora -- should be more than generous for a tiny app on a tiny DB -- we started looking into how many cursors the app actually has open at any given time. While investigating this, I see that the number of rows in V$OPEN_CURSORS isn't consistent with the value of stat 3 (opened cursors current) of V$SESSSTAT. Of the two processes I looked at, each had a V$SESSSTAT value of 3 for stat 3, while the first had a single entry in V$OPEN_CURSOR and the second had four. Should these values match? I looked on Metalink but was unable to find any reference to the relationship between these, other than a forum article where the OraSupport person was extraordinarily unhelpful. TIA, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing
RE: last column in a table is added with a DEFAULT
Title: RE: last column in a table is added with a DEFAULT as soon as you add a column all depending code goes invalid, the dependency checking process doesn't discriminate about the default value. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Ehresmann, David [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 07, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Subject: last column in a table is added with a DEFAULT Has anybody ever heard of this? I have a developer saying this is an oracle bug. It caused some packages to go invalid. The error seems to be related to a bug in oracle caused when the last column in a table is added with a default. David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.*1
RE: ** is there PL/SQL for case
RTFM on DECODE: DECODE(state, 1, CA, 2, OR, 3, WA, 4, AR) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A Joshi Sent: Thursday, August 07, 2003 3:24 PM To: Multiple recipients of list ORACLE-L Subject: ** is there PL/SQL for case Hi, Is there a statement in pl/SQL like case or is if elsif the only way. Meaning if I need to transalate state depending on input number asfollows : 1 - CA 2 - OR 3 - WA 4 - AR Can I have one statement like case 'state# : 1: state := 'CA' 2: state := 'OR' etc. or do i have to do : IF state# = 1 THEN state := 'CA'; ELSIF state# = 2 THEN state := 'OR'; etc Thank You. Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: Implementing different document types with different attributes
Tim, How about this? Table Doc_Types Doc_Type_IDNumberPK ,Doc_Type_Descr VarChar2(100) Table Doc_Attributes Doc_Attrib_ID NumberPK ,Doc_Attrib_Descr VarChar2(100) Table Doc_Type_Attributes (Intersect table of the above two) Doc_Type_IDNumberPK and FK to Doc_Types ,Doc_Attrib_ID NumterPK and FK to Doc_Attributes Table Documents Doc_ID NumberPK ,Doc_Type_IDNumberpart of FK to Doc_Type_Attributes ,Doc_Attrib_ID Numberpart of FK to Doc_Type_Attributes ,Doc_DescrVarChar2(100) ,Doc_Body BLOB(store this segment out-of-line somewhere) This way you can have as many Doc Types and Attributes as you want and never have to modify table designs if you add or remove some. Also, no blank columns. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Vernaillen Tim [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] gs.be cc: Sent by: Subject: Implementing different document types with different attributes [EMAIL PROTECTED] .com 08/07/2003 08:34 AM Please respond to ORACLE-L Hello I've an installation/implementation question! We've to analyse 50 document types, in total those 50 has 70 different attributes. We don't want to put all those document types into one table, because more than the half (35) of the attributes are not always used for each document type. This will have to much disk space for each record, if most of the fields are just blank. Has anyone suggestions how to build our table-structure? I've heart something about FlexFields, what are they? Takes every field diskspace, even if it's blank (null)? Thanks in advance for the response! Tim -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-00600 with Bind variable. Please help!
Hi, I got this error when I run my procedure: !-- SQL ERROR:ORA-00600: internal error code, arguments: [15212], [1], [], [], [], [], [], [] -- Errors in file /ora817/app/oracle/admin/ORCL/udump/orcl_ora_4345.trc ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [15212], [1], [], [], [], [], [], [] Here is part of my procedure: query:='select/*+ FIRST_ROWS */ from article a where a.aid=e.aid and d.eventid=e.eventid and d.verbid=''REVW'''||chr(13)||' and d.actorid=:mactor '||chr(13); handle:=dbms_sql.open_cursor; dbms_sql.parse(handle,query,dbms_sql.native); dbms_sql.bind_variable(handle, ':mactor', myactorid); dbms_sql.define_column_char(handle,11,nresult,24); rownum:=dbms_sql.execute(handle); dbms_sql.column_value_char(handle,11,myrnum); dbms_sql.close_cursor( handle ); Thanks, Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Liu, Jack INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Multiple Datafiles and performance?
I don't mean to be argumentative, but every time I see assertions like these, I suspect someone has been reading some rather discredited books... So, my apologies in advance, but comments are inline below... In my experience, spreading datafiles across volumes (specially if you are careful not to locate the a table's datafiles and its indexes datafiles in the same drive) greatly increases performance. The assertion that performance is enhanced by distributing datafiles containing tables and datafiles containing indexes to different volumes is a myth. Think about it. Indexed access is a purely sequential activity from an I/O standpoint, putting aside the reality that a buffer cache exists. First, we access the root block of the index and read its contents in order to know where to perform the next I/O (i.e. a branch block). Then we read that branch block and read its contents in order to know where to perform the next I/O (i.e. a leaf block). Then we read the leaf block and read its contents in order to know where to perform the next I/O (i.e. a block in a table). And so on... Since we are performing sequential single-block I/O (hence the name of the wait event db file sequential read), how can separating datafiles containing tables from datafiles containing indexes matter to performance? As for the file size, I can not say because I have not tested it, but I think it should have no real impact compared to splitting it. Reorganizing the database regularly is a better way to optimize performance. And in what ways does reorganizing the database regularly improve performance? To break the question down into more manageable pieces: * In what way does rebuilding a table improve performance? * In what way does rebuilding an index improve performance? There are specific answers to these questions. For example, there are situations in which both tables and indexes can become sparsely populated. Tables become sparsely populated due to large-scale deletions. Indexes become sparsely populated due to monotonically-ascending data values inserted transactionally. In these cases, how can you detect this condition? The package DBMS_SPACE has procedures that help for tables and the ANALYZE INDEX ... VALIDATE STRUCTURE command helps for indexes. Does rebuilding a table or index that is not sparsely populated aid performance in any way? Quite frankly, no... ...well, there is one condition involving the clustering factor of an index where a rebuild of the table can help, but you'll end up hosing the clustering factor of other indexes. It is a case of favoring one index over another, and that is a decision that requires intimate knowledge of the application's usage of the table and its indexes... So, reorganizing the database on a regular basis is purely a waste of time. Regularly monitoring the database for sparsely populated tables and indexes, and then determining if the condition is affecting performance of any important processes before rebuilding, will indeed help performance. Sorry for the combative tone, but I hope this helps... -Tim -Mensaje original- De: Dave Phillips [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 06 de agosto de 2003 22:14 Para: Multiple recipients of list ORACLE-L Asunto: Multiple Datafiles and performance? Oracle 8.1.7.4 Win2k What is the consensus on datafile sizing and the impact/overhead multiple datafiles have on performance? For example, if I have one 2.5g datafile, and three 1g datafiles, and I need more space, would it be better to increase the size of the 1g to 2g or add another 1g datafile?. Is it better to keep them all uniform in size? I would think having multiple datafiles that could be spread across drive volumes would be beneficial, am I wrong? (Wouldn't be the first time :) TIA David Phillips Support DBA Gasper Corp. BAARF member #30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ora-600 when analyzing IOT
I am not sure but based on the definition of an IOT I would think you would
have to ANALYZE TABLE since the primary key data is not duplicated in
a separate index.
Rick
Igor Neyman
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
on.com cc:
Sent by: Subject: RE: ora-600 when analyzing
IOT
[EMAIL PROTECTED]
.com
08/07/2003 02:14
PM
Please respond to
ORACLE-L
Does it mean, that if I don't have overflow segment in my IOT, I don't
have to analyze table, just analyze PK?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:45 PM
To: Multiple recipients of list ORACLE-L
Igor,
When you gather statistics on an IOT with the ANALYZE command, it will
analyze the primary key and overflow segment so I would think
this is all that is required by CBO. Hopefully others have more
insight.
Rick
Igor Neyman
[EMAIL PROTECTED]To:
[EMAIL PROTECTED]
on.com cc:
[EMAIL PROTECTED]
Subject: RE: ora-600
when analyzing IOT
08/07/2003 12:13
PM
Rick,
Thanks for prompt reply.
I tried it (deleting statistics before analyzing table) and it worked.
Any ideas, whether cost-based optimizer need statistics on IOT or having
statistics on PK index of IOT would be enough?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L
According to MetaLink you need to delete statistics and re-analyze
I do not know your database version but this is supposely fixed in 8.1.6
execute
dbms_stats.delete_table_stats('owner','IPN_MEASUREMENT');
analyze table ipn_measurement estimate statistics ;
HTH
Rick
Igor Neyman
[EMAIL PROTECTED]To: Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]
on.com cc:
Sent by: Subject: ora-600 when
analyzing IOT
[EMAIL PROTECTED]
.com
08/07/2003 12:44
PM
Please respond to
ORACLE-L
Ok, since my original message still hasn't arrived (sent couple hours
ago), here it goes again (sorry, if you get duplicate).
First, sorry for asking this list before searching Metalink, but I don't
have access to it right now.
So, here it is:
I'm getting:
ORA-00600: internal error code, arguments: [15163], [333], [17424],
[16191], [], [], [], []
when analyzing one of the index-organized tables:
ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS.
Now, similar statement analyzing other IOTs works fine.
Also, on the same table (ipn_measurement) analyze worked fine yesterday,
and the table didn't grew too much since yesterday.
Any ideas?
Another question, do I really have to run analyze table on IOT for
cost-based optimizer, or analyzing index would be enough:
ANALYZE INDEX
RE: ** is there PL/SQL for case
Starting with Oracle9i, PL/SQL supports the use of case statements and case expressions. These constructs are often convenient to use in place of complex if-then-else logic and function similarly to counterparts in other languages (e.g. the C switch statement). Case statements are standalone statements that can appear anywhere any other PL/SQL statement can appear. Case expressions are similar to case statements, but return a value and can only appear in places where any other PL/SQL expression can appear. Rick Guang Mei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: ** is there PL/SQL for case .com 08/07/2003 04:34 PM Please respond to ORACLE-L I am not sure in 9i. But in 8i I think you can use case in sql but not in pl/sql. You have to use if elsif in pl/sql. Guang -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of A Joshi Sent: Thursday, August 07, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: ** is there PL/SQL for case Hi, Is there a statement in pl/SQL like case or is if elsif the only way. Meaning if I need to transalate state depending on input number as follows : 1 - CA 2 - OR 3 - WA 4 - AR Can I have one statement like case 'state# : 1:
RE: last column in a table is added with a DEFAULT
Title: RE: last column in a table is added with a DEFAULT David, Nope. Your programmer is wrong. Tom Mercadante Oracle Certified Professional -Original Message-From: Ehresmann, David [mailto:[EMAIL PROTECTED]Sent: Thursday, August 07, 2003 3:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: last column in a table is added with a DEFAULT I understand that part. What the programmer is saying that you can not add the last column to a table with a default value. Does that sound reasonable? thanks, Raj. David. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Thursday, August 07, 2003 1:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: last column in a table is added with a DEFAULT as soon as you add a column all depending code goes invalid, the dependency checking process doesn't discriminate about the default value. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Ehresmann, David [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 07, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Subject: last column in a table is added with a DEFAULT Has anybody ever heard of this? I have a developer saying this is an oracle bug. It caused some packages to go invalid. "The error seems to be related to a bug in oracle caused when the last column in a table is added with a default." David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
