using oracle 817 driver against oracle 92 database
Hi all: I just learned that our application team is using oracle 817 driver to connect to a oracle 92 application (via das and via websphere). Is anyone aware of any big problems with that setup? I am going to recommend to upgrade to oracle9, but I don't think this is an emergency. Am I wrong? thanks Gene __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how do I interpret this in bstat/estat
Tim, Thanks a lot. This does help. The data that I have are a result from a test run, so I can't do anything right now, but this does give me something to look at if I see the enqueue timeouts increasing in the database in the future. thank you Gene --- Tim Gorman <[EMAIL PROTECTED]> wrote: > Gene, > > This is the problem with high-level aggregate > reports like > BSTAT/ESTAT and STATSPACK. A possible problem is > highlighted, but there is no detail on the possible > cause. > > One way to get more info is monitor V$SESSION_EVENT > view > searching for sessions with lots of waits on > "enqueue" > wait-event: > > select sid, time_waited > from v$session_event > where event_name = 'enqueue' > order by 2 desc; > > When you find an active session that seems to fit, > then find > out more about it, and most especially slap a > "level-8" SQL > trace on the session for a period of time. Also, > get more > information from V$SESSION to understand what > program is > running, etc... > > Very often, you find out that the situation is > benign, for > example: > >* a single session is responsible for all enqueue > timeouts; this session simply waits on a > DBMS_PIPE for messages, but the sending > application is not sending any messages, thus > waiting application racks up lots of > timeouts... > > Hope this helps... > > -Tim > > > > Hi. > > > > I am looking at the bstat/estat report and see a > high > > number of enqueue timeouts in the statistics > section > > of the report. How do I tackle that? In the > Niemec's > > book he receoomends increasing the > enqueue_resources > > parameter. Metalink says that these may be related > to > > DISTRIBUTED_LOCK_TIMEOUT being exceeded in a > > distributed transaction. Comments about changing > > ENQUEUE_RESOURCES are ill-founded. But it doesn't > make > > any recommendations. > > So my question is whether anyone has any practical > > suggestions what I can do to address this issue. I > am > > running Oracle 9203 (yes, I should be usuns the > > statspack, but I haven't switched to it yet). > > > > TIA > > > > Gene > > > > > > __ > > Do you Yahoo!? > > Yahoo! Hotjobs: Enter the "Signing Bonus" > Sweepstakes > > http://hotjobs.sweepstakes.yahoo.com/signingbonus > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net -- > > Author: Gurelei > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 > > http://www.fatcity.com San Diego, California > -- > > Mailing list and web hosting services > > > -- > > --- To REMOVE yourself from this mailing > list, > > send an E-Mail message to: [EMAIL PROTECTED] > (note > > EXACT spelling of 'ListGuru') and in the message > BODY, > > include a line containing: UNSUB ORACLE-L (or the > name of > > mailing list you want to be removed from). You > may also > > send the HELP command for other information (like > > subscribing). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Tim Gorman > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
how do I interpret this in bstat/estat
Hi. I am looking at the bstat/estat report and see a high number of enqueue timeouts in the statistics section of the report. How do I tackle that? In the Niemec's book he receoomends increasing the enqueue_resources parameter. Metalink says that these may be related to DISTRIBUTED_LOCK_TIMEOUT being exceeded in a distributed transaction. Comments about changing ENQUEUE_RESOURCES are ill-founded. But it doesn't make any recommendations. So my question is whether anyone has any practical suggestions what I can do to address this issue. I am running Oracle 9203 (yes, I should be usuns the statspack, but I haven't switched to it yet). TIA Gene __ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
how do I interpret this in bstat/estat
Hi. I am looking at the bstat/estat report and see a high number of enqueue timeouts in the statistics section of the report. How do I tackle that? In the Niemec's book he receoomends increasing the enqueue_resources parameter. Metalink says that these may be related to DISTRIBUTED_LOCK_TIMEOUT being exceeded in a distributed transaction. Comments about changing ENQUEUE_RESOURCES are ill-founded. But it doesn't make any recommendations. So my question is whether anyone has any practical suggestions what I can do to address this issue. I am running Oracle 9203 (yes, I should be usuns the statspack, but I haven't switched to it yet). TIA Gene __ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ora-1555 under automatic undo management
Hi all: I'm having an ora-1555 under Oracle9 database and not sure what I can do to get rid of it. I had some recollecions from Oracle8 days , but the things like adding a new rollback segment or shrinking the segments I don't think are applicable under the auto undo management. Besides separating the long queries from batch programs, is ther anything that I can do here? thanks Gene __ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
what is free buffer scans
Hi. I'm reading chapter 16 of Niemec's book Oracle performance tuning and tips. It deals with using bstat/estat and he mentiones a statistics called free buffer scans. I couldn't see a statistics with that name in the v$statistics table on Oracle 817. Does anyone know whether it goes under a different name? thanks Gene __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
low number of free buffers in the buffer cache
Hi. I have noticed that the number of free db buffers in my bd cache is pretty low. Based on this query select decode(state,0,'Free',1,decode lrba_seq,0,'Available','Being Used'), 'Being Used',state) "Block Status", count(*) from x$bh group by decode(state,0,'Free',1,decode(lrba_seq,0,'Available','Being Used'), 3,'Being Used',state) I see 36000 available buffers and about 1000 being used. I don't have any complaints about performance (yet) and I know that too many free buffers indicates that the db_cache is too big. Is there the minimum number of free buffers that I would need to keep or can I just ignore it. TIA Gene __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ora 1575?
Chris, The query is showing 99.19% of the sorts done in memory. thank you Gene --- [EMAIL PROTECTED] wrote: > Are you doing a lot of sorting with a small > sort_area_size set? __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ora 1575?
Tim and Tanel: thanks for your help. The temp tablespace is LMTS and is TEMPORARY and less than 1% of all sorts has been done on disk. I will make sure to check the v$locks when this issue appears again. I have changed the pcticrease for all the tablespaces to 0% so may be this will take care of the issue. Gene --- Tim Gorman <[EMAIL PROTECTED]> wrote: > Tanel hit the nail on the head. In the past, > ORA-01575 was usually > associated with temporary tablespaces that were DMT > and not tablespace type > "TEMPORARY" (which started in Oracle7.3). First and > foremost, please make > sure you are using a TEMPORARY tablespace which is > locally-managed and uses > TEMPFILEs... > > It might be interesting to monitor V$LOCK for TYPE = > 'ST' to see what > sessions are holding this enqueue. If the activity > is too transient, > perhaps querying V$SESSION_EVENT where EVENT = > 'enqueue' might indirectly > imply which sessions have waited on an enqueue (not > necessarily "ST", > thought!) sometime in the past... > > > > on 8/13/03 7:04 AM, Tanel Poder at > [EMAIL PROTECTED] wrote: > > > Hi! > > > > You can always schedule alter tablespace > coalesce's during low usage time. > > But you should check whether you have adjacent > free extents in your > > tablespaces at all? If you're not doing lot's of > dropping or truncating > > objects, then you shouldn't have. Thus no need for > coalesce either. Just > > check that all of your sort segments go to the > temp tablespace (which should > > be in temporary mode, preferrably LMT as well). > > > > Tanel. > > > > > >> thanks for the info. We do have a number of DMTS > in > >> the database. Three of them have pct_increase of > 50%, > >> the rest - 0. Should I consider changing the > >> pct_increase to 0 in all tablespaces in order to > get > >> rid of this ora 1575? Wouldn't I want to have an > >> automatic coalesce process for the DMTS though? > >> > >> thank you > >> > >> Gene > >> --- Tim Gorman <[EMAIL PROTECTED]> wrote: > >>> Haven't seen this error since Oracle7... > >>> > >>> If the message is hitting the "alert.log", then > >>> chances are good it is > >>> coming from SMON. SMON is attempting to acquire > the > >>> "ST" (a.k.a. Space > >>> transaction) enqueue in preparation for > coalescing > >>> free space in some > >>> tablespaces. However, if it is unable to > acquire > >>> "ST" after a couple > >>> seconds, it times out and issues ORA-01575 to > the > >>> alert.log. > >>> > >>> So, based on experiences from 6-7 years ago: > >>> > >>> * do you have a lot of "dictionary-managed" > >>> tablespaces? > >>> * do these DMT's have default PCTINCREASE > >>> non-zero, thus attacting > >>> SMON to do coalescing? > >>> > >>> If so, I'd suggest going to "locally-managed" > >>> tablespaces if at all > >>> possible... > >>> > >>> > >>> > >>> on 8/12/03 12:44 PM, Gurelei at > [EMAIL PROTECTED] > >>> wrote: > >>> > >>>> Hi all: > >>>> > >>>> I'm seeing the ora-01575 error in the alert > >>> logfile. > >>>> The article on the metalink refers to the > >>> parameter > >>>> which I think is obsolete in the ORacle version > we > >>> are > >>>> running (8.1.7). What does this error refer to? > >>> Any > >>>> thoughts? references? > >>>> > >>>> thanks > >>>> > >>>> gene > >>>> > >>>> __ > >>>> Do you Yahoo!? > >>>> Yahoo! SiteBuilder - Free, easy-to-use web site > >>> design software > >>>> http://sitebuilder.yahoo.com > >>> > >>> -- > >>> Please see the official ORACLE-L FAQ: > >>> http://www.orafaq.net > >>> -- > >>> Author: Tim Gorman > >>> INET: [EMAIL PROTECTED] > >>> > >>> Fat City Network Services-- 858-538-5051 > >>> http://www.fatcity.com > >>> San Diego, California-- Mailing
ora 1575?
Hi all: I'm seeing the ora-01575 error in the alert logfile. The article on the metalink refers to the parameter which I think is obsolete in the ORacle version we are running (8.1.7). What does this error refer to? Any thoughts? references? thanks gene __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ora 1575?
Tim, thanks for the info. We do have a number of DMTS in the database. Three of them have pct_increase of 50%, the rest - 0. Should I consider changing the pct_increase to 0 in all tablespaces in order to get rid of this ora 1575? Wouldn't I want to have an automatic coalesce process for the DMTS though? thank you Gene --- Tim Gorman <[EMAIL PROTECTED]> wrote: > Haven't seen this error since Oracle7... > > If the message is hitting the "alert.log", then > chances are good it is > coming from SMON. SMON is attempting to acquire the > "ST" (a.k.a. Space > transaction) enqueue in preparation for coalescing > free space in some > tablespaces. However, if it is unable to acquire > "ST" after a couple > seconds, it times out and issues ORA-01575 to the > alert.log. > > So, based on experiences from 6-7 years ago: > > * do you have a lot of "dictionary-managed" > tablespaces? > * do these DMT's have default PCTINCREASE > non-zero, thus attacting > SMON to do coalescing? > > If so, I'd suggest going to "locally-managed" > tablespaces if at all > possible... > > > > on 8/12/03 12:44 PM, Gurelei at [EMAIL PROTECTED] > wrote: > > > Hi all: > > > > I'm seeing the ora-01575 error in the alert > logfile. > > The article on the metalink refers to the > parameter > > which I think is obsolete in the ORacle version we > are > > running (8.1.7). What does this error refer to? > Any > > thoughts? references? > > > > thanks > > > > gene > > > > __ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free, easy-to-use web site > design software > > http://sitebuilder.yahoo.com > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Tim Gorman > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
alter table exchage partition moves statistics?
Hi. The subject actually says it all. someone on my team did a quick test and it looked like the statistics have been moved by the alter table command along with the data. Is that what others experience? Are there situations where this is not the case? thanks gene __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: optimizer parameters in Oracle 9.2
Tim, I noticed that, but still it is a great article and I'm looking forward to reading the updated version thanks for your help Gene --- Tim Gorman <[EMAIL PROTECTED]> wrote: > Please be aware that the paper and presentation were > written in the 8i > timeframe. I swear I'll get it updated for 9i, but > already 10G is looming > and it's summer and the weather is > nice... > > > > on 7/30/03 8:24 AM, Gurelei at [EMAIL PROTECTED] > wrote: > > > Rich, > > > > thanks a lot. I just finished reading Tim's paper > and > > it is truly enjoyable reading. I'm going to test > his > > advices in a few moments. > > > > Gene > > --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > >> Paging Mr. GormanMr. Tim Gorman... > >> > >> I had wonderful luck with Tim's paper "The Search > >> for Intelligent Life in > >> the Cost-Based Optimizer" from: > >> http://www.evdbt.com/papers.htm > >> > >> Enjoy! (And thanks again, Tim!) > >> > >> Rich > >> > >> Rich Jesse > System/Database > >> Administrator > >> [EMAIL PROTECTED] Quad/Tech > Inc, > >> Sussex, WI USA > >> > >> > >>> -Original Message- > >>> From: Gurelei [mailto:[EMAIL PROTECTED] > >>> Sent: Wednesday, July 30, 2003 9:04 AM > >>> To: Multiple recipients of list ORACLE-L > >>> Subject: optimizer parameters in Oracle 9.2 > >>> > >>> > >>> Hi all: > >>> > >>> I have been reading on the optimizer_ parameters > >> for > >>> Oracle 9i - optimizer_max_permutation, > >>> optimizer_index_caching and > >> optimizer_index_cost_adj. > >>> I have also been playing whith them and I can > see > >> that > >>> they affect the explain plans for some queries > >> quite > >>> dramatically. Is there any guidelines for what > >> they > >>> should be set to for optimum performance (some > >> kind of > >>> range and may be relationship between the > values) > >> or > >>> is this mostly a trial and error kind of thing? > I > >> have > >>> seen a few discussion on the Oracle Metalink, > but > >>> mostly they are very specific - try this value > or > >> try > >>> that value. Does anyone have a link to a > document > >> with > >>> something more systematic than that? > >>> > >>> > >>> thank you > >>> > >>> Gene > >> -- > >> Please see the official ORACLE-L FAQ: > >> http://www.orafaq.net > >> -- > >> Author: Jesse, Rich > >> INET: [EMAIL PROTECTED] > >> > >> Fat City Network Services-- 858-538-5051 > >> http://www.fatcity.com > >> San Diego, California-- Mailing list and > web > >> hosting services > >> > > > - > >> To REMOVE yourself from this mailing list, send > an > >> E-Mail message > >> to: [EMAIL PROTECTED] (note EXACT spelling of > >> 'ListGuru') and in > >> the message BODY, include a line containing: > UNSUB > >> ORACLE-L > >> (or the name of mailing list you want to be > removed > >> from). You may > >> also send the HELP command for other information > >> (like subscribing). > > > > > > __ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free, easy-to-use web site > design software > > http://sitebuilder.yahoo.com > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Tim Gorman > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: optimizer parameters in Oracle 9.2
Rich, thanks a lot. I just finished reading Tim's paper and it is truly enjoyable reading. I'm going to test his advices in a few moments. Gene --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > Paging Mr. GormanMr. Tim Gorman... > > I had wonderful luck with Tim's paper "The Search > for Intelligent Life in > the Cost-Based Optimizer" from: > http://www.evdbt.com/papers.htm > > Enjoy! (And thanks again, Tim!) > > Rich > > Rich Jesse System/Database > Administrator > [EMAIL PROTECTED] Quad/Tech Inc, > Sussex, WI USA > > > > -Original Message- > > From: Gurelei [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, July 30, 2003 9:04 AM > > To: Multiple recipients of list ORACLE-L > > Subject: optimizer parameters in Oracle 9.2 > > > > > > Hi all: > > > > I have been reading on the optimizer_ parameters > for > > Oracle 9i - optimizer_max_permutation, > > optimizer_index_caching and > optimizer_index_cost_adj. > > I have also been playing whith them and I can see > that > > they affect the explain plans for some queries > quite > > dramatically. Is there any guidelines for what > they > > should be set to for optimum performance (some > kind of > > range and may be relationship between the values) > or > > is this mostly a trial and error kind of thing? I > have > > seen a few discussion on the Oracle Metalink, but > > mostly they are very specific - try this value or > try > > that value. Does anyone have a link to a document > with > > something more systematic than that? > > > > > > thank you > > > > Gene > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Jesse, Rich > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
optimizer parameters in Oracle 9.2
Hi all: I have been reading on the optimizer_ parameters for Oracle 9i - optimizer_max_permutation, optimizer_index_caching and optimizer_index_cost_adj. I have also been playing whith them and I can see that they affect the explain plans for some queries quite dramatically. Is there any guidelines for what they should be set to for optimum performance (some kind of range and may be relationship between the values) or is this mostly a trial and error kind of thing? I have seen a few discussion on the Oracle Metalink, but mostly they are very specific - try this value or try that value. Does anyone have a link to a document with something more systematic than that? thank you Gene __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
convert a b-tree index into a bitmap index
Hi all: If I wanted to convert a b-tree index into a bitmap one under oracle 817, is the only way to drop and recreate it or is there an alter index bitmap command? (couldn't find it in the manual, so I suspect there is no such command) thank you gene __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
IOT
Hi all: I'm condsidering creating an IO table in Oracle9. It is a small table with 2 columns and has a composite index so I thought that an IO table would make sence. Do they have any disadvantages I should be aware of? What I read on OTN site didn't mention any. thanks Gene __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
how do I test a restore of a raw device?
Hi all: I'm testing a restore of a database file (tools TS). The datafile is a raw device and I have link to that raw evice in my links directory. I"m running Oracle 9203 and UNIX 5l. And the problem I ran into is as follows. If I drop the logical link , the rman restores the data as a datafile. If I remove the link and recreate it pointing to another raw device, the restore fails bacause the file exist. How do I force rman to restore the data in the raw device form? IS there a way to do it? thanks gene __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: tunning an index built
Hi all. Thanks to all who replied. I'm still trying to speed the things up and wondering whether someone can explain what is "paging to file system" is referring to? Does this indicate that I don't have enough memory? Is it related to reads? Anything else? thanks > > --- zhu chao <[EMAIL PROTECTED]> wrote: > > Hi, > > First of all, you should use nologging. > This helps a lot. > > Second, when using parallel clause, the > sort_area_size of the > > ora_pxxx size is not the sort_area_size of your > session, it is the > > instance's default size, when it is started. I > have verified it. > > So if you want to make parallel works > better, you can increase the > > sort_area_size of your instance and bounce the > instance. > > Also try to increase > multiblock_read_count, pay attention to your > > cpu number and max ora_pxxx number. > > > > Regards > > zhu chao > > msn:[EMAIL PROTECTED] > > www.cnoug.org > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > > Sent: Wednesday, July 09, 2003 7:09 AM > > > > > > > DB Cache doesn't help you. IO waits mean that > oracle processes are waiting > > > to complete I/O requests. The I/O waits usually > come with arguments like > > > P1TEXT,P1,P2TEXT,P2,P3TEXT and P3 which can help > you in locating file and > > > block that the oracle processes are waiting for. > Move those files and > > blocks > > > to separate I/O devices, so that I/O can be > processed in parallel. When > > > creating so big indexes you need not only CPU > power, you need I/O > > throughput. > > > Paging is to be expected in order for oracle > processes to allocate that > > big > > > sort_area_size. Try tuning you memory and disk > drives by using sar, > > iostat, > > > vmstat, top and/or glance (Glance is the best > monitor I've seen so far). > > > You may need to move your temporary tablespace > to alternative location. > > Also, > > > be sure to fix you OS paging/swapping > parameters. If possible, use direct > > IO > > > to prevent double buffering and if not, limit OS > buffer cache to a very > > small > > > portion of memory. Paging and caching is usually > a consequence of dynamic > > > buffer cache. Parameter name should be something > like NBUF. > > > > > > On 2003.07.08 17:29, Gurelei wrote: > > > > Hi. > > > > > > > > I'm trying to tune an index build. The table > currently > > > > has about 65mil rows and I'm building a unique > index, > > > > which takes about 55min to finish. The table > size is > > > > about 3.4G, index is about the same size. I > have tried > > > > different degrees of parallelism (up to 32), > nologging > > > > is set in the create index script as well as > on the > > > > tablespace. I noticed a lot of i/o waits > during the > > > > buid and a lot of paging to and from > filesystem, the > > > > paging area however appears to be unused. when > I do > > > > lsps -a, it only shows 1% usage. What should > be my > > > > next move? What should I look at? i have > increased > > > > db_cache to 800M, sort area to 50M > > > > > > > > thanks > > > > > > > > Gene > > > > > > > > > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Kirtikumar Deshpande > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (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!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: tunning an index built
Sort_area_size is set to 50M for this session. almost 16000 sotrs in memory vs 23 sotrs on disk Gene --- John Kanagaraj <[EMAIL PROTECTED]> wrote: > How about Sort_area_size? This will matter - check > for 'sorts to disk' and > 'sort rows' from that session's v$sesstat (joined to > v$statname). > > John Kanagaraj > Oracle Applications DBA > DBSoft Inc > (W): 408-970-7002 > > Grace - Getting something we don't deserve; Mercy - > NOT getting something we > deserve > Click on 'http://www.needhim.org' for Grace and > Mercy that is freely > available! > > ** The opinions and statements above are entirely my > own and not those of my > employer or clients ** > > > > -Original Message- > > From: Gurelei [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, July 08, 2003 2:30 PM > > To: Multiple recipients of list ORACLE-L > > Subject: tunning an index built > > > > > > Hi. > > > > I'm trying to tune an index build. The table > currently > > has about 65mil rows and I'm building a unique > index, > > which takes about 55min to finish. The table size > is > > about 3.4G, index is about the same size. I have > tried > > different degrees of parallelism (up to 32), > nologging > > is set in the create index script as well as on > the > > tablespace. I noticed a lot of i/o waits during > the > > buid and a lot of paging to and from filesystem, > the > > paging area however appears to be unused. when I > do > > lsps -a, it only shows 1% usage. What should be my > > next move? What should I look at? i have increased > > db_cache to 800M, sort area to 50M > > > > thanks > > > > Gene > > > > __ > > Do you Yahoo!? > > SBC Yahoo! DSL - Now only $29.95 per month! > > http://sbc.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: Gurelei > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > San Diego, California-- Mailing list and > web hosting services > > > - > > To REMOVE yourself from this mailing list, send an > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > ORACLE-L > > (or the name of mailing list you want to be > removed from). You may > > also send the HELP command for other information > (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: John Kanagaraj > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: tunning an index built
I had it set to 50M. I'll try 200M tonight. thanks Gene --- Kevin Toepke <[EMAIL PROTECTED]> wrote: > what is your sort_area_size? Try changing it to > something huge (say > 200MB)for the session... > alter session set sort_area_size = 2; > > Kevin > > -Original Message- > Sent: Tuesday, July 08, 2003 5:30 PM > To: Multiple recipients of list ORACLE-L > > > Hi. > > I'm trying to tune an index build. The table > currently > has about 65mil rows and I'm building a unique > index, > which takes about 55min to finish. The table size is > about 3.4G, index is about the same size. I have > tried > different degrees of parallelism (up to 32), > nologging > is set in the create index script as well as on the > tablespace. I noticed a lot of i/o waits during the > buid and a lot of paging to and from filesystem, the > paging area however appears to be unused. when I do > lsps -a, it only shows 1% usage. What should be my > next move? What should I look at? i have increased > db_cache to 800M, sort area to 50M > > thanks > > Gene > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Gurelei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be 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: Kevin Toepke > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (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!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
tunning an index built
Hi. I'm trying to tune an index build. The table currently has about 65mil rows and I'm building a unique index, which takes about 55min to finish. The table size is about 3.4G, index is about the same size. I have tried different degrees of parallelism (up to 32), nologging is set in the create index script as well as on the tablespace. I noticed a lot of i/o waits during the buid and a lot of paging to and from filesystem, the paging area however appears to be unused. when I do lsps -a, it only shows 1% usage. What should be my next move? What should I look at? i have increased db_cache to 800M, sort area to 50M thanks Gene __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
enable fk into exceptions
hi. I'm trying to enable a FK on a table. when I execute aler table t1 enable constraint c1; everything works fine; when I execute alter table t1 enable constraint c1 exceptions into exceptions; I'm getting ora 2445 - exceptions table not found. when I do desc exceptions, the table is right here and the id I'm logged as is the owner. Can someone see what is wrong with this picture? Am I missing some privileges here? thanks gene __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
how to pass a parameter to a MS Access path-through query running against Oracle
Hi all: My developers are having problem when running a path-through query with parameters via Access against Oracle. Does anyone happen to know a way to do it (or a reference to explain it)? thanks __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: MS Access as a front-end to Oracle DB
Dick, what if the second table is not local, but another remote ORacle table. How would the things change in this case? Gene --- "Goulet, Dick" <[EMAIL PROTECTED]> wrote: > Yes, you are misunderstanding it. A simple > statement like your will result in only the data > required being sent over the network. But if you > add in a second table things change, especially if > that table is a local access table. > > Dick Goulet > Senior Oracle DBA > Oracle Certified 8i DBA > > -Original Message- > Sent: Friday, June 20, 2003 5:45 PM > To: Multiple recipients of list ORACLE-L > > > Hi all: > > I have been hearing from many people that MS Access > is > bad as a front-end tool because it tends to do data > processing on the clien side instead of the DB side > thus moving way too much data over the network. > Assuming that this is correct, what is the mechanism > of this? If I execute a simple query like: > > select f1, f2 from t1 > where f3='X'; > > is MS access going to copy the whole table t1 to my > machine and only show the rows with f3 equal to 'X'? > Am I misinderstanding it? > > tia > > Gene > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Gurelei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Goulet, Dick > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
MS Access as a front-end to Oracle DB
Hi all: I have been hearing from many people that MS Access is bad as a front-end tool because it tends to do data processing on the clien side instead of the DB side thus moving way too much data over the network. Assuming that this is correct, what is the mechanism of this? If I execute a simple query like: select f1, f2 from t1 where f3='X'; is MS access going to copy the whole table t1 to my machine and only show the rows with f3 equal to 'X'? Am I misinderstanding it? tia Gene __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: copy a datafile to a raw device
Dennis, Thanks for your input. I am doing what the manual says - shutdown , copy, mount, alter, open and every time I'm getting the "unknown file header" message. I suspect that I am missing some parameters in my dd command because of - as Stephen suggested - different formats. I guess I will have to play with different parameters to see which one is correct. thanks Gene --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Gene >Good ideas from Stephen. A more basic issue if > you haven't dealt with it > is that copying a system datafile is different from > copying other datafiles. > You can't have the database open. I recall that you > have to STARTUP MOUNT > and ALTER DATABASE DATAFILE. This procedure is > covered in the Oracle > Manuals. Just thought I should mention this in case > you hadn't considered > it. > > Dennis Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
copy a datafile to a raw device
hi all: I need to move a SYSTEM datafile from a filesystem to a raw device. I have shutdown the database, copied the file via dd if=system of=/dev/raw renamed the file in the database, but couldn't open the database because of the error: Unknown File Header Version read for file number 1. I tried another dd: dd if=system of=/dev/raw obs=32768 (32K is the db block size), but I got the same error. Can anyone see what else could be wrong with my dd command thanks Gene __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: exchange partition in 9.2
Arup and Jacques: thanks for your help. The command we are executing is alter table exchange partition ... with table .. include indexes. We have also a PK on each of the tables (local index on a partitioned table). Go global indices. We have tried the same command with novalidate and the results were the same. Any thougths? --- Jacques Kilchoer <[EMAIL PROTECTED]> wrote: > Did you say alter table exchange partition ... > validate or alter table exchange partition ... > novalidate? If you say novalidate then Oracle will > sort the exchange table to make sure that you are > not creating duplicate rows for the PK constraint. > There is a Metalink note on that. > Does the table have a PK enforced by a locally > partitioned unique index? Any global indexes? > What options did you have on the exchange partition > statement? > > > -Original Message- > > From: Gurelei [mailto:[EMAIL PROTECTED] > > > > We are having an interesting issue with the > exchange > > partition command in our shop. We are running > oracle > > 9202 on AIX 5l. The exchange partition command > used > > to take about 1 sec (or less) in our old > environmen > > (oracle 81 Dynix 4.3.3). Now the same operation > takes > > about a minute and creates a ton of IO. an Oracle > rep > > said that this has to do something with the way > > exchange partition now treats primary and unique > keys. > > Recently however a DBA on my team did 4 exchanges > in a > > row between the same two tables. First one took > about > > 45s and did a lot of IO. next one took almost no > time > > and no IO (and it did move the data), the next one > - > > again 45s and IOs, the fourth one - no time again. > > Does any one has any experience with this issue > and > > may be can shed some light on it. > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Jacques Kilchoer > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
exchange partition in 9.2
Hi all: We are having an interesting issue with the exchange partition command in our shop. We are running oracle 9202 on AIX 5l. The exchange partition command used to take about 1 sec (or less) in our old environmen (oracle 81 Dynix 4.3.3). Now the same operation takes about a minute and creates a ton of IO. an Oracle rep said that this has to do something with the way exchange partition now treats primary and unique keys. Recently however a DBA on my team did 4 exchanges in a row between the same two tables. First one took about 45s and did a lot of IO. next one took almost no time and no IO (and it did move the data), the next one - again 45s and IOs, the fourth one - no time again. Does any one has any experience with this issue and may be can shed some light on it. thanks gene __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
change a LMTS parameters
Hi all: Is there a way to change an extent size for a localy managed tablespace? Or do I have to create a new TS with a right extent size? I'm not finding it in a manual (so far) so if this is in RTFM please mention which exactly FM I should R :) thanks __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to use rPerf to estimate a partitioned server capabilities
Hi. I'm trying to see whether a logical partition of a IBM server will have enough resources to support our databse based on the size of the DB. In the past the way I did it was to take an rperf, multipy by 100-125M and see whether it will exceed my estimate for a DB size (as suggested in IBM REd book). This time around we are going to get a logical partition of a p690 server. The question that I face now is how do I get the value of an rperf. For example, the rperf for 8CPUs is 12.72. If we get 4CPU, do I use 6.36 for the rperf (that is half of 12.72)? Or should I use some other number? Has anyone been through this process before and has any insight? thanks Gene __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: the number of concurrent queries
thank you
--- Stephane Paquette
<[EMAIL PROTECTED]> wrote:
> select * from v$sysstat where NAME in ('user
> commits','user rollbacks');
>
>
>
> -Original Message-
> Sent: Monday, March 24, 2003 7:54 AM
> To: Multiple recipients of list ORACLE-L
>
>
> I guess I have been using a "sql statement" and a
> "query" interchangebly. I am using status = 'ACTIVE'
> to identify the sqls currently running. For the
> user
> commits and rollbacks, which table contain these
> values?
>
> thank you
> gene
> --- Stephane Paquette
> <[EMAIL PROTECTED]> wrote:
> > v$session.status indicates that a sql statement is
> > going on.
> > I do not know any indicator on query.
> >
> > But you do have indicators on transactions.
> >
> > The total transactions are the sum of user commits
> > and user rollbacks.
> > Check those 2 statistics.
> >
> > Stephane
> >
> > -Original Message-
> > Sent: Friday, March 21, 2003 12:29 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Stephane:
> >
> > Why do you think if would records the number of
> > concurrent transactions, but not the queries? what
> > is
> > the difference from the v$session standpoint/
> >
> >
> > --- Stephane Paquette
> > <[EMAIL PROTECTED]> wrote:
> > > I do not think that you could record the number
> of
> > > concurrent queries.
> > > On the other hand, you can record the number of
> > > concurrent transactions.
> > > That is what your query will do.
> > >
> > > Another way is to use statspack.
> > > We're running statspack on our 22 production
> > > instances at each hour.
> > > We're keeping 2 months of stats.
> > > We have a central point collecting data through
> > > views on statspack tables
> > > and producing nice graphs
> > >
> > > My next job is to convince that we should
> monitor
> > > jobs also.
> > >
> > >
> > > Stephane
> > >
> > >
> > > -Original Message-
> > > Sent: Friday, March 21, 2003 9:24 AM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Hi.
> > >
> > > I'm trying to get a feel of the number of the
> > > queries
> > > executed at the same time against a database. I
> > have
> > > been using the following script (actually this
> is
> > a
> > > part of a script)
> > >
> > > Select a.type, count(1) col1
> > > from v$session a,
> > > v$process b
> > > where
> > > a.paddr=b.addr
> > > and a.status like 'ACTIVE%'
> > > and a.type <> 'BACKGROUND'
> > > and a.osuser <> 'oracle'
> > >
> > > I want to see only ACTIVE sessions,
> not-background
> > > and
> > > not executed by oracle. Does anyone see anything
> > > wrong
> > > with that?
> > >
> > > thanks
> > > Gene
> > >
> > >
> > >
> __
> > > Do you Yahoo!?
> > > Yahoo! Platinum - Watch CBS' NCAA March Madness,
> > > live on your desktop!
> > > http://platinum.yahoo.com
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.net
> > > --
> > > Author: Gurelei
> > > INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services-- 858-538-5051
> > > http://www.fatcity.com
> > > San Diego, California-- Mailing list and
> > web
> > > hosting services
> > >
> >
>
-
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> > 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: St
RE: the number of concurrent queries
I guess I have been using a "sql statement" and a "query" interchangebly. I am using status = 'ACTIVE' to identify the sqls currently running. For the user commits and rollbacks, which table contain these values? thank you gene --- Stephane Paquette <[EMAIL PROTECTED]> wrote: > v$session.status indicates that a sql statement is > going on. > I do not know any indicator on query. > > But you do have indicators on transactions. > > The total transactions are the sum of user commits > and user rollbacks. > Check those 2 statistics. > > Stephane > > -Original Message- > Sent: Friday, March 21, 2003 12:29 PM > To: Multiple recipients of list ORACLE-L > > > Stephane: > > Why do you think if would records the number of > concurrent transactions, but not the queries? what > is > the difference from the v$session standpoint/ > > > --- Stephane Paquette > <[EMAIL PROTECTED]> wrote: > > I do not think that you could record the number of > > concurrent queries. > > On the other hand, you can record the number of > > concurrent transactions. > > That is what your query will do. > > > > Another way is to use statspack. > > We're running statspack on our 22 production > > instances at each hour. > > We're keeping 2 months of stats. > > We have a central point collecting data through > > views on statspack tables > > and producing nice graphs > > > > My next job is to convince that we should monitor > > jobs also. > > > > > > Stephane > > > > > > -Original Message- > > Sent: Friday, March 21, 2003 9:24 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Hi. > > > > I'm trying to get a feel of the number of the > > queries > > executed at the same time against a database. I > have > > been using the following script (actually this is > a > > part of a script) > > > > Select a.type, count(1) col1 > > from v$session a, > > v$process b > > where > > a.paddr=b.addr > > and a.status like 'ACTIVE%' > > and a.type <> 'BACKGROUND' > > and a.osuser <> 'oracle' > > > > I want to see only ACTIVE sessions, not-background > > and > > not executed by oracle. Does anyone see anything > > wrong > > with that? > > > > thanks > > Gene > > > > > > __ > > Do you Yahoo!? > > Yahoo! Platinum - Watch CBS' NCAA March Madness, > > live on your desktop! > > http://platinum.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > -- > > Author: Gurelei > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 > > http://www.fatcity.com > > San Diego, California-- Mailing list and > web > > hosting services > > > - > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > (or the name of mailing list you want to be > removed > > from). You may > > also send the HELP command for other information > > (like subscribing). > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > -- > > Author: Stephane Paquette > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 > > http://www.fatcity.com > > San Diego, California-- Mailing list and > web > > hosting services > > > - > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > (or the name of mailing list you want to be > removed > > from). You may > > also send the HELP command for other information > > (like subscribing). > > > > > __ > Do you Yahoo!? > Yahoo! Platinum - Watch CBS' NCAA March Madness, > live on your desktop! > http://platinum.yahoo.com > -- > Please see the official ORACLE-
RE: the number of concurrent queries
prakash, thanks. --- "BALA,PRAKASH (HP-USA,ex1)" <[EMAIL PROTECTED]> wrote: > Hi Gene, > > The following where clause will work: > > a.paddr=b.addr > and a.status = 'ACTIVE' > and a.username is not null > > > -Original Message- > Sent: Friday, March 21, 2003 09:24 > To: Multiple recipients of list ORACLE-L > > > Hi. > > I'm trying to get a feel of the number of the > queries > executed at the same time against a database. I have > been using the following script (actually this is a > part of a script) > > Select a.type, count(1) col1 > from v$session a, > v$process b > where > a.paddr=b.addr > and a.status like 'ACTIVE%' > and a.type <> 'BACKGROUND' > and a.osuser <> 'oracle' > > I want to see only ACTIVE sessions, not-background > and > not executed by oracle. Does anyone see anything > wrong > with that? > > thanks > Gene > > > __ > Do you Yahoo!? > Yahoo! Platinum - Watch CBS' NCAA March Madness, > live on your desktop! > http://platinum.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Gurelei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be 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: BALA,PRAKASH (HP-USA,ex1) > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (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!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: the number of concurrent queries
Stephane: Why do you think if would records the number of concurrent transactions, but not the queries? what is the difference from the v$session standpoint/ --- Stephane Paquette <[EMAIL PROTECTED]> wrote: > I do not think that you could record the number of > concurrent queries. > On the other hand, you can record the number of > concurrent transactions. > That is what your query will do. > > Another way is to use statspack. > We're running statspack on our 22 production > instances at each hour. > We're keeping 2 months of stats. > We have a central point collecting data through > views on statspack tables > and producing nice graphs > > My next job is to convince that we should monitor > jobs also. > > > Stephane > > > -Original Message- > Sent: Friday, March 21, 2003 9:24 AM > To: Multiple recipients of list ORACLE-L > > > Hi. > > I'm trying to get a feel of the number of the > queries > executed at the same time against a database. I have > been using the following script (actually this is a > part of a script) > > Select a.type, count(1) col1 > from v$session a, > v$process b > where > a.paddr=b.addr > and a.status like 'ACTIVE%' > and a.type <> 'BACKGROUND' > and a.osuser <> 'oracle' > > I want to see only ACTIVE sessions, not-background > and > not executed by oracle. Does anyone see anything > wrong > with that? > > thanks > Gene > > > ______ > Do you Yahoo!? > Yahoo! Platinum - Watch CBS' NCAA March Madness, > live on your desktop! > http://platinum.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Gurelei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Stephane Paquette > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 number of concurrent queries
Hi. I'm trying to get a feel of the number of the queries executed at the same time against a database. I have been using the following script (actually this is a part of a script) Select a.type, count(1) col1 from v$session a, v$process b where a.paddr=b.addr and a.status like 'ACTIVE%' and a.type <> 'BACKGROUND' and a.osuser <> 'oracle' I want to see only ACTIVE sessions, not-background and not executed by oracle. Does anyone see anything wrong with that? thanks Gene __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Weekly aggregates
I think I need to read more on the MVs. Right now I'm very unclear how can they add something to a prebuild table. I thought they could replace an aggregate. Thanks for the advice, everyone (Paula, Beth, Dennis, Darrel, hope I didn't miss anyone). I might revive this thread when I get some more knoledge about MVs. Gene --- Jonathan Lewis <[EMAIL PROTECTED]> wrote: > > One of the options for materialized views > is to 'create MV on prebuilt table' > > If you want to use the table for direct queries, > then you can - if you enable query rewrite then > suitable queries against the base tables can > be rewritten to take advantage of the MV. > > It's a technique I've advised clients to use > when materialized views first appeared and > they already had a big investment in code to > create aggregate tables. Prebuilt tables > are also my favourite option for MVs - you > don't get the awful unpredictable that can > appear with dynamic refreshes. > > > 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___April 8th > UK___April 22nd > > Denmark May 21-23rd > > 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: 20 March 2003 22:09 > > > > Paula, > > > > I'm not sure what would an MV on top of an > aggregate > > give me. I mean, I have a base table and I need to > > aggregate. I can see doing it via an aggregate > table > > or (as someone suggested) via an MV. But you are > > suggesting both. What benefit do you see from > that? > > What am I missing? > > > > > -- > 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). > __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Weekly aggregates
Paula, I'm not sure what would an MV on top of an aggregate give me. I mean, I have a base table and I need to aggregate. I can see doing it via an aggregate table or (as someone suggested) via an MV. But you are suggesting both. What benefit do you see from that? What am I missing? thanks --- [EMAIL PROTECTED] wrote: > What about materialized view on top of the > partitioned table aggregated by > week? I don't remember is there a restriction that > the material. view must > be partitioned same as table? If not it might be a > bit of a perf. hit but > only once. Also, can determine how to populate the > mat. view and if you > just get a week at a time can set it up so that just > those changes are > pushed out to the mater. view. - Just a thought. > > Oracle OCP DBA > > > -Original Message- > Sent: Thursday, March 20, 2003 3:24 PM > To: Multiple recipients of list ORACLE-L > > > > I've used both methods at different sites for > different reasons. > > If you need the performance of the > partition-wise join, then you keep > the weekly aggregates in monthly > partitions, and work around the > problems of not being able to do > a single week > create as select / exchange partition > > If the performance is adequate without > partitionwise joins, but you need to > be able to build the aggregates as > rapidly as possible, then use weekly > partitions. 3 years at weekly partitions > is only 150 - 160 partitions - even with > a handful of indexes, that shouldn't be > a problem at parse time. > > > 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___April 8th > UK___April 22nd > > Denmark May 21-23rd > > 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: 20 March 2003 19:18 > > > > Hi all: > > > > An application I'm supporting needs weekly > aggregates. > > Nothing wrong with that except I'm thinking of how > to > > partition that aggregate table. The requirement is > to > > keep 3 year history of data. I have been > partitioning > > other aggregate tables (monthly etc) by month. > This > > makes it easy to drop old partitions AND Oracle > can > > use the partitions to reduce the size of the data > for > > some queries. I'd like to keep the montly > partitioning > > in for the uniformity reasons, but weeks do not > lay > > over months, a week can span two months and > therefore > > the usefulness of partitions for some of the > reports > > will be reduced. I'm wondering how do others > approach > > this. Do people partition weekly aggregates by > week > > instead of months? ANy other thoughts? > > > > thanks > > > > Gene > > > -- > 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). > __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Weekly aggregates
Hi all: An application I'm supporting needs weekly aggregates. Nothing wrong with that except I'm thinking of how to partition that aggregate table. The requirement is to keep 3 year history of data. I have been partitioning other aggregate tables (monthly etc) by month. This makes it easy to drop old partitions AND Oracle can use the partitions to reduce the size of the data for some queries. I'd like to keep the montly partitioning in for the uniformity reasons, but weeks do not lay over months, a week can span two months and therefore the usefulness of partitions for some of the reports will be reduced. I'm wondering how do others approach this. Do people partition weekly aggregates by week instead of months? ANy other thoughts? thanks Gene __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: update 30mil rows - thanks
Chao, Connor, Sanjay, cary, mark, richard: Thanks for your input. I didn't think of creating a new table and then renaming it as many of you suggested this may take les time. thank you all very much Gene --- chao_ping <[EMAIL PROTECTED]> wrote: > Gurelei, > I suggest your create your new table as select > other_columns,0 from your old table with nologging > and parallel. > > > > > > Regards > zhu chao > msn:[EMAIL PROTECTED] > www.happyit.net > www.cnoug.org(China Oracle User Group) > > === 2003-02-25 13:26:00 ,you wrote£º=== > > >Hi all: > > > >I need to update every row 30mil-rows table. > >I have dropped the indices and running the update > > in parallel: > > > >update /*+ parallel (degree 8) */ table_name > >set field1=0; > > > >Is there anything else I could to to speed up this > >process. I don't think I can do an update in > nologging > >mode. I'm running 8.1.7.4 > > > >thanks for any advice. > > > >__ > >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: Gurelei > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > >San Diego, California-- Mailing list and > web hosting services > >- > >To REMOVE yourself from this mailing list, send an > E-Mail message > >to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > >the message BODY, include a line containing: UNSUB > ORACLE-L > >(or the name of mailing list you want to be 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: 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). > __ 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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
update 30mil rows
Hi all: I need to update every row 30mil-rows table. I have dropped the indices and running the update in parallel: update /*+ parallel (degree 8) */ table_name set field1=0; Is there anything else I could to to speed up this process. I don't think I can do an update in nologging mode. I'm running 8.1.7.4 thanks for any advice. __ 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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OEM-related *.osc files
Hi all: WE are running OEM 817 and today got a message that some task failed because a file /tmp/011518.osc not found. Does anyone know what does "osc" stand for. On the metalink I found Oracle Sales Compensation items, but we are not using this. Has anyone seen this message before? thanks __ 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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
what does INACTIVE session do?
Hi this is a pretty trivial question. I have executed a set of sql scripts. The scripts included a set ofSELECT command followed by exit. While the scripts have been running, I was checking the number of sessions open and their status. I have noticed that at any time I had a number of sessions with ACTIVE status and the number of sessions with INACTIVE status. I alway thought that INACTIVE session is an idle session, a user that is connected to oracle, but is not running anything. In my case, a session was supposed to be closed after the last SELECT and therefore I did not expect any INACTIVE sessions. What is the explanaion here? thanks Gene __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle 7.3 recovery
Hi, This is a question from ancient times. I need to restore data from backup. The backup was created under EBU for a 7.3.4 database. The database I will need to restore the data to will be 7.3.3 (don't ask me why we are still having 7.3 databases). Does anyone know/remember whether I can do it or whether this will not work because of difference in versions. thanks Gene __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
UNDO segments in 9.2
Hi all: I'm seeing something odd with the undo segments in Oracle 9.2. I have a 1G undo tablespace and am trying to import a 200M table. This is the only transaction going on at the time and it is failing because it is running out of space. When I check the undo segments I see TWO undo segments of a significant size. One that is used by my import and one more, which eats up about a half of the tablespace. I thought that Oracle would use up the space taken by this "other" segment, but it doesn't and my import fails. What am I not knowing about the undo segments and oracle9.2? thanks for any help Gene __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ora -1002
A-ha. This may be the reason. My database is 9.2 the other is 7.3. I bet that't the problem. thanks Gene --- Kevin Lange <[EMAIL PROTECTED]> wrote: > I was getting that when I tried to select accros a > DB Link when my Database > and my Client Code were at different levels. > > In our case, Database 8.0.5 and Client Code of 8.1.7 > caused a failure. > > Since we upgrade our database to 8.1.7 all is well. > > What are the versions of your client code and > database ?? > > Kevin > > -Original Message- > Sent: Tuesday, January 07, 2003 4:34 PM > To: Multiple recipients of list ORACLE-L > > > HI. > > Does anyone have any info regarding ora-1002 error > "fetch out of sequence"? All the info I've found on > the MEtalink is referring to a PL/SQL or a select > for > update. All I'm doing is select from table@dblink > and I'm getting ora-1002. I set autocommit off, but > this didn't help. Does anyone have any ideas? > > thanks > > > Gene > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up > now. > http://mailplus.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Gurelei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be 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: Kevin Lange > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (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!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ora -1002
HI. Does anyone have any info regarding ora-1002 error "fetch out of sequence"? All the info I've found on the MEtalink is referring to a PL/SQL or a select for update. All I'm doing is select from table@dblink and I'm getting ora-1002. I set autocommit off, but this didn't help. Does anyone have any ideas? thanks Gene __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ora 3237
Hi all: I'm trying to create an 9.2 database. I have created a rollback tablespace (not using UNDO TS yet) size 500M. Then I try to create a rollback segment via: create rollback segment r01_01 tablespace rbs01 storage (initial 1M next 1M minextents 16 maxextents 1017 optimal 16M) This gives me an error 3237 - initial extent can not be allocated. Reduce the number of free list groups. What is the problem here? Isn't the default number of free list groups 1? thanks Gene __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 and BMC backup
Hi all. Yeaterday we were tyring to rename a table. The rename command failed with ORA-00054: resource busy and acquire with NOWAIT specified Could that error somewhow be a result of a hot backup running at the same time via BMC. I know that the hot backup doesn't lock table for DML, but this is a DDL command. Is there any scenario that can explain this error by the BMC backup? Our version of Oracle is 8.1.7. Afaik no transactions after the table being renamed have been running at the time thanks Gene __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how do I calculate the Oracle's usage
Wouldn't this only give me the statistics for MY current session? I'm looking for the data on all the running sessions. Gene --- Jeremiah Wilton <[EMAIL PROTECTED]> wrote: > How about you collect the following value from each > instance on the > host, wait a few hours, then collect the numbers > again? You can then > subtract the earlier values from the later values, > and you'll have a > good idea which instances are using more CPU > relative to the others. > > SQL> select value from v$sysstat where name = 'CPU > used by this session'; > > -- > Jeremiah Wilton > http://www.speakeasy.net/~jwilton > > On Mon, 25 Nov 2002, Gurelei wrote: > > > I'm trying to figure out how much of the srever's > resources > > (CPU-wise) a database and the application running > against it is > > taking. There are several databases on that server > and I'm only > > interested in one so vmstat won't really help that > much. Besides > > just running ps -ef | grep INSTANCE is there any > other way for me to > > get a feeling of the load that puts on the CPU my > database? > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jeremiah Wilton > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
how do I calculate the Oracle's usage
Hi. I'm trying to figure out how much of the srever's resources (CPU-wise) a database and the application running against it is taking. There are several databases on that server and I'm only interested in one so vmstat won't really help that much. Besides just running ps -ef | grep INSTANCE is there any other way for me to get a feeling of the load that puts on the CPU my database? thanks Gene __ Do you Yahoo!? Yahoo! Mail Plus Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: low buffer hit ratio
Dennis: OEM calculates the BHR for all the queries. I presume it is being calculated using the same formula but only using the disk reads and buffer gets for the single query. I started with this query because it has the lowest bhr in the database. --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Gurelei - What do you mean by a low buffer hit ratio > for a SQL statement? > BHR is measured for the system, not for an > individual query. >Why have you singled this query out for > attention? Are there complaints > about its performance? Does it hit more blocks that > other queries on your > system? > Gene __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: low buffer hit ratio
--- Stephane Faroult <[EMAIL PROTECTED]> wrote: > > - How many rows does your query return ? If it's a > huge number I would > feel more comfortable with table scans than index > accesses in the plan. It returns 8 rows in about 2-2.5 seconds. > - You seem to feed three things into your query, > racf (any > relationship to the IBM product?), product (your > query doesn't say from > which table it comes) and the date (BTW implicit > conversions like here > always make me feel nervous). Which one is the most > selective ? Try to > have your query start with the corresponding table > (I am a big fan of > /*+ ORDERED */). I did something like that. I have prevented Oracle from using the index STATS_FK2 (on a manager field) by comcatinating ||'' to the manager field name. It forced Oracle to use the index on the date field (more selective) and reduced the time to 0.5 sec and I think increased the bhr to 95%. Thanks for your help Gene __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
low buffer hit ratio
Hi. I found a query with a buffer hit ratio about 60%: SELECT b.Name, a.Racf, c.Manager, < fileds from STATS table> FROM BT.Stats a, BT.Employees b, BT.Employees c WHERE a.Stats_Date Between '13-Nov-02' and '13-Nov-02' and a.Manager = c.Manager and c.racf = 'RLEWI01' and b.Racf = a.Racf and ( Product='RTI' OR Product = 'RIM' ... or Product = 'WEB') Group by b.Name, a.Racf, c.Manager Order by b.Name Here is the plan under oracle 7.3.3 rbo: 1.0 SELECT STATEMENT (, , ) 2.1 SORT GROUP BY (, , ) 3.1 NESTED LOOPS (, , ) 4.1 NESTED LOOPS (, , ) 5.1 TABLE ACCESS BY ROWID EMPLOYEES (, , ) 6.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , ) 5.2 TABLE ACCESS BY ROWID STATS (, , ) 6.1 INDEX RANGE SCAN STATS_FK2 (, , ) 4.2 TABLE ACCESS BY ROWID EMPLOYEES (, , ) 5.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , ) The largest table is STATS - 1.6 mil rows. STATS_FK2 is an index on MANAGER field. We have only 115 different managers so the index is not very selective. The stats_date field is more selective. Does it make sence for me to force Oracle to use that index in order to reduce the number of disk reads? thanks Gene __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
BMC's dtoarchmon goes to lunch
Hi. We are running Oracle 8172 on Dynix 452 and backing it up via BMC's obacktrac 3.0.0.6. Since we upgraded the OS and obakctrack dtoarchmon started dying. The process either disappears altogether or doesn't wake up to start an archive backup when it should. The dtoachmon -ping shows that everything is OK. Has anyone experienced this and has any insight to a possible solution? thanks Gene __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Orace setting on NT
Hi . This is a very easy question. I'm usually working with Oracle on UNIX, but today I need to connect to a remote instance from my NT desktop. Is there an equivalent to TNS_ADMIN varaiable in NT? ( I'm trying to force Oracle to use a specific tnsnames.ora file on my machine adn TNS_ADMIN variable is the way to do it on UNIX.) thanks for any help Gene __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos & More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Can I do this in Oracle 8i?
Hi. I want to update a table based on data in another table. Something like: update table1 a set f1 = (select f2 from table2 where table2.f3 = table1.f3 and table2.f4 = table1.f4); this seems to work, but generates a lot of redo logs. So I tried to add NOLOGGING. Alas, seems like NOLOGGING and alias don't tolerate each other. I haven't been able to run an Update with both alias and NOLOGGING. Is that something that Oracle restricts or did I not try hard enough? Any suggestions? TIA Gene __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-19587
Hi all: I'm getting the following errors when trying to do an arch backup against the database: ORA-19587: error occurred reading 512 bytes at block number 1 ORA-27091: skgfqio: unable to queue I/O IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 1 ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 1974 ORA-06512: at line 1 RMAN-06097: text of failing SQL statement: begin :rc := sys . dbms_backup_restore . validateArchivedLog ( recid => :recid , stamp => :stamp , fname => :fname , thread => :thread , sequence => :seq , resetlogs_change => :rstscn , first_change => :lowscn , blksize => :blksize ) ; end ; Looks like an archived log is missing. What should I do to fix this? Looked at Metalink, but didn't find anything hepful. Thanks for any help. Gene __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why does my insert creates so many logs?
All the indices are dropped and the PK is disabled. I agree that there are some changes to be done in the data disctinary tables, but the amount of archived logs - about 90M per minute looked too high to me --- Viral Desai <[EMAIL PROTECTED]> wrote: > > A couple of things to try --- > > 1. Drop the indexes and primary key instead of > disabling them. Insert the > data and recreate pk. > > 2. This could be due to changes in data dictionary, > when you insert large > number of rows in the table, new extents may be > allocated or high water mark > of the table would be modified. This information > need to be logged. I think > that the redo generated due to this should not be > very large though. > > Hope this helps. > Viral Desai > > > >From: Gurelei <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > >Subject: RE: Why does my insert creates so many > logs? > >Date: Thu, 19 Sep 2002 06:03:29 -0800 > > > >None. > >--- "Nicoll, Iain (Calanais)" > ><[EMAIL PROTECTED]> wrote: > > > Doesn't have any triggers does it? > > > > > > -Original Message- > > > Sent: Wednesday, September 18, 2002 8:39 PM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > Hi. > > > > > > A developer of mine is running a large insert as > > > select: > > > > > > insert /* parallel hint */ into table A > > > nologging > > > (select * from table b where ...); > > > > > > There are no indices on table A and a PK > disabled. > > > Still that insert generates a large amount of > logs. > > > What could be the reason for that? Any ideas? > Table > > > A > > > is not partitioned and has NOLOGGING attribute > on > > > the > > > dba_tables set to Yes. > > > > > > thanks > > > > > > Gene > > > > > > > __ > > > Do you Yahoo!? > > > Yahoo! News - Today's headlines > > > http://news.yahoo.com > > > -- > > > Please see the official ORACLE-L FAQ: > > > http://www.orafaq.com > > > -- > > > Author: Gurelei > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > > > http://www.fatcity.com > > > San Diego, California-- Mailing list and > web > > > hosting services > > > > >- > > > To REMOVE yourself from this mailing list, send > an > > > E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of > > > 'ListGuru') and in > > > the message BODY, include a line containing: > UNSUB > > > ORACLE-L > > > (or the name of mailing list you want to be > 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 > > > http://www.fatcity.com > > > San Diego, California-- Mailing list and > web > > > hosting services > > > > >- > > > To REMOVE yourself from this mailing list, send > an > > > E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of > > > 'ListGuru') and in > > > the message BODY, include a line containing: > UNSUB > > > ORACLE-L > > > (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!? > >New DSL Internet Access from SBC & Yahoo! > >http://sbc.yahoo.com > >-- > >Please see the official ORACLE-L FAQ: > http://www.orafaq.com > >-- > >Author: Gurelei > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > >San Diego, California-- Mailing list and > web hosting services > >---
RE: Why does my insert creates so many logs?
None. --- "Nicoll, Iain (Calanais)" <[EMAIL PROTECTED]> wrote: > Doesn't have any triggers does it? > > -Original Message- > Sent: Wednesday, September 18, 2002 8:39 PM > To: Multiple recipients of list ORACLE-L > > > Hi. > > A developer of mine is running a large insert as > select: > > insert /* parallel hint */ into table A > nologging > (select * from table b where ...); > > There are no indices on table A and a PK disabled. > Still that insert generates a large amount of logs. > What could be the reason for that? Any ideas? Table > A > is not partitioned and has NOLOGGING attribute on > the > dba_tables set to Yes. > > thanks > > Gene > > __ > Do you Yahoo!? > Yahoo! News - Today's headlines > http://news.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Gurelei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be 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 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (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!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Why does my insert creates so many logs?
No. It only has 12 extents --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > > Does you table have many small extents ? > > On Wednesday 18 September 2002 21:39, you wrote: > > Hi. > > > > A developer of mine is running a large insert as > > select: > > > > insert /* parallel hint */ into table A > > nologging > > (select * from table b where ...); > > > > There are no indices on table A and a PK disabled. > > Still that insert generates a large amount of > logs. > > What could be the reason for that? Any ideas? > Table A > > is not partitioned and has NOLOGGING attribute on > the > > dba_tables set to Yes. > > > > thanks > > > > Gene > > > > __ > > Do you Yahoo!? > > Yahoo! News - Today's headlines > > http://news.yahoo.com > > -- > > Anjo Kolk > http://www.oraperf.com > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Anjo Kolk > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Why does my insert creates so many logs?
Hi. A developer of mine is running a large insert as select: insert /* parallel hint */ into table A nologging (select * from table b where ...); There are no indices on table A and a PK disabled. Still that insert generates a large amount of logs. What could be the reason for that? Any ideas? Table A is not partitioned and has NOLOGGING attribute on the dba_tables set to Yes. thanks Gene __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
BMC Backtrack until_cancel
Hi all: I have been trying to use the until_cancel option in BMC Backtrack 3.3.00 to do "until cancel" recovery. But in the maual the description of this option is "applies archive logs until there are no more". This is not exactly "until cancel" IMO. Is there any way to do a real until cancel via Backtrack? thank you Gene __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
BMC Obacktrack
Hi all, I'm testing BMC Backtrack v 3.30 on Dynix 4.5.2 and experiencing some strange behavior of the tool. I have deleted a datafile and use the tool to restore it from the backup. When I let the tool to do a restore, everything runs great and fast. When however I have the tool generate a script and run that script manually, it attempts to restore ALL the datafiles (even though it was generated to only restore one). Has anyone experienced this before and is there something I can do about it? thanks Gene __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ora 14130 when doing an alter table exchange partition
Hi. I'm getting an ora-14130 error when trying to exchange a partition with a table. Both table and partition have the same set of the indices, constraints. All indices/constraints are enabled and have the same fields. The only way I can fix it is to rebuild the indices. Does anyone have any idea what may be wrong here? thanks Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Reasons to upgrade from Oracle 7.3.4 to 8i
Did you try telling them that 7.3.4 is not supported by Oracle anymore and they shouldn't be running an important database on an unsupported version? Gene --- Andrey Bronfin <[EMAIL PROTECTED]> wrote: > Dear gurus ! > I know it's a "beaten to death horse", but... > I want to convince a customer to upgrade their OPS > 7.3.4 DB to an Oracle > 8.1.7 OPS or even (in a best case for me) to > Oracle9i RAC (don't even know > whether or not one exists and is supported for > Digital UNIX on EMC Symmetrix > storage). > I am sure this has been discussed 1000s of times > here , but ... i need the > arguments to convince them. > I can't tell them "upgrade because 8.1.7 delivers > better performance , > availability etc...and 7.3.4 is outdated" , they > simply won't buy it. > They run that OPS 7.3.4 DB for several years now and > are quite happy with > it. > I need to explain in details why they should invest > in the upgrade. > Now , the only real reason i can think of is > partitioning . They will > definetly benefit from it. > I need more reasons. > Thanks a lot in advance. > > > DBAndrey > > * 03-9254520 > * 058-548133 > * mailto:[EMAIL PROTECTED] > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Andrey Bronfin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what is wrong with this idea ... - thanks
This is to thank all those who replied to my post - Thomas, Dennis, Jack, Melissa, Manavendra, Jerome, Steven , Stephen and Michael (sorry if I missed someone). I have been able to create the aggregate which IMO will speed up the reports so hopefully the developers will go along. thank you all for your responses. Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: insert nologging parallel/noparallel and archiving
Rick, These are the transactions: This one does not create any logs (nothing was written into arch directory); insert /*+ parallel (egurev1.offr,16) */ into egurev1.offr nologging (select a.PRDCT_ID , a.OFFR_RSPNS_TYP_CDE , a.PRTY_TYP_NBR , a.PREFR_IND , a.OFFR_CNT , a.PROC_MTH_VAL, a.TRSFR_RSPNS_CDE , a.EXPCT_GRID_SCR_VAL, b.OFFR_CALL_KEY from ccsrep.offr a, egurev1.offr_call b where a.acct_nbr = b.acct_nbr and a.dt_nbr = b.dt_nbr and a.usr_id = b.usr_id ); This one created a few logs in the arch directory: insert /*+ parallel (egurev1.offr_Call,16) */ into egurev1.offr_Call nologging (sele ct /*+ parallel (b,16) */ ACCT_NBR , USR_ID , DT_NBR , OPERS_CNTR_CDE, DEPT_ID , CRD_TYP_NBR , OPT_CDE, IVR_CDE , IVR_FL_IND , IVR_PASS_IND , CALL_CNT , PROC_MTH_VAL, INBD_OUTBD_CDE , offr_call_key.nextval from ccsrep.offr_call b, dual) / When executing no indices were existing on the target tables. Gene --- [EMAIL PROTECTED] wrote: > > As long as you are doing INSERT INTO ... using CTAS > it can make use of > NOLOGGING according to Oracle. I do not know > why the single table does not use it. Perhaps > posting relevant portion of > insert stmt may provide some help __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what is wrong with this idea ...
Dennis, Yes, at this moment I'm debating whether to go with one combined string or several separate fields. I'm not thrilled by any of the options, but the former really doesn't look good to me. What I'm trying to avoid is every transaction having to run a query against a large (tens of millions rows) child table in order to count the statuses. Another - totally different - option would be to create an aggregate (I think someone did mention that), but I haven't had much sucess designing an appropriate aggregate yet Gene --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: Is your tradeoff between > four separate columns > or a single column with concatenated values? __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what is wrong with this idea ...
Tom, The child table is not going to be updated ad-hoc. As I said this is a data warehouse and there is no ad-hoc updates. Only one load during the day and during this load that field is going to be populated. The thing I don't like about the procedure is that it will be doing a query against a large table every time the developer runs a query using that procedure. By loading that field(d) during the load we only do it once. --- "Mercadante, Thomas F" <[EMAIL PROTECTED]> wrote: > Gene, > > You ask what's wrong with this? The main challenge > I see is: how are you > going to maintain this field? If a status gets > updated in the child table, > are you supposed to update the parent table? And to > do that, you will need > to requery the child table to get all the status's > of all children records > whenever one status changes. > > Pretty silly really, > > Why not do this - create a function that, given the > PK of the parent table, > queries all the child records and concatenates all > the status values into > one string. Then either create a view for the > duhveloper to use to return > what he wants to return at query time. > > Something like this: > > Create or replace function > get_child_status(in_pk_column) return varchar2 is > > cursor c1 is > select status_code > from child_table > where pk_column=in_pk_column; > ret_string varchar2(10); > begin > for c1_rec in c1 loop > ret_string := ret_string || c1_rec.status_code; > end loop; > return ret_string > end; > > The duhveloper can then: > > select parent_pk_column, > get_child_status(parent_pk_column) >from parent_table; > > Hope this helps. > > Tom Mercadante > Oracle Certified Professional > > > -Original Message- > From: Gurelei [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, August 14, 2002 9:19 AM > To: Multiple recipients of list ORACLE-L > Subject: what is wrong with this idea ... > > > Hi. We have a table in our data warehouse which > keeps > info about calls made. This table has a child table > with some detailed information about parts of the > call. There may be any number of "parts" within a > call > (1 to many) and every part has a status. > > MY developer wants to add a string field to the > parent > table which will concatinate all the statuses for > all the parts within this call. For example if > a call has 4 parts and their statuses are > "A","B","A" > and "F", the value of that field will be "ABFA". > Then > the developer will be able to query smalle parent > table instead of a large child table in order to see > how many calls had at least one part with status "A" > or statuses "A" and "F" etc by using a INSTR (or > SUBSTR) command. > > Would it be better (from performance/CPU standpoint) > to add several separate fields: STATUS_A_CNT, > STATUS_B_CNT (the list of status codes is fairly > static) instead? There is something about this > string > that rubs me the wrong way, but I can't put my > finger > on it. > > Any thoughts? > > thank you > > Gene > > __ > Do You Yahoo!? > HotJobs - Search Thousands of New Jobs > http://www.hotjobs.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Gurelei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (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!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: insert nologging parallel/noparallel and archiving
Jack, I have another twist on this situation. Again I'm loading the tables in nologging mode with all the indices dropped. Both transactions are insert into table nologging (select *). The only difference is that one select is a select from a single table while the other - is a select from two joined tables. The first insert (from a single) table produce archives, while the other (from two tables) - doesn't. Is there en explanation for that? thanks Gene --- Jack Silvey <[EMAIL PROTECTED]> wrote: > sorry, meant insert as select, not insert as append > --- Jack Silvey <[EMAIL PROTECTED]> wrote: > > Gene, > > > > This sounds right. > > > > Standard insert as append does freelist block > > checking > > and generates redo. Parallel DML generates > minimial > > redo, just for the new temp segments being > created, > > not for the datablock updates. This is one reason > it > > is so fast. > > > > Index changes are always logged, even in PDML and > > append modes. > > __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
what is wrong with this idea ...
Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of "parts" within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are "A","B","A" and "F", the value of that field will be "ABFA". Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status "A" or statuses "A" and "F" etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Good basic hardware cource
Hi. Can someone recommend a good basic hardware course for a DBA. I'm looking for something dealing with both the general concepts (raw devices vs filesystems, logical volumes, partitioning etc) and somewhat hands-on application preferably on IBM servers (that's what ewe are currently on). any ideas? thanks Gene __ 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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Compatibility martix on Metalink - Thanks
Thanks you, Dennis, Stephen and Chandesh. I found it. Gene --- Gurelei <[EMAIL PROTECTED]> wrote: > Hi, > > I remember being able to find a compatibility matrix > on Oracle MEtalink with the info of which Oracle > version is running under which OS. LAtely, I haven't > been able to locate it. Can someone point me to > correct page? > > thanks > > __ > 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: Gurelei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (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!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Compatibility martix on Metalink
Hi, I remember being able to find a compatibility matrix on Oracle MEtalink with the info of which Oracle version is running under which OS. LAtely, I haven't been able to locate it. Can someone point me to correct page? thanks __ 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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Delete performance
Hi all: Someone at my shop wants to delete about 20% of roes in a table (20 rows out of a million). He wants to set a commit frequency (like every 1000 records or so) to keep the rbs under control. I am not aware of any easy way to do it other then writing a procedure, but I may be missing something here. Is there any simple way to accomplish this? Also I have suggested instead of deleting 20% of the rows, create a new table as a select and insert the rest of the rows into it (then rebuild the indices and rename). This can be done in nologging mode, without redo logs and rbs segments. Is this a good idea to try? thanks for any info Gene __ 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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How do I check whether a rollback activity is going on
Hi. Here is my situation. I have killed an ORacle transaction. Oracle came back with "session marked for kill" and the status of the session is now "Killed". I presume that the reason is that Oracle needs to roll back the changes made. Can I confirm whether this is indeed the case via some system tables? thakns gene __ 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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Fwd: Please Advise--Your Reaction to Ellison Comment]
t; > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Joe Testa > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be 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: Thomas Day > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: insert nologging parallel/noparallel and archiving - thanks
Just wanted to thank Jack, Connor and Jared for their help!! Gene --- Jack Silvey <[EMAIL PROTECTED]> wrote: > sorry, meant insert as select, not insert as append > --- Jack Silvey <[EMAIL PROTECTED]> wrote: > > Gene, > > > > This sounds right. __ 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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
insert nologging parallel/noparallel and archiving
Hi all. I've been trying to copy a 20mil rows table between two databases via insert as select statement. I have dropped all the indices and ran in nologging mode with hints forcing parallel execution of both ISERT and SELECT. While checking the archive directory I have noticed that the archives have been created every 2 minutes. I have cancelled the load and noticed that I forgot to alter system enable parallel dml so in effect the load was going in sequential mode. Then I reran my script having added the alter system statement and there we no archive created while it ran. So it looks like even without the indices a sequential insert as select nologging still creates the archives wihle the parallel - doesn't. Does it sound right? If so, why. I thought the nollogging and with no indices shouldn't produce any log info regardless of the mode. TIA Gene __ 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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
suggestion for a class/book
Hi. I'm tyring to find a good class dealing with hardware stuff from an Oracle DBA perspective (adapters, switches, LVs, PP, etc etc) on a beginners level. Can anyone suggest something along these lines? thanks __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
a simple question about FKs
Hi. I was trying to truncate a table, which is a parent to two other tables (its PK is referred to by two FKs from two other tables). These "other" tables are empty. When I tried to truncate the parent table, however, I got an error message ORA-02266: unique/primary keys in table referenced by enabled foreign keys. I thought that as long as the child tables are empty, the RI shouldn't matter, but I had to disable the FKs before being able to truncate the table. Is that the way Oracle checks truncates (no enabled RIs even if the tables are empty) or did I do something incorrectly? thanks Gene __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Database restore question
Hi. I have posted a question yesterday regarding a partial database restore.This is sort of a fallout of that restore. Because I was only interested in restoring two tables which are very stable I decided not to restore a rollback tablespace. But when my restore was completed and the databse opened I was unable to see the tables bacase Oracle was trying to read data from the rollback TS and that TS was not available. I'm 100% sure that the tables were not being changed during the backup. Why would Oracle need the rollback segments in this case. We are running 8.1.7.0 thanks for any input Gene __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
partial restore of a database
Hi all: I'm doing a partial restore of a database in order to recover two lost tables. I figured I would only need to restore the tablespaces where the tables are stored, SYSTEM and RBS tablespaces. I will also need to restore to a specific point in time (last Friday). I'm a little less clear on what I will have to do after the restore of the datafiles, archived logs and controlfile is done. I will need to recover the datafiles, drop all other datafiles that I don't need and open the database in resetlogs mode. Is that a good plan or am I missing something? thanks Gene __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
is this a valid scenario?
Hi. We are running ORacle 8.1.7.2 OPS on 4 SP/2 nodes. This morning one of the nodes crashed. some users experienced their reports which were executed on other nodes, failed and then completed successfully when restarted. My explanation for that was that when one node crashed, the parallel slaves running on that node also died and that resulted in reports failing. Then when the reports we reexecuted immediately, they were not using the node that crashed and completed successfully. Is this a valid explanation or am I missing something? thank you Gene __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 question
Peter, Charile: Sore area size is 5 times more in prod DB, shared pool is larger in dev, db_block_buffers in dev is twice the size in prod. I guess this answers my question. thanks Gene --- Peter Gram <[EMAIL PROTECTED]> wrote: > Gurelei > > Are the parameter's the same ? > > sort* > hash* > *pool* > db* > > Gurelei wrote: > > >Hi. > > > >I have executed an explain plan on a dev and prod > >databases. Both databases have the same data, use > the > >same version of ORacle (8.1.7.0) and the same OS > (AIX > >4.3.3). All the tables are analyzed. The plans > however > >are somewhat different (below). What could explan > the > >differences? For example, index usage (one plan > uses > >an index to get to all the data, another - access > the > >index and then the table). All the indices are the > >same on both databases. when I tried to force > ORacle > >to use the indices with hints, the cost grew from > 322 > >to 566. MY concernt is that I may not be able to > tune > >a query if I can't replicate the explain plan > exactly > >in dev as it is in production. > > > >Thanks for any input > > > >Gene > > > >0-0-3211.321 SELECT STATEMENTSQL1 Cost = 321 > >1-0-1 2.1 SORT GROUP BY > >2-1-1 3.1 NESTED LOOPS > >3-2-1 4.1 HASH JOIN OUTER > >4-3-15.1 HASH JOIN OUTER > >5-4-1 6.1 HASH JOIN > >6-5-1 7.1 TABLE ACCESS FULL TELESLS_EMPL > >7-5-2 7.2 INDEX RANGE SCAN TS_EMP_HST_ALT1 > UNIQUE > >8-4-2 6.2 INDEX FAST FULL SCAN PERF_STATS_ALT1 > >UNIQUE > >9-3-25.2 INDEX FAST FULL SCAN PRDCT_STAT_ALT1 > >UNIQUE > >10-2-2 4.2 INDEX UNIQUE SCAN ORG_UNT_HST_PK > UNIQUE > > > >vs > > > >0-0-3231.323 SELECT STATEMENTSQL1 Cost = 323 > >1-0-1 2.1 SORT GROUP BY > >2-1-1 3.1 NESTED LOOPS OUTER > >3-2-1 4.1 NESTED LOOPS OUTER > >4-3-15.1 NESTED LOOPS > >5-4-1 6.1 HASH JOIN > >6-5-1 7.1 TABLE ACCESS FULL TELESLS_EMPL > >7-5-2 7.2 INDEX RANGE SCAN TS_EMP_HST_ALT1 > UNIQUE > >8-4-2 6.2 INDEX UNIQUE SCAN ORG_UNT_HST_PK > UNIQUE > >9-3-25.2 TABLE ACCESS BY INDEX ROWID PERF_STATS > >10-9-1 6.1 INDEX RANGE SCAN PERF_STATS_FK2_X > >NON-UNIQUE > >11-2-2 4.2 TABLE ACCESS BY INDEX ROWID > PRDCT_STATS > >12-11-15.1 INDEX RANGE SCAN PRDCT_STAT_FK1_X > >NON-UNIQUE > > > > > >__ > >Do You Yahoo!? > >Yahoo! - Official partner of 2002 FIFA World Cup > >http://fifaworldcup.yahoo.com > > > > > > -- > > /regards > > Peter Gram > > Mobil : +45 2527 7107 > Fax : +45 4466 8856 > > Miracle A/S > Kratvej 2 > 2760 Måløv > http://miracleas.dk > > /* > The process of preparing programs for a digital > computer is especially > attractive, not only because it can be economically > and scientifically > rewarding, but also because it can be an aesthetic > experience much like > composing poetry or music > > Donald Knuth > */ > > > > ATTACHMENT part 2 application/x-pkcs7-signature name=smime.p7s __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
explain plan question
Hi. I have executed an explain plan on a dev and prod databases. Both databases have the same data, use the same version of ORacle (8.1.7.0) and the same OS (AIX 4.3.3). All the tables are analyzed. The plans however are somewhat different (below). What could explan the differences? For example, index usage (one plan uses an index to get to all the data, another - access the index and then the table). All the indices are the same on both databases. when I tried to force ORacle to use the indices with hints, the cost grew from 322 to 566. MY concernt is that I may not be able to tune a query if I can't replicate the explain plan exactly in dev as it is in production. Thanks for any input Gene 0-0-3211.321 SELECT STATEMENTSQL1 Cost = 321 1-0-1 2.1 SORT GROUP BY 2-1-1 3.1 NESTED LOOPS 3-2-1 4.1 HASH JOIN OUTER 4-3-15.1 HASH JOIN OUTER 5-4-1 6.1 HASH JOIN 6-5-1 7.1 TABLE ACCESS FULL TELESLS_EMPL 7-5-2 7.2 INDEX RANGE SCAN TS_EMP_HST_ALT1 UNIQUE 8-4-2 6.2 INDEX FAST FULL SCAN PERF_STATS_ALT1 UNIQUE 9-3-25.2 INDEX FAST FULL SCAN PRDCT_STAT_ALT1 UNIQUE 10-2-2 4.2 INDEX UNIQUE SCAN ORG_UNT_HST_PK UNIQUE vs 0-0-3231.323 SELECT STATEMENTSQL1 Cost = 323 1-0-1 2.1 SORT GROUP BY 2-1-1 3.1 NESTED LOOPS OUTER 3-2-1 4.1 NESTED LOOPS OUTER 4-3-15.1 NESTED LOOPS 5-4-1 6.1 HASH JOIN 6-5-1 7.1 TABLE ACCESS FULL TELESLS_EMPL 7-5-2 7.2 INDEX RANGE SCAN TS_EMP_HST_ALT1 UNIQUE 8-4-2 6.2 INDEX UNIQUE SCAN ORG_UNT_HST_PK UNIQUE 9-3-25.2 TABLE ACCESS BY INDEX ROWID PERF_STATS 10-9-1 6.1 INDEX RANGE SCAN PERF_STATS_FK2_X NON-UNIQUE 11-2-2 4.2 TABLE ACCESS BY INDEX ROWID PRDCT_STATS 12-11-15.1 INDEX RANGE SCAN PRDCT_STAT_FK1_X NON-UNIQUE __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
alter table move resets NEXT?
Hi all: We are running oracle 8.1.7.2 in parallel mode. A DBA on my team noticed that when he moves a table into a new tablespace via: alter table XXX move tablespace YYY; the table XXX's NEXT extent is being changed to its original value (the one table was created with). Even when he tried alter table XXX move tablespace YYY storage (NEXT ZZZ) it still didn't help and the next extent was not set to ZZZ. I know that truncate command does this - resets NEXT to its original value, but does alter table move resets NEXT too? Does anyone know if this is a bug? a feature? thanks Gene __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OS patches required by Oracle
Hi. How do I find the list of OS patches that are required by Oracle 8.1.7 on DYNIX. I have looked at both melink and ibm.com sites, but couldn't find the info there. Am I looking at wrong places? thanks Gene __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: extents allocation in parallel load
Tim, Thanks for all your help. I will check the LAST_DDL_TIME field (although I didn't know what INITIAL parameter can be modified) and will let you know if something comes out of it. Gene --- Tim Gorman <[EMAIL PROTECTED]> wrote: > Well! I'm out of ideas. The only other thing I can > think of is a recent > ALTER TABLE which changed the INITIAL on the table > since the load, but > that's grasping (gasping?). Still, could you look > at LAST_DDL_TIME on > DBA_OBJECTS for the table, just to grasp that last > straw? > > - Original Message - > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: Wednesday, May 15, 2002 8:13 AM > > > > Tim, > > > > The tablespace is dictionary managed. > > --- Tim Gorman <[EMAIL PROTECTED]> wrote: > > I was hoping to see "*" column values from > > DBA_TABLESPACES, not just > > the > > "default storage" column values. This would show > > whether the > > tablespace in > > question was locally-managed (and SYSTEM or > UNIFORM, > > if so) as well... > > > > > > ______ > > Do You Yahoo!? > > LAUNCH - Your Yahoo! Music Experience > > http://launch.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > -- > > Author: Gurelei > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 > FAX: (858) 538-5051 > > San Diego, California-- Public Internet > access / Mailing Lists > > > > > To REMOVE yourself from this mailing list, send an > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > ORACLE-L > > (or the name of mailing list you want to be > 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: Tim Gorman > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (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!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: extents allocation in parallel load
Tim, The tablespace is dictionary managed. --- Tim Gorman <[EMAIL PROTECTED]> wrote: I was hoping to see "*" column values from DBA_TABLESPACES, not just the "default storage" column values. This would show whether the tablespace in question was locally-managed (and SYSTEM or UNIFORM, if so) as well... __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: extents allocation in parallel load
Tim, SQL> select TABLESPACE_NAME, INITIAL_EXTENT/1024, NEXT_EXTENT/1024, PCT_INCREASE 2 from dba_tablespaces 3 where tablespace_name like 'TEMP%' 4 or tablespace_name = 'TREPD01'; TABLESPACE_NAMEINITIAL_EXTENT/1024 NEXT_EXTENT/1024 PCT_INCREASE -- --- TREPD01 40 400 TEMP011024 10240 TREPD01 is where the tables are located, TTEMP01 is the temp tablespace. Gene --- Tim Gorman <[EMAIL PROTECTED]> wrote: > What does "SELECT * FROM DBA_TABLESPACES" show for > the tablespace involved? > > - Original Message - > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: Tuesday, May 14, 2002 12:53 PM > > > > Tim, > > > > Thanks for the explanation. The table I'm looking > at > > has an initial size 32K while the new extents are > all > > 16K. Why would this happen? > > > > thanks > > > > Gene > > --- Tim Gorman <[EMAIL PROTECTED]> wrote: > > > SQL*Loader in direct-parallel mode (i.e. > > > DIRECT=TRUE, PARALLEL=TRUE) first > > > loads into a TEMPORARY segment. After the load > > > completes, then the > > > TEMPORARY segment is "merged" with the table > > > segment. All of the "direct" > > > (a.k.a. "append") operations work this way when > > > executed in parallel (i.e. > > > INSERT /*+ APPEND PARALLEL */, CREATE INDEX ... > > > PARALLEL, CREATE TABLE ... > > > PARALLEL ... AS SELECT, etc)... > > > > > > Only SQL*Loader's direct-nonparallel mode (i.e. > > > DIRECT=TRUE, PARALLEL=FALSE) > > > loads into space on the table segment above the > > > current "high-water mark". > > > That expected scenario is probably the reason > why > > > you expect all new extents > > > to be sized according to NEXT... > > > > > > So, the newly added extents were probably > "INITIAL" > > > extents for the original > > > TEMPORARY segments, before they were merged into > the > > > table segment, perhaps? > > > > > > - Original Message - > > > To: "Multiple recipients of list ORACLE-L" > > > <[EMAIL PROTECTED]> > > > Sent: Tuesday, May 14, 2002 10:33 AM > > > > > > > > > > Hi, > > > > > > > > I'm confused by the way some of my extents are > > > > allocated in Oracle. I've talked to Oracle > reps, I > > > > thing I've posted here before and jsut when I > > > thought > > > > I got an understanring - ooops, everything is > > > gone. So > > > > I'll give it another try. I have a table with > > > initial > > > > set to 32K and next set to 1M. This table is > being > > > > loaded on a daily basis via Ab initio. The > load is > > > > done by SQL*loader in direct mode in parallel > (I > > > > believe it is 8 ways). Now all the segments > that > > > have > > > > been created for this table (besides the 1st > one) > > > are > > > > 16K. Where does this size come from? I thought > it > > > > should use the NEXT parameter for the new > extents, > > > but > > > > it doesn't. Can someone give me some pointers? > > > > > > > > thanks > > > > > > > > Gene > > > > > > > > > __ > > > > Do You Yahoo!? > > > > LAUNCH - Your Yahoo! Music Experience > > > > http://launch.yahoo.com > > > > -- > > > > Please see the official ORACLE-L FAQ: > > > http://www.orafaq.com > > > > -- > > > > Author: Gurelei > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Services-- (858) 538-5051 > > > > FAX: (858) 538-5051 > > > > San Diego, California-- Public > Internet > > > access / Mailing Lists > > > > > > > > > > > > > > To REMOVE yourself from this mailing list, > send an > > > E-Mail message > > > > to: [EMAI
Re: extents allocation in parallel load
Tim, Thanks for the explanation. The table I'm looking at has an initial size 32K while the new extents are all 16K. Why would this happen? thanks Gene --- Tim Gorman <[EMAIL PROTECTED]> wrote: > SQL*Loader in direct-parallel mode (i.e. > DIRECT=TRUE, PARALLEL=TRUE) first > loads into a TEMPORARY segment. After the load > completes, then the > TEMPORARY segment is "merged" with the table > segment. All of the "direct" > (a.k.a. "append") operations work this way when > executed in parallel (i.e. > INSERT /*+ APPEND PARALLEL */, CREATE INDEX ... > PARALLEL, CREATE TABLE ... > PARALLEL ... AS SELECT, etc)... > > Only SQL*Loader's direct-nonparallel mode (i.e. > DIRECT=TRUE, PARALLEL=FALSE) > loads into space on the table segment above the > current "high-water mark". > That expected scenario is probably the reason why > you expect all new extents > to be sized according to NEXT... > > So, the newly added extents were probably "INITIAL" > extents for the original > TEMPORARY segments, before they were merged into the > table segment, perhaps? > > - Original Message - > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: Tuesday, May 14, 2002 10:33 AM > > > > Hi, > > > > I'm confused by the way some of my extents are > > allocated in Oracle. I've talked to Oracle reps, I > > thing I've posted here before and jsut when I > thought > > I got an understanring - ooops, everything is > gone. So > > I'll give it another try. I have a table with > initial > > set to 32K and next set to 1M. This table is being > > loaded on a daily basis via Ab initio. The load is > > done by SQL*loader in direct mode in parallel (I > > believe it is 8 ways). Now all the segments that > have > > been created for this table (besides the 1st one) > are > > 16K. Where does this size come from? I thought it > > should use the NEXT parameter for the new extents, > but > > it doesn't. Can someone give me some pointers? > > > > thanks > > > > Gene > > > > __ > > Do You Yahoo!? > > LAUNCH - Your Yahoo! Music Experience > > http://launch.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > -- > > Author: Gurelei > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 > FAX: (858) 538-5051 > > San Diego, California-- Public Internet > access / Mailing Lists > > > > > To REMOVE yourself from this mailing list, send an > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > ORACLE-L > > (or the name of mailing list you want to be > 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: Tim Gorman > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (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!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
extents allocation in parallel load
Hi, I'm confused by the way some of my extents are allocated in Oracle. I've talked to Oracle reps, I thing I've posted here before and jsut when I thought I got an understanring - ooops, everything is gone. So I'll give it another try. I have a table with initial set to 32K and next set to 1M. This table is being loaded on a daily basis via Ab initio. The load is done by SQL*loader in direct mode in parallel (I believe it is 8 ways). Now all the segments that have been created for this table (besides the 1st one) are 16K. Where does this size come from? I thought it should use the NEXT parameter for the new extents, but it doesn't. Can someone give me some pointers? thanks Gene __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: data warehousing desing - to denormalize or not to denormaliz
--- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > The key point is your users. A typical normalized > design means a lot of > tables. These tables must be joined in specific > manners. Non-I.S. users find > this intimidating. So they don't use it. So you've > spent a lot of time > creating a DW only to have nobody use it. > How many tables > did your 3nf schema > produce? Our schema has 4 fact tables, 6 aggregates build on them and more than 20 dimension tables. And we have exctly the situation of users asking to denormalize them to simplify the report creation. > Or go ahead, build a normalized one, then > study and > build the second > version incorporating the hard-won lessons others > have learned. What I'm trying to do is to pick the best "starting point" design. Having a totally normalized schema would be one option. If the performance or report creation (in terms of simplisity) prove unsatisfactory we can change the design and denormalize to address the known issues. If however I'm able to identify the tables that can be denormalized without causing damage I'd rather do it now. __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: data warehousing desing - to denormalize or not to denormalize - that
Jared, Thanks for the answer. I must admit my ignorance in terminology as for me data warehouse and data mart a pretty much the same thing except for size. I understand that data mart is smaller. The database I'm referring to could probably be described as data mart as it is going to be rather small - a gig or so maybe. --- [EMAIL PROTECTED] wrote: > One philosophy of DW states that you build a DW that > is > fairly normalized, much like an OLTP database, > albeit one > with a temporal component and complete logging of > transactions > within the data. > > This is then used as a warehouse. The data from the > DW is used > to assemble data marts. These data marts are queried > by users. > > They never look at the data warehouse. > > So to answer your question: It depends. > > * On how much time you have > * do you want the ability to create new data marts > without adding to > the ETL system ? ( it should already be getting > everything you need ) > > Since you already have something that looks like a > DW, why not > use that to build data marts that employ star > schemas and bitmap > indexes? They are easier to query, and faster. > > I believe both Kimball and Inmon subscribe to this > philosphy. > > Jared > > > > > > > Gurelei <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 05/10/2002 11:43 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:data warehousing desing - to > denormalize or not to denormalize - that is > the question > > > Hi. > > We are designing a small database using a data > warehousing desing. We have created a 3rd normal > form > and are now debating whether and how to denormalize > it. I see the pluses of denormalization - easier > queries creation and tuning. What are the > disadvantages that we should be aware of? Wasted > space > is not an issue because the tables a pretty small. > What else should we consider as a potential issue? > > thank you > > ______ > Do You Yahoo!? > Yahoo! Shopping - Mother's Day is May 12th! > http://shopping.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Gurelei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be 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). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
data warehousing desing - to denormalize or not to denormalize - that is the question
Hi. We are designing a small database using a data warehousing desing. We have created a 3rd normal form and are now debating whether and how to denormalize it. I see the pluses of denormalization - easier queries creation and tuning. What are the disadvantages that we should be aware of? Wasted space is not an issue because the tables a pretty small. What else should we consider as a potential issue? thank you __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
unknown value
Hi all: This is probably a simple question, but then again may be not. We are developing an application and will be loading data into bunch of tables. We will have a situation where some of the values will not be provided for various reasons. We don't want to make them NULL because some of these fields are part of a PK so we decided to come up with some values to mean "unknown". Is there any reason NOT to use a question mark as such a value for char strings? To me it sounds like a character is a characted and question mark shouldn't be treated differently then, say, an "X". Is there a situation when having "?" in a character field instead of a letter can create problems? thank you Gene __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
groupping and percentages
Hi all: There have been a few threads here dealing with calculating percentages e.g: Group Number Percent 1 10 10% 2 10 10% 4 70 70% 9 10 10% I'm also sure I saw articles on how to create a report like this via Oracle, but I couldn't find one when I need it. If someone has a query or a link to such an artice, could you please froward it to me. thanks Gene __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
