Re: direct distributed insert causes massive sorting on target, why?
Jonathan, Let me setup a test in a test schema just to make sure I got it right and I will get the details. I was in a hurry to get the data over the first time and the sorts were killing us. (We swung a domain to a new server this weekend and it has been one of those catnap every two hours and get paged experiences and I haven't even had time to drink beer and watch the Cowboys get beat. Well, maybe some time.) Here are some overview stats: 8.1.7.4, sun solaris, range partitioned tables on both sides. Make sure your target side is range partitioned and alter the default DOP to 1 on the target and see if that helps. Jack I've had a little play around with this on 8.1.7.4.1, and I can't get the INSERT to run in parallel at all - all I get is a parallel select on the remote database. Are there any other details you had to set up to make this work ? What does the full execution plan look like - run it through Oracle's utlxplp.sql or something similar to get all the details - autotrace won't be good enough. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 26 December 2002 22:07 target, why? alter session enable parallel dml; insert /*+ append parallel(a,12) */ into tablea a select /*+ full(b) parallel(b,12) */ * from tableb@dblnk b; However, unexpectedly, this causes two sets of parallel processes to be spawned on the target. One of these sets goes to work immediately, -- 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). Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Row Migration
Larry is the SQL guru. Every time he drops a pearl of wisdom like we really want to make sure all the not-null columns are identified because it can affect the ability of the optimizer to transform a corelated IN subquery into an equi-join, and allow us to drop this index now that we won't need it for a correlated lookup, which will force all of our queries to rip through this table using hash joins I wonder how the heck he KNOWS all that stuff. I THINK it is just pure brains, although sometimes I suspect he has a deal with the devil ... or Larry Ellison. :) Jack don't feel too sheepish, I didn't know it either. Larry is the SQL guru and I bow to his knowledge. and had already saved off this email as this sort of update is something we do often and I ALWAYS have problems figuring out the correct SQL :) rachel --- Jared Still [EMAIL PROTECTED] wrote: Geez, I didn't know you could do that. Sheepishly, Jared On Friday 27 December 2002 03:38, Larry Elkins wrote: Someone asked in a back channel email if parallelism is used. The select portion of the update statement uses parallelism (though the updates themselves get serialized) through the use of an in-line join update (to avoid the second sub-query commonly used to constrain the rows being updated): Update (Select /*+ parallel hints */ From a,b Where a.key = b.key) Set a.col1 = b.col1, a.col2 = b.col2 . Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Larry Elkins Sent: Thursday, December 26, 2002 6:09 PM To: Multiple recipients of list ORACLE-L Subject: Row Migration Listers, 8.1.7.4 64 Bit Solaris Does row migration utilize DB File Sequential Reads on the table? Off the top of my head I would expect so, but I've never tested something like that before. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rebuilding Indexes...
). __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services === message truncated === __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rebuilding Indexes...
Hey Rachel, Consider using a non-unique index for your primary key constraint. If you prebuild it and then add the constraint, Oracle will not drop the index when you drop the PK constraint, and you can control the index build that a way (and build it in parallel to boot). hth, Jack Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rebuilding Indexes...
ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: direct distributed insert causes massive sorting on target, why?
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). Thanks, Jack Silvey Could you clarify what you mean by 'two sets of slaves' ? Does this mean you got 24 slaves ? Do you get any clue about how these may be related by looking at v$px_sesstat ? This may be related in some way to the fact that when you do a direct insert on a table, Oracle still has to do ordinary index maintenance - so it sorts the incoming data for each index in turn because this improves the probability of reducing the UNDO and REDO overhead from a 'per row' cost to a 'per block' cost. PX does do some funny things because there is insufficient communication between slaves at the same level, which may be why you don't see this effect so dramatically when you serialise. Serially, Oracle may determine something about the data that it doesn't determine in parallel because it has to work on the basis that 'one of the other slaves may ... ' which causes a generic solution to be applied rather than a special case that a serial process could see. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 26 December 2002 05:50 why? All, In our 8.1.7.4 warehouse, we are attempting to copy records from a partitioned table in one domain to a partitioned table in another domain (via a database link) like so: alter session enable parallel dml; insert /*+ append parallel(a,12) */ into tablea a select /*+ full(b) parallel(b,12) */ * from tableb@dblnk b; However, unexpectedly, this causes two sets of parallel processes to be spawned on the target. One of these sets goes to work immediately, building huge sort segments in their temporary tablespace, and the others sit idle. Since we are extracting 250m+ records, we are blowing out temp tablespace. (The sort segments being created are not the same as the temporary segments that are written by CTAS or index creation statements, instead, they are true sort segments.) I would expect this behavior from a large query that needed to sort (since these types of queries can spawn two sets of PQ processes - one for record retrieval, one for sorting) however, this is just an insert of all the records with no criteria in the select statement. Why would a direct distributed parallel DML insert as select (I guess we could call this a DDPDMLIAS? :)) cause an initial run of sorting? My working hypothesis is that Oracle is following its usual rules of distributed transactions and bringing the source resultset to the target for manipulation, building it into temp segments and then using that as a source for inserts, much as it does in a typical distributed query. Perhaps a driving_site hint would help here. However, that does not explain why I was able to cause it to stop it from doing the massive sorts by serializing the target insert. -- 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). Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note
Re: direct distributed insert causes massive sorting on target, why?
as it does in a typical distributed query. Perhaps a driving_site hint would help here. However, that does not explain why I was able to cause it to stop it from doing the massive sorts by serializing the target insert. -- 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). Thanks, Jack Silvey Could you clarify what you mean by 'two sets of slaves' ? Does this mean you got 24 slaves ? Do you get any clue about how these may be related by looking at v$px_sesstat ? This may be related in some way to the fact that when you do a direct insert on a table, Oracle still has to do ordinary index maintenance - so it sorts the incoming data for each index in turn because this improves the probability of reducing the UNDO and REDO overhead from a 'per row' cost to a 'per block' cost. PX does do some funny things because there is insufficient communication between slaves at the same level, which may be why you don't see this effect so dramatically when you serialise. Serially, Oracle may determine something about the data that it doesn't determine in parallel because it has to work on the basis that 'one of the other slaves may ... ' which causes a generic solution to be applied rather than a special case that a serial process could see. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 26 December 2002 05:50 why? All, In our 8.1.7.4 warehouse, we are attempting to copy records from a partitioned table in one domain to a partitioned table in another domain (via a database link) like so: alter session enable parallel dml; insert /*+ append parallel(a,12) */ into tablea a select /*+ full(b) parallel(b,12) */ * from tableb@dblnk b; However, unexpectedly, this causes two sets of parallel processes to be spawned on the target. One of these sets goes to work immediately, building huge sort segments in their temporary tablespace, and the others sit idle. Since we are extracting 250m+ records, we are blowing out temp tablespace. (The sort segments being created are not the same as the temporary segments that are written by CTAS or index creation statements, instead, they are true sort segments.) I would expect this behavior from a large query that needed to sort (since these types of queries can spawn two sets of PQ processes - one for record retrieval, one for sorting) however, this is just an insert of all the records with no criteria in the select statement. Why would a direct distributed parallel DML insert as select (I guess we could call this a DDPDMLIAS? :)) cause an initial run of sorting? My working hypothesis is that Oracle is following its usual rules of distributed transactions and bringing the source resultset to the target for manipulation, building it into temp segments and then using that as a source for inserts, much as it does in a typical distributed query. Perhaps a driving_site hint would help here. However, that does not explain why I was able to cause it to stop it from doing the massive sorts by serializing the target insert. -- 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). Thanks, Jack Silvey
Re: compile errors
Does the owner of the package have *direct* privs (not through a role) on the object in question? List, Can anybody tell me what is happening here. I am constantly getting the PLS-00201 error when I try to compile. It is looking inside the package at a procedure and saying the identifier must be declared. I have gone over metalink docs and notes. I first compile the package spec and then the package body. I get the following response: SQL alter package schema.p_messages compile package; Warning: Package altered with compilation errors. SQL show errors Errors for PACKAGE schema.P_MESSAGES: LINE/COL ERROR - 193/5PL/SQL: Declaration ignored 197/38 PLS-00201: identifier 'HSD_TYPES.T_RETURN_CODE' must be declared 218/5PL/SQL: Declaration ignored 219/34 PLS-00201: identifier 'HSD_TYPES.T_RETURN_CODE' must be declared SQL alter package schema.p_messages compile body; Warning: Package Body altered with compilation errors. SQL show errors Errors for PACKAGE BODY schema.P_MESSAGES: LINE/COL ERROR - 0/0 PL/SQL: Compilation unit analysis terminated 1/14 PLS-00905: object schema.P_MESSAGES is invalid 1/14 PLS-00304: cannot compile body of 'P_MESSAGES' without its specification thanks, David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
direct distributed insert causes massive sorting on target, why?
All, In our 8.1.7.4 warehouse, we are attempting to copy records from a partitioned table in one domain to a partitioned table in another domain (via a database link) like so: alter session enable parallel dml; insert /*+ append parallel(a,12) */ into tablea a select /*+ full(b) parallel(b,12) */ * from tableb@dblnk b; However, unexpectedly, this causes two sets of parallel processes to be spawned on the target. One of these sets goes to work immediately, building huge sort segments in their temporary tablespace, and the others sit idle. Since we are extracting 250m+ records, we are blowing out temp tablespace. (The sort segments being created are not the same as the temporary segments that are written by CTAS or index creation statements, instead, they are true sort segments.) I would expect this behavior from a large query that needed to sort (since these types of queries can spawn two sets of PQ processes - one for record retrieval, one for sorting) however, this is just an insert of all the records with no criteria in the select statement. Why would a direct distributed parallel DML insert as select (I guess we could call this a DDPDMLIAS? :)) cause an initial run of sorting? My working hypothesis is that Oracle is following its usual rules of distributed transactions and bringing the source resultset to the target for manipulation, building it into temp segments and then using that as a source for inserts, much as it does in a typical distributed query. Perhaps a driving_site hint would help here. However, that does not explain why I was able to cause it to stop it from doing the massive sorts by serializing the target insert. Oracle support has been able to provide no technical information on this topic. Any input appreciated. Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Arm Twisting?
Agree with Rachel here - if you start getting the feeling that they can't help you, get your stuff reassigned. See escalating your tar in the TAR section of metalink. I don't think that they can reassign the TAR by themselves, and if that is true, they have to just try their best until you ask to have it moved, and sometimes they are just too inexperienced to assist. Jack --- Rachel Carmichael [EMAIL PROTECTED] wrote: she is. And she does. And she's the one in charge of the RDA project -- the Remote Diagnostic Agent And from experience, yeah, some people at Oracle Support are less than wonderful. And some (like Anita) are amazing. As with everything, you win some, you lose some. Depends on who you talk to. Oh, and it's always possible to get your TAR reassigned. You ask to speak to the duty manager. And keep asking. The only reason you get stuck with someone who doesn't know what they are doing is because you let yourself get stuck. --- Weaver, Walt [EMAIL PROTECTED] wrote: Anybody know if Anita Bardeen is still working for Oracle Support? --Walt Weaver Bozeman, Montana -Original Message- Sent: Wednesday, August 14, 2002 10:54 AM To: Multiple recipients of list ORACLE-L Yes, Oracle support sucks, and is staffed with morons. However one thing you get with Oracle support that you cannot get with third party support is bug fixes and product upgrades. If you want this without a support contract, you need to buy new licenses each time you upgrade. Rodd On Wed, 2002-08-14 at 11:13, Steven Lembark wrote: -- Naveen Nahata [EMAIL PROTECTED] Now they say, that this can't be done and the product license will be terminated if we want this. The thing is that Oracle support is useless(I get more help from this forum :) ), but we still want it, just in case. Ever heard of FUD? That's what Oracle is selling you if you think their support is that bad: fear of not having support that you don't want because you think it's bad. Q: Does anyone know of any reliable 3rd party support for Oracle? -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 ...
Gene, One of the basic rules of third normal form is to not store multiple (non-atomic) values or logic inside a column like this. The reason is that you end up storing the same values more than once, wasting space, and you have to substr the value to get your logic out of it, which could prevent index lookups, resulting in potentially nasty table scans and bad performance. If you have a concatenated index on the FK and status columns of the child table, that status lookup should be rapid. With an index that contains all the columns to be looked up, your query might be totally resolved in the index itself and not need a table visit at all. An extension of this idea would be storing the child records on an IOT, with all low-usage columns in the overflow. This would help ensure that the developer could resolve any set of highly-used columns rapidly, with the downside of relatively slow lookups for low-usage columns. Research this one first, though, since IOT tables come with their own set of challenges. hth, Jack --- Gurelei [EMAIL PROTECTED] wrote: 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Transferring data from one table to another
To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). === message truncated === __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: dropping a user's objects
Bill,
Pull out the big cannon and go boom:
export the CREATE USER to create_user script
write a script containing:
DROP USER uname CASCADE;
@CREATE_USER
run this new script.
have beer.
Now of course, this will not be as fast as a parallel
DROP TABLE user.tab CASCADE CONSTRAINTS, but hey, more
time to have beer.
hth,
Jack
--- Magaliff, Bill [EMAIL PROTECTED]
wrote:
Good day, all:
I have a PL/SQL routine that I use to drop all
objects owned by a given
user, which I'm attaching below.
I first drop primary keys, then unique constraints,
and finally all other
objects. The routine to drop all additional objects
uses a loop to select
the object name and then drops it. As you can see
by the output below
(which is just for this last piece), I drop a table
and then the next piece
is to drop the index, which doesn't exist once the
table is dropped
(obviously).
However, why is the cursor even finding the index in
the data dictionary
once the table is dropped? Is the data dictionary
not updated that quickly?
thanks
bill
ROUTINE:
declare
v_cname varchar2(30);
v_tname varchar2(30);
v_oname varchar2(30);
v_otype varchar2(30);
cursor get_pk is
select table_name from user_constraints
where constraint_type = 'P';
cursor get_unique is
select constraint_name, table_name from
user_constraints
where constraint_type = 'U';
cursor get_object is
select object_name, object_type from user_objects;
begin
open get_pk;
loop
fetch get_pk into v_tname;
exit when get_pk%notfound;
dbms_output.put_line ('dropping primary key on ' ||
v_tname);
execute immediate ('alter table ' || v_tname || '
drop primary key
cascade');
end loop;
close get_pk;
open get_unique;
loop
fetch get_unique into v_cname, v_tname;
exit when get_unique%notfound;
dbms_output.put_line ('dropping unique constraint '
|| v_cname || ' on ' ||
v_tname);
execute immediate ('alter table ' || v_tname || '
drop constraint ' ||
v_cname || ' cascade');
end loop;
close get_unique;
open get_object;
loop
fetch get_object into v_oname, v_otype;
exit when get_object%notfound;
dbms_output.put_line ('dropping ' || v_otype || ' '
|| v_oname);
execute immediate ('drop ' || v_otype || ' ' ||
v_oname);
end loop;
close get_object;
end;
/
**
OUTPUT
SQL @y:\scripts\drop_all_objects
dropping SEQUENCE AMDC_DOCUMENT_SEQ
dropping TABLE AMDC_DOC_HISTORY
dropping INDEX AMDC_DOC_HISTORY_I1
declare
*
ERROR at line 1:
ORA-01418: specified index does not exist
ORA-06512: at line 44
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(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: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
IO wait
All, We are tuning a new vital process on our data warehouse, and it is IO intensive - lots of parallel direct reads and writes. During our testing we are driving IO wait to ~60% (per top). questions: 1) is top a valid measure of IO wait? 2) Is a high io wait an issue to be concerned about? 3) how else can it be accurately measured? 4) How can I link IO wait to what is happening inside the database? Thanks, Jack __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Myers-Briggs
Here is the online test, very interesting. I am INTJ as well. http://www.humanmetrics.com/cgi-win/JTypes1.htm Jack That's very funny. INFP DBA... Wow! You do what you want with verve and to Hell the consequences whenever you blow up. Being a P, you are pragmatic about your outbursts... ? I am an INTJ most of the time. I - I am stubborn N - I follow my own inner sense of direction T - I mull things over before I act J - I have an opinion about everything. E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Monday, July 22, 2002 5:49 PM To: Multiple recipientsBoth my parents are psychologists, go figure. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO of list ORACLE-L Subject: RE: Rant-Rant FYI: SJs make up about 40% of the population (app. 10% for each sub-division). Jay Miller infp (yeah, I know, what's an infp doing in a technical job... :) -Original Message- Sent: Monday, July 22, 2002 1:23 PM To: Multiple recipients of list ORACLE-L That is why I use the machinist analogy: somebody who is satisfied with a career spending 25 years doing essentially the same thing. If you are into Myers-Briggs type indicator, I think the personality dimension is SJ and roughly 25% of the population fits this profile. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Rodd Holman Enterprise Data Systems Engineer LodgeNet Entertainment Corporation [EMAIL PROTECTED] (605) 988-1373 __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 check whether a rollback activity is going on
Gene, Run this repeatedly - used_ublk will reduce as it rolls back - after it is finished the session should dissappear. column sql_text word_wrapped select vs.sid, vs.serial#,vt.used_ublk, vt.start_time, vs.username, vs.osuser, substr(vsq.sql_text,1,75) sql_text from v$transaction vt, v$session vs, v$sqlarea vsq where vt.ses_addr = vs.saddr and vsq.address(+) = vs.sql_address / hth, Jack --- Gurelei [EMAIL PROTECTED] wrote: 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). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBMS_JOB does not run on NT
Andrey, Are you issuing a commit after your dbms_job.submit? jack --- Andrey Bronfin [EMAIL PROTECTED] wrote: Dear gurus ! I submit a job via the DBMS_JOB package , it looks OK , select from user_jobs shows that broken='N' . When i execute the job manually , using dbms_job.run , it runs , but it does not run automatically (each 5 minutes , as i have specified when submitting the job). Any guess ? Thanks a lot ! 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!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL query
,
cls.TABLE_NAME||'.'||cls.COLUMN_NAME cln,
clf.OWNER||'.'||clf.TABLE_NAME||'.'||clf.COLUMN_NAME
clfn
fromall_constraints cns,
all_cons_columns clf ,
all_cons_columns cls
where cns.table_name=upper('table_name')
and cns.owner=user
and cns.CONSTRAINT_TYPE='R'
and cns.constraint_name=cls.constraint_name
and clf.CONSTRAINT_NAME = cns.R_CONSTRAINT_NAME
and clf.OWNER = cns.OWNER
and clf.POSITION = clf.POSITION
order by cns.CONSTRAINT_NAME, cls.position
/
prompt Check Constraints
prompt
column se format a75 heading 'Criteria'
set arraysize 1
set long 32000
select CONSTRAINT_NAME cn,
initcap(STATUS) st,
SEARCH_CONDITION se
from all_constraints
where table_name=upper('table_name')
and owner=user
and CONSTRAINT_TYPE='C'
/
prompt View Constraints
select CONSTRAINT_NAME cn,
initcap(STATUS) st,
SEARCH_CONDITION se
from all_constraints
where table_name=upper('table_name')
and owner=user
and CONSTRAINT_TYPE='V'
/
spool off
btitle off
ttitle off
clear breaks
clear columns
clear computes
set verify on
set long 80
set arraysize 30
--- Vandana [EMAIL PROTECTED] wrote:
I am sorry for not being precise in my question. I
would like to
view the primary key, foreign key and other
'constraints' information of
my table. While this could be viewed with 'desc' in
other dbmss, this
information is not provided with the 'desc' in
oracle. With what command
can I view it in Oracle?
On Fri, 12 Jul 2002, Abdul Aleem wrote:
At SQL*Plus it is the same command
Describe or desc and name of the table
You also need to be connected to the database
HTH
Aleem
-Original Message-
Sent: Saturday, July 13, 2002 11:53 AM
To: Multiple recipients of list ORACLE-L
Subject:SQL query
I am using an Oracle database running in Linux. I
would like to
view the description of a table. For ex., if there
is a table called
'person'. I would like to see the names of the
columns in this table,
their datatypes and such other details. In other
dbmss like mysql a
command 'describe tablename' gives the
information. What is the
equivalent in Oracle?
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Vandana
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(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! Autos - Get free new car price quotes
http://autos.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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:killing sessions
Dick, interesting. tell us why it was such a mess. regards, jack --- [EMAIL PROTECTED] wrote: NO, they want their session killed, then they can call. I did allow this a few years ago. It turned into a real mess, PERIOD. Dick Goulet Reply Separator Author: Joe Armstrong-Champ [EMAIL PROTECTED] Date: 7/11/2002 10:51 AM We have developers who occassionally want to be able to kill their sessions in a dev instance. We are concerned about giving them alter system priviledge and were wondering what people do about this. Is there any other way they can do this? Do some shops allow certain users to do this with some training? Thanks. Joe [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Armstrong-Champ INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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!? 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Delete query
try: delete tab where (user_id, created) in (select user_id, min(created) from tab group by user_id); hth, jack --- Steven Hovington [EMAIL PROTECTED] wrote: Hi, Can anyone help me with this delete statement? I have the following table: Name Null? Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATED DATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Alter table move command
Alan, The alter table move command will invalidate all existing indicies and you will have to rebuild them. This is because the alter table move is implemented as a CTAS in the background, and all of the rowids will change. table move tips: 1) use parallelism - however, parallel processes will write to their own segments, and will trim the unused space off the end for all but one of the segments during the final merge of all the segments into the new index segment. This can give you odd sized segments, throwing off uniform space allocation (if you use that). index rebuilding tips: 1) If your index is partitioned, you can rebuild the partitions at the same time, and rebuild all of your indexes at the same time. However, you cannot update/insert/delete the table while this is going forward, unless you use the online option, which has limitations. 2) Use parallel (degree x) to rebuild, since it will spawn off more processes and take less time. see space considerations above. 3) Use nologging, unless you want to store your indexes in the redo logs (otherwise, you can always just recreate, much easier) 4) use the compute statstics clause to gather stats during the build 5) consider using initrans 4 or better, otherwise, you risk running into deadlocks during parallel updates (doesn't take up that much more space - 23 bytes or so). Same for pctfree - leave 1 pct, otherwise the ITL list can't grow and you might get into trouble. 6) some indexes can be built online, which allows updates to go forward during the rebuild. hth, jack --- Alan Davey [EMAIL PROTECTED] wrote: Hi All, I want to use the alter table move command (under 9i) to change the storage parameters for a couple of tables. The tables will remain in the current tablespace. Will I need to rebuild any indicies on that table, or will the rowid's be updated automatically. I've looked in the FM, but I don't see any caveats about indicies when using the move option. So, am I safe in assuming that I don't need to worry about them? Thanks, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Delete query
Vadim, excellent answer. answers the question and the underlying intent, and takes into account performance. jack --- Vadim Gorbounov [EMAIL PROTECTED] wrote: To avoid aggregates you can : delete from table t where not exists (select null from table x where t.USER_ID=x.USER_ID and t.CREATED x.CREATED); it seems to run faster given the USER_ID and CREATED are indexed -Original Message- Sent: Tuesday, July 09, 2002 1:00 PM To: Multiple recipients of list ORACLE-L Hi, Can anyone help me with this delete statement? I have the following table: Name Null? Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATED DATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Vadim Gorbounov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle 8.1.7 function based indexes
Imma, First thought is no - oracle implements function-based indexes by using a hidden psuedocolumn to hold the resultant data of the function. Since you would be using a bind variable, it would not be able to create this column. Since you are passing a variable, Oracle cannot use the table stats (high/low values, histograms) to determine access path easily, so is choosing a FTS. If you are sure that you want to use an index, you might consider forcing an index lookup via a hint. A word of caution - when using the index hint, consider not putting the name of the index in the hint, since if you do, and the index name ever changes, the hint will be ignored and the FTS behavior will return. hth, Jack --- Imma C. Rocco [EMAIL PROTECTED] wrote: Hi, I have read that on Oracle 8.1.7 it is possible to create a function based index like the one: Create index ind1 on table (substr(column_name)) tabelspace etc And if Oracle optimizer is in CBO mode and query_rewrite_enabled = true query_rewrite_integrity = trusted compatible = 8.1.0 or greater A statement which has a where clause that involve a substr(colum_name) should use the index. (*)I would like to know if it is also possible to create a function based index on a NVL function that involve a bind variable, because a have a statement like the one that follow: select 'x' from table where col_name = nvl(:p_aa, col_name) This statement perform a full table scan on table ps: if (*) is possible could you please give an sintax example? Thanks Imma _ Supercharge your e-mail with a 25MB Inbox, POP3 Access, No Ads and NoTaglines -- LYCOS MAIL PLUS. http://www.mail.lycos.com/brandPage.shtml?pageId=plus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imma C. Rocco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle 8.1.7 function based indexes
A friend of mine named Jane just called me and pointed out that you could create a function-based index on the column using this syntax, but you can't use a bind variable to define it. If you create an index with this form nvl(column_name, '_null_') and then issue a query with the form where nvl(column_name, '_null_') = :bind then the function-based index is a candidate to be used. However, since you are using bind variables, oracle is having to use a default data estimate to decide on execution plan, so you may still have to hint the index to get it to be used. As always, test and then test some more. hth, Jack --- Jack Silvey [EMAIL PROTECTED] wrote: Imma, First thought is no - oracle implements function-based indexes by using a hidden psuedocolumn to hold the resultant data of the function. Since you would be using a bind variable, it would not be able to create this column. Since you are passing a variable, Oracle cannot use the table stats (high/low values, histograms) to determine access path easily, so is choosing a FTS. If you are sure that you want to use an index, you might consider forcing an index lookup via a hint. A word of caution - when using the index hint, consider not putting the name of the index in the hint, since if you do, and the index name ever changes, the hint will be ignored and the FTS behavior will return. hth, Jack --- Imma C. Rocco [EMAIL PROTECTED] wrote: Hi, I have read that on Oracle 8.1.7 it is possible to create a function based index like the one: Create index ind1 on table (substr(column_name)) tabelspace etc And if Oracle optimizer is in CBO mode and query_rewrite_enabled = true query_rewrite_integrity = trusted compatible = 8.1.0 or greater A statement which has a where clause that involve a substr(colum_name) should use the index. (*)I would like to know if it is also possible to create a function based index on a NVL function that involve a bind variable, because a have a statement like the one that follow: select 'x' from table where col_name = nvl(:p_aa, col_name) This statement perform a full table scan on table ps: if (*) is possible could you please give an sintax example? Thanks Imma _ Supercharge your e-mail with a 25MB Inbox, POP3 Access, No Ads and NoTaglines -- LYCOS MAIL PLUS. http://www.mail.lycos.com/brandPage.shtml?pageId=plus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imma C. Rocco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to calculate table size in 8i
New, No guru here, but that is the way that I do it - average row size vs estimated number of rows * fudge factor of 30% plus a little room for underestimation. Also, don't forget room for indexes. I have found this method to be quite successful, since the major hurdle is figuring out how many rows the customers will have. Often, even they don't know for sure, so you have to help them not hurt themselves by being liberal in your space estimations. Remember, if you overestimate, no one will know except you and other dbas, since no one really cares after as long as the system runs well in production. I have had systems that were 50 megs sitting on 50 gig Veritas clusters and everone was happy. Underestimate space, though, and say hello to long periods of data shuffling and constant firefighting. As the old saying goes, Goofups are forever. hth, Jack --- CHAN Chor Ling Catherine (CSC) [EMAIL PROTECTED] wrote: Hi Gurus, I found an article in metalink 105765.1 How to Determine Approximate Hard Drive Space Needed for a Specific Table. The formula for disk space is simply multiplying the average row length (by analyzing the table) * the number of rows in the table. It's very different from Metalink 10640.1 Extent and Block Space Calculation and Usage in V7 Database where it takes the block header etc in considerations but of course, article 10640.1 is for Version 7. How do you gurus calculate table space in Version 8 ? Please advise. Thanks. Regds, New Bee Doc ID /help/usaeng/Search/search.html : Note:105765.1 Content Type: TEXT/PLAIN Specific TableCreation Date: 18-APR-2000 Type: PROBLEM Last Revision Date: 26-DEC-2000 Status: PUBLISHED Problem Description --- How can you determine how much disk space is needed for a table? Solution Description You can use SQL to determine how much space is needed for the table based upon the average row length. 1. compute statistics about this table: analyze CEUSER.CE_STATEMENT_LINES compute statistics; Now determine the average row length in bytes: select avg_row_len from dba_tables where table_name=' CE_STATEMENT_LINES'; AVG_ROW_LEN === 98 2. Multiple the average row length in bytes by the number of rows you believe you will need: 98 (bytes) x 1 records = 98 bytes needed References -- For more information on the ANALYSE command, you may wish to refer to: Oracle8 SQL Reference Release 8.0, Part No.A58225-01 Also: Oracle8i SQL Reference Release 8.1.5, Part Number: A67779-01 . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Exporting stats for a production server to a dev/test server
Cherie,
Great name.
Our initial stats were generated with the ANALYZE
command. We did export all the stats with the
dbms_stats - one parameter of the GATHER_TABLE_STATS
procedure is CASCADE, which picks up the index and
column stats too. Please test first on your own system
to make sure this is true for your version and setup.
We did not delete the stats before doing the new, what
we did was this:
1) export the old stats
2) analyze using a new sample, cascade, with histos
3) export the new stats (with a new label)
4) test, reimport the old and new stats as necessary
Do this quick test to show yourself what it can do:
a) create table tester.statstest as select * from
dba_tables;
b) create index tester.statstestidx on
statstest(table_name);
c) analyze table tester.statstest estimate statistics
sample 1 percent for table for all indexes for all
columns size 254;
d) create the stats holding table in the tester schema
using
DBMS_STATS.CREATE_STAT_TABLE('tester','stats','tsuser01')
e) export the stats using
DBMS_STATS.EXPORT_TABLE_STATS('tester','statstest',null,'stats','1pcttest',true,null);
f) select * from tester.stats to see what is stored
g) analyze table tester.statstest delete statistics;
h) import the stats using
DBMS_STATS.IMPORT_TABLE_STATS('tester','statstest',null,'stats','1pcttest',true,null);
i) select * from dba_tables, dba_indexes,
dba_tab_histograms to see what was imported
You can store multiple versions of stats for the same
table with different lables ('1pctest' in the example)
but if you try to store stats with the same label it
will overwrite.
We did have some data dictionary locking issues trying
to import multiple partitions of the same table at the
same time, but our db has suspect dd issues anyway, so
this might just be a feature of our system.
hth,
Jack
--- [EMAIL PROTECTED] wrote:
Jack,
Were your old statistics generated with the ANALYZE
command? If yes, were
you able to export ALL of the old statistics with
the export option of
DBMS_STATS before you actually analyzed with
DBMS_STATS for the first time?
If yes, after exporting them, did you delete the old
statistics using the
delete option of ANALYZE command before gathering
new statistics using
DBMS_STATS?
Thanks,
Cherie Machler
Oracle DBA
Gelco Information Network
Jack Silvey
jack_silvey@y To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
ahoo.comcc:
Sent by: Subject:
Re: Exporting stats for a production server to a
dev/test
[EMAIL PROTECTED]server
om
07/07/02 08:58
PM
Please respond
to ORACLE-L
We just used it with great success to test our new
analyze strategy. You can label each set of stats,
and
quickly switch between different analyze levels.
Store
your old stats first and you can always fall back if
necessary.
Jack
--- [EMAIL PROTECTED] wrote:
Hi
Can anyone give feedback good or bad on the
dbms_stats feature of
exporting statistics. Is there any gotcha's or
does
it work well
Cheers
--
=
Peter McLarty E-mail:
[EMAIL PROTECTED]
Technical ConsultantWWW:
http://www.mincom.com
APAC Technical Services Phone: +61 (0)7 3303
3461
Brisbane, AustraliaMobile: +61 (0)402 094
238
Facsimile: +61 (0)7
3303
3048
=
A great pleasure in life is doing what people say
you cannot do
Re: insert nologging parallel/noparallel and archiving - thanks
Gene, Glad to help. A word of caution, do consider the recovery aspects of nologging carefully before use. A backup immediately after the move might be prudent, or keeping the old data around as a fallback. Jack --- Gurelei [EMAIL PROTECTED] wrote: 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). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Exporting stats for a production server to a dev/test server
We just used it with great success to test our new analyze strategy. You can label each set of stats, and quickly switch between different analyze levels. Store your old stats first and you can always fall back if necessary. Jack --- [EMAIL PROTECTED] wrote: Hi Can anyone give feedback good or bad on the dbms_stats feature of exporting statistics. Is there any gotcha's or does it work well Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- 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!? 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: resetting sequence numbers
Alexandre, Take it easy, just a little friday humor. All ways have their advantages and disadvantages. jack --- Alexandre Gorbatchev [EMAIL PROTECTED] wrote: Yeah? What if he had dropped the sequence, recreated it, and goddamn application didn't work because he had lost goddamn grants? Good if he knew what were these grants. Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 05, 2002 4:20 PM ah jeez, you ferrin car drivers, always making thangs complicated when all you need is a big hammer cubic inch approach. What next? Setting maxvalue equal to the current number and selecting one number? Renaming the sequence and creating a synonym to point to another? Asking WHY he needs to do this, only to find out that the code is flawed since it should not rely on absolute serialization from a sequence in the first place, for god's sake? Don't you know that promoting alternate lifestyle DBA ideas you are encouraging the inexperienced to question authority and addin' to the moral corruption of Ameruca? Stop poisonin the newbies with fancy ideas about reverse select and such as that. If recreate was good enuf for us, it is darn sure good enuf for them! end jingoistic rural reactionary rant I slay me. I am gonna be at work in one hour, and then we can DO STATS! --- Larry Elkins [EMAIL PROTECTED] wrote: Jack, But then you have to redo grants, any invalidations. No big deal automating. But if you set the increment to a negative to take you right back down to zero, then set the increment to 1 (or whatever it was), there you go, no dropping the sequence. 10 to 1 odds Jared posts a nice little script that automates this second approach ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jack Silvey Sent: Friday, July 05, 2002 8:03 AM To: Multiple recipients of list ORACLE-L Subject: Re: resetting sequence numbers drop sequence, recreate sequence, rinse, repeat. Jack --- Malik, Fawzia [EMAIL PROTECTED] wrote: Hi, Is there a way of resetting sequence numbers back to zero???The application we have needs to have the sequence numbers reset... Thanks in advance Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an === message truncated === __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself
Re: insert nologging parallel/noparallel and archiving
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. One way to consider would be to alter indexes to unusable and rebuild with nologging and compute statistics clauses. This way, your load is fast, you generate minimal redo, and your indexes are balanaced and have good stats afterwards. A space consideration with PDML and dictionary managed tablespaces - each PQ process in your PDML is going to have its own extent. This extent will begin life with a size=initial, but will have the empty space trimmed off at the end of the write, leaving an odd size extent. Since you are doing PDML, you might have these extents close together, and when the trim occurs, it will leave odd sized holes in between and throw off your space managment strategy. I don't believe that LMT tablespaces have this issue. hth, jack --- Gurelei [EMAIL PROTECTED] wrote: 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). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
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. One way to consider would be to alter indexes to unusable and rebuild with nologging and compute statistics clauses. This way, your load is fast, you generate minimal redo, and your indexes are balanaced and have good stats afterwards. A space consideration with PDML and dictionary managed tablespaces - each PQ process in your PDML is going to have its own extent. This extent will begin life with a size=initial, but will have the empty space trimmed off at the end of the write, leaving an odd size extent. Since you are doing PDML, you might have these extents close together, and when the trim occurs, it will leave odd sized holes in between and throw off your space managment strategy. I don't believe that LMT tablespaces have this issue. hth, jack --- Gurelei [EMAIL PROTECTED] wrote: 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). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 ora certified masters cert, yet again
Good morning Don, I know that you are not suggesting that those with the OCP are bad persons. Listers, I suggest that we drop this topic now, since it might start to offend if it goes further. We all have much more in common that we have that divides us, and this topic is a downright silly thing to risk hurting each other over. It is worth far less than the harmony of this community. Jack OCPx4 --- Don Granaman [EMAIL PROTECTED] wrote: From my experience with a few DBAs that waved their OCP around like it was a Nobel prize, some probably should have malpractice insurance. Lots of it. Don Granaman [Certifiable OraSaurus - NOCP] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 27, 2002 1:08 AM But doctors have malpractice insurance and OCP does not or do/should OCP have one too. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
report showing free space per tablespace over time
Listers, Ever been asked how much free space you have in the tablespaces over time? I wrote a tool that tracks and reports on free space per tablespace over time and thought I would share it. It consists of a simple stored procedure that stores values in a holding table and a report that will tell you how much each tablespace had over the past week (you can change the coverage of this report.) 1) Here is the stored procedure: CREATE OR REPLACE PROCEDURE tablespace_proc AS v_errmsg varchar2(100); v_errcode varchar2(100); BEGIN delete dbmon.dbmon_tablespace_stats where trunc(record_date) = trunc(sysdate); INSERT INTO dbmon.dbmon_tablespace_stats (tablespace_name, total_space, megs_free, max_extent, autoextend, record_date) SELECT fs.tablespace_name, round(df.total_bytes/1024/1024,0), round(fs.bytes_free/1024/1024,0), round(fs.max_bytes/1024/1024,0), decode(a.tablespace_name,null,'No','Yes'), sysdate FROM (SELECT tablespace_name, SUM(bytes) bytes_free, max(bytes) max_bytes FROM dba_free_space fs GROUP BY tablespace_name) fs, (SELECT tablespace_name, sum(bytes) total_bytes FROM dba_data_files GROUP BY tablespace_name) df, (SELECT DISTINCT tablespace_name FROM dba_data_files WHERE autoextensible = 'YES') a, (select tablespace_name from dba_tablespaces) ts WHERE df.tablespace_name = fs.tablespace_name(+) AND df.tablespace_name = a.tablespace_name(+) AND df.tablespace_name = ts.tablespace_name; commit; exception when others then v_errmsg := substr(SQLERRM,1,100); v_errcode := SQLCODE; insert into dbmon_activity_log (activity_date, activity_desc, procedure_name, error_code, error_msg, error_date) values (sysdate, 'tablespace_proc', 'tablespace_proc', v_errcode, v_errmsg, sysdate); commit; end; / 2) Here is the code to submit it to the job scheduler (every day at 5AM): variable jobno number exec sys.dbms_job.submit(job=:jobno, what='begin dbmon.dbmon_tablespace_proc;end;', next_date=trunc(sysdate+1)+5/24,interval='trunc(sysdate+1)+5/24'); 3) and here is the report: @save_sqlplus_settings set term off set head off spool temp.sql select 'col c'||rownum ||' for 999,999,990 head '|| to_char(sysdate+1-rownum,'dd-mon')||'' from dba_tablespaces where rownum8; select 'compute sum of c'||rownum ||' on report' from dba_tablespaces where rownum8; select 'break on report' from dual; spool off @temp.sql exec dbmon.dbmon_tablespace_proc; set term on set lines 135 prompt prompt Free space per tablespace: prompt select tablespace_name, sum(decode(old,7,value)) c7, sum(decode(old,6,value)) c6, sum(decode(old,5,value)) c5, sum(decode(old,4,value)) c4, sum(decode(old,3,value)) c3, sum(decode(old,2,value)) c2, sum(decode(old,1,value)) c1 from (select tablespace_name, megs_free value, decode (trunc(record_date), trunc(sysdate),1, trunc(sysdate)-1,2, trunc(sysdate)-2,3, trunc(sysdate)-3,4, trunc(sysdate)-4,5, trunc(sysdate)-5,6, trunc(sysdate)-6,7) old from dbmon.dbmon_tablespace_stats) group by tablespace_name order by 1 / good luck, Jack __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: report showing free space per tablespace over time
,5,value)) c5, sum(decode(old,4,value)) c4, sum(decode(old,3,value)) c3, sum(decode(old,2,value)) c2, sum(decode(old,1,value)) c1 from (select tablespace_name, megs_free value, decode (trunc(record_date), trunc(sysdate),1, trunc(sysdate)-1,2, === message truncated === __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 ora certified masters cert, yet again
The question is, are you going to allow your clearminded moral stance and total disdain for a thinly veiled DBA tax to interfere with your pursuit of filth lucre? *I* ain't! ;) It is just another hoop to jump through so that a hring manager can say that is an impressive hoop you jumped through and you can respond yes, and I can jump through some hoops for you too and allow them to say here is an outrageous sum of money to work on our computers. I love this job. jack silvey ocp 7.3, 8.0, 8i, 9i --- Don Granaman [EMAIL PROTECTED] wrote: They aren't - unless it exceeds a non-trivial percentage (6%? 7%? more? I can't remember now...) of their income and is required (?). This new requirement for OCP is just another in a long line of propaganda/baloney from Oracle in its never-ending attempts to suck up every buck it possibly can. [Oracle likes $. HR likes mindless checklist items. It is a match made in heaven.] I thought that the need practically any two ILT classes, no matter how irrelevant 9i OCM was going to be the limit of extending the the greedy grab for OCP bucks - for 9i at least. This isn't about certification anymore (as if it ever was), its about revenue. Since this new requirement (for the moment at least) doesn't apply to upgrade from an 8i certification, does anyone know if there is (or soon will be) a new constraint/surprise/ambush limiting that to 8i OCP obtained prior to, oh say, June 15, 2002? September 2002? Don Granaman [OraSaurus - with more disdain than ever for the evil vampire Larry's OCP DBA tax] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 21, 2002 12:23 AM I thought employees were not allowed to write things off as business expenses... Confusedly yours, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Thursday, June 20, 2002 10:13 PM To: Multiple recipients of list ORACLE-L Subject: RE: the ora certified masters cert, yet again Are you trying to promote it? -Original Message- Sent: Thursday, June 20, 2002 6:50 PM To: Multiple recipients of list ORACLE-L I am seriously considering pursuing one, since it can be sold to hiring managers as a sign of professional competence. Look at it from a cost/benefit ratio standpoint. Will someone with this cerifification make $2000 more over her professional life than she would without? So it takes a round trip ticket and three days of vacation. Get the company to pay for it or write it off as a business expense. Good investment, easy money, instant credibility to many hiring managers. jack silvey On 19 Jun 2002 at 4:38, Ron Rogers wrote: Date sent: Wed, 19 Jun 2002 04:38:18 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] It seems that our list has made mention in this report from Searchdatabase.com. And Oracle is trying to justify the $2000 expence. If I read this correct the $2000 is for 9i OCP. === LEAD STORY ORACLE FUELS CERTIFICATION CONTROVERSY | SearchDatabase Oracle has a new requirement for its potential certified professionals, and the price tag is about $2,000. Many DBAs aren't happy about the new policy but Oracle says the class makes their certification more valuable than ever. Read the details of the new mandate, and what DBAs and industry experts have to say about it. For the full details, click: http://www.searchdatabase.com/originalContent/0,289142,sid13_gci833782,00.ht ml ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from
Re: the ora certified masters cert, yet again
Rachel, So, you substitute books and presentations in place of the OCP? This sounds like we are in agreement in principle... ;) jack --- Rachel Carmichael [EMAIL PROTECTED] wrote: okay, I realize this won't work for everyone on this list but... I hand them my resume. the third page of which is FILLED with lists of presentations I have given, awards I have gotten for presentations I have given and books I have written if they STILL want me to have OCP on my resume after that, I don't want to work there anyway --- Jack Silvey [EMAIL PROTECTED] wrote: The question is, are you going to allow your clearminded moral stance and total disdain for a thinly veiled DBA tax to interfere with your pursuit of filth lucre? *I* ain't! ;) It is just another hoop to jump through so that a hring manager can say that is an impressive hoop you jumped through and you can respond yes, and I can jump through some hoops for you too and allow them to say here is an outrageous sum of money to work on our computers. I love this job. jack silvey ocp 7.3, 8.0, 8i, 9i --- Don Granaman [EMAIL PROTECTED] wrote: They aren't - unless it exceeds a non-trivial percentage (6%? 7%? more? I can't remember now...) of their income and is required (?). This new requirement for OCP is just another in a long line of propaganda/baloney from Oracle in its never-ending attempts to suck up every buck it possibly can. [Oracle likes $. HR likes mindless checklist items. It is a match made in heaven.] I thought that the need practically any two ILT classes, no matter how irrelevant 9i OCM was going to be the limit of extending the the greedy grab for OCP bucks - for 9i at least. This isn't about certification anymore (as if it ever was), its about revenue. Since this new requirement (for the moment at least) doesn't apply to upgrade from an 8i certification, does anyone know if there is (or soon will be) a new constraint/surprise/ambush limiting that to 8i OCP obtained prior to, oh say, June 15, 2002? September 2002? Don Granaman [OraSaurus - with more disdain than ever for the evil vampire Larry's OCP DBA tax] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 21, 2002 12:23 AM I thought employees were not allowed to write things off as business expenses... Confusedly yours, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Thursday, June 20, 2002 10:13 PM To: Multiple recipients of list ORACLE-L Subject: RE: the ora certified masters cert, yet again Are you trying to promote it? -Original Message- Sent: Thursday, June 20, 2002 6:50 PM To: Multiple recipients of list ORACLE-L I am seriously considering pursuing one, since it can be sold to hiring managers as a sign of professional competence. Look at it from a cost/benefit ratio standpoint. Will someone with this cerifification make $2000 more over her professional life than she would without? So it takes a round trip ticket and three days of vacation. Get the company to pay for it or write it off as a business expense. Good investment, easy money, instant credibility to many hiring managers. jack silvey On 19 Jun 2002 at 4:38, Ron Rogers wrote: Date sent: Wed, 19 Jun 2002 04:38:18 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] It seems that our list has made mention in this report from Searchdatabase.com. And Oracle is trying to justify the $2000 expence. If I read this correct the $2000 is for 9i OCP. === LEAD STORY ORACLE FUELS CERTIFICATION CONTROVERSY | SearchDatabase Oracle has a new requirement for its potential certified professionals, and the price tag is about $2,000. Many DBAs aren't happy about the new policy but Oracle says the class makes their certification more valuable than ever. Read the details of the new mandate, and what DBAs and industry experts have to say about it. For the full details, click: http://www.searchdatabase.com/originalContent/0,289142,sid13_gci833782,00.ht ml ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 === message truncated === __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http
RE: Suggestions on MV Implementation !!!!!!!
of my Sr.DBA told me that the MV definition will not allow us for a FAST refresh( Are there any limitations for FAST refresh ). Here is a sample MV Definition : CREATE MATERIALIZED VIEW GENRELOB NOLOGGING BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND DISABLE QUERY REWRITE AS SELECT DISTINCT '1' AS CLIP, LOB.LOB_ID, LOB.LOB_CD, GENRE.GENRE_ID, GENRE.GENRE_DESC, GENRE.GENRE_DESC AS INSTANCENAME FROM GENRE, GENRE_LOB_XREF, LOB, GENRE_PRODUCT_XREF WHERE GENRE.GENRE_ID = GENRE_LOB_XREF.GENRE_ID AND GENRE_LOB_XREF.LOB_ID = LOB.LOB_ID AND GENRE_PRODUCT_XREF.genre_id = GENRE.genre_id AND GENRE.DSPLY_IND = 'Y' ORDER BY LOB_CD, GENRE_DESC ; My Goal is to view the FRESH data on web pages all the time , irrespective of MV Refresh. Would anybody suggest me some bright === message truncated === __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: distributed timeout error
Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 ora certified masters cert, yet again
Not necessarily, just saying that it is one more bullet in the gun when it comes time to ask for more money, and it is probably a decent investment. --- Khedr, Waleed [EMAIL PROTECTED] wrote: Are you trying to promote it? -Original Message- Sent: Thursday, June 20, 2002 6:50 PM To: Multiple recipients of list ORACLE-L I am seriously considering pursuing one, since it can be sold to hiring managers as a sign of professional competence. Look at it from a cost/benefit ratio standpoint. Will someone with this cerifification make $2000 more over her professional life than she would without? So it takes a round trip ticket and three days of vacation. Get the company to pay for it or write it off as a business expense. Good investment, easy money, instant credibility to many hiring managers. jack silvey On 19 Jun 2002 at 4:38, Ron Rogers wrote: Date sent: Wed, 19 Jun 2002 04:38:18 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] It seems that our list has made mention in this report from Searchdatabase.com. And Oracle is trying to justify the $2000 expence. If I read this correct the $2000 is for 9i OCP. === LEAD STORY ORACLE FUELS CERTIFICATION CONTROVERSY | SearchDatabase Oracle has a new requirement for its potential certified professionals, and the price tag is about $2,000. Many DBAs aren't happy about the new policy but Oracle says the class makes their certification more valuable than ever. Read the details of the new mandate, and what DBAs and industry experts have to say about it. For the full details, click: http://www.searchdatabase.com/originalContent/0,289142,sid13_gci833782,00.ht ml ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
distributed timeout error
Listers, We have two stored procedures that are suddenly throwing random distributed operation not supported errors. These pieces of code are doing an insert into as select across a database link. The data is being pushed, not pulled. They have been running for months in production without a problem. Suddenly, they start throwing these errors, but will often succeed on the retry. Anyone have a similiar problem? thx, jack __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Suggestions on MV Implementation !!!!!!!
Recreate the snapshot to allow fast refresh, (you will have to create a snapshot log on your source table) and refresh once every 5/10/20/30 minutes. Fast refreshes are just one commit that is either committed or rolled back at the end. Viola, fresh data instantaneously. You can do a refresh immediate when you recreate the snapshots so it will build the data right away. To do it really fast, create a new snapshot with the correct definition, rename the old snapshot, rename the new snapshot to the old name, recompile your packages and procedures, drop the old snapshot, and viola, new snapshot. Snapshots refresh via a job in the Oracle job queue. You can adjust timing on this job to adjust your refresh frequency. Make sure you get your indexes, stats, and grants in place on the new snap too. Check your synonyms as well. hth, jack --- Reddy, Madhusudana [EMAIL PROTECTED] wrote: Hello All, I have a set of Materialized views in my DB . we refresh ( COMPLETE) these MVs, couple of times a day. Web server ( application ) will hit these MVs to show the data on web pages. But the complete Refresh of MVs are consuming much time and , at this point of time , Application is not able to show right data on web pages. This is like a down time. I need some suggestions from you all, in order to minimize or zeroing this down time. The first thing I can think of is , FAST refresh , but one of my Sr.DBA told me that the MV definition will not allow us for a FAST refresh( Are there any limitations for FAST refresh ). Here is a sample MV Definition : CREATE MATERIALIZED VIEW GENRELOB NOLOGGING BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND DISABLE QUERY REWRITE AS SELECT DISTINCT '1' AS CLIP, LOB.LOB_ID, LOB.LOB_CD, GENRE.GENRE_ID, GENRE.GENRE_DESC, GENRE.GENRE_DESC AS INSTANCENAME FROM GENRE, GENRE_LOB_XREF, LOB, GENRE_PRODUCT_XREF WHERE GENRE.GENRE_ID = GENRE_LOB_XREF.GENRE_ID AND GENRE_LOB_XREF.LOB_ID = LOB.LOB_ID AND GENRE_PRODUCT_XREF.genre_id = GENRE.genre_id AND GENRE.DSPLY_IND = 'Y' ORDER BY LOB_CD, GENRE_DESC ; My Goal is to view the FRESH data on web pages all the time , irrespective of MV Refresh. Would anybody suggest me some bright ideas , to have no or less down time ??? Thanks in advance Madhu V Reddy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: distributed timeout error
Oracle support is saying much the same thing. They are advising an upgrade to 8.1.7.4 (from 8.1.7.2). Doesn't give us the reason WHY it is happening, but it might fix it. We have run 10053 and 10046 traces and can see no obvious issues. --- Michael P Sale [EMAIL PROTECTED] wrote: Without seeing more information, I have seen this type of a response where either the instance is not registered with the listener, or the db is not accepting connections for a variety of reasons (e.g. no more memory all the way to the db is shut down). Regards, Michael Sale Author: Oracle9i for Windows(R) 2000 Tips Techniques http://www.amazon.com/exec/obidos/ASIN/0072194626 -Original Message- Silvey Sent: Friday, June 21, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Listers, We have two stored procedures that are suddenly throwing random distributed operation not supported errors. These pieces of code are doing an insert into as select across a database link. The data is being pushed, not pulled. They have been running for months in production without a problem. Suddenly, they start throwing these errors, but will often succeed on the retry. Anyone have a similiar problem? thx, jack __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Michael P Sale INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Suggestions on MV Implementation !!!!!!!
Thanks Mahu. Do you get the feeling that I might have done that a few times? ;) Snapshots and materialized views are the same thing. I guess I might start calling them materialized views in the next version or two, but it is so hard to give up old habits. Another thing you might need to know - you can't easily change a job in the Oracle job queue unless you are the owner, which means that you can't do it as DBA. There is a package called dbms_ijob that will allow you to change jobs as a dba even if you don't own them. There is usually no public synonym for this package, so you will have to refer to it as sys.dbms_ijob. I believe that this package is not officially supported, so you might not find a lot of documentation on it, but I have used it for over a year without any problems. To turn off a snapshot refresh, use the sys.dbms_ijob.broken function. *BE ADVISED* If you unbreak a job in the Oracle job queue, it will try to run immediately. This includes snapshot jobs. If you unbreak a *complete* snapshot refresh job, the first thing it does is truncate the target table. Unbreak a complete snapshot refresh job in the middle of the day and viola, the users suddenly have no data. Be careful. hth, jack --- Reddy, Madhusudana [EMAIL PROTECTED] wrote: Jack, Nice picture of the whole thing . Through out your solution , mentioned SNAPSHOT , you mean Materialized view ??? Thanks, Madhu -Original Message- Sent: Friday, June 21, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Recreate the snapshot to allow fast refresh, (you will have to create a snapshot log on your source table) and refresh once every 5/10/20/30 minutes. Fast refreshes are just one commit that is either committed or rolled back at the end. Viola, fresh data instantaneously. You can do a refresh immediate when you recreate the snapshots so it will build the data right away. To do it really fast, create a new snapshot with the correct definition, rename the old snapshot, rename the new snapshot to the old name, recompile your packages and procedures, drop the old snapshot, and viola, new snapshot. Snapshots refresh via a job in the Oracle job queue. You can adjust timing on this job to adjust your refresh frequency. Make sure you get your indexes, stats, and grants in place on the new snap too. Check your synonyms as well. hth, jack --- Reddy, Madhusudana [EMAIL PROTECTED] wrote: Hello All, I have a set of Materialized views in my DB . we refresh ( COMPLETE) these MVs, couple of times a day. Web server ( application ) will hit these MVs to show the data on web pages. But the complete Refresh of MVs are consuming much time and , at this point of time , Application is not able to show right data on web pages. This is like a down time. I need some suggestions from you all, in order to minimize or zeroing this down time. The first thing I can think of is , FAST refresh , but one of my Sr.DBA told me that the MV definition will not allow us for a FAST refresh( Are there any limitations for FAST refresh ). Here is a sample MV Definition : CREATE MATERIALIZED VIEW GENRELOB NOLOGGING BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND DISABLE QUERY REWRITE AS SELECT DISTINCT '1' AS CLIP, LOB.LOB_ID, LOB.LOB_CD, GENRE.GENRE_ID, GENRE.GENRE_DESC, GENRE.GENRE_DESC AS INSTANCENAME FROM GENRE, GENRE_LOB_XREF, LOB, GENRE_PRODUCT_XREF WHERE GENRE.GENRE_ID = GENRE_LOB_XREF.GENRE_ID AND GENRE_LOB_XREF.LOB_ID = LOB.LOB_ID AND GENRE_PRODUCT_XREF.genre_id = GENRE.genre_id AND GENRE.DSPLY_IND = 'Y' ORDER BY LOB_CD, GENRE_DESC ; My Goal is to view the FRESH data on web pages all the time , irrespective of MV Refresh. Would anybody suggest me some bright ideas , to have no or less down time ??? Thanks in advance Madhu V Reddy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network
the ora certified masters cert, yet again
I am seriously considering pursuing one, since it can be sold to hiring managers as a sign of professional competence. Look at it from a cost/benefit ratio standpoint. Will someone with this cerifification make $2000 more over her professional life than she would without? So it takes a round trip ticket and three days of vacation. Get the company to pay for it or write it off as a business expense. Good investment, easy money, instant credibility to many hiring managers. jack silvey On 19 Jun 2002 at 4:38, Ron Rogers wrote: Date sent:Wed, 19 Jun 2002 04:38:18 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] It seems that our list has made mention in this report from Searchdatabase.com. And Oracle is trying to justify the $2000 expence. If I read this correct the $2000 is for 9i OCP. === LEAD STORY ORACLE FUELS CERTIFICATION CONTROVERSY | SearchDatabase Oracle has a new requirement for its potential certified professionals, and the price tag is about $2,000. Many DBAs aren't happy about the new policy but Oracle says the class makes their certification more valuable than ever. Read the details of the new mandate, and what DBAs and industry experts have to say about it. For the full details, click: http://www.searchdatabase.com/originalContent/0,289142,sid13_gci833782,00.html ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: sys corrupted in warehouse, sev1 tar open - resolved
Bruce, Thanks for the info, it is a useful thing to remember. Now for the post-game analysis: the way we found this was that RMAN stopped functioning with a dbms_backup_restore.somethingorother must be declared because the one in the wrong schema was invalid. RMAN logs in as sys. When the oncall ran catproc again under the advice of tech support, it recreated all the stored procedures in the schema set by the alter schema. This rendered people logging in from sqlplus unable to find dbms_application_info, although the public synonym was pointing to the sys package. This was the red herring that kept throwing us off - the public synonyms were pointing to the sys objects, but the processes kept trying to go to the schema set by the login trigger. We were working under the implicit assumption (oops! Unproven Assumptions Bite DBAS, Details at 10) that the object refs in the rman and sqlplus login code were hard-coded (after all, why would you NOT hardcode the sys owner in your dd refs? not like anyone else will ever own those objects) and that somehow sys and the new schema were crossed up in the data dictionary. so, What We Learned: 1) be vewwwy careful with alter schema command, wabbit 2) apparently neither the sqlplus nor the rman code use schema prefixes in their object references. Lot of work for that little tidbit of info, I would say! ;) jack --- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: Jack, Something you may find useful if you're not already aware is the schemaname field in v$session. Compare this to username and this may help determine if set current_schema is being used. We use the set current_schema in a login trigger, though the trigger has smarts in it to only do it for application users and not for schema owners, sys, system etc. HTH, Bruce Reardon -Original Message- Sent: Thursday, 13 June 2002 4:20 To: Multiple recipients of list ORACLE-L It was the alter system set current_schema=x statement after all. I am sure that this raises some interesting questions, if only I had time to dwell on it. Just now cleaning up all the broken indexes from the loads that abended when the db went down. I love this job. Where else do you get to play at work? jack --- Jack Silvey [EMAIL PROTECTED] wrote: All, thanks for the input. Looks like someone implemented a login trigger. haven't seen the code yet, but I would venture a guess he used the unsupported alter system set current_schema=x. sometimes you live and learn, sometimes you just live! thx, jack silvey --- Hately Mike [EMAIL PROTECTED] wrote: I don't hold out much hope here Jack. It sounds like data dictionary corruption; maybe somewhere round user$(?). That's not to say the situation's irretrievable; I've seen OTS fix some bad situations in my time but I'm not sure that I'd want to keep the database even if Oracle Support can fix the problem. Regards, Mike -Original Message- Sent: 12 June 2002 14:23 To: Multiple recipients of list ORACLE-L Listers, Our warehouse now has a split personality and we have a sev1 open on it. Suspect recovery is in the cards, but want to avoid if possible. Yesterday, users unable to get to their own functions. Soon after, RMAN cannot find package dbms_backup_restore, even though it exists under sys. Oncall ran the sql script to recreate - and the pacakge was recreated under a schema called dma_rbate2. RMAN now finds the package under dma_rbate2, although it is invalid. Drop the package under dma_rbate2, and now RMAN cannot find the package any longer, although it still exists under sys. Logged in as sys. Tried to desc dbms_backup_restore - no luck. Tried to desc sys.dbms_backup_restore - success. Analyst reccomends running catalog.sql. Oncall does so, and it creates as many packages as it is able under dma_rbate2. I get up this AM and can't login, because the sessions can't find the package dma_rbate2.dbms_application_info. Anyone? Buhler? Buhler? thx, jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
Re: Wait event problems
Lee, This is an idle wait event, meaning that the query process is waiting on instructions from the client. Usually this process is benign, but sometimes can indicate that the feeding process is not providing data in a timely fashion. hth, jack silvey --- Robertson Lee - lerobe [EMAIL PROTECTED] wrote: All, Oracle 8.0.5.0.0 Tru64 v4.0f We are running a job and statspack reports show that our only problem (it is running like a dog) is the following SQL*Net more data from client. Done some reading and still none the wiser. Anyone else had this sort of problem and if so how did you get around it ?? Regards Lee The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: set sort_area_size, sort_retained_size,hash_area_size but sti
Paula, what is the size of your hash_area_size, sort_area_size, and sort_area_retained_size? what does your explain plan look like? hashes, sort/merge, or nested loops? do you have a lot of parallel to parallel, parallel to serial in your plans? jack silvey --- [EMAIL PROTECTED] wrote: Have 12Gb RAM available , using parallel query with large mv joined to small code tables and setting session parameters to use Gb's of memory (have system to myself at the time) but system shows 12Gb RAM available still and writing to temporary segment - why or why or why? __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Wait event problems
Sorry, Tim is exactly right. Got this confused with the SQL*Net message from client event. Need to stop drinking more Budweiser and start drinking more Guiness. At least, that is what *I* am taking away from this experience. ;) jack --- Tim Gorman [EMAIL PROTECTED] wrote: This is never an idle event. The phrase more data from client indicates that the individual SQL operation is larger than a single SQL*Net packet. No big deal; it happens all the time, and SQL*Net handles it with continuation packets. Only issue is that the client is taking a lot of time between each packet sent. Jack's conclusion that the client process (in the client-server database connection) is not providing data in a timely fashion is exactly correct. You most likely have a slow client process... Oracle documentation frequently tries to encourage mucking about with SDU/TDU parameters in SQL*Net configuration files, but I've rarely seen this be more effective than tuning the client process... :-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 13, 2002 10:08 AM Lee, This is an idle wait event, meaning that the query process is waiting on instructions from the client. Usually this process is benign, but sometimes can indicate that the feeding process is not providing data in a timely fashion. hth, jack silvey --- Robertson Lee - lerobe [EMAIL PROTECTED] wrote: All, Oracle 8.0.5.0.0 Tru64 v4.0f We are running a job and statspack reports show that our only problem (it is running like a dog) is the following SQL*Net more data from client. Done some reading and still none the wiser. Anyone else had this sort of problem and if so how did you get around it ?? Regards Lee The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
warehouse gets very strange - sys corrupted - sev1 tar
Listers, Our warehouse now has a split personality and we have a sev1 open on it. Suspect recovery is in the cards, but want to avoid if possible. Yesterday, users unable to get to their own functions. Soon after, RMAN cannot find package dbms_backup_restore, even though it exists under sys. Oncall ran the sql script to recreate - and the pacakge was recreated under a schema called dma_rbate2. RMAN now finds the package under dma_rbate2, although it is invalid. Drop the package under dma_rbate2, and now RMAN cannot find the package any longer, although it still exists under sys. Logged in as sys. Tried to desc dbms_backup_restore - no luck. Tried to desc sys.dbms_backup_restore - success. Analyst reccomends running catalog.sql. Oncall does so, and it creates as many packages as it is able under dma_rbate2. I get up this AM and can't login, because the sessions can't find the package dma_rbate2.dbms_application_info. Anyone? Buhler? Buhler? thx, jack __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
sys corrupted in warehouse, sev1 tar open
Listers, Our warehouse now has a split personality and we have a sev1 open on it. Suspect recovery is in the cards, but want to avoid if possible. Yesterday, users unable to get to their own functions. Soon after, RMAN cannot find package dbms_backup_restore, even though it exists under sys. Oncall ran the sql script to recreate - and the pacakge was recreated under a schema called dma_rbate2. RMAN now finds the package under dma_rbate2, although it is invalid. Drop the package under dma_rbate2, and now RMAN cannot find the package any longer, although it still exists under sys. Logged in as sys. Tried to desc dbms_backup_restore - no luck. Tried to desc sys.dbms_backup_restore - success. Analyst reccomends running catalog.sql. Oncall does so, and it creates as many packages as it is able under dma_rbate2. I get up this AM and can't login, because the sessions can't find the package dma_rbate2.dbms_application_info. Anyone? Buhler? Buhler? thx, jack __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: sys corrupted in warehouse, sev1 tar open - resolved
All, thanks for the input. Looks like someone implemented a login trigger. haven't seen the code yet, but I would venture a guess he used the unsupported alter system set current_schema=x. sometimes you live and learn, sometimes you just live! thx, jack silvey --- Hately Mike [EMAIL PROTECTED] wrote: I don't hold out much hope here Jack. It sounds like data dictionary corruption; maybe somewhere round user$(?). That's not to say the situation's irretrievable; I've seen OTS fix some bad situations in my time but I'm not sure that I'd want to keep the database even if Oracle Support can fix the problem. Regards, Mike -Original Message- Sent: 12 June 2002 14:23 To: Multiple recipients of list ORACLE-L Listers, Our warehouse now has a split personality and we have a sev1 open on it. Suspect recovery is in the cards, but want to avoid if possible. Yesterday, users unable to get to their own functions. Soon after, RMAN cannot find package dbms_backup_restore, even though it exists under sys. Oncall ran the sql script to recreate - and the pacakge was recreated under a schema called dma_rbate2. RMAN now finds the package under dma_rbate2, although it is invalid. Drop the package under dma_rbate2, and now RMAN cannot find the package any longer, although it still exists under sys. Logged in as sys. Tried to desc dbms_backup_restore - no luck. Tried to desc sys.dbms_backup_restore - success. Analyst reccomends running catalog.sql. Oncall does so, and it creates as many packages as it is able under dma_rbate2. I get up this AM and can't login, because the sessions can't find the package dma_rbate2.dbms_application_info. Anyone? Buhler? Buhler? thx, jack This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: sys corrupted in warehouse, sev1 tar open - resolved
It was the alter system set current_schema=x
statement after all.
I am sure that this raises some interesting questions,
if only I had time to dwell on it. Just now cleaning
up all the broken indexes from the loads that abended
when the db went down.
I love this job. Where else do you get to play at
work?
jack
--- Jack Silvey [EMAIL PROTECTED] wrote:
All,
thanks for the input. Looks like someone
implemented
a
login trigger. haven't seen the code yet, but I
would
venture a guess he used the unsupported alter
system
set current_schema=x.
sometimes you live and learn, sometimes you just
live!
thx,
jack silvey
--- Hately Mike [EMAIL PROTECTED] wrote:
I don't hold out much hope here Jack.
It sounds like data dictionary corruption; maybe
somewhere round user$(?).
That's not to say the situation's irretrievable;
I've seen OTS fix some bad
situations in my time but I'm not sure that I'd
want
to keep the database
even if Oracle Support can fix the problem.
Regards,
Mike
-Original Message-
Sent: 12 June 2002 14:23
To: Multiple recipients of list ORACLE-L
Listers,
Our warehouse now has a split personality and we
have
a sev1 open on it. Suspect recovery is in the
cards,
but want to avoid if possible.
Yesterday, users unable to get to their own
functions.
Soon after, RMAN cannot find package
dbms_backup_restore, even though it exists under
sys.
Oncall ran the sql script to recreate - and the
pacakge was recreated under a schema called
dma_rbate2. RMAN now finds the package under
dma_rbate2, although it is invalid. Drop the
package
under dma_rbate2, and now RMAN cannot find the
package
any longer, although it still exists under sys.
Logged in as sys. Tried to desc
dbms_backup_restore
-
no luck. Tried to desc sys.dbms_backup_restore -
success.
Analyst reccomends running catalog.sql. Oncall
does
so, and it creates as many packages as it is
able
under dma_rbate2.
I get up this AM and can't login, because the
sessions
can't find the package
dma_rbate2.dbms_application_info.
Anyone? Buhler? Buhler?
thx,
jack
This email and any attached to it are
confidential
and intended only for the
individual or
entity to which it is addressed. If you are not
the
intended recipient,
please let us know
by telephoning or emailing the sender. You
should
also delete the email and
any attachment
from your systems and should not copy the email
or
any attachment or
disclose their content
to any other person or entity. The views
expressed
here are not necessarily
those of
Churchill Insurance Group plc or its affiliates
or
subsidiaries. Thank you.
Churchill Insurance Group plc. Company
Registration
Number - 2280426.
England.
Registered Office: Churchill Court, Westmoreland
Road, Bromley, Kent BR1
1DP.
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Hately Mike
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051
FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send
an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing:
UNSUB
ORACLE-L
(or the name of mailing list you want to be
removed
from). You may
also send the HELP command for other information
(like subscribing).
__
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: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051
FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be
removed
from). You may
also send the HELP command for other information
(like subscribing).
=
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
Some days you're the pigeon, some days you're the
statue
__
Do You Yahoo!?
=== message
Re: URGENT: Vendor App locked me out of my Prod DB Pls Advise
smack a couple of those sessions at the UNIX level and login right quick. make sure beforehand that this will not lead to data corruption or anything nasty like that. and if it does, have a heart to heart with your manager about this app. hth, jack silvey --- [EMAIL PROTECTED] wrote: I third party replication production spurred a runaway process on my target db and it has generated mulitple sessions. Now all my sessions are used and I cannot log into the db to kill the vendor sessions. Help. My log on attempts hang. __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ldap info
my last gig was senior DBA at an internet travel shop, and we used LDAP to authenticate users. Since it is really just a glorified list sitting in memory, it is perfectly suited to small, straightforward uses such as this. We did not use oracle LDAP, but another vendor that I can't remember. The LDAP was accessed by the Java processes and gathered the password and username from it to login to the database. hth, jack silvey --- Rachel Carmichael [EMAIL PROTECTED] wrote: One of the users here came to me to talk about setting up user authentication and wanted to know what I knew about LDAP and Oracle. since the sum total of my knowledge on the subject consists of Oracle Internet Directory is a pain to set up I thought I might ask for advice from some who are more familiar with the subject. We will have something on the order of a million hits a day, this is definitely ONLY user authentication. We are not wedded to the idea that it has to be part of Oracle, if there is another, better (easier/stable) way to handle this, we are open to using that. Any suggestions on places to look? A search on LDAP in the 9.2 docs got me over 1300 matches... a bit overwhelming! I'm going to be reading through the OID admin manual, but that's somewhat prejudiced towards Oracle :) Rachel __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ldap info
free is good, especially when it comes to ldap and beer. ;) jack --- Rachel Carmichael [EMAIL PROTECTED] wrote: thanks! that's pretty much the way we want to use it as well, for a universal login across any of our websites. Sun has an LDAP that we are looking at as well. The plus is, it's free. and we don't need another oracle license etc etc etc and even better, it wouldn't be MY responsibility to maintain :) Rachel --- Jack Silvey [EMAIL PROTECTED] wrote: my last gig was senior DBA at an internet travel shop, and we used LDAP to authenticate users. Since it is really just a glorified list sitting in memory, it is perfectly suited to small, straightforward uses such as this. We did not use oracle LDAP, but another vendor that I can't remember. The LDAP was accessed by the Java processes and gathered the password and username from it to login to the database. hth, jack silvey --- Rachel Carmichael [EMAIL PROTECTED] wrote: One of the users here came to me to talk about setting up user authentication and wanted to know what I knew about LDAP and Oracle. since the sum total of my knowledge on the subject consists of Oracle Internet Directory is a pain to set up I thought I might ask for advice from some who are more familiar with the subject. We will have something on the order of a million hits a day, this is definitely ONLY user authentication. We are not wedded to the idea that it has to be part of Oracle, if there is another, better (easier/stable) way to handle this, we are open to using that. Any suggestions on places to look? A search on LDAP in the 9.2 docs got me over 1300 matches... a bit overwhelming! I'm going to be reading through the OID admin manual, but that's somewhat prejudiced towards Oracle :) Rachel __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from
RE: ldap info
XXtra online, owner of powertrip and MyTrip. Yet another failed dotcom in the vast wasteland of free money and no management to speak of. --- Larry Elkins [EMAIL PROTECTED] wrote: Jack, What was the internet travel company? Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 my last gig was senior DBA at an internet travel shop, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: buffer cache
p,
this query will tell you how many blocks are in cache
for each segment. multiply by your db_block_size and
divide that number by 1048576 to get megabytes.
remember that full table scan blocks get put on the
LRU end of the LRU list (pre-816) so they might not be
represented fairly by this query.
hth,
jack silvey
select
e.owner||'.'||e.segment_name segment_name,
sum(cnt) all_buffers,
sum(hot) hot_buffers,
sum(tch) touches
from
( select
min(file#||'.'||dbablk) fb,
count(*) cnt,
sum(decode(lru_flag, 8, 1, 0)) hot,
sum(tch) tch
from
sys.x_$bh
where
inst_id = userenv('Instance') and
state in (1, 3)
group by
obj,
class
) b,
sys.apt_extents e
where
e.file_id = substr(b.fb, 1, instr(b.fb, '.') - 1)
and
substr(b.fb, instr(b.fb, '.') + 1) between
e.block_id and e.block_id + e.blocks - 1
group by
e.owner||'.'||e.segment_name
order by
2
/
--- BigP [EMAIL PROTECTED] wrote:
Hi Guys ,
IS there any way I can find what is occupying how
much of buffer cache . Like .. what table is taking
most of space etc .
Thanks ,
Bp
__
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: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
grant access to another user's objects?
All, We have a new info security group that is going to do all the object grants on our warehouse. I don't want them to login as schema owners to do this. Until this time, I have been granting access to other user's object by logging in as a dba, creating a procedure in the owner's schema with the EXECUTE immediate statement, and passing it the 'grant select on table a' statement for execution. That way, the grant actually executes as the object owner, but can be issued from a DBA account. Short of having a custom SP in each user's schema for this type of grant, can anyone think of another way? thx, jack silvey __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: using a lot of temporary tablespace with large sort area size
Paula, With so much data, Oralce (or Oracle, if you prefer) might be hash joining your tables and writing temp hash segments. What is your hash_area_size? jack silvey --- [EMAIL PROTECTED] wrote: Guys, Have 20Gb and 16CPUS available on host. Need to do large full-table scans/joins to create materialized view. Since I have to do the full-table scans of large tables - decided to use parallel query option. Eliminated significant I/O contention by using DIRECT IO. Using very very large sort_area_size, however, still writing out significant segments to temporary tablespace which doesn't make sense to me. Any ideas? __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mysterious Deadlock
Walter, we had a situation where our pctfree was 0, our initrans was 4, and we were trying to update the table using 8 concurrent processes. Since we had no space to grow, our ITL could not expand, and some of the processes deadlocked with a similiar error. What is your pctfree and initrans? how many procs are trying to insert/delete/update it at once? are ALL dying, or just some? jack silvey --- Walter K [EMAIL PROTECTED] wrote: We have an application that is periodically encountering what appears to be a self-deadlock. Only one session is listed and it holds an exclusive (X) lock and is waiting for a share (S) lock with NO ROWS waited. I have pasted the deadlock graph at the bottom- -hopefully it will be legible. Does anyone know how such a lock could be produced? I'd really like to be able to recreate such a scenario. Thanks! -w Current SQL statement for this session: update ACCOUNT set ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_ MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE _ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTIO N_DATE=:ACCOUNT_ACTION_DATE where ACCOUNT_ID=:key_ACCOUNT_ID The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: -Blocker(s) --- --Waiter(s)- Resource Name process session holds waits process session holds waits TX-0007004c-26bf34 95 X 34 95 S session 95: DID 0001-0025-0002E096 session 95: DID 0001-0025-0002E096 Rows waited on: Session 95: no row -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mysterious Deadlock
Seems that the no rows message was the identifying criteria of our problem, however, I have both slept and drank since then. Not at the same time, of course. Well, maybe a little overlap. How about your indexes - initrans? pctfree? any bitmap indexes involved? --- Walter K [EMAIL PROTECTED] wrote: Jack, Thanks for the reply. The table is defined as: INI_TRANS = 1 PCT_FREE = 10 I meant to mention in my original posting that the platform is Solaris 8, Oracle 8.1.7.0/32-bit. I don't know what the exact number of concurrent transactions is, probably no more than a few (1-3). The deadlock does not occur all the time. What's even stranger, is that the error can show up in the alert log at different times yet with the SAME pid for the trace file name--for example, today might be prod_ora_12345.trc showing up at different times in the alert log and next week Monday it might show prod_ora_67890.trc multiple times. Did your deadlocks show up as no rows and only one line (S waiting for X) in the deadlock graph? Thanks again. -w --- Original Message --- To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Walter, we had a situation where our pctfree was 0, our initrans was 4, and we were trying to update the table using 8 concurrent processes. Since we had no space to grow, our ITL could not expand, and some of the processes deadlocked with a similiar error. What is your pctfree and initrans? how many procs are trying to insert/delete/update it at once? are ALL dying, or just some? jack silvey --- Walter K [EMAIL PROTECTED] wrote: We have an application that is periodically encountering what appears to be a self-deadlock. Only one session is listed and it holds an exclusive (X) lock and is waiting for a share (S) lock with NO ROWS waited. I have pasted the deadlock graph at the bottom- -hopefully it will be legible. Does anyone know how such a lock could be produced? I'd really like to be able to recreate such a scenario. Thanks! -w Current SQL statement for this session: update ACCOUNT set ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST _ MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NOD E _ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=: A CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=: I NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRE D IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUN T ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTI O N_DATE=:ACCOUNT_ACTION_DATE where ACCOUNT_ID=:key_ACCOUNT_ID The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: -Blocker(s) --- --Waiter(s)- Resource Name process session holds waits process session holds waits TX-0007004c-26bf34 95 X 34 95 S session 95: DID 0001-0025-0002E096 session 95: DID 0001-0025-0002E096 Rows waited on: Session 95: no row -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- -- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You
unable to split a partition in parallel
Listers,
We have a table range partitioned on a date column.
Last night I tried to split the earliest partition
into itself and an earlier (empty) partition in
parallel. The partition is ~ 25 gigs. This ran
overnight and did not finish.
Here is the statement:
alter table dwcorp.t_claim_alv
split partition p_200107
at (TO_DATE(' 2001-07-01 00:00:00', 'S-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
into (partition p_200106, partition p_200107)
parallel(Degree 12)
/
A quick check of waits showed that the processes were
active and not waiting. A check of the tablespace
showed no temp segments being written there by the
parallel processes.
I removed the parallelism clause and the statement
immediately started writing temp segs that were
growing. This ran for 5 hours and was less than 1/2
done, so we need the PQ to work since the split will
break indexes and remove stats on the split partitions
and we can't have THAT in the middle of the day.
Tried these things to rectify:
1) created empty table with same structure and indexes
in a temp schema, parallel partition split worked.
2) altered the partition to nologging
3) altered the pq of the partition to match that of
the statement
4) ran as both DBA and schema owner
5) removed the leading space from the partition clause
(this is a historical design flaw, might be time to
rectify)
Since the empty table worked, this is probably either
a problem unique to this table or related to the data.
My first thought is that the pq process co-ordinator
is unable to resolve the partition key adequately and
so is unable to properly handoff the required
information to the child pq procs, so they are active
but cannot proceed. This would explain why they write
no temp segs but are active. I would expect to see pq
enqueue waits of some sort, however.
Otherwise, perhaps the pq procs cannot write, but have
never had problems with our PQ procs writing before
and have done many parallel CTAS to this tablespace.
thanks,
jack silvey
__
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: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
custom DD views to allow users to see source without needing exe rights
Good afternoon co-listers, Recently we had a problem with TOAD and I thought I would share our solution. TOAD looks at the views ALL_ARGUMENTS and ALL_OBJECTS to see procedural code. Unless a user has the ability to execute a package/procedure/function, they cannot see the source code through these views, and can't see the source in TOAD. This limitiation is hard-coded in the view structure. Upon reflection, it occured to me that I could recreate these views in the users' schema, customized to remove the necessity of having execute priv to see the code, and since Oracle looks local first during object name resolution, it would probably use these views instead of the data dictionary views. This worked. The two views that I customized are below - feel free to use. jack silvey ALL_ARGUMENTS: select u.name owner, /* OWNER */ nvl(a.procedure$,o.name) object_name, /* OBJECT_NAME */ decode(a.procedure$,null,null, o.name) package_name, /*PACKAGE_NAME */ o.obj# object_id, /* OBJECT_ID */ decode(a.overload#,0,null,a.overload#) overload, /*OVERLOAD */ a.argument argument_name, /* ARGUMENT_NAME */ a.position# position, /* POSITION */ a.sequence# sequence, /* SEQUENCE */ a.level# data_level, /* DATA_LEVEL */ decode(a.type#, /* DATA_TYPE */ 0, null, 1, decode(a.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(a.scale, -127, 'FLOAT', 'NUMBER'), 3, 'NATIVE INTEGER', 8, 'LONG', 9, decode(a.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 11, 'ROWID', 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 29, 'BINARY_INTEGER', 69, 'ROWID', 96, decode(a.charsetform, 2, 'NCHAR', 'CHAR'), 102, 'REF CURSOR', 104, 'UROWID', 105, 'MLSLABEL', 106, 'MLSLABEL', 110, 'REF', 111, 'REF', 112, decode(a.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 121, 'OBJECT', 122, 'TABLE', 123, 'VARRAY', 178, 'TIME', 179, 'TIME WITH TIME ZONE', 180, 'TIMESTAMP', 181, 'TIMESTAMP WITH TIME ZONE', 231, 'TIMESTAMP WITH LOCAL TIME ZONE', 182, 'INTERVAL YEAR TO MONTH', 183, 'INTERVAL DAY TO SECOND', 250, 'PL/SQL RECORD', 251, 'PL/SQL TABLE', 252, 'PL/SQL BOOLEAN', 'UNDEFINED') data_type, default$ default_value, /* DEFAULT_VALUE */ deflength default_length, /* DEFAULT_LENGTH */ decode(in_out,null,'IN',1,'OUT',2,'IN/OUT','Undefi ned') in_out, /* IN_OUT */ length data_length, /* DATA_LENGTH */ precision# data_precision, /* DATA_PRECISION */ scale data_scale, /* DATA_SCALE */ radix radix, /* RADIX */ decode(a.charsetform, 1, 'CHAR_CS', /* CHARACTER_SET_NAME */ 2, 'NCHAR_CS', 3, NLS_CHARSET_NAME(a.charsetid), 4, 'ARG:'||a.charsetid) char_cs, a.type_owner type_owner, /* TYPE_OWNER */ a.type_name type_name, /* TYPE_NAME */ a.type_subname type_subname, /* TYPE_SUBNAME */ a.type_linkname type_link, /* TYPE_LINK */ a.pls_type pls_type /* PLS_TYPE */ from sys.obj$ o,sys.argument$ a,sys.user$ u where o.obj# = a.obj# and o.owner# = u.user# ALL_OBJECTS: select u.name owner, o.name object_name, o.subname subobject_name, o.obj# object_id, o.dataobj# data_object_id, decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 39, 'LOB PARTITION', 40, 'LOB SUBPARTITION', 43, 'DIMENSION', 44, 'CONTEXT', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA', 'UNDEFINED') object_type, o.ctime created, o.mtime last_ddl_time, to_char(o.stime, '-MM-DD:HH24:MI:SS') timestamp, decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID') status, decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N') temporary, decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N') generated, decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N') secondary from sys.obj$ o, sys.user$ u where o.owner# = u.user# and o.linkname is null and (o.type# not in (1 /* INDEX - handled below */, 10 /* NON-EXISTENT */) or (o.type# = 1 and 1 = (select 1 from sys.ind$ i where i.obj# = o.obj# and i.type# in (1, 2, 3, 4, 6, 7, 9 and o.name != '_NEXT_OBJECT' and o.name != '_default_auditing_options_' union all select u.name, l.name, NULL, to_number(null), to_number(null), 'DATABASE LINK', l.ctime, to_date(null), NULL, 'VALID','N','N', 'N' from sys.link$ l, sys.user$ u where l.owner# = u.user# __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: partition tables
Big (or should we just call you P?), I have become somewhat experienced at manipulating large partition tables since I have had to do it so often (can you say poor initial design?). Export / import is not the fastest way to go. Here are some tips from the trenches: 1) You mention that each partition should have only 100,000 rows, but it might be beneficial for you to focus more on the partition key. If you choose the right partition key, queries will be able to do partition pruning, where they can look at the data dictionary and see that they only want to look at certain partitions and not others. This is the big win for partitioned tables - remove as much data from the initial lookup as possible by skipping partitions. 2) Investigate create table as select with the nologging option, in combination with the partition exchange option. Let's assume that you want a partition table with 10 partitions. You can create an empty partition table with 10 partitions, CTAS 10 new tables from your original table, and exchange the partitions. After this, you will have a partitition table full of data. Ain't it cool. 3) If you are sure of your data integrity, use the without validation clause of the partition exchange. Otherwise, Oracle will look at each and every row in each and every partition when it is swapped in - really slows things down. 4) Another way of creating a partition table from a standalone is to create the empty partition table and do a insert /*+ nologging append parallel(a,12) */ into tablea a select /*+ parallel (b,12) /* from tableb b; and this will spawn off 12 parallel processes for the select, 12 parallel processes for the insert, use almost no rollback (appends the data) and use almost no logging. This screams like a banshee, very fast. Remember, *each* pq process will write to its own extent, size your extents accordingly. 5) Create bitmap partitioned indexes on your low cardinality join columns (look at number of distinct values / number of rows) - make sure and set your sort_area_size wayy high (and set your sort_area_retained_size to the same value - bug in oracle with the two not being equal throwing a -600 error) but remember - *each* pq process gets its own sort_area_size - don't run the box out of ram. 6) Don't create the indexes before you load - this will fragment them and slow down your insert. 7) Remember to set your parallelism on your table back to a reasonable level if you CTAS with pq - otherwise, a high parallelism level on the table will tend to make Oracle favor full table scans and hash or sort joins over nested loops and index lookups. Same goes with indexes - more PQ favors full index scans. 8) You can analyze all your partitions separate from each other, and in tandem if you wish. 9) alter your index partitions unusable before you load and then rebuild those partitions with the compute statistics clause - this is faster and optimizes your indexes. Bitmap indexes do not like to be up while loading. Be advised, if someone tries to query this table and they don't have skip_unusable_indexes=true set in their session, they will get an error. One way to set this parameter in every session is to include it in a logon trigger using execute immediate 'alter session set skip_unusable_indexes=true' - HOWEVER, this will change their execution plan to favor FTS since the index is *not available*. Use caution. 10) Create and rebuild your indexes in parallel. 11) Use a MAXVALUE partition - this will allow you to load all data and catch that data that falls outside the other partition ranges. If you don't have a maxvalue partition, and you try to insert a row that does not match the other partitions, you will get inserted value beyond highest legal partition key and your insert will fail and might stop your load. You can always split the MAXVALUE partition later. 12) Put all your table partitions in one tablespace and all your index partitions in another single tablespace (each suitably striped, of course, and respecting recovery plans.) This will allow you to automate partition management (addition of new partitions and dropping of old) if you need to and manage your tablespace space more effectively. 13) Use the monitoring option on your partitions - not all partitions change enough to be analyzed each time necessarily, and this will tell you which ones need it. 14) *do not* create your table with pctfree = 0 and *do* create with healthy initrans value (we use 8). If you have initrans of 2 and pctfree of 0, and you try to insert/update the table with 3 or more parallel processes, the ITL table (the thing that the processes register with when they use the table) cannot grow (no space with pctfree 0) and the extra processes will either wait or fail with a deadlock error. To change pctfree you will have to rebuild the table. hth, jack silvey -Original Message- Sent: Thursday, May 30, 2002 8:59 PM To: Multiple recipients of list ORACLE-L Hi
Re: How to grant privileges on all the tables of owner1 to owner2?
try something like this:
set serveroutput on size 100;
declare
v_sql varchar2(4000);
cursor c_cur is select table_name from user_tables;
begin
begin
for v_cur in c_cur loop
v_sql = 'grant select,insert,update,delete on
'||v_cur.table_name||' to owner2';
dbms_output.put_line('did '||v_sql);
execute immediate v_sql;
exception
when others then
dbms_output.put_line ('problem with '||v_sql);
end;
end loop;
end;
/
jack silvey
--- Mandal, Ashoke [EMAIL PROTECTED]
wrote:
Greetings,
Here is the scenario.
I have 2 users(owner1, owner2) in an oracle
database.
owner1 owns 150 tables.
owner2 needs select,insert,update,delete privilege
on all the tables owned by owner1.
One option is : login as owner1 and
grant select,insert,update,delete on owner1.table1
to owner2;
.
.
.
grant select,insert,update,delete on owner1.table150
to owner2;
I was wondering if there is any way I can perform
the same work using one sql statement instead of
using 150 statements.
Thanks,
Ashoke
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Mandal, Ashoke
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(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! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: rebuilding indexes
Matt, 1) Storage parameter changes? Do you specify storage at the index or tablespace (or top partition) level?pctfree go up? initrans go up? bigger INITIAL or NEXT? 2) did you build it the first time using parallelism and rebuild using single thread? When a PQ processes is used to build an object it uses its own extent, and then *deallocates the extra space* at the end. If you will notice, when you build using PQ your segments have wacko sizes, and that is why. If you rebuilt using a single process, it could be that you have a full allocation of space, some empty. 3) more data? did you alter unusable, load, and rebuild? 4) have you changed the minimize_records_per_block parameter and the index is a bitmap? 5) has your data distribution changed? bitmap indexes tend to get bigger as your data moves towards high cardinality. 6) has this index been eating too much sugar again? sometimes, this can cause an index glucose spike and tend to make it store more fat in the leaf blocks, especially around the middle of the index, but sometimes on the hips too. Perhaps you are not doing DDL on the table enough, and so the index is not getting enough exercise. hth, jack silvey --- Adams, Matthew (GEA, MABG, 088130) [EMAIL PROTECTED] wrote: Under what conditions would an 'alter index .. rebuild' actually case the size of the index to increase by about 12 percent? Matt Adams - GE Appliances - [EMAIL PROTECTED] Contrary to popular opinion, Unix is user friendly. It's just particular about who it makes friends with. __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jr.DBA, Mid level DBA, Sr.DBA
The jr thinks that she knows. The mid knows that she knows. The sr knows that she knows not. Awareness of ignorance is the mark of true knowledge. I like cake. jack silvey --- Fink, Dan [EMAIL PROTECTED] wrote: I agree, the Jr. DBA must focus on learning. Mid DBA...is still learning. Many Mid still view tuning/troubleshooting as an art (with a little magic thrown in) Sr. DBA...is still learning. Realizes that database management is a science, requiring research, expirementation and a very healthy dose of skepticism. The best Sr. DBAs that I know are the first ones to say 'I don't know'. That is the only true path to learning. No one can know everything. Often times the Jr. DBA will be a great source of knowledge since they don't know what NOT to ask. Reaction to reading Books/Documentation Junior - I did not know that Mid - I know that Senior - Perhaps...let's prove it When a developer/user asks for a change to the database Junior - I'll look it up and change it Mid - I have a script to do that, I'll let you know when I am done Senior - Why are you needing this change? Did you realize that x will cause y? Let's figure out the best way to accomplish the result. When faced with an undocumented condition/unknown error Junior - Log a TAR, get frustrated with 'We need a trace file. We need more information. We have no clue...'. Calls more senior help. Mid - Remembers a passage in a book, tries out the command. Fixes the symptom. Senior - Knows that x can cause y, if z is present. Tracks condition from symptom through to actual problem. Attends sessions at IOUG Junior - Assumes that all speakers know exactly what they are talking about and all vendor tools work as advertised. Mid - Listens to and believes Tim, Cary, Craig, Rich, Rachel, Gaja and all other High Holy Oracle Gurus preach Senior - Listens to, questions and tests (on non production systems) what Tim, Cary, Craig, Rich, Rachel, Gaja and all other High Holy Oracle Gurus preach Knowledge level Junior - Has no clue what they know and don't know Mid - Knows what they know Senior - Knows what they don't know Every Senior DBA is a mix of Jr. and Mid. They may know a great deal about one subsystem of Oracle, but lack knowledge in another area. Daniel W. Fink Sr. Oracle DBA MICROMEDEX 303.486.6456 -Original Message- Sent: Friday, May 31, 2002 2:43 AM To: Multiple recipients of list ORACLE-L Junior DBA's job is a learning. Mid DBA's job is a science. Sr. DBA's job is the Art. Srs feel database, users, developers and everything else. They feel what, where, how, when and why should by done. Their intuition is of high degree. ... and everybody is sure - the Sr DBA knows everything. (so one of the priority of Sr DBA is to make this impression) -- Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). i -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
Re: rebuilding indexes
Lose 20 blocks in 10 days! Load as much as you normally do and still lose index width. no segment size restrictions or tiring delete routines that never seem to finish. easy-to-follow substr(column,1,1) update routine that guarentees a maximum of data loss and a minimum of storage! email to [EMAIL PROTECTED] for details. --- Rachel Carmichael [EMAIL PROTECTED] wrote: I'm sure it's #6 :) --- Jack Silvey [EMAIL PROTECTED] wrote: Matt, 1) Storage parameter changes? Do you specify storage at the index or tablespace (or top partition) level?pctfree go up? initrans go up? bigger INITIAL or NEXT? 2) did you build it the first time using parallelism and rebuild using single thread? When a PQ processes is used to build an object it uses its own extent, and then *deallocates the extra space* at the end. If you will notice, when you build using PQ your segments have wacko sizes, and that is why. If you rebuilt using a single process, it could be that you have a full allocation of space, some empty. 3) more data? did you alter unusable, load, and rebuild? 4) have you changed the minimize_records_per_block parameter and the index is a bitmap? 5) has your data distribution changed? bitmap indexes tend to get bigger as your data moves towards high cardinality. 6) has this index been eating too much sugar again? sometimes, this can cause an index glucose spike and tend to make it store more fat in the leaf blocks, especially around the middle of the index, but sometimes on the hips too. Perhaps you are not doing DDL on the table enough, and so the index is not getting enough exercise. hth, jack silvey --- Adams, Matthew (GEA, MABG, 088130) [EMAIL PROTECTED] wrote: Under what conditions would an 'alter index .. rebuild' actually case the size of the index to increase by about 12 percent? Matt Adams - GE Appliances - [EMAIL PROTECTED] Contrary to popular opinion, Unix is user friendly. It's just particular about who it makes friends with. __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat
Re: UPGRADE
I second that emotion. Write your plan down and think it through. Do some research about what problems you will encounter. Decide how to backout in the event that the data dictionary goes poof or all the datafile headers become inconsistent. Script and test your upgrade on another system if possible - there are usually gotchas that don't show up until testing. Remember, glory fades quickly but screw-ups are remembered forever. --- Rachel Carmichael [EMAIL PROTECTED] wrote: don't do it this afternoon... read the release notes, check for any known bugs on Metalink and PLAN what you are going to do, including backout of the upgrade if necessary. upgrades are not not something you do on the spur of the moment. of course if you decide to do this anyway and it breaks, please don't send URGENT NEED HELP messages to the list. --- Hamid Alavi [EMAIL PROTECTED] wrote: Hi List, I have decided to Upgrade the database from 8.1.7.0 to 8.1.7.4 this afternoon on sun solaris. Please give me any advise before I go for upgrade. Thanks allot and have a good weekend Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Undocumented Parameters
Lee, Here ya go. Remember, be *careful* with those hidden params, cowboy. Oracle does not support changing them, and you can really hose the system with em, perhaps beyond the normal capacity to repair it. As they say, this script is unsupported and for educational purposes only, but can come in handy on occasion. Did I say be careful? Might want to buff up the resume before you start acting like the Mad Hidden Parameter Scientist. ;) jack silvey -- -- -- author: jack silvey -- about: shows hidden and non-hidden parameters -- usually must connect as sys -- usage: @params -- -- -- column name format a45 column description format a70 column value format a20 set lines 145 set pages select nam.ksppinm name, val.KSPPSTVL value fromx$ksppi nam, x$ksppsvval where nam.indx = val.indx order by 1 / --- Robertson Lee - lerobe [EMAIL PROTECTED] wrote: Someone posted a bit of SQL to list out the undocumented parameters and guess what ... I deleted it. Could someone repost please ?? TIA Lee The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Statistical sampling and representative stats collection
tom, This is interesting. How did you determine max distribution and uniform distribution? Did you use standard deviation and variance? regards, jack silvey --- Terrian, Tom [EMAIL PROTECTED] wrote: John, I know in a previous job, we determined that histograms where not worth it. The following is from a test that we performed: *** Table-F_tab Uniform DistributionMax Distribution Field-P_code 0.65% 18% Therefore, from the above numbers, the field should be a good candidate for histograms so I did the following tests. Based on the following combinations of statistics and histograms, I timed how fast a sample query ran: w/o stats w/ statsw/stats w/stats P_Codeno histograms 100 buckets 50 buckets ---- -- -- -- 0101 342 secs. 428 385 500 0101 406 416 326 340 0101 391 390 327 359 6501 458 490 337 342 6501 475 380 358 490 6501 518 395 326 354 --- -- -- -- Total Secs. 1730162913482085 (w/o high and low values) Avg time 7Min 12Sec 6Min 47Sec 5Min 37Sec 5Min 51Sec per run However, to create the histogram it takes 1hr42min. Too long for the benefit that we gain. *** Tom -Original Message- Sent: Tuesday, May 28, 2002 3:25 PM To: Multiple recipients of list ORACLE-L Ian, John are you saying to create histograms on all indexed columns, or just the ones with distributions which are skewed and also for ones which although symmetric in distribution have some values much more prevalent than others? To keep this simplistic, I wouldn't use Histograms (or let it default to 2) *unless* hardcoded values are known to be used, at least in 8i. The situation becomes different in 9i as the CBO is able to peek into these values even when bind variables are used. (I think there is a script out there on Steve Adam's site called 'Histogram Helper' which can suggest this for you). However, as Larry mentioned in a previous email, the CBO is influenced by distributions in non-indexed colummns. The issue here is that the number of buckets really matter, and the default of 2 can influence incorrect decisions (haven't we all seen 'em? :) So what I am essentially saying is this: Use COMPUTE and Histograms when you have to, but don't sweat over it unless it pinches ya. And how do we determine it is pinching? V$SYSSTAT is a pretty good indicator: (At the risk of being called a part of the 'ratios' group) Is the ratio of 'table scan blocks gotten' to 'table scan rows gotten' acceptable? Is the number of table scans acceptable? Is the number of 'db block gets' too much - as compared to 'physical reads'? I am in the process of determining the overheads of having 'too many' histograms - I am observing some 'row cache lock' latch waits and think that this could have been the result of too many histograms. Hope to post some info back to the list soon. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Terrian, Tom INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
parallel dml and set transaction quirk
All, Just had an interesting thing happen that I though I would share. Moving data between two 25 gig tables. Here is the script I used: #!/bin/ksh echo 'start of ALV Copy' sqlplus -S EOF ax00332/pwd set transaction use rollback segment batch_rbs; alter session enable parallel dml; set serveroutput on size 100; set timing on; insert /*+ append parallel(b,12) */ into dwcorp.$1_new b select /*+ parallel(a,12) */ * from dwcorp.$1 a; commit; EOF This process showed 12 pq procs doing a select and waiting on pq: send blocked event, and one process doing the insert and waiting on file open event. These waits persisted for 15 minutes before I killed the processes. The problem was that the enable parallel dml statement has to be the first statement and cannot come after the set transaction statement. Once I put the DML statement before the transaction statement, the query spawned off 24 processes and finished in 16 minutes. /jack silvey __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Bitmap vs btree - how to tell? how to test?
Listers, We are creating indexes on a 300m row fact table today. I am researching bitmap versus btree (again) to see where the latest info points. Anyone care to comment on the way to determine whether an index should be a btree or a bitmap? Anyone even know a good way to test this? thx, /jack __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Data Warehouse experts, a simple question for you| Outdated?
Is there such a thing as being TOO knowledgable and well-read? I don't think so! /jack Dennis, I have on my desk, all in varying stages of being read: Inmon's book Building the Data Warehouse (very understandable) Kimball's articles from his site and from the Intelligententerprise.com site (somewhat understandable, I think you need a base from which to read his articles). His books are on order and should arrive today Tim Gorman's book Essential Oracle8i Data Warehousing (this I haven't started, as Tim tells me to read it AFTER I have a basic understanding of data warehousing) The Oracle8i Data Warehousing documentation (actually pretty readable and understandable) Ya think I might be over-researching this stuff and panicking a bit? Rachel --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Ian, - In the beginning was the data warehouse and yeah it was good. It would solve all corporate problems and would encompass all corporate data so all corporate minions would see the same data. - But yeah it took so long to create the corporate data warehouse that management despaired and canceled the project. Or by the time the monster data warehouse came blinking and straining into the daylight all the users said that the company had evolved in the meanwhile and the warehouse was obsolete. - So data warehouses gained a bad rep from corporate managers and yeah none would fain to propose the conception of a data warehouse for fear of castigation. - Then some marketing interns bribed a DBA to send them data weekly. And they stored this data in a database and lo, their superiors were impressed. - Everyone was in awe of the marketing database, but none dared tarnish it by speaking the name which shall not be mentioned, so it was christened a data mart. - And lo, the data marts multiplied and were fruitful. And the DBA cursed the day she was weak and did give data to the marketing interns. - Then another prophet did arise and did challenge the prophet Kimball. His name was Inmon. And he did claim to be the progenitor of data warehouses. And therefore all should do data warehousing his way and use his terms. - And great confusion arose over the land. And many debates ensued, including some face to face between Inmon and Kimball. And terms such as Operational Data Store (ODS) were bandied about. - And some said that queries against the ODS were acceptable and others deemed them forbidden. And some said that if it looks like a data warehouse and smells like a data warehouse it verily indeed is a data warehouse. - And consultants warred against consultants and did call the other consultants ignoramuses in front of management such that nobody knew what anybody was talking about. - And the DBAs said that creating a data warehouse or data mart was not nearly as hard as figuring out what to call it. The moral of the story is to figure out what you need to do and be aware that different authors use the same terms for different purposes and coin their own terms. Personally, I have understood everything that Kimball has written and have never been able to read one of Inmon's articles to the end. But maybe that is just me. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 22, 2002 2:38 PM To: Multiple recipients of list ORACLE-L Outdated? Ian, Good question. I think that I've seen more recenct references in articles that state the current thinking of DW/DM. I'm sure that I've seen Inmon refer to them that way, or maybe it was Richard Winter? Anyway, I guess that part is a bit dated. There is so much good information in that book though, that it's still worth its weight in gold. You won't find too many publications for $60 that will take you step by step through building an entire data warehouse, including the infrastructure. Jared MacGregor, Ian A. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/21/2002 05:48 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Warehouse experts, a simple question for you| Outdated? I am new to his books, three chapters in. The first release of the Data Warehouse Toolkit defines a data warehouse much as a data mart is today. Today we think of a data warehouse as having a highly normalized === message truncated === __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services
RE: Statistical sampling and representative stats collection
and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Jack Silvey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 21, 2002 2:44 PM To: Multiple recipients of list ORACLE-L Subject: Statistical sampling and representative stats collection Hi all, Did some investigation about statistical sampling this weekend since we are going to optimize our analyze process soon, and would like some input from all you orabrains on this one. I opened a TAR with Oracle asking about the sampling algorithm of stats collection, and they assured me it was random. The goal of analyze...estimate is to collect stats that are representative of the data population as a whole using a given sample set. Since analyzing tables takes up resources (does sorts to order the data for investigation) the fewer rows you use in estimate, the less system resources you use and the faster the analyze will go. Since our goal is to get as small a sample as possible and still have stats that are representative, my contention is that we could start by finding what the margin of error will be for each sample size and gauge our tolerance for it. One standard way to calculate margin of error for a given sample is by using this formula: M = 1/SQRT(N) where: M = margin of error N=sample size So, if we can tolerate stats that have a 1% a margin of error (will deviate from representative of the whole population by 1%), our sample size should be 10,000 rows. Also, a corollary (not a toyota corollary, though) to this would be that the more rows you add to your === message truncated === __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Data Warehouse experts, a simple question for you| Outdated?
. - And the DBAs said that creating a data warehouse or data mart was not nearly as hard as figuring out what to call it. The moral of the story is to figure out what you need to do and be aware that different authors use the same terms for different purposes and coin their own terms. Personally, I have understood everything that === message truncated === __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Statistical sampling and representative stats collection
Cherie, Had some problems with DBMS_STATS, but we are making our way towards it. Just have to be careful, since it can do quirky things like analyze the dictionary. We are using dba_tab_modifications to monitor our tables. Thanks for the input. Jack --- [EMAIL PROTECTED] wrote: Jack, What version are you on? Are you able to utilize the gather_stale option. That way you would not only be optimizing the amount you are estimating but the interval between analyzing. Of course, that option of DBMS_STATS is not available on older versions. Cherie Machler Oracle DBA Gelco Information Network Jack Silvey jack_silvey@y To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ahoo.comcc: Sent by: Subject: Re: Statistical sampling and representative stats [EMAIL PROTECTED]collection om 05/21/02 06:19 PM Please respond to ORACLE-L Hi Rafiq, We have been using 35 percent on our warehouse, even on our fact partitions. Now that I have thought about it for a while, that seems like a lot given the volume of data. If a representative sample can be gathered with 10,000 or 50,000 or 100,000 rows, and our fact partitions have millions of rows each, seems like we could go 1% on our analyze and it would be within acceptable tolerances. /jack --- Mohammad Rafiq [EMAIL PROTECTED] wrote: The most of the list memeber agrees on estimate with 30% Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 21 May 2002 13:43:33 -0800 Hi all, Did some investigation about statistical sampling this weekend since we are going to optimize our analyze process soon, and would like some input from all you orabrains on this one. I opened a TAR with Oracle asking about the sampling algorithm of stats collection, and they assured me it was random. The goal of analyze...estimate is to collect stats that are representative of the data population as a whole using a given sample set. Since analyzing tables takes up resources (does sorts to order the data for investigation) the fewer rows you use in estimate, the less system resources you use and the faster the analyze will go. Since our goal is to get as small a sample as possible and still have stats that are representative, my contention is that we could start by finding what the margin of error will be for each sample size and gauge our tolerance for it. One standard way to calculate margin of error for a given sample is by using this formula: M = 1/SQRT(N) where: M = margin of error N=sample size So, if we can tolerate stats that have a 1% a margin of error (will deviate from representative of the whole population by 1%), our sample size should be 10,000 rows. Also, a corollary (not a toyota corollary, though) to this would be that the more rows you add to your sample, the closer to representative your sample will be. So, in order to test whether your sample is representative enough, you could analyze using either estimate 49% or compute, take a snapshot of the stats, and then compare the stats from a 10,000 row estimate to those. Then, add rows to your estimate until you are satisfied with the stats. This of course is a pie in the sky mathematical model, but seems like a reasonable place to start with testing. Input? Input? Buhler
Re: Statistical sampling and representative stats collection
That is a very good question. Anyone else know the answer? My approach to skewness right now is to compare the mean and the median of the entire dataset, since if you have two measures of centrality that are not the same, you have skewness. /jack --- [EMAIL PROTECTED] wrote: Jack, Isn't the problem with this concept that it doesn't take into consideration how skewed the data is? Statistically significance would be relevant to perfectly distributed data but wouldn't you need a higher percentage of data for significance in more highly skewed data? Just something to consider. Cherie Machler Oracle DBA Gelco Information Network Jack Silvey jack_silvey@y To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ahoo.comcc: Sent by: Subject: Re: Statistical sampling and representative stats [EMAIL PROTECTED]collection om 05/21/02 06:19 PM Please respond to ORACLE-L Hi Rafiq, We have been using 35 percent on our warehouse, even on our fact partitions. Now that I have thought about it for a while, that seems like a lot given the volume of data. If a representative sample can be gathered with 10,000 or 50,000 or 100,000 rows, and our fact partitions have millions of rows each, seems like we could go 1% on our analyze and it would be within acceptable tolerances. /jack --- Mohammad Rafiq [EMAIL PROTECTED] wrote: The most of the list memeber agrees on estimate with 30% Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 21 May 2002 13:43:33 -0800 Hi all, Did some investigation about statistical sampling this weekend since we are going to optimize our analyze process soon, and would like some input from all you orabrains on this one. I opened a TAR with Oracle asking about the sampling algorithm of stats collection, and they assured me it was random. The goal of analyze...estimate is to collect stats that are representative of the data population as a whole using a given sample set. Since analyzing tables takes up resources (does sorts to order the data for investigation) the fewer rows you use in estimate, the less system resources you use and the faster the analyze will go. Since our goal is to get as small a sample as possible and still have stats that are representative, my contention is that we could start by finding what the margin of error will be for each sample size and gauge our tolerance for it. One standard way to calculate margin of error for a given sample is by using this formula: M = 1/SQRT(N) where: M = margin of error N=sample size So, if we can tolerate stats that have a 1% a margin of error (will deviate from representative of the whole population by 1%), our sample size should be 10,000 rows. Also, a corollary (not a toyota corollary, though) to this would be that the more rows you add to your sample, the closer to representative your sample will be. So, in order to test whether your sample is representative enough, you could analyze using either estimate 49% or compute, take a snapshot of the stats, and then compare the stats from a 10,000 row estimate to those. Then, add rows to your estimate until you are satisfied with the stats. This of course is a pie in the sky mathematical model, but seems like a reasonable place to start
dbms_stats issues on 8i
Hello fellow orabrains, Although Oracle has made it clear that DBMS_STATS is the future and that ANALYZE will be desupported, and it seems to work well in 9i, here are some things that I uncovered in my research of it during our initial stats process implementation (we were on 8.1.6 during this time): 1) GATHER_DATABASE_STATS gathers stats on SYS schema. Also creates possible deadlock scenario that terminates the process with DD stats half gathered, and the workaround is to delete the SYS stats. 2) ANALYZE_SCHEMA does not work I found this to be true in our warehouse. Fixed in 9i. 3) ANALYZE_SCHEMA does not gather stats on all tables Workaround is to analyze those tables manually (er, um,.never mind) 5) DBMS_STATS does not gather all stats Gathers only stats relevant to CBO, such as num_rows, not those such as empty_blocks. Not really relevant, necessarily. 6) PARALLEL clause does not work unless you specify FOR ALL COLUMNS SIZE x 7) GATHER_STALE clause does not gather stale stats Also found this to be true, which is why I wrote a looping analyze procedure. 8) Would not analyze our partitioned fact table at the top level, no matter what I tried. Waited 9 hours, restarted, and waited another 6. Used parallel degree 24 and still no good. Gave it up and used analyze which did each partition in ~3-5 minutes. 9) Cannot use ANALYZE after dbms_stats. DBMS_STATS sets GLOBAL_FLAG to Y, which prevents ANALYZE from storing stats for that table. Workaround is to delete stats with DBMS_STATS.DELETE_TABLE_STATS and then use ANALYZE. There are others, like the DESC index problem that I did not research, just noted in passing. /jack silvey __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Data Warehouse experts, a simple question for you| Outdated?
And politics.
--- [EMAIL PROTECTED] wrote:
Ian,
Good question. I think that I've seen more recenct
references in articles
that state the current thinking of DW/DM. I'm sure
that I've seen Inmon
refer to them that way, or maybe it was Richard
Winter?
Anyway, I guess that part is a bit dated. There is
so much good
information
in that book though, that it's still worth its
weight in gold. You won't
find too many
publications for $60 that will take you step by step
through building an
entire
data warehouse, including the infrastructure.
Jared
MacGregor, Ian A. [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/21/2002 05:48 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: Data Warehouse experts,
a simple question for you| Outdated?
I am new to his books, three chapters in. The first
release of the Data
Warehouse Toolkit defines a data warehouse much as
a data mart is today.
Today we think of a data warehouse as having a
highly normalized
structure which stores information from various
sources. We build data
marts with structures optimized for querying; e.g.,
star schemas, from the
warehouse. Kimball writes of the warehouse itself
being based on a star
schema.
The term data warehouse has not been immutable over
the years. It was
probably defined exactly as he has done when the
book was first written.
Do his new books redefine data warehouse?
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, May 21, 2002 2:16 PM
To: Multiple recipients of list ORACLE-L
I second Jared's opinion. Ralph's books are clear
and easy to read. This
is
the fundamentals of data warehousing.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, May 21, 2002 2:30 PM
To: Multiple recipients of list ORACLE-L
Yup, $60, and worth every penny.
It may be 4 years old, but the information is still
pertinent.
Jared
Joe Testa [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/20/2002 05:53 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: Data Warehouse experts,
a simple question for
you
looks like published aug of 98 for that book?, like
$60?
joe
[EMAIL PROTECTED] wrote:
Joe,
Add a generated PK to the time dimension. The PK
is stored
as an FK in the fact table.
That way you can select from the time dimension by
year, day, qtr,
whatever,
and easily pick out the correct fact table rows.
The Data Warehouse Lifecycle Toolkit includes a
spreadsheet to generate
the DDL/DML for a very robust time dimension. I
think it has about 20
columns.
Very good book, can't recommend it enough.
Jared
Joe Testa [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/20/2002 04:08 PM
Please respond to ORACLE-L
To: Multiple recipients of list
ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Data Warehouse experts, a
simple question for you
Ok i'm messing with dimensions.
dm_time to be exact:
create table dm_time
( calendar_date date not null,
calendar_month number(2) not null,
calendar_qtr number(1) not null,
calendar_year number(4) not null);
insert into dm_time
values(to_date('20020101','MMDD'), 1,1,2002);
insert into dm_time
values(to_date('20030101','MMDD'), 1,1,2003);
2 rows nice and simple
trying to validate the dimension comes up with an
error, my guess is
because of the design of the table
where basically calendar_date is child of
calendar_month is child of calendar_qtr is
child of calendar_year,
wont validate.
- the question i have is this, should month really
be like 2002-01 with
the year included, likewise with qtr, then it
will validate ok.
Was the design of dm_time just dont wrong or am i
missing something here.
thanks, joe
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
=== message truncated ===
__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP
Re: Data Warehouse experts, a simple question for you
your fields should be consistent across tables, otherwise, you risk losing the ability for your queries to use an index if necessary. If state code is char in one and num in the other, consider conversion on one of them, otherwise, oracle may do an implicit conversion on one of them during your queries. For instance, if you write a query that says SELECT * FROM tab_a, tab_b where tab_a.state_code = tab_b.state_code; and these columns are two different datatypes, Oracle will actually run code similiar to the following: SELECT * FROM tab_a, tab_b where to_num(tab_a.state_code) = tab_b.state_code; and the use of this function in the where clause will disable the availability of an index on that column. The reason is that the index will be in characters and the value you are seeking will be a number. You can use function based indexes to work around, but probably just better to store it the same in the first place. hth, /jack Paula_Stankus @doh.state.flTo: Multiple recipients of list ORACLE-L .us [EMAIL PROTECTED] Sent by: rootcc: Subject: RE: Data Warehouse experts, a simple question for you 05/21/2002 08:28 PM Please respond to ORACLE-L Okay you guys are silly. I have probably a stupid basic question to ask. How important is it to store data (let's say state codes, county codes with leading zeroes as character versus numeric). What is the standard out there? Does '02' mean the same thing as 2 for state code if you are consistent throughout your warehouse or do we need to consider other datasets out there that might be linked maybe sometime in the future? Can I leave it as is numeric and create materialized views with it padded or should I bite the bullet and reload into char/varchar2 datatypes? __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Data Warehouse experts, a simple question for you
I second that emotion. the guy that wrote it is a PhD
and owns Red Brick or something. totally knows what he
is talking about. One of my top five books, best
warehousing book by far I have ever read.
/jack silvey
--- [EMAIL PROTECTED] wrote:
Yup, $60, and worth every penny.
It may be 4 years old, but the information is still
pertinent.
Jared
Joe Testa [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/20/2002 05:53 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: Data Warehouse experts,
a simple question for you
looks like published aug of 98 for that book?, like
$60?
joe
[EMAIL PROTECTED] wrote:
Joe,
Add a generated PK to the time dimension. The PK
is stored
as an FK in the fact table.
That way you can select from the time dimension by
year, day, qtr,
whatever,
and easily pick out the correct fact table rows.
The Data Warehouse Lifecycle Toolkit includes a
spreadsheet to generate
the DDL/DML for a very robust time dimension. I
think it has about 20
columns.
Very good book, can't recommend it enough.
Jared
Joe Testa [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/20/2002 04:08 PM
Please respond to ORACLE-L
To: Multiple recipients of list
ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Data Warehouse experts, a
simple question for you
Ok i'm messing with dimensions.
dm_time to be exact:
create table dm_time
( calendar_date date not null,
calendar_month number(2) not null,
calendar_qtr number(1) not null,
calendar_year number(4) not null);
insert into dm_time
values(to_date('20020101','MMDD'), 1,1,2002);
insert into dm_time
values(to_date('20030101','MMDD'), 1,1,2003);
2 rows nice and simple
trying to validate the dimension comes up with an
error, my guess is
because of the design of the table
where basically calendar_date is child of
calendar_month is child of calendar_qtr is
child of calendar_year,
wont validate.
- the question i have is this, should month really
be like 2002-01 with
the year included, likewise with qtr, then it
will validate ok.
Was the design of dm_time just dont wrong or am i
missing something here.
thanks, joe
--
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:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(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: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Data Warehouse experts, a simple question for you
I find that if I wrap my books in Saran Wrap, I can
read in the shower. And if you prop the book up on
your shoulder, you can read it backwards in the
rearview mirror during drive time. Also, if you learn
to read in your sleep, you can get LOADS of stuff
done.
;)
hth,
/jack silvey
--- Rachel Carmichael [EMAIL PROTECTED] wrote:
it's cheaper on bookpool :)
especially when Borders is out of stock
you guys are killing my credit card! I went out and
bought Inmon's
Building the Data Warehouse, BOTH Kimball books and
and considering the
Webhouse one as well geez, when do I have time
to READ this stuff?
Rachel
--- [EMAIL PROTECTED] wrote:
Yup, $60, and worth every penny.
It may be 4 years old, but the information is
still pertinent.
Jared
Joe Testa [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/20/2002 05:53 PM
Please respond to ORACLE-L
To: Multiple recipients of list
ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: Data Warehouse
experts, a simple question
for you
looks like published aug of 98 for that book?,
like $60?
joe
[EMAIL PROTECTED] wrote:
Joe,
Add a generated PK to the time dimension. The PK
is stored
as an FK in the fact table.
That way you can select from the time dimension
by year, day, qtr,
whatever,
and easily pick out the correct fact table rows.
The Data Warehouse Lifecycle Toolkit includes a
spreadsheet to
generate
the DDL/DML for a very robust time dimension. I
think it has about
20
columns.
Very good book, can't recommend it enough.
Jared
Joe Testa [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/20/2002 04:08 PM
Please respond to ORACLE-L
To: Multiple recipients of list
ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Data Warehouse experts, a
simple question
for you
Ok i'm messing with dimensions.
dm_time to be exact:
create table dm_time
( calendar_date date not null,
calendar_month number(2) not null,
calendar_qtr number(1) not null,
calendar_year number(4) not null);
insert into dm_time
values(to_date('20020101','MMDD'),
1,1,2002);
insert into dm_time
values(to_date('20030101','MMDD'),
1,1,2003);
2 rows nice and simple
trying to validate the dimension comes up with
an error, my guess
is
because of the design of the table
where basically calendar_date is child of
calendar_month is child of calendar_qtr is
child of
calendar_year,
wont validate.
- the question i have is this, should month
really be like 2002-01
with
the year included, likewise with qtr, then it
will validate ok.
Was the design of dm_time just dont wrong or am i
missing something
here.
thanks, joe
--
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:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051
FAX: (858) 538-5051
San Diego, California-- Public Internet
access / Mailing
Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(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: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858)
=== message truncated ===
__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public
Re: Data Warehouse experts, a simple question for you
Yeah, that's a common misconception. You actually have
to prop the book on your forehead since English flows
to the left and downwards. I have heard that Chinese
flows from right to left and upwards or something
though - you might consider taking it up as a new
language if you are set on the underpillow method of
knowledge transfer.
/jack
--- Rachel Carmichael [EMAIL PROTECTED] wrote:
gee, and here I thought all I had to do was put the
book under my
pillow and let the words seep in through osmosis :)
--- Jack Silvey [EMAIL PROTECTED] wrote:
I find that if I wrap my books in Saran Wrap, I
can
read in the shower. And if you prop the book up on
your shoulder, you can read it backwards in the
rearview mirror during drive time. Also, if you
learn
to read in your sleep, you can get LOADS of stuff
done.
;)
hth,
/jack silvey
--- Rachel Carmichael [EMAIL PROTECTED]
wrote:
it's cheaper on bookpool :)
especially when Borders is out of stock
you guys are killing my credit card! I went out
and
bought Inmon's
Building the Data Warehouse, BOTH Kimball books
and
and considering the
Webhouse one as well geez, when do I have
time
to READ this stuff?
Rachel
--- [EMAIL PROTECTED] wrote:
Yup, $60, and worth every penny.
It may be 4 years old, but the information is
still pertinent.
Jared
Joe Testa [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/20/2002 05:53 PM
Please respond to ORACLE-L
To: Multiple recipients of list
ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: Data Warehouse
experts, a simple question
for you
looks like published aug of 98 for that book?,
like $60?
joe
[EMAIL PROTECTED] wrote:
Joe,
Add a generated PK to the time dimension.
The PK
is stored
as an FK in the fact table.
That way you can select from the time
dimension
by year, day, qtr,
whatever,
and easily pick out the correct fact table
rows.
The Data Warehouse Lifecycle Toolkit
includes a
spreadsheet to
generate
the DDL/DML for a very robust time dimension.
I
think it has about
20
columns.
Very good book, can't recommend it enough.
Jared
Joe Testa [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/20/2002 04:08 PM
Please respond to ORACLE-L
To: Multiple recipients of list
ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Data Warehouse
experts, a
simple question
for you
Ok i'm messing with dimensions.
dm_time to be exact:
create table dm_time
( calendar_date date not null,
calendar_month number(2) not null,
calendar_qtr number(1) not null,
calendar_year number(4) not null);
insert into dm_time
values(to_date('20020101','MMDD'),
1,1,2002);
insert into dm_time
values(to_date('20030101','MMDD'),
1,1,2003);
2 rows nice and simple
trying to validate the dimension comes up
with
an error, my guess
is
because of the design of the table
where basically calendar_date is child of
calendar_month is child of calendar_qtr
is
child of
calendar_year,
wont validate.
- the question i have is this, should month
really be like 2002-01
with
the year included, likewise with qtr, then it
will validate ok.
Was the design of dm_time just dont wrong or
am i
missing something
here.
thanks, joe
--
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
=== message truncated ===
__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail
RE: Data Warehouse experts, a simple question for you
And these CD's.are you supposed to eat them one byte at a time? insert rimshot here Thank you, thank you, tip your waiters and waitresses, I will be here all week! /jack --- Gogala, Mladen [EMAIL PROTECTED] wrote: No, no, no and no! You are supposed to eat your documentation CD, preferably with fajitas or spaghetti and lots of Tabasco sauce. There are some versions of habanera sauce which can prepare your mouth to such extent that it becomes impossible to tell oracle documentation CD and a chicken wing apart. Make sure that you have few gallons of water at hand, though. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 21, 2002 4:39 PM To: Multiple recipients of list ORACLE-L Subject: Re: Data Warehouse experts, a simple question for you gee, and here I thought all I had to do was put the book under my pillow and let the words seep in through osmosis :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Statistical sampling and representative stats collection
Hi all, Did some investigation about statistical sampling this weekend since we are going to optimize our analyze process soon, and would like some input from all you orabrains on this one. I opened a TAR with Oracle asking about the sampling algorithm of stats collection, and they assured me it was random. The goal of analyze...estimate is to collect stats that are representative of the data population as a whole using a given sample set. Since analyzing tables takes up resources (does sorts to order the data for investigation) the fewer rows you use in estimate, the less system resources you use and the faster the analyze will go. Since our goal is to get as small a sample as possible and still have stats that are representative, my contention is that we could start by finding what the margin of error will be for each sample size and gauge our tolerance for it. One standard way to calculate margin of error for a given sample is by using this formula: M = 1/SQRT(N) where: M = margin of error N=sample size So, if we can tolerate stats that have a 1% a margin of error (will deviate from representative of the whole population by 1%), our sample size should be 10,000 rows. Also, a corollary (not a toyota corollary, though) to this would be that the more rows you add to your sample, the closer to representative your sample will be. So, in order to test whether your sample is representative enough, you could analyze using either estimate 49% or compute, take a snapshot of the stats, and then compare the stats from a 10,000 row estimate to those. Then, add rows to your estimate until you are satisfied with the stats. This of course is a pie in the sky mathematical model, but seems like a reasonable place to start with testing. Input? Input? Buhler? Buhler? /jack silvey __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Statistical sampling and representative stats collection
Hi Rafiq, We have been using 35 percent on our warehouse, even on our fact partitions. Now that I have thought about it for a while, that seems like a lot given the volume of data. If a representative sample can be gathered with 10,000 or 50,000 or 100,000 rows, and our fact partitions have millions of rows each, seems like we could go 1% on our analyze and it would be within acceptable tolerances. /jack --- Mohammad Rafiq [EMAIL PROTECTED] wrote: The most of the list memeber agrees on estimate with 30% Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 21 May 2002 13:43:33 -0800 Hi all, Did some investigation about statistical sampling this weekend since we are going to optimize our analyze process soon, and would like some input from all you orabrains on this one. I opened a TAR with Oracle asking about the sampling algorithm of stats collection, and they assured me it was random. The goal of analyze...estimate is to collect stats that are representative of the data population as a whole using a given sample set. Since analyzing tables takes up resources (does sorts to order the data for investigation) the fewer rows you use in estimate, the less system resources you use and the faster the analyze will go. Since our goal is to get as small a sample as possible and still have stats that are representative, my contention is that we could start by finding what the margin of error will be for each sample size and gauge our tolerance for it. One standard way to calculate margin of error for a given sample is by using this formula: M = 1/SQRT(N) where: M = margin of error N=sample size So, if we can tolerate stats that have a 1% a margin of error (will deviate from representative of the whole population by 1%), our sample size should be 10,000 rows. Also, a corollary (not a toyota corollary, though) to this would be that the more rows you add to your sample, the closer to representative your sample will be. So, in order to test whether your sample is representative enough, you could analyze using either estimate 49% or compute, take a snapshot of the stats, and then compare the stats from a 10,000 row estimate to those. Then, add rows to your estimate until you are satisfied with the stats. This of course is a pie in the sky mathematical model, but seems like a reasonable place to start with testing. Input? Input? Buhler? Buhler? /jack silvey __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partition Exchange
Hello Holly, Make sure and pay attention to the clause that allows you to skip validation. Don't remember it right off the top, but it will save you beaucoup time if you are sure about your data. Jack --- Deshpande, Kirti [EMAIL PROTECTED] wrote: It means partitioning a non-partitioned table using exchange partition mechanism, and creating non-partitioned table from partitions of a partitioned table. You can find detail info in the Database Administrator's Guide. Regards, - Kirti -Original Message- Sent: Tuesday, May 21, 2002 9:18 PM To: Multiple recipients of list ORACLE-L Dear all, What's the meaning of Partition Exchange? tia, Holly -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: I/O EVENTS
Payroll program did a pretty bad thing in their code: they passed several really long (10KB) SQL texts from the client to the server in their parse calls (oh yeh, and with no bind variables either). Anyway, each time the Oracle kernel received a packet containing only part of a SQL statement, it tallied to SQL*Net more data from client while it waited for the next piece. Of course, communicating via the network interface card instead of IPC didn't help, but passing huge SQL statements from the app to the db is bad form to begin with (see Ixora for more detail on just how bad this is). Finally, realize that Oracle tallies to SQL*Net message from client pretty much every time it's between working on db call fulfillment. Therefore, there were on the order of 95,161 db calls during this 33-minute period. Looking at the SQL, we discovered that of course, since the app developers didn't use bind variables, there was a parse call for almost every execute. This accounted for thousands of unnecessary parse calls. Next, each execute call manipulated only zero or one row, which added up to dozens of execute calls for each single SQL statement with a multi-row result source. The program could have performed the business function with a fraction of the db calls. Oh yes... Why did the Payroll program get *slower* after the upgrade? Little Payroll program time was spent using CPU, so the upgrade had very little direct positive effect upon the program. Most of the program's time was spent waiting for network latency. Other programs ran at the same time as this Payroll job. The CPU upgrade made *them* faster, which intensified *their* constant number of network calls into a smaller time window. The result was increased competition for the network during the === message truncated === __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Telephone Alerts
Simon, Many cellphone providers provide a service whereby an email recieved at a specific address is sent on to a mobile phone in the form of a page. Here is the routine that I use to scrape my output logs and send a success or failure page to my cellphone: #!/bin/ksh # shell script to run sql scripts and contact the user with succes or failure # Jack Silvey #---Oracle variable section --- export ORACLE_SID=iron export ORACLE_HOME=/u01/app/oracle/product/8.1.7 #---shell variable section --- SCRIPTPATH=/u01/app/oracle/admin/iron/scripts SQLPATH=$SCRIPTPATH/sql SCRIPTNAME=$SQLPATH/$1 OUTPATH=$SCRIPTPATH/output [EMAIL PROTECTED] SCRIPTOUTPUT=$OUTPATH/$1.out USERNAME=$2 PASS=$3 #---Clean out the output file--- cat /dev/null$SCRIPTOUTPUT #---Run the SQL*Plus script, storing results in a variable--- SQLRESULTS=`sqlplus /nolog @$SCRIPTNAME $SCRIPTOUTPUT $SQLPATH/endsql.sql` #---Check the command-line return code--- SCRIPTERROR=$? #---Check to make sure output doesn't contain SQl*PLUS errors--- cat $SCRIPTOUTPUT|grep SP2- GREPOUTPUT=$? if [[ $GREPOUTPUT -eq 0 ]] then SCRIPTERROR=`expr $SCRIPTERROR + 1` fi #---Check to make sure output doesn't contain Oracle errors--- cat $SCRIPTOUTPUT|grep ORA- GREPOUTPUT=$? if [[ $GREPOUTPUT -eq 0 ]] then SCRIPTERROR=`expr $SCRIPTERROR + 1` fi #---Check to make sure the resulting variable doesn't contain errors--- echo $SQLRESULTS|grep SP2- GREPOUTPUT=$? if [[ $GREPOUTPUT -eq 0 ]] then SCRIPTERROR=`expr $SCRIPTERROR + 1` fi echo $SQLRESULTS|grep ORA- GREPOUTPUT=$? if [[ $GREPOUTPUT -eq 0 ]] then SCRIPTERROR=`expr $SCRIPTERROR + 1` fi --this section pages the dba with success or failure-- if [[ $SCRIPTERROR -ne 0 ]] then echo $1 failed | mailx $CONTACT exit 99 else echo $1 successful | mailx $CONTACT fi hth, Jack Silvey --- FOX, Simon [EMAIL PROTECTED] wrote: We have some UNIX batch processes that send us e-mails when they have finished. Does anyone have any experience of sending a SMS message to a mobile phone? Simon Fox Room 221 Furlong House Queens Drive NOTTINGHAM NG2 1AL Phone 0115 957 6536 Fax 0115 957 6548 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ways to allow developers to see all packge bodies without EXECUTE ANY PROCEDURE?
Good day listers, We have some developers that would like to see all the package bodies in our data warehouse. This is a reasonable request. The developers use Toad, which used the ALL_SOURCE view. I pulled the code for all_source and discovered that the execute_any priv exclusion is hardcoded in this view. Rather than granting them EXECUTE ANY PROCEDURE to allow them to get to the code (can you say resume.ksh?), I have three options I am considering. 1) give select_catalog_role and asking them to use sqlplus (let the hatemail ensue) 2) create a new dd view based on all or dba_source and give them a synonym called ALL_SOURCE in their private schema to fool toad into seeing dba source 3) create a view in their schema called all_source that is actually a select on dba_source 4) telling them that once source is in the database it cannot be seen by anybody, even dbas. I have heard that TOAD can use dba_source, but they have not had luck with this (might be a default role thing, have not verified). Opinions or other options? Jack Silvey __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Cannot import--URGENT
Ravi, Why is your fromuser=sys? Are you specifying a parfile anywhere? Jack --- Ravindra B [EMAIL PROTECTED] wrote: I am trying to import from my production database to another database.Table level export was done on the production databse for 4 table.It ended w/o any errors. When I am trying to import to anther user on a different datasbe the import compltes successfully without any warning but no table/records are imported The tables being imported are not existing in the new user's schema. I have tried this on 8.1.7 to 8.1.7 and also on 8.1.7 to 9.0.1. These tables on the production database are protected so I can export them only as sys user.I am attaching the log from the import.What is the problem. Am i missing anything imp sys/change_on_install fromuser=sys touser=ravi file=g.dmp log=g.log Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option JServer Release 9.0.1.1.1 - Production Export file created by EXPORT:V08.01.07 via conventional path import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set export client uses WE8ISO8859P1 character set (possible charset conversion) export server uses US7ASCII NCHAR character set (possible ncharset conversion) Import terminated successfully without warnings. Ravindra __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: script to show heirarchical list of object dependencies for a
Great minds think alike? Either that or we are the
borg and just don't know that we are part of the
collective yet.
--- [EMAIL PROTECTED] wrote:
How is this essentially different than what you can
get from utldtree.sql?
when Kevin Loney and I were writing the Annotated
Archives a few years ago, I
tried to write a script that would show the
dependencies, because part of the
reason for the book was to provide home-grown
scripts that wre documented. I
ended up essentially rewriting the cod from
utldtree.sql and we gave up that
particular effort.
|+---
|| |
|| |
|| jack_silvey@y|
|| ahoo.com |
|| |
|| 05/09/2002 |
|| 07:03 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
|
|
| To: [EMAIL PROTECTED]
|
| cc: (bcc: Rachel Carmichael)
|
| Subject: script to show heirarchical
list|
| of object dependencies for a given object
|
|
Greetings listers,
Ever worry about wrinkles, loss of hair, and bad
breath?
Well, the following script can't help you with THAT,
but it CAN show you a heirarchical list of objects
that depend upon the given object! Just pass in
1=owner and 2=object name, and viola! No more
changing objects without knowing what depends on
them
(and might break). (The reason for the temp table is
that you can't do recursive self-join sql on complex
views.) This can be a very useful script if you make
production database changes.
prompt
prompt objects depending on 1. 2:
prompt
set termout off
set head off
set verify off
drop table depends;
create table depends as select * from
sys.dba_dependencies tablespace tools;
set termout on
select '*'||lpad(' ',level+3)||type||'
'||owner||'.'||name
from depends
connect by prior owner = referenced_owner
and prior name = referenced_name
and prior type = referenced_type
start with referenced_owner = upper('1')
and referenced_name = upper('2')
and owner is not null
/
good luck,
jack silvey
__
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: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be 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: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: DBA_EXTENTS problem
Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
Babu, issue this in your sqlplus session: show parameter optim and then this: alter session set optimizer_mode=choose; and then this: select /*+ RULE */ count(1) from dba_extents; and then this: select count(1) from dba_extents; and let us know what happens. Rachel: we were running all_rows in our dwh and it was slowing down dd access. not sure why, choose and all_rows are kissing cousins. true, dd is supposed to be rule and with no stats. Have heard of cases where dbms_statistics analyzed the sys schema while doing a analyze_database, which is one reason I stopped using it. Might be the problem here? We will find out. hth, jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: I have recently changed the analyze script. Earliar it was dbms_utility.analyze_schema(...) statement, It is now changed to dbms_stats.gather_database_stats(); Is this a problem? The response time for select count(*) from dba_extents is also 30 minutes. It is not specific to any table. Thanks, -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:36 AM To: Multiple recipients of list ORACLE-L that's interesting... since the data dictionary is NOT analyzed, setting optimizer_mode=choose would force the query against dba_extents to RULE which it what it is supposed to be doing anyway, Hm. The question now is, what is the optimizer_mode set to when the problem happens? Did any of the data dictionary tables get accidentally analyzed? And -- how many extents are in use in the database? Could it just be a symptom of missized tables and indexes so that the number of extents is way high? Although Oracle is *supposed* to allow unlimited extents, in practice anything higher than 4096 extents in an object (at least in 8i) tends to slow things down. |+--- || | || | || jack_silvey@y| || ahoo.com | || | || 05/09/2002 | || 02:18 PM | || Please | || respond to | || ORACLE-L | || | |+--- | | | | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: DBA_EXTENTS problem | | Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
Re: too low optimizer_index_cost_adj causing bizarre index choice
Hi Johnathan,
here is the skinny:
db_file_multiblock_read_count = 64
number of rows=15m
blocks=251071
empty_blocks=0
db_block_size=16384
total plan cost=4924
tablescan cost = 4924
Jack
--- Jonathan Lewis [EMAIL PROTECTED]
wrote:
There seems to be a perfectly good theoretical
reason
for this. But it would be interesting to know:
Number of blocks below HWM
Setting for db_file_multiblock_read_count
Maximum usable value for db_file_mbrc
The cost given by Explain Plan for the tablescan
before I confuse the issue further by expounding a
hypothesis that may be totally misleading.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Date: 09 May 2002 14:30
|Good morning listers,
|
|Some weeks ago I noticed a query plan that was
using a
|bizarre index choice and opened a TAR. Here is that
|query:
|
|select DSS_EMPR.V_CLAIM_EMPR_HX.AMT_COPAY from
|DSS_EMPR.V_CLAIM_EMPR_HX where
|DSS_EMPR.V_CLAIM_EMPR_HX.BATCH_NUM between
|to_date('2000-01-01','-mm-dd') and
|to_date('2000-01-31','-mm-dd');
|
|Here is the query plan:
|
| SELECT STATEMENT (all_rows) Cost
|(4924,636953,12739060)
|
| 1 0 1 2
|TABLE ACCESS (analyzed) DSS_EMPR
T_CLAIM_EMPR_HX
|(by index rowid) Cost (4924,636953,12739060)
|
| 2 1 1
| BITMAP CONVERSION(to rowids)
|
| 3 2 1
|BITMAP INDEX DSS_EMPR X_CLAIM_EMPR_HX_N01
|(full scan)
|
|
|The index x_claim_empr_hx_n01 is on the phmcy_gid
|column of the t_claim_empr_hx table.
|
|Now, here is the crux of the matter: phmcy_gid is
|referred to nowhere in the query, not in select nor
in
|where nor even in order by.
|
|Worked through the tar with Oracle and they advised
|that Oracle can and will cost ALL indexes during a
|plan parse and eval, so it became a matter of
|discovering why the index was being incorrectly
|costed.
|
|Remembered that our optimizer_index_cost_adj was
set
|to 1 (don't ask). When I upped this value to 2 or
more
|and reran the query, it returned the appropriate
FTS
|plan.
|
|hth,
|
|Jack Silvey
|
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
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: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: DBA_EXTENTS problem
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
script to show heirarchical list of object dependencies for a given object
Greetings listers,
Ever worry about wrinkles, loss of hair, and bad
breath?
Well, the following script can't help you with THAT,
but it CAN show you a heirarchical list of objects
that depend upon the given object! Just pass in
1=owner and 2=object name, and viola! No more
changing objects without knowing what depends on them
(and might break). (The reason for the temp table is
that you can't do recursive self-join sql on complex
views.) This can be a very useful script if you make
production database changes.
prompt
prompt objects depending on 1. 2:
prompt
set termout off
set head off
set verify off
drop table depends;
create table depends as select * from
sys.dba_dependencies;
set termout on
select '*'||lpad(' ',level+3)||type||'
'||owner||'.'||name
from depends
connect by prior owner = referenced_owner
and prior name = referenced_name
and prior type = referenced_type
start with referenced_owner = upper('1')
and referenced_name = upper('2')
and owner is not null
/
good luck,
jack silvey
__
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: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: script to show heirarchical list of object dependencies for a given
Aw, DANG, I knew I was gonna get that one. Same story,
give someone a script named after an adult
incontenence device, get ready for the teasing!
Don't even ask for the viagra script.
--- [EMAIL PROTECTED] wrote:
create table depends as select * from
sys.dba_dependencies;
Depends? Like the adult diaper?
Doing a little CYA?
Jared
Jack Silvey [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/09/2002 04:03 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:script to show heirarchical
list of object dependencies for a given object
Greetings listers,
Ever worry about wrinkles, loss of hair, and bad
breath?
Well, the following script can't help you with THAT,
but it CAN show you a heirarchical list of objects
that depend upon the given object! Just pass in
1=owner and 2=object name, and viola! No more
changing objects without knowing what depends on
them
(and might break). (The reason for the temp table is
that you can't do recursive self-join sql on complex
views.) This can be a very useful script if you make
production database changes.
prompt
prompt objects depending on 1. 2:
prompt
set termout off
set head off
set verify off
drop table depends;
create table depends as select * from
sys.dba_dependencies;
set termout on
select '*'||lpad(' ',level+3)||type||'
'||owner||'.'||name
from depends
connect by prior owner = referenced_owner
and prior name = referenced_name
and prior type = referenced_type
start with referenced_owner = upper('1')
and referenced_name = upper('2')
and owner is not null
/
good luck,
jack silvey
__
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: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be 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: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: too low optimizer_index_cost_adj causing bizarre index choice
I think the interesting thing is, why did it look at an index not in select/where/sort operations at all?Best idea I have heard so far is that somehow it thinks that gathering the rowids from the index and doing a table lookup is better than a fts. LElking floated the idea that it might have to do with a high HWM and a low number of of rows, perhaps combined with an index on a not-null col. Even Oracle was reluctant to admit that it was accessing a wacko index until I pressed them for it. I asked them to give me the relevant CBO code, but noo, not even a decent 10053 doc. --- Jonathan Lewis [EMAIL PROTECTED] wrote: I believe that Waleed's response is essentially correct. The numbers still don't make sense, but ... a) 250,000 blocks with the mbrc = 64. The t/s cost (according to my observations on 9.0.1 and 8.1.7) should be based on an adjusted mbrc of 15.5 250,000 / 15.5 = 16,000 b)Using an index to hit every block, Oracle is able to determine from data clustering stats that many consecutive index leaf values will hit a single data block, therefore can estimate the single read-count via index as 250,000 - but you have told it to reduce this by a factor of 1/100 - for a total of 2,500. Unfortunately for the theory, both your costs are 4,924 - so the T/S cost is close to the traditional table blocks / simple mbrc, and index cost is too high be a factor of 2, which may be due to some internal bitmap index clustering fudge factor - some of the critical bitmap index numbers apparently used to be hard-coded constants. Possibly it just means that your bitmap column has two values (and of course I haven't allowed a count for the number of leaf blocks in the index !). Details notwithstanding - I suspect that calculations like the above are the reason why Oracle decided that a full indexed path was cheaper than a scan. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 09 May 2002 22:16 choice |Hi Johnathan, | |here is the skinny: | |db_file_multiblock_read_count = 64 |number of rows=15m |blocks=251071 |empty_blocks=0 |db_block_size=16384 | |total plan cost=4924 |tablescan cost = 4924 | |Jack | | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
