RE: Export Problem - Urgent
You should try changing the nls_lang value on the client to match the server. Rick -Original Message- Sent: Friday, November 30, 2001 8:10 AM To: Multiple recipients of list ORACLE-L Hi, I am trying to export a data from server and getting following error message: EXP-00041: Export done in server's WE8ISO8859P1, different from user's character set US7ASCII EXP-0: Export terminated unsuccessfully How do I fix a problem. My environment is SUN OS 5.6 and Oracle 8.1.6 and trying to export from server itself but still getting error message. Thanks, Meghdeep -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MRaval INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle 7.2 32 bit Oracle Ole Objects
Hi, Does anyone know how I can get a copy of 16 bit Oracle 7.2 Oracle Ole Objects to install? I know probably next to impossible to find but any help is appreciated. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: SqlSever 2000 question
Hi All, Sorry for OT but I am very new(few hours) to sql server and need help with constructing a query. I have 2 tables Table1 Table2 id id name, county state There is 1 - many from table1 to table2 I want to retrieve each record in table1 along with all the counties in table2 for that ID in one record For ex. 1,SmithJ,TN,county1 | county2 | county3 | countyN Any ideas, Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Script to Disable Constraint, Change Value, then Enable Const
I have had the same type of requirement and decided to use deferrable constraints. Works great! Rick -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 11:56 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Script to Disable Constraint, Change Value, then Enable Const Not good approach. Instead, use 'deferrable constraints, should work in your situation. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: David Wagoner To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 28, 2001 11:24 AM Subject: RE: Script to Disable Constraint, Change Value, then Enable Const I can see the confusion here. The point is not to let someone enter data that would violate the referential integrity. Let me explain with an example: 1. User wants to update a primary key record in parent table 2. Dependent data exists in a child table so the user gets an error while trying to perform step 1 3. It is necessary to disable the FK constraint in order to update both tables 4. Enable the FK constraint successfully Does that make sense? This is a process we have to do routinely and it has happened in the past that the FK was mistakenly not re-enabled, which allowed "illegal" data to be loaded later. Thus the need for a script. David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide Inc. 4815 Emperor Blvd., Suite 110 Durham, NC 27703 Tel. (919) 941-4645 Fax (919) 474-0735 Email mailto:[EMAIL PROTECTED] Web http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender at (919) 941-4645 and delete this e-mail message from your computer. Thank you. -Original Message-From: Mark Leith [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 5:20 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Script to Disable Constraint, Change Value, then Enable Constrain How could this be user proof? You are essentially disabling the constraint that WILL enforce data integrity, then letting the user input whatever rubbish he wants to, and are then going to try and enable the constraint afterwards? Not a good approach.. How can you ensure that the user hasn't put a duplicate value in (unique constraint) or something else that might break the constraint rule? The only way you are going to know is when you try and re-enable the constraint it will fail.. I struggle to see why you would want to do this - do you have any more info? -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of David WagonerSent: 27 November 2001 21:30To: Multiple recipients of list ORACLE-LSubject: Script to Disable Constraint, Change Value, then Enable Constrain Listers, Does anyone have a script that will do the following: 1. Accept user input for old data value 2. Accept user input for new data value 3. Disable table constraint 4. Update record with new data value 5. Enable constraint A script like this would help ensure that constraints are not left "off" after updates, allowing "illegal" data into the tables. Good user-proof script I would think. TIA, david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide Inc. 4815 Emperor Blvd., Suite 110 Durham, NC 27703 Tel. (919) 941-4645 Fax (919) 474-0735 Email mailto:[EMAIL PROTECTED] Web http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender at (919) 941-4645 and delete this e-mail message from your computer. Thank you.
RE: Doubts reg :Export and import
compress=y means all the extents will be compressed into a single extent. It has nothing to do with compressing .dmp file. Rick -Original Message- Sent: Wednesday, November 28, 2001 12:16 PM To: Multiple recipients of list ORACLE-L hi list, what is the exact use of mentioning 'compress' yes or no while exportingwill this store the .dmp file in compressed format in system,if given 'yes'. while importing the dumpfile why is it necessary to give 'fromuser',is 'touser' not enough. sangeetha __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sangeetha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Script to Disable Constraint, Change Value, then Enable Const
David, You need a semi-colon after line2 i.e., update table1 ...; Rick -Original Message-From: David Wagoner [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 1:16 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Script to Disable Constraint, Change Value, then Enable Const Rick, thanks for your input. When I test the deferred constraint in SQL*Plus I get an error. What am I doing wrong? I did not find much information in the docs about deferred constraints. SQL set constraints all deferred 2 update table1 set host_name = 'tigerz' where host_name = 'tiger' 3* update table2 set host_name = 'tigerz' where host_name = 'tiger'; SQL update host set host_name = 'tigerz' where host_name = 'tiger' * ERROR at line 2: ORA-00933: SQL command not properly ended TIA, David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide Inc. 4815 Emperor Blvd., Suite 110 Durham, NC 27703 Tel. (919) 941-4645 Fax (919) 474-0735 Email mailto:[EMAIL PROTECTED] Web http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender at (919) 941-4645 and delete this e-mail message from your computer. Thank you. -Original Message-From: Cale, Rick T (Richard) [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 12:29 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Script to Disable Constraint, Change Value, then Enable Const I have had the same type of requirement and decided to use deferrable constraints. Works great! Rick -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 11:56 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Script to Disable Constraint, Change Value, then Enable Const Not good approach. Instead, use 'deferrable constraints, should work in your situation. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: David Wagoner To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 28, 2001 11:24 AM Subject: RE: Script to Disable Constraint, Change Value, then Enable Const I can see the confusion here. The point is not to let someone enter data that would violate the referential integrity. Let me explain with an example: 1. User wants to update a primary key record in parent table 2. Dependent data exists in a child table so the user gets an error while trying to perform step 1 3. It is necessary to disable the FK constraint in order to update both tables 4. Enable the FK constraint successfully Does that make sense? This is a process we have to do routinely and it has happened in the past that the FK was mistakenly not re-enabled, which allowed "illegal" data to be loaded later. Thus the need for a script. David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide Inc. 4815 Emperor Blvd., Suite 110 Durham, NC 27703 Tel. (919) 941-4645 Fax (919) 474-0735 Email mailto:[EMAIL PROTECTED] Web http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender at (919) 941-4645 and delete this e-mail message from your computer. Thank you. -Original Message-From: Mark Leith [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 5:20 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Script to Disable Constraint, Change Value, then Enable Constrain How could this be user proof? You are essentially disabling the constraint that WILL enforce data integrity, then letting the user input whatever rubbish he wants to, and are then going to try and enable the constraint afterwards? Not a good approach.. How can you ensure that the user hasn't put a duplicate value in (unique constraint) or something else that might break the constraint rule? The only way you are going to know is when you try and re-enable the constraint it will fail.. I struggle to see why you would want to do this - do you have any more info? -Original Message-From:
RE: SQL Query OutPut
In sqlplus BREAK ON deptno skip 1 SELECT deptno,empno FROM your_table ORDER BY deptno,empno; You may want to set pagesize,spool etc. Rick -Original Message- Sent: Thursday, November 15, 2001 5:05 AM To: Multiple recipients of list ORACLE-L Hi, I need the Query for the following result. If any body help it will be very great. Its very urgent. the output needed is DeptNoEmpNo 101 2 3 4 5 2011 12 13 The DeptNo should not repeat for all the employees. Thanks in advance Regards Pruthvi _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pruthvi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Command line utility to show installed Oracle software
Hi All, I have forgotten the command line utility to show installed Oracle software. Oracle 8.0.5/NT 4. Can someone tell me? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
LMTs Uniform vs Autoallocate
Hi All, Does anyone know Pros/Cons of using Uniform vs Autoallocate extent allocation for LMTs? I know temp has to use uniform but are there are there other restrictions/recommendation for other tablespace types? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problems with Pro*C
Do you have userid between? All variables used in embedded sql must be declare in this section. EXEC SQL BEGIN DECLARE SECTION; EXEC SQL END DECLARE SECTION; Rick -Original Message- Sent: Wednesday, November 14, 2001 7:16 AM To: Multiple recipients of list ORACLE-L Hello I am a novice to ProC. I have Some problems with compiling a sample file from ProC manual for the code: char userid[12]=scott/tiger; EXEC SQL CONNECT :userid I receive an error that says userid is an undefined variable, could someone help me? Thanks very much E. __ Do You Yahoo!? Find the one for you at Yahoo! Personals http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ehsan sinavalda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sqlimportante
You can try something like SELECT SUBSTR(field1,1,INSTR(field1,' ',-1)-1) FROM your_table; This are limitations. This assumes no trailing spaces and words are separated by a single space. If without limitations you could write pl/sql to do the same thing. HTH Rick -Original Message- Sent: Wednesday, November 14, 2001 7:35 AM To: Multiple recipients of list ORACLE-L Hallo, If I have this field1 Kenny Jenny A1000 Jenny L1000 No info Missing Info I would have: Kenny Jenny Jenny No info Missing Info How do I write the select statement to solve this I just want the last word in the field to be taken away in the result from the select statement. Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: import/export problem in oem
Do you have sysdba or sysoper privs and are you connecting to OEM with sysdba/ Rick -Original Message- Sent: Wednesday, November 14, 2001 12:31 PM To: Multiple recipients of list ORACLE-L hi,gurus help me ,in this problem through command prompt i got export/import facility .but with same user (with all permissions),i am not able to do same job. error is: The following error has occurred while trying to make the connection to the target database using preferential credentials ora-01031:insufficient previleges. thanks to all babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: hari babu gottipati INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Where are the Import/Export utilities in 9i
Hi All, I have just installed Oracle 9i server on a test machine and I cannot find the import/export utilities. There is no EM like version 8 or DBA studio as in 8i. I started EM console and saw wizards but I always get wizard can only be used while connected to oracle management server. I have never needed this before. How can I just do a simple import/export? Oracle 9i/Win 2k environment Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
IMP-00037 Character marker set unknown
Hi DBAs, I trying to import uncorrupted 8.0.5 export into a Oracle 9i database. I get a IMP-00037 Character marker set unknown. MetaLink says it may be corrupted. It is not so anyone have any other suggestions? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: inactive sessions
An INACTIVE status means that the process is still connected to Oracle but not running a query. These are valid connections and will not be cleaned up. It is the job of the pmon process to clean up dead connections. It usually wakes up approximately every 5 minutes. Rick -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, November 02, 2001 6:50 AM To: Multiple recipients of list ORACLE-L Hi can anybody tell me when a sessions status becomes INACTIVE in v$session? thnx and regards, srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what is the fastest way to update a big table with more than 50000 rows and 20 columns
UPDATE join a SET a.source = (SELECT b.source FROM cs b WHERE b.totid = a.totid); Rick -Original Message- Sent: Wednesday, October 31, 2001 7:15 AM To: Multiple recipients of list ORACLE-L rows and 20 columns hi guys, i have a big problem in updating an oracle 8i 1.7 database table with more than 5 rows and 20 columns. The problem is: i have another table with approximately 17000 rows and 12 columns. The latter one holds newer data, and most of them have the same primary key as the previous one (16000). Now, i want to update the older table with newer data from the new table. I wrote a pl/sql procedure to achieve this task, but it takes too long ( nearly 12 hours) , can you have a look at the below procedure and tell me what is wrong with it and where i am making mistake? CAn you suggest me a faster and more efficient way to do that? thanks alot for your help in advance. cheers :) my Pl/sql procedure : declare cursor c1 is select c.totid,j.source from join j,cs c where j.source is not null and trim(j.totid)=trim(c.totid); --join is the new table (17700 rows) --cs id the older one (50300rows) tot cs.TOTID%type;--totid is the primary key for both tables src cs.source%type;--source is the column to be updated begin open c1; loop fetch c1 into tot,src; tot:=trim(tot); exit when c1%notfound; update cs set source=src where trim(totid)=tot; end loop; commit; close c1; end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yilmaz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what is the fastest way to update a big table with more than
When you create the new table with char(8), I would use varchar2(8) but, do a trim when inserting into the new table because in your old table since it is char(255) it is blank padded so you are trying to put length of 255 into a length of 8. HTH Rick -Original Message- Sent: Wednesday, October 31, 2001 8:50 AM To: Multiple recipients of list ORACLE-L thanks Mercadante, actually i have to use trim() function because the type of join.totid (varchar2(255) and type of cs.totid (char(255) are different.So when i compare those two values my program can never find an equal value.I tried to change both of htem to char(8) , but i am having an error message saying that it can't be changed when the table is not empty. When i try to create new table with a totid of char(8), then i ama having two large data error message. Any idea how to avoid that? cheers :) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 31, 2001 9:00 PM yilmaz, First of all, your query is probably performing a full table scan on each table because of your use of the TRIM function. Remove this. Secondly, why join the two tables together? Just query the table with the newer data in a loop, and update the older table like this: declare cursor c1 is select j.totid, j.source from join j where j.source is not null; tot join.TOTID%type;--totid is the primary key for both tables src cs.source%type;--source is the column to be updated begin open c1; loop fetch c1 into tot,src; exit when c1%notfound; update cs set source=src where totid=tot; end loop; commit; close c1; end; Hope this helps. Tom Mercadante Oracle Certified Professional -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yilmaz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Consistent Gets
CONSISTENT GETS is the number of blocks accessed in buffer cache for normal queries (SELECTs without for update clause). Rick -Original Message- Sent: Tuesday, October 30, 2001 4:46 PM To: Multiple recipients of list ORACLE-L Hi. What exactly does Consistent Gets mean? Thanks Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Wiegard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle9i-Download
Title: Oracle9i-Download I had the same problem and gave up, submitted a tar to client relations and they sent complete 9i CD suite within a week. Rick -Original Message-From: Nirmal Kumar Muthu Kumaran [mailto:[EMAIL PROTECTED]]Sent: Friday, October 26, 2001 7:45 AMTo: Multiple recipients of list ORACLE-LSubject: Oracle9i-Download Hi guru's anybody had an exp of downloading oracle 9i for nt/2000 from oracle site? I downloaded 3 winzip files. But i'm not able open the 2nd zip file, i got winzip error" Cannot openfile: it does not appear tobe a valid archive" however 1st and 3rd are perfect. I downloaded 2nd file(558m) itself, 3 times... i had the same problem What shall i do, to successfully download it. I have very eagar to practice, 9i features. Rgds, Nirmal.
RE: Database will not shutdown in Normal or IMMEDIATE mode
Thanks everyone with the DROP TABLE ... and SHUTDOWN replies. This morning is much better. I was able to DROP tables I needed. The shutdown problem would not occur even after the DROP finally succeeded. I was in a position to do a cold boot of the server and viola I was able to shutdown and do backups. It is a beautiful sunny day in east TN. Thanks again, Rick -Original Message- Sent: Friday, October 05, 2001 9:20 AM To: Multiple recipients of list ORACLE-L Rick, By now, I hope your database is back in working order. My guess as to why you could not shut down your database is that the Drop Table command was still running. I've seen these long-running DDL commands get started and just stick around until they finish, even if you killed the sqlplus session. Just a guess. Hope this morning brings a better day to you. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 04, 2001 5:25 PM To: Multiple recipients of list ORACLE-L Hi All, It is one of those days. Anyway Oracle 8.0.5 / Win 4.0. I am trying to shutdown instance in Normal mode but just hangs. I am able to shutdown abort and restart. Heck I even rebooted server in case files were locked. There are no errors in alert log Any ideas why I cannot shutdown in NORMAL or IMMEDIATE. No other users/sessions on the system. I have done this many times but not working today. I need to do this because I want to do an offline backup. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Write to file in XML format
Hi All, I wanting to read data from table(s) and then create an output file in XML format. I have never done it so does anyone have good examples or point me to some good resources. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Dropping table is not completing
Hi All, I am trying to drop a table. It is not locked and has very few records in it. I am trying to drop it but it will not. What can I check to find out why it will not drop? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Dropping table is not completing
It just hangs and I am not sure what tables/views to query to find out what the problem is. Thanks Rick -Original Message- Sent: Thursday, October 04, 2001 2:30 PM To: Multiple recipients of list ORACLE-L What is the error? Or does it just hang? Have you looked at session wait events to see what is holding it up? If it was locked, it would echo right back saying acquire with nowait failed. Perhaps trying to drop it with a trace behind it. Or trace the session as you wait from sys. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, October 04, 2001 1:55 PM To: Multiple recipients of list ORACLE-L Hi All, I am trying to drop a table. It is not locked and has very few records in it. I am trying to drop it but it will not. What can I check to find out why it will not drop? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Database will not shutdown in Normal or IMMEDIATE mode
Hi All, It is one of those days. Anyway Oracle 8.0.5 / Win 4.0. I am trying to shutdown instance in Normal mode but just hangs. I am able to shutdown abort and restart. Heck I even rebooted server in case files were locked. There are no errors in alert log Any ideas why I cannot shutdown in NORMAL or IMMEDIATE. No other users/sessions on the system. I have done this many times but not working today. I need to do this because I want to do an offline backup. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Migration v. Upgrade
Migrating is the process of transforming one database version to a later database version. Oracle 7 to Oracle 8 is migration. Upgrading is the process of transforming one database release to another database release of the same database version. Oracle 8.0.1 to 8.0.5 is upgrading database. HTH Rick -Original Message- Sent: Tuesday, October 02, 2001 1:20 PM To: Multiple recipients of list ORACLE-L Erik - I believe the upgrade / recovery manual will have the more concise definition you seek. Look for the Oracle version you are on and the one you want to move to and it will tell you whether you need to upgrade or migrate. With an upgrade you usually just bring up the new version and run some procedures to modify your system tables. With migrate it is much more involved. To put things concisely: upgrade - good migrate - bad upgrade - minor, easy. migrate - hard, difficult, headaches. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, October 02, 2001 11:00 AM To: Multiple recipients of list ORACLE-L I cannot find a definitive definition of these two terms on metalink. Can these terms be used interchangeably or is there a difference? Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle - SQL Server
Title: Oracle -> SQL Server I have not used it but Oracle Transparent Gateway can query against a SQL Server database. This is a very expensive product. Rick -Original Message-From: Christopher Spence [mailto:[EMAIL PROTECTED]]Sent: Tuesday, October 02, 2001 2:41 PMTo: Multiple recipients of list ORACLE-LSubject: Oracle - SQL Server I know there is a way in SQL server to query oracle tables, is there a way to do the reverse? I am looking at trying to create access from an Oracle DB to SQL Server data until we convert the SQL server database. Anyone have any ideas on this, I searched technet (ms/oracle) and metalink, as well as some search engines without luck. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863
RE: Repost: Nested loop in PL/SQL
Part of the code must be missing because plan_id cursor is being
open/fetched but NOTHING done with it.
Rick
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 02, 2001 2:29 PM
To: Multiple recipients of list ORACLE-L
The reasons this doesn't work are fairly numerous. What is it you're
trying to accomplish here? Perhaps if you could explain your requirement,
somebody could help you out. I'm quite surprised you were able to get a
version of this to compile and run even if you did use a 'FOR' loop. Is it
possible we're not seeing all the code?
David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002
Feng, Jun
jfeng@verisig To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
n.com cc:
Sent by: Subject: Repost: Nested loop in
PL/SQL
[EMAIL PROTECTED]
om
10/02/2001
10:05 AM
Please respond
to ORACLE-L
Sorry, I meant to say doesn't work.
-Original Message-
Sent: Monday, October 01, 2001 6:50 PM
To: Multiple recipients of list ORACLE-L
Could anyone tell me why following nested loop does work? It stopped after
inner loop finished. I tried for loop, it worked ok.
Thanks,
Jun
declare v_plan_id number;
v_channel_id pricing_plan.channel_id%TYPE;
v_row pricing_plan%ROWTYPE;
cursor plan_id is
select distinct pricing_Plan_id from pricing_plan
where CHANNEL_ID = 'GROUP19'
and promotion_cd = 'INFO'
and trunc(end_date) = trunc(sysdate)
and p_mode = 'LIVE'
group by PRICING_PLAN_ID;
cursor channel_id is
select distinct channel_id from pricing_plan
where CHANNEL_ID not in ('GROUP1','GROUP2', 'GROUP3')
and transaction_type = 'REGISTRATION'
order by channel_id;
begin
open plan_id;
open channel_id;
loop
fetch plan_id into v_plan_id;
exit when plan_id%NOTFOUND;
loop
fetch channel_id into v_channel_id;
exit when channel_id%NOTFOUND;
select * into v_row
from pricing_plan
where PRICING_PLAN_ID = 1;
dbms_output.put_line(v_row.price_cd);
end loop;
end loop;
close plan_id;
close channel_id;
end;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Feng, Jun
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Feng, Jun
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cale, Rick T (Richard)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: changing the internal password
You can use the orapwd utility to recreate the password file. Rick -Original Message- Sent: Tuesday, September 25, 2001 6:45 AM To: Multiple recipients of list ORACLE-L Hello, I am trying to log in the sqlplus (from command line) as the internal user but I can´t remember its password. Is there any way to change it? TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partition
In 8.0.x it will NOT do the update. I think in 8i it will but not sure. Rick -Original Message-From: Maya Kenner [mailto:[EMAIL PROTECTED]]Sent: Monday, September 24, 2001 10:50 AMTo: Multiple recipients of list ORACLE-LSubject: partition Hello, I have a partitioned table by range on a column like STATUS , where STATUS can be either say YES or NO . i do my partitionning on this STATUS column. When i do insert in this table, data are correctly inserted in the right partition BUT if I want to update the STATUS field, it returns me an error I found ambiguous: "ORA-14402: updating partition key column would cause a partition change" The "would" is confusing , does this mean it won't do it anyhow or with some special keyword would move it to the other partition. Thanks, Maya
RE: ORA-04031
According to MetaLink Error: ORA 4031 Text: unable to allocate %s bytes of shared memory (%s,%s,%s) --- Cause: More shared memory is needed than was allocated in the shared pool. Action: Either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the init.ora parameter shared_pool_size. Rick -Original Message- Sent: Monday, September 24, 2001 11:35 AM To: Multiple recipients of list ORACLE-L Friends : I am using 9i and receiving : ORA-04031: unable to allocate 8704 bytes of shared memory (large pool,unknown object,hash-join subh,kllcqas:kllsltba) I have 2gb of memory In init.ora i have large values for pool parameters ... What is happening ? Any idea ? The db_cache_size is around 932 mb shared_pool is around 369 mb Regards Eriovaldo _ Chegou o novo MSN Explorer. Instale já. É gratuito! http://explorer.msn.com.br -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eca Eca INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to find the current instance name?
You probably want to do the following. v$database.name is the database name not the instance name. Although most name the same but not required. SELECT instance FROM v$thread; Rick -Original Message- Sent: Thursday, September 20, 2001 6:55 AM To: Multiple recipients of list ORACLE-L List, How to find the current instance name for the session?. From svrmgrl, i can using SELECT name FROM v$database. What is the equivalant from the ordinary user? Regards Nirmal. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nirmal Kumar Muthu Kumaran INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to find the current instance name?
You can if you grant select privs. -Original Message- Sent: Thursday, September 20, 2001 8:50 AM To: Multiple recipients of list ORACLE-L i cann't access V$ views as an ordinary user, rt? -Original Message- From: Cale, Rick T (Richard) [SMTP:[EMAIL PROTECTED]] Sent: Thursday, September 20, 2001 3:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to find the current instance name? You probably want to do the following. v$database.name is the database name not the instance name. Although most name the same but not required. SELECT instance FROM v$thread; Rick -Original Message- Sent: Thursday, September 20, 2001 6:55 AM To: Multiple recipients of list ORACLE-L List, How to find the current instance name for the session?. From svrmgrl, i can using SELECT name FROM v$database. What is the equivalant from the ordinary user? Regards Nirmal. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nirmal Kumar Muthu Kumaran INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nirmal Kumar Muthu Kumaran INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: inspdver... equivalent in 8i and above...
From MetaLink To explain the disappearance of the inspdver utility from theOracle8i. The inspdver utility is not supplied with Oracle8i. Previous versions of Oracle supplied the 'inspdver' utility in the$ORACLE_HOME/orainst directory. It relied on information maintainedby the 'orainst' utility to display information about which Oraclecomponents were installed in a given $ORACLE_HOME directory. With Oracle8i, the Installer is completely new and the 'inspdver' utility is no longer provided. However, information about installedproducts can be obtained by running the Oracle Universal Installer(runInstaller) and clicking on the 'Installed Products' button locatedon the opening screen. Rick -Original Message-From: Anjan Thakuria [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 20, 2001 12:45 PMTo: Multiple recipients of list ORACLE-LSubject: inspdver... equivalent in 8i and above...Listers, I am hoping someone knows the equivalent of inspdver in 8i and above. Can you please let me know. thanks Anjan
RE: sizeiof table!!!
Does anyone have a script that gives actual space used instead of size of
extents allocated?
Thanks
Rick
-Original Message-
Sent: Wednesday, September 19, 2001 10:05 AM
To: Multiple recipients of list ORACLE-L
Select sum(bytes) / 1048576 Size in Mb
From dba_extents
Where owner = upper('owner')
and segment_name = upper('tablename');
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, September 18, 2001 1:05 PM
To: Multiple recipients of list ORACLE-L
Hi gurus,
I need get the size in bytes of any table!!!
thanks!!!
@lex
Lic. Alexander Ordóñez Arroyo
Caja Costarricense del Seguro Social
Soporte Técnico - División de Informática
Telefono: 295-2004, San José, Costa Rica
[EMAIL PROTECTED]Icq# 30173325
The true is out there in WWW
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alexander Ordonez
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cale, Rick T (Richard)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Dump Oracle Tables To ASCII/Comma Delimited File
!! Please do not post Off Topic to this List !!Go to http://www.cybcon.com/~jkstill/util/util_master.html and download utility. It works well for this. Rick -Original Message- Sent: Thursday, September 13, 2001 9:20 AM To: Multiple recipients of list ORACLE-L Hi everybody, Can anybody tell me the command/tool in oracle that can dump Oracle Tables To ASCII/Comma Delimited File. Regds deepender gupta -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
What is maximum length of sql statement sent to Oracle server?
Hi All What is maximum length of sql statement sent to Oracle Server 8.0.5? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sad News - Jets crashed into World Trade Center
To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: STOP THE PRESS!!
of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: STOP THE PRESS!!
). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: query for top customer
SELECT customer,sales FROM (SELECT customer,sales FROM table_a ORDER BY sales DESC) WHERE rownum 4; Must be running at least 8i for this to work Rick -Original Message- Sent: Monday, September 10, 2001 9:30 AM To: Multiple recipients of list ORACLE-L how to write a query to find top 3 customer based on their sales . eg. TABLE A customer sales A100 100 A101 200 A102 105 A103109 A104108 RESULTS should be.. A101 200 A103109 A104108 Thanks in advance Brajesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Oracle DBA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PK Violation on insert
I have not done it but you should be able to capture records into an exception table. 1. Create exception table create table exceptions(row_id rowid, owner varchar2(30), table_name varchar2(30), constraint varchar2(30)); The script UTLEXCPT.SQL located in home/rdbms/admin directory will create this table for you. 2. Capture records in violation into your created exceptions table ALTER TABLE.EXCEPTIONS INTO OWNER.EXCEPTIONS Rick -Original Message- Sent: Friday, September 07, 2001 8:41 AM To: Multiple recipients of list ORACLE-L Good Morning, I am selecting information from 3 tables and inserting it into a new table. My compound primary key is composed of: DODAAC VARCHAR2(6) NSN VARCHAR2(13) CONTRACTVARCHAR2(14) The results BEFORE I put the PK on the INSERT resulted in about 650,000 records. After I put the PK in I am getting: ORA-1: unique constraint (SHOPPINGSA.SCAN_CONTRACT_PK) violated How do I find the bad data ??? TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Alter tablespace add datafile
Yes I am also, however the replies are not very helpful. -Original Message- Sent: Thursday, September 06, 2001 8:36 AM To: Multiple recipients of list ORACLE-L You are not the only one. I was wondering who is this guy and out of nowhere he is answering all the questions (I didn't read them). :) [EMAIL PROTECTED] 09/06/01 02:18AM is it just me, or is anybody else getting blank replies (exluding the sig) from Arich Henneman? -Original Message- [mailto:X] Sent: Thursday, 6 September 2001 2:37 PM To: X -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arich Henneman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Where are temp segments created?
Hi DBAs, I have a user Test who has a default tablespace trf_data and temporary tablespace temporary. When trying to create a table I got unable to extend temp segment in tablespace trf_data. My question is if I have temporary tablespace set up as temporary why is Oracle creating temp segments in trf_data? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ms acces
Supposely Oracle Migration Workbench will do this. You can get it from OTN. See http://technet.oracle.com/software/tech/migration/workbench/content.html There is also a tool with sql server client DTS that does a great job of this. Rick -Original Message- Sent: Thursday, September 06, 2001 3:59 PM To: Multiple recipients of list ORACLE-L is there any tool that imports directly form ms acces to oracle? with out having to export ms acces files to flat text files and then load them in to oracle but some sort of direct importation? cheers The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Please do not re-send by any reazon in any way or form any of the informatino here contained. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: agc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: create sequence in PL/SQL anonymous block - needs to be in se
I do not know the history behind this thread but wouldn't this solution only work in a single user system? I can see problems in a mult-user system. Rick -Original Message- Sent: Wednesday, September 05, 2001 9:27 AM To: Multiple recipients of list ORACLE-L se like Connor said, why use a sequence? you have a perfectly good sequence number in the loop index. why waste the time of creating and dropping a sequence? change your proc to: SQL declare 2 i pls_integer ; 3 begin 5 for i in 1..1000 6 loop 7 insert into t (n) 8 values 9 (i) ; === the only thing I changed. 10 end loop ; 11 commit ; 13 end ; 14 / Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, September 05, 2001 5:23 AM To: Multiple recipients of list ORACLE-L separa I'm not sure if what you've posted is just an example, but why not just do insert into t select rowum from sys.source$ where rownum 1000; It's a lot quicker and easier hth connor --- Jacques Kilchoer [EMAIL PROTECTED] wrote: Using Oracle 8.1.6 on Windows 2000 I try to use EXECUTE IMMEDIATE to create a sequence in an anonyms PL/SQL block, and then immediately use it in an INSERT statement. The INSERT statement fails saying sequence does not exist. However, if I create the sequence in a separate PL/SQL anonymous block immediately before the anonymous PL/SQL block containing the insert, there is no error. A DROP SEQUENCE that follows the insert is successful. Any suggestions? SQL -- Creating sequence in same anonymous block: SQL -- Insert statement doesn't recognize the SQL -- sequence name SQL declare 2 i pls_integer ; 3 begin 4 execute immediate 'create sequence temporary_sequence_s' ; 5 for i in 1..1000 6 loop 7 insert into t (n) 8 values 9 (temporary_sequence_s.nextval) ; 10 end loop ; 11 commit ; 12 execute immediate 'drop sequence temporary_sequence_s' ; 13 end ; 14 / (temporary_sequence_s.nextval) ; * ERREUR à la ligne 9 : ORA-06550: Ligne 9, colonne 9 : PLS-00201: l'identificateur 'TEMPORARY_SEQUENCE_S.NEXTVAL' doit être déclaré ORA-06550: Ligne 7, colonne 6 : PL/SQL: SQL Statement ignored SQL -- When creating the sequence in a separate block, SQL -- I see no error message SQL begin 2 execute immediate 'create sequence temporary_sequence_s' ; 3 end ; 4 / Procédure PL/SQL terminée avec succès. SQL declare 2 i pls_integer ; 3 begin 4 for i in 1..1000 5 loop 6 insert into t (n) 7 values 8 (temporary_sequence_s.nextval) ; 9 end loop ; 10 commit ; 11 execute immediate 'drop sequence temporary_sequence_s' ; 12 end ; 13 / Procédure PL/SQL terminée avec succès. SQL -- please note that 'execute immediate drop sequence' SQL -- was successful SQL select * from user_sequences ; aucune ligne sélectionnée = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California
OT: Reverse engineer access database
Hi DBAs, Sorry for the OT post but can someone tell me how I can reverse engineer an access database to produce the sql code that will run in ACCESS or point me to a web site/user group/list server that may be able to help. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Access to Oracle
Hi All, What is the best way to load Access tables into Oracle? I can do Oracle to Access Ok but not vice-versa. I could dump it out into csv and use sql*loader but hoping more for a more automated way. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Column Name
If you have 8i you can add new column name,populate with old column data,drop old column. Rick -Original Message- Sent: Tuesday, September 04, 2001 2:38 PM To: Multiple recipients of list ORACLE-L List, Is there any way to rename a column without changing the position of column? Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with DBMS_SQL
Roles are disabled in procedures so that user must have CREATE TABLE
priviledge explicitly granted to them but
I think in this case the user can create tables in their own schema.
Rick
-Original Message-
Sent: Friday, August 31, 2001 8:41 AM
To: Multiple recipients of list ORACLE-L
Hi,
I guess so - the user has RESOURCE role granted and I can create table
manually, no problem.
Procedure and table sit both in the user schema
Cheers,
Dimitri
-Original Message-
Sent: Friday, August 31, 2001 3:27 PM
To: Multiple recipients of list ORACLE-L
Daft question but do you have the priviliges to create tables? Silly I
know...
hit any user to continue
__
Kevin Thomas
Technical Analyst
Deregulation Services
Calanais Ltd.
(2nd Floor East - Weirs Building)
Tel: 0141 568 2377
Fax: 0141 568 2366
http://www.calanais.com
-Original Message-
Sent: 31 August 2001 13:01
To: Multiple recipients of list ORACLE-L
Dear All
Every time when I try to create table with the DBMS_SQL I get error
---
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_SYS_SQL, line 782
ORA-06512: at SYS.DBMS_SQL, line 32
ORA-06512: at DL.SP_CR, line 6
ORA-06512: at line 2
---
The procedure code is trivial and I call it as
begin
sp_cr('CREATE TABLE XTTBL(a NUMBER)');
end;
/
CREATE OR REPLACE procedure SP_CR(pvi_str VARCHAR2) AS
cid INTEGER;
err_code INTEGER;
begin
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, pvi_str, DBMS_SQL.native);
err_code:=DBMS_SQL.EXECUTE(cid);
DBMS_SQL.CLOSE_CURSOR(cid);
END;
/
What might be wrong here?
Yes, I can try native SQL (I'm running 8i) but code has to be portable to
the 8.05
Cheers,
Dimitri
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Dmitriy M. Labutin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas, Kevin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Dmitriy M. Labutin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cale, Rick T (Richard)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: 9i NT
from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Export Problem
From MetaLink Error: EXP 41 Text: Export done in server's %s, different from user's character set %s --- Cause: The specified NLS_LANG/NLS_NCHAR parameter is incompatible with the value specified for the server in props$. Action: Change the client's value for NLS_LANG/NLS_NCHAR to server's value and retry the operation. Rick -Original Message- Sent: Friday, August 31, 2001 12:23 PM To: Multiple recipients of list ORACLE-L Hi List, I have problem with export, when I try to run EXP got this error msg: Any Idea ?? Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production EXP-00041: Export done in server's US7ASCII, different from user's character set WE8ISO8859P1 EXP-0: Export terminated unsuccessfully -- Finished at: Thu 08/30/2001 5:07p Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i NT
To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
PK/Fk question
Hi DBAs, Table1 PK is on columns col1,col2,col3 I want to create a FK on table2 to table1 for col1. What is the alternative to ALTER TABLE table2 ADD CONSTRAINT fk_table2 FOREIGN KEY (col1) REFERENCES table1(col1); I always get ORA-2270: No matching unique or primary key for this column-list. Every value for table2.col1 is in the table table1.col1. I want to enforce this at the database server level. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OCP Test timeframe
Hi Traci, There is no time frame from start to finish and as of yet Oracle has no plans in retiring the Oracle 8i track. Rick -Original Message- Sent: Wednesday, August 22, 2001 10:22 AM To: Multiple recipients of list ORACLE-L Hello, does anyone know if there is a time limit on exams taken for the OCP DBA 8i track? More specifically, from when the first test is taken, do you have to complete the others in a specified amount of time, say within 1 year? Thanks Traci -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Traci Rebman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Delete Alertlog on NT
No you can delete it anytime and Oracle will recreate it. Rick -Original Message- Sent: Wednesday, August 22, 2001 1:27 PM To: Multiple recipients of list ORACLE-L Is there any problem deleting the alert log on NT while the instance is up and running? V7.3.4 - 8.1.6 Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Spool 32 Error
See http://www.computerhope.com/issues/ch000373.htm http://www.completeclinic.com/sn_spool32.html Rick -Original Message- Sent: Tuesday, August 21, 2001 9:31 AM To: Multiple recipients of list ORACLE-L We recently have Spool 32 Error message appear on almost all of computers when user request printing. Could anyone share the experience why and how to solve this problem. Thx. Warmest regards, Le Quang Huy Manager -- Information Technology Chinfon Manulife Insurance Company Limited Diamond Plaza, 12fl, 34 Le Duan, District 1, Ho Chi Minh city, Vietnam Phone: (848) 825 7722 Ext: 2999 --- Fax: (848) 825 7718 --- Mobile: (848) == This message is confidential and may also be privileged. If you are not the intended recipient, please notify me by return e-mail and delete this message from your system. If you are not the intended recipient, any use by you of this message is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Two (probably simple) questions, function based index and table p
What version of Oracle are you using? Function based indexes only work in EE. Rick -Original Message- Sent: Friday, August 17, 2001 6:46 AM To: Multiple recipients of list ORACLE-L p Hello folks! I need to create a function based index, but when I do this, Oracle gives me the message ORA-00439, function not installed, so I have tried and tried to figure out how to install this function, but I have failed, therefore this e-mail. I have looked in ORACLE_HOME/rdbms/admin and read the manual but without sucess. I have the same problem with table partitioning, where I have not figured out how to install that option either. Thanks /Jonas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonas A Wetterberg INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Export
In NT/2000 EXP username/password file=... tables=... query=where condition log=exp.log That is 3 double quotes with NO spaces surrounding them UNIX exp demo/demo query=\where lname like \'FRAN%\'\ tables=emp file=exp.dmp log=exp.log Rick -Original Message- Sent: Friday, August 17, 2001 7:07 AM To: Multiple recipients of list ORACLE-L Hi Can you please tell me if there is a way of exporting select rows from a given table in 8.1.6.2??? Thanks in advance Rgds Fawzia -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
No TNSListner service after intall 8.1.7 on Win 2k
Hi All, I did a typical installation of Oracle 8.1.7 on Win 2k. It appears to have installed Ok. I can use srvmgrl - create database - etc. However when I try to use worksheet I get no listner. I should not need one from the server. Anyway the install did not create service to start the listener. All other installs I have done on NT always created a tnslistner service. This in my first install on Win 2k. Anyone have any ideas on why service did not get created and how to correct? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Can a SYSTEM tablespace be locally managed.
Hi DBAs, I have ready many conflicting notes on MetaLink whether or not a SYSTEM tablespace can be locally managed. Most of the article state definitely not in 8.1.6 and less. While others say Yes and No in 8.1.7 Anyone know for sure thru actual use. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
What file(s) does Oracle read in deciding what has been installed.
Hi All, I am having great difficulty in getting Oracle 8.1.7 installed on Win 2k. I have completely removed Oracle, i.e., directory,registry entries, program groups and services. When I try to re-install it again Installer says 167 products installed when in fact there are none. Where is Oracle reading this from so I can delete this d__n file. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: No TNSListner service after intall 8.1.7 on Win 2k
Title: RE: No TNSListner service after intall 8.1.7 on Win 2k Ok, I am getting a little closer to figuring out what is going on. I did mis-state the type of install I did first. It was minimal installation without the starter database. This did NOT create a listener service. However it clearly states it would install networking services. I did a 2nd TYPICAL installand it did create a tnslistener service. As soon as I get time/ambition/curiousity I will do a 3rd install WITH starter database to see it that will create a listener service. I really do not want a starter database installed to get a tnslistener service but I could always delete it if I do not want it(Kldugy)!!! Rick -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, August 16, 2001 1:28 PMTo: Multiple recipients of list ORACLE-LSubject: RE: No TNSListner service after intall 8.1.7 on Win 2k Hi Rick, I ran into this as well. I figured I didn't need a listener on my w2k box because everything was going to be local. However I couldn't connect via sqlplus gui without a listener (no listener error - maybe because I am specifying the sid). I guess it's just another difference between Oracle on windows and Oracle on Unix. If I remember right (it's been s long) when you connect in Unix with the $ORACLE_SID as part of your env, you can do so without @. If you do it with the @, it looks for a listener. When connecting via a windows gui, you are specifying the sid. I'm guessing it's doing something similar to the @ in the background. The cmd window connects without the @ because it's in the environment. By the way, how do you display an environment variable in Windows? I thought it was echo %ORACLE_SID%, but that doesn't work. Anyone? Again I may be pre-coffee. Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Cale, Rick T (Richard) [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 16, 2001 12:36 PM To: Multiple recipients of list ORACLE-L Subject: No TNSListner service after intall 8.1.7 on Win 2k Hi All, I did a typical installation of Oracle 8.1.7 on Win 2k. It appears to have installed Ok. I can use srvmgrl - create database - etc. However when I try to use worksheet I get no listner. I should not need one from the server. Anyway the install did not create service to start the listener. All other installs I have done on NT always created a tnslistner service. This in my first install on Win 2k. Anyone have any ideas on why service did not get created and how to correct? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Allow only certain ports for sql*net traffic
Hi DBAs, I asking on behalf of another user so bear with me. They are running Oracle 7.3.x and they want to restrict certain ports for sql*net traffic. Currently when a client makes a sql*net connect it spawns another process another port for sql*net traffic. Is there some oracle configuration file to handle this? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: No TNSListner service after install 8.1.7 on Win 2k
Thanks for the input. I am going to have a database but I did not want the starter database I want to create my own. A tnslistener service should get created regardless of whether I opted for the starter database or not. Thanks Rick -Original Message- Sent: Thursday, August 16, 2001 4:13 PM To: Multiple recipients of list ORACLE-L I'll give this a whack though I suspect that there are people who could do a better job. The Windows registry is the conceptual equivalent of the UNIX environmental variables. ORACLE_SID, ORACLE_HOME, etc can be found under HK_LOCAL_MACHINE\Software\Oracle. You can change these using REGEDIT; however, make a backup of the registry before you change anything (REGEDIT has that capability). If you make enough of a hash of the registry you can end up with an un-bootable machine, so be careful. ORADIM can create the services for LISTENER and the database. It can also create the database. I'm not sure why you would want a listener on a machine where you don't have a database? Windows Services are a pain to remove. You have to find them in the registry and delete the entry. It's easier to go the Services under Windows Control Panel and set them for Manual startup. They add a little bit of bulk to the registry but consume no other resources. Anyone have anything else to add or correct? Cale, Rick T (Richard) To: Multiple recipients of list ORACLE-L RICHARD.T.CALE[EMAIL PROTECTED] @saic.com cc: Sent by: Subject: RE: No TNSListner service after [EMAIL PROTECTED]intall 8.1.7 on Win 2k m 08/16/2001 03:13 PM Please respond to ORACLE-L Ok, I am getting a little closer to figuring out what is going on. I did mis-state the type of install I did first. It was minimal installation without the starter database. This did NOT create a listener service. However it clearly states it would install networking services. I did a 2nd TYPICAL install and it did create a tnslistener service. As soon as I get time/ambition/curiousity I will do a 3rd install WITH starter database to see it that will create a listener service. I really do not want a starter database installed to get a tnslistener service but I could always delete it if I do not want it(Kldugy)!!! Rick -Original Message- Sent: Thursday, August 16, 2001 1:28 PM To: Multiple recipients of list ORACLE-L Hi Rick, I ran into this as well. I figured I didn't need a listener on my w2k box because everything was going to be local. However I couldn't connect via sqlplus gui without a listener (no listener error - maybe because I am specifying the sid). I guess it's just another difference between Oracle on windows and Oracle on Unix. If I remember right (it's been s long) when you connect in Unix with the $ORACLE_SID as part of your env, you can do so without @. If you do it with the @, it looks for a listener. When connecting via a windows gui, you are specifying the sid. I'm guessing it's doing something similar to the @ in the background. The cmd window connects without the @ because it's in the environment. By the way, how do you display an environment variable in Windows? I thought it was echo %ORACLE_SID%, but that doesn't work. Anyone? Again I may be pre-coffee. Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Cale, Rick T (Richard) [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 16, 2001 12:36 PM To: Multiple recipients of list ORACLE-L Subject: No TNSListner service after intall 8.1.7 on Win 2k Hi All, I did a typical installation of Oracle 8.1.7 on Win 2k. It appears to have installed Ok. I can use srvmgrl - create database - etc. However when I try to use worksheet I get no listner. I should not need one from the server. Anyway the install did not create service to start the listener. All other installs I have done on NT always created a tnslistner service. This in my first install on Win 2k. Anyone have any ideas on why service did not get created and how to correct? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include
Size of Oracle binaries of Oracle 8i vs 8.0 on Windows
Hi, I have searched the Oracle 8i EE getting started for how much larger the Oracle 8i binaries are bigger than version 8. It said it was 3 times larger than Oracle 7 but I am curious how much larger than Oracle8.0.x Also, since MetaLink is down once again does anyone know when Oracle 8.0 will be desupported. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ON UPDATE CASCADE?
I think you will have write a on update, on insert trigger to accomplish this. Rick -Original Message-From: Bunyamin K. Karadeniz [mailto:[EMAIL PROTECTED]]Sent: Monday, August 13, 2001 11:00 AMTo: Multiple recipients of list ORACLE-LSubject: ON UPDATE CASCADE? I want to update referencing tables when the master table is updated. Does it have an easy wayas in on delete cascade or do I need to Write code? Bunyamin
CLOBs storage vs Varchar storage
Hi, I am resending this question as I cannot find the answer CLOBs vs VARCHAR. If a varchar datatype is not completely used it will not allocate storage for what is was defined. Does a CLOB data type use entire storage if not completely used? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL length overflow prob
This is a restriction to using dbms_output that maximum length is 255. From MetaLink This is a restriction of the package DBMS_OUTPUT(). The restriction and resulting error messages are documented in the file dbmsotpt.sql. This file can be found in the directory $ORACLE_HOME/rdbms/admin on UNIX platforms. Rick -Original Message- Sent: Wednesday, August 08, 2001 9:50 AM To: Multiple recipients of list ORACLE-L HI, I am encountering the following problem in my pl/sql programme.Please let me know how to counter ths prob: DECLARE * ERROR at line 1: ORA-2: ORU-10028: line length overflow, limit of 255 bytes per line ORA-06512: at SYS.DBMS_OUTPUT, line 99 ORA-06512: at SYS.DBMS_OUTPUT, line 65 ORA-06512: at line 30 Thanks in advance, Uma -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, UmaSankara S (CAP) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Convert LONG to VARCHAR2
Thanks for the reply but if that is all you have to offer please keep it to yourself. -Original Message- Sent: Thursday, August 02, 2001 5:14 PM To: Multiple recipients of list ORACLE-L By loosing a gig or two of information. -Original Message- From: Cale, Rick T (Richard) [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 02, 2001 3:04 PM To: Multiple recipients of list ORACLE-L Subject: Convert LONG to VARCHAR2 Hi All, I know it has been discussed before but can anyone tell me how to convert a LONG datatype to VARCHAR2? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Convert LONG to VARCHAR2
Hi All, I know it has been discussed before but can anyone tell me how to convert a LONG datatype to VARCHAR2? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: grants to schema query
I think you need to something like CREATE ROLE upd_privs; GRANT UPDATE ON table1 TO upd_privs; GRANT UPDATE ON tableN TO upd_privs; GRANT upd_privs TO username; Rick -Original Message- Sent: Thursday, August 02, 2001 11:01 AM To: Multiple recipients of list ORACLE-L Hope there is not an obvious answer to this. I want to grant UPDATE priv to all objects in a schema to a user. Do I need to grant to each object or can I somehow wildcard all the objects?. What are my option(s)?. I'd appreciate example grant statements! Sean :) Rookie Data Base Administrator Oracle 7.3.3, 8.0.5, 8.1.7 - NT, W2K [0%] OCP Oracle8i DBA [0%] OCP Oracle9i DBA Organon (Ireland) Ltd. E-mail: [EMAIL PROTECTED] [subscribed: Digest Mode] Visit: http://groups.yahoo.com/group/Oracle-OCP-DBA Nobody loves me but my mother... and she could be jivin' too. - BB King -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: upgrade
What type errors are you getting? That may help in determing if you need to run some Oracle scripts. Rick -Original Message- Sent: Wednesday, August 01, 2001 9:51 AM To: Multiple recipients of list ORACLE-L Hi, I have just upgraded 8.0.5.2 to 8.1.6 and some of my objects are invalid. I have tried to recompile them to no avail. I did the upgrade manually running the script :u0800050. Is there anything else I needed to run??? Rgds Fawzia -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: upgrade
to no avail. I did the upgrade manually running the script :u0800050. Is there anything else I needed to run??? Rgds Fawzia -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Version of personal oracle that runs on Window ME
Hi All, Does anyone know where I can get/download copy of Oracle that runs on Window ME I downloaded 8i for 98 but BSOD. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Rollback Segment needs recovery
Hi DBAs, Oracle 8.0.5/Windows NT4 I shutdown database normal and restarted ok. I tried to drop a rollback segment and getora-1545 rollback segment not available. When I select from dba_rollback_segs I get needs recovery. I want to drop rollback tablespace and all rollback segment then rebuild because I got datafile corruption error. I am in noarchive mode. Any ideas how to proceed? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rollback Segment needs recovery
I was able to do everything I needed by using undocumented/unsupported _offline_rollback_segments parameter. Rick -Original Message- Sent: Friday, July 27, 2001 1:37 PM To: Multiple recipients of list ORACLE-L Hi DBAs, Oracle 8.0.5/Windows NT4 I shutdown database normal and restarted ok. I tried to drop a rollback segment and getora-1545 rollback segment not available. When I select from dba_rollback_segs I get needs recovery. I want to drop rollback tablespace and all rollback segment then rebuild because I got datafile corruption error. I am in noarchive mode. Any ideas how to proceed? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Help
Title: Help Lots of ways CREATE TABLE ouruser AS SELECT * FROM myuser UNION ALL SELECT * FROM uruser; This will create a table ouruser with 10,000 records. Rick -Original Message-From: Deewaker G.V. [mailto:[EMAIL PROTECTED]]Sent: Thursday, July 26, 2001 7:47 AMTo: Multiple recipients of list ORACLE-LSubject: Help Hi DBA Gurus Suppose I have a Table myuser which has 5000 records and another table uruser also 5000 records, and the structure is same for the both... how do I put "MYUSER" AND 'URUSER" to a new table callled "OURUSER" Ples help me.. with warm regards, Deewaker G. V. Baazee.com India Pvt. Ltd. (: 4611323 Extn: 216 Fax : 4611324 Deewaker G.V..vcf
RE: deletion of data from a large table
I think this is happening because when you DELETE data the High Water Mark(HWM) is not lowered. Essentially you have not gained any freespace. If possible you could export remaining data truncate table then re-import. Correct me if I am wrong here. Rick -Original Message- Sent: Thursday, July 26, 2001 7:41 AM To: Multiple recipients of list ORACLE-L Hi All, I have deleted 3 lakhs records from a large table. But there is no effect on tablespace i.e. before I delete the data freespace in TS is 100MB , after deletion also it is showing 100MB. What could be the reason ? How to get the freespace after deleting the data ? Thanks rukmini -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rukmini Devi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sql/PLUS Question
You can do SELECT * FROM nls_database_parameters; Rick -Original Message- Sent: Thursday, July 26, 2001 10:06 AM To: Multiple recipients of list ORACLE-L Hi All, Just a short How to... Is there some way to find out what your session parameters are. I am aware of the show parameters; But an example is NLS_DATE_FORMAT, if you change it from the system default using alter session. And run show parameter It still remains with no value in the VALUE column of the output. Hope I made myself clear. Thanks In Advance Denham -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denham Eva INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Best way to upgrade database/move data
Hi DBAs, Currently I have NT server 4.0/Oracle 8.0.5 with about 400 million records/450 tables,indices,etc. We will be getting a new NT server with Oracle 8.1.7. We will increase block size from 2k to 8k What would be the best way to move data/users/sequences,etc from existing server to new server? Essentially replicate everything from old server to new server. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: strtSID.cmd security hole??
Under oracle_home\database -Original Message- Sent: Wednesday, July 25, 2001 5:07 PM To: Multiple recipients of list ORACLE-L Where is the strtSID.cmd file? I don;t see it anywhere under $ORACLE_HOME. -Original Message- From: Farnsworth, Dave [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, July 25, 2001 4:47 PM To: Multiple recipients of list ORACLE-L Subject: strtSID.cmd security hole?? I inherited an Oracle 7.3.4 database that nobody knew the internal password for. So I was doing some research on metalink and came across an article that mentioned the strtSID.cmd file would have the password. I was amazed to open up this file and see the unencrypted password for internal. I then check my 8.0.5 database and the same thing. Then I checked my 8.1.7 database and it was not there. Did this gaping security hole disappear in the 8i database? I sure hope so. Both the 7.3.4 and 8.0.5 have the remote_login_passwordfile init paramater set to SHARED, whereas my 8.1.7 is set to EXCLUSIVE. I don't know if this has something to do with it. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Input truncated
In your pl/sql script add a carriage return after / Rick -Original Message- Sent: Tuesday, July 24, 2001 10:01 AM To: Multiple recipients of list ORACLE-L I don't know why when I try to load error_p1 procedure it's printed Input truncated to 35 characters. SQL @error_p1 Procedure created. Input truncated to 35 characters No errors. Can anybody help me ? Thanks, Andrea -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Quaglio Andrea INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: get yesterdays date
I am not sure of exact syntax but just select sysdate -1 into variable from dual; Rick -Original Message- Sent: Monday, July 23, 2001 7:36 AM To: Multiple recipients of list ORACLE-L Anyone know how to get yesterdays date in PL/SQL? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally managed tablespaces
Not positive but I would think Oracle just ignores the NEXT parameter in locally managed tablespaces Rick -Original Message- Sent: Thursday, July 19, 2001 12:43 PM To: Multiple recipients of list ORACLE-L HPUX 11i 64 bit Oracle 8.1.7.1 32 bit I am creating a new database and decided to go with locally managed tablespaces. I was going to go with autoallocate because the best I can tell the only possible drawback with this is a little wasted space. However, I was under the impression that you could not specify a next in the storage clause of a table creation. Yet you can. So what happens if I have a next defined? Do they pretty much just throw that away or should I really not define it? Kimberly Smith Database Administrator IT Dept. - Fujitsu/GMD Phone: (503) 669-6050 Fax: (503) 669-5705 Email : [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Java
From MetaLink Do the following Set the CLASSPATH to ORACLE_HOME/javavm/lib/aurora.zip Then run the initjvm.sql script from ORACLE_HOME/javavm/install. Rick -Original Message- Sent: Tuesday, June 19, 2001 11:16 AM To: Multiple recipients of list ORACLE-L Hi Do you know what is meen: ORA-29547 Java system class not available: oracle/aurora/rdbms/Compiler Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alon Ben-Zvi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how to rename a database SID
Title: how to rename a database SID See Note Note:15390.1Subject:How to Determine and Change DB_NAME or ORACLE_SID on MetaLink Here it is below Rick Purpose This entry describes how to find and change the "db_name" for a database, or the ORACLE_SID for an instance, without recreating the database. SCOPE APPLICATION For DBAs requiring to either find or change the db_name or ORACLE_SID. RELATED DOCUMENTS [NOTE:1018634.102] AFTER RENAMING THE DATABASE SELECT FROM DUAL RETURNS OLD DATABASE NAME [NOTE:9560.1] ALTER TABLESPACE/DATABASE TO RENAME FILES To find the current DB_NAME and ORACLE_SID:=== Query the views v$database and v$thread. V$DATABASE gives DB_NAME V$THREAD gives ORACLE_SID If ORACLE_SID = DB_SID and db_name = DBNAME: To find the current value of ORACLE_SID: SVRMGR select instance from v$thread; INSTANCE DB_SID To find the current value of DB_NAME: SVRMGR select name from v$database; NAME - DBNAME Modifying a database to run under a new ORACLE_SID: === 1. Shutdown the instance The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT. 2. Backup all control, redo, and data files. 3. Go through the .profile, .cshrc, .login, oratab, tnsnames.ora, (for SQL*Net version 2), and redefine the ORACLE_SID environment variable to a new value. For example, search through disks and do a grep ORACLE_SID * 4. Change locations to the "dbs" directory % cd $ORACLE_HOME/dbs and rename the following files: o initsid.ora (or use pfile to point to the init file.) o control file(s). This is optional if you do not rename any of the controlfiles, and the control_files parameter is used. The "control_files" parameter is set in the "initSID.ora" file or in a file it references with the ifile parameter. Make sure that the control_file parameter does not point to old file names, if they have been renamed. o "crdbsid.sql" "crdb2sid.sql", This is optional. These are only used at database creation. 5. To rename the database files and redo log files, follow the instructions in [NOTE:9560.1]. 6. Change the ORACLE_SID environment variable to the new value. 7. Check in the "$ORACLE_HOME/dbs" directory to see if the password file has been enabled. If enabled, the file "orapwOLD_SID" will exist and a new password file for the new SID must be created (renaming the old file will not work). If "orapwOLD_SID" does not exist, skip to step 8. To create a new password file, issue the following command as oracle owner: orapwd file=orapwNEWSID password=?? entries=number of users to be granted permission to start the database instance 8. Start up the database and verify that it works. Once this is done, shutdown the database and take a final backup of all control, redo, and data files. The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT. 9. When the instance is started, the control file is updated with the current ORACLE_SID. Changing the "db_name" for a Database: == 1. Login to Server Manager % svrmgrl SVRMGR connect internal 2. Type SVRMGR alter system switch logfile; to force a checkpoint. 3. Type SVRMGR alter database backup controlfile to trace resetlogs; This will create a trace file containing the "CREATE CONTROLFILE" command to recreate the controlfile in its current form. 4. Shutdown the database and exit SVRMGR SVRMGR shutdown SVRMGR exit The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT. 5. Change locations to the directory where the trace files are located. They are usually in the "$ORACLE_HOME/rdbms/log" directory. If "user_dump_dest" is set in the "initSID.ora" file, then go to the directory listed in the "user_dump_dest" variable. The trace file will have the form "ora_.trc with being a number. 6. Copy the contents of the trace file starting from the line with STARTUP NOMOUNT down to the end of the trace file and put it in a new file called something like "ccf.sql". 7. Edit the "ccf.sql" file FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS ... TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ... Change the word 'REUSE' to 'set' and the 'olddbname' to 'newdbname'. It is possible to recreate the controlfile using the syntax: CREATE CONTROLFILE REUSE set DATABASE "newdbname" RESETLOGS ... But this syntax will allow the existing controlfiles to be overwritten without giving an error. FROM: # Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal
RE: how to rename a database SID
You must also change the dbname parameter in the init.ora file -Original Message- Sent: Monday, June 18, 2001 2:18 PM To: Multiple recipients of list ORACLE-L just recreate database control file. On Mon, 18 Jun 2001, Andrey Bronfin wrote: HI ! How can i rename a database on NT / UNIX ? Thanks a lot in advance ! -- Lucy Lin Oracle DBA condenet.com 212-286-3852 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lucy Lin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: transfer of large datafile Oracle7.3.4.4 databases to 8.1.7.
, or if it is still a bug in 8.1.7.. I logged a TAR with Oracle, but haven't heard back from them yet. They asked me to do a database-to-database comparison in Change Manager, instead of doing a database-to-baseline or baseline-to-baseline comparison (which I have done, both report missing objects and tablespaces). We are considering what our options are at this point. Pre-creating all the objects and then importing user by user doesn't sound good to me. Likewise with the migrate utility, if the problem is with the rdbms engine, it won't work either. I could do a full import in rows=n mode I suppose, to see what would happen then. The error appears to be in the import code, however. Oracle no longer fixes bugs in Oracle 7.3.4., they will not fix this problem in the older version. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Data storage for Numeric data
Hi All, If I have fields defined as NUMBER(2,0) and NUMBER(7,0) how much storage does Oracle used if fields are NULL or the fields do not use entire width. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Surrogate keys
Hi All, Can someone explain pros/cons to using surrogate keys(i.e.,sequences) vs non-surrogate keys? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Data storage for numeric data
Hi All, If I have fields defined as NUMBER(2,0) and NUMBER(7,0) how much storage does Oracle used if fields are NULL or the fields do not use entire width. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle to Access indexes do not import
I have a lot of Oracle tables that I need to import into Access. I can move the data over fine with get external data. However I have to go in access and manually add primary key. Does anyone know how to make that automatically happen as I have a lot of tables to move to ACCESS. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: adding columns to system tables
Absolutely NOT!!! As soon as you do that you are completely unsupported by Oracle and probably everyone else. Rick -Original Message- Sent: Friday, May 18, 2001 12:26 PM To: Multiple recipients of list ORACLE-L Does anyone know if it is 'safe' to add additional columns to user_tab_columns? I am running 8.1.5. I would use the new columns to tell the application software which columns on any one table are user editable by a dynamically generated dialog form. Thanks, Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Schraiber INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB Link Between Prod and Test?
If all he needs is select he cannot corrupt the DB if the DBA does not grant unnecessary privileges. Folks use dblinks all the time. Rick -Original Message- Sent: Tuesday, May 15, 2001 2:26 PM To: Multiple recipients of list ORACLE-L One of our development DBAs suggested today to build a private database link between production and test database so that he can run some queries against both at the same time. I am a little concerned because he might make a mistake and accidentally corrupt the prod database. Anybody out there who is doing the same thing? Are there any other potential ramifications? TIA Dennis Meng Database Administrator Focal Communications 847-954-8328 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silver vs. Gold support
We still have Bronze support which you can no longer get but we would not even consider upgrading to Gold because their phone support is not very good at all. There are too many resources available where you can actually get a correct answer much sooner. Rick -Original Message- Sent: Thursday, May 10, 2001 2:39 PM To: Multiple recipients of list ORACLE-L Kim, I have not seen a reason to get GOLD support from Oracle. Unless you've got a boss who doubts your capabilities. Sounds like time to brush off the resume!! Dick Goulet Reply Separator Author: Kimberly Smith [EMAIL PROTECTED] Date: 5/10/2001 9:16 AM My manager is thinking of moving us up to Gold support. Does anyone out there have it and if so could you give me you general impression on having that over Silver. She says that at sites she used to work at it means having a dedicated resource on site. Their phone support for SEV1 calls is basically sucks unless you hit the day shift. Kimberly Smith Database Administrator EDS - Fujitsu/GMD Phone: (503) 669-6050 Fax: (503) 669-5705 Email : [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Actual size of database
Hi All, Oracle 8.0.5 NT 4 Does anyone have a script they would share that can calculate the ACTUAL size of a database(space_used) not what has been allocated? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Datafile needs media recovery
Hi All, Oracle 8 running in Archivelog One of my coworkers said they did the following: 1. Added a datafile to a tablespace 2. Took datafile offline and renamed at OS 3. ALTER DATABASE RENAME ... to new name 4. Attempted to bring datafile back online and get file needs media recovery. Tried to alter database recover automatic and other options but does not work. This is all the info I have does anyone have any suggestions on why it needs media recovery and how he can get it back online. We cannot drop the tablespace and rebuild. We could maybe export all objects in that tablespace then rebuild. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Datafile needs media recovery
Thanks to all replies Turns out they started recovery and it appear to be hung and they attempted to start another recovery while that was running. After stopping the recovery and reissuing it recovered. Thanks Rick -Original Message- Sent: Friday, April 20, 2001 3:31 PM To: Multiple recipients of list ORACLE-L Hi, I may be wrong but Oracle suggests that the file should be "COPIED" at OS level before renaming at Oracle level. Here I think is that Datafile is "MOVED" before renaming at Oracle level. This is what causing the difference .. I am afraid there is solution to this (Media recovery can't be completed).. Please go ahead with alternative method Or if you would like to wait for some other suggestion.. Any way I'll wait for your final steps and updates too ... HTH, Rajesh -Original Message- T (Richard) Sent: Friday, April 20, 2001 10:51 PM To: Multiple recipients of list ORACLE-L Hi All, Oracle 8 running in Archivelog One of my coworkers said they did the following: 1. Added a datafile to a tablespace 2. Took datafile offline and renamed at OS 3. ALTER DATABASE RENAME ... to new name 4. Attempted to bring datafile back online and get file needs media recovery. Tried to alter database recover automatic and other options but does not work. This is all the info I have does anyone have any suggestions on why it needs media recovery and how he can get it back online. We cannot drop the tablespace and rebuild. We could maybe export all objects in that tablespace then rebuild. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rajesh Dayal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Prompt and accept a value inside a procedure
Hi All, Is there a way to prompt a user and accept input from the user inside a procedure. I cannot get the prompt/accept to work inside a procedure. It works it sql*plus. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: tnsnames problem
Was worksheet and sqlplus installed from the same version of the IMS. It could be possible that one is an older version and may be looking for sqlnet 2.3.x. Just a guess. Rick -Original Message- Sent: Wednesday, April 18, 2001 3:40 PM To: Multiple recipients of list ORACLE-L When I open sqlworksheet I have no problem getting into the database but when I try getting into the db using sqlplus I get the following error: ERROR: ORA-12154: TNS:could not resolve service name Don't they both use the same tnsnames file? Thanks, Eric -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chesebro, Eric INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
