RE: Was 8.1.6 certified on Solaris 8?
Thanks everyone! -Original Message- Sent: Tuesday, January 14, 2003 10:51 AM To: Multiple recipients of list ORACLE-L We are running 8.1.6 against Solaris 8 patch level Generic_108528-15. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 3:10 PM To: Multiple recipients of list ORACLE-L We're upgrading from Solaris 2.6 to Solaris 8 and 8.1.6 to 8.1.7 on one of our boxes. I want to know if it's possible to do the OS upgrade first and then the database upgrade (e.g., I'd be running 8.1.6 on Solaris 8 for a day or so). The Oracle certification matrix only says that 8.1.6 is desupported and therefore doesn't list any certified OS versions for it. Did anyone run 8.1.6 on Solaris 8 or remember if it was ever certified? TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Was 8.1.6 certified on Solaris 8?
We're upgrading from Solaris 2.6 to Solaris 8 and 8.1.6 to 8.1.7 on one of our boxes. I want to know if it's possible to do the OS upgrade first and then the database upgrade (e.g., I'd be running 8.1.6 on Solaris 8 for a day or so). The Oracle certification matrix only says that 8.1.6 is desupported and therefore doesn't list any certified OS versions for it. Did anyone run 8.1.6 on Solaris 8 or remember if it was ever certified? TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: #of blocks in extent
And of course to calculate you'll need to know your block size and your extent size. -Original Message- Sent: Saturday, January 11, 2003 10:34 AM To: Multiple recipients of list ORACLE-L UNIFORM SIZE clause of Tablespace, if it is LMT. --- Igor Neyman [EMAIL PROTECTED] wrote: INITIAL, NEXT, PCTINCREASE -- if it's not LMT Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 10, 2003 2:54 PM How many blocks are allocated to an extend . what parameter decides that . Is it some storage param ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: BigP INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = cool amar The best way to express yourself is to be yourself. __ 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: Amar Kumar Padhi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hotsos Clinic
I'd go if I were you. In fact I've been kicking myself that I didn't just go and pay for it myself when they were in NY and my company refused to pay for it. Jay Miller -Original Message- Sent: Thursday, January 02, 2003 1:42 PM To: Multiple recipients of list ORACLE-L I have an opportunity to attend a Hotsos Clinic. It seems I have heard good things about them on this list, but I thought I might double-check. Is this 3-day class worthwhile or is it an expensive way to sell their product? Will this class be beneficial, even if we don't buy their product? Keith -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry, Keith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: New course for 9i - Dumps/Crashes from Oracle University
That was my reaction after taking the classes. I was, 'gee, if I could take these again in a year I'd really get a lot more out of them'. 3 years ago I thought I was a knowledgeable DBA. Now, I have some idea of how little I know. Jay -Original Message- Sent: Monday, December 16, 2002 3:39 PM To: Multiple recipients of list ORACLE-L I took this seminar set last year for Oracle 8i. It's really very good, but only a little was able to really soak in. I'm trying to convince manglement to let us go again. I had a professor in college who's first lesson was that he - despite speaking 8 languages, having 2 or 3 doctoral degrees in languages and such, and having many years of teaching experience - was more ignorant than we students. His lesson was The more you know, the more you know you don't know. I am now more ignorant of Oracle than I was a year ago, and I think I could learn more this time. So far, it's not flying, but I haven't given up! Cheers, Mike -Original Message- Sent: Monday, December 16, 2002 12:04 PM To: Multiple recipients of list ORACLE-L Oracle Corporation is conducting these 3 highly technical seminars. Each one of them is a full day class at a cost of $500 per class. http://education.oracle.com/web_prod-plq-dad/plsql/show_desc.redirect?dc=D12 856GC10 http://education.oracle.com/web_prod-plq-dad/plsql/show_desc.redirect?dc=D12 858GC10 http://education.oracle.com/web_prod-plq-dad/plsql/show_desc.redirect?dc=D12 860GC10 -Original Message- Sent: Monday, December 16, 2002 12:05 PM To: Multiple recipients of list ORACLE-L There will probably be others in the series... Looks like one of the 8i Internals seminars has made it to 9i! 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 E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Monday, December 16, 2002 11:54 AM To: Multiple recipients of list ORACLE-L What is the world coming to? -Original Message- Sent: Friday, December 13, 2002 5:44 PM To: Multiple recipients of list ORACLE-L hmmm ... http://education.oracle.com/web_prod-plq-dad/plsql/cdesc?dc=D12856GC10 http://education.oracle.com/web_prod-plq-dad/plsql/cdesc?dc=D12856GC10p_or g_id=1001lang=US p_org_id=1001lang=US Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 causes table to grow
Just wanted to follow up on this in the unlikely event that anyone was still wondering. In retrospect it seems likely that what caused my table to grow while doing the Alter Table Move was the same thing that was causing my problem with new extents being claimed when there was lots of space available in the freelist (same table). When the move command was issued I'm guessing that for some rows Oracle couldn't find a block on the freelist after the first 5 tries that had enough space for the next row and therefore grabbed another extent. I'd guess this table is much larger than it needs to be just now. Once we upgrade to 9i this tablespace is a definite candidate for an increase in blocksize... Jay Miller -Original Message- Sent: Thursday, September 05, 2002 1:55 PM To: Multiple recipients of list ORACLE-L Jay: I would also wonder that the PCTINCREASE was on the table and the indexes. 10% PCTFREE is fine, but does lead to a significant number of empty blocks. What is your PCTUSED? If small, you will have lots of free space within blocks. Just a thought. Don't let your disk person know this happened as they may try to sell you more hardware. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Subject:ALTER TABLE MOVE command causes table to grow Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backup DB files to the Tape
). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Brian Dunbar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Installing Pro*C for 9.2?
I'm installing 9.2 on a test box that needs Pro*C (my other installations were on different boxes that didn't require it). In 8i it was under the Client Installation but I don't see it there now. Is it under some other heading? Am I just not seeing it? Pro*C searches on Metalink turned up tons of irrelevant references. I'm sure the answer is there somewhere... Thanks! Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-1653: unable to extend table - Resolved?
Well, I reduced the PCTUSED and PCTFREE on Friday and a small insert (app. 30,000 rows) seems to have worked as expected. No additional space was claimed and NUM_FREELIST_BLOCKS declined. I'll wait and see what happens during our next large insert. But for now it seems like changing these parameters may have resolved the problem. Jay Miller (with fingers crossed) -Original Message- Sent: Friday, December 06, 2002 4:19 PM To: Multiple recipients of list ORACLE-L How badly do you want the space back? I believe you will indeed need to touch each row. You could update each row with something like (update set column-1=column-1) Good luck! Barb Miller, Jay [EMAIL PROTECTED] wrote: But will this solve my problem in the near term? My understanding is that simply changing the PCT USED won't move the problematic blocks off the freelist until some sort of DML touches the block. Am I correct in this and if so is there any way to resolve it? Jay _ Do you Yahoo!? Yahoo! Mail http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com Plus - Powerful. Affordable. Sign up http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com now -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-1653: unable to extend table - Why?
Very interesting! I was out sick yesterday so I'm just getting caught up on the email today. There are currently 898334 rows and 2654300 blocks in the table (the number of rows will grow over the next 2 months before the next big delete which is done quarterly). But this does seem to imply that it is only allocating one row/block. Might adding more freelists enable it to make more use of the available blocks by avoiding timeouts while walking the freelist? I've also been investigating the application and have come across some annoying features that unfortunately the developer assures me can't change for various reasons. One is that the loading process is doing frequent commits (it commits on the account level which will be usually be one insert but unlikely to be more than 6). Also it is doing a Select from Dual for every insert. Don't know if this is relevant to anything other than performance though. Matt: You suggest changing the storage parameters or by changing the block size. Changing the block size isn't really an option just now (though once I upgrade to 9i I'll seriously consider changing it for just this tablespace). What storage parameter changes did you have in mind? Waleed: The table is not partitioned. Extent size is 25Meg. Jay Miller x48355 -Original Message- Sent: Thursday, December 05, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Dan, I think you nailed it! It will be interesting to see the # of blocks and # of rows in this table. - Kirti -Original Message- Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-1653: unable to extend table - Why?
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-1653: unable to extend table - Why?
One row/insert. One commit every 1-6 inserts (rows). Column values are passed to pl/sql procedure which does the insert (i.e,. passed in variables). Maximum row length: I assume you mean the largest row in the table? Does anyone have an easy way to get this? Other than applying formulas to each individual column based on datatype and length of the value? Jay -Original Message- Sent: Friday, December 06, 2002 11:51 AM To: Multiple recipients of list ORACLE-L How is the insert being used? Is it one row per insert? Is the column values hardcoded or passed in variables? What is the maximum row length? -Original Message- Sent: Friday, December 06, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Very interesting! I was out sick yesterday so I'm just getting caught up on the email today. There are currently 898334 rows and 2654300 blocks in the table (the number of rows will grow over the next 2 months before the next big delete which is done quarterly). But this does seem to imply that it is only allocating one row/block. Might adding more freelists enable it to make more use of the available blocks by avoiding timeouts while walking the freelist? I've also been investigating the application and have come across some annoying features that unfortunately the developer assures me can't change for various reasons. One is that the loading process is doing frequent commits (it commits on the account level which will be usually be one insert but unlikely to be more than 6). Also it is doing a Select from Dual for every insert. Don't know if this is relevant to anything other than performance though. Matt: You suggest changing the storage parameters or by changing the block size. Changing the block size isn't really an option just now (though once I upgrade to 9i I'll seriously consider changing it for just this tablespace). What storage parameter changes did you have in mind? Waleed: The table is not partitioned. Extent size is 25Meg. Jay Miller x48355 -Original Message- Sent: Thursday, December 05, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Dan, I think you nailed it! It will be interesting to see the # of blocks and # of rows in this table. - Kirti -Original Message- Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send
RE: ORA-1653: unable to extend table - Why?
by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-1653: unable to extend table - Why?
there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858
RE: ORA-1653: unable to extend table - Why?
over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com
RE: ORA-1653: unable to extend table - Why?
should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services
RE: ORA-1653: unable to extend table - Why?
/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-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: ORA-1653: unable to extend table - Why?
. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
RE: ORA-1653: unable to extend table - Why?
. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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
RE: ORA-1653: unable to extend table - Why?
if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing
RE: ORA-1653: unable to extend table - Why?
. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP
RE: ORA-1653: unable to extend table - Why?
Ron, Good idea, but DEGREE=1 There are two indexes, but they are in a different tablespace (which has plenty of free space available and did not give an error). Next extent size is 25M but, as mentioned, it shouldn't have needed a new extent. I'm still at a loss... Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http
RE: ORA-1653: unable to extend table - Why?
Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: ORA-1653: unable to extend table - Why?
- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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
RE: ORA-1653: unable to extend table - Why?
I was told by the department manager that they were neither using a direct load nor the Append hint. But the developer is back from vacation today so I'll get a more definite answer from him. Thanks, Jay Miller -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L How are the inserts being done? Are you doing an insert with append hint? -Original Message- Sent: Friday, November 29, 2002 12:59 PM To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-1653: unable to extend table - Why?
Okay, I just heard back from the developer. It was definitely not using either a Direct load or and Append hint. Just a regular insert. Any more ideas? -Original Message- Sent: Friday, November 29, 2002 1:39 PM To: Multiple recipients of list ORACLE-L Did you insert using direct path ? If so the insert inserts after the highwater mark. The highwater mark is not reinitialized after deletes. So maybe that's why the insert failed. --- Miller, Jay [EMAIL PROTECTED] a écrit : Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-1653: unable to extend table - Why?
Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
ORA-1653: unable to extend table - Why?
Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Do user processes apply against shmmax limit?
Good morning everyone, Thanks for confirming my belief. He was so definite I was starting to doubt myself (surely a Unix SA must know how the Unix parameters work right?). And the problem has been tracked down to a bad network switch (so I'm in the office again today to switch to our standby box while they work on it and then switch back when they're done). Jay Miller -Original Message- Sent: Saturday, November 23, 2002 9:44 PM To: Multiple recipients of list ORACLE-L Jay, I would suggest that your SA look at the 'w' column under procs. This shows that _since_ UNIX restart 23 jobs were continuously in the wait queue. Maybe something starts up on system reboot... procs memorypagedisk r b w swap free re mf pi po fr de sr s2 s4 s4 sd in 0 0 23 4366736 97528 1 2186 16 12 12 95520 0 0 0 0 0 1104 0 0 23 4365992 96056 1 451 16 24 52 85968 3 0 0 0 0 935 0 0 23 4364712 95512 2 310 36 24 492 85968 68 0 0 0 0 1036 Also, could he show you 'sar -q' stats? This should show any swapping (as opposed to paging). John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Miller, Jay [mailto:[EMAIL PROTECTED]] Sent: Saturday, November 23, 2002 10:49 AM To: Multiple recipients of list ORACLE-L Subject: Do user processes apply against shmmax limit? Hi everyone, I was always under the impression that the only concern with shmmax was that it be large enough for the SGA to fit into it. One of my System Administrators has just told me that the individual user processes (i.e., the PGA since we're not using multi-threaded server) get added to the SGA and if that SGA + user processes shmmax the system will start swapping. I haven't found anything to specifically address this issue on Metalink so I though I'd throw it open. We've started experiencing system slowdown and he says that increasing shmmax could resolve it. I'm skeptical (he also suggested increasing SGA to decrease swapping which I told him in no uncertain terms was nonsense). If anyone has a link to a note or white paper I'd appreciate that too. I've appended his email at the bottom. This slowdown seems to occur even when there's virtually on oracle activity so I'm suspecting some other cause. Thanks, Jay Miller nycsun1 and njsun7 has 6 GB of memory and only 2 GB of share memory. This morning nycsun1 was very slow and I noticed that there was lots of swaping. see vmstst and iostat below in red: procs memorypagedisk faults cpu r b w swap free re mf pi po fr de sr s2 s4 s4 sd in sy cs us sy id 0 0 23 4366736 97528 1 2186 16 12 12 95520 0 0 0 0 0 1104 3330 974 11 8 81 0 0 23 4365992 96056 1 451 16 24 52 85968 3 0 0 0 0 935 847 416 3 1 96 0 0 23 4364712 95512 2 310 36 24 492 85968 68 0 0 0 0 1036 2183 670 13 4 84 0 0 23 4361568 95488 9 2264 0 76 964 95520 136 0 0 0 0 979 4065 607 12 6 82 0 0 23 4362384 96080 1 6 4 8 8 77376 0 0 0 0 0 975 465 457 2 1 97 0 0 23 4361944 95712 4 730 92 48 532 95520 64 0 0 0 0 1040 1859 734 8 3 89 0 0 23 4360424 95480 4 41 36 40 100 77376 7 0 0 0 0 986 1250 542 6 0 94 0 0 23 4361304 96096 3 264 76 36 88 88496 7 0 0 0 0 1037 942 665 5 3 92 0 0 23 4359680 95784 2 449 4 28 84 95520 8 0 0 0 0 922 1047 374 4 1 95 0 0 23 4359936 95464 2 544 4 20 332 95520 44 0 0 0 0 931 1095 384 2 2 96 /s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 0.0 0.00.00.0 0.0 0.00.00.0 0 0 c2t6d0 0.0 34.50.0 270.0 0.2 13.86.7 399.5 6 44 c5t12d0 -- swap disk 0.0 34.50.0 270.0 0.5 10.7 15.5 309.4 18 39 c5t13d0 -- swap disk This shows that the system is not effectively using memory. I suggest increasing the share memory to 4 GB so that DBAs can increase their memory usage. Also set priority paging on. Priority paging will give application first priority then free memory will be allocated to file cache( Solaris 2.6 and 7. Solaris 8 is set dynamically). * ORACLE CONFIGS set shmsys:shminfo_shmmax =204800 -- increase to 409600 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=300 set shmsys:shminfo_shmseg=30 set semsys:seminfo_semmap=500 set semsys:seminfo_semmni=200 set semsys:seminfo_semmns=2000 set semsys:seminfo_semmsl=1000 set semsys:seminfo_semmnu=500 set semsys:seminfo_semume=150 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Do user processes apply against shmmax limit?
Hi everyone, I was always under the impression that the only concern with shmmax was that it be large enough for the SGA to fit into it. One of my System Administrators has just told me that the individual user processes (i.e., the PGA since we're not using multi-threaded server) get added to the SGA and if that SGA + user processes shmmax the system will start swapping. I haven't found anything to specifically address this issue on Metalink so I though I'd throw it open. We've started experiencing system slowdown and he says that increasing shmmax could resolve it. I'm skeptical (he also suggested increasing SGA to decrease swapping which I told him in no uncertain terms was nonsense). If anyone has a link to a note or white paper I'd appreciate that too. I've appended his email at the bottom. This slowdown seems to occur even when there's virtually on oracle activity so I'm suspecting some other cause. Thanks, Jay Miller nycsun1 and njsun7 has 6 GB of memory and only 2 GB of share memory. This morning nycsun1 was very slow and I noticed that there was lots of swaping. see vmstst and iostat below in red: procs memorypagedisk faults cpu r b w swap free re mf pi po fr de sr s2 s4 s4 sd in sy cs us sy id 0 0 23 4366736 97528 1 2186 16 12 12 95520 0 0 0 0 0 1104 3330 974 11 8 81 0 0 23 4365992 96056 1 451 16 24 52 85968 3 0 0 0 0 935 847 416 3 1 96 0 0 23 4364712 95512 2 310 36 24 492 85968 68 0 0 0 0 1036 2183 670 13 4 84 0 0 23 4361568 95488 9 2264 0 76 964 95520 136 0 0 0 0 979 4065 607 12 6 82 0 0 23 4362384 96080 1 6 4 8 8 77376 0 0 0 0 0 975 465 457 2 1 97 0 0 23 4361944 95712 4 730 92 48 532 95520 64 0 0 0 0 1040 1859 734 8 3 89 0 0 23 4360424 95480 4 41 36 40 100 77376 7 0 0 0 0 986 1250 542 6 0 94 0 0 23 4361304 96096 3 264 76 36 88 88496 7 0 0 0 0 1037 942 665 5 3 92 0 0 23 4359680 95784 2 449 4 28 84 95520 8 0 0 0 0 922 1047 374 4 1 95 0 0 23 4359936 95464 2 544 4 20 332 95520 44 0 0 0 0 931 1095 384 2 2 96 /s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 0.0 0.00.00.0 0.0 0.00.00.0 0 0 c2t6d0 0.0 34.50.0 270.0 0.2 13.86.7 399.5 6 44 c5t12d0 -- swap disk 0.0 34.50.0 270.0 0.5 10.7 15.5 309.4 18 39 c5t13d0 -- swap disk This shows that the system is not effectively using memory. I suggest increasing the share memory to 4 GB so that DBAs can increase their memory usage. Also set priority paging on. Priority paging will give application first priority then free memory will be allocated to file cache( Solaris 2.6 and 7. Solaris 8 is set dynamically). * ORACLE CONFIGS set shmsys:shminfo_shmmax =204800 -- increase to 409600 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=300 set shmsys:shminfo_shmseg=30 set semsys:seminfo_semmap=500 set semsys:seminfo_semmni=200 set semsys:seminfo_semmns=2000 set semsys:seminfo_semmsl=1000 set semsys:seminfo_semmnu=500 set semsys:seminfo_semume=150 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 specs of columns
Take a look at dba_cons_columns for primary and foreign key constraints Jay Miller -Original Message- Sent: Friday, November 15, 2002 11:59 AM To: Multiple recipients of list ORACLE-L I have a request from a developer to determine if a column is computed, has a primary key has a foreign key. Im looking through the dictionary tables eg user_tab_columns but these firlds seem to be elusive. Any ideas what views to querry ? Many thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
10046 Level 8 Trace file disappeared
After playing around with this in development for a while I just tried setting Event 10046, Level 8 trace on a production process that was taking too long to run. Everything went smoothly, the trace file was being written to, when suddenly the trace file disappeared. The last time I checked it was a little over 2 Meg but I had max file size set to 2 Gig. All the space was freed up according to df -k. I tried setting the event to level 0 and back to level 8 but there was still no trace file. The session was still running. Here's what I ran: exec sys.dbms_system.set_bool_param_in_session(265, 16097, 'timed_statistics', true); exec sys.dbms_system.set_int_param_in_session(265, 16097, 'max_dump_file_size', 2147483647); exec sys.dbms_system.set_ev(265,16097,10046, 8, ''); I'm on Oracle 8.1.7.2, Solaris 2.6. Does anyone have any ideas as to what might have happened to my file? Thanks, Jay Miller x48355 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: System Tablespace and Autoextend
Actually, SYSTEM is one of the only tablespaces I like to have it set on. While I originally set it to autoextend when I was doing an upgrade I left it active on the grounds that while I know how to recover if by some chance one of my datafiles runs out of space, I'm uncertain as to the implications of SYSTEM needing to extend and failing. -Original Message- Sent: Thursday, November 07, 2002 1:09 PM To: Multiple recipients of list ORACLE-L I use AUTOEXTEND and it has been extremely helpful to me in managing growth. However, my policy is not to use autoextend for SYSTEM, rollback tablespace, or temp tablespace. SYSTEM (for me) is relatively stable. The only time I have significant growth in SYSTEM is during an upgrade. For rollback and temp tablespaces, I don't want to throw away diskspace on unreasonable or abnormal usage. If I grow these spaces, I've made certain that it is necessary. Kip Bryant |FWIW I'd go with Dennis here. I don't like AUTOEXTEND on the SYSTEM |tablespace. |(In fact I'm not overenamoured of AUTOEXTEND on any datfile, except maybe on |dev and sandbox databases). |If the SYSTEM tablespace isn't used for rollbacks (apart from the SYSTEM |rollback) or temporary segments and the auditing information is written to |it's own tablespace then I can't think of a set of circumstances that would |cause the SYSTEM tablespace to rapidly fill up. |System upgrade/migrations are the exception to this rule but in general a |DBA would plan ahead for those occasions anyway. |-Original Message- |Sent: Thursday, November 07, 2002 4:29 PM |To: Multiple recipients of list ORACLE-L |I've been running with autoextend on (though limited to 2Gig) and never had |a problem. |-Original Message- |Sent: Thursday, October 24, 2002 5:26 PM |I run my SYSTEM tablesaces in autoextend, and have for some time. I run them |that way from the point of database creation and have never had a problem. |There were some problems with autoextend in earlier versions of 8 (and I |think they managed to migrate to early 8i versions as well) with 2GB |boundaries, but those have all been corrected. |RF |Robert G. Freeman - Oracle OCP |Oracle Database Architect |CSX Midtier Database Administration |-Original Message- |Sent: Thursday, October 24, 2002 4:46 PM |Sam - | I haven't made the system tablespace autoextend because I can't easily |recover the space if it overextends. I would rather take the risk that |something hits an error from a lack of space in the system tablespace. With |other tablespaces you can always rebuild the tablespace if you need to. |Dennis Williams |DBA, 40%OCP |Lifetouch, Inc. |[EMAIL PROTECTED] |-Original Message- |Sent: Thursday, October 24, 2002 1:27 PM |Hello All, |I have heard several times that if the SYSTEM tablespace runs out of space |and needs to autoextend (assuming autoextend is turned on for the data |file), then you run the risk of the database crashing and of data dictionary |corruption. I have never personally encountered this problem, so I have no |experience on what actually does happen. |I looked in metalink for documents on this, but turned up nothing. Does |anybody have experience on the dangers of allowing the SYSTEM tablespace to |autoextend and also any documents on Metalink or OTN that describe this |problem? |We are running Oracle versions 7.3.4, 8.0.5, 8.1.7, and 9.2. All our Oracle |versions are running on Windows NT (or Windows 2000). |Thanks for any feedback. |Sam Bootsma, OCP |[EMAIL PROTECTED] |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Hately, Mike (NESL-IT) | INET: [EMAIL PROTECTED] |Fat City Network Services-- 858-538-5051 http://www.fatcity.com |San Diego, California-- Mailing list and web hosting services |- |To REMOVE yourself from this mailing list, send an E-Mail message |to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in |the message BODY, include a line containing: UNSUB ORACLE-L |(or the name of mailing list you want to be removed from). You may |also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City
RE: Destination address unreachable
Hmm, the same way 8.0.1 was stable, and 8.1.5 was stable, and...? -Original Message- Sent: Friday, November 08, 2002 2:09 PM To: Multiple recipients of list ORACLE-L So, in my opinion, Oracle really has no choice but to pursue the course that it is. I think they have learned some lessons down the road, and I'm willing to bet that 10.0.1 (or whatever) will be far more stable than 9.0.1 was. RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
CLOB inserts
One of my developers is testing a process that reads a CLOB, modifies it, and inserts it to another table. The reading and modifying goes very quickly, but the inserts take a long time. Looking at the main Wait Events I'm seeing a lot of direct path read (lob) and direct path write (lob) which seems to imply that a lot of the delay is i/o related. Does anyone have any suggestions to speed up the inserts? The developer has been playing around with chunk size but without any improvement. Oracle 8.1.7.2 Solaris 2.6 Here are the main wait events from statspack. The SQL*Net message from dblink is from a different process that was running at the same time: SQL*Net message from dblink 381,218 0 0 0 689.4 SQL*Net message to dblink 381,216 0 0 0 689.4 db file scattered read 11,034 0 0 0 20.0 direct path read (lob) 1,233 0 0 0 2.2 log file parallel write 696 0 0 0 1.3 direct path write (lob) 548 0 0 0 1.0 db file sequential read 217 0 0 0 0.4 db file parallel write170 0 0 0 0.3 control file parallel write 161 0 0 0 0.3 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: System Tablespace and Autoextend
I've been running with autoextend on (though limited to 2Gig) and never had a problem. -Original Message- Sent: Thursday, October 24, 2002 5:26 PM To: Multiple recipients of list ORACLE-L I run my SYSTEM tablesaces in autoextend, and have for some time. I run them that way from the point of database creation and have never had a problem. There were some problems with autoextend in earlier versions of 8 (and I think they managed to migrate to early 8i versions as well) with 2GB boundaries, but those have all been corrected. RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -Original Message- Sent: Thursday, October 24, 2002 4:46 PM To: Multiple recipients of list ORACLE-L Sam - I haven't made the system tablespace autoextend because I can't easily recover the space if it overextends. I would rather take the risk that something hits an error from a lack of space in the system tablespace. With other tablespaces you can always rebuild the tablespace if you need to. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 24, 2002 1:27 PM To: Multiple recipients of list ORACLE-L Hello All, I have heard several times that if the SYSTEM tablespace runs out of space and needs to autoextend (assuming autoextend is turned on for the data file), then you run the risk of the database crashing and of data dictionary corruption. I have never personally encountered this problem, so I have no experience on what actually does happen. I looked in metalink for documents on this, but turned up nothing. Does anybody have experience on the dangers of allowing the SYSTEM tablespace to autoextend and also any documents on Metalink or OTN that describe this problem? We are running Oracle versions 7.3.4, 8.0.5, 8.1.7, and 9.2. All our Oracle versions are running on Windows NT (or Windows 2000). Thanks for any feedback. Sam Bootsma, OCP [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sam Bootsma INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 Purging Strategy
FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Can any one tell me how can i read parameter info from RDF(Re
You'll have to open the report using Oracle Developer (Reports). If it is on Unix I usually ftp it to my local workstation and open it there. Jay Miller x48355 -Original Message- Sent: Tuesday, November 05, 2002 1:13 AM To: Multiple recipients of list ORACLE-L definition file) Can any one tell me how can i read parameter info from RDF(Reports definition file) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Clone Production Server to Stand by Server on 8.1.7 on Win 2k
You don't need to rebuild when a datafile is added. Once the recovery fails you just issue the create datafile command on the standby. Jay Miller -Original Message- Sent: Tuesday, November 05, 2002 12:34 PM To: Multiple recipients of list ORACLE-L 2k First off, your process is doing a lot of unnecessary work. Standby databases are available in 7.3.4. I believe that would be somewhat simpler than your current procedure. I haven't tried it though, so I could be wrong. Even with your current procedure, you don't need to copy all of the files, most of the time anyway. Build your standby database, put it in recovery mode, and just keep applying archive log files to it. You may need to rebuild when a datafile is added to a tablespace, not sure. In 8i, why not just use standby database? Jared Arif Khan (GWL) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 06:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Clone Production Server to Stand by Server on 8.1.7 on Win 2k Hello We currently have two Identical servers (identical in terms of both Hardware and SW). We run Oracle 7.3.4.0 (Workgroup server) on Win NT 4 on both the servers. We call them as Production Server and Stand by server. The Stand by server is passive in nature (i.e. does not do anything). Every night a batch process shuts down Oracle instance on both the machines and copies over all the files (Data, log, ctl etc) from Production Server to the Stand by server (Drive to drive, directory to directory...) In case the Production Server fails, we simply switch over the users (with a different alias to the stand by server) and they are back in business. Now, we are thinking of migrating to 8.1.7, however while trying to install this version, one needs to specify a Global name which I believe has to be unique on the network. So will the same process that I used to run (i.e. copy all files over from production to stand by ) work??? I guess both my servers will now have to have separate/unique Global Names. Is there any other approach that any of you can suggest??? TIA Arif -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arif Khan (GWL) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 Purging Strategy
Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Adhoc queries and limiting the amount of records queried...
Aside from severely restricting access to analysts who need to investigate problems in production data we limit the CPU time in their profile to 5 minutes. That's a pretty generous limit and they've never had any issues with it. -Original Message- Sent: Tuesday, October 29, 2002 2:29 PM To: Multiple recipients of list ORACLE-L I just wanted to ping the list to see what other people have done to control or constrain adhoc query users??? We have a group that is struggling with the adhoc query piece that's in production. Some of the users end up firing off insane queries. The group is trying to find a way to limit the amount of records queried for, so that a wild query doesn't hose the database. Appending a ROWNUM to the WHERE clause is one idea. Using USER PROFILEs is another. Any other thoughts?? Dare I ask...this custom app also runs on SQL Server, so SQL Server ideas would also be appreciated. Many thanks!!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE:
That's DROP USER user_name CASCADE; Jay Miller -Original Message- Sent: Tuesday, October 22, 2002 6:49 AM To: Multiple recipients of list ORACLE-L You can do a DELETE USER User_Name CASCADE; But this will get rid of a whole schema. Dave -Original Message- Sent: Tuesday, October 22, 2002 4:33 AM To: Multiple recipients of list ORACLE-L What is the syntax to delete multiple objects, or more specifically multiple tables from a user in 1 go. I need to delete 50+ tables/objects without deleting them 1 by 1. Thanks Clint -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Creating new user
He mentions checking /oracle/bin, not $ORACLE_HOME/bin. Santosh: If this is a Windows environment (as it seems to be) you can check the Registry Editor for the value of ORACLE_HOME (it should be under HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE) -Original Message- Sent: Tuesday, October 22, 2002 12:05 PM To: Multiple recipients of list ORACLE-L Probably he is not in %ORACLE_HOME%/bin directory. A 'Find' for imp* on all drives would tell where it is hiding.. - Kirti -Original Message- Sent: Tuesday, October 22, 2002 9:34 AM To: Multiple recipients of list ORACLE-L Not really. dir imp* will list all files which start with 'imp' so it will list imp.exe, imp80.exe, imp.log etc But I'm clueless as to why there is nothing like imp or imp80 in the %oracle_home%/bin. Use some other machine to do the import, is the only solution i can offer Regards Naveen -Original Message- Sent: Tuesday, October 22, 2002 7:44 PM To: Multiple recipients of list ORACLE-L If it is windows, you'll have to use dir imp*.* to capture those with extensions of .exe Phil Wilson ([EMAIL PROTECTED]) DBA, Operations Group SkillSoft, Learning Solutions for the Human Enterprise 506.462.1124(w) 506.447.0334(c) -Original Message- Sent: Tuesday, October 22, 2002 10:49 AM To: Multiple recipients of list ORACLE-L What kind of client do you have? Maybe the install installed only net8 without the utilities? Check if you have exp*. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 22, 2002 3:14 PM but my friend... i gave the command dir imp* and there are no files found... so now where and how and what do i use my imagination santosh -Original Message- Sent: Tuesday, October 22, 2002 6:25 PM To: Multiple recipients of list ORACLE-L There were imp73 and imp80 for appropriate versions, I don't know about more recent ones Use your imagination, friend! :-) Gints Plivna, Softex Latvia, Tel. 7204520 Fax 7204260 http://www.softex.lv -Original Message- Sent: otrdiena, 2002. gada 22. oktobri 15:19 To: Multiple recipients of list ORACLE-L thanks ...but still i gave imp command on command prompt.. but it tells imp not found... and i checked the path also...i have given f:\oracle\bin as the path and there are no imp* files in oracle/bin. any ideas ? santosh -Original Message- Nahata Sent: Tuesday, October 22, 2002 5:40 PM To: Multiple recipients of list ORACLE-L SQL CREATE USER username IDENTIFIED BY password [DEFAULT TABLESPACE user_tablespace TEMPORARY TABLESPACE temp_tablespace] for importing on command prompt imp username/password@database fromuser=username touser=username file=export_dump_file If you just want the table structure and no data then use ROWS=N option too. regards Naveen -Original Message- Sent: Tuesday, October 22, 2002 5:14 PM To: Multiple recipients of list ORACLE-L how to create a new user ? in oracle 8.1.7 ? and i want to import a dump file into that user so that i could create the tables. How to acheive this ? any help will be appreciated. Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE:
That's the sort of thing that drove me crazy when I took the Certification exam. Niggling syntax issues that you'd figure out as soon as you typed the wrong command in... ;) Jay -Original Message- Sent: Tuesday, October 22, 2002 12:40 PM To: Multiple recipients of list ORACLE-L Doh, my bad. Send me to my room with no supper. -Original Message- Sent: Tuesday, October 22, 2002 10:58 AM To: Multiple recipients of list ORACLE-L That's DROP USER user_name CASCADE; Jay Miller -Original Message- Sent: Tuesday, October 22, 2002 6:49 AM To: Multiple recipients of list ORACLE-L You can do a DELETE USER User_Name CASCADE; But this will get rid of a whole schema. Dave -Original Message- Sent: Tuesday, October 22, 2002 4:33 AM To: Multiple recipients of list ORACLE-L What is the syntax to delete multiple objects, or more specifically multiple tables from a user in 1 go. I need to delete 50+ tables/objects without deleting them 1 by 1. Thanks Clint -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: admin_restrictions_listener
Although if you set the lsnrctl to 700 that problem goes away (that's what we did). I'm still amazed that it's world executable. Jay Miller -Original Message- Sent: Thursday, October 17, 2002 10:35 AM To: Multiple recipients of list ORACLE-L yup. i can run lsnrctl from my laptop somewhere on your network and stop the listener otherwise... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 17, 2002 3:38 AM hi all I have my listener.ora owned by the oracle owner with 600 permissions and admin_restrictions_listener set. On a machine in the DMZ. Is there any point in having a password as well? thanks trevor Disclaimer. This e-mail is private and confidential. If you are not the intended recipient, please advise us by return e-mail immediately, and delete the e-mail and any attachments without using or disclosing the contents in any way. The views expressed in this e-mail are those of the author, and do not represent those of this company unless this is clearly indicated. You should scan this e-mail and any attachments for viruses. This company accepts no liability for any direct or indirect damage or loss resulting from the use of any attachments to this e-mail. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: admin_restrictions_listener
Ah, you're right of course. I'm so used to doing everything from telnet sessions (even from my laptop) that I forgot it's possible to have the software locally. And of course I've made the mistake you mention (only once!) so I should have realized it. Jay -Original Message- Sent: Thursday, October 17, 2002 2:11 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Importance: High Jay, That doesn't solve the problem. What Tim was referring to was executing the copy of lsnrctl that exists on his laptop. I recall a DBA doing this accidentally at a previous job. He copied the listener.ora file to another server, then ran 'lsnrctl stop' before modifying the file for the new server. Guess what happened? Jared Miller, Jay [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/17/2002 11:29 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: admin_restrictions_listener Although if you set the lsnrctl to 700 that problem goes away (that's what we did). I'm still amazed that it's world executable. Jay Miller -Original Message- Sent: Thursday, October 17, 2002 10:35 AM To: Multiple recipients of list ORACLE-L yup. i can run lsnrctl from my laptop somewhere on your network and stop the listener otherwise... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 17, 2002 3:38 AM hi all I have my listener.ora owned by the oracle owner with 600 permissions and admin_restrictions_listener set. On a machine in the DMZ. Is there any point in having a password as well? thanks trevor Disclaimer. This e-mail is private and confidential. If you are not the intended recipient, please advise us by return e-mail immediately, and delete the e-mail and any attachments without using or disclosing the contents in any way. The views expressed in this e-mail are those of the author, and do not represent those of this company unless this is clearly indicated. You should scan this e-mail and any attachments for viruses. This company accepts no liability for any direct or indirect damage or loss resulting from the use of any attachments to this e-mail. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Advice needed on move to Sun 15K (losing spindles)
I obviously left out a lot of information :). We would be using server partitioning, with seperate ORACLE_HOMES for each database (necessary since we have a variety of versions running). The box would be running 1+0, the Sun reps suggest striping across all disks (my first red flag). I hadn't even thought of the problem of not being able to reboot the server, that's an excellent point. Currently we have absolutely no performance problems on our OLTP database. This whole kerfuffle was an outgrowth of my pushing really hard to get a backup box for our datawarehouse (which currently has no standby, no box that it can restored to and no QA box). The suggestion was made that rather than get a separate box for the datawarehouse - get the 15K and have the OLTP and datawarehouse on different partitions. This would certainly speed up the data transfer between them (data is transferred from OLTP - Data Warehouse on a daily basis). We could then put other databases that access my databases on other partitions (several other databases have snapshots on some of my tables). So this would make some processes more efficient, but i/o on my OLTP database is currently tuned so well that it hurts every time I think of giving it up. One spindle has the Oracle executables with the redo logs on the outside of the disk. Another has the various .dat files, shell scripts, etc, with the archive logs on the outside of the disk. Even when we run really intensive updates our wio rarely gets very high. Regarding the load question: We have fairly active transaction activity during the day but most connections are managed by Microsoft Transaction Server in a middle tier so while there are usually app. 200 sessions (including some old client server apps) we rarely have more than 20 or so active at any one time. The datawarehouse has fewer sessions but often has some resource intensive queries running. If anyone can point me to docs/websites saying that a large caches does *not* make up for fewer disks/spindles I would greatly appreciate it. Currently I'm being told that Sun must know what they're talking about. Thanks again, Jay Miller -Original Message- Sent: Wednesday, October 09, 2002 5:19 PM To: Multiple recipients of list ORACLE-L Others have addressed the performance issues. What about the admin issues? If consolidate to a single server, consider a separate ORACLE_HOME for each database. You may need to apply different patches to fix different problems in various databases. You have this ability now, but will lose it if you consolidate without separate ORACLE_HOME's. Something else you will lose is the ability to reboot the server if needed for a single database. Since you may be moving to a 15k, investigate server partitioning to retain this functionality. Jared Miller, Jay [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/09/2002 11:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Advice needed on move to Sun 15K (losing spindles) Our CIO has suggested that we get a Sun 15K to house all of our databases. This has some advantages (communication between the various boxes would be much faster) but I have some performance concerns. Specifically, our main OLTP database would go down from 18 spindles to 8 spindles. Mirroring will take away 4 of those leaving 4 spindles. The vendor (Sun) was recommending striping across all 4 spindles. He said we don't need to worry about i/o issues because there will be a large cache. I'm skeptical and argued for cutting them in half (striping 2 and 2). We could then at least seperate the redo logs from the datafiles (probably putting them with the oracle executables and some other files). The Sun rep kept talking up how much more powerful the CPUs were and I kept saying, but we're not CPU bound, we don't need any more CPU. If anyone can either a) tell me I'm worrying for nothing b) recommend a better way to stripe/distribute my files c) provide references or experience to show this is a bad idea I'd really appreciate it. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: Advice needed on move to Sun 15K (losing spindles)
Thanks Kirti! I loved the line The first thing to do, regardless of platform or claims by the vendor, is to completely forget the existence of a cache Any similar references will be greatly appreciated. The more ammunition I have the likelier I am to kill something :) Jay -Original Message- Sent: Friday, October 11, 2002 12:36 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] I suggest reviewing James Morle's paper 'Sane SAN' at http://www.oraperf.com/whitepapers.html. - Kirti -Original Message- Sent: Friday, October 11, 2002 11:15 AM To: Multiple recipients of list ORACLE-L I obviously left out a lot of information :). We would be using server partitioning, with seperate ORACLE_HOMES for each database (necessary since we have a variety of versions running). The box would be running 1+0, the Sun reps suggest striping across all disks (my first red flag). I hadn't even thought of the problem of not being able to reboot the server, that's an excellent point. Currently we have absolutely no performance problems on our OLTP database. This whole kerfuffle was an outgrowth of my pushing really hard to get a backup box for our datawarehouse (which currently has no standby, no box that it can restored to and no QA box). The suggestion was made that rather than get a separate box for the datawarehouse - get the 15K and have the OLTP and datawarehouse on different partitions. This would certainly speed up the data transfer between them (data is transferred from OLTP - Data Warehouse on a daily basis). We could then put other databases that access my databases on other partitions (several other databases have snapshots on some of my tables). So this would make some processes more efficient, but i/o on my OLTP database is currently tuned so well that it hurts every time I think of giving it up. One spindle has the Oracle executables with the redo logs on the outside of the disk. Another has the various .dat files, shell scripts, etc, with the archive logs on the outside of the disk. Even when we run really intensive updates our wio rarely gets very high. Regarding the load question: We have fairly active transaction activity during the day but most connections are managed by Microsoft Transaction Server in a middle tier so while there are usually app. 200 sessions (including some old client server apps) we rarely have more than 20 or so active at any one time. The datawarehouse has fewer sessions but often has some resource intensive queries running. If anyone can point me to docs/websites saying that a large caches does *not* make up for fewer disks/spindles I would greatly appreciate it. Currently I'm being told that Sun must know what they're talking about. Thanks again, Jay Miller -Original Message- Sent: Wednesday, October 09, 2002 5:19 PM To: Multiple recipients of list ORACLE-L Others have addressed the performance issues. What about the admin issues? If consolidate to a single server, consider a separate ORACLE_HOME for each database. You may need to apply different patches to fix different problems in various databases. You have this ability now, but will lose it if you consolidate without separate ORACLE_HOME's. Something else you will lose is the ability to reboot the server if needed for a single database. Since you may be moving to a 15k, investigate server partitioning to retain this functionality. Jared Miller, Jay [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/09/2002 11:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Advice needed on move to Sun 15K (losing spindles) Our CIO has suggested that we get a Sun 15K to house all of our databases. This has some advantages (communication between the various boxes would be much faster) but I have some performance concerns. Specifically, our main OLTP database would go down from 18 spindles to 8 spindles. Mirroring will take away 4 of those leaving 4 spindles. The vendor (Sun) was recommending striping across all 4 spindles. He said we don't need to worry about i/o issues because there will be a large cache. I'm skeptical and argued for cutting them in half (striping 2 and 2). We could then at least seperate the redo logs from the datafiles (probably putting them with the oracle executables and some other files). The Sun rep kept talking up how much more powerful the CPUs were and I kept saying, but we're not CPU bound, we don't need any more CPU. If anyone can either a) tell me I'm worrying for nothing b) recommend a better way to stripe/distribute my files c) provide references or experience to show this is a bad idea I'd really appreciate it. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego
RE: Advice needed on move to Sun 15K (losing spindles)
Yes, it's entirely separate CPUs and disks. If I can believe the Sun rep (ehem) there should be no interference. -Original Message- Sent: Friday, October 11, 2002 3:30 PM To: Multiple recipients of list ORACLE-L Jay - Will your server partitioning protect the OLTP users from the DW queries? In the normal situation, a company first adds their DW to an existing system. Then they find that the DW doesn't make a good neighbor and buy a separate server. The DW typically does a LOT of full-table scans, so if you share disks, that may not be good for your OLTP. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, October 11, 2002 11:15 AM To: Multiple recipients of list ORACLE-L I obviously left out a lot of information :). We would be using server partitioning, with seperate ORACLE_HOMES for each database (necessary since we have a variety of versions running). The box would be running 1+0, the Sun reps suggest striping across all disks (my first red flag). I hadn't even thought of the problem of not being able to reboot the server, that's an excellent point. Currently we have absolutely no performance problems on our OLTP database. This whole kerfuffle was an outgrowth of my pushing really hard to get a backup box for our datawarehouse (which currently has no standby, no box that it can restored to and no QA box). The suggestion was made that rather than get a separate box for the datawarehouse - get the 15K and have the OLTP and datawarehouse on different partitions. This would certainly speed up the data transfer between them (data is transferred from OLTP - Data Warehouse on a daily basis). We could then put other databases that access my databases on other partitions (several other databases have snapshots on some of my tables). So this would make some processes more efficient, but i/o on my OLTP database is currently tuned so well that it hurts every time I think of giving it up. One spindle has the Oracle executables with the redo logs on the outside of the disk. Another has the various .dat files, shell scripts, etc, with the archive logs on the outside of the disk. Even when we run really intensive updates our wio rarely gets very high. Regarding the load question: We have fairly active transaction activity during the day but most connections are managed by Microsoft Transaction Server in a middle tier so while there are usually app. 200 sessions (including some old client server apps) we rarely have more than 20 or so active at any one time. The datawarehouse has fewer sessions but often has some resource intensive queries running. If anyone can point me to docs/websites saying that a large caches does *not* make up for fewer disks/spindles I would greatly appreciate it. Currently I'm being told that Sun must know what they're talking about. Thanks again, Jay Miller -Original Message- Sent: Wednesday, October 09, 2002 5:19 PM To: Multiple recipients of list ORACLE-L Others have addressed the performance issues. What about the admin issues? If consolidate to a single server, consider a separate ORACLE_HOME for each database. You may need to apply different patches to fix different problems in various databases. You have this ability now, but will lose it if you consolidate without separate ORACLE_HOME's. Something else you will lose is the ability to reboot the server if needed for a single database. Since you may be moving to a 15k, investigate server partitioning to retain this functionality. Jared Miller, Jay [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/09/2002 11:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Advice needed on move to Sun 15K (losing spindles) Our CIO has suggested that we get a Sun 15K to house all of our databases. This has some advantages (communication between the various boxes would be much faster) but I have some performance concerns. Specifically, our main OLTP database would go down from 18 spindles to 8 spindles. Mirroring will take away 4 of those leaving 4 spindles. The vendor (Sun) was recommending striping across all 4 spindles. He said we don't need to worry about i/o issues because there will be a large cache. I'm skeptical and argued for cutting them in half (striping 2 and 2). We could then at least seperate the redo logs from the datafiles (probably putting them with the oracle executables and some other files). The Sun rep kept talking up how much more powerful the CPUs were and I kept saying, but we're not CPU bound, we don't need any more CPU. If anyone can either a) tell me I'm worrying for nothing b) recommend a better way to stripe/distribute my files c) provide references or experience to show this is a bad idea I'd really appreciate it. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http
RE: Advice needed on move to Sun 15K (losing spindles)
Thanks, I'm reading the first one now. Jay -Original Message- Sent: Friday, October 11, 2002 3:19 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Well, there are Gaja's papers : Proactive Storage Management - A Method to Predictable System Performance, and Implementing RAID on Oracle systems available at http://www.quest.com/whitepapers. Scan the page for Title and for not Gaja's name. - Kirti -Original Message- Sent: Friday, October 11, 2002 1:20 PM To: Multiple recipients of list ORACLE-L Thanks Kirti! I loved the line The first thing to do, regardless of platform or claims by the vendor, is to completely forget the existence of a cache Any similar references will be greatly appreciated. The more ammunition I have the likelier I am to kill something :) Jay -Original Message- Sent: Friday, October 11, 2002 12:36 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] I suggest reviewing James Morle's paper 'Sane SAN' at http://www.oraperf.com/whitepapers.html. - Kirti -Original Message- Sent: Friday, October 11, 2002 11:15 AM To: Multiple recipients of list ORACLE-L I obviously left out a lot of information :). We would be using server partitioning, with seperate ORACLE_HOMES for each database (necessary since we have a variety of versions running). The box would be running 1+0, the Sun reps suggest striping across all disks (my first red flag). I hadn't even thought of the problem of not being able to reboot the server, that's an excellent point. Currently we have absolutely no performance problems on our OLTP database. This whole kerfuffle was an outgrowth of my pushing really hard to get a backup box for our datawarehouse (which currently has no standby, no box that it can restored to and no QA box). The suggestion was made that rather than get a separate box for the datawarehouse - get the 15K and have the OLTP and datawarehouse on different partitions. This would certainly speed up the data transfer between them (data is transferred from OLTP - Data Warehouse on a daily basis). We could then put other databases that access my databases on other partitions (several other databases have snapshots on some of my tables). So this would make some processes more efficient, but i/o on my OLTP database is currently tuned so well that it hurts every time I think of giving it up. One spindle has the Oracle executables with the redo logs on the outside of the disk. Another has the various .dat files, shell scripts, etc, with the archive logs on the outside of the disk. Even when we run really intensive updates our wio rarely gets very high. Regarding the load question: We have fairly active transaction activity during the day but most connections are managed by Microsoft Transaction Server in a middle tier so while there are usually app. 200 sessions (including some old client server apps) we rarely have more than 20 or so active at any one time. The datawarehouse has fewer sessions but often has some resource intensive queries running. If anyone can point me to docs/websites saying that a large caches does *not* make up for fewer disks/spindles I would greatly appreciate it. Currently I'm being told that Sun must know what they're talking about. Thanks again, Jay Miller -Original Message- Sent: Wednesday, October 09, 2002 5:19 PM To: Multiple recipients of list ORACLE-L Others have addressed the performance issues. What about the admin issues? If consolidate to a single server, consider a separate ORACLE_HOME for each database. You may need to apply different patches to fix different problems in various databases. You have this ability now, but will lose it if you consolidate without separate ORACLE_HOME's. Something else you will lose is the ability to reboot the server if needed for a single database. Since you may be moving to a 15k, investigate server partitioning to retain this functionality. Jared Miller, Jay [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/09/2002 11:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Advice needed on move to Sun 15K (losing spindles) Our CIO has suggested that we get a Sun 15K to house all of our databases. This has some advantages (communication between the various boxes would be much faster) but I have some performance concerns. Specifically, our main OLTP database would go down from 18 spindles to 8 spindles. Mirroring will take away 4 of those leaving 4 spindles. The vendor (Sun) was recommending striping across all 4 spindles. He said we don't need to worry about i/o issues because there will be a large cache. I'm skeptical and argued for cutting them in half (striping 2 and 2). We could then at least seperate the redo logs from the datafiles (probably putting them with the oracle executables and some other files). The Sun rep kept talking up how much more
RE: Advice needed on move to Sun 15K (losing spindles)
Thank you very much! I can tell what I'll be reading this weekend :). With highlighter in hand... Jay -Original Message- Sent: Friday, October 11, 2002 4:31 PM To: Multiple recipients of list ORACLE-L Check out www.hotsos.com/dnloads/1.Littlefield2000.01.03-Specs.pdf, written a couple of years ago by Jim Littlefield of Real Networks. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Jay Sent: Friday, October 11, 2002 1:20 PM To: Multiple recipients of list ORACLE-L Thanks Kirti! I loved the line The first thing to do, regardless of platform or claims by the vendor, is to completely forget the existence of a cache Any similar references will be greatly appreciated. The more ammunition I have the likelier I am to kill something :) Jay -Original Message- Sent: Friday, October 11, 2002 12:36 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] I suggest reviewing James Morle's paper 'Sane SAN' at http://www.oraperf.com/whitepapers.html. - Kirti -Original Message- Sent: Friday, October 11, 2002 11:15 AM To: Multiple recipients of list ORACLE-L I obviously left out a lot of information :). We would be using server partitioning, with seperate ORACLE_HOMES for each database (necessary since we have a variety of versions running). The box would be running 1+0, the Sun reps suggest striping across all disks (my first red flag). I hadn't even thought of the problem of not being able to reboot the server, that's an excellent point. Currently we have absolutely no performance problems on our OLTP database. This whole kerfuffle was an outgrowth of my pushing really hard to get a backup box for our datawarehouse (which currently has no standby, no box that it can restored to and no QA box). The suggestion was made that rather than get a separate box for the datawarehouse - get the 15K and have the OLTP and datawarehouse on different partitions. This would certainly speed up the data transfer between them (data is transferred from OLTP - Data Warehouse on a daily basis). We could then put other databases that access my databases on other partitions (several other databases have snapshots on some of my tables). So this would make some processes more efficient, but i/o on my OLTP database is currently tuned so well that it hurts every time I think of giving it up. One spindle has the Oracle executables with the redo logs on the outside of the disk. Another has the various .dat files, shell scripts, etc, with the archive logs on the outside of the disk. Even when we run really intensive updates our wio rarely gets very high. Regarding the load question: We have fairly active transaction activity during the day but most connections are managed by Microsoft Transaction Server in a middle tier so while there are usually app. 200 sessions (including some old client server apps) we rarely have more than 20 or so active at any one time. The datawarehouse has fewer sessions but often has some resource intensive queries running. If anyone can point me to docs/websites saying that a large caches does *not* make up for fewer disks/spindles I would greatly appreciate it. Currently I'm being told that Sun must know what they're talking about. Thanks again, Jay Miller -Original Message- Sent: Wednesday, October 09, 2002 5:19 PM To: Multiple recipients of list ORACLE-L Others have addressed the performance issues. What about the admin issues? If consolidate to a single server, consider a separate ORACLE_HOME for each database. You may need to apply different patches to fix different problems in various databases. You have this ability now, but will lose it if you consolidate without separate ORACLE_HOME's. Something else you will lose is the ability to reboot the server if needed for a single database. Since you may be moving to a 15k, investigate server partitioning to retain this functionality. Jared Miller, Jay [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/09/2002 11:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Advice needed on move to Sun 15K (losing spindles) Our CIO has suggested that we get a Sun 15K to house all of our databases. This has some advantages (communication between the various boxes would be much faster) but I have some performance concerns. Specifically, our main OLTP database would go down from 18 spindles to 8 spindles. Mirroring will take away 4 of those leaving 4 spindles. The vendor (Sun) was recommending striping across all 4 spindles. He said we don't need to worry about i/o issues because there will be a large cache. I'm skeptical and argued for cutting them in half (striping
RE: Advice needed on move to Sun 15K (losing spindles)
Fortunately my SA believes that so we were able to present a united front at the presentation (and yes, the Sun rep said that with a large enough cache RAID 5 works just as well as 1+0 - which is what we would be using). Jay Miller -Original Message- Sent: Friday, October 11, 2002 4:54 PM To: Multiple recipients of list ORACLE-L One thing that should be made clear: Never, ever, stripe with parity (i.e. RAID 5, etc.) unless you are force, at gunpoint, to do it. That is BAD. Your database will run faster on an abacus ... well ... maybe a slide rule. -Original Message- Yes, it's entirely separate CPUs and disks. If I can believe the Sun rep (ehem) there should be no interference. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Advice needed on move to Sun 15K (losing spindles)
Our CIO has suggested that we get a Sun 15K to house all of our databases. This has some advantages (communication between the various boxes would be much faster) but I have some performance concerns. Specifically, our main OLTP database would go down from 18 spindles to 8 spindles. Mirroring will take away 4 of those leaving 4 spindles. The vendor (Sun) was recommending striping across all 4 spindles. He said we don't need to worry about i/o issues because there will be a large cache. I'm skeptical and argued for cutting them in half (striping 2 and 2). We could then at least seperate the redo logs from the datafiles (probably putting them with the oracle executables and some other files). The Sun rep kept talking up how much more powerful the CPUs were and I kept saying, but we're not CPU bound, we don't need any more CPU. If anyone can either a) tell me I'm worrying for nothing b) recommend a better way to stripe/distribute my files c) provide references or experience to show this is a bad idea I'd really appreciate it. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backups
, include a line containing: UNSUB ORACLE-L (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: 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
A really stupid question
How can I access the Oracle-L archives? There's something I remember reading a few months ago that I want to look up. Embarassedly yours, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: help!! smon
SMON is the Oracle background process that handles recovery. Did you perhaps do a shutdown abort and then restart your database? If so SMON is rolling forward and rolling back uncommitted transaction. -Original Message- Sent: Friday, October 04, 2002 4:44 PM To: Multiple recipients of list ORACLE-L Hi gurus ,,, i need check what do you doing the smon proc!!! this process have 99% of CPU somebody help me!!! @lex Lic. Alexander Ordóñez Arroyo Soporte Tru64Unix BD Oracle Caja Costarricense del Seguro Social Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED] Celular 397-0532 The truth is out there in WWW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexander Ordonez INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 8.1.6 to 8.1.7 upgrade
If you have java installed be aware that there are a lot of changes with 8.1.7. If you have problems with the upgrade you *cannot* simply run rmjvm.sql and initjvm.sql There are two documents on removing and installing java which are must reads (I don't have the numbers handy, sorry). Allow a lot of time for the java upgrade. In the end I found it easier to remove java and do a fresh install (it helped that we weren't actually using it outside of development at the time). Good luck! Jay Miller -Original Message- Sent: Tuesday, October 01, 2002 11:49 AM To: Multiple recipients of list ORACLE-L Gurus! Any docs / URLs / do's don'ts / tips for 8.1.6 to 8.1.7 upgrade? I have gone throught the metalink docs and Oracle Documentation. Thanks, -Rachna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachna Vaidya INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 work load
Actually I called the group that handles 24 hour monitoring and emailed anyone else who might potentially be interested (hence getting in early the next day). The next day I was called on the carpet because I didn't get the name of the person I spoke with the previous night and they had to wait for the night shift to come in to find out who I spoke with to put it in their report. My boss' boss reads Dilbert as a management guide... Jay Miller -Original Message- Sent: Friday, September 27, 2002 9:03 AM To: Multiple recipients of list ORACLE-L Must say I liked Inka's notion of calling all interested parties with details, though it could be income threatening =:-0 Maybe a compromise would be to agree some type of SOP for such situations and have the interested callers list put in there. I mean if they are so keen to know first thing next morning... OTOH, why not send a summary e-mail to interested parties and tell them you'll fill in any gaps when you have caught up on lost time. If the problem has been resolved, details of how are of a mainly historical nature anyhow - right? Tim: Can you give me the ISBN's to some of your novels. What!!! You haven't written any yet! - A waste of talent ;) Thanks for the homour in any case. PS: The BDBAFH has gone right over my head. My psyche is suggesting it's profane. What does it stand for? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 work load
Yes, of course. If, like last night, I needed to dial in from home for 2 hours then I get compensated by coming in early the next day to answer questions about what happened. -Original Message- Sent: Tuesday, September 24, 2002 11:08 AM To: Multiple recipients of list ORACLE-L Obviously, not all of those 168 hours are equally intense, requiring a conscious person to be available on-call (you *do* get compensated for being on-call during off-hours, don't you?). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 DROP COLUMNS CONTINUE - how far has it gotten
Thanks Tim. That gives me somewhere to look. I'll set up a test table sometime this week and try it out for future reference. If I come up with anything I'll post it back here. Jay -Original Message- Sent: Sunday, September 22, 2002 6:23 PM To: Multiple recipients of list ORACLE-L Jay, I've not played with that particular command lately, but it is likely that V$TRANSACTION can provide some info. For example, when I'm doing a TRUNCATE on a large table, I have found that the value in the column USED_UREC (i.e. number of undo records created) on V$TRANSACTION corresponded to the number of extents in the table being truncated. When I queried DBA_SEGMENTS to find the number of extents for the table, I found that the TRUNCATE finished when USED_UREC hits the same number as the number of extents, hence my assumption about the meaning of the value in USED_UREC. I don't know if you might be able to find similar info for a DROP COLUMNS command, but I'd guess that USED_UREC might correspond to the number of rows being modified by the DROP COLUMN, so having the original NUM_ROWS or COUNT(*) on the table might be helpful. Since you are doing a CONTINUE operation, even if this was true it might be difficult to gauge where you are currently since you probably don't know how many rows you had processed in the previous transaction(s)... For query purposes, V$SESSION.TADDR joins to V$TRANSACTION.ADDR... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, September 22, 2002 3:03 PM A drop unused columns checkpoint was interrupted opn a large table. As a result the table is not readable while we are running ALTER TABLE DROP COLUMNS CONTINUE Is there any way to determine how far it has gotten? There is no entry in V$SESSION_LONGOPS. Thanks. Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ALTER TABLE DROP COLUMNS CONTINUE - how far has it gotten
A drop unused columns checkpoint was interrupted opn a large table. As a result the table is not readable while we are running ALTER TABLE DROP COLUMNS CONTINUE Is there any way to determine how far it has gotten? There is no entry in V$SESSION_LONGOPS. Thanks. Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backup Strategy - Informal Survey
of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Consulting Position Available-Oracle DBA/Developer/NYC
But 4th normal form? Does anyone really use this? -Original Message- Sent: Tuesday, September 17, 2002 3:35 PM To: Multiple recipients of list ORACLE-L well the market must be picking up some, been definitely more reqs posted here a/o recent. joe Bill Christison wrote: Interested candidates reply to: [EMAIL PROTECTED] * Consulting Position Title: Oracle DBA/Developer Duties and Responsibilities: This position is a conversion to an Oracle Data warehouse. Excellent verbal skills are needed to obtain technical specification from the architect and users then to transform into written specifications. Experience Required: *Must have experience in PL SQL and T SQL *Must have 3rd and 4th form data normalization *Must have done business systems analysis Consulting Assignment Duration: 6 months to 12 months Assignment Location: New York City = Bill Christison Knowlton Group, LLC 845-258-5129 www.knowltongroup.com __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Recreating database from hot backup but only 1 tablespace - U
Thanks to everyone who helped, I finally got the files restored to a new box (1 week later!) and the recovery and export took about a half hour. Jay Miller -Original Message- Sent: Monday, September 09, 2002 6:53 PM To: Multiple recipients of list ORACLE-L U Jay - Here is a link to the Oracle documentation for what you are attempting. You'll have to patch the link back together. I don't know what your Oracle version is, but the main change with the newer version is that you can use transportable tablespaces to move the resulting tablespace back to production, while on the older Oracle versions you must use something like export/import. Well, I am glad my study for the OCP benefited you, rather than me. I'm heading home for the evening, but there are other list participants in different time zones if you hit a glitch. Best of luck. http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90134/os tspitr.htm Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 5:38 PM To: Multiple recipients of list ORACLE-L U Thanks Dennis, The rollback segments occurred to me already, I'm composing the email to our SAs to restore the files (I'm going to have to wipe out another test box but it's one that can easily be recreated). Thanks! Jay Miller -Original Message- Sent: Monday, September 09, 2002 5:14 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' U rgen Jay You should also bring the tablespace holding your ROLLBACK segments. Archive and redo logs, of course. Other than that, I think you are off to a promising start. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Urgen We need to recover a table that had been truncated. Unfortunately at this time we don't have a server large enough to copy our complete backup (I know, I've been yelling about this for over a year). Can we copy just the tablespaces holding the SYS objects and the table we need, modify our create controlfile script (backed up to trace) and open it that way? I've never tried this before and it's rather urgent so I'm e-mailing while I'm still searching the Velpuri book. Any other suggestions are welcome. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 causes table to grow
Clever thought, but I should have supplied the size before. It grew from app. 4Gig to app. 6Gig. That's why I was so surprised, growing 2 Gig during a Move was rather unexpected. -Original Message- Sent: Tuesday, September 10, 2002 4:03 PM To: Multiple recipients of list ORACLE-L I hope this isn't overly simplistic but, is it because of the larger extent size? For instance, if it was in 4m extents before and used 26M, it would have fit in 7 extents with 2M of free space. But when moved to 25M extents, it would use 2 extents with 24M free space. How many segments is the table using, and what's the real size ( ie. sum(bytes))? Beth -Original Message- Sent: Tuesday, September 10, 2002 2:43 PM To: Multiple recipients of list ORACLE-L DEGREE=1 -Original Message- Sent: Monday, September 09, 2002 8:38 PM To: Multiple recipients of list ORACLE-L What is the DEGREE setting on the table? -Original Message- Sent: Monday, September 09, 2002 4:48 PM To: Multiple recipients of list ORACLE-L alter table email_request_queue move tablespace ts_email_request storage (initial 25m next 25m pctincrease 0); -Original Message- Sent: Monday, September 09, 2002 1:54 PM To: Multiple recipients of list ORACLE-L How did you perform the move? Was the operation done in parallel? -Original Message- Sent: Friday, September 06, 2002 4:13 PM To: Multiple recipients of list ORACLE-L No LOBS. Here's the definition: REQUEST_IDNOT NULL NUMBER PROFILE_IDNOT NULL NUMBER ACCOUNT_IDNOT NULL NUMBER TEMPLATE_ID NOT NULL NUMBER GENERIC_DIFFERENTIATORNOT NULL NUMBER REQUEST_TYPE NOT NULL CHAR(1) ACCOUNT_NONOT NULL CHAR(8) EFFECTIVE_DATENOT NULL DATE EMAIL_ADDRESS NOT NULL VARCHAR2(100) EMAIL_SUBJECT NOT NULL VARCHAR2(100) EMAIL_BODYNOT NULL VARCHAR2(4000) STATUSNOT NULL CHAR(1) STATUS_CHANGE_DATENOT NULL DATE TWEED_SERVER_IDNUMBER TWEED_PACKAGE_PRIORITY NUMBER TWEED_SENDER_ACCOUNT NOT NULL VARCHAR2(50) TWEED_SCHEDULED_DELIVERY_DATE NOT NULL DATE TWEED_CUSTOMER_URLNOT NULL VARCHAR2(255) SENDER_SERVER_ID NUMBER SENDER_INSTANCE_ID NUMBER CREATE_DATE NOT NULL DATE CREATE_USER NOT NULL VARCHAR2(35) UPDATE_DATEDATE UPDATE_USERVARCHAR2(35) -Original Message- Sent: Thursday, September 05, 2002 5:23 PM To: Multiple recipients of list ORACLE-L What the table definition? Are there any LOB's on it? -Original Message- Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ
RE: ALTER TABLE MOVE command causes table to grow
DEGREE=1 -Original Message- Sent: Monday, September 09, 2002 8:38 PM To: Multiple recipients of list ORACLE-L What is the DEGREE setting on the table? -Original Message- Sent: Monday, September 09, 2002 4:48 PM To: Multiple recipients of list ORACLE-L alter table email_request_queue move tablespace ts_email_request storage (initial 25m next 25m pctincrease 0); -Original Message- Sent: Monday, September 09, 2002 1:54 PM To: Multiple recipients of list ORACLE-L How did you perform the move? Was the operation done in parallel? -Original Message- Sent: Friday, September 06, 2002 4:13 PM To: Multiple recipients of list ORACLE-L No LOBS. Here's the definition: REQUEST_IDNOT NULL NUMBER PROFILE_IDNOT NULL NUMBER ACCOUNT_IDNOT NULL NUMBER TEMPLATE_ID NOT NULL NUMBER GENERIC_DIFFERENTIATORNOT NULL NUMBER REQUEST_TYPE NOT NULL CHAR(1) ACCOUNT_NONOT NULL CHAR(8) EFFECTIVE_DATENOT NULL DATE EMAIL_ADDRESS NOT NULL VARCHAR2(100) EMAIL_SUBJECT NOT NULL VARCHAR2(100) EMAIL_BODYNOT NULL VARCHAR2(4000) STATUSNOT NULL CHAR(1) STATUS_CHANGE_DATENOT NULL DATE TWEED_SERVER_IDNUMBER TWEED_PACKAGE_PRIORITY NUMBER TWEED_SENDER_ACCOUNT NOT NULL VARCHAR2(50) TWEED_SCHEDULED_DELIVERY_DATE NOT NULL DATE TWEED_CUSTOMER_URLNOT NULL VARCHAR2(255) SENDER_SERVER_ID NUMBER SENDER_INSTANCE_ID NUMBER CREATE_DATE NOT NULL DATE CREATE_USER NOT NULL VARCHAR2(35) UPDATE_DATEDATE UPDATE_USERVARCHAR2(35) -Original Message- Sent: Thursday, September 05, 2002 5:23 PM To: Multiple recipients of list ORACLE-L What the table definition? Are there any LOB's on it? -Original Message- Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538
RE: ALTER TABLE MOVE command causes table to grow
alter table email_request_queue move tablespace ts_email_request storage (initial 25m next 25m pctincrease 0); -Original Message- Sent: Monday, September 09, 2002 1:54 PM To: Multiple recipients of list ORACLE-L How did you perform the move? Was the operation done in parallel? -Original Message- Sent: Friday, September 06, 2002 4:13 PM To: Multiple recipients of list ORACLE-L No LOBS. Here's the definition: REQUEST_IDNOT NULL NUMBER PROFILE_IDNOT NULL NUMBER ACCOUNT_IDNOT NULL NUMBER TEMPLATE_ID NOT NULL NUMBER GENERIC_DIFFERENTIATORNOT NULL NUMBER REQUEST_TYPE NOT NULL CHAR(1) ACCOUNT_NONOT NULL CHAR(8) EFFECTIVE_DATENOT NULL DATE EMAIL_ADDRESS NOT NULL VARCHAR2(100) EMAIL_SUBJECT NOT NULL VARCHAR2(100) EMAIL_BODYNOT NULL VARCHAR2(4000) STATUSNOT NULL CHAR(1) STATUS_CHANGE_DATENOT NULL DATE TWEED_SERVER_IDNUMBER TWEED_PACKAGE_PRIORITY NUMBER TWEED_SENDER_ACCOUNT NOT NULL VARCHAR2(50) TWEED_SCHEDULED_DELIVERY_DATE NOT NULL DATE TWEED_CUSTOMER_URLNOT NULL VARCHAR2(255) SENDER_SERVER_ID NUMBER SENDER_INSTANCE_ID NUMBER CREATE_DATE NOT NULL DATE CREATE_USER NOT NULL VARCHAR2(35) UPDATE_DATEDATE UPDATE_USERVARCHAR2(35) -Original Message- Sent: Thursday, September 05, 2002 5:23 PM To: Multiple recipients of list ORACLE-L What the table definition? Are there any LOB's on it? -Original Message- Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
Recreating database from hot backup but only 1 tablespace - Urgen
We need to recover a table that had been truncated. Unfortunately at this time we don't have a server large enough to copy our complete backup (I know, I've been yelling about this for over a year). Can we copy just the tablespaces holding the SYS objects and the table we need, modify our create controlfile script (backed up to trace) and open it that way? I've never tried this before and it's rather urgent so I'm e-mailing while I'm still searching the Velpuri book. Any other suggestions are welcome. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Recreating database from hot backup but only 1 tablespace - U
Thanks Dennis, The rollback segments occurred to me already, I'm composing the email to our SAs to restore the files (I'm going to have to wipe out another test box but it's one that can easily be recreated). Thanks! Jay Miller -Original Message- Sent: Monday, September 09, 2002 5:14 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' U rgen Jay You should also bring the tablespace holding your ROLLBACK segments. Archive and redo logs, of course. Other than that, I think you are off to a promising start. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Urgen We need to recover a table that had been truncated. Unfortunately at this time we don't have a server large enough to copy our complete backup (I know, I've been yelling about this for over a year). Can we copy just the tablespaces holding the SYS objects and the table we need, modify our create controlfile script (backed up to trace) and open it that way? I've never tried this before and it's rather urgent so I'm e-mailing while I'm still searching the Velpuri book. Any other suggestions are welcome. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ALTER TABLE MOVE command causes table to grow
No LOBS. Here's the definition: REQUEST_IDNOT NULL NUMBER PROFILE_IDNOT NULL NUMBER ACCOUNT_IDNOT NULL NUMBER TEMPLATE_ID NOT NULL NUMBER GENERIC_DIFFERENTIATORNOT NULL NUMBER REQUEST_TYPE NOT NULL CHAR(1) ACCOUNT_NONOT NULL CHAR(8) EFFECTIVE_DATENOT NULL DATE EMAIL_ADDRESS NOT NULL VARCHAR2(100) EMAIL_SUBJECT NOT NULL VARCHAR2(100) EMAIL_BODYNOT NULL VARCHAR2(4000) STATUSNOT NULL CHAR(1) STATUS_CHANGE_DATENOT NULL DATE TWEED_SERVER_IDNUMBER TWEED_PACKAGE_PRIORITY NUMBER TWEED_SENDER_ACCOUNT NOT NULL VARCHAR2(50) TWEED_SCHEDULED_DELIVERY_DATE NOT NULL DATE TWEED_CUSTOMER_URLNOT NULL VARCHAR2(255) SENDER_SERVER_ID NUMBER SENDER_INSTANCE_ID NUMBER CREATE_DATE NOT NULL DATE CREATE_USER NOT NULL VARCHAR2(35) UPDATE_DATEDATE UPDATE_USERVARCHAR2(35) -Original Message- Sent: Thursday, September 05, 2002 5:23 PM To: Multiple recipients of list ORACLE-L What the table definition? Are there any LOB's on it? -Original Message- Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
ALTER TABLE MOVE command causes table to grow
Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PCTUSED - when is block added to freelist?
So my understanding from this is that a) A table has a PCTUSED of 40 which is changed to 75 b) A block is 60% free c) This block will be added to the freelist if an update or delete affects it (it was not added when the PCTUSED was changed). d) So my next question (which I may have to try and test myself): Would a global update on the table that didn't change anything move the blocks that fit the new PCTUSED criteria to the freelist? For example: update big_table set column1=column1; This would touch every block, at the end of the update the block would be below the new PCTUSED, but no data actually changes. Would this make the block available for inserts? Hmm, this is getting interesting! Jay Miller -Original Message- Sent: Wednesday, September 04, 2002 12:33 PM To: Multiple recipients of list ORACLE-L See Note: 1029850.6 on MetaLink for more details but here is algorithm used for freelist A block is put on free list if the free space in the block is greater than the space reserved by PCTFREE. Blocks linked in a free list are available for future updates or inserts. A block is unlinked from a free list if the free space in the block is not enough to allow a new row insert, and if the percentage of the used space remains above PCTUSED. A block is relinked to a free list if after DELETE or UPDATE operations, the percentage of the used space falls below PCTUSED. Each time a block is added to a free list, it is linked at the head of the chain. Rick Miller, Jay JayMiller@TDWater To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] house.com cc: Sent by: Subject: RE: PCTUSED - when is block added to freelist? [EMAIL PROTECTED] 09/04/2002 11:03 AM Please respond to ORACLE-L Yes, that's what I intended to ask :). Thanks, Jared. Jay -Original Message- Sent: Tuesday, September 03, 2002 9:18 PM To: Multiple recipients of list ORACLE-L If I understand Jay's question correctly, what he's asking is not how PCTUSED and PCTFREE work, but what action or actions trigger Oracle to put a block back on the freelist after changing PCTUSED to a higher value? Is that correct Jay? Jared On Tuesday 03 September 2002 15:38, Miller, Jay wrote: I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage
RE: ALTER TABLE MOVE command causes table to grow
pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ALTER TABLE MOVE command causes table to grow
Hi Stephen, PCT Increase 0, the indexes are in a different tablespace. PCT Used was 40, I just recently increased it to 75. Do you know if the MOVE command moves blocks as they currently exist or if it behaves like an export/import? If the latter (which was my assumption) I'd expect that a low PCTUSED would actually shrink the table since each block in the new tablespace would fill up entirely except for the PCTFREE. Jay -Original Message- Sent: Thursday, September 05, 2002 1:55 PM To: Multiple recipients of list ORACLE-L Jay: I would also wonder that the PCTINCREASE was on the table and the indexes. 10% PCTFREE is fine, but does lead to a significant number of empty blocks. What is your PCTUSED? If small, you will have lots of free space within blocks. Just a thought. Don't let your disk person know this happened as they may try to sell you more hardware. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Subject:ALTER TABLE MOVE command causes table to grow Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ALTER TABLE MOVE command causes table to grow
Yes, it's from DBA_SEGMENTS. And there was no DML against the table during the ALTER TABLE MOVE command which is when it grew. Re the 10% PCT_FREE there's minimal update activity. It has constant low level insert activity during the month, then a batch delete at the end of the month followed by a batch insert at the beginning of the month. But even if that wasn't the case how would a low PCT_FREE cause it to grow during the MOVE command? I'm not certain I understand what you're aiming at there. Jay -Original Message- Sent: Thursday, September 05, 2002 3:18 PM To: Multiple recipients of list ORACLE-L just to be certain we are on the same page, you mention uniform sizing which is on the tablespace level, so I want to make sure the PCT_INCREASE you provided was pulled from dba_segments. if so then i'd say a bit more info would need to have some light shed on it persay was there much DML put against this table last week. a PCT_FREE of 10% wouldn't be such a good idea for a table with varying length column data. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PCTUSED - when is block added to freelist?
Yes, that's what I intended to ask :). Thanks, Jared. Jay -Original Message- Sent: Tuesday, September 03, 2002 9:18 PM To: Multiple recipients of list ORACLE-L If I understand Jay's question correctly, what he's asking is not how PCTUSED and PCTFREE work, but what action or actions trigger Oracle to put a block back on the freelist after changing PCTUSED to a higher value? Is that correct Jay? Jared On Tuesday 03 September 2002 15:38, Miller, Jay wrote: I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of usage. This seems to imply that it won't be moved to the freelist until a delete or update is done that affects that block. But they also say: A higher PCTUSED increases processing cost during INSERTs and UPDATEs. This seems to imply that when it's looking to do the insert it might find that it can insert to a block. Anyway, why would a lower PCTUSED reduce processing costs during a DELETE but a higher PCTUSED wouldn't increase processing costs during a DELETE. That makes no sense. I'm befuddled. TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: automatic segment space management
We didn't implement a Large tablespace on any of our OLTP databases for that very reason. The tables that didn't fit into Small or Medium got their own tablespaces. There are only a few big tables on the OLTP databases and on the Data Warehouse we've moved toward giving the really big partitioned tables one tablespace/partition. It's made storage management a bit easier. -Original Message- Sent: Tuesday, September 03, 2002 6:39 PM To: Multiple recipients of list ORACLE-L So, proper LMT means no LBE? ;) Great analogy! All the head-spinning and the green projectile vomiting and such... BTW, yes that is a good paper. I've read it and am trying to deal with the extent sizes as it applies to our DB, as only about two dozen of the 800+ tables are larger than 128MB and none are larger than 4GB. So, either I consider making the Large LMTs smaller, or maybe our li'l 25GB DB doesn't need a Papa Bear. Or maybe I just need to get used to the idea of having more than a couple hundred extents... :) Thanks! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 3:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: automatic segment space management Rich - Good point. Yes, I create all the tables here, at least in production, and I probably wouldn't use autoextend if the situation were otherwise. The other thing to consider is if you are using uniform extents, by definition you have bought into the philosophy that you can have many extents and your database will not do a Linda Blair Exorcist imitation on you. If we use the guideline that the number of extents should be not many more than 1,000, then the 128K extent will get you 128M, which is good for most tables. While we are on the subject, anyone considering switching to LMTs should carefully read How to Stop Defragmenting and Start Living by Juan Loaiza, Rosanne Toohey, Bhaskar Himatsingka. You can find a copy on http://www.hotsos.com. Trying to implement a philosophy without fully understanding it is a recipe for failure. Dennis Williams -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
PCTUSED - when is block added to freelist?
I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of usage. This seems to imply that it won't be moved to the freelist until a delete or update is done that affects that block. But they also say: A higher PCTUSED increases processing cost during INSERTs and UPDATEs. This seems to imply that when it's looking to do the insert it might find that it can insert to a block. Anyway, why would a lower PCTUSED reduce processing costs during a DELETE but a higher PCTUSED wouldn't increase processing costs during a DELETE. That makes no sense. I'm befuddled. TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: upgrade 8.1.6.2 to 8.1.7.3 or 8.1.7.4
My understanding is that 8.1.7.3 is the buggy one. It was supposed to be the terminal 8.1.7 release but it had so many problems they had to come out with 8.1.7.4 -Original Message- Sent: Thursday, August 29, 2002 5:44 PM To: Multiple recipients of list ORACLE-L DBA's I have to upgrade an 8.1.6.2 DB on Solaris this weekend (so much for a three day weekend). Is there any reason not to go to 8.1.7.4??? Is 8.1.7.3 less buggy, etc??? I hope to upgrade this DB to 9.2.0.1 as soon as the app vendor ok's it, thanks to our Oracle friends we can't go direct from 8.1.6.x to 9.2.0.1... ...JIM... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Howerton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ONLINE index creation in 8.1.7.2
I've done it 4-6 times on 8.1.7.2 without problems. I'm not sure about this bug though, do you have a number? -Original Message- Sent: Monday, July 29, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Hi all, Has anyone experienced problems using the ONLINE option to create an index on a production 8.1.7.2 DB (HP/UX 11.0, if that matters)? I need to create a largish (for us -- ~300 MB) index on an audit table, but I don't want to lock it while the index is created. I've heard of bugs using ONLINE with indexes and am being paranoid, as usual. TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Bind Variables in VB
Someone pointed me towards these articles when I asked this question a week or two ago: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/ mdobjparameter.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/ mdmscadoobjmod.asp -Original Message- Sent: Wednesday, July 24, 2002 8:30 AM To: Multiple recipients of list ORACLE-L All, Can anyone provide examples of how to issue an SQL statement in VB using Bind Variables (using DAO or ADO). Many thanks for any responses. Dave Leach Technical Services Claybrook Computing Internal ext * 4992 Phone * 01293 604992 Fax 01293 604029 E-Mail * [EMAIL PROTECTED] The above information is confidential to the addressee and may be privileged. Unauthorised access and use is prohibited. Internet communications are not secure and therefore this Company does not accept legal responsibility for the contents of this message. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. Claybrook Computing Limited is a subsidiary of Claybrook Computing (Holdings) Limited Registered Office: Abbey House. 282 Farnborough Road, Farnborough, Hampshire GU14 7NJ Registered in England and Wales No 1287205 A Hogg Robinson plc company -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Leach INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Recompiling blocked package - locating blocking session
Actually, I think the executing_packages script should be exactly what I need. Thank you very much. I had browsed ixora but didn't make the connection between this script and what I was looking for. Jay Miller -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 23, 2002 11:03 PM To: Multiple recipients of list ORACLE-L Jay, Don't know for sure but you could try Ixora's script to show executing packages available at http://www.ixora.com.au/scripts/misc.htm You could also look for blocked internal locks - try the following: select * from dba_lock_internal where ( mode_held = 'Null' OR mode_held = 'None' ) AND ( mode_requested 'None' ) ; dba_lock_internal is created by catblock.sql but blocks that are in there do not show up in dba_blockers. From catblock.sql for dba_lock_internal * NOTE: This view can be very, very slow depending on the size of your * shared pool area and database activity. We haven't found this an issue on (low concurrent load) database. HTH, Bruce Reardon -Original Message- Sent: Wednesday, 24 July 2002 1:24 Had a problem this morning where a package was invalid and it would hang when we tried to recompile. We assumed that some other session was trying to recompile it but was hanging for some reason. I have plenty of ways to look at table locks but don't have a query to show locked packages. Frantic searching through the index of the SQL PL/SQL Annotated Archives didn't help. I ended up searching the Active sessions that looked likely and killing them (and needing to do a kill -9 on the OS level as well) until I was able to recompile the package. Does anyone have a query that will save the random searching next time? Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OEM changes
Oooh! That sounds promising! I don't suppose they'll give up on the idea of writing it in Java? It's been running so slw since I gave in and upgraded. Jay -Original Message- Sent: Wednesday, July 24, 2002 3:26 PM To: Multiple recipients of list ORACLE-L Well, it's not exactly a secret that our friend Gaja is now working for Oracle as a Director of Systems Management Tools ( I think the title is correct ), and that that title would include oversight of OEM, so I think we can expect good things to happen with OEM. Jared Rachel Carmichael [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/23/2002 08:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: OEM changes not often... but there were at least 8 or 10 people there who were DBAs and this party was the thank goodness the darned book is done party so we started talking about writing books and someone said something about OEM 101 and I'm usually the one at the party saying (screaming?) NO MORE GEEK TALK :) --- Conboy, Jim [EMAIL PROTECTED] wrote: Now please, please tell me you don't REALLY talk about this geeky stuff at parties! Jim -Original Message- Sent: Monday, July 22, 2002 9:53 PM To: Multiple recipients of list ORACLE-L Wish I could. This was at a party, and the person speaking was an Oracle employee. We were talking/ranting about how often Oracle changes things and he mentioned that OEM was changing AGAIN. That's all he said Rachel --- Boivin, Patrice J [EMAIL PROTECTED] wrote: Rachel, Could you shed any light on the changes they are proposing? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Conboy, Jim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Recompiling blocked package - locating blocking session
Had a problem this morning where a package was invalid and it would hang when we tried to recompile. We assumed that some other session was trying to recompile it but was hanging for some reason. I have plenty of ways to look at table locks but don't have a query to show locked packages. Frantic searching through the index of the SQL PL/SQL Annotated Archives didn't help. I ended up searching the Active sessions that looked likely and killing them (and needing to do a kill -9 on the OS level as well) until I was able to recompile the package. Does anyone have a query that will save the random searching next time? Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rant-Rant
Actually I'd think entj would be just about the ideal for a dba or sa. You have the leadership ability to ride herd on people (such as developers), are driven to keep up on the technical stuff, are well-organized enough to keep track of everything... I very aware of working against my natural instincts when I set up my backup and recovery plans, since improvisation and leaving things open is generally *not* advisable. Oh, we'll figure it out as we go along isn't a good choice there :), despite being my being rather good at doing just that. Jay -Original Message- Sent: Monday, July 22, 2002 5:25 PM To: Multiple recipients of list ORACLE-L How about an ENTJ as a DBA and System Admin... I'm loads of fun with a bunch of touchy feely type SP's here at the college I work for ;-) Joe On Mon, 22 Jul 2002, Miller, Jay wrote: 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 To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Joe LaCascio INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Had to get this off my chest
We need to move our standby database to a new location. I put together a plan for this. My boss said that I needed to draw pictures in Visio showing the old location and new location since his boss won't approve a plan that doesn't have pictures. I'm ready to move to a new company please. Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rant-Rant
Really? I had you pegged as an E given how well you organize all the NYOUG events/people. The J part was obvious :) Jay -Original Message- Sent: Monday, July 22, 2002 10:18 PM To: Multiple recipients of list ORACLE-L hey, I'm an infj -- according to the out placement company, I'd make a good nun (Robert, did you see my test results?) --- Miller, Jay [EMAIL PROTECTED] wrote: 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 To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to notify the password expiration in oracle using email?
This is what I use to send an email 3 days before and 1 day before
(obviously this is on a Unix box):
passwd_exp.sh:
cd /oracle/oper
sqlplus !JCM
xxx/$XXX_ID
set pagesize 0
set echo off
set feed off
set term off
spool pw_exp_mail.sh
@passwd_exp.sql
exit;
!JCM
sed 1d pw_exp_mail.sh pw_send_mail.sh
chmod ug+x pw_send_mail.sh
pw_send_mail.sh
passwd_exp.sql:
select 'mail '||email||' pw_exp_message_3.txt'
from employee e, dba_users du
where e.id=du.username
and trunc(expiry_date)-trunc(sysdate)=3
and du.profile in ('_PROFILE',etc.);
select 'mail '||email||' pw_exp_message_1.txt'
from employee e, dba_users du
where e.id=du.username
and trunc(expiry_date)-trunc(sysdate)=1
and du.profile in ('_PROFILE',etc.')
and du.account_status'LOCKED';
spool off;
pw_exp_message_1.txt:
Your Oracle password will expire in 1 day.
-Original Message-
Sent: Tuesday, July 23, 2002 12:53 PM
To: Multiple recipients of list ORACLE-L
Hi All,
Does anybody have a standard code or process for sending email from oracle
to address the following issue.
Thanks,
Ashoke
-Original Message-
Sent: Monday, July 22, 2002 3:37 PM
To: Multiple recipients of list ORACLE-L
Not sure it would be the best but... logically I would:
1. get sysdate
2. get expiration date
3. if expiration date - sysdate = 7 (let's say you wanted to warn them 7
days in advance) send them e-mail.
I have not done e-mail from Oracle but there are plenty examples everywhere.
Or, I think you could create a screen in your app. and display that screen
for them any time they get close to expiration date. You can even let them
change their password through that screen, too.
-Original Message-
Sent: Monday, July 22, 2002 3:10 PM
To: Multiple recipients of list ORACLE-L
Greetings,
As I understand that from Oracle 8 onward we have the feature of password
management like account locking, password aging and expiration, password
history and password complexity requirements.
My understanding is that if the password is expired for an user then that
user will be notified during his first attempt to login to the oracle
database. Instead of waiting for the user to login and then know about his
password expiration, we
are trying to find out a way by which the oracle can automatically send an
email to the respective user as it is close to the password expiration or as
the password has just expired.
Any ideas?
Thanks in advance,
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lyuda Hoska
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Miller, Jay
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP
RE: Had to get this off my chest
Thanks to everyone. After laughing my way through these I feel a lot better about the assignment :) I guess the 96 degree weather was eroding my sense of humor. Jay -Original Message- Sent: Tuesday, July 23, 2002 5:09 PM To: Multiple recipients of list ORACLE-L Use some stickers and some gold colored macaroni as well Kathy -Original Message- Sent: Tuesday, July 23, 2002 1:39 PM To: Multiple recipients of list ORACLE-L _ _ | | | | | | | | | | | | | OLD Location |-| NEW Location | | | | | | | | | |_| |_| Use above template. (WORD ClipArt even better). still like the crayon idea Once we were told that management wanted everything including a dancing peanut for a product demo (ie, they wanted everything)! AHEAD of deadline etc etc and NOW (ie yesterday). We did it. even found an animated DANCING PEANUT.. the VP almost choked on his drink when it popped up on screen: Point was well taken. Least he had a sense of humor. Sorry, lame I know.. but long day needed a wind down: Hannah We need to move our standby database to a new location. I put together a plan for this. My boss said that I needed to draw pictures in Visio showing the old location and new location since his boss won't approve a plan that doesn't have pictures. I'm ready to move to a new company please. Jay Miller -- 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). Confidential This e-mail and any files transmitted with it are the property of Belkin Components and/or its affiliates, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipients or otherwise have reason to believe that you have received this e-mail in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kathy Duret INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to notify the password expiration in oracle using email?
Oh, I forgot to mention (although it should be obvious) that the table
employee contains ids linked to dba_users.username as well as a column for
email.
-Original Message-
Sent: Tuesday, July 23, 2002 5:43 PM
To: Multiple recipients of list ORACLE-L
email?
This is what I use to send an email 3 days before and 1 day before
(obviously this is on a Unix box):
passwd_exp.sh:
cd /oracle/oper
sqlplus !JCM
xxx/$XXX_ID
set pagesize 0
set echo off
set feed off
set term off
spool pw_exp_mail.sh
@passwd_exp.sql
exit;
!JCM
sed 1d pw_exp_mail.sh pw_send_mail.sh
chmod ug+x pw_send_mail.sh
pw_send_mail.sh
passwd_exp.sql:
select 'mail '||email||' pw_exp_message_3.txt'
from employee e, dba_users du
where e.id=du.username
and trunc(expiry_date)-trunc(sysdate)=3
and du.profile in ('_PROFILE',etc.);
select 'mail '||email||' pw_exp_message_1.txt'
from employee e, dba_users du
where e.id=du.username
and trunc(expiry_date)-trunc(sysdate)=1
and du.profile in ('_PROFILE',etc.')
and du.account_status'LOCKED';
spool off;
pw_exp_message_1.txt:
Your Oracle password will expire in 1 day.
-Original Message-
Sent: Tuesday, July 23, 2002 12:53 PM
To: Multiple recipients of list ORACLE-L
Hi All,
Does anybody have a standard code or process for sending email from oracle
to address the following issue.
Thanks,
Ashoke
-Original Message-
Sent: Monday, July 22, 2002 3:37 PM
To: Multiple recipients of list ORACLE-L
Not sure it would be the best but... logically I would:
1. get sysdate
2. get expiration date
3. if expiration date - sysdate = 7 (let's say you wanted to warn them 7
days in advance) send them e-mail.
I have not done e-mail from Oracle but there are plenty examples everywhere.
Or, I think you could create a screen in your app. and display that screen
for them any time they get close to expiration date. You can even let them
change their password through that screen, too.
-Original Message-
Sent: Monday, July 22, 2002 3:10 PM
To: Multiple recipients of list ORACLE-L
Greetings,
As I understand that from Oracle 8 onward we have the feature of password
management like account locking, password aging and expiration, password
history and password complexity requirements.
My understanding is that if the password is expired for an user then that
user will be notified during his first attempt to login to the oracle
database. Instead of waiting for the user to login and then know about his
password expiration, we
are trying to find out a way by which the oracle can automatically send an
email to the respective user as it is close to the password expiration or as
the password has just expired.
Any ideas?
Thanks in advance,
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lyuda Hoska
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Miller, Jay
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE
RE: STATSPACK space requirement feature on 8i (FYI)
Well, it only goes away if you choose your extent size correctly :) I put the objects in their own tablespace and had the same problem with STATS$PARAMETER_PK since I set the default to the same as my TS_SMALL. Jay Miller -Original Message- Sent: Friday, July 19, 2002 4:49 PM To: Multiple recipients of list ORACLE-L Jesse - Since you will probably be collecting and deleting statistics, which may create fragmentation, I feel this is a good time to consider: 1. Separate tablespace. 2. Locally managed tablespace with uniform extents. Then your issue with extent size goes away. This has worked quite well for me. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, July 19, 2002 2:39 PM To: Multiple recipients of list ORACLE-L Hi all, I've been having problems with a STATSPACK index running out of extents in 8.1.7. Turns out that the docs are wrong or at least a little misleading. In MetaLink note 149113.1 and also in $ORACLE_HOME/rdbms/admin/spdoc.txt, it states: The default initial and next extent size is 1MB for all tables and indexes which contain changeable data. (spdoc.txt may be slightly different) This is not true for some of the indexes created by PK constraints, like STATS$PARAMETER_PK. The index will be created with the tablespace's default extent sizes. In my case, it was way too small. I looked in the $ORACLE_HOME/rdbms/admin/spctab.sql on my 9i install and the missing storage parameters have been inserted there. So, I filed a TAR on it and the analyst said I was the first to report the problem. He also stated that since 8.1.7.4 is now frozen that there wouldn't be a backport. I've asked that the issue be a new Metalink note. Just in case anyone else has had an issue with this. I can't be the first one, can I? Enjoy! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
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 To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to change VB code to use bind variables?
Hi, In checking my v$SQLArea I recently discovered that there was an often executed SQL statement that was not using bind variables. I tracked it down to a VB front end and asked the development group to modify the code. They asked me how to do it. Not being a VB programmer I said I had no idea but would try to find out. Here's an abbreviated version of the code. It's the sAcctId that seems to be causing the multiple executions. Anyone know how this can be rewritten to use bind variables? Can it be done from VB or do we need to transfer the code to PL/SQL on the back end? Private Function GetSQL(ByVal sKey As String, _ ByVal sAcctId As String, _ Optional ByVal sAcctNum As String, _ Optional ByVal sWhereAnd As String) Dim sConnect As String Dim sSql As String Dim oWsiLibData As WSILib.CDisplayData Dim rsCustomer As ADODB.Recordset Dim lCustId As Long Dim sCustId As String Select Case sKey ... ... Case accountinfo: sSql = SELECT a.id, a.account_no, a.type, a.title1, a.title2, a.title3, _ a.inventory_date date_opened, a.last_modified_date last_change, _ rc.description rebatecmsn, _ sSql = sSql account_info.fetch_email( sAcctId ) email sSql = sSql FROM account a, status s, usa_wh_tax u, fund f, _ stock_commission_codes sc, cmsn_rebate_codes rc, account_kind ak sSql = sSql , dual sSql = sSql WHERE a.status_id=s.id and a.usa_tax_code=u.code(+) _ and a.fund_id=f.id and _ a.s_stk_cmsn_code=sc.code(+) and a.cmsn_reb_code=rc.code(+) and a.kind_id=ak.id _ and a.id = sAcctId sConnect = sConnectWSI GetSQL = Array(sConnect, sSql) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to change VB code to use bind variables?
Hi Jared, Thanks for the suggestion. But I went to www.microsoft.com, clicked on support and searched on the words bind variables oracle visual basic After a dozen Internet Explorer Script errors I got a bunch of links with names like 26jbkjakjbmon That one led to a blank page. Others led to articles that didn't even mention bind variables. And I thought Metalink was bad. Does anyone have a link that goes directly to an article? Thanks, Jay Miller -Original Message- Sent: Tuesday, July 16, 2002 4:45 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Check on the MS support site. There are several articles detailing how to do this. Jared Miller, Jay [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/16/2002 02:08 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How to change VB code to use bind variables? Hi, In checking my v$SQLArea I recently discovered that there was an often executed SQL statement that was not using bind variables. I tracked it down to a VB front end and asked the development group to modify the code. They asked me how to do it. Not being a VB programmer I said I had no idea but would try to find out. Here's an abbreviated version of the code. It's the sAcctId that seems to be causing the multiple executions. Anyone know how this can be rewritten to use bind variables? Can it be done from VB or do we need to transfer the code to PL/SQL on the back end? Private Function GetSQL(ByVal sKey As String, _ ByVal sAcctId As String, _ Optional ByVal sAcctNum As String, _ Optional ByVal sWhereAnd As String) Dim sConnect As String Dim sSql As String Dim oWsiLibData As WSILib.CDisplayData Dim rsCustomer As ADODB.Recordset Dim lCustId As Long Dim sCustId As String Select Case sKey ... ... Case accountinfo: sSql = SELECT a.id, a.account_no, a.type, a.title1, a.title2, a.title3, _ a.inventory_date date_opened, a.last_modified_date last_change, _ rc.description rebatecmsn, _ sSql = sSql account_info.fetch_email( sAcctId ) email sSql = sSql FROM account a, status s, usa_wh_tax u, fund f, _ stock_commission_codes sc, cmsn_rebate_codes rc, account_kind ak sSql = sSql , dual sSql = sSql WHERE a.status_id=s.id and a.usa_tax_code=u.code(+) _ and a.fund_id=f.id and _ a.s_stk_cmsn_code=sc.code(+) and a.cmsn_reb_code=rc.code(+) and a.kind_id=ak.id _ and a.id = sAcctId sConnect = sConnectWSI GetSQL = Array(sConnect, sSql) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Increase size of REDO log
You also might need to do an alter database checkpoint; if you get a particular error when you try to drop the log (don't remember it offhand, I always recognize it when I see it :). Jay Miller -Original Message- Sent: Monday, July 08, 2002 2:04 PM To: Multiple recipients of list ORACLE-L First, Check for the one that is not the current one. Select * from V$log; ALTER DATABASE DROP LOGFILE '/u01/oradata/orcl/redo01.log'; delete the file manually ALTER DATABASE ADD LOGFILE '/u01/oradata/orcl/redo01.log' size 30M; Change your size to your desire one. For the other ones do the same. To change the active one. ALTER SYSTEM SWITCH LOGFILE; hth Ramon - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, July 08, 2002 11:33 AM How do I increase size of redo logs and how do I create additional redo logs? Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon E. Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Unidentified sessions brought us to maximum processes - more
I'm on 8.1.6.2, Solaris 2.6. All the unidentified sessions were active but had no SQL associated with them. Jay Miller -Original Message- Sent: Tuesday, July 02, 2002 3:43 PM To: '[EMAIL PROTECTED]' We just had a problem on our datawarehouse where we hit the maximum number of processes (had never happened before). When I checked in Enterprise Manager and v$sessions I saw dozens of sessions with no username or osusername attached. I was able to resolve the issue with a shutdown abort and restart (after spending 15 minutes to get the requisite approvals). There were no errors in the alert log and no trace files. Nothing odd on the Unix side either. I'll be keeping my eye out for a recurrence and logging a TAR with Oracle but has anyone seen this sort of thing happen before? Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hints being ignored
] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Frequent ''ORA -1650: unable to extend rollback segment error
Have you checked if someone might be holding space in the rollback segment you're getting the error for? We had a similar problem two weeks ago which we resolved by killing the session that was holding the space (oddly, they weren't holding any locks so I don't know why they were holding RBS space). You can use this script (from the remarkably useful SQL PL/SQL Annotated Archives) to find out: column rr heading 'RB Segment' format a18 column us heading 'Username' format a15 column os heading 'OS User' format a10 column te heading 'Terminal' format a10 select R.name rr, NVL(S.Username, 'no transaction') us, S.Osuser os, S.Terminal te from v$lock l, v$session s, v$rollname r where l.sid=s.sid(+) and trunc(l.id1/65536)=r.usn and l.type='TX' and l.lmode=6 order by r.name; -Original Message- Sent: Monday, June 24, 2002 6:38 PM To: Multiple recipients of list ORACLE-L errors'' in the Hi All, For the last 10-12 days Database is giving frequent ORA 1650 errors. My Database size is 74 GB and Total No. of Rollback segments are 19, with Initial Extent 60MB, Next extent 60MB, MIN_EXTENTS 2, MAX_EXTENTS 505. There are 2 Tablespaces holding these Rollback segments( 4GB and 1 GB) There is no extra load on the database. I am really worried why this problem has been started all of a sudden. The rollback segment statistics is showing high number of Waits and wrap. For few rollback segments wait statistics it is exceeding even 100 which I have never seen. and now because of this few importants reports are aborted. Mon Jun 24 00:49:48 2002 Thread 1 advanced to log sequence 5416 Current log# 2 seq# 5416 mem# 0: /oracle05/oradata/GASPROD/redo02.log Mon Jun 24 00:49:55 2002 ORA-1650: unable to extend rollback segment RBS12 by 7680 in tablespace RBS Failure to extend rollback segment 12 because of 1650 condition Mon Jun 24 00:52:33 2002 Thread 1 advanced to log sequence 5417 Current log# 1 seq# 5417 mem# 0: /oracle05/oradata/GASPROD/redo01.log Mon Jun 24 00:56:24 2002 Thread 1 advanced to log sequence 5418 Kindly suggest if anybody has some clue. Regards deepender -- 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: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE
RE: how to you stop an export?
I just kill the exp process from another session. -Original Message- Sent: Monday, June 10, 2002 1:08 PM To: Multiple recipients of list ORACLE-L how do you stop an command-line export? press CTL-C countless times and ususally just stops the current table . . . usually end up killing the entire session. was hoping there's a different way? thx bill -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is this a good upgrade path from 8.0.4 to 8.1.7.2?
I did an 8.0.4.3 to 8.1.7.2 upgrade back in January. My main nightmare with that particular upgrade is not likely to affect you (we had a disk failure while backing up the database prior to upgrade and the server didn't switch to the mirrored disk until after we rebooted - ended up delaying the start of the upgrade from midnight to 3am). Other than that (which understandably stands out in my memory) the main issue was with installing java. If you intend to install it I highly recommend you print out a copy of Document 156477.1. Allow plenty of time for the installation (I hadn't allowed enough time since I had only done 8.1.6 java installations before and hadn't realized how long all the extra steps took). And just in case you have problems and need to uninstall be sure to have a copy of Document 159801.1 handy. I recommend doing the java installation *after* you apply the patch. This will save considerable time. Good luck! Jay Miller -Original Message- Sent: Thursday, June 06, 2002 12:44 PM To: Multiple recipients of list ORACLE-L We are preparing to do an upgrade of our Data Warehouse on Sun Solaris from version 8.0.4.0.0 to 8.1.7.2. We've done a number of upgrades in the past but this is the first time we are going from 8.0.4 to 8.1.7. Following is the basic, high level plan: 1. Upgrade from 8.0.4 to 8.1.7.0 (Oracle software already pre-installed in separate Oracle Home). 2. Apply 8.1.7.2 patchset 3. Apply separate one-off-patch that we had created for a star transformation bug. We are going to use the manual migration method as outlined in the 8.1.7 migration manual. I've already tested this approach on a full-size RMAN clone of production that we moved to our QA box. However, that box already had 8.1.7.2 pre-installed on it before I actually migrated the database. Just wondering if anyone has gone directly from 8.0.4 to 8.1.7.2.Are there any gotchas or concerns? At one point I thought we might have to upgrade from 8.0.4 to 8.1.5 and then from 8.1.5 to 8.1.7. But now I can't find anywhere in the documentation where it says that I need to do that so we are thinking about saving a little time and going straight from 8.0.4 to 8.1.7. This is a 200 Gig warehouse which uses Verita Quick I/O, partitioning, star schema, etc. Thanks to anyone who can alert me to any potential pitfalls. I cannot afford any glitches at all on this upgrade. Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
