RE: Using the /*+ append */ insert hint
From experience, do not use the APPEND hint for singular inserts. You will get tons of wasted space. Only use it for bulk inserts such as INSERT INTO .. SELECT FROM, sqlldr, PL/SQL bulk inserts and the like. Converting from buld inserts without the append hint to bulk inserts with the append hint, I've seen as much as a 50% reduction in execution time. Adding the append hint to single-row inserts not only wastes space but generally slows things down. Kevin -Original Message- Sent: Monday, March 17, 2003 7:29 AM To: Multiple recipients of list ORACLE-L I can't say in regards to 7.3.x or 8.0.x, but in an 8.1.7.4, I've traced a complete snapshot refresh and seen that Oracle is using an insert /*+ append */. Good, bad, or otherwise, someone at Oracle believes in it. I will say that it is very likely the hint will just be ignored if not supported. For example: (this is the exact text of a query against an 8.0.6 instance) SQL select /*+ BADHINT */ * from dual; D - X SQL Darrell [EMAIL PROTECTED] 03/17/03 04:23AM Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience with this hint? Specifically, does anyone know what would happen on Oracle 7.3.x or 8.0.x if I used this (assuming it's supported ... would it silently ignore the hint if not supported?) Thanks Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LMT monitoring
. The benefit is that bitmaps rather than freelists are used to identify free space. With UNIFORM, you can tell exactly how many allocations will be required to fill up the tablespace. (freespace / extent-size = #allocs) With User Allocated use the same logic as you would for dictionary managed tablespaces. With Auto-Allocated extents, um. You don't know what the next extent size will be. Kevin -Original Message- Sent: Monday, March 10, 2003 12:04 PM To: Multiple recipients of list ORACLE-L I admit to being sleep-deprived but I don't see how there is a difference between monitoring dictionary managed and locally managed tablespaces when you are talking about the inability to allocate another extent. It seems relatively simple to me: check the size of the next extent that will be allocated (this can be calculated, regardless of auto allocate, uniform or dictionary managed next and pctincrease). Check to see if there is that much space available in the tablespace. If you REALLY want to be paranoid, do this as if you expect EVERY table and index in the tablespace to extend at the same time. If remaining unallocated space is greater than the next extent allocation you calculate, you have enough space. If it is not, you have to add a datafile or extend the existing one. Or am I missing something? Rachel --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: I have been reading some docs on free space etc., but I have not yet found any conclusive document on how to monitors tables/indexes in LMT environment so that we could *predict* when a segment would not be able to allocated extent. Basically we want to monitor, when we needs to add more space to a tablespace, at-least 1 day before we get error messages. Devl team doesn't always tell us before dataload (it happens, and now they have been warned). I believe this is little bit complex compared to dictionary managed tablespaces, as (in auto allocate mode), there is no fixed published formula for next extent ... Any ideas? TIA Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2 __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke
RE: LMT monitoring
The version is 8.1.7.1.0 The report is via this query: SELECT partition_name, extent_id, bytes/1024, bytes/1024/1024 FROM dba_extents WHERE segment_name = 'FORMATTER_DATA_HISTORY' ANDowner = 'KEVIN' ORDER BY 1, 2 The file is not autoextent. The table is populated by sqlldr direct path. The tablespace is locally managed with system allocated extents TABLESPACE_NAMEEXTENT_MAN ALLOCATIO -- -- - KEVIN_TS LOCAL SYSTEM Kevin -Original Message- Sent: Monday, March 10, 2003 5:21 PM To: Multiple recipients of list ORACLE-L Which version of Oracle ? How are you getting the report ? Is the file autoextent - if so at what unit ? How are you filling the table ? Is the tablespace ASS Managed ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 10 March 2003 21:02 As mydata load continues, the saga continues. The simplistic algorithm does not hold Can anyone explain these results? PARTITION_NAME EXTENT_ID BYTES/1024 BYTES/1024/1024 -- -- -- --- FINS_FM_DATA_CLOSED_200207 84 6144 6 FINS_FM_DATA_CLOSED_200207 85 5120 5 FINS_FM_DATA_CLOSED_200207 86 6144 6 FINS_FM_DATA_CLOSED_200207 87 5120 5 FINS_FM_DATA_CLOSED_200207 88 5120 5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LMT monitoring
There are three (3) types of LMTs (yes, three!) UNIFORM Extent sizes Every extent created in the tablespace will be the same size, no matter the storage parameters specified. AUTOALLOCATE (System managed) The system will decide the next extent size at creation. This is based on a large number of things. (I think the phase of the moon and the number of sun-spots on 03-03-1942 are included in this calculation) The min extent size is 64K USER Allocated This is only available for tablespaces that were converted from dictionary managed tablespaces. As it would seem, the user determines the space allocation -- the space allocation is the same as for dictionary managed tablespaces. The benefit is that bitmaps rather than freelists are used to identify free space. With UNIFORM, you can tell exactly how many allocations will be required to fill up the tablespace. (freespace / extent-size = #allocs) With User Allocated use the same logic as you would for dictionary managed tablespaces. With Auto-Allocated extents, um. You don't know what the next extent size will be. Kevin -Original Message- Sent: Monday, March 10, 2003 12:04 PM To: Multiple recipients of list ORACLE-L I admit to being sleep-deprived but I don't see how there is a difference between monitoring dictionary managed and locally managed tablespaces when you are talking about the inability to allocate another extent. It seems relatively simple to me: check the size of the next extent that will be allocated (this can be calculated, regardless of auto allocate, uniform or dictionary managed next and pctincrease). Check to see if there is that much space available in the tablespace. If you REALLY want to be paranoid, do this as if you expect EVERY table and index in the tablespace to extend at the same time. If remaining unallocated space is greater than the next extent allocation you calculate, you have enough space. If it is not, you have to add a datafile or extend the existing one. Or am I missing something? Rachel --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: I have been reading some docs on free space etc., but I have not yet found any conclusive document on how to monitors tables/indexes in LMT environment so that we could *predict* when a segment would not be able to allocated extent. Basically we want to monitor, when we needs to add more space to a tablespace, at-least 1 day before we get error messages. Devl team doesn't always tell us before dataload (it happens, and now they have been warned). I believe this is little bit complex compared to dictionary managed tablespaces, as (in auto allocate mode), there is no fixed published formula for next extent ... Any ideas? TIA Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2 __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LMT monitoring
') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LMT monitoring
allocate, uniform or dictionary managed next and pctincrease). Check to see if there is that much space available in the tablespace. If you REALLY want to be paranoid, do this as if you expect EVERY table and index in the tablespace to extend at the same time. If remaining unallocated space is greater than the next extent allocation you calculate, you have enough space. If it is not, you have to add a datafile or extend the existing one. Or am I missing something? Rachel --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: I have been reading some docs on free space etc., but I have not yet found any conclusive document on how to monitors tables/indexes in LMT environment so that we could *predict* when a segment would not be able to allocated extent. Basically we want to monitor, when we needs to add more space to a tablespace, at-least 1 day before we get error messages. Devl team doesn't always tell us before dataload (it happens, and now they have been warned). I believe this is little bit complex compared to dictionary managed tablespaces, as (in auto allocate mode), there is no fixed published formula for next extent ... Any ideas? TIA Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2 __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 improve queries remotely
I have found in my testing that an inline view works just as well as a normal view . If you are unsure, run explain plan and check the OTHER column in the plan_table. It'll show what is being executed remotely. Kevin -Original Message- Sent: Wednesday, March 05, 2003 5:40 PM To: Multiple recipients of list ORACLE-L I am not a fan of views, but build remote views to have has much screening of data and processing done remotely before you return the data. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 Reliable is Explain Plan in 9.2
Yes, the v$sql_plan is new in 9i -Original Message- Sent: Thursday, March 06, 2003 10:34 AM To: Multiple recipients of list ORACLE-L MessageIs that something new in 9i? I don't have it in 8.1.7. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 05, 2003 4:19 PM A, that is what he is talking about, I wasn't aware of v$sql_plan. -Original Message- Sent: Wednesday, March 05, 2003 2:30 PM To: Multiple recipients of list ORACLE-L As far as I can tell, the Explain Plan is 100% reliable. It shows how the query would execute if it was run from that session at that momement in time. If you want to see how a query in the SGA actually ran, query the Oracle9i v$sql_plan view. Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 Reliable is Explain Plan in 9.2
Title: Message As far as I can tell, the Explain Plan is 100% reliable. It shows how the query would execute if it was run from that session at that momement in time. If you want to see how a query in the SGA actually ran, query the Oracle9i v$sql_plan view. Kevin -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 2:30 PMTo: Multiple recipients of list ORACLE-LSubject: RE: How Reliable is Explain Plan in 9.2 Wolfgang ... Now that explain plan is unreliable and will be even more in10i, I'll always keep a silver dollar in my pocket. The coin toss is still right(in almost) 50% of the time. Jeez ... what's next ... in 11i, SELECT statement *may* return data and in some cases would actually perform inserts into some other tables, because that's what you *wanted to do* anyways ... right? I want my dBase IV back ... Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message-From: Wolfgang Breitling [mailto:[EMAIL PROTECTED]Sent: Wednesday, March 05, 2003 2:09 PMTo: Multiple recipients of list ORACLE-LSubject: Re: How Reliable is Explain Plan in 9.2 Yes, explain plan will become increasingly unreliable as the cbo takes more and more factors and current conditions current into account. Some of the factors that can change the outcome of a parse from session to session are: for Oracle 8 - different session parameters (db_file_multiblock_read_count, hash_multiblock_io_count, sort_area_size, hash_area_size) for Oracle 9i additionally - you can let Oracle dynamically set the sort_area_size and other memory parameters so you have a moving target now - bind variable peeking - the first parse determines the plan for all following sql depending on its bind value - system statistics in effect at the time of parse. If the system statistics get changed, existing plans do not get invalidated, but if you do an explain the cbo will use the current values - dynamic sampling where the optimizer tries to improve on its estimates by sampling predicate values at the time of parsing. from comments I heard, it will get "worse" (as far as explain differing from reality is concerned) with Oracle 10. The optimizer will try and learn from past executions of a sql and modify the plan if appropriate. At 08:35 AM 3/5/2003 -0800, you wrote: Just had a fellow tell me that explain plan in completely unreliable in 9.2 and getting accurate results requires direct SGA access on executing SQL (he is working in a RAC environment). They are running Precise, a good product, but this sounds like something a sales person told him. I can only recall that occasionally the plan executed is not the plan you see in explain plan. Anyone know the truth of this issue? Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications. Cette communication par courrier électronique est une communication privée à l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'êtes pas le destinataire prévu, vous êtes avisé, par les présentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'êtes pas spécifiquement autorisé à recevoir ce courriel ou si vous croyez l'avoir reçu par erreur, veuillez en aviser l'expéditeur original immédiatement. Nous respectons les demandes similaires qui touchent la confidentialité des communications par courrier électronique.
RE: Optimizer help, get query to run as good as with RULE hint
Glenn What is the execution plan _without_ the DISTINCT? I'd be willing to bet that it uses the same indexes as the RBO. If so, try the following: SELECT /*+ NO_MERGE(x) */ DISTINCT * FROM(original query without the distinct) x; Kevin -Original Message- Sent: Friday, February 14, 2003 11:04 AM To: Multiple recipients of list ORACLE-L I have a problem query which will not complete. This query is part of a report run within Oracle Applications. Our versions are: Oracle Server 8.1.7.4, Apps 11.5.3, HP-UX 11.11 Listed below is the query and the explain plan. I ran full statistics on all the tables immediately before executing the query. Using the CBO, it never returns. I cancel the query, but it won't die until I kill the unix process. If I use the /*+ RULE */ hint, the query plan (also listed below) changes dramatically and the query executes in 30 seconds. What could cause the optimizer to behave so differently? We cannot change our instance to RBO, as it would adversely affect everything else and Apps requires CBO anyway. Any suggestions on what else I could do to improve the explain plan withou having to use the hint? (I've tried setting optimizer_index_cost_adj=10, and it changes the plan a little but still does not complete). 1 SELECT COUNT(*) 2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE, 3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN 4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID 5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID 6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID 7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99) 8* ; Id Par Pos Ins Plan - -- 0 3218SELECT STATEMENT (choose) Cost,rows,bytes (3218,1,31) 10 1 SORT(aggregate) 21 1NESTED LOOPS Cost,rows,bytes (3218,1466,45446) 32 1 HASH JOIN Cost,rows,bytes (3218,617422807,16052992982) 43 1HASH JOIN Cost,rows,bytes (2681,4307,77526) 54 15 TABLE ACCESS (analyzed) AR HZ_CUST_ACCT_SITES_ALL (full) Cost,rows,bytes (1263,4307,34456) 64 22 TABLE ACCESS (analyzed) AR HZ_PARTY_SITES (full) Cost,rows,bytes (1414,493760,4937600) 73 24 TABLE ACCESS (analyzed) AR HZ_LOC_ASSIGNMENTS (full) Cost,rows,bytes (533,430060,3440480) 82 2 INDEX (analyzed) UNIQUE AR HZ_LOCATIONS_U1 (unique scan) Using the RULE hint (completes in 30 seconds): 1 SELECT /*+ RULE */ COUNT(*) 2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE, 3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN 4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID 5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID 6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID 7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99) 8* ; Id Par Pos Ins Plan - -- 0 SELECT STATEMENT (hint: rule) 10 1 SORT(aggregate) 21 1NESTED LOOPS 32 1 NESTED LOOPS 43 1NESTED LOOPS 54 14 TABLE ACCESS (analyzed) AR HZ_LOC_ASSIGNMENTS (full) 64 2 INDEX (analyzed) UNIQUE AR HZ_LOCATIONS_U1 (unique scan) 73 22 TABLE ACCESS (analyzed) AR HZ_PARTY_SITES (by index rowid) 87 1 INDEX (analyzed) NON-UNIQUE AR HZ_PARTY_SITES_N2 (range scan) 92 25 TABLE ACCESS (analyzed) AR HZ_CUST_ACCT_SITES_ALL (by index rowid) 109 1INDEX (analyzed) NON-UNIQUE AR HZ_CUST_ACCT_SITES_N1 (range scan) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Re: parallel index creation again:in which case, can we use p
- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: parallel index creation again:in which case, can we use p
41540.94 PX Deq: Execute Reply 176 34033.54 PX Deq Credit: send blkd 47,704 24824.47 control file parallel write 112 5 .48 PX Deq Credit: need buffer 1,835 4 .38 - ^LWait Events for DB: ORA9 Instance: ora9 Snaps: 19 -20 - s - second - cs - centisecond - 100th of a second - ms - millisecond -1000th of a second -- Best regards Michael Ivanov, TD ERA = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Count(*) last 30 seconds
With Oracle 6 and sometimes in Oracle 7 one count was faster than the other. With Oracle 8.0+ you'll see that count(1) == count(*) == count(unique column) == count(not null column) Don't believe me? Try it. -Original Message- Sent: Tuesday, February 11, 2003 12:55 PM To: Multiple recipients of list ORACLE-L Interestingly enough, I haven't seen an official statement on count(*) being slower than count(column name). Sunil Nookala DBA Dell Corp. -Original Message- Sent: Tuesday, February 11, 2003 11:09 AM To: Multiple recipients of list ORACLE-L Hi, 1. create unique index or primary key AND update statistics of the table 2. use count(1) instead of count(*) JP On Tuesday 11 February 2003 17:19, you wrote: Hi list, I issue a select count(*) from mytable and last 30 seconds. The table has 1,466,196 records and were loaded with a batch process, so they are in a countinous space. I consider that time exagerated. The TBS is LMT with a Uniform size of 128 MB. The block size is 8MB, version 9.2.0.1.0 in Windows 2000. Where should I start looking ??? TIA Ramon E. Estevez [EMAIL PROTECTED] 809-565-3121 -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 9i new features survey
We are using automatic undo management and automatic memory management (pga aggregate target) both with much success. -Original Message- Sent: Thursday, January 16, 2003 5:45 PM To: Multiple recipients of list ORACLE-L Hi lister: Just want to know how well you adopt those 9i new features. (if you are already 9i) and what's your recommendations about those new features. (any pros and cons) TIA --DJ ITResource == Oracle9i Database Release 1: New Features ANSI ISO SQL Support In Oracle9i Automatic Segment Free Space Management Automatic Undo Management Bitmap Join Indexes Data Guard Export And Import Enhancements In Oracle9i Flashback Query High Availability Enhancements in Oracle9i Index Organized Table (IOT) Enhancements in Oracle9i Index Skip Scanning LOB Enhancements In Oracle9i Logminer Enhancements In Oracle9i Memory Management In Oracle9i Metadata API Multiple Block Sizes Oracle Managed Files Partitioning Enhancements In Oracle9i Performance Enhancements In Oracle9i Persistent Initialization Parameters Real Application Clusters Recovery Enhancements In Oracle9i Recovery Manager (RMAN) Enhancements In Oracle9i Resource Manager Enhancements In Oracle9i Resumable Space Allocation Scalable Session Management Security Enhancements In Oracle9i SQL New Features In Oracle9i Workspace Management In Oracle9i Advanced Queuing In Oracle9i External Tables Heterogeneous Services Oracle9i Database Release 2: New Features Export BLOB Contents Using UTL_FILE DBMS_XPLAN Parse XML Documents Renaming Columns And Constraints UTL_FILE Enhancements _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Jones INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Multiple block sizes in 9i
Using transportable tablespaces to move data from the operational database to the ODS or warehouse is one. -Original Message-From: Jos [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 16, 2003 8:14 AMTo: Multiple recipients of list ORACLE-LSubject: Multiple block sizes in 9i List, Oracle 9i supported multiple block sizes, I am wondering under what circumstancesone would setup a database with multiple block sizes? Jos Yahoo! Greetings- Send your seasons greetings online this year!
RE: slowish query causing problems...
Title: slowish query causing problems... Ouch! I hate to see queries written this way...the query should answer the question you are trying to ask. It appears you are checking for the existance of a record in fwepcode1 that matches the criteria. If I understand the query correctly, you want to return 1 row if one of 2 conditions is met. 1) A row in fwepcode1 that matches 1 of the 3 where clauses 2) A row does not exist in valuelist with the below conditions. My suggestion for rewriting the query is below. Kevin SELECT 1 FROM fwepcode1 WHERE wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2' AND rownum = 1 UNION SELECT 1 FROM dual WHERE NOT EXISTS ( SELECT 1 FROM valuelist WHERElistname = 'STATUS' ANDmaxvalue = 'A' ANDvalue= 'INPRG') )l -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 6:29 AMTo: Multiple recipients of list ORACLE-LSubject: slowish query causing problems... Hello List, Pls help me on this problem. Our application does a validation when it uses a certain screen, as it so happens this screen is used very intensively. The performance is very slow, I have isolated the main culprit. I have tried the following. I have dropped all the indexes and tried recreating them individually. Each time I have run an explain plan on the query, the optimizer (both rule and Choose) have chosen to do a FULL table scan on the fwepcode table. Even when using a hint to explicitly use the index it still uses FULL. This is very frustrating indeed. SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') OR 'INPRG' NOT IN (SELECT VALUE FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A') Is the reason that the optimizer does not use any of the indexes because of the SELECT DISTINCT (1)? I have tried adjusting this query slightly to remove this and it still insists on doing a full table scan. Funny enough the sub query on valuelist table does use a index. The table contains 8920 rows. The cost according to the explain plan is 703 and bytes 9834. The system is a Oracle 817 on Win2k. Pls advise, any options or help will be appreciated. Many Thanks Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity." Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
RE: corrupted database
Title: corrupted database We had a situation where the filesystems were cross mounted. You know /ora1234 mapped to the same physical disks on the EMC array as /ora3253 filesystem! Luckily we weren't in production! -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Friday, December 20, 2002 1:55 PMTo: Multiple recipients of list ORACLE-LSubject: corrupted database Yes. Twice. First time - bad controller. It wrote CRAP in random spots throughout the database. We had to recover after the hardware was replaced. Second time - no idea. Oracle Support said they'd need mounds of info from the o/s and even then they may not be able to explain it. Since I didn't have the time to gather all the necessary info we let it go. What a lovely thought for a Friday afternoon. At least my databases aren't corrupted. Lisa Koivu Oracle Datababy Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Lyndon Tiu [mailto:[EMAIL PROTECTED]] Sent: Friday, December 20, 2002 1:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle connection/listener/dispatcher stops allowing connecti The reason I ask is because people around me always blame Oracle when things stop working. Anyways, the problem was traced to a corrupted Oracle database (as to whether the tables or the data file got corrupted. how and why - a $$$ consultant is trying to find out). How could an Oracle database get corruppted in the first place? Anyone here with an experience of their Oracle database getting corrupted and what caused it and what was done to fix it? -- Lyndon Tiu Quoting DENNIS WILLIAMS [EMAIL PROTECTED]: Lyndon I would look at the wait statistics to see what is happening inside Oracle. I would also look at the O.S. performance statistics to see what is happening underneath Oracle. Don't make any rash assumptions. Also, are any error messages or trace files generated? For me the funny story was a misunderstanding of the Unix "nice" value for an unfamiliar platform. Long story short, we wound up with batch running at a higher priority than interactive users. New users were shut out. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 20, 2002 9:45 AM To: Multiple recipients of list ORACLE-L connections Hello, Just in time for the Holidays. Oracle stops accepting connections. I am wondering if people here can give me their horror stories when Oracle stops accepting new connections or stops accepting connections altogether? Scalability problems when you've got around 5,000 concurrent connections? MTS/Shared server configurations enabled or disabled? It could be an Oracle problem or the application that's trying to connect's fault. Any tips and insights into what caused your horror story and how it was fixed. Thanks. Have a Happy Holidays everybody. -- Lyndon Tiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lyndon Tiu INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lyndon Tiu INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of
Happy Holidays
Tis the season to give. At least thats what i've been told. Anyway, I've put together a little page of my favorite sql scripts. (I've actually written only about 1/2 of them. The rest are stolen from various sources, including this list!) http://www.tuningoracle.com/my_stuff/sql Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 9.0.1 and 9.2
The move to 9.2 from 9.0.1 requires a migration rather than a simple update. Can you explain this? I've upgraded multiple databases (Solaris) from 9.0.1 to 9.2.0 by doing a simple upgrade. -Original Message- Sent: Thursday, December 05, 2002 4:18 AM To: Multiple recipients of list ORACLE-L Stefan, The move to 9.2 from 9.0.1 requires a migration rather than a simple update. Whether you want to go through that is your choice really. If I had the disk to burn I'd go for the fresh install but then I like to have a version of everything available. Regards, Mike Hately -Original Message- Sent: 05 December 2002 08:29 To: Multiple recipients of list ORACLE-L Hi everybody I've got 9.0.1 and 8.1.7 running on one laptop (to test stuff). Now I want to upgrade the 9.0.1 to 9.2. Does it make sense to just install the 9.2 to a new Oracle home or would it be better to get rid of 9.0.1 and do a fresh install of 9.2 ? Can 9.2 generally be seen as a minor update like 8.1.5 - 8.1.6 or more like 8.0.5 to 8.1.x, which would be a different major version ? Regards, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Hately, Mike (NESL-IT) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: To_Number
Title: To_Number
My
guess is that you have leading or trailing spaces. try
select
to_number(LTRIM(RTRIM(unit_cost)),'$999,999,999.99') from
elas.qdr
-Original Message-From: Burton, Laura L.
[mailto:[EMAIL PROTECTED]]Sent: Thursday, December 05, 2002
2:24 PMTo: Multiple recipients of list ORACLE-LSubject:
To_Number
I have a table which contains a
Unit_Cost varchar2(16) which contains $34,000.05. I can enter select to_number('$34,990.08','$999,999,999.99') from dual; and the results is 34990.08. However when I
enter select
to_number(unit_cost,'$999,999,999.99') from elas.qdr I get ora-01722: invalid number.
Is there any other way to do
this? I am trying to add a varchar2 field
that contains $ and commas. I thought the
to_number function would convert the data to a number field.
Thanks,
Laura
RE: Oracle is a time machine!!
TO_CHAR(TH
--
10/15/1582
-Original Message-
Sent: Friday, November 22, 2002 12:55 PM
To: Multiple recipients of list ORACLE-L
Don't believe me?? Try this:
create table test(the_date date);
insert into test values (to_date('10-05-1582','mm-dd-') );
select to_char(the_date, 'mm/dd/') from test;
What do you get? :-))
Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!
Londo Mollari: Ah, arrogance and stupidity all in the same package. How
efficient of you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Freeman, Robert
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be 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: Toepke, Kevin M
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Oracle is a time machine!!
okay, time to nip this discussion in the bud...
First of all, there are/were 2 calendar systems. The gregorian and the
juilan calendars. They both got out of synch with the seasons (due to no
leap year or something). They were both adjusted forward a number of days to
synch back up the real world.. One of them (the one Oracle uses) just so
happened to jump forward in October 1582. The other one (used by UNIX) on a
different date.
Read about it here...http://serendipity.magnet.ch/hermetic/cal_stud.htm
-Original Message-
Sent: Friday, November 22, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
It must have converted the date to metric.
-Original Message-
Sent: Friday, November 22, 2002 12:55 PM
To: Multiple recipients of list ORACLE-L
Don't believe me?? Try this:
create table test(the_date date);
insert into test values (to_date('10-05-1582','mm-dd-') );
select to_char(the_date, 'mm/dd/') from test;
What do you get? :-))
Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!
Londo Mollari: Ah, arrogance and stupidity all in the same package. How
efficient of you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Freeman, Robert
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be 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: Hayes, Scott
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be 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: Toepke, Kevin M
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Invalid Objects but no errors
try SHOW ERRORS PACKAGE BODY DBMS_REPCAT_RGT show errors may be showing there are no errors in the package spec! Kevin -Original Message- Sent: Friday, November 15, 2002 12:40 PM To: Multiple recipients of list ORACLE-L I checked for invalid objects and two of them came up. When I tried to recompile them it says there was compilation errors. Yet show errors doesn't show anything. Does anyone have familiarity with this particular package or have an idea on how to fix this so it is valid? SYSDBMS_REPCAT_RGTPACKAGE BODY SVRMGR alter package dbms_repcat_rgt compile body; MGR-00072: Warning: PACKAGE DBMS_REPCAT_RGT created with compilation errors. SVRMGR show errors No errors for PACKAGE DBMS_REPCAT_RGT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: database link tuning
Yes, be particularly careful when accessing tables across database links. The threshold for when a FTS is more efficient than an index access changes. Also, i found out this week, that if you use in-line views in your query, Oracle can send the whole in-line view across the database link!. In the following example, oracle will execute the inline view on the cust database! Without the inline view, oracle chose a FTS of tablea hash joined to FTS of tableb. SELECT * FROM (SELECT * FROM tablea@cust, tableb@cust WHERE tablea.key = tableb.key) t ,tablec WHERE tablec.key = t.key -Original Message- Sent: Friday, November 08, 2002 2:43 PM To: Multiple recipients of list ORACLE-L Paula I was hoping someone with more specific experience in this area. Here is a quote from Oracle Performance Tuning 101: Be particularly when joining a local table to a remote table. My vote if you only have 60 rows would be to populate a local table with those rows before running your query. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, November 08, 2002 11:40 AM To: Multiple recipients of list ORACLE-L BTW, Was doing join of views pointing to tables through 2 database links and gateway. Just wondering - faster to move the tables then perform join locally esp. if I can do CTAS each time - h. -Original Message- Sent: Friday, November 08, 2002 12:34 PM To: '[EMAIL PROTECTED]' Trying to bring over 60 rows with database link and write to table using CTAS. It is taking forever - going through Oracle - going through another Oracle that is hooked up with a gateway to Informix - yuck!! The waits are: Event Waits Waits/sec % total Wait Time (ms) Wait Time/sec % totalAverage Wait SQL*Net more data from dblink 1352 80.870917573872599.120234604105614250 852.374685967221 0 0 Any ideas how to speed up other than getting another gateway, creating ascii file and using direct load. Is there a way to tune related to Net8, database links? Thanks, Paula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 Brain Teaser Challenge
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sqlplus /nolog
Title: sqlplus /nolog It doesn't? $ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.1.0 - Production on Tue Oct 29 10:39:53 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to:Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.1.0 - Production SYS@hydra@fins SQL -Original Message-From: Rodd Holman [mailto:[EMAIL PROTECTED]]Sent: Tuesday, October 29, 2002 9:54 AMTo: Multiple recipients of list ORACLE-LSubject: Re: sqlplus /nologIt opens up a sqlplus session session/shell on the server without logging into a database. The 9i version of sqlplus does not allow for a command line: sqlplus / as sysdba command. You have to login to sqlplus and when prompted for the username then add / as sysdba. The /nolog allows you to script running sqlplus and then having the connect / as sysdba as the first line in your sqlscript ie: sqlplus /nolog @myscript.sql myscript.sql: connect / as sysdba select name from v$database; Rodd On Tue, 2002-10-29 at 07:29, Daiminger, Helmut wrote: Hi! I was wondering what sqlplus /nolog actually does on Unix? Is it only used for not listing username/password when doing a ps ? Or anything else? This is 8.1.7 on Sun Solaris. Thanks, Helmut -- Rodd Holman Enterprise Data Systems Engineer LodgeNet Entertainment Corporation [EMAIL PROTECTED]
RE: SQL Joins vs Sub Queries vs Cursors
The answer, as always is: it depends. Each situation is different. The only way to tell is to run all versions of the query each time. -Original Message- Sent: Tuesday, October 15, 2002 6:19 AM To: Multiple recipients of list ORACLE-L Which is best Joins vs. Sub Queries vs. Cursors if all options are possible in a given situation? from a performance perspective) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: No Nulls? (was: Warehouse design: snowflake vs star schem
Title: Message
Use
your friendly FBI (function-based indexes)
create
index my_fbi on my_table (
nvl(end_employment,
TO_DATE('01-jan-4000'));
Then
the following select will use the index.
select * from table where
NVL(END_EMPLOYMENT,TO_DATE('01-jan-4000')=
to_date('01-jan-4000');
-Original Message-From:
Grabowy, Chris [mailto:[EMAIL PROTECTED]]Sent: Monday, October 14,
2002 5:11 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: No Nulls? (was: Warehouse design: snowflake vs
star schem
Hmmm...but what about the index? Which is
faster?
select * from table where END_EMPLOYMENT IS NULL;
OR
select * from table where END_EMPLOYMENT =
'01/01/4000';
I
like NULL, but I am leaning towards Igor, and others, to agree upon and use a
default value, or a "business sense" replacement value for NULL. I want
to be able to take the awesome advantage of anindex...versus FTS?
Am I headed in the wrong direction?? Any other
thoughts??
-Original Message-From: Fink, Dan
[mailto:[EMAIL PROTECTED]] Sent: Monday, October 14, 2002 4:49
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
No Nulls? (was: Warehouse design: snowflake vs star
schem
The problem I see with NO NULLS is that artificial data must be
created, where the data is truly not known. Whether you deal with NULLs or
artificial data, you will always have to code accordingly, so it is a wash.
Igor's example is angood one. When I write an app to access the
END_EMPLOYMENT date, I must handle a date of '01/01/4000'. Or I can handle
the NULL condition. As a person who has had to support some very convoluted
code, I'd rather deal with NULL. What if the employee record contained
TERM_CODE? I would rather have the value NULL, meaning they have not been
terminated rather than dealing with hard-coded or lookup
values.
-Original Message-From: Igor Neyman
[mailto:[EMAIL PROTECTED]]Sent: Monday, October 14, 2002
2:14 PMTo: Multiple recipients of list
ORACLE-LSubject: Re: No Nulls? (was: Warehouse design:
snowflake vs star schem
END_EMPLOYEMENT date for still employed employees equals
to "01/01/4000" (or any other pre-defined date in distant
future).
Igor Neyman, OCP DBA[EMAIL PROTECTED]
- Original Message -
From:
Adams, Matthew (GECP, MABG,
088130)
To: Multiple recipients of list
ORACLE-L
Sent: Monday, October 14, 2002 3:39
PM
Subject: RE: No Nulls? (was:
Warehouse design: snowflake vs star schem
"No application that I can reasonably think of should
use NULLS, except those pre-81 where there are obsolete columns."
Everytime somebody says this to me, I ask them:
How do you handle still employed employees in an
EMPLOYEE table that contains a END_EMPLOYEMENT
date column?
What's your take?
Matt Adams - GE Appliances -
[EMAIL PROTECTED] Write a poem about a
haircut! But lofty, noble, tragic, full of love, treachery, retribution, quiet heroism in the face of certain
doom! Six lines, cleverly rhymed, and every word
beginning with the letter s!
-Original Message- From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 14, 2002 2:29 PM To: Multiple recipients of list ORACLE-L Subject: Re:No Nulls? (was: Warehouse design: snowflake vs star
schem
Jesse,
I'll refrain from personal comments,
but on CJ's quote, he's correct. Nulls are
an oddity. They cannot be true or false (column_name =
NULL or column_name != NULL), nor can
they equal anything. They are in effect a third logical state of nothingness. You also have to code most
applications with indicator variables to check
for their existence. All in all a real pain in the
backside. BUT, if you give me the possibility
that nulls exist in the data I much prefer using
them vs. many a third party solution of a single space. No
application that I can reasonably think of should use
NULLS, except those pre-81 where there are
obsolete columns.
Dick Goulet
Reply
Separator Author: "Jesse;
Rich" [EMAIL PROTECTED] Date: 10/14/2002 9:33
AM
On the link below is this quote from C.J.Date:
"I don't want you to think that my SQL solution to your
problem means I advocate the use of nulls.
Nulls are a disaster."
Of course, he doesn't expound upon it (probably not a
need except for dummies like me). Anyone
care to comment? (On
RE: RE: Re[2]: No Nulls? (was: Warehouse design: snowflake vs
of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments.http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: MIcrosoft Blackmail
Title: OT: MIcrosoft Blackmail I view a RD project not as"adding value to thebusiness", buttraining for myself. Everytime I have participated on an RD project I have learned a significant amount and have gained invaluable experience! If you go in expecting to throw away all of your "work", you will be frustrated. If you go in expecting to learn about a technology or product in a "real world" setting, then you'll come away enriched by the experience! -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 26, 2002 11:54 AMTo: Multiple recipients of list ORACLE-LSubject: RE: MIcrosoft Blackmail you obviously have not wasted enough time with tasks like this. they really suck. you end up spinning your wheels for a week, all for nothing. your report ends up on somebody's shelf someplace - never read. and 6 months later, they ask the same questions. the biggest task that a body needs to learn is how to duck these research projects. unless you *really* like doing them. Tom Mercadante Oracle Certified Professional -Original Message-From: Inka Bezdziecka [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 26, 2002 11:04 AMTo: Multiple recipients of list ORACLE-LSubject: RE: MIcrosoft Blackmail Well, regardless of MS tactics, the last paragraph reads: "Therefore, I support investigating SQL server, Biz Talk, and dot Net, but I emphasize the word INVESTIGATING. " What is better than RD projects? At the end one wins no matter what. Knowingmore does not hurt, does it? inka -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 26, 2002 10:23 AMTo: Multiple recipients of list ORACLE-LSubject: RE: MIcrosoft Blackmail Exactly. In other words, thank the MS-sales-dweeb for his time, and tell him "bye-bye now", "no-more-sales-for-you". Tom Mercadante Oracle Certified Professional -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 26, 2002 9:43 AMTo: Multiple recipients of list ORACLE-LSubject: Re: MIcrosoft Blackmail What exactly is your problem? Lets say that you are a factory that sells paper. You need to buy a computer system. One supplier also sell printers and the other advocate paperless office. All things being equal, which one will you give your business to?? Yechiel AdarMehish - Original Message - From: Thomas Jeff To: Multiple recipients of list ORACLE-L Sent: Thursday, September 26, 2002 1:13 AM Subject: OT: MIcrosoft Blackmail This came to our DBA team today. I'd appreciate your thoughts. I'm not a business guy, just a plain old Apps DBA, but this really pisses me off. Is it common practice by MS? It is important from an Architecture point of view that we understand all the various approaches to "web services" (also known as "grid computing" -- see my recent report). Microsoft's dot Net initiative is their approach to this grand overarching software strategy. There is a second reason why we might be interested specifically in dot Net. Subsidiary XYZ earns $xyz a year for us from Microsoft by [performing certain services], etc. Microsoft has told our management that one of their criteria for evaluating their vendors will be how good of a MS customer is the potential vendor. Specifically, has the vendor bought in to the dot Net strategy. Now we aren't going to make our global enterprise solutions strategy decisions based upon that point alone, but it's not something we are going to ignore either. Therefore, I support investigating SQL server, Biz Talk, and dot Net, but I emphasize the word INVESTIGATING.
User / Synonym Question
Good Morning/Afternoon/Evening! I have been charged with implementing a data-archive strategy into an existing, production system. The only issue I have remaining is that damagement wants the developers programs to only see the active data when they query the transaction table -- and the power users to see a partition-view of active and history data when they query the transaction table. program connects SELECT * FROM transaction; -- hits the transaction table power user connects SELECT * FROM transaction; -- hits the transaction_all view Is there any easy way to do this without reverting to the use of private synonyms for each power user? I've fought the good fight and have been unable to convince damangement that this is not the best way to do this, so I'm looking for a technical solution. TIA Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: User / Synonym Question
Stephen I missed one example... Developer Connects SELECT * FROM transaction; -- hits the transaction table I had thought of public synonym for user, private synonym for application. But then I would have to create private synonyms for each developer. I know, I know. The developers should be smart enough to know which table they accessing. Trust me, they aren't. Kevin -Original Message- Sent: Monday, September 23, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Create the program to access the transaction table within its schema directly and use a public synonym for power users. That should work. If the transaction table is owned by another user, create a single synonym within the program schema and a public synonym thereafter. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Monday, September 23, 2002 11:28 AM To: Multiple recipients of list ORACLE-L Subject:User / Synonym Question Good Morning/Afternoon/Evening! I have been charged with implementing a data-archive strategy into an existing, production system. The only issue I have remaining is that damagement wants the developers programs to only see the active data when they query the transaction table -- and the power users to see a partition-view of active and history data when they query the transaction table. program connects SELECT * FROM transaction; -- hits the transaction table power user connects SELECT * FROM transaction; -- hits the transaction_all view Is there any easy way to do this without reverting to the use of private synonyms for each power user? I've fought the good fight and have been unable to convince damangement that this is not the best way to do this, so I'm looking for a technical solution. TIA Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from
RE: User / Synonym Question
Dennis: The goal of the partitioning/archive are: better performance, faster and fewer full-table scans, faster RMAN backups (move history to read-only tablespaces), indexing history differently than active data to speed research and to generally reduce the frequency of the the database is running slow, please investigate pages. Yes, we are using partitioning (or hope to if management will approve the design. Don't ask.) Most of the applications will not be effected by the partitioning-based design. Only the reports and a couple screens will have to be changed. Yes, this is a hack to improve the performance of a system that had the database originally designed by the power users. (Again, don't ask.) One of the stated acceptance criteria for the design was minimal code impact. (back to the management making the design decisions. Oh well.) I guess I'm off to figure out a way to automate the creation of private synonyms for developers. Kevin -Original Message- Sent: Monday, September 23, 2002 12:28 PM To: Multiple recipients of list ORACLE-L Kevin I have always heard private synonyms are the preferred way to accomplish what you want. If you prefer, you could assign developers the private synonyms instead and everyone else gets the public one that gives access to the entire table. Looking at the larger issue, what is your goal with this archiving strategy? Faster full-table scans? Have you investigated Oracle partitioning? Will any applications be used to access these tables, and how will their queries be affected? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 10:28 AM To: Multiple recipients of list ORACLE-L Good Morning/Afternoon/Evening! I have been charged with implementing a data-archive strategy into an existing, production system. The only issue I have remaining is that damagement wants the developers programs to only see the active data when they query the transaction table -- and the power users to see a partition-view of active and history data when they query the transaction table. program connects SELECT * FROM transaction; -- hits the transaction table power user connects SELECT * FROM transaction; -- hits the transaction_all view Is there any easy way to do this without reverting to the use of private synonyms for each power user? I've fought the good fight and have been unable to convince damangement that this is not the best way to do this, so I'm looking for a technical solution. TIA Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Invitation: OOUG Meeting: October 24th, 2002. Dual Track Meeting
Oracle Developers, DBAs, Managers You are cordially invited to attend the first Dual Track meeting of the Ohio Oracle Users Group When:Thursday, October 24, 2002 8:00am - 4:00pm. Happy Hour following the meeting Where: Dublin Community Recreation Center in Dublin, Ohio 5600 Post Rd Dublin, Ohio 43123 Cost: $20.00 to non-members Free to members For more information, maps please visit the OOUG website at www.ooug.org Please RSVP to [EMAIL PROTECTED] by Thursday, October 10, 2002 as space is limited to the first 200 regististrants. *Early Bird Special: RSVP by September 27, 2002 and be automatically entered in a special drawing for a $100 Gift Certificate!* Agenda Time Speaker Topic 8:00amRegistration 9:00amBusiness Meeting * Website tour * Message Board Tour * Evaluation Feedback * Corporate Membership Overview * Elections 9:45am Headline Sponsor Address 10:00am Keynote Address by Paul Dorsey -- Dulican, Inc and IOUG 11:30am Ask the Experts Noon Lunch and Vendor Hall Exhibits 1:00pm - 4:00pm Dual Track Sessions 4:00pm Happy Hour DBA Track 1:00pmOracle Workspace Manger -- Bill Beauregard from Oracle Corporation 2:00pmPerformance Tuning -- Bill Burke 3:00pm9iAS -- Oracle Technical Speaker Developer Track 1:00pmJDeveloper 9i - What's Hot? What's Not? -- Paul Dorsey from Dulican, Inc and IOUG 2:00pmData Warehousing -- Steve Brown and Kim Beery 3:00pmTBA Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: bind variables
Yes. -Original Message- Sent: Friday, September 06, 2002 4:28 PM To: Multiple recipients of list ORACLE-L Kevin, Are you saying then, that by default, any static statement that is executed within PL/SQL will not have be re-parsed eg sp_proc(var in varchar2) as begin select last_name from emp where last_name = var; end; If that's the case, I wont have to change much code. mkb --- Toepke, Kevin M [EMAIL PROTECTED] wrote: Actually its easy. Any variable declared in PL/SQL and referenced in a non-dynamic SQL statement is a bind variable. In the following example (#1), some_var is an output bind-variable and other_var is a input bind variable. PL/SQL does manipulation on the statement and will send something like the following (#2) to the database #1 DECLARE some_var NUMBER(1); other_var NUMBER(1) BEGIN SELECT 1 INTO some_var FROM my_table WHERE my_column = other_var; END; #2 SELECT 1 FROM MY_TABLE WHERE MY_COLUMN = :1 Kevin -Original Message- Sent: Friday, September 06, 2002 1:59 PM To: Multiple recipients of list ORACLE-L John, You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title. Dick Goulet Reply Separator Author: John Dunn [EMAIL PROTECTED] Date: 9/6/2002 7:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse
RE: bind variables
and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna 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: Nicoll, Iain \(Calanais\) 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: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Toepke, Kevin M 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: Same SQL statement, Same Oracle, Different OS == Different E
Title: RE: Same SQL statement, Same Oracle, Different OS ==> Different Expla I the RBO, the order the indexes were created in is important! I was able to show this to management on a project I was on. How? By doing a difinitive proof (follows) Import the table and data into an empty database. Create index A Create index B EXPLAIN PLAN shows query using index A. Drop table Import the table and data into an empty database Create index B Create index A EXPLAIN PLAN shows query using index B. Drop table Import the table and data into an empty database Create index A Create index B EXPLAIN PLAN shows query using index A. All other things being equal, the RBO will choose the index with the lower object_id! Proof took place in Oracle 8.0.5 on a Sun Solaris box. Kevin -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]Sent: Friday, September 06, 2002 3:28 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Same SQL statement, Same Oracle, Different OS == Different E (see answer below) -Original Message- From: Sam Bootsma [mailto:[EMAIL PROTECTED]] One of our developers is encountering a situation where Oracle 9.0.x explain plan chooses one index when on UNIX, and chooses a different index when running on Windows NT. I'd appreciate any insights or similar experiences. The following are the facts: 1. The explain plan is run against the same SELECT statement on both platforms 2. It has been confirmed that there are no statistics on either of the databases 3. Both databases are using RBO (not CBO) 4. The UNIX database has about 100 times as many rows (in this table) as the NT database 5. The SELECT statement that gives different explain plans on different platforms is: SELECT FN.*, FN.ROWID FROM UNITFUND FN WHERE FN.FU_CODE = :cFuCode AND FN.MKEY = :cMkey AND FN.CLNT = :sKey AND FN.PLANNO = :sKey AND FN.DATE_FROM = :dDate AND FN.SOURCE = :cSource AND FN.TSTATUS = 'O' ORDER BY FN.DATE_FROM, FN.TSECOND; 6. Between the following 2 indexes, Oracle 9.0x chooses (2) on Unix and (1) on Windows NT. 1) clnt, mkey, planno, fu_code, date_from, source, tracode, tsecond... 2) date_from, clnt, planno, mkey, fu_code Just a wild guess, but maybe the optimizer is just picking the first index it finds because it thinks both are equally good candidates. Were both indexes created in the same order on both databases? I.e. is Object_id (from dba_objects) for Index A smaller than object_id for Index B on the UNIX database, but the reverse is true on Windows? You say the databases are using RBO. How do you know? Remember that if you use some new features (from the manual: Partitioned tables and indexes Index-organized tables Reverse key indexes Function-based indexes SAMPLE clauses in a SELECT statement Parallel query and parallel DML Star transformations and star joins Extensible optimizer Query rewrite with materialized views Enterprise Manager progress meter Hash joins Bitmap indexes and bitmap join indexes Index skip scans ) the query optimizer will use CBO because new features are not supported by RBO. Finally, this section of the manual may help you guess what's happening: Oracle9i Database Performance Guide and Reference Part Number A87503-02 Chapter 8 Using the Rule-Based Optimizer ... Understanding Access Paths for the RBO
RE: open cursors problem
WHAT! A cap of 750 open cursors in the database? Where did you hear that? -Original Message- Sent: Monday, July 29, 2002 3:38 AM To: Multiple recipients of list ORACLE-L Hi everyone, I am running EJB under weblogic 5.1 jdbc 2.0, and when I last checked the database the number of open cursors was 564. I know 8.1.7 has a cap of 750 open cursors after which the db will crash. Since this is the first time I have faced this problem I would be grateful if anyone could point out what could be the possible areas that could cause this problem ( other than the logical one of not closing cursors once they have been opened and used !). any help here would be appreciated. thank you, Gavin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gavin D'Mello 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: Toepke, Kevin M 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: Explain Plan and SQL Text Length
Ian: Any valid SQL statement can be explained. I've explained queries that were well over 4k -- even when you excluded the whitespace! Kevin -Original Message- Sent: Wednesday, July 24, 2002 11:50 AM To: Multiple recipients of list ORACLE-L What is the longest SQL statement that can be analyzed via explain plan. Is it the maximum length equal to the maximum length of a varchar2. Does the new virtual explain view have any problems with the length of the statement? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: bind vars change explain plan
Mike: According to the docs, the first time an query is parsed, Oracle peaks at the bind variable and bases the explain plan on those values. The values of the bind variables are never looked at again Kevin -Original Message- Sent: Tuesday, July 23, 2002 6:58 AM To: Multiple recipients of list ORACLE-L Hi, Beginning with Oracle9i, the optimizer will consider bind variable values when choosing execution plans. Does anyone know how Oracle manages to do this? The 9i Database Performance Tuning Guide infers that the treatment of bind variables has not changed but maybe we attribute this to manual lag. Presumably it must perform some kind of parse to rejig the execution plan despite the use of bind variables. Somewhere between hard and soft perhaps. A fairly solid parse. Anyone know the mechanics of this? Has anyone seen this new functionality in practice? Cheers, Mike Hately -Original Message- Sent: 23 July 2002 09:58 To: Multiple recipients of list ORACLE-L From the O'Reilly Oracle SQL Tuning Pocket Reference There is one situation in which bind variables are not such a great choice. If you have column data in a table having a disproportionate number of rows with certain values, and a very small number of rows with other values, you should be using histograms. Bind variables cannot use histogram information. Using bind variables will prevent the optimizer from doing this, [using histograms] because the optimizer is unaware of the value that will be in the bind variable at the time it decides on the execution plan Beginning with Oracle9i, the optimizer will consider bind variable values when choosing execution plans. HTH Kev. This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike 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: Toepke, Kevin M 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: Rant-Rant
of my own. It seems that the implicit expectation is that every DBA should be or should aspire to be a Master Technical DBA. I have a slightly different take on the situation. It is a little convoluted but I believe that the DBA world needs some additional job classifications. In a decent sized organization, the day to day management functions should be accomplished by an Admin DBA who might be someone who was perfectly happy spending his/her working career operating a precision milling machine at Boeing. Since the machinist jobs are going away, I see no reason why a competent machinist could not become a competent admin DBA. Such a person is not suited by aptitude or disposition to become a Master Technical DBA, but would do a great job at the admin level. I'll extend the analogy a little more: the manufacturing organization does not expect the machinist to program the machine. They either have on staff or bring in a numerical control programming specialist. Similarly, the Admin DBA should know which tasks he/she can perform and which tasks should be kicked up or out to the next level. === message truncated === __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: peopleslop HR question
Joe on previous PS HR implementations (versions through PS 7.1) the answer is no. In fact there was no encrypted data in the system. Kevin -Original Message-From: JOE TESTA [mailto:[EMAIL PROTECTED]]Sent: Thursday, July 18, 2002 12:34 PMTo: Multiple recipients of list ORACLE-LSubject: OT: peopleslop HR question For those of you running PS HR, a question was posed to me, is the salary field encrypted, so that the DBA can't go in and look at everyones salary. OB oracle part: OMF stands for what?, Oracle Managed Files. joe
RE: a couple of questions
Rachel Check out the SYS.ANY datatype in Oracle 9i (from the SQL reference). Me thinks its what SQL server would call a variant data type. Follow this link for more info http://download-east.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89852/to c.htm Kevin The Any types provide highly flexible modeling of procedure parameters and table columns where the actual type is not known. These datatypes let you dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type. These types have OCI and PL/SQL interfaces for construction and access. SYS.AnyData This type contains an instance of a given type, with data, plus a description of the type. AnyData can be used as a table column datatype and lets you store heterogeneous values in a single column. The values can be of SQL built-in types as well as user-defined types. -Original Message- Sent: Wednesday, July 17, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Okay, I'm working on what feels like 30 new projects all at once and I WILL be RTFM'ing as soon as I can get more than 5 minutes out of meetings but first: has anyone heard of any problems with 64-bit Oracle on a Solaris 64-bit OS? second (and this one confuses me a bit)... I've been asked if Oracle9i supports a variant datatype -- they are not familiar with oracle but are familiar with SQL Server and say that there is a datatype called variant there where you can basically overload the column with whatever datatype you want (string, number, date) and the database knows what type of data it is storing within the column. They referred me to C++ and Java, neither of which I know. Can anyone point in the right direction to start researching this? Thanks! Rachel __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: RE: a couple of questions
I agree, it looks messy and confusing... However, I found an example that makes it a little easier to understand. http://asktom.oracle.com/pls/ask/f?p=4950:8:1062923::NO::F4950_P8_DISPLAYID, F4950_P8_CRITERIA:3099475696866,%7Banydata%7D My guess it was implemented for 2 reasons 1) to say they have every feature as M$ 2) to support 3rd party vendors porting stuff from M$ Kevin -Original Message- Sent: Wednesday, July 17, 2002 1:35 PM To: Toepke, Kevin M; Multiple recipients of list ORACLE-L Kevin, Looks messy to me, and damned confusing to boot. Dick Goulet The more you overtake the pluming the easier it is to stop up the drain. Scotty of Star Trek, Search for Spock. Reply Separator Author: Toepke; Kevin M [EMAIL PROTECTED] Date: 7/17/2002 9:58 AM Rachel Check out the SYS.ANY datatype in Oracle 9i (from the SQL reference). Me thinks its what SQL server would call a variant data type. Follow this link for more info http://download-east.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89852/to c.htm Kevin The Any types provide highly flexible modeling of procedure parameters and table columns where the actual type is not known. These datatypes let you dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type. These types have OCI and PL/SQL interfaces for construction and access. SYS.AnyData This type contains an instance of a given type, with data, plus a description of the type. AnyData can be used as a table column datatype and lets you store heterogeneous values in a single column. The values can be of SQL built-in types as well as user-defined types. -Original Message- Sent: Wednesday, July 17, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Okay, I'm working on what feels like 30 new projects all at once and I WILL be RTFM'ing as soon as I can get more than 5 minutes out of meetings but first: has anyone heard of any problems with 64-bit Oracle on a Solaris 64-bit OS? second (and this one confuses me a bit)... I've been asked if Oracle9i supports a variant datatype -- they are not familiar with oracle but are familiar with SQL Server and say that there is a datatype called variant there where you can basically overload the column with whatever datatype you want (string, number, date) and the database knows what type of data it is storing within the column. They referred me to C++ and Java, neither of which I know. Can anyone point in the right direction to start researching this? Thanks! Rachel __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Toepke, Kevin M 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: external authentication
Don't do it unless the application is running locally on the same physical server as the database. It is (or at least was) extremely easy to break into a system that uses external authentication across a network. (...don't ask:) The following example shows how to connect to the PLAY database using external authentication $ORACLE_SID=play $export ORACLE_SID $sqlplus / Kevin -Original Message-From: Lyuda Hoska [mailto:[EMAIL PROTECTED]]Sent: Wednesday, July 17, 2002 3:24 PMTo: Multiple recipients of list ORACLE-LSubject: external authentication Does anyone here have experience working with database that supports external authentication application users? My question is how would application know which database to connect to if there are multiple databases on the server? Does it have to be hard-coded in application? Thank you.
RE: Oracle 9.2 spfile catch 22
Just a FYI. You can use _any_ 9i instance to recreate the init.ora for the instance from a spfile. It does not need to be the instance that the spfile is for! -Original Message- Sent: Thursday, July 11, 2002 5:54 PM To: Multiple recipients of list ORACLE-L I was migrating a V7.3.4.5 to V9.2 and the wizard does NOT create a new initSID.ora which is R2 compliant; only a spfileSID.ora file. My solution was to write a simple SQL query which generated a spool file, initSID.ora, which contained the parameter name and values where the value was not the default value. This certainly saved my bacon. Deshpande, Kirti wrote: I would get the DB to function the way I want first using init.ora. Then switch to SPFILE from init.ora, while preserving a copy of working init.ora file. Also, when a parameter is changed dynamically with SCOPE=both or spfile, I recreate my init.ora file as the backup. SPFILE concept is good and it is required for reaching the goal of 'no dba required', but it is still far from being 'ready for prime time'. I stopped using SPFILE with a couple of my databases when I was able to add db_cache_size to an spfile that already had db_block_buffers (two mutually exclusive parameters). The next restart of the DB failed. It was good that I had kept the init.ora file. I have not installed 9.2 yet, so I do now know if such things are verified beforehand and prevented. HTH, - Kirti -Original Message- From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] Sent: Thursday, July 11, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Subject: Oracle 9.2 spfile catch 22 Thanks to everyone for the ideas on the init.ora. You were correct that the answer is indeed with spfile. Now I have discovered a catch-22. Oracle 9.2, Solaris 1. If spfile is created with LOG_ARCHIVE_START = true, database will not start. Receive the error ORA-00439 feature not enabled: Managed Standby 2. Creating the spfile with LOG_ARCHIVE_START = false works and the database starts. Issuing ALTER SYSTEM ARCHIVE LOG START works, but it isn't changed in the spfile, so the next time Oracle is bounced, the archiver doesn't start. This is starting to smell like a bug, but I thought I'd run it past the list on the off-chance someone else has gotten archiving turned on for 9.2. Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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). -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 Lack of planning on your part doesn't constitute an emergency on my part! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Oracle Warehouse Builder
Top of the Morning! I've been tasked by my boss's PHB with performing a pro/con evaluation of Oracle Warehouse Builder without having the benefit of actually installing or using the product. I would greatly appreciate hearing about any real-world experience Warehouse Builder -- both pro con. TIA Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: runInstaller -- Linux help?
PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: 2003 IOUG Meeting
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). ~ Tim Levatich, Database Administrator Cornell Laboratory of Ornithology, 159 Sapsucker Woods Road, Ithaca, New York 14850 [EMAIL PROTECTED]phone 607-254-2113fax 607-254-2415 http://birds.cornell.eduhttp://birdsource.cornell.edu ~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Levatich 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Upgrade from 9.0.1.2 - NOPE!
This is fixed in 9.0.1.3. I have verified it. -Original Message- Sent: Wednesday, July 03, 2002 12:43 PM To: Multiple recipients of list ORACLE-L Cutting and pasting from an old post: by Jonathan Lewis: The upshot of it seems to be that anyone who can get an sql session can look at any data, and given 'create view' as well can change data at will. You may be able to use the database in production, but only if your users can't access it directly. I've not tested it myself, but it should still be on metalink. _ This just in from comp.databases.oracle.server. See metalink bug 2121935. Using ANSI syntax joins (CROSS JOIN, LEFT OUTER etc) allows you to view data from tables on which you have no privilege. For example, try this COMPLETE script: connect / as sysdba create user us1 identified by us1; grant create session to us1; connect us1/us1 select userid, password from sys.link$ cross join dual ; Worse still, if you have the privilege to create views then this loophole allows you to seek and destroy ANY DATA in the database that you might want to. The bug is fixed in 9iR2. I didn't see any note about a backport, or a security alert on OTN. Conclusion: 9.0.1 should not be in use on production system until Oracle supplies a fix. _ With apologies for the mangled quick quote. Simon Anderson -- 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: Toepke, Kevin M 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: Count rows in textfile
try surfing to shopping.yahoo.com and search for infra red glasses (without the quotes) and you'll see a variety of products. May I recommend that you instead get night vision goggles (search for night vision goggles) Most of the better ons have built-in IR illumination that allows you to see in _total_ darkness! -Original Message- Sent: Wednesday, July 03, 2002 1:58 PM To: Multiple recipients of list ORACLE-L No problem. Just use infra red glasses. Question: What are infra red glasses? Where can I get one. Can someone give me an example? :-))) Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 03, 2002 5:18 PM Roland, How about counting black cats in the dark room without turning on the light? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 03, 2002 4:43 AM Anyone whom has a good example on how to check how many rows (without opening the file) does a text file consists of? What is the command? 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: Igor Neyman 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: Yechiel Adar 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: Toepke, Kevin M 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: Alternative to Crystal Reports
My exprience with Crystal is responding to problems like I can't get Crystal to do this or that. My usual answer is create a packaged function that returns a REF CURSOR that returns the results in a format that Crystal deal with. The more recent the Oracle version, the easier this is to accomplish. -Original Message- Sent: Thursday, June 20, 2002 2:17 PM To: Multiple recipients of list ORACLE-L Several times in the past I have seen folks on this list refer people to a reporting product to use as an alternative to Crystal reports. Apparently I didn't think I would need it, cause I usually save posts with information I am likely to forget.(So Yeah, I do save a lot of posts) Well now Crystal is being touted here as a possible solution for a new client's reporting requirements. We have been unable to convince Oracle Reports to perform in the way we would like, so we need an alternative. The good thing is that the new client is a large one, so my company will actually be able to invest in a nicer product. -- 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: Toepke, Kevin M 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 loader question
Did you, perchance, mean to specify I:\dvh\tuppy.txt? -Original Message- Sent: Tuesday, June 11, 2002 7:58 AM To: Multiple recipients of list ORACLE-L Hallo I am running this script but gets this errormessage: Why is it so? I get the errormessage The system cannot find the file specified.(I:dvh\tuppy.txt) but this file really exists: What is wrong. I include the textfile and also the ctl file. (See attached file: nielsen.ctl)(See attached file: Tuppy.txt) please check the files and see what is wrong. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: set command
Title: RE: set command Here is what I use. It wraps at the 32768th character. SET PAUSE OFFSET TRIMSPOOL ONSET TRIMOUT ONSET TERMOUT OFFSET PAGESIZE 5SET LINESIZE 32767 -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: Monday, June 10, 2002 2:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: set command Paula, TRIMSPOOL will only remove the trailing characters in an output file. Normally, the output is padded withblanksup to the length defined by LINESIZE. I don't think TRIMSPOOL will help, but I could be wrong. How long is the command you are trying to output? What is the setting for WRAP? Daniel W. Fink Sr. Oracle DBA MICROMEDEX 303.486.6456 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Monday, June 10, 2002 11:28 AMTo: Multiple recipients of list ORACLE-LSubject: RE: set command set linesize alone doesn't help - but will try it with set trimspool -Original Message-From: Alexandre Gorbatchev [mailto:[EMAIL PROTECTED]]Sent: Monday, June 10, 2002 12:48 PMTo: Multiple recipients of list ORACLE-LSubject: Re: set command Oracle doesn't place anything. SQL*Plus does. If you generate scripts from SQL*Plus: set linesize ... -- Alexandre - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Monday, June 10, 2002 3:58 PM Subject: RE: set command Guys, Building scripts from SQL generating SQL in Oracle on Unix. I know that there are set commands so that Oracle does not put CR or LF in the middle of a long command. Does anyone know these off the top of their head? Thanks, Paula set array...? set maxdata...?
RE: Asinine security in Oracle, Part Deux
You might want to check out 9iR2. -Original Message- Sent: Monday, June 10, 2002 2:58 PM To: Multiple recipients of list ORACLE-L So, there I am, following up on past MetaLink forum articles, when I noticed one about auditing DBA actions. This, of course, is not supported by Oracle. Why would anyone want to audit a DBA? After all, a DBA never makes mistakes, right? ;) The answer from Oracle was that auditing SYS would be available in 9.2. Can anyone confirm? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: Toepke, Kevin M 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: why so much slower
I'll betyou are running...Oracle...8.1.6. Right? If so, the solution is SELECT /*+ NO_MERGE(x) HASH(st) */ * FROM (your 2 table join) x ,small_table st WHERE x.fk = st.pk; -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 05, 2002 4:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: why so much slower Set sort_area_size to very large as 20Gb (obscene) amount of space available. Doing 2 large table outer joins returns results in .341 seconds - both partitioned on same criteria added one small codetable equijoin with one of the larger tables. There is a foreign key to codetable and index that is unique. Used hash join hint Used nested loop hint Basically saw two large joins sort merged hash join then nested join to smaller table - much much smaller codetable. NO matter what it seems query is much much slower - Any ideas?
DataWarehouse Design Training
Hello! Can anyone recommend a good training class on DataWarehouse design/implementation? I have a basic understanding of the concepts,etc from reading books, but would like a hands-on course to get more of a feel for the subject. TIA Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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*Loader question
$ sqlldr help=y userid ORACLE username/password control Control file name log Log file name bad Bad file name dataData file name discard Discard file name discardmax Number of discards to allow skipNumber of logical records to skip loadNumber of logical records to load -Original Message- Sent: Tuesday, June 04, 2002 12:09 PM To: Multiple recipients of list ORACLE-L I don't know if you can load the first 100 records, but you could load the last 100 by setting the skip value to the total - 100. HTH, Beth -Original Message- Sent: Tuesday, June 04, 2002 11:39 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun 2.6. I have tried reviewing the docs, but I didn't see anything that answered the question. Is it possible to limit the number of records being loaded? We have a file that has records in the 6 digit range. I'd like to test the controlfile, but I don't want to load the whole file. Is there a way to tell loader to only load, say the 1st 100 records? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Toepke, Kevin M 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: 9iR2 Install Problem
All: Remember this? Well, after losing an support analyst, we finally got the java stuff to work with 9iR2...the problem was the inventory_loc=/opt/oracle entry in /var/opt/oracle/oraInst.loc. it should have been inventory_loc=/opt/oracle/oraInventory The installer did not complain that it could not create an oraInventory directory under /opt/oracle! The solution was to delete the oraInst.loc file and re-install oracle. Kevin -Original Message- Sent: Wednesday, May 22, 2002 9:23 AM To: Multiple recipients of list ORACLE-L Hello! I am having a little difficulty with the 9iR2 install on a SUN Sparc (Solaris 8) box. The installer completes normally, but when I try to run anything java (e.g. dbca) I get the following message Could not locate Java runtime It looks to me that Oracle's provided jre did not get installed properly Any suggestions on what I can do to correct this error? Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Toepke, Kevin M 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: So, What is a 'Production DBA'?
A Harvey Wall Banger? I've never heard of that type of hammer before :) -Original Message- Sent: Thursday, May 30, 2002 12:36 PM To: Multiple recipients of list ORACLE-L I guess it's that old Russian proverb To a hammer, all the world looks like a nail. Developers have experience as hammers and everything revolves around the code. As an ex-developer, now DBA, I know that sometimes you need a screwdriver (or a Harvey Wall Banger). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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).
9iR2 Install Problem
Hello! I am having a little difficulty with the 9iR2 install on a SUN Sparc (Solaris 8) box. The installer completes normally, but when I try to run anything java (e.g. dbca) I get the following message Could not locate Java runtime It looks to me that Oracle's provided jre did not get installed properly Any suggestions on what I can do to correct this error? Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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).
Rename Column in 9iR2
Summary: it works! SQL SELECT * FROM v$version; BANNER Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production CORE9.2.0.1.0 Production TNS for Solaris: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production SQL create table test_table (wrong_name VARCHAR2(40)); Table created. SQL alter table test_table rename column wrong_name to right_name; Table altered. SQL desc test_table; Name Null?Type - RIGHT_NAME VARCHAR2(40) SQL Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: 9iR2 Install Problem
Stephen: I can run Java programs I wrote using the installed jvm. Those from $ORACLE_HOME/bin appear to use the JVM that was installed with 9i. The dbca and downstream scripts appear to re-create the Java environment for you. Caver -Original Message- Sent: Wednesday, May 22, 2002 11:08 AM To: Multiple recipients of list ORACLE-L Check and see if the jre runtime libraries are in your path for the session. Otherwise, it will not dynamically find them. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, May 22, 2002 9:23 AM To: Multiple recipients of list ORACLE-L Subject:9iR2 Install Problem Hello! I am having a little difficulty with the 9iR2 install on a SUN Sparc (Solaris 8) box. The installer completes normally, but when I try to run anything java (e.g. dbca) I get the following message Could not locate Java runtime It looks to me that Oracle's provided jre did not get installed properly Any suggestions on what I can do to correct this error? Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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 contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen 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: Toepke, Kevin M 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: Rename Column in 9iR2
I reran my test. with 250,000 rows in the table. It took a whopping 0.25 seconds to complete. -Original Message- Sent: Wednesday, May 22, 2002 12:59 PM To: Multiple recipients of list ORACLE-L Kevin, Yes but does it work with data in the column?, I do not have 9i set up yet. If it works as you describe with data in the column it will really help with the multi million row tables that they want to rename a column after a release of a new lotto game. thanks, for the test and update. Ron ROR mª¿ªm [EMAIL PROTECTED] 05/22/02 10:38AM Summary: it works! SQL SELECT * FROM v$version; BANNER Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production CORE9.2.0.1.0 Production TNS for Solaris: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production SQL create table test_table (wrong_name VARCHAR2(40)); Table created. SQL alter table test_table rename column wrong_name to right_name; Table altered. SQL desc test_table; Name Null?Type - RIGHT_NAME VARCHAR2(40) SQL Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Ron Rogers 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB Freeze
Do you have archive logging turned on? Yes: Is your log_archive_dest full? Is the directory for your online redo logs full? -Original Message- Sent: Tuesday, May 21, 2002 4:00 PM To: Multiple recipients of list ORACLE-L Last Friday, our 9012 database froze, I mean the only way we could get in was through sqlplus, no other tool would connect. We needed to bring the other side up as soon as possible so didn't spend any time running any queries to see what was the problem. The other side (of the cluster) wouldn't come up because it reported that some resource it needed was locked. So we had to shot the pmon process. When analyzing the situation later, we found that Oracle didn't dump a single useful trace file, so contacting OWS was pretty much useless. OS logs were clean, no alarms raised there. Has anyone encountered this situation that Oracle freezes and crashes without any trace files? How does one collect useful information in such cases? Are there any tools that we could use to gather at-lease some information before the DB crashes? Thanks in advance for any ideas and tips. 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! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Order rows
using a date will work unless you get multiple records created in a given second. Use a sequence generated number. The larger the number, the newer the record. Just order by the sequence to see the order the records were inserted. Caver -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 10:03 AM To: Multiple recipients of list ORACLE-L Do you want to physically order them or do you just want to know by time the order in which they were entered. if the first, no, not that I know of. If the later, yes, add another column (ins_date date) and a trigger to populate that column with sysdate when you insert a row. You can then order by ins_date |+-- || | || | || systems_ho/VGIL@vguard.sat| || yam.net.in | || | || 05/15/2002 02:08 AM | || Please respond to ORACLE-L | || | |+-- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Order rows | | Hi All Is there any method in Oracle to capture or order the rows in a table in the order they were entered. I tried it with rowid but when a row is deleted, the rowid corresponding to this row is reassigned for a new row which is inserted into the table at a later stage. Eg. SQL select rowid,abc.* from abc order by rowid; ROWID A -- -- AAAFmYAASAAAYsqAAA100 AAAFmYAASAAAYsqAAB200 AAAFmYAASAAAYsqAAC300 I deleted one transaction. delete from abc where a=200; commit; Then I inserted two rows. insert into abc values(500); insert into abc values(600); commit; Now when I order by rowid SQL select rowid,abc.* from abc order by rowid; ROWID A -- -- AAAFmYAASAAAYsqAAA100 AAAFmYAASAAAYsqAAB600 AAAFmYAASAAAYsqAAC300 AAAFmYAASAAAYsqAAD500 I.e The values I entered last appeared second.The rowid (AAAFmYAASAAAYsqAAB ) corresponding to the row I deleted was reassigned for the last entered row (a=600). What I want is that this must be sorted in the order of its entry. Can anyone help me out. Thanks in advance Systems. -- 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: 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: {9i New Features: Online Reorg or DBMS_REDEFINITION Package}
A quick followup to this...
I've done some testing of this package and
concur with Joe. Its kewl. Outside of renaming a column, it can be used to
quickly partition a non-partitioned table. Its much faster and easier than using
exchange partition.
The 9iR2 new features whitepaper hints at a
native rename column command.
Caver
-Original Message-From: JOE TESTA
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 2:58
PMTo: Multiple recipients of list ORACLE-LSubject: {9i
New Features: Online Reorg or DBMS_REDEFINITION Package}
Welcome to the next installment of 9i New Features, today's topic is
Online changes of objects, specifically we'll cover the new package called
DBMS_REDEFINITION.
The spec for this package is located where all of the other package
sources are:
ORACLE_HOME/rdbms/admin. The file is dbmshord.sql
So what does this package give us? Well it gives the capability to
do online reorganization of a table. Ok so now if you're not confused,
you should be :)
In easy to understand terms, in the past when you wanted to move a
table to a new tablespace, drop a column, add a column, change a column
datatype, it require a exclusive lock on the table during the operation(which
if it was a large table could lock it up for a long time). Well that is
no longer the case, those kinds of changes can be done while DML is still
being applied to the object.
Let's take for an example something that all of us have been asking for
YEARS, the rename of a column.
Look at this code, I've included comments within it so its pretty much
self-explanatory and you can run it against your 9i database to see what if it
really works.
-- BEGINNING OF SCRIPT
---
set serveroutput on size 10;
-- let's drop a couple of tables so if we re-run we won't get
errorsdrop table sales;drop table sales_temp;
-- create a new table, handful of columns with the last one named
incorrectly.
create table sales(sales_id number not null,sales_amount
number(10,2) not null,salesman_id number(5) not
null,tax_amount number(5,2) not null,bad_column_name
varchar2(20) not null);
-- add a PK since for online reorg it's required
alter table sales add primary key(sales_id)-
-- insert some data
insert into sales values(1,20,4,5.70,'bogus');insert into sales
values(2,30,6,6.70,'no way');insert into sales values(3,40,7,7.70,'XX
way');insert into sales values(4,50,8,8.70,'YY way');insert into sales
values(5,60,9,9.70,'ZZ way');insert into sales values(6,70,1,0.70,'AA
way');insert into sales values(7,80,2,1.70,'BB way');insert into sales
values(8,90,3,2.70,'CC way');insert into sales values(9,10,4,3.70,'DD
way');insert into sales values(10,25,5,4.70,'EE way');
-- commit the data
commit;
-- run the proc to see if this table can be reorganized online, if we
get an error,-- then its not possible, otherwise we're
good to go.
execute dbms_redefinition.can_redef_table(USER,'SALES');
-- we must create the temp table for this reorg to happen manually,
either with a -- create table statement or via a create
table as select(no rows please to be copied)-- this exercise is
going to be to do a rename on the column, so we need to
create-- the table making sure we have the new column
name
create table sales_temp(sales_id number not
null,sales_amount number(10,2) not null,salesman_id
number(5) not null,tax_amount number(5,2) not
null,good_column_name varchar2(20) not null);
-- have to have a PK on the temp table also.
alter table sales_temp add primary key(sales_id);
-- lets desc the original to see what it looks like before
prompt this is the sales desc before the changedesc sales;
-- ok lets fire up the redefinition, the parms are(SCHEMA, OLD_TBL,
TEMP_TBL,-- then column mapping, notice we're mapping
bad_column_name to good_column_name).
execute
dbms_redefinition.start_redef_table(USER,'SALES','SALES_TEMP',
-'sales_id sales_id, sales_amount sales_amount, salesman_id salesman_id,
-tax_amount tax_amount, bad_column_name good_column_name');
-- at this point its YOUR responsiblity to put the triggers, other FK
constraints(disabled)-- and indexes on the "temp" table before
calling the next part to do the "switch".
-- DO THE OTHER INDEXES, ETC HERE
-- ok time to finish up
execute
dbms_redefinition.finish_redef_table(USER,'SALES','SALES_TEMP');
prompt this is the definition of sales AFTER the changedesc
sales;select * from sales;
--- END OF SCRIPT
---
So what you think, pretty kewl(geek kids spelling)?
Check out the docs on dbms_redefinition for limitations, etc, don't want
to give you allthe answers.
Until
Oracle 9iR2 Relase date
http://www.infoworld.com/articles/hn/xml/02/01/31/020131hnoradb.xml Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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).
Password Checking
All: I am looking for an algorithm that will verify that a pasword meets minimum requirements (like 8 chars, mix of chars nbrs, != username, etc), but am just feeling too darn lazy to write one myself. Can anyone on the list help me out by pointing me to a good one? Thanks Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i Release 2 features/fixes
RF: You must have missed my post from last week. Oracle has published the 9iR2 new features. And, yes A revolutionary improvement in Oracle9i Database Relase 2 is the introduction of the Oracle Data Guard - Logical Standby datbase (from the Oracle9i Database Release 2 New Features; An Oracle White Paper; March 2002) Follow this link. is somewhere on the page http://www.oracle.com/features/9i/index.html?t1db_unbreakable.html Caver -Original Message- Sent: Monday, May 06, 2002 3:14 PM To: Multiple recipients of list ORACLE-L I have been trying, like you wouldn't believe, to get a list of new features in 9iR2. I have run into stone wall after stone wall. I think there has been some internal confusion and debate over what to include in 9iR2, based on some of what I've heard. I've even heard that Logical Stand-by will not be in 9iR2, but I can not verify this. Looking hard, will report if I find anything. RF -Original Message- Sent: Monday, May 06, 2002 11:14 AM To: Multiple recipients of list ORACLE-L The big thing that I'm looking forward to is the logical standby database. This will allow you to apply logs between different versions of Oracle. Should allow us to drastically reduce downtime during upgrades. -Original Message- Sent: Friday, May 03, 2002 6:18 PM To: Multiple recipients of list ORACLE-L You can sure that Oid will be better - because lets face it - it couldn't have got any worse :-) --- Jesse, Rich [EMAIL PROTECTED] wrote: Does anyone know of or have a list of new features/fixes for Release 2 of 9i? I can't find anything on oracle.com except XML XML XML. Just wondering if OiD gets any better, I guess. :) TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Freeman, Robert 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
RE: Partitoned Table Insert Performance
Way back in the days of Oracle 8.0.5 I did some performance testing of bulk inserts/sqlldr of range partitioned tables v.s. non-partitioned tables. I don't have the benchmarks on hand, but here's what I found. All tests were done using the direct path inserts (sqlldr direct=true or /*+ APPEND */) If the table had no indexes, then there was no noticable difference when inserting approx 8 million rows. If the partitioned table had only local indexes, then the inserts into the tables were slightly faster. The difference was in seconds for my 8million row test. If the partitioned table had global partitioned indexes, then the inserts were generally slower. Again, the diference was in seconds. If the partitioned table had a global non-partitioned index there was no noticable diffence in time. In sort, range partitioning a table does not effect performance. The indexing of the range-partitioned table does. HTH Caver -Original Message- Sent: Thursday, May 02, 2002 12:45 PM To: Multiple recipients of list ORACLE-L I am in the process of implementing partitioning on some existing tables. I have been asked by management to evaluate the performance impacts of the changes. I am aware of many of the performance advantages of partitioning: partition pruning, partition-wise joins and parallel data loads. What I am concerned about is the additional overhead of inserting data into a partitioned table. What sort of overhead is associated with partitioned table inserts? Does determining the correct partition slow insertions? We are utilizing only range partitions, so hash value computations should not be a factor. Our application is very insertion intensive. One of the operations that performs insertions does so synchronously, so any decrease in insertion performance would be quite visible. Do you have any thoughts or experiences with this issue. Any performance tests on two similar tables, one partitioned and the other not? Any input would be appreciated. Thanks. Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Slow select distinct
This may be my favorite Oracle 8i bugs (and no, I don't have the bug#s.)
Performing a sort sometimes causes wildly inefficient execution plans.
I'm guessing that if you run the explain plans for the query without the
DISTINCT and with the DISTINCT you will get completely different results. If
this is my favorite bugs, then you will get SORT/MERGE joins when you add
the DISTINCT where the joins were either NESTED LOOPS or HASH joins before.
My typical solution is to use an inline view and the NO_MERGE hint as in:
SELECT /*+ NO_MERGE(data) */
DISTINCT wrecks
FROM (SELECT LTRIM(L.STREET_ADDRESS_BEGIN||' '||L.STREET_NAME_PREFIX||
''||L.STREET_NAME||' '||L.STREET_NAME_SUFFIX) WRECKS
FROM LOCATION L,
STREET_REQS SR
WHERE L.ID = SR.ID
AND L.STREET_NAME IS NOT NULL
AND SR.TYPE IN ('KED','KAD')
AND SR.CODE LIKE 'O%'
AND SR.ORIG_STREET_REQ_ID IS NULL) data
Caver
-Original Message-
Sent: Monday, April 29, 2002 5:44 PM
To: Multiple recipients of list ORACLE-L
I got a query that selects a list of addresses based an occurence at that
location.
this query comes back in less than 2 seconds without a distinct clause on
the concatenated name. When I add the distict clause it takes over 40
seconds. I've tried adjusting various sort area sizes and buffer sizes to
see if I can speed this up. Anybody have a clue if there is something in
particular that I can check.
8.1.6 on NT rules based. wtihout the distinct clause it brings back about
10,000 records.
select DISTINCT LTRIM(L.STREET_ADDRESS_BEGIN||' '||L.STREET_NAME_PREFIX||'
'||L.STREET_NAME||' '||L.STREET_NAME_SUFFIX) WRECKS
FROM LOCATION L,
STREET_REQS SR
WHERE L.ID = SR.ID
AND L.STREET_NAME IS NOT NULL
AND SR.TYPE IN ('KED','KAD')
AND SR.CODE LIKE 'O%'
AND SR.ORIG_STREET_REQ_ID IS NULL
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Shaw John-P55297
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: Toepke, Kevin M
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).
Partitioning Quandry
here's one for the partitioning gurus out there I have an INVOICE table that I want to partition for performance and purging. The way I want to partition it is to do range partitioning on the INVOICE_STATE column, then sub-partition some of the partitions by UPDATE_DATE. The logic behind this is: 1) An invoice may be in sent, but unpaid (A) state for several months. 2) We never want to purge off unpaid invoices 3) After an invoice has been in paid (P) state for 6 months, we want to purge the invoice My basic idea was to have partition-movement enabled and to use a partitioning scheme like the following: TABLE invoice ( invoice_id, invoice_state, update_date, ... ) partition by range (invoice_state) ( partition inv_act values less than 'B' ,partition inv_hist values less than 'R' subpartition by range (update_date) ); Alas, you can only subpartition by HASH (or LIST in 9iR2) The only solution I can come up with is a 2 table solution -- keeping the unpaid invoices in one table and the paid invoices in another table that is range partitioned on UPDATE_DATE. The difficulties with this solution are coding the row movements (bi-directional) and having to code a partition-view. Any suggestions would be helpful. Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Partitioning Quandry
. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What block size are you using for your new 9i data warehouse?
Cherie: If you are using Oracle 9i, you can use mutliple block sizes! You still create your database with a default block size, but each tablespace (except system) can have a block size different from the default. Valid blocksizes are limited by OS, but they range from 2K to 32k. Check out the 9i docs for CREATE TABLESPACE for more info. Caver -Original Message- Sent: Thursday, April 25, 2002 11:24 AM To: Multiple recipients of list ORACLE-L We are building a new version 9.0.1 data warehouse on Sun Solaris 2.6 migrating to Solaris 2.8. We will be using striped disk that is striped using Veritas Volume Manager on EMC disk. The datawarehouse will be about 200 Gig. It will be written to throughout the day. To this point, almost all of our databases have been created with an 8k block size. i'm not sure if that blocksize is optimal anymore. With 9i, what block sizes are people using in the field nowadays for data warehouses? Also, if you use striped disk, what stripe size are you using and why? Thanks for weighing in on this topic. Cherie Machler Oracle DBA Gelco Information Network -- 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: Toepke, Kevin M 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: Is sqlplus too slow to unload data?
If you have PRO*C and a c-compiler, you can follow this link (you need both lines) to download a basic sqlunldr tool. It is almost as fast as exp. http://asktom.oracle.com/pls/ask/f?p=4950:8:646297::NO::F4950_P8_DISPLAYID,F 4950_P8_CRITERIA:459020243348,%7BSQLDA%7D Caver -Original Message- Sent: Wednesday, April 24, 2002 3:33 AM To: Multiple recipients of list ORACLE-L Bruce, SQLPlus: set pages 0; set lines 1; set termout off; set trimspool on; set trimout on; set echo off; set feedback off; set verify off; set recsep off; set arraysize 2000; PDQOut is 3rd party product I test. I also test the PL/SQL from Thomas Kyte's book. I call it from sqlplus, and the speed is only 1.5 time faster than sqlplus one. exp can achieve 500M/minute. However, I intend to change it as small as possible. I wonder if about 5M/minute is max speed for sqlplus. Regards, Bin Reardon, Bruce (CALBBAY) wrote: Bin, Have you tried setting term off in your sqlplus session - what effect does this have? I would guess that the Pro*C program also uses Net8 so the problem would be in SQLPlus. Which 3rd party product did you try? Have you tested PDQOut from http://www.oriole.com - this is written in OCI. Also, I'm sure someone will suggest using Perl. HTH, Bruce Reardon -Original Message- Sent: Wednesday, 24 April 2002 14:53 Hi, Our application uses sqlplus + sqlloader to transfer data between databases. It takes nearly four hours to unload to data to flat files(1G), which is far too slow. In the application, the query looks like the following. All those 3,4,5 are for sqlldr format. select ' ' || '4' || replace( replace ( ltrim(dealerid), '4', '4' || '4' ), CHR(10), CHR(10) || '5' ) ||'4'||'3' || ... from table_name f where eventdate = to_date(1) and eventdate = to_date(2); Firstly, there is nothing wrong with the query, since if I insert into a table it only takes less than 15 minutes. Therefore, there must be problem with either sqlplus or Networking. With sqlplus, I increase arraysize from 1 to 2000. With Networking, I put tcp.nodelay=yes on protocol.ora. Both doesn't work. I try thrid party software which is writen by Pro*C to download tables to flat file. Its speed is more than 60M/minute. I monitor v$session_event while it's running.The only different is event SQL*Net message from client. In AVERAGE_WAIT and MAX_WAIT, the different is huge. sqlplus: TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT 49 0 5998 122.4 1004 Pro*C: TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT 351 0 677 1.92 42 What's the problem sqlplus or net8? BTW, dblink doesn't work since the two databases on isolated network. emp/imp is an option. However, I just try to find out what is wrong with sqlplus one. I test 8.0.5 and 8.1.7 on solaris 2.6-2.8. Thanks in advance, Bin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Bin Wang 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: Toepke, Kevin M 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: Currval and buffer gets
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: Yechiel Adar 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). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha 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: Yechiel Adar 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: Khedr, Waleed 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: Yechiel Adar 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: Toepke, Kevin M 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: Currval and buffer gets
Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha 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: Yechiel Adar 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). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha 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: Yechiel Adar 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: Khedr, Waleed 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: Yechiel Adar 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services
RE: Currval and buffer gets
command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Khedr, Waleed 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE:
upgrade to Oracle 9i and use external tables. -Original Message- Sent: Monday, April 22, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Hi all! I need a solution about calling sql*loader from pl/sql. I have a version now with external dlls, but actually I don't know the platform so it not seems a good choice. I would like something native oracle solution with oracle's packages or something like that. -- 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: Toepke, Kevin M 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: function based index
the query_rewrite_enabled init.ora parameter has to be set properly. -Original Message-From: Big Planet [mailto:[EMAIL PROTECTED]]Sent: Monday, April 22, 2002 4:15 PMTo: Multiple recipients of list ORACLE-LSubject: function based index I have created a function based index on one column , but query is still noy using it . What should be the reason ? oracle 8.1.7 cost based optimizer table and index analyzed recently
RE: Upgrade 8.0.5 to 8.1.7.3
Title: RE: Upgrade 8.0.5 to 8.1.7.3 Matt: I don't have the bug#s. When I opened a TAR on this (now unpublished) the support person called me and told me that I had to downgrade the database to workaround the bug. (All the TAR saysis thatthey called me.)The analyst said thatbug is one that is fixed in 9.0.2. In our case, whenever a specific stored procedure was called with a certain range or parameters, Oracle would use all of the available memory on the server -- causing the server to crash. Kevin -Original Message-From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 17, 2002 11:58 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Upgrade 8.0.5 to 8.1.7.3 Keven, Can you supply bug numbers for these bugs? Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] Reason is 6/7ths of treason. - The Xtals -Original Message- From: Toepke, Kevin M [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 17, 2002 11:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: Upgrade 8.0.5 to 8.1.7.3 Jack: First of all, there are some serious problems with 8.1.7.3 that can cause database crashes and corruption. One bug (may be solaris specific) can crash the box. I would highly recommend that you "only" upgrade to 8.1.7.2 unless absolutely necessary. We have downgraded all of our 8.1.7.3 databases to 8.1.7.2 (a *very* painful experience) That said, I never had trouble upgrading directly to 8.1.7.2 directly from 8.1.5.x or 8.1.6.x. We have done in development, staging and production without any adverse effects. I don't think I've ever done a 8.0.5 to 8.1.7 directly. Kevin -Original Message- Sent: Wednesday, April 17, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Hi All, we are in the process of upgrading to 8.1.7.3 some of our databases (now 8.0.5) According to the Doc's thsi has to be done in two steps Upgrade to 8.1.7.0.0 followed by and upgrade to 8.1.7.3.0. This means that we have to upgrade all our databases in one go, or install another base 8.1.7 install to do some databases later. On our test system however we have upgraded directly form 8.0.5 and all seems to be fine. Anybody care to comment/share their opinions/experiences TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mai
RE: Design question...
H
from a pure relational standpoint (something like 4th or 5th normal form),
keys shouldn't have any inherent meaning so you should go with STATE_ID as
the PK.
From a real-world perspective, I would use STATE_CD as the PK. Its not going
to change very often (how often do state abbreviations change?) unless you
are going international. And, if you have the appropriate indexes on the fk
columns you can eliminate a join from many queries.
From a space perspective, there is no difference:
SELECT VSIZE(1) FROM dual; -- result is 2
SELECT VSIZE(50) FROM dual; -- result is 2
SELECT VSIZE('AK') FROM dual; -- result is 2
I've never seen a noticable difference in performance or FK management
either.
Caver
-Original Message-
Sent: Wednesday, April 17, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
To simplify my question, if I am creating a STATE table to hold all the
states of the US, should I create it like this...
Name Null?Type
-
STATE_CODENOT NULL CHAR(2) -- PK
STATE_DESCNOT NULL VARCHAR2(50)
or like this...
Name Null?Type
-
STATE_ID NOT NULL NUMBER -- PK
STATE_CODENOT NULL CHAR(2)
STATE_DESCNOT NULL VARCHAR2(50)
I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
when doing a PK lookup, dealing with FKs, etc.
Many TIA!!!
Chris
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Grabowy, Chris
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: Toepke, Kevin M
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 with hints or without hints (LONG)
Using RBO is unnecessary if you are using Oracle8 v8.0 or above. The CBO outperforms RBO in any situation except queries against the data dictionary (because you cannot analyze the data dictionary). Never say never and Never say always. I have found the above statement to be true except in one case, and that involves a bug that was introduced somewhere in the 8.1.5 tree and_almost_fixed in 8.1.7.1. It is particularly nasty in the 8.1.6 tree and it appears to befixed in 9.0.1.2. I don't have the bug #, but the situation is follows: 1) You are joining multiple large tables together -- The more larger the tablesyou are joining, the worse the effects 2) One or more of the join columns is in the SELECT list 3) You are ordering by 1 of the join columns. -- this can be an ORDER BY, GROUP BY or DISTINCT clause or even a UNION, INTERSECT or MINUS! Anything that causes a sort to occur on the join column The CBO will choose to do SORT/MERGE joins (with full table scans) when any other join method is more efficient. Through normal hintingyou CAN NOT get the CBO to use nested loops with index range scans (that's part of the bug). If you specify the INDEX_ASC() and USE_NL() hints, the CBO will do an FAST FULL SCAN on the index on the specified index. If you move the unsorted query into an inline view and sort outside the inline view, you can get a near-optimal execution path -- and hinting works properly. However, if you just use the /*+ RULE */ hint, you will get better performancethan with the inline view method. How do you determine if you are running into this bug? There are several ways, but thebest way is to run your query without your "sort" operation. If the query runs significantly faster without the sort than with, you may be hitting this bug. An example: SELECT e.empno, e.deptid, dept.name, d.dependent_name FROM emp e, dept, dependent d WHERE e.deptid = dept.id AND emp.empno = d.empno ORDER BY e.empno, e.deptid; Inline view method SELECT /*+ NO_MERGE(x) */ * FROM ( SELECT e.empno, e.deptid, dept.name, d.dependent_name FROM emp e, dept, dependent d WHERE e.deptid = dept.id AND emp.empno = d.empno) x ORDER BY empno, deptid; Rule Hint: SELECT /*+ RULE */ e.empno, e.deptid, dept.name, d.dependent_name FROM emp e, dept, dependent d WHERE e.deptid = dept.id AND emp.empno = d.empno ORDER BY e.empno, e.deptid; Even with this bug around, I would still highly recommend the CBO over the RBO. You just have to know the exceptions. Caver
RE: recordset as argument
In Oracle 8.x you can pass in a INDEX BY Table, Varray, etc. In Oracle 9i a procedure can accept a REF CURSOR as an input parameter. Caver -Original Message-From: Shishir [mailto:[EMAIL PROTECTED]]Sent: Thursday, April 04, 2002 8:08 AMTo: Multiple recipients of list ORACLE-LSubject: recordset as argument Hi Gurus ! Is it possible to pass recordset or cursor as argument in stored procedure?? thanx in advance.. Shishir Kumar MishraAgni Software (P) Ltd.www.agnisoft.com
RE: recordset as argument
Rakesh: I haven't implemented it. Just read about it in the docs. Caver -Original Message- Sent: Thursday, April 04, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Hi Caver, Can u pls give in details with a sample code, if posible the implementation of the recorset(or resultset) in REF CURSOR. With Regards Rakesh Banerjee From: Toepke, Kevin M [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: recordset as argument Date: Thu, 04 Apr 2002 05:33:26 -0800 In Oracle 8.x you can pass in a INDEX BY Table, Varray, etc. In Oracle 9i a procedure can accept a REF CURSOR as an input parameter. Caver -Original Message- Sent: Thursday, April 04, 2002 8:08 AM To: Multiple recipients of list ORACLE-L Hi Gurus ! Is it possible to pass recordset or cursor as argument in stored procedure ?? thanx in advance.. Shishir Kumar Mishra Agni Software (P) Ltd. www.agnisoft.com http://www.agnisoft.com _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: rakesh banerjee 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle From a Sybase DBA perspective| What is a Database to S
Here is the URL for the PRO*C code to dump data from a query to a flat file. http://asktom.oracle.com/pls/ask/f?p=4950:8:646297::NO::F4950_P8_DISPLAYID,F 4950_P8_CRITERIA:459020243348,%7BSQLDA%7D -Original Message- Sent: Monday, April 01, 2002 11:10 AM To: Toepke, Kevin M; '[EMAIL PROTECTED]' S whoopse. pasted the wrong URL. I'll send the correct one as soon as I can find it. -Original Message- Sent: Monday, April 01, 2002 11:00 AM To: '[EMAIL PROTECTED]' S There is code for a basic SQL Unloader tool available at asktom.oracle.com. Completely dynamic. Limited support for LONGs and no support for LOBs or objects, but generally pretty good. Does bulk fetches. http://osi.oracle.com/~tkyte/flat/index.html -Original Message- Sent: Monday, April 01, 2002 9:53 AM To: Multiple recipients of list ORACLE-L S - bcp out - It's time Oracle came up with some utility to extract the data in ascii format other than recommending sqlplus and spool What about utl_file functions? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle From a Sybase DBA perspective| What is a Database to S
There is code for a basic SQL Unloader tool available at asktom.oracle.com. Completely dynamic. Limited support for LONGs and no support for LOBs or objects, but generally pretty good. Does bulk fetches. http://osi.oracle.com/~tkyte/flat/index.html -Original Message- Sent: Monday, April 01, 2002 9:53 AM To: Multiple recipients of list ORACLE-L S - bcp out - It's time Oracle came up with some utility to extract the data in ascii format other than recommending sqlplus and spool What about utl_file functions? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle From a Sybase DBA perspective| What is a Database to S
whoopse. pasted the wrong URL. I'll send the correct one as soon as I can find it. -Original Message- Sent: Monday, April 01, 2002 11:00 AM To: '[EMAIL PROTECTED]' S There is code for a basic SQL Unloader tool available at asktom.oracle.com. Completely dynamic. Limited support for LONGs and no support for LOBs or objects, but generally pretty good. Does bulk fetches. http://osi.oracle.com/~tkyte/flat/index.html -Original Message- Sent: Monday, April 01, 2002 9:53 AM To: Multiple recipients of list ORACLE-L S - bcp out - It's time Oracle came up with some utility to extract the data in ascii format other than recommending sqlplus and spool What about utl_file functions? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert 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: Toepke, Kevin M 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: Pro-C differences VMS to Unix
One new thing you can do with PRO*C 8i is to use the The PREFETCH Precompiler Option if your application is processing a lot of data in a sequential manner (and you don't already do bulk fetches) Caver -Original Message-From: Stephen Andert [mailto:[EMAIL PROTECTED]]Sent: Friday, March 29, 2002 11:08 AMTo: Multiple recipients of list ORACLE-LSubject: Pro-C differences VMS to Unix Hey all, I know there are developers lurking out there as well as dba's that have a great deal of development experience that could help me to help our developers on this project. We have an old application that is running on a VMS machine. The Oracle version there is 8.0.5. Our development group is porting this to Unix (Tru64 5.1). We are planning on deploying this with Oracle version 8.1.7.3 as the client. The database in test is currently 8.1.7.3 and production will be upgraded to 8.1.7.3 soon. The question I have is this: The development VMS and Unix machines are comparably equipped, but application performancefrom the VMS machine is much better thanfrom Unix. There have been no changes to the application other than being recompiled in the new OS. What can anyone tell me about changes that should/could be done to improve performance when moving from VMS (Oracle 8.0) to Unix (Oracle 8.1)? Thanks in advance for any advice or suggestions. Stephen Andert
RE: bulk collect in 9i
Yes, that limitation still exists in Oracle 9i, R1. -Original Message- Sent: Wednesday, March 27, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Hello, In 8i, the BULK COLLECT and FORALL commands are limited to working with scalar arrays. Does this restriction still apply in 9i? We are still on 8.1.6. In my case, I would like to do something like: TYPE tab_type IS TABLE OF oracle_table%ROWTYPE INDEX BY BINARY_INTEGER; plsql_table tab_type; SELECT * BULK COLLECT INTO plsql_table FROM oracle_table; and FORALL j IN plsql_table.FIRST..plsql_table.LAST INSERT INTO oracle_table VALUES (plsql_table(j)); Is this possible in 9i? Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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: Toepke, Kevin M 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: Customize my SQLPlus login
I have a custom-coded @connect.sql script that I run instead of the build-in connect statement HOST chmod 600 /tmp/xxx.sql /dev/null 2/dev/null SAVE /tmp/xxx.sql REPLACE CONNECT 1 @login.sql /bin/rm -f /tmp/xxx.sql /dev/null UNDEFINE 1 -Original Message- Sent: Tuesday, March 26, 2002 12:24 PM To: Multiple recipients of list ORACLE-L What happens if the user then issues a connect statement to connect to another database? It would be nice if there was a .sql file run every time a new connection is set, I don't know if that is the case in the 8i / 9i versions. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Tuesday, March 26, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Subject:Re: Customize my SQLPlus login in your oracle /bin directory - you need to alter (Or create) a login.sql file here's what mine looks like - feel free to alter /* start */ set heading on set pause off set pages 23 set lines 100 set verify off set feedback on set space 1 set serveroutput on size 100 set echo off set termout off column d_bname new_value d_bname select user ||'@'||instance_name d_bname from v$instance ; set sqlprompt 'd_bname. ' set termout on set pause on /* end */ Brian. -- 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: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Snapshots VS Materiazlized Views
materialized views are snapshots snapshots are materialized views. Any questions? -Original Message- Sent: Thursday, March 14, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Hello All, Sun 5.8 Oracle 8.1.7.2 At a client site the developers are building an application that runs against several data-marts. The marts have tables with many 16+ million record tables. I have explained to them that read-only materialized views would help speed up some of their queries. The local developers are pushing the use of snapshots, I would like to use materialized views. I do not have much experience with either one. I have been unable to find a good comparison of snapshots vs. materialized views. When should one be used over the other? What are the dvantages/disadvantages of either one in this situation? I know that query rewrite only works with MVs, but that isn't a factor in this situation. Thank you for any information you can provide. Todd Carlson Oracle Database Administrator Tripos, Inc. (314) 647-8837 Ext.3246 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Todd Carlson 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: Toepke, Kevin M 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: local partition index question
Yes. This is true -- even in Oracle 9i Caver -Original Message- Sent: Monday, March 11, 2002 9:58 AM To: Multiple recipients of list ORACLE-L Hi all, I am new to partition and I was told the following statement and I couldn't verify it in the doc. Could someone please tell me if it's true or not? The partition column must be included in the primary key for the resulting unique index to be locally partitioned. Thanks _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oracle dba INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Non-partitioned table to Hash Partitioned table
Thanks for the suggestion. I'll look into it and report back to the group. -Original Message- Sent: Thursday, February 28, 2002 2:14 PM To: Multiple recipients of list ORACLE-L Oracle9i provides on online data move facility that will allow you to do this with a minimum of restrictions. I discuss this, and give an example in my Oracle Press book, Oracle9i New Features. This is facilitated throughout the new dbms_redefinition package. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Thursday, February 28, 2002 11:28 AM To: Multiple recipients of list ORACLE-L Hello! I am trying to figure out the best way to convert a non-partitioned table with approx 20 million rows into a hash-partitioned table. This should be done with minimal down-time. This will be in an Oracle 9i environment done at a time when only SELECTs are occuring on the table. The best way I can figure is to create a second table and copy the data over; create the indexes; and rename the tables. I would appreciate any hints on what section of TFM to read. Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Freeman, Robert 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: Toepke, Kevin M 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: Non-partitioned table to Hash Partitioned table
Ron: Thanks for the suggestion. I have looked into this, and found that its very slow for hash partitions. It works well for range partitions. But, with hash parititions a row may move more than once (each time you add a partition, the row may move) And since I want 8 hash partitions, this is very slow. the first time you add a partition, you move 1/2 the rows the second time, you move AT LEAST 1/3 of the rows (as much as 1/2 of the rows) the third time, you move AT LEAST 1/4 of the rows (as much as 1/2 of the rows) etc all of the way to adding 7 partitions for a total of 8 kevin -Original Message- Sent: Thursday, February 28, 2002 1:44 PM To: Multiple recipients of list ORACLE-L Kevin, how about using the ALTER TABLE EXCHANGE command to move the info from a non-partitioned to a partitioned table. ROR mª¿ªm [EMAIL PROTECTED] 02/28/02 11:28AM Hello! I am trying to figure out the best way to convert a non-partitioned table with approx 20 million rows into a hash-partitioned table. This should be done with minimal down-time. This will be in an Oracle 9i environment done at a time when only SELECTs are occuring on the table. The best way I can figure is to create a second table and copy the data over; create the indexes; and rename the tables. I would appreciate any hints on what section of TFM to read. Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Ron Rogers 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: Toepke, Kevin M 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: Non-partitioned table to Hash Partitioned table
All:
The dbms_redefinition thing works. My test on a table that I have exclusive
access to it took 10 minutes to convert a non-partitioned table of 9.5
million rows into a has partitioned table with 8 partitions!
I have included the script I used.
Thanks to all that replied!
Caver
BEGIN
DBMS_REDEFINITION.can_redef_table('KTOEPKE', 'INVOICE_DETAIL');
END;
/
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('KTOEPKE'
,'INVOICE_DETAIL'
,'T_INVOICE_DETAIL');
END;
/
PROMPT
PROMPT ** Here is where to do the following:
**
PROMPT ** 1) Create Triggers on t_invoice_detail
**
PROMPT ** 2) Create Indexes on t_invoice_detail
**
PROMPT ** 3) Create Constraints on t_invoice_detail
**
PROMPT **The Constraints should be disabled
**
PROMPT ** 4) Perform any grants on t_invoice_detail
**
PROMPT
@@new_invoice_detail_idx.sql
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('KTOEPKE'
,'INVOICE_DETAIL'
,'T_INVOICE_DETAIL');
END;
/
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('KTOEPKE'
,'INVOICE_DETAIL'
,'T_INVOICE_DETAIL');
END;
/
-Original Message-
Sent: Thursday, February 28, 2002 2:14 PM
To: Multiple recipients of list ORACLE-L
Oracle9i provides on online data move facility that will allow you to
do this with a minimum of restrictions. I discuss this, and give an example
in my Oracle Press book, Oracle9i New Features. This is facilitated
throughout the new dbms_redefinition package.
RF
Robert G. Freeman - Oracle8i OCP
Oracle DBA Technical Lead
CSX Midtier Database Administration
The Cigarette Smoking Man: Anyone who can appease a man's conscience can
take his freedom away from him.
-Original Message-
Sent: Thursday, February 28, 2002 11:28 AM
To: Multiple recipients of list ORACLE-L
Hello!
I am trying to figure out the best way to convert a non-partitioned table
with approx 20 million rows into a hash-partitioned table. This should be
done with minimal down-time. This will be in an Oracle 9i environment done
at a time when only SELECTs are occuring on the table.
The best way I can figure is to create a second table and copy the data
over; create the indexes; and rename the tables.
I would appreciate any hints on what section of TFM to read.
Kevin Toepke
[EMAIL PROTECTED]
The information in this electronic mail message is Trilegiant Confidential
and may be legally privileged. It is intended solely for the addressee(s).
Access to this Internet electronic mail message by anyone else is
unauthorized. If you are not the intended recipient, any disclosure,
copying, distribution or action taken or omitted to be taken in reliance on
it is prohibited and may be unlawful.
The sender believes that this E-mail and any attachments were free of any
virus, worm, Trojan horse, and/or malicious code when sent. This message and
its attachments could have been infected during transmission. By reading the
message and opening any attachments, the recipient accepts full
responsibility for taking protective and remedial action about viruses and
other defects. Trilegiant Corporation is not liable for any loss or damage
arising in any way from this message or its attachments.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Toepke, Kevin M
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: Freeman, Robert
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
Non-partitioned table to Hash Partitioned table
Hello! I am trying to figure out the best way to convert a non-partitioned table with approx 20 million rows into a hash-partitioned table. This should be done with minimal down-time. This will be in an Oracle 9i environment done at a time when only SELECTs are occuring on the table. The best way I can figure is to create a second table and copy the data over; create the indexes; and rename the tables. I would appreciate any hints on what section of TFM to read. Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: cost based optimizer
Title: cost based optimizer
John:
The
Cost of a query is based on Oracle's estimation of the number of datablocks that
will have to be read in order to resolve the query. The Index-access Plan's cost
is higher because Oracle is estimating that it will have to process more
datablocks to return the requested rows.
Oracle's optimizer isn't perfect. I have found that the
Cost of a query is a reasonable measure of relative performance of queries only
when the estimated number of rows(Card=999) is reasonably
accurate.
You
didn't say what version of Oracle you are using. If you are using Oracle 8i or
above, you can use the Plan Stability feature to specify the explain plan for
the query without using hints.
HTH
Kevin
-Original Message-From: Baylis, John
[mailto:[EMAIL PROTECTED]]Sent: Friday, February 08, 2002 1:08
PMTo: Multiple recipients of list ORACLE-LSubject: cost
based optimizer
I have a table of 500,000 records that is analyzed
and contains several indexes that are analyzed.
Can someone explain to me why the cost without a
hint is much lower than the cost with a hint?
Using a hint is 10 times faster than without a hint
even though the cost is much higher.
Since this is a third party app, I cannot add
hints. What aoptions do I have?
select /*+ Index("ICMSSHDR" XSKSHDRS181M1)
Use this index XSKSHDRS181M1 */ PKTS_ICMSSHDR from ICMSSHDR where
(FK_IX_ICMSSHDR_DELV='x' and (SHDR_DELV_WHSE_CODE'86'));
Execution Plan --
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=213529 Card=228346
Bytes=12102338)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ICMSSHDR'
(Cost=213529 Card=228346 Bytes=12102338) 2 1 INDEX (RANGE
SCAN) OF 'XSKSHDRS181M1' (NON-UNIQUE) (Cost=2346 Card=228346)
select PKTS_ICMSSHDR from ICMSSHDR
where (FK_IX_ICMSSHDR_DELV='x' and
(SHDR_DELV_WHSE_CODE'86'));
Execution Plan --
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=3526 Card=228346
Bytes=12102338)
1 0 TABLE ACCESS (FULL) OF 'ICMSSHDR' (Cost=3526
Card=228346 Bytes=12102338)
Thanks
John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada
(604) 697-6476
(Office) (604)
313-6054 (Cell)
RE: CASE statement
Jared et al: Yes, the case statement has made it into 9i. There is the CASE expression that is usable in SQL (and PL/SQL) and two types of CASE statements as part of the PL/SQL language specification. Kevin -Original Message- Sent: Wednesday, January 30, 2002 1:25 AM To: Multiple recipients of list ORACLE-L This sounds like a SQL feature that has not made it into the PL/SQL engine as yet. There are a number of statistical functions such as regr_slope and regr_intercept that work in SQL, but not in PL/SQL. They are supposed to be in PL/SQL as of 9i, though I haven't checked myself. One way to work around this is to use EXECUTE IMMEDIATE in your PL/SQL so that the SQL engine must parse it. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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).
