RE: possible to load a string with paragraphs?
one word ... CLOB field ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- David Boyd Sent: Tuesday, January 27, 2004 2:30 PM To: Multiple recipients of list ORACLE-L Hi List, I have a web application that allows users to type notes with paragraphs. Is it possiable to load the string with paragraphs into Oracle (not save the note as a file)? Later on the application has to display the same format for the note when the user queries that record on the web. Thanks for any inputs. _ Check out the coupons and bargains on MSN Offers! http://shopping.msn.com/softcontent/softcontent.aspx?scmId=1418 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
possible to load a string with paragraphs?
Hi List, I have a web application that allows users to type notes with paragraphs. Is it possiable to load the string with paragraphs into Oracle (not save the note as a file)? Later on the application has to display the same format for the note when the user queries that record on the web. Thanks for any inputs. _ Check out the coupons and bargains on MSN Offers! http://shopping.msn.com/softcontent/softcontent.aspx?scmId=1418 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
is this possible ?
i hv to write an sql query in which i hv to fetch all the tables frm the database having a column say EMPNO and where the value of this empno column is say 9 and this should be thru a single sql query is this possible ? Thanks and Regds, Rohan _ Contact brides grooms FREE! http://www.shaadi.com/ptnr.php?ptnr=hmltag Only on www.shaadi.com. Register now! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rohan Karanjawala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: is this possible ?
What are you trying to get? Column values or (TABLE,COLUMN) combination? You could probably use a function like this: create or replace function trickery(T varchar2,C varchar2, V number) return number deterministic as qry varchar2(2048); cnt number:=0; begin qry='select count(*) from '||T||' where '||C||'='||V; execute immediate qry into cnt; return(cnt); end; / The rest is simply using this function in a query against dba_tab_columns. On 01/23/2004 11:54:26 PM, Rohan Karanjawala wrote: i hv to write an sql query in which i hv to fetch all the tables frm the database having a column say EMPNO and where the value of this empno column is say 9 and this should be thru a single sql query is this possible ? Thanks and Regds, Rohan _ Contact brides grooms FREE! http://www.shaadi.com/ptnr.php?ptnr=hmltag Only on www.shaadi.com. Register now! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rohan Karanjawala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
is it possible to force different 'types' of index scans?
I know you can hint a fast full scan. I have run into cases lately where depending on circumstances Oracle will use an index, but use a sub-optimal type of index scan with dramatic differences in performances. This is on 9.2. Any hints for forcing an 'index range scan'. Anything stronger than a 'hint'. Something like 'do it because I said so'. This only happens occasionaly, so Id just like to know if it exists for future reference. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: is it possible to force different 'types' of index scans?
put in a between clause in where clause on appropriate columns for a range scan. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 8:14 AM To: Multiple recipients of list ORACLE-L I know you can hint a fast full scan. I have run into cases lately where depending on circumstances Oracle will use an index, but use a sub-optimal type of index scan with dramatic differences in performances. This is on 9.2. Any hints for forcing an 'index range scan'. Anything stronger than a 'hint'. Something like 'do it because I said so'. This only happens occasionaly, so Id just like to know if it exists for future reference. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: is it possible to force different 'types' of index scans?
You can have range scan with equality search (=) as well, if your index is non-unique and there is no unique constraint on column. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 3:24 PM put in a between clause in where clause on appropriate columns for a range scan. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 8:14 AM To: Multiple recipients of list ORACLE-L I know you can hint a fast full scan. I have run into cases lately where depending on circumstances Oracle will use an index, but use a sub-optimal type of index scan with dramatic differences in performances. This is on 9.2. Any hints for forcing an 'index range scan'. Anything stronger than a 'hint'. Something like 'do it because I said so'. This only happens occasionaly, so Id just like to know if it exists for future reference. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
anyone seen this? (possible controlfile corruption)
Hi. tar already opened - awaiting feedback. W2K Adv Svr Sp4 Oracle 9i R2 Std Ed 9.2.0.4 NTFS 5.0 filesystem Dell 2650, PERC3/Di controller, writeback caching enabled qa database, not a huge deal at the moment. I have a backup controlfile, but would rather not open resetlogs, as there is a remote standby database for this - but then again, that gets refreshed over the weekend anyways. I only found one similar posting on the Oracle Metalink Forums, so this seems to be uncommon. just wondering if anyone has experienced this. glad that I haven't seen it yet in production. so it seems that the battery in the PERC just decided to charge itself in an unscheduled fashion. I'd recommend that if you have any Dell Servers that you periodically re-charge the battery explicitly, rather than wait for it to do it itself, at some inopportune time. Paul * ATTENTION: The controlfile header block returned by the OShas a sequence number that is too old. The controlfile might be corrupted.PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE without following the steps below.RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE TO THE DATABASE, if the controlfile is truly corrupted.In order to re-start the instance safely, please do the following:(1) Save all copies of the controlfile for later analysis and contact your OS vendor and Oracle support.(2) Mount the instance and issue: ALTER DATABASE BACKUP CONTROLFILE TO TRACE;(3) Unmount the instance. (4) Use the script in the trace file to RE-CREATE THE CONTROLFILE and open the database. *kccchb_6: Controlfile sequence number mismatch ! SGA Seq: 198992 lseqno: 198991 lfileno: 0 hseqno: 198991 hfileno : 0 Event Type:ErrorEvent Source:afamgtEvent Category:NoneEvent ID:1Date:12/4/2003Time:6:37:14 PMUser:N/AComputer:NY-ORCL-001Description:\Device\AFA0 : Battery is Charging Data:: 00 00 00 00 02 00 4e 00 ..N.0008: 00 00 00 00 01 00 07 e0 ...à0010: 00 00 00 00 00 00 00 00 0018: 00 00 00 00 00 00 00 00 0020: 00 00 00 00 00 00 00 00 Do you Yahoo!? Free Pop-Up Blocker - Get it now
possible to do a top -N update?
Im familiar with top n queries. Im trying to tune an update and try to do it in one update statement instead of a query. Im basically trying to update only the top N fields. Here is the cursor(I have rewritten it as an analytic function, but I really want it as a single update. any suggestions? I re-wrote the cursor as follows, which is much faster, but I want to get away from pl/sql select * from (select pk, date, dense_rank() over (partition by pk order by date desc) tab from mytable a) tab where tab = 1 here is the cursor: declare cursor c_update is select pk, last_day(date) monthend_date, max(date) max_date from mytable group by pk, last_day(date); row integer; l_date date; begin select last_day (add_months(sysdate, -1) ) into l_date from dual; row := 0; for update_rec in c_update loop if update_rec.max_date = l_date then update mytable set monthend_date = update_rec.monthend_date where pk = update_rec.pk and date = update_rec.max_perfdate; end if; end loop; commit; end; / I tried re-writing it as follows: but i get errors on the order by. any other possibilities? update mytable a set monthend_date = (select last_day(date) from mytable b where a.pk = b.pk and rownum = 1) where (pk,date) in (select pk,date from mytable c where c.pk = a.pk and rownum = 1 order by date desc) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how is it possible
Thanks Ron , This worked . But do you know why it happens . As I said its happening third time with me . My unix screen behaves weired sometimes it doesn't print any character I type and some time it prints characters which I never type ( in this particular case ). Thanks, -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 5:04 PM This generally happens when there is a CTL-H embedded in the filename. Try mv *m* anotherfilename You should be able to get at it after that. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: how is it possible .com 10/23/2003 04:49 PM Please respond to ORACLE-L This happening with me 3 rd time on this hp box . When I do ls -alt I can see a file in current directory but when I try to open it (vi/cat ) it says no such file or directory . I am same user who created the file .. ( no permission problem ) Any idea ?? -ak /home/ak/myscripts/shell_scr $ ls -alt total 4 drwxrwxrwx 2 ak dba 96 Oct 23 14:40 . -rwxrwxrwx 1 ak dba412 Oct 23 14:40 mon_scr drwxr-x--- 10 ak dba 1024 Oct 13 16:07 .. /home/ak/myscripts/shell_scr $ cat mon_scr cat: Cannot open mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ cat ./mon_scr cat: Cannot open ./mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ /home/ak/shell_scr $ whoami ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 is it possible
What type of terminal are you emulating in your telnet session, what is the value of your TERMINAL environmental variable and what are your stty settings in your .profile? I used to use VT110 or VT220 for the terminal. AK oramagic @hotmail.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: ml-errorsSubject: Re: how is it possible 10/24/2003 12:24 PM Please respond to ORACLE-L Thanks Ron , This worked . But do you know why it happens . As I said its happening third time with me . My unix screen behaves weired sometimes it doesn't print any character I type and some time it prints characters which I never type ( in this particular case ). Thanks, -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 5:04 PM This generally happens when there is a CTL-H embedded in the filename. Try mv *m* anotherfilename You should be able to get at it after that. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: how is it possible .com 10/23/2003 04:49 PM Please respond to ORACLE-L This happening with me 3 rd time on this hp box . When I do ls -alt I can see a file in current directory but when I try to open it (vi/cat ) it says no such file or directory . I am same user who created the file .. ( no permission problem ) Any idea ?? -ak /home/ak/myscripts/shell_scr $ ls -alt total 4 drwxrwxrwx 2 ak dba 96 Oct 23 14:40 . -rwxrwxrwx 1 ak dba412 Oct 23 14:40 mon_scr drwxr-x--- 10 ak dba 1024 Oct 13 16:07 .. /home/ak/myscripts/shell_scr $ cat mon_scr cat: Cannot open mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ cat ./mon_scr cat: Cannot open ./mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ /home/ak/shell_scr $ whoami ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY
Re: how is it possible
Are you using vi? Sometimes if you try to :wq! to a specific name a little too quickly, you might accidentally punch in a non-printable control character in the filename. Adam AK [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/24/2003 10:24 AM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Re: how is it possible Thanks Ron , This worked . But do you know why it happens . As I said its happening third time with me . My unix screen behaves weired sometimes it doesn't print any character I type and some time it prints characters which I never type ( in this particular case ). Thanks, -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 5:04 PM This generally happens when there is a CTL-H embedded in the filename. Try mv *m* anotherfilename You should be able to get at it after that. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: how is it possible .com 10/23/2003 04:49 PM Please respond to ORACLE-L This happening with me 3 rd time on this hp box . When I do ls -alt I can see a file in current directory but when I try to open it (vi/cat ) it says no such file or directory . I am same user who created the file .. ( no permission problem ) Any idea ?? -ak /home/ak/myscripts/shell_scr $ ls -alt total 4 drwxrwxrwx 2 ak dba 96 Oct 23 14:40 . -rwxrwxrwx 1 ak dba412 Oct 23 14:40 mon_scr drwxr-x--- 10 ak dba 1024 Oct 13 16:07 .. /home/ak/myscripts/shell_scr $ cat mon_scr cat: Cannot open mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ cat ./mon_scr cat: Cannot open ./mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ /home/ak/shell_scr $ whoami ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how is it possible
I see this from time to time on our HP systems. The terminal emulator on the client gets out of sync with the host, especially in regards to CTL-H and the Backspace key. I've seen this caused by applications that set the emulator into raw mode (Oracle Applications 10.7 character) and then back as well as su - user ... exit. That CTL-H trick embedded in a filename is how I test our Jr admins. Yes... I can be a tad evil at times... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: how is it possible .com 10/24/2003 10:24 AM Please respond to ORACLE-L Thanks Ron , This worked . But do you know why it happens . As I said its happening third time with me . My unix screen behaves weired sometimes it doesn't print any character I type and some time it prints characters which I never type ( in this particular case ). Thanks, -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 5:04 PM This generally happens when there is a CTL-H embedded in the filename. Try mv *m* anotherfilename You should be able to get at it after that. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: how is it possible .com 10/23/2003 04:49 PM Please respond to ORACLE-L This happening with me 3 rd time on this hp box . When I do ls -alt I can see a file in current directory but when I try to open it (vi/cat ) it says no such file or directory . I am same user who created the file .. ( no permission problem ) Any idea ?? -ak /home/ak/myscripts/shell_scr $ ls -alt total 4 drwxrwxrwx 2 ak dba 96 Oct 23 14:40 . -rwxrwxrwx 1 ak dba412 Oct 23 14:40 mon_scr drwxr-x--- 10 ak dba 1024 Oct 13 16:07 .. /home/ak/myscripts/shell_scr $ cat mon_scr cat: Cannot open mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ cat ./mon_scr cat: Cannot open ./mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ /home/ak/shell_scr $ whoami ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY
how is it possible
This happening with me 3 rd time on this hp box . When I do ls -alt I can see a file in current directory but when I try to open it (vi/cat ) it says no such file or directory . I am same user who created the file . ( no permission problem ) Any idea ?? -ak /home/ak/myscripts/shell_scr $ ls -alt total 4drwxrwxrwx 2 ak dba 96 Oct 23 14:40 .-rwxrwxrwx 1 ak dba 412 Oct 23 14:40 mon_scrdrwxr-x--- 10 ak dba 1024 Oct 13 16:07 .. /home/ak/myscripts/shell_scr $ cat mon_scrcat: Cannot open mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ cat ./mon_scrcat: Cannot open ./mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ /home/ak/shell_scr $ whoamiak
Re: how is it possible
Likely there are non-printing characters in the name of the file... To see them, try ls -alt | od -c | more. To view, rename, or delete the file, use wildcards... This happening with me 3 rd time on this hp box . When I do ls -alt I can see a file in current directory but when I try to open it (vi/cat ) it says no such file or directory . I am same user who created the file . ( no permission problem ) Any idea ?? -ak /home/ak/myscripts/shell_scr $ ls -alt total 4 drwxrwxrwx 2 ak dba 96 Oct 23 14:40 . -rwxrwxrwx 1 ak dba412 Oct 23 14:40 mon_scr drwxr-x--- 10 ak dba 1024 Oct 13 16:07 .. /home/ak/myscripts/shell_scr $ cat mon_scr cat: Cannot open mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ cat ./mon_scr cat: Cannot open ./mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ /home/ak/shell_scr $ whoami ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how is it possible
This generally happens when there is a CTL-H embedded in the filename. Try mv *m* anotherfilename You should be able to get at it after that. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: how is it possible .com 10/23/2003 04:49 PM Please respond to ORACLE-L This happening with me 3 rd time on this hp box . When I do ls -alt I can see a file in current directory but when I try to open it (vi/cat ) it says no such file or directory . I am same user who created the file .. ( no permission problem ) Any idea ?? -ak /home/ak/myscripts/shell_scr $ ls -alt total 4 drwxrwxrwx 2 ak dba 96 Oct 23 14:40 . -rwxrwxrwx 1 ak dba412 Oct 23 14:40 mon_scr drwxr-x--- 10 ak dba 1024 Oct 13 16:07 .. /home/ak/myscripts/shell_scr $ cat mon_scr cat: Cannot open mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ cat ./mon_scr cat: Cannot open ./mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ /home/ak/shell_scr $ whoami ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 is it possible
It probably contains an unprintable control character or an extra space.
Try doing
$ ls -li# to get the inode
$ find . -inode inode -exec mv {} newname \;
or something similar.
Adam
AK [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/23/2003 04:49 PM
Please respond to
[EMAIL PROTECTED]
To
Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc
Subject
how is it possible
This happening with me 3 rd time on this hp box . When I do ls -alt I can
see a file in current directory but when I try to open it (vi/cat ) it
says no such file or directory . I am same user who created the file . (
no permission problem )
Any idea ??
-ak
/home/ak/myscripts/shell_scr $ ls -alt
total 4
drwxrwxrwx 2 ak dba 96 Oct 23 14:40 .
-rwxrwxrwx 1 ak dba412 Oct 23 14:40 mon_scr
drwxr-x--- 10 ak dba 1024 Oct 13 16:07 ..
/home/ak/myscripts/shell_scr $ cat mon_scr
cat: Cannot open mon_scr: No such file or directory
/home/ak/myscripts/shell_scr $ cat ./mon_scr
cat: Cannot open ./mon_scr: No such file or directory
/home/ak/myscripts/shell_scr $
/home/ak/shell_scr $ whoami
ak
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: how is it possible
Could it have some special characters in the name? Try "cat *" instead. - Original Message - From: AK To: Multiple recipients of list ORACLE-L Sent: Thursday, October 23, 2003 6:49 PM Subject: how is it possible This happening with me 3 rd time on this hp box . When I do ls -alt I can see a file in current directory but when I try to open it (vi/cat ) it says no such file or directory . I am same user who created the file . ( no permission problem ) Any idea ?? -ak /home/ak/myscripts/shell_scr $ ls -alt total 4drwxrwxrwx 2 ak dba 96 Oct 23 14:40 .-rwxrwxrwx 1 ak dba 412 Oct 23 14:40 mon_scrdrwxr-x--- 10 ak dba 1024 Oct 13 16:07 .. /home/ak/myscripts/shell_scr $ cat mon_scrcat: Cannot open mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ cat ./mon_scrcat: Cannot open ./mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ /home/ak/shell_scr $ whoamiak
Re: how is it possible
It could have unprintable characters in the name. You can do ls -m, then rename the file. $ ls -m mytest1.lis, mytest.lis , mytest2.lis $ mv mytest.lis mytest.lis From: AK [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: how is it possible Date: Thu, 23 Oct 2003 14:49:24 -0800 This happening with me 3 rd time on this hp box . When I do ls -alt I can see a file in current directory but when I try to open it (vi/cat ) it says no such file or directory . I am same user who created the file . ( no permission problem ) Any idea ?? -ak /home/ak/myscripts/shell_scr $ ls -alt total 4 drwxrwxrwx 2 ak dba 96 Oct 23 14:40 . -rwxrwxrwx 1 ak dba412 Oct 23 14:40 mon_scr drwxr-x--- 10 ak dba 1024 Oct 13 16:07 .. /home/ak/myscripts/shell_scr $ cat mon_scr cat: Cannot open mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ cat ./mon_scr cat: Cannot open ./mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ /home/ak/shell_scr $ whoami ak _ Add MSN 8 Internet Software to your current Internet access and enjoy patented spam control and more. Get two months FREE! http://join.msn.com/?page=dept/byoa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Killough INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Opinions sought on possible TOAD replacement
Many thanks to everyone who responded! Looks like the outcome will be PL/SQL Developer licenses for our developers, while I get to keep my Toad Xpert / DBA module. Thanks again for the useful insights! Paul Vincent DBA University of Central England
RE: Opinions sought on possible TOAD replacement
Paul, I've used PL/SQL Developer by Allround Automations to develop PL/SQL procs, packages and it's fine including a well featured de-bugger. I believe that a site licence costs $3,000. As to it being a DBA tool I'd have to say it's not in the same league as TOAD Xpert with DBA module. Chris Dunscombe Accenture Worthing Unit Internal: 71-3558 External: 01903-283558 Email: [EMAIL PROTECTED] -Original Message- Sent: 15 October 2003 16:59 To: Multiple recipients of list ORACLE-L Management have been grumbling about the cost of TOAD Professional licenses, and have been recommended a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!. But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Opinions sought on possible TOAD replacement
Hi, I'll second Chris' statement. I found PL/SQL Developer to be a great and inexpensive PL/SQL development tool. Its debugger is especially good. In fact from a development perspective I found it to be far superior to TOAD. Have no idea about support and consideration of user suggestions as I had no issues to report. However as its name suggests it is purely a PL/SQL development tool and not an administration tool, but then I don't think its trying to be the latter. Would recommend you stick with TOAD for administration, I don't know of anything else significantly cheaper out there to replace TOAD and any small licensing savings will be lost to lower productivity during the learning curve anyway. Regards, Mark. From: Dunscombe, Chris [EMAIL PROTECTED] Date: Thu, 16 Oct 2003 07:50:30 +0100 Subject: RE: Opinions sought on possible TOAD replacement Paul, I've used PL/SQL Developer by Allround Automations to develop PL/SQL procs, packages and it's fine including a well featured de-bugger. I believe that a site licence costs $3,000. As to it being a DBA tool I'd have to say it's not in the same league as TOAD Xpert with DBA module. Chris Dunscombe Accenture Worthing Unit Internal: 71-3558 External: 01903-283558 Email: [EMAIL PROTECTED] -Original Message- Sent: 15 October 2003 16:59 To: Multiple recipients of list ORACLE-L Management have been grumbling about the cost of TOAD Professional licenses, and have been recommended a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!. But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Patterson, Mark INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Opinions sought on possible TOAD replacement
Title: Message We do something similar here. Developers gets Golden (from Benthic). Which has a nice little PL/SQL Editor and SQL spreadsheet. DBAs get TOAD. Babette Turner-Underwood work: [EMAIL PROTECTED] home: [EMAIL PROTECTED] 954-3752 (Mon - Fri 7am - 3pm) -Original Message-From: Rich Gesler [mailto:[EMAIL PROTECTED] Sent: 2003-10-15 1:24 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Opinions sought on possible TOAD replacement You are correct in that it is very developer centric. As far, as hidden charms...I'd have to say the Reports capabilities. You can easily incorporate your DBA scripts into this little tool. Still not as good as TOAD (or even TORA) for a DBA but a nice, cheap addition to the arsenal. Download it and give it a try, Rich -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Paul VincentSent: Wednesday, October 15, 2003 11:59 AMTo: Multiple recipients of list ORACLE-LSubject: Opinions sought on possible TOAD replacement Management have been grumbling about the cost of TOAD Professional licenses, and have been "recommended" a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying "fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!". But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England
RE: Opinions sought on possible TOAD replacement
Title: Message FYI... Golden is a query tool (and a damn good one)... In my opinion, it's what SQL Worksheet should be... Simple to use and fast... The PL/SQL editor from Benthicis separate tool called PLEdit... It's ok but not great... Like Golden it is quick and simple... But, if you are looking for advanced functionality then I would definitely go with another solution... I took a peek at PL/SQL Developer a few months agoand thought it was a nice tool... Especially for the price... That being said, I use PLEdit for my own use...I buy my own tools and don't do enough to justify the cost... Tim -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, October 16, 2003 3:29 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Opinions sought on possible TOAD replacement We do something similar here. Developers gets Golden (from Benthic). Which has a nice little PL/SQL Editor and SQL spreadsheet. DBAs get TOAD. Babette Turner-Underwood work: [EMAIL PROTECTED] home: [EMAIL PROTECTED] 954-3752 (Mon - Fri 7am - 3pm) -Original Message-From: Rich Gesler [mailto:[EMAIL PROTECTED] Sent: 2003-10-15 1:24 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Opinions sought on possible TOAD replacement You are correct in that it is very developer centric. As far, as hidden charms...I'd have to say the Reports capabilities. You can easily incorporate your DBA scripts into this little tool. Still not as good as TOAD (or even TORA) for a DBA but a nice, cheap addition to the arsenal. Download it and give it a try, Rich -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Paul VincentSent: Wednesday, October 15, 2003 11:59 AMTo: Multiple recipients of list ORACLE-LSubject: Opinions sought on possible TOAD replacement Management have been grumbling about the cost of TOAD Professional licenses, and have been "recommended" a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying "fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!". But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England
RE: Opinions sought on possible TOAD replacement
TORA, OraC, sqlplus, vi, perl and a linux box to run it on, though the database servers are mostly Win2k. Software $0.00 OS $0.0 HW $12k A procedure return with no errors Priceless g FWIW for a nice sql editor golden is nice. Others on this list sold me on that bob Paul Vincent [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/15/2003 08:59 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Opinions sought on possible TOAD replacement Management have been grumbling about the cost of TOAD Professional licenses, and have been recommended a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!. But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England
RE: Opinions sought on possible TOAD replacement
I forgot to include years of troubleshooting and debugging experience the techniques to go with it. :) Jared Bob Metelsky [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/16/2003 01:24 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Opinions sought on possible TOAD replacement TORA, OraC, sqlplus, vi, perl and a linux box to run it on, though the database servers are mostly Win2k. Software $0.00 OS $0.0 HW $12k A procedure return with no errors Priceless g FWIW for a nice sql editor golden is nice. Others on this list sold me on that bob Paul Vincent [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/15/2003 08:59 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Opinions sought on possible TOAD replacement Management have been grumbling about the cost of TOAD Professional licenses, and have been recommended a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!. But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England
Opinions sought on possible TOAD replacement
Management have been grumbling about the cost of TOAD Professional licenses, and have been "recommended" a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying "fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!". But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England
RE: Opinions sought on possible TOAD replacement
Title: Message Management wants to standardize to a single tool for support and maintenance purposes. At least that's the story here. I have heard good things about PL/SQL Developer. If it's an option, you could checkout TOra (http://www.globecom.se/tora/) which is comparable to TOAD and $100 a license on Windows. This option offers some middle ground anyway. -Original Message-From: Paul Vincent [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 11:59 AMTo: Multiple recipients of list ORACLE-LSubject: Opinions sought on possible TOAD replacement Management have been grumbling about the cost of TOAD Professional licenses, and have been "recommended" a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying "fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!". But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England
RE: Opinions sought on possible TOAD replacement
You are correct in that it is very developer centric. As far, as hidden charms...I'd have to say the Reports capabilities. You can easily incorporate your DBA scripts into this little tool. Still not as good as TOAD (or even TORA) for a DBA but a nice, cheap addition to the arsenal. Download it and give it a try, Rich -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Paul VincentSent: Wednesday, October 15, 2003 11:59 AMTo: Multiple recipients of list ORACLE-LSubject: Opinions sought on possible TOAD replacement Management have been grumbling about the cost of TOAD Professional licenses, and have been "recommended" a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying "fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!". But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England
RE: Opinions sought on possible TOAD replacement
You may also want to consider OraEdit Pro by DKG Advanced Solutions Inc. (www.dkgas.com) Shamita Shamita Singh Mohawk College Hamilton, Ontario --- Rich Gesler [EMAIL PROTECTED] wrote: You are correct in that it is very developer centric. As far, as hidden charms...I'd have to say the Reports capabilities. You can easily incorporate your DBA scripts into this little tool. Still not as good as TOAD (or even TORA) for a DBA but a nice, cheap addition to the arsenal. Download it and give it a try, Rich -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Paul Vincent Sent: Wednesday, October 15, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Subject: Opinions sought on possible TOAD replacement Management have been grumbling about the cost of TOAD Professional licenses, and have been recommended a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!. But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England = Shamita __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Shamita Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Opinions sought on possible TOAD replacement
Have you looked at Toolkit for Oracle (TOra)? http://sourceforge.net/projects/tora/ I've heard rather good things about it. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED]We have enough youth.How about a fountain of intelligence? -Original Message-From: Paul Vincent [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 11:59 AMTo: Multiple recipients of list ORACLE-LSubject: Opinions sought on possible TOAD replacement Management have been grumbling about the cost of TOAD Professional licenses, and have been "recommended" a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying "fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!". But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England
RE: Opinions sought on possible TOAD replacement
TORA -Original Message-From: Paul Vincent [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 10:59 AMTo: Multiple recipients of list ORACLE-LSubject: Opinions sought on possible TOAD replacement Management have been grumbling about the cost of TOAD Professional licenses, and have been "recommended" a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying "fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!". But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England
Re: Opinions sought on possible TOAD replacement
TORA, OraC, sqlplus, vi, perl and a linux box to run it on, though the database servers are mostly Win2k. Software $0.00 OS $0.0 HW $12k Jared Paul Vincent [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/15/2003 08:59 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Opinions sought on possible TOAD replacement Management have been grumbling about the cost of TOAD Professional licenses, and have been recommended a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!. But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England
possible to have a primary key with a bitmap indx?
is it possible to have a primary key that is enforced with a bitmap index? if so what is the syntax? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to have a primary key with a bitmap index?
I don't know what's the syntax (or if it even exists). But, logically bitmap indexes are for the columns with low cardinality, while primary key index is unique. So, why do you want bitmap index for your primary key? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 3:55 PM To: Multiple recipients of list ORACLE-L is it possible to have a primary key that is enforced with a bitmap index? if so what is the syntax? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to have a primary key with a bitmap indx?
A bitmap index is for a low cardinality column, so I doubt it would be a good idea to use it to enforce a primary key. Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 4:55 PM To: Multiple recipients of list ORACLE-L is it possible to have a primary key that is enforced with a bitmap index? if so what is the syntax? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to have a primary key with a bitmap indx?
It's not possible: SQL create bitmap index emp_empno_b on emp(empno); Index created. SQL alter table emp add constraint emp_pk primary key(empno) using index emp_empno_b novalidate; alter table emp add constraint emp_pk primary key(empno) using index emp_empno_b novalidate * ERROR at line 1: ORA-01418: specified index does not exist SQL drop index emp_empno_b; Index dropped. SQL create index emp_empno_b on emp(empno); Index created. SQL alter table emp add constraint emp_pk primary key(empno) using index emp_empno_b novalidate; Table altered. SQL -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 4:55 PM To: Multiple recipients of list ORACLE-L Subject: possible to have a primary key with a bitmap indx? is it possible to have a primary key that is enforced with a bitmap index? if so what is the syntax? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: possible to have a primary key with a bitmap index?
im just playing around and testing things. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 5:34 PM I don't know what's the syntax (or if it even exists). But, logically bitmap indexes are for the columns with low cardinality, while primary key index is unique. So, why do you want bitmap index for your primary key? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 3:55 PM To: Multiple recipients of list ORACLE-L is it possible to have a primary key that is enforced with a bitmap index? if so what is the syntax? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to have a primary key with a bitmap index?
Keep playing -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Ryan Sent: Tuesday, September 23, 2003 4:45 PM To: Multiple recipients of list ORACLE-L im just playing around and testing things. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 5:34 PM I don't know what's the syntax (or if it even exists). But, logically bitmap indexes are for the columns with low cardinality, while primary key index is unique. So, why do you want bitmap index for your primary key? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 3:55 PM To: Multiple recipients of list ORACLE-L is it possible to have a primary key that is enforced with a bitmap index? if so what is the syntax? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to have a primary key with a bitmap indx?
Restrictions on Unique Indexes You cannot specify both UNIQUE and BITMAP -Original Message- Sent: Tuesday, September 23, 2003 5:35 PM To: Multiple recipients of list ORACLE-L It's not possible: SQL create bitmap index emp_empno_b on emp(empno); Index created. SQL alter table emp add constraint emp_pk primary key(empno) using index emp_empno_b novalidate; alter table emp add constraint emp_pk primary key(empno) using index emp_empno_b novalidate * ERROR at line 1: ORA-01418: specified index does not exist SQL drop index emp_empno_b; Index dropped. SQL create index emp_empno_b on emp(empno); Index created. SQL alter table emp add constraint emp_pk primary key(empno) using index emp_empno_b novalidate; Table altered. SQL -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 4:55 PM To: Multiple recipients of list ORACLE-L Subject: possible to have a primary key with a bitmap indx? is it possible to have a primary key that is enforced with a bitmap index? if so what is the syntax? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to have a primary key with a bitmap indx?
Yes, but you can enforce a PK constraint without a unique index. If you take a look at the code, you'll see that my index is also nonunique. Theoretically, there shouldn't be much difference between non-unique indexes and bitmap indexes. Practically, there is. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Khedr, Waleed Sent: Tuesday, September 23, 2003 5:50 PM To: Multiple recipients of list ORACLE-L Subject: RE: possible to have a primary key with a bitmap indx? Restrictions on Unique Indexes You cannot specify both UNIQUE and BITMAP -Original Message- Sent: Tuesday, September 23, 2003 5:35 PM To: Multiple recipients of list ORACLE-L It's not possible: SQL create bitmap index emp_empno_b on emp(empno); Index created. SQL alter table emp add constraint emp_pk primary key(empno) using SQL index emp_empno_b novalidate; alter table emp add constraint emp_pk primary key(empno) using index emp_empno_b novalidate * ERROR at line 1: ORA-01418: specified index does not exist SQL drop index emp_empno_b; Index dropped. SQL create index emp_empno_b on emp(empno); Index created. SQL alter table emp add constraint emp_pk primary key(empno) using SQL index emp_empno_b novalidate; Table altered. SQL -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 4:55 PM To: Multiple recipients of list ORACLE-L Subject: possible to have a primary key with a bitmap indx? is it possible to have a primary key that is enforced with a bitmap index? if so what is the syntax? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from
RE: possible to have a primary key with a bitmap indx?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] is it possible to have a primary key that is enforced with a bitmap index? I don't think so. Here's an example from a 9.2.0.3 database: SQL create table t (v varchar2 (10), d date) ; Table créée. SQL create bitmap index bit1 on t (v) ; Index créé. SQL alter table t add (constraint t_pk primary key (v)) ; alter table t add (constraint t_pk primary key (v)) * ERREUR à la ligne 1 : ORA-01408: such column list already indexed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-600 Error [Possible Data Loss]
We have found the cause of the problem. If populate the varray for a row with 100
values, copy that data to a temporary table and then perform split and exchange
partition operations everything is fine. However if we populate the varray with 1000
values for a row and then perform the same operations. Selecting from the partition
results in the error.
For those wanting details ...
create or replace type int_values as varray(16384) of number(38);
create table arch_wave_i
(pv_id number(38),
timestamp date,
nanosecs number(9),
stat number(8),
sevr number(8),
ostat number(16),
value int_values)
partition by range (timestamp)
( partition first values less than
(TO_DATE('01/01/2003 00:00:00', 'mm/dd/ hh24:mi:ss')),
partition bin values less than (MAXVALUE) );
create index archive_wave_index on archive_wave_i (timestamp, pv_id) local;
create table arch_wave_i
(pv_id number(38),
timestamp date,
nanosecs number(9),
stat number(8),
sevr number(8),
ostat number(16),
value int_values)
partition by range (timestamp)
( partition first values less than
(TO_DATE('01/01/2003 00:00:00', 'mm/dd/ hh24:mi:ss')),
partition SEP1803 values less than
(TO_DATE('09/19/2003 00:00:00', 'mm/dd/ hh24:mi:ss')),
partition bin values less than (MAXVALUE) );
create index arch_wave_i_index on arch_wave_i (timestamp, pv_id) local;
To create the error
To recreate the problem:
1. Populate arch_wave_i with one or more rows that have the
following characteristics:
a. the timestamp field is = '09/18/2003 00:00:00' and
'09/19/2003 00:00:00'
b. the value field is an array of 1000 or more integer
values (the actual values do not matter)
c. the other fields (pv_id, nanosecs, stat, sevr, ostat)
may be set to 0
2. create table temp_wave_i as select * from arch_wave_i;
3. create index temp_wave_i_index on temp_wave_i (timestamp, pv_id);
4. alter session set nls_date_format = 'mm/dd/ hh24:mi:ss';
5. alter table archive_wave_i split partition bin at
('09/19/2003 00:00:00') into
(partition SEP1803, partition bin);
6. alter table archive_wave_i exchange partition SEP1803 with
table temp_wave_i including indexes with validation;
7. select * from archive_wave_i partition (SEP1803);
This results in one of the following messages:
a. ORA-00600: internal error code, arguments:
[koxsisz1], [1], [], [], [], [], [], []
b. ORA-00600: internal error code, arguments:
[kcbgtcr_4], [2094072], [0], [1], [], [], [], []
Sometimes the second argument is 2094096 or 2094120
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, September 18, 2003 1:35 PM
To: Multiple recipients of list ORACLE-L
Ian,
I was going to recommend to try exporting your problematic partition with direct=y,
that way normal SQL query processing layer is bypassed in Oracle kernel all data
belonging to segment is read directly (thus hopefully avoiding the koxsisz1 crash) ...
but I'm not sure whether varrays don't turn exp to conventional as is the case with
objects and LOBs...
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, September 18, 2003 8:29 PM
The error was first discovered when we tried to move a partition. (
alter
table archive_wave_i move partition SEP1103 tablespace
CHANARCH_NLC_2003_09_DATA). The table was renamed to OLD_archive_wave_i
once it was determined that no new varray data was accessible. My theory is that this
is logical corruption of the data dictionary, but I have not worked out the
particulars.
Selecting via an index or FTS does not matter. What type of error do
you
believe koxsisz1 to be?
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, September 18, 2003 4:13 AM
To: Multiple recipients of list ORACLE-L
Hi!
If even Oracle hasn't seen this error, then probably we can't help
much
here either. Just a wild guess, try to move this partition to another location and
select from it then (although koxsisz1 isn't a data layer error as far as I
understand). Try to read using index if available, then using full hint etc..
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, September 18, 2003 3:04 AM
This is not my week ...
Has anyone seen anything like this
select value from chanarch_nlc.old_archive_wave_i partition
(SEP1603)
where pv_id =
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [],
[],
[], [],
[]
I have Oracle support working on the problem.They say they have
never
seen such an error before. Oracle though often tells me that. The
table
looks like
Name Null?Type
-
---
-
PV_ID NUMBER(38)
TIMESTAMP
Re: ORA-600 Error [Possible Data Loss]
Hi! If even Oracle hasn't seen this error, then probably we can't help much here either. Just a wild guess, try to move this partition to another location and select from it then (although koxsisz1 isn't a data layer error as far as I understand). Try to read using index if available, then using full hint etc.. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 3:04 AM This is not my week ... Has anyone seen anything like this select value from chanarch_nlc.old_archive_wave_i partition (SEP1603) where pv_id = * ERROR at line 1: ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [], [], [], [], [] I have Oracle support working on the problem.They say they have never seen such an error before. Oracle though often tells me that. The table looks like Name Null?Type - --- - PV_ID NUMBER(38) TIMESTAMP DATE NANOSECS NUMBER(9) STAT NUMBER(8) SEVR NUMBER(8) OSTAT NUMBER(16) VALUE CHANARCH_NLC.INT_VALUES The last column is a varray. I can retrieve any of the other columns from that partition. I can also retrieve the value column from any partition before September 11, 2003. Any partition after that fails with the 0ra-600 error when the value column is selected. DBV at first gave errors along the lines of BV-00102: File I/O error on FILE (/u9/oradata/NLCO/chanarch_nlc_active_data01.dbf) during end read op eration (-1) The file is 2018 MB in size. But resizing the files downwards and back up again fixed That problem. The files are allowed to autoextend with 2018 being the maximum size. This is not the same database which had the RAID problem. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-600 Error [Possible Data Loss]
The error was first discovered when we tried to move a partition. ( alter table archive_wave_i move partition SEP1103 tablespace CHANARCH_NLC_2003_09_DATA). The table was renamed to OLD_archive_wave_i once it was determined that no new varray data was accessible. My theory is that this is logical corruption of the data dictionary, but I have not worked out the particulars. Selecting via an index or FTS does not matter. What type of error do you believe koxsisz1 to be? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 18, 2003 4:13 AM To: Multiple recipients of list ORACLE-L Hi! If even Oracle hasn't seen this error, then probably we can't help much here either. Just a wild guess, try to move this partition to another location and select from it then (although koxsisz1 isn't a data layer error as far as I understand). Try to read using index if available, then using full hint etc.. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 3:04 AM This is not my week ... Has anyone seen anything like this select value from chanarch_nlc.old_archive_wave_i partition (SEP1603) where pv_id = * ERROR at line 1: ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [], [], [], [], [] I have Oracle support working on the problem.They say they have never seen such an error before. Oracle though often tells me that. The table looks like Name Null?Type - --- - PV_ID NUMBER(38) TIMESTAMP DATE NANOSECS NUMBER(9) STAT NUMBER(8) SEVR NUMBER(8) OSTAT NUMBER(16) VALUE CHANARCH_NLC.INT_VALUES The last column is a varray. I can retrieve any of the other columns from that partition. I can also retrieve the value column from any partition before September 11, 2003. Any partition after that fails with the 0ra-600 error when the value column is selected. DBV at first gave errors along the lines of BV-00102: File I/O error on FILE (/u9/oradata/NLCO/chanarch_nlc_active_data01.dbf) during end read op eration (-1) The file is 2018 MB in size. But resizing the files downwards and back up again fixed That problem. The files are allowed to autoextend with 2018 being the maximum size. This is not the same database which had the RAID problem. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-600 Error [Possible Data Loss]
Hi! I'm on thin ice here, but I think KO means some sort of object layer (KOC is Kernel-Object-Cache module as far as I understand), so koxsisz1 has probably something to do with objects as well (thus not caching, x might mean execute or transaction or anything else:). On the other hand, varrays are somewhat related to objects as well, at least in sense that they are data structures, not basic datatypes. (This one probably won't work, but try to temporarily increase OBJECT_CACHE_MAX_SIZE_PERCENT and OBJECT_CACHE_OPTIMAL_SIZE in your session (put first one to 1000 and second one to 1048576 for example). Maybe there are some specific problems with caching your varray in client side, but as I said I doubt it will help (but it's easy to try out).) Otherwise, try to isolate those varrays causing you problem. Are all varrays in SEP1603 partition unusable? Only some of them? Only new ones? Are any other partitions affected? One more way would be to take one working record and insert it to new partition. If now the new record gets unusable again, it's possible to take relevant blockdumps for both these records, to see whether theres any difference in low-level data. For summary, since you had some file IO errors (as shown by DBV), you might have corrupted your data during the move operation. Maybe some disk controller or memory caused this error... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 8:29 PM The error was first discovered when we tried to move a partition. ( alter table archive_wave_i move partition SEP1103 tablespace CHANARCH_NLC_2003_09_DATA). The table was renamed to OLD_archive_wave_i once it was determined that no new varray data was accessible. My theory is that this is logical corruption of the data dictionary, but I have not worked out the particulars. Selecting via an index or FTS does not matter. What type of error do you believe koxsisz1 to be? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 18, 2003 4:13 AM To: Multiple recipients of list ORACLE-L Hi! If even Oracle hasn't seen this error, then probably we can't help much here either. Just a wild guess, try to move this partition to another location and select from it then (although koxsisz1 isn't a data layer error as far as I understand). Try to read using index if available, then using full hint etc.. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 3:04 AM This is not my week ... Has anyone seen anything like this select value from chanarch_nlc.old_archive_wave_i partition (SEP1603) where pv_id = * ERROR at line 1: ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [], [], [], [], [] I have Oracle support working on the problem.They say they have never seen such an error before. Oracle though often tells me that. The table looks like Name Null?Type - --- - PV_ID NUMBER(38) TIMESTAMP DATE NANOSECS NUMBER(9) STAT NUMBER(8) SEVR NUMBER(8) OSTAT NUMBER(16) VALUE CHANARCH_NLC.INT_VALUES The last column is a varray. I can retrieve any of the other columns from that partition. I can also retrieve the value column from any partition before September 11, 2003. Any partition after that fails with the 0ra-600 error when the value column is selected. DBV at first gave errors along the lines of BV-00102: File I/O error on FILE (/u9/oradata/NLCO/chanarch_nlc_active_data01.dbf) during end read op eration (-1) The file is 2018 MB in size. But resizing the files downwards and back up again fixed That problem. The files are allowed to autoextend with 2018 being the maximum size. This is not the same database which had the RAID problem. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name
Re: ORA-600 Error [Possible Data Loss]
Ian, I was going to recommend to try exporting your problematic partition with direct=y, that way normal SQL query processing layer is bypassed in Oracle kernel all data belonging to segment is read directly (thus hopefully avoiding the koxsisz1 crash) ... but I'm not sure whether varrays don't turn exp to conventional as is the case with objects and LOBs... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 8:29 PM The error was first discovered when we tried to move a partition. ( alter table archive_wave_i move partition SEP1103 tablespace CHANARCH_NLC_2003_09_DATA). The table was renamed to OLD_archive_wave_i once it was determined that no new varray data was accessible. My theory is that this is logical corruption of the data dictionary, but I have not worked out the particulars. Selecting via an index or FTS does not matter. What type of error do you believe koxsisz1 to be? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 18, 2003 4:13 AM To: Multiple recipients of list ORACLE-L Hi! If even Oracle hasn't seen this error, then probably we can't help much here either. Just a wild guess, try to move this partition to another location and select from it then (although koxsisz1 isn't a data layer error as far as I understand). Try to read using index if available, then using full hint etc.. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 3:04 AM This is not my week ... Has anyone seen anything like this select value from chanarch_nlc.old_archive_wave_i partition (SEP1603) where pv_id = * ERROR at line 1: ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [], [], [], [], [] I have Oracle support working on the problem.They say they have never seen such an error before. Oracle though often tells me that. The table looks like Name Null?Type - --- - PV_ID NUMBER(38) TIMESTAMP DATE NANOSECS NUMBER(9) STAT NUMBER(8) SEVR NUMBER(8) OSTAT NUMBER(16) VALUE CHANARCH_NLC.INT_VALUES The last column is a varray. I can retrieve any of the other columns from that partition. I can also retrieve the value column from any partition before September 11, 2003. Any partition after that fails with the 0ra-600 error when the value column is selected. DBV at first gave errors along the lines of BV-00102: File I/O error on FILE (/u9/oradata/NLCO/chanarch_nlc_active_data01.dbf) during end read op eration (-1) The file is 2018 MB in size. But resizing the files downwards and back up again fixed That problem. The files are allowed to autoextend with 2018 being the maximum size. This is not the same database which had the RAID problem. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
RE: ORA-600 Error [Possible Data Loss]
The varray data is stored in a LOB. So I expect direct export will not work. But I'll give it a try. Ian -Original Message- Sent: Thursday, September 18, 2003 1:35 PM To: Multiple recipients of list ORACLE-L Ian, I was going to recommend to try exporting your problematic partition with direct=y, that way normal SQL query processing layer is bypassed in Oracle kernel all data belonging to segment is read directly (thus hopefully avoiding the koxsisz1 crash) ... but I'm not sure whether varrays don't turn exp to conventional as is the case with objects and LOBs... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 8:29 PM The error was first discovered when we tried to move a partition. ( alter table archive_wave_i move partition SEP1103 tablespace CHANARCH_NLC_2003_09_DATA). The table was renamed to OLD_archive_wave_i once it was determined that no new varray data was accessible. My theory is that this is logical corruption of the data dictionary, but I have not worked out the particulars. Selecting via an index or FTS does not matter. What type of error do you believe koxsisz1 to be? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 18, 2003 4:13 AM To: Multiple recipients of list ORACLE-L Hi! If even Oracle hasn't seen this error, then probably we can't help much here either. Just a wild guess, try to move this partition to another location and select from it then (although koxsisz1 isn't a data layer error as far as I understand). Try to read using index if available, then using full hint etc.. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 3:04 AM This is not my week ... Has anyone seen anything like this select value from chanarch_nlc.old_archive_wave_i partition (SEP1603) where pv_id = * ERROR at line 1: ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [], [], [], [], [] I have Oracle support working on the problem.They say they have never seen such an error before. Oracle though often tells me that. The table looks like Name Null?Type - --- - PV_ID NUMBER(38) TIMESTAMP DATE NANOSECS NUMBER(9) STAT NUMBER(8) SEVR NUMBER(8) OSTAT NUMBER(16) VALUE CHANARCH_NLC.INT_VALUES The last column is a varray. I can retrieve any of the other columns from that partition. I can also retrieve the value column from any partition before September 11, 2003. Any partition after that fails with the 0ra-600 error when the value column is selected. DBV at first gave errors along the lines of BV-00102: File I/O error on FILE (/u9/oradata/NLCO/chanarch_nlc_active_data01.dbf) during end read op eration (-1) The file is 2018 MB in size. But resizing the files downwards and back up again fixed That problem. The files are allowed to autoextend with 2018 being the maximum size. This is not the same database which had the RAID problem. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for
ORA-600 Error [Possible Data Loss]
This is not my week ... Has anyone seen anything like this select value from chanarch_nlc.old_archive_wave_i partition (SEP1603) where pv_id = * ERROR at line 1: ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [], [], [], [], [] I have Oracle support working on the problem.They say they have never seen such an error before. Oracle though often tells me that. The table looks like Name Null?Type - PV_ID NUMBER(38) TIMESTAMP DATE NANOSECS NUMBER(9) STAT NUMBER(8) SEVR NUMBER(8) OSTAT NUMBER(16) VALUE CHANARCH_NLC.INT_VALUES The last column is a varray. I can retrieve any of the other columns from that partition. I can also retrieve the value column from any partition before September 11, 2003. Any partition after that fails with the 0ra-600 error when the value column is selected. DBV at first gave errors along the lines of BV-00102: File I/O error on FILE (/u9/oradata/NLCO/chanarch_nlc_active_data01.dbf) during end read op eration (-1) The file is 2018 MB in size. But resizing the files downwards and back up again fixed That problem. The files are allowed to autoextend with 2018 being the maximum size. This is not the same database which had the RAID problem. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: possible to dynamicly connect to a different user in pl/sql?
I think that there are two reasons to do it: 1) You need permissions that are granted to another user 2) You need to work in another schema and you have permissions to these objects. In 1) you can grant yourself the necessary permissions. in 2) try execute immediate 'alter session set current schema' (I did not check if this will work in PL/sql. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 5:21 PM I doubt it... but this could save me from coding it into a unix script and keeping all my code in my package. I tried execute immediate 'connect user/password; and it failed. is this doable? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
possible to dynamicly connect to a different user in pl/sql?
I doubt it... but this could save me from coding it into a unix script and keeping all my code in my package. I tried execute immediate 'connect user/password; and it failed. is this doable? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to dynamicly connect to a different user in pl/sql?
My guess is that 'connect' is a sql*plus command (rather than a pl/sql command) so wouldn't work with execute immediate. But maybe you could break out the portions of your code that need to run as a different user into a separate package, owned by that user? HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, August 20, 2003 8:22 AM To: Multiple recipients of list ORACLE-L I doubt it... but this could save me from coding it into a unix script and keeping all my code in my package. I tried execute immediate 'connect user/password; and it failed. is this doable? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: possible to dynamicly connect to a different user in pl/sql?
the user would be sys. I need to create grants to DBA_ views on the fly so we can scale an application. We add new users and some of them need access to these to run a load process properly. ill just run it from unix. From: Pardee, Roy E [EMAIL PROTECTED] Date: 2003/08/20 Wed PM 01:24:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: possible to dynamicly connect to a different user in pl/sql? My guess is that 'connect' is a sql*plus command (rather than a pl/sql command) so wouldn't work with execute immediate. But maybe you could break out the portions of your code that need to run as a different user into a separate package, owned by that user? HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, August 20, 2003 8:22 AM To: Multiple recipients of list ORACLE-L I doubt it... but this could save me from coding it into a unix script and keeping all my code in my package. I tried execute immediate 'connect user/password; and it failed. is this doable? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
possible to set max_extents for lobs at tablespace level?
my max_extents for my tablespace is set to 505, yet all my lob segments default to a max_extent of 121. is it possible to have them use the tablespace default? or set up a default for all lob segments to use? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible Bug in Oracle 9.2.0.2
I've no experience with Peoplesoft and Oracle 9, but .. Try setting the following parameters optimizer_features_enable = 8.1.6 As I recall this prevented Oracle from incorrectly tossing out some subselects. I'm not sure if it is even valid in 9iR2 _ignore_desc_in_index = TRUE Not setting this can result in horrendous performance problems. Ian MacGregor -Original Message- Sent: Wednesday, August 06, 2003 9:19 PM To: Multiple recipients of list ORACLE-L Can you please list select emplid, empl_rcd, effdt, effseq, empl_status from ps_job where emplid = '3442' At 03:34 PM 8/6/2003 -0800, you wrote: While I am waiting for oracle support to respond to my tar update (2nd callback) I am just wondering if anybody has found this problem. We have the following select query (from a peoplesoft implementation) SELECT a.emplid, a.effdt FROM PS_JOB A WHERE A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT = SYSDATE) AND A.EFFSEQ = (SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A.EMPLID = A2.EMPLID AND A.EMPL_RCD = A2.EMPL_RCD AND A.EFFDT = A2.EFFDT) AND A.EMPL_STATUS = 'A' and a.emplid='3442' when we run the query we get one row back, but when we replace the field names with count(*), the resulting answer back is 2. We have tested it in 8.0.5.1.1 and we get the correct results, 1 row, and a count of 1. Darren --- - -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
possible Bug in Oracle 9.2.0.2
While I am waiting for oracle support to respond to my tar update (2nd callback) I am just wondering if anybody has found this problem. We have the following select query (from a peoplesoft implementation) SELECT a.emplid, a.effdt FROM PS_JOB A WHERE A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT = SYSDATE) AND A.EFFSEQ = (SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A.EMPLID = A2.EMPLID AND A.EMPL_RCD = A2.EMPL_RCD AND A.EFFDT = A2.EFFDT) AND A.EMPL_STATUS = 'A' and a.emplid='3442' when we run the query we get one row back, but when we replace the field names with count(*), the resulting answer back is 2. We have tested it in 8.0.5.1.1 and we get the correct results, 1 row, and a count of 1. Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
possible to set max
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible Bug in Oracle 9.2.0.2
Title: RE: possible Bug in Oracle 9.2.0.2 You it is a bug alter session|system set _unnest_subquery=false / Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Browett, Darren [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 06, 2003 7:34 PM To: Multiple recipients of list ORACLE-L Subject: possible Bug in Oracle 9.2.0.2 While I am waiting for oracle support to respond to my tar update (2nd callback) I am just wondering if anybody has found this problem. We have the following select query (from a peoplesoft implementation) SELECT a.emplid, a.effdt FROM PS_JOB A WHERE A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT = SYSDATE) AND A.EFFSEQ = (SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A.EMPLID = A2.EMPLID AND A.EMPL_RCD = A2.EMPL_RCD AND A.EFFDT = A2.EFFDT) AND A.EMPL_STATUS = 'A' and a.emplid='3442' when we run the query we get one row back, but when we replace the field names with count(*), the resulting answer back is 2. We have tested it in 8.0.5.1.1 and we get the correct results, 1 row, and a count of 1. Darren -- Darren Browett P.Eng This message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: possible Bug in Oracle 9.2.0.2
Title: Message That fixed it, thank you. I still haven't heard from oracle support yet. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 4:44 AMTo: Multiple recipients of list ORACLE-LSubject: RE: possible Bug in Oracle 9.2.0.2 You it is a bug alter session|system set "_unnest_subquery"=false / Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Browett, Darren [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 06, 2003 7:34 PM To: Multiple recipients of list ORACLE-L Subject: possible Bug in Oracle 9.2.0.2 While I am waiting for oracle support to respond to my tar update (2nd callback) I am just wondering if anybody has found this problem. We have the following select query (from a peoplesoft implementation) SELECT a.emplid, a.effdt FROM PS_JOB A WHERE A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT = SYSDATE) AND A.EFFSEQ = (SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A.EMPLID = A2.EMPLID AND A.EMPL_RCD = A2.EMPL_RCD AND A.EFFDT = A2.EFFDT) AND A.EMPL_STATUS = 'A' and a.emplid='3442' when we run the query we get one row back, but when we replace the field names with count(*), the resulting answer back is "2". We have tested it in 8.0.5.1.1 and we get the correct results, 1 row, and a count of 1. Darren -- Darren Browett P.Eng This message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible Bug in Oracle 9.2.0.2
Did you open an iTar with Oracle? If so, what severity was it logged at? How you answer certain questions on the iTAR form will impact the severity of the tar, and as a result the time for responses from Oracle. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 8/7/2003 1:49 PM That fixed it, thank you. I still haven't heard from oracle support yet. -Original Message- Sent: Thursday, August 07, 2003 4:44 AM To: Multiple recipients of list ORACLE-L You it is a bug alter session|system set _unnest_subquery=false / Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Wednesday, August 06, 2003 7:34 PM To: Multiple recipients of list ORACLE-L While I am waiting for oracle support to respond to my tar update (2nd callback) I am just wondering if anybody has found this problem. We have the following select query (from a peoplesoft implementation) SELECT a.emplid, a.effdt FROM PS_JOB A WHERE A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT = SYSDATE) AND A.EFFSEQ = (SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A.EMPLID = A2.EMPLID AND A.EMPL_RCD = A2.EMPL_RCD AND A.EFFDT = A2.EFFDT) AND A.EMPL_STATUS = 'A' and a.emplid='3442' when we run the query we get one row back, but when we replace the field names with count(*), the resulting answer back is 2. We have tested it in 8.0.5.1.1 and we get the correct results, 1 row, and a count of 1. Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: possible Bug in Oracle 9.2.0.2
Can you please list select emplid, empl_rcd, effdt, effseq, empl_status from ps_job where emplid = '3442' At 03:34 PM 8/6/2003 -0800, you wrote: While I am waiting for oracle support to respond to my tar update (2nd callback) I am just wondering if anybody has found this problem. We have the following select query (from a peoplesoft implementation) SELECT a.emplid, a.effdt FROM PS_JOB A WHERE A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT = SYSDATE) AND A.EFFSEQ = (SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A.EMPLID = A2.EMPLID AND A.EMPL_RCD = A2.EMPL_RCD AND A.EFFDT = A2.EFFDT) AND A.EMPL_STATUS = 'A' and a.emplid='3442' when we run the query we get one row back, but when we replace the field names with count(*), the resulting answer back is 2. We have tested it in 8.0.5.1.1 and we get the correct results, 1 row, and a count of 1. Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible Bug in Oracle 9.2.0.2
Title: Message I already have :) -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 12:04 PMTo: Multiple recipients of list ORACLE-LSubject: RE: possible Bug in Oracle 9.2.0.2 Tell them ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Browett, Darren [mailto:[EMAIL PROTECTED]Sent: Thursday, August 07, 2003 2:49 PMTo: Multiple recipients of list ORACLE-LSubject: RE: possible Bug in Oracle 9.2.0.2 That fixed it, thank you. I still haven't heard from oracle support yet. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 4:44 AMTo: Multiple recipients of list ORACLE-LSubject: RE: possible Bug in Oracle 9.2.0.2 You it is a bug alter session|system set "_unnest_subquery"=false / Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Browett, Darren [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 06, 2003 7:34 PM To: Multiple recipients of list ORACLE-L Subject: possible Bug in Oracle 9.2.0.2 While I am waiting for oracle support to respond to my tar update (2nd callback) I am just wondering if anybody has found this problem. We have the following select query (from a peoplesoft implementation) SELECT a.emplid, a.effdt FROM PS_JOB A WHERE A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT = SYSDATE) AND A.EFFSEQ = (SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A.EMPLID = A2.EMPLID AND A.EMPL_RCD = A2.EMPL_RCD AND A.EFFDT = A2.EFFDT) AND A.EMPL_STATUS = 'A' and a.emplid='3442' when we run the query we get one row back, but when we replace the field names with count(*), the resulting answer back is "2". We have tested it in 8.0.5.1.1 and we get the correct results, 1 row, and a count of 1. Darren -- Darren Browett P.Eng This message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible Bug in Oracle 9.2.0.2
Title: Message Tell them ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Browett, Darren [mailto:[EMAIL PROTECTED]Sent: Thursday, August 07, 2003 2:49 PMTo: Multiple recipients of list ORACLE-LSubject: RE: possible Bug in Oracle 9.2.0.2 That fixed it, thank you. I still haven't heard from oracle support yet. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 4:44 AMTo: Multiple recipients of list ORACLE-LSubject: RE: possible Bug in Oracle 9.2.0.2 You it is a bug alter session|system set "_unnest_subquery"=false / Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Browett, Darren [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 06, 2003 7:34 PM To: Multiple recipients of list ORACLE-L Subject: possible Bug in Oracle 9.2.0.2 While I am waiting for oracle support to respond to my tar update (2nd callback) I am just wondering if anybody has found this problem. We have the following select query (from a peoplesoft implementation) SELECT a.emplid, a.effdt FROM PS_JOB A WHERE A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT = SYSDATE) AND A.EFFSEQ = (SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A.EMPLID = A2.EMPLID AND A.EMPL_RCD = A2.EMPL_RCD AND A.EFFDT = A2.EFFDT) AND A.EMPL_STATUS = 'A' and a.emplid='3442' when we run the query we get one row back, but when we replace the field names with count(*), the resulting answer back is "2". We have tested it in 8.0.5.1.1 and we get the correct results, 1 row, and a count of 1. Darren -- Darren Browett P.Eng This message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: possible to set continuation prompt in sql*plus?
Building on Jacques' solution, here is one that aligns all the statements nice and neat. Of course, it does not have the username/instance, so I don't know how acceptable it is select ' 1 ' as new_prompt from dual / set sqlprompt new_sqlprompt 1 select 1, 2 2, 3 3, 4 4, 5 5, 6 6, 7 7, 8 8, 9 9, 10 10 11 from dual; 1 2 3 4 5 6 7 8 9 10 -- -- -- -- -- -- -- -- -- -- 1 2 3 4 5 6 7 8 9 10 Jacques Kilchoer wrote: You can always set your prompt to take up two lines, but that looks ugly to me too. See example. SQL show user USER est SYSTEM SQL show sqlprompt sqlprompt SQL SQL -- change prompt to be connected username and last 4 SQL -- characters of instance name SQL column new_prompt new_value new_sqlprompt SQL select 2 user || '@' || chr (10) || 3 substr (instance_name, length (instance_name) - 3) || '' 4 as new_prompt 5 from v$instance ; NEW_PROMPT - SYSTEM@ jrk1 SQL set sqlprompt new_sqlprompt SYSTEM@ jrk1select * 2 from dual ; D - X SYSTEM@ jrk1 -Original Message- From: Pardee, Roy E [mailto:[EMAIL PROTECTED] Apologies--I wasn't clear in my original post. Right now I'm getting: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; What I'd *really* like to have is: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; That way I could copy/paste sql commands as easily as I could when my prompt was just SQL . Setting sqlnumber off gets me: [EMAIL PROTECTED] select username [EMAIL PROTECTED] from dba_users [EMAIL PROTECTED] where username like '%MC%' ; Which isn't horrible, although I do miss the numbers. But no matter--I'll just live with it... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
RE: possible to set continuation prompt in sql*plus?
Roy, You got me thinking on a tangent. How can I cut and paste the whole multi-line SQL statement at once and have it useable. Now you can, of course, use sqlplus in silent mode (-s), but that's a little too silent for my tastes. So how 'bout set sqlnumber off sqlprompt ' '. That way you get: select blah, deblah from blahde where blah != deblah and ... Nicely cutable pasteable, just thought I'd share. Mike Hand -Original Message- Sent: Tuesday, July 22, 2003 6:19 PM To: Multiple recipients of list ORACLE-L Apologies--I wasn't clear in my original post. Right now I'm getting: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; What I'd *really* like to have is: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; That way I could copy/paste sql commands as easily as I could when my prompt was just SQL . Setting sqlnumber off gets me: [EMAIL PROTECTED] select username [EMAIL PROTECTED] from dba_users [EMAIL PROTECTED] where username like '%MC%' ; Which isn't horrible, although I do miss the numbers. But no matter--I'll just live with it... Many thanks! -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
set sqlprompt ^G Will make it less quiet. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 4:19 PM To: Multiple recipients of list ORACLE-L Roy, You got me thinking on a tangent. How can I cut and paste the whole multi-line SQL statement at once and have it useable. Now you can, of course, use sqlplus in silent mode (-s), but that's a little too silent for my tastes. So how 'bout set sqlnumber off sqlprompt ' '. That way you get: select blah, deblah from blahde where blah != deblah and ... Nicely cutable pasteable, just thought I'd share. Mike Hand -Original Message- Sent: Tuesday, July 22, 2003 6:19 PM To: Multiple recipients of list ORACLE-L Apologies--I wasn't clear in my original post. Right now I'm getting: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; What I'd *really* like to have is: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; That way I could copy/paste sql commands as easily as I could when my prompt was just SQL . Setting sqlnumber off gets me: [EMAIL PROTECTED] select username [EMAIL PROTECTED] from dba_users [EMAIL PROTECTED] where username like '%MC%' ; Which isn't horrible, although I do miss the numbers. But no matter--I'll just live with it... Many thanks! -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
possible to set continuation prompt in sql*plus?
Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? Thanks! -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
The command is set sqlprompt. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 22, 2003 12:45 PM To: Multiple recipients of list ORACLE-L Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? Thanks! -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: possible to set continuation prompt in sql*plus?
Sure, just set the following (assuming 9i) SET SQLNUMBER OFF HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 12:44 PM Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? Thanks! -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
I got the impression that the question is if sql plus has the equivalent of the Unix PS1, PS2, PS3, PS4 prompts. -Original Message- The command is set sqlprompt. -Original Message- Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
I think so too. I think sqlcontinue and sqlnumber is what this guy is looking for. -Original Message- Sent: Tuesday, July 22, 2003 1:50 PM To: Multiple recipients of list ORACLE-L I got the impression that the question is if sql plus has the equivalent of the Unix PS1, PS2, PS3, PS4 prompts. -Original Message- The command is set sqlprompt. -Original Message- Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). Many thanks to all who responded. Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, July 22, 2003 11:15 AM To: Multiple recipients of list ORACLE-L I think so too. I think sqlcontinue and sqlnumber is what this guy is looking for. -Original Message- Sent: Tuesday, July 22, 2003 1:50 PM To: Multiple recipients of list ORACLE-L I got the impression that the question is if sql plus has the equivalent of the Unix PS1, PS2, PS3, PS4 prompts. -Original Message- The command is set sqlprompt. -Original Message- Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: possible to set continuation prompt in sql*plus?
I suggested using SET SQLNUMBER OFF. This sure works in SQL*Plus 8i and above; not sure if it does in 8.0.6 and I don't have a test executable to test it. But have you tried it? Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 3:14 PM That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). Many thanks to all who responded. Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, July 22, 2003 11:15 AM To: Multiple recipients of list ORACLE-L I think so too. I think sqlcontinue and sqlnumber is what this guy is looking for. -Original Message- Sent: Tuesday, July 22, 2003 1:50 PM To: Multiple recipients of list ORACLE-L I got the impression that the question is if sql plus has the equivalent of the Unix PS1, PS2, PS3, PS4 prompts. -Original Message- The command is set sqlprompt. -Original Message- Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
sqlcontinue changes the prompt for the continuation of a SQL*Plus command, not a SQL command. sqlnumber off will mean that instead of having number prompts on the continuation of a SQL statement the SQL prompt will be continued (which I personally find annoying). set sqlnumber off should eliminate the problem of the indented first line, but you won't get a continuation prompt at all. Example (using SQL*Plus 8.1.7): SQL -- continuation of a SQL*Plus command. SQL prompt - Hello World Hello World SQL set sqlcontinue Next SQL prompt - Next Hello World Hello World SQL -- +++ SQL -- continuation of a SQL command SQL select * 2 from dual where 1 = 2 ; aucune ligne sélectionnée SQL set sqlnumber off SQL select * SQL from dual where 1 = 2 ; aucune ligne sélectionnée -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: mardi, 22. juillet 2003 12:44 To: Multiple recipients of list ORACLE-L Subject: Re: possible to set continuation prompt in sql*plus? I suggested using SET SQLNUMBER OFF. This sure works in SQL*Plus 8i and above; not sure if it does in 8.0.6 and I don't have a test executable to test it. But have you tried it? Arup Nanda - Original Message - That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
If that's the impression, then no, there's only one, sqlprompt. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 22, 2003 1:50 PM To: Multiple recipients of list ORACLE-L I got the impression that the question is if sql plus has the equivalent of the Unix PS1, PS2, PS3, PS4 prompts. -Original Message- The command is set sqlprompt. -Original Message- Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: possible to set continuation prompt in sql*plus?
Setting SQLNUMBER OFF will make the SQLPROMPT continuation prompt, not completely eliminate it. Isn't it what the OP wanted in the first place? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 3:59 PM sqlcontinue changes the prompt for the continuation of a SQL*Plus command, not a SQL command. sqlnumber off will mean that instead of having number prompts on the continuation of a SQL statement the SQL prompt will be continued (which I personally find annoying). set sqlnumber off should eliminate the problem of the indented first line, but you won't get a continuation prompt at all. Example (using SQL*Plus 8.1.7): SQL -- continuation of a SQL*Plus command. SQL prompt - Hello World Hello World SQL set sqlcontinue Next SQL prompt - Next Hello World Hello World SQL -- +++ SQL -- continuation of a SQL command SQL select * 2 from dual where 1 = 2 ; aucune ligne sélectionnée SQL set sqlnumber off SQL select * SQL from dual where 1 = 2 ; aucune ligne sélectionnée -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: mardi, 22. juillet 2003 12:44 To: Multiple recipients of list ORACLE-L Subject: Re: possible to set continuation prompt in sql*plus? I suggested using SET SQLNUMBER OFF. This sure works in SQL*Plus 8i and above; not sure if it does in 8.0.6 and I don't have a test executable to test it. But have you tried it? Arup Nanda - Original Message - That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
Yes, what I meant is that the continuation prompt will be identical to the first line prompt - meaning that there is no SEPARATE continuation prompt. -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: mardi, 22. juillet 2003 14:25 To: Multiple recipients of list ORACLE-L Subject: Re: possible to set continuation prompt in sql*plus? Setting SQLNUMBER OFF will make the SQLPROMPT continuation prompt, not completely eliminate it. Isn't it what the OP wanted in the first place? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 3:59 PM sqlcontinue changes the prompt for the continuation of a SQL*Plus command, not a SQL command. sqlnumber off will mean that instead of having number prompts on the continuation of a SQL statement the SQL prompt will be continued (which I personally find annoying). set sqlnumber off should eliminate the problem of the indented first line, but you won't get a continuation prompt at all. Example (using SQL*Plus 8.1.7): SQL -- continuation of a SQL*Plus command. SQL prompt - Hello World Hello World SQL set sqlcontinue Next SQL prompt - Next Hello World Hello World SQL -- +++ SQL -- continuation of a SQL command SQL select * 2 from dual where 1 = 2 ; aucune ligne sélectionnée SQL set sqlnumber off SQL select * SQL from dual where 1 = 2 ; aucune ligne sélectionnée -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: mardi, 22. juillet 2003 12:44 To: Multiple recipients of list ORACLE-L Subject: Re: possible to set continuation prompt in sql*plus? I suggested using SET SQLNUMBER OFF. This sure works in SQL*Plus 8i and above; not sure if it does in 8.0.6 and I don't have a test executable to test it. But have you tried it? Arup Nanda - Original Message - That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
Apologies--I wasn't clear in my original post. Right now I'm getting: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; What I'd *really* like to have is: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; That way I could copy/paste sql commands as easily as I could when my prompt was just SQL . Setting sqlnumber off gets me: [EMAIL PROTECTED] select username [EMAIL PROTECTED] from dba_users [EMAIL PROTECTED] where username like '%MC%' ; Which isn't horrible, although I do miss the numbers. But no matter--I'll just live with it... Many thanks! -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, July 22, 2003 2:25 PM To: Multiple recipients of list ORACLE-L Setting SQLNUMBER OFF will make the SQLPROMPT continuation prompt, not completely eliminate it. Isn't it what the OP wanted in the first place? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 3:59 PM sqlcontinue changes the prompt for the continuation of a SQL*Plus command, not a SQL command. sqlnumber off will mean that instead of having number prompts on the continuation of a SQL statement the SQL prompt will be continued (which I personally find annoying). set sqlnumber off should eliminate the problem of the indented first line, but you won't get a continuation prompt at all. Example (using SQL*Plus 8.1.7): SQL -- continuation of a SQL*Plus command. SQL prompt - Hello World Hello World SQL set sqlcontinue Next SQL prompt - Next Hello World Hello World SQL -- +++ SQL -- continuation of a SQL command SQL select * 2 from dual where 1 = 2 ; aucune ligne sélectionnée SQL set sqlnumber off SQL select * SQL from dual where 1 = 2 ; aucune ligne sélectionnée -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: mardi, 22. juillet 2003 12:44 To: Multiple recipients of list ORACLE-L Subject: Re: possible to set continuation prompt in sql*plus? I suggested using SET SQLNUMBER OFF. This sure works in SQL*Plus 8i and above; not sure if it does in 8.0.6 and I don't have a test executable to test it. But have you tried it? Arup Nanda - Original Message - That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
You can always set your prompt to take up two lines, but that looks ugly to me too. See example. SQL show user USER est SYSTEM SQL show sqlprompt sqlprompt SQL SQL -- change prompt to be connected username and last 4 SQL -- characters of instance name SQL column new_prompt new_value new_sqlprompt SQL select 2 user || '@' || chr (10) || 3 substr (instance_name, length (instance_name) - 3) || '' 4 as new_prompt 5 from v$instance ; NEW_PROMPT - SYSTEM@ jrk1 SQL set sqlprompt new_sqlprompt SYSTEM@ jrk1select * 2 from dual ; D - X SYSTEM@ jrk1 -Original Message- From: Pardee, Roy E [mailto:[EMAIL PROTECTED] Apologies--I wasn't clear in my original post. Right now I'm getting: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; What I'd *really* like to have is: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; That way I could copy/paste sql commands as easily as I could when my prompt was just SQL . Setting sqlnumber off gets me: [EMAIL PROTECTED] select username [EMAIL PROTECTED] from dba_users [EMAIL PROTECTED] where username like '%MC%' ; Which isn't horrible, although I do miss the numbers. But no matter--I'll just live with it... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is range partitioning possible on part of varchar2 column ???
This seems to be a good idea. I will see if this is acceptable to my people.
Earliar I suggested to change to date field, and was not acceptable for them
as there seems plenty of code needs to be changed. I will see if this change
is acceptable for them.
One thing I could understand clearly from the LIST MEMBERS is that it is not
at all possible to range partition without changing the column
type/contents. I have two options now, one with what you suggested.
Thanks for your help and thanks for all those who replied.
-- Babu
-Original Message-
Sent: Thursday, March 13, 2003 8:34 PM
To: Multiple recipients of list ORACLE-L
???
Babu,
On a slightly different approach, is it possible to update the column to the
format MON, from the present MON? If so, then there is hope. You
could create the partitions like this
PARTITIONING BY RANGE (REPORT_CYCLE_CD)
(
PARTITION P1998 VALUES LESS THAN ('1999%'),
PARTITION P1999 VALUES LESS THAN ('2000%'),
PARTITION P2000 VALUES LESS THAN ('2001%'),
.
PARTITION PMAX VALUES LESS THAN (maxvalue)
)
Hope this helps.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, March 13, 2003 5:19 PM
Babu
I don't think partitions are clearly documented anywhere. Here is some SQL
that works so you can see how to use a date function. It partitions on two
columns, but I wanted you to see something that works.
add partition sum_fy_28
values less than ('FY', to_date('02012003','mmdd'))
tablespace data_fy_28
-Original Message-
Sent: Thursday, March 13, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
??
Dear List,
I have a table of size approx 10gig, and I need to partition based on the
YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
data in the column of format MON . I need to partition the table
based
on the year , that is, substr(report_cycle_cd, 4,4).
Substr function doesn't seem to be permitted in the partitioning syntax
and
so am getting errors. Only TO_DATE function seems to be permitted. Since
it
is not a date column, I would like to know if there is a way to RANGE
partition the table, instead of HASH partitioning.
Appreciate any suggestions.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Arup Nanda
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Is range partitioning possible on part of varchar2 column ??????
Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is range partitioning possible on part of varchar2 column ???
Babu
I don't think partitions are clearly documented anywhere. Here is some SQL
that works so you can see how to use a date function. It partitions on two
columns, but I wanted you to see something that works.
add partition sum_fy_28
values less than ('FY', to_date('02012003','mmdd'))
tablespace data_fy_28
-Original Message-
Sent: Thursday, March 13, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
??
Dear List,
I have a table of size approx 10gig, and I need to partition based on the
YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
data in the column of format MON . I need to partition the table based
on the year , that is, substr(report_cycle_cd, 4,4).
Substr function doesn't seem to be permitted in the partitioning syntax and
so am getting errors. Only TO_DATE function seems to be permitted. Since it
is not a date column, I would like to know if there is a way to RANGE
partition the table, instead of HASH partitioning.
Appreciate any suggestions.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Is range partitioning possible on part of varchar2 column ??????
No, I think you will have to add a column to store '' separately in order to partition on it. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:14 PM Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is range partitioning possible on part of varchar2 column ???
Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is range partitioning possible on part of varchar2 column ???
Is list partitioning available in 8i? Iam on 8.1.7.4. -- Babu -Original Message- Sent: Thursday, March 13, 2003 1:49 PM To: Multiple recipients of list ORACLE-L ??? Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is range partitioning possible on part of varchar2 column ???
No -Original Message- Sent: Thursday, March 13, 2003 5:49 PM To: Multiple recipients of list ORACLE-L ??? Is list partitioning available in 8i? Iam on 8.1.7.4. -- Babu -Original Message- Sent: Thursday, March 13, 2003 1:49 PM To: Multiple recipients of list ORACLE-L ??? Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Is range partitioning possible on part of varchar2 column ???
Babu,
On a slightly different approach, is it possible to update the column to the
format MON, from the present MON? If so, then there is hope. You
could create the partitions like this
PARTITIONING BY RANGE (REPORT_CYCLE_CD)
(
PARTITION P1998 VALUES LESS THAN ('1999%'),
PARTITION P1999 VALUES LESS THAN ('2000%'),
PARTITION P2000 VALUES LESS THAN ('2001%'),
.
PARTITION PMAX VALUES LESS THAN (maxvalue)
)
Hope this helps.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, March 13, 2003 5:19 PM
Babu
I don't think partitions are clearly documented anywhere. Here is some SQL
that works so you can see how to use a date function. It partitions on two
columns, but I wanted you to see something that works.
add partition sum_fy_28
values less than ('FY', to_date('02012003','mmdd'))
tablespace data_fy_28
-Original Message-
Sent: Thursday, March 13, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
??
Dear List,
I have a table of size approx 10gig, and I need to partition based on the
YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
data in the column of format MON . I need to partition the table
based
on the year , that is, substr(report_cycle_cd, 4,4).
Substr function doesn't seem to be permitted in the partitioning syntax
and
so am getting errors. Only TO_DATE function seems to be permitted. Since
it
is not a date column, I would like to know if there is a way to RANGE
partition the table, instead of HASH partitioning.
Appreciate any suggestions.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Arup Nanda
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Middle Tier spawning sessions, possible performance issues
Title: RE: Middle Tier spawning sessions, possible performance issues Anjo, Actually I saw this in your (et al) famous YAPP paper. Still curious, Steve -Original Message-From: Orr, Steve Sent: Thursday, January 30, 2003 10:32 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Middle Tier spawning sessions, possible performance issues I read somewhere that for apps that constantly logon/logoff (like web apps), one possible thing to do is increase the cache size for the AUDSES$ sequence. Anyone done this and seen improvements? Curiously, Steve Orr -Original Message- From: Anjo Kolk [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: Middle Tier spawning sessions, possible performance issues You are kind of on the right track. The number of sessions doesn't really matter. What matters is that they logon/logoff all the time. That is the worst thing that you can do in an Oracle database. Why? The session will allocate the cursors, parse them, close them everytime the session will logon/logoff. The symptoms that you will see. Latch contention (shared pool, library cache), shared pool fragmentation etc. Anjo. On Thursday 30 January 2003 05:14, [EMAIL PROTECTED] wrote: I'm far more of a developer than a DBA, but when someone told me this it set off a big red light in my head. We are using an Oracle Backend with a .net front front. One of our .net guys told me that the middle tier they are using 'spawns' sessions. We have 2 pretty distinct skillsets here so fixing the middle tier is probably way beyond my pervue. However, they told me that in a recent demo, the performance degraded overtime. Its my understanding that generally this is caused by one of two things. 1. Failure to use Bind Variables... we are using them everywhere. 2. Too many sessions. Am I on the right track here? How much would shared server mode help? This may be an enormous issue since they are expecting 250 contiguous users. Another option I tossed around was moving as much logic from the client side to the database to avoid the session spawning. I know that generally this is a good idea, but its difficult when the database people are lousy in C# and the .net people our lousy in PL/SQL and SQL. Any opinions? -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Middle Tier spawning sessions, possible performance issues
R - I haven't yet been the victim of .net (thankfully), and I hope someone with direct experience will reply. But just in case, I'll mention a couple of ideas. Try to sample the SQL that is being inflicted on Oracle. Microsoft interfaces tend to have default settings for the lowest common denominator (like accessing a flat file). I've seen them do stuff like pull the entire table over just to verify that it hasn't changed. This sort of thing can usually be corrected by using other than default settings. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 30, 2003 7:15 AM To: Multiple recipients of list ORACLE-L I'm far more of a developer than a DBA, but when someone told me this it set off a big red light in my head. We are using an Oracle Backend with a .net front front. One of our .net guys told me that the middle tier they are using 'spawns' sessions. We have 2 pretty distinct skillsets here so fixing the middle tier is probably way beyond my pervue. However, they told me that in a recent demo, the performance degraded overtime. Its my understanding that generally this is caused by one of two things. 1. Failure to use Bind Variables... we are using them everywhere. 2. Too many sessions. Am I on the right track here? How much would shared server mode help? This may be an enormous issue since they are expecting 250 contiguous users. Another option I tossed around was moving as much logic from the client side to the database to avoid the session spawning. I know that generally this is a good idea, but its difficult when the database people are lousy in C# and the .net people our lousy in PL/SQL and SQL. Any opinions? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Middle Tier spawning sessions, possible performance issues
I'm far more of a developer than a DBA, but when someone told me this it set off a big red light in my head. We are using an Oracle Backend with a .net front front. One of our .net guys told me that the middle tier they are using 'spawns' sessions. We have 2 pretty distinct skillsets here so fixing the middle tier is probably way beyond my pervue. However, they told me that in a recent demo, the performance degraded overtime. Its my understanding that generally this is caused by one of two things. 1. Failure to use Bind Variables... we are using them everywhere. 2. Too many sessions. Am I on the right track here? How much would shared server mode help? This may be an enormous issue since they are expecting 250 contiguous users. Another option I tossed around was moving as much logic from the client side to the database to avoid the session spawning. I know that generally this is a good idea, but its difficult when the database people are lousy in C# and the .net people our lousy in PL/SQL and SQL. Any opinions? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Middle Tier spawning sessions, possible performance issues
250 connections from the middle tier does sound a bit suspect, way over the top. I'm assuming 'spawns' relates to establishing connections as and when required and then disconnecting when done. I think you will find that there is quite an Oracle overhead in handling all of the connect/disconnect requests that Oracle is coping with in a non connection pooled environment. Ideally, you shouldn't be spawning connection from the middle tier. You should look at connection pooling ie. configure a fixed pool of connections per middle tier server, 20/40/whatever (and have more than one middle tier server, load balanced) and have these connect when the app/web server starts up. You then round robin the connections to the clients when required, and throw them back in the pool when finished. Ade -Original Message- Sent: 30 January 2003 14:35 To: Multiple recipients of list ORACLE-L R - I haven't yet been the victim of .net (thankfully), and I hope someone with direct experience will reply. But just in case, I'll mention a couple of ideas. Try to sample the SQL that is being inflicted on Oracle. Microsoft interfaces tend to have default settings for the lowest common denominator (like accessing a flat file). I've seen them do stuff like pull the entire table over just to verify that it hasn't changed. This sort of thing can usually be corrected by using other than default settings. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 30, 2003 7:15 AM To: Multiple recipients of list ORACLE-L I'm far more of a developer than a DBA, but when someone told me this it set off a big red light in my head. We are using an Oracle Backend with a .net front front. One of our .net guys told me that the middle tier they are using 'spawns' sessions. We have 2 pretty distinct skillsets here so fixing the middle tier is probably way beyond my pervue. However, they told me that in a recent demo, the performance degraded overtime. Its my understanding that generally this is caused by one of two things. 1. Failure to use Bind Variables... we are using them everywhere. 2. Too many sessions. Am I on the right track here? How much would shared server mode help? This may be an enormous issue since they are expecting 250 contiguous users. Another option I tossed around was moving as much logic from the client side to the database to avoid the session spawning. I know that generally this is a good idea, but its difficult when the database people are lousy in C# and the .net people our lousy in PL/SQL and SQL. Any opinions? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Adrian Roe INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Middle Tier spawning sessions, possible performance issues
You are kind of on the right track. The number of sessions doesn't really matter. What matters is that they logon/logoff all the time. That is the worst thing that you can do in an Oracle database. Why? The session will allocate the cursors, parse them, close them everytime the session will logon/logoff. The symptoms that you will see. Latch contention (shared pool, library cache), shared pool fragmentation etc. Anjo. On Thursday 30 January 2003 05:14, [EMAIL PROTECTED] wrote: I'm far more of a developer than a DBA, but when someone told me this it set off a big red light in my head. We are using an Oracle Backend with a .net front front. One of our .net guys told me that the middle tier they are using 'spawns' sessions. We have 2 pretty distinct skillsets here so fixing the middle tier is probably way beyond my pervue. However, they told me that in a recent demo, the performance degraded overtime. Its my understanding that generally this is caused by one of two things. 1. Failure to use Bind Variables... we are using them everywhere. 2. Too many sessions. Am I on the right track here? How much would shared server mode help? This may be an enormous issue since they are expecting 250 contiguous users. Another option I tossed around was moving as much logic from the client side to the database to avoid the session spawning. I know that generally this is a good idea, but its difficult when the database people are lousy in C# and the .net people our lousy in PL/SQL and SQL. Any opinions? -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Middle Tier spawning sessions, possible performance issues
Title: RE: Middle Tier spawning sessions, possible performance issues I read somewhere that for apps that constantly logon/logoff (like web apps), one possible thing to do is increase the cache size for the AUDSES$ sequence. Anyone done this and seen improvements? Curiously, Steve Orr -Original Message- From: Anjo Kolk [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: Middle Tier spawning sessions, possible performance issues You are kind of on the right track. The number of sessions doesn't really matter. What matters is that they logon/logoff all the time. That is the worst thing that you can do in an Oracle database. Why? The session will allocate the cursors, parse them, close them everytime the session will logon/logoff. The symptoms that you will see. Latch contention (shared pool, library cache), shared pool fragmentation etc. Anjo. On Thursday 30 January 2003 05:14, [EMAIL PROTECTED] wrote: I'm far more of a developer than a DBA, but when someone told me this it set off a big red light in my head. We are using an Oracle Backend with a .net front front. One of our .net guys told me that the middle tier they are using 'spawns' sessions. We have 2 pretty distinct skillsets here so fixing the middle tier is probably way beyond my pervue. However, they told me that in a recent demo, the performance degraded overtime. Its my understanding that generally this is caused by one of two things. 1. Failure to use Bind Variables... we are using them everywhere. 2. Too many sessions. Am I on the right track here? How much would shared server mode help? This may be an enormous issue since they are expecting 250 contiguous users. Another option I tossed around was moving as much logic from the client side to the database to avoid the session spawning. I know that generally this is a good idea, but its difficult when the database people are lousy in C# and the .net people our lousy in PL/SQL and SQL. Any opinions? -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Middle Tier spawning sessions, possible performance issues
Title: RE: Middle Tier spawning sessions, possible performance issues Any takers on this ?? -Original Message-From: Orr, Steve Sent: Thursday, January 30, 2003 10:32 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Middle Tier spawning sessions, possible performance issues I read somewhere that for apps that constantly logon/logoff (like web apps), one possible thing to do is increase the cache size for the AUDSES$ sequence.Anyone done this and seen improvements? Curiously, Steve Orr -Original Message- From: Anjo Kolk [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: Middle Tier spawning sessions, possible performance issues You are kind of on the right track. The number of sessions doesn't really matter. What matters is that they logon/logoff all the time. That is the worst thing that you can do in an Oracle database. Why? The session will allocate the cursors, parse them, close them everytime the session will logon/logoff. The symptoms that you will see. Latch contention (shared pool, library cache), shared pool fragmentation etc. Anjo. On Thursday 30 January 2003 05:14, [EMAIL PROTECTED] wrote: I'm far more of a developer than a DBA, but when someone told me this it set off a big red light in my head. We are using an Oracle Backend with a .net front front. One of our .net guys told me that the middle tier they are using 'spawns' sessions. We have 2 pretty distinct skillsets here so fixing the middle tier is probably way beyond my pervue. However, they told me that in a recent demo, the performance degraded overtime. Its my understanding that generally this is caused by one of two things. 1. Failure to use Bind Variables... we are using them everywhere. 2. Too many sessions. Am I on the right track here? How much would shared server mode help? This may be an enormous issue since they are expecting 250 contiguous users. Another option I tossed around was moving as much logic from the client side to the database to avoid the session spawning. I know that generally this is a good idea, but its difficult when the database people are lousy in C# and the .net people our lousy in PL/SQL and SQL. Any opinions? -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Follow-up: It's NOT possible to set role in db's logon
Hi Roy, I don't know if you solved your problem yet but I have a similar situation here. I have an Oracle account used by PHP programs (third party programs) to access some tables. I don't want anyone to log in to the database with this account unless the connection comes from apache and from our web server machine. So what I did is that I created a logon trigger on that schema and if the conditions are not met, then I raise an application error and the connection dies. As you know, these informations (program, machine, etc...) can be found in v$session and the SID of the current session can be found with select sid from v$mystat where rownum = 1. HTH. Louis At 15:13 2003-01-27 -0800, you wrote: In case anyone cares--it looks like it is *not* possible to set a role in an after logon trigger. Had I only looked at metalink: AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOTp_id=106140.1 Bummer, that. Thanks again to all who responded. Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, January 13, 2003 7:42 AM To: 'ORACLE-L' Greetings all, I'm trying to support a COTS application that is back-end agnostic makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus. My collegues I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on off in conjunction with users opening closing the client program. This works, but is a pain to maintain. I've recently discovered the v$session.program field am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client. Can anybody comment as to whether this is a viable approach on an 8.1.6 database if not, on a 9i db? In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time. Second, whether the SET ROLE statement is legal in a logon trigger. All help will be most welcome. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Louis Brouillette Analyste en informatique (DBA) Universite du Quebec a Trois-Rivieres Tel: (819) 376-5011 ext. 2435 Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Louis BROUILLETTE INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Follow-up: It's NOT possible to set role in db's logon
That's a great idea--many thanks. I bet I could put up a table of permitted username/client program combinations just do a SELECT from it translate the no_data_found exception into a 'connect via your program verboten!' message... Thanks again, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, January 29, 2003 6:04 AM To: Multiple recipients of list ORACLE-L Hi Roy, I don't know if you solved your problem yet but I have a similar situation here. I have an Oracle account used by PHP programs (third party programs) to access some tables. I don't want anyone to log in to the database with this account unless the connection comes from apache and from our web server machine. So what I did is that I created a logon trigger on that schema and if the conditions are not met, then I raise an application error and the connection dies. As you know, these informations (program, machine, etc...) can be found in v$session and the SID of the current session can be found with select sid from v$mystat where rownum = 1. HTH. Louis At 15:13 2003-01-27 -0800, you wrote: In case anyone cares--it looks like it is *not* possible to set a role in an after logon trigger. Had I only looked at metalink: AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data b ase_id=NOTp_id=106140.1 Bummer, that. Thanks again to all who responded. Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, January 13, 2003 7:42 AM To: 'ORACLE-L' Greetings all, I'm trying to support a COTS application that is back-end agnostic makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus. My collegues I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on off in conjunction with users opening closing the client program. This works, but is a pain to maintain. I've recently discovered the v$session.program field am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client. Can anybody comment as to whether this is a viable approach on an 8.1.6 database if not, on a 9i db? In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time. Second, whether the SET ROLE statement is legal in a logon trigger. All help will be most welcome. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Louis Brouillette Analyste en informatique (DBA) Universite du Quebec a Trois-Rivieres Tel: (819) 376-5011 ext. 2435 Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Louis BROUILLETTE INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from
Re: Follow-up: It's NOT possible to set role in db's logon trigger
Roy,
I missed the first run of the question, so you've
probably had this answer already.
You can set a role inside a procedure if
the procedure is declared with invoker
rights (authid current_user) although the
procedure cannot then be run in a logon
trigger.
However, in Oracle 9, you could define the
role as an application role protected by
a package. If COTS attaches to the database
using OCI, then you could consider using
the PROXY_USER features. In this case,
COTS connects as itself, then becomes
the end-user, without knowing the end-user
password. Your package could then set
the role based on the fact that
sys_context('userenv','proxy_user')
was 'COTS'.
When the user logs in normally, their
'proxy_user' value will be null. The only
way that they could switch on the role
would be to write their own OCI program
that logged on as COTS first - which means
they'd have to know the COTS password
anyway, so your data would have been
unprotected anyway.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March
USA_(FL)_May
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 27 January 2003 23:48
trigger
In case anyone cares--it looks like it is *not* possible to set a role
in an
after logon trigger. Had I only looked at metalink:
AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles
Enabled
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p
_datab
ase_id=NOTp_id=106140.1
Bummer, that.
Thanks again to all who responded.
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Follow-up: It's NOT possible to set role in db's logon trigger
In case anyone cares--it looks like it is *not* possible to set a role in an after logon trigger. Had I only looked at metalink: AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOTp_id=106140.1 Bummer, that. Thanks again to all who responded. Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, January 13, 2003 7:42 AM To: 'ORACLE-L' Greetings all, I'm trying to support a COTS application that is back-end agnostic makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus. My collegues I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on off in conjunction with users opening closing the client program. This works, but is a pain to maintain. I've recently discovered the v$session.program field am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client. Can anybody comment as to whether this is a viable approach on an 8.1.6 database if not, on a 9i db? In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time. Second, whether the SET ROLE statement is legal in a logon trigger. All help will be most welcome. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 from 7.3.4 to 8.0.6 - Possible Solution - Comments?
Title: Message Chris, That column is added to the table when upgrading to 7.3.3 release, by cat7303.sql script.It is possible that this script was not run after opening the database under 7.3.3. If you are notusing Replication, I don't think you need to run catrep8m script. If you reviewed Note# 47290.1 on Metalink, you will find your answer :) I don't think your action (adding this column to the dictionary table)was wrong. Good Luck, - Kirti -Original Message-From: Bowes, Chris [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 21, 2003 3:21 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Upgrade from 7.3.4 to 8.0.6 - Possible Solution - Comments? I found a way around the problem. I don't think Oracle will like it... I hunted down the offending table that was missing the column DELIVERY_ORDER and added it to that dictionary table. I then re-ran the scripts and they rebuilt without incident. This was on the test system of course. The offending table was system.repcat$_repprop. On all other databases, this table has that column and has no rows in it and on the problem base, it doesn't have the column, but it also has no rows in it. I know that doesn't mean we aren't using that table, but, does anyone know when it gets used? Is what I did to fix this acceptable? Changing dictionary tables is a major red flag, so before I even think of trying to push for this solution, I want any and all thoughts on this. Thanks again. --Chris -Original Message-From: Bowes, Chris [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 10:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Upgrade from 7.3.4 to 8.0.6 Hi Hemant, Thank you for your response. I am calling it directly. I do the startup nomount, alter database convert, alter database open resetlogs. Then I fire off the cat8000 and then the catrep8m from svrmgrl. I think this is a problem in my dictionary in 7.3.4 as when I recreate the test base and do an export/import and convert the base, it goes flawlessly. This base has limited downtime available, so I cannot do that to the live base. Oracle support has told me to look at offline datafiles, but this is in the system tablespace, so if that file was offline or needed recovery, the base would be dead. Any thoughts? Thank you again, --Chris -Original Message-From: Hemant K Chitale [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 8:55 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Upgrade from 7.3.4 to 8.0.6Are you calling the catrep8m script directly ? Or is it being calledfrom some other script [eg catproc.sql ?].Are you using Advanced Replication ?HemantAt 02:28 PM 20-01-03 -0800, you wrote: Hi everyone! Has anyone seen this and know a solution? I am upgrading a base from 7.3.4 to 8.0.6. Everything appears to go fine until the catrep8m script. In that script I get several errors:Statement processed. (P.delivery_order C.cscn) * ORA-00904: invalid column name grant select on defcalldest to select_catalog_role * ORA-00942: table or view does not exist comment on table DEFCALLDEST is * ORA-00942: table or view does not exist comment on column DEFCALLDEST.CALLNO is * ORA-00942: table or view does not exist comment on column DEFCALLDEST.DEFERRED_TRAN_ID is * ORA-00942: table or view does not exist comment on column DEFCALLDEST.DBLINK is * ORA-00942: table or view does not exist DROP PUBLIC SYNONYM defcalldest * ORA-01432: public synonym to be dropped does not exist Statement processed. OR (P.delivery_order C.cscn * ORA-00904: invalid column name Statement processed. Statement processed. Statement processed. This then forces several dictionary packages invalid and they wont' recompile. I have tried rebuilding the dictionary before I upgrade and rebuilding after I upgrade. Neither one seems to work. When I rebuild, the catrep script gives that same error.So far Oracle hasn't been able to find it. Has anyone seen this and know the fix? Thanks in advance. --Chris [EMAIL PROTECTED] Hemant K ChitaleMy web site page is : http://hkchital.tripod.com
8.1.6: possible to set role in db's logon trigger?
Greetings all, I'm trying to support a COTS application that is back-end agnostic makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus. My collegues I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on off in conjunction with users opening closing the client program. This works, but is a pain to maintain. I've recently discovered the v$session.program field am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client. Can anybody comment as to whether this is a viable approach on an 8.1.6 database if not, on a 9i db? In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time. Second, whether the SET ROLE statement is legal in a logon trigger. All help will be most welcome. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
