RE: oracle client on PC's
Jeffrey, I've probably missed something, but if the application uses Merant's ODBC driver - which I believe is a wire-protocol driver, how come you need to install the client at all? Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Wednesday, 7 January 2004 7:14 AM The application is a third-party application using Merant's ODBC driver. How do people normally install the client. Do you do an install to every workstation??? [EMAIL PROTECTED] 1/6/04 2:54:34 PM At 11:39 AM 1/6/2004, Jeffrey Beckstrom wrote: Rather than installing the Oracle client on every client PC, we have been: - installing client on 1 PC - copying directory to a network server - extract the registry for oracle key - fix registry that was extracted to reference the network drive - load registry on client PCs - add the network pc as a search drive to the client pc. We are now experiencing problems over the WAN and looking at ways to eliminate the Oracle dll overhead. Short of installing Oracle on every client PC, what are our options? There is a reason that Oracle doesn't support configurations like this. There is a fair amount of chatter between an application and the Oracle client DLL's. When this chatter starts flying over the network rather than merely going to a local DLL, you start to get performance problems. How are your application(s) designed? It's probably possible to tweak an OCI application to make fewer OCI calls. If you're using ODBC, and have the budget, you could purchase one of the wire-protocol ODBC drivers. Justin Cave Distributed Database Consulting -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Dblink versus odbc
They may be a bit more comparable than that. a dblink can utilise ODBC (via HS ODBC) to connect to non-Oracle databases (or to Oracle databases if you really wanted to). ODBC can be used by client but also by server via HSODBC. ODBC can also be used by a non Oracle server (eg SQL Server) to connect back to an Oracle database. So ask your manager for more information on why they want to compare the two. HTH, Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Wednesday, 3 December 2003 3:04 AM To: Multiple recipients of list ORACLE-L Sure it is but ... As the manager asked and even if I did say that there would be no Oracle Paper on this ... Thanks a lot. Regards PG -Mensagem original- De: Igor Neyman [mailto:[EMAIL PROTECTED] Enviada: terça-feira, 2 de Dezembro de 2003 15:55 Para: Multiple recipients of list ORACLE-L Assunto: RE: Dblink versus odbc Can't compare apples and oranges. ODBC is for client-database connections, dblink is for database-database connections. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Paulo Gomes Sent: Tuesday, December 02, 2003 10:00 AM To: Multiple recipients of list ORACLE-L Hi guys Have a request here. My management wants to know the advantage of using dblink instead of odbc. Does anyone have any ideas on this? Regards PG -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Move datafiles, but can't delete them
When dropping a tablespace, we've had better success deleting the files by the delete command from CMD rather than using Windows Explorer. Also, found that you need to wait a period of time before you can delete the file - eg try again in 30 secs 5 mins time. This is mentioned in a forum at http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FORp_id=279331.996. From the note below, you should exit your tool (eg SQLPLus) and then wait a bit. Also see note 222033.1 http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOTp_id=222033.1 which says: fact: Oracle Server - Enterprise Edition 8.1.5 fact: MS Windows NT symptom: Unable to delete datafile from OS after drop tablespace symptom: Cannot delete %s: There has been a sharing violation symptom: Error deleting OS file cause: Bug:480928 OPEN HANDLE TO OFFLINE DATAFILES Fixed in ver. 8.1.6 and higher. fix: After issuing a DROP TABLESPACE command the corresponding datafile is still locked on OS level and not possible to remove. Workaround: Disconnect from the session and exit the tool (Server Manager or SQL*Plus) you were running the DROP TABLESPACE command from. Then wait for some time (minutes) , and the datafile will be released by the OS and possible to delete. To find out who has files open, you can use process explorer from www.sysinternals.com or tlist from the resource kit. HTH, Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Wednesday, 26 November 2003 1:49 AM To: Multiple recipients of list ORACLE-L In a way, it's better than Unix. You can't delete Windows Oracle files while the database is open, but in Unix you can. In a way, it's a real pain in the butt. Try looking at log files that are held open by other apps while they write to them. No problem on unix, often impossible on windows. No, I'm not talking about Oracle. NetBackup for instance, on windows it is often impossible to read the logfiles for a backup in progress. If you do happen to erroneously delete an open file on unix, you can recover from it if you keep your wits about you and don't panic. Jared On Tue, 2003-11-25 at 05:24, Mercadante, Thomas F wrote: Luc, The next time you bounce the database you will be able to delete the files. Windows keeps a lock on these files for some odd reason. In a way, it's better than Unix. You can't delete Windows Oracle files while the database is open, but in Unix you can. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 25, 2003 8:09 AM To: Multiple recipients of list ORACLE-L Hi gurus, Oracle 8.1.7.4 on Windows 2000 Yesterday I wanted to move 2 datafiles (for the same tablespace) to another disk. 1- I placed my tablespace offline 2- I copied my 2 datafiles 3- I altered my controlfiles to reflect the new path 4- I brought my tablespace back online 5- I backuped up my controlfile to trace to make sure it using the new path 6- When I wanted to delete the 2 old datafiles, Windows gave me an sharing violation error. My question is Who using it? My controlfiles are changed, when I query DBA_DATA_FILES, i'm using the new path. I don't want to bounce my production database Any ideas TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: what is oracle rdb?
Yes Oracle still sells it. Yes they still develop it - new versions released regularly with new features. It's integrated with VMS, had clustering for many years, support for Galaxy, existing applications etc See http://www.oracle.com/rdb/ http://www.jcc.com/ and more Bruce NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Tuesday, 25 November 2003 8:30 AM To: Multiple recipients of list ORACLE-L does oracle still sell it? why would you buy it over the rdbms? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, November 24, 2003 3:59 PM RDB was bought from Digital Corporation many years ago. Supposedly a lot of the CBO was lifted from it. -Original Message- [EMAIL PROTECTED] Sent: Monday, November 24, 2003 1:49 PM To: Multiple recipients of list ORACLE-L I see it referred to on metalink alot. I know its seperate from the rdbms. Author: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Email Notification
Have a look at Metalink note 74269.1 How to Test an SMTP Mail Gateway From a Command Line Interface This should help determine if the mail connection works outside of OEM - though this is pretty much covered by the email client working - unless it is doing something fancy. Then to obtain further debugging information, turn on OMS Tracing as outlined in [NOTE:69522.1] How to Activate Logging and Tracing for EM 2 Components Is the server Exchange? If so, there are a few bugs and patches that you might want to investigate - they also contain examples of what the traces might show you See bugs 1180760, 1823589, 1935218 2268674 HTH, Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Tuesday, 25 November 2003 3:04 PM I've forgot to say that it is fully qualified production server with pop3, smtp, imap protocol support. But there are no events in smtp server logs from Oracle connection. Oracle cannot connect to it when the network is okay. You need SMTP server and it is a different kind of server from the e-mail server. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Hello! It's me else one :) When I setup Email Notifications to sysman (sender's email, smtp server, etc) it cannon send report to me cause of VD-4280 error: The SMTP Server could not be connected to. Email server is working properly, configured too. Mail client from the same machine works fine. Alert logs are clean. Where am I wrong? -- Oracle 9i DBA beginner -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Email Notification
You might like to give the version of OEM as well - though if you reply to this message cut the Metalink extracts out to reduce message size Doc ID: Note:74269.1 Type: BULLETIN Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 22-SEP-1999 Last Revision Date: 23-APR-2002 Goal: How to test and diagnose why SMTP email notifications do not work from Enterprise Manager. Environment: Oracle Enterprise Manager Simple Mail Transport Protocol (SMTP) Solution: With the steps listed below, email is tested outside the Enterprise Manager product to determine if the actual problem is with the SMTP configuration or the Enterprise Manager product. IMPORTANT NOTE! Go slowly. Any backspacing will corrupt the email message. If a message from SMTP comes back saying unrecognized command, start over. Also, the responses from the local SMTP server may be a little different from those described in this document. 1. Start a telnet session to the SMTP server's communication port. From a command prompt on either Windows NT or Unix, type: telnet smtp_server 25ENTER Where: - smtp_server is the local smtp gateway name. The name must match the name provided in the Gateway configuration window inside the EM Console. - 25 is the SMTP communication port 2. A telnet session should open with a response from SMTP: response from smtp ---220 ukxxx1 Sendmail SMI-8.6/SMI-SVR4 ready at Thu, 16 Sep 1999 15:14:25 +0100 NOTE: If the telnet session is running on Windows NT, customize the terminal settings to echo back what is typed in at the command prompt: Choose Terminal, then Preferences, then Local echo. 3. Now introduce the client machine to the server by typing: helo ENTER (The correct spelling is helo - not hello) A response from smtp ---250 ukxxx1 Hello userver.uk.oracle.com [xxx.xxx.xxx.xxx], pleased to meet you If a message is returned --- 501 helo requires domain address Then type: --- helo uk.oracle.com (substitute uk.oracle.com with the local domain of the SMTP server) 4. Tell the SMTP Gateway who the test email is coming fro by typing: --- mail from: [EMAIL PROTECTED] ENTER ([EMAIL PROTECTED] is the same name listed in the SMTP Gateway Configuration window in the EM Console) If a message is returned --- 501 Syntax error in return path Then type: --- mail from: [EMAIL PROTECTED] ENTER NOTE: Include the angle brackets around the address. A response from smtp --- 250 [EMAIL PROTECTED] Sender ok 5. Tell the SMTP Gateway who to send the test email to by typing: rcpt to: [EMAIL PROTECTED] ENTER or rcpt to: [EMAIL PROTECTED] ENTER (if using the address enclosed in angled bracket in previous step) ([EMAIL PROTECTED] is the email receiver name listed in the EM Console) A response from smtp --- 250 [EMAIL PROTECTED] Recipient ok 6. Tell the SMTP Gateway what type of information is being sent by typing: --- data ENTER A response from smtp --- 354 Enter mail, end with . on a line by itself 7. Enter the test message and remember to close the email with a dot . Type --- Subject: SMTP Test ENTER Hello this is an smtp test for EM.ENTER . ENTER A response from smtp --- 250 PAA15913 Message accepted for delivery 8. End the SMTP connection session by typing: quit ENTER response from smtp ---221 ukxxx1 closing connection The connection has been terminated. The email should then be delivered to the receiver via the SMTP server. If the command line test doesn't work, hopefully a helpful error messages from the SMTP server will be displayed indicating a problem will be with the SMTP server setup and not with Enterprise Manager. However, if the command line test is successful, a problem may exist with the EM interface or with the local configuration of EM. To obtain further debugging information, turn on OMS Tracing as outlined in [NOTE:69522.1] How to Activate Logging and Tracing for EM 2 Components
RE: SQL_Trace versus Statspack
from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 compiling pkg hangs???
This was discussed on 31-October and look in the archives for the details, but: What did you check for locking - if dba_locks or standard utllockt.sql then it won't show. You could use Steve Adam's script Executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm to see what packages are executing and who is executing them. Also, can use dba_lock_internal to look at what is being blocked: based on Oracle-L script by Diego Cutrone [mailto:[EMAIL PROTECTED] (Friday, 29 August 2003 7:54 AM) COLUMN lock_id2 FORMAT A30 select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,45) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 lock_addr FROM dba_lock_internal WHERE mode_requested 'None' and mode_requested mode_held ; and use inverse of this with a given object_name to find who has the internal locks. (I know Yong will suggest this query needs a predicate in the where clause but it works for me and I haven't had time to test alterations.) HTH, Bruce Reardon mailto:[EMAIL PROTECTED] NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Wednesday, 19 November 2003 9:15 AM To: Multiple recipients of list ORACLE-L Hi all, Our app is written using pl/sql's packages. When I tried to display one form and it runs forever. So I closed the form, and changed the sql in the package that generates the form, and recompiled the pkg, and the recompiling hangs. I checked the database, there is no locking there. What shall I do? (Oracle9i on Linux RedHat, 9iAS.) Thanks. Janet __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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[3]: How do you generate primary keys?
Haven't tried this but how does this sound: Use a sequence cache it - to allow scalability pin it so it doesn't age out of the cache have a shutdown trigger make the sequence nocache so don't lose values on a clean shutdown That leaves instance crashes and shutdown aborts to worry about So, create a startup trigger that resets the current value of all sequences to the next correct value For speed, the shutdown trigger could leave a flag somewhere so that the startup trigger only tried to process if it needed to There's probably flaws in this so certainly test, test and test. HTH, Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Friday, 7 November 2003 1:15 AM To: Multiple recipients of list ORACLE-L Wednesday, November 5, 2003, 1:14:26 PM, Jamadagni, Rajendra ([EMAIL PROTECTED]) wrote: JR hypothetically, When you have a requirement that no gaps allowed in a sequence no matter what, JR would you still use sequences? Ah! This is a good question. If no gaps are acceptable, period, end of story, then what is a viable solution? I do not think sequences are it. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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[3]: How do you generate primary keys?
And to reply to my own message - the flaw is rollbacks I guess you could use a savepoint after the sequence number, and if you want to undo the transaction put the record in an unused record history table, but all very messy. Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Friday, 7 November 2003 8:34 AM To: Multiple recipients of list ORACLE-L Haven't tried this but how does this sound: Use a sequence cache it - to allow scalability pin it so it doesn't age out of the cache have a shutdown trigger make the sequence nocache so don't lose values on a clean shutdown That leaves instance crashes and shutdown aborts to worry about So, create a startup trigger that resets the current value of all sequences to the next correct value For speed, the shutdown trigger could leave a flag somewhere so that the startup trigger only tried to process if it needed to There's probably flaws in this so certainly test, test and test. HTH, Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Friday, 7 November 2003 1:15 AM To: Multiple recipients of list ORACLE-L Wednesday, November 5, 2003, 1:14:26 PM, Jamadagni, Rajendra ([EMAIL PROTECTED]) wrote: JR hypothetically, When you have a requirement that no gaps allowed in a sequence no matter what, JR would you still use sequences? Ah! This is a good question. If no gaps are acceptable, period, end of story, then what is a viable solution? I do not think sequences are it. Best regards, Jonathan Gennick --- Brighten the corner where you are -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 9iAS Application Server
Just a thought - do you have the Intelligent Agent running a regular event / job? I'm guessing this is Windows - do the same entries appear in the Security event log with more information? Any clues in the listener log - if not listed there then (assuming not turned off) the connection must (probably?) is from a process running on the server Anything scheduled via AT or Windows scheduler (or CRON etc if this is Unix)? HTH, Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Thursday, 6 November 2003 2:49 AM To: Multiple recipients of list ORACLE-L I have a 9iAS Application Server configuration release 9.0.2 with patch set 9.0.2.1. There is one application server in addition to the infrastructure. Both reside on the same server. The Discoverer reports has a one off patch version 53. The database is release 9i version 9.0.1.3 The infrastructure has the oidmon 'Oracle Internet Directory Monitor' running The problem I'm experiencing is that an audit file is getting created about every 2 seconds in the ORACLE_HOME/rdbms/audit directory. Each audit file contains the following connect message: Wed Nov 5 10:32:04 2003 ACTION : 'connect ' OSPRIV : DBA CLIENT USER: oracle CLIENT TERMINAL: STATUS: SUCCEEDED ( 0 ) I can't determine who is connecting. Has anyone experienced this problem? Thanks Brian -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Brian McNally/AMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Finding the session causing compile to hang
David, You could use Steve Adam's script Executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm to see what packages are executing. More generally, use dba_lock_internal to look at what is being blocked: based on Oracle-L script by Diego Cutrone [mailto:[EMAIL PROTECTED] (Friday, 29 August 2003 7:54 AM) COLUMN lock_id2 FORMAT A30 select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,45) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 lock_addr FROM dba_lock_internal WHERE mode_requested 'None' and mode_requested mode_held ; and use inverse of this with a given object_name to find who has the internal locks. HTH, Bruce Reardon -Original Message- Sent: Friday, 31 October 2003 10:59 AM I need to figure out a way to see if a procedure is running before attempting a compile and I can't figure out what tables to look in. Here's a test I set up create or replace procedure sleep(i_val number) is begin dbms_lock.sleep(i_val); end; / exec sleep(60); I then check v$lock, v$access, dba_locks(9i I think only) and can't seem to spot the sleep stored procedure or it's session. Of course I could look in v$session and see it in this example but in a stored procedure that has more to it you will only see the current step it is at in the procedure and not the procedure itself. I'm trying to be able to identify sessions that hold the lock/latch on a stored procedure so I can kill them when sometimes the session is disconnected and just hangs. Thx, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Finding the session causing compile to hang
Yong, Certainly agree Steve's code is good :-) I don't think v$sql will give you the same information - it would show the top level actual package being executed, but not those which are called by that package - these do show up in Steve's code. Regarding the dba_lock_internal code - the only problem appears to be in the name used for the column - any rows returned are actually blocked - is that correct? Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Friday, 31 October 2003 12:34 PM To: Multiple recipients of list ORACLE-L Hi, Bruce, Steve Adams' code is based on x$kglpn (librarycache pin), which is correct. But the code based on dba_lock_internal blindly assumes id1 is the object name. There's a similar common misinterpretation; many DBAs assume v$lock.id1 is the object ID, which is only true for type = 'TM' (or maybe several other types). This info *is* in dba_lock_internal, but the script below just needs a type predicate in the WHERE clause. A simpler solution may be just look at v$sql where users_executing 0 for your package or procedure (shown in sql_text column). Although v$open_cursor could also be used, I don't think a row showing in there always indicates a library cache pin (executing) on the object. Yong Huang --- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: David, You could use Steve Adam's script Executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm to see what packages are executing. More generally, use dba_lock_internal to look at what is being blocked: based on Oracle-L script by Diego Cutrone [mailto:[EMAIL PROTECTED] (Friday, 29 August 2003 7:54 AM) COLUMN lock_id2 FORMAT A30 select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,45) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 lock_addr FROM dba_lock_internal WHERE mode_requested 'None' and mode_requested mode_held ; and use inverse of this with a given object_name to find who has the internal locks. HTH, Bruce Reardon -Original Message- Sent: Friday, 31 October 2003 10:59 AM I need to figure out a way to see if a procedure is running before attempting a compile and I can't figure out what tables to look in. Here's a test I set up create or replace procedure sleep(i_val number) is begin dbms_lock.sleep(i_val); end; / exec sleep(60); I then check v$lock, v$access, dba_locks(9i I think only) and can't seem to spot the sleep stored procedure or it's session. Of course I could look in v$session and see it in this example but in a stored procedure that has more to it you will only see the current step it is at in the procedure and not the procedure itself. I'm trying to be able to identify sessions that hold the lock/latch on a stored procedure so I can kill them when sometimes the session is disconnected and just hangs. Thx, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: probe database using OEM event or job
package. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 01, 2003 2:14 AM Can someone help me with this? Running Oracle 9.2.0.3 under win2000 I have an application server that occasionally looses connectivity with the listener on the database server although other application servers have no problems connecting. I get error 'Fatal NI connect error 12535' I would like to setup an OEM event (user defined?) or user defined job that must run from the application server having the problem which will test the connection to the listener on the database server (could be like a tnsping XXX) and if it is unsuccessful, notify me via email, pager, etc. has anyone done this? Can it be done with a TCL script? Any samples appreciated? John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada (604) 697-6476 (Office) (604) 313-6054 (Cell) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hiding passwords
Jared, Not saying this is elegant or does the same thing yet but couldn't you do something like this: C:\Tempcopy con: pwd.txt orcl tiger !my Orcl instance ^Z 1 file(s) copied. C:\Temptype pwd.txt orcl tiger !my Orcl instance C:\Tempget_pwd Instance is [orcl] pwd is [tiger] C:\Temp C:\Temptype get_pwd.bat @ECHO OFF FOR /F tokens=1-2 delims= eol=! %%i IN (pwd.txt) DO call :get_pwd %%i %%j GOTO :end :get_pwd (SET theinst=%1) (SET thepwd=%2) ECHO Instance is [%theinst%] ECHO pwd is [%thepwd%] :end (SET theinst=) (SET thepwd=) C:\Temp Enhance the batch to take some parameters and enhance get_pwd subroutine to search for that which matches the parameters. Anyway hope this is of use to some. Regards, Bruce Reardon -Original Message- Sent: Wednesday, 24 September 2003 9:45 AM there is no command.com equivalent for this: MY_PASSWORD=$(pwc.pl -instance dv01 -username scott) Simple in ksh, impossible in un-enhanced Windohs. That previous bit is something I use in several cron jobs for retrieving passwords, as well as command line logins to several databases as many different users. To do this in Windohs, you must embed the entire job in Perl. HTH Jared -- Wolfgang Breitling [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/23/2003 03:29 PM I don't quite get that. Why can't you set a local environment variable from a script? If you could, where do you propose to get the value that you want to put into an environment variable? At 01:59 PM 9/23/2003 -0800, you wrote: Paul, Any chance these scripts could be run from Cygwin, Uwin, MKS Toolkit, or anything that will let you use a korn shell? That would simplify things tremendously. One of the problems with Windohs is that you cannot execute a script or program so that it can return a value to a local environment variable. That ability would make this task simple from command.com. Another possibility is to put your passwords in the registry, restrict that portion of the registry, ( or the whole thing ), and use a Perl script to retrieve the passwords and kick off the other jobs. What I do in linux is use a password server ( as seen in Perl for Oracle DBA's) and retrieve the password across the network, encrypted of course. This works on windows as well, though you're there restricted to doing this strictly from within the Perl script. Jared Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Isnumeric question
Previously posted by Jared is the is_number function below:
You could avoid the decode statement and have the exception return zero for a
non-numeric parameter - of course you'd want to rename the function to something more
meaningful in that case.
-Original Message-
Sent: Saturday, 1 February 2003 8:05 AM
To: Multiple recipients of list ORACLE-L
create or replace function is_number( chk_data_in varchar2 )
return boolean
is
dummy number(38,4);
begin
dummy := to_number(chk_data_in);
return true;
exception
when value_error then
return false;
when others then
raise;
end;
/
show errors function is_number
declare
v_test varchar2(10) := '1E';
begin
if is_number(v_test) then
dbms_output.put_line(v_test || ' is a number');
else
dbms_output.put_line(v_test || ' is NOT a number');
end if;
end;
/
HTH,
Bruce Reardon
-Original Message-
Sent: Tuesday, 23 September 2003 11:00 AM
On 2003.09.22 20:15, Teresita Castro wrote:
Hi!!
I want to made something like this ina query
select decode( isnumeric(line_comment), to_number(line_comment),0)
how can I do this in Oracle?
my $line_comment;
my $sth=$dbh-prepare(Select line_comment from);
$sth-execute();
$sth-bind_col(1,\$line_comment);
while ($sth-fetchrow_array() {
if ( $line_comment =~ /^[0-9]+/) {..}
}
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reardon, Bruce (CALBBAY)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Insert performance
Rick, I haven't tried this myself but you could consider a reverse key index (depending on your version). That way multiple inserts won't go to the same block. However, (from Perf Tuning 101) you will incur much more IO than a normal index if you do range scans, so you'd need to consider how your index is accessed (probably not doing range scans on a primary key sequence). Obviously this will need testing to see how well it goes for you. HTH, Bruce Reardon -Original Message- Sent: Tuesday, 23 September 2003 1:45 PM Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes buffer busy waits. I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: log miner views contain object IDs and HEX values
Steve,
Did you have set serveroutput on?
I have found you need this before you'll get error messages from Logminer.
I found this in Metalink note Note:69606.1
HTH in the future,
Bruce Reardon
-Original Message-
Sent: Saturday, 20 September 2003 9:40 AM
OK seems I must have fat fingered something when I typed in my dictionary
location in start_logmnr. Odd though I saw no errors.
-Original Message-
Steve McClure
Sent: Friday, September 19, 2003 1:25 PM
OK my first usage of log miner seemed to go exactly by the book until I went
to look at the data in v$logmnr_contents. The sql_redo column was filled
with strings like the following.
update UNKNOWN.Objn:3167 set Col[43] = HEXTORAW('7867090f010101') ...
I created the dictionary file, and didn't get any errors when I referenced
it in the start_logmnr procedure. I understand that the dictionary file is
required to get my actual object names and column names and such. I am also
pretty sure it was created correctly, so it shouldn't be the root of my
problem.
As I am typing this, thinking it over, I realize that I was logged in as my
own user connected as sysdba. Maybe I should redo this connected as ths sys
user. I created the dictionary file as the sys user. I am gonna have to
try that after lunch. While I do though, and since I have already typed
this much, I am gonna toss this up to my fellow listers. The only
documentation I have been using is the 8i Administrators Guide, and one of
its examples references a column that isn't in the v$logmnr_contents view.
Perhaps I need a different reference.
Thanks,
Steve
Author: Steve McClure
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reardon, Bruce (CALBBAY)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Client hangs on some key values
Have a look at the view dba_lock_internal (?\rdbms\admin\catblock.sql) - maybe it is hanging on a pin / parse - perhaps from a session someone killed when it took too long? Have a look at Metalink note 122567.1 about an update to that view that greatly improves performance under 8i. A suitable query for dba_lock_internal was posted today by Diego Cutrone: select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,45) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 lock_addr FROM dba_lock_internal WHERE mode_requested 'None' and mode_requested mode_held ; We had a situation where Forms 4.5 generated a download query that was a page or too long The session would hang and never parse Then the stats collection would fail on an internal lock Then all access to the table would be blocked As for view to see if the query is received, not a view but you could try Net8 tracing - on the client side and / or at the listener. Have a look at Metalink note 16658.1 on Tracing Net8 + associated parameters. You could also try tracing of the Oracle session - eg sql_trace or event 10046. HTH, Bruce Reardon -Original Message- Sent: Friday, 29 August 2003 2:14 PM New DBA on the block and already ran into my first problem: We have an Oracle Database (8.1.7.0) running on Clustered HP-UX. Oracle client running on a HP-UX (11) B2000 machine. The query looks like select col1, col2, col3, 0, col4, col5, 0, 0, col100 from table 1 where col1 = '123' This query works for most everytime except certain selected col1 values. col1 a primary key. For the unfortuante col1 values, the client just hangs. I looked under v$session_wait, don't see anthying unusual. Only following events I have noticed: Message to/from SQL*CLient, more data to SQL*Client, file open The values for the wait were not any different from the sessions when the SQL got executed. Although the query never makes to V$SQL view. No locks on the table. verified with V$lock views. No corrupt blocks: verified with analyze ...cascade command. TNSPING normal It is interesting that these queries only fail from this one client. It works fine on the server or any other client. Network guru says he doesn't see any problem with network either. Changed the machine but still didn't help. Memory, CPU looks good on the client and server. Do you have any idea what might be wrong here? Is there a view I can refrence to show that the sever never recived the client request? TIA SB This is going to be a great day!!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Forms - as sysdba
Hi I don't have 9i to test, but I believe you might be able to set O7_DICTIONARY_ACCESSIBILITY = TRUE in init.ora and then you should be able to connect as sys without specifying sysdba. This might be suitable for the upgrade - certainly test it out on a test system first. Regarding the upgrade - why exactly does it need to be connected as sys - eg what doesn't work if you connect as system. HTH, Bruce Reardon -Original Message- Sent: Friday, 22 August 2003 4:49 PM Robo You can connect as ifrun60.EXE C:\...\..\logon2.fmx Regards Suhen -Original Message- Sent: Friday, 22 August 2003 4:39 PM I MUST connect as sys, because it's a part of an upgrade of our system. I will try to find some kind of patch for this situation as Jared suggested. Anyway, it should be possible to connect from command line, shouldn't it? Something like: c:\orant\bin\ifrun60.exe C:\dev\form.fmx sys/[EMAIL PROTECTED] \/'as sysdba' =RP= -Original Message- Sent: Thursday, August 21, 2003 5:54 PM Rather, why do you want to connect as SYS ? If you've created some custom tables in the SYS schema in an earlier version, create another user, connect as SYS in sqlplus, GRANT privileges to that user and connect as that new user in Forms. -- and think about migrating the tables out of the SYS schema. If you are querying standard tables, OEM provides good views for most of what you need and the group here can provide better SQL scripts anyway. Hemant At 07:29 AM 21-08-03 -0800, you wrote: Try if it works if you put sys/sys as sysdba to forms username prompt? (without quotes) But why do you want to connect as sysdba anyway? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, August 21, 2003 11:54 AM Hi all, I have a 9.2.0.3 DB and I need to connect to Forms 6i as user sys. There are 3 boxes for username, password and database. I tried a lot of combinations, eg: Username: sys as sysdba Password: sys Database: db sys/[EMAIL PROTECTED] as sysdba sys/[EMAIL PROTECTED] /as sysdba sys/[EMAIL PROTECTED] 'as sysdba' sys/[EMAIL PROTECTED] '/as sysdba' But I always get an error message - either invalid username/password or TNS error (can't recognize the connect string). I have also tried it from command line but didn't succeed. Does anyone know if/how is it possible? Thanks a lot Robert Pipich -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Urgent! Is it safe to aply patch Windows2000-KB823980?
Hi, We've applied this on NT4 and W2K servers running 81745 with no Oracle issues. However, have seen 1 anomaly after this patch: nbtstat against a remote server no longer shows the logged on user name. This may have been a security improvement by design. Note you can still use the freeware psloggedon to see who is logged on to a server (may require admin privs on the target). Regards, Bruce Reardon -Original Message- Sent: Wednesday, 20 August 2003 3:49 AM Luis: We're patching like crazy, too. So far, none of our systems has experienced any issues. We're running mostly 8 and 8i, with a couple 9i machines in there too, on NT4 and Win2k. Cheers, Mike -Original Message- Sent: Tuesday, August 19, 2003 10:39 AM Has anybody applied Windows2000-KB823980-x86-ENU on their boxes hosting the Database? We are running 9i 9.0.1.1.1 on an Windows 2000 advanced server and our network group is patching all the systems. Is there anything that I need to watch for? We are also runing 9iAS ver 1.0.2.2.0 on Windows 200 advanced server, anything to be aware here? Any information is very welcomed. TIA Luis Octavio de Urioste -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle and Windows 2000 SP4 - any experiences to share
Hi, Does any one have experiences with Oracle and Windows 2000 SP4 to share? Has it worked, are you using it? We are hoping to have it in use by the end of the month (we're currently using W2K SP3). We are using Oracle version 8.1.7.4.5. Also, has any one applied hotfix MS03-026 to a Windows server and got any good or bad experiences to share? Thanks, Bruce Reardon -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: enterprise manager
When you say start the enterprise manager console and try to login as sys do you mean that the EM console login box that prompts for username, pwd and management server you enter sys as the user? If so, you need to enter an EM username password rather than a DB username / password. Also, in the Management server prompt you would normally enter a computer name rather than database name. To help us with more information - are you hoping to have the repository, OMS and console all on server As others have said - is the OMS service actually running. Sorry if this is on the wrong track. HTH, Bruce Reardon -Original Message- Sent: Thursday, 17 July 2003 3:45 AM Martin, I am wondering if the OMS is running at all... oemctl status oms on UNIX or check the services applet if you are in Windows. Patrice. -Original Message- Sent: Wednesday, July 16, 2003 1:20 PM the problem is you're trying to use enterprise mangler :P -Original Message- Sent: Wednesday, July 16, 2003 10:50 AM Check: drive:\ORACLE_HOME\sysman\log review the oms.log file... and send the details... HTH JL --- Ruth Gramolini [EMAIL PROTECTED] wrote: Is it an 8.0.x or 8i database? What version of OEM are you running? We need more info to be able to help you. Ruth - Original Message - Sent: Wednesday, July 16, 2003 6:19 AM hi i run ora8 on w2000 server. my problem is i can ping my database mydb without problems i can login with sqlplus as sys and it works fine but if i start the enterprise manager console and try to login as sys a error occours and means please check the management server status and settings. as managementserver i select mydb. what ican be the problem? thx martin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: FTP command -without user interaction
What OS? Since you say batch file I will presume Windows. Use the -s:filename parameter and pass a scriptfile to the ftp command You can generate the scriptfile on the fly with echo commands and symbol redirection: eg: echo verbose off %ftp_tfile% echo open %ftp_tgt_node% %ftp_tfile% echo %ftp_tgt_user% %ftp_tfile% echo %ftp_tgt_pwd% %ftp_tfile% echo type ascii %ftp_tfile% echo cd %3 %ftp_tfile% echo put %1%2 %ftp_tfile% echo bye %ftp_tfile% rem Do the actual ftp using this script file rem TYPE %ftp_tfile% ftp -s:%ftp_tfile% Example: C:\Temptype ftpscript.ftp open mynode username secretpassword type ascii cd sys$login get login.com bye C:\Tempftp -s:ftpscript.ftp ftp open mynode Connected to mynode. 220 mynode FTP Server (Version V4.1-12) Ready. User (mynode:(none)): 331 Username USERNAME requires a Password. 230 User logged in. ftp ftp type ascii 200 TYPE set to ASCII. ftp cd sys$login 250 CWD command succesful. ftp get login.com 200 PORT command successful. 150 Opening data connection for login.com (a.b.c.d,2828) 226 Transfer complete. ftp: 1570 bytes received in 0.20Seconds 7.85Kbytes/sec. ftp bye 221 Goodbye. C:\Temp HTH, Bruce Reardon -Original Message- Sent: Friday, 4 July 2003 4:09 PM Hi All, How to execute FTP command without interaction. Basically i want to supply hostname,username,passwors,filename thru batch script. For example ftp hostname userName passWord fileName Could someone help me to do the same? Thanks Sami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Cannot allocate new log - checkpoint not complete
There are some other effects I can think of. Up to you if these are important / significant to your users. If you make them bigger and you have a standby database then the standby might end up being further behind production (unless you have a script to workaround this) and also in this case you may then not get the files transferring successfully (depends on your network etc). Also, if you make them bigger and you have a loss of all redo logs (hopefully unlikely if they are mirrored on mirrored disks) then you will lose more data as it will be a longer period of time since the last archive log was created. And what about your archive log management scripts - do they keep x days worth of files (in which case the volume of archive log on disk will not change) or do they keep y files - in this case the volume of disks would increase unless the script(s) are altered. Regards, Bruce Reardon -Original Message- Sent: Friday, 4 April 2003 3:44 AM To: Multiple recipients of list ORACLE-L Correct. The only potential disadvantage is that recovery will take longer when bringing up the database after a crash. Jay Miller -Original Message- Sent: Thursday, April 03, 2003 9:39 AM To: Multiple recipients of list ORACLE-L I dissagree, they will be bigger but there will be less of them. If the amount of processed data does not change, I do not think changing the size of the redolog files should affect the total amount fo bytes to be backeup up Thanks for the recommendation anyway ;) Cheers, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Stefan Jahnke Enviado el: jueves, 03 de abril de 2003 15:44 Para: Multiple recipients of list ORACLE-L Asunto: AW: Cannot allocate new log - checkpoint not complete Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
RE: Function problem
Alec,
Have a look at the 2 explain plans and see how they are different.
Have a look at wait stats / 10046 trace for the two and see how they are different.
What version of Oracle?
Do you have histograms?
If 8i or below and using the function then you will be using bind values and not
getting best effect from your histograms.
If you put the values directly into the select then your histograms will be used for
selectivity determination in creating
Of course, it might be something else entirely...
HTH,
Bruce Reardon
-Original Message-
Sent: Friday, 21 March 2003 10:24 AM
I have written a function to return a drug price from our database. If I use
this function in a SQL statement it take a long time to return a value.
However running the main cursor in the function in SQL returns a value
immediately. DOing a little debugging I find that the function does 6000
physical reads compared to 8 as a SELECT statement.
Function looks like this
FUNCTION GetPrice(DrugID varchar2, PriceListID number) return number IS
cursor main(DrugId varchar2, PrcId number) is
SELECT price
FROM prices p
WHERE ndc = DrugId
AND price_list = PrcId
AND effective_begin (SELECT max(effective_begin)
FROM prices
WHERE ndc = p.ndc
AND price_list = p.price_list);
ReturnVal number(10,2);
BEGIN
OPEN main(DrugId,PriceListID);
FETCH main
INTO ReturnVal;
CLOSE main;
RETURN ReturnVal;
END Getprice;
IF I run 'SELECT GetPrice('1234',1) FROM dual;' it takes 6000 physical
reads.
If I run the select statement in main replacing DrugID and PrcID with values
it take 8 reads or less.
I know I will see a preformance hit for embedding a function in a select
statement but this seems a bit draconian. Could someone recommend a path
that might explain why I have so much overhead on this function?
Alec
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reardon, Bruce (CALBBAY)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: optimizer_mode=FIRST_ROWS
Karen, Are you on version 8? I imagine so given the problem you are seeing. By using FIRST_ROWS you are forcing the optimizer to use CBO even when there are no statistics. Most likely you have no stats on your sys objects (and this is a good thing) and thus the execution plan the CBO is providing will be a bad one. There a few options: rewrite the query - as you have done hint the query with specific hints to cause the correct execution path For DBA queries like this, the easiest may be to hint to use RULE base optimisation- eg select /*+RULE*/ Some notes suggested by Anita Bardeen when I posted on a similar topic in Nov 2001. Note: 35272.1 Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)? Note: 35934.1 TECH: Cost Based Optimizer - Common Misconceptions and Issues Note: 66484.1 Which Optimizer is used Some other points I have found: In first_rows mode you will encounter some very bad queries against the data dictionary. An example of 1 which has been fixed by Oracle is catblock.sql - there is an updated version available on Metalink - see note 122567.1 titled Poor Performance in Query onDBA_WAITERS Whilst searching for the notes suggested by Anita, I came across a good forum discussion (see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FORp_id=279251.999 ) This describes how the ODBC driver 8.1.7.4 has been fixed / improved to use rule hints when accessing the data dictionary. Before this if you used the Oracle ODBC driver and were in first_rows mode we had to wait 5 - 10 minutes just to link a table in Access HTH, Bruce Reardon -Original Message- Sent: Tuesday, 18 March 2003 7:39 AM To: Multiple recipients of list ORACLE-L All, I've run into the following queries hanging when ran on a database with the optimizer_mode set to FIRST_ROWS. If the optimizer_mode is CHOOSE, no problems. When set to FIRST_ROWS both queries show never-ending wait events for direct path read. I killed the sessions before they finished after waiting for almost an hour for the queries to complete. I re-wrote the 1st query against dba_tables to use EXISTS (also shown below) and that seemed to work fine. But I'm not sure why or how to re-write the 2nd query to also be able to workI've tried a couple of things with no luck. Any ideas? Thanks for the help, Karen Morton select dba_tab_columns.table_name, dba_tab_columns.column_name, dba_tab_columns.column_id, dba_tab_columns.data_length, dba_tab_columns.data_type, dba_tab_columns.nullable, dba_tab_columns.data_precision from dba_tables, dba_tab_columns where dba_tables.owner = 'XYZDBA' and dba_tables.table_name = dba_tab_columns.table_name order by dba_tab_columns.table_name, dba_tab_columns.column_id ; select dba_indexes.table_name, dba_indexes.index_name, dba_indexes.uniqueness, dba_ind_columns.column_name, dba_ind_columns.column_position from dba_indexes, dba_ind_columns where dba_indexes.owner = 'XYZDBA' and dba_indexes.index_name = dba_ind_columns.index_name order by dba_indexes.table_name, dba_indexes.index_name, dba_ind_columns.column_position ; -- Rewritten dba_tables query that works select dba_tab_columns.table_name, dba_tab_columns.column_name, dba_tab_columns.column_id, dba_tab_columns.data_length, dba_tab_columns.data_type, dba_tab_columns.nullable, dba_tab_columns.data_precision from dba_tab_columns where EXISTS (SELECT * FROM dba_tables WHERE owner = 'XYZDBA' AND table_name = dba_tab_columns.table_name) order by dba_tab_columns.table_name, dba_tab_columns.column_id ; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tablespaces - datafiles
I don't do this as I'm using Windows but how about this for a suggestion? get the sum of max sizes from Oracle - via dba_temp_files get the sum of the current sizes from Unix get current free space from Unix of the mount point Check that (Unix free space + Unix current sizes) - (sum Oracle max sizes) some acceptable value I don't know for sure its possible but it seems reasonable to me Regards, Bruce Reardon -Original Message- Sent: Thursday, 13 March 2003 5:24 AM yup.. we haven't been bitten but we know the day is coming when its going to happen... I'm still looking for a trick or rule of thumb to help avoid the problem of over allocating that mount point at 3:00AM. So far what I have done only put tempfiles on the mount point not other datafile. Put a warning message file in the directory...but I still feel this is not enough logic protection against someone forgetting it. Anybody got any other ideas of how to manage these tempfiles? Brian -Original Message- Sent: Tuesday, March 11, 2003 3:38 PM Use caution with tempfiles. They are wonderful, but when they create, they do not take up their specified size on the file system. For example, you create a temp tablespace with one temp file of 1000M on a 2000M filesystem. An ls -l will show the 1000M file size but a bdf will show that it is not really using that space yet. Just something to keep in mind so that no other files or temp files are put on that file system that will exceed the space that both of them need. What makes this really confusing is when a sort operation is trying to use that space and can't get it. You get errors that appear that you have run out of temp space, then look in dbastudio / oem, etc. and see that your 10 GB temporary tablespace is only 50% full. Once the sort tries to use that space in the file which is being inhibited by space, it can't get past that. Sound like I've been bitten by this? (more than once) [EMAIL PROTECTED] 3/11 2:30p Jared, Same behaviuor on HP-UX version 11 with Oracle 8.1.7.2. Whatever size you define for tempfile , it is created with that size. Only observation that it is created much quicker than normal datafile of same size. Regards Rafiq Reply-To: [EMAIL PROTECTED] Date: Tue, 11 Mar 2003 11:42:54 -0800 Tom, Do you have that doc ref handy? Using this SQL: create temporary tablespace temp3 tempfile '/u01/oradata/dv03/temp3.dbf' size 500m extent management local uniform size 1m / On both 8.1.7.0 and 9.2.0.1 on RH 7.2 I found that the file was immediately created full size. Platform dependencies maybe? Jared Mercadante, Thomas F [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/11/2003 06:19 AM Please respond to ORACLE-L Subject:RE: Tablespaces - datafiles LeRoy, I just struggled with this last week. You can't move Temporary Data files. You need to drop and recreate the TEMP tablespace - creating the data files in the correct directories. On a side note - here is an interesting feature. When Oracle creates files for the TEMP tablespace, it does not create the files full sized like it does for normal data files. It creates them smaller for speed purposes (it creates the TEMP tablespace very fast) and will allow the TEMP data files to grow as needed. Now here is the kicker. Let's say you have a disk that is 9 gig is size. You can create 10-1 Gig Temp data files on that disk. Since Oracle does not create the files full-sized, there is nothing to stop this from happening. Sometime later, as the TEMP tablespace gets used, the files grow until eventually the disk fills up, and a sql query crashes with an obscure disk io error. Oracle is trying to expand the TEMP datafiles to the size it's been told they should be. But there is no physical space left on disk. Documentation in 817 does not mention this. But 92 doc's are up to date. nice surprise, eh? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 11, 2003 8:39 AM All - I am having a problem with the datafiles in a temporary tablespace. I need to move and rename three different datafiles in the tablespace. I am able to take them offline - no problem. I cna make the changes at the OS level. I am running on Unix. But I can't get the changes to show up in the OEM inorder to bring them back on-line. Do I need to remove all users from this tablespace before making these changes? The tablespace is temporary so does that make a difference? Any suggestions? LeRoy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
RE: shared tnsnames.ora
Regarding distributing the tnsnames - you could use a very simple Windows batch file to do that (eg have PC names in 1 text file) and use FOR /F to process it. This works best if tnsnames lives in the same place on each client. Or create an env variable / registry key on each client to point to a common network share (that had better be available all the time). With respect to listener.ora and localhost - has anyone using OEM's Intelligent Agent (IA) got the IA to be able to discover databases where you use localhost in the listener - I haven't yet. Regards, Bruce Reardon -O riginal Message- Sent: Friday, 28 February 2003 9:14 AM To: Multiple recipients of list ORACLE-L If you are not going to use Onames, another consideration is to create an DNS alias for your db server host and use the alias in the tnsnames file for host value. It makes it easy to the move database to another server and make 1 change to DNS. On the listener side, I always use localhost for host value. hth, Gene [EMAIL PROTECTED] 02/27/03 03:49PM Below is the end of my own tnsnames.ora located on my PC. There is a common network tnsnames.ora on a network share located on my 'R' drive. The network version has all the permanent databases defined in it. I can add any temporary or new test servers to my copy of tnsnames.ora and still get to all the permanent entries when ever I need to. Works great! R. Smith test.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = someserver) (Port = 1521) ) ) (CONNECT_DATA = (SID=test) ) ) ifile=r:\tnsnames.ora -Original Message- Sent: Thursday, February 27, 2003 2:24 PM Use Oracle Names. Easy to setup/maintain. never touch a client config again. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan - I am looking for info on how you support a large number of PCs (200+) and keep each ones tnsnames.ora file in sync. It seems that most people do not touch them. Some try to modify them and when new databases are created, the tnsnames.ora files must be changes as well. It seems to be that a shared tnsnames.ora file on a network drive may work. I remember a thread awhile back about the order of resolution (home directory, then OH/network/admin .). Again, I am asking about people using the Oracle client to connect to 15+ databases (v7.3.4 - 9.0.x) on 10+ different servers. I have just started to think about this and posted here before I started my MetaLink search. TIA for any info. JF John Fedock K Line America, Inc. www.kline.com ( 804.327. * [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Purely for your amusement
SORT (GROUP BY) 11 10 TABLE ACCESS (BY ROWID) OF 'WO_CHARG' (Cost=690469 Card=2615655 Bytes=68007030 12 11 INDEX (FULL SCAN) OF 'PK_WO_CHARG' (UNIQUE) 138 SORT (JOIN) 14 13 TABLE ACCESS (FULL) OF 'PUB' (Cost=968 Card=608188 Bytes=42573160) 156 SORT (JOIN) 16 15 TABLE ACCESS (FULL) OF 'WO' (Cost=190903 Card=15662455 Bytes=4228862850) 175 SORT (JOIN) 18 17 TABLE ACCESS (FULL) OF 'NAD' (Cost=2792 Card=500401 Bytes=24519649) 194 SORT (JOIN) 20 19 TABLE ACCESS (FULL) OF 'CNR' 213 SORT (JOIN) 22 21 TABLE ACCESS (FULL) OF 'WOE' (Cost=47 Card=18407 Bytes=1583002) 231 SORT (JOIN) 24 23 TABLE ACCESS (FULL) OF 'WO' (Cost=190903 Card=15662455 Bytes=610835745) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Alter table monitoring ... impact on performance??
From Steve Adam's July 2000 newsletter (http://www.ixora.com.au/newsletter/2000_07.htm, line spacing below is mine): Despite the potential for improved statistics gathering, many DBAs have not yet adopted modification monitoring. One of the concerns that people have is that the monitoring might have a significant performance overhead. In fact, this is not the case. The modification counts are maintained in an efficient hash table is the SGA, and are updated without the protection of a latch (although the structure of the hash table itself is protected by the hash table modification latch). Even in heavy OLTP environments, the cost of maintaining the modification counts is likely to be less than 1% of additional CPU usage. However, because of the latch-free nature of the feature, the modification counts are not guaranteed to be accurate. Another source of potential inaccuracy is that if a transaction is rolled back, its changes to the modification counts are not rolled back as well. These inaccuracies have been allowed by Oracle to keep the performance overhead of this feature minimal. Therefore, you can use modification monitoring with confidence that it will not affect performance significantly, while giving you a very helpful indication of which tables may have stale statistics. HTH, Bruce Reardon -Original Message- Sent: Friday, 17 January 2003 10:26 AM I've seen mention a of negligible performance hit for this. It was from someone I trust, but I can't recall just who that was. Jared --- Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/16/2003 12:50 PM Subject:Alter table monitoring ... impact on performance?? Does any one know the performance impact on 'alter table monitoring' ?? (this is for Oracle 9202) Should we expect any slowness ?? Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tracing or not tracing, this is the question now.
Mladen, Form 2 previous list postings: -Original Message- Sent: Wednesday, 30 January 2002 5:51 AM To: Multiple recipients of list ORACLE-L Here you go... 1) Find the OSPID for the suspected user (other than pmon, smon and their famiy) select s.username, p.spid from v$session s, v$process p where s.paddr = p.addr; 2) Use oradebug to connect to that spid (here I have 26073, and the session was tracing event 10046) SVRMGR oradebug setospid 26073 Oracle pid: 11, Unix process pid: 26073, image: oracle@myservername (TNS V1-V3) 3) Dump the events for the connected spid... SVRMGR oradebug dump events 1 Statement processed. 4) Look in the trace file in the udump directory.. *** 2002-01-29 09:58:55.847 Dump event group for level SESSION TC Addr Evt#(b10) Action TR AddrArmLife 400E1B68 10046 1 400e1ba8 0 0 TR Name TR level TR address TR arm TR life TR type CONTEXT 8 -1 2 0 ^^^ This session is tracing event 10046 context at level 8 5) Dump it again (from 3)... 6) Look in the trace file again *** 2002-01-29 10:01:57.316 WAIT #1: nam='SQL*Net message from client' ela= 34381 p1=1650815232 p2=1 p3=0 = PARSING IN CURSOR #1 len=56 dep=0 uid=80 oct=42 lid=80 tim=2447763925 hv=3475487367 ad='a0de14e0' alter session set events '10046 trace name context off' END OF STMT PARSE #1:c=0,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2447763926 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2447763927 -- Oops! The the smart A$$ turned off the trace, But got caught!!! Following is what you see in the trace file when the session is not tracing. Dump event group for level SESSION TC Addr Evt#(b10) Action TR AddrArmLife ~ I thank Ross for his nifty hints and his time last night, when I was having a 'brain fart' while testing this... I learned something new from him... :) Cheers! - Kirti OR It would seem this approach will only work for the current session -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, 6 July 2001 10:11 AM To: Multiple recipients of list ORACLE-L Hi Walt - Kinda getting into this myself recently. I found a reference to an undocumented dbms package on Metalink today called: dbms_system.read_ev I'm playing around with their test scripts to see what I can do with this in conjunction with v$session and tracing and auditing. Check out notes 1020308.6 and 28446.1 Hope this helps. David A. Barbour Oracle DBA, OCP AISD 512-414-1002 -Original Message- Sent: Thursday, 16 January 2003 8:41 AM I'm looking for a way of checking which sessions on the database have trace turned on. I can set event in another session, but I'd like to know whether the event (10046 in this case) is set in some sessions. Mladen Gogala Oracle DBA Oxford Health Plans www.oxhp.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Info about running procs
Take a look at Steve Adam's http://www.ixora.com.au/scripts/sql/executing_packages.sql This script lists the packages (and other stored code objects) that are currently being executed, and the SIDs of the executing sessions. It is listed as for 8.0 / 8.1 Remember to check out the prerequisites at http://www.ixora.com.au/scripts/prereq.htm HTH, Bruce Reardon -Original Message- Sent: Wednesday, 15 January 2003 7:29 AM [EMAIL PROTECTED] wrote: Hi list, I'm looking for a Oracle system view or table where I can see all actual running procedures. Where can I find this info. TIA Volker Schoen E-Mail: [EMAIL PROTECTED] http://www.inplan.de I think that a join between v$session (where status = 'ACTIVE') and x$kglrd should do it (join SQL_ADDRESS + SQL_HASH_VALUE to kglrdhdl + kglnadhv). You need to be SYS of course, so perhaps you'll want to create a view. Author: Stephane Faroult -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Export/Import Error and Validation !!!
Tracy, Have a look at bug 2475331 / 2410612 RDBMS CONVENTIONAL EXPORT HAS WRONG DATA ON IMPORT This is listed as having happened in 8173. Bug has no details of why or who it might affect. 1. Use direct=true and the problem would not reproduce 2. Set the buffer size 100 and the problem would not reproduce. Note 199416.1 has more info: 8170-8172 not affected. Regards, Bruce Reardon -Original Message- Sent: Wednesday, 8 January 2003 10:11 AM We attempted to reorg a table and data corruption resulted. We have isolated the issue and currently have a tar open with Oracle. But basically, when we export the table with a buffer=10485760 the import process corrupts the data. The row count matches, however some fields that initially were null now contain data (there were some other odd data issues in addition). No errors were produced during either the export or import process. When we used a buffer=65536 the table data was correct. This is reproducable. Has anybody seen this before? (AIX 4.3.3 / Oracle 8.1.7.3) Can the buffer size be set too high? In addition, are there any audits that we can employ to catch such an error? For example, row counts were the same prior and post the process. Are there other things that can be checked. How do others validate that a reorg was successful? We were thinking about executing a data compare through sql of the old and newly reorged table. This seems excessive and not practical for very large tables. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Application Server on .net
.Net does refer to an OS (in addition to referring to a platform / group of languages). To see some information from Oracle on this have a look at http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=161546.1p_database_id=NOT This includes ...In the future Microsoft will be delivering server editions of Windows XP, these will use the Windows .NET Server naming convention. ... To see some details on Microsoft's web site look at http://www.microsoft.com/windows.netserver/default.mspx Windows .NET Server 2003 Release Candidate 2 is here. You can download it from http://www.microsoft.com/windows.netserver/preview/default.mspx What error does the developer doing the install get? Regards, Bruce Reardon -Original Message- Sent: Wednesday, 18 December 2002 10:54 AM .Net is not an OS. The developer (here it comes) doesn't know what he's talking about. -Original Message- Sent: Tuesday, December 17, 2002 3:34 PM List: I have a developer trying to install Oracle9i Application Server Release 2 (9.0.3) on a .net server. He's using the install disk for Windows NT and 2000. Needless to say, it gives him an error and throws up. He considers .net just another operating system like np or 2000. I think of it more like a competitor for OAS. At any rate, I can't find any mention of .net on either Metalink or OTN (except how much better OAS is than .net) Does anyone know if Oracle has an application server installation for .net? If so, do you know how I might get it? Is this a silly question? Should I be hanging my head in shame?? Thanks in advance for any information. Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Leading spaces in dbms_output.put_line
Steve,
Have a look at Metalink Doc ID: Note:108091.1
For those without Metalink access this suggests:
1) Use format wrapped at the end of your Set Serveroutput command.
This works with SQL Plus version 3.3 or above.
This will keep leading spaces and double spacing
2) If you're on an older database, you can use CHR(10) and CHR(9)
to produce blank lines and leading spaces respectively
I have also found that
3) chr(0) works as well (found in
http://www.quest-pipelines.com/Pipelines/PLSQL/archives/search.sql)
Cheers,
Bruce Reardon
-Original Message-
Sent: Tuesday, 17 December 2002 7:14 AM
Good afternoon,
Some time ago there was a response to the
question of how to get leading spaces to display
when using dbms_output.put_line.
I have tried -
dbms_output.put_line(' '||tabrec.table_name);
but do not get the leading spaces.
When someone mentioned how to do this I said of
course but naturally have forgotten the
technique.
Can some one share?
Thanks folks...
Steve Haas
Opus Consultants, LLC
860.408.1512 (office/fax)
860.651.9475 (home)
[EMAIL PROTECTED]
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reardon, Bruce (CALBBAY)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: IOT Issues?
Larry, Have you seen paper 138 at Orapub.com (http://www.orapub.com/cgi/genesis.cgi?p1=subp2=abs138) titled Index Organized Tables -- When should they be used? This has some benchmark figures. Also, do you use Forms as a client - this can introduce some gotchas with IOTs (particularly if still on Forms 4.5)? HTH, Bruce Reardon -Original Message- Sent: Friday, 13 December 2002 1:19 PM Listers, Solaris 7, 8.1.7.4 64 bit, E10K. Have a test IOT of around 120 million rows being created as we speak -- partitioned by month (3 months for the test), overflow by naming the column at which to break, compressing the concatenated key, using secondary BMI's. BMI's would be marked as unusable and rebuilt after loads if used in the real world. We've been reviewing Metalink for gotcha's (found a few, some fixed in our version, some minor), and have opened a tar since many known bugs aren't published, but just curious if anyone else has run into some big issues. I'm looking forward to running some comparison queries, and inserts, against the IOT and the existing partitioned heap table (with 400+ columns, don't ask why, but gives a hint as to why we are looking at IOT's and the use of the overflow ;-)). So ok, plans are to split that table into 20 some odd commonly used columns and the rest into a separate table in a 1 to 1 relationship, greatly reducing the number of blocks we have to visit to satisfy the typical query. Or really looking at a re-design more complex than that -- the modelers (not the original ones!) have a few things in mind. Using an IOT and the overflow might help avoid this and a lot of code changes, and might be a good intermediate relief step, or maybe even long term. Secondly, we CTAS partitions out using an order by and exchange partition on a routine basis for the sake of clustering around a commonly used key, greatly reducing the number blocks to be visited (queries always include a month range which does the partition pruning, and a cust id, by which we order when doing the CTAS -- tremendous benefit performance wise since any cust id is concentrated in as few blocks as possible). Having that cust id as the leading column of the IOT key can give us the clustering without us having to do it manually as data is added to each partition over time. At least that's what we are hoping ;-) Ok, I swore I would be brief, but decided it would be worth bringing up some of the reasons above for conducting the test in case anyone has done similar things for the same reasons and has things they want to share. Anyway, Friday should be a fun day! Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Increase size of data files and rollback segments
Yechiel, Thanks for pointing out the potential issues with autoextending onto an exact 4 Gb boundary (as I forgot to include them). Reading the bug you referenced (at http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=BUGp_id=1668488) it says: ... Does not reproduce on 8.1.7 Sun Solaris. ... Rediscovery Information: Resize a datafile file to [exactly] 4GB, 8G, 12G, 16G, etc. After resizing a datafile to 4G, alter system checkpoint was failing with ORA-27069 ... This fix is in 8.1.7.4.1 and 9.0.1.4.0 but not in 9.2.0.2.1 I am on 8.1.7.4.5 and so am not affected by this issue - I can't remember the version the original poster was on. Anyone using 817x may also want to check note 120607.1 (http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOTp_id=120607.1) titled Support Status and Alerts for Oracle8i Release 3 (8.1.7.X) for information on this and other issues. This refers you to Note 148894.1 (http://metalink.oracle.com/metalink/plsql/showdoc?db=Notid=148894.1) titled ALERT: Problems with Datafile AUTOEXTEND/RESIZE on Oracle8i on NT/2000 Platforms This contains the information ... A fix is now available in 8.1.7.1.4 Bug.1823173 and will be included in8.1.7.3 BUG.1794199 ... For general Oracle information on 2Gb files see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=62427.1p_database_id=NOT titled 2Gb or not 2Gb - File limits in Oracle. This contains links to other notes with Port specific information. I hope this helps. Regards, Bruce Reardon -Original Message- Sent: Wednesday, 11 December 2002 11:19 PM Hello Jeremiah I did some research on metalink and it says: 1) Do not use datafiles more then 4GB on NT systems. 2) There is a bug on NT, W2K that if you resize datafile (direct command or autoextend) to 4GB boundary, i.e 4,8,12, then there are two conditions: 2.1) No archive log - database crashes but you can start again and resize the datafiles. 2.2) Archive log - restore and regenerate to a point in time prior before the resize. This bug was reported also on 9.2. The work around is to resize to 4.1, 8.1 GB datafiles. Bug number 1668488. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 11, 2002 12:54 PM On Wed, 11 Dec 2002, Yechiel Adar wrote: Do not allow your datafile to autoextend across 4GB boundary. There is a bug that cause this datafile to be unusable. I think the 4Gb limit is confined to a handfull of older operating system versions or older Oracle versions. For about the last five years I have been accustomed to creating 16Gb datafiles with no problem. Imagine trying to build a 5 terabyte data warehouse out of 1900Mb datafiles! It would require 2760 datafiles! Can anyone confirm that this is no longer a problem after certain versions of O/S and Oracle? Note another mean bug (8.1.6.2 / HP-UX 64-bit) where Oracle lets you specify no size for a datafile, then adds it to the controlfile /data dictionary in a way that makes it look like it has a ton (like a terabyte) of free space. The datafile can't be resized or offline dropped, and the tablespace must be dropped and recreated (unless you get the patch). Let one segment extend into there and watch the ORA-600s. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 11, 2002 2:46 AM As a start, look up the following commands in the SQL Reference guide: alter database datafile 'C:\ORADATA\fred\DAT_LARGE_01.dbf' autoextend on maxsize 20480M; Read up on the implications of autoextend and whether you want it alter database datafile 'C:\ORADATA\fred\DAT_LARGE_01.dbf' resize 10240M; For rollback datafile - same as any other datafile, eg: alter database datafile 'C:\ORADATA\fred\ROLLBACK1.DBF' autoextend on maxsize 10240M; You may then need to add another rollback segment or increase the max extents of an existing one. eg to add another rollback segment: CREATE PUBLIC ROLLBACK SEGMENT r09_big TABLESPACE rollback STORAGE ( minextents 20 INITIAL 10M NEXT 10M MAXEXTENTS UNLIMITED) (you would then need to bring this online - eg alter rollback segment r09_big online;) For tempfile (ie LMT temporary tablespaces): alter database tempfile 'C:\ORADATA\fred\TEMP1.dbf' autoextend on maxsize 10240M; Remember - don't use the exact sizes I've shown - alter to suit your case (these were part of a huge load into a new test system) Hope this helps (and willing to learn if some of the above could be improved). Regards, Bruce -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network
RE: Guidelines/Standards for supporting non-oracle databases
Rick, I would consider defining small based on something other than size. eg perhaps some combination of: small means less critical (ie small business loss if data is unavailable or lost forever) small means small number of concurrent users small means don't need extremely quick response times small means small uptime requirements small means small need for features (eg replication / standby / failover etc) Maybe you could consider implementing small databases as schemas within a single Oracle database and use MS Access front-ends via ODBC? Hope this gives some ideas for thought. Cheers, Bruce Reardon -Original Message- Sent: Wednesday, 11 December 2002 8:15 AM Hi, We are virtually an Oracle shop with 2-3 sql server databases due to 3rd-party software restrictions. We have been asked about supporting other small databases such as Access,etc within our company. My question is if you were asked to support smaller databases what restrictions/guidelines/standards are worth considering? Be kind-constructive answers only :-) Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Increase size of data files and rollback segments
David, As a start, look up the following commands in the SQL Reference guide: alter database datafile 'C:\ORADATA\fred\DAT_LARGE_01.dbf' autoextend on maxsize 20480M; Read up on the implications of autoextend and whether you want it alter database datafile 'C:\ORADATA\fred\DAT_LARGE_01.dbf' resize 10240M; For rollback datafile - same as any other datafile, eg: alter database datafile 'C:\ORADATA\fred\ROLLBACK1.DBF' autoextend on maxsize 10240M; You may then need to add another rollback segment or increase the max extents of an existing one. eg to add another rollback segment: CREATE PUBLIC ROLLBACK SEGMENT r09_big TABLESPACE rollback STORAGE ( minextents 20 INITIAL 10M NEXT 10M MAXEXTENTS UNLIMITED) (you would then need to bring this online - eg alter rollback segment r09_big online;) For tempfile (ie LMT temporary tablespaces): alter database tempfile 'C:\ORADATA\fred\TEMP1.dbf' autoextend on maxsize 10240M; Remember - don't use the exact sizes I've shown - alter to suit your case (these were part of a huge load into a new test system) Hope this helps (and willing to learn if some of the above could be improved). Regards, Bruce -Original Message- Sent: Wednesday, 11 December 2002 8:15 AM From: Nguyen, David M [mailto:[EMAIL PROTECTED]] Alright guys, just because you know Jeremiah better than me so you can take his joke. I have no idea who Jeremiah is from Adam, and I got the joke. Hell, I nearly flamed you myself, and I'm just a lowly developer. My apology to him as I did not recognize it was a joke, however when I am asking for help from the group, I am in a situation to resolve problem ASAP and hope to find a answer not a joke. The problem is that if enough people like you refuse to RTFM, then no one will want to answer questions because it'll be duller'n your resume. If you were in my situation, you would understand. I've been in your situation, and been damned ashamed of myself for not having researched the question first. It ain't all that hard... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: moving from dedicated connections to MTS
Or get a firewall that understands the Net8 protocol and open that protocol rather than specific ports. Also, some further Metalink references that should help: On Metalink check out the following note: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOTp_id=132729.1 This is the Technical Library Connection Manager and Firewalls index. eg check out: How to enable USE_SHARED_SOCKET on WINNT and Windows 2000 124140.1 Oracle And Firewalls : Answers To Frequently Asked Questions 2084440.6 Oracle Connectivity with Firewalls 125021.1 Firewalls, Windows NT and Redirections 66382.1 Solving Firewall problems on NT 68652.1 In particular, with a normal connection you come in on the listener port (often 1521 or 1526) but the listener then selects a random port for communication from the server back to the client. So the summary is - you shouldn't need to use MTS just to get Oracle to work with a firewall Regards, Bruce Reardon -Original Message- Sent: Wednesday, 20 November 2002 4:30 AM Doc: 125021.1 talks about firewalls. Look for USE_SHARED_SOCKET to keep the dedicated connections. -Original Message- From: Yechiel Adar [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 19, 2002 11:24 AM Hello all First a little background. We work with oracle 8.1.6.3.4 on NT or win2000 servers. The technical people have just move an application server behind a firewall. The application servers access a database that is a central repository of user connections (i.e. all applications on the intranet access this database for each page for each user). They saw that the application works fine for a while and then they get access denied. They track it down to the port numbers in the firewall. We are working with dedicated connections and it seems that the port numbers for each connections are climbing up until they exceeded the range of open ports in the firewall. They said that they had the same problems in another server, they brought an outside guy (of course without telling the DBA group) and he solved the problem. They brought me the init.ora file of that database (I can not access it via the firewall) and showed me the parameters that made the difference. The guy put in: mts_dispatchers= ... port=8000) (5 dispatchers). Since they want me to do it on a central and essential database I want to ask you guys: 1) Any gotcha moving from dedicated connections to MTS? 2) Is each dispatcher assigned for the current sql command and then released or is it assigned for the duration of the session? 3) What is the ratio of users per dispatcher? 4) Is there a way to tell oracle to reuse port numbers for dedicated connections that were closed? 5) Anything else you care to share. Sorry if my questions are somewhat trivial but we need a decision tomorrow morning (in 18 hours) as they start doing some training session on the system on Sunday and time is short. TIA Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Call for a featured speaker for St. Louis Oracle Users Group...
Does Australia get included in the travel expenses offer? :-) Cheers, Bruce Reardon -Original Message- Sent: Thursday, 14 November 2002 7:06 AM To: Multiple recipients of list ORACLE-L Group... We would, of course, pick up all travel expenses, From how far :-) ? -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: e: when is an error msg not an error msg ?
Paul, I've seen something similar under windows when somebody incorrectly defined an oracle_home environment variable. Might this be happening in you case? HTH, Bruce Reardon -Original Message- Sent: Thursday, 14 November 2002 9:59 AM Sherman, Paul R. wrote: Hello, I've looked over hill and over dale at many web-sites (MetaLink, google, asktom, etc.), to no avail, so could someone on the list bail me out on this question - what causes the following error message: ORACLE ERROR=Error while trying to retrieve text for error ORA-03114 Now, I know what causes the 03114; I need to know why I am getting the 'error while trying to retrieve... I get this error on my test and production servers, but not the development server. All have the same # of files in $ORACLE_HOME/rdbms/mesg. All are Oracle 8.1.7.4. Some file missing somewhere that I know not of ? Any help would be greatly appreciated. Thank you, Paul Sherman DBAElcom, Inc. voice - 781-501-4143 (direct #) fax- 781-278-8341 (secure) email - [EMAIL PROTECTED] Paul, Could there be any difference in the environment ? I am thinking about something such as env | grep -i lang Less likely to occur in the US than elsewhere, but ... Also, in which context are you getting this? Do you get it in an anonymous PL/SQL package, say set serveroutput on begin dbms_output.put_line(sqlerrm(-3114)); end; / ? -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Solaris vs Windows 2000
applications, I suspect you discovered that it can be an excrutiatingly painful experience ... If you even succeeded at all. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Queues - does anyone use them
Hi, I've sent a couple of questions on queues and got no answers - that's fine and I understand we're all busy. What I'm wondering though is whether anyone is actually using Oracle queues at all? Any feedback would be appreciated. For anyone out there who does use Advanced queues: one of our developers read that Creating a queue table in a tablespace will disable that particular tablespace for point-in-time recovery. - Do you normally put your AQ tables in a separate tablespace (we're currently looking at doing just that)? - Who normally owns the queues and queue tables - system or the application schema. Thanks, Bruce Reardon mailto:bruce.reardon;comalco.riotinto.com.au -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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*Plus COPY error solved (sort of)
When you tried under NT, did you use sqlplus.exe or sqlplusw.exe? If you used sqlplusw.exe, this sounds like Bug No. 1504702 The workaround is to use the copy command from the character version of sqlplus (sqlplus.exe) rather than the GUI version. HTH, Bruce Reardon -Original Message- Sent: Saturday, 26 October 2002 10:49 AM it's oraus.msg located in rdbms/mesg but that file does NOT exist on Windows systems anymore --- Stephen Lee [EMAIL PROTECTED] wrote: Some severe rustiness here on this topic; but the mists of time have me thinking that Oracle is looking for the file that contains the text for messages it wants to display, and in your case, it can't find the file. I used to know what file this was and where it was located; but would have to look it up now. -Original Message- Sent: Friday, October 25, 2002 3:24 PM This COPY script failure occurred when running in SQL*Plus on Windows NT. In SQL*Plus on (Unix) database server, runs just fine, so immediate problem solved. Shouldn't it work from PC, though? --- Paul Baumgartel [EMAIL PROTECTED] wrote: I'm trying to use (for the first time in 15 years of Oracle experience!) SQL*Plus COPY, and am receiving the following incomprehensible error: SQL @odss/cmsr Array fetch/bind size is 100. (arraysize is 100) Will commit after every 10 array binds. (copycommit is 10) Maximum long size is 2. (long is 2) ERROR: ORA--3393898: Message -3393898 not found; product=RDBMS; facility=ORA Anyone seen this or anything like it? My environment is set up properly; i.e., errors returned by operations other than COPY display the way they're supposed to. TIA! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: PROCESS column in V$SESSION
Charlie, I think Tom explained the machine column. For process column it is: = client PID eg start sqlplusw on your PC, connect to a DB and run query then start Taskmanager on your PC - = PID of sqlplusw.exe Not sure what (it's not the thread ID on the server - that is v$process.spid Run orakill with no parameters to see the join syntax for v$process and v$session Regards, Bruce Reardon -Original Message- Sent: Saturday, 12 October 2002 5:03 AM Charlie, = NT Domain = Machine Name Hope this helps. PS - do I get a Home Depot Discount Card for this? :) Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, October 11, 2002 11:59 AM To: Multiple recipients of list ORACLE-L For folks connecting to the DB server from another Unix box the PROCESS field is the Process ID on the host named in the MACHINE field. For folks connecting to the DB server from a PC the values are in the form of : So what do signify? WENDYC wendyc 1112:1116 HDSWIN\CSCSOPC034 WENDYC wendyc 1172:1140 HDSWIN\CSCSOPC034 MWH williamd22968 pan MWH williamd26653 pan OPS$WILLIAMD williamd26974 titan OPS$WKLINE wkline 22717 titan OPS$WSPENCER wspencer24664 titan OPS$WSPENCER wspencer4824 titan YSULLIVA ysulliva728:300 HDSWIN\CSCCSPC105 YSULLIVA ysulliva1104:1076 HDSWIN\CSCCSPC105 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: A really stupid question
Jay, From my personal archive, I have previously found the following 4 archive sites. It depends on what you want - that is, in sent order, threaded or searchable. http://www.orafaq.com/archive/oracle-l - not threaded and doesn't seem to be searchable but does display them in sorted order I also found this site by searching Google for Oracle-L http://faqchest.dynhost.com/prgm/oracle-l/ try http://www.fatcity.com/ListGuru/my.php and http://www.mail-archive.com/oracle-l%40fatcity.com/-- this has them threaded HTH, Bruce Reardon -Original Message- Sent: Saturday, 5 October 2002 3:04 AM How can I access the Oracle-L archives? There's something I remember reading a few months ago that I want to look up. Embarassedly yours, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 and Disk Space
RMAN doesn't backup the entire database , but it does backup space that has EVER been used (as opposed to space that is currently used). that is, perhaps at one stage more than 9Gb of space was in use and then records were deleted. One way around this is to resize the datafile (this can only be done if the free space is both continuous and at the start of the datafile. Have a look at Metalink Note:105208.1. HTH, Bruce Reardon -Original Message- Sent: Saturday, 5 October 2002 8:09 AM Since everyone's advocating RMAN here, I decided to start an RMAN backup for one of our development databases. And I had issues with disk space. Now, one of the tablespaces has been allocated 15 Gb of space, of which only 2.2 Gb has been used as per dba_extents. I started an RMAN backup directing this tablespace backup to be written to a file system with 9 Gb of free space. This backup filled up the file system and errored out. So, my question is, the first time you take a backup, does RMAN backup the entire datafile? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: auto start db???
Leslie, In addition to oradim.log and alert.log (as mentioned by others) check the event log for anything. Also, was this machine upgraded to W2K from NT4? If so, I remember a bug with autostart under this case - can't remember more details but check Metalink. HTH, Bruce Reardon -Original Message- Sent: Friday, 4 October 2002 8:41 AM Thank you for the feedback! ORA_SID_AUTOSTART is already set to true in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\... Any other suggestion? --- Igor Neyman [EMAIL PROTECTED] wrote: Under Windows if you want to startup database automatically (along with OracleService), you should set ORA_SID_AUTOSTART to TRUE in Windows registry under your Oracle_home key (HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\...) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - Sent: Thursday, October 03, 2002 5:38 PM Hi, I have a 817 db on Win2000. The service is configured to be started automatically. But the database is not start up automatically. Everyday I need to manually issue Startup command. Also my collegue has 2 db (one 817, one 9i) on Win2000. The 817 db was be able to startup autolly, but ever since he installed 9i, none of the db starts autolly, even though both services are configured to be auto start. So how do I let the db start automatically? Thank you. Leslie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Advanced Queues for dummies
Hi, We're investigating the use of Advanced Queues as a way of transferring information between OSI's PI and Oracle. The data will originate on the Oracle side and end up in PI. We were thinking of using Oracle's Heterogeneous services and PI's ODBC driver, but ran into 2 phase commit errors (how do you tell a commit not be a 2-phase commit) and problems (ie not supported until 9iR2) with autonomous transactions and database links. We're using Oracle 81714 (soon to be 81745) on Windows. Having not used Advanced Queues before, I will look at the manuals, but is their a site or book on Advanced Queues for dummies (otherwise known as Intro to Advanced Queues in Oracle). For those who use Advanced queues, one of our developers read that Creating a queue table in a tablespace will disable that particular tablespace for point-in-time recovery. -Do you normally put your AQ tables in a separate tablespace? - Who normally owns the queues and queue tables - system or the application schema. Any suggestions, links or books would be appreciated. Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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-04031
Also check out Metalink note 146599.1 which is on diagnosing ora-4031 errors. This has helped me. Also you can consider adding an event in your init.ora file like 4031 trace name errorstack level 3 This will dump an errorstack when the 4031 error occurs - this can be interpreted by Oracle support. However, realise those files will take up room and that when the error occurs the user will experience a delay whilst the dump file is written. HTH, Bruce Reardon -Original Message- X$KSMLRU is the free list chunks. On Thu, 2002-09-12 at 17:03, Jesse, Rich wrote: As one who has been there, I can sympathize. I had a TAR open for months going back and forth with Oracle Support. The entire problem boiled down to the fact that Oracle Corporation does not have a supported method (supported is the key word) to query the free chunks of the shared pool to determine the amount of fragmentation. Fragmentation of the shared pool was the most likely cause of our ORA-4031 errors, but because Oracle Support said it couldn't be measured, I wasn't able to measure the effect of my changes to the shared pool size and the pinning. Their answer was bump up the shared pool, bounce the instance, repeat until it stopped. So, completely frustrated, I ended up on Steve Adams' site at http://www.ixora.com.au There are some great QA sections there about dealing with ORA-4031s. I also purchased Steve's book, Oracle8i Internal Services (http://www.oreilly.com/catalog/orinternals/) from http://www.bookpool.com The book complements the website by going in-depth into some of the X$ views to get info on the shared pool and other structures. I am by no means an expert on the shared pool, but we were able to solve our ORA-4031s with help from these resources. GL! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 2:18 PM its 8.1.7.4 From: Mark J. Bobak [EMAIL PROTECTED] Date: Wed, 11 Sep 2002 21:08:18 -0800 Um, version? (To at least 4 places, please!) On the off change you are on 8.1.7.x, where x 3, please upgrade to 8.1.7.4. There were several bugs related to ORA-4031 from 8.1.7.0 through at least 8.1.7.2. -Mark On Wed, 2002-09-11 at 18:14, Seema Singh wrote: Hi One of instance are showing ORA-04031 error more frequntly(2 times in a week).I increased SGA ,pinned some of DBMS packages,flush SGA etc but all these are not helping at all.Let me know what could be look next. Thx -Seema -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Rollback !
And you may well use Logminer to determine what that exact point in time is (and then use tablespace point in time recovery to do the actual recovery). Bruce Reardon -Original Message- Sent: Thursday, 29 August 2002 2:52 There is a way of undoing DDL and it is described in the RMAN manual. The solution is simple: recover the database to the point in time before the problematic DDL. -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 10:49 AM Alexandre - Thanks for posting the link. I read the document. It describes how to track the DDL statements. Unless I overlooked something, it doesn't say you can UNDO DDL. I think the reason was explained earlier on this post, that Oracle doesn't write any redo records. For example, in the case of a DROP TABLE statement, in order to undo that statement, Oracle would have to write the equivalent of a DELETE statement for each row. This could be enormous if the table was large, and take a long time. Then we DBAs would be clamoring for a parameter that would bypass all that. I could be wrong, which is why I respond to issues like this. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 28, 2002 6:13 AM For 9i: http://otn.oracle.com/docs/products/oracle9i/doc_library/901_d oc/server.901/ a90117/logminer.htm#18681 For 8i not sure. Has anyone tried DROP undo in 8i? (8i docs are very poor on LogMiner) Alexandre - Original Message - Sent: Wednesday, August 28, 2002 11:48 AM Alexandre Gorbatchev, As far as i know, you cannot recover from drop using logmnr.It is internal dml to data dictionary, maybe in 9i it will work, i am not sure.But in 8i, it won't work. Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] === 2002-08-28 00:13:00 ,you wrote£º=== 1.) Can we rollback a drop statement ? No. However, you can use Log Miner. Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Notification of failed backup
Eric, How are you doing your backup - OS cold, OS hot, RMAN or other? How are you scheduling it? For information - If you use OEM this can be achieved automatically by setting it to email on failed jobs. Otherwise, some options are: at the end of export command check %errorlevel% - if not 0 then you have an error This may well work after RMAN or ocopy but I haven't checked to confirm that. Alternatively, search the log files (via find or whatever) for strings such as ora-, exp- etc Checking errorlevel will show you if it was found. Then you need to send an email - 1 freeware cmd line approach is to use blat http://www.blat.net In the past others have mentioned Automailer (http://www.duodata.de/automailer/) and I'm sure there are others. I'm sure it also could be done using Perl, Cygwin, VBScript or any other scripting tool as well. If you have some more specific questions then get back to the list. Regards, Bruce Reardon -Original Message- Sent: Thursday, 29 August 2002 8:09 Does anyone have a script (or some method) that works on NT to send an email to someone if a backup failed? There are two types of backups currently running including an export and hot backup. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Upgrade to 8.1.7.4?
What OS? eg if W2K I have had to stop the Distributed Transaction Coordinator Service before the patch could be applied (otherwise files were locked). (This applies to any Oracle patch for me) Also, if Windows note that there is an 81745 patch. Have you looked at Metalink http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOTp_id=120607.1 to see the major known issues with 817 releases? eg ORA-1002 possible after ROLLBACK TO SAVEPOINT Note:202261.1 Also, you can search Metalink for a list of bugs in 8174 (search for (say) Server-Enterprise Edition with a version of 8.1.7.4) Have a look at the patches available for 8174 on your platform - this will give an indication of the critical bugs that are others have found and got patched. HTH, Bruce Reardon -Original Message- Sent: Tuesday, 27 August 2002 16:38 To: Multiple recipients of list ORACLE-L Hi there. Has anyone picked up any problems while installing the 8.1.7.4 patch? I am going from 8.1.7.0 to 8.1.7.4 and would like to know if tehre are any known problems? Thanks Clint * Clinton S. Naudé * Head DBA Services * Tel: 011 685 4304 * Fax: 011 685 4303 * Cell: 082 377 1726 * E-mail: [EMAIL PROTECTED] Confidentiality Warning === The contents of this message and any attachments are intended solely for the addressee's use and may be legally privileged and/or confidential. If you are not the addressee indicated in this message, any retention, distribution, copying or use of this message is strictly prohibited. If you received this message in error, kindly notify the sender immediately by reply e-mail and then destroy the message and any copies thereof. The content and any views expressed therein are, unless otherwise stated, the views of the author and not those of the company or any of its management or directors. Whilst all reasonable steps are taken to ensure the accuracy and integrity of information transmitted, the company does not accept responsibility for any corruption of the information or data or breach of confidentiality as a result of electronic submission. When addressed to the Momentum Employee Benefits clients any opinion or advice contained in this e-mail is subject to the terms and conditions expressed in any applicable terms of business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Clinton Naude 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: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 81745 upgrade and PL/SQL version
Patrice, You are right - I didn't put enough info in my email. My database was at 8.1.7.2.5 I then applied 8.1.7.4.1 via the installer I then copied the files that comprise 8.1.7.4.5 to my Oracle home I then ran catalog, catproc and the other stuff from the 8.1.7.4.1 release notes. At this stage I queried product_component_version and the PL/SQL version was shown as 8.1.7.3. It seems no-one else has seen this. I'll repeat the upgrade on 2 other databases (with and without JVM installed) and see if I can learn more. Thanks, Bruce -Original Message- Sent: Tuesday, 27 August 2002 22:19 Bruce, I think the minor releases have to be applied to the first one in the series, i.e. 8.1.7.4.5 would go on top of 8.1.7.4.1.. To go from 8.1.7.2.0, follow this path: 8.1.7.2.0 -- 8.1.7.4.1. -- 8.1.7.4.5. It may be though that 8.1.7.4.1 can only go on top of 8.1.7.3, which would add at least another step. The first in the series usually uses the Oracle Installer; the later (minor) ones ask us to archive files and replace them manually. I haven't applied 8.1.7.4.5, maybe that one is different. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 27, 2002 1:03 AM Hi, Yesterday I upgraded a database from 81725 to 81745 on W2K SP3 (the DB doesn't have JVM in it). Before the upgrade, product_component_version showed the PL/SQL version as version 8.1.7.2.0. After the upgrade, the PL/SQL version is shown as 8.1.7.3. The product_component_version table in total shows: Product Version --- - NLSRTL 3.4.1.0.0 Oracle8i Enterprise Edition 8.1.7.4.1 PL/SQL 8.1.7.3.0 TNS for 32-bit Windows: 8.1.7.4.0 So the other products are showing as 8174 but not PLSQL. Has any one else seen this - I couldn't find any reference to it on Metalink? We have another database that has been upgraded to 81741 (this one is on NT4 SP6a). This one shows PL/SQL 8.1.7.4.0 in product_component_version. This one does have JVM installed so I don't know if the difference is related to that. Any suggestions are welcome. I don't know at this stage if this is a problem, but it is an (as yet) unexplained difference and I don't like those sort of differences. Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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).
Oracle 81745 upgrade and PL/SQL version
Hi, Yesterday I upgraded a database from 81725 to 81745 on W2K SP3 (the DB doesn't have JVM in it). Before the upgrade, product_component_version showed the PL/SQL version as version 8.1.7.2.0. After the upgrade, the PL/SQL version is shown as 8.1.7.3. The product_component_version table in total shows: Product Version --- - NLSRTL 3.4.1.0.0 Oracle8i Enterprise Edition 8.1.7.4.1 PL/SQL 8.1.7.3.0 TNS for 32-bit Windows: 8.1.7.4.0 So the other products are showing as 8174 but not PLSQL. Has any one else seen this - I couldn't find any reference to it on Metalink? We have another database that has been upgraded to 81741 (this one is on NT4 SP6a). This one shows PL/SQL 8.1.7.4.0 in product_component_version. This one does have JVM installed so I don't know if the difference is related to that. Any suggestions are welcome. I don't know at this stage if this is a problem, but it is an (as yet) unexplained difference and I don't like those sort of differences. Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Win2000/8.1.7.3.0/SQL
or is that 1 db set to first_rows? If so try the query with a rule hint? Bruce Reardon -Original Message- Sent: Tuesday, 20 August 2002 8:49 Mike, ...and it works great everywhere. Well, almost everywhere, and there's the rub. Thie query works on my 7.3, 8.0, 8i, 8iR2, and most 8iR3 databases. In this one database, on Windows 2000, it blows out the TEMP tablespace. Every time. And this is the only system where that happens. There are only 12 tablespaces, and 15 datafiles. Any ideas why this would fill up the TEMP space? Does that particular Db have it's SYS schema ANALYZED? This should return no rows - if it does, then you can drop the stats for SYS and run this query again. select table_name from dba_tables where last_analyzed is not null and owner = 'SYS'; The problem could be that partial/complete ANALYZE of the SYS objects is resulting in Queries-from-Hell. Queries against the DD are written for Rule and the stats forces the CBO to be used instead.. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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 doesn't show in OEM ???
Are they present in the services.ora (in ?\network\agent) and snmp_ro / snmp_rw files (in your tns_admin dir)? I would guess that only 1 DB is in those files. Did you create the 2nd database after the agent was already running? Consider a clean start of the agent service - see Metalink Note:71913.1. Are both databases listed in your listener.ora or is 1 using automatic registration? HTH, Bruce Reardon -Original Message- Sent: Friday, 16 August 2002 8:09 To: Multiple recipients of list ORACLE-L Are the databases listed in the tnsnames.ora file? Bryan -Original Message- Sent: Thursday, August 15, 2002 5:24 PM To: Multiple recipients of list ORACLE-L Hi, I have two local databases on my machine. In OEM, after I find node, one db show but the other doesn't. How does that happen? OEM(2.2.0.0.0), Oracle 817 on Win2000. Thank you. Janet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Dba tools on NT
for these functions and, if you be so kind, why you choose them. TIA Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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 recovery stuck
This reminded me of a Metalink note I once found.
Dennis - you might want to look at Note:145624.1 (RMAN: Resolving an RMAN Hung Job)
for some more hints information.
Tim - the note mentions the debug command line parameter but doesn't show the
trace=1 phrase so its good to learn that.
Dennis - Is this the same hanging issue you had a month or so ago?
Regards,
Bruce Reardon
-Original Message-
Sent: Thursday, 15 August 2002 13:23
Call RMAN from command-line as follows:
rman nocatalog log=logfilename debug trace=tracefilename
Both the logfilename and tracefilename should have copious amounts of
output, which can provide a clue.
When you allocate the channel, make sure to add the phrase trace=1 to the
end of the ALLOCATE command. This will produce .trc files in your
USER_DUMP_DEST directory with additional diagnostic output...
Hope this helps...
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
I am trying to perform an RMAN disaster recovery task. While I use an RMAN
catalog to make backups, I am trying to recover using just the control
file
information.
Oracle 8.1.6, Compaq/HP Tru64
I start RMAN with
rman target sys/password nocatalog
then,
startup mount
run {
set until time to_date('08/11/2002 01:00:00','MM/DD/ HH24:MI:SS');
allocate channel d1 type disk;
restore database;
recover database;
alter database open resetlogs;
}
Everything appears normal for awhile. In the alert log RMAN tries to find
each file, doesn't find them. Then it successfully recovers 5 data files
(including system and rollback) and reports success in the alert log. Then
.
. nothing for hours. RMAN doesn't return an error. The RMAN shadow
processes are still present but with no CPU consumption. Nothing is
written
to the alert log.
I check V$SESSION_WAIT, and the only entry for the RMAN shadow
processes is one is SQL*Net message to client with seconds_in_wait = 0,
state = waited unknown time.
In V$SYSTEM_EVENT, time_waited and average_wait are zero for all
events. The following events have values of total_waits that are
increasing:
Increase in total_waits in 10-minutes
rdbms ipc message 401
pmon timer 57
control file parallel write 56
SQL*Net message to client24
SQL*Net message from client 24
virtual circuit status5
dispatch timer3
smon timer1
Archiving is turned off.
I have attempted this recovery many times using different RMAN backup
sets,
but the system always hangs at this point.
Any ideas would be appreciated.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reardon, Bruce (CALBBAY)
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: List archives on line?
Try the following: http://www.fatcity.com/ListGuru/my.php http://www.mail-archive.com/oracle-l%40fatcity.com/-- this has them threaded http://faqchest.dynhost.com/prgm/oracle-l/ Jared - maybe we could get these 3 added to the mail signatures or added to a section at orafaq? Regards, Bruce Reardon -Original Message- Sent: Friday, 2 August 2002 6:51 Hi, Are there any list archives online ? Alot of some interesting threads are coming throught to me blank (probably my server is blocking content) but I still want to read them. Please let me know. Thanks, Hannah -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: SOLVED: IFILE in node-specific init file in VMS
Mark, I'm glad you got it solved. Maybe it is platform specific? We don't have OPS but do use ifile notation We have lots of blank lines in addition to a number of comment lines beginning with # and we haven't had any problems, that I know of :-). We're running 81714 under NT4 SP6a. Did support give a reference to a Metalink note describing issues with blank lines in init files? Thanks, Bruce Reardon -Original Message- Sent: Friday, 2 August 2002 10:33 Blank lines in your init.ora are a very bad thing. Oracle support and Metalink get beat up quite often on this list but I must say they solved this problem very quickly. Thanks to everyone who replied. Mark -Original Message- Sent: Thursday, August 01, 2002 9:38 AM Is the logical defined as /system/exec? If so and doesn't work, I would open a tar b/c it must be something in your env. I am running Oracle 8.1.7.3 OpenVMS 7.2 and have no problem. I do put the ifile stmt 1st line in initSID.ora file so I can easily override any parameter. Gene [EMAIL PROTECTED] 07/31/02 06:30PM Hi Gene, I've tried using both a logical (ora_system) and the full path. It doesn't work either way. Tom - I'm tempted but I would like the simplicity of maintaining a single init.ora. Mark -Original Message- Sent: Wednesday, July 31, 2002 3:34 PM I use ifile w/out a problem in OpenVMS. Do you reference it w/ full path or logical? I don't like using init...ora and config...ora. I use an instance specific initSID.ora that ifile's a common init.ora file into each db. Its nice to put a change in 1 file and its applied to all dbs. Gene [EMAIL PROTECTED] 07/31/02 05:04PM Mark, throw away the IFILE thingy. it is not worth the trouble! create instance specific init.ora files. solves your problem. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, July 31, 2002 4:49 PM Greetings elitist *nix bigots, I'm starting to upgrade our OPS databases on OpenVMS from 7.3 to 8.1.7.3 and I've run into a problem with the IFILE parameter in the node specific init file. It appears Oracle is not reading the generic init.ora specified in the IFILE parameter in the node specific init file. Only parameters defined in the node specific init file are effective. Has anyone been there, done that? Thanks, Mark Stahlke Elitist VMS Bigot Denver Newspaper Agency -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Heterogenous Services
And for technical info on setup of Heterogeneous services try the following: I found note 114820.1 helpful - QUICK START GUIDE: WIN NT - Generic Connectivity using ODBC Also note 109730.1 - How to setup generic connectivity (Heterogeneous Services) for Windows NT Also note-107227.1 setup and troubleshoot gencon on UNIX And have a look at http://technet.oracle.com/products/gateways Regards, Bruce Reardon -Original Message- Sent: Thursday, 1 August 2002 9:49 I think I am jumping into this thread a bit late. Take a look at http://www.unixodbc.com for some good info on ODBC on Unix. I am not sure where to get Oracle ODC drivers for Unix. Try installing a Unix client and see if it comes along with it... Babu Robertson Lee - lerobe [EMAIL PROTECTED]@fatcity.com on 07/31/2002 11:53:36 AM Mladen, I have been told that I definitely need the odbc driver installed on my UNIX server. How do I get these from OTN, they have the odbc software for Windows but not for UNIX. (Not that I can see anyway !!) Lee -Original Message- Sent: 31 July 2002 17:29 If it's just a regular ODBC connection, download the driver from OTN, configure it appropriately and enjoy. No heterogeneous services needed. Heterogeneous services are needed only if you have more then one protocol on your network (like LU 6.2, TCP/IP and IPX). -Original Message- From: Robertson Lee - lerobe [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 31, 2002 11:13 AM John, I have done this from PC to Oracle Database on a UNIX server but not the other way around. I think it is different and that is what I am trying to get to the bottom of Lee -Original Message- Sent: 31 July 2002 15:14 To: Multiple recipients of list ORACLE-L And is it needed?. I am sure I have set up odbc connections between Oracle 8i databases on Unix and Access/Excel and I have never heard of Heterogeneous Services before -Original Message- Sent: 31 July 2002 13:24 To: Multiple recipients of list ORACLE-L I did a bit of research on Heterogeneous Services, apparently it lets you create ODBC connections between Oracle on UNIX and Windows apps. Can this be true? Does it actually work? What is the performance like? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) . -Original Message- Sent: Wednesday, July 31, 2002 6:48 AM To: Multiple recipients of list ORACLE-L Hi, Oracle 8.1.7.3 Tru64 5.1 MSAccess 2000 Preparing to be shot down in flames but I have been looking through some documentation and also some White Papers but for some reason I just don't get it. I need to see Access tables from an Oracle DB. The paper I am reading (from Metalink) states that I should be able to do this via Heterogeneous Services and ODBC agent but I cannot see how it all hangs together. Has anyone done this and if so could you point me in the right direction please. I don't need an idiots guide (or maybe I do) but a gentle nudge in the right direction should suffice. Regards Lee -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Recompiling blocked package - locating blocking session
Jay, Don't know for sure but you could try Ixora's script to show executing packages available at http://www.ixora.com.au/scripts/misc.htm You could also look for blocked internal locks - try the following: select * from dba_lock_internal where ( mode_held = 'Null' OR mode_held = 'None' ) AND ( mode_requested 'None' ) ; dba_lock_internal is created by catblock.sql but blocks that are in there do not show up in dba_blockers. From catblock.sql for dba_lock_internal * NOTE: This view can be very, very slow depending on the size of your * shared pool area and database activity. We haven't found this an issue on (low concurrent load) database. HTH, Bruce Reardon -Original Message- Sent: Wednesday, 24 July 2002 1:24 Had a problem this morning where a package was invalid and it would hang when we tried to recompile. We assumed that some other session was trying to recompile it but was hanging for some reason. I have plenty of ways to look at table locks but don't have a query to show locked packages. Frantic searching through the index of the SQL PL/SQL Annotated Archives didn't help. I ended up searching the Active sessions that looked likely and killing them (and needing to do a kill -9 on the OS level as well) until I was able to recompile the package. Does anyone have a query that will save the random searching next time? Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: v$sort_usage
Do you use global temporary tables? (select owner,table_name from dba_tables where temporary='Y';) If so the segments shown could be associated with that. HTH, Bruce Reardon -Original Message- Sent: Wednesday, 17 July 2002 2:00 Hi, Could someone shed some light on how v$sort_usage is populated? It is supposed to show active sorts in the database. If that is the case then I don't understand why I see the following: 1. An entry exists for a user/sort, yet according to v$session the session is NOT active. 2. Multiple entries exist for a user with the SAME session_addr. I don't understand how a session could have multiple sort segments. As always, you're feedback is appreciated. Thanks. -w -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: OEM Intelligent Agent and port numbers - How can I assign/configure a port
Cherie, I summarised the ports used by Oracle for our Network support specialist and have copied it below: Metalink note 69511.1 refers to SNMP and the Oracle agent - hopefully that document or some of the others listed can help you out. Oracle TCP IP Ports - for future reference Oracle uses a number of fixed TCP ports, these include (Metalink note 99721.1 has a more complete list if the ports that Oracle listens on) 1521 / 1526 - used by the Oracle listener as the initial point a client connects to 2481 - the recommended and officially registered listening port for client connections to the Java option using TCP/IP. 2482 - the recommended and officially registered listening port for client connections to the Java option using TCP/IP with SSL. Client Connections xyz - ie arbitrary port In a standard Oracle configuration , when a client connects to the listener, the listener spawns a dedicated connection thread for that client on a dedicated TCP port. From Metalink note 125021.1, the server port that is assigned to the client is randomly chosen by the operating system and can't be modified. It can be any free port available that the server determines is not is use by any other software or hardware. From note 66382.1, for the port on the client PC, what happens is that the networking software on the client chooses at random, or in sequential order, a valid port (between 1024 and 65535) so the client can send and receive data. (Metalink notes such as 125021.1, 124140.1, 2084440.6 + 66382.1 describe use of Oracle with Firewalls where one option is to force all clients to share the same singular TCP port - other possibilities include using a Firewall that supports SQLNet / Net8 or using Oracle's connection manager program). The following are used for SNMP (Metalink note 69511.1) 161 - the Oracle SNMP Master agent needs to work as the SNMP master agent so it works on default port of 161 and SNMP itself gets moved to another port 8161 - the port we have chosen to move Windows SNMP service to Oracle Enterprise Manager (OEM) ports (from Metalink note 94394.1) 1748 / 1754 - Used under full control by the Oracle Intelligent agent (process that schedules jobs and monitoring events) These ports are used during the discovery process of Oracle services - 1748 is used for basic communication and 1754 is used for file transfer 7771 - The OEM client tools use this to talk to the Oracle Management Service (OMS) processes. 7772 - The Oracle intelligent agent (IA) uses this to talk to the OMS 7773 - used as a SSL channel to transfer information from the Intelligent Agent to Enterprise Manager framework 1808 - Tools which communicate with the Oracle Data Gatherer use this port for basic communication 1809 - is also used for SSL communication between the client application and the Data Gatherer. xyz - ie arbitrary port based on availability -Communication from the Data Gatherer back to the Capacity planner is done via a callback mechanism. Capacity Planner will initiate a communication via 1808, and the Data Gatherer will initiate the response back via an arbitrary port, based on availability. HTH, Bruce Reardon -Original Message- Sent: Tuesday, 16 July 2002 2:49 We have a third-party application that needs to use the SNMP agent associated with OEM. They want to be able to get to it via a particular port. We have a port assigned to each database, based on which Oracle version it is running. However, as far as I know, there is not a particular port number associated with the OEM agent. The only way you can designate a particular port number is by going through a particular database. Is this correct. I looked through the 9.0.1 Installing the Instelligent Agent manual and I don't see any place where it states how you can associate the OEM agent with a specific port number on UNIX. Does anyone know how I can do this or am I limited to just associating port numbers with databases. Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: DOS Script for pop up question ?
. @echo off 002. echo. 003. call rdstring ZQusername Please enter your name: 004. call rdstring ZQuserphone Phone: 005. call rdstring ZQaddress1 Street Address: 006. call rdstring ZQcity City: 007. call rdstring ZQstate State: 008. call rdstring ZQZipZIP Code: 009. set ZQ 010. goto :EOF 011. :EOF ==end file C:\cmd\TEST\ZZZINPUT.CMD == ==begin file C:\cmd\TEST\RDSTRING.CMD == 001. @echo off 002. :: 003. :: based on method originally provided by Walter Zachary 004. :: and modified by Tom Lavedas 005. :: 006. :: syntax: call rdstring (varname) your prompt in double quotes 007. :: 008. setlocal 009. set target=%1 010. set myprompt=%2 011. set myprompt=%myprompt:=% 012. echo. 013. echo %myprompt% 014. for /f tokens=* %%a in ( 015. 'format/f:160 a: ^|find ...') do set input=%%a 016. set input=%input:~30% 017. endlocalset %target%=%input%goto :EOF 018. :EOF 019. ==end file C:\cmd\TEST\RDSTRING.CMD == eg 1 convoluted freeware option is to use ask from http://www.kik-it.com to put the response into a text file and then parse that with for command. HTH, Bruce Reardon -Original Message- Sent: Friday, 12 July 2002 7:34 You could use utility choice.exe (I think it comes with NT Resource Kit). Here is an example of the batch file: echo off choice /c:YN Do you want to Continue ? if errorlevel 2 goto end rem do whatever you need here ... .. :end echo End of processing. Obviously N answer returns errorlevel 2. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, July 11, 2002 4:37 PM All, I have a DOS batch file. In between this script, I would like to add user interactive question. Ex: Do you want to Continue [Y/N]? Once they hit Y, it will continue rest of the batch file. Could someone able to help me out as per the above requirement? Thanks, Bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: How to?
David,
Have a look at the session_info column in v$logmnr_contents
This includes machine, terminal, osuser + OS program
Whether this will tell you what user logs in from which computer will depend-
eg do your users share usernames, do they have generic OS logins, do you have an app
server, and so on
HTH,
Bruce Reardon
-Original Message-
Sent: Friday, 12 July 2002 9:58
Ramon,
I know how to setup LogMiner to run. My question is if LogMiner can tell me
where an user is logging in from what computer?
Thanks,
David
-Original Message-
Sent: Thursday, July 11, 2002 4:15 PM
Nguyen,
1) Be sure to have set the parameter UTL_FILE_DIR='d:\directorio'
2) Create the dictonary file
Execute the package DBMS_LOGMNR_D.BUILD('dictionary', 'd:\directorio');
3) Reset the list and create a new one
Execute the package DBMS_LOGMNR.ADD_LOGFILE('redo01.log',
dbms_logmnr.new);
4) Add more redos to analyze
Execute the package DBMS_LOGMNR.ADD_LOGFILE('redo01.log',
dbms_logmnr.addfile);
5) Perform the analysis
Execute
DBMS_LOGMNR.START_LOGMNR(dictionary='d:\directorio\dictionary');
6) Do a select from the V$LOGMNR_CONTENTS
HTH
Ramon
- Original Message -
Sent: Thursday, July 11, 2002 12:48 PM
Using LogMiner to read redo log, how do I tell a transaction is modified
by
which user and where user logins from like what columns in LogMiner should
tell me this kind of information?
Thanks,
David
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reardon, Bruce (CALBBAY)
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: dblink problem ( ORA-02019 )
Check the sqlnet.ora on both boxes - are they the same? There was a bug in 1 version where the domain would get selected from the default_domain present in sqlnet.ora HTH, Bruce Reardon -Original Message- Sent: Thursday, 11 July 2002 16:13 Hi many thanks to all who replied to my query. I was able to solve the problem by changing the dblink create stmt. It seems the user had created the links as CREATE PUBLIC DATABASE LINK abc.WORLD CONNECT TO username IDENTIFIED BY pwd USING 'dbname.world' ; I recreated the dblinks as CREATE PUBLIC DATABASE LINK abc CONNECT TO username IDENTIFIED BY pwd USING 'dbname.world' ; However , Kevin's reply has resulted in me finding another problem. When i did a select * from global_name on db A , i get dbname.world . but when i do the same on db B , i get only dbname ??? i crosschecked all init.ora , sqlnet tnsnames params on both A B ,and i did not find any differences. db_domain for both A B is world, global_names is FALSE. db_name, instance_name service_names are consistent across both A B. According to oradocs , global_name is a view for the init.ora param GLOBAL_NAME. Why is this diff coming and how do i resolve it. thanks again ratnesh -Original Message- Sent: Wednesday, July 10, 2002 10:23 PM I had this problem in the past at a site where we were switching from .world to named domains. I can't remember exactly where I found it but I remember that the method Oracle used to determining the dblink domain was funky. On both databases, check select * from global_name and compare the result to select * from dba_db_links. It seems to me that the domain extension on the global_name was used as the default extension when creating links. Caused me no end of heartburn -- nothing I couldn't work around, but more work arounds than I was happy with. Hope my memory is sufficient since I don't have the problem at my current site. Kevin Kennedy First Point Energy Corporation -Original Message- Sent: Wednesday, July 10, 2002 5:53 AM hi i have 3 databases(A,B,X) on 3 diff boxes. i have created 1 dblink each from A to X from B to X. The syntax for dblink creation is exactly same for both dblinks. I am able to query from dblink A-X as select * from user.table@dblinkAX but when i try to query from dblink B-X as select * from user.table@dblinkBX i get the foll error Error: ORA-02019: connection description for remote database not found When i modify my query by suffixing '.world' as select * from [EMAIL PROTECTED] , the query works fine. I have compared the entries in init,sqlnet,tnsnmames,listener files on both A B databases and they are absolutely similar . i was thinking that the domain or globalnames parameters might be diff on A B , but they are absolutely same . any suggestions are most welcome ... thanks in advance ratnesh --- Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Redo log
David, Based on the ls output it seems you may have 4 groups with 1 member in groups 1,2 4 but 3 members in group 3. Do a select * from v$log; and see how the logs are really setup. I would suggest you want to have the same number of members in each group. If you want them to switch less often either (a) produce less redo (use logminer to analyse and maybe updates / deletes etc are happening that don't need to) or (b) use bigger logfiles. This assumes they're switching because they're full. How big are the archive logs that are produced - are they all around the same size or are some very small? As for LogMiner - if you're in archive log mode and you've backed up / got a copy of the archive logs from 2 days ago then yes LogMiner will be able to analyse them. Regards, Bruce Reardon -Original Message- Sent: Wednesday, 3 July 2002 2:43 I found couple redo logs in my Oracle8.0.5 database, as shown they are switched every 5 minutes. I'd like to have only two or three logs out there and don't want them to be switched until every 4 hours. Is there a way to control it that way? Secondly, if redo log switchs every 5 minutes, does LogMiner have capability to view transaction history from two days ago? Please advise. $ ls -l redo* -rw-r- 1 oracle dba 512512 Jul 2 11:20 redo01.log -rw-r- 1 oracle dba 512512 Jul 2 11:20 redo02.log -rw-r- 1 oracle dba 512512 Jul 2 11:15 redo03.log -rw-r- 1 oracle dba 512512 Jul 2 11:20 redo03_1.log -rw-r- 1 oracle dba 512512 Jul 2 11:20 redo03_2.log -rw-r- 1 oracle dba 512512 Jul 2 11:15 redo04.log Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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 restore hung?
Dennis, What does v$session_wait say for the RMAN sessions (on each database - if using a catalog) Can you connect to the catalog yourself? Can you connnect nomoun to the target? I'm sure you've looked but anything in cdump, bdump, udump dir's? Has your OS process been suspended (not knowing if that can be done on Tru64)? Bruce Reardon -Original Message- Sent: Thursday, 27 June 2002 2:39 I am performing a test restore with RMAN on a test system, and for the past hour it appears to be hung on the restore. The only detectable activity is the control file seems to be getting updated. It restored several files, but is stuck on one. Has anyone hit anything like this? Anything to check for? Obviously this wouldn't be a good scenario in a real recovery. Oracle 8.1.6 Compaq Tru64 Restoring a 4-gig. database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: V9.2 SGA
Also, you probably thought of this, but have you looked at v$sga (which seems to be the info you get on startup) and v$sgastat - which has details of each of the sections. On 81714 on NT, v$sga shows: Fixed Size Variable Size Database Buffers Redo Buffers And select pool,sum(bytes) from v$sgastat group by pool; gives 4 rows: blank ( which is sum of fixed_sga, db_block_buffers, log_buffer java pool large pool shared pool The sum of java, large shared is nearly equal to the variable size in v$sga - see below for some links on this difference: From http://www.ixora.com.au/q+a/memory.htm Variable SGA size 6 June 1999 Why is the variable size shown when I type SHOW SGA different than the shared_pool_size I set in the init.ora? Other than the shared pool, the variable area also contains the memory arrays behind V$PROCESS, V$SESSION, V$TRANSACTION, V$LOCK and so on, plus a good number of other things. Some are fixed in size, but many are sensitive to the setting of various init.ora parameters. On some platforms each structure starts on a memory protection boundary, so some extra memory is needed for padding. Nevertheless, this does not affect the size of the shared pool, because Oracle calculates what it needs here on instance startup, and then adds the value of the shared_pool_size parameter before allocating the variable area of the SGA. From askTom http://asktom.oracle.com/pls/ask/f?p=4950:8:965938::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:71012348056,%7Bvariable%7D%20and%20%7Bsize%7D The variable portion of the SGA holds all of the control structures for the SGA itself. The bigger the SGA, the bigger the variable portion. APPROXIMATING SGA SIZE AND SHOWING EXISTING SGA To approximate the size of the SGA (Shared Global Area), use the following formula: ( (db_block_buffers * block size) + shared_pool_size + log_buffers) /.9 ... I guess the above formula would need large pool and Java pool adding in for post version 8 (Java only post 8i) For a detailed discussion see http://asktom.oracle.com/pls/ask/f?p=4950:8:965938::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:365088445659,%7Bvariable%7D%20and%20%7Bsize%7D eg this says every control_file will consume 256 bytes of variable size memory. And http://asktom.oracle.com/pls/ask/f?p=4950:8:965938::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:382418730963,%7Bvariable%7D%20and%20%7Bsize%7D goes on to mention The variable sized component of the SGA, as its name implies, contains data structures that are variably sizes (eg: things like sessions, processes, db_files will affect this marginally -- things like shared_pool_size will large effects on this). The variable size of the SGA is the sum of the sizes of all of these variably sized structures (but not block buffers, they are reported separately). Hope all this helps. Regards, Bruce Reardon -Original Message- Sent: Tuesday, 25 June 2002 11:43 Try shared_pool_size, large_pool_size, java_pool_size and shared_pool_reserved size. This is from 8i, there may be additional ones on 9i, or 1 or 2 of those I mentioned may be deprecated. Jared On Monday 24 June 2002 15:05, Charlie Mengler wrote: Yes, I know I need to RTFM, but if some kine soul has a quick answer for me, I'd appreciate it. startup ORACLE instance started. Total System Global Area 168788768 bytes Fixed Size 729888 bytes Variable Size 100663296 bytes Database Buffers 33554432 bytes Redo Buffers 33841152 bytes Database mounted. Database opened. exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production oracle@actaeon:CAN# I just got done upgrading two V7.3.4.5 instances to V9.2 on a sandbox which has only 256MB RAM. Both SGAs are currently sized the same way. The OS is paging/swapping like carzy because SGA1+SGA2256MB. :-( Which initSGA.ora parameters control the Variable Size piece of the 9i SGA? I'd like to shrink this total to around 32MB. TIA HAND! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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).
Forms 4.5, views and instead-of triggers
Hi, We're using Forms 4.5 against an 8.1.7.1.4 database. The database is on NT4 and clients are on NT4, W2K XP. 1 of the forms is based on a view which joins 2 tables (by an equi-join on 2 fields) and has an instead-of trigger in place. If I update a field in that view from SQLPlus, a lock gets taken out on both of the base tables - as I would expect. When a record is modified in a Form based on that view, a lock initially only gets taken out on 1 table - the lock is always on the same table and sometimes not even on the table the field being modified is from. The problem is this allows another form to change the same record and we can end up with changes get overwritten by other users. I'm not a Forms developer but am hoping someone else may have a suggestion on how to get the form to put locks on both tables or whatever other suggestions come forth. I can then pass these onto our Developer. Also, is anyone else using Forms 4.5 with Instead-of triggers? Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: sequence question
Short answer - yes you could loose some numbers. Have a look at http://www.ixora.com.au/scripts/library.htm and the unload_sequences.sql and keep_sequences.sql which mention that you can lose when: Shutdown abort is done the sequence gets aged out of the library cache The scripts suggest 2 ways to help avoid this. If its really critical to never skip a number then maybe you shouldn't cache the sequence. But then again, if its that critical maybe you shouldn't use a sequence - eg what if someone gets the sequence number and then does a rollback. It is my understanding from a simple test that you will now have a hole / gap in the sequence numbers. So, does a gap REALLY matter (ie can you never have a gap) or is it just that you need to avoid gaps where possible. HTH, Bruce Reardon -Original Message- Sent: Friday, 14 June 2002 11:23 Hi List , I want to create a sequence which will be used by some external process to generate some unique number . since this sequence will be used very frequently I would prefer to cache around 1 numbers . Am I going to loose some numbers ? what is SEQUENCE_CACHE_ENTRIES and how does it affect number or sequence cached ? Thanks , -Bp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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 sort_area_size, sort_retained_size,hash_area_size but sti
Paula, and what OS / database version? eg if Windows is the server setup to use all 16 Gb of memory? Bruce Reardon -Original Message- Sent: Friday, 14 June 2002 2:29 Paula, what is the size of your hash_area_size, sort_area_size, and sort_area_retained_size? what does your explain plan look like? hashes, sort/merge, or nested loops? do you have a lot of parallel to parallel, parallel to serial in your plans? jack silvey --- [EMAIL PROTECTED] wrote: Have 12Gb RAM available , using parallel query with large mv joined to small code tables and setting session parameters to use Gb's of memory (have system to myself at the time) but system shows 12Gb RAM available still and writing to temporary segment - why or why or why? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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).
SQLPlusw output is white on white
Hi, I am running sqlplusw 81714 under NT4 SP6a. 2 days ago I noticed that on 1 of our servers sqlplusw was showing a blank screen. That is, I can log into a database (and see the login on another computer via v$session) but no visible output is on the screen. It's like sqlplusw is writing in white font on a white background. Though the cursor is present as its normal flashing black bar. I can run scripts and the cursor scrolls down the screen, but no output is visible. It happens regardless of which database I connect to and only from that 1 server. I've tried logging off and back on, altering screen size, colours refresh rate and none have had any effect. There's no login.sql on that server (not that I know of a sqlplus parameter that could cause this anyway). Sqlplus from a CMD prompt works ok as do other Windows programs (eg notepad is ok). Any suggestions on what to try? As it is a server I haven't tried rebooting but I will try that if no other ideas come through. Any help is appreciated. Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Installing Oracle 9i Developer suite
Stephane, I imagine they (assistive technologies such as screen readers) would be programs / hardware that convert what is on the screen into say voice or Braille. Used by say visually impaired people, people who can't read and others. That is, they assist people who can't see or interpret the information that is normally shown on the screen. Hope this is correct helps, Bruce Reardon -Original Message- Sent: Friday, 14 June 2002 13:29 I'm installing Oracle 9i Developper suite on winXP and the doc says : If you use assistive technologies such as screen readers to work with Java-based applications and applets, run access_setup.bat before starting your screen reader. What the hell is assistive technologies such as screen readers = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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).
Forms system editor not creating temp file under Windows
Hi, We are using Forms 4.5 under NT4 and Windows 2000 against 81714 database on Windows. For a particular form, the fields are set to invoke the System Editor when Edit, Edit is selected from the menu or Ctrl-E is pressed. This works under NT4 but under W2K. Under W2K, Forms 4.5 tries to create a temp file with a 4 character extension (eg .1TMP) but the file is not created. Under NT4, the file is created successfully with the expected 3 character .TMP extension. Has anyone seen this behaviour and know of a workaround? We are considering altering the item properties to use the default editor instead of the system editor, but it would be better to allow the system editor (notepad) to keep working. I have tried altering the system editor to write or the NT4 version of notepad (by the registry setting forms45_editor) but this has no effect. Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: sys corrupted in warehouse, sev1 tar open - resolved
Jack, Something you may find useful if you're not already aware is the schemaname field in v$session. Compare this to username and this may help determine if set current_schema is being used. We use the set current_schema in a login trigger, though the trigger has smarts in it to only do it for application users and not for schema owners, sys, system etc. HTH, Bruce Reardon -Original Message- Sent: Thursday, 13 June 2002 4:20 To: Multiple recipients of list ORACLE-L It was the alter system set current_schema=x statement after all. I am sure that this raises some interesting questions, if only I had time to dwell on it. Just now cleaning up all the broken indexes from the loads that abended when the db went down. I love this job. Where else do you get to play at work? jack --- Jack Silvey [EMAIL PROTECTED] wrote: All, thanks for the input. Looks like someone implemented a login trigger. haven't seen the code yet, but I would venture a guess he used the unsupported alter system set current_schema=x. sometimes you live and learn, sometimes you just live! thx, jack silvey --- Hately Mike [EMAIL PROTECTED] wrote: I don't hold out much hope here Jack. It sounds like data dictionary corruption; maybe somewhere round user$(?). That's not to say the situation's irretrievable; I've seen OTS fix some bad situations in my time but I'm not sure that I'd want to keep the database even if Oracle Support can fix the problem. Regards, Mike -Original Message- Sent: 12 June 2002 14:23 To: Multiple recipients of list ORACLE-L Listers, Our warehouse now has a split personality and we have a sev1 open on it. Suspect recovery is in the cards, but want to avoid if possible. Yesterday, users unable to get to their own functions. Soon after, RMAN cannot find package dbms_backup_restore, even though it exists under sys. Oncall ran the sql script to recreate - and the pacakge was recreated under a schema called dma_rbate2. RMAN now finds the package under dma_rbate2, although it is invalid. Drop the package under dma_rbate2, and now RMAN cannot find the package any longer, although it still exists under sys. Logged in as sys. Tried to desc dbms_backup_restore - no luck. Tried to desc sys.dbms_backup_restore - success. Analyst reccomends running catalog.sql. Oncall does so, and it creates as many packages as it is able under dma_rbate2. I get up this AM and can't login, because the sessions can't find the package dma_rbate2.dbms_application_info. Anyone? Buhler? Buhler? thx, jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Connecting as sysdba via ODBC / VB
Alex, Got it from Technet under downloads. Bruce Reardon -Original Message- Sent: Thursday, 13 June 2002 5:53 From where did you download it? Maybe there exist new version of OLEDB provider for ADO? Alex Hillman -Original Message- Bruce (CALBBAY) Sent: Monday, June 10, 2002 11:08 PM Someone in the last few days requested info on connecting as sysdba from VB. I deleted the email at the time but I just downloaded the ODBC driver version 8.1.7.6.0 and found the following: NEW FEATURES ODBC 8.1.7.6.0 Added support for connection syntax username/password as sysdba and username/password as sysoper. The as sysdba and as sysoper string can also be contained in the password field. (Bug 2114033) Added support for the statement attribute SQL_ATTR_RETRIEVE_DATA. (Bug 2165706) ODBC 8.1.7.5.0 Added support for username/password syntax to be used as username to log on. (as requested by Bug 1094170) I hope this might be of use and perhaps will work via OCI etc from VB. HTH, Bruce Reardon -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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).
SQLPlusw shows blank screen
Hi, I am running sqlplusw 81714 under NT4 SP6a. Today I have noticed that on 1 of our servers sqlplusw is showing a blank screen. That is, I can log into a database (and see the login on another computer via v$session) but no visible output is on the screen. It's like sqlplusw is writing in white font on a white background. Though the cursor is present as its normal flashing black bar. I can run scripts and the cursor scrolls down the screen, but no output is visible. It happens regardless of which database I connect to and only from that 1 server. I've tried logging off and back on, altering screen size, colours refresh rate and none have had any effect. There's no login.sql on that server (not that I know of a sqlplus parameter that could cause this anyway). Sqlplus from a CMD prompt works ok as do other Windows programs (eg notepad is ok). Any suggestions on what to try? As it is a server I haven't tried rebooting but I will try that if no other ideas come through. Any help is appreciated. Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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).
Connecting as sysdba via ODBC / VB
Someone in the last few days requested info on connecting as sysdba from VB. I deleted the email at the time but I just downloaded the ODBC driver version 8.1.7.6.0 and found the following: NEW FEATURES ODBC 8.1.7.6.0 Added support for connection syntax username/password as sysdba and username/password as sysoper. The as sysdba and as sysoper string can also be contained in the password field. (Bug 2114033) Added support for the statement attribute SQL_ATTR_RETRIEVE_DATA. (Bug 2165706) ODBC 8.1.7.5.0 Added support for username/password syntax to be used as username to log on. (as requested by Bug 1094170) I hope this might be of use and perhaps will work via OCI etc from VB. HTH, Bruce Reardon -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: runaway oracle.exe thread on NT / W2K
Jeffrey, As an idea - does orakill let you kill the thread off? This may let you workaround the issue without restarting the service. Bruce Reardon -Original Message- Sent: Friday, 7 June 2002 6:05 This has now happened on 3 separeate boxes. This has happened while putting on an Oracle Applications patch or in the last case, after starting the concurrent managers for 11i with a lot of requests scheduled to compile all of the flex fields. In every instance, the thread id does not match anything in oracle. We notice that box is using 50-100% cpu even though nothing is running. Stop concurrent managers. Terminate web sessions. Exit all sqlplus sessions. Use pslist from sysinternals.com and it shows a running thread of oracle.exe using lots of user and kernal time. This thread id is not shown in v$session/process Oracle has not been of much help to date. Even after doing a shutdown immediate, cpu is still high and thread is running. Have to stop the service to get rid of it all. We had been on 8.1.7.1.5 but upgraded to 8.1.7.3.2 since minimum for our Oracle Apps patches was 8.1.7.2.x just went to the latest and greatest since know eventually would be required. Has anyone else seen anything like this. Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Can we find SQL user
Have a look at v$session In particular the osuser, terminal and machine fields - these may help Also look at the listener log file - this may help Or do you have an application server sitting in the middle? HTH, Bruce Reardon -Original Message- Sent: Friday, 7 June 2002 15:18 Hi List, Suppose I have m1,m2,m3 machines, all the users sitting on these machines are using oracle 'user1' to connect to the server. As all the people are logged in with the same user name ,Can we find which user(or machine) has issued which SQL statement. Thanks Sam -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: ONLINE or not ONLINE? That is the question.
Rich, Are you using PQO? In which case online rebuilds don't work - I'm not sure if this means they fail or more likely it means they revert to being non-online. You may have done it, but have you searched the 8174 release notes as another way of looking at issues with online rebuilds? Bruce Reardon -Original Message- Sent: Friday, 31 May 2002 5:44 Not an option for partition indexes. Other than that, I've had pleasant experiences with it. Kevin Kennedy First Point Energy Corporation -Original Message- Sent: Thursday, May 30, 2002 11:54 AM So I'm finally able to implement procedures for index rebuilds. I plan on using the ONLINE option for our 8.1.7.2.0 DB, so I hit Metalink to search for possible problems. The only major problem I see is pre-8.1.7.1 where the ONLINE option on an ALTER INDEX REBUILD can actually corrupt the index. Since that shouldn't be a concern with our current patch level, does anyone know of a reason to avoid using ONLINE? TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: USE_SHARED_SOCKET with Oracle 8.1.7 on NT.
Andrey, I don't use USE_SHARED_SOCKET, but according to the bug description (bug 1566794 as you listed) this is fixed in 81714 and above. So it seems the solution is to upgrade to 81714 or above (list consensus seems to be to use 8.1.7.2.x in preference to 8173x but I presume that 8174 should be out for Windows soon. Regards, Bruce Reardon -Original Message- Sent: Thursday, 23 May 2002 20:53 hi ! Did anyone successfully use the USE_SHARED_SOCKET env variable with Oracle 8.1.7 on NT? If so , how do you overcome the bug 1566794 , please ? Thanks a lot in advance DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: so when did you switch from NT to unix for oracle
I'd agree that Windows can run reliably - if administered appropriately and the server is dedicated to single (or very few) tasks. Uptimes of 300 days (whilst not at all earth shattering compared to VMS, Unix and others) are possible and repeatable. Database uptimes of 3 figures are possible and in our case get affected by application upgrades / database configuration changes. And the above is with NT4. If you want better uptime use W2K - 1 good reason is that it can (with correct controllers) support adding disks without an OS reboot. Sounds trivial for VMS and probably Unix but it can't be done (at least not easily) with NT4. BUT, While ever the admin believes it won't be reliable it probably won't be. Joe - did you find the reason for running out of memory? Are they using PQO and 8171x by any chance? We have had memory issues but they were due to Oracle bugs rather than due to OS (Windows) issues. There are stable and there are unstable Windows servers / sites. There are also stable and there are unstable VMS (/Unix/...) servers / sites. Does the site have good Windows admins? Are they planning on becoming good Windows admins? If the answer to both of the above is no and they do have good Unix admins then maybe they should consider moving to Unix. Someone else said Windows can only have 4 CPUs - this is incorrect. It may be that it won't scale linearly above x CPUs (I have never tried) but it can certainly run with 32 (and maybe more). Regards, Bruce Reardon -Original Message- Sent: Friday, 24 May 2002 6:55 here we go again - NT bashing. I will say again, NT is a perfectly fine platform if it is being administered by a competent NT Admin, and it is dedicated to runing only Oracle. there. I feel better. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, May 23, 2002 4:03 PM Can you afford non-scheduled reboots? If no, don't even think of NT/2000. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Runaway threads in 8.1.7.3.2 on NT/W2K
Jeffrey, Can you use pview or pviewer from Resource kit to work out which thread is using the CPU and which ones get left behind. Use v$Process to see which threads correspond to which processes. And tlist will show what the threads were doing last and may shed some light on this. Stop everything to do with Apps (cman etc) and see if the database still shows the same behaviour. Have you stopped the database service or just the instance - see if stopping the service has any effect. Hope these ideas help, Bruce Reardon -Original Message- Sent: Friday, 24 May 2002 0:13 We are running Oracle 8.1.7.3.2 on W2K/NT. We have experienced a problem while applying Oracle Apps patches that CPU shot to 100% for 4 threads. The weird part is that the patch had aborted so nothing was running in the database. Bouncing database cleared the problem. Continued with more patches and noticed CPU running at 25% with nothing running. Again bounced database and back to normal. Latest item we noticed today is that CPU is normal (less than 5%) with no users but we have 40 threads showing in task manager. Did shutdown immediate and count only went down to 30. Bounced database and now have 12 threads. If connect / disconnect, I see the count go up and then down again. Looks like Oracle is missing some threads - possibly why during our patching of the Oracle Apps we see CPU out of control. We have a TAR on this but so far Oracle is lost regarding the high CPU. Especially given that don't have it right now. Hoping the extra threads will point to something. Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: How to find current ORACLE_HOME programmatically
Alex, As another alternative - can you just define tns_admin at environment level (and / or within the registry under each and every home tree) and then both EMs will (should?) be happy to use the same tnsnames.ora file. As for current value of oracle_home - one cludge would be to parse the path and see which directory comes first. The other would be to use the Oracle Home selector (which no longer has a cmd line interface - at least the 817 version doesn't) and use a program which lets you pass keystrokes and try to grab the given home name off that screen. However the home selector says your primary home is the one that appears first in the path so maybe just parse the path. Please let the list know if either of these approaches works. HTH, Bruce Reardon -Original Message- Sent: Thursday, 23 May 2002 2:54 Looks like no takers, except one whos great advice was to find it somewhere in the registry. Alex Hillman -Original Message- Hillman Sent: Monday, May 20, 2002 7:08 PM Hi, guys. I need to find current value of ORACLE_HOME on the client from VB program, running on that client. I understand that it is in the registry somewhere. I have 2 client installations - one is 8.1.7 and another is 9 on the same client. Apparently EM from 9 installation knows that needed tsnames.ora is in 9 ORACLE_HOME and 8.1.7 EM knows that tsnames.ora in 8.1.7. installation. And my VB program select tsnames.ora from 8.1.7 installation Any ideas? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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).
OT: Miracle Database forum in Sydney - get together Sunday night
Hi all, If you don't live in Sydney or you're not going to the Miracle Database forum in Sydney then you can delete this. I'm arriving into Sydney early Sunday evening (missing the presenter's chat from 3pm - 6pm on the Sunday afternoon) and wondering who else out of attendees or list members might be there and want to catch up on the Sunday evening? Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: ok one for you windows/oracle people out there
Joe, From an old posting I made about a year ago ( I hope it is still correct): As for Very Large Memory (VLM) support under NT / Windows 2000 I have a copy of a document posted to this list (paper called Oracle8i on Windows NT/2000: Architecture, Scalability, and Tuning April, 2000 that states a Xeon is required for VLM support (in addition to requiring NT Enterprise Edition under v4). However, 4GB RAM Tuning (4GT)is available using just Enterprise Edition and it will provide 3GB of memory to applications as opposed to 2GB - the MS Web site states that VLM and 4GT are incompatible. The Oracle documentation (817 Admin guide for Windows Ch 10) states that ESMA (VLM under NT 4) is only available on Intel Pentium II and Pentium III Xeon 32-bit processors and the Intel site (http://support.intel.com/support/performancetools/pse36/tti/softrequ.htm) seems to confirm that a Xeon is required. I haven't used 4GT or ESMA / VLM as I haven't needed to. Also, does your machine actually have memory available? Eg open up Task Manager - What does phys mem available show. And for commit charge - how does total and peak compare to your limit? And is your limit less than or more than 2 Gb? How much memory does the server have? Hope this helps as some starting questions. Regards, Bruce Reardon PS - Have you written that logminer book yet ? :-) -Original Message- Sent: Wednesday, 22 May 2002 7:24 Whenever more users logon to the database we get Oracle error 'out of process memory' and the process fails. It indicates that OS could not allocate memory for the process. For Windows NT Enterprise Edition a feature 4 GB Memory Tuning is available. Anyone know anything about it, does it work, etc? thanks, joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: calling a stored procedure from a sitescope monitor
Chris, You could try exec but I'm still not sure what your procedure is going to show. If it has no parameters then it can't return a value as such. Maybe it shows a value on the screen using dbms_output? If so, I would doubt this will work over JDBC. What should your procedure do / show? Bruce Reardon -Original Message- Sent: Tuesday, 21 May 2002 16:43 Sitescope is monitoring software produced by Freshwater software. It has many different types of templates for monitors for OS, web servers, databases, etc. You're right that I could probably do what I want here with a function and not a procedure. This is really a prototype for more complicated things I want to do, so I made it a procedure. But this one is really just a wrapper for a function that does all the work. If I say desc foo in sqlplus, it returns PROCEDURE foo and that is it. The Freshwater support guy I talked to suggested that I try call foo and that did not work, but I just noticed that it doesn't work in sqlplus either -- but call foo() does. Tried to redo the monitor to use that but it didn't work there either. Gives an ora-900 invalid sql error. Let's say bar is the one function in procedure foo. I actually can just say select bar from dual in the monitor, and it runs, but I want to keep at it until I can call procedures too. BTW, desc bar returns FUNCTION bar RETURNS NUMBER -Chris From: Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: RE: calling a stored procedure from a sitescope monitor Date: Tue, 21 May 2002 14:42:45 +1000 Chris, I don't know what Sitescope is and I haven't used JDBC but can perhaps offer a suggestion. You say foo doesn't have any arguments and that it returns a value. I take it that foo is actually a function then? From sqlplusw, what does desc foo show? Consider the following: SQL create or replace function foo return varchar is 2 begin 3 return 'a'; 4 end; 5 / Function created. SQL desc foo FUNCTION foo RETURNS VARCHAR2 SQL select foo from dual; FOO --- a SQL So, can you just do select foo from dual via JDBC? If I've misunderstood, what is sitescope and how would you execute your foo from SQLPlus? Regards, Bruce Reardon -Original Message- From: Chris McGrail [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 21 May 2002 14:33 Has anyone done this? Let's say I have a procedure named foo and want to call it in a Sitescope monitor. There are no arguments to foo. I just want to call it and get the one value it returns. Freshwater doesn't have any doc for this and they've been sitting on my request for information for a week. They do have a document with an example for SQL Server but nothing for Oracle. If Oracle were like SQL Server, you'd just put the name of the procedure in the query line on a typical database monitor, but I tried that and it doesn't work. We're using the jdbc thin driver to a version 8.1.7.2 instance if it makes any difference. Thanks. -Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Anyone using 8.1.7.4
Ron, We currently use 8.1.7.1.4 on NT and have been considering testing an upgrade to 8.1.7.4 However, to my knowledge, 8174 isn't yet out for Windows. I can find it on Metalink for Solaris (ID:1697372 Patchset::2376472) but have not found it for Windows. Do you know where 8.1.7.4 for Windows is available from? (I haven't been able to try ftp://updates.oracle.com/ due to problems with my browser (IE) not prompting me for a username). Thanks, Bruce Reardon -Original Message- Sent: Tuesday, 21 May 2002 1:43 I have an upgrade of Oracle 7.3.4 on NT. I hear 8.1.7.3 had a lot of bugs. Should I go with 8.1.7.4? Any experience with 8.1.7.4? Ron Smith [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Statspack Problem
Have you connected via BEQ or Net8 initially? Have you got oracle_sid set as an environment variable, as it looks like the connect perfstat/perfstat uses a BEQ connection. After connecting as sys, can you now do a connect perfstat/perfstat (though maybe the scripts cleaned up on error and deleted the perfstat user. HTH, Bruce Reardon -Original Message- Sent: Tuesday, 21 May 2002 11:39 I am running 8.1.6 on a Win2000 SP2 machine. When I try running 'statscre.sql' as SYS, the first script completes fine, but when the second script tries to connect perfstat/perfstat , I get the error, You are no longer connected to Oracle TNS:ORA-12154. Of course the system then proceeds to attempt to run the other two scripts returning about a hundred not connected error messages. The Perfstat user was created and I did not have the chance to intervene and change its password. Any ideas as to why the script would fail to connect? I connected as internal in the first place to run the script, so I don't think its TNS. Tom Schruefer - [EMAIL PROTECTED] 410-313-6825 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: ANY IDEA?
You might like to take a look at the following 2 links and work through the information contained within them. diagnosing ora-4031_errors on Metalink at http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOTp_id=146599.1 Also see http://www.zoftware.org/tuning/tune_shared_pool.html#fixed_table (Tim Gorman pointed this out on 16-May when he found it using the very good search facilities at www.ixora.com.au) You may also want to search the list archives - there are numerous ways: http://www.fatcity.com/ListGuru/my.php http://faqchest.dynhost.com/prgm/oracle-l/ http://www.mail-archive.com/oracle-l%40fatcity.com/-- this has them threaded and is a nice web interface to viewing the messages as well Regards, Bruce Reardon -Original Message- Sent: Tuesday, 21 May 2002 10:38 There is not a single segment of memory large enough for you to grab. The way I see it, you have two options, increase your shared pool or flush your shared pool. Regards, Melanie Burns -Original Message- Sent: Monday, May 20, 2002 5:38 PM Hello List, I got the following error today(Oracle 8.1.7.0 Sun Solaris) ORA-04031: unable to allocate 4200 bytes of shared memory (shared pool,unknown object,sga heap,state objects) At the moment my shared_pool size is 30M Thanks for your help Hamid Alavi Office 818 737-0526 Cell818 402-1987 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Wait Event PX Deq: Execution Msg
Nancy, Hope this helps - it is some information I have saved over time. If someone else can help clarify this I would find it helpful too. From Jonathan Lewis in a Metalink discussion. The most useful giveaway is often the 'send blocked' wait - this indicates a degree of contention as multiple producers all want to feed a given consumer at the same time. and from Anjo's wait event paper: Event Name P1 P2 P3 parallel query dequeue wait queue/reasonsleeptime passes 2.75 parallel query dequeue wait The process is waiting for a message during a parallel execute Wait time The wait time depends on how quickly the message arrives. So wait times may vary, but it will normally be a short period of time. Parameters queue/reason Before 7.2, it was queue which indicated the process queue to dequeue. With 7.2 and higher it shows the reason for dequeueing. Check the fixed table x$kxfpsds for the current list of reasons for your release. sleeptime/senderid If sleeptime greater than 0x1000, the lower sixteen bits indicate the slave number number on the remote instance indicated by the higher sixteen bits of the first 32 bits. Look at P2RAW to get the best information. loop The number of times we have waited sofar. Advise The init.ora parameter _parallel_server_sleep_time determines how long you will sleep on this event. The default is 100 msec if there are no credits and 2000 msec if there are. You can specificy the init.ora _parallel_server_sleep_time twice in your init.ora file. The first entry will set the sleep time for if there are credits. The second entry will set the sleep time for if there are no credits. saved from a Metalink forum at 1 stage. Here is some information I was able to find on these parameters. Documentation bug, 1077684, has been filed requesting the description of these and other missing wait_events. PX Deq:Table Q Normal No description available Parameters: P1: sleeptime/senderid P2: passes P3: PX Deq:Execution Msg Definition:PQ slave is waiting to be told what to do. This is one of the main events used in a parallel query dialogue as the slave is told to parse / execute / fetch etc.. Parameters: P1: sleeptime/senderid P2: passes PX Deq: Execute Reply Definition: QC is waiting for a reply Parameters: P1: sleeptime/senderid P2: passes PX Deq Credit: send blkd Definition: Waiting for the CREDIT Parameters: P1: sleeptime/senderid P2: passes P3: qref This Wait Event replaces Parallel_Query_Dequeue, the name was changed in Oracle8i. PX Deq Credit: need buffer Definition: Waiting for the CREDIT so we can fill a buffer to send data. Parameters: P1: sleeptime/senderid P2: passes P3: qref This Wait Event replaces Parallel_Query_Dequeue, the name changed in Oracle8i. PX Deq Credit: free buffer Definition: Wait for credit to free a null message Parameters: P1: sleeptime/senderid P2: passes P3: qref This Wait Event replaces Parallel_Query_Dequeue, the name changed in Oracle8i. Hope this helps, if you are having a specific problem, please post details. Reem Munakash Enterprise Server Analyst -Original Message- Sent: Friday, 17 May 2002 10:53 Hello, I am hoping someone can help me with an elusive wait event called 'PX Deq: Execution Msg'. As you can see from the statspack Top 5 Wait Events this event accounts for almost 75% of the wait time. I have been looking in past Oracle-L posts, Google, Oracle FAQ, OTN, Metalink, Oracle manuals, etc. I can find almost nothing about this event. What I did find leads me to believe may be an idle event and that it is the second event 'PX Deq Credit: send blkd' that may be the more troublesome one. However I am still confused about both. Can someone help set me straight or tell me where there might be some more information about these 2 events? Thanks, Nancy Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time - -- PX Deq: Execution Msg 42,1448,639,803 74.48 PX Deq Credit: send blkd 15,3392,878,443 24.81 log file parallel write41,934 30,787 .27 db file parallel write 8,776 25,046 .22 db file sequential read24,767 18,255 .16 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: cache buffer chains
Lee, Firstly, I presume v$system_event shows a significant number and time waited for some latch related event. Go to http://www.ixora.com.au/scripts/latches.htm and get a copy of latch_sleeps.sql and child_sleeps.sql latch_sleeps will help confirm that the problem is the cache buffers chain latch. Then run child_sleeps.sql for that latch - see if the sleeps are evenly distributed or if they are bunched on a small number of latches. Then you need to work out which buffer blocks database objects those latches are referring to. Go to http://www.ixora.com.au/scripts/cache.htm and consider using hot_blocks.sql, hot_hash_latches.sql and blocks_on_hot_latches.sql. I hope the above is helpful and that I have interpreted it correctly. Also, from a Steve Adams posting: -Original Message- Sent: Tuesday, 9 October 2001 1:35 To: Multiple recipients of list ORACLE-L Hi Doug, If you can catch sessions in V$SESSION_WAIT waiting for 'latch free' on a 'cache buffers chains' latch, you can join to V$SESSION to get the SQL statement address. Something like this ... select s.sql_address from sys.v_$latchname ln, sys.v_$session_wait sw, sys.v_$session s where ln.name = 'cache buffers chains' and sw.p2 = ln.latch# and sw.event = 'latch free' and s.sid = sw.sid / Regards, Bruce Reardon. -Original Message- Sent: Friday, 17 May 2002 3:12 All, Oracle 8.0.5 Tru64 4.0f I was doing a stats pack analysis and noticed that we had latch problems. I drilled in a bit further and it would appear that the issue was down to cache buffer chains. The Metalink article (I was flying blind here) states To identify the heavily accessed buffer chain look at the latch stats for this latch under View:V$Latch_Children I did this and it came back with over 1 rows Can someone give me a little guidance here ?? Regards Lee -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle RDB vs. Plain Oracle
One could say lots more but here is the 1 minute summary: They are 2 different databases but both are owned by Oracle. (Rdb used to be owned by Digital.) Rdb only has a production version for VMS (it does have a workbench version for Windows). Rdb currently only runs on Alphas but is being ported to VMS / Itanium. Both are full featured enterprise class databases and both are being actively developed and supported. See http://www.oracle.com/rdb/ and http://www.oracle.com/ and come back if you have specific question(s). Regards, Bruce Reardon -Original Message- Sent: Friday, 17 May 2002 12:58 Hello, What are the differences between the two? Thanks. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Zero-term'd machine in V$SESSION from Winders
Jesse, Our database is 81714 on NT4. Our clients are a mix of 816 OCI, 817 sqlplus and forms 4.5. The forms 4.5 clients include only the PC name in machine column and it is not chr(0) terminated. Their terminal field is set to 'Windows NT PC' Our 816 817 clients have machine set to 'domain\pc_name' and it is chr(0) terminated. Their terminal field has just the PC name and it is not chr(0) terminated. Maybe you can use something like decode ( a.terminal , 'Windows NT PC' , a.machine , a.terminal ) Terminal Regards, Bruce Reardon -Original Message- Sent: Wednesday, 15 May 2002 6:23 So, there I am, creating a special kill user script (don't ask) for one of our instances, 8.1.6.0.0 on Solaris. The problem is that some of the output rows were getting truncated on the output. Here's the proc I had started: CREATE OR REPLACE PROCEDURE Euthanize AS v_printline VARCHAR2(140); CURSOR c_sessions IS SELECT vs.username, vs.osuser, -- --REPLACE(vs.machine,CHR(0),NULL) MACHINE, vs.machine, -- , vs.logon_time, vs.last_call_et, vp.SPID FROM v$session vs, v$process vp WHERE vs.username IS NOT NULL AND vs.paddr = vp.addr ORDER BY vs.last_call_et DESC; BEGIN FOR rsess IN c_sessions LOOP v_printline := rsess.username||'|'|| rsess.osuser||'|'||rsess.machine||'|'|| rsess.logontime||'|'||rsess.idletime||'|'|| rsess.spid||'|'||rsess.logon_time; dbms_output.put_line(v_printline); END LOOP; END Euthanize; After compiling this, I called it from SQL*Plus using execute euthanize;. I noticed that all the DBMS_OUTPUT lines that were truncated were sessions from Windohs workstations. Examining a SELECT DUMP(machine) FROM V$SESSION showed that all of the MACHINE columns from Windohs sessions were zero-terminated. No other client (Solaris) was. Since the output from DBMS_OUTPUT is being prematurely truncated by this zero-term'd field, the workaround is to either move the problem field to the end of the output line, or to use the REPLACE function, as I've commented out in the above code. The latter is necessary if there is more than one bastardized field like this or if the order of the columns in the output is important. Can anyone reproduce this? The particular clients I found with the problem are 8.1.7. TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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).
