Serial# changes when rolling back
Title: Serial# changes when rolling back Jared,Deepak I did not see a reply on this. From a brief experiment I don't see the serial# changing when rolling back a transaction. The code posted by Jared certainly works as the number of blocks to rollback reduces as the job nears completion. If the serial# changes I would be interetsed to understand why and to what purpose John -Original Message- From: Deepak Thapliyal [mailto:[EMAIL PROTECTED]] Sent: 03 December 2001 17:55 To: Multiple recipients of list ORACLE-L Subject: Re: killing system user Hi Jared why does the serial# have to change due to rollback? lots of us would be curious for a brief expln ... Thx Deepak --- Jared Still [EMAIL PROTECTED] wrote: The session is rolling back, you can't kill it. This is why the serial# is changing. The following query can be used to track its progress. select s.osuser ,s.username ,s.sid ,r.segment_name ,t.space ,t.recursive ,t.noundo ,t.used_ublk ,t.used_urec ,t.log_io ,t.phy_io ,substr(sa.sql_text,1,200) txt from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa where s.taddr=t.addr and t.xidusn=r.segment_id(+) and s.sql_address=sa.address(+); Jared On Sunday 02 December 2001 22:55, Tatireddy, Shrinivas (MED, Keane) wrote: Hi lists, Solaris 2.7 oracle 8i I have a session SYSTEM doing import into a table. (logged into server thru telnet from win 98 PC) Suddenly the power outage occurred to my PC. When I logged into the server thru telnet, I found that the session is active. By mistake, I killed the process at o/s level. For somereasons,I tried to drop the table. But I failed to do it, as it is locked by import process. I tried to kill the user SYSTEM. But the oracle is giving error that there is not user with such sid and serial number. The serial# number is often getting changed when I query from v$session. Is there a way to kill this user, without shutting down the database. And why different serial# number each time, I query v$SESSION.? Any clues? Thnx and Regards, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Buy the perfect holiday gifts at Yahoo! Shopping. http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Duplexing Archived Log Files
Under 8.0.X you can use log_archive_duplex_dest. It is also available on 8i. However, if you use log_archive_dest_n on 8i, you cannot set log_archive_duplex_dest. Similarly, on 9i, if you use log_archive_dest, you could not set log_archive_dest_n. Finally and regarding rman on 9i, you can configure for backing up many copies of archives and datafiles as you want. F.e.: rmanconfigure archivelog backup copies for device type disk to n; Probably this is not what you are looking for, however it is there if you wanna try Regards. --- Jack C. Applewhite [EMAIL PROTECTED] wrote: Dennis, We use log_archive_dest_1 log_archive_dest_2 sending a copy of each archived redo log to our Standby database (log_archive_dest_2). log_archive_dest_2 is used with the OPTIONAL parameter, since we don't want to stop archiving if the Standby is down or there's some problem with the LAN. We also have log_archive_min_succeed_dest = 1 to make sure that the archiving succeeds at least to the local target drive (log_archive_dest_1). It's worked great for us (8.1.6.0.0 EE under Win2k). BTW, we use RMan for hot backups and like it very much. We can also issue the Alter Tablespace Begin Backup command and get instantaneous results - which I had to do recently to restore some datafiles to the Standby after one of its disks failed. I wonder why you get such slow response? Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- WILLIAMS Sent: Thursday, December 06, 2001 12:29 PM To: Multiple recipients of list ORACLE-L Is anyone using the LOG_ARCHIVE_DUPLEX_DEST parameter? Any problems with it? We are on Oracle 8.1.6 on Compaq Tru64. On our ERP 150-gig. database, we are currently doing a cold backup weekly. This means that we are very dependent on archive logs. - Recovery time isn't a big concern. In the event of a failure, recovery time isn't that critical. Loss of a week of work would be. - We have tried hot backups successfully on smaller databases, but we found that once we issue ALTER TABLESPACE BEGIN BACKUP there was often a delay of hours for that command to complete. So we gave up on hot backups. - We are starting to work with RMAN and feel in the long run this will be an ideal solution. Thanks to everyone for answering my newby RMAN questions. - Recently Oracle has added a feature to duplex the archive log files. This sounds like it would lessen our archive log vulnerability. However, it causes my sys admin more work, so I would appreciate some feedback before I add this burden to his work. Thanks for any thoughts you care to share. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Serial# changes when rolling back
Check this Oracle Note : Doc ID: Note:1020545.102 Subject: ORA-00026: CANNOT KILL SESSION; SERIAL# KEEPS CHANGING Type: PROBLEM Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 23-NOV-1999 Last Revision Date: 24-AUG-2000 Problem Description --- You have killed a process at the operating system level that was running a long-running transaction. Now, you are trying to issue the command: alter system kill session 'sid, serial#'; To kill the associated Oracle session, but you can't kill it. You may receive the following error: ORA-00026: missing or
RE: Database down
Or check whether u have a large pool too..reduce the large_pool_size parameter. Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 10 December 2001 20:16 To: Multiple recipients of list ORACLE-L There is not enough memory on the server to allocate the shared memory that the init.ora requires. Make you shared_pool smaller and try again. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 10, 2001 2:25 PM ORA-27123: unable to attach to shared memory segment SVR4 Error: 12: Not enough space Any ideas? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Wiegard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Setting Expiry Date of User Password
Hi List,
is there a possibility to set the expiry date of the password to a date ?
Background: After a migration to Oracle 8.0.6 we set the expire password option to all
users but when the user logs on
ORA-1841 ((full) year must be between -4713 and +, and not be 0
Only after the user change his/her password the error disappear.
Is there a solution for this problem ?
Klaus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Zinsmeister, Klaus {PTBF~Basel}
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
The Oracle List Archive?
Title: The Oracle List Archive? Hi, Forgive the rather dumb newbie question. However I regularly see people on this list refer to the archive. How can I access this? I have poked around at fatcity.com and not found much help! TIA Denham This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Re: Database link
db_domain in init.ora is commented out in both cases. I also set it to: db_domain = but it didn't make any difference. Setting GLOBAL_NAMES to false helped but someone mentioned that it is not a good practice. Can anybody explain little bit why? Thanks Witold On 10 Dec 2001 at 21:50, Nikunj Gupta wrote: Check for DB_DOMAIN.. in INIT.ORA else Set GLOBAL_NAMES to FALSE ALTER SYSTEM SET GLOBAL_NAMES=FALSE; HTH Make a FREE long distance call from your PC! http://www.eboom.com/free/ - Original Message - From: Witold Iwaniec To: Multiple recipients of list ORACLE-L Sent: Monday, December 10, 2001 11:00 AM Subject: Database link Hi DBAs I have a problem with database links in one of the databases - the name of the link gets us.oracle.com appended to it. create database link test connect to test_user identified by test_pwd using 'test_db' When I execute: select * from my_table@test I get the ORA-02085 error message the the link test.us.oracle.com connects to test. When I execute: select * from global_name; in the target database the result is: TEST but in the database where I created the link the database name has the suffix: NOVALIS.US.ORACLE.COM I have no problem with database links in the TEST database but would like to know which parameter causes the suffix to be appended in the NOVALIS database. In both init.ora files the global_names is set to true and db_domain is commented out. In both databases the command show parameters; returns the db_domain as blank. Actually both databases are on the same server - Oracle 8.1.6. What should I change to remove the US.ORACLE.COM suffix. I don't care too much about the database, it's my test only, but don't want it appended in the links and maybe other objects... Thanks Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
nomount
Hi Anyone now which 2 V$ views can be accessed while in nomount mode ? Also what is a tempfile ? TIA Saj Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DB Design and Views
Hi, as I remember, it was always recommended to avoid the use of views upon views upon views in the design of an Oracle DB (as for version 7/8), since the optimizer might get confused. Does that still apply ? I'm supposed to give some guidelines to developers about the usage of views. My point of view is, that views should only be used to grant limited access to tables in a schema (horizontal/vertical) or to consolidate/pre-calculate data. Any input ? Regards, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: The Oracle List Archive?
Title: The Oracle List Archive? Hi Eva, Try the following link to access the archives. http://www.fatcity.com/ListGuru/login.php I can't remember where you get the password from (I think it might be blank) - it validates on your e-mail address I believe John -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: 11 December 2001 12:00To: Multiple recipients of list ORACLE-LSubject: The Oracle List Archive? Hi, Forgive the rather dumb newbie question. However I regularly see people on this list refer to the archive. How can I access this? I have poked around at fatcity.com and not found much help! TIA Denham This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
Re: nomount
Hi Saj, I just tried at least 4 on 8.1.7 v$instance v$sga v$sgastat v$parameter There are probably more, don't know where the two number is coming from. John [EMAIL PROTECTED] wrote: Hi Anyone now which 2 V$ views can be accessed while in nomount mode ? Also what is a tempfile ? TIA Saj Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orantdba INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: nomount
Title: RE: nomount Is this some sort of OCP test for us listers?? -Original Message- From: Sajid Iqbal [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 12:20 To: Multiple recipients of list ORACLE-L Subject: nomount Hi Anyone now which 2 V$ views can be accessed while in nomount mode ? Also what is a tempfile ? TIA Saj Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: DB Design and Views
Uh Oh ... I have a forms application that is built on views that are built on views that are built on views ... up to 5 levels deep. The fun part is we have to go to CBO now ... and the tables underlying the views range from 10 rows to 20M rows. Talk about fun ... 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! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
Re: Database link
Hi Witold, if you use oracle replication you're limited to use global_name=true I don't know any other restrictions. Regards, Ed db_domain in init.ora is commented out in both cases. I also set it to: db_domain = but it didn't make any difference. Setting GLOBAL_NAMES to false helped but someone mentioned that it is not a good practice. Can anybody explain little bit why? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: nomount
lol looks like it eh? I know that you can view v$instance while the database is not mounted - not sure about the other one.. And - straight from the Oracle Concepts Manual (page 126) Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles except that: Tempfiles are always set to NOLOGGING mode. You cannot make a tempfile read-only. You cannot rename a tempfile. You cannot create a tempfile with the ALTER DATABASE statement. Media recovery does not recognize tempfiles: BACKUP CONTROLFILE does not generate any information for tempfiles. CREATE CONTROLFILE cannot specify any information about tempfiles. Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view. Looks like I failed my OCP :P Mark -Original Message- Sent: 11 December 2001 13:15 To: Multiple recipients of list ORACLE-L Is this some sort of OCP test for us listers?? -Original Message- Sent: 11 December 2001 12:20 To: Multiple recipients of list ORACLE-L Hi Anyone now which 2 V$ views can be accessed while in nomount mode ? Also what is a tempfile ? TIA Saj Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RMAN - archive log files
Must I have RMAN back up the archive log files? We are at the delicate position of getting acquainted with RMAN. We are using it but not yet ready to completely trust it, or rather trust that our setup is entirely correct (we production DBAs tend to be a conservative lot). We are just writing RMAN files to disk, no media manager. Needless to say, we are getting the disadvantages of both methods. In addition to the disk space taken up by the archive log files, I also have the disk space taken up by the RMAN archive log files, so I have used twice the amount of disk. If I didn't have RMAN store the archive log files, would it still be able to perform a recovery? Would it just grab the existing log files off disk? I'm thinking it must since it has to use some archive log files that were created since RMAN last ran. Appreciate your insights. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAID system max throughput
Thanks to everyone that shared their thoughts on this issue. Steven - Thanks very much for your insight of throughput vs. latency. I think this reduces the concepts to a simple enough level that I can explain them to others. The system administrators also do our wide-area networks and the focus tends to be on throughput, but they have also realized that latency times can also produce poor performance. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 07, 2001 8:40 PM To: Multiple recipients of list ORACLE-L -- DENNIS WILLIAMS [EMAIL PROTECTED] Whenever I discuss disk waits with my system administrator, I always get the reply that the RAID system isn't anywhere near its rated throughput. Maybe I'm wrong, but I don't see any of the tuning books mentioning that as a relevant performance characteristic. However, I've never been able to move the discussion beyond this point. Can anyone straighten me out on this point or point me to a resource that might be applicable. Our system is Oracle 8.1.6, Compaq Tru64. We use hardware RAID-5 with a battery-backed RAM cache, and have about 3 RAID sets (plus some extra disks for redo logs, etc.), and performance is fine, but I'm always looking as to how we can improve Oracle performance. The application is our corporate ERP system. Two things will zap you on device I/O: bandwidth or latency. Most people look at bandwidth -- same basic numbers for both networking and disks. Latency is basically the turnaround time. If you screw up the setup of any I/O system then latency can reduce performance to the point where bandwidth is irrelevant. By analogy, you can put concrete tires on a Porsche and go nowhere also. The real measure of what's going on starts at the O/S level looking at the frequency and duration of proc's in a device wait state (a.k.a. blocked for I/O) on the disks. If this is minimal then forget it. You can also end up with screwy results on large shared disk systems due to competition. SAN's can get placed on overloaded network segments; ERP's can easily get hot-spots from various users colliding. In general RAID5 with a stripe size == system I/O page will perform rather nicely. If your system page sizes vary or the raidset has an offball number of disks (e.g., 6 drives for an 8K page) then you'll take a hit writing extra data to maintain the RAID5 parity. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Listeners listening to multiple IPs
Something I learned today, but haven't seen it documented anywhere and would like to see whether anybody has come across it. We have a Sun server (call it oracle.acme.com). It came to life by combining two machines (oracle1.acme.com and oracle2.acme.com). So now it has two IP addresses: hme0: x.y.z.001 - from oracle1 and hme1: x.y.z.002 - from oracle2. There are few databases there (some of them were on oracle1 some on oracle2) and one listener listening to all of them. If you configure listener.ora file so that HOST=x.y.z.001, or HOST=x.y.z.002 that listener listens to that ONE IP only. However, if you put HOST=oracle.acme.com in listener.ora than it listens to all IPs (in netstat -an you see that it is listening to *.1521 instead of to oracle.acme.com.1521). That would mean that oracle does not resolve DNS at replaces it with IP but first checks whether this is the primary IP for that box. So, once we have put HOST=oracle.acme.com, all the connections were OK (coming from clients with tnsnames files that are pointing to old oracle1.acme.com and oracle2.acme.com) Of course DNS was changed.. Anybody has come across this undocumented feature ? Any explanation to that ? Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DB Design and Views
Forget the optimizer, what about the developer? This just doesn't seem like a good idea, or in the words of Wayne Brady 'That is just wrong on so many levels'. Jared On Tuesday 11 December 2001 04:30, Stefan Jahnke wrote: Hi, as I remember, it was always recommended to avoid the use of views upon views upon views in the design of an Oracle DB (as for version 7/8), since the optimizer might get confused. Does that still apply ? I'm supposed to give some guidelines to developers about the usage of views. My point of view is, that views should only be used to grant limited access to tables in a schema (horizontal/vertical) or to consolidate/pre-calculate data. Any input ? Regards, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: select data using trigger
Hope I'm not being blunt here, ( I will admit to being frank, but not Frank ) What you're suggesting here is to put a patch on a bad design. 15 indexes? That's too many. Your table is undoubtedly highly denormalized and has too many columns. How many columns does this table have? You really need to take a step back and look at the design of the data you are querying. If this is really critical data ( Contracts? Sounds critical ) then strongly consider bringing in a consultant with strong data modeling and database design experience to help you put this data in a manageable form. Or maybe you need to build a data mart and use partitioning? This sounds to me like you need more consulting that you can get from a few emails on a list server. Jared On Monday 10 December 2001 19:50, Tatireddy, Shrinivas (MED, Keane) wrote: Hi lists, Can anybody help me...? Table CONTRACTS is accessed by many applications and has many selects hit this table. This table has 15 indexes. Still It needs some more indexes. But we were instructed to stop creating indexes. We have another table CONT2 in another schema. This table is copy of CONTRACTS. Is there a way to write a trigger on CONT2 for the following issue: if a particular select / select statement issued against CONTRACTS table, that query should select data from CONT2. (There is only one particular select statement that is more resource intensive. We need to divert that query to CONT2. As the applications are already tuned, we were not permitted to modify that code.) Thnx in advance, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: The Oracle List Archive?
No, the password is not blank. You have to supply one the first time you use it. Jared On Tuesday 11 December 2001 05:00, you wrote: Hi Eva, Try the following link to access the archives. http://www.fatcity.com/ListGuru/login.php http://www.fatcity.com/ListGuru/login.php I can't remember where you get the password from (I think it might be blank) - it validates on your e-mail address I believe John -Original Message- Sent: 11 December 2001 12:00 To: Multiple recipients of list ORACLE-L Hi, Forgive the rather dumb newbie question. However I regularly see people on this list refer to the archive. How can I access this? I have poked around at fatcity.com and not found much help! TIA Denham _ 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 _ = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. = Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: query rewrite
Try with TIMESTAMP literal. Like this: create materialized view mv_test enable query rewrite as select * from birthdays where birth= timestamp '1971-05-03 09:00:00 PM' at time zone 'Europe/London'; Regards. --- elain he [EMAIL PROTECTED] wrote: Hi, Does anyone know how can I create a materialized view for query rewrite on the following query: select distinct parttable from emp where exists (select * from addr where ((addr.zip=24811) and emp.timestamp='11/23/2001') and emp.id=addr.id and emp.sn=addr.sn) order by emp.parttable; I tried creating a materialized view but it errored out on the emp.timestamp='11/23/2001'. ORA-30353: expression not supported for query rewrite I created another materialized view without the 'timestamp' and it got created fine. ie select distinct parttable from emp where exists (select * from addr where ((addr.zip=24811)) and emp.id=addr.id and emp.sn=addr.sn) order by emp.parttable; Now, when I try running the query below, Oracle does not do a query rewrite but when I ran the query without referencing 'timestamp', Oracle uses the materialized view. The query below needs to reference the timestamp. How can I create the materialized view to enable Oracle to utilize the materialized view whenever the below query is ran? select distinct parttable from emp where exists (select * from addr where ((addr.zip=24811) and emp.timestamp='11/23/2001') and emp.id=addr.id and emp.sn=addr.sn) order by emp.parttable; thanks in advance for any assistance you can provide. elain _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB Design and Views
I would avoid views based on view. As well, I would avoid using views where you will later turn around and throw a distinct or a group by or anything else on it (especially if you are dealing with a lot of data). Basically, You cannot be assured that the optimizer will pick the proper path once the select is within a view and you do anything other then a straight select on that. I have seen it work fine and I have seen it bring an application to a grinding halt. -Original Message- Jahnke Sent: Tuesday, December 11, 2001 4:30 AM To: Multiple recipients of list ORACLE-L Hi, as I remember, it was always recommended to avoid the use of views upon views upon views in the design of an Oracle DB (as for version 7/8), since the optimizer might get confused. Does that still apply ? I'm supposed to give some guidelines to developers about the usage of views. My point of view is, that views should only be used to grant limited access to tables in a schema (horizontal/vertical) or to consolidate/pre-calculate data. Any input ? Regards, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: nomount
v$parameter v$sga v$option v$process v$session v$version v$instance nomount stage, right from my Oracle Admin book published by Oracle. David Ehresmann Oracle DBA 8 8i OCP MCI Worldcom -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sajid Iqbal Sent: Tuesday, December 11, 2001 6:20 AM To: Multiple recipients of list ORACLE-L Subject: nomount Hi Anyone now which 2 V$ views can be accessed while in nomount mode ? Also what is a tempfile ? TIA Saj Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: nomount
Hi, Views that can be read in the nomount stage are: v$parameter, v$sga, v$option, v$process, v$session, v$version and v$instance. And tempfile is latest method of defining Oracle database temporary files for Temp tablespaces. Mujeeb Chowdhry [EMAIL PROTECTED] 12/11/01 07:20AM HiAnyone now which 2 V$ views can be accessed while in nomount mode ?Also what is a tempfile ?TIASaj Iqbal-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Sajid Iqbal INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: select data using trigger
Hi Thnq for responding. I am mistaken, in understanding the request. I am supposed to create a trigger that will do update onto a target table con2 whenever the contract is updated. 15 indexes on a table: There are several tables that have 15 indexes. All such tables will have 10-20,000 rows and are majorly used for querying. They have around 10-30 columns. But we never got a complaint, that the accessing is slow. Is this a harmful situation, having 15 indexes on a table. (The table data almost never grow) Thnx and regards, Srinvias -Original Message- Sent: Tuesday, December 11, 2001 11:56 AM To: [EMAIL PROTECTED]; Tatireddy, Shrinivas (MED, Keane) Hope I'm not being blunt here, ( I will admit to being frank, but not Frank ) What you're suggesting here is to put a patch on a bad design. 15 indexes? That's too many. Your table is undoubtedly highly denormalized and has too many columns. How many columns does this table have? You really need to take a step back and look at the design of the data you are querying. If this is really critical data ( Contracts? Sounds critical ) then strongly consider bringing in a consultant with strong data modeling and database design experience to help you put this data in a manageable form. Or maybe you need to build a data mart and use partitioning? This sounds to me like you need more consulting that you can get from a few emails on a list server. Jared On Monday 10 December 2001 19:50, Tatireddy, Shrinivas (MED, Keane) wrote: Hi lists, Can anybody help me...? Table CONTRACTS is accessed by many applications and has many selects hit this table. This table has 15 indexes. Still It needs some more indexes. But we were instructed to stop creating indexes. We have another table CONT2 in another schema. This table is copy of CONTRACTS. Is there a way to write a trigger on CONT2 for the following issue: if a particular select / select statement issued against CONTRACTS table, that query should select data from CONT2. (There is only one particular select statement that is more resource intensive. We need to divert that query to CONT2. As the applications are already tuned, we were not permitted to modify that code.) Thnx in advance, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: nomount
No, these are questions I have been asked but couldn't readily find the information on... so I thought someone out there must know :-) Regards Saj On Tue, 11 Dec 2001, Mark Leith wrote: lol looks like it eh? I know that you can view v$instance while the database is not mounted - not sure about the other one.. And - straight from the Oracle Concepts Manual (page 126) Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles except that: Tempfiles are always set to NOLOGGING mode. You cannot make a tempfile read-only. You cannot rename a tempfile. You cannot create a tempfile with the ALTER DATABASE statement. Media recovery does not recognize tempfiles: BACKUP CONTROLFILE does not generate any information for tempfiles. CREATE CONTROLFILE cannot specify any information about tempfiles. Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view. Looks like I failed my OCP :P Mark -Original Message- Sent: 11 December 2001 13:15 To: Multiple recipients of list ORACLE-L Is this some sort of OCP test for us listers?? -Original Message- Sent: 11 December 2001 12:20 To: Multiple recipients of list ORACLE-L Hi Anyone now which 2 V$ views can be accessed while in nomount mode ? Also what is a tempfile ? TIA Saj Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Sajid Iqbal Database Team Leader VIA NET.WORKS 620 Birchwood Boulevard Birchwood Warrington WA3 7QZ DDI: +44 (0) 1925 484485 Fax: +44 (0) 1925 484466 Email: [EMAIL PROTECTED] Website: http://www.vianetworks.co.uk local touch global reach -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Why is this code not working?
When I run this code both columns I get no rows returned. When I do a desc on one of the tables I see both columns. So, why am I not getting any data? select table_name, column_name from dba_tab_columns where (column_name = 'REGISTRATION_NUMBER' and column_name = 'DOCUMENT_NUMBER') Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
TEMP Tablespace Problem
Somehow I deleted the TEMP tablespace but not the datafile. How do I go about recreating this tablespace? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: nomount
And you believed it ? -Original Message- Sent: Tuesday, December 11, 2001 9:10 AM To: Multiple recipients of list ORACLE-L v$parameter v$sga v$option v$process v$session v$version v$instance nomount stage, right from my Oracle Admin book published by Oracle. David Ehresmann Oracle DBA 8 8i OCP MCI Worldcom -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sajid Iqbal Sent: Tuesday, December 11, 2001 6:20 AM To: Multiple recipients of list ORACLE-L Subject: nomount Hi Anyone now which 2 V$ views can be accessed while in nomount mode ? Also what is a tempfile ? TIA Saj Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: select data using trigger
Well ... According to a document on Metalink .. it is pretty safe to assume the cost of a DML action on an index as 3 times of that of on the table. So, if inserting one record costs you one unit, on your table with 15 indexes it would be (1 * 1 table) + (3 * 15 indexes) = 46 units. And this is for each row ... HTH 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! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
excel data into oracle through sql loader
Hi lists, I need to load the excel sheet data to oracle tables. A date column is improperly entered by users. In the excel sheet the date column was filled up without using hyphens or slashes the data is like this todays date: 10th Dec 2001 (it is supposed to be 10/12/2001 or 10-Dec-2001 etc...) But it was entered in the cells as 101201 When I formatted the data using the excel option cells - format The data it is displaying in a strage format: 1/27/2177 All the date values are displayed improperly. Does anybody come across such a situation. How to format the date cells in excelsheet. (should I change any options for the sheet) Thnx in advance, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
sql query
Hi, I have this sql statement: select to_number(null) as id, to_char(null) as car_make from dual union select id, car_make from carmake order by car_make; So this selects a blank record and then the records from carmake. But I want the blank record to appear at the top of the list, and it must be done in the select statement. Can this be done? TIA, Thanks, Steven Hovington -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Why is this code not working?
Change the 'and' to 'or'. Ken Janusz wrote: When I run this code both columns I get no rows returned. When I do a desc on one of the tables I see both columns. So, why am I not getting any data? select table_name, column_name from dba_tab_columns where (column_name = 'REGISTRATION_NUMBER' and column_name = 'DOCUMENT_NUMBER') Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis M. Heisler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why is this code not working?
This was returned by the mailer-daemon because of a locking problem ?? -Oorspronkelijk bericht- Van: Jeroen van Sluisdam Verzonden: dinsdag 11 december 2001 17:08 Aan: '[EMAIL PROTECTED]' Onderwerp: RE: Why is this code not working? You need an or-clause because one row has either the value 'registration_number' or 'document_number', one of both is the issue Hth, Jeroen -Oorspronkelijk bericht- Van: Ken Janusz [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 11 december 2001 16:20 Aan: Multiple recipients of list ORACLE-L Onderwerp: Why is this code not working? When I run this code both columns I get no rows returned. When I do a desc on one of the tables I see both columns. So, why am I not getting any data? select table_name, column_name from dba_tab_columns where (column_name = 'REGISTRATION_NUMBER' and column_name = 'DOCUMENT_NUMBER') Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeroen van Sluisdam INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why is this code not working?
I guess this is what you want:
select table_name, column_name
from dba_tab_columns
where (column_name = 'REGISTRATION_NUMBER' or column_name =
'DOCUMENT_NUMBER')
or
select table_name, column_name
from dba_tab_columns
where column_name in ('REGISTRATION_NUMBER', 'DOCUMENT_NUMBER')
-Original Message-
Sent: Tuesday, December 11, 2001 5:20 PM
To: Multiple recipients of list ORACLE-L
**
This email has been tested for viruses by F-Secure Antivirus
administered by IT Network Department.
**
When I run this code both columns I get no rows returned. When I do a desc
on one of the tables I see both columns. So, why am I not getting any data?
select table_name, column_name
from dba_tab_columns
where
(column_name = 'REGISTRATION_NUMBER' and
column_name = 'DOCUMENT_NUMBER')
Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient System, Inc.
Minneapolis, MN
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ken Janusz
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
**
The information contained in this communication is confidential and
may be legally privileged. It is intended solely for the use of the
individual or entity to whom it is addressed and others authorised to
receive it. If you are not the intended recipient you are hereby
notified that any disclosure, copying, distribution or taking action in
reliance of the contents of this information is strictly prohibited and
may be unlawful. Mobil Rom is neither liable for the proper, complete
transmission of the information contained in this communication nor
any delay in its receipt.
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: RMAN - archive log files
If you want to be able to restore your database to a point in time or do a complete recovery you database you must have all of the archive logs you need. If you don't include them in a backup you must keep them in the archivelog destination, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 9:10 AM Must I have RMAN back up the archive log files? We are at the delicate position of getting acquainted with RMAN. We are using it but not yet ready to completely trust it, or rather trust that our setup is entirely correct (we production DBAs tend to be a conservative lot). We are just writing RMAN files to disk, no media manager. Needless to say, we are getting the disadvantages of both methods. In addition to the disk space taken up by the archive log files, I also have the disk space taken up by the RMAN archive log files, so I have used twice the amount of disk. If I didn't have RMAN store the archive log files, would it still be able to perform a recovery? Would it just grab the existing log files off disk? I'm thinking it must since it has to use some archive log files that were created since RMAN last ran. Appreciate your insights. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why is this code not working?
U - cause one row cannot have both values in the same column???
How about changing it to:
select table_name, column_name
from dba_tab_columns
where
(column_name = 'REGISTRATION_NUMBER' or
column_name = 'DOCUMENT_NUMBER')
or
where
(column_name in ('REGISTRATION_NUMBER','DOCUMENT_NUMBER')
Hope this helps
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, December 11, 2001 10:20 AM
To: Multiple recipients of list ORACLE-L
When I run this code both columns I get no rows returned. When I do a desc
on one of the tables I see both columns. So, why am I not getting any data?
select table_name, column_name
from dba_tab_columns
where
(column_name = 'REGISTRATION_NUMBER' and
column_name = 'DOCUMENT_NUMBER')
Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient System, Inc.
Minneapolis, MN
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ken Janusz
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Why is this code not working?
You are trying to look for a column name be equal to two different names... I assume you want an OR instead of the AND... select table_name, column_name from dba_tab_columns where (column_name = 'REGISTRATION_NUMBER' OR column_name = 'DOCUMENT_NUMBER') -Original Message- Sent: Tuesday, December 11, 2001 10:20 AM To: Multiple recipients of list ORACLE-L When I run this code both columns I get no rows returned. When I do a desc on one of the tables I see both columns. So, why am I not getting any data? select table_name, column_name from dba_tab_columns where (column_name = 'REGISTRATION_NUMBER' and column_name = 'DOCUMENT_NUMBER') Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Why is this code not working?
I think you are confusing the logical and with the fact that you want both columns. Try using or in place of and (column_name = 'REGISTRATION_NUMBER' or column_name = 'DOCUMENT_NUMBER') Pat When I run this code both columns I get no rows returned. When I do a desc on one of the tables I see both columns. So, why am I not getting any data? select table_name, column_name from dba_tab_columns where (column_name = 'REGISTRATION_NUMBER' and column_name = 'DOCUMENT_NUMBER') Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: excel data into oracle through sql loader
Have you considered to use findreplace tool in excel and replace all the th string with nothing, and then reformat the cell as you need. Iulian -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 6:00 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** Hi lists, I need to load the excel sheet data to oracle tables. A date column is improperly entered by users. In the excel sheet the date column was filled up without using hyphens or slashes the data is like this todays date: 10th Dec 2001 (it is supposed to be 10/12/2001 or 10-Dec-2001 etc...) But it was entered in the cells as 101201 When I formatted the data using the excel option cells - format The data it is displaying in a strage format: 1/27/2177 All the date values are displayed improperly. Does anybody come across such a situation. How to format the date cells in excelsheet. (should I change any options for the sheet) Thnx in advance, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why is this code not working?
funny, i run it in my database (mutatis mutandis for column names, natch) and it works JUST FINE with AND. buti am hoping an acknowledged SQL guru will step in and say this -Original Message- Change the 'and' to 'or'. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: excel data into oracle through sql loader
Hi Workaround: Treat the Column as text (within Excel) and transform it with a little VBA Macro into the format you want to use in Oracle. Tatireddy, Shrinivas (MED, Keane) schrieb: Hi lists, I need to load the excel sheet data to oracle tables. A date column is improperly entered by users. In the excel sheet the date column was filled up without using hyphens or slashes the data is like this todays date: 10th Dec 2001 (it is supposed to be 10/12/2001 or 10-Dec-2001 etc...) But it was entered in the cells as 101201 When I formatted the data using the excel option cells - format The data it is displaying in a strage format: 1/27/2177 All the date values are displayed improperly. Does anybody come across such a situation. How to format the date cells in excelsheet. (should I change any options for the sheet) Thnx in advance, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). - This Mail has been checked for Viruses Attention: Encrypted mails can NOT be checked! ** Diese Mail wurde auf Viren geprueft Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden! - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Listeners listening to multiple IPs
it's not so much a feature of the listener as it is of DNS as far as I know. One of the reasons it's best not to hard-code IP addresses into the listener.ora is because when you do a failover, or combine machines as you have, the listener.ora and tnsnames.ora files need to be updated. If you use DNS and let the DNS server resolve the address, you don't have to change anything. This has been around for as long as I can remember using the listener, which means going back to version 6 Rachel --- Djordje Jankovic [EMAIL PROTECTED] wrote: Something I learned today, but haven't seen it documented anywhere and would like to see whether anybody has come across it. We have a Sun server (call it oracle.acme.com). It came to life by combining two machines (oracle1.acme.com and oracle2.acme.com). So now it has two IP addresses: hme0: x.y.z.001 - from oracle1 and hme1: x.y.z.002 - from oracle2. There are few databases there (some of them were on oracle1 some on oracle2) and one listener listening to all of them. If you configure listener.ora file so that HOST=x.y.z.001, or HOST=x.y.z.002 that listener listens to that ONE IP only. However, if you put HOST=oracle.acme.com in listener.ora than it listens to all IPs (in netstat -an you see that it is listening to *.1521 instead of to oracle.acme.com.1521). That would mean that oracle does not resolve DNS at replaces it with IP but first checks whether this is the primary IP for that box. So, once we have put HOST=oracle.acme.com, all the connections were OK (coming from clients with tnsnames files that are pointing to old oracle1.acme.com and oracle2.acme.com) Of course DNS was changed.. Anybody has come across this undocumented feature ? Any explanation to that ? Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RV: Import/Export table
Hi, you can try: exp file= log= tables= owner = A imp file= log= ignore=y tables= fromuser=A touser=B - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 10, 2001 5:35 AM Hi list. I have 3 tables in a schema and I want to export them to another schema in another database. Any ideeas? Thanks! Iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Natalia Laracca INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
CREATE DATABASE scripts
Hi, I am trying to generate some generic CREATE DATABASE scripts from the Oracle Database Configuration assistant and I cannot see the Generate Database Creation Scripts option that is mentioned in the HELP. It is not listed on the screen with the Create Database and Save as a Database Template options. Can someone let me know how it is done with the 9i configuration assistant or pass on some Oracle 9i CREATE DATABASE scripts. Thanks, Lindsay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stoddard, Lindsay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: TEMP Tablespace Problem
Hi Assuming that you actually use the TEMP tablespace as a real temp tablespace and didn't put any actual data in it, you can just go ahead recreate it by reusing the old datafile. Ken Janusz schrieb: Somehow I deleted the TEMP tablespace but not the datafile. How do I go about recreating this tablespace? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). - This Mail has been checked for Viruses Attention: Encrypted mails can NOT be checked! ** Diese Mail wurde auf Viren geprueft Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden! - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sql query
Does this suit you? select to_number(null) as id, to_char(null) as car_make, 0 ordcol from dual union select id, car_make, 1 ordcol from carmake order by ordcol, car_make; Iulian -Original Message- Sent: Tuesday, December 11, 2001 5:45 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** Hi, I have this sql statement: select to_number(null) as id, to_char(null) as car_make from dual union select id, car_make from carmake order by car_make; So this selects a blank record and then the records from carmake. But I want the blank record to appear at the top of the list, and it must be done in the select statement. Can this be done? TIA, Thanks, Steven Hovington -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: TEMP Tablespace Problem
Use the REUSE option on the CREATE TABLESPACE command. Ken Janusz ken.janusz To: Multiple recipients of list ORACLE-L @sufsys.com [EMAIL PROTECTED] Sent by: rootcc: Subject: TEMP Tablespace Problem 12/11/2001 10:50 AM Please respond to ORACLE-L Somehow I deleted the TEMP tablespace but not the datafile. How do I go about recreating this tablespace? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why is this code not working?
Change the and in your WHERE clause to an or. Kevin L. Bass Database Administrator Americal Corporation -Original Message- Sent: Tuesday, December 11, 2001 9:20 AM To: Multiple recipients of list ORACLE-L When I run this code both columns I get no rows returned. When I do a desc on one of the tables I see both columns. So, why am I not getting any data? select table_name, column_name from dba_tab_columns where (column_name = 'REGISTRATION_NUMBER' and column_name = 'DOCUMENT_NUMBER') Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Bass INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: TEMP Tablespace Problem
Ken - I assume you dropped the TEMP tablespace, which doesn't delete the datafile. I'm assuming you are on Unix. Just remove the data file and recreate the TEMP tablespace the same way as you originally did. I believe that the temp tablespace settings for all users will be unaffected, but you might check one or two to make sure. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 11, 2001 9:50 AM To: Multiple recipients of list ORACLE-L Somehow I deleted the TEMP tablespace but not the datafile. How do I go about recreating this tablespace? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Why is this code not working?
one column (COLUMN_NAME) can't have two separate and distinct values. I think you want an or not an and in the query --- Ken Janusz [EMAIL PROTECTED] wrote: When I run this code both columns I get no rows returned. When I do a desc on one of the tables I see both columns. So, why am I not getting any data? select table_name, column_name from dba_tab_columns where (column_name = 'REGISTRATION_NUMBER' and column_name = 'DOCUMENT_NUMBER') Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Listeners listening to multiple IPs
Hi I did this (don't know wether it's documented or not), but somebody recommended to split it up into 2 listeners (1 for each interface) to have a better way of controlling the workload (especially with multiple instances). Made sense to me. Djordje Jankovic schrieb: Something I learned today, but haven't seen it documented anywhere and would like to see whether anybody has come across it. We have a Sun server (call it oracle.acme.com). It came to life by combining two machines (oracle1.acme.com and oracle2.acme.com). So now it has two IP addresses: hme0: x.y.z.001 - from oracle1 and hme1: x.y.z.002 - from oracle2. There are few databases there (some of them were on oracle1 some on oracle2) and one listener listening to all of them. If you configure listener.ora file so that HOST=x.y.z.001, or HOST=x.y.z.002 that listener listens to that ONE IP only. However, if you put HOST=oracle.acme.com in listener.ora than it listens to all IPs (in netstat -an you see that it is listening to *.1521 instead of to oracle.acme.com.1521). That would mean that oracle does not resolve DNS at replaces it with IP but first checks whether this is the primary IP for that box. So, once we have put HOST=oracle.acme.com, all the connections were OK (coming from clients with tnsnames files that are pointing to old oracle1.acme.com and oracle2.acme.com) Of course DNS was changed.. Anybody has come across this undocumented feature ? Any explanation to that ? Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). - This Mail has been checked for Viruses Attention: Encrypted mails can NOT be checked! ** Diese Mail wurde auf Viren geprueft Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden! - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sql query
Title: RE: sql query add NULLS FIRST after the ORDER BY CAR_MAKE: select to_number(null) as id, to_char(null) as car_make from dual union select id, car_make from carmake order by car_make NULLS LAST; Tony Aponte -Original Message- From: Steven Hovington [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 10:45 AM To: Multiple recipients of list ORACLE-L Subject: sql query Hi, I have this sql statement: select to_number(null) as id, to_char(null) as car_make from dual union select id, car_make from carmake order by car_make; So this selects a blank record and then the records from carmake. But I want the blank record to appear at the top of the list, and it must be done in the select statement. Can this be done? TIA, Thanks, Steven Hovington -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sql query
Steve, Use the NULLS FIRST option of the ORDER BY (and you probably want to change your UNION to a UNION ALL). Note that you could also include a constant in each select to enforce order, do an order by nvl(col,looow value), etc. Lots of ways, but, the NULLS FIRST option seems clearest to me. 1 select to_number(null), to_char(null) 2 from dual 3 union ALL 4 select empno, ename 5 from emp 6* order by 1 nulls first SQL / TO_NUMBER(NULL) TO_CHAR(NU --- -- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 15 rows selected. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Steven Hovington Sent: Tuesday, December 11, 2001 9:45 AM To: Multiple recipients of list ORACLE-L Subject: sql query Hi, I have this sql statement: select to_number(null) as id, to_char(null) as car_make from dual union select id, car_make from carmake order by car_make; So this selects a blank record and then the records from carmake. But I want the blank record to appear at the top of the list, and it must be done in the select statement. Can this be done? TIA, Thanks, Steven Hovington -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA 0600 on histgrm$ after computed statistics on database
Title: ORA 0600 on histgrm$ after computed statistics on database Hello, I keep getting numerous ORA 600 since I have launched: dbms_utility.analyze_database and dbms_stats.gather_database_stats Has anybody run into this type of problem ? PS: get ORA 600 on : select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket Alain Saury L'Oréal - Informatique RAD Clichy Tel: 147 56 84 29 This message and any attachments are confidential and intended solely for the addressees. If you receive this message in error, please delete it and immediately notify the sender. If the reader of this message is not the intended recipient, you are hereby notified that any unauthorized use, copying or dissemination is prohibited. E-mails are susceptible to alteration. Neither L'OREAL nor any of its subsidiaries or affiliates shall be liable for the message if altered, changed or falsified.
RE: sql query
Title: RE: sql query Oops. The statement was wrong. I'll try it gain: select to_number(null) as id, to_char(null) as car_make from dual union select id, car_make from carmake order by car_make NULLS FIRST; Tony Aponte -Original Message- From: Aponte, Tony Sent: Tuesday, December 11, 2001 12:21 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: sql query add NULLS FIRST after the ORDER BY CAR_MAKE: select to_number(null) as id, to_char(null) as car_make from dual union select id, car_make from carmake order by car_make NULLS LAST; Tony Aponte -Original Message- From: Steven Hovington [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 10:45 AM To: Multiple recipients of list ORACLE-L Subject: sql query Hi, I have this sql statement: select to_number(null) as id, to_char(null) as car_make from dual union select id, car_make from carmake order by car_make; So this selects a blank record and then the records from carmake. But I want the blank record to appear at the top of the list, and it must be done in the select statement. Can this be done? TIA, Thanks, Steven Hovington -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Where is the memory gone?
I agree with Christian. We had the same memory leak problem here before. It turned out there are some heavily used sql statement without the bind variables. --- Christian Trassens [EMAIL PROTECTED] wrote: Answering your first message about the shrink of free space in shared pool. Check the parsing ratio: The hard parse with something like this SELECT substr(sql_text,1,40) SQL, count(*) , sum(executions) TotExecs FROM v$sqlarea WHERE executions 5 GROUP BY substr(sql_text,1,40) HAVING count(*) 30 ORDER BY 2 ; The soft parsing something like this: select count(*) from v$sqlarea where version_count5; And about parsing look for a note in metalink about parameter _sqlexec_progression_cost. Try with cursor_sharing. Also refer to possibles bugs related in metalink. And about parsing you can also look in the v$sysstat for statistics related. Check the shared pool latch rate too. Regards. --- Kimberly Smith [EMAIL PROTECTED] wrote: Only thing I have to add here is that I was not using MTS so which ever bug I was hitting (doco is at the office) it was not related to that. -Original Message- Sent: Sunday, December 09, 2001 9:50 PM To: Multiple recipients of list ORACLE-L WinterSun, Hmm, this doesn't sound like bug 1397603. That bug manifests itself as a memory leak in the 'State objects' area of the shared pool, not the 'Miscellaneous' area. Bug 1397603 is also fixed in the 8.1.7.2 patchset, so if you already have that applied (hint, hint!) you're not encountering this bug and there's no need to set _db_handles_cached = 0. I believe the bug Kimberly is referring to is bug 1240484, which is a process memory leak (i.e. ORA-4030, not ORA-4031) with MTS shared server sessions when there are frequent connect/disconnects. That bug is fixed in 8.1.7.1. If IOT's are involved, you could be hitting bug 1642964 if the IOT is the inner table in a nested loop join. Fixed in 9.0.1. Workaround: set optimizer_index_caching = 1 I'm more inclined to believe you're hitting bug 1921561 or bug 1970290, both of which have been awaiting more info from the customer for whom the bug was filed. The workaround for both of them was to set STAR_TRANSFORMATION_ENABLED = FALSE or _db_file_noncontig_mblock_read_count = 1. If you use bitmap indexes, disabling star transformation is likely to cause a noticeable performance hit so you might want to try the other workaround. Otherwise, if you can reproduce this in a test environment, it would be helpful to file a tar with support so a bug can be filed. HTH, -- Anita --- WinterSun Zhao [EMAIL PROTECTED] wrote: Hi, Kimberly: Thank you for your guide. I checked Metalink and find that is a bug. Bug No. 1397603. I think I will add the parameter _db_handles_cached to 1 later. I will also patch it to 8.1.7.2 too. Thank all of you. B.R. This sound pretty much like the same problem I had with 8.1.7 on HP. They have a memory leak when you connect/disconnect and it you have a lot of those you start to see it in your SGA. I had to patch to 8.1.7.1 plus an additional bug fix. Check on Metalink to see if there is the same problem with Windows. It also could be that you really do need more then 50M. Pin in the bigger packages you use (including Oracle's) right after startup and see where you are memory wise. -Original Message- Zhao Sent: Sunday, December 09, 2001 5:30 PM To: Multiple recipients of list ORACLE-L Hi, DBAs: I find that one of our database's Shared Pool's memory decreased every day. It is Oracle 8.1.7 on Windows 2K, with 512M Physical memory. When I check v$sgastat, I find the miscellaneous part of shared pool increased every day, it begans with 500K, then, after two days, it increased to 5586228 bytes, after about 10 days, it increased to 40M, and because I had allocated 50M to the Shared Pool, So the memory available became less and less. And I had to shutdown and restart the database when the available memory of shared pool is below 5M. I want to know why the memory occupied by miscellaneous part is increased? The other database on solaris did not increased. How can I find out what is it? How to prevent it or resolve it without shutdown and restart the database? ( alter system flush shared pool only flush the sql and library cache, and it didn't decrease the miscellaneous part's memory usage. ) Thank you very much! WinterSun __ Do You Yahoo!? Send your FREE holiday
RE: DB Design and Views
Stephan, Contrary to what has been mentioned, the optimizer actually performs a number of transformations on the submitted query using the Query transformer - this includes 'view merging' which basically rewrites the query by merging the view query block into the query block that contains the view. According to the doco, most of the views are merged, with an exception of few types of views. Now there is no detail on what these few types are, but I do know that 'simple' views that provide a layer for security (and multiple 'simple' layers for that matter) are efficiently merged. I think Guy Harrison's SQL tuning book has the details. Warm wishes for the season, John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Wanna know the reason for the season? Click on 'http://www.needhim.org' ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Kimberly Smith [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 6:40 AM To: Multiple recipients of list ORACLE-L Subject: RE: DB Design and Views I would avoid views based on view. As well, I would avoid using views where you will later turn around and throw a distinct or a group by or anything else on it (especially if you are dealing with a lot of data). Basically, You cannot be assured that the optimizer will pick the proper path once the select is within a view and you do anything other then a straight select on that. I have seen it work fine and I have seen it bring an application to a grinding halt. -Original Message- Jahnke Sent: Tuesday, December 11, 2001 4:30 AM To: Multiple recipients of list ORACLE-L Hi, as I remember, it was always recommended to avoid the use of views upon views upon views in the design of an Oracle DB (as for version 7/8), since the optimizer might get confused. Does that still apply ? I'm supposed to give some guidelines to developers about the usage of views. My point of view is, that views should only be used to grant limited access to tables in a schema (horizontal/vertical) or to consolidate/pre-calculate data. Any input ? Regards, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Lookup table design thoughts needed
Title: RE: Lookup table design thoughts needed I would start by considering how the application is deployed. For code that is easily deployed (E.I.. executables are located on a few application servers or a shared drive) I would consider compiling the rule data along with the logic. It generally improves run-time performance because there is no database access, network traffic, etc. Initialization of the bloated executable would be offset by the run-time savings. You incur a penalty when adding a new codes since you have to recompile and deploy the new executable but itsanotsobad in an app. server/shared drive deployment. Another consideration to take is the frequency that rules may change. Your example for state codes has remained the same since the 50's. I would pursue the hardcoded route if the code tables are more or less stable, or if the changes in the rules would involve code changes anyway. In a 2-tier client-server deployment I would go the database route, although separate tables for each type of code. This will simplify code table changes by not requiring a redeployment of the compiled code. Tony Aponte -Original Message- From: Tracy Rahmlow [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 05, 2001 12:15 PM To: Multiple recipients of list ORACLE-L Subject: Lookup table design thoughts needed We are currently looking at rewriting our entry system and one issue that I am looking for some feedback involves the use of lookup tables and populating/editing screens. We are looking at creating a generic table that contains all the valid entries for each drop-down list. For example, we may display a list of valid states for the user to select. The proposed edit table contains a row for each state with the following columns as an example: table_name: address column_name: state_cd code: WI description: Wisconsin In addition, we have situations on the screen where a user may select option 'a' in a drop-down list, but can not choose option 'c,d or f' in a different drop down list. Any suggestions for designing a flexible system that would incorporate issues like the above. We have been considering either hard-coding the edits within the screen as well as creating a rules/validation table that would incorporate these edits. How practical is a rules table? (We do have situations where we may have multiple entries to validate to each other). I realize these are very broad questions, so I am looking for generic theories that may be applied that are flexible for adapting to changes within the business. What else should I consider? It appears as if there are several ways to skin the cat how do we go about choosing the best method for our situation. In addition, does anybody know of any good websites/books that contain relational design strategies, tips ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN - archive log files
Thanks, Ruth. Because we aren't yet ready to bet the farm on RMAN, we are keeping them in both locations. And since we only do a weekly cold backup, we keep a week's worth on disk. I'm thinking that for the time being we can forgo RMAN backing up the archive logs. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 11, 2001 10:35 AM To: Multiple recipients of list ORACLE-L If you want to be able to restore your database to a point in time or do a complete recovery you database you must have all of the archive logs you need. If you don't include them in a backup you must keep them in the archivelog destination, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 9:10 AM Must I have RMAN back up the archive log files? We are at the delicate position of getting acquainted with RMAN. We are using it but not yet ready to completely trust it, or rather trust that our setup is entirely correct (we production DBAs tend to be a conservative lot). We are just writing RMAN files to disk, no media manager. Needless to say, we are getting the disadvantages of both methods. In addition to the disk space taken up by the archive log files, I also have the disk space taken up by the RMAN archive log files, so I have used twice the amount of disk. If I didn't have RMAN store the archive log files, would it still be able to perform a recovery? Would it just grab the existing log files off disk? I'm thinking it must since it has to use some archive log files that were created since RMAN last ran. Appreciate your insights. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
UKOUG Drink!
Hi guys and gals, OK - so who's going to be going to the UKOUG on Monday? And who wants to meet up for a beer? I have a partial list of people, but if we could all confirm now that would great! The thoughts on the venue were Broad Street - maybe somewhere like Ronnie Scotts? Or the cafe/restaurant area out the back of the ICC in Brindley place - across the canal. So far there will be me. lol. On my own if I have to! Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
UKOUG
Hi guys and gals, OK - so who's going to be going to the UKOUG on Monday? And who wants to meet up for a beer? I have a partial list of people, but if we could all confirm now that would great! The thoughts on the venue were Broad Street - maybe somewhere like Ronnie Scotts? Or the cafe/restaurant area out the back of the ICC in Brindley place - across the canal. So far there will be me. lol. On my own if I have to! Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How Big is Your SAP Database?
We are trying to get an idea of how much disk we'll need to roll out the MySAP suite to about 200 users on Oracle 8.1.7/HP-UX 11. We're planning on using most of the core SAP package and not CRM or APO. Can anyone else give me a guess on how much disk space we might need? Jon Behnke Applications Development Manager Industrial Electric Wire Cable Phone (262) 957-1147 Fax (262) 957-1647 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Behnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA 0600 on histgrm$ after computed statistics on database
Title: ORA 0600 on histgrm$ after computed statistics on database Hi, I think you should not analyze sys schema. - Original Message - From: SAURY Alain To: Multiple recipients of list ORACLE-L Sent: Tuesday, December 11, 2001 6:10 PM Subject: ORA 0600 on histgrm$ after computed statistics on database Hello, I keep getting numerous ORA 600 since I have launched: dbms_utility.analyze_database and dbms_stats.gather_database_stats Has anybody run into this type of problem ? PS: get ORA 600 on : select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket Alain Saury L'Oréal - Informatique RAD Clichy Tel: 147 56 84 29 This message and any attachments are confidential and intended solely for the addressees. If you receive this message in error, please delete it and immediately notify the sender. If the reader of this message is not the intended recipient, you are hereby notified that any unauthorized use, copying or dissemination is prohibited. E-mails are susceptible to alteration. Neither L'OREAL nor any of its subsidiaries or affiliates shall be liable for the message if altered, changed or falsified.
RE: Oracle on Pentium 4 conflict
I have a P4 - which I actually had the below problem with on both 8.1.7, and 9i. The below problem fixed the install problems with 8.1.7, but 9i STILL won't install on my machine (Win2K O.S.). It keeps complaining that it cant find jrew.exe, and that I need to install JRE 1.2.1(?) or greater.. I have the JRE 1.3.1 installed! I gave up! -Original Message- Sent: 07 December 2001 01:30 To: Multiple recipients of list ORACLE-L Except for the most current version of 9i -Original Message- Sent: Thursday, December 06, 2001 2:40 PM To: Multiple recipients of list ORACLE-L Ron, does this affect 9i? Dave Ron Rogers [EMAIL PROTECTED] 12/07/01 04:28am List, Received this the the SearchDatabase org today. I thought some of you might find it usefull. ROR mª¿ªm ORACLE ON PENTIUM 4 CONFLICT | by Liviu Obreja Here's a quick tip: The installation of Oracle8i on a Pentium 4 computer will exit without any notice. The reason for this is that the Pentium 4 architecture has problems with the Just In Time installer. To fix this problem, just find all copies of the file symcjit.dll and rename them to symcjit.org. Then the installer will work. Copy the installation CD to the HDD and make the changes, then install from the HDD. Voila! CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci784876,00.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail, including any attachments sent with it, is confidential and for the sole use of the intended recipient(s). This confidentiality is not waived or lost if you receive it and you are not the intended recipient(s), or if it is transmitted/ received in error. Any unauthorised use, alteration, disclosure, distribution or review of this e-mail is prohibited. It may be subject to a statutory duty of confidentiality if it relates to health service matters. If you are not the intended recipient(s), or if you have received this e-mail in error, you are asked to immediately notify the sender by telephone or by return e-mail. You should also delete this e-mail message and destroy any hard copies produced. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David McVeigh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khoa Do INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Listeners listening to multiple IPs
I tried doing this but had problems running two listener that listen on the same port number on two different IPs. One of the two did not want to start. Djordje -Original Message- From: Stefan Jahnke [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 12:01 PM To: Multiple recipients of list ORACLE-L Subject: Re: Listeners listening to multiple IPs Hi I did this (don't know wether it's documented or not), but somebody recommended to split it up into 2 listeners (1 for each interface) to have a better way of controlling the workload (especially with multiple instances). Made sense to me. Djordje Jankovic schrieb: Something I learned today, but haven't seen it documented anywhere and would like to see whether anybody has come across it. We have a Sun server (call it oracle.acme.com). It came to life by combining two machines (oracle1.acme.com and oracle2.acme.com). So now it has two IP addresses: hme0: x.y.z.001 - from oracle1 and hme1: x.y.z.002 - from oracle2. There are few databases there (some of them were on oracle1 some on oracle2) and one listener listening to all of them. If you configure listener.ora file so that HOST=x.y.z.001, or HOST=x.y.z.002 that listener listens to that ONE IP only. However, if you put HOST=oracle.acme.com in listener.ora than it listens to all IPs (in netstat -an you see that it is listening to *.1521 instead of to oracle.acme.com.1521). That would mean that oracle does not resolve DNS at replaces it with IP but first checks whether this is the primary IP for that box. So, once we have put HOST=oracle.acme.com, all the connections were OK (coming from clients with tnsnames files that are pointing to old oracle1.acme.com and oracle2.acme.com) Of course DNS was changed.. Anybody has come across this undocumented feature ? Any explanation to that ? Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). - This Mail has been checked for Viruses Attention: Encrypted mails can NOT be checked! ** Diese Mail wurde auf Viren geprueft Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden! - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: UKOUG
WOW!! They got through!! I only sent these last Wednesday!!! -Original Message- Sent: 11 December 2001 17:55 To: Multiple recipients of list ORACLE-L Hi guys and gals, OK - so who's going to be going to the UKOUG on Monday? And who wants to meet up for a beer? I have a partial list of people, but if we could all confirm now that would great! The thoughts on the venue were Broad Street - maybe somewhere like Ronnie Scotts? Or the cafe/restaurant area out the back of the ICC in Brindley place - across the canal. So far there will be me. lol. On my own if I have to! Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Listeners listening to multiple IPs
I am not talking whether putting DNS entry works, but am just pointing to the fact that oracle behaves differently if you put hardcoded IP - it than listens to one IP only, and if you put the dns name - listens to all IPs. Djordje -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:50 AM To: Multiple recipients of list ORACLE-L Subject: Re: Listeners listening to multiple IPs it's not so much a feature of the listener as it is of DNS as far as I know. One of the reasons it's best not to hard-code IP addresses into the listener.ora is because when you do a failover, or combine machines as you have, the listener.ora and tnsnames.ora files need to be updated. If you use DNS and let the DNS server resolve the address, you don't have to change anything. This has been around for as long as I can remember using the listener, which means going back to version 6 Rachel --- Djordje Jankovic [EMAIL PROTECTED] wrote: Something I learned today, but haven't seen it documented anywhere and would like to see whether anybody has come across it. We have a Sun server (call it oracle.acme.com). It came to life by combining two machines (oracle1.acme.com and oracle2.acme.com). So now it has two IP addresses: hme0: x.y.z.001 - from oracle1 and hme1: x.y.z.002 - from oracle2. There are few databases there (some of them were on oracle1 some on oracle2) and one listener listening to all of them. If you configure listener.ora file so that HOST=x.y.z.001, or HOST=x.y.z.002 that listener listens to that ONE IP only. However, if you put HOST=oracle.acme.com in listener.ora than it listens to all IPs (in netstat -an you see that it is listening to *.1521 instead of to oracle.acme.com.1521). That would mean that oracle does not resolve DNS at replaces it with IP but first checks whether this is the primary IP for that box. So, once we have put HOST=oracle.acme.com, all the connections were OK (coming from clients with tnsnames files that are pointing to old oracle1.acme.com and oracle2.acme.com) Of course DNS was changed.. Anybody has come across this undocumented feature ? Any explanation to that ? Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN - archive log files
Dennis, To perform media recovery using RMAN, it is necessary that u backup archived log files too since it is the key to a successful recovery. I suggest that u store the RMAN recovery catalog on a different machine cos if ur machine crashes thus crashing the database, ur RMAN catalog goes as well. Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 11 December 2001 14:10 To: Multiple recipients of list ORACLE-L Must I have RMAN back up the archive log files? We are at the delicate position of getting acquainted with RMAN. We are using it but not yet ready to completely trust it, or rather trust that our setup is entirely correct (we production DBAs tend to be a conservative lot). We are just writing RMAN files to disk, no media manager. Needless to say, we are getting the disadvantages of both methods. In addition to the disk space taken up by the archive log files, I also have the disk space taken up by the RMAN archive log files, so I have used twice the amount of disk. If I didn't have RMAN store the archive log files, would it still be able to perform a recovery? Would it just grab the existing log files off disk? I'm thinking it must since it has to use some archive log files that were created since RMAN last ran. Appreciate your insights. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
netasst in linux
Hi all, I'm trying to ruin netasst in a linux box running RH7.2, but it only sits there doing nothing. Does anybody know if I have to set something before? I was able to create the DB w/o a problem. any help greatly appreciated TIA --Yohans -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yohans Mendoza INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Listeners listening to multiple IPs
well yeah... that's the whole point of USING DNS, so that it will work like that --- Djordje Jankovic [EMAIL PROTECTED] wrote: I am not talking whether putting DNS entry works, but am just pointing to the fact that oracle behaves differently if you put hardcoded IP - it than listens to one IP only, and if you put the dns name - listens to all IPs. Djordje -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:50 AM To: Multiple recipients of list ORACLE-L Subject: Re: Listeners listening to multiple IPs it's not so much a feature of the listener as it is of DNS as far as I know. One of the reasons it's best not to hard-code IP addresses into the listener.ora is because when you do a failover, or combine machines as you have, the listener.ora and tnsnames.ora files need to be updated. If you use DNS and let the DNS server resolve the address, you don't have to change anything. This has been around for as long as I can remember using the listener, which means going back to version 6 Rachel --- Djordje Jankovic [EMAIL PROTECTED] wrote: Something I learned today, but haven't seen it documented anywhere and would like to see whether anybody has come across it. We have a Sun server (call it oracle.acme.com). It came to life by combining two machines (oracle1.acme.com and oracle2.acme.com). So now it has two IP addresses: hme0: x.y.z.001 - from oracle1 and hme1: x.y.z.002 - from oracle2. There are few databases there (some of them were on oracle1 some on oracle2) and one listener listening to all of them. If you configure listener.ora file so that HOST=x.y.z.001, or HOST=x.y.z.002 that listener listens to that ONE IP only. However, if you put HOST=oracle.acme.com in listener.ora than it listens to all IPs (in netstat -an you see that it is listening to *.1521 instead of to oracle.acme.com.1521). That would mean that oracle does not resolve DNS at replaces it with IP but first checks whether this is the primary IP for that box. So, once we have put HOST=oracle.acme.com, all the connections were OK (coming from clients with tnsnames files that are pointing to old oracle1.acme.com and oracle2.acme.com) Of course DNS was changed.. Anybody has come across this undocumented feature ? Any explanation to that ? Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid
RE: Listeners listening to multiple IPs
I used to define it on localhost (127.0.0.1) and this worked great with all ip addresses (only on Solaris). Did not work on NT! Regards, Waleed -Original Message- Sent: Tuesday, December 11, 2001 9:40 AM To: Multiple recipients of list ORACLE-L Something I learned today, but haven't seen it documented anywhere and would like to see whether anybody has come across it. We have a Sun server (call it oracle.acme.com). It came to life by combining two machines (oracle1.acme.com and oracle2.acme.com). So now it has two IP addresses: hme0: x.y.z.001 - from oracle1 and hme1: x.y.z.002 - from oracle2. There are few databases there (some of them were on oracle1 some on oracle2) and one listener listening to all of them. If you configure listener.ora file so that HOST=x.y.z.001, or HOST=x.y.z.002 that listener listens to that ONE IP only. However, if you put HOST=oracle.acme.com in listener.ora than it listens to all IPs (in netstat -an you see that it is listening to *.1521 instead of to oracle.acme.com.1521). That would mean that oracle does not resolve DNS at replaces it with IP but first checks whether this is the primary IP for that box. So, once we have put HOST=oracle.acme.com, all the connections were OK (coming from clients with tnsnames files that are pointing to old oracle1.acme.com and oracle2.acme.com) Of course DNS was changed.. Anybody has come across this undocumented feature ? Any explanation to that ? Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
9i install.
Title: 9i install. Hi, I am trying to run dbca (the new name for dbassist) on Sun Solaris 2.7 and I get the following java.lang.NoClassDefFoundError: org/omg/CORBA/UserException at oracle.sysman.emSDK.common.registry.PropertiesFileRegistry.get(Proper tiesFileRegistry.java:44) at oracle.sysman.vtx.vtxOemApp.OemJavaLauncher.launch(OemJavaLauncher.ja va:161) at oracle.sysman.vtx.vtxOemApp.OemApp.launch(OemApp.java:222) at oracle.sysman.vtx.vtxOemApp.OemApp.main(OemApp.java:80) Exception in thread main wwf2:test9i /export/home/oracle/app/oracle/product/9. 0.1/bin$ ./dbca java.lang.NoClassDefFoundError: com/inprise/vbroker/CORBA/Object at oracle.sysman.assistants.dbca.backend.Host.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.UIHost.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.InteractiveHost.init(Compiled Code ) at oracle.sysman.assistants.dbca.Dbca.getHost(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.execute(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.main(Compiled Code) Exception in thread main wwf2:test9i /export/home/oracle/app/oracle/product/9. trying to re-install Oracle did not help. Nor metalink. Thanks. Yuval.
Re: Database down
Well, I think the issue is two-fold. 1. They have been tinkering with user sessions, and the trace file output below is discussed on Steve Adams' website. PMON unable to acquire latch 80002060 modify parameter values possible holder pid = 58 ospid=29937 2001.12.09.15.27.43.000 I'm reviewing the settings in /etc/system. It is my belief that the additional application server pushed Oracle over some threshhold set in /etc/system. We are not running multi-threaded server, and we now have three application servers. 2. The issue with the patch has to do with my inability to restart the damn server due to an 'ORA-27123: unable to attach to shared memory segment'. I've asked our UNIX admin to verify we have the following patch: PATCH 105181-07 We are running 8.0.6 on Solaris 2.6. There are some issues with the sgadef.dbf file upon shutdown. This is only supposed to occur on a shutdown abort, but I've seen it occur on a shutdown immediate. I do not know if this patch specifically addresses the lingering sgadef.dbf file. Jeff [EMAIL PROTECTED] 12/11/01 12:10AM Jeff, I had a similar situation last week. I would like to know what patch are talking about and what's your version of Oracle? Regards, Ed I discovered the trace file after restarting Oracle - so that process ID is no longer out there. I've also discovered that there is a specific patch for this problem, which appears not to have been applied. I will move forward with this, but would like to provide management with an explanation as to why we had problems now, after so many days of uptime. Thanks for your help. Jeff [EMAIL PROTECTED] 12/10/01 03:35PM I don't suppose you've run ps -ef | grep 29937 yet, just out of curiousity, have you? -Original Message- Just to follow up. I have the database backup. I am afraid however, that the problem is still floating around. Briefly, here is what occured: I restarted Oracle because our application was unable to connect. I could connect through svrmgrl, but a 'select sysdate from dual' just hung. So I brought the database down, and recieved the memory segment error. So we were having problems while the database was up. Here is a trace file dump that may be linked to the problem: PMON unable to acquire latch 80002060 modify parameter values possible holder pid = 58 ospid=29937 *** 2001.12.09.15.27.43.000 We added a third application server last week. Could that somehow be the source of these problems? This application has been running problem free for over 100 days. Thanks for your input Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Wiegard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Wiegard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: select data using trigger
I always find it amusing that the SDD_ELEMENTS table which is part of an Oracle product meant to encourage good design (ie Designer) has more than this number of indexes :-) --- Jared Still [EMAIL PROTECTED] wrote: Hope I'm not being blunt here, ( I will admit to being frank, but not Frank ) What you're suggesting here is to put a patch on a bad design. 15 indexes? That's too many. Your table is undoubtedly highly denormalized and has too many columns. How many columns does this table have? You really need to take a step back and look at the design of the data you are querying. If this is really critical data ( Contracts? Sounds critical ) then strongly consider bringing in a consultant with strong data modeling and database design experience to help you put this data in a manageable form. Or maybe you need to build a data mart and use partitioning? This sounds to me like you need more consulting that you can get from a few emails on a list server. Jared On Monday 10 December 2001 19:50, Tatireddy, Shrinivas (MED, Keane) wrote: Hi lists, Can anybody help me...? Table CONTRACTS is accessed by many applications and has many selects hit this table. This table has 15 indexes. Still It needs some more indexes. But we were instructed to stop creating indexes. We have another table CONT2 in another schema. This table is copy of CONTRACTS. Is there a way to write a trigger on CONT2 for the following issue: if a particular select / select statement issued against CONTRACTS table, that query should select data from CONT2. (There is only one particular select statement that is more resource intensive. We need to divert that query to CONT2. As the applications are already tuned, we were not permitted to modify that code.) Thnx in advance, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Listeners listening to multiple IPs
Ditto. In addition, DNS will use multiple IP's in a round robin fashion as a crude form of load balancing. Jared Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100@y cc: ahoo.comSubject: RE: Listeners listening to multiple IPs Sent by: [EMAIL PROTECTED] om 12/11/01 10:41 AM Please respond to ORACLE-L well yeah... that's the whole point of USING DNS, so that it will work like that --- Djordje Jankovic [EMAIL PROTECTED] wrote: I am not talking whether putting DNS entry works, but am just pointing to the fact that oracle behaves differently if you put hardcoded IP - it than listens to one IP only, and if you put the dns name - listens to all IPs. Djordje -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:50 AM To: Multiple recipients of list ORACLE-L Subject: Re: Listeners listening to multiple IPs it's not so much a feature of the listener as it is of DNS as far as I know. One of the reasons it's best not to hard-code IP addresses into the listener.ora is because when you do a failover, or combine machines as you have, the listener.ora and tnsnames.ora files need to be updated. If you use DNS and let the DNS server resolve the address, you don't have to change anything. This has been around for as long as I can remember using the listener, which means going back to version 6 Rachel --- Djordje Jankovic [EMAIL PROTECTED] wrote: Something I learned today, but haven't seen it documented anywhere and would like to see whether anybody has come across it. We have a Sun server (call it oracle.acme.com). It came to life by combining two machines (oracle1.acme.com and oracle2.acme.com). So now it has two IP addresses: hme0: x.y.z.001 - from oracle1 and hme1: x.y.z.002 - from oracle2. There are few databases there (some of them were on oracle1 some on oracle2) and one listener listening to all of them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL Lock consistency - a great answer
I have to share this post I got on another list. Someone had asked a question about how Oracle locks work, here is the question... Q: i'd like to understand the lock modes (share exclusive) how to make them on table in DML transaction why locking is automatically use the lowest level of restrictiveness how the read consistency occures Here is the response someone sent An Oracle table is like a public restroom with an open door. When you go in to use the restroom, you shut the door as you enter, and you have placed the bathroom in shared lock mode. When another person want to use the restroom too, they can push open the door and enter. Both of you are now in the restroom and sharing it. However, if you go into the restroom, shut the door, and also lock it, then you have acquired the restroom in exclusive lock mode. Nobody else can come into the bathroom, even though there are multiple stalls in there: your exclusiveness prevents anybody else from going in there. In a third situation, you go into the restroom in shared mode, and then somebody else comes in after you, then it would be very presumptious of him to lock the door behind him because it just isn't done for someone to lock another stranger into the restroom; it would also make you very apprehensive to know that he's now locked you in there with him. So, if someone already is in the restroom and is sharing it, it prohibits someone else from coming in later and claiming exclusivity on it (at least not until the first person leaves.) So, when you acquire the restroom, you should use the lowest level of lockage, otherwise you're just being rude in not sharing use of the facilities, and you would probably cause a long queue of people outside the restroom causing contention problems waiting to gain access to the bathroom because it's been locked in exclusive mode. Now, suppose you set up web cams inside the restroom to take video images of the restroom and the stalls therein, and issue every one who enters virtual reality googles. The web cams guarantees users' privacy by use of read- consistency: the moment someone goes into a stall and closes the door, the web cam switches from live feed to replaying footage of an empty stall that was recorded from just prior to someone going into that stall. So, although there may be several people in the stall doing their business, your virtual reality googles always seem to show you clean, empty stalls. You pick an empty stall and try to enter it. If the stall is occupied, the stall door will be locked, so you can't enter it and do things to it, but through the magic of read-consistency, it looks as if it is unused; so like an obstinate idiot, you keep trying, and trying to open the door that won't open; at least not until the person in the stall comes out again, but you don't know that because your eyes tell you that the stall is free; you can see it, you just can't seem to be able to touch it. If the stall is truly unoccupied, you may enter and close the door behind you and now you have locked your stall record in exclusive mode (although the restroom as a whole can still be in shared mode; you just don't want anyone else with you in the stall itself.) After you do your business in the stall, and piss all over the toilet seat making a huge mess, you can either commit your work by just exiting the stall, exposing what you've done to the stall to all the other web cams now looking in on your messy, empty stall; or you can rollback your work by cleaning up the mess so that when you exit, nobody else is the wiser that you peed with the toilet seat down. The moment you commit or rollback by exiting the stall, your claim on the stall is released, and someone else may now acquire the stall. So, the lesson to be learnt here is: either learn to piss sitting down, or raise the seat when you pee. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ugly sql contest entry / Re: ORACLE-L Digest -- Volume 2001, Number 333
I've seen a 14 page (9 point Courier point) SQL which was generated by my ol' favourite Discoverer! It actually ran fine, it was more of Discover's penchant for writing code along the lines of: select column_list from ( select column_list from ( select column_list from ( select column_list from ( select column_list from ( select column_list from you get the idea... :-) --- [EMAIL PROTECTED] wrote: Is that the best you can do? ;) I recall something from the list many years ago where a poster was asking for help with a SQL statement that was unbelievably long. If I recollect correctly, it was about a 64K single SQL statement. It doesn't seem like maintaining something like that would actually be possible. Think of what the explain plan output would look like. Jared Eric D. Pierce To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] PierceED@csus cc: .eduSubject: ugly sql contest entry / Re: ORACLE-L Digest -- Volume 2001, Sent by: Number 333 [EMAIL PROTECTED] om 12/07/01 04:20 PM Please respond to ORACLE-L 1 UPDATE 2 TRIO_STUDENT_MASTERF00S01part5 T 3 SET 4 ( 5 T.T08_STUDENT_RACE_ETHNICITY 6 ) 7 = ( 8 SELECT 9 decode( 10 decode( X.STU_ETH_CODE, 11 'D', '0', /* Declined to State */ 12 '8', '0', /* Other (Not Listed) (10/8/1999 ??) */ 13 '9', '0', /* No Response */ 14 '1', '1', /* American Indian/ Native American */ 15 '2', '3', /* Black/ African American */ 16 '3', '4', /* Chicano/ Mexican American*/ 17 '4', '4', /* Other Hispanic (Not Listed) */ 18 'A', '4', /* Central American */ 19 'B', '4', /* South American */ 20 'P', '4', /* Puerto Rican */ 21 'Q', '4', /* Cuban */ 22 '5', '2', /* Other Asian */ 23 'C', '2', /* Chinese */ 24 'J', '2', /* Japanese */ 25 'K', '2', /* Korean */ 26 'L', '2', /* Laotian */ 27 'M', '2', /* Cambodian */ 28 'R', '2', /* Asian Indian */ 29 'S', '2', /* Other Southeast Asian*/ 30 'T', '2', /* Thai */ 31 'V', '2', /* Vietnamese */ 32 '6', '6', /* Other Pacific Islander (new 10/8/1999) */ 33 'F', '6', /* Filipino */ 34 'G', '6', /* Guamanian */ 35 'H', '6', /* Hawaiian */ 36 'N', '6', /* Samoan */ 37 '7', '5', /* White/ Caucasian */ 38 decode( Z.STU_ETH_CODE, 39
RE: 9i install.
Title: 9i install. I hate Java! -Original Message-From: Yuval Arnon [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 11, 2001 2:20 PMTo: Multiple recipients of list ORACLE-LSubject: 9i install. Hi, I am trying to run dbca (the new name for dbassist) on Sun Solaris 2.7 and I get the following java.lang.NoClassDefFoundError: org/omg/CORBA/UserException at oracle.sysman.emSDK.common.registry.PropertiesFileRegistry.get(Proper tiesFileRegistry.java:44) at oracle.sysman.vtx.vtxOemApp.OemJavaLauncher.launch(OemJavaLauncher.ja va:161) at oracle.sysman.vtx.vtxOemApp.OemApp.launch(OemApp.java:222) at oracle.sysman.vtx.vtxOemApp.OemApp.main(OemApp.java:80) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. 0.1/bin$ ./dbca java.lang.NoClassDefFoundError: com/inprise/vbroker/CORBA/Object at oracle.sysman.assistants.dbca.backend.Host.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.UIHost.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.InteractiveHost.init(Compiled Code ) at oracle.sysman.assistants.dbca.Dbca.getHost(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.execute(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.main(Compiled Code) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. trying to re-install Oracle did not help. Nor metalink. Thanks. Yuval.
RE: 9i install.
Title: 9i install. can you check javaversion...as i recall it should be 1.1.7 or better for 9i. -Original Message-From: Yuval Arnon [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 11, 2001 2:20 PMTo: Multiple recipients of list ORACLE-LSubject: 9i install. Hi, I am trying to run dbca (the new name for dbassist) on Sun Solaris 2.7 and I get the following java.lang.NoClassDefFoundError: org/omg/CORBA/UserException at oracle.sysman.emSDK.common.registry.PropertiesFileRegistry.get(Proper tiesFileRegistry.java:44) at oracle.sysman.vtx.vtxOemApp.OemJavaLauncher.launch(OemJavaLauncher.ja va:161) at oracle.sysman.vtx.vtxOemApp.OemApp.launch(OemApp.java:222) at oracle.sysman.vtx.vtxOemApp.OemApp.main(OemApp.java:80) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. 0.1/bin$ ./dbca java.lang.NoClassDefFoundError: com/inprise/vbroker/CORBA/Object at oracle.sysman.assistants.dbca.backend.Host.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.UIHost.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.InteractiveHost.init(Compiled Code ) at oracle.sysman.assistants.dbca.Dbca.getHost(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.execute(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.main(Compiled Code) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. trying to re-install Oracle did not help. Nor metalink. Thanks. Yuval.
RE: CREATE DATABASE scripts
I did something similar on 9i recently and I believe there was a check button in DBCA for Create Scripts, in addition to Create Template. Did you follow DBCA through to the last screen? I think it is there. I tried to use the 9i DBCA to create a custom database with rollback segments instead of the UNDO tablespace, 3 uniform extent sizes, custom tablespaces, locally managed tablespaces, etc. and it crashed burned every time. A couple weeks of Oracle support and they got nowhere! Moral of the story: you are correct to generate scripts! HTH, david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide Inc. 4815 Emperor Blvd., Suite 110 Durham, NC 27703 Tel. (919) 941-4645 Fax (919) 474-0735 Email mailto:[EMAIL PROTECTED] Web http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, 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 the sender at (919) 941-4645 and delete this e-mail message from your computer. Thank you. -Original Message- Sent: Tuesday, December 11, 2001 12:05 PM To: Multiple recipients of list ORACLE-L Hi, I am trying to generate some generic CREATE DATABASE scripts from the Oracle Database Configuration assistant and I cannot see the Generate Database Creation Scripts option that is mentioned in the HELP. It is not listed on the screen with the Create Database and Save as a Database Template options. Can someone let me know how it is done with the 9i configuration assistant or pass on some Oracle 9i CREATE DATABASE scripts. Thanks, Lindsay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stoddard, Lindsay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Wagoner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i install.
Title: 9i install. Hi, java -versionjava version "1.1.7" Yuval. -Original Message-From: Mohan, Ross [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 11, 2001 3:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 9i install. can you check javaversion...as i recall it should be 1.1.7 or better for 9i. -Original Message-From: Yuval Arnon [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 11, 2001 2:20 PMTo: Multiple recipients of list ORACLE-LSubject: 9i install. Hi, I am trying to run dbca (the new name for dbassist) on Sun Solaris 2.7 and I get the following java.lang.NoClassDefFoundError: org/omg/CORBA/UserException at oracle.sysman.emSDK.common.registry.PropertiesFileRegistry.get(Proper tiesFileRegistry.java:44) at oracle.sysman.vtx.vtxOemApp.OemJavaLauncher.launch(OemJavaLauncher.ja va:161) at oracle.sysman.vtx.vtxOemApp.OemApp.launch(OemApp.java:222) at oracle.sysman.vtx.vtxOemApp.OemApp.main(OemApp.java:80) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. 0.1/bin$ ./dbca java.lang.NoClassDefFoundError: com/inprise/vbroker/CORBA/Object at oracle.sysman.assistants.dbca.backend.Host.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.UIHost.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.InteractiveHost.init(Compiled Code ) at oracle.sysman.assistants.dbca.Dbca.getHost(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.execute(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.main(Compiled Code) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. trying to re-install Oracle did not help. Nor metalink. Thanks. Yuval.
RE: 9i install.
Title: 9i install. ah well, a swing and a miss. -Original Message-From: Yuval Arnon [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 11, 2001 4:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 9i install. Hi, java -versionjava version "1.1.7" Yuval. -Original Message-From: Mohan, Ross [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 11, 2001 3:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 9i install. can you check javaversion...as i recall it should be 1.1.7 or better for 9i. -Original Message-From: Yuval Arnon [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 11, 2001 2:20 PMTo: Multiple recipients of list ORACLE-LSubject: 9i install. Hi, I am trying to run dbca (the new name for dbassist) on Sun Solaris 2.7 and I get the following java.lang.NoClassDefFoundError: org/omg/CORBA/UserException at oracle.sysman.emSDK.common.registry.PropertiesFileRegistry.get(Proper tiesFileRegistry.java:44) at oracle.sysman.vtx.vtxOemApp.OemJavaLauncher.launch(OemJavaLauncher.ja va:161) at oracle.sysman.vtx.vtxOemApp.OemApp.launch(OemApp.java:222) at oracle.sysman.vtx.vtxOemApp.OemApp.main(OemApp.java:80) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. 0.1/bin$ ./dbca java.lang.NoClassDefFoundError: com/inprise/vbroker/CORBA/Object at oracle.sysman.assistants.dbca.backend.Host.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.UIHost.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.InteractiveHost.init(Compiled Code ) at oracle.sysman.assistants.dbca.Dbca.getHost(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.execute(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.main(Compiled Code) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. trying to re-install Oracle did not help. Nor metalink. Thanks. Yuval.
Insatiable smon process
OS Environment HP-UX 11.0 Oracle Version 8.0.5.2.1 A situation arose whereby the DBA issued a shutdown immediate, which did not complete in what was considered a reasonable time period, so a shutdown abort was issued. I believe there was active transaction(s) running at the time. The DBA restarted Oracle instance and accordingly crash recovery was completed, however SMON process obviously feels the need to 'clean up' something as it is clocking up cpu hand-over-fist, and shows no inclination to ever stop. Ithas all the appearance of looping.. And NowA shutdown immediate will never complete...Shutdown abort followed byrestart sees SMON going at it all over again - Why not...it believes it's got some clearing-up to do; but it's NOT able to...no matter how much time you give it. A sample trace in UDUMP dest shows ..."Waiting for SMON to disable TX recovery" ( this is from my memory) If anyone has any ideas on what might be causing SMON to fret so, and how we might "arrest SMON". It is non Production, but nevertheless a semi-important System test environment. This is a colleague of mine's database, and I have attempted to assist, butso far I'm no nearer to resolving it. It's been put to sleep tonight, but when it wakes up tomorrow, SMON will still havethis Hangover. regards../Bob P.S Jo King is just an alias !
RE: 9i install.
Title: 9i install. Check the required O/S patches (in Oracle installation doc), required Java release, CLASSPATH env variable. -Original Message-From: Yuval Arnon [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 11, 2001 2:20 PMTo: Multiple recipients of list ORACLE-LSubject: 9i install. Hi, I am trying to run dbca (the new name for dbassist) on Sun Solaris 2.7 and I get the following java.lang.NoClassDefFoundError: org/omg/CORBA/UserException at oracle.sysman.emSDK.common.registry.PropertiesFileRegistry.get(Proper tiesFileRegistry.java:44) at oracle.sysman.vtx.vtxOemApp.OemJavaLauncher.launch(OemJavaLauncher.ja va:161) at oracle.sysman.vtx.vtxOemApp.OemApp.launch(OemApp.java:222) at oracle.sysman.vtx.vtxOemApp.OemApp.main(OemApp.java:80) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. 0.1/bin$ ./dbca java.lang.NoClassDefFoundError: com/inprise/vbroker/CORBA/Object at oracle.sysman.assistants.dbca.backend.Host.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.UIHost.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.InteractiveHost.init(Compiled Code ) at oracle.sysman.assistants.dbca.Dbca.getHost(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.execute(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.main(Compiled Code) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. trying to re-install Oracle did not help. Nor metalink. Thanks. Yuval.
RE: 9i install.
Title: 9i install. Is your classpath set? -Original Message-From: Khedr, Waleed [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 11, 2001 3:45 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 9i install. I hate Java! -Original Message-From: Yuval Arnon [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 11, 2001 2:20 PMTo: Multiple recipients of list ORACLE-LSubject: 9i install. Hi, I am trying to run dbca (the new name for dbassist) on Sun Solaris 2.7 and I get the following java.lang.NoClassDefFoundError: org/omg/CORBA/UserException at oracle.sysman.emSDK.common.registry.PropertiesFileRegistry.get(Proper tiesFileRegistry.java:44) at oracle.sysman.vtx.vtxOemApp.OemJavaLauncher.launch(OemJavaLauncher.ja va:161) at oracle.sysman.vtx.vtxOemApp.OemApp.launch(OemApp.java:222) at oracle.sysman.vtx.vtxOemApp.OemApp.main(OemApp.java:80) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. 0.1/bin$ ./dbca java.lang.NoClassDefFoundError: com/inprise/vbroker/CORBA/Object at oracle.sysman.assistants.dbca.backend.Host.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.UIHost.init(Compiled Code) at oracle.sysman.assistants.dbca.ui.InteractiveHost.init(Compiled Code ) at oracle.sysman.assistants.dbca.Dbca.getHost(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.execute(Compiled Code) at oracle.sysman.assistants.dbca.Dbca.main(Compiled Code) Exception in thread "main" wwf2:test9i /export/home/oracle/app/oracle/product/9. trying to re-install Oracle did not help. Nor metalink. Thanks. Yuval.
ODBC Microsoft Transaction Server
Hello, The application frequently (several times per day) hangs. Restarting the MTS (Microsoft Transaction Server) (Package shutdown) resolves the problem and the users are able to work again. The database appears to be unaffected and appears ready to process. The Oracle ODBC version is 8.01.74.00 7/24/01. The Client on the MTS server is 8.1.7 The Server is 8.1.5 running on NT 4.0 I traced ODBC for awhile until after it hung the next time. Some of the things I saw that looked like errors are: SQLPOINTER 0x0003 (BADMEM) DIAG [IM006][Microsoft][ODBC Driver Manager] Driver's SQL SetConnectAttr Failed(0) SQLPOINTER [Unknown attribute 201] Any help pointing me where to look for the root cause of this problem would be appreciated. Thanks If you have any questions, please feel free to drop me a note. Stephen Andert Scottsdale, Arizona -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Lock consistency - a great answer
Absolutely tremendous. No mention of core dumps, I noticed -Original Message- Sent: 11 December 2001 20:10 To: Multiple recipients of list ORACLE-L I have to share this post I got on another list. Someone had asked a question about how Oracle locks work, here is the question... Q: i'd like to understand the lock modes (share exclusive) how to make them on table in DML transaction why locking is automatically use the lowest level of restrictiveness how the read consistency occures Here is the response someone sent An Oracle table is like a public restroom with an open door. When you go in to use the restroom, you shut the door as you enter, and you have placed the bathroom in shared lock mode. When another person want to use the restroom too, they can push open the door and enter. Both of you are now in the restroom and sharing it. However, if you go into the restroom, shut the door, and also lock it, then you have acquired the restroom in exclusive lock mode. Nobody else can come into the bathroom, even though there are multiple stalls in there: your exclusiveness prevents anybody else from going in there. In a third situation, you go into the restroom in shared mode, and then somebody else comes in after you, then it would be very presumptious of him to lock the door behind him because it just isn't done for someone to lock another stranger into the restroom; it would also make you very apprehensive to know that he's now locked you in there with him. So, if someone already is in the restroom and is sharing it, it prohibits someone else from coming in later and claiming exclusivity on it (at least not until the first person leaves.) So, when you acquire the restroom, you should use the lowest level of lockage, otherwise you're just being rude in not sharing use of the facilities, and you would probably cause a long queue of people outside the restroom causing contention problems waiting to gain access to the bathroom because it's been locked in exclusive mode. Now, suppose you set up web cams inside the restroom to take video images of the restroom and the stalls therein, and issue every one who enters virtual reality googles. The web cams guarantees users' privacy by use of read- consistency: the moment someone goes into a stall and closes the door, the web cam switches from live feed to replaying footage of an empty stall that was recorded from just prior to someone going into that stall. So, although there may be several people in the stall doing their business, your virtual reality googles always seem to show you clean, empty stalls. You pick an empty stall and try to enter it. If the stall is occupied, the stall door will be locked, so you can't enter it and do things to it, but through the magic of read-consistency, it looks as if it is unused; so like an obstinate idiot, you keep trying, and trying to open the door that won't open; at least not until the person in the stall comes out again, but you don't know that because your eyes tell you that the stall is free; you can see it, you just can't seem to be able to touch it. If the stall is truly unoccupied, you may enter and close the door behind you and now you have locked your stall record in exclusive mode (although the restroom as a whole can still be in shared mode; you just don't want anyone else with you in the stall itself.) After you do your business in the stall, and piss all over the toilet seat making a huge mess, you can either commit your work by just exiting the stall, exposing what you've done to the stall to all the other web cams now looking in on your messy, empty stall; or you can rollback your work by cleaning up the mess so that when you exit, nobody else is the wiser that you peed with the toilet seat down. The moment you commit or rollback by exiting the stall, your claim on the stall is released, and someone else may now acquire the stall. So, the lesson to be learnt here is: either learn to piss sitting down, or raise the seat when you pee. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that
Re: SQL Lock consistency - a great answer
this was treat man .. cool and imaginative for sure!! --- Farnsworth, Dave [EMAIL PROTECTED] wrote: I have to share this post I got on another list. Someone had asked a question about how Oracle locks work, here is the question... Q: i'd like to understand the lock modes (share exclusive) how to make them on table in DML transaction why locking is automatically use the lowest level of restrictiveness how the read consistency occures Here is the response someone sent An Oracle table is like a public restroom with an open door. When you go in to use the restroom, you shut the door as you enter, and you have placed the bathroom in shared lock mode. When another person want to use the restroom too, they can push open the door and enter. Both of you are now in the restroom and sharing it. However, if you go into the restroom, shut the door, and also lock it, then you have acquired the restroom in exclusive lock mode. Nobody else can come into the bathroom, even though there are multiple stalls in there: your exclusiveness prevents anybody else from going in there. In a third situation, you go into the restroom in shared mode, and then somebody else comes in after you, then it would be very presumptious of him to lock the door behind him because it just isn't done for someone to lock another stranger into the restroom; it would also make you very apprehensive to know that he's now locked you in there with him. So, if someone already is in the restroom and is sharing it, it prohibits someone else from coming in later and claiming exclusivity on it (at least not until the first person leaves.) So, when you acquire the restroom, you should use the lowest level of lockage, otherwise you're just being rude in not sharing use of the facilities, and you would probably cause a long queue of people outside the restroom causing contention problems waiting to gain access to the bathroom because it's been locked in exclusive mode. Now, suppose you set up web cams inside the restroom to take video images of the restroom and the stalls therein, and issue every one who enters virtual reality googles. The web cams guarantees users' privacy by use of read- consistency: the moment someone goes into a stall and closes the door, the web cam switches from live feed to replaying footage of an empty stall that was recorded from just prior to someone going into that stall. So, although there may be several people in the stall doing their business, your virtual reality googles always seem to show you clean, empty stalls. You pick an empty stall and try to enter it. If the stall is occupied, the stall door will be locked, so you can't enter it and do things to it, but through the magic of read-consistency, it looks as if it is unused; so like an obstinate idiot, you keep trying, and trying to open the door that won't open; at least not until the person in the stall comes out again, but you don't know that because your eyes tell you that the stall is free; you can see it, you just can't seem to be able to touch it. If the stall is truly unoccupied, you may enter and close the door behind you and now you have locked your stall record in exclusive mode (although the restroom as a whole can still be in shared mode; you just don't want anyone else with you in the stall itself.) After you do your business in the stall, and piss all over the toilet seat making a huge mess, you can either commit your work by just exiting the stall, exposing what you've done to the stall to all the other web cams now looking in on your messy, empty stall; or you can rollback your work by cleaning up the mess so that when you exit, nobody else is the wiser that you peed with the toilet seat down. The moment you commit or rollback by exiting the stall, your claim on the stall is released, and someone else may now acquire the stall. So, the lesson to be learnt here is: either learn to piss sitting down, or raise the seat when you pee. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at
Re: Serial# changes when rolling back
yeah i did read this thread but still wonder why oracle needs to use serial# column at all. Does the SID not gaurentee uniqueness for a session ... damn the name says so atleast (session identifier).. or maybe they just coded it like that and make some use of the serial # Deepak --- [EMAIL PROTECTED] wrote: Check this Oracle Note : Doc ID: Note:1020545.102 Subject: ORA-00026: CANNOT KILL SESSION; SERIAL# KEEPS CHANGING Type: PROBLEM Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 23-NOV-1999 Last Revision Date: 24-AUG-2000 Problem Description --- You have killed a process at the operating system level that was running a long-running transaction. Now, you are trying to issue the command: alter system kill session 'sid, serial#'; To kill the associated Oracle session, but you can't kill it. You
Update table problem
Hi, We inserted data into 1 of the column as test 1 abc while the actual value is test1abc without spaces. There are about 1 million rows in a table. Is there a way to update the table column and remove the extra space . Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: diagnosing latch free
Hey Anita! Thanks for the useful info. Actually, after the stats were hanging I started querying various tables and those queries also started hanging; queries on v$latch and a couple of others. I finally shutdown abort and got an ORA-600 error. I looked it up on the extremely handy ORA-600 tool and found that the first argument - 1113 indicated the following... VERSIONS: versions 7.3.X to 8.1.7 DESCRIPTION: We are freeing a state object but it already appears to be on the free list. This is generally an in memory (SGA) corruption or due to a bug in mishandling the state objects. FUNCTIONALITY: STATE OBJECT MANAGER IMPACT: PROCESS FAILURE POSSIBLE INSTANCE FAILURE IF DETECTED BY PMON PROCESS NON CORRUPTIVE - No underlying data corruption. Bug 1307247: ORA-600 [1113] WHEN AN ANALYZE OPERATION FAILS OR IS CANCELLED fixed in 8.0.6.3, 8.1.7.1 and 9.0.0 releases. So I think it had something to do with bailing out of an analyze. I'm not sure how things got gummed up in the first place but the db is fine now. - Doug On Sun, 09 Dec 2001 22:20:18 -0800, you wrote: Hi Doug! It sounds like SMON is busy doing something else, most likely coalescing free space or deallocating temp segments. See metalink Note: 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing While there are events that can be set to prevent smon from coalescing or cleaning up temp segments, they are only a temporary measure to allow one to defer the cleanup to a more convenient time. The best bet is to let smon finish its job and then set proper extent sizes for temp tablespaces or use locally managed temp tablespaces. Did the db crash or was a shutdown abort done? SMON could be doing instance recovery. I've seen cases where SMON was stalled when doing recovery when FAST_START_PARALLEL_ROLLBACK was set. Shutting down and setting FAST_START_PARALLEL_ROLLBACK = FALSE allowed SMON to finish recovery. As a workaround in any of the above situations, you can create a permanent tablespace and redirect users' temporary tablespace to that permanent tablespace. HTH, -- Anita --- Doug C [EMAIL PROTECTED] wrote: Ok.. it's a sort segment latch.. any way to find out why? It's been sitting around for over an hour ... On Sat, 08 Dec 2001 14:35:18 -0800, you wrote: oops, probably only want the events that are latch frees: select ln.name from v$session_wait sw, v$latchname ln where sw.p2 = ln.latch# and sw.event = 'latch free'; On Saturday, December 8, 2001, at 04:50 PM, George Schlossnagle wrote: Try: select ln.name from v$session_wait sw, v$latchname ln where sw.p2 = ln.latch#. Best, George www.pythian.com -- [EMAIL PROTECTED] -- 877-PYTHIAN Smarter than adding another team member, Pythian has new services for supplementing DBAs: get our help with monitoring, 24x7 on-call, daily verifications, storage management, performance and more. On Saturday, December 8, 2001, at 04:05 PM, Doug C wrote: I have a session that seems to be hung on a sql_statment. Here is it's session_wait entry: SID SEQ# -- -- EVENT P1TEXT P1 -- P1RAWP2TEXT P2 P2RAW -- P3TEXT P3 -- P3RAW WAIT_TIME SECONDS_IN_WAIT STATE -- --- --- 62 1239 latch free address 805352248 3000B338 number 88 0058 tries 923 039B 0 0 WAITING The seq# goes up from time to time. My question is how to determine what kind of latch is bothering it? Does P2 (88) indicate what type of latch? Can I join with some other table to find out what 88 is? Thanks, D __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Doug C INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT
Re: Serial# changes when rolling back
One explanation I had heard for the combination of Sid+Serial# goes something like this ... say you identify a particular session to monitor ... you then decide to kill it. However, it could so happen that that user logged out between the time you decided to kill the session and the time you actually killed the session (even if you have scripted the kill command). When a user logs out, the Sid is available to the next user who logs in. Since another user might be holding the same Sid at the time you come around to kill the Sid, you might actually kill the wrong user-session. To avoid this, the next user, getting the same Sid gets a different Serial# and your kill must be on Sid+Serial# together. That still does not explain why pmon increments the Serial#, though. Hemant Deepak Thapliyal [EMAIL PROTECTED] 12/12/2001 06:26 AM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group) Subject: Re: Serial# changes when rolling back yeah i did read this thread but still wonder why oracle needs to use serial# column at all. Does the SID not gaurentee uniqueness for a session ... damn the name says so atleast (session identifier).. or maybe they just coded it like that and make some use of the serial # Deepak --- [EMAIL PROTECTED] wrote: Check this Oracle Note : Doc ID: Note:1020545.102 Subject: ORA-00026: CANNOT KILL SESSION; SERIAL# KEEPS CHANGING Type: PROBLEM Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 23-NOV-1999 Last Revision Date: 24-AUG-2000 Problem Description --- You have killed a process at the operating system level that was running a long-running transaction. Now, you are trying to issue the command: alter system kill session 'sid, serial#'; To kill the associated Oracle session, but you can't kill it. You may receive the following error: ORA-00026: missing or invalid session id Cause: The session ID string specified in the ALTER SYSTEM KILL SESSION command was invalid, or no string was specified. Action: Retry the command with a valid session ID. In v$session, you notice that the serial# for the session keeps changing. Also, pmon may be creating a trace file that keeps growing. Solution Description It is best to let pmon roll back the changes. If you shutdown at this point, this work will still have to be done at the next startup. pmon is rolling back changes and will let the session die when it is finished. To verify that work is being done, select used_urec from v$transaction. If the value for this column keeps going down, then work is being done. When used_urec reaches zero, then the rollback will be done, and the session will die. With Oracle8, you can list dead transactions by issuing the following query: select * from x$ktuxe where ktuxecfl='DEAD'; Explanation --- pmon has control of the session and is rolling back all of the work that has been done so far. Search Words
Feedback please for PROGNOSIS's db monitoring tool !
Hello everybody, My team is currently evaluating Oracle monitoring tool from a company called PROGNOSIS. Is there anybody currently using this tool or evaluated the tool before? May I get some feedback about the Oracle monitoring tools of PROGNOSIS from any one of you? Your insight would be highly appreciated. Regards, Shantanu Talukder Mpower Communications. __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shantanu Talukder INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Serial# changes when rolling back
intresting ... --- [EMAIL PROTECTED] wrote: One explanation I had heard for the combination of Sid+Serial# goes something like this ... say you identify a particular session to monitor ... you then decide to kill it. However, it could so happen that that user logged out between the time you decided to kill the session and the time you actually killed the session (even if you have scripted the kill command). When a user logs out, the Sid is available to the next user who logs in. Since another user might be holding the same Sid at the time you come around to kill the Sid, you might actually kill the wrong user-session. To avoid this, the next user, getting the same Sid gets a different Serial# and your kill must be on Sid+Serial# together. That still does not explain why pmon increments the Serial#, though. Hemant Deepak Thapliyal [EMAIL PROTECTED] 12/12/2001 06:26 AM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group) Subject: Re: Serial# changes when rolling back yeah i did read this thread but still wonder why oracle needs to use serial# column at all. Does the SID not gaurentee uniqueness for a session ... damn the name says so atleast (session identifier).. or maybe they just coded it like that and make some use of the serial # Deepak --- [EMAIL PROTECTED] wrote: Check this Oracle Note : Doc ID: Note:1020545.102 Subject: ORA-00026: CANNOT KILL SESSION; SERIAL# KEEPS CHANGING Type: PROBLEM Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 23-NOV-1999 Last Revision Date: 24-AUG-2000 Problem Description --- You have killed a process at the operating system level that was running a long-running transaction. Now, you are trying to issue the command: alter system kill session 'sid, serial#'; To kill the associated Oracle session, but you can't kill it. You may receive the following error: ORA-00026: missing or invalid session id Cause: The session ID string specified in the ALTER SYSTEM KILL SESSION command was invalid, or no string was specified. Action: Retry the command with a valid session ID. In v$session, you notice that the serial# for the session keeps changing. Also, pmon may be creating a trace file that keeps growing. Solution Description It is best to let pmon roll back the changes. If you shutdown at this point, this work will still have to be done at the next startup. pmon is rolling back changes and will let the session die when it is finished. To verify that work is being done, select used_urec from v$transaction. If the value for this column keeps going down, then work is being done. When used_urec reaches zero, then the rollback will be done, and the session will die. With Oracle8, you can list dead transactions by issuing the following query: select * from x$ktuxe
like 'wall' unix comman in forms
Hi all, How to broadcast a message to all user who running Forms 6i ? we use Sun as the database server. I use 'wall' command from root to user when we still use Forms 3.0 ( they login via telnet). regards -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: hernawan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Set Time Zone
Hi Gurus, How can I set up a time zone for each region that connect on SAME Database 8i Case: Database in NewYork. Clients in San Franciso, Paris .. When they use select sysdate from dual ; ( on Client or server side ) They should see their local time .?? Thanks in advance Tran -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Set Time Zone
Can't , not in 8i. 9i has introduced a TIMEZONE datatype where time can be reported offset by the UTC+/- offset. You'd have to build the logic in your application to increment/decrement the sysdate, depending on the user/client location (use a lookup table for location and timezone-offset) when fetching the sysdate. SYSDATE would always return the date on the server where the database instance is running. Hemant Mike T [EMAIL PROTECTED]12/12/2001 10:50 AM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group) Subject: Set Time Zone Hi Gurus, How can I set up a time zone for each region that connect on SAME Database 8i Case: Database in NewYork. Clients in San Franciso, Paris .. When they use select sysdate from dual ; ( on Client or server side ) They should see their local time .?? Thanks in advance Tran -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). [This e-mail is confidential and may also be privileged. If you are not the intended recipient, please delete it and notify us immediately; you should not copy or use it for any purpose, nor disclose its contents to any other person. Thank you.] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Lock consistency - a great answer
I have to share this post I got on another list. Excuse me but what List is this ? Thanks Robert Chin - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 3:10 PM I have to share this post I got on another list. Someone had asked a question about how Oracle locks work, here is the question... Q: i'd like to understand the lock modes (share exclusive) how to make them on table in DML transaction why locking is automatically use the lowest level of restrictiveness how the read consistency occures Here is the response someone sent An Oracle table is like a public restroom with an open door. When you go in to use the restroom, you shut the door as you enter, and you have placed the bathroom in shared lock mode. When another person want to use the restroom too, they can push open the door and enter. Both of you are now in the restroom and sharing it. However, if you go into the restroom, shut the door, and also lock it, then you have acquired the restroom in exclusive lock mode. Nobody else can come into the bathroom, even though there are multiple stalls in there: your exclusiveness prevents anybody else from going in there. In a third situation, you go into the restroom in shared mode, and then somebody else comes in after you, then it would be very presumptious of him to lock the door behind him because it just isn't done for someone to lock another stranger into the restroom; it would also make you very apprehensive to know that he's now locked you in there with him. So, if someone already is in the restroom and is sharing it, it prohibits someone else from coming in later and claiming exclusivity on it (at least not until the first person leaves.) So, when you acquire the restroom, you should use the lowest level of lockage, otherwise you're just being rude in not sharing use of the facilities, and you would probably cause a long queue of people outside the restroom causing contention problems waiting to gain access to the bathroom because it's been locked in exclusive mode. Now, suppose you set up web cams inside the restroom to take video images of the restroom and the stalls therein, and issue every one who enters virtual reality googles. The web cams guarantees users' privacy by use of read- consistency: the moment someone goes into a stall and closes the door, the web cam switches from live feed to replaying footage of an empty stall that was recorded from just prior to someone going into that stall. So, although there may be several people in the stall doing their business, your virtual reality googles always seem to show you clean, empty stalls. You pick an empty stall and try to enter it. If the stall is occupied, the stall door will be locked, so you can't enter it and do things to it, but through the magic of read-consistency, it looks as if it is unused; so like an obstinate idiot, you keep trying, and trying to open the door that won't open; at least not until the person in the stall comes out again, but you don't know that because your eyes tell you that the stall is free; you can see it, you just can't seem to be able to touch it. If the stall is truly unoccupied, you may enter and close the door behind you and now you have locked your stall record in exclusive mode (although the restroom as a whole can still be in shared mode; you just don't want anyone else with you in the stall itself.) After you do your business in the stall, and piss all over the toilet seat making a huge mess, you can either commit your work by just exiting the stall, exposing what you've done to the stall to all the other web cams now looking in on your messy, empty stall; or you can rollback your work by cleaning up the mess so that when you exit, nobody else is the wiser that you peed with the toilet seat down. The moment you commit or rollback by exiting the stall, your claim on the stall is released, and someone else may now acquire the stall. So, the lesson to be learnt here is: either learn to piss sitting down, or raise the seat when you pee. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Chin
