RE: DB upgrade problems from 8.1.7.3 to 8.1.7.4
Try replacing "?" with your ORACLE_HOME directory ... HTH, Remco -Oorspronkelijk bericht- Van: Valdis Erglis [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 11 juni 2002 16:43 Aan: Multiple recipients of list ORACLE-L Onderwerp: DB upgrade problems from 8.1.7.3 to 8.1.7.4 Hello! We were trying to upgrade from 8.1.7.3 to 8.1.7.4 on AIX (32 bit) and Linux (Intel). Just software upgrade was o.k. - no problems at all, but attempt to upgrade the database was unsuccessfull. We followed all steps as described in patch set notes - * disabling system triggers * select * from duc$ where PACK='JIS$INTERCEPTOR$' ; -- nothing * ALTER SESSION SET EVENTS '10520 TRACE NAME CONTEXT FOREVER, LEVEL 10'; and after that we should run catalog, catproc and catrep scripts, but after invoking to run catalog script "?/rdbms/admin/catalog.sql" - nothing happens, system does not returns to SQL prompt, OS is absolutely idle. Normally catalog script very fast compiles packages and views, and prompts about it like PACKAGE CREATED VIEW CREATED ... The same happens trying to upgrade on Linux from 8.1.7.3 to 8.1.7.4 Does anybody else experiencing the same problems? Any suggestions? Workaround could be to export a DB under 8.1.7.3, upgrade Oracle software, create DB under 8.1.7.4 and import DB from dump, but it is a silly way. Thanks! Valdis _ Sign up for FREE email from RT.NL at http://www.rt.nl/ _ Promote your group and strengthen ties to your members with [EMAIL PROTECTED] by Everyone.net http://www.everyone.net/?btn=tag -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Valdis Erglis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: "snapshot too old" error - strange
Hi Andrey, Are there any LOB columns in the tables ? I read something on Metalink about bugs on ORA-01555 messages (followed by a ORA-22924), on tables with LOBs ... HTH, Remco -Oorspronkelijk bericht- Van: Andrey Bronfin [mailto:[EMAIL PROTECTED]] Verzonden: maandag 27 mei 2002 14:53 Aan: Multiple recipients of list ORACLE-L Onderwerp: "snapshot too old" error - strange Dear list ! There is something strange going on in my production DB. There is a program that reads fom 2 huge tables (A and B - select only) and writes a fraction of records into some third table (let's call it C - inserts only). Now , NO ONE carries a DML agains A or B . But occasionally i get the Ora-1555 - "snapshot too old" error during the run of the aforementioned program. Any ideas , please ? TIA DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Smart Update, How ?
Can't you extract the update out of the cursor loop, and build a new cursor just for this update, in which you exclude the rows with "XXX is null" ? If you execute both loops, the result would be the same ... -Oorspronkelijk bericht- Van: Steven Joshua [mailto:[EMAIL PROTECTED]] Verzonden: woensdag 13 maart 2002 15:53 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: Smart Update, How ? No, I can't do that. The whole row contains a lot of other information for other updates. Any other ideas? Steven --- "Daemen, Remco" <[EMAIL PROTECTED]> wrote: > Hi Steven, > > Exclude the rows with "XXX is null" in the cursor > definition. Does that > get you what you want ? > > HTH, Remco > > -Oorspronkelijk bericht- > Van: steven wndy [mailto:[EMAIL PROTECTED]] > Verzonden: dinsdag 12 maart 2002 22:34 > Aan: Multiple recipients of list ORACLE-L > Onderwerp: Smart Update, How ? > > > Hello list: > > In my cursor loop, I have an update statement, see > below. Now I need to the update only for the field > (in > that cursor) that has data in it. (which means when > is > null, that field will not be updated). > - Dynamic SQL can do it. But now for many reasons > that > I can't use Dynamic SQL in this code. Hard for other > people to maintain > - And I believe use multiple "if XXX is not null > then > update ...", this is accomplishable. But that will > be > a lot of "if .. then ... update ..." statement. > > Is there a smarter way to do this update? I don't > know > if DECODE can be used here. at least I don't know > how. > Any ideas? Many Thanks > > > UPDATE KOMP > SET C_INVENTORY_DATE = > DECODE(rec_UpdIES.PHYINVENTORYDATE, NULL, NULL, > TO_DATE(rec_UpdIES.PHYINVENTORYDATE, 'MMDD')), > KFLDC03 = RTRIM(rec_UpdIES.RESPONSIBLEDEPT), > KFLDC24 = RTRIM(rec_UpdIES.COMPBILLCODE), > KFLDA01 = DECODE(RTRIM(rec_UpdIES.DATEINSTALLED), > NULL, NULL, TO_DATE(RTRIM(rec_UpdIES.DATEINSTALLED), > 'MMDD')), > KFLDA04 = DECODE(RTRIM(rec_UpdIES.DATERECEIVED), > NULL, > TO_DATE(TO_CHAR(SYSDATE, 'MMDD'), 'MMDD'), > TO_DATE(RTRIM(rec_UpdIES.DATERECEIVED), > 'MMDD')), > KFLDC11 = RTRIM(rec_UpdIES.MAINTPONUMBER), > > > KFLDC12 = RTRIM(rec_UpdIES.MAINTPOLINE), > > > KFLDC13 = RTRIM(rec_UpdIES.MAINTRELEASENO), > > > KFLDC14 = RTRIM(rec_UpdIES.MAINTVENDOR), > > > GARANTBIS = > TO_DATE(RTRIM(rec_UpdIES.WARRANTYENDDATE), > 'MMDD'), > C_BILLCODE = V_BILLCODE, > ACQUISITIONMODE = rec_UpdIES.OWNERSHIPCODE, > USERCHG = 'RECEIPT', > DATCHG = > TO_CHAR(SYSDATE,'-MM-DD-HH24.MI.SS')||'.00' > > > WHERE in_IDENT = IDENT; > > > __ > Do You Yahoo!? > Try FREE Yahoo! Mail - the world's greatest free > email! > http://mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: steven wndy > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > -------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Daemen, Remco > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free emai
RE: Smart Update, How ?
Hi Steven, Exclude the rows with "XXX is null" in the cursor definition. Does that get you what you want ? HTH, Remco -Oorspronkelijk bericht- Van: steven wndy [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 12 maart 2002 22:34 Aan: Multiple recipients of list ORACLE-L Onderwerp: Smart Update, How ? Hello list: In my cursor loop, I have an update statement, see below. Now I need to the update only for the field (in that cursor) that has data in it. (which means when is null, that field will not be updated). - Dynamic SQL can do it. But now for many reasons that I can't use Dynamic SQL in this code. Hard for other people to maintain - And I believe use multiple "if XXX is not null then update ...", this is accomplishable. But that will be a lot of "if .. then ... update ..." statement. Is there a smarter way to do this update? I don't know if DECODE can be used here. at least I don't know how. Any ideas? Many Thanks UPDATE KOMP SET C_INVENTORY_DATE = DECODE(rec_UpdIES.PHYINVENTORYDATE, NULL, NULL, TO_DATE(rec_UpdIES.PHYINVENTORYDATE, 'MMDD')), KFLDC03 = RTRIM(rec_UpdIES.RESPONSIBLEDEPT), KFLDC24 = RTRIM(rec_UpdIES.COMPBILLCODE), KFLDA01 = DECODE(RTRIM(rec_UpdIES.DATEINSTALLED), NULL, NULL, TO_DATE(RTRIM(rec_UpdIES.DATEINSTALLED), 'MMDD')), KFLDA04 = DECODE(RTRIM(rec_UpdIES.DATERECEIVED), NULL, TO_DATE(TO_CHAR(SYSDATE, 'MMDD'), 'MMDD'), TO_DATE(RTRIM(rec_UpdIES.DATERECEIVED), 'MMDD')), KFLDC11 = RTRIM(rec_UpdIES.MAINTPONUMBER), KFLDC12 = RTRIM(rec_UpdIES.MAINTPOLINE), KFLDC13 = RTRIM(rec_UpdIES.MAINTRELEASENO), KFLDC14 = RTRIM(rec_UpdIES.MAINTVENDOR), GARANTBIS = TO_DATE(RTRIM(rec_UpdIES.WARRANTYENDDATE), 'MMDD'), C_BILLCODE = V_BILLCODE, ACQUISITIONMODE = rec_UpdIES.OWNERSHIPCODE, USERCHG = 'RECEIPT', DATCHG = TO_CHAR(SYSDATE,'-MM-DD-HH24.MI.SS')||'.00' WHERE in_IDENT = IDENT; __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: steven wndy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Funny?
Well, try this:
set sqltrace on --> issue drop user command --> set sqltrace off -->
look in the trace file for the first (internal) oracle statement that
generates an ora-error, and work from there. That's how I cracked our
problem.
Remco
-Oorspronkelijk bericht-
Van: Mark Leith [mailto:[EMAIL PROTECTED]]
Verzonden: vrijdag 1 maart 2002 12:38
Aan: Multiple recipients of list ORACLE-L
Onderwerp: RE: Funny?
Nope - not using replication.
Thanks anyway..
Mark
-Original Message-
Remco
Sent: 01 March 2002 09:58
To: Multiple recipients of list ORACLE-L
Mark,
Have you used or are you using replication ? I got these messages some
time ago when someone had dropped the system replication tables without
removing the snapshot groups ...
HTH, Remco
-Oorspronkelijk bericht-
Van: Mark Leith [mailto:[EMAIL PROTECTED]]
Verzonden: vrijdag 1 maart 2002 1:33
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Funny?
Hi All,
Anyone ever seen this before? 8.1.7 on a Win2K machine.
SQL> select username,
2 account_status
3from dba_users
4 where username in ('MARK','SCOTT');
USERNAME ACCOUNT_STATUS
--
SCOTT OPEN
MARK OPEN
SQL> drop user scott cascade;
drop user scott cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
I can't drop *ANY* users! Not even logged in as SYS!
Cheers
Mark
===
Mark Leith | T: +44 (0)1905 330 281
Sales & Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: [EMAIL PROTECTED]
===
http://www.cool-tools.co.uk
Maximising throughput & performance
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Daemen, Remco
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Daemen, Remco
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Funny?
Mark,
Have you used or are you using replication ? I got these messages some
time ago when someone had dropped the system replication tables without
removing the snapshot groups ...
HTH, Remco
-Oorspronkelijk bericht-
Van: Mark Leith [mailto:[EMAIL PROTECTED]]
Verzonden: vrijdag 1 maart 2002 1:33
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Funny?
Hi All,
Anyone ever seen this before? 8.1.7 on a Win2K machine.
SQL> select username,
2 account_status
3from dba_users
4 where username in ('MARK','SCOTT');
USERNAME ACCOUNT_STATUS
--
SCOTT OPEN
MARK OPEN
SQL> drop user scott cascade;
drop user scott cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
I can't drop *ANY* users! Not even logged in as SYS!
Cheers
Mark
===
Mark Leith | T: +44 (0)1905 330 281
Sales & Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: [EMAIL PROTECTED]
===
http://www.cool-tools.co.uk
Maximising throughput & performance
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Daemen, Remco
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Sql question
Thomas, "NOT EXISTS" and "<>" equals "must be at least one" Right ? That's not what Zsolt wants ... :-) -Oorspronkelijk bericht- Van: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 12 februari 2002 14:28 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: Sql question Zsolt, Try: select a.something ,c.searchvalue from a, b, c where a.a= b.a and b.b1= c.b1 and b.b2= c.b2 and c.searchvalue= 'first one' and and not exists(select 1 from c c1 where c1.b1 = c.b1 and c1.b2 = c.b2 and c1.searchvalue<> 'second one') the above presumes that the columns b1 and b2 are part of the identifying columns for the c table. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, February 11, 2002 4:40 PM To: Multiple recipients of list ORACLE-L Hi, I have the following sql: select a.something ,c.searchvalue from a, b, c where a.a= b.a and b.b1= c.b1 and b.b2= c.b2 and c.searchvalue= 'first one' and c.searchvalue<> 'second one' The problem is that if a company has a record with c.searchvalue= 'first one' then the query above list it although it has another record with c.searchvalue= 'second one' To be more precise : I need to get the companies that have searchvalue = 'first one' but I don't want to see companies that has 'second one'. (the main problem is with companies that have both values) Thank you Zsolt Csillag, Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Csillag Zsolt INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sql question
You have to realize that you need two occurences of table c, as you want to match values of different records. You can do this in various different ways. One of them is something like: select a.something,c.searchvalue from a , b , c where a.a= b.a and b.b1= c.b1 and b.b2= c.b2 and c.searchvalue= 'first one' and not exists ( select '' from c c_2 where c_2.b1=b.b1 and c_2.b2=b.b2 and c.searchvalue='second one' ); HTH, Remco -Oorspronkelijk bericht- Van: Csillag Zsolt [mailto:[EMAIL PROTECTED]] Verzonden: maandag 11 februari 2002 22:40 Aan: Multiple recipients of list ORACLE-L Onderwerp: Sql question Hi, I have the following sql: select a.something ,c.searchvalue from a, b, c where a.a= b.a and b.b1= c.b1 and b.b2= c.b2 and c.searchvalue= 'first one' and c.searchvalue<> 'second one' The problem is that if a company has a record with c.searchvalue= 'first one' then the query above list it although it has another record with c.searchvalue= 'second one' To be more precise : I need to get the companies that have searchvalue = 'first one' but I don't want to see companies that has 'second one'. (the main problem is with companies that have both values) Thank you Zsolt Csillag, Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Csillag Zsolt INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Not able to drop table
Hi, I'm stuck with a table I can't drop and can't update. Select works fine, status is valid and the name appears in dba_tables and dba_objects. I seem to remember someone posting a solution to this problem some time ago, but can't find it. Something like manually adding a record in a dictionary table, and then dropping the table. Anyone remember the name of that table ? Or some other solution ? Regards, Remco -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OFF TOPIC: RE: PL/SQL
Hear hear ! You tell 'm, Kevin ! I'm with you all the way. Anybody can ask me a "simple" question when they are looking for a quick answer, but you can't do a good job when you're always looking for quick answers. You have to LEARN. There's a difference between learning and constantly asking questions. Sorry, couldn't resist ... -Oorspronkelijk bericht- Van: Thomas, Kevin [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 29 januari 2002 9:00 Aan: Multiple recipients of list ORACLE-L Onderwerp: OFF TOPIC: RE: PL/SQL When I arrive in the morning to a mailbox containing 150+ messages from this list, I'm almost certainly guaranteed that 10% of them will be questions that can easily be found via otn or a manual. That 10% usually comes from the same person!! No wonder I'm irritated!!! }:o| -Original Message- Sent: 28 January 2002 17:35 To: Multiple recipients of list ORACLE-L Geez... lay off already. If you don't think the question is worthy of your time, then don't answer it. What a grouch. -Original Message- Sent: Monday, January 28, 2002 11:16 AM To: Multiple recipients of list ORACLE-L RRR TTTFF !! -Original Message- Sent: 28 January 2002 15:46 To: Multiple recipients of list ORACLE-L How can I in a pl/sql block write null if I want null to be inserted in a field when I use dynamic sql.? . I mean nothing is going to be inserted. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespace
Best possible solution: rewrite the query and try to avoid large sorts ... or split the query, and make use of temporary tables (by using CTAS) to save results of the first part ... HTH, Remco -Oorspronkelijk bericht- Van: Sajid Iqbal [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 15 januari 2002 10:50 Aan: Multiple recipients of list ORACLE-L Onderwerp: Locally Managed Tablespace Hi all I am getting this error while running a large query, I recently created this locally managed temp tablespace... Any advice on possible solutions, the tablespace is 5 gig ORA-01652: unable to extend temp segment by 32 in tablespace TEMP_LOCAL TIA -- Saj Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE:
Hi Steve, We've had the same problem: a lot of DG machines, and we had to find a replacement. We also have Clarion disk cabinets, and that was one of the reasons we chose for SUN. Don't worry about the quality: SUN is at least as good as DG (in our experience). I don't know anything about the prices, though, so I can't tell you what you can get for under 20K, but I suppose that if you order the same machine from SUN (4 CPU's and 1Gb RAM), performance will be about the same. HTH, Remco -Oorspronkelijk bericht- Van: Steve McClure [mailto:[EMAIL PROTECTED]] Verzonden: donderdag 29 november 2001 21:55 Aan: Multiple recipients of list ORACLE-L Onderwerp: Hello All, It has been a while since I have been here, but I am back and properly s ubscribed to the list again. I am looking to draw from your experiences here with hardware. Our current database resides on a Data General Aviion system with a Clarion drive cabinet. While we are very pleased with the system, and it's performance Data General is going the way of the Dinosaur, so we need to look at other options. The ones most currently floated have been the Sparc 880 or the 420R either configured with dual gigahertz processors. The IT manager has a desk piled high with marketing gobbledeegook, and has asked me if I know anything about either system. All I have been able to do is assure him that Solaris is essentially UNIX, and tell him I would check with some knowledgeable folks here about the hardware. Our DG box sports a Gig of RAM, and 4 300 mhz Intel processors. The best thing by far about our system is the Clarion drive cabinet that handles all our drives. The good news is I hear our cabinet is compatible with Sun hardware, so that might come right along with us. I have done a bit of internet searching, and seen these Sun boxes priced under 20K. My question is this. Are these serious platforms for a business currently handling 10K OLTP transactions a day, and looking to double or triple that volume within two years? Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve McClure INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Replication: general overview
Sorry, don't agree ... -Oorspronkelijk bericht- Van: Garner, John (NESL-IT) [mailto:[EMAIL PROTECTED]] Verzonden: maandag 19 november 2001 11:15 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: Replication: general overview The oracle documentation is good -Original Message- Sent: 19 November 2001 09:50 To: Multiple recipients of list ORACLE-L Hi list, Does anybody know any links to docs that give a general technical overview of how oracle deals with replication ? I'm especially interested in a schema that shows what oracle-user is doing what in the process of replication. I've got it to work on our databases, and have made various new accounts in the process, but haven't got a clear overall picture in my head. Anybody ?! TIA, Remco -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator or telephone 0191 210 2060 or e-mail [EMAIL PROTECTED] This e-mail and any attachments have been scanned for certain viruses prior to sending but neither Northern Electric plc nor any of the companies in the Northern Electric group of companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. Northern Electric plc Carliol House Market Street Newcastle-upon-Tyne NE1 6NE Registered in England and Wales: Number 2366942 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Garner, John (NESL-IT) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Replication: general overview
Hi list, Does anybody know any links to docs that give a general technical overview of how oracle deals with replication ? I'm especially interested in a schema that shows what oracle-user is doing what in the process of replication. I've got it to work on our databases, and have made various new accounts in the process, but haven't got a clear overall picture in my head. Anybody ?! TIA, Remco -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: perplexing plan?
Doug, Sorting is caused by the "distinct", and is probably the cause of your performance problem. Try to limit the sorting to a minimal number of rows, e.g. by creating a temp table containing all (including the multiple copies) rows and then select the distinct values of that table. You could also try : select distinct * from (select ..) to replace the select distinct. Another tip: don't you hints unless you really have to ... HTH, Remco -Oorspronkelijk bericht- Van: Doug C [mailto:[EMAIL PROTECTED]] Verzonden: vrijdag 5 oktober 2001 16:30 Aan: Multiple recipients of list ORACLE-L Onderwerp: perplexing plan? I'm a little perplexed by this query and it's associated plan. It's also a big performance problem. The problem is the 35 million row table clearly. But looking at the plan at the bottom, I'm not sure where the sorting is going on. Would anyone say the index full scan on the 35 million row table is being sorted? Or does it look more like it's being fed to a nested loops query? Thanks, Doug SELECT /*+ ORDERED INDEX(S_ S15_IX1) INDEX(BUS_FID F15_UK1) INDEX(STREET A15_IX1) */ SDE.STREET.CFCC, SDE.STREET.BUS_FID ,S_.eminx,S_.eminy, S_.emaxx,S_.emaxy, BUS_FID.fid,BUS_FID.numofpts,BUS_FID.entity, BUS_FID.points,BUS_FID.rowid FROM (SELECT /*+ INDEX(SP_ S15_IX1) */ DISTINCT sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.S15 SP_ WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3 AND SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx >= :7 AND SP_.emaxy >= :8) S_ , SDE.STREET , SDE.F15 BUS_FID WHERE S_.sp_fid = BUS_FID.fid AND S_.sp_fid = SDE.STREET.BUS_FID call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 45473.15 475.04 223532 66153503 0 4494 --- -- -- -- -- -- -- total 47473.15 475.04 223532 66153503 0 4494 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 20 Rows Row Source Operation --- --- 4494 HASH JOIN 4494NESTED LOOPS 4495 VIEW 4495 SORT UNIQUE 4817 INDEX RANGE SCAN (object id 7356) 4494 TABLE ACCESS BY INDEX ROWID STREET 8988INDEX UNIQUE SCAN (object id 7355) 33065402 TABLE ACCESS BY INDEX ROWID F15 33065403INDEX FULL SCAN (object id 7283) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Doug C INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Platform
How about trying one, build an exception, and on exception try the other ? HTH, Remco -Oorspronkelijk bericht- Van: Libal, Ivo [mailto:[EMAIL PROTECTED]] Verzonden: woensdag 26 september 2001 15:10 Aan: Multiple recipients of list ORACLE-L Onderwerp: Platform Hello how is it possible in PLSQL to decide on which operating system (NT/LINUX)is server running? I would like to automatically specify directory where output from dbms_file will go. On nt I need something like c:\log and on unix I would like to have something like /log . Is there any view where I can find this information about OS? Thank you for responce Ivo -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Libal, Ivo INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Any experience with Lecco Sql Expert Pro?
We've had it for a year. Works fine for simple queries, but don't expect too much for complex queries ... one big disadvantage is that it only converts SQL into different SQL statements, while sometimes a lot can be gained from exploring other options like pl/sql or operating system functions or whatever. At least that was true for the version we had ... version 2.1.0. If you have a choice, go for qualified developers instead of tools ! HTH, Remco -Oorspronkelijk bericht- Van: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Verzonden: maandag 24 september 2001 23:05 Aan: Multiple recipients of list ORACLE-L Onderwerp: Any experience with Lecco Sql Expert Pro? Hi all, Anyone has any experience with this product? Would you share your thoughts? I am looking at this product right now, so just asking for your views. Thanks Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 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. *1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: question about the query..
How about: select distinct ename,job,sal from ( select ename,job,sal from ( select * from emp order by salary desc ) where rownum <= 1000 ); Uses a lot of temp tablespace, though, and will not be very fast for large tables ... you should probably try one of the new functions of Oracle 8(i?). What verion do you use ? HTH, Remco -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Verzonden: woensdag 5 september 2001 17:39 Aan: Multiple recipients of list ORACLE-L Onderwerp: question about the query.. Hi List I have a question regarding a SQL query I am working on. I have a table named 'tmp_scores', with a column 'rank' and other columns. Rank varies from 1 to 12. I need to get the top 10 million records based on the rank. I have used the logic behind the following query(to get top 4 salaried employees) - select ename,job,sal from emp e where 4 > (select count(*) from emp where e.sal = (SELECT count(*) FROM tmp_stg_van_customer_details WHERE a.combined_score_demi_decil_rank < combined_score_demi_decil_rank) ORDER BY combined_score_demi_decil_rank; I have issued the query and it has been running for the last three days without giving any error. I am not able to conclude what to do. Any help is highly appreciated. Thanks in advance. srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: NT-Question: Where to put TNS_ADMIN
I put an entry in Control Panel --> System --> Environment for TNS_ADMIN, and put one tnsnames.ora on a central place on the network. This way we make sure that all DBA's use the same tnsnames. Works fine for most applications ... HTH, Remco -Oorspronkelijk bericht- Van: Kevin Kostyszyn [mailto:[EMAIL PROTECTED]] Verzonden: donderdag 30 augustus 2001 15:42 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: NT-Question: Where to put TNS_ADMIN I usually just make sure that all of my tnsnames files and listeners match. Then using only one of the listenters I am able to connect to all of the db's. Sincerely, Kev -Original Message- Sent: Thursday, August 30, 2001 8:08 AM To: Multiple recipients of list ORACLE-L hi, Ora817 documentation says, that I have to put TNS_ADMIN under each ...\SOFTWARE\ORACLE\HOMEn . And it works very well on a machine with four OraHomes! But on another machine with only 806-installation I have to put it under ...\SOFTWARE\ORACLE. Is there any rule behind that? Where do I find more about Net*8 and the registry? Thanx a lot Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax:+49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Nested loop very slow
Thanx Jonathan, I was assuming that more reads could be done in 1 second. I'll try sorting the data based on the index to speed up the query. -Oorspronkelijk bericht- Van: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Verzonden: donderdag 23 augustus 2001 14:21 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: Nested loop very slow We really need more details about the data and the query to give you the 'correct' answer. But let us assume that the 97,000 result rows joining one row in the large table to a related parent row in the smaller table. If you index to find those 97,000 rows - with a perfect index, not a range scanned index - then the rows could all be in different blocks, which could result in 97,000 individual physical block reads. At peak operation, you are unlikely to get more than one hundred reads per second, so that would equate to 100 rows per second - which required 970 seconds for 97,000 rows, which comes to: a little over 16 minutes. Under those circumstances, your 14 minutes seems quite justifiable. NB if you include an ORDER BY in your query, Oracle has to get all the rows before it can sort them (unless you have a convenient index path which allows a 'no-sort order by'), so you couldn't get your result in just a few seconds. Jonathan Lewis Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases See http://www.jlcomp.demon.co.uk/book_rev.html For latest news of public appearances See http://www.jlcomp.demon.co.uk Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research. -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 23 August 2001 12:08 |Hi, | |I'm joining a table with 32000 rows with a very large table (3 Gb), |resulting in 97000 records. When I execute the query without hints, it uses |a full table scan and a hash join, and returns a result in 4 minutes. When I |use a hint forcing a nested loop (and part of an index (range scan)), the |query takes 14 minutes to complete. The question is: why does a join between |an inner table with 32000 records and a large table using a range scan on an |index take so long ? I would expect Oracle to give a result within seconds, |a few minutes tops ! | |The table has very few chained rows and I've rebuilt the index recently. |RDBMS is 8.1.6. Index and data are divided over many different disks. | |Any suggestions ? Except kicking the server ? | |Remco | |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Daemen, Remco | INET: [EMAIL PROTECTED] | |Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California-- Public Internet access / Mailing Lists | |To REMOVE yourself from this mailing list, send an E-Mail message |to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in |the message BODY, include a line containing: UNSUB ORACLE-L |(or the name of mailing list you want to be removed from). You may |also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Nested loop very slow
Hi, I'm joining a table with 32000 rows with a very large table (3 Gb), resulting in 97000 records. When I execute the query without hints, it uses a full table scan and a hash join, and returns a result in 4 minutes. When I use a hint forcing a nested loop (and part of an index (range scan)), the query takes 14 minutes to complete. The question is: why does a join between an inner table with 32000 records and a large table using a range scan on an index take so long ? I would expect Oracle to give a result within seconds, a few minutes tops ! The table has very few chained rows and I've rebuilt the index recently. RDBMS is 8.1.6. Index and data are divided over many different disks. Any suggestions ? Except kicking the server ? Remco -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL QUERY
Hi Pallv,
Should be something like this:
set serveroutput on size 100
set linesize 200
declare
last_value_of_oid number:= null;
result_string varchar2(100) := 'Hoi';
skipfirst boolean := true;
begin
for rec in (select * from your_table)
loop
--
dbms_output.put_line('--'||lpad(last_value_of_oid,10)||rec.oid||lpad(result_
string,100));
if rec.oid = last_value_of_oid
then
result_string := result_string || ',' || rec.interest;
else
if skipfirst
then skipfirst := false;
else
dbms_output.put_line(lpad(last_value_of_oid,10)||lpad(result_string,100));
end if;
result_string := rec.interest;
end if;
last_value_of_oid := rec.oid;
end loop;
dbms_output.put_line(lpad(last_value_of_oid,10)||lpad(result_string,100));
end;
/
100
1,2,3
200
3,4
PL/SQL procedure successfully completed.
SQL> select * from your_table;
OID INTEREST
- -
100 1
100 2
100 3
200 3
200 4
HTH, Remco
-Oorspronkelijk bericht-
Van: Pallav Kalva [mailto:[EMAIL PROTECTED]]
Verzonden: maandag 20 augustus 2001 15:16
Aan: Multiple recipients of list ORACLE-L
Onderwerp: SQL QUERY
>
>
> Hi List,
>
> I need an help on sql query, have a table with the following data
>
>OID INTEREST
> -- --
>100 1
>100 2
>100 3
>100 4
>
>
> I want the output to be displayed as
>
> OID INTEREST
> 1001,2,3,4
>
> is it possible thru sql or pl/sql ?
>
> Thanks!
> Pallav
>
>
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Pallav Kalva
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Daemen, Remco
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Data Warehouse book
Oracle 8i Data Warehousing, from Oracle Press, ISBN 0-07-212675-2 -Oorspronkelijk bericht- Van: Thomas, Kevin [mailto:[EMAIL PROTECTED]] Verzonden: maandag 20 augustus 2001 10:35 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: Data Warehouse book I bought one from the Oracle Press, don't actually have it on me at the moment, but it does explain the basics through to the more complicated concepts...I think it was simply called "data warehousing" or "the datawarehouse handbook"...something along those lines. -Original Message- Sent: 17 August 2001 20:23 To: Multiple recipients of list ORACLE-L We are starting a Data Warehouse here and I would like to buy a good book to explain some of the basics. Does anyone have any recommendations? Thanks, Tom Tom Terrian Oracle DBA WPAFB - DAASC [EMAIL PROTECTED] 937-656-3844 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Terrian, Tom INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-24323
How about trying c:\mypath\mysql.sql ? Note the backslashes instead of the forward slashes ! Tip: start the day with lots of coffee ... :) -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 17 juli 2001 15:51 Aan: Multiple recipients of list ORACLE-L Onderwerp: ORA-24323 Hello All. When i am running my sql script from SQL/PLUS command using @C:/mypath/mysql.sql giving error ORA-24323: value not allowed Error accessing package DBMS_APPLICATION_INFO, and disconnected the user from the database . Second time when i reconnect and run the script again it runs successfully. I have nearly 150 such scripts to be run and i can not use batch sql file to run these scripts as it generally fails after one script. Can some one help me that what is wrong with my databse. Why ORA-24323 error is coming. Thanks in advance Vinay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: "ALTER SYNONYM"?
+ | Rick Osterberg [EMAIL PROTECTED]| | Database Applications Specialist FAS Computer Services | +--+ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rick Osterberg INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Select only one of three tables
Hi Witold, Try this: select * from ( select dept from dept_one union all select dept from dept_two union all select dept from dept_three ) where rownum <=1 ; HTH, Remco -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 3 juli 2001 18:37 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: Select only one of three tables Tom Thanks for the reply. The UNION would be good if I wanted all dept values from the tables. But the rule is more complex - if there is eg. one record in table DEPT_ONE, I have to get back only this one record even though there may other/more records for the same employee in the other tables. If there is no record in table DEPT_ONE and there is rcord in DEPT_TWO - I want back only what is in DEPT_TWO, regardless of what is in DEPT_THREE. Witold "Mercadante, Thomas F" <[EMAIL PROTECTED]> on 07/03/2001 13:01:51 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) Witold, have you tried using the UNION operator? like: select dept from dept_one union select dept from dept_two union select dept from dept_three order by 1; this will give you only one occurrence of the value of dept from all three tables. hope this helps Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 03, 2001 10:11 AM To: Multiple recipients of list ORACLE-L Hello list I have a scenario in which I have to check three tables. If there is record in table A, take it otherwise check table B, if there is record in table B, take it otherwise check table C. Let say I am looking for DEPT column and the tables are DEPT_ONE, DEPT_TWO, and DEPT_THREE. At the end I need only one DEPT column. While I can check each of the tables in order I would like to do it in one statement. I have tried DECODE but it did not like combination of count and column names - error ORA-00937. To make it simpler here is my query from two tables only: select decode (count(d2.emp_id), 0, d3.dept, d2.dept) dept from dept_two d2, dept_three d3 where d3.emp_id = TESTER_1' and d2.emp_id(+) = d3.emp_id Can someone recommend a solution? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DUPLICATE VALUE CHECK
How about: create table tmp_table as select substr(rowid,length(rowid)-7,7) last_seven from ; and then: select last_seven duplicates from ( select last_seven,count(*) from tmp_table group by last_seven having count(*) > 1 ); I didn't test it, but it should be pretty fast with "only" 450.000 rows. HTH, Remco -Oorspronkelijk bericht- Van: Shirish Khapre [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 26 juni 2001 17:42 Aan: Multiple recipients of list ORACLE-L Onderwerp: DUPLICATE VALUE CHECK Hi all i have one column in my table (in which daily 10 rows are added to the table) which has values like XYZ_A_LO_001 XYZ_A_LO_002 XYZ_A_LO_003 XYZ_A_LO_004 XYZ_A_LO_005 XYZ_A_LO_006 XYZ_A_LO_007 XYZ_A_LO_008 XYZ_A_LO_009 i want to check duplicate values.. there are 2 cases of duplication Case I :- i am using the following query select from my_table where rowid not in( select max(rowid) from my_table group by my_column_name ); i am getting the rows which are duplicate .. CASE II : - i want to check duplication in last 7 characters(which are actually nos) in my column like 001 002 003so on i am using substr function to get this value and i am checking the values with remaining rows.. but as the table contains nearly 45(present rowcount) the query is taking lot of time...which i can't afford... the column has index on it. plz suggest me what to do?? Shirish Khapre, SE Rolta India Ltd. Off Ph No. (+91) (022) 832,826,8300568 Ext'n 2730 Minds are like parachutes. They only function when they are open -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shirish Khapre INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: QUERY HELP
Title: QUERY HELP Do you want a query to return the missing numbers, or do you want a query to return the records AFTER some numbers have been skipped ? The first can be done in pl/sql (loop with counter compared to rownum), the latter in sql (use "where not exists ..."). HTH, Remco -Oorspronkelijk bericht-Van: Nirmal Kumar Muthu Kumaran [mailto:[EMAIL PROTECTED]]Verzonden: dinsdag 26 juni 2001 15:01Aan: Multiple recipients of list ORACLE-LOnderwerp: QUERY HELP Dear Guru's, How can i refer the previous record detail(s), when oracle fetchs the current row details?. sql> SELECT rownum rnum, empno eno, ename FROM EMP; RNUM ENO ENAME -- 1 7369 SMITH 2 7499 ALLEN 3 7521 WARD 4 7566 JONES 7 7782 CLARK 8 7788 SCOTT 10 7844 TURNER In the above, can i able to put * mark in record 7 and 10, since before these two records, some records are missing. Is this possible to do this by query. I need this in reports. Basically my question is, How can i refer the previous row detail(s), when oracle fetchs the current row details?. Thanks in adv. REgards, Nirmal.
RE: * I'm Looking for an Oracle Financials DBA for Miami,
Sounds like I'm working in one of the more relaxed countries of the world, with a low cost-of-living :). You've just made my day. I'll go home whistling and in a very good mood, today ! -Oorspronkelijk bericht- Van: Guy Hammond [mailto:[EMAIL PROTECTED]] Verzonden: donderdag 21 juni 2001 14:31 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: * I'm Looking for an Oracle Financials DBA for Miami, I was having a similar conversation with a friend of mine a couple of months ago. We were both London (England) based consultants, but we'd been staffed on engagements in Amsterdam (Holland). The cost-of-living is indeed a lot less, if you look at things like grocery shopping, the cost of riding the T, rent you'd pay on an apartment etc. But then, average pay is a lot less too, if you convert to GBP or USD and compare to London or Boston (MA), and the rate of tax is higher. Anyway, he really liked it there, and was thinking about a permanent relocation. I said, that will mean a big cut in your pay, to local rates, and replied that it didn't matter, he would be able to afford a much better quality of life than he could living in London. So, he had a point, he would have less money, but that money would go further, and he liked the Dutch pace of life and attitude, which is a lot more relaxed than London or the East Coast (I think that's what he's done now). But what quality of life is, is subjective. I love to travel, and even tho' I was traveling a lot for work, whenever I took vacation I'd get straight back on a plane and go visit friends somewhere else in the world, or just go exploring. This freedom is important to my perception of quality of life. Which means that when I think about remuneration, I have to benchmark my salary against cost of living in different cities, the cost of airline tickets/hotels/hire cars, even the exchange rates between currencies. (A plague on the Chancellor for the USD/GBP rate at the moment). So, that's complicated, but it does mean that I'm happy to work long hours, because that's the way of life that I have chosen. Maybe in a few years what will matter to me most is personal time, and then I'll take a job that lets me work fewer hours and I won't mind having less money. Cheers, g -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: * I'm Looking for an Oracle Financials DBA for Miami,
ng list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Quick select question
Hi
Lee,
Here's
how you can do it in PL/SQL. It can easily be converted to SQL if realy realy
want it :).
set
serveroutput on size 100declare cursor c is select table_name
from user_tables; rec
c%rowtype;begin open c; fetch c into
rec;
dbms_output.put_line('select '); dbms_output.put_line( 'select
'||||rec.table_name||||'
table_name'
||',count(*) from
'||rec.table_name
); fetch c into rec;
while c%found loop dbms_output.put_line( 'union
all
'||chr(10)
||'select
'||||rec.table_name||
||',count(*) from
'||rec.table_name
); fetch c into rec; end
loop;
dbms_output.put_line(';');end;/
HTH, Remco
-Oorspronkelijk bericht-Van: Robertson Lee - lerobe
[mailto:[EMAIL PROTECTED]]Verzonden: woensdag 20 juni 2001
19:17Aan: Multiple recipients of list ORACLE-LOnderwerp:
Quick select question
All,
Anyone know how to
get a list of tablenames and the count of rows in them
TABLE_NAME
COUNT
=== ==
LEE
10
LEE1
25
LEE2
17etc etc
I know it can be
done if the tables are analyzed and from user_tables but was wanting to know
how to do it from sqlplus.
Tru64
8.0.5.0.0
TIA
Lee
The information contained in this
communication isconfidential, is intended only for the use of the
recipientnamed above, and may be legally privileged. If the reader of
this message is not the intended recipient, you arehereby notified that
any dissemination, distribution orcopying of this communication is
strictly prohibited. If you have received this communication in error,
please re-send this communication to the sender and delete the
original message or any copy of it from your
computersystem.
RE: Enforced Costraints ??
I think there are a number of ways to implement something like that (like catching exceptions), and choosing solutions like permitting temporary states of non-uniqueness is asking for trouble. As long as I can see any other option to return the same result, I would take it. This also prevents the developers from getting lazy and delivering 'quick-and-dirty' solutions :). Enabling a primary key by deferred constraint checking is something that I would consider, because in that case it is still Oracle that enforces the constraint, and at the and of the transaction, no duplicate values are allowed, but I didn't think this was the issue here. I thought it was about disabling constraints, fooling around and then enabling the constraints with the "novalidate" option. hth too Remco -Oorspronkelijk bericht- Van: Connor McDonald [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 12 juni 2001 14:31 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: Enforced Costraints ?? I disagree. A simple reason for a non-unique index to enforce a primary key are is to allowing a temporary state of "non-uniqueness" to be permitted during a transaction. eg insert a batch of new records (some of which may be duplicates), then remove the bad ones, then commit. (with deferred constraint checking) hth connor --- "Daemen, Remco" <[EMAIL PROTECTED]> wrote: > Not good enough ! According to various documents, > you should add your own > primary key (surrogate key), which also makes it > easier to keep track of > history and combine various sources. I agree with > Lee: you should never > implement a PK with non-unique values. > > Remco > > -Oorspronkelijk bericht- > Van: Rahul [mailto:[EMAIL PROTECTED]] > Verzonden: dinsdag 12 juni 2001 11:56 > Aan: Multiple recipients of list ORACLE-L > Onderwerp: RE: Enforced Costraints ?? > > > DSS ! > > > > -- > > From: Robertson Lee - > lerobe[SMTP:[EMAIL PROTECTED] ] > > Reply To: [EMAIL PROTECTED] > > Sent: Tuesday, June 12, 2001 4:00 PM > > To: Multiple recipients of list ORACLE-L > > Subject:RE: Enforced Costraints ?? > > > > am I missing something here ?? Why would you ever > need to be in that > > situation (a table with non-unique values in a PK > column) ? > > > > Apologies if this is a no-brainer. > > > > Lee > > > > > > -Original Message- > > Sent: 12 June 2001 09:41 > > To: Multiple recipients of list ORACLE-L > > > > > > i think the solution is to . > > > > (on a table with non-unique values in a PK > candidate column) > > > > 1) create a non-unique index on the pk candidate > colunm > > 2) create the pk using enable novalidate clause > > > > this way the existing data will NOT checked for > uniqueness, the constraint > > will be "enforced" for the upcoming data only... > > > > Regards > > Rahul > > > > > > > > -- > > > > From: Anshumn[SMTP:[EMAIL PROTECTED]] > > > > Sent: Tuesday, June 12, 2001 12:30 PM > > > > To: Rahul > > > > Subject:Re: Enforced Costraints ?? > > > > > > > > Hi Rahul, > > > > > > > > That is true. In Oracle 8, there is option to > enable the constraint in > > > > novalidate mode or validate mode. The > novalidate mode is the enforce > > > mode, > > > > where only the forthcoming data is checked. It > does not check the > > > exisitng > > > > data. The validate mode is the normal > constraint enable mode. > > > > > > > > The syntax is > > > > Alter table table enable novalidate constraint > name; > > > > > > > > So after I put the constraint in the > novalidate mode, the uniqueness > > > will > > > > be checked only amongst the coming data. But > if later I set the > > > constraint > > > > ti validate mode(enable), then it may give > error as the loaded data > > was > > > > never checked for uniqueness with the exisitng > data. In this case I am > > > > very much confused with the usefulness of this > feature. > > > > Can you please give any inputs for the same to > clear my doubts ? > > > > > > > > Thanks & Regards, > > > > Anshumn > > > > > > > > Rahul wrote: > > > > > > > > Anshuman, > > > > > > > > whenever u add a co
RE: Enforced Costraints ??
In this case I may have loaded a record, with a > > > value > > > > which is already present in the existing data(Since the existing > > > data > > > > was not checked while enforcing the constraint). Now if I enable > > > the > > > > constraint, then that time it will check all the records for > > > uniqueness. > > > > > > > > How does it work in that case ? Won't it give me an error ? Then > > > how > > > > good is the use of an enforced constraint ? > > > > > > > > Please give your valuable feedback to clear this doubt. I need it > > > > urgently. > > > > > > > > Thanks in advance, > > > > Anshumn > > > > > > > > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Rahul > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Rahul > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > The information contained in this communication is > confidential, is intended only for the use of the recipient > named above, and may be legally privileged. If the reader > of this message is not the intended recipient, you are > hereby notified that any dissemination, distribution or > copying of this communication is strictly prohibited. > If you have received this communication in error, please > re-send this communication to the sender and delete the > original message or any copy of it from your computer > system. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Robertson Lee - lerobe > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Full Table Scan and TKPROF Output
Hi Ethan, If selectivity is that low, try using a bitmapped index. HTH, Remco -Oorspronkelijk bericht- Van: Post, Ethan [mailto:[EMAIL PROTECTED]] Verzonden: vrijdag 8 juni 2001 2:26 Aan: Multiple recipients of list ORACLE-L Onderwerp: Full Table Scan and TKPROF Output My theory...We are running J.D. Edwards OneWorld. OneWorld allows the CNC (code word for OneWorld admin) to configure a number of job queues that check a table (the F986110) for new jobs that need to be processed. Each of these processes and occasionally a few more update, delete and select from this table almost constantly. The SQL being executed against the table uses a "WHERE" clause on 5 columns which are indexed but the selectivity is really bad, only 5 distinct values out of 100+ thousand records, so it does a full table scan. A few months ago I cached the table. At the moment the table is 100 MB and only has 30 MB of data. I will reorg it the next time we get some down time. The trouble is that I experience a lot of buffer busy waits on these processes. Also when I ran SQLTRACE it showed an almost unbelievable number of buffers read in consistent mode, way! way! larger than the size of the table. The CPU associated with these processes runs around 10% each so we are at 50% CPU even when the system is dead. Luckily they seem to take a low priority and the % CPU drops when the job kicks off, this may be because the queue is waiting on the job. My guess why CPU is 10% is that the CPU is reading all the blocks in memory a bazillion times. I can't find anything about this on the J.D. Edwards Knowledge Garden. By the way CPU time is really high also. This is a huge performance problem for OneWorld. My proposed official "duct tape" solution is to make the table much smaller by moving the records into another table after they are more than N days old. At the moment we clean up after 90 days but I think there would be a terrific gain if we reduce it to 7 days or so, (some of this is for the benefit of folks on the JDELIST, sorry I'm gonna cross-post). Am I missing anything? Are there any other solutions to this dilemma? Thanks, Ethan Post -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to identify sql texts which are having open cursor?
How about sys.V_$OPEN_CURSOR to get the SIDs, and then try to get the query they are executing ? HTH, Remco -Oorspronkelijk bericht- Van: Shantanu Talukder [mailto:[EMAIL PROTECTED]] Verzonden: woensdag 6 juni 2001 0:14 Aan: Multiple recipients of list ORACLE-L Onderwerp: How to identify sql texts which are having open cursor? Our application is using up open_cursor limits of 500. We are suspecting that application code written in Java are not closing cursors explicitly. Is there a way to idntify those sql code which are not closing cursor? We just don't want to increase open_cursor parameter in init.ora and bounce the db without identifying sql. HTH, Shantanu --- "Kirsh, Gary" <[EMAIL PROTECTED]> wrote: > Jared, > > I think that the default java_pool_size is 20M, not > 30M. > > Gary > > > -Original Message- > Sent: Tuesday, June 05, 2001 4:01 PM > To: Multiple recipients of list ORACLE-L > > > > Take a look at the SGA with: > > select * from v$sgastat > order by 1,2; > > You will likely see 30M dedicated to the > java pool, as that is the default. This > can be cut back substantially if you are > not making use of java. > > Jared > > > On Tuesday 05 June 2001 05:01, C.S.Venkata > Subramanian wrote: > > One of my co-worker came with this ques. In the > init.ora file he had set > > the shared_pool_size to 10M and > db_block_buffers=1024 and redolog buffer > to > > 32768. but when he did sho sga in the sql*plus > prompt he got the following > > output > > > > Total System Global Area 41297948 bytes > > Fixed Size75804 bytes > > Variable Size 32755712 bytes > > Database Buffers8388608 bytes > > Redo Buffers 77824 bytes > > > > Doing a select on V$SGA also gave the same result. > > > > My shared pool size is only 10M, why it is showing > nearly 40M. From where > > did oracle derive the extra MB's? I ran throu the > oracle manual and got > > only answer for database buffers. > > > > Can any one tell from where these extra MB's come > to Oracle from the OS. > > > > TIA > > > > Venkat > > > > > > Get 250 color business cards for FREE! > > http://businesscards.lycos.com/vp/fastpath/ > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Kirsh, Gary > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shantanu Talukder INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMA
RE: help me : error on import
It has to do with your NLS environment settings. If you're on unix, make sure the NLS_LANG variable is set to the same value as it was during the export. HTH, Remco -Oorspronkelijk bericht- Van: benajam lhoussain [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 22 mei 2001 19:00 Aan: Multiple recipients of list ORACLE-L Onderwerp: help me : error on import Export file created by EXPORT:V08.01.07 via conventional path import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set IMP-00069: Could not convert to environment national character set's handle IMP-0: Import terminated unsuccessfully thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: benajam lhoussain INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: unhandled user-defined exception
Title: unhandled user-defined exception Hi Venu, Did you set the database parameter utl_file_dir ? Remco -Oorspronkelijk bericht-Van: Venugopal, R (GEP, Contractor) [mailto:[EMAIL PROTECTED]]Verzonden: donderdag 17 mei 2001 14:52Aan: Multiple recipients of list ORACLE-LOnderwerp: RE: unhandled user-defined exception I am attaching the source code of the trigger ,Please let me know where I made mistake .Thanks.. Venu -Original Message-From: Venugopal, R (GEP, Contractor) Sent: Thursday, May 17, 2001 7:25 PMTo: Multiple recipients of list ORACLE-LSubject: unhandled user-defined exception Hi Gurus I am getting the error attached below when trigger fires. what could be the reasons for this error. DB error: ORA-06510: PL/SQL: unhandled user-defined exception Thanks in Advance Venu
RE: Oracle 8.1.7.1 NEW Installation on SunOS 5.8
Hi Vivek, I had a simular problem on DG/UX. It turned out that the shared memory was the problem: after freeing shared memory that had no processes connected to it, the problem was solved. HTH, Remco -Oorspronkelijk bericht- Van: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]] Verzonden: donderdag 17 mei 2001 16:31 Aan: Multiple recipients of list ORACLE-L Onderwerp: Oracle 8.1.7.1 NEW Installation on SunOS 5.8 SunOS 5.8 , ORA 8.1.7.1 For Creating a NEW Database ( 1st Time ) SVRMGR> connect internal; connected SVRMGR> startup nomount pfile=/in1/ora817/dbs/initfin61.ora; ORA-03113 end-of-file on communication channel RESULT Database NOT Coming to NOMOUNT OBSERVATIOM - "core" of 9MB Dumped in the CORE_DUMP_DEST Dir NOTE - Core Dumped only when Attempting "STARTUP NOMOUNT" thru the $ORACLE_OWNER When Trying the SAME Command thru Another Unix user of DBA Group . NO Core is Dumped though the Error ORA-3113 Continues NOTE - 1) NO SQL*Net being Done 2) TWO_TASK NOT Set 3) RE-Created the oracle Exes using " make -f ins_rdbms.mk install" Considering Deleting ORA 8.1.7.1 & Installing 8.1.7.0 & RE-Trying -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Imedia query tuning
6930 consistent gets
1708 physical reads
0 redo size
2244834 bytes sent via SQL*Net to client
252240 bytes received via SQL*Net from client
2265 SQL*Net roundtrips to/from client
11 sorts (memory)
1 sorts (disk)
552 rows processed
Here are the table details.
1. To create category table
CREATE TABLE CATEGORY
PK_CATEGORY_IDNUMBER NOT NULL,
PARENT_CATEGORY NUMBER NOT NULL,
NAME VARCHAR2 (1000) NOT NULL,
DEPTH VARCHAR2 (4000) NOT NULL,
STATUSNUMBER NOT NULL,
UPDATED_DATETIME DATE,
PRIMARY KEY ( PK_CATEGORY_ID ) );
2. To create site table.
CREATE TABLE SITE
PK_SITE_ID NUMBER NOT NULL,
FK_CATEGORY NUMBER NOT NULL,
TITLECLOB,
URL VARCHAR2 (4000) NOT NULL,
DESCRIPTION CLOB,
STATUS NUMBER NOT NULL,
PAGE_HITSNUMBER NOT NULL,
EDITOR_CHOICE VARCHAR2 (10),
PRIMARY KEY ( PK_SITE_ID ));
ALTER TABLE SITE ADD CONSTRAINT FKSITE
FOREIGN KEY (FK_CATEGORY)
REFERENCES VCPLNEW.CATEGORY (PK_CATEGORY_ID) ;
3. creating an index after inserting the data(Datebase Updation) in both the
tables.
a) Execute this script to create a preference.
begin
ctx_ddl.create_preference('sitelexer', 'BASIC_LEXER');
ctx_ddl.set_attribute('sitelexer', 'printjoins', '_-''');
ctx_ddl.set_attribute('sitelexer', 'endjoins', '%');
ctx_ddl.set_attribute ( 'sitelexer', 'index_text', 'YES');
ctx_ddl.set_attribute ( 'sitelexer', 'mixed_case', 'NO');
end;
b) Execute this script to create an indexes.
CREATE INDEX site1descidx ON
site(description)
indextype is ctxsys.context
parameters ( 'LEXER sitelexer' );
CREATE INDEX site2titleidex ON
site(title)
indextype is ctxsys.context
parameters ( 'LEXER sitelexer' );
4.Deleting a preference
begin
ctx_ddl.drop_preference('sitelexer');
end
Thanks in advance and Regards,
Ranganath
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ranganath K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Daemen, Remco
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: ORA-3113 and PLSQL Runtime Dump
3 51710785 51810876 51910968 52011060 52111152 52511244 52611278 52711293 52811326 53011341 53411357 53911364 54311382 54711388 55111394 55511400 55911406 56311412 56711418 57111424 57411475 57811492 58111850 58511864 5881 59212236 59612242 59912322 60312342 60712349 60812384 60912400 61012434 61212450 61612466 62012473 62312670 62712677 63112684 63512703 63912710 64312717 64712736 65012743 70813063 73013163 72813169 73413169 73513175 73613191 73713207 73913213 74313245 74413262 74713279 74813297 74913317 75013335 75413352 76013414 76113432 76513449 76613465 77213471 77313509 77913515 78013553 78613559 78713596 79313602 72213656 81514303 81714303 83214303 83014309 83614309 83814649 84314669 84614676 84914683 85214690 85314714 85614721 86314728 86514757 86614764 86714771 86814778 87314790 87815064 88215097 88415103 88515121 88615134 88715152 88915165 89415172 89715184 90015250 90415263 91015325 91315349 96715560 96815576 97015608 82515860 ANONYMOUS BLOCK: library unit=81a6fc68 line=7 opcode=38 static link=0 scope=0 FP=16c3194 PC=81adeaf9 Page=0 AP=0 ST=16c31e8 DL0=16819c0 GF=16819e8 DL1=16819d0 DPF=16819e4 DS=820b1d64 DON library unit variable list instantiation -- - - 0 81a6fc68 16819e8 16751b0 1 822ae29c 16783f8 1678158 2 82481f94 167a354 1682638 3 scopeframe 1 16c3194 version=43123476 instantiation size=36 line pcode offset 4 14 5 43 6 48 7 65 ***END PLSQL RUNTIME DUMP oracle@oracle8> sqlplus SQL*Plus: Release 8.1.5.0.0 - Production on Wed May 16 09:52:39 2001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dati Tecnici INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL statement performance
Hi Guang, Try something like this: select distinct(spid) spid,name,commonname from ( select distinct(spid) spid,name,commonname from results a , species where a.queryspid = species.id union all select distinct(spid) spid,name,commonname from results b , species where a.subjspid = species.id ); HTH, Remco -Oorspronkelijk bericht- Van: Guang Mei [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 15 mei 2001 1:20 Aan: Multiple recipients of list ORACLE-L Onderwerp: SQL statement performance Oracle : 8.0.5 Platform : Sun SQL statement: select distinct(spid) spid, name, commonname from ( select distinct queryspid spid from results union select distinct subjspid spid from results ) a, species where a.spid=species.id ; Table "Species" only has 33 records, while table "results" has about 800 records. There are indexes on queryspid and subjspid. This query is somehow slow. Is there any "easy" way to speed it up? Thanks. _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
