RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
Well, you can't. Windows is different from other systems in that you have these "services" that have to be running in the background. Once the services are running, you can use OEM to shutdown and startup the database. But the service has to be running first. Sorry. Have you looked at OEM Jobs? Maybe you can create an OEM job to start the service. The DOS command is: NET START OracleServiceSID and NET STOP OracleServiceSID Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- 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 Softhome"To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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, incl
RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to 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 Softhome"To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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 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).
RE: !!Please Read - Oracle-L is moving!!
I agree. The *least* we can do is to subscribe to the service. Jared had all the other work getting the service started. Good job Jared. We love you, we really really do. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, January 28, 2004 12:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: !!Please Read - Oracle-L is moving!! may i just say - this resource is worth the minimal effort it took -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED]Sent: Wednesday, January 28, 2004 12:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: !!Please Read - Oracle-L is moving!!You sir, have obviously never done this. I have. :) Nor read my first post on the matter. No, it would not be easier, not by a long shot. This is free service, so my thinking is, share the workload. Jared <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/27/2004 11:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: !!Please Read - Oracle-L is moving!!Hi List Manger- Couldn't a immigration of our subscribed accounts been the most logical and error free option ? All this fire would have been avaoided. CSW Simon.
RE: Getting sysdate across a DB link
Mladen, there you go again! LOL. now go back and trade your Wang in for a new one. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, January 26, 2004 2:04 PM To: Multiple recipients of list ORACLE-L On 01/26/2004 01:34:24 PM, "Mercadante, Thomas F" wrote: > Ashish, > > Why do you think that the dates would be different on the two > machines > - is > one across the international date line? Shouldn't the dates be the > same? Thomas, if we learned anything from Einstein, it is that the time is relative to the observer and depends on the curvature of the space, speed and alike. This is particularly easy to prove when it comes to computers, because very seldomly do two computers have exactly the same time. As you are probably very well aware of, the central theorem of the relativity theory reads as follows: "The duration of a second depends on which side of the restroom door is the observer." Unfortunately, taking one server as a reference doesn't help because even if it is decreed that everybody should invoke the get_date function from the central server, there will be a developer who will smuggle a sysdate into the code. I've seen machines 5" apart differing in time for more then a hour. It becomes especially interesting on the days when DST takes effect. To make the long story short, Thomas, your Newtonian world is obsolete. -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Getting sysdate across a DB link
Title: Getting sysdate across a DB link Ashish, Why do you think that the dates would be different on the two machines - is one across the international date line? Shouldn't the dates be the same? How about getting the time from both servers - they *might be* different by a few seconds. Tom Mercadante Oracle Certified Professional -Original Message-From: Ashish Sahasrabudhe [mailto:[EMAIL PROTECTED]Sent: Monday, January 26, 2004 11:14 AMTo: Multiple recipients of list ORACLE-LSubject: Getting sysdate across a DB link I'm trying to get the value of SYSDATE on a remote server. I have a database link to the server, but I'm not sure how to force SYSDATE to be evaluated on the remote machine. Following query gives same results, both dates are same. select sysdate local_date, remote_date from ( select sysdate remote_date from [EMAIL PROTECTED]) How can this be done? Thanks
RE: internal date value
Harry,
Look at the to_char function in Oracle. It will convert a date field to
*any* format you want.
for example:
select to_char(date_field,'mm/dd/ hh24miss') will return a date in the
format as noted. You have about as many options as you probably need. You
can combine as many format and functions as you think you need.
to_char translates dates to chars and to_date does the opposite - but both
use the same format statements.
This should work fine for you.
And for you info - Oracle dates are stored internally based on a date going
back many centuries - not just to 1968!
Good Luck
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Friday, January 23, 2004 3:09 PM
To: Multiple recipients of list ORACLE-L
Thomas:
I'm a SAS guy who must pull Oracle data from the back-end DB.
SAS stores dates internally as elapsed days since Jan 1, 1960. If I request
an Oracle date field, SAS creates a datetime variable, number of seconds
since midnight Jan 1, 1960. Rather than use SAS functions to extract the
date ( e.g. datepart function ), I'd like to push that back to Oracle if
possible and create a simple date field on SAS. I can accomplish the
desired effect with:
select date_fld - to_date('01Jan1960','ddmon') as sas_date
but I was hoping there was an Oracle function to surface the internal value
( appears to be days since Jan 1, 1968 ). Presumably such a function would
be more efficient.
Thanks for your help.
-Original Message-
Sent: January 23, 2004 2:50 PM
To: Multiple recipients of list ORACLE-L
Harry,
Can you explain why you need to raw internal value? Just curious.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Friday, January 23, 2004 1:40 PM
To: Multiple recipients of list ORACLE-L
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).
--
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: 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 B
RE: internal date value
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Friday, January 23, 2004 3:09 PM
To: Multiple recipients of list ORACLE-L
Thomas:
I'm a SAS guy who must pull Oracle data from the back-end DB.
SAS stores dates internally as elapsed days since Jan 1, 1960. If I request
an Oracle date field, SAS creates a datetime variable, number of seconds
since midnight Jan 1, 1960. Rather than use SAS functions to extract the
date ( e.g. datepart function ), I'd like to push that back to Oracle if
possible and create a simple date field on SAS. I can accomplish the
desired effect with:
select date_fld - to_date('01Jan1960','ddmon') as sas_date
but I was hoping there was an Oracle function to surface the internal value
( appears to be days since Jan 1, 1968 ). Presumably such a function would
be more efficient.
Thanks for your help.
-Original Message-
Sent: January 23, 2004 2:50 PM
To: Multiple recipients of list ORACLE-L
Harry,
Can you explain why you need to raw internal value? Just curious.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Friday, January 23, 2004 1:40 PM
To: Multiple recipients of list ORACLE-L
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).
--
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: 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: 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 EXA
RE: internal date value
Harry, Can you explain why you need to raw internal value? Just curious. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 23, 2004 1:40 PM To: Multiple recipients of list ORACLE-L 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). -- 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).
RE: Metalink on the blink
Title: Metalink on the blink but their education site is off the air (at least it was an hour ago) Tom Mercadante Oracle Certified Professional -Original Message-From: Goulet, Dick [mailto:[EMAIL PROTECTED]Sent: Thursday, January 22, 2004 10:09 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Metalink on the blink A little slow, but nothing out of the ordinary. Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Adams, Matthew (GECP, MABG, 088130) [mailto:[EMAIL PROTECTED]Sent: Thursday, January 22, 2004 9:59 AMTo: Multiple recipients of list ORACLE-LSubject: Metalink on the blink Is anyone else having problems with Metalink this morning or is it just us? We can't log in at all. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] "The swim only hurt once - from the beginning to the end" - Doc Counsilman on swimming the English Channel at age 58
RE: Spool to Excel File
"SYLK allows cell references, etc, if needed, which you won't get with CSV. " Ahh. but you can with my method! If you use tab separated columns, you can also generate formula's that look like text, but work just fine in the spreadsheet! Tom Mercadante Oracle Certified Professional -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 20, 2004 3:44 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Spool to Excel FileStrange, no one has mentioned OWA_SYLK. Do a search on SYLK at asktom.oracle.com There are 2 versions, one for web output and one for excel output. SYLK allows cell references, etc, if needed, which you won't get with CSV. Jared "Mudhalvan, Moovarkku" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/19/2004 08:44 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Spool to Excel FileDear Friends, I am trying to send output from SQLPlus to Excel file. If anyone did the same before please let me know. Thank YouMudhalvan M.M-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mudhalvan, Moovarkku INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: tnsnames.ora not working ?
Reuben, If the normal connection is throwing an error, then it stands to reason that the seond one would not connect either. You need to get a connection working first before you try something else. The Oracle error you are getting is complaining about the service_name entry. Is this the same value for the database that you are trying to connect to? Is the database advertising itself as a service of dev_db? Fix this first and get the connection to work via the normal method. And then you can try the other method. And, finally, why in the world do you want to do this? Good Luck. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, January 20, 2004 10:49 AM To: Multiple recipients of list ORACLE-L Hello, I'm trying to add description in my $ORACLE_HOME/network/admin/tnsnames.ora, but it seems that the client (ie. sqlplus) wont use it. Whenever I try to connect to the service using sqlplus, I got : $> sqlplus Enter user-name: [EMAIL PROTECTED] Enter password: * ORA-12154: TNS:could not resolve service name I tried to add the description to my ~/.tnsnames.ora too with no luck. The entry in the tnsnames.ora is: DEV_DB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dev_db) ) ) (note: I removed the real hostname for privacy/security reason of course) However, when I use sqlplus using the following way: $> sqlplus Enter user-name: developer@(description=(address=(protocol=tcp)(host=hostname)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = dev_db))) Enter password: * It would work, where all the information from the description is just a copy-paste from the tnsnames.ora file. Is there anything I overlook? Sorry if this is kinda a newbie question. I'm still learning my way around this. I'm using Oracle9i on Redhat Linux. Thanks for any help. Reuben D. Budiardja -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - "To be a nemesis, you have to actively try to destroy something, don't you? Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect." - Linus Torvalds - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Re[2]: Oracle vs Mysql
The RPT & RPF Oracle class was what made me go looking very quickly for a batch Oracle tool. Then I found SQR. (This was all before PL/SQL and the current versions of Oracle Reports). We bought it and the rest was history. Why Oracle didn't buy SQR when they had a chance amazes me. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, January 20, 2004 9:40 AM To: Multiple recipients of list ORACLE-L Careful Mladen, your revealing your age!! Bet you remember RPT & RPF as well!! Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, January 20, 2004 2:04 AM To: Multiple recipients of list ORACLE-L On 2004.01.19 23:39, Jonathan Gennick wrote: > I used to use a SQL Module compiler. Not with Oracle though. > It's rare for me to run into someone else who likes that > approach. Actually, it's rare for me to encounter someone > who's even heard of it... Jonathan, I've been around for a long time. I've seen things like DataLens for Lotus123, SQL*Calc, Easy*SQL, then there was an Oracle version of then popular DB2 tool, which looked like an IBM 3874 terminal on top of VT320, SQL*Graph does deserve a honorable mention, then there was PRO*Pascal, and a myriad of other exotic stuff that I cannot remember now. I was laughing when I saw "UNDO TABLESPACES" in 9i. What exactly is a difference between a specialized undo tablespace and a file that was just laying around and couldn't be touched and was named "Before Image file" or "BI file". Logical names (another concept that many youngsters are probably unfamiliar with) were usually VAX$BI or ORACLE$BI. Unfortunately, discussions like that are not part of OCP curriculum. The file is not really part of the database, you can't create any objects in it, it manages itself and it stores the old values of oracle blocks, in case rollback is needed. I could be talking about "BI" file or "UNDO TABLESPACE", there is no difference whatsoever. -- 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: 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re[2]: Oracle vs Mysql
Ahhh. Sql*Calc, Sql*Graph, Sqr & EasySqr. Those were the good old days. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, January 20, 2004 8:05 AM To: Multiple recipients of list ORACLE-L Mladen Gogala scribbled on the wall in glitter crayon: > On 2004.01.19 23:39, Jonathan Gennick wrote: > >> I used to use a SQL Module compiler. Not with Oracle though. >> It's rare for me to run into someone else who likes that >> approach. Actually, it's rare for me to encounter someone >> who's even heard of it... > > Jonathan, I've been around for a long time. I've seen things like > DataLens for Lotus123, SQL*Calc, Easy*SQL, then there was an Oracle > version of then popular DB2 tool, which looked like an IBM 3874 > terminal on top of VT320, SQL*Graph does deserve a honorable mention, hey, i resemble that remark.;-) i enjoyed using SQL*Calc and SQL*Graph and i even remember the very first version of SQR when it stood for Structured Query Reporter.;-) oh damn, have we been at this too long?;-) -- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] Nothing in life is to be feared. It is only to be understood. - Marie Curie -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Spool to Excel File
Mudhalvan,
I generate files that excel can open all the time. they are not actual
"real" excel files, but Excel can deal with them quite easily.
Here is a tablespace report I run every week. Note the use of the CHR(9)'s.
This is a TAB character. This forces each column into a new cell in the
spreadsheet. CHR(10) is a line-feed.
Feel free to "borrow" all of this!
Hope this helps!
SET serveroutput ON
SET feedback OFF
SET lines 150
SET pages 100
SET trimspool ON
exec dbms_output.enable(10)
spool tbslspace_rpt.xls
DECLARE
CURSOR UpTime IS
SELECT INITCAP(instance_name) Instance_Name ,INITCAP(Host_Name) Host_Name
,Version,
ROUND(SYSDATE+1-startup_time) || DECODE(ROUND(SYSDATE+1-startup_time),1,'
Day ', ' Days ') ||
MOD(ROUND((SYSDATE+1 - startup_time) * 24),24) || ' Hours ' ||
MOD(ROUND((SYSDATE+1 - startup_time) * 24*60),60) || ' Minutes ' UpTime
FROM v$instance;
UpTime_Rec UpTime%ROWTYPE;
CURSOR TblSpace IS
SELECT d.status , d.tablespace_name ,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'999,990') Tbs_Size,
TRUNC(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024)Used,
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00')
Used_Pct ,
DECODE(SIGN(80 - NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100,
0)),-1,'** Warning > 80% **',NULL) Msg
FROM sys.DBA_TABLESPACES d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM DBA_DATA_FILES GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM DBA_FREE_SPACE GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
ORDER BY 2;
TblSpace_Rec TblSpace%ROWTYPE;
c_email_list VARCHAR2(300);
mail_message VARCHAR2(32000);
mail_message1 VARCHAR2(32000);
loc_start_time DATE;
TblSpace_Msg NUMBER := 0;
BEGIN
BEGIN
SELECT email_notify_txt
INTO c_email_list
FROM WTW_JOB_NOTIFY
WHERE job_name = UPPER('Wtw_Report_Tablespaces');
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
loc_start_time := SYSDATE;
OPEN UpTime;
FETCH UpTime INTO UpTime_Rec;
CLOSE UpTime;
dbms_output.put_line(CHR(9) || CHR(9) ||
UpTime_Rec.Instance_Name || INITCAP('
Uptime/TABLESPACE Report FOR ') ||
TO_CHAR(SYSDATE,'fmMonth ddth, '));
dbms_output.put_Line(CHR(9) || CHR(9) ||
'UpTime : ' || UpTime_Rec.UpTime);
dbms_output.put_Line(
'Status' || CHR(9) ||
INITCAP('TABLESPACE Name') || CHR(9) ||
INITCAP('SIZE (M)') || CHR(9) ||
'Used (M)' || CHR(9) ||
'Used (Pct)' || CHR(9) ||
'Message');
OPEN TblSpace;
FETCH TblSpace INTO TblSpace_Rec;
WHILE TblSpace%FOUND LOOP
dbms_output.put_Line(
TblSpace_Rec.Status || CHR(9) ||
TblSpace_Rec.Tablespace_Name || CHR(9) ||
TblSpace_Rec.Tbs_Size|| CHR(9) ||
TblSpace_Rec.Used|| CHR(9) ||
TblSpace_Rec.Used_Pct|| CHR(9) ||
TblSpace_Rec.Msg);
IF TblSpace_Rec.Msg IS NOT NULL THEN
TblSpace_Msg := 1;
END IF;
FETCH TblSpace INTO TblSpace_Rec;
EXIT WHEN TblSpace%NOTFOUND;
END LOOP;
CLOSE TblSPace;
END;
/
spool OFF
exit
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, January 19, 2004 11:44 PM
To: Multiple recipients of list ORACLE-L
Dear Friends,
I am trying to send output from SQLPlus to Excel file. If any
one did the same before please let me know.
Thank You
Mudhalvan M.M
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mudhalvan, Moovarkku
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: [
RE: Connection Problem
Mark, I don't think that all three on the same port is a problem - heck - I do it all the time. I would comment out the GLOBAL_DBNAME entry. It certainly is not required. And output from lsnrctl would be helpful. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 16, 2004 9:20 AM To: Multiple recipients of list ORACLE-L I've asked for the out put on a lsnrctl status already, but haven't heard back from him as yet.. The "." does look strange.. Also, what would the impact be to have the GLOBAL_DBNAME set to the same value for all three instances? Would it not be better to set it to the same name as the instance name for each? The same goes for port too.. I thought that all three on the same port would be OK? Cheers Mark -----Original Message- Mercadante, Thomas F Sent: 16 January 2004 13:30 To: Multiple recipients of list ORACLE-L Mark, The (GLOBAL_DBNAME= l1000.) entry looks strange. Should there be a '.' in there? I've never seen this entry in this file. What does : lsnrctl status & lsnrctl services show? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 16, 2004 6:14 AM To: Multiple recipients of list ORACLE-L Hi All, I'm helping out a friend of mine who's having problems with connecting from an NT system to an HP system running Oracle 8.0.5 (don't ask!). They keep getting an "12154 - Could not resolve service name" error.. The TNSNAMES.ORA file looks OK, and I feel there is something iffy about their listener set up. Here's there set-up: TNSNAMES.ORA: extproc_connection_data = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = LIVE)) (CONNECT_DATA = (SID = extproc)) ) LIVE = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host= l1000)(Port= 1521)) (CONNECT_DATA = (SID = LIVE)) ) TEST = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host= l1000)(Port= 1521)) (CONNECT_DATA = (SID = TEST)) ) ARCHIVE = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host= l1000)(Port= 1521)) (CONNECT_DATA = (SID = ARCHIVE)) ) LISTENER>ORA: LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC)(KEY= FROUDE)) (ADDRESS= (PROTOCOL= IPC)(KEY= PNPKEY)) (ADDRESS= (PROTOCOL= TCP)(Host= l1000)(Port= 1521)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME= l1000.) (ORACLE_HOME= /usr/oracle/product/8.0.5) (SID_NAME = LIVE) ) (SID_DESC = (GLOBAL_DBNAME= l1000.) (ORACLE_HOME= /usr/oracle/product/8.0.5) (SID_NAME = TEST) ) (SID_DESC = (GLOBAL_DBNAME= l1000.) (ORACLE_HOME= /usr/oracle/product/8.0.5) (SID_NAME = ARCHIVE) ) (SID_DESC = (SID_NAME = extproc) (ORACLE_HOME = /usr/oracle/product/8.0.5) (PROGRAM = extproc) ) ) STARTUP_WAIT_TIME_LISTENER = 0 CONNECT_TIMEOUT_LISTENER = 10 TRACE_LEVEL_LISTENER = OFF When he tries to connect "user/[EMAIL PROTECTED]" the connection fails - and the same for TEST and ARCHIVE. The thing that sticks out to me is the GLOBAL_DBNAME parameter being set to "l1000" for every instance - do you think this could be causing the problem? Is there anything else that catches your eye? Cheers! Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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
RE: Connection Problem
Mark, The (GLOBAL_DBNAME= l1000.) entry looks strange. Should there be a '.' in there? I've never seen this entry in this file. What does : lsnrctl status & lsnrctl services show? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 16, 2004 6:14 AM To: Multiple recipients of list ORACLE-L Hi All, I'm helping out a friend of mine who's having problems with connecting from an NT system to an HP system running Oracle 8.0.5 (don't ask!). They keep getting an "12154 - Could not resolve service name" error.. The TNSNAMES.ORA file looks OK, and I feel there is something iffy about their listener set up. Here's there set-up: TNSNAMES.ORA: extproc_connection_data = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = LIVE)) (CONNECT_DATA = (SID = extproc)) ) LIVE = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host= l1000)(Port= 1521)) (CONNECT_DATA = (SID = LIVE)) ) TEST = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host= l1000)(Port= 1521)) (CONNECT_DATA = (SID = TEST)) ) ARCHIVE = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host= l1000)(Port= 1521)) (CONNECT_DATA = (SID = ARCHIVE)) ) LISTENER>ORA: LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC)(KEY= FROUDE)) (ADDRESS= (PROTOCOL= IPC)(KEY= PNPKEY)) (ADDRESS= (PROTOCOL= TCP)(Host= l1000)(Port= 1521)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME= l1000.) (ORACLE_HOME= /usr/oracle/product/8.0.5) (SID_NAME = LIVE) ) (SID_DESC = (GLOBAL_DBNAME= l1000.) (ORACLE_HOME= /usr/oracle/product/8.0.5) (SID_NAME = TEST) ) (SID_DESC = (GLOBAL_DBNAME= l1000.) (ORACLE_HOME= /usr/oracle/product/8.0.5) (SID_NAME = ARCHIVE) ) (SID_DESC = (SID_NAME = extproc) (ORACLE_HOME = /usr/oracle/product/8.0.5) (PROGRAM = extproc) ) ) STARTUP_WAIT_TIME_LISTENER = 0 CONNECT_TIMEOUT_LISTENER = 10 TRACE_LEVEL_LISTENER = OFF When he tries to connect "user/[EMAIL PROTECTED]" the connection fails - and the same for TEST and ARCHIVE. The thing that sticks out to me is the GLOBAL_DBNAME parameter being set to "l1000" for every instance - do you think this could be causing the problem? Is there anything else that catches your eye? Cheers! Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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).
RE: Opinion on Changing Names
Ramon, Take a look at the DBMS_RANDOM package. You can replace the name with a random number. That ought to do the trick. Good Luck Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 16, 2004 7:49 AM To: Multiple recipients of list ORACLE-L > Hi list, > > Every night I update the development DB through a cron job, now I have the requirement of change or scramble the names of our customers to protect and privacy of information. I have been thinking of adding a procedure to the script that update all the names using a REPLACE clause and change some letters. > > Is there any way or a better one to accomplish that ? > > Scenario 8.1.7.4 from HP-UX (Prod) to RH AS 1.2 8.1.7.4 (lab). > > TIA > > Ramon E. Estevez > [EMAIL PROTECTED] > 809-535-8994 > > -- 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).
RE: table reorganizations
Chris, I would argue that in your two examples, nothing needs to be done if you are using Locally Managed Tablespaces. All of the free space that your deletes generated would be reused by new inserts. When you say "not good for FTS", I think you are wrong. Have you tried testing this statement? How much "slower" is it? Of course, I am talking about using Oracle 9i. Prior versions behaved much differently. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 09, 2004 6:09 AM To: Multiple recipients of list ORACLE-L Richard, I agree there are a number of reasons for reorganising tables. LMTs remove the need to reorganise a tablespace but not to reorganise a table. Two further real- ilfe examples of table reorgs: 1) The purge programs have at last been written and run deleting data > 2 years old. The system's been running for 4 years. So in simple terms most of the tables are approx 50% empty. You need to reorg in this case. 2) A "transaction log" table is inserted to throughout the day and most of the night. A clear down processing job runs at the end of the day and deletes all the rows its processed, but more rows are being added. So the table is now < 1% full. Not good for FTS. So instead of a conventional reorg we implemented a nightly "table-swap". This meant locking the source table, copying it's contents to a replica empty single extent table, target table. The names of the target and source tables are swapped, hence "table-swap". The new source table is now available to the application and the original source is truncated and ready to be the target in 24 hrs time. Cheers, Chris Dunscombe Quoting Richard Foote <[EMAIL PROTECTED]>: > MessageHi Thomas, > > Never say never (oh bugger, I've just gone and done it myself). > > A large table accessed via a FTS for various important reporting requirements > has permanently shrunk in size from 10G to 100M (say list of Informix > customers ;) > > Business requirements have changed and you need to add some columns to a > table resulting in mucho row migration. > > You were told (incorrectly) that rows would grow significantly after loading > (honestly) but now the 80 pctfree value you've set is causing problems for > other really important reports. > > There are of course other cases but you get my point ;) > > Cheers > > Richard > - Original Message - > From: Mercadante, Thomas F > To: Multiple recipients of list ORACLE-L > Sent: Thursday, January 08, 2004 6:34 AM > Subject: RE: table reorganizations > > > Jolene, > > Tables should never *need* to be reorganized. This is an old falacy. If > you know how big a table is going to grow, say in a year, then place it in a > Locally Managed tablespace with extent sizes to hold enough data for one year > (say 1M). > > You should never have to reorganize a table. > > Tom Mercadante > Oracle Certified Professional > -Original Message- > From: Shrake, Jolene [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 07, 2004 2:39 PM > To: Multiple recipients of list ORACLE-L > Subject: table reorganizations > > > What SQL statement do you use to identify tables that need > reorganization? > > How do you identify tables that are used in full table scans? How often > do you run this query? > > Thanks, > Jolene > Chris Dunscombe [EMAIL PROTECTED] - Everyone should have http://www.freedom2surf.net/ -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple question
Remove the r number(10); and it should be fine. Tom Mercadante Oracle Certified Professional -Original Message-From: oranew2004 [mailto:[EMAIL PROTECTED]Sent: Thursday, January 08, 2004 3:24 PMTo: Multiple recipients of list ORACLE-LSubject: Re: simple question CREATE OR REPLACE PROCEDURE labware_admin.truncate_tables( schema_owner IN VARCHAR2) AS r number(10); BEGIN FOR r IN (SELECT table_name FROM dba_tables WHERE owner=schema_owner) LOOP EXECUTE IMMEDIATE 'truncate table schema_owner.'||r.table_name; END LOOP; END; ERROR: Line 14 Column 55 PLS-00364: loop index variable 'R' use is invalid. Any ideas? Thank you! Do you Yahoo!?Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
RE: simple question
Greg, Try this: create or replace procedure truncate_tables(in_owner in varchar2) is BEGIN FOR r IN (SELECT table_name FROM dba_tables WHERE owner=in_owner) LOOP EXECUTE IMMEDIATE 'truncate table ' || in_owner || '.' || r.table_name; END LOOP;END;/ You will need to grant select on DBA_TABLES to the owner of this procedure. You may also need to grant ALTER ANY TABLE to the same user to allow you to perform the Truncate function. I forget exactly which priv you need to do this - but something extra needs to be granted to the owner of the procedure to perform the trancate function. Good Luck Tom Mercadante Oracle Certified Professional -Original Message-From: Greg Sorrel [mailto:[EMAIL PROTECTED]Sent: Thursday, January 08, 2004 2:55 PMTo: Multiple recipients of list ORACLE-LSubject: simple question Hi! I need to wrap this SQL into SP where I can pass new owner name. I'm planning create db table with all owners I need to pass. BEGIN FOR r IN (SELECT table_name FROM dba_tables WHERE owner='TRAIN1') LOOP EXECUTE IMMEDIATE 'truncate table TRAIN1.'||r.table_name; END LOOP;END;/ Thanks. Greg Do you Yahoo!?Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
RE: DBA position
Title: RE: DBA position I like the "relocation" info. It says 'None' and then 'click here'. When you do that, it says: None - Lifetouch offers no assistance. I guess this is in case you don't understand the meaning of the word "None". Tom Mercadante Oracle Certified Professional -Original Message-From: April Wells [mailto:[EMAIL PROTECTED]Sent: Thursday, January 08, 2004 2:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: DBA position WORSE than that! they want TEN YEARS dba experience with Oracle AND Sql Server experience, and management and BS and Masters and a red cape... 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: Joe Testa [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 08, 2004 1:45 PM To: Multiple recipients of list ORACLE-L Subject: Re: DBA position this always amazes me: # Bachelor's degree in Computer Science, Engineering, or related discipline. # Master's degree is desirable. guess that leaves me out :) joe Jared Still wrote: >http://careers.lifetouch.com/Job.asp?Job_id=1050&seas=Office+%2D+FullTime > >The domain name may be familiar, a prolific poster on this >list happens to be there as well. > >The location is Minneapolis, MN, USA > > > > > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.
RE: Difference on ArchiveLog
Mauricio, Think about it. Your redo logs are now larger, and they have to be cleared out by the archiver less often. So archivelog files are produced less often. So you have less physical archive log files being produced. The other data that *used to be* in archivelog files are now in the Redo Log files. It hasn't gone anyplace - it's just waiting for the Redo Log file to fill up so it can be archived. The metric that you should be looking at is not how much archive log is being generated - but how often a log file switch is being performed. I like no less than 20 minutes - that is - an archivelog file is not being created more often that 20 minutes. This ensures that the database is never waiting for the archiver to complete it's work (thus stopping updates) before it can continue. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message-From: Mauricio "Vilez [mailto:[EMAIL PROTECTED]Sent: Wednesday, January 07, 2004 3:10 PMTo: Multiple recipients of list ORACLE-LSubject: Difference on ArchiveLog Hello Everybody Some days ago the database I work on had 3 logfiles that sized 100M and the database was generating 4G of archive daily. I changed the size to 20M and the database began to generate 2G of archive daily, then I changed to 50M and It began to generate 3G of archive daily. So I don't understand why this difference occurs. The database I'm working on is oracle 8i and I'is on Windows NT. Regards Mauricio Vélez Do you Yahoo!?Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
RE: table reorganizations
Title: Message Jolene, Tables should never *need* to be reorganized. This is an old falacy. If you know how big a table is going to grow, say in a year, then place it in a Locally Managed tablespace with extent sizes to hold enough data for one year (say 1M). You should never have to reorganize a table. Tom Mercadante Oracle Certified Professional -Original Message-From: Shrake, Jolene [mailto:[EMAIL PROTECTED]Sent: Wednesday, January 07, 2004 2:39 PMTo: Multiple recipients of list ORACLE-LSubject: table reorganizations What SQL statement do you use to identify tables that need reorganization? How do you identify tables that are used in full table scans? How often do you run this query? Thanks, Jolene
RE: Verisign and MetaLink
Jesse, Aren't these pieces of mail great? Talk about Spam. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, January 07, 2004 10:34 AM To: Multiple recipients of list ORACLE-L So I got two e-mails today from Oracle Support warning about Verisign Certs expiring today and to check MetaLink (thanks for the advanced warning guys). I've been trying for over an hour now and am getting no joy. Anyone know what the Verisign Certs are used for in Oracle products? I don't think we have any of those products, but... TIA, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Upgrade
Now now all you MS bigots. Just leave us alone. There are several of us on this list who are forced into using MS because of management decisions. And our systems are running just fine. Just leave us alone. You don't see us posting all of the other op systems problems (all 2 of them). thank you. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, January 06, 2004 6:09 AM To: Multiple recipients of list ORACLE-L I'm confused: did you REALLY expect anything out of microslop to work according to expectation? Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - > Dick, > What kind of a nautical person are you??? the NT box will not even > make a good anchor because the sides are flat and it will drag on the > bottom during a small wind or current. -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Can't connect MTS from remote.
ourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wendry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Oracle OID and Production Databases Sharing the same
Paul, Ron, Ravi & Brian, Thanks for the replies. I should have known that keeping them separate was the smart thing to do. We've tried twice now to apply patch 62 (a security notice) to the 9.2.0.4 software. It's failed both times. The first with Java errors, and the second with something else. If we had two separate instances, at least our production instance would probably have been patched easily and just the OID instance would be "in the weeds". Now I need to schedule some other down time to create a new Oracle home. Thanks again. Tom Mercadante Oracle Certified Professional -Original Message-From: paul bennett [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 30, 2003 6:09 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Oracle OID and Production Databases Sharing the same Hi Ron and Brian: We have been running the OID database in the same ORACLE_HOME as a production database since Nov 2002. I am running both 8.1.7 on AIX 4.33 and 9.2.0.4 on AIX 5.1. I feel using the same ORACLE_HOME or separate ORACLE_HOMES depends on how you plan to use OID. On the servers where the same ORACLE_HOME is being shared we are only using generic LDAP functions. If you plan to use Oracle LDAP/OID functionality such as Single-Sign-On then you I believe you need to have them in separate ORACLE_HOMES. Whenever you need the Infrastructure that is part of 9iAS I would say it should be a separate ORACLE_HOMES with some possible exceptions. I have not had any issues applying patchsets so far when sharing a ORACLE_HOME. Collaboration Suite is different problem. OCS requires an Infrastructure that must be installed in a 9.0.4.1 database. This version of the database is not supported on AIX 5L. (This is because 9.0.4.1 came out before 5.L was out according to Oracle.) It is only supported on AIX 5L when installed as part of OCS. The Infrastructure for OCS must be in a separate ORACLE_HOME. There may be a way to have the OID database outside of the Infrastructure but I would not recommend it. You also need to make sure the server where the Infrastructure is installed is not likely to change because moving the OCS Infrastructure is almost impossible to do. In summary I would say the OID database should be a separate ORACLE_HOME unless you only plan to use it for non-Oracle LDAP functions. Paul Bennett>>> [EMAIL PROTECTED] 12/30/03 02:04PM >>>Brian,From reading into your message I get the impression the you wanted touse the OID supplied database for your other database and supportfrowned upon it. Was there any problem with using the "normal" Oracledatabase for the OID database?Ron>>> [EMAIL PROTECTED] 12/30/2003 1:59:26 PM >>>I would opt to separate them. I ran into some problems with a Collaboration Suite install, where Iwanted to use the OID database, to store my files data as well. Itfailed in a spectacular fashion. An Oracle support analyst said thatwas a bad idea. When I asked him if I could just create anotherinstance with the same $ORACLE_HOME, he frowned on it, because thedatabase version used by OID isn't really *that* supported by Oracle. I don't know what makes it so special, but I got the impression fromhim that it was a fragile database release, meant for use by Oracleonly.My $.02 - Brian- Original Message -Date: Tuesday, December 30, 2003 12:34 pm> All,> > We have a AIX 5.2 box serving a new 9.2 database. We also > installed the OID> software on this box. Oid requires it's own database. So we havetwo> instances on this machine.> > We received notice from Oracle that a security patch (#62) was > required. We> were applying the patch and it failed because some Java stuff > needed to be> installed separatly first. > > Now I'm wondering if we should have created two Oracle Homes - one > for the> production database and one for the OID database. I'm worried > that we will> always have problems keeping these two instances sharing the same > Oraclehome.> > Anybody got an opinion?> > Tom Mercadante> Oracle Certified Professional> > -- > 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 hostingservices> ---> --> To REMOVE yourself from this mailing list, send an E-Mail message> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in> the message BODY, include
Oracle OID and Production Databases Sharing the same Oracle Home
All, We have a AIX 5.2 box serving a new 9.2 database. We also installed the OID software on this box. Oid requires it's own database. So we have two instances on this machine. We received notice from Oracle that a security patch (#62) was required. We were applying the patch and it failed because some Java stuff needed to be installed separatly first. Now I'm wondering if we should have created two Oracle Homes - one for the production database and one for the OID database. I'm worried that we will always have problems keeping these two instances sharing the same Oracle home. Anybody got an opinion? Tom Mercadante Oracle Certified Professional -- 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).
RE: Export/Import issue.
Avnish, Are you absolutely sure that the target table is either gone before the import, or empty? You should not be getting a unique constraint violation if the table does not exists before import. The step that Oracle import follows for importing is: Create the table import the data create constraints create triggers apply grants. If you are getting a unique constraint violation during import, then the constraints exist before the table is being imported which leads me to believe everthing is not as clean as you think. Either that, or the uniqe constraint in the source database has been disabled, and you have bad data. Make sure you drop the target table before you start the import. And look at the source data table's constraints to make sure they are all enabled. Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 1:44 PM To: Multiple recipients of list ORACLE-L I am trying to export a table from one database to another. Table in target database is dropped before importing and is being created as part of import process. During import process data Oracle is complaining Unique constraint violated and throwing out following messages. Its complaining on index abhset1 which is on columns [asset, book, yr_end_date]. I tried to compare data in source and target. Somehow yr_end_date for year 2003 is changing to 2002 while importing data. Table doesnt have any triggers. Indexes in both source and target are same. Table definition is exactly same and created during import. Tried to export 2-3 times but same result. Anybody ran into this issue before. How should I approach to troubleshoot. Thanks Error Message Column 1 101536 Column 2 SL Column 3 5 Column 4 31-DEC-2002:00:00:00 Column 5 53.29 Column 6 0 Column 7 4.84 Column 8 96 Column 9 01-JAN-1700:00:00:00 IMP-00019: row rejected due to ORACLE error 1 IMP-3: ORACLE error 1 encountered ORA-1: unique constraint (LDDEV.ABHSET1) violated Column 1 101537 Column 2 SL Column 3 5 Column 4 31-DEC-2002:00:00:00 Column 5 38.75 Column 6 0 Column 7 4.84 Column 8 96 Column 9 01-JAN-1700:00:00:00 IMP-00019: row rejected due to ORACLE error 1 IMP-3: ORACLE error 1 encountered ORA-1: unique constraint (LDDEV.ABHSET1) violated Data in Source ASSET BOOK COMPANY YR_END_DA DEPR_YTD DEPR_LTD BEG_DEPR_LTD LIFE METH_SW_D -- -- -- - -- -- -- - 101536 SL 5 31-DEC-02 4.84 0 0 96 01-JAN-00 101536 SL 5 31-DEC-03 53.29 0 4.84 96 01-JAN-00 ASSET BOOK COMPANY YR_END_DA DEPR_YTD DEPR_LTD BEG_DEPR_LTD LIFE METH_SW_D -- -- -- - -- -- -- - 101537 SL 5 31-DEC-02 4.84 0 0 96 01-JAN-00 101537 SL 5 31-DEC-03 38.75 0 4.84 96 01-JAN-00 Data in Target. ASSET BOOK COMPANY YR_END_DA DEPR_YTD DEPR_LTD BEG_DEPR_LTD LIFE METH_SW_D -- -- -- - -- -- -- - 101536 SL 5 31-DEC-02 4.84 0 0 96 01-JAN-00 ASSET BOOK COMPANY YR_END_DA DEPR_YTD DEPR_LTD BEG_DEPR_LTD LIFE METH_SW_D -- -- -- - -- -- -- - 101537 SL 5 31-DEC-02 4.84 0 0 96 01-JAN-00 -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database Instance
I would be very careful about doing this if you have purchased application packages. Sooner or later, you will want to upgrade one of the packages, and it will require a different release of Oracle - and you will be stuck. Tom Mercadante Oracle Certified Professional -Original Message-From: anu [mailto:[EMAIL PROTECTED]Sent: Wednesday, December 24, 2003 11:24 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Database Instance One other disadvantage of putting all instances together is if you need to say bounce the database (for parameter change or other maintenance etc) then all other applications will get affected. Whereas with separate instances other applications will not get affected. To some extent one application failing will not affect other applications. Except if one application does not close its connections then it could lead to maximum connections (sessions) being reached and affecting other applications. If the nature of the applications is different : OLTP, warehousing then you cannot really tune the parameters. On the positive side I think putting instances together will lead to some memory savings. I would suggest : Do not worry about who wants to put the instances together just list the advantages, disadvantages and make the decision. [EMAIL PROTECTED] wrote: It is not necessarily true that an error in one application will affect allapplications. If there is a problem with oracle instance or the database,then all applications might be affected.Multiple schemas which have the same table names can be a problem. If yourapplications uses public synonyms, then you might have a big problem.If everything is working fine now, it seems pointless to move things around.But this is philosophy. I do believe that isolating applications from eachother as much as possible is usually a good thing."Good fences make good neighbors." (usually)But, if your manager insists on it, you have no choice. Just do your bestto keep the old stuff around in case it becomes apparent that the new waywill not work and you must go back to the old way.> -Original Message-> Lately, m! y manager want me to remove all the databases> and remain a single instance. I was wondering if i> move everything into single database then if one of> the application fail due to oracle error , then all> other four application will fail also rite ? > > Each of our web application needs to have 2 schema and> both schema have to be transparent to each other.> While other application schema will be invisible to> each other. Since i have 5 web app then i will need 10> schema.One major problem is all the 10 schema will> contain same table name. It will be a mess putting so> much app in a single db . > > Pls correct me if i am wrong and do let me know what> are the pro and cons or maybe you can educate me with> some of the best practice to setup a proper production> server environment.> > Thank You> > Regards,> Jkean > > > -- 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.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!?Free Pop-Up Blocker - Get it now
RE: seq
because the second insert is returning the result of the first insert. try running each one twice in a row and see what happens. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 23, 2003 3:49 PM To: Multiple recipients of list ORACLE-L Hello list , why does : insert into test values (mysequence.nextval, mysequence.currval ) ; have the same effect as insert into test values (mysequence.currval, mysequence.nextval ) ; where 1. mysequence is create sequence mysequence increment by 1 start with 1 maxvalue 1000 nocycle nocache ; and 2. test is Name Null?Type ID NUMBER(7) ID2NUMBER -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ora-942 message in alert log
Sorry. I read your email twice and still have not seen a question. Are you wondering if this is normal? :) Tom Mercadante Oracle Certified Professional -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 23, 2003 10:39 AMTo: Multiple recipients of list ORACLE-LSubject: ora-942 message in alert log Yesterday, we received the below message in our alert log. Normally, we do not see such messages in an alert log. It might be a spurious message since in the associated trace file it first says: FATAL ERROR IN TWO-TASK SERVER: error = 12571 which is a networking error. Maybe just had a connection problem and gave a bad error. Mon Dec 22 12:05:43 2003Errors in file f:\oracle\trace\ORA04312.TRC:ORA-00942: table or view does not exist In the trace file: *** SESSION ID:(13.65246) 2003-12-22 12:05:43.380FATAL ERROR IN TWO-TASK SERVER: error = 12571*** 2003-12-22 12:05:43.427ksedmp: internal or fatal errorORA-00942: table or view does not existNo current SQL statement being executed. @m:\sql\grants\triggers\pa_projects_all_rta Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113
RE: TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE
ANd one more thing about Ping vs Tnsping when going thru a firewall. Some firewalls are setup to not allow a Ping to pass thru, but sql connections are allowed. So a Ping will return not found, while a tnsping will return ok. I have that situation here all over the freakin place. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 23, 2003 5:54 AM To: Multiple recipients of list ORACLE-L I came across just this last week with one of our monitoring tools. We set up an execution of a script that was using PING to check whether the status of a list of remote POS devices to make sure they were available. The collection worked fine - until we shutdown the POS device, and physically switched it off - and the PING still replied! The POS devices have UPS built in to them.. The client also thought it may have been the way that DNS/DHCP was set up via the router. It stumped me for a time.. ;) We use NETSVC now to check for the status of a service. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -Original Message- Jared Still Sent: 23 December 2003 04:34 To: Multiple recipients of list ORACLE-L Paul mentioned a few reasons for this. Another is that a ping does not get past the NIC. The ping is answered by software running on the card. You may have noticed at times that a ping is not a reliable method for determining if a server is still functioning. The OS can crash, but the NIC still responds to a ping. Tnsping on the other hand must get a response from Oracle Net service or daemon running on the server, a much longer path as Paul pointed out. HTH Jared On Mon, 2003-12-22 at 17:29, Murali Vallath wrote: > I have recently noticed in this one situation that there is a great difference between a tnsping vs a regular ping to the same server. > > for example this tnsping took about 270 ms which is strange and its consistent > > Used TNSNAMES adapter to resolve the alias > Attempting to contact (DESCRIPTION = (ADD > RESS_LIST = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(HOST > = myhost1.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2.com)( > PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xyzdb) (FAILOVER_MODE = (TYPE > = SELECT) (METHOD = BASIC) (RETRIES = 20) (DELAY = 15 > OK (270 msec) > > and a ping to the same host > > Ping statistics for x.x.x.x: > Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), > Approximate round trip times in milli-seconds: > Minimum = 61ms, Maximum = 70ms, Average = 67ms > > Why could there be such a difference? > > > > > > > > - > Do you Yahoo!? > Yahoo! Photos - Get your photo on the big screen in Times Square -- 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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
RE: RMAN - the time has come
Title: RMAN - the time has come Raj, It really is a quick learn. The best way is to get some scripts (great examples in the rdbms/demo directory) and try them on a test database. Once you get something running, the rest is simple. If you need some samples, let me know. It's really basic stuff. Tom Mercadante Oracle Certified Professional -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Monday, December 22, 2003 10:35 AMTo: Multiple recipients of list ORACLE-LSubject: RMAN - the time has come Okay, its time to bite the bullet ... time to learn RMAN. Outside of TFM (which I just started reading), are there any good books/articles on RMAN? I know there is one by RFreeman, and it is for 9i (Robert, will there be a 10g version?) Yeah, I am also google'ing, taeoma'ing and in general stfw'ing whenever time permits. 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.**4
RE: sys as sysdba with password through ODBC
Craig, the correct syntax is sys/[EMAIL PROTECTED] as sysdba. So you might try: >$username = "SYS" >$password = "syspassword" >$dsn="dsn AS SYSDBA" PS - can you tell I'm guessing? :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, December 19, 2003 10:50 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] I have done that it works through SQLPLUS but through my page i can connect to the dsn but now i get Warning: odbc_exec(): SQL error: [Oracle][ODBC][Ora]ORA-03114: not connected to ORACLE , SQL state S1000 in SQLExecDirect >From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> >To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> >CC: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> >Subject: RE: sys as sysdba with password through ODBC >Date: Fri, 19 Dec 2003 10:36:39 -0500 > >Craig, > >did you try: > >$username = "SYS" >$password = "syspassword AS SYSDBA" >$dsn="dsn" > > >Tom Mercadante >Oracle Certified Professional > > >-Original Message- >From: Craig Richards [mailto:[EMAIL PROTECTED] >Sent: Friday, December 19, 2003 10:29 AM >To: Multiple recipients of list ORACLE-L >Subject: sys as sysdba with password through ODBC > > >Can this be done a want to connect through ODBC, for a php web page > >Can this be done if so how do I declare the username, password and db >string > >Currently i do > >$username = "name" >$password = "password" >$dsn="dsn" > >Any help appreciated > >Cheers > >_ >Express yourself with cool emoticons - download MSN Messenger today! >http://www.msn.co.uk/messenger > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Craig Richards > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Express yourself with cool emoticons - download MSN Messenger today! http://www.msn.co.uk/messenger -- 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).
RE: sys as sysdba with password through ODBC
Craig, did you try: $username = "SYS" $password = "syspassword AS SYSDBA" $dsn="dsn" Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, December 19, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Can this be done a want to connect through ODBC, for a php web page Can this be done if so how do I declare the username, password and db string Currently i do $username = "name" $password = "password" $dsn="dsn" Any help appreciated Cheers _ Express yourself with cool emoticons - download MSN Messenger today! http://www.msn.co.uk/messenger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Richards INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: OEM permissions
is mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: RMAN Retention Policy
Ian,
I think retention policy is new in 9i.
I purge my repository of backups that are older than 90 days (because our
tape systems rotates and reuses tapes after that time) using the "change
backuppiece 330783 delete; " command. I run a sql script againts the rman
repository looking for pieces that satisfy this requirement. The sql looks
like this:
select 'change backuppiece bp.bp_key delete;'
from rc_backup_piece bp,rc_database db
where db.name = upper('ORACLE_SID')
and bp.db_id = db.dbid
and bp.start_time < sysdate-90
/
This is in an 8i database.
Hope this is what you were looking for.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, December 17, 2003 5:04 PM
To: Multiple recipients of list ORACLE-L
How is this set on 8.1.7 and 8.1.6 databases
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "identifier": expecting one of: "compatible"
RMAN-01008: the bad identifier was: RETENTION
RMAN-01007: at line 1 column 11 file: standard input
I looked at commands such as
crosscheck backup of database completed before 'SYSDATE-7';
delete expired backup of database completed before 'SYSDATE-7';
But crosscheck only expires backups which are in the catalog, but not
available on the backup media.
Do I have to use the change command and designate each backup piece?
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: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: How to refresh
Ron, here's an ugly way to do this: Empty your TEST database of data. Drop constraints & Indexes. Perform and export of the TEST database with ROWS=N - this will export all Functions, packages and procedures. Import data from production. Import the stuff from step 2 above. This should overlay all functions, packages and procedures from production. Like I said, this is uuugggly. I like Dennis's suggestions - force them to keep scripts of their functions and have them reload them once your refresh the database. One other option is to empty your database, drop indexes and constraints, and bring data one table at a time using insert into table as select * from [EMAIL PROTECTED] Then apply constraints and indexes. I would attempt this approach before the ugly approach above. good Luck Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 16, 2003 10:14 AM To: Multiple recipients of list ORACLE-L Like I said. There are new packages, procedures, functions, etc... in TEST that we do not want to lose. Ron -Original Message- Sent: Tuesday, December 16, 2003 9:04 AM To: Multiple recipients of list ORACLE-L Ron - You should consider refreshing the instance by cloning your production database. This way you get an exact replica in all respects, and you can test your backup as a bonus. If you are interested in this method, tell us how you backup your production database. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 16, 2003 8: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: 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: 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: connection pooling from an application server to oracle
; control standpoint, however, the important thing to remember is that each > connection will be assigned to a particular user for the length of that > user's transaction. > Justin Cave > Distributed Database Consulting > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Justin Cave > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: connection pooling from an application server to oracle
Ryan, My understanding is that the transactions are packaged differently. For example, a trans that would come thru the app server will be packaged - like begin trans, exec trans and end trans - all as one call to the database. Almost like a two-phased commit (indeed, the errors that get reported to me are all distributed transaction errors). Because everything is packaged together, the app server waits for an open slot in the connection, sends the total transaction, and gets out. Almost like scheduling disk writes. And with DCOM or COM+, MTS is required (at least on my project). How this all compares to Oracle Connection pooling, I don't know. Didn't get the chance to propose a test. Don't know about you, but these decisions were made without the DBA's being involved, so I just went with them. And from my point of view, what does it matter? As long as it works, I don't want to start a fight I can't win. Got to pick your battles. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, December 15, 2003 9:24 AM To: Multiple recipients of list ORACLE-L so if user A has 10 DML statements to do in one transaction. The application server will be smart enough to to only allow user 'A' to use that connection until a 'commit' is issued? how does application level connection pooling compare to MTS? > > From: Rachel Carmichael <[EMAIL PROTECTED]> > Date: 2003/12/15 Mon AM 09:04:26 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: RE: connection pooling from an application server to oracle > > nope. the application server watches the connections and > transactions > > the main problem is it's very hard to do a 10046 trace on a session > with connection pooling going on, as a user session may actually be > several distinct database sessions. > > > > --- [EMAIL PROTECTED] wrote: > > doesnt this force you to commit after every single DML statement? > > > > > > From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> > > > Date: 2003/12/15 Mon AM 08:36:09 EST > > > To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > > CC: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > > Subject: RE: connection pooling from an application server to > > oracle > > > > > > Ryan, > > > > > > This is becoming for normal. There are a lot of software pieces > > that do > > > connection pooling - basically, everybody is plaing in everbody > > else's > > > space. > > > > > > I have a couple of projects where the app-server does the > > connection > > > pooling. One using Dcom and the other IBM WebSphere. > > > > > > From your point of view, it's just one less thing to worry about. > > The > > > number of db connections will be relatively small. The app server > > keeps > > > track of transactions. As long as they say it works, it's not your > > problem. > > > > > > Tom Mercadante > > > Oracle Certified Professional > > > > > > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > > Sent: Monday, December 15, 2003 7:59 AM > > > To: Multiple recipients of list ORACLE-L > > > Subject: connection pooling from an application server to oracle > > > > > > > > > The software engineers here are using an application server with > > connection > > > pooling to connect to our oracle instances. > > > They are doing it with a dedicated connection to Oracle. No MTS. > > > > > > they compartmentalize stuff here, so Im having trouble figuring out > > exactly > > > how this affects the database and how to monitor performance. All I > > know is > > > that I see a handful of constantly open dedicated connections. I > > have been > > > told that this is actually alot of users connecting to the > > database. > > > > > > This concerns me. how do you handle transaction control in this > > type of > > > environment? in this type of environment do you have to commit > > after every > > > DML statement? since multiple users will access the database with > > the same > > > conneciton? > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: <[EMAIL PROTECTED] > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Net
RE: connection pooling from an application server to oracle
Ryan, This is becoming for normal. There are a lot of software pieces that do connection pooling - basically, everybody is plaing in everbody else's space. I have a couple of projects where the app-server does the connection pooling. One using Dcom and the other IBM WebSphere. >From your point of view, it's just one less thing to worry about. The number of db connections will be relatively small. The app server keeps track of transactions. As long as they say it works, it's not your problem. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, December 15, 2003 7:59 AM To: Multiple recipients of list ORACLE-L The software engineers here are using an application server with connection pooling to connect to our oracle instances. They are doing it with a dedicated connection to Oracle. No MTS. they compartmentalize stuff here, so Im having trouble figuring out exactly how this affects the database and how to monitor performance. All I know is that I see a handful of constantly open dedicated connections. I have been told that this is actually alot of users connecting to the database. This concerns me. how do you handle transaction control in this type of environment? in this type of environment do you have to commit after every DML statement? since multiple users will access the database with the same conneciton? -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Fedora (pink hat)
well, somebody has to be first. why not you? come-on - go out on a limb for once! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, December 12, 2003 3:20 PM To: Multiple recipients of list ORACLE-L Has anyone actually installed Oracle on Fedora? I don't want to be the first one. 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Who are certified Oracle Masters?
All this nonsense about who got what and did they deserve it. Only one questions comes to mind. Are we all in High School or something? Did I miss the throwback in time? sheesh. Tom Mercadante Oracle Certified Professional -- 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).
RE: 3rd Party Oracle Licenses
Jay, I would call your local Oracle Sales office and run it by them. As my old boss used to say - "ALWAYS cut the cards". Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, December 11, 2003 9:14 AM To: Multiple recipients of list ORACLE-L We are purchasing a software package from a vendor. The vendor states that the package includes sufficient Oracle licenses. Since I'm supposed to keep on top of our licensing costs, I'm trying to make sure that there are no surprises down the road - such as additional Oracle support fees or Oracle claiming that we don't have this new box licensed, etc. How can the vendor prove that they are providing a license? When I asked them for some type of proof, they forward the OLSA to me, which is basically generic - it doesn't tell me if the license is SE, EE, SE One, perpertual, term, CPU, Named User, etc. Any thoughts or do I just take their word for it? Thanks, Jay **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 D&E 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Little competition
taining: UNSUB ORACLE-L (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).
RE: 10g stuff on otn
"so are they really new versions or just minor upgrades to the 9i version?" let's see. Otn is a marketing tool. From a marketing point of view, what's better - changing the name to make it look like it's brand-spanking new, or making what is obvious obvious - that they are simply recycling the current version into a new version. Tom Mercadante Oracle Certified Professional -Original Message-From: Ryan [mailto:[EMAIL PROTECTED]Sent: Wednesday, December 10, 2003 10:54 PMTo: Multiple recipients of list ORACLE-LSubject: 10g stuff on otn they list the new application server and the new developer suite as '10g', but then say 9.0.4 next to it? so are they really new versions or just minor upgrades to the 9i version?
RE: char is going away?
Gene, I remember Oracle saying that char was going away - about 6 years ago. That's when they created varchar and varchar2. Is this a new rumor? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, December 10, 2003 11:09 AM To: Multiple recipients of list ORACLE-L Hi all: Someone told me that Oracle is planning to retire char variable and therefore they need to be replaced by varchar2. Has anyone heard anything about it? thanks Gene __ 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: Gene Gurevich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Creating the Java System
Mark & Mike, Thanks for the replies. They both were right on the mark. The full removal did the trick. thanks again Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 09, 2003 4:44 PM To: Multiple recipients of list ORACLE-L Thomas, This error seems to vaguely ring a bell. You may try: http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_ database_id=NOT&p_id=209870.1 Hope that's helpful. -Mark -Original Message- Sent: Tuesday, December 09, 2003 4:19 PM To: Multiple recipients of list ORACLE-L gee thanks. where do you live? I'll meet you at your car. I'll be the one with the baseball bat. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 09, 2003 4:09 PM To: Multiple recipients of list ORACLE-L I would advise full export and then executing "create database controlfile reuse datafile.logfile..." command before "initjvm" and everything will work. On 12/09/2003 03:44:26 PM, "Mercadante, Thomas F" wrote: > All, > > I am attempting to install the Java system in a 9.2 database on Sun Solaris. > This is a db that I migrated up to 9.2 from 8.1.7. > > I am getting the following: > > SQL> create or replace java system > 2 / > create or replace java system > * > ERROR at line 1: > ORA-00604: error occurred at recursive SQL level 2 > ORA-01422: exact fetch returns more than requested number of rows > ORA-06512: at line 11 > > I get this when I run the initjvm.sql text. Java_Shared_Pool is at 32 M. > > Thanks for any ideas. > > Tom Mercadante > Oracle Certified Professional > > -- > 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). > 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: 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: Bobak, Mark INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Creating the Java System
gee thanks. where do you live? I'll meet you at your car. I'll be the one with the baseball bat. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 09, 2003 4:09 PM To: Multiple recipients of list ORACLE-L I would advise full export and then executing "create database controlfile reuse datafile.logfile..." command before "initjvm" and everything will work. On 12/09/2003 03:44:26 PM, "Mercadante, Thomas F" wrote: > All, > > I am attempting to install the Java system in a 9.2 database on Sun Solaris. > This is a db that I migrated up to 9.2 from 8.1.7. > > I am getting the following: > > SQL> create or replace java system > 2 / > create or replace java system > * > ERROR at line 1: > ORA-00604: error occurred at recursive SQL level 2 > ORA-01422: exact fetch returns more than requested number of rows > ORA-06512: at line 11 > > I get this when I run the initjvm.sql text. Java_Shared_Pool is at 32 M. > > Thanks for any ideas. > > Tom Mercadante > Oracle Certified Professional > > -- > 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). > 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: 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).
Creating the Java System
All, I am attempting to install the Java system in a 9.2 database on Sun Solaris. This is a db that I migrated up to 9.2 from 8.1.7. I am getting the following: SQL> create or replace java system 2 / create or replace java system * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 2 ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 11 I get this when I run the initjvm.sql text. Java_Shared_Pool is at 32 M. Thanks for any ideas. Tom Mercadante Oracle Certified Professional -- 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).
RE: dc_used_extents ,dc_free_extents and dc_histogram_defs
somebody has free time on his hands. Jared, what's your bosses email addy? Tom Mercadante Oracle Certified Professional -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Monday, December 08, 2003 3:59 PMTo: Multiple recipients of list ORACLE-LSubject: Re: dc_used_extents ,dc_free_extents and dc_histogram_defsThough there has been an average increase in the total number of init parameter of 83% from versions 7.3.4 - 9.2.0.4, the percentage of tunable/undocumented parameters has gone from 62%/38% in 7.3.4, to 31%/69% in 9.2.0.4. version undoc tunable total %undoc %tunable - 7.3.4 97 158 255 38 62 8.1.7.4 300 204 504 60 40 9.2.0.4 587 258 845 69 31 To achieve the stated goal of 100 tunable parameters in 10g, with an expected growth rate of 30% ( a guesstimate ) or so in the total number of parameters, 10g should look somthing like this: version undoc tunable total %undoc %tunable - 10.0.0 999 100 1099 91 9 ;) Jared Mladen Gogala <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 12/08/2003 11:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: dc_used_extents ,dc_free_extents and dc_histogram_defsLarry Ellison has publicly stated that his goal is to produce a database with less then 100 tunable parameters. Allegedly, he came rather close with 10g.As far as 10g is concerned, I'm rather disappointed with the marketing hype being created with oracle not making an early version available. I don't planon migrating to 10g until I learn it well and if some oracle sales guytries to exert pressure on me to migrate, he will get a very stable signused by English archers after the battle at Agincourt to signify that they still have all the fingers needed to operate a longbow. I've had my fill ofwhite papers and articles and now I want to see the software.
RE: Exclusive Password file, multiple databases
Title: Exclusive Password file, multiple databases April, I just went thru this. Look in the sqlnet.ora file. If :SQLNET.AUTHENTICATION_SERVICES= (NTS)is set, comment it out. It was the cause of the problems on my machine. Good Luck! Tom Mercadante Oracle Certified Professional -Original Message-From: April Wells [mailto:[EMAIL PROTECTED]Sent: Monday, December 08, 2003 1:35 PMTo: Multiple recipients of list ORACLE-LSubject: Exclusive Password file, multiple databases Okay, I have been trying Google and Tahiti for 2 days, and I can't for the life of me figure out how to clue in my database. 9.2.0.2 on AIX 5l I am running multiple databases and am trying to get all of the instances running with remote_login_passwordfile=exclusive. Most of the databases are running fine, but one insists on looking for the default file name, despite my having provided filename= parameter to orapwd. I can't find anything that is supposed to clue it in in this circumstance, and recreating the password file doesn't really so any good either. Every time I try to start the database using the pfile, it complains about wanting the orapwd file... All of the other databases are happy enough with what I did. What am I doing wrong with this one... where do I start looking? 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 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.
RE: Exclusive Password file, multiple databases
Title: Exclusive Password file, multiple databases April, I just went thru this. Look in the sqlnet.ora file. If : Tom Mercadante Oracle Certified Professional -Original Message-From: April Wells [mailto:[EMAIL PROTECTED]Sent: Monday, December 08, 2003 1:35 PMTo: Multiple recipients of list ORACLE-LSubject: Exclusive Password file, multiple databases Okay, I have been trying Google and Tahiti for 2 days, and I can't for the life of me figure out how to clue in my database. 9.2.0.2 on AIX 5l I am running multiple databases and am trying to get all of the instances running with remote_login_passwordfile=exclusive. Most of the databases are running fine, but one insists on looking for the default file name, despite my having provided filename= parameter to orapwd. I can't find anything that is supposed to clue it in in this circumstance, and recreating the password file doesn't really so any good either. Every time I try to start the database using the pfile, it complains about wanting the orapwd file... All of the other databases are happy enough with what I did. What am I doing wrong with this one... where do I start looking? 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 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.
RE: running both shared server and dedicated mode on the same ins
Ryan, I am doing this. In our Tnsnames.ora file, I have two entries - one for dedicated and one for shared. The application needs to use the correct tnsnames.ora connection for the type of connection that they want. Optionally, you can do the same thing using either ora names or ora ldap. Same concept - two entries, one for each type of connection. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 02, 2003 7:24 AM To: Multiple recipients of list ORACLE-L instance i think this is possible. any docs on how to set this up with suggested methods? -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Happy Thanksgiving
how 'bout a little DUI? -Original Message- Sent: Wednesday, November 26, 2003 10:50 AM To: Multiple recipients of list ORACLE-L And may you all be back here on Monday, in one piece, safe & sound. No DWI's now, you hear! Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -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! -- 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: 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: WebSphere and Oracle
Thanks Dick. I agree it's not a database problem, but they try and push it my way because "it's Oracle". I'm pushing back. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 25, 2003 9:50 AM To: Multiple recipients of list ORACLE-L The problem is most likely in the JDBC driver, not the database. I've run into similar problems with JDBC and ODBC drivers in the past. I've got to agree with IBM, upgrade the driver & all should be well. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, November 25, 2003 7:44 AM To: Multiple recipients of list ORACLE-L All, We are putting up a new Java application that is running under WebSphere. WebSphere uses two different schema's - the one where the application tables are all built, and another schema that contains one table (SESSIONS) that it uses to keep track of the application. This SESSIONS table has three RAW columns that essentionally hold BLOB information. The Application is connecting using the thin driver. We are getting a java error. According to the IBM web site: "When using the LOB columns (BLOBS and CLOBS) with Oracle 8i, you cannot use setCharacterStream or setBinaryStream to set BLOBS and CLOBS when the size of the LOB columns is greater than 4k. Oracle has lifted the restriction in the 9i R2 OCI driver (thick driver). If you have one of these error messages or you know that your data exceeds 4k, update to Oracle 9i R2 and use the OCI driver when connecting to Oracle server. If you cannot update to Oracle 9i R2, contact Oracle Support." I will be contacting Oracle support, but was wondering if anyone else has experienced this and did they find an answer to the problem. thanks Tom Mercadante Oracle Certified Professional -- 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: 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Move datafiles, but can't delete them
Luc, Its weird. I've seen it both ways. Sometimes, Windows releases the lock and allows you to delete the file, and sometimes it doesn't. Bouncing the database always allows you to do it. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 25, 2003 8:43 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Tom, I didn't know that I absolutely have to bounce the database. Tx Luc -Original Message- Sent: November 25, 2003 8:36 AM To: '[EMAIL PROTECTED]' Cc: Demanche, Luc Luc, The next time you bounce the database you will be able to delete the files. Windows keeps a lock on these files for some odd reason. In a way, it's better than Unix. You can't delete Windows Oracle files while the database is open, but in Unix you can. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 25, 2003 8:09 AM To: Multiple recipients of list ORACLE-L Hi gurus, Oracle 8.1.7.4 on Windows 2000 Yesterday I wanted to move 2 datafiles (for the same tablespace) to another disk. 1- I placed my tablespace offline 2- I copied my 2 datafiles 3- I altered my controlfiles to reflect the new path 4- I brought my tablespace back online 5- I backuped up my controlfile to trace to make sure it using the new path 6- When I wanted to delete the 2 old datafiles, Windows gave me an "sharing violation" error. My question is Who using it? My controlfiles are changed, when I query DBA_DATA_FILES, i'm using the new path. I don't want to bounce my production database Any ideas TIA Luc - Luc Demanche AstraZeneca R&D Montreal Oracle Database Administrator 514.832.3200 x2356 -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Move datafiles, but can't delete them
Luc, The next time you bounce the database you will be able to delete the files. Windows keeps a lock on these files for some odd reason. In a way, it's better than Unix. You can't delete Windows Oracle files while the database is open, but in Unix you can. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 25, 2003 8:09 AM To: Multiple recipients of list ORACLE-L Hi gurus, Oracle 8.1.7.4 on Windows 2000 Yesterday I wanted to move 2 datafiles (for the same tablespace) to another disk. 1- I placed my tablespace offline 2- I copied my 2 datafiles 3- I altered my controlfiles to reflect the new path 4- I brought my tablespace back online 5- I backuped up my controlfile to trace to make sure it using the new path 6- When I wanted to delete the 2 old datafiles, Windows gave me an "sharing violation" error. My question is Who using it? My controlfiles are changed, when I query DBA_DATA_FILES, i'm using the new path. I don't want to bounce my production database Any ideas TIA Luc - Luc Demanche AstraZeneca R&D Montreal Oracle Database Administrator 514.832.3200 x2356 -- 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: 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).
WebSphere and Oracle
All, We are putting up a new Java application that is running under WebSphere. WebSphere uses two different schema's - the one where the application tables are all built, and another schema that contains one table (SESSIONS) that it uses to keep track of the application. This SESSIONS table has three RAW columns that essentionally hold BLOB information. The Application is connecting using the thin driver. We are getting a java error. According to the IBM web site: "When using the LOB columns (BLOBS and CLOBS) with Oracle 8i, you cannot use setCharacterStream or setBinaryStream to set BLOBS and CLOBS when the size of the LOB columns is greater than 4k. Oracle has lifted the restriction in the 9i R2 OCI driver (thick driver). If you have one of these error messages or you know that your data exceeds 4k, update to Oracle 9i R2 and use the OCI driver when connecting to Oracle server. If you cannot update to Oracle 9i R2, contact Oracle Support." I will be contacting Oracle support, but was wondering if anyone else has experienced this and did they find an answer to the problem. thanks Tom Mercadante Oracle Certified Professional -- 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).
RE: how to recycle sbtio
AK, Yes. You can simply rename it or delete it. I rename it monthly. And then delete it the next month. It truely is a worthless file. Tom Mercadante Oracle Certified Professional -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Tuesday, November 18, 2003 12:04 PMTo: Multiple recipients of list ORACLE-LSubject: how to recycle sbtio How to recycle sbtio.log . Is it same way as alert.log ? -ak
RE: 'internal' role and 9i
Yong, You said "It's not always easy to have a futuristic view to avoid potential problems. The developer probably shouldn't have granted anything to internal back then. But it wasn't obvious at that time that doing so could cause a problem later." I totally disagree with you. Your quote implies that it is ok for developers to be using the SYS account - which is what Internal actually is. This is totally wrong. Sys/Internal is for creating/patching the database - for internal use only - never for application use. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 18, 2003 11:49 AM To: Multiple recipients of list ORACLE-L Barry, I suggest you open a Tar with Oracle, unless you're sure changing your application is easy. Oracle obviously missed this little detail by over-rejecting a previously legitimate role. If 9i's Release note doesn't say how to deal with this case, then Oracle support should open a bug. Tom, It's not always easy to have a futuristic view to avoid potential problems. The developer probably shouldn't have granted anything to internal back then. But it wasn't obvious at that time that doing so could cause a problem later. Yong Huang --- Barry Deevey <[EMAIL PROTECTED]> wrote: > This would explain why it worked when I tried it on oracle 8 then. > > The developers that originally created the application left quite a while > ago, so I don't think I'll be able to ask them why they did it this way. > But basically you're saying that it shouldn't have been done like this and > now it needs to be changed. > > Not the answer I was hoping for, but at least now I know!! > > Thanks for your help all. > > -Original Message- > Mercadante, Thomas F > Sent: 18 November 2003 14:54 > To: Multiple recipients of list ORACLE-L > > > Yong, > > It seems to me that you are missing the point here. The real point is that > you should not have granted "select on some table to internal" - ever. And > a new release caught you on this mistake. And now, you have to fix it. It > is not an Oracle problem, but a mis-use of an Oracle "internal" user. > > What you did implies that you are running sql statements while connected as > internal. Why in the world you would take this chance is beyond me - > strictly a no-no. > > Tom Mercadante > Oracle Certified Professional > > > -Original Message- > Sent: Tuesday, November 18, 2003 8:59 AM > To: Multiple recipients of list ORACLE-L > > > Tim, > > I checked v$reserved_words. At least in 9.2.0.1, INTERNAL is not in there. > Oracle should address this issue. > > When I trace the SQL GRANT SELECT ON SOMETABLE TO INTERNAL, it stops in > parsing. > > Nonetheless, it's confusing to say the least to create a role called > internal. > > Yong Huang > > --- Tim Gorman <[EMAIL PROTECTED]> wrote: > > Barry, > > > > Why make life difficult? It's just a role, not a data > > object referenced by applications (hopefully). Change it's > > name to something that is not a "reserved word" and move on. > > There is a list of "reserved words" in the SQL Language > > reference. > > > > Hope this helps... > > > > -Tim > > > > > > > Hello all, > > > > > > I'm attempting to import into 9.2.0.1.0 from 7.3.4 and I'm > > > getting loads of the same error: > > > > > > IMP-00017: following statement failed with ORACLE error > > > 9275: > > > "GRANT SELECT ON "DOWNLOAD_SEQ" TO "INTERNAL"" > > > IMP-3: ORACLE error 9275 encountered > > > ORA-09275: CONNECT INTERNAL is not a valid DBA connection > > > > > > I'm aware that connect internal does not exist in 9i, but > > > 'internal' is a role. > > > > > > So as a test I dropped the role, recreated it and then > > > manually tried to grant it something - The same error > > > occurred: > > > SQL> select * from dba_roles where role like 'INTER%'; > > > > > > ROLE PASSWORD > > > -- > > > INTERNAL NO > > > > > > SQL> > > > SQL> drop role internal; > > > > > > Role dropped. > > > > > > SQL> create role internal; > > > > > > Role created. > > > > > > SQL> GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL; > > > GRANT SELECT ON DOWNLOAD_SEQ TO IN
RE: 'internal' role and 9i
Yeah,
I realized that afterward - sorry Yong.
Tom Mercadante Oracle Certified Professional
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]Sent: Tuesday, November 18, 2003
11:24 AMTo: Multiple recipients of list ORACLE-LSubject:
RE: 'internal' role and 9iYong didn't do it, he merely posted a reply.
"Mercadante, Thomas F"
<[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED]
11/18/2003 06:54 AM
Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]> cc:
Subject: RE: 'internal' role and
9iYong,It seems to me that you are missing the point here.
The real point is thatyou should not have granted "select on some
table to internal" - ever. Anda new release caught you on this
mistake. And now, you have to fix it. Itis not an Oracle
problem, but a mis-use of an Oracle "internal" user.What you did
implies that you are running sql statements while connected asinternal.
Why in the world you would take this chance is beyond me -strictly a
no-no.Tom MercadanteOracle Certified
Professional-Original Message-Sent: Tuesday, November
18, 2003 8:59 AMTo: Multiple recipients of list
ORACLE-LTim,I checked v$reserved_words. At least in
9.2.0.1, INTERNAL is not in there.Oracle should address this
issue.When I trace the SQL GRANT SELECT ON SOMETABLE TO INTERNAL, it
stops inparsing.Nonetheless, it's confusing to say the least to
create a role calledinternal.Yong Huang--- Tim Gorman
<[EMAIL PROTECTED]> wrote:> Barry,> > Why make life
difficult? It's just a role, not a data> object referenced by
applications (hopefully). Change it's> name to something that is
not a "reserved word" and move on.> There is a list of "reserved
words" in the SQL Language> reference.> > Hope this
helps...> > -Tim> > > > Hello
all,> > > > I'm attempting to import into 9.2.0.1.0 from
7.3.4 and I'm> > getting loads of the same error:> >
> > IMP-00017: following statement failed with ORACLE error>
> 9275:> > "GRANT SELECT ON "DOWNLOAD_SEQ" TO
"INTERNAL""> > IMP-3: ORACLE error 9275 encountered> >
ORA-09275: CONNECT INTERNAL is not a valid DBA connection> >
> > I'm aware that connect internal does not exist in 9i,
but> > 'internal' is a role.> > > > So as a test
I dropped the role, recreated it and then> > manually tried to grant
it something - The same error> > occurred: > > SQL>
select * from dba_roles where role like 'INTER%';> > > >
ROLE
PASSWORD> > --
> > INTERNAL
NO> > > >
SQL>> > SQL> drop role internal;> > > >
Role dropped.> > > > SQL> create role internal;>
> > > Role created.> > > > SQL> GRANT
SELECT ON DOWNLOAD_SEQ TO INTERNAL;> > GRANT SELECT ON DOWNLOAD_SEQ
TO INTERNAL> >
*> >
ERROR at line 1:> > ORA-09275: CONNECT INTERNAL is not a valid DBA
connection> > > > SQL>> > > > This
doesn't make any sense to me. Can anybody help to> > shed any
light on this??> > > > TIA for any response, they're much
appreciated.> > > > Cheers,> >
Barry__Do you Yahoo!?Protect
your identity with Yahoo! Mail
AddressGuardhttp://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.comSan Diego, California
-- Mailing list and web hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing
list you want to be removed from). You mayalso send the HELP command
for other information (like subscribing).-- 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.comSan Diego, California
-- Mailing list and web hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing
list you want to be removed from). You mayalso send the HELP command
for other information (like
subscribing).
RE: 'internal' role and 9i
Barry, What you need to do is stop using the INTERNAL role. Create your own role. Grant access to the tables to this role. And then grant this role to your application user. Everything should be fine. As I said, you made a mistake back when you started using the INTERNAL role. Now that this has gone away, you have to pay the piper. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 18, 2003 10:09 AM To: Multiple recipients of list ORACLE-L As of yet I'm unsure how the application would be affected if I rename the role - I need to do some investigation. I tried this in Oracle 8 and it worked fine - It just seems to be oracle 9 that doesn't like it. I've also checked v$reserved_words and INTERNAL is not listed, INTERNAL_USE and INTERNAL_CONVERT are. As a test I created roles for INTERNAL_USE and INTERNAL_CONVERT, hoping that it would not allow me to create them, but it did, so I then ran the grant again and it also allowed it. Now I'm really confused!! -Original Message- Tim Gorman Sent: 18 November 2003 13:34 To: Multiple recipients of list ORACLE-L Barry, Why make life difficult? It's just a role, not a data object referenced by applications (hopefully). Change it's name to something that is not a "reserved word" and move on. There is a list of "reserved words" in the SQL Language reference. Hope this helps... -Tim > Hello all, > > I'm attempting to import into 9.2.0.1.0 from 7.3.4 and I'm > getting loads of the same error: > > IMP-00017: following statement failed with ORACLE error > 9275: > "GRANT SELECT ON "DOWNLOAD_SEQ" TO "INTERNAL"" > IMP-3: ORACLE error 9275 encountered > ORA-09275: CONNECT INTERNAL is not a valid DBA connection > > I'm aware that connect internal does not exist in 9i, but > 'internal' is a role. > > So as a test I dropped the role, recreated it and then > manually tried to grant it something - The same error > occurred: > SQL> select * from dba_roles where role like 'INTER%'; > > ROLE PASSWORD > -- > INTERNAL NO > > SQL> > SQL> drop role internal; > > Role dropped. > > SQL> create role internal; > > Role created. > > SQL> GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL; > GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL > * > ERROR at line 1: > ORA-09275: CONNECT INTERNAL is not a valid DBA connection > > SQL> > > This doesn't make any sense to me. Can anybody help to > shed any light on this?? > > TIA for any response, they're much appreciated. > > Cheers, > Barry. > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net -- > Author: Barry Deevey > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com San Diego, California-- > Mailing list and web hosting services > -- > --- To REMOVE yourself from this mailing list, > send an E-Mail message to: [EMAIL PROTECTED] (note > EXACT spelling of 'ListGuru') and in the message BODY, > include a line containing: UNSUB ORACLE-L (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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barry Deevey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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' role and 9i
Yong, It seems to me that you are missing the point here. The real point is that you should not have granted "select on some table to internal" - ever. And a new release caught you on this mistake. And now, you have to fix it. It is not an Oracle problem, but a mis-use of an Oracle "internal" user. What you did implies that you are running sql statements while connected as internal. Why in the world you would take this chance is beyond me - strictly a no-no. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 18, 2003 8:59 AM To: Multiple recipients of list ORACLE-L Tim, I checked v$reserved_words. At least in 9.2.0.1, INTERNAL is not in there. Oracle should address this issue. When I trace the SQL GRANT SELECT ON SOMETABLE TO INTERNAL, it stops in parsing. Nonetheless, it's confusing to say the least to create a role called internal. Yong Huang --- Tim Gorman <[EMAIL PROTECTED]> wrote: > Barry, > > Why make life difficult? It's just a role, not a data > object referenced by applications (hopefully). Change it's > name to something that is not a "reserved word" and move on. > There is a list of "reserved words" in the SQL Language > reference. > > Hope this helps... > > -Tim > > > > Hello all, > > > > I'm attempting to import into 9.2.0.1.0 from 7.3.4 and I'm > > getting loads of the same error: > > > > IMP-00017: following statement failed with ORACLE error > > 9275: > > "GRANT SELECT ON "DOWNLOAD_SEQ" TO "INTERNAL"" > > IMP-3: ORACLE error 9275 encountered > > ORA-09275: CONNECT INTERNAL is not a valid DBA connection > > > > I'm aware that connect internal does not exist in 9i, but > > 'internal' is a role. > > > > So as a test I dropped the role, recreated it and then > > manually tried to grant it something - The same error > > occurred: > > SQL> select * from dba_roles where role like 'INTER%'; > > > > ROLE PASSWORD > > -- > > INTERNAL NO > > > > SQL> > > SQL> drop role internal; > > > > Role dropped. > > > > SQL> create role internal; > > > > Role created. > > > > SQL> GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL; > > GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL > > * > > ERROR at line 1: > > ORA-09275: CONNECT INTERNAL is not a valid DBA connection > > > > SQL> > > > > This doesn't make any sense to me. Can anybody help to > > shed any light on this?? > > > > TIA for any response, they're much appreciated. > > > > Cheers, > > Barry __ 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Configuring multi-threaded server
Peter, Do you mean with starting the database? Not at all. The db will register when the listener comes up. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, November 17, 2003 4:04 PM To: Multiple recipients of list ORACLE-L If you remove the database declarations from your listener.ora file does it cause a problem if the listener is not running when the database comes up? Thanks, Peter Schauss -Original Message- Sent: Monday, November 17, 2003 2:55 PM To: Multiple recipients of list ORACLE-L Peter, If you declare in the init.ora file where the listener is running, then the database automatically connects to the listener. This is the second tawk entry in your lsnrctl status listing. If you declare the database in the listener.ora file, then the listener goes looking for the database, thus the other entry in your output. I now remove the database entry in the listener.ora file, and keep all my connection info in the init.ora file. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, November 17, 2003 2:14 PM To: Multiple recipients of list ORACLE-L When I do "lsnrctl services", I get two entries for each instance. For example: tawk has 1 service handler(s) DEDICATED SERVER established:33 refused:0 LOCAL SERVER tawk has 2 service handler(s) DEDICATED SERVER established:0 refused:0 LOCAL SERVER DISPATCHER established:0 refused:0 current:0 max:1022 state:ready D000 (ADDRESS=(PROTOCOL=tcp)(HOST=bvdesi02.esid.northgrum.com)(PORT=33420)) It looks like the listener is ignoring the second entry. Any idea what is going on here? Thanks, Peter Schauss -Original Message- Sent: Monday, November 17, 2003 11:55 AM To: Multiple recipients of list ORACLE-L You have to make sure the dispatchers are registered with the listener. Use "lsnrctl services" to make sure. The error you gave shows that they are not registered. If your listener is not on 1521, then the dispatchers won't know how to find it to register. You'll have to add the listener= atttribute to the mts_dispatchers parameter. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Mon, 17 Nov 2003, Schauss, Peter wrote: > Oracle version 8.1.7 > AIX 4.3.3 > > I am trying to configure mult-threaded server on one of my development > databases. In my init.ora file, I set > > (mts_dispatchers="(address=(protocol=tcp))" > mts_servers=10 > > When I bring up the database, I can see the dispatcher and > servers. When I connect to the database with sqlnet, however, > it still gives me a dedicated server. > > I tried forcing the connection by putting (server=shared) into > the tnsnames.ora file on my client. Then I get: > > ORA-12520: TNS:listener could not find available handler for requested type > of > server -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). -- Ple
RE: Configuring multi-threaded server
Peter, If you declare in the init.ora file where the listener is running, then the database automatically connects to the listener. This is the second tawk entry in your lsnrctl status listing. If you declare the database in the listener.ora file, then the listener goes looking for the database, thus the other entry in your output. I now remove the database entry in the listener.ora file, and keep all my connection info in the init.ora file. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, November 17, 2003 2:14 PM To: Multiple recipients of list ORACLE-L When I do "lsnrctl services", I get two entries for each instance. For example: tawk has 1 service handler(s) DEDICATED SERVER established:33 refused:0 LOCAL SERVER tawk has 2 service handler(s) DEDICATED SERVER established:0 refused:0 LOCAL SERVER DISPATCHER established:0 refused:0 current:0 max:1022 state:ready D000 (ADDRESS=(PROTOCOL=tcp)(HOST=bvdesi02.esid.northgrum.com)(PORT=33420)) It looks like the listener is ignoring the second entry. Any idea what is going on here? Thanks, Peter Schauss -Original Message- Sent: Monday, November 17, 2003 11:55 AM To: Multiple recipients of list ORACLE-L You have to make sure the dispatchers are registered with the listener. Use "lsnrctl services" to make sure. The error you gave shows that they are not registered. If your listener is not on 1521, then the dispatchers won't know how to find it to register. You'll have to add the listener= atttribute to the mts_dispatchers parameter. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Mon, 17 Nov 2003, Schauss, Peter wrote: > Oracle version 8.1.7 > AIX 4.3.3 > > I am trying to configure mult-threaded server on one of my development > databases. In my init.ora file, I set > > (mts_dispatchers="(address=(protocol=tcp))" > mts_servers=10 > > When I bring up the database, I can see the dispatcher and > servers. When I connect to the database with sqlnet, however, > it still gives me a dedicated server. > > I tried forcing the connection by putting (server=shared) into > the tnsnames.ora file on my client. Then I get: > > ORA-12520: TNS:listener could not find available handler for requested type > of > server -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Re[2]: var source_data varchar2(12)
> the message BODY, include a line containing: UNSUB ORACLE-L > (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: 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Create Rule
Hamid, Create a view on top of the table and apply the where clause in the view. Only give the view to the application folks - not the base table itself. create or replace view my_view as select * from some_table where sysdate between effective date & end date; How about that?? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, November 14, 2003 3:14 PM To: Multiple recipients of list ORACLE-L NO I mean these tables always filtered even if some body run a query like this: select * from table1 -- >> return the all the records where the sysdate between effective date & end date -Original Message- Sent: Friday, November 14, 2003 9:59 AM To: Multiple recipients of list ORACLE-L you answered your own question ... select * from my_table where trunc(sysdate) between trunc(eff_date) and trunc(end_date) / 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: Friday, November 14, 2003 12:49 PM To: Multiple recipients of list ORACLE-L List, I have 50 lookup tables and all of them have effective date & end date I want to create a rule or some thing like this which any select statement select the data from these lookup where the sysdate between effective date & end date. Example: select * from table1 ( always select those records where sysdate between effective date & end date). Any Idea? Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- 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: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for 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).
RE: Clean temporary tablespace
I agree with you Bill. My temp space is always 80% full. I ignore it until I get a report of a sql error. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, November 14, 2003 1:09 PM To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] scribbled on the wall in glitter crayon: > I got the impression that the poster was thinking segments allocated > in temp tablespace must be released, cleaned out, blown away, etc. > before something else can come along and use the space. i was always under the impression that you didn't need to do anything to the temp tablespace. that the segments were just reused as needed. am i behind the times here? -- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] Shift to the left! Shift to the right! Pop up, push down, byte, byte, byte! -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Migration utility to convert 32 bit DB to 64 bit?
Paula, I moved a database (Sun box) from 32 to 64 bit Oracle 9.2.x. I did nothing except point the database to the 64 bit Oracle software. Of course, it was the same release levels (32 & 64 were both 9.2.x). Everything is fine. At the very least, I would think that you need to run catalog & catproc. Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 3:29 PM To: Multiple recipients of list ORACLE-L So - this is a stupid question: We cannot use Oracle's migration utility to go from 32-bit 8.1.7 to 64-bit 9i? - Hmmm, create database , export, import. -Original Message- Sent: Thursday, November 13, 2003 2:49 PM To: Multiple recipients of list ORACLE-L It's actually an sql script that's located in the $ORACLEHOME/rdbms/admin directory called utlirp. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- [mailto:Murali_Pavuloori/[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 10:39 AM To: Multiple recipients of list ORACLE-L hello, We currently have oracle (32 bit) 9.2.0.3 db running on Win 2000. We got new itanium machines running Win 2003 (64 bit). We want to move the db running on 32 bit win 2000 server to Wiin 2003 (64 bit) server. Someone suggested that (after installing the 64 bit oracle software) we copy the datafiles over and run a migration utility to convert the 32 bit db to 64 bit. I personally have never heard of such utility. Could you please share your experiences. How reliable is this utility and where to find the docs on this Thanks, Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: <[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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: data modelling question - job vs. job history table
Jacques, Why make it sooo complicated? I like the third table - Job_History that shows what job ran and when. Much clearer now, and in a year from now when you (or someone else) goes and reviews what you did. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 12, 2003 7:49 PM To: Multiple recipients of list ORACLE-L Warning - this is a little long. Thank you to those who take the time to read it. I have a data modelling question (the target database will be an Oracle database.) I am keeping track of scheduled jobs run by a job agent. Table 1: JOB with columns JOBNO (primary key), JOBNAME Table 2: JOB_SCHED with columns JOB_SCHED_ID (primary key), JOBNO (foreign key to JOB), JOB_INTERVAL, JOB_START_DATE, JOB_END_DATE, JOB_RESULT Table JOB_SCHED can have: completed jobs: JOB_START_DATE not null and JOB_END_DATE not null scheduled jobs: JOB_START_DATE not null and JOB_END_DATE null unscheduled jobs: JOB_START_DATE null and JOB_END_DATE null The job can be scheduled to run only once: JOB_INTERVAL null or scheduled to run periodically: JOB_INTERVAL not null A user can save an unscheduled job and then schedule it later. As currently designed JOB_SCHED contains job history for past jobs. My background scheduler often looks up jobs to see which jobs should run now. If JOB_SCHED contains the history of all jobs run then I will have to scan through many rows to find out those jobs which should run now. I could do this in several ways: Option 1: put completed jobs in a different table called JOB_HISTORY, and then JOBNO would be UNIQUE in JOB_SCHED, or I could combine the columns in JOB and JOB_SCHED Option 2: select * from JOB a, JOB_SCHED b where a.JOBNO = b.JOBNO and b.JOB_START_DATE is not null and b.JOB_END_DATE is null But I propose option 3: Add to JOBNO a column called CURRENT_JOB_SCHED_ID (foreign key to JOB_SCHED) This should make it faster to find the current schedule for the job. The tables have reciprocal foreign key relationships: JOB_SCHED.JOBNO foreign key references JOB.JOBNO -> FK_JOBNO JOB.CURRENT_JOB_SCHED_ID foreign key references JOB_SCHED.JOB_SCHED_ID -> FK_JOB_SCHED FK_JOBNO characteristics: ON DELETE CASCADE FK_JOB_SCHED characteristics: DEFERRABLE INITIALLY DEFERRED (you insert into JOB before you insert into JOB_SCHED) On JOB I have a BEFORE INSERT TRIGGER that generates JOBNO and CURRENT_JOB_SCHED_ID based on a sequence On JOB_SCHED I have a BEFORE INSERT TRIGGER that generates JOB_SCHED_ID based on a sequence if JOB_SCHED_ID is null To create a new job: insert into JOB returning the new JOBNO and CURRENT_JOB_SCHED_ID set by trigger -- the insert into JOB will succeed because the FK relationship to JOB_SCHED is a DEFERRABLE FK constraint insert into JOB_SCHED using the schedule ID returned by the above insert commit When a periodic job has completed: update JOB_SCHED set JOB_END_DATE = SYSDATE insert into JOB_SCHED returning the new JOB_SCHED_ID set by trigger, START_DATE = previous START_DATE + INTERVAL update JOB set CURRENT_JOB_SCHED_ID to the schedule ID returned by the above insert commit When a "run-once" job has completed: update JOB_SCHED set JOB_END_DATE = SYSDATE update JOB set CURRENT_JOB_SCHED_ID to null commit Is there any reason why option 3 should be avoided? -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Stop using SYS, SYSTEM?
I agree 100% with Dick. Nobody should be using SYS or SYSTEM. If RMAN requires a SYS connection, then so be it. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 12, 2003 3:45 PM To: Multiple recipients of list ORACLE-L Personal Opinion here: I don't use SYS or system for anything where it is not absolutely required. All of the DBA's have the DBA role granted to them & we log on as ourselves. This is simply so that we don't accidentally step on something really important. In general one should never create anything in the SYS schema since it won't get exported when you do a full database export. System is a little safer, but still.. I have seen a couple of "white papers" that have made statements such as "SYS and SYSTEM should be locked and never opened" as well as other similar alarming (to the pointy headed non-technical types) statements that indicate that disaster is waiting in the wings. All of them can be summarily dismissed as having been written by those who are similarly non-technical. BTW: Even if you are auditing, a DBA can eliminate the records in V$Audit of they wish. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, November 12, 2003 3:05 PM To: Multiple recipients of list ORACLE-L We are being asked by Auditing to stop using the SYS, and SYSTEM accounts. They would like for us to create an Oracle Role with the same permissions a SYS and SYSTEM, then grant the role to each of the DBA's. Don't ask me why. Nothing is being audited in 99% of the databases. They just say it in a paper some where so they said we shouldn't use it. This seems like it would cause lots of problems with exports, imports, installs, etc... Has anyone had to deal with this type of request? Any potential problems with making the change? 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: 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Uncle Larry, wake up!!!
it's a quiet little secret in consultant-land right now that the older technologies are in play. as the older-folks retire, there is a need for cobol-based support. especially in NY state agencies. -Original Message- Sent: Friday, November 07, 2003 3:04 PM To: Multiple recipients of list ORACLE-L Goulet, Dick scribbled on the wall in glitter crayon: > OH, ANCIENT History!! u... do i admit to getting a job hit last week because i know CICS?;-) it's still out there and still being used. -- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] A hundred times every day I remind myself that my inner and outer life are based on the labours of others. - 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Uncle Larry, wake up!!!
tion. 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why does script have more than one slash(/) inside?
Maryann, It could mean very bad things. For example. If you have the following: Insert into table as select * from another_table / / The insert will run twice. Try it. You may not like it. A slash means to execute what's in the sqlplus sql buffer. So whatever is in there will be executed. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, November 07, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Sorry if I bore you all with my dumb questions, its just that the simplest and silliest things appear complex, if we dont know them... Often when I learn something, I go : was that all? and they go, yes, yes, that was all... Well, let me show you what I mean.. What does it mean when a .sql script has more than one slashes(/) inside it? code -- 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).
RE: Point-In-Time recovery question, Non-RMAN solution
Rick, In a perfect world, we would be tracking major changes to the database (even updates) by time. In your case, you are stuck with taking a WAG, or more better, and educated WAG. You said that you think you dropped the table at about 1700. You can choose a point-in-time recovery to be 1630, to be safe. When you open the database, if the table is there, then you are done. If not, do it again. Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 06, 2003 10:44 AM To: Multiple recipients of list ORACLE-L How was the timestamp derived prior to logminer as Point-In-Time recovery has been around a long time? Thanks Rick "Scott Canaan" <[EMAIL PROTECTED]> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED]Subject: RE: Point-In-Time recovery question, Non-RMAN solution .com 11/06/2003 10:09 AM Please respond to ORACLE-L Have you looked into using logminer? Even if it can't restore your table, it can give you the exact time that it was dropped. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 "Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer. -Original Message- Sent: Thursday, November 06, 2003 8:45 AM To: Multiple recipients of list ORACLE-L Hi DBAs, Oracle 8i, ArchiveLog, No RMAN Testing Point-In-Time Recovery I am confused on what time to substitute in the RECOVER DATABASE UNTIL TIME 'timestamp'; For example 2 days ago 11/04/2003 approximately 17:00 I drop a table. Today I decide I want that table back. I want to do an incomplete recovery to get the table back. How do I know what timestamp to use? I have an idea the I dropped the table but not exact. 1. SHUTDOWN Normal 2. BACKUP current database 3. Restore datafile that has the table in it. 4. connect internal 5. startup mount 6. recover database until time 'timestamp??'; 7. Alter database open resetlogs; 8. BACKUP current database Step 5 is my confusion. Also I assume all data is now lost since last archive restored to the present. The only way I know to get that data back is to 1. Export the table that was dropped. 2. Restore database from step2 3. Import table from step1 Is there better ways. Thanks Rick -- 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: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 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 me
RE: Metalink: Pwd Problems?
I was on this morning checking out an ora-600. It was slow, but I got there. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 06, 2003 9:50 AM To: Multiple recipients of list ORACLE-L Walked in to major problems this morning, and I cannot log into metalink. I keep getting invalid password errors. (We have 3 CSI's and I cannot get in on any of them) Anyone else having problems? Thanks! Barb __ 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: 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How do you genrate primary keys?
Yong, I hope you read the other replies. Soc. is the *worst* use of a PK if there ever was one. You say it is a minor problem so it can be easily changed. What if the SOC is used as the PK/FK in a hundred tables in your system? Is this an "easy" change? The first rule of thumb about PK's is - never change it Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 9:00 AM To: Multiple recipients of list ORACLE-L Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- "Mercadante, Thomas F" <[EMAIL PROTECTED]> wrote: > Jonathan, > > I think your idea of a paper is a good one. But I think we need to back th > question up to what the requirements are. > > First, to me, a primary key should not be something that a user would ever > see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a > college. Want to know how many times we had to change the Soc. for an > individual student because the parent filled the form out and used their > soc, or the kid used the wrong one?). Any id entered by a user is subject > to mistakes and changes. So the PK value must be protected from these types > of errors. > > The next requirement that may be needed is sequentiallity (is this a word?). > Does the application require that every sequence number be used. Sometimes > the answer is yes, and sometimes it just doesn't matter. > > These are the only two requirements I can think of. Based on the answers, > we then have options. Right now, Oracle sequences are working well for me. > I like the idea of SYS_GUID, just not sure where I would need it. > > Good idea and good luck! > > Tom Mercadante > Oracle Certified Professional > > > -Original Message- > Sent: Wednesday, November 05, 2003 8:19 AM > To: Multiple recipients of list ORACLE-L > > > 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 > ema
RE: How do you genrate primary keys?
--- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: How do you genrate primary keys?
Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L 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). -- 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 re
RE: database terminated
bad news. 106 is an "unreported" error in the metalink/ora-600 site. you need to submit a tar with Oracle. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 04, 2003 2:20 PM To: Multiple recipients of list ORACLE-L did someone by chance set any event for pmon process? There seems to be a bug 2329767 reported. Metalink has lot of information ... type in ora-600 106 in the search box. 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, November 04, 2003 2:04 PM To: Multiple recipients of list ORACLE-L Hi List, One of our databases was terminated due to error 600. Below is the error message from alert log file: Tue Nov 4 12:00:25 2003 Errors in file /oracle/admin/adminp0/bdump/adminp0_pmon_24501.trc: ORA-00600: internal error code, arguments: [106], [], [], [], [], [], [], [] Tue Nov 4 12:00:32 2003 Errors in file /oracle/admin/adminp0/bdump/adminp0_pmon_24501.trc: ORA-00600: internal error code, arguments: [106], [], [], [], [], [], [], [] Tue Nov 4 12:00:32 2003 PMON: terminating instance due to error 600 Instance terminated by PMON, pid = 24501 Could any one please let me know what was the reason caused this happened? Where can I start to look at the potential cause? Thanks in advance. We are on Oracle 8.1.7 and OS Sun 5.8. David ** 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. **4 -- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sequences in OPS/RAC
Hemant, I would guess that this is true if you are caching values for the sequence. Each database instance might cache the same set of values. Turn sequence caching off, and I would think that the problem goes away. Havn't tried this in awhile, but it makes sense. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, November 03, 2003 10:39 AM To: Multiple recipients of list ORACLE-L I have always been comfortable with the idea that Sequences continue to guarantee uniqueness even in OPS / RAC environments. However, a recent Builder.Com article by Scott Stephens on the SYS_GUID function has these lines : "Sequence generator numbers are guaranteed to be unique only for a single instance, which is unsuitable for use as a primary key in parallel or remote environments, where a sequence in each environment might generate the same number and result in conflicts. An identifier created by SYS_GUID is guaranteed to be unique for each database." Huh ?! Do the lines mean that a single sequence can have duplicate values in the two instances of an RAC cluster ? Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Moving projects from development/test to production
Helmut, Not sure of the scope of your request, but I'll try and help. If you are talking about ERP packages, I think it's easier to control - you only move stuff after much testing in a development/user test environment, and it all goes as a package. If you are talking about home-grown stuff, change control starts at the development level. For the current project that I am on, I (the Dba team) established a formal change request application for the developers. They submit a request to change a database object. the request gets reviewed and implemented by the DBA team in development. There are four check boxes for each request that indicate when the request was implemented in each of the environments (Dev, User Test, Train & Prod). Reports are run to indicate which request is "eligible" to be moved to the next level (implemented in Dev, but not in User test). When a build is requested, this report is reviewed, and things are moved up as needed. So we always know when things are moved up and what request is where. A pretty simple application (written in MSAccess) that is working well for us. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, October 31, 2003 2:59 AM To: Multiple recipients of list ORACLE-L Hi! A somewhat off-topic question this time. I am put in charge of defining the procedure of moving projects from test/development into the production environment. This is to be seen from the entire IT-perspective (i.e. not just databases, but also Unix, Oracle and SAN). I.e. we should come up with check-lists and the like; although having an eye on quality assurance... We urgently need to set procedures up for that since the last time this was a nightmare... Did anybody out there work on a similar project? What are the procedures that you are following? Any input would be appreciated. This is 9.2 on HP-UX 11. Thanks, Helmut Helmut Daiminger WWK Lebensversicherung a.G. Marsstrasse 37 80292 München Telefon: (0 89) 51 14 - 3490 Fax: (0 89) 51 14 - 27 62 mailto:[EMAIL PROTECTED] http://www.wwk.de *** select 'bye for now' from sys.dual *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: proc to pl/sql
=3D'font-family:Tahoma;color:blue;mso-color-alt: > windowtext'> > > face=3DTahoma> style=3D'font-size:12.0pt;font-family:Tahoma;color:blue;font-weight:bold= > '>Stephen > P. Karniotis > > style=3D'font-size: > 12.0pt;font-family:Tahoma;color:red'>Technical Alliance = > Manager > > style=3D'font-size: > 12.0pt;font-family:Tahoma;color:blue'>Compuware = > Corporation > > style=3D'font-size: > 12.0pt;font-family:Tahoma;color:blue'>Direct: style=3D'mso-tab-count:1'> &nbs= > p; (313) > 227-4350 > > style=3D'font-size: > 12.0pt;font-family:Tahoma;color:blue'>Mobile: style=3D'mso-tab-count:1'> &nbs= > p; (248) > 408-2918 > > style=3D'font-size: > 12.0pt;font-family:Tahoma;color:blue'>Email: style=3D'mso-tab-count:1'> [EMAIL PROTECTED] > > > style=3D'font-size: > 12.0pt;font-family:Tahoma;color:blue'>Web: style=3D'mso-tab-count:1'> www.compuware.com > > > class=3DEmailStyle15> size=3D2 color=3Dblue face=3D"Comic Sans MS"> style=3D'font-size:10.0pt; > mso-bidi-font-size:12.0pt;font-family:"Comic Sans MS"'> = > > > color=3Dblack > face=3DTahoma> style=3D'font-size:10.0pt;font-family:Tahoma;color:black'>-Original > Message- > From: AK > [mailto:[EMAIL PROTECTED] > Sent: Thursday, October = > 30, 2003 > 11:44 AM > To: Multiple recipients = > of list > ORACLE-L > Subject: proc to = > pl/sql > > face=3D"Times New Roman"> style=3D'font-size:12.0pt'> > > color=3Dblack > face=3DArial> style=3D'font-size:10.0pt;font-family:Arial;color:black'>Is > there any tool out there that can convert pro*c code to pl/sql = > ? color=3Dblack> style=3D'color:black;mso-color-alt:windowtext'>= > > > color=3Dblack > face=3D"Times New Roman"> style=3D'font-size:12.0pt;color:black'> color=3Dblack> style=3D'color:black;mso-color-alt:windowtext'>= > > > color=3Dblack > face=3DArial> style=3D'font-size:10.0pt;font-family:Arial;color:black'>thanks,<= > /font> color=3Dblack> style=3D'color:black;mso-color-alt:windowtext'>= > > > color=3Dblack > face=3DArial> style=3D'font-size:10.0pt;font-family:Arial;color:black'>-ak t> color=3Dblack> style=3D'color:black;mso-color-alt:windowtext'>= > > > color=3Dblack > face=3D"Times New Roman"> style=3D'font-size:12.0pt;color:black'> color=3Dblack> style=3D'color:black;mso-color-alt:windowtext'>= > > > > > > > > > > > > The contents of this e-mail are = > intended for the named addressee only. It contains information that may = > be confidential. Unless you are the named addressee or an authorized = > designee, you may not copy or use it, or disclose it to anyone else. If = > you received it in error please notify us immediately and then destroy = > it. > > > --_=_NextPart_001_01C39F0A.3F044680-- > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Karniotis, Stephen > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Todd Boss INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Oracle failsafe and RMAN
Yuval, I'm assuming you are running Microsoft Cluster Services. Are you running the Rman backup on the cluster, or on the physical box? Either way, I think your Rman backup will fail, but the database will come back just fine. Rman simply has a connection attached to the database running on the primary server (just like a user) If that database goes away, then the Rman session goes away. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 30, 2003 1:49 PM To: Multiple recipients of list ORACLE-L List, This question is for anyone using RMAN with Oracle 8i and Failsafe on Win2k. What will happen when the primary node fails and Oracle Failsafe switches to the other node while RMAN is runningg.?? Will the database come up or it will not be able to?? Thanks. Yuval This transmission may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you, [EMAIL PROTECTED], are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arnon, Yuval INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: RE: Date-based query Q
How about this?
SELECT col_date, SUM(COUNT)
FROM (
SELECT ukdate, COUNT(*) COUNT
FROM tomtest
GROUP BY ukdate
UNION
SELECT ADD_MONTHS(TO_DATE('12012002','mmdd'),ROWNUM) dba_month,0
FROM DBA_OBJECTS
WHERE ROWNUM < 13)
GROUP BY ukdate
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, October 29, 2003 9:09 AM
To: Multiple recipients of list ORACLE-L
Same idea as what Iain suggests, dreadful implementation :
SQL> select trunc(ukdate) ukdate, count(*) from test
2 group by trunc(ukdate);
UKDATE COUNT(*)
-- --
01/01/2003 5
02/01/2003 6
04/01/2003 6
SQL> get x
1 select y.full_ukdate ukdate,
2 nvl(x.cnt, 0) "COUNT(*)"
3 from (select trunc(ukdate) ukdate,
4 count(*) cnt
5from test
6group by trunc(ukdate)) x,
7 (select a.rn + b.mindate - 1 full_ukdate
8from (select rownum rn
9 from all_tab_columns) a,
10 (select min(ukdate) mindate,
11 max(ukdate) maxdate
12 from test) b
13 where a.rn <= b.maxdate - b.mindate + 1) y
14* where x.ukdate (+) = y.full_ukdate
SQL> /
UKDATE COUNT(*)
-- --
01/01/2003 5
02/01/2003 6
03/01/2003 0
04/01/2003 6
Do you _really_ want that :-) ?
Didn't find analytical functions of much help on this one ...
SF
>- --- Original Message --- -
>From: "Nicoll, Iain" <[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>Sent: Wed, 29 Oct 2003 04:44:25
>
>You colud try joining to an in-line view something
>like
>
>SELECT :XDATE+(ROWNUM-1) DDATE
>FROM DBA_OBJECTS
>WHERE ROWNUM <= (:YDATE - :xdate)+1
>
>where dba_objects could be any table with enough
>rows to ensure you always
>covered the complete range.
>
>
>
>-Original Message-
>Aidan Whitehall
>Sent: 29 October 2003 10:49
>To: Multiple recipients of list ORACLE-L
>
>
>This is probably a no-brainer...
>
>We have some date-based data for which most days
>have several records
>but where some days have none. I'm COUNT()ing the
>number of records for
>each day (between day x and day y) and need a
>record set that also
>includes a row for those days which have no
>records:
>
>UkDate Total
>1/1/20035
>2/1/20036
>3/1/20030
>4/1/20036
>
>I could post-process the record set to achieve
>this, but is there any
>way in 9i to do an aggregate query with an outer
>join on a date range
>(if that makes sense)?
>
>Someone made the suggestion of creating another
>table with a row for
>every day under the sun in it, against which you
>could inner join the
>main query, but I'm not keen on that (that is just
>a gut response
>though).
>
>Any ideas? Thanks!
>
>--
>Aidan Whitehall
>
>Macromedia ColdFusion Developer
>Fairbanks Environmental Ltd +44 (0)1695 51775
>Queen's Awards Winner 2003
>
--
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: =?utf-8?B?TWVyY2FkYW50ZSwgVGhvbWFzIEY=?=
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Clone db 9.2 on AIX 5L
line containing: UNSUB ORACLE-L (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: John Blake INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Query Tuning Help
Title: Query Tuning Help David, The calculation "(> SYSDATE - 35)" is not causing the problem. The To_Date(a2.file_dts, 'mmddyyhh24miss') is. You said you created a function based index. I think you also need to set: Query_ReWrite_Integrity = TRUSTEDQuery_ReWrite_Enabled = TRUE for function based indexes to work. Check out the doco for function based indexes. Good Luck! Tom Mercadante Oracle Certified Professional -Original Message-From: David Wagoner [mailto:[EMAIL PROTECTED]Sent: Monday, October 27, 2003 10:34 AMTo: Multiple recipients of list ORACLE-LSubject: Query Tuning Help I'm trying to tune the following query to use an index on the FILE_DTS column, rather than a FTS on the CLASS_CONFIG table (~350,000 rows). SELECT a2.class_config_id, a1.schedule_name FROM class_config a2, class_schedule a1 WHERE a2.class_config_id = a1.class_config_id AND to_date(a2.file_dts, 'mmddyyhh24miss') > SYSDATE - 35 I created a function-based index on FILE_DTS, like this: CREATE INDEX CLASS_CONFIG_FILE_DTS_FX_IDX ON CLASS_CONFIG to_date(file_dts, 'mmddyyhh24miss') and analyzed the table, but the explain plan still shows a FTS. I can change the query to something simpler and get it to use the new index, but I assume the calculation (> SYSDATE - 35) is causing the problem. Any suggestions? Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions Web: http://www.arsenaldigital.com <<...OLE_Obj...>> The contents of this e-mail message may be privileged and/or confidential. If you are not the intended recipient, any review, dissemination, copying, distribution or other use of the contents of this message or any attachment by you is strictly prohibited. If you receive this communication in error, please notify us immediately by return e-mail or by telephone (919-466-6700), and please delete this message and all attachments from your system.Thank you.
RE: Coalescing tablespace
subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle pricing ain't going down
ala 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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).
RE: full recovery
AK, First, your normal backup should backup your database in the following order: Database, archivelogs and then control file. I actually do this in three separate Rman steps. This is so that the most recent control file is backups up after all of the data. This allows your to perform an incoimplete-recovery-restore to as late a time as possible. Also, consider adding a database trigger that, upon startup, perform an "alter database backup controlfile to trace;" Keep a copy of this trace file someplace safe as a sanity check. You could use it to recreate your controlfiles if all else fails. Your recovery steps are as follows: 1. restore oracle software from tape. 2. restore config files ( init.ora , listener. ora ). 3. startup instance with nomount. 4. run Rman to restore the control file from tape. 5. Alter database mount 6. run Rman to restore database files 7. alter database open resetlogs. 8. perform a brand-new Rman backup (database, logs & controlfile) turn the system back to the users (with many back-pats from management). You should be testing this on a regular basis. Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, October 24, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Here is a scenerio : I am taking full database backup everynight using rman to tape . which includes archive logs and control file. Not using catalog.Also have a backup of complete file system including oracle software and configuration files ( init.ora , listener.ora etc.. ) I lost the host on a particular day at 12 am afternoon. Now I want to restore this db to latest possible time to another host ( with same name )
RE: how to specify tablespace in Designer
sundeep, are you saying that the people who responded to this question are not "real experts"? where do you live? look out your front door. that's me with a baseball bat! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 23, 2003 9:44 PM To: Multiple recipients of list ORACLE-L Ben, If you had posted this question to designer-L you'd have gotten insight from some real experts. In designer table definition(table columns, indexes, keys, constraints) is different from table implementation(schema, tablespace, storage parameters). The same table definition can be associated with many different table implementation (e.g. different storage for the same table depending on test, prodction environment etc). In design editor's db admin tab you need to define database (under your application folder) then tablespace and/or storage definitions and users under that database. You also need to give users quota on tablespaces. Now generate the table DDL while in D Admin tab. Your DDL will now contain the all the artifacts. This is tedious process if you have large set of tables. If you want to do it repeatedly, create the database, tablespace, user and quotas ahead of time. While converting your ER model (you use ER modeler right?) using database design transformer, you can specify the table space for table and indexes. The transformer will create both table definitions and table implementations for you. Then from Design Editor's DB Admin tab you generate DDL for for your tables. --- Ben <[EMAIL PROTECTED]> wrote: > Hi > > Can someone who has used Designer to generate DDL let > me know how you specify which tablespace a table or index > is to be created in. I am using the Design Editor and > the DBAdmin tab and can't seem to find it. > Thanks in advance. > > Ben > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Ben > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting > services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: dba interview questions
A French or English Swallow? -Original Message- Sent: Thursday, October 23, 2003 5:39 PM To: Multiple recipients of list ORACLE-L Mladen -- I'm *stunned* you missed the most obvious of all DBA 101 questions... What is the average air velocity of an unladen swallow? Bambi. -Original Message- Sent: Thursday, October 23, 2003 4:14 PM To: Multiple recipients of list ORACLE-L 1) What does it mean to grokkk? 2) What is the answer to the question of life, universe and everything? 3) What happened to Sauron when he flipped the bird to Izildur? 4) What is the monolyth and what was its effect on the resident apes? 5) What is damagement? What color of the database saves memory? 6) What can you tell me about lord Edmund Blackadder? Chances are that if someone answers those questions correctly, you've got yourself a worthy DBA. On 10/23/2003 04:14:34 PM, system manager wrote: > Dear List,Can anyone send me a list of dba interview questions? > > Thanks, > > > _ > Free email with personality! Over 200 domains! > http://www.MyOwnEmail.com > Looking for friendships,romance and more? > http://www.MyOwnFriends.com > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: system manager > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (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: 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how to specify tablespace in Designer
Ben, You need to implement the table to assign storage attributes to it. This can be done either in the Ron or Db Designer where you are at. In Db Designer/DbAdmin tab, expand the database|Users|spcific User|Schema|Table Implementations entry. Click the green plus to implement a table in this area, and you should see the properties attribute pop up. Optionally right click on the Table Implementations selection and select "Create Table Implementations". Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 23, 2003 2:45 PM To: Multiple recipients of list ORACLE-L Hi Can someone who has used Designer to generate DDL let me know how you specify which tablespace a table or index is to be created in. I am using the Design Editor and the DBAdmin tab and can't seem to find it. Thanks in advance. Ben -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Can I concatenate several rows without a procedure?
yeah, but it's a convoluted requirement. if they really wanted to retrieve all rows in one column, why didn't they use a Clob instead??? :) Tom Mercadante Oracle Certified Professional -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, October 21, 2003 5:55 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Can I concatenate several rows without a procedure?Oh my, that *is* convoluted. :) Stephane Faroult <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/21/2003 02:04 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: Can I concatenate several rows without a procedure?Jake Johnson wrote:> > The following query returns 33 records.> > SYS0 freestyle!! 12-MAY-02> SYSTEM5 freestyle!! 12-MAY-02> OUTLN11 freestyle!! 12-MAY-02> > > But, I would like to have all 33 records appended together to have one long record.> > SYS0 freestyle!! 12-MAY-02SYSTEM5 freestyle!! 12-MAY-02OUTLN11 freestyle!! 12-MAY-02> > Thanks again,> Jake> > On Tue, Oct 21, 2003 at 07:49:25AM -0800, Bob Metelsky wrote:> > select username||user_id||' freestyle!! '|| created as concat from> > all_users;> >> >> > Hello,> > I am trying to concatenate several records with simple sql. Is this> > possible?> >> >> > --> > Thanks,> > Jake Johnson> > [EMAIL PROTECTED]> >SQL> select * from sliced_kipling; VERSE PIECE CHUNK-- -- -- 1 1 Oh, East is East, 1 2 and West is West, 1 3 and never the twain shall meet, 2 1 Till Earth and Sky stand 2 2 presently at God's great Judgment Seat; 3 1 But there is neither East nor West, 3 2 Border, 3 3 nor Breed, 3 4 nor Birth, 4 1 When two strong men stand face to face, 4 2 tho' they come from the ends of the earth!11 rows selected.SQL> @magic_queryVERSEOh, East is East, and West is West, and never the twain shall meet,Till Earth and Sky stand presently at God's great Judgment Seat;But there is neither East nor West, Border, nor Breed, nor Birth,When two strong men stand face to face, tho' they come from the ends ofthe earth!SQL> l 1 select translate(ltrim(x.text, '/'), '/', ' ') verse 2 from (select verse, level lvl, sys_connect_by_path(chunk, '/') text 3 from sliced_kipling 4 connect by verse = prior verse 5 and piece - 1 = prior piece) x, 6 (select verse, max(piece) piecemax 7 from sliced_kipling 8 group by verse) y 9 where x.verse = y.verse10 and x.lvl = y.piecemax11* order by x.verseSQL> I am not sure though that I satisfy the 'simple SQL' requirement :-).Stephane Faroult-- 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.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Can I concatenate several rows without a procedure?
Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Tires, and Wheel Packages. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jake 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: 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: disaster recovery doc
poor bastard. you'll spend hours and hours developing a document nobody really wants, nobody will read, and (hopefully) you will never use. if I were you, I'd start lobbying for an outside agency to write it for you. sure, you'd have to work with them. but they would get it done faster, create a better report because they could focus on just it, and would have better experience in what to put into it. good luck! Tom Mercadante Oracle Certified Professional -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Tuesday, October 21, 2003 2:14 PMTo: Multiple recipients of list ORACLE-LSubject: disaster recovery doc Hi Guys , I have been recently tasked to write up procedures and steps / documentation for disaster recovery of db/system . Any help , pointers ,links related to this is highly appreciated . Thanks, -ak OCP DBA 8i
RE: Cache a table
hin client can easily query these tables only >once and store >the results. Nope. No sirree... > > >It's a little-known fact that Java code actually has the consistency of >concrete, once in production. There are so many interdependencies from >shared modules and RPCs that people are terrified of modifying >anything, >probably for good reason. Far easier to shift blame or say >"hear hear" when >the vendor proposes another 4-8 CPUs. > >Ah, I believe I'll have another beer when you're ready, Sammy... > > >Anyway, first tune the SQL. Then, tune to the application to >get rid of >unnecessary SQL. Then and only then, consider tuning the >Buffer Cache to >segregate "bad" tables to the RECYCLE pool or "pinning" tables >to the KEEP >pool. Reversing the order is a great way to convert a happy >application >capable of running on a small server to an unhappy application >demanding a >huge server... > > > >on 10/21/03 5:21 AM, Arup Nanda at [EMAIL PROTECTED] wrote: > >> Never. Altering the table to cache does not gurantee that it >will be always >> be available in the cache. It simply means the table will be >placed in the >> Least recently used end of the LRU list and it will age away >as time goes >> by, just like any other table. >> >> A better approach is to use KEEP pool and place teh table >(and all other >> tables that are accessed frequently) there. This is >particualrly true for >> datawarehouses wherethe lookup tables or small dimension >tables can be >> placed in KEEP pool. >> >> Ah, come tho think about it, actually there is one situation >where I will >> consider the CACHE option, when I restart the instance and >want the hit >> ratio to look good :) >> >> HTH. >> >> Arup Nanda >> >> - Original Message - >> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> >> Sent: Tuesday, October 21, 2003 3:39 AM >> >> >>> Hi all, >>> >>> when you would consider to put a table a cache... >>> >>> rgds >>> >>> gb >>> >>> >___ >_ >>> Want to chat instantly with your online friends? Get the >FREE Yahoo! >>> Messenger http://mail.messenger.yahoo.co.uk >>> -- >>> Please see the official ORACLE-L FAQ: http://www.orafaq.net >>> -- >>> Author: =?iso-8859-1?q?Gunnar=20Berglund?= >>> INET: [EMAIL PROTECTED] >>> >>> Fat City Network Services-- 858-538-5051 http://www.fatcity.com >>> San Diego, California-- Mailing list and web >hosting services >>> >- >>> To REMOVE yourself from this mailing list, send an E-Mail message >>> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >>> the message BODY, include a line containing: UNSUB ORACLE-L >>> (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: Tim Gorman > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: WHERE 1 = 1 (any info on this)
Raj, What's wrong with SQR? I used it for a few years and found it great. Especially for batch processing (both report writing and batch updating). We chose it when we ran away from Cobol about 9 years ago. At the time, our choices were Oracle Rpt (can you say RPG?), the very first version of Oracle Reports (boy, did that ever suck) and staying with Cobol. We were very happy with our choice. it is still the tool used by Banner (College Erp software). Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, October 21, 2003 12:14 PM To: Multiple recipients of list ORACLE-L I have seen people use this in pseudo-dynamic sql in a beast called SQR. Especially when they have code like ... select ... union select ... union select ... union select ... limit to your imagination. Lot of people think that using 1=1 in absence of a valid limiting condition will let them evaluate (and get data from) a union clause where as putting 1=2 will help them avoid ... I don't hate just because I don't like SQR, I don't like it because until CBO encounters and evaluates 1=2 clause, it still does all the work of selecting etc and that is just a waste. 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 -- 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: 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).
