RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
Dharminder, OracleServiceSID starts the database automatically, because by default the registry key ORA_SID_AUTOSTART IN HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 is set to TRUE. If you don't want your OracleServiceSID to start the database automatically, change the value to FALSE. This way service will be still running, but you should be able to startup the database from OEM. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Dharminder Softhome Sent: Friday, January 30, 2004 2:20 PM To: Multiple recipients of list ORACLE-L star Tom, As stated earlier, here the intention is to startup the database using OEM only and without using windows service. As for as I understand if the OracleServiceSID is properly setup and if startmode for the service is set to automatic then it will startup the database automatically once you boot the machine. Thanks. -Original Message- Mercadante, Thomas F Sent: Friday, January 30, 2004 10:44 AM To: Multiple recipients of list ORACLE-L star Dharminder, Changes the service to AutoStart when the machine reboots. As Thomas said, the NT service has to be running for you to be able to start the database. The service will stay running even if you shut the database down. Think of the service as a backgrouond placeholder for the database. It needs to be running for the database to run, but you can still shut the database down as normal and keep the background service running. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 30, 2004 10:34 AM To: Multiple recipients of list ORACLE-L start a Thomas, That is the real problem. I can start the database using Windows 2000 OracleServiceSID. But I want to start up the database using OEM only. Thanks. -Original Message- Thomas Day Sent: Friday, January 30, 2004 9:19 AM To: Multiple recipients of list ORACLE-L start a Go to the SERVICES and start the service. Dharminder SofthomeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dharminder cc: @softhome.net Subject: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated Sent by: ml-errors 01/29/2004 06:14 PM Please respond to ORACLE-L Hey all, Following is the environment. Windows 2000 Oracle Enterprise 9.2, OEM 9.2 I have two 9.2 database installed on my system. OEM is configured properly. One of the database is in automatic startmode and this DB contains OEM repository. I start the machine, open OEM, login to OEM and then try to start the other database, but it does not start the DB, instead I get ORA-12500 Failed to start a dedicated OEM doc says that with OEM, you can start/shutdown the database using OEM. Can someone point to me what I am doing wrong. I am using sys username/password for the startup of the second database. Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dharminder Softhome INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dharminder Softhome INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing
RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
Yes, I'm sure. If in the services control panel you change OracleServiceSID starup properties, it does not affect ORA_SID_AUTOSTART key in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 (and does not change the way the database is started when the service is started). What it does, it changes the value of Start key in HKEY_LOCAL_MACHINE\CurrentControlSet\Services\OracleServiceSID (and the way service is started when the machine is rebooted - Automatic/Manual). So, if you have Start key in HKEY_LOCAL_MACHINE\CurrentControlSet\Services\OracleServiceSID set to 2 (which means automatic) and you have ORA_SID_AUTOSTART key in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 set to FALSE, then on reboot OracleServiceSID will start automatically, but the database will not startup. So, then (whenever you ready) you can do: set oracle_sid=instance_name sqlplus /nolog connect sys/password as sysdba startup which will startup the database. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jacques Kilchoer Sent: Friday, January 30, 2004 4:04 PM To: Multiple recipients of list ORACLE-L star -Original Message- Igor Neyman OracleServiceSID starts the database automatically, because by default the registry key ORA_SID_AUTOSTART IN HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 is set to TRUE. If you don't want your OracleServiceSID to start the database automatically, change the value to FALSE. This way service will be still running, but you should be able to startup the database from OEM. Igor Neyman, OCP DBA [EMAIL PROTECTED] Are you sure about that? This is the way I thought it worked with Oracle 8.1 and 9.2 (I just tried it again today using Oracle 8.1.7 on Windows 2000) If in the services control panel the database service has Startup Type Automatic, then the registry entry you mention will have ORA_sid_AUTOSTART TRUE, and when the machine is rebooted, the service will start up AND the instance will be started up. If you want to startup the instance manually on system reboot, you should set the Startup Type for the service to Manual (either in the Services control panel or the registry). Then once the machine is rebooted, you can 1) go to the Services control panel and start the Service, which will start the service and the instance OR 2) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% which will start the service and the instance OR 3) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% -starttype srvc which will start the service, then oradim -startup -sid %ORACLE_SID% -starttype inst which will start the instance OR 4) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% -starttype srvc which will start the service, then sqlplus /nolog connect sys/password as sysdba startup which will start the instance I personally use method 4. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Trigger, how?
Use: when updating col1, col2,... clause. Better yet, read oracle docs. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Friday, January 23, 2004 3:19 AM To: Multiple recipients of list ORACLE-L Hallo all, Anyone whom know how to write the trigger if you just want a trigger to be fired if only two fields in a table is changed, not all of them? Any good example? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: internal date value
You gather it wrong :) Oracle stores date in 8 bytes, one for each: year, month, day, hour, min, ... etc. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Droogendyk, Harry Sent: Friday, January 23, 2004 12:40 PM To: Multiple recipients of list ORACLE-L Folks: From what I gather, Oracle stores dates as the number of elapsed days since Jan 1, 1968. When I query a column of type 'DATE', it returns me the default format, dd-mon-yy. I know I can use to_char(date_col,'MMDD') etc... to define many output formats. What can I do to get the raw internal value of the date? i.e. today is 13172. Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Droogendyk, Harry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Views for a table
Mladen, Any privileges required to view this table, or just common sense? :-) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mladen Gogala Sent: Friday, January 23, 2004 1:24 PM To: Multiple recipients of list ORACLE-L The table you want to look into is USER_OTN, Column DOCUMENTATION. On 01/23/2004 12:49:34 PM, Mauricio V?lez wrote: Hi everybody I have the following question How can I query a table's views? For example I have the table students and I want to know the views related to this table. Thanks, Mauricio V?lez - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: internal date value
Oops... Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Peter Gram Sent: Friday, January 23, 2004 2:54 PM To: Multiple recipients of list ORACLE-L Igor Sorry but Oracle uses 7 bytes for a date century (1 byte) year (1 byte) month (1 byte) day (1 byte) hour (1 byte) minute (1 byte) second (1 byte) SQL desc d Name Null?Type - D DATE SQL col dump format a40 SQL select to_char(d, 'dd mon hh24:mi:ss'), dump(d) dump from d; TO_CHAR(D,'DDMON DUMP 05 may 0001 15:30:45 Typ=12 Len=7: 100,101,5,5,16,31,46 05 may 0100 15:30:45 Typ=12 Len=7: 101,100,5,5,16,31,46 05 may 0500 15:30:45 Typ=12 Len=7: 105,100,5,5,16,31,46 05 may 1000 15:30:45 Typ=12 Len=7: 110,100,5,5,16,31,46 05 may 1500 15:30:45 Typ=12 Len=7: 115,100,5,5,16,31,46 05 may 1999 15:30:45 Typ=12 Len=7: 119,199,5,5,16,31,46 05 may 2000 15:30:45 Typ=12 Len=7: 120,100,5,5,16,31,46 05 may 2100 15:30:45 Typ=12 Len=7: 121,100,5,5,16,31,46 05 may 2500 15:30:45 Typ=12 Len=7: 125,100,5,5,16,31,46 05 may 2600 15:30:45 Typ=12 Len=7: 126,100,5,5,16,31,46 05 may 2900 15:30:45 Typ=12 Len=7: 129,100,5,5,16,31,46 05 may 3000 15:30:45 Typ=12 Len=7: 130,100,5,5,16,31,46 12 rows selected. /peter Igor Neyman wrote: You gather it wrong :) Oracle stores date in 8 bytes, one for each: year, month, day, hour, min, ... etc. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Droogendyk, Harry Sent: Friday, January 23, 2004 12:40 PM To: Multiple recipients of list ORACLE-L Folks: From what I gather, Oracle stores dates as the number of elapsed days since Jan 1, 1968. When I query a column of type 'DATE', it returns me the default format, dd-mon-yy. I know I can use to_char(date_col,'MMDD') etc... to define many output formats. What can I do to get the raw internal value of the date? i.e. today is 13172. Thanks. -- Best regards/Venlig hilsen /*Peter Gram*/ mailto:[EMAIL PROTECTED] Miracle A/S http://www.miracleas.dk/ Kratvej 2 DK - 2760 Måløv Cell: (+45) 2527 7107 Phone: (+45) 4466 8855 Fax: (+45) 4466 8856 Home: (+45) 3874 5696 Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] SQL Server Master Class 8-10 Marts, Database Forum 28-30 October Master Class 17-19 Januar 2005. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Gram INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Re[2]: Oracle vs Mysql
I was using Lattice-C on x286. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Carel-Jan Engel Sent: Tuesday, January 20, 2004 5:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: Re[2]: Oracle vs Mysql At 03:29 PM 1/20/2004, you wrote: I do indeed. Rumor was that rpt/rpf was written by Larry himself. Now I understand! I once applied for a job at Oracle, and got asked: What do you think about RPT/RPF. My answer: Probably som hobby-project of one or another developer, which, after demonstration to his boss, was turned into a product. That was a disloyal remark, even in Holland, and I wasn't hired. Anyone used HLI, with Lattice-C? Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) ===
RE: Exporting a partition with transport tablespace
Title: Exporting a partition with transport tablespace
You could create new table in
transportable tablespace, exchange data with the partition you want to export, move
transportable tablespace file to the destination system, exchange data back
from the table into desired partition.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of NGUYEN
Philippe (Cetelem)
Sent: Monday, December 22, 2003
10:34 AM
To: Multiple recipients of list
ORACLE-L
Subject: Exporting a partition
with transport tablespace
Hi list,
is it
possible to export a partition with the transportable tablespace feature ?
My
partition is over 8 Go.
Here my statements , thank you in advance !
SQLexec
sys.dbms_tts.transport_set_check('HISTO_DOSSIER_P1_MD_TAB',FALSE);
SQL
select * from sys.transport_set_violations;
VIOLATIONS
Partitioned
table TOPASE.HISTO_DOSSIER is partially contained in the transportab
le
set: check table partitions by querying sys.dba_tab_partitions
Default Partition (Table) Tablespace
HISTO_DOSSIER_P2_MD_TAB for HISTO_DOSSIER n
ot
contained in transportable set
Default Partition (Table) Tablespace
HISTO_DOSSIER_P3_MD_TAB for HISTO_DOSSIER n
ot
contained in transportable set
Default Partition (Table) Tablespace
HISTO_DOSSIER_P4_MD_TAB for HISTO_DOSSIER n
ot
contained in transportable set
RE: How to refresh
You can always disable triggers and constraints in existing schema before running import (and then, enable them after import is done). Also, specify CONSTRAINTS=N and TRIGGERS=N when exporting. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Smith, Ron L. Sent: Tuesday, December 16, 2003 9:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: How to refresh
Here is a sample of the script I run to disable FK constraints: declare lTables DBMS_SQL.VARCHAR2_TABLE; lConstraints DBMS_SQL.VARCHAR2_TABLE; nJ BINARY_INTEGER; BEGIN SELECT table_name, constraint_name BULK COLLECT INTO lTables, lConstraints FROM user_constraints WHERE owner = 'IPN_DBA' AND constraint_type = 'R'; FOR nJ IN 1..lTables.COUNT LOOP DBMS_OUTPUT.PUT_LINE(lTables(nJ) || ': ' || lConstraints(nJ)); -- just for logging EXECUTE IMMEDIATE 'ALTER TABLE ' || lTables(nJ) || ' MODIFY CONSTRAINT ' || lConstraints(nJ) || ' DISABLE'; END LOOP; END; / Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Smith, Ron L. Sent: Tuesday, December 16, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Seems like last time I tried to disable constraints Oracle complained and wouldn't let me due to dependant objects or something. Ron -Original Message- Sent: Tuesday, December 16, 2003 9:15 AM To: Multiple recipients of list ORACLE-L You can always disable triggers and constraints in existing schema before running import (and then, enable them after import is done). Also, specify CONSTRAINTS=N and TRIGGERS=N when exporting. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Smith, Ron L. Sent: Tuesday, December 16, 2003 9:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Re[2]: http://www.wintercorp.com/vldb/2003_TopTen_Survey/TopT
Mladen, Have you been to Sam's club or Costco? Those are the amounts they sell. Well, almost :) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mladen Gogala Sent: Friday, December 12, 2003 11:49 AM To: Multiple recipients of list ORACLE-L http://www.wintercorp.com/vldb/2003_TopTen_Survey/TopT April, I wasn't saying that they were dumb and I know that the stuff is selling but I must admit that I would have a problem consuming a gallon of pickles before they get moldy and yucky. I'd probably turn into a pickle after that. I wasn't critical of Wal-Mart, they're only catering to the market demand, but I find it incredible that market demands gallon sized jars of pickles. What is next? 50 LBS bag of Hershey's kisses? On 12/12/2003 11:24:25 AM, April Wells wrote: A 9 year old little girl who will BUY pickle juice at school will insist on gallon jars of (good) pickles. Walmart isn't dumb, they know what they can and can't sell. April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas /\ / \ / \ \ / \/ \ \ \ \ Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 -Original Message- Sent: Friday, December 12, 2003 9:54 AM To: Multiple recipients of list ORACLE-L http://www.wintercorp.com/vldb/2003_TopTen_Survey/TopT Jonathan is correct - WalMart uses Teradata. And they're selling gallon-sized Vlasic pickles. I always wondered who was buying such a monstrosity. It's a bi-annual pickles supply in a single package. Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment. Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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
RE: dropping materialized view
Disabling constraint (when you want to drop the parent table) will not help. This should help: Alter table DEBTOR_CLAIM_COMPONENTS drop constraint FK_DCC_MEDIUMS. After this you should be able to drop MV. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Tuesday, December 09, 2003 2:40 PM To: Multiple recipients of list ORACLE-L select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'SNAP$_MEDIUMS') -Oorspronkelijk bericht- Mohammed, Thanks for your reaction but this doesn't help. I think table must be snap$_mediums and when I issue your query I get: TABLE_NAME CONSTRAINT_NAMESTATUS -- -- DEBTOR_CLAIM_COMPONENTSFK_DCC_MEDIUMS DISABLED I even tried disabling all constraints. I tried removing the view Through enterprise manager, it looked ok because it doesn't show there Anymore but in the dictionary it does. I tried set constraints all deferred; drop table snap$_mediums; alter table snap$_mediums drop primary key all do not seem to work, I am starting to think I have somehow corrupted the dictionary. I hope you can shed some light on this Regards, Jeroen Van: Mohammed Shakir [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 17:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: dropping materialized view Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jeroen van Sluisdam INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: dropping materialized view
Don't (feel stupid) :) It happens to all of us... Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Tuesday, December 09, 2003 3:39 PM To: Multiple recipients of list ORACLE-L Oops I feel very stupid, thanks a lot Igor this did the tric. Regards Jeroen -Oorspronkelijk bericht- Van: Igor Neyman [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 21:09 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: dropping materialized view Disabling constraint (when you want to drop the parent table) will not help. This should help: Alter table DEBTOR_CLAIM_COMPONENTS drop constraint FK_DCC_MEDIUMS. After this you should be able to drop MV. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Tuesday, December 09, 2003 2:40 PM To: Multiple recipients of list ORACLE-L select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'SNAP$_MEDIUMS') -Oorspronkelijk bericht- Mohammed, Thanks for your reaction but this doesn't help. I think table must be snap$_mediums and when I issue your query I get: TABLE_NAME CONSTRAINT_NAMESTATUS -- -- DEBTOR_CLAIM_COMPONENTSFK_DCC_MEDIUMS DISABLED I even tried disabling all constraints. I tried removing the view Through enterprise manager, it looked ok because it doesn't show there Anymore but in the dictionary it does. I tried set constraints all deferred; drop table snap$_mediums; alter table snap$_mediums drop primary key all do not seem to work, I am starting to think I have somehow corrupted the dictionary. I hope you can shed some light on this Regards, Jeroen Van: Mohammed Shakir [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 17:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: dropping materialized view Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jeroen van Sluisdam INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: tis a puzzlement...
When exporting, use native (8.1) Export utility. When importing into 9.2 native Import utility (9.2) will perfectly well read 8.1 export files. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Thater, William Sent: Friday, December 05, 2003 1:34 PM To: Multiple recipients of list ORACLE-L Jared Still scribbled on the wall in glitter crayon: Bill, why are you trying to export an 8i database with 9i exp? because i thought i read somewhere that it would work. maybe i'm confusing it with imp? will 9i imp read an 81 exp file? it's either that, or i've experienced an ORA 99 - brain burnt out.;-) -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] Great spirits have always found violent opposition from mediocrities. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence. - Albert Einstein -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: Plan stability
No, you don't. You could use dbms_stats to create (without analyzing) your statistics (if you know, what kind of data you'll be getting), and then store outline. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Wartiak Rastislav Sent: Tuesday, December 02, 2003 7:15 AM To: Multiple recipients of list ORACLE-L I agree, but still you have load data, analyze tables, check explain plan that this is what you actually wanted and store outline. rw Correct. The point is that stored outlines can be viewed as a tool for those who like the predictability of the RBO. When the RBO is no longer available, the best way I know of to force the CBO to use your plan is stored outlines. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wartiak Rastislav Sent: 2. desember 2003 09:39 To: Multiple recipients of list ORACLE-L Subject: RE: Plan stability AFAIK RBO cannot be used for partitioned tables, not talking about the fact that RBO might not be supported in future versions. What is complicated about stored outlines? If you don't like those you can always go back to the RBO. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wartiak Rastislav Sent: 2. desember 2003 08:44 To: Multiple recipients of list ORACLE-L Subject: Plan stability Hi, my question is about the same, but more general. How can i force Oracle to use my prefered way of explain plan and not use CBO's. I mean, apart from stored outlines, it somehow seems to complicated. I would like to say what order and join types it should use. But, try as I might, I many times cannot force Oracle to use my way, even though I know it is possible, for I saw this kind of explain plan for that specific query.. I tried to use hints like ordered and use_hj etc. Can someone give some examples of full set of hints for some simple queries? Thx, rw Hi, list friends: We are using partition to archive history data in our production OLTP database. We get great performance gains(Far less disk io), but we also hit performance trouble sometimes. So I am here ask for your experience. We used local index on all partitioned tables.We add/drop partition monthly to archive the history data. But the trouble is, when add/drop partition is being done on the partitioned table, CBO sometimes changed SQL execution path.We implemented partition 2 monthes ago, and in the first time, add/drop partition went on quite smoothly, but in the second time we add/drop partition, two SQL (just TWO SQL) get bad execution path and server load rushed to 10 times(from 2 to 20 in uptime), all waiting for latch free event. It severely affected our application. We are an online system and we do not have scheduled time every month so we have to add/drop partition while db is still running. So, with system still up and running, how do you add/drop partition without changing the SQL execution path? We do not have the time to reanalyze/dbms_stats the tables ,analyze takes hours and if SQL execution path changed, during these time, system is nearly unusable. I tried to import old statistics(dbms_stats.import_table_stats), but did not fix the problem. So, can you share your experience on managing partitioned table? Regards Zhu Chao www.cnoug.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni Josepsson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
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).
RE: RE: when do you use v$statname?
You are right, that v$statname is not needed when querying v$sysstat, since v$sysstat has name column. But, if you look at v$sesstat: SQL describe v$sesstat; Name SID STATISTIC# VALUE It doesn't have name column, and that's where you'll need v$statname to find the name of statistic. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 8:39 AM To: Multiple recipients of list ORACLE-L both store that info SQL desc v$statname Name Null?Type - STATISTIC# NUMBER NAME VARCHAR2(64) CLASS NUMBER SQL desc v$sysstat Name Null?Type - STATISTIC# NUMBER NAME VARCHAR2(64) CLASS NUMBER VALUE NUMBER -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: Happy Thanksgiving
Mladen, You should be writing specs for Oracle11 -:) Happy Thanksgiving everyone... Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mladen Gogala Sent: Wednesday, November 26, 2003 11:44 AM To: Multiple recipients of list ORACLE-L Happy Thanksgiving to everybody. As for maintaining the weight, that precisely is the problem. I am unable to lose weight, not to maintain it. There quite an abundance of me, I want to shrink. Does anyone know if oracle 10g has any options for shrinking DBAs? Something like ALTER DBA SET WEIGHT=240LBS BANK_ACCOUNT=10M DROP POINTS FROM DRIVERS LICENSE; On 11/26/2003 10:49:43 AM, Jamadagni, Rajendra wrote: Come to think of it, out fitness center has a special program ... it runs for 8 weeks and the aim is maintaining the weight ... no points for loosing any. bi-weekly weight watch and help to maintain weight is included. Happy Thanksgiving everyone ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 10:34 AM To: Multiple recipients of list ORACLE-L Guys, Happy Thanksgiving to everyone. May your bellies be full but your waistline not expand, may you enjoy the time with your family and friends and avoid any of the other drama Thanks so much to everyone for their help and camaraderie. Just enjoy yaself! -- ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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
ORA-28595 when calling external proc
Oracle 9.2.0.1 on Windows XP Im getting ORA-28595: Extproc agent: Invalid DLL Path when calling external procedure. I know, I specified the correct path to my dll, when issuing CREATE LIBRARY statement. I noticed (when switched from 8.1.5 on NT to 9.2 on Win2000) that dll path became case-sensitive (all of a sudden). But, thats not a problem in this case. Any thoughts? Btw, ORA-28595 is not in Error Messages manual. Igor Neyman, OCP DBA [EMAIL PROTECTED]
RE: xref table - design consideration
Could you use partitioned table with partitioning key type: Prospect/Customer, or Private Party, or Agency? In this case, if end-user knows type he is searching for, the query will go to specific partition, if not - it'll deal with the whole table. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Barbara Baker Sent: Monday, November 24, 2003 12:44 PM To: Multiple recipients of list ORACLE-L List: We're trying to design a CRM app. We believe we need 3 tables (Prospect/Customer, Private Party, and Agency) because those 3 kinds of (potential) customers have different attributes. The sales rep should know whether they're looking up cust, private party, or agency. But what if they don't? (They're sales, after all. What if the have a hangover?) For performance reasons, we'd prefer not to join all 3 tables for a lookup. I was thinking about 1 cross-reference table with the primary key from each of the 3 tables stored in one cross-ref table. Any way to keep such a table updated other than with a trigger? Any other ideas about how to do a quick lookup without 1 big join? In case you can't tell, db design is NOT my forte. Thanks for any ideas! Barb __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Initalization files, etc
But, in this case you can always start it specifying explicitly PFILE=... (instead of using SPFILE). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jared Still Sent: Monday, November 24, 2003 1:05 PM To: Multiple recipients of list ORACLE-L Mladen, I'm surprised at you. You didn't mention that using SPFILE makes it possible to put your database in a state that will prevent it from being started. ie. alter system set shared_pool=1024m scope spfile; I doubt many systems could handle that. Kirti and others convinced me to abandon spfile for now. Jared On Sun, 2003-11-23 at 23:44, Mladen Gogala wrote: On 2003.11.24 01:34, VirVit wrote: Hello. I'am a newbie in Oracle, so I have some questions to experts :) 1) What difference between SPFILE and PFILE? Big one. It's best described in the concepts manual as well as in the DBA guide. To make long story short, you can modify SPFILE from sqlplus which is not possible with PFILe. SPFILE is binary (quotes are here because it's not very binary, just a little), while PFILE is a standard text file. 2) Where by default Oracle search these files when instance starts? In %ORACLE_HOME%\DBS directory. 3) Where oracle searches datafiles by default? Nowhere. It doesn't search them. Locations of the control files are a mandatory parameter in the parameter file (PFILE and SPFILE) and control files contain, among other things, locations of all data, temporary and redo log files. 4) Sorry for lame questions :( No problem. There is plenty of literature available on OTN (technet.oracle.com) as well as in the bookshops just across the wire, like http://www.bn.com (Barnes Noble). Oracle has a site, maintained by the book writer of the year (no, it's not Cary Millsap, it's like NFL, the best team never wins. I have quite a few funny stories about NFL and being dead certain that I know which team will win, but this is not an NFL mailing list). The site is http://asktom.oracle.com. It's maintained by Tom Kyte who answers as many questions as humanly possible. Not all answers are useful, but all are factually correct. The good books to read are probably 1) Oracle9i DBA Handbook by Kevin Loney and Marlene Theriault (don't shoot, Rachel, it's French!) 2) Expert 1-on-1 , Tom Kyte 3) Practical Oracle 8i (one of the best DBA books ever written, even for a beginner, pertinent for version 9i as well), written by J. Lewis 4) Oracle Administratiion and Management by M. Ault (don't buy Oracle8i Admin. and Mgmt. as it is obsolete. the second edition is much better) There are other good books, which are not, in my opinion, suitable for a beginner. 1) Cary Millsap: Optimizing Oracle Performance (excellent book) 2) Gaja V., Kirti Deshpande, John Kostelac 101 Oracle Performance Tuning (really great book) 3) Guy Harrison Oracle SQL High Performance Tuning The mandatory parts of the DBA arsenal are also Robert Freeman's RMAN book, Don Burleson's Statspack book and last but least, Andy Duncan, Jared Still: Perl for Oracle DBAs (just for the lovers of the pathologically eclectic rubish lister) Oracle 9.2.0.4, win2k. It's not your fault. You may want to try with Linux, though. -- (VirVit) Oracle 9i DBA beginner -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VirVit INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: ORA-28595 when calling external proc
$ORACLE_HOME/bin is where I put my dll. Whats puzzling is that it works under 8.1.5/NT or 9.2/Win2000, but not under 9.2/XP. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Goulet, Dick Sent: Monday, November 24, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-28595 when calling external proc Igor, I can't remember exactly where I read it, but under Oracle 9i the DLL or SO file(unix) needs to be in either $ORACLE_HOME/bin or $ORACLE_HOME/lib only. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED] Sent: Monday, November 24, 2003 12:19 PM To: Multiple recipients of list ORACLE-L Subject: ORA-28595 when calling external proc Oracle 9.2.0.1 on Windows XP Im getting ORA-28595: Extproc agent: Invalid DLL Path when calling external procedure. I know, I specified the correct path to my dll, when issuing CREATE LIBRARY statement. I noticed (when switched from 8.1.5 on NT to 9.2 on Win2000) that dll path became case-sensitive (all of a sudden). But, thats not a problem in this case. Any thoughts? Btw, ORA-28595 is not in Error Messages manual. Igor Neyman, OCP DBA [EMAIL PROTECTED]
RE: ORA-28595 when calling external proc
Title: Message Thanks a lot! Adding EXTPROC_DLLS= in listener.ora made it working. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Monday, November 24, 2003 1:15 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-28595 when calling external proc This is new features of 9.2. The same on LINUX. oerr ORA 28595 28595, 0, Extproc agent : Invalid DLL Path // *Cause: The path of DLL supplied for the extproc execution is invalid. // *Action: Check if the DLL path is set properly using the EXTPROC_DLLS // environment variable. // add to listener.ora (PROGRAM = extproc) (ENVS=EXTPROC_DLLS=x:\path\a.dll;y:\path\b.dll) For more detail see 198523.1 External Procedure Calls and ORA-28595 using Oracle9i release 2 Alex. -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED] Sent: Monday, November 24, 2003 9:19 AM To: Multiple recipients of list ORACLE-L Subject: ORA-28595 when calling external proc Oracle 9.2.0.1 on Windows XP I'm getting ORA-28595: Extproc agent: Invalid DLL Path when calling external procedure. I know, I specified the correct path to my dll, when issuing CREATE LIBRARY...' statement. I noticed (when switched from 8.1.5 on NT to 9.2 on Win2000) that dll path became case-sensitive (all of a sudden). But, that's not a problem in this case. Any thoughts? Btw, ORA-28595 is not in Error Messages manual. Igor Neyman, OCP DBA [EMAIL PROTECTED]
RE: ORA-28595 when calling external proc
My bad. Thanks to everyone who replied. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Goulet, Dick Sent: Monday, November 24, 2003 3:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-28595 when calling external proc Well!! You didn't say itthat eXtreme Perversion was the OS. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED] Sent: Monday, November 24, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-28595 when calling external proc $ORACLE_HOME/bin is where I put my dll. Whats puzzling is that it works under 8.1.5/NT or 9.2/Win2000, but not under 9.2/XP. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Goulet, Dick Sent: Monday, November 24, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-28595 when calling external proc Igor, I can't remember exactly where I read it, but under Oracle 9i the DLL or SO file(unix) needs to be in either $ORACLE_HOME/bin or $ORACLE_HOME/lib only. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED] Sent: Monday, November 24, 2003 12:19 PM To: Multiple recipients of list ORACLE-L Subject: ORA-28595 when calling external proc Oracle 9.2.0.1 on Windows XP Im getting ORA-28595: Extproc agent: Invalid DLL Path when calling external procedure. I know, I specified the correct path to my dll, when issuing CREATE LIBRARY statement. I noticed (when switched from 8.1.5 on NT to 9.2 on Win2000) that dll path became case-sensitive (all of a sudden). But, thats not a problem in this case. Any thoughts? Btw, ORA-28595 is not in Error Messages manual. Igor Neyman, OCP DBA [EMAIL PROTECTED]
RE: what is oracle rdb?
RDB is rdbms (just another rdbms supported by Oracle corp.) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Ryan Sent: Monday, November 24, 2003 4:30 PM 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tony Johnson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: ora-600 / ora-00604 during migrate
What was wrong with export? How big the database is? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Thursday, November 20, 2003 3:45 PM To: Multiple recipients of list ORACLE-L -Oorspronkelijk bericht- Van: Jeroen van Sluisdam Verzonden: donderdag 20 november 2003 21:41 Aan: 'DENNIS WILLIAMS'; '[EMAIL PROTECTED]' Onderwerp: RE: ora-600 / ora-00604 during migrate Dennnis, Priority 2. I have tried export but after 3 days of experimenting And not getting results I switched to this scenario How could I persuade them to go to priority 1 if this isn't A production situation? Tnx, Jeroen -Oorspronkelijk bericht- Van: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Verzonden: donderdag 20 november 2003 21:24 Aan: '[EMAIL PROTECTED]' CC: '[EMAIL PROTECTED]' Onderwerp: RE: ora-600 / ora-00604 during migrate Jeroen - What priority did Oracle assign the TAR? Given the seriousness of your situation, you should get it rated a priority 1. Is there any possibility you can export/import your data instead of performing a migration? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 20, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Hi, I'm experiencing an ora-600 during migration of a 7.3.4 to 9.2.0.4 Error occurs during issueing alter database open resetlogs migrate All previous steps (all according to the migrate manual): Migprep Mig Alter database convert Succeeded successfully Details: HP-UX 11.11 Source-DB 7.3.4.5 (HP11 version) Target 9.2.0.4 Error in alert-file: Errors in file /var/opt/oracle/product/admin/VU_2/bdump/vu_2_smon_8589.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist Thu Nov 20 16:42:11 2003 ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY; Thu Nov 20 16:42:11 2003 ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY; Thu Nov 20 16:42:11 2003 ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY; Thu Nov 20 16:42:11 2003 Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_9760.trc: ORA-00600: internal error code, arguments: [16608], [2], [0], [0xC000234BAB80], [], [], [], [] I could find one similar notice on metalink but it didn't describe what they did to resolve this. I entered a tar, but probably too late for today. I need help urgently because this is causing Major problems in our timeschedule for testing and going live as planned in 2 weeks. Hope you can help soon, Regards, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeroen van Sluisdam INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: New Virus alert from paypal
I received such e-mail from PayPal, didn't bother to follow instructions, just deleted it. I'm not using PayPal anymore. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Ron Rogers Sent: Wednesday, November 19, 2003 10:40 AM To: Multiple recipients of list ORACLE-L List, Any one else get word of this new virus? Ron Mar-D Greer 11/19/2003 9:34:44 AM There is a new virus that has surfaced since last week. This virus reports that users need to update their PayPal accounts. Do not open this, delete the e-mail as PayPal has not issued this e-mail and the e-mail itself contains a virus. The actual e-mail reads as follows: (Found virus WORM_MIMAIL.J in file www.paypal.com.pif) ** Dear PayPal member, We regret to inform you that your account is about to be expired in next five business days. To avoid suspension of your account you have to reactivate it by providing us with your personal information. To update your personal profile and continue using PayPal services you have to run the attached application to this email. Just run it and follow the instructions. IMPORTANT! If you ignore this alert, your account will be suspended in next five business days and you will not be able to use PayPal anymore. Thank you for using PayPal. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Re[2]: var source_data varchar2(12)
Since making love is simpler, should they start with outsourcing it? -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mladen Gogala Sent: Tuesday, November 18, 2003 12:44 PM To: Multiple recipients of list ORACLE-L On 11/18/2003 11:54:41 AM, Bellow, Bambi wrote: [EMAIL PROTECTED] uname -a dgux orion R4.20MU06 generic AViiON PentiumPro [EMAIL PROTECTED] make love Make: Don't know how to make love. Stop. Raj -- That's fbulous! Thanks, Bambi. -- Actually, it really is. It shows us that we are still very far away from Ellison's dream of a DBA in a box. If computers do not know how to make love, how are they going to do more complicated things, like tuning an oracle instance, for example? This tells us that our jobs are safe, unless they get ousourced overseas. Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: bug info, not displayed!!
Enough already! Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- rahul Sent: Wednesday, November 12, 2003 9:30 AM To: Multiple recipients of list ORACLE-L the bug no. is correct, it relates to forms 6i (running on web) is not able to connect to the DB when using ASO, and third party authentication (securID) we have configured sucureID authentication for our DB, but out back office app using forms6i does not connect to the DB when this is configured.. when i remark the lines in sqlnet.ora, it can connect. there is so little information regarding using web forms, and third party authentication , methods.. On Wed, 12 Nov 2003 04:34:40 -0800, DENNIS WILLIAMS [EMAIL PROTECTED] wrote : Rahul Are you certain you have the correct number? What is the bug about -- you may be able to search using alternate words. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 12, 2003 6:30 AM To: Multiple recipients of list ORACLE-L List, i need some help on this... i am trying to get information on this but , and the patches available, the bug no is 1809113. but metaling does not allow to display this bug !!! even when i use my PARTNERS login !!! a.. The bug is not classified as publicly accessible (non-public). b.. The bug is filed under a product for which you have no license. To view your product licenses go in your User profile and select the Show License option. c.. The bug was filed before June 1996. Only bugs that were opened after this date are accessible on this server. d.. The bug number does not exist (it was referenced incorrectly). i need info on this but to patch my forms6i server using ASO and secureID authentication. i would appreciate if someone can give me info on this bug. -rahul -- -- -- -- The information contained in this email and its attachments if any may contain privileged and confidential information intended only for the attention of the recipient(s) specified. If you are not a recipient , any forwarding , disclosure , photocopying , distribution or use of the information in any way is prohibited . If you have received this email in error , please email us immediately on [EMAIL PROTECTED] or contact us on (62 21) 522 8775. -- -- -- -- - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul sharma INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: How do you genrate primary keys?
Oracle has RETURNING clause for insert. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Stefan Jahnke Sent: Monday, November 10, 2003 10:34 AM To: Multiple recipients of list ORACLE-L Hi Rachel In most Java applications I've seen so far, the issue of caching rows by an id, which is usually the primary key, arises. JDBC v3 implements a method which allows you to return a key after the insert completed (for example MS SQL Server can do this). How do you get a hold of the PK, after you inserted the key via trigger without an extra roundtrip ? Regards, Stefan -Ursprüngliche Nachricht- Von: Rachel Carmichael [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 5. November 2003 14:34 An: Multiple recipients of list ORACLE-L Betreff: Re: How do you genrate primary keys? At one site I worked at, the programmers insisted on using Java milliseconds as the primary key -- so that they wouldn't have to hit the database twice (once to get the sequence number, once to insert the row). They swore up, down and six ways from Sunday that there could never, ever, EVER be a collision. After we had collisions in development, we switched to sequences (one per table), with a trigger to populate the field on insert so that they wouldn't have to make the second round-trip. --- Jonathan Gennick [EMAIL PROTECTED] wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? 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). __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: How do you generate primary keys?
True, Just in this case insert was a point of discussion. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jamadagni, Rajendra Sent: Monday, November 10, 2003 11:34 AM To: Multiple recipients of list ORACLE-L ... and for updates,delets as well ... Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, November 10, 2003 11:24 AM To: Multiple recipients of list ORACLE-L Oracle has RETURNING clause for insert. Igor Neyman, OCP DBA [EMAIL PROTECTED] ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Great story! Metalink down caused inhouse crash
That must be so true! Great story! Thank you for sharing. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jay Hostetter Sent: Friday, November 07, 2003 1:29 PM To: Multiple recipients of list ORACLE-L No wonder MetaLink is slow - it's too busy serving up graphics to customer installations. [EMAIL PROTECTED] 11/07/03 11:44AM At my last project, we were putting in OraFin and we had a team of Oracle consultants doing up the front-end setup stuff (populating screens, etc). Well, one day, I get this panicked call that the system was down. Well, of course, that was silly. The system was up just fine, thank-you-very-much, and the database was fine, and even my version of the app was just fine-and-dandy. This reminded me of the old lightbulb joke (I got one over here just like it and it works fine for me), so I tried to log in as the user from my app, and it worked fine. I went upstairs, and sure-nuf, she couldn't log in and couldn't log in. She *swore* she had never logged onto Unix and had never changed *anything* except data, and it was working fine til 9:15 then boom! Well, after awhile, I ran out of ideas and tried to log a TAR, and, as you can tell from the subject line, Metalink was down. It was down all morning, and started coming up slowly around 1pm. When I was finally able to enter the text of the TAR, I was just about to log the TAR and my phone rang with a Gee, you fixed it, thank you!. Well, *that* was a bit too much of a coincidence for me. Well, the upshot of the whole thing was that, for whatever reason, in their setups, they wanted the actual Oracle splash page to come up instead of the one that ships with OraFin and one of their in-house experts who had since disappeared had hardcoded in the server that hosted Metalink into the app. And the really great thing was when I took out the reference to that machine leaving the default splash screen as was, they didn't even notice. What a day that was! Bambi. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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).
RE: Re[2]: How do you genrate primary keys?
What about doing it in one step? Declare lCounter int; Begin UPDATE counter_table SET counter := counter+1 WHERE counter_name = 'table name' RETURNING counter INTO lCounter; End; / Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jonathan Gennick Sent: Thursday, November 06, 2003 8:59 AM To: Multiple recipients of list ORACLE-L Wednesday, November 5, 2003, 9:24:41 AM, Hemant K Chitale ([EMAIL PROTECTED]) wrote: HKC 1. Hit a table that keeps a counter. HKC Used to be a mechanism in the Oracle5 days [If I remember correctly, HKC Sequences came in Oracle6]. Issues were with locking the single HKC record used as the generator or scanning for the max(value) of the HKC key. HKC Not quite sure I understand how you encountered concurrency issues, though. My concurrency issues probably boil down to the locking business. The app I'm thinking of originally did something like: SELECT counter INTO :1 FROM counter_table WHERE counter_name = 'table name'; ...some app code goes here... UPDATE counter_table SET counter := counter+1 WHERE counter_name = 'table name'; Well, it all worked fine in single-user modegrin. But it was easy enough for me to sit down in front of two computers, create two new records, press SAVE at the same time, and cause two sessions to grab the same key value, because they would both issue the SELECT before either one got around to the UPDATE. I couldn't screw things up consistently, but just by hitting the SAVE button at the same time I could screw things up often enough to make the problem obvious. Maybe there's a way to lock the table, to make the above approach work. In my case, I didn't bother trying to find that solution. Once I did my little demo, it was easy enough to convince the project manager that we should switch to using Oracle sequences. 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: 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).
nologging for IOT
As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] -- 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).
RE: nologging for IOT
Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ 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: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: nologging for IOT
Unfortunately my source is another table. By the way (btw.), will sqlldr direct=true work with IOT? m.b - may be. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 12:25 PM To: Multiple recipients of list ORACLE-L I see. Sorry for misreading. How about direct path load? sqlldr direct=true. But this means your data source is on the filesystem. What is M.b.? Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ 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: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). __ 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: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: nologging for IOT
Well, that's not a trick -:) I wouldn't be asking, if I had enough space for both table and index. It's a huge narrow table, which never gets updated (only inserts/deletes) - perfectly fits IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Khedr, Waleed Sent: Wednesday, November 05, 2003 2:19 PM To: Multiple recipients of list ORACLE-L A trick, use a regular table and create an index that has all the needed columns. Waleed -Original Message- Sent: Wednesday, November 05, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ 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: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: converting sybase stored procedure into oracle sp
Look for Migration Workbench on OTN. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Saminathan Sent: Monday, November 03, 2003 3:10 PM To: Multiple recipients of list ORACLE-L Hi List, Does oracle provide any utility to convert sybase stored procedure into oracle stored procedure. In OTN I found some document which docs about conv72 which was distributed with oracle 7.2v. I dodn't see nything similar in oracle 8i or 9i. Does anyone have any idea or experience on this? Any 3rd part tool or something? Thanks Sami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saminathan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Redo log corruption
They get corrupted like any other OS files (hardware and/or OS problems). Have the second destination for archive logs. This way, if one gets corrupted, the second one could be used. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Shibu MB Sent: Friday, October 31, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Hi all , While applying archive logs to my standby database i got theerror ORA-00353: log corruption near block 207725 change 111482169731153 time 10/25/2003 12:14:01 Can anybody tell me how archive logs can get corrupted ?? How can i avoid oracle from generating corrupted archive logs ??How can i check the health of archive logs ?? Please help !. TIA Shibu Alphawest Disclaimer --- If this communication is not intended for you and you are not an authorised recipient of this email you are prohibited by law from dealing with or relying on the email or any file attachments. This prohibition includes reading, printing, copying, re-transmitting, disseminating, storing or in any other way dealing or acting in reliance on the information. If you have received this email in error, we request you contact Alphawest immediately by returning the email to [EMAIL PROTECTED] and destroy the original. This email is confidential and may contain privileged client information. Alphawest has taken reasonable steps to ensure the accuracy and integrity of all its communications, including electronic communications, but accepts no liability for materials transmitted. --- DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- 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).
RE: Perm job opening in MA
LOL! Mladen, I think you are missed on off-topic list -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mladen Gogala Sent: Tuesday, October 28, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Don't get me wrong, I've recently changed positions and am not interested, but what are phone skills? I know how to use a phone, and I can do it in yoga position with one hand tied behind my back. I've never used a phone under water or in space. I use it on a regular basis while commuting or in restaurants. It helps tremendously with finding a free seat. As for the communication over the phone, you should hear my inventive use of the English language when I'm talking to telemarketers. Creative assumptions about their ancestry and its position on the evolution tree and sexual preferences of their parents are the most common opener after which I usually take the poor soul to the place where no telemarketer has gone before. Do I have the right idea about the phone skills or you have in mind some extremely innovative use of phone which would be inappropriate for a good catholic like me? On 2003.10.28 20:09, John Spencer wrote: I hope I am not breaching any rules, but I would like to make it public that I am currently trying to fill a temp to perm position for a Sr level Oracle/customer support person in Massachusetts. This person must have strong Oracle and Sun Solaris skills and some Java (J2EE and Java beans) experience. Must have excellent phone skills and the ability to work with customers on installs and other issues. Experience must include stored procedures and triggers. Local candidates only please. Please reply directly to me at [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Thanks again for your time. Regards, John Spencer Sr. Staffing Consultant ProStart Inc. 603-893-7772 ext 45 603-893-7704 fax mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: Oracle pricing ain't going down
IDENTITY does not have exactly SEQUENCE functionality. It is a property, you can assign to a column. And it has buggy implementation, I've seen duplicate values (not sure about the latest version). So be careful with this feature. As for clustered indexes - you are correct. Actually SQL Server (Sybase) had them before Oracle implemented IOTs. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Abey Joseph Sent: Friday, October 24, 2003 1:44 PM To: Multiple recipients of list ORACLE-L My workplace is going in the same direction as David Mitchell's. Our OLTP systems are Oracle, basically everything else is being (or being considered) migrated to MSSQL2000. I am not that familiar with SQL Server, but I believe SQL2000 has sequences. I think MS calls it identity. I think MS also has IOT, which they call clustered indexes. MS might even have function based indexes with SQL2000, but not very sure. Anyone care to comment? Abey. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:19 PM -Original Message- From: Ryan [mailto:[EMAIL PROTECTED] Sent: Friday, 24 October 2003 12:44 To: Multiple recipients of list ORACLE-L Subject: Re: Oracle pricing ain't going down what is MSEE lacking in? sound of can of worms opening Here's a start. MSSQLServer EE has ... No bitmap indexes, no partitioned indexes, no function-based indexes, no domain indexes, no reverse key indexes, no object tables, no before triggers (can be kludged, not pretty), no multiple actions per trigger event, no 3rd-party language support a la Oracle's JVM and pro*... modules, no built-in OLAP (it's a weird bolt-on), no control over extent size, no control over block size, no star query optimisation, no sequences, no synonyms, no packages, no structured exception handling in stored proc language (TSQL), no MINUS union operator, no multiplexing or mirroring of log files, no cyclical log management, no escalation-free locking, no index organised tables. (Working with both every day, do you get the feeling I've been asked this before? :-)) Half of those things are available in Oracle SE One :-) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Abey Joseph INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Your new book
Is it (review) as good as Mladen's? -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Freeman Robert - IL Sent: Tuesday, October 21, 2003 2:54 PM To: Multiple recipients of list ORACLE-L Well I got the honor of being the first to publish a review on Amazon for Cary's book it is a good read! Robert -Original Message- To: Multiple recipients of list ORACLE-L Sent: 10/21/2003 2:24 PM I'll try to correct spelling errors before I post it to the Amazon, but I will do it, despite the fact that I'm not very fond of Amazon. On 10/21/2003 03:09:32 PM, Michael Milligan wrote: MLaden, Thank you very, very much for a great review. I hope you'll post that to Amazon. As a matter of fact, I enjoy queuing theory. I remember almost buying a book called Practical Queuing Analysis by Mike Tanner. I was a biology major in college, so I may muddle through the math, but it'll be good for me anyway. Thanks again for taking the time to write that great review, Michael Milligan Oracle DBA Ingenix, Inc. 2525 Lake Park Blvd. Salt Lake City, Utah 84120 wrk 801-982-3081 mbl 801-628-6058 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Cache a table
Thanks for correction.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Tim Gorman
Sent: Tuesday, October 21, 2003 3:34 PM
To: Multiple recipients of list ORACLE-L
The switch being referred to occurred with 8i, where Oracle
went to the touch-count algorithm. See
http://www.orapub.com/cgi/genesis.cgi?p1=subp2=papers_main
for paper #136.
I guess most frequently used is a good way to describe it
-- nice choice of words!
Tom,
I think you are correct, if we are talking about Oracle 9,
where oracle switched from most recently used to most
frequently used algorithm.
But, prior to that, it seems possible to think of
scenarios, where cache would be helpful. May be, that's
one of the reasons, why oracle changed algorithm.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Mercadante, Thomas F
Sent: Tuesday, October 21, 2003 2:09 PM
To: Multiple recipients of list ORACLE-L
I always wondered why Oracle thought this was a useful
table attribute.
My gut feeling is that it is an extra that does little.
For example, say we want to keep a code table in memory
because it is constantly being hit for column verifiction.
By definition, if a table is
constantly being queried, it's segments will be in memory
because they never
age out. That sounds like cacheing to me.
And then I remember a specific piece of Oracle
documentation saying that,
even though we may mark a table to be cached, it *still*
may be aged out
if memory is needed for other data blocks.
Like I said, sounds a little like here you have it, and
here you don't.
I'm sure that my impression is wrong and someone will
correct me. But I doubt I will use the CACHE option
anytime soon.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, October 21, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L
My understanding is that the KEEP and RECYCLE Pools are
just 'names' in the
sense that they are placeholders for assigning an object
to the BUFFER_POOL
{ KEEP | RECYCLE | DEFAULT } clause, and that the 'aging'
algorithms for KEEP and RECYCLE are exactly the same.
Assigning a specific object to one of
these named pools segregates objects by
retention-requirements. Thus, KEEP
does not imply a different treatment of the Buffers -
rather it makes sure
that objects that you would like to 'keep' around are
specifically directed
to a common pool and vice versa
Does anyone have additional information that can verify
this? I heard this
from a knowledgeable Oracle instructor in an Oracle Tuning
training Class.
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Disappointment is inevitable, but Discouragement is
optional!
** The opinions and facts contained in this message are
entirely mine and do
not reflect those of my employer or customers **
-Original Message-
From: Tim Gorman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 6:59 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Cache a table
Good points, Arup.
Actually, I would argue that there is better reason to
consider using the
RECYCLE pool than to consider how to cache tables or
use the KEEP pool.
The advantage of effective use of the RECYCLE pool is
better behavior in the
rest of the Buffer Cache...
When you think of it, the default DEFAULT buffer pool and
the KEEP pool have
essentially the same purpose: long-term caching of
blocks. What keeps them
from accomplishing that mission but objects whose blocks
waste space and
energy cycling into and out from the Buffer Cache?
It's kind of like a school teacher admonishing his/her
class that a troublesome few have ruined things for
everybody. When I was in school,
troublemakers were segregated from the rest of the
class, sometimes cumulatively into a separate classroom
(we called ourselves the mentals
and read Mad magazines all the time, which accounts for a
lot, then and now). Nowadays, I'm sure that such a
measure isn't considered for fear of
lawsuit for hurting the self-esteem of the poor dears.
Never mind the
confusion between the useless feel-good phrase
self-esteem and the more
useful and thought-provoking phrase self-respect. Oh
well, better stop
now...
Anyway, marking a table as CACHE and placing it in a KEEP
buffer pool which
is large enough to accommodate all of the used blocks is
the closest thing
to pinning a table into the Buffer Cache as you'll get,
as Arup described.
Of course, there is little benefit from such a move, as
Arup also mentioned.
Just yesterday, I visited a customer who had a series of
SQL statements that
were executing some 10 million times _each_ per day,
averaging about 20-1500
LIOs per execution. They each had a 99.999%
buffer cache hit ratio, yet strangely enough the
performance on the server is absolute crap
because the eight brand-new
RE: How To Pull Second Row from 100 ROWS
SELECT blah, blah, blah... FROM (SELECT blah, blah, blah..., ROWNUM r FROM table_name WHERE ...) WHERE r = 2; No guarantees, that you will be always getting the same row (depending on in-line query). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Muqthar Ahmed Sent: Tuesday, October 21, 2003 3:44 PM To: Multiple recipients of list ORACLE-L Hi, Is there a way to pull ONLY 2nd row from the selected rows. Thanks Muqthar Ahmed DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: job opportunity in Dallas
2% - that's all we get? -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mladen Gogala Sent: Monday, October 20, 2003 2:59 PM To: Multiple recipients of list ORACLE-L Speaking of a tight market http://update.informationweek.com/cgi-bin4/DM/y/hcut0BdZe50V20CBbm0Aj On 10/20/2003 02:59:25 PM, [EMAIL PROTECTED] wrote: Hey guys - this is funny. Don't respondit is actually a trick...various employers are using this service to determine how loyal their current DBA's are while the market is tight Just kidding -Original Message- Sent: Monday, October 20, 2003 1:54 PM To: Multiple recipients of list ORACLE-L I wonder... in the VMS world, they were executables, so is this a VMS job? Would it be '.o'ers in Unix? Or batchers in DOS? Bambi. -Original Message- Sent: Monday, October 20, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Hummm, sounds interesting. Been doing that on Duhvelopers for the last 10 years! *-) Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, October 20, 2003 12:29 PM To: Multiple recipients of list ORACLE-L Executioner. On 10/20/2003 12:14:35 PM, Stefick Ronald S Contr ESC/HRIDD wrote: What kind of job? Mail Carrier, garbage collector, retail clerk Details. Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's
RE: Deleting from Global temporary Tables
Title: Deleting from Global temporary Tables
Raj,
Global temporary tables are session-specific.
They have global definition, but each session deals with its
own incarnation of the table.
So, the question is: why would you DELETE
FROM TEMP_SCHEDULE WHERE session_id = USERENV('sessionid')?
Data in temp table will be deleted anyway
automatically at the end of transaction or session (your choice).
Or, I dont understand something here?
Now, trace shows some physical reads. What is
your sort_area_size compared to the average size of
the temp table?
Also, just recently it was a discussion
about temp tables over-using RedoLog (specifically in 9.2 version).
Trace shows waits on undo extension, m.b. there is also problem/bug
with temp tables using undo tablespace?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni,
Rajendra
Sent: Wednesday, October 15, 2003
7:45 AM
To: Multiple recipients of list
ORACLE-L
Subject: Deleting from Global
temporary Tables
Any specific reason what
this should be costly ?? Look below for a snippet from a tkprof analysis ... db
is 9202, 2 node RAC, everything is LMT and TEMP TS is 16GB
Trace file:
abc1_ora_9879592_crenshaj_1014.trc
Sort
options: prsela fchela exeela
count
= number of times OCI procedure was executed
cpu
= cpu time in seconds executing
elapsed
= elapsed time in seconds executing
disk
= number of physical reads of buffers from disk
query
= number of buffers gotten for consistent read
current
= number of buffers gotten in current mode (usually for update)
rows
= number of rows processed by the fetch or execute call
DELETE FROM TEMP_SCHEDULE
WHERE
session_id = USERENV('sessionid')
call
count cpu
elapsed disk
query current rows
---
-- -- -- -- --
--
Parse
1 0.01
0.00
0 0
0 0
Execute
52 111.19
128.72
94 10498
12989188 35211
Fetch
0 0.00
0.00
0 0
0 0
---
-- -- -- -- --
--
total
53 111.20 128.73
94 10498
12989188 35211
Misses in library cache
during parse: 1
Optimizer
goal: CHOOSE
Parsing
user id: 47 (recursive depth: 1)
Rows
Row Source Operation
---
---
0 DELETE (cr=10498 r=94 w=0 time=128723041 us)
35211 TABLE ACCESS BY INDEX ROWID TEMP_SCHEDULE (cr=9329 r=56 w=0
time=5507639 us)
35211 INDEX RANGE SCAN TEMP_SCHED_INDX (cr=596 r=8 w=0
time=120949 us)(object id 228499)
Elapsed times include waiting
on following events:
Event waited
on
Times Max. Wait Total Waited
Waited
--
global cache open
x
229 0.00
0.05
latch
free
88
0.65 7.41
buffer busy
waits
28 3.00
12.73
log file switch
completion
2
0.00 0.01
undo segment
extension
2543495
0.00 1.59
enqueue
24
0.00 0.00
global cache s to
x
8 0.00
0.00
KJC: Wait for msg sends to
complete
30
0.00 0.00
db file sequential
read
94
0.02 0.43
Needless to say this is a
heavily used table, heavy inserts and deletes, no updates. Any tricks to
insert/delete large number of rows from GTTs? All inserts are bulk inserts, but
not delets.
TIA
Raj
Rajendra
dot Jamadagni at nospamespn dot com
All
Views expressed in this email are strictly personal.
QOTD:
Any clod can have facts, having an opinion is an art !
**
This e-mail message is confidential, intended only for the named recipient(s)
above and may contain information that is privileged, attorney work product or
exempt from disclosure under applicable law. If you have received this message
in error, or are not the named recipient(s), please immediately notify
corporate MIS at (860) 766-2000 and delete this e-mail message from your
computer, Thank you.
**5
RE: Passed Net8 OCP Exam
Congratulations, Dennis! Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- DENNIS WILLIAMS Sent: Thursday, October 16, 2003 2:24 PM To: Multiple recipients of list ORACLE-L I just passed the Net8 Administration OCP exam. This was my last exam, so I suppose I am now an OCP for Oracle8i. Thanks to everyone for their helpful tips. Some people said (or implied) that this is a very easy exam. I would dispute that. I found it just like the other exams, in that if you have quite a bit of on-the-job experience in the area, then it is easy. If not, you'll have to study more. In my case, - I don't like networking, and hate to drop everything to configure or debug someone's tnsnames.ora file. - Never had an Oracle networking class. - Our site has only needed local naming, so that is pretty much all my networking experience. Found all the questions related to local naming pretty easy. Never used Oracle Names, Connection Manager, MTS, Advanced Security. With the trend toward application servers with their connection pooling feature, I don't foresee us using any of those Oracle tools. I may need to use LDAP in the future, but that wasn't covered in the Oracle8i Net8 exam My goal was just to study and practice enough to practice the exam, and I accomplished the goal. Not a very lofty goal, but sometimes you do what you gotta do. Thanks everyone for the helpful suggestions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: space taken up by number data types
Yes, T.Kyte is still right J Space is not reserved for NUMBER type, it is variable length column. Amount of space occupied depends on particular number, which is stored in the column. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ryan Sent: Tuesday, October 14, 2003 4:10 PM To: Multiple recipients of list ORACLE-L Subject: space taken up by number data types I could have swarn I read that Precision with number data types effects how much space is reserved in the database. So number(38) and number(3) reserve different amounts of space. Here is a link from Tom Kyte in 1998 saying the opposite. Is what he says still true? http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=355e25d0.17874392%40192.86.155.100rnum=1prev=/groups%3Fas_q%3Dnumber%2520datatype%2520storage%26safe%3Dimages%26ie%3DUTF-8%26oe%3DUTF-8%26as_ugroup%3D*oracle*%26lr%3D%26num%3D50%26hl%3Den
RE: Hiding the names of Web Toolkit procedures in Browser Address boxes
Will your customer allow displaying a synonym instead of the stored procedure name? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Melanie Caffrey Sent: Monday, October 13, 2003 1:04 PM To: Multiple recipients of list ORACLE-L boxes Hello Listers, I'm trying to work out a solution for a client that I've not been able to find any substantial documentation for. Anybody familiar with the 9iAS and the PL/SQL Web Toolkit out there? I thought I was, but a client of mine has come up with a very interesting (and, I believe, very reasonable) request. They'd like to ensure that, say, when a user clicks on a hyperlink, for instance, the name of the subsequently called procedure is not displayed in the address line of the browser; in other words, no visibility of the name of the called procedure to the user using the Web app. There is a lot of documentation on aliasing directory paths in Apache, but not procedure names, per se. Particularly since, naturally, the Apache server is open source. The solution I use must be Oracle-specific (I ... er ... believe). I could throw up a JS window that temporarily covers the address window, but that is not really an ideal solution. Has anyone ever tried this before? TIA, Melanie Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Melanie Caffrey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Tools to Execute Stored Procedures in Debug Mode !
What she said. Very good tool. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Monday, October 13, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Subject: Re: Tools to Execute Stored Procedures in Debug Mode ! Try PL/SQL Developer from Allround Automations. The cost is modest and the tool was developed just for Oracle. Vicki Pierce Database Administration x2401 Rama, Shreekantha (K.) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/13/2003 01:19 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Tools to Execute Stored Procedures in Debug Mode ! Hi List, I am looking for a tool to debug the sql procedures. I tried with TOAD, but at times this is not working.. Any suggestions on any other tools would be great help.. Shreekanth -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rama, Shreekantha (K.) INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 the names of Web Toolkit procedures in Browser Address boxes
Melanie, I haven't used Web Toolkit. Just assumed, that if it includes db call in address line, than procedure name could be hidden, if application calls synonym created for stored procedure. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Melanie Caffrey Sent: Monday, October 13, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Address boxes Igor, Possibly. I've not tried this approach. Have you? Is the synonym, in fact, then displayed as opposed to the procedure name, in your address line? I am temporarily away from the testing site or I would test this out straight away. Thank you for your feedback. Cheers, Melanie -Original Message- Igor Neyman Sent: Monday, October 13, 2003 2:19 PM To: Multiple recipients of list ORACLE-L Address boxes Will your customer allow displaying a synonym instead of the stored procedure name? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Melanie Caffrey Sent: Monday, October 13, 2003 1:04 PM To: Multiple recipients of list ORACLE-L boxes Hello Listers, I'm trying to work out a solution for a client that I've not been able to find any substantial documentation for. Anybody familiar with the 9iAS and the PL/SQL Web Toolkit out there? I thought I was, but a client of mine has come up with a very interesting (and, I believe, very reasonable) request. They'd like to ensure that, say, when a user clicks on a hyperlink, for instance, the name of the subsequently called procedure is not displayed in the address line of the browser; in other words, no visibility of the name of the called procedure to the user using the Web app. There is a lot of documentation on aliasing directory paths in Apache, but not procedure names, per se. Particularly since, naturally, the Apache server is open source. The solution I use must be Oracle-specific (I ... er ... believe). I could throw up a JS window that temporarily covers the address window, but that is not really an ideal solution. Has anyone ever tried this before? TIA, Melanie Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Melanie Caffrey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Melanie Caffrey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: Using ' in Update statement
Rick, What's wrong with reading the manuals before asking the list? In the long run it will definitely help more than getting ready solution from someone on the list. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Friday, October 10, 2003 10:40 AM To: Multiple recipients of list ORACLE-L Ahhh!!! there is no elite and the question was trivial. The great and powerful has spoken, perhaps all questions should be filtered your way for classification. I can assure nothing on this forum is worthy of becoming Testy, merely trying to provide a solution for someone who needed help and of course one must stand their ground when necessary. Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: RE: Using ' in Update statement [EMAIL PROTECTED] .com 10/10/2003 10:19 AM Please respond to ORACLE-L There is no elite here, but this was a trivial question which I answered by listing manuals in which the answer could be found. I was asked for clarification of my reply, so I provided the clarification. No need to get testy about it. On Fri, 2003-10-10 at 08:54, Mercadante, Thomas F wrote: Naaa. He's an equal opportunity ball-buster. -Original Message- Sent: Thursday, October 09, 2003 5:54 PM To: Multiple recipients of list ORACLE-L Hamid started his question How can I user comma ' in my update statement? I am merely correcting his mistake and then providing a solution. Wow, what a concept on a Oracle Listserv site design for that very purpose. Just curious, Would you have answered in the same way if it had been asked by one of the elite? Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 05:09 PM Please respond to ORACLE-L I didn't mean quote, I meant RTFM. On Thu, 2003-10-09 at 16:34, [EMAIL PROTECTED] wrote: I assume you mean quote update tablea set fielda =' james'ste Camp 'first,'sec' ' update tablea set fielda =' james''ste Camp ''first,''sec'' '; Just use 2 single quotes Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 02:29 PM Please respond to ORACLE-L It's in the documentation. Start with the concepts manual, then SQL*Plus manual and SQL reference. I'm sure that you'll run across the answer because that's where I have found the answer to the very same question. On Thu, 2003-10-09 at 13:59, Hamid Alavi wrote: List, How can I user comma ' in my update statement? update tablea set fielda =' james'ste Camp 'first,'sec' ' Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET
RE: Redos gone crazy--a job for audit?
Why? Did you have bad experiences with temp tables? I thought, using temp tables should reduce amount of redo. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? --- Daniel Fink [EMAIL PROTECTED] wrote: Barb, Even if you can't find the user, you can still find the session info and run a trace on the session. If it is consistent, you should be able to trace for a short amount of time and retrieve the statements that are generating redo. Then you can go back to the vendor and say This statement (update emp set empno = empno) is generating 3g of redo per day and it is not performing any work. Please consider this a P1 bug and we need a fix in 10 days. It is especially valuable if you can trace the 'old-good' app and compare it with the 'new-bad' app. Dan Barbara Baker wrote: Dan: Thanks for this -- I'll definitely tuck this away for future reference. Sadly, it's not going to help this time. I don't have a user generating redo, I have an application running amuck. The users (reporters) never log into the database. Some service (Solaris high availability service, I believe) logs a database user on 20 times, then buffers requests from the HA service to the database. A minute or two later, it logs the 20 sessions out and logs in 20 more. Between around 5:30 am and 3:00 am the following day, the database is rolling a new redo log about every 16 minutes. Pretty much new log file every 16 minutes like clockwork. Between 3:00 and 5:30, the HA service is disabled and some kind of maintenance is running. The entire database is about 4100 megs. We're generating more than 3 gigs of redo per day. I sure would like to know what's in those redo logs. Thanks for the help! Looks like another beautiful weekend to hang out on top of a mountain. Did you get to see the leaves turning this year?? Barb begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Redos gone crazy--a job for audit?
Boris, I used your script (well, almost: in your script you create temporary global table, but never use it, so, I modified it). And it shows redo size increase substantially lower (~7 times) in case of using temp table. But, I was running script on 8.1.5. When, running on 9.2 it appears, that you are correct: temp table generates much more redo than permanent table. Both results are shown below: Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options PL/SQL Release 8.1.5.0.0 - Production SQL create table t6 (i int) ; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 8780 SQL SQL insert into t6 select obj# from sys.obj$ where rownum = 1; 1 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 157964 SQL = The redo size increase with permanent table is: 157964 - 8780 = 149184 SQL create global temporary table t7 (i int) on commit 2 delete rows; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 162060 SQL SQL insert into t7 select obj# from sys.obj$ where rownum = 1; 1 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 189264 SQL = The redo size increase with global temporary table is: 189264 - 162060 = 27204 Now, on 9.2: Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL create table t6 (i int) ; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 7204 SQL SQL insert into t6 select obj# from sys.obj$ where rownum = 1; 9038 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 150252 SQL = The redo size increase with permanent table is: 150252 - 7204 = 143048 SQL create global temporary table t7 (i int) on commit 2 delete rows; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 154032 SQL SQL insert into t7 select obj# from sys.obj$ where rownum = 1; 9039 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 1287624 SQL = The redo size increase with global temporary table is: 1287624 - 154032 = 1133592 which is quite different from the testing results under 8.1.5. I don't have access to Metalink right now to check Bug# 2874489. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Igor, Try running the following test: create table t6 (i int) ; create global temporary table t7 (i int) on commit delete rows; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Note the value here insert into t6 select obj# from sys.obj$ where rownum = 1; commit; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Compare the two. In my case it's almost 10-fold increase in the amount of redo generated. We ran into this not too long ago. Bug# 2874489. Fixed in 10.1.0.1 with some backports available for 9.2.0.3/4 on **some** platforms Note: Bug above doesn't affect direct path load Thanks, Boris. --- Igor Neyman [EMAIL PROTECTED] wrote: Why? Did you have bad experiences with temp tables? I thought, using temp tables should reduce amount of redo. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from
RE: Using ' in Update statement
Do you really not see the difference between the question we are discussing here and the ones Tom Kyte is answering? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Friday, October 10, 2003 2:54 PM To: Multiple recipients of list ORACLE-L You guys are mean !! Tom Kyte would have given me 10 ways of writing the statement, would have traced every one of them under different versions and on different platforms, pointed out the number of logical reads, elapsed time, et all, and told me which one is better. Regards Raj [EMAIL PROTECTED] disys.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Using ' in Update statement ity.com 10/10/2003 01:54 PM Please respond to ORACLE-L What he said. Mladen Gogala [EMAIL PROTECTED] To:Multiple recipients of Sent by: list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject:RE: Using ' in Update statement 10/10/2003 09:14 AM Please respond to ORACLE-L Here is the reason for that: this list would not be useful to me if it was devoted to answering beginner's questions. List would get flooded, I would stop reading as would many other people. It has already happened. This list is a very valuable resource to me and I would hate to lose it to the people asking things like how to set prompt in sqlplus. Usenet groups are the proper place for that. People can learn the basics by reading books and manuals and I don't have much sympathy for the people who don't want to read but post their questions to this list instead. I am trying to help when I think that help is needed, but I am also trying to discourage trivial questions asked for 10th time. Don't get me wrong, I'm not apologizing for my actions, I'm just explaining them. This is my last reply in this thread because I don't intend to create a flame war on this list. I'll surround myself with an SEP field and vanish from sight. the basic stuff by reading manuals On Fri, 2003-10-10 at 11:39, [EMAIL PROTECTED] wrote: Ahhh!!! there is no elite and the question was trivial. The great and powerful has spoken, perhaps all questions should be filtered your way for classification. I can assure nothing on this forum is worthy of becoming Testy, merely trying to provide a solution for someone who needed help and of course one must stand their ground when necessary. Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: RE: Using ' in Update statement [EMAIL PROTECTED] .com 10/10/2003 10:19 AM Please respond to ORACLE-L There is no elite here, but this was a trivial question which I answered by listing manuals in which the answer could be found. I was asked for clarification of my reply, so I provided the clarification. No need to get testy about it. On Fri, 2003-10-10 at 08:54, Mercadante, Thomas F wrote: Naaa. He's an equal opportunity ball-buster. -Original Message- Sent: Thursday, October 09, 2003 5:54 PM To: Multiple recipients of list ORACLE-L Hamid started his question How can I user comma ' in my update statement? I am merely correcting his mistake and then providing a solution. Wow, what a concept on a Oracle Listserv site design for that very purpose. Just curious, Would you have answered in the same way if it had been asked by one of the elite? Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 05:09 PM Please respond to ORACLE-L I didn't mean quote, I meant RTFM. On Thu, 2003-10-09 at 16:34, [EMAIL PROTECTED] wrote: I assume you mean quote update tablea set fielda =' james'ste Camp 'first,'sec' ' update tablea set fielda =' james''ste Camp
RE: Can someone please verify this for me?
Strange... works fine here (same environment 9.2.0.1 on Win2K server): SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 9 13:43:23 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL create role new_role 2 / Role created. SQL create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 6 'set role new_role'; 7 end; 8 / Procedure created. SQL execute turn_on_role; PL/SQL procedure successfully completed. SQL Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Nuno Souto Sent: Thursday, October 09, 2003 10:09 AM To: Multiple recipients of list ORACLE-L I have a problem with the new procedure based roles, Secure Application Roles. The following is taken from an example in ASKTOM. Basically, I'm trying to setup a role that is enabled or not by a procedure. The original code from Tom: [EMAIL PROTECTED] l 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role identified by password'; 6* end; [EMAIL PROTECTED] create role new_role identified by password; Role created. [EMAIL PROTECTED] set role none; Role set. [EMAIL PROTECTED] select * from session_roles; no rows selected [EMAIL PROTECTED] set role new_role; set role new_role * ERROR at line 1: ORA-01979: missing or invalid password for role 'NEW_ROLE' [EMAIL PROTECTED] exec turn_on_role; PL/SQL procedure successfully completed. [EMAIL PROTECTED] select * from session_roles; ROLE -- NEW_ROLE [EMAIL PROTECTED] Now, if I try this using what I need: 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role'; 6* end; and then try to run it: exec turn_on_role; I get a ORA-6565 error: Cannot execute SET ROLE from within stored procedure Any ideas what am I missing here? 9.2.0.1, Win2K. Did the usual searches everywhere including Metaclick, nothing that I can relate to... TIA for any help. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: interesting dynamic pl/sql question
You should be getting errors, because PL/SQL inside execute immediate knows nothing about mystorageArray (or i for that matter) declared in your stored procedure. Probably, you could get by using package variables (and referring to them properly: package_name.var_name, specifically inside your dynamic sql). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 1:49 PM To: Multiple recipients of list ORACLE-L Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: Help with a scripting problem
Try using double quotes: select 'host c:\program files\resource kit\robocopy f:\oracle\oradata\llbot1\archive\ e:\BACKUP\llbot1\HOT\arch /Move;' from dual; Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Smith, Ron L. Sent: Wednesday, October 08, 2003 5:05 PM To: Multiple recipients of list ORACLE-L Sorry, this is what I am trying to run. But again, it barfs on the 'program files' portion of the script. I have tried with and without quotes. set heading off; set feedback off; set linesize 1000; spool e:\BACKUP\llbot1\scripts\ARC_PLUS2.sql; select 'spool e:\BACKUP\llbot1\HOT\log\ARC_BACKUP.LOG2;' from dual; select 'alter system switch logfile;' from dual; select 'alter system switch logfile;' from dual; select 'alter system switch logfile;' from dual; select 'alter system archive log stop;' from dual; select 'host c:\program files\resource kit\robocopy f:\oracle\oradata\llbot1\archive\ e:\BACKUP\llbot1\HOT\arch /Move;' from dual; select 'host mkdir f:\oracle\oradata\llbot1\archive;' from dual; select 'alter system archive log start;' from dual; select 'exit;' from dual; spool off; exit; Thanks! Ron -Original Message- Sent: Wednesday, October 08, 2003 3:57 PM To: [EMAIL PROTECTED]; Smith, Ron L. Ron, First of all, is echo a SQL*Plis command? Secondly, I think this will do what you want: select 'host c:\program files\resource kit\robocopy' from dual; as long as what you want is to execute the robocopy program from the SQL*Plus prompt. If you want to do something else, clarify your intentions on the list. Stephen [EMAIL PROTECTED] 10/08/03 04:34PM I am trying to issue the following command in SQL*PLUS but it doesn't like the space between 'program' and 'files'. Can anyone tell me how to get around this? echo select 'host c:\program files\resource kit\robocopy;' from dual; Thanks! Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Cary's Book - new topic
It's called optimization. Do you really need to see your post? -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Wolfgang Breitling Sent: Tuesday, October 07, 2003 2:04 PM To: Multiple recipients of list ORACLE-L A totally different point: How come I see your response before I see my own post? At 12:39 PM 10/7/2003, you wrote: 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: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Third party application - how to begin performance tuning efforts?
How do I know that my Oracle database is running optimally (if there is such a thing)? If there are no complaints from end-users, why would you want to do tuning? If there are complaints, focus on the area of the app causing most complaints. How to? is described very well in Cary Millsap new book Optimizing Oracle Performance. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Saira Somani-Mendelin Sent: Thursday, September 25, 2003 3:35 PM To: Multiple recipients of list ORACLE-L efforts? List, I begin with an apology for repeating something that has probably been asked before in different words. We use an integrated ERP/WMS/Query application provided by a vendor but we do not have the ability to change any code. How do I know that my Oracle database is running optimally (if there is such a thing)? Obviously I cannot rewrite queries in the application code (which is 4GL code BTW). So what other aspects of the database can I change/tune? I can definitely see some costly SQL statements when I feel curious and want to check what's happening on the database. But isn't cost all relative? Are there any recommendations for articles, white papers, books on how to tune the database for a third party application? Also, I will be attending the DBA/Developer Day in Toronto on Monday October 6. I am looking forward to the sessions by Tim Gorman, Tim Quinlan and Michael Abbey. Anyone else attending this conference? Thanks in advance, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Multiple block sizes
Title: Message Dont have experience with that. But, dont forget to configure memory subcaches for multiple block sizes (along with specifying new block size for the tablespace). Check Oracle 9i New Features by R.Freeman (p.13). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mladen Gogala Sent: Wednesday, September 24, 2003 11:05 AM To: Multiple recipients of list ORACLE-L Subject: Multiple block sizes Does anybody have any experience with the multiple block sizes in the database? I'm about to reconfigure my database to have a tablespace with blocksize 16k in addition to the existing 8k tablespaces. Tables in this tablespace will be loaded weekly and read daily, frequently using full table scan (DW style reporting. I'm planning to have bitmap indexes and the rest of the DW arsenal). Does anybody have any negative experiences with that kind of stuff? It's 9.2.0.4 on RH 7.3. Am I running into ora-7445 and ora-0600 type errors? -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error,please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient.Wang Trading LLCand any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
RE: possible to have a primary key with a bitmap index?
I don't know what's the syntax (or if it even exists). But, logically bitmap indexes are for the columns with low cardinality, while primary key index is unique. So, why do you want bitmap index for your primary key? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 3:55 PM To: Multiple recipients of list ORACLE-L is it possible to have a primary key that is enforced with a bitmap index? if so what is the syntax? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: possible to have a primary key with a bitmap index?
Keep playing -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Ryan Sent: Tuesday, September 23, 2003 4:45 PM To: Multiple recipients of list ORACLE-L im just playing around and testing things. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 5:34 PM I don't know what's the syntax (or if it even exists). But, logically bitmap indexes are for the columns with low cardinality, while primary key index is unique. So, why do you want bitmap index for your primary key? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 3:55 PM To: Multiple recipients of list ORACLE-L is it possible to have a primary key that is enforced with a bitmap index? if so what is the syntax? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Package Body created with compilation errors-
The command would be: Show errors package body XYZ_PKG; Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Oracle DBA Sent: Friday, September 19, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Hi List, Could you please help to see the errors in the package compilation? Please see below SQL @xyz_pkg.sql DOCCREATE OR REPLACE PACKAGE XYZ_PKG DOCAS DOC-- DOCPROCEDURE UPDATE_TEST(COL1 IN DROP_ME.C1%TYPE); DOCEND XYZ_PKG; DOC DOC*/ Warning: Package Body created with compilation errors. SQL show err No errors. SQL __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle DBA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Move SQL Server Tables to Oracle
Yeah, that's what I thought, though I'm not familiar with UPI. Sorry, question was kind of tongue in cheek -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Tanel Poder Sent: Thursday, September 18, 2003 6:20 PM To: Multiple recipients of list ORACLE-L No it doesn't, sqlldr probably uses OCI... or in some cases maybe even lower-level layer, UPI (user program interface). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 19, 2003 1:19 AM Ken, Could you please elaborate on how SQL*Loader uses SQL*Plus? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- KENNETH JANUSZ Sent: Thursday, September 18, 2003 3:40 PM To: Multiple recipients of list ORACLE-L That will work as long as they are sets of tables are the same layout. If not, then you have to map from SQL Server to Oracle which could be a complex time consuming task. Then the best bet is to generate delimited flat files from SQL Server and use SQL*Loader to load them into Oracle. For this I recommend the book: SQL*Loader, The definitive Guide by Gennick Mishra. Sorry to say it SQL*Loader uses SQL*Plus, not Perl. My $0.02 worth, Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 2:59 PM If you are familiar with Perl and DBI, you can pre-create the tables in Oracle, open a connection to both the MS and Oracle databases, select from MS, insert to Oracle. Do it all in one step, it's pretty straightforward. Jared On Thu, 2003-09-18 at 00:59, Gunnar Berglund wrote: Hi all, I need to move some sql server tables to oracle. Do you have some ideas how to do it. I have no idea ... rgds G - Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: KENNETH JANUSZ INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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
RE: PL/SQL Question:Eliminate duplicate rows
Check SQL Reference for exception_clause when creating Primary Key. Could help to do what you need just using SQL (no PL/SQL). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mercadante, Thomas F Sent: Friday, September 19, 2003 2:55 PM To: Multiple recipients of list ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.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).
RE: dbms_job issue.
You said for the last 3 days, so what do you do to get it running again? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 12:30 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME -- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER: This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
RE: Move SQL Server Tables to Oracle
Ken, Could you please elaborate on how SQL*Loader uses SQL*Plus? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- KENNETH JANUSZ Sent: Thursday, September 18, 2003 3:40 PM To: Multiple recipients of list ORACLE-L That will work as long as they are sets of tables are the same layout. If not, then you have to map from SQL Server to Oracle which could be a complex time consuming task. Then the best bet is to generate delimited flat files from SQL Server and use SQL*Loader to load them into Oracle. For this I recommend the book: SQL*Loader, The definitive Guide by Gennick Mishra. Sorry to say it SQL*Loader uses SQL*Plus, not Perl. My $0.02 worth, Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 2:59 PM If you are familiar with Perl and DBI, you can pre-create the tables in Oracle, open a connection to both the MS and Oracle databases, select from MS, insert to Oracle. Do it all in one step, it's pretty straightforward. Jared On Thu, 2003-09-18 at 00:59, Gunnar Berglund wrote: Hi all, I need to move some sql server tables to oracle. Do you have some ideas how to do it. I have no idea ... rgds G - Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: KENNETH JANUSZ INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: set correct next time after fixing broken job
You could use LAST_SEC (from dba_jobs) specification for broken job, which is time portion of LAST_DATE, when you calculate NEXT_DATE for dbms_job.broken. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeffrey Beckstrom Sent: Tuesday, September 16, 2003 7:39 AM To: Multiple recipients of list ORACLE-L Subject: set correct next time after fixing broken job If I mark a job as broken to prevent its being run and later mark it as being unbroken, it appears that by default the next run date is set as the current date. If I want to have the job rescheduled back to its original date/time, is there a way to do that. Let's say the job runs daily at 10:00 p.m. If I mark the job as broken and later as unbroken without specifying a date, it runs immediately. However, I know that I can code a date when I unbreak the job, but how can I do this in sql without hardcoding a date. We have several jobs we would sometimes like halted during maintenance and would like to avoid having to hardcode a date when unbreak them. Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204
RE: translate, replace...?
Looks like you've got one too many REPLACEs. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Wednesday, September 17, 2003 11:00 AM To: Multiple recipients of list ORACLE-L IA, The following code should be close to what you want select replace(replace(replace(a,chr(13),'R'),chr(10),'')) from table John -Original Message- Sent: 16 September 2003 16:15 To: Multiple recipients of list ORACLE-L Hi, I have a column with carriage returns (chr(13) ) and line feeds (chr(10)). I want to select this column replacing the chr(13) with 'R' and chr(10) with ' ' . Whats the best way to do this? Regards IA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Is Cary's new book shipping now?
Title: RE: Is Cary's new book shipping now? References? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of April Wells Sent: Wednesday, September 17, 2003 3:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: Is Cary's new book shipping now? I believe, per one of the other DBAs here, OCPs get like either a 20% or 30% discount on books. April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas /\ / \ / \ \ / \/ \ \ \ \ Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 17, 2003 2:50 PM To: Multiple recipients of list ORACLE-L Subject: Re: Is Cary's new book shipping now? Oracle Press is not a subsidiary of Oracle but rather of Osborne/McGraw-Hill --- Oracle DBA [EMAIL PROTECTED] wrote: Hi List, Does OCP emembers get any discount on Tom's Effective Oracle by Design (Osborne ORACLE Press Series), since it is coming from Oracle Press. Any idea? TIA Sami --- Rachel Carmichael [EMAIL PROTECTED] wrote: Tom has a NEW, different book out, from Oracle Press. That is the one Cary is referring to. Which means you'll have to buy a new book. As for Cary's book, I have it on order at Amazon as well, but according to the site today, it's still not released. :( --- Dwayne Cox [EMAIL PROTECTED] wrote: Why oh why did I not wait? I tracked down and purchased Tom's previous Expert One-on-One Oracle a couple months ago. I even checked to make sure a new edition was not forthcoming. *sigh* Did not check enough I guess. I'll have to compare the two to see if I want to 'upgrade'. I am waiting, impatiently, for Cary's book (preordered on Amazon). I just attended the Oracle9i Performance Tuning Class and am quite pumped about tuning. Especially since we have recently experienced some performance issues. Dwayne -- Dwayne Cox Info Tech, Inc. Gainesville, FL Cary Millsap wrote: Tom's book, by the way, is spectacular. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic http://www.hotsos.com/training/clinic101 101 in Sydney - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Jamadagni, Rajendra Sent: Tuesday, September 16, 2003 11:40 AM To: Multiple recipients of list ORACLE-L Thanks Cary I have your new book and Tom's new book on order . Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, September 16, 2003 12:05 PM To: Multiple recipients of list ORACLE-L Raj, The book will be in the O'Reilly warehouses tomorrow (9/17), presumably outbound to stores on the same or next day. I'd expect preorders to arrive at customers' homes on or near this weekend. Cary Millsap -- Dwayne Cox Corporate DBA Info Tech, Inc. 5700 SW 34th Street, Suite 1235 Gainesville, FL 32608 email: [EMAIL PROTECTED] phone: 352.381.4521 fax: 352.381. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dwayne Cox INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting
RE: Raid Arrays and Power Loss
Ian, Thanks for sharing (seriously). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- MacGregor, Ian A. Sent: Monday, September 15, 2003 11:34 PM To: Multiple recipients of list ORACLE-L Last Friday was hot here, and rumor has it our 230 KV power line sagged and touched some tree branches. The local power company shut it off. Leaving our systems to depend on UPS. About 30 minutes afterwards one system produced these errors. This was jus before the system went dead Fri Sep 12 12:58:40 2003 Errors in file /opt/oracle/admin/BBRO/bdump/bbro_ckpt_1420.trc: ORA-00206: error in writing (block 3, # blocks 1) of controlfile ORA-00202: controlfile: '/u1/oradata/BBRO/BBROcntrl01.ctl' ORA-27063: skgfospo: number of bytes read/written is incorrect SVR4 Error: 5: I/O error Additional information: -1 Additional information: 8192 Fri Sep 12 12:58:42 2003 Errors in file /opt/oracle/admin/BBRO/bdump/bbro_ckpt_1420.trc: ORA-00221: error on write to controlfile ORA-00206: error in writing (block 3, # blocks 1) of controlfile ORA-00202: controlfile: '/u1/oradata/BBRO/BBROcntrl01.ctl' ORA-27063: skgfospo: number of bytes read/written is incorrect SVR4 Error: 5: I/O error Additional information: -1 Additional information: 8192 Fri Sep 12 12:58:42 2003 CKPT: terminating instance due to error 221 Instance terminated by CKPT, pid = 1420 - Things look pretty shaky here. When things were restarted the following error was produced. Fri Sep 12 13:32:01 2003 ORA-00204: error in reading (block 1, # blocks 1) of controlfile ORA-00202: controlfile: '/u1/oradata/BBRO/BBROcntrl01.ctl' ORA-27091: skgfqio: unable to queue I/O SVR4 Error: 6: No such device or address Additional information: 1 The raid array had not been powered on --- However Fri Sep 12 15:33:08 2003 ORA-00202: controlfile: '/u1/oradata/BBRO/BBROcntrl01.ctl' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3 Fri Sep 12 15:33:11 2003 ORA-205 signalled during: alter database mount... Now the file system is available, but the file itself has disappeared. It was not corrupted, just disappeared. We duplex a copy to an internal disk. So recovery was easy. However once this was fixed Fri Sep 12 16:18:58 2003 Thread recovery: start rolling forward thread 1 Fri Sep 12 16:18:58 2003 Errors in file /opt/oracle/admin/BBRO/udump/bbro_ora_1804.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u2/oradata/BBRO/redo0301.log' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3 ORA-313 signalled during: ALTER DATABASE OPEN... - These files are on a RAID 1 LUN. Both copies of the file are gone. Again not corrupted but gone. I don't know if using duplexing rather than RAID 1 would have mattered here, but I am changing things so that one group of redo logs is on internal disk and written via the duplexing method. Ian MacGregor Stanford linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: what is a materilized view ?
Having fun, while Jared isn't watching? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Orr, Steve Sent: Wednesday, September 10, 2003 11:15 AM To: Multiple recipients of list ORACLE-L Philosophically speaking, when the scales fall from the eyes of an idealist and he/she becomes a rationalist, they've adopted a materialized view. ;-) But maybe it only exists if you believe it exists. -Original Message- Sent: Wednesday, September 10, 2003 5:04 AM To: Multiple recipients of list ORACLE-L Dear Freinds, what is a materilized view ? what is the use of it and how to create it. Any docs or notes or white papers will be helpful. TIA, Rajuveera -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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).
RE: Jobs and OEM... how to
Since DBMS_JOB executes PL/SQL code (and that's what you want to use), you'll have to write an external stored procedure, which calls Export, wrap it in PL/SQL procedure, and call it from DBMS_JOB. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jose Luis Delgado Sent: Tuesday, September 09, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Hi Stefick... thanks for your input... well, I was thinking about dbms_job, may be I was not so clear... thanks again. Regards JL --- Stefick Ronald S Contr ESC/HRIDD [EMAIL PROTECTED] wrote: Perl and cron. -Original Message- Sent: Tuesday, September 09, 2003 10:04 AM To: Multiple recipients of list ORACLE-L Hi to everybody!! I would like to create a job that EXPort my database, but... I *do not* want to use OEM. OEM uses tcl and the intelligent agent to schedule its jobs... how can I create a job that can export my database? (no problem if I have to program with tcl files) Any ideas? any sample (would be better :-) TIA JL __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: size of NUMBER datatype
It depends on the number that is stored. Check archives, there were couple discussions on this issue. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Monday, September 08, 2003 11:50 AM To: Multiple recipients of list ORACLE-L How many bytes does oracle use to store number datatype ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: decode
decode(sign(8 - length(sn)), -1, substr(sn,-7), sn ) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Imran Ashraf Sent: Wednesday, September 03, 2003 1:00 PM To: Multiple recipients of list ORACLE-L Hi, I have this query... select decode(length(sn),8),substr(sn,-7),sn )from . However i want to change it so that if the length of sn is = 8 then set sn to last 7 characters. How can i implement this? Regards -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: how to do a variable in-list of numbers?
This method works; I have some code using it.
The only problem, it could be slow (on large tables), because index on
code_column will not help.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Chelur, Jayadas {PBSG}
Sent: Friday, August 29, 2003 10:45 AM
To: Multiple recipients of list ORACLE-L
SELECT columns
FROM sometable
WHEREINSTR(','||list_of_comma_separated_codes||',' ,
','||code_column||',' ) 0;
(1). Concatenate Commas at BOTH ENDS of the string containing
different code values. e.g. If the string is
'101,102,554,336,678,301,201,199' , the concatenate commas
on both ends to make it ',101,102,554,336,678,301,201,199,'
this is to make sure that EVERY code ( including the first
and the LAST ) confirms to the pattern ,value,
(2) For each row in the table, use INSTR() function to see whether
the pattern ,value of columna, is CONTAINED in the string
INSTR() is used so that pattern matching can be used instead of
any range checking etc on the string. The actual code values in
the string can be IN ANY ORDER.
-Original Message-
Sent: Friday, August 29, 2003 10:32 AM
To: Multiple recipients of list ORACLE-L
I need to do an insert select of the form
insert into tab2
select col1
from tab1
where col2 in (inlist of numbers);
I do not know how many values will be in my inlist at runtime. With
strings
I just build a big string. How do I build an 'inlist' of numbers at
runtime?
Im using a cursor to determine which values need to be added to my
inlist. I
think I can do some kind of cast, but im not familiar with it.
Im on 8i. I do not want to j ust run this inside my cursor. It could
then
execute 300-400 times and will run all day.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Chelur, Jayadas {PBSG}
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).
RE: get sid (session id) and serial#?
Title: Message Its not in 8.1.5 Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Kanagaraj Sent: Friday, August 29, 2003 3:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: get sid (session id) and serial#? Need to install @?/rdbms/admin/dbmssupp while connected as SYS. Available on all platforms 8.0.6+ I understand. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Murali_Pavuloori/[EMAIL PROTECTED] [mailto:Murali_Pavuloori/[EMAIL PROTECTED]] Sent: Friday, August 29, 2003 3:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: get sid (session id) and serial#? Raj, Which version of db are you on? This is not available on 9.2.0.3 Murali.
RE: How to invoke stored procedures from another instance?
It could be a combination of trigger/pooling. Trigger writes changes locally into some kind queue table. The second instance is pooling this queue table (using db link) at it's own rate without affecting transactions against original table. Also, in this case when network is down, original instance is not affected, and when network restored the second instance picks up where it stopped before network was down. I have this mechanism implemented here, and it works pretty smoothly. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Stephane Faroult Sent: Thursday, August 28, 2003 6:00 AM To: Multiple recipients of list ORACLE-L Hi listers, =20 Assume that there are two instances in Oracle. Both instances are on = different machines and different Oracle versions. There is a table on = first instance. Any update on this table should invoke stored procedures = on the second instance. This should be real time based. Options we = looked at are =20 1. Trigger on the table invoking the procedures of the other instance 2. Using dbms_alert 3. Some kind of polling mechanism =20 Triggers we would like to avoid. Options we are left with are dbms_alert = and polling mechanism.=20 =20 Is it possible to use dbms_alert in this case? If yes how? =20 Can you think of some kind of polling mechanism which will satisfy the = need of real time communication? Updates on the table is done at a very = fast rate, hence processing should also be at a fast rate. =20 Any help in this regard is very much appreciated. =20 Thanks and Regards, =20 Ranganath =20 I agree with your reluctance to use triggers; the problem is that whenever the second instance is down, then you couldn't do anything on the first. Basically, what you want to implement are near real-time although not quite synchronous snapshots. I have never used DBMS_ALERT in this way, but I think that it would be possible to have a database link on the second instance referencing the first one and invoking DBMS_ALERT through it. Beware with DBMS_ALERT though, my memories are not very fresh but there are some problems with COMMITs (which you can workaround with autonomous transactions, but then the alertee can be woken up by a rolled back transaction, a case which has to be handled by your code); DBMS_PIPE is another solution, which also has its flaws. Avanced queuing seems to me to be a fine mess, but perhaps it's worth a look too. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: sanity check time...
Bill, Services should be there. Listener service is created, when Oracle is installed. And database service is created when database is created, using oradim, and has database name as part of the service name: OracleServicedatabase_name. Tell them to check if services are running, check listener, sqlnet, and tnsnames config files, check alert file (service could be running, but database is not open). HTH Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Thater, William Sent: Thursday, August 28, 2003 11:25 AM To: Multiple recipients of list ORACLE-L Ok, i'm a unix guy i don't usually work with windows. well, the Keane office just called and some of the people over there are having trouble connecting to a windows 9i database. when i asked them if the services were running i got a basic huh? so what do i have to run to set up the services? and no i can't RTFM because i don't got the FM to R for windows nor access to technet since the worm attacks. [don't go there, i'm trying to explain that i kind of need to get to there and metalink to do my job.] -- Bill Shrek Thater ORACLE DBA BAARF Party member #25 [EMAIL PROTECTED] Sooner of later that which is now life shall be poetry, and every fair and manly trait shall add a richer strain to the song. - Ralph Waldo Emerson -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: Bounced from the list
It happens, if your mail-server is unavailable/down for extended period of time. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- GL2Z/ INF DBA BENLATRECHE Sent: Wednesday, August 27, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Hi all, I have been bounced twice from the list since July. I don't know the reason why ?. I re-subscribe this morning and I am afraid to be bounced again ? The listmaster have been contacted without a feedback. Is this happened to someone else ? And what to do ??? Regards Kamel Benlatreche -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: GL2Z/ INF DBA BENLATRECHE INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: tables and views
Views arent in all_tables, they are in all_views. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of AK Sent: Friday, August 08, 2003 1:19 PM To: Multiple recipients of list ORACLE-L Subject: tables and views How to diiferentiate views and tables in all_tables and all_tab_columns . which column and what criteria can return only tables ?? -ak
RE: ora-600 when analyzing IOT
Does it mean, that if I don't have overflow segment in my IOT, I don't
have to analyze table, just analyze PK?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:45 PM
To: Multiple recipients of list ORACLE-L
Igor,
When you gather statistics on an IOT with the ANALYZE command, it will
analyze the primary key and overflow segment so I would think
this is all that is required by CBO. Hopefully others have more
insight.
Rick
Igor Neyman
[EMAIL PROTECTED]To:
[EMAIL PROTECTED]
on.com cc:
[EMAIL PROTECTED]
Subject: RE: ora-600
when analyzing IOT
08/07/2003 12:13
PM
Rick,
Thanks for prompt reply.
I tried it (deleting statistics before analyzing table) and it worked.
Any ideas, whether cost-based optimizer need statistics on IOT or having
statistics on PK index of IOT would be enough?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L
According to MetaLink you need to delete statistics and re-analyze
I do not know your database version but this is supposely fixed in 8.1.6
execute
dbms_stats.delete_table_stats('owner','IPN_MEASUREMENT');
analyze table ipn_measurement estimate statistics ;
HTH
Rick
Igor Neyman
[EMAIL PROTECTED]To: Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]
on.com cc:
Sent by: Subject: ora-600 when
analyzing IOT
[EMAIL PROTECTED]
.com
08/07/2003 12:44
PM
Please respond to
ORACLE-L
Ok, since my original message still hasn't arrived (sent couple hours
ago), here it goes again (sorry, if you get duplicate).
First, sorry for asking this list before searching Metalink, but I don't
have access to it right now.
So, here it is:
I'm getting:
ORA-00600: internal error code, arguments: [15163], [333], [17424],
[16191], [], [], [], []
when analyzing one of the index-organized tables:
ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS.
Now, similar statement analyzing other IOTs works fine.
Also, on the same table (ipn_measurement) analyze worked fine yesterday,
and the table didn't grew too much since yesterday.
Any ideas?
Another question, do I really have to run analyze table on IOT for
cost-based optimizer, or analyzing index would be enough:
ANALYZE INDEX pk_ipn_measurement ESTIMATE STATISTICS
because analyze index still runs with no errors.
Oracle version is 8.1.5.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
--
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:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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
RE: ora-600 when analyzing IOT
Rick,
Thanks for prompt reply.
I tried it (deleting statistics before analyzing table) and it worked.
Any ideas, whether cost-based optimizer need statistics on IOT or having
statistics on PK index of IOT would be enough?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L
According to MetaLink you need to delete statistics and re-analyze
I do not know your database version but this is supposely fixed in 8.1.6
execute
dbms_stats.delete_table_stats('owner','IPN_MEASUREMENT');
analyze table ipn_measurement estimate statistics ;
HTH
Rick
Igor Neyman
[EMAIL PROTECTED]To: Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]
on.com cc:
Sent by: Subject: ora-600 when
analyzing IOT
[EMAIL PROTECTED]
.com
08/07/2003 12:44
PM
Please respond to
ORACLE-L
Ok, since my original message still hasn't arrived (sent couple hours
ago), here it goes again (sorry, if you get duplicate).
First, sorry for asking this list before searching Metalink, but I don't
have access to it right now.
So, here it is:
I'm getting:
ORA-00600: internal error code, arguments: [15163], [333], [17424],
[16191], [], [], [], []
when analyzing one of the index-organized tables:
ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS.
Now, similar statement analyzing other IOTs works fine.
Also, on the same table (ipn_measurement) analyze worked fine yesterday,
and the table didn't grew too much since yesterday.
Any ideas?
Another question, do I really have to run analyze table on IOT for
cost-based optimizer, or analyzing index would be enough:
ANALYZE INDEX pk_ipn_measurement ESTIMATE STATISTICS
because analyze index still runs with no errors.
Oracle version is 8.1.5.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
--
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:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).
ora-600 when analyzing IOT
First, sorry for asking this list before searching Metalink, but I dont have access to it right now. So, here it is: Im getting: ORA-00600: internal error code, arguments: [15163], [333], [17424], [16191], [], [], [], [] when analyzing one of the index-organized tables: ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS. Now, similar statement analyzing other IOTs works fine. Also, on the same table (ipn_measurement) analyze worked fine yesterday, and the table didnt grew too much since yesterday. Any ideas? Another question, do I really have to run analyze table on IOT for cost-based optimizer, or analyzing index would be enough: ANALYZE INDEX pk_ipn_measurement ESTIMATE STATISTICS because analyze index still runs with no errors. Igor Neyman, OCP DBA [EMAIL PROTECTED]
ora-600 when analyzing IOT
Ok, since my original message still hasn't arrived (sent couple hours ago), here it goes again (sorry, if you get duplicate). First, sorry for asking this list before searching Metalink, but I don't have access to it right now. So, here it is: I'm getting: ORA-00600: internal error code, arguments: [15163], [333], [17424], [16191], [], [], [], [] when analyzing one of the index-organized tables: ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS. Now, similar statement analyzing other IOTs works fine. Also, on the same table (ipn_measurement) analyze worked fine yesterday, and the table didn't grew too much since yesterday. Any ideas? Another question, do I really have to run analyze table on IOT for cost-based optimizer, or analyzing index would be enough: ANALYZE INDEX pk_ipn_measurement ESTIMATE STATISTICS because analyze index still runs with no errors. Oracle version is 8.1.5. Igor Neyman, OCP DBA [EMAIL PROTECTED] -- 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).
RE: ** is there PL/SQL for case
To overcome this in PL/SQL you can run sql,
which includes case, as dynamic sql:
EXECUTE IMMEDIATE SELECT
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Guang
Mei
Sent: Thursday, August 07, 2003
4:24 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: ** is there PL/SQL
for case
Below is an example (with Oracle 8173),case works
in sql, not in pl/sql.
Guang
[EMAIL PROTECTED]
create table t1 (name varchar2(30), salary number );
Table created.
[EMAIL PROTECTED] insert into t1
(name,salary) values ('Bill', 1000);
1 row created.
[EMAIL PROTECTED] insert into t1
(name,salary) values ('George', 2000);
1 row created.
[EMAIL PROTECTED] insert into t1
(name,salary) values ('Gore', 3000);
1 row created.
[EMAIL PROTECTED] insert into t1
(name,salary) values ('Dick', 4000);
1 row created.
[EMAIL PROTECTED] commit;
Commit complete.
[EMAIL PROTECTED] select * from
t1;
NAME
SALARY
-- --
Bill
1000
George
2000
Gore
3000
Dick
4000
[EMAIL PROTECTED] select
count(case when salary 2000 then 1 else null end) poor,
count(case when salary between 2000 and
3000 then 1 else null end) middle_class,
count(case when salary 3000 then 1
else null end) rich
from t1;
POOR
MIDDLE_CLASS RICH
-- --
1
2 1
[EMAIL PROTECTED] declare
c1 number;
c2 number;
c3 number;
begin
select count(case when salary 2000 then 1 else null end)
poor,
count(case when salary between
2000 and 3000 then 1 else null end) middle_class,
count(case when salary
3000 then 1 else null end) rich
into c1,c2,c3
from t1;
end;
/
select count(case when salary 2000 then 1 else null end)
poor,
*
ERROR at line 6:
ORA-06550: line 6, column 16:
PLS-00103: Encountered the symbol CASE when expecting one of the
following:
( * - + all mod null an identifier
a double-quoted delimited-identifier a bind variable avg
count current distinct max min prior sql stddev sum unique
variance execute forall time timestamp interval date
a string literal with character set specification
a number a single-quoted SQL string
-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of A
Joshi
Sent: Thursday, August 07, 2003
4:55 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: ** is there PL/SQL
for case
Guang,
Thanks for your help. Do you have an example
you can send me. I thought whatever one can do in sql one can do in pl/sql.
meaning sql is a subset of pl/sql. Correct me if i am wrong. Thank You.
Guang Mei
[EMAIL PROTECTED] wrote:
I am not sure in 9i. But in 8i I think you can use case
in sql but not in pl/sql. You have to use if elsif in pl/sql.
Guang
-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of A
Joshi
Sent: Thursday, August 07, 2003
4:24 PM
To: Multiple recipients of list
ORACLE-L
Subject: ** is there PL/SQL for
case
Hi,
Is there a statement in pl/SQL like case or is
if elsif the only way. Meaning if I need to transalate state depending on input
number asfollows :
1 - CA
2 - OR
3 - WA
4 - AR
Can I have one statement like case 'state# :
1: state := 'CA'
2: state := 'OR'
etc.
or do i have to do :
IF state# = 1 THEN
state := 'CA';
ELSIF state# = 2 THEN
state := 'OR';
etc
Thank You.
Do you Yahoo!?
Yahoo!
SiteBuilder - Free, easy-to-use web site design software
Do you Yahoo!?
Yahoo!
SiteBuilder - Free, easy-to-use web site design software
RE: ** is there PL/SQL for case
RTFM on DECODE: DECODE(state, 1, CA, 2, OR, 3, WA, 4, AR) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A Joshi Sent: Thursday, August 07, 2003 3:24 PM To: Multiple recipients of list ORACLE-L Subject: ** is there PL/SQL for case Hi, Is there a statement in pl/SQL like case or is if elsif the only way. Meaning if I need to transalate state depending on input number asfollows : 1 - CA 2 - OR 3 - WA 4 - AR Can I have one statement like case 'state# : 1: state := 'CA' 2: state := 'OR' etc. or do i have to do : IF state# = 1 THEN state := 'CA'; ELSIF state# = 2 THEN state := 'OR'; etc Thank You. Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software
RE: Oracle to MSSQL conversion?
For once, there are many more buttons to push when configuring/tuning Oracle instance/db (I'm not talking about GUI here -:). And, many more options when designing db. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Wolfe Stephen S GS-11 6 MDSS/SGSI Sent: Monday, August 04, 2003 7:15 AM To: Multiple recipients of list ORACLE-L SNIP Wolfe Knowing SQL Server and moving to Oracle is going to be tough. The other way round is very simple though from Oracle to SQL Server. Interesting, why is it more difficult to go from SQL Server to Oracle than the other way around? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfe Stephen S GS-11 6 MDSS/SGSI INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: On TRUNCATE table does the indexes also get truncated.
The simplest way to find out is to try it (on test box). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Denham Eva Sent: Wednesday, July 30, 2003 9:59 AM To: Multiple recipients of list ORACLE-L Hello, When you truncate a table using:- TRUNCATE TABLE TEMP; does any of the indexes on the table also get truncated? TIA Denham Eva Oracle DBA Linux like TeePee... No Windows, No Gates and Apache inside! _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com _ # Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denham Eva INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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).
RE: 9i-OCP Question
Well, in archivelog mode LogWriter may use one group and Archiver may use the other group, so I'd agree with Kirti. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mladen Gogala Sent: Wednesday, July 30, 2003 12:59 PM To: Multiple recipients of list ORACLE-L There are two reasons: 1) Redo log groups are never used simultaneously, so they can reside on the same disk. Log members should not be on the same disks for increased survivability. That gives us 2 groups with 4 members, each two members sharing the same device - 4 disks alltogether. 2) It's because I say so and I'm an 8i OCP. On 2003.07.30 13:44, KENNETH JANUSZ wrote: Why? What is the logic? Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 12:29 PM The answer is 4. Of course, no one in the right mind would have 2 groups with 4 members each. On 2003.07.30 13:19, Senthil Kumar wrote: Hi all, What is the correct answer for this? Q If you have 2 redo log groups with 4 members each, how many disks does Oracle recommend to keep the redo log files? 1. 8 2. 2 3. 1 4. 4 Which is the correct answer. TIA Senthil -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: KENNETH JANUSZ INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: Antw: SQL question : How to retrieve the File_name without
eat this:
Is it chewable? -:)
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Guido Konsolke
Sent: Wednesday, July 23, 2003 3:14 AM
To: Multiple recipients of list ORACLE-L
Hi Philippe,
eat this:
select
substr('/oracle/d0/data/user.dbf',instr('/oracle/d0/data/user.dbf','/',-
1)+1) from dual;
hth,
Guido
[EMAIL PROTECTED] 23.07.2003 09.59 Uhr
Hi Gurus!
a very simple problem for You :I just want to retrieve the .dbf name
from
file_name column in dba_data_files.
eg :'/oracle/d0/data/user.dbf' -- user.dbf
Maybe using translate function ?
Thank in advance !
Philippe
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guido Konsolke
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).
RE: DBMS_JOB scheduling
Title: RE: DBMS_JOB scheduling Raj, You must be speaking from UNIX heights -J Under Windows I find dbms_job much more reliable than windows at scheduling. Actually, never had problems with dbms_job forgetting to run a job. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, Rajendra Sent: Wednesday, July 23, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB scheduling Garry, 1. have you tried select to_char(sysdate,'D') from dual ?? This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number. nevertheless, I think what you have attempted is fantastic and worthy of adoption ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Garry Gillies [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 23, 2003 5:59 AM To: Multiple recipients of list ORACLE-L Subject: DBMS_JOB scheduling Any Interest? The DBMS_JOB package is supplied by Oracle to allow the running of procedures at regular intervals. Unfortunately the INTERVAL parameter is limited to 128 characters, which prevents you from getting very complex (user defined functions [in the interval parameter] do not work well - according to Fuerstein in his book Oracle Built In Packages). The situation is eased somewhat by the fact that the NEXT_DATE parameter can be supplied to the procedure as an in/out parameter - and the procedure can contain whatever code is necessary to calculate when next to run. This is all very well, but custom coding scheduling routines can quickly become tedious. On the basis of do it once and get it over with I have written a function called NEXT_DATE which I have wrapped in a package called CRON. There is a Unix program called cron which runs jobs on a regular basis. Although the scheduling data supplied to cron is simple and concise, complex schedules are easy to specify. The NEXT_DATE function takes in a cron schedule string and returns the next date that conforms to the schedule - or you can supply a cron schedule and a date and it will return the first date after the supplied date that conforms to the schedule. At the moment it is not very friendly on the error detection front. A VALUE_ERROR is returned if it deems the cron schedule to be invalid. You will also get a VALUE_ERROR if the next valid date is more than twenty seven years in the future. DBMS_OUTPUT is used to display error messages which will hopefully give you a clue. This will be improved if I receive enough complaints ( and suggestions for improvements). THE CRON SCHEDULE A cron schedule consists of five components, each separated from the next by a space. The syntax is identical for all components. The components represent Minute in Hour Hour in day Day in month Month in year Day of Week - A bit of a bugger this one. In Unix land the day numbering runs from 0-6 with 0 being Sunday. In Oracle the day numbering depends on the setting of NLS_TERRITORY. I have chosen to go with ISO standard 8601:1998 which runs from 1-7 with 1 being Monday. This is so close to the Unix convention that I can interpret Unix cron schedules correctly. Curiously, Oracle do not provide a date format which supplies this number. The ISO week number is available with the format 'IW', but not the ISO day number. If you have a field of type date called dt, you can obtain the ISO day number with ( trunc(dt) - trunc(dt ,'IW') ) + 1 A component can consist of an asterisk * which represents all valid values or a number of elements separated by a comma (if only one element is supplied, forget the comma). An element can be a single number - valid for the component (32 in Day in month is invalid) or two numbers separated by a hyphen - which represents a range. EXAMPLES Run every hour on the hour 0 * * * * Run twice every hour, on the hour and on the half hour 0,30 * * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59 0,30 8-16 * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59, Monday to Friday 0,30 8-16 * * 1-5 Run at 11:12 every Friday the 13th 11 12 13 * 5 Run at 04:00 every leap year on february 29 0 4 29 2 * Run at 04:00 every leap year on february 29 when february 29 is a Thursday 0 4 29 2 4 Garry Gillies Database Administrator Business Systems Weir Pumps Ltd 149 Newlands Road, Cathcart, Glasgow, G44 4EX T: +44 0141 308 3982 F: +44 0141 633 1147 E: [EMAIL
RE: Managing Archived Redo Logs
Yes, you need to establish some process to manage archived RedoLogs. All archived RedoLogs accumulated between two consecutive online/hot backups should be included into backup set. Then you can delete them or keep for a while if space permits. Read on backups for db in archivelog mode. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Farnsworth, Dave Sent: Wednesday, July 23, 2003 2:09 PM To: Multiple recipients of list ORACLE-L I finally get to put our 8.1.7 on NT databases into archivelog mode. We have a third party app vendor that would not support us if I did this but I finally convinced them that is the way to go and it should not effect the app. Anyway, I am reading chapter 7 from the Administrators Guide, Managing Archived Redo Logs. I know I have to set the parameters in the init.ora to achieve automatic archiving; log_archive_start=true log_archive_dest_1 = location=my\disk\drive log_archive_format=%%ORACLE_SID%%T%T%S.ARC -or somthing like that One thing I don't see in TFM is, do these archived redo logs just keep accumulating in the destination directory set in the log_archive_dest_1 parameter? Do I need to create a process to get them to tape and then once on tape, delete these old archive redo logs through my process? I'm just excited to be able to finally go to archivelog mode. Once I get the basics down then I want to investigate using RMAN. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Who Says Oracle does not listen
Doing it my way just seems to be cleaner: why forcing exception, when
it could be avoided?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Alex Feinstein
Sent: Tuesday, July 22, 2003 1:44 AM
To: Multiple recipients of list ORACLE-L
RE: Who Says Oracle does not listenAgree.
One can improve EXCEPTION section to ignore only relevant errors.
Alex.
- Original Message -
To: Multiple recipients of list ORACLE-L
Sent: Monday, July 21, 2003 6:19 AM
That's not good enough.
I don't want to discard ANY exception.
When dropping table, I don't want to see error messages only if there is
nothing to drop. While if let's say there is a problem with privileges,
it
will go unnoticed.
Or, when adding a table, it's fine not be getting an error, if table
already
exists. But, if there is no room to create a table, or to add a
partition
to the table, I want to see this error message.
So, I still prefer my way of doing it (see scripts in my original
message)
comparing to Oracle's script, you refer to.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
[EMAIL PROTECTED]
Sent: Friday, July 18, 2003 6:10 PM
To: Multiple recipients of list ORACLE-L
From ORACLE own script:
Rem
Rem Drop tables without raising errors if they do not exist
Rem
declare
PROCEDURE drop_force(tab varchar2) IS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || tab;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
begin
drop_force('utl_recomp_invalid');
drop_force('utl_recomp_sorted');
drop_force('utl_recomp_compiled');
drop_force('utl_recomp_backup_jobs');
drop_force('utl_recomp_log');
end;
/
Alex.
-Original Message-
Sent: Friday, July 18, 2003 2:04 PM
To: Multiple recipients of list ORACLE-L
Sure, I would.
But I can't wait till Oracle turns around.
My scripts are executed by our field engineers, who know next to
nothing
about Oracle, and the only thing they can do is to check log files for
error
messages (and even this is done automatically).
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Goulet, Dick
Sent: Friday, July 18, 2003 3:44 PM
To: Multiple recipients of list ORACLE-L
Igor,
True enough, but wouldn't you like it as part and parcel of the
command?
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
Sent: Friday, July 18, 2003 4:35 PM
To: Multiple recipients of list ORACLE-L
To avoid those errors in my scripts I'm checking data dictionary for the
existence of the object (fortunately, dynamic sql helps here):
REM Dropping synonym
DECLARE lCounter integer;
begin
SELECT COUNT(*) INTO lSyn
FROM dba_synonyms WHERE synonym_name = 'PRCPV_REPORT_INFO' AND
OWNER
= 'PUBLIC'; IF (lSyn = 1) THEN
EXECUTE IMMEDIATE 'drop PUBLIC SYNONYM PRCPV_Report_Info'; END
IF;
end; /
or:
REM Adding column
DECLARE lCounter integer;
begin
SELECT count(*) INTO lCounter
FROM DBA_TAB_COLUMNS
WHERE table_name = 'PRCP_MENU'
AND column_name = 'MENU_NAME'
AND owner = 'IPN_DBA';
IF (lCounter = 0) THEN
EXECUTE IMMEDIATE 'ALTER TABLE prcp_menu ADD menu_name
VARCHAR2(50) NULL';
END IF;
end;
/
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Alex Feinstein
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).
RE: Job to run first Wednesday
Are you saying 03-SEP-03 is wrong?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Rachel Carmichael
Sent: Tuesday, July 22, 2003 3:15 PM
To: Multiple recipients of list ORACLE-L
don't rush off to use it. I tried it, substituting August 1 and got
September.
1* select
next_day(last_day(to_date('08/01/2003','MM/DD/')),'WED') from dual
SQL /
NEXT_DAY(
-
03-SEP-03
--- [EMAIL PROTECTED] wrote:
Hmm... much more elegant than mine, and everyone elses.
Guess I better RTFM the next_day function.
Mercadante, Thomas F [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/22/2003 12:34 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: Job to run first Wednesday
Josh,
With the following functions, you could probably get it to work:
select next_day(last_Day(sysdate),'WED') from dual
This (today) returns Wed, August 6th.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, July 22, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
Greetings,
How can I set the interval in my dbms job to have it run on the first
Wednesday of every month? Is this even possible? I have been trying
to
noodle it thru for a week to no avail.
tia,
Josh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Josh Collier
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).
RE: Who Says Oracle does not listen
Title: RE: Who Says Oracle does not listen
In this case performance is not an issue.
I dont drop/create/modify
tables/columns/synonyms every minute. The script runs, when we install new
release of our product, happens once in a few months.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, July 22, 2003 3:30
PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Who Says Oracle does not
listen
Performance.
You do check, and the DBMS does
check internally.
Alex.
-Original Message-
From: Igor Neyman [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 22, 2003 6:34
AM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Who Says Oracle does
not listen
Doing it my way just seems to be cleaner:
why forcing exception, when it could be avoided?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Alex Feinstein
Sent: Tuesday, July 22, 2003 1:44
AM
To: Multiple recipients of list
ORACLE-L
RE: Who Says Oracle does not listenAgree.
One can improve EXCEPTION section
to ignore only relevant errors.
Alex.
- Original Message -
To: Multiple recipients of list
ORACLE-L
Sent: Monday, July 21, 2003 6:19 AM
That's not good enough.
I don't want to discard ANY
exception.
When dropping table, I don't want
to see error messages only if there is nothing to drop. While if let's
say there is a problem with privileges, it will go unnoticed. Or, when adding a
table, it's fine not be getting an error, if table already exists. But,
if there is no room to create a table, or to add a partition to the table, I
want to see this error message.
So, I still prefer my way of doing it (see scripts in
my original
message)
comparing to Oracle's script, you
refer to.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
[EMAIL PROTECTED]
Sent: Friday, July 18, 2003 6:10 PM
To: Multiple recipients of list
ORACLE-L
From ORACLE own script:
Rem
Rem Drop tables without raising
errors if they do not exist
Rem
declare
PROCEDURE
drop_force(tab varchar2) IS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || tab;
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
begin
drop_force('utl_recomp_invalid');
drop_force('utl_recomp_sorted');
drop_force('utl_recomp_compiled');
drop_force('utl_recomp_backup_jobs');
drop_force('utl_recomp_log');
end;
/
Alex.
-Original Message-
Sent: Friday, July 18, 2003 2:04 PM
To: Multiple recipients of list
ORACLE-L
Sure, I would.
But I can't wait till Oracle
turns around.
My scripts are executed by our
field engineers, who know next to nothing about Oracle, and the
only thing they can do is to check log files for error messages (and even this
is done automatically). Igor Neyman, OCP DBA [EMAIL PROTECTED]
-Original Message-
Goulet, Dick
Sent: Friday, July 18, 2003 3:44 PM
To: Multiple recipients of list
ORACLE-L
Igor,
True enough, but wouldn't you like it as part and parcel of the command? Dick
Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message-
Sent: Friday, July 18, 2003 4:35 PM
To: Multiple recipients of list
ORACLE-L
To avoid those errors in my scripts I'm checking data
dictionary for the existence of the object (fortunately, dynamic
sql helps here): REM Dropping synonym DECLARE lCounter integer; begin SELECT
COUNT(*) INTO lSyn
FROM
dba_synonyms WHERE synonym_name = 'PRCPV_REPORT_INFO' AND OWNER = 'PUBLIC'; IF
(lSyn = 1) THEN
EXECUTE IMMEDIATE 'drop PUBLIC SYNONYM PRCPV_Report_Info'; END IF; end; /
or:
REM Adding column
DECLARE lCounter integer;
begin
SELECT count(*) INTO lCounter
FROM DBA_TAB_COLUMNS
WHERE table_name = 'PRCP_MENU'
AND column_name = 'MENU_NAME'
AND owner = 'IPN_DBA';
IF (lCounter = 0) THEN
EXECUTE IMMEDIATE 'ALTER TABLE prcp_menu ADD menu_name
VARCHAR2(50) NULL';
END IF;
end;
/
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
--
Please see the official ORACLE-L
FAQ: http://www.orafaq.net
--
Author: Alex Feinstein
INET: [EMAIL PROTECTED]
Fat City Network Services --
858-538-5051 http://www.fatcity.com
San Diego,
California -- Mailing list and web
hosting services
-
To REMOVE yourself from this
mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note
EXACT spelling of 'ListGuru') and in the message BODY, include a line
containing: UNSUB ORACLE-L (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
RE: Job to run first Wednesday
This should fix the flaw:
select CASE WHEN to_char(to_date('09/04/2003','MM/DD/'), 'DD') = 6
THEN CASE WHEN to_char(to_date('09/04/2003','MM/DD/'),
'DY') = 'WED'
THEN to_date('09/04/2003', 'MM/DD/')
ELSE CASE WHEN
to_char(next_day(to_date('09/04/2003', 'MM/DD/'), 'WED'), 'DD') 6
THEN
next_day(last_day(to_date('09/04/2003','MM/DD/')),'WED')
ELSE next_day(to_date('09/04/2003',
'MM/DD/'), 'WED')
END
END
ELSE
next_day(last_day(to_date('09/04/2003','MM/DD/')),'WED')
END
from dual;
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Daniel Fink
Sent: Tuesday, July 22, 2003 4:04 PM
To: Multiple recipients of list ORACLE-L
Jared,
If you will closely examine the specs, the assumption is not
documented. Therefore, the application code must take into account that
the 1st Wednesday of the month may still be in the future.
select CASE WHEN to_char(to_date('08/07/2003','MM/DD/'), 'DD') = 6
THEN CASE WHEN to_char(to_date('08/07/2003','MM/DD/'),
'DY') = 'WED'
THEN to_date('08/07/2003', 'MM/DD/')
ELSE next_day(to_date('08/07/2003', 'MM/DD/'),
'WED')
END
ELSE
next_day(last_day(to_date('08/07/2003','MM/DD/')),'WED')
END
from dual;
Of course, there is a flaw in this logic. Can anyone spot it?
It's particularly nasty...
Daniel
[EMAIL PROTECTED] wrote:
Rachel,
The assumption is that the current day is already = the first
wednesday
of the month,
making it useful for the dbms_job interval.
Did you read the specs? :)
Jared
Rachel Carmichael [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/22/2003 01:14 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: Job to run first Wednesday
don't rush off to use it. I tried it, substituting August 1 and
got
September.
1* select
next_day(last_day(to_date('08/01/2003','MM/DD/')),'WED') from dual
SQL /
NEXT_DAY(
-
03-SEP-03
--- [EMAIL PROTECTED] wrote:
Hmm... much more elegant than mine, and everyone elses.
Guess I better RTFM the next_day function.
Mercadante, Thomas F [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/22/2003 12:34 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: Job to run first Wednesday
Josh,
With the following functions, you could probably get it to work:
select next_day(last_Day(sysdate),'WED') from dual
This (today) returns Wed, August 6th.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, July 22, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
Greetings,
How can I set the interval in my dbms job to have it run on the
first
Wednesday of every month? Is this even possible? I have been trying
to
noodle it thru for a week to no avail.
tia,
Josh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Josh Collier
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting
services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting
services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting
services
-
To REMOVE yourself from
RE: SHOW ERRORS not working
Not sure, what causes the problem. But, probably a workaround is to grant him rights to select from dba_errors. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Farnsworth, Dave Sent: Monday, July 21, 2003 10:45 AM To: Multiple recipients of list ORACLE-L I have a developer that has ALTER ANY PROCEDURE rights. He changes a procedure, then compiles it and it says there are errors. But when he does a SHOW ERRORS in SQPlus, it shows nothing. If I do the same thing I can see the errors from the compiled procedure. I assume this is a permissions thing but have not been able to figure this one out. Anyone have any ideas on this? Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Who Says Oracle does not listen
To avoid those errors in my scripts I'm checking data dictionary for the existence of the object (fortunately, dynamic sql helps here): REM Dropping synonym DECLARE lCounter integer; begin SELECT COUNT(*) INTO lSyn FROM dba_synonyms WHERE synonym_name = 'PRCPV_REPORT_INFO' AND OWNER = 'PUBLIC'; IF (lSyn = 1) THEN EXECUTE IMMEDIATE 'drop PUBLIC SYNONYM PRCPV_Report_Info'; END IF; end; / or: REM Adding column DECLARE lCounter integer; begin SELECT count(*) INTO lCounter FROM DBA_TAB_COLUMNS WHERE table_name = 'PRCP_MENU' AND column_name = 'MENU_NAME' AND owner = 'IPN_DBA'; IF (lCounter = 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE prcp_menu ADD menu_name VARCHAR2(50) NULL'; END IF; end; / Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Goulet, Dick Sent: Friday, July 18, 2003 3:04 PM To: Multiple recipients of list ORACLE-L http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_top. gif http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_top .gif Update TAR Go to End http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_bott om.gif http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_bot tom.gif http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_top. gif http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_top .gif TAR Number 3169102.996 Open Date 17-JUL-03 19:18:03 Support Identifier 1208611 Name Richard Goul Priority 4 Last Update 18-JUL-03 15:22:33 Product Oracle Server - Enterprise Edition Product Version 9.2.0.1.0 Platform HP-UX PA-RISC (64-bit) Detailed Status Soft Close TAR Reference n/a BUG Reference n/a Abstract http://metalink.oracle.com/images/black.gif DROP OBJECT NO_FAIL http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_bott om.gif http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_bot tom.gif http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_top. gif http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_top .gif Resolution History http://metalink.oracle.com/images/black.gif 17-JUL-03 19:18:03 GMT ### Selected Industry ### Discrete Manufacturing ### Reason current product functionality is insufficient. ### Today if you issue a drop table my_table; command and the table does not exist you get an error back. ### Detailed description of the Enhancement Request. ### It would be great if we had an addition to the drop object command, something like drop table my_table no_fail; which would always return Table dropped whether it existed or not. ### How the product can be changed to achieve the desired result. ### Have no idea. ### Reasons to consider the Enhancement Request. ### When one creates scripts you run them many times with whenever sqlerror exit or else have to look in a log file for errors. Many times not being able to drop an object is an error, but many times, like when your running a script for the first time, it isn't. Run any of the CAT scripts that you provide, you have a marathon time scanning the script for real errors, like could not extend and have to filter these nuisance errors. ### Business impact if the Enhancement is not considered. ### Well, many a DBA will continue to waste time reviewing these errors for no purpose. ### Enhancement is affecting an implementation milestone. ### NO ### Description of the business flow that is affected by this Enhancement ### Creating a database, installing any package third party or not, ETL processes, etc Contact me via : E-mail - [EMAIL PROTECTED] 17-JUL-03 19:19:19 GMT TAR has been assigned to an analyst -- Sending email. 17-JUL-03 19:46:59 GMT PROBLEM === Today if you issue a drop table my_table; command and the table does not exist you get an error back. It would be great if we had an addition to the drop object command, something like drop table my_table no_fail; which would always return Table dropped whether it existed or not. PROBLEM VERIFICATION Is this what you are requesting? This should be incorporated in our cat*.sql scripts so the output log file doesn't show all these ignorable errors/warnings and dba only has to look for 'real' errors. 17-JUL-03 19:47:26 GMT Email Update button has been pressed -- Sending email. 17-JUL-03 20:15:38 GMT New info : That's a part thereof. The Cat scripts are an example. Allow me if you please: This is from the CATREP.SQL script: drop synonym dbms_offline_snapshot * ERROR at line 1: ORA-01434: private synonym to be dropped does not exist What would be the enhancement, general in nature not just the cat* scripts, would be to have an option on the command that allows it to not fail. Namely in this particular case the command would be drop synonym dbms_offline_snapshot nofail; which would have return Synonym dropped.. 17-JUL-03 21:20:03 GMT This would have to be filed
