Re: Views for a table
Experiment with dba_dependencies view. - Kirti --- Mauricio Vélez [EMAIL PROTECTED] wrote: Hi everybody I have the following question How can I query a table's views? For example I have the table students and I want to know the views related to this table. Thanks, Mauricio Vélez - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] create tablespace with different block size error???
Because, you left db_16k_cache_size parameter to the default value of 0 (zero). - Kirti --- dba1 mcc [EMAIL PROTECTED] wrote: I tried to create tablespace with 16K (default on my init.ora blocksize =8k). I have error happen. The database version is 9.2.0.4. SQL create tablespace index1 logging datafile '/u0/oradata/leg92/index1.dbf' size 20m 2 blocksize 16384 3 autoextend on 4 next 1280k 5 maxsize unlimited 6 extent management local 7 segment space management auto 8 uniform size 128k; autoextend on * ERROR at line 3: if I take out blocksize 16384, then it work fine. SQL create tablespace index1 logging datafile '/u0/oradata/leg92/index1.dbf' size 20m 2 reuse autoextend on 3 next 1280k 4 maxsize unlimited 5 extent management local 6 segment space management auto 7 uniform size 128k; Tablespace created. Does anyone know why?? __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: pga_aggregate_target and a memory leak
Paul, Most of my work is on HP-UX and AIX. I have yet to see any ORA-600 and memory leaks related to P_A_T. All databases that I work with are on 9.2.0.4, except just one running on 9.2.0.3. No memory leak there either. - Kirti --- Paul Drake [EMAIL PROTECTED] wrote: --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to get unique value using AWK?
Why not process the trace file with 9i tkprof?
It will nicely summarize the wait times for those events.
After seeing those unique wait events, your boss may ask for the wait times next!! Be
proactive
:)
- Kirti
--- Simpson, Ken [EMAIL PROTECTED] wrote:
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of [EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 11:09 AM
To: Multiple recipients of list ORACLE-L
Subject: How to get unique value using AWK?
Hi All,
My manager wants to get all the unique wait events from the
trace file.
I tried the below but how do i get DISTICT wait event name?
Any help would be really appreciated.
$ grep -i WAIT devdb1_ora_989.trc_orig|awk '{print $3 $4
$5 $6}'|more
nam='SQL*Net message to client'
nam='SQL*Net message from client'
nam='library cache lock' ela=
nam='SQL*Net message to client'
nam='SQL*Net message from client'
nam='SQL*Net message to client'
nam='SQL*Net message from client'
nam='SQL*Net message to client'
nam='SQL*Net message from client'
nam='SQL*Net message to client'
nam='SQL*Net message from client'
nam='SQL*Net message to client'
nam='SQL*Net message from client'
nam='SQL*Net message to client'
Thanks
Jay
How about piping it through uniq?
$ grep -i WAIT devdb1_ora_989.trc_orig|awk '{print $3 $4 $5
$6}'| uniq
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Simpson, Ken
INET: [EMAIL PROTECTED]
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: AQ
Thanks, Tanel. Sorry, if I caused any confusion. May be I should have used 'pipe get' event instead of 'SQL*Net message from client' as an example, which of course, should not be ignored in a multi-tier, networked environment. In fact, I remove it, among a few others, from PERFSTAT.STAT$IDLE_EVENT table, when if, I use Statspack.. Cheers! - Kirti --- Tanel Poder [EMAIL PROTECTED] wrote: Hi! I think what Kirti meant here, is that from only database's point of view (scope), the SQL*Net message from client waits do not indicate any database bottlenecks. Anyway, when you have network bottleneck, from my experience you usually see other SQL*Net message waits, like more data to/from client etc as well. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 6:39 PM However... do not blindly treat SQL*Net messages as Idle waits. They can be important indicators of networking issues. Stephen [EMAIL PROTECTED] 01/20/04 08:24AM It is defined as below in the Reference Guide: The session is waiting on an empty OLTP queue (Advanced Queuing) for a message to arrive so that the session can dequeue that message. I would treat it as an Idle Wait, similar to, SQL*Net message from client. - Kirti --- Ehresmann, David [EMAIL PROTECTED] wrote: Is queue message an idle or an non-idle wait event? I have looked through the docs at tahti and metalink and can't find much info on AQ. Does anybody know where there are resources on this topic? thanks, David Ehresmann -- __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pga_aggregate_target and a memory leak
Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked, however, the disk sorts increased. Finally, Developers chose no hash joins, 1GB P_A_T and 'AUTO' workarea_size_policy... seems to run okay... - Kirti --- Stephane Faroult [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field. -- Regards, Stephane Faroult Oriole Software -- __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Parallel Query determined by?
I could not find an Oracle wait event named 'Parellel Sync Wait' (in v$event_name view in 7.3.4, 8.1.7.4, 9.2.0.4). Precise may be calling something else a 'Parallel Sync Wait', or is smart enough to figure this out when it seems Oracle isn't instrumented for this particular wait(?). Just pondering... Does Presice on-line help have any mention of this wait? Have you contacted Precise Tech Support? - Kirti --- [EMAIL PROTECTED] wrote: No other replies yet, so here goes. First of all, what does it really mean when you say: users are complaining that Precise is showing a whole lot of time in Parallel Sync Wait. Are these end users, or developers? Seems rather curious that users would be mentioning this. Second, what % of wait time do the Sync Waits contribute to? If a small % of total, then there's not much point in spending time on it. Third, is this actually causing a performance problem, or is it just appearing as a 'trouble' item on some monitor? Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/20/2004 11:29 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Parallel Query determined by? I've inherited a system that has a whole lot of indexes set to degree 10 and many tables set to 2 4. The users are complaining that Precise is showing a whole lot of time in Parallel Sync Wait. It is an HP box running 8.1.7.4 with 16 processors. The box is normally not very busy. Are there various init.ora settings that help the Parallel servers sync up, or is this just too high a setting? I'm suggesting we back of a good many of these things to simply 2 or 4 and then work our way up from there. Some of those indexes set to 10 are only 20 meg and 4 extents. There's no way they are getting 10 on that I would think. Can't find a whole lot on Metalink either. Or a good book on 800 gig warehouses using parallel? -- 13308 Thornridge Ct Midlothian, VA 23112 804-744-1545 __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pga_aggregate_target and a memory leak
Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked, however, the disk sorts increased. Finally, Developers chose no hash joins, 1GB P_A_T and 'AUTO' workarea_size_policy... seems to run okay... - Kirti --- Stephane Faroult [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field. -- Regards, Stephane Faroult Oriole Software -- __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: pga_aggregate_target and a memory leak
I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti --- [EMAIL PROTECTED] wrote: kirti-- would you recommend avoiding pga_aggregate_target for now? From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked, however, the disk sorts increased. Finally, Developers chose no hash joins, 1GB P_A_T and 'AUTO' workarea_size_policy... seems to run okay... - Kirti --- Stephane Faroult [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field. -- Regards, Stephane Faroult Oriole Software -- __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: pga_aggregate_target and a memory leak
Thanks, Ryan. Yes, it is on OWI, for those who are new to OWI. Covers OWI from 8i to 10g. Co-authored with Richmond Shee and K.Gopalakrishnan. It will not be out till 10g goes production. Unfortunately, April 12th is not firm. 10g changes Regards, - Kirti --- Ryan [EMAIL PROTECTED] wrote: Im assuming its his wait interface book. Ill get it as soon as it comes out. Hopefully it will be as good as his other tuning book. Is the April 12th date firm? Now the bigger question: Will it be out before the 10G database? http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/qid=1074724628/sr=1 -2/ref=sr_1_2/104-1361632-8254324?v=glances=books - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 5:14 PM A comment I picked up from Tom Kyte's Masterclass in Copenhagen last week was that there is an effective limit of 1GB to P_A_T - and although a single session is supposed to be allowed 5% of the P_A_T, you could get about 90MB. So there are some funny things going on in that area which still need fixing. It's a bit tough for big systems, as I've found that the optimizer seems to be much smarter about memory user and access paths when P_A_T and W_S_P are set. What's the book about ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 7:44 PM Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
9i Automatic Memory Damagement:)
Enjoy: http://www.vldb.org/conf/2002/S29P03.pdf This explains how Oracle9i does the P_A_T, W_S_P 'magic'. Cheers! - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: AQ
It is defined as below in the Reference Guide: The session is waiting on an empty OLTP queue (Advanced Queuing) for a message to arrive so that the session can dequeue that message. I would treat it as an Idle Wait, similar to, SQL*Net message from client. - Kirti --- Ehresmann, David [EMAIL PROTECTED] wrote: Is queue message an idle or an non-idle wait event? I have looked through the docs at tahti and metalink and can't find much info on AQ. Does anybody know where there are resources on this topic? thanks, David Ehresmann -- __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rename tablespace in 9I
No. It is coming in 10g (from Oracle World presentations I attended last year). - Kirti --- [EMAIL PROTECTED] wrote: Is there anyway to rename tablespace in Oracle 9202. Thanks __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: dba interview questions
The problem with such lists is that the prospects also have those... A few years ago Mike Ault published one. We used it to screen candidates via phone interviews. Two out of four I talked to gave me perfect answers: word to word from Mike's list. Use Conner's approach: Give the candidate a white/black board, and ask him/her to draw the SGA with all it interal structures, all background processes, and explain how all this works together. - Kirti --- system manager [EMAIL PROTECTED] wrote: Dear List,Can anyone send me a list of dba interview questions? Thanks, _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: shutting down oracle on AIX during machine shutdown
We have our own 'system_shutdown' script that shuts down all instances and then the server. - Kirti --- John Dunn [EMAIL PROTECTED] wrote: Does everyone use the method documented in the install docs (write a script called shut to call dbshut and use this instead of shutdown), or are other methods used? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: dba interview questions
Oh! Well. I have not seen Tom's book yet. But still, when the candidate is explaining this stuff to you, there are plenty of opps to question him/her to find out if he/she really knows fundamental things.. During one phone interview, we could clearly hear the paper shuffle in the background, while the candidate asked us to repeat the question (a couple of times) to 'make sure' he understood it correctly before answering (reading?)it :) We stopped phone interview process after this!! - Kirti --- [EMAIL PROTECTED] wrote: that question is diagrammed and answered in tom kytes new book. :) im waiting to get asked it. there is a new ault book out on interview questions. I dont think they are very tough. I think situational questions are better. Have a development DB set up with things for the applicant to do. I find that most employers ask the same easy questions. Particularly developer questions From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2003/10/24 Fri AM 09:49:26 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: dba interview questions The problem with such lists is that the prospects also have those... A few years ago Mike Ault published one. We used it to screen candidates via phone interviews. Two out of four I talked to gave me perfect answers: word to word from Mike's list. Use Conner's approach: Give the candidate a white/black board, and ask him/her to draw the SGA with all it interal structures, all background processes, and explain how all this works together. - Kirti --- system manager [EMAIL PROTECTED] wrote: Dear List,Can anyone send me a list of dba interview questions? Thanks, _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: anyone have opinions on the future of the 'grid'?
Bingo! That's how we will make Larry the richest man in the World... - Kirti --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: But that would require Oracle software to be installed on all the grids, don't you think? I'm really curious how they are going to pull this off. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 16, 2003 6:30 PM To: Multiple recipients of list ORACLE-L I think the assumption must be that all the computers on the grid are attached to a SAN. Does that seem reasonable? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 16, 2003 5:04 PM To: Multiple recipients of list ORACLE-L For database type stuff, one must wonder how the data itself can be handled by CPU resources scattered hither and yon. The answer comes from the fairly recent knowledge that, in our universe, every particle has a matching particle; and changing one of the particles results in a change in the other particle. Well, the solution to trying to shove data all over the grid becomes obvious: One need only establish central management of the matching particles that make up the CPU and memory of all the computers involved. Initially, this would seem to be a daunting task ... until we recall that Larry is God. -- __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Redo Log (Largest size used)
I have redo log sized to 800MB in a one of our databases. I am considering to double its size, as the application generates too much redo, due to bad application code. Even for one column change, the rest of the row gets updated with the same values. I have heard of no issues with large redo log files. I just need to make sure I have enough log groups and enough archived dest space to not affect archiving of redo logs.. This is on AIX 5.2 with Oracle 9.2.0.4 - Kirti --- Denny Koovakattu [EMAIL PROTECTED] wrote: Hi, Platform : HP-UX 11.11 (64bit) PA-RISC Oracle : 8.1.7.4 EE (64bit) On a system I am looking at, during peak loads the redo logs are switching almost every minute. The logs are currently sized at 1G. I am recommending the redo logs be resized to a much larger value. (The largest I have had to use in Prod. is 2G). Before we implement I want to confirm there are no issues/bugs I should be aware of. (We would be testing it in a test environment before implementing but just wanted to pick the knowledge of the collective.) It would also be interesting to know what's the size of the largest redo logs being used and amount of redo being generated during peak loads. Thanks, Denny -- Denny Koovakattu - This mail sent through IMP: http://horde.org/imp/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Redo Log (Largest size used)
Yes we did.. It's a non-issue. We just print Yellow Pages ;) Applicaton Recovery time is not very critical. - Kirti --- Murali_Pavuloori/[EMAIL PROTECTED] wrote: Have you looked at MTTR to see if it meets the needs of the recovery scenarios at your site? Murali. |-+ | | Kirtikumar Deshpande | | | kirtikumar_deshpande| | | @yahoo.com | | | Sent by: | | | [EMAIL PROTECTED]| | || | || | | 10/17/2003 04:04 PM | | | Please respond to| | | ORACLE-L | | || |-+ --| | | | To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] | | cc: | | Subject: Re: Redo Log (Largest size used) | --| I have redo log sized to 800MB in a one of our databases. I am considering to double its size, as the application generates too much redo, due to bad application code. Even for one column change, the rest of the row gets updated with the same values. I have heard of no issues with large redo log files. I just need to make sure I have enough log groups and enough archived dest space to not affect archiving of redo logs.. This is on AIX 5.2 with Oracle 9.2.0.4 - Kirti --- Denny Koovakattu [EMAIL PROTECTED] wrote: Hi, Platform : HP-UX 11.11 (64bit) PA-RISC Oracle : 8.1.7.4 EE (64bit) On a system I am looking at, during peak loads the redo logs are switching almost every minute. The logs are currently sized at 1G. I am recommending the redo logs be resized to a much larger value. (The largest I have had to use in Prod. is 2G). Before we implement I want to confirm there are no issues/bugs I should be aware of. (We would be testing it in a test environment before implementing but just wanted to pick the knowledge of the collective.) It would also be interesting to know what's the size of the largest redo logs being used and amount of redo being generated during peak loads. Thanks, Denny -- Denny Koovakattu - This mail sent through IMP: http://horde.org/imp/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing
Re: insert and commit 1000 records at a time
1. Define a variable to count inserted rows.
insert_count number := 0;
2. Increment it after inserting the row
insert_count:=insert_count + 1;
3. Check if insert_count = 1000
then commit and reset counter to zero insert_count :=0;
4. At the end when no rows found, and insert_count 0 then commit.
I think you get the idea
- Kirti
--- MaryAnn Atkinson [EMAIL PROTECTED] wrote:
I still dont get it...
I dont know what I have done to have me confused more
than I first asked the question...
--- [EMAIL PROTECTED] wrote:
My bad. The SQL is not quite right: 'append' is a hint:
alter table resource nologging;
dont know what nologging does.
insert /*+ append */ into resource
select * from rqmt;
me no understand... me no see 1000 anywhere...
Read up on direct load insert in the concepts manual,
along with nologging.
And one more thing... If I asked the question, thats just it,
I asked a question. If anyone knows the answer, please offer it here,
but dont tell me to go read it up in the national enquirer or
I-dont-know-where-you-mean...
Folks, please, if we have something to offer, lets go ahead,
if not, just bypass that email and read another one...
thanks,
maa
Bypass the redo and undo - no need for commits.
Just back it up when finished.
Jared
MaryAnn Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 01:49 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: insert and commit 1000 records at a time
--- [EMAIL PROTECTED] wrote:
That will work, slowly.
You might like to try something like this
insert into resource
nologging
select * from rqmt
append;
How's that commiting every 1000 records?
Read up on the 'append' and 'nologging' first.
???
Maryann Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 08:54 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Effect on Oracle of changing server time from EST to GMT
Good point, Dan. If using SCN to flashback then it's a non-issue. Avoid using date/time stamp to flashback during that 1 hour period. Or find out the SCN from SYS.SMON_SCN_TIME table closest to the past time, and use it to flashback. - Kirti --- Daniel Fink [EMAIL PROTECTED] wrote: Another thought for 9i...how would a time change impact flashback query? Daniel Fink david davis wrote: Another issue might be related to doing time-based recovery. Oracle has a couple of notes on metalink related to changing server time and database impact. Note:1013279.6 - HOW DOES DAYLIGHT SAVINGS TIME AFFECT THE DATABASE? Note:77370.1 - What are the effects of changing the system clock on an Oracle Server instance? From: Stephane Faroult [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Effect on Oracle of changing server time from EST to GMT Date: Thu, 16 Oct 2003 15:04:40 -0800 Well, some jobs (including snapshot refreshes) may get confused, and if you create or alter objects I wonder how Oracle may handle dependencies ... Fortunately you are moving 5 hours ahead, rather than back, so risks are probably quite limited. -- Regards, Stephane Faroult Oriole Software __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: anyone have opinions on the future of the 'grid'?
Current issue of ComputerWorld has an article on Grid, but I enjoyed the fantastic cartoon :) - Kirti --- Jared Still [EMAIL PROTECTED] wrote: Here's a recently curmudgeonly reply to a friend that asked the same question. - As for 10g itself: the 'g' stands for 'grid' as you probably know. The 'grid' is really the infrastructure for 'utility computing', the latest plot to make IT folks obsolete. Personally I see it being useful in large organizations where large numbers of servers can be used in a grid, and computing power being doled out to apps as needed. It's still very much vaporware, as the tools to effectively administer and monitor this aren't available yet. I don't agree with the prognostications that businesses will begin buying computing power from a utility computing company on an as needed basis, much as they do electricity. The whole 'utility' part of the moniker is a pretty poor analogy IMO. Oracle's last big push was RAC. Reading the news makes it pretty obvious why they're pushing it: revenue is down, few new customers, gotta sell new stuff to existing ones. The hype for RAC was/is really overblown, not many people actually need it, nor can afford to purchase and maintain it. 10g will help Larry run in the America's Cup again. :) On Thu, 2003-10-16 at 08:04, [EMAIL PROTECTED] wrote: I was at the Wash DC Oracle conference yesterday and the key note address was about the future direction of Oracle. It was pretty positive about Grid technology and blade servers. Anyone out there have any opinions? It seems like this technology is probably several years away from being used in the business and government contracting world. Most places you see it are in academia. __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ** increasing size for a partitioned table key field
No workarounds. You are violating one of the rules of partitioning. You will need to rebuild the table and the indexes etc df2hp103 [IDST]: oerr ora 14060 14060, 0, data type or length of a table partitioning column may not be changed // *Cause: User issued ALTER TABLE statement attempting to modify // data type and/or length of a column used to partition the // table named in ALTER TABLE statement, which is illegal // *Action: Avoid modifying data type and/or length of table // partitioning column(s) - Kirti --- A Joshi [EMAIL PROTECTED] wrote: Hi, I am getting this error ORA-14060 while modifying a column in partitioned table. The table is partitioned on that column. Is there a workaround or any other alternative. Thanks - Do you Yahoo!? The New Yahoo! Shopping - with improved product search __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: continuously copy archive redo logfiles to tape
We use cron facility to run a script that copies archived log files, not in use by archiver process, (fuser command) to a different location. We also use 'Autosys' software to run such scripts on remote servers. Another script, that runs after the 'copying' script, confirms that the 'copying' was successful and removes 'copied' archived log from archived log destination When hired junior DBAs in the distant past, we 'oriented' them by asking them to manually do such copying and removing of archived logs :) - Kirti --- Roger Xu [EMAIL PROTECTED] wrote: Hello, How do I continuously copy archive redo logfiles to tape ? Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: hrs to run catpatch.sql
0.50 to 2.00 depending on Java/XML/XDK etc... etc... - Kirti --- Shibu MB [EMAIL PROTECTED] wrote: Hi all .. Can anybody tell me how many hours it will take to run the patch catpatch.sql . I am planning to upgrade the server. My server info is given below BANNER Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production PL/SQL Release 9.2.0.3.0 - Production CORE9.2.0.3.0 Production TNS for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production Thanks Shibu DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Shibu MB INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: hrs to run catpatch.sql
Thanks for this info about your problem. I will keep it hande for future reference. May be I got lucky, because I did not encounter major issues when I upgraded 9.2.0.3 to 9.2.0.4 on HP-UX 11i. Just one lib had to be renamed so Oracle could create it afresh. Other than that, it was pretty smooth on 3 servers. - Kirti --- Odland, Brad [EMAIL PROTECTED] wrote: I have been testing the Oracle 9.2.0.4 patch on HP/UX dev box and have a open TAR. I ran into a bunch of problems relinking after applying the 9.2.0.4 patch on HP/UX. Oracle had me run RDA and gave me a list of missing OS patches, I still had the same errors they had me run RDA again and gave me another list of OS patches...finally escalated the issue and got some help getting the relink done but now... The catpatch.sql exits sqlplus or the connection would get broken. And the script would continue running saying, NOT CONNECTED TO ORACLE.a gazillion times...So its a mess right now. And SMON generates ORA-600's every five minutes until it shuts the intance down. With Arguemt [51] that is unpublished at this time and the stack trace offers zero infoand Oracle is suddenly very quiet in my TAR... Make sure you test that patch COMPLETELY before doing anything to a production server I am probably going to unistall the whole mess and start over This has been the most problematic patch that I have seen(I suppose I was due) -Original Message- Sent: Tuesday, October 14, 2003 7:59 AM To: Multiple recipients of list ORACLE-L Hi all .. Can anybody tell me how many hours it will take to run the patch catpatch.sql . I am planning to upgrade the server. My server info is given below BANNER Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production PL/SQL Release 9.2.0.3.0 - Production CORE9.2.0.3.0 Production TNS for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production Thanks Shibu __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with undo tablespace and snapshot too old
Is your particular problem reproducible? Without any changes to the cache size? Many times simply running the job again works. - Kirti --- John Kanagaraj [EMAIL PROTECTED] wrote: Helmut, The SELECT article 'Understanding ORA-0155' by Tim Gorman is a must-read. Get a subscription to IOUG, or ask Tim nicely and he might give a copy of the article to you... (Probably better to get an IOUG subs - there is a ton of excellent articles and tech stuff out there) John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Daiminger, Helmut [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 9:25 AM To: Multiple recipients of list ORACLE-L Subject: Problem with undo tablespace and snapshot too old Hi! We are experiencing a weird problem here... We have automatic undo management enabled and the undo tablespace is 6 GB in size. undo_retention is set to 30 minutes. when a certain transaction runs, it fails with ORA-1555 Snapshot too old, although the undo tablespace only uses 700 MB (out of 6 GB possible). That loos weird to me... Then our other DBA suggested to cut the size of the buffer cache in half and let the transaction run again. We have done that and it worked flawlessly... WHY??? What is the relation between the buffer cache size und rollback (i.e. undo retention)? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: continuously copy archive redo logfiles to tape
Sai, We do not delete the files right away. The 'delete' scripts verifies that the file is present (with correct file size) at the destination before deleting it. We have not had any problems with this method for over 5 years now. I never used 8.1.7.2 much, so I can't say how the ARCH process behaved in that version. - Kirti --- Sai Selvaganesan [EMAIL PROTECTED] wrote: hi kirti i was using the below method to copy archives to drsites and running this cron very frequently. the job used to ship and delete them immediately after successful copy and i used to have fuser check. but soon we started hitting this issue when we used to ship archives even when they are being used by the archiver. i think you are using fuser to find whether the file is open and then you ship it. when i implemented this i thought archiver process open the archive file once and then starts copying the redo..which seems logical..but it so happens archiver closed the files (ran truss os ARCH process) frequently when writing to the same file and my fusuer check used to fail occasionally. This was in 8.1.7.2. but these things may have changed now. just fyi. thanks sai Kirtikumar Deshpande [EMAIL PROTECTED] wrote: We use cron facility to run a script that copies archived log files, not in use by archiver process, (fuser command) to a different location. We also use 'Autosys' software to run such scripts on remote servers. Another script, that runs after the 'copying' script, confirms that the 'copying' was successful and removes 'copied' archived log from archived log destination When hired junior DBAs in the distant past, we 'oriented' them by asking them to manually do such copying and removing of archived logs :) - Kirti --- Roger Xu wrote: Hello, How do I continuously copy archive redo logfiles to tape ? Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Shell script broken, bdf adds new line for long filesystem name.
Hi Ethan,
Our SA gave me the following script...
Put it in your /usr/local/bin and include the dir in PATH...
- Kirti
#!/bin/ksh
# -- bdfg
COUNT=0
bdfit () {
if [ -z $SIZE ] ; then
LVOL2=$LVOL
((COUNT=1))
else
if [ $COUNT -eq 1 ] ; then
echo $LVOL2 $LVOL $SIZE $USED $FREE $PERCNT|awk '{ printf %-33s %10s%10s%10s%6s
%-26s\n,
$1, $2, $3, $4, $5, $6}'
COUNT=0
unset LVOL2
else
echo $LVOL $SIZE $USED $FREE $PERCNT $MOUNT|awk '{ printf %-33s %10s%10s%10s%6s
%-26s\n,
$1, $2, $3, $4, $5, $6}'
fi
fi
}
if [ -z $1 ] ; then
/usr/bin/bdf|while read LVOL SIZE USED FREE PERCNT MOUNT ; do
bdfit
done
else
echo Filesystem kbytes used avail %used Mounted |awk '{ printf %-33s
%10s%10s%10s%6s %-26s\n,
$1, $2, $3, $4, $5, $6}'
for FILESYS in $* ; do
/usr/bin/bdf $FILESYS|grep -v Filesystem|while read LVOL SIZE USED FREE PERCNT
MOUNT ; do
bdfit
done
done
fi
#
--- Post, Ethan [EMAIL PROTECTED] wrote:
Note that the foofoofoo volume causes the disk info to show up on the
following line. A script I have that loops through each line coming back
from bdf is breaking because of this. Any simple way to bring the line back
up to the Filesystem line using awk, sed etc...?
bdf -l
Filesystem kbytesused avail %used Mounted on
/dev/vg02/foofoo 2048000 1973804 70415 97% /foofoo
/dev/vg02/foofoofoo
532486624 43711 13% /foofoofoo
/dev/vg02/foofoof 512000 259286 236944 52% /foofoof
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Estimating space needed for UNDO tablespaces
Hi Jeff, Stealing extents is normal when there is no free space available to grow the active undo segment. If expired extents are getting stolen, I would not worry too much about adding more space to the undo tablespace, but monitor how much undo space the segment takes up. If unexpired extents are getting stolen, then you may have to consider either adding more space, or reducing undo retention time. If the undo tablespace does not have enough space to accommodate your largest transaction, event after stealing extents, and the data files are not autoexensible, then you will get ORA-1650 (I think, that the error#). And to avoid it, auto undo management still needs to be monitored! HTH, - Kirti --- Thomas Jeff [EMAIL PROTECTED] wrote: Kirti, Thanks for this information.I've implemented AUM in a number of our development databases.One of the things I have to do is write up a monitoring policy to hand to our contracted production DBAs -- guidelines on how to address certain scenarios and so forth -- otherwise, they will simply resort to adding 'more' of whatever they presume is in short supply in event of a production crisis. For example, I'm seeing some steal counts in v$undostat, implying that the undo tablespace needs more space. However, from what you are saying, it seems that if undo_retention is consistently larger then maxquerylen during the period of time when the steal counts occur, that maybe the smarter thing to do is simply reduce the undo_retention parameter before considering adding more space? Jeff -Original Message- Sent: Friday, September 26, 2003 11:50 PM To: Multiple recipients of list ORACLE-L You can run following query to get an idea of undo generation rate and max query length: SELECT to_char(min(begin_time),'MM/DD/ HH24:MI:SS') Begin Time, to_char(max(end_time),'MM/DD/ HH24:MI:SS') End Time, (max(end_time)-min(begin_time))*24*60*60 Seconds, sum(undoblks) UndoBlks, sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60) UndoBlksPerSec, max(maxquerylen) MaxQueryLenSecs FROM v$undostat; Unfortunately, the TXNCOUNT column in v$undostat view in Oracle 9.2.0.x is screwed up. It does not correctly report the transaction count for the sample interval. Instead it keeps accumulating. One needs to do the math to get the correct count for the desired sample interval. It will show the time of high transaction activity with related undo generation. Oracle recommends setting undo_retention to the max(maxquerylen), but use your judgement. If data loads and queries accessing same tables, do not run at the same time (in DW, for example), setting undo_retention to a high number (maxquerylen) will simply waste disk space. If undo_retention is not set appropriately, you will get ORA-1555, and it will be reported in alert.log along with the affected SQL statement. The log entry will also contain the query time, in seconds, before it got aborted due to ORA-1555. Also, the above query works only when the database is using AUM. V$undostat does not report anything in 9.2.0.x when using MUM (manual undo mode). In 9.0.1, it returns one useless row when using MUM! BTW, you can also use the OEM to see the undo generation rate. It is one of the few things in OEM (standalone mode) I use. Hth. - Kirti snip __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Estimating space needed for UNDO tablespaces
You can run following query to get an idea of undo generation rate and max query length: SELECT to_char(min(begin_time),'MM/DD/ HH24:MI:SS') Begin Time, to_char(max(end_time),'MM/DD/ HH24:MI:SS') End Time, (max(end_time)-min(begin_time))*24*60*60 Seconds, sum(undoblks) UndoBlks, sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60) UndoBlksPerSec, max(maxquerylen) MaxQueryLenSecs FROM v$undostat; Unfortunately, the TXNCOUNT column in v$undostat view in Oracle 9.2.0.x is screwed up. It does not correctly report the transaction count for the sample interval. Instead it keeps accumulating. One needs to do the math to get the correct count for the desired sample interval. It will show the time of high transaction activity with related undo generation. Oracle recommends setting undo_retention to the max(maxquerylen), but use your judgement. If data loads and queries accessing same tables, do not run at the same time (in DW, for example), setting undo_retention to a high number (maxquerylen) will simply waste disk space. If undo_retention is not set appropriately, you will get ORA-1555, and it will be reported in alert.log along with the affected SQL statement. The log entry will also contain the query time, in seconds, before it got aborted due to ORA-1555. Also, the above query works only when the database is using AUM. V$undostat does not report anything in 9.2.0.x when using MUM (manual undo mode). In 9.0.1, it returns one useless row when using MUM! BTW, you can also use the OEM to see the undo generation rate. It is one of the few things in OEM (standalone mode) I use. Hth. - Kirti --- Daniel Fink [EMAIL PROTECTED] wrote: That sounds very reasonable. You can check that number against the values in v$undostat as it runs. Remember, UNDO_RETENTION is not guaranteed. If the space is needed by another segment, it may be taken even if the expire time has not been reached. Daniel Thomas Jeff wrote: Thanks for the reply Dan. Would you suggest setting UNDO_RETENTION to roughly the length of time of the longest running job in the database? For example, in our DW, our BI analysts tell me that their longest batch run is about 1 hr 45 minutes. My uneducated guess is to accordingly set the parameter to approx 2 hours. -Original Message- Sent: Friday, September 26, 2003 4:15 PM To: Multiple recipients of list ORACLE-L That is a good place to start. You might consider adding a little if you have many concurrent transactions or want to increase the undo_retention to a high number. Once you are using AUM, keep a close eye on v$undostat, though there are some known issues with it not populating properly, keep an eye on the begin_time and end_time. However, for estimation purposes it should work. Daniel Fink Thomas Jeff wrote: I'm beginning the process of converting over to automatic undo management. I'm wondering as to exactly how large to initially build the UNDO tablespace.Make it roughly the same size as the sum of the current rollback tablespaces?Or has your experience been different, i.e., you've found you've generally needed more or less space with respect to the previous allocation for rollback segments (manual undo)? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re[2]: 10g: SQL Plus
http://www.orafaq.net/faqscrpt.htm #12 under Unix Shell Scripts - Kirti --- [EMAIL PROTECTED] wrote: This can be done with ksh as well. Someone else may have a link to the script that a rather good ksh coder put together and posted here a few years ago. Jared Tanel Poder [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/24/2003 09:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Re[2]: 10g: SQL Plus Hi! Press F7 in windows command line sqlplus to see history of your sql commands (it's actually cmd.exe feature). And particularily nice feature is that you can search the F7 history list, by pressing the first letter of a command - you'll be navigate to alter command for example if you press a in history list, one more a will navigate to previous command starting with a. Pressing enter will execute the command again, pressing right arrow will just put the command to prompt where you can edit it before executing. This is one of the reasons why I'm staying on windows with my desktop :) Tanel. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Stuck Query
And there is a bug (#2803772) in 9.2.0.3 (may exist in all 9i, not sure). The seconds_in_wait is reset to 0 when the SEQ# in v$session_wait increments. A patch is available, and the bug is fixed in 9.2.0.4. - Kirti --- Cary Millsap [EMAIL PROTECTED] wrote: Fyi, seconds_in_wait is not incremented by queries of v$session_wait. It is incremented by LGWR approximately every three seconds. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- MacGregor, Ian A. Sent: Monday, September 22, 2003 6:40 PM To: Multiple recipients of list ORACLE-L Good catch. It was showing Waited Known Time, and the seconds_in_wait kept increasing. I was fooled by the latter being incremented each time v$session_event was queried. I did run a trace on it to track the waits, but had to kill the session. I've never gotten dbms_system session traces to work. They never return anything even on newly rebooted machines. Ian MacGregor -Original Message- Sent: Saturday, September 20, 2003 4:05 AM To: Multiple recipients of list ORACLE-L Hi! How do you verify that your session is still waiting on sequential read? From v$session_event? Is the status column saying WAITING there? (if it says WAITED%, then your CPU is doing something else already, and this record in session wait just shows the last wait). Tanel. snip __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Offshore protests + Idle events (to keep it On-track!)
If I install statspack, I always 'fix' the STATS$IDLE_EVENT table, and also change the varibale top_n_events to 10 (from 5) in spreport.sql. Statspack is a very generic tool. One must 'tune' it to reflect ones environment... :) - Kirti --- John Kanagaraj [EMAIL PROTECTED] wrote: Dan, You said that very well (as usual). The problem is that Management doesn't usually see the downside of their downsizing actions until the stuff hits the fan. And Larry's statements about self-tuning databases are not helping as well - they help apply pressure on a dwindling market, whether real or apparent. The ability to communicate complex problems in simple, everyday language will _always_ stand you in good stead - whether to the business, to management or your fellow workers. I recently replied patiently without sarcasm to a question on an internal Java user group about why compiling procedures/packages when users are online is a bad idea - I could have been rude and told the largerly development audience off. The VP of Development was on that list (I didn't know about this) and wrote me a nice complimentary note. I hope to stand well when there is a shake-up later on :) On Idle events - I had a situation previously when I was remotely diagnosing a performance issue and noticed a large number of PQ related idle events. Turns out that the DBA had switched on PQ to make the queries 'run faster' - the two CPU server was just choked to death after this. These 'idle' events from a Statspack report helped me solve the issue (turn off PQ on all tables - it was an OLTP system). John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) 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 ** __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Offshore protests + Idle events (to keep it On-track!)
I remove from this table the events deemed not idle for the database and Application supported by it. SQL*Net message related events can be important in client/server type environments. HTH, - Kirti --- Stephane Paquette [EMAIL PROTECTED] wrote: Can you elaborate on fixing the STATS$IDLE_EVENT table. Thanks Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Kirtikumar Deshpande Sent: Tuesday, September 23, 2003 2:15 PM To: Multiple recipients of list ORACLE-L If I install statspack, I always 'fix' the STATS$IDLE_EVENT table, and also change the varibale top_n_events to 10 (from 5) in spreport.sql. Statspack is a very generic tool. One must 'tune' it to reflect ones environment... :) - Kirti --- John Kanagaraj [EMAIL PROTECTED] wrote: Dan, You said that very well (as usual). The problem is that Management doesn't usually see the downside of their downsizing actions until the stuff hits the fan. And Larry's statements about self-tuning databases are not helping as well - they help apply pressure on a dwindling market, whether real or apparent. The ability to communicate complex problems in simple, everyday language will _always_ stand you in good stead - whether to the business, to management or your fellow workers. I recently replied patiently without sarcasm to a question on an internal Java user group about why compiling procedures/packages when users are online is a bad idea - I could have been rude and told the largerly development audience off. The VP of Development was on that list (I didn't know about this) and wrote me a nice complimentary note. I hope to stand well when there is a shake-up later on :) On Idle events - I had a situation previously when I was remotely diagnosing a performance issue and noticed a large number of PQ related idle events. Turns out that the DBA had switched on PQ to make the queries 'run faster' - the two CPU server was just choked to death after this. These 'idle' events from a Statspack report helped me solve the issue (turn off PQ on all tables - it was an OLTP system). John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) 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 ** __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SGA Max size
Please make sure SGA_MAX_SIZE works on your h/w platform. It does not work on AIX as advertised, and I think Sun Solaris needs some tweaking (related to ISM use) to make it work. - Kirti --- Tanel Poder [EMAIL PROTECTED] wrote: In 9202, DB_BUBBER_CACHE, SHARED_POOL, LARGE_POOL and JAVA_POOL can be dynamically altered. But in 901, LARGE_POOL and JAVA POOL are static. If MAX SGA is less than 128MB then Oracle will use 4MB granule size to allocate/deallocate memory. For SGA greater than 128M, Oracle granule size is 16MB. Btw, in Windows the granule size is 8M if SGA_MAX_SIZE is set larger than 128M. Note that there's more stuff in SGA than above mentioned areas. Fixed SGA for example etc.. Tanel. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 9/20/03 11:19 AM My understanding of SGA is SGA = x + y + z where x = (dbblksize*db_blk_buf OR db_cache_Size if 9i) y=shared_pool z=java pool, log_buffer If 9i oracle introduced SGA_MAX_SIZE; the sum of x+y+z can be SGA_MAX_SIZE; if so, which part of x/y/z expands when need arises. Thanks Quriyat __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: YEAAAAAAAAAAAAAAAAAAA!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!, drop in number
Is 42 a better number? ;) - Kirti --- KENNETH JANUSZ [EMAIL PROTECTED] wrote: I don't agree. If it were up to me I would cut it to 65 total. Ken - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 22, 2003 3:44 PM number http://story.news.yahoo.com/news?tmpl=storyu=/nm/20030922/us_nm/life_usa_im migration_dc_4 __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Huge optimization costs with 9.2
Were tables/indexes anlayzed after the upgrade? - Kirti --- Jeff Landers [EMAIL PROTECTED] wrote: Hello All Version OS: Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. Problem: We've captured the sql text and optimization plans for critical sql prior to upgrading to 9.2. After the upgrade we have noticed that the cost associated with every sql statement is now HUGE compared to its 9.0.1.4 counterpart. Per the statistics being captured via traces, these statement are noticeably slower per execution. Anyone experiencing/experienced the same problem with 9.2? Thank you in advance. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT: Tempfile weirdness
Yeah, I got that too.. I did not have in my post any of those words you mentioned. - Kirti --- Tanel Poder [EMAIL PROTECTED] wrote: Heh, look what happens if you try to help someone ;) (an automatic reply to my message) --- Trend SMEX Content Filter has detected sensitive content. Place = Multiple recipients of list ORACLE-L; ; ; [EMAIL PROTECTED] Sender = Tanel Poder Subject = Re: Tempfile weirdness Delivery Time = September 18, 2003 (Thursday) 05:43:08 Policy = Sexual Discrimination Action on this mail = Quarantine message Warning message from administrator: Notice: A message you sent appears to have violated GE Financial Assurance email policies for inappropriate language or content and may not have been received by the recipient. -- Where the heck was the sexual discrimination part in my post? The bad sectors part or perhaps hardware errors? :) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 1:29 PM It also can be caused by bad sectors or other hardware errors, but I'd bet on sparse file problem as you described. When I create temp tablespaces, I either copy the tempfile into another filename and then rename to original using cp, that way the file isn't sparse anymore, thus no problems using it. Or another way is to create a regular tablespace with a datafile first, then drop the tablespace then create temp tablespace reusing this datafile. That way can be handy when you don't have shell access for some reason. Btw, in most unixes I've worked with, you can see the real size of a sparse file using ls -ls (in file system blocks). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 2:04 AM We also ran into the IO error situation on a few of our 9i R2 databases using TEMPfiles. DBAs forgot that the file system contained those tempfiles and created other datafiles on those. We are now considering using dedicated file system naming convention that will hold TEMPfiles. We already have /u888, and /u999 for dump dirs and archive logs respectively. Now /u777 for tempfiles is under review. - Kirti --- Stephen Lee [EMAIL PROTECTED] wrote: Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong and make life a bitch? Note that the index create blew up immediately, so the original tempfile never grabbed any space. -- __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: IBM AIX 32-Bit
Metalink Note # 225551.1 'AIX - 32bit vs 64bit' will explain what is supported and how to do it. - Kirti --- Daiminger, Helmut [EMAIL PROTECTED] wrote: Hi there! I got a question: Can I run Oracle 64 Bit on IBM AIX 32 Bit? I know that AIX 64 Bit can run either Oracle 32 Bit or 64 Bit. Does it also work the other way round? Thanks, Helmut __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to extract data directly from a datafile ?
At the recent OW conf, Oracle folks did announce that Oracle10g will support TTS across platforms/OS. No word on any data extraction tool as you guys are discussing here. - Kirti --- Richard Ji [EMAIL PROTECTED] wrote: KG, Yes, if Oracle start to address this, then it will be pretty hard to compete with them. :) Besides they already have the DUL, just hasn't made it freely availble to us. So this is something they address in 10G? Both the ability to unload data from data file directly and convert data files accross different platforms? That'd be great. But it might still be good to have such tools for 8i and 9i since people will probably be on it for years to come. Richard -Original Message- Sent: Wednesday, September 17, 2003 6:30 PM To: Multiple recipients of list ORACLE-L Richard: Not sure whether somebody else also tried this. Sometime back we (yours turley and few others ) tried developing a tool to convert a datafile from one OS format to another OS Format (little endian-big endian or Solaris to NT etc) by reading the datablocks from one format and writing to another format. The original plan was to have a iDUL kind of tool with a fileconverter to covert oracle datafiles from one OS to another OS. So the user will have a choice to download the data from data files or convert from one os to another OS. Since oracle started addressing this facility/feature in the current (for future?) versions we have dropped that idea as we don't want to compete with Oracle :D I would be interested in knwoing abt your tool too .. KG __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL help needed
Hello All, Thanks for your wonderful ideas. I just forwarded those to my dear Developer (my wife, that is :) After posting my question to the list last night, I was told that the query will be run against an Informix database :) Typical Developers !!! ;) Cheers! - Kirti --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: Hello Listers, I need some SQL help. Not sure if this is possible in plain SQL or not, but here it is: I have a table with two columns: EMPNBR LINENBR -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). Thanks. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: +++Need an urgent info+++
Find out what are the job responsibilities of a Support Engineer. If your friend needs a job badly, and if the salary is acceptable, this could be a good opp to learn on the job while helping the Customers. Hyderabad is an interesting but a crowded city (no surprise there, it's India :) with a mix of old and new culture. Language can pose some challenges in some parts. Great spicy food, though! HTH, - Kirti - Original Message - From: Ora DBA To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 17, 2003 4:44 PM Subject: +++Need an urgent info+++ Dear Friends, Good day. Hope doing well , sorry for OFF topic. My Friend has got an offer with Oracle India at Hyderabad (as a support engineer in Indian support centre , Hyderabad, India ). I need your suggestions , inputs and valuable thoughts for making a decision. What will be the work environment , benifits and so on..(probs and cons ). Please share your thoughts, have 2 make an important decision in life. Thanks a lot. Cheers, Oracle DBA. -- Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tempfile weirdness
We also ran into the IO error situation on a few of our 9i R2 databases using TEMPfiles. DBAs forgot that the file system contained those tempfiles and created other datafiles on those. We are now considering using dedicated file system naming convention that will hold TEMPfiles. We already have /u888, and /u999 for dump dirs and archive logs respectively. Now /u777 for tempfiles is under review. - Kirti --- Stephen Lee [EMAIL PROTECTED] wrote: Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong and make life a bitch? Note that the index create blew up immediately, so the original tempfile never grabbed any space. -- 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL help needed
Hello Listers, I need some SQL help. Not sure if this is possible in plain SQL or not, but here it is: I have a table with two columns: EMPNBR LINENBR -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). Thanks. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
U R Welcome. Although, reducing SGA size is technically possible, it can be a very expensive operation in a busy environemt and can take a long time to complete. I have not run any specific tests to see how long it takes to reduce SGA by a certain amount, but you can imagine what must be done (find least used blocks/age out blocks, keep them from re-use, and finally 'detach' them etc..) - Kirti --- Mohammed Shakir [EMAIL PROTECTED] wrote: Kirti Thanks for the info. I could not raise dynamically the size of db_cache_size and did not know why, until I noticed the new parameter sga_max_size. Anyway, I tried to lower the size of db_cache_size dynamically and I did not have a problem. So sgma_max_size does play its role in db_cache_sizing. I have not tested where I increase the size of sga_max_size using init.ora and then try to increase the size of db_cache_size dynamically by the same size as the increase in sga_max_size. That is the test for coming weekend. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what is BAARF? --- OFA
You may want to check out Bill Burke's paper Putting Oracle's OFA on Steriods at http:// www.oracleguru.com Is Bill still on this list? - Kirti --- Jesse, Rich [EMAIL PROTECTED] wrote: Hey Tim, D'oh! The problem with e-mail is that it takes an hour to convey what would take 5 minutes in person... :) While I stand by my original e-mail, I do understand that OFA was designed to do exactly what the acronym says: be optimal and flexible. My problem with it is that I do not see OFA as optimal, at least not anymore. Perhaps it's just me, but I just don't understand the reasoning of the parts of OFA I had outlined now nor 14 years ago. Maybe it's because I had much more hair 13-14 years ago (more on my head, at least, with less in other places), and the combined human caused faults/accidents over those years has caused me to be much more strict in the way I'd like to see hardware and software set up. Or perhaps it's because I cut my teeth on VMS and haven't conformed to accepted Unix practice, nor do I see a need to if an alternative can be established as being subjectively better (re: /unn mountpoints). OK, enough of my babble. I would LOVE to see OFA updated! I won't hold my breath that any of my suggestions would be incorporated, but then again I'm just one person. I'll happily continue using a mostly-OFA setup. Except on VMS, which Oracle Corp does not conform to OFA at least thru v8.1.7. Then again, we have no more VMS... :( BTW, wasn't dangling from a clock tower Harold Lloyd's trademark? :) Thanks for listening to my whining, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Tim Gorman [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 11:09 AM To: Multiple recipients of list ORACLE-L Subject: Re: what is BAARF? --- OFA Rich, __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
Because you are still using db_block_buffers in init.ora file.. - Kirti --- Roger Xu [EMAIL PROTECTED] wrote: Why I have db_cache_size set to zero in my database? SQL show parameter db_cache_size NAME TYPEVALUE --- -- db_cache_sizebig integer 0 SQL -Original Message- Sent: Tuesday, August 05, 2003 5:34 PM To: Multiple recipients of list ORACLE-L snip __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Opatch
Is the correct perl lib in the PATH? - Kirti --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: I'm trying to use opatch and receive the following error [EMAIL PROTECTED] $ opatch apply PERL5LIB=; export PERL5LIB /usr/local/bin/perl /opt/oracle/admin/general/patchsets/opatch/patch2617419/OPat ch/opatch.pl apply Can't call method build_option_details on an undefined value at /opt/oracle/admin/general/patchsets/opatch/patch2617419/OPatch/opatch_modules/Apply.pm line 2299. ORACLE_HOME is correctly set, PATH is correct as well. Any idea what the problem may be. Every opatch command fails with the above error including such things as opatch -version. Ian MacGregor Stanford Linear Accelerator Center -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 9i - Dynamic SGA - SGA_MAX_SIZE
Thanks, Mladen. That makes sense. One just needs to make sure (by testing) that the OS supports this feature. - Kirti --- Mladen Gogala [EMAIL PROTECTED] wrote: Kirti, problem is in the malloc mechanism. The subroutine to free the memory doesn't return it to the OS, it returns it to the allocation pool instead. As a consequence, when oracle does free, its address space is not going to go down. You realize, of course, that malloc, calloc, realloc and free are the only options, because of their portability. There are low level allocation routines like brk() to increase the address space, but unaware of any routine to decrease the address space. Here is the manual page for the library subroutine called mallopt, which is mostly obsolete but still exists on HP-UX. The mechanism remained the same, but the control was taken away from the users. Manual page does illustrate the mechanism, though. malloc(3C) malloc(3C) snip __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti --- Mohammed Shakir [EMAIL PROTECTED] wrote: I am little confused about this issue to. I am working on Oracle 9.2.0.3 on Solaris 9 (64 bits) platform. I did not set sga_max_size parameter and I see it set. I am not sure what it means and what kind of problem it will cause me. I have pga_aggregate_target is set for 512MB and it seems it is not counted in this count. I know it is a separate space in the memory. Since this is a new system for me, I am little concerned that Oracle does not chock on me. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: It does not work as advertised, in AIX either... I played with this in AIX 5L. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
It does not work as advertised, in AIX either... I played with this in AIX 5L. - Kirti --- Hately, Mike (LogicaCMG) [EMAIL PROTECTED] wrote: Stephen, The documentation is pretty wooly regarding this issue but the way it seems to be intended to work is this: At startup Oracle will allocate an SGA sized as specified in the sga_max_size parameter. This is to ensure that the system has enough memory accomodate what you see as a maximum requirement for the SGA. After it's allocated this and started the database it should deallocate any memory it holds over and above that required to store the components of the SGA. In some platforms/versions this deallocation doesn't occur. Solaris for example behaves like this unless you move to version 8. It's possible that your version of Tru64 has a similar limitation or that you're seeing a bug. To my mind though, Oracle Support's claim that this is expected behaviour is a bit of a cop out. This is certainly not the way it was supposed to work. The concept guide states the following: The SGA can grow in response to a database administrator statement, up to an operating system specified maximum and the SGA_MAX_SIZE specification. and Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE Both of these statements imply that the unused memory is supposed to be released back to the operating system. The way that this feature operates on your system it allows you to juggle storage backwards and forwards between caches which is still useful but not 'what it says on the box'. I'd ask Oracle under what cirtcumstances this is normal behaviour. It's not the way the software is intended to work so maybe it's a platform limitation. In order to give you a better idea of what Oracle thinks it's SGA is using you can query the following views : - V$SGA_CURRENT_RESIZE_OPS: Information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component. - V$SGA_RESIZE_OPS: Information about the last 100 completed SGA resize operations. This does not include any operations currently in progress. - V$SGA_DYNAMIC_COMPONENTS: Information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations since startup. - V$SGA_DYNAMIC_FREE_MEMORY: Information about the amount of SGA memory available for future dynamic SGA resize operations. Hope this helps, Mike Hately __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to display FLOAT in 99999.999 format
Try 9.000 instead. - Kirti --- Odland, Brad [EMAIL PROTECTED] wrote: Tried that. 1234.1 won't show the trailing zeros... -Original Message- Sent: Friday, August 01, 2003 1:19 PM To: Multiple recipients of list ORACLE-L Not sure if this is the best way but you could do something like SELECT TO_CHAR(1,'999.999') FROM DUAL; Only drawback that I have been running into is if you don't allot of enough spaces before the decimal it display a value of . Hope this helps. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 01, 2003 12:14 PM How do I force display of a FLOAT in a format like Original number Result 1234.34 1234.340 12345.456 12345.456 123.1 123.100 123123.000 The dev on a project wants to preformat the data for a report on the database side. He wants 3 places after the decimal to display even if the number is a whole number (I know most VB report controls do this easilybut...) I thought it was complicated as it would require a to_char with format description then searching for the postion of the decimal and then moving from there to the right counting the characters if the number of characters to the right of the decimal was 3 then pad with zeros... I messed around with RPAD, TRUNC, ROUND and TO_CHAR and found I was not able handle all cases of possible significant digits. Sounded like a complicated function to do something thats automagic in a reporting control. Or am I forgetting a oracle functionor fomat? This is 8.1.7 I told the developer to use the field formatting contols of his reporting control because the additional zeros padded in a number are basically meaningless in a database and mathematical operations. Brad O. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: 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: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ** how to check version and problems
Another way to is to install the 'opatch' utility (needs Perl;) and use 'opatch lsinventory'. - Kirti --- Pete Finnigan [EMAIL PROTECTED] wrote: Hi, have a look at $ORACLE_BASE/oraInventory/logs/installActions.log. hth kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: undo tablespace and rollback segments in oracle9i
Your question is not very clear. Sorry. Can you please post results of following commands? Thanks. - Kirti SQL show parameter undo SQL select segment_name, tablespace_name from dba_rollback_segs; SQL select a.usn, a.name, b.status 2 from v$rollname a, 3 v$rollstat b 4 where a.usn = b.usn; --- [EMAIL PROTECTED] wrote: I use manual managed undo tablespace but there are some system named rollback segments on that tablespace ( I did not created them ) and they can not be dropped. What are they , how can I drop them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: undo tablespace and rollback segments in oracle9i
Your attachment was removed by the list server. However, your direct e-mail to me had the following: SQL show parameter undo ; NAME TYPEVALUE --- -- undo_management string MANUAL undo_retention integer 900 undo_suppress_errors boolean FALSE undo_tablespace string rbs SQL SQL select segment_name, tablespace_name from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME -- -- SYSTEM SYSTEM _SYSSMU1$ RBS _SYSSMU2$ RBS _SYSSMU3$ RBS _SYSSMU4$ RBS _SYSSMU5$ RBS _SYSSMU6$ RBS _SYSSMU7$ RBS _SYSSMU8$ RBS _SYSSMU9$ RBS _SYSSMU10$ RBS SEGMENT_NAME TABLESPACE_NAME -- -- RBS01 RBS RBS02 RBS RBS03 RBS RBS04 RBS RBS05 RBS RBS06 RBS 17 rows selected. SQL select a.usn, a.name, b.status 2 from v$rollname a, 3 v$rollstat b 4 where a.usn = b.usn; USN NAME STATUS -- -- --- 0 SYSTEM ONLINE 11 RBS01 ONLINE 12 RBS02 ONLINE 13 RBS03 ONLINE 14 RBS04 ONLINE 15 RBS05 ONLINE 16 RBS06 ONLINE 7 rows selected. --- So, it is clear that RBSnn were created in an undo tablespace titled RBS. To remove system managed undo segments (_SYSSMUn$) one must drop the undo tablespace itself. But, in your case, you will first have to: 1. Create a new, normal tablespace, say, rollback_ts, to hold new rollback segments (do not create it using create undo tablespace ) 2. Create required rollback segments (rbs07, rbs08 etc) in rollback_ts. 3. Bring them all online. 4. Offline RBS01 to RBS06 and drop them. 5. Drop tablespace RBS. Modify init.ora parameter as required. Hope this helps. - Kirti --- [EMAIL PROTECTED] wrote: hi , please look attached file ; -Original Message- Sent: Wednesday, July 30, 2003 3:52 PM To: [EMAIL PROTECTED] Cc: Bahar, Arslan Your question is not very clear. Sorry. Can you please post results of following commands? Thanks. - Kirti SQL show parameter undo SQL select segment_name, tablespace_name from dba_rollback_segs; SQL select a.usn, a.name, b.status 2 from v$rollname a, 3 v$rollstat b 4 where a.usn = b.usn; --- [EMAIL PROTECTED] wrote: __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
Re: 9i-OCP Question
I will guess -- 1. - Kirti --- Senthil Kumar [EMAIL PROTECTED] wrote: Hi all, What is the correct answer for this? Q If you have 2 redo log groups with 4 members each, how many disks does Oracle recommend to keep the redo log files? 1. 8 2. 2 3. 1 4. 4 Which is the correct answer. TIA Senthil -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i-OCP Question
Right, You Are!! :) That's how I arrived at my answer 1. - 8 disks ;) - Kirti --- Orr, Steve [EMAIL PROTECTED] wrote: Nah, the answer is 42. :-) -Original Message- Sent: Wednesday, July 30, 2003 11:39 AM To: Multiple recipients of list ORACLE-L I will guess -- 1. - Kirti --- Senthil Kumar [EMAIL PROTECTED] wrote: Hi all, What is the correct answer for this? Q If you have 2 redo log groups with 4 members each, how many disks Q does Oracle recommend to keep the redo log files? 1. 8 2. 2 3. 1 4. 4 Which is the correct answer. TIA Senthil -- Please see the official ORACLE-L FAQ: http://www.orafaq.net __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: direct path write waits, please help
But, I would like to know how this seemingly high wait for 'direct path write' is
affecting the
overall response time. (ResponseTime = WaitTime + ServiceTime)
If the 'CPU used by this session' is not considered in light of these wait times,
aren't you
getting ready to bark at the wrong tree?
- Kirti
--- John Kanagaraj [EMAIL PROTECTED] wrote:
Hans,
Now let me guess Your disks are all RAID 5, right? And you possibly are
bottlenecking on CPU as well? It is clear from the Top 5 that writes are an
issue across the board, to TEMP (direct path write), Redo (log file sync)
and DB files (db file parallel writes). Creating a RAID 1 set of disks and
moving at least the TEMP, RBS, Redo (and Arch if present) to this will
definitely help.
John Kanagaraj
Phone: 408-970-7002 (W)
Fax: 408 327 3086 (Call/Email prior to fax)
-Original Message-
Sent: Tuesday, July 29, 2003 8:54 AM
To: Multiple recipients of list ORACLE-L
Hi All,
Please help me tune this i/o related wait event. This is my 8.1.6 statspack
top-5 wait list:
Top 5 Wait Events
~ Wait %
Total
Event Waits Time (cs) Wt
Time
---
direct path write 304,867 35,925
49.83
log file sync 145,015 23,441
32.52
db file sequential read11,3703,684
5.11
file open 9813,326
4.61
db file parallel write 1,8933,115
4.32
You'll notice that 'direct path write' is the most expensive one in the
list. I cannot find enough info on the net about this wait event, therefore
I'm asking the real experts.
What events in Oracle trigger this wait event? In what way is this event
different from db file parallel write?
I mostly read comments that suggest lots of sorting and parallallel queries.
However, most sorts are done in memory and degree = 0 for all tables.
Any suggestions are very welcome.
Thanks,
Hans de Git
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Oracle 7.3.2
We are running 7.3.4.0 on HP 11.0 with no problems at all, with compatible set to 7.3.3.0. The Application uses some old OO stuff written in GemStone. - Kirti --- Ehresmann, David [EMAIL PROTECTED] wrote: We are going to upgrade a server from HP10.2 to HP11 or HP11i. There is an old version of Oracle 7 running on the box, Oracle 7.3.2. I looked through Oracle's certification matrix and found nothing about HP11 and 7.3.2. I am not concerned if Oracle has certified or desupported this, I just want to know will 7.3.2 run on HP11 and if there are any issues I need to be concerned about. thanks, David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: upgrade to 9i
Are you missing something in your reply? If its not an then. From migrating 8.1.7 to 9i, one has to follow prescribed procedure. To my knowledge one needs to run u0801070.sql after starting the 8i database in 'migrate' mode...etc.. etc... I have the GUI thing, sorry! - Kirti --- [EMAIL PROTECTED] wrote: If its not an then you can simply run catalog.sql and catproc.sql to upgrade oracle dictionary tables. Also during 9I installation Oracle ask to upgrade existing databases on that system. -Original Message- Sent: Tuesday, July 29, 2003 3:54 PM To: Multiple recipients of list ORACLE-L Hi where can I find steps ( procedure ) for upgrading from 8.1.7 to 9i . Thanks, -ak __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Unix root account remote access
That would work. I have done that in the past, by hanging a modem to the Console RS-232 port and dialing in. Loaded complete AIX 3.2.5, and Oracle 6.x from my office in Irving TX, while the server was in Everglades, FL. Worked fine, except it was a bit slow.. There was no GUI and Java stuff in that Oracle Installer ;) - Kirti --- Stephen Lee [EMAIL PROTECTED] wrote: One possibility -- probably a slim possibility: If the remote site can hook up a console server to the box, then you connect to the console server, and it will be like you are right there at the console ... That's the theory anyway. -Original Message- Sent: Friday, July 18, 2003 9:04 AM To: Multiple recipients of list ORACLE-L Get airline tickets, you are out of luck -Original Message- Sent: Friday, July 18, 2003 8:49 AM To: Multiple recipients of list ORACLE-L Thanks for the suggestion. Unfortunately, there is no other account created at this point, and I don't have access to the machine it is remote. Mohammed Shakir [EMAIL PROTECTED] wrote: Why do not you login as you and then login as root? It is for your protection that nobody should be able to remote login from outside and any one who is login as root can be monitored. HTH --- M.Godlewski wrote: List, I want to install Oracle on a UNIX system no sys admin on board yet, so I get to set up the system with the Oracle account etc. Unfortunately, I can not log into the root account remotely. I get a non console message. Is there a way to allow remote root connections? - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) ! 257-6001 (Home) __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sqlldr questions
An excellent book for SQL Loader new comers, and experienced users as well, is one by Jonathan Gennick and Sanjay Mishra : Oracle SQL Loader, The definitive Guide, by O'Reilly. Stenphen Andert will agree with me ;) - Kirti --- John Dunn [EMAIL PROTECTED] wrote: I'm a sqlldr newbie. Platform is Oracle 9.2. My question is...can I prevent sqlldr loading any data if there is even a single bad record, even if it is, for example, the last record? I am using conventional path load. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why are SPFILEs binary anyway? (was RE: How to make SPFILE in
Editing spfile is not supported by Oracle. And I think it is documented as such. You can just view it, copy it and play with the copied version what whatever... One thing to remeber is to always hold on to the init.ora version. No matter what. Or at least until Oracle completely removes init.ora file when the Oracle RDBMS software becomes smarter than human brain ;) - Kirti -Original Message- Sent: Thursday, July 17, 2003 10:54 AM To: Multiple recipients of list ORACLE-L in My experience also on Win2K. 9.2.0.1.0 the file is editable. On 9.2.0.3.0 results are unpredictable if you edit the file. I guess that they really don't want us editing it. This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2 __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: STATSPACK in Oracle 8.1.6.0
The latter. Scripts are already supplied. - Kirti --- Seema Singh [EMAIL PROTECTED] wrote: Hi, I want to delete all old records of statspack from perfstat schema. What would be best way to do this either by truncating all tables or remove and recreate ? Pl advice. thx -Seema _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN SQL Backtrack
Wow! Mladen, thanks for listing my name in your list. As Arup mentioned, you forgot yourself. I would also suggest Dan Fink, Tim Gorman, and Dr. Mogens! Gaja is moving the other (dark) side, so I won't mind excluding him :) When it comes to RMAN, I am not even in the rookie stage. We bought a few copies of RF's book. But mine has now found its place under my desk! The Damagement is not yet willing to commit any time and resources to play with RMAN and without the Tivoli API, we can not do much. Although RMAN is free and the MML API may not cost as much, but when you have a merged Company like ours (VERIZON=GTE + BellAtlantic), we have our own 'sand boxes' and playing fields. Old BA stuff still uses SQL*BT while we, the fGTE DBAs want to move to RMAN But may be we all leave that decision to Amdocs, as they might get our IT business pretty soon, per some official rumors. Till then we will continue to use good old, and aged, hot backup scripts to backup databases of all shapes and sizes (from 1G to over 500G) Cheers! - Kirti --- Gogala, Mladen [EMAIL PROTECTED] wrote: I don't have any experience with SQL*Backtrack and I do have some experience with RMAN. Here are my comments: a) RMAN is reliable. Once you write the backup scripts, they are executed by operations and there no surprises. In order to rely on those scripts, one needs to test them, especially the recovery part. b) RMAN needs a 3rd party backup software to run. Things like OmniBackup, Tivoli, Legato or SyncSort can be rather expensive. RMAN doesn't write to tapes itself. RMAN delegates a backup software contacted through the routines from libobk.so (or libobk.dll or libobk.sl) to do its writing. To get the libobk.so from you backup software vendor of choice, you generally have to write a check. That means that RMAN is NOT free. c) Before version 9, RMAN was arcane and hard to learn. Thanks to Robert Freeman, it is no longer so. You can learn how to configure and use RMAN and you can find a decent book to learn RMAN from. It's not very hard and it's fairly logical. One reading of the books suffices for a good general understanding. d) Quality of the software: RMAN leaves a lot to be desired. Its biggest drawback is the fact that it doesn't do any coordination with the underlying backup catalog. In other words, you can happily declare backup obsolete in RMAN and Legato will not know anything about it and vice versa. You can even delete backup in Legato and reuse the tape while RMAN knows nothing about it. On the other hand, RMAN, in contrast to all other methods, does not put tablespaces into the backup mode, thus generating floods of redo archives. RMAN doesn't backup data blocks that have never been used (behind the watermark blocks), which is great if you have a fresh new datafile which was added to the tablespace just in case something might run out of space. e) Personnel. Despite the certification process, it is not always easy to find a trained personnel which knows how to use it and how to recover the database. I consider the ability to recover the database a basis for someone to call himself/herself a DBA. You would be surprised how many people which claim that title do not know how to recover the database. Even smaller number knows how to use RMAN. f) I would suggest Jared Still, Cary Millsap, Rachel Carmichael, Jonathan Lewis, Wolfgang Breitling, Steve Adams, Gaja V., Arup Nanda, Kirti Deshpande and Anjo Kolk to start the Oracle List certification process. I would trust that one more then the OCP. I apologize to anyone who I might have forgotten. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 16, 2003 10:59 AM To: Multiple recipients of list ORACLE-L __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RMAN SQL Backtrack
Well, he is part of Oracle 'chefs' now. Praising the food before they cooked it ;) - Kirti --- Arup Nanda [EMAIL PROTECTED] wrote: Kirti, Gaja is moving the other (dark) side??? Elaboration, please. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 5:29 PM Wow! Mladen, thanks for listing my name in your list. As Arup mentioned, you forgot yourself. I would also suggest Dan Fink, Tim Gorman, and Dr. Mogens! Gaja is moving the other (dark) side, so I won't mind excluding him :) When it comes to RMAN, I am not even in the rookie stage. We bought a few copies of RF's book. But mine has now found its place under my desk! The Damagement is not yet willing to commit any time and resources to play with RMAN and without the Tivoli API, we can not do much. Although RMAN is free and the MML API may not cost as much, but when you have a merged Company like ours (VERIZON=GTE + BellAtlantic), we have our own 'sand boxes' and playing fields. Old BA stuff still uses SQL*BT while we, the fGTE DBAs want to move to RMAN But may be we all leave that decision to Amdocs, as they might get our IT business pretty soon, per some official rumors. Till then we will continue to use good old, and aged, hot backup scripts to backup databases of all shapes and sizes (from 1G to over 500G) Cheers! - Kirti snip __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to make SPFILE in sync with INIT.ORA ?
the init.ora file. You should be able to just make the change once. Also, there is a new generation of I.S. people coming along that doesn't think you can operate a computer without a mouse. Against Microsoft's super GUI interfaces, the idea of manually editing a text configuration file seems very last century. I think with the pfile and spfile, Oracle was trying to give us the best of both worlds. How well they succeeded is a matter of judgement. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 15, 2003 9:14 PM To: Multiple recipients of list ORACLE-L The ability to change the system parameters without bouncing the system is not provided by spfile, neither in RAC nor single instance databas. It depends upon the the parameter that can be changed dynamically or not. I guess you wanted to convey the impression that the using spfiles the parameters can be changed and the changes can be persistent across shutdowns. But that is not just in RAC; it's true for single instance DBs, too. Now, suppose you want to set a parameter that can't be changed using ALTER SYSTEM, such as, say, java_pool_size. How do you plan to make the change? You have to open up the old favorite init.ora file and start the database with pfile=init.ora option. At that stage the spfile is not active and your issuing Alter system set db_cache_size = 800m scope = [ memory | spfile | both ] sid = * has no effect. You must create the spfile from the pfile and then use the newly created spfile to use this dynamic parameter persistent. Note the complexity involved - spfile allowed you to make the changes to some parmeters using alter system persistent; but for all other parametrs you are forced to use pfile. What happens if you ommit the pfile=init.ora clause? The database will pickup the spfile, which will _not_ have your changes. To fully appreciate the value of the spfile parameter, Oracle should have allowed editing spfile directly and completely done away with pfile. Splitting functionality across two different implementations adds to difficulties, does not resolve them. I hope future versions of Oracle do offer that functionality. It's not that difficult or unheard of, either. Listener.ora, tnsnames.ora are all editable and also read by Net Assistant. just my .02 Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 8:09 PM === message truncated === __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why are SPFILEs binary anyway? (was RE: How to make SPFILE in
Keeping them binary gives them power I guses... On most platforms there is just a hashed value on the first line. In 9i R1 (9.0.1), I was able to replace some param value without changing number of chars on the line, and the changes were taken without any problem (HP-UX 11, it was I think). Could not believe that it worked. No such luck in 9i R2... Binary, text, whatever, I will just stay away from him till pfile really goes away :) - Kirti --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: it isn't binary on solaris ... pure text ... Raj -Original Message- Sent: Wednesday, July 16, 2003 6:40 PM To: Multiple recipients of list ORACLE-L sync with INIT.ORA ?) I can definitely appreciate the benefits of dynamic parameters, and of being able to persist the values of those parameters accross shutdown/startup cycles. But I would have guessed that oracle could have gotten both of those features together without going to a binary parameter file--couldn't whatever process writes to the spfile just write to a plain text file instead? Does anybody know what the advantage of having a binary store of params is? Cheers, -Roy This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2 __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 10i reg.
Thanks for the amusement, btw there is nothing in the article that isn't on metalink, go figure Very true.. Not worth to share your e-mail address to receive more 10i information as it becomes available :) Oracle will be pushing it down our throat for free... I bet those sign makers in SFO know more about it as they paint 10i stuff on billboards, cabs, buses for the OW Party ;) - Kirti :) --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: From the article ... quote Oracle 10i is expected to include Oracle Real Application Cluster (RAC) support for Sun, HP and IBM Unix boxes, bringing them up to date with technology currently available for Windows and Linux. /quote Ha Ha Ha Ha Ha Ha . The person wrote this has probably NO idea what RAC is and where it runs and its availability ... Thanks for the amusement, btw there is nothing in the article that isn't on metalink, go figure ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to make SPFILE in sync with INIT.ORA ?
make spfile from pfile; something like that. I would suggest just deleting it altogether. :) - Kirti --- Prem Khanna J [EMAIL PROTECTED] wrote: Guys, i have a 9iR2/win2k test instance. i just renamed CONTROL01.CTL once and tried to start the instance. SQLstartup But it gave a ORA-00205 error. so i removed CONTROL01.CTL from INIT.ORA file and started the instance SQLstartup pfile='d:\oracle\admin\pe92\pfile\init.ora' Now it worked fine. BUT: SQLstartup This is not possible because the SPFILE still has CONTROL01.CTL in it. How do i make my SPFILE in sync with the INIT.ORA ? Regards, Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: import from 8i to 9i /IMP-00003:ORA-02298
I suggest calling Oracle Support. Get a test case ready. Please post how the problem gets resolved. - Kirti --- [EMAIL PROTECTED] wrote: I tried it on my sun box, still same problem. Also I tried with no compressing/uncompressing of dmp file, no luck. -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 5:25 PM To: Multiple recipients of list ORACLE-L With all my today's trials(including import into 8i db on Sun) I used different export file than the one I have used other day. I am wondering if the ftp(I did it in bin mode only) from unix box to windows box caused something? Anyway, I got my Sun box ready with 9i and will know soon if the problem is between Unix and Windows. Thanks for your reply. Surendra -Original Message- Sent: Tuesday, July 08, 2003 3:59 PM To: Multiple recipients of list ORACLE-L Could your export file be corrupt. There have been a few alerts on 8.1.7 exports producing bad dump files. One alert is Note:223399.1. Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 [EMAIL PROTECTED] 7/8/03 3:34:29 PM What userid is exporting data and importing data? Try exporting and importing as system. Raj __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: tunning an index built
When using parallelization with higher values for S_A_S, keep in mind that *each* PQ slave will potentially use that much memory for sorting. Should this sorting use disk, it would cause even more direct i/o to temp tablespace. Parallelization can very easily paralyze you system ;) Also, S_A_S and S_A_R_S can be set at session level. No need to bounce the instance. - Kirti --- zhu chao [EMAIL PROTECTED] wrote: Hi, First of all, you should use nologging. This helps a lot. Second, when using parallel clause, the sort_area_size of the ora_pxxx size is not the sort_area_size of your session, it is the instance's default size, when it is started. I have verified it. So if you want to make parallel works better, you can increase the sort_area_size of your instance and bounce the instance. Also try to increase multiblock_read_count, pay attention to your cpu number and max ora_pxxx number. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 7:09 AM DB Cache doesn't help you. IO waits mean that oracle processes are waiting to complete I/O requests. The I/O waits usually come with arguments like P1TEXT,P1,P2TEXT,P2,P3TEXT and P3 which can help you in locating file and block that the oracle processes are waiting for. Move those files and blocks to separate I/O devices, so that I/O can be processed in parallel. When creating so big indexes you need not only CPU power, you need I/O throughput. Paging is to be expected in order for oracle processes to allocate that big sort_area_size. Try tuning you memory and disk drives by using sar, iostat, vmstat, top and/or glance (Glance is the best monitor I've seen so far). You may need to move your temporary tablespace to alternative location. Also, be sure to fix you OS paging/swapping parameters. If possible, use direct IO to prevent double buffering and if not, limit OS buffer cache to a very small portion of memory. Paging and caching is usually a consequence of dynamic buffer cache. Parameter name should be something like NBUF. On 2003.07.08 17:29, Gurelei wrote: Hi. I'm trying to tune an index build. The table currently has about 65mil rows and I'm building a unique index, which takes about 55min to finish. The table size is about 3.4G, index is about the same size. I have tried different degrees of parallelism (up to 32), nologging is set in the create index script as well as on the tablespace. I noticed a lot of i/o waits during the buid and a lot of paging to and from filesystem, the paging area however appears to be unused. when I do lsps -a, it only shows 1% usage. What should be my next move? What should I look at? i have increased db_cache to 800M, sort area to 50M thanks Gene __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tech meetings
organization has tech meetings, and what is discussed and what the goals of the meetings are? I've been asked about this, and was wondering if there is a quick list out there any where. TIA __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OEM tools
Tools - Tuning Pack - Tablespace Map - Kirti --- Gabriel Aragon [EMAIL PROTECTED] wrote: Hi, I'm using the OEM 92010 with oracle 92 when I look for the option SHOW TABLESPACE MAP I can't find it, I remember using the OEM with 806 oracle DB and that option was available choosing STORAGE-TABLESPACES-tb_name and then click right button, in the database's tree. What happened with the TS Map? Is it a problem with the OEM version or am I missing something? Any Ideas? TIA Gabriel __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: import from 8i to 9i /IMP-00003:ORA-02298
Are your sure that the parent table data is getting imported fully? Number of exported rows match the number of imported rows? During your subsequent import attempts, are you truncating the table? - Kirti --- [EMAIL PROTECTED] wrote: I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Hi Kirti, As I have mentioned, my data is clean. I could import this data into an existing 8i database. Do you still think that importing data in two passes would fix it? Anything changed form 8i to 9i regarding iport process? I tried catching the invalid data into excetions and did cross checking and observed that I have valid data. I mean to say I could not enable those failed constarints manually as well. Surprisingly my STATUS column in USER_CONSTARINTS tables reads 'ENABLED' for those failed constarints also. Am I missing something? Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 12:44 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] I don't think this is related to NLS stuff... 02298, 0,cannot validate (%s.%s) - parent keys not found // *Cause: an alter table validating constraint failed because the table has // orphaned child records. // *Action: Obvious I suggest cleaning up target database (drop target tables, indexes, etc). Re-Create all target tables (empty). Do not enable constraints (disable them if already enabled). Re-import with ignore=y, and then enable constraints. Oracle imports tables in table_name order for the schema. Enabled RI constraints can pose a problem. - Kirti --- [EMAIL PROTECTED] wrote: Hello All, We are working on migrating 8i databases to 9i. As Sun boxes are not available yet, I have decided to do some experiments on my PC. I am trying to import into 9i database using export dump file of 8i database. I have my 8i database created in US7ASCII and I have created 9i database also in US7ASCII with UTF8, just to make sure that I won't hit any conversion issues. When I tried to import the data into this newly created database it is giving me some strange ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found errors. I have verified that the data in 8i database is valid. I have successfully imported into an existing 8i database with no problem. I am wondering if the Character set conversion the import utility is doing making any difference. The following are the messages I am getting when I invoked import: == import done in WE8MSWIN1252 character set and UTF8 NCHAR character set import server uses US7ASCII character set (possible charset conversion) export client uses US7ASCII character set (possible charset conversion) export server uses US7ASCII NCHAR character set (possible ncharset conversion) == I have gone through the globalization support guide and concluded that I should not worry about charctersets if I am not using any special datatypes or NCHAR datatypes. Please advise me how to resolve this issue. Is character set any issue at all? Do I have to influence the import utility to use the characterset of my interest? I don't want to do this as I will have to do this each time I import. I am wondering if any bug is associated with this. Thanks for your help. Surendra -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: import from 8i to 9i /IMP-00003:ORA-02298
I don't think this is related to NLS stuff... 02298, 0,cannot validate (%s.%s) - parent keys not found // *Cause: an alter table validating constraint failed because the table has // orphaned child records. // *Action: Obvious I suggest cleaning up target database (drop target tables, indexes, etc). Re-Create all target tables (empty). Do not enable constraints (disable them if already enabled). Re-import with ignore=y, and then enable constraints. Oracle imports tables in table_name order for the schema. Enabled RI constraints can pose a problem. - Kirti --- [EMAIL PROTECTED] wrote: Hello All, We are working on migrating 8i databases to 9i. As Sun boxes are not available yet, I have decided to do some experiments on my PC. I am trying to import into 9i database using export dump file of 8i database. I have my 8i database created in US7ASCII and I have created 9i database also in US7ASCII with UTF8, just to make sure that I won't hit any conversion issues. When I tried to import the data into this newly created database it is giving me some strange ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found errors. I have verified that the data in 8i database is valid. I have successfully imported into an existing 8i database with no problem. I am wondering if the Character set conversion the import utility is doing making any difference. The following are the messages I am getting when I invoked import: == import done in WE8MSWIN1252 character set and UTF8 NCHAR character set import server uses US7ASCII character set (possible charset conversion) export client uses US7ASCII character set (possible charset conversion) export server uses US7ASCII NCHAR character set (possible ncharset conversion) == I have gone through the globalization support guide and concluded that I should not worry about charctersets if I am not using any special datatypes or NCHAR datatypes. Please advise me how to resolve this issue. Is character set any issue at all? Do I have to influence the import utility to use the characterset of my interest? I don't want to do this as I will have to do this each time I import. I am wondering if any bug is associated with this. Thanks for your help. Surendra -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Windows server max file size
Ferenc, From what (little) I know of Win2K, the file size is only limited by the Volume Size (max vol size is 2TB). However, NTFS5 allows Disk Quotas. You may be running into quota limitations. Check under Properties dialog box for the Volume in question. There is a Quota tab and then.. happy drilling... ;) Cheers! - Kirti --- Ferenc Mantfeld [EMAIL PROTECTED] wrote: Hi All We have a process that is trying to create a huge flat file for a marketing export on a windows 2000 server host, and the file size seems to be constraining us. If I put a constraint (where clause) on the result set, it generates fine, but without it, I think I am exceeding some maximum file size. This is NTFS5, and the only thing I have found on the Microsquish web site knowledge base (after 2 hours of digging) is that the NFTS volumes are created with a default cluster size of 4096 bytes. I am not sure if this places a limit on the maximum file size and what other repercussions there are to this. Am I an the right track ? Anyone care to enlighten me to the wondrous (sic) workings of Microsquish Windows 2000 server ? Thanks. Ferenc Mantfeld Dreaming costs you nothing. Not dreaming costs you everything. __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Should percent increase higher than 0 in 817?
Yes, it does. (Sorry, Dan :). From OEM I see it set to 32765. OEM (9i R2) is still a bit dumb when it comes to undo segments, as it allowed me to *attempt* to change Rollback Segment _SYSSMU1$ to alter its maxextents. I received an error, of course, and alert log contained following: Fri Jul 4 11:15:48 2003 /* OracleOEM */ ALTER ROLLBACK SEGMENT _SYSSMU1$ STORAGE ( MAXEXTENTS UNLIMITED) Fri Jul 4 11:15:48 2003 ORA-30019 signalled during: /* OracleOEM */ ALTER ROLLBACK SEGMENT _SYSSMU1$... In OEM, I got to the Edit window for Rollback Segments via Tablespace - Dependencies route ;) - Kirti --- [EMAIL PROTECTED] wrote: Don't know. I bet Dan Fink does. He's in the air right now on his way to Portland, so I guess he won't be answering this. I can ask him at lunch. :) Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/02/2003 03:50 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: RE: Should percent increase higher than 0 in 817? does the 32k limit hold in 9i with undo tablespaces? - Original Message - To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 5:31 PM Due to bugs and limitations in the internal structure of rollback segments. ie. they aren't allowed more than 32k extents. See MetaLink note 50380.1 Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/02/2003 01:55 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: RE: Should percent increase higher than 0 in 817? i read this somewhere. why is it a bad idea to use maxextents unlimited in your rollback tablespaces? From: Goulet, Dick [EMAIL PROTECTED] Date: 2003/07/02 Wed PM 04:40:45 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Should percent increase higher than 0 in 817? Guang, In temp I really don't watch anything. In Rollback I watch for segments that are approaching maxextents and just plain running out of space. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, July 02, 2003 3:51 PM To: Multiple recipients of list ORACLE-L What are the things that we should be monitoring in ROLLBACK or TEMP tablespace? So far I don't have any script to monitor ROLLBACK or TEMP (havn't had any problem though). I too thought Oracle would take care of rollback and temp ts space management. Right or wrong? Guang snip __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle DBA 9i Books ever needed.
Not a problem, Cary :) I was pleasently surprised to see Chris Lawson's new book at Borders. Did a fair amount of reading (3 chapters), while waiting for Harry Potter's book to go on sale by 12:01 AM on June 21! (also, wondered what would happen if Hogwarts taught Oracle Tuning using wands spells ;) Chris has indeed done a very nice job. I should have bought the book that night. It's now on order. - Kirti --- Cary Millsap [EMAIL PROTECTED] wrote: I'll second the recommendation of Christopher Lawson's new book. Very nice. grinBy not mentioning Gaja and Kirti's work explicitly in the first sentence should not be considered a withholding of endorsement. My opinion of their work is--hey!--right on the cover./grin Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Tuesday, July 01, 2003 11:00 AM To: Multiple recipients of list ORACLE-L Rajuveera Are you seeking a book on DBA (administration) or performance tuning or real time database design? The more specific the question, the more likely the answer will match your needs. If you have some books, don't be shy about mentioning them so you don't just get recommendations that include books you already have. Since most of us collect books, if you say what you need that isn't included in your existing books, we can recommend books that cover different material. For performance tuning, a great book to start with is: Oracle 101 Performance Tuning by Jaga Krishna Vaidyanatha, Kirtikumar Deshpande, and John Kostelac. I have also been impressed with: The Art and Science of Oracle Performance Tuning by Christopher Lawson. This is very new, just published. Both of these books emphasize the fact that successful performance tuning begins with a correct philosophy. With a mistaken philosophy you can spend a lot of time and may even think you are being successful. There are two aspects to performance tuning, tuning the system and tuning your SQL. Which is more important will vary by your situation and as a competent DBA you need to understand both. For example, some packaged applications do not allow you to tune the SQL at all. But often tuning SQL statements can yield great gains in performance. As to the other topics you mention, perhaps it would be best if you reply with more specifics as to your situation. Sitting here in the U.S., it is rather difficult for me to tell what books are available to you in Hyderabad. Can you buy books from www.amazon.com? Getting a book that applies to both 8i and 9i is easy, in my experience. When a new Oracle version arrives, publishers often brush up an existing book with some new information so they can place the new version number on the book. It takes awhile for authors to absorb the impact that the changes can have on the daily work of a DBA and produce a book that incorporates these changes. I'm just trying to accurately represent the situation, not being critical. Fortunately when Oracle creates a new version, 99% of the features already there still work. Just familiarize yourself with the main feature differences between versions and you'll be fine with any book. I will be on vacation for several days, so if you are sleeping when this reply is posted, I will be unable to reply. However, there are others on this list that can provide far more competent assistance than I, even including some book authors. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 3:40 AM To: Multiple recipients of list ORACLE-L Dear Friends, I am willing to buy Oracle DBA 9i books. ,I need your suggestion on this. I got information friends from this list that the book (Practical Oracle 8i - Jonathan Lewis.) is practically good. Even I am also looking for the same kind of book, But I want it for both 8i and 9i. It should be more practical oriented , performance tuning and real time database design and problems. The book should be more practical oriented . What abt performance tuning 101 techniques , Oracle 9i DBA handbook. What are the best books for Oracle DBA (Intermediate level ) available in INDIA , HYDERABAD. Any URL / best sites also will be helpful. Thanks Rajuveera __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Should percent increase higher than 0 in 817?
After we rolled out our own scripts to monitor TS usage (de-Installing BMC Patrol) following is a line from a Very Senior DBA's email sent to us (not-so-senior-DBA-team-members) yesterday: I don't think the script should monitor ROLLBACK or TEMP tablespace for space issues - these normally resolve themselves (I will surely get in trouble when my co-workers see this post, but what the heck.. We will get outsourced soon anyway);) BTW, the VSDBA supports 8.1.7.4 databases. - Kirti --- Goulet, Dick [EMAIL PROTECTED] wrote: That's OK, I know a couple who never heard of optimal and/or organization index either. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 01, 2003 7:14 PM To: Multiple recipients of list ORACLE-L I saw a Sr. DBA few years ago, who claimed he had worked with Oracle since V4.0, and he didn't have a clue what PCTINCREASE is... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:51 PM What about ones that you don't know? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 4:11 PM To: Multiple recipients of list ORACLE-L PCTINCREASE is a bad good thing. Every DBA that I know of hunts down objects with a pctincrease other than 0 and resets them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Redo Copy Latch contention ??
According that suggestion you do seem to have redo copy latch contention.
As far as getting that ratio close to suggested value, you may set some special
init.ora
parameters. There is plenty of notes on Metalink for that.
But, you should first determine if this is causing any performance issue. Have you
explored all
other avenues to address those issues. If not, I would not worry about this
contention.
- Kirti
--- Reddy, Madhusudana [EMAIL PROTECTED] wrote:
Hello ALL,
Do you guys think we have redo copy latch contention ?? Also what are your
suggestions on tuning
Redo Copy Latch ??
SUBSTR(LN.NAME,1,20)GETS
MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
--
-- --
redo allocation
943350646 8862115 0 0
redo copy
22097 497 907958724 1592481
14:54:54 SQL select (497/22097)*100 from dual;
(497/22097)*100 ~ (misses/gets)*100
---
2.2491741 --- Oracle suggests it
should be under 1%
Madhu Reddy
X13944
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Should percent increase higher than 0 in 817?
Thanks for all the replies (to the list and some private..) All that was done by the script was to send an e-mail to the VSDBA, when TS usage crossed some percentage, set by the VSDBA for those databases (they wanted almost everything that Patrol did). The only difference now is that the scripts are run via cron, at a frequency set by the VSDBA. The scripts do not have 'Patrol Intelligence' to send e-mails only once after detecting some TS crossed the threshold. THAT is the real rub. They were used to ignore such notification from Patrol. The script sends them e-mails until they fix the problem. Auto extension of datafiles was not the solution in the VSDBA's mind. My suggestion to set datafiles to use auto extension was rejected (for whatever reasons). I am still waiting to hear how rollback/temp space issues in VSDBAs resolve automagically, other than after the jobs got terminated... - Kirti --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: After we rolled out our own scripts to monitor TS usage (de-Installing BMC Patrol) following is a line from a Very Senior DBA's email sent to us (not-so-senior-DBA-team-members) yesterday: I don't think the script should monitor ROLLBACK or TEMP tablespace for space issues - these normally resolve themselves (I will surely get in trouble when my co-workers see this post, but what the heck.. We will get outsourced soon anyway);) BTW, the VSDBA supports 8.1.7.4 databases. - Kirti --- Goulet, Dick [EMAIL PROTECTED] wrote: That's OK, I know a couple who never heard of optimal and/or organization index either. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 01, 2003 7:14 PM To: Multiple recipients of list ORACLE-L I saw a Sr. DBA few years ago, who claimed he had worked with Oracle since V4.0, and he didn't have a clue what PCTINCREASE is... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:51 PM What about ones that you don't know? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 4:11 PM To: Multiple recipients of list ORACLE-L PCTINCREASE is a bad good thing. Every DBA that I know of hunts down objects with a pctincrease other than 0 and resets them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] __ __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Dedicated Server problem ORA-03113
ORA-3113 error is not very easy to track down. It's like ORA-600, but relates to issues in N/W communication with Oracle datbases. There are a number of notes/articles on Metalink discussing this error. Hopefully some of that information helps you. - Kirti --- Denham Eva [EMAIL PROTECTED] wrote: That is the whole problem, there is no logged errors, or trace files. Everyone can connect via the MTS servers, no problem, however no one can connect via dedicated server. Go figure. Regards Denham -Original Message- Sent: Saturday, June 28, 2003 6:54 PM To: Multiple recipients of list ORACLE-L Hi! What does your alert log say? Or event log then? Maybe, for some reason, Oracle is unable to start new processes on your system. For MTS connection, no new processes are needed, but for dedicated, there are. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, June 28, 2003 12:24 PM Hello Gurus, I am at wits end. The enviroment is Windows 2000 SP3. Oracle 817, MTS is also set up. The server has been up for 32 days. Everything fine and then suddenly all the power users using dedicated server because of their resource intensive sql etc just cann't connect. We get this error: ORA-03113: end-of-file on communication channel I have tried reloading, stopping and starting the Listener, but to no avail. There are only two dedicated sessions connected to the server and it still refuses any connections, and I have seen up to nine dedicated server connections before. Anyone experience this before? Regards Denham Eva Oracle DBA __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log for transaction
Oracle does not have any ready-made logging process to assist you. Some of our developers have utilized 'utl_file' to track the progress of their long running jobs. Entries are made to the job log file at proper intervals to tell them what happened in case the job aborted. - Kirti --- [EMAIL PROTECTED] wrote: Hi there, I got a question on log. If my program crashes, can I check some log to see what recent transaction is? It will give me a big help on trouble shooting. Thanks, Jin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=12
Better call Oracle support for this one. These are references to some internal deadlocks and enqueue problems not reported in the v$ views. I think Steve Adams discusses this in his Oracle8i Internal Services book. Search his web site, you may find some more information (www.ixora.com.au). - Kirti --- Basavaraja, Ravindra [EMAIL PROTECTED] wrote: I am seeing WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=12 in the alert log file? there are no blocking locks and at this time the weblogic connection pool cannot get connections to the database but the client connections from sql*client gets connected (sqlplus or toad). What could be the problem?any thoughts? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Inserting blanks from a PROCOBOL program
Can't he use a working storage variable that is defined with value space? Else try inserting ASCII value for a blank. - Kirti --- Schauss, Peter [EMAIL PROTECTED] wrote: I have a programmer who wants to insert blanks from a PIC X host variable into a not null CHAR column from a COBOL program. The generated code insists on converting his blanks to a null which is, of course, rejected since the column is defined as not null. Is there a way of forcing the blanks to go into the field? Oracle version is 8.1.7 on AIX 4.3 Procobol 1.8.52.0.0 Thanks, Peter Schauss -- __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: copy a datafile to a raw device
What platform is this on? On some UNIX platforms the file header for the raw device may vary in size. (It has been a while since I dealt with raw devices). You may have to use seek= option (dd command) to skip the header bytes before actually writing the data using 'dd'. Or just call Oracle Support. - Kirti --- Stephen Lee [EMAIL PROTECTED] wrote: Well heck. Nobody else has replied, so I might take a stab at it here. I suspect that the format of the data (bits and bytes) required on a raw device is different than the format of the data in a file in a file system. I'm stretching a bit here, but I think an analogy would be what would happen if you dd a file from UFS to NTFS. The data on the NTFS might be a digital copy of what was on UFS, but it's format is totally useless in the context of NTFS. -Original Message- From: Gurelei [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 1:55 PM To: Multiple recipients of list ORACLE-L Subject: copy a datafile to a raw device hi all: I need to move a SYSTEM datafile from a filesystem to a raw device. I have shutdown the database, copied the file via dd if=system of=/dev/raw renamed the file in the database, but couldn't open the database because of the error: Unknown File Header Version read for file number 1. I tried another dd: dd if=system of=/dev/raw obs=32768 (32K is the db block size), but I got the same error. Can anyone see what else could be wrong with my dd command thanks Gene __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OEM question
You got it.. I use OEM in the stand-alone mode only, without the repository. However, not all functionality is available in this mode. But it will get you going to taste OEM ;) - Kirti --- Koivu, Lisa [EMAIL PROTECTED] wrote: - OEM question Hello all, I'm configuring OEM for the first time. I see it needs a repository to operate. So for the db-up functionality, if the database that contains your repository goes down, your db-up function is dead, right? Sounds like the rman catalog quandry. YOu have a catalog to backup your database, but then you have to backup your catalog... And just as a fyi: I completed the upgrade from 8.1.7.4-9.2.0.3 on Windows 2000 without incident last night. It actually went pretty quickly. Thanks in advance for any comments about OEM. Lisa Koivu Oracle Database Stressmonkey Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 Office: 954-935-4117 Fax:954-935-3639 Cell:954-683-4459 __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-01555 with Automatic Undo Management mode
What's in the alert log file for this error? Does it report any undo segments getting off-lined? - Kirti --- Thomas Day [EMAIL PROTECTED] wrote: Sorry, I'm the only user with access to this database and this was the only job running. Very frustrating two days of reading manuals and metalink has not improved my warm and fuzzy about using UNDO. Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100 cc: @yahoo.com Subject: Re: ORA-01555 with Automatic Undo Management mode Sent by: ml-errors 06/18/2003 12:14 PM Please respond to ORACLE-L I'm sure Dan Fink will have more (and better) information on this, as he did a presentation on AUM at IOUG. But we did do a demonstration of how you can get an ORA-1555 even with AUM. It has nothing to do with trying to create another extent or how much room is left in the tablespace but has much to do with the fact that other transactions are running in the database at the same time. Undo_retention (if I remember Dan's presentation correctly) is a wish -- Oracle can and will overwrite the undo segment extent even if the retention time is not past if space is needed. Dan -- did I get it right? Rachel --- Thomas Day [EMAIL PROTECTED] wrote: I'm trying to create a materialized view on a 7G table. I'm using UNDO and undo_retention=108000, which if I understand correctly is 30 hours. I have 7G in UNDOTBS1 --- I shouldn't need that much. After 7 hrs I get ORA-01555: snapshot too old: rollback segment number with name too small The FM says: ORA-01555 snapshot too old: rollback segment number string with name string too small Cause: Rollback records needed by a reader for consistent read are overwritten by other writers. Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments. UNDOTBS1 still had 3G of space free with about 88 active undo extents, the largest was 4,154,458,112 bytes. Was it trying to create another 4G extent? Is there something I am missing? This Automatic stuff doesn't seem to be so automatic. I can create this using rollback segments but I wanted to use UNDO because it allows past point in time queries. Oracle 9.2.0.1.0 Win2K. Any thoughts (besides get a real operating system and use rollback segments) would be greatly appreciated. __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
Re: Partitions of table read only
Rachel, You forgot to kill all other active transactions... ;( - Kirti --- Rachel Carmichael [EMAIL PROTECTED] wrote: okay, am I missing something? I created an LMT. Created a table in it. Gave no one quota on the tablespace. did (both as system and sysdba) alter tablespace test_drop read only; and hung what did I forget to do? --- Rachel Carmichael [EMAIL PROTECTED] wrote: that actually makes sense when you think about it, with one question -- was the tablespace a dictionary-managed one or an LMT? If it was dictionary-managed, it makes perfect sense. The metadata about the table and the extents used in the tablespace are not stored IN that tablespace, so drop table would affect only the system tablespace and the data dictionary. But in an LMT, the bitmap of extent usage is stored within the tablespace itself, so I wonder if you could actually drop a table from a read-only lmt. Off to experiment.. Rachel --- Darrell Landrum [EMAIL PROTECTED] wrote: I haven't tested this but would imagine it entirely possible. What I wanted to throw out though, is somewhat of a related caution. You can drop a table from a read only tablespace. I discovered this in test, fortunately when I was finished testing with that table and intentionally dropped it while the tablespace was in read only mode. [EMAIL PROTECTED] 06/18/03 09:49AM Hi, I would like to know if it is possible and what the pitfalls are if I do the following. Partition a large table into partitions based on date. Data is only entered and read and never altered, so I would like to move older partitions to read only tablespaces and possible read only devices so the backup will be made quicker. Is it possible to have parttions of the same table spread across read only and read/write tablespaces? Am I correct in assuming that once you backup a read only tablespace there is no need to backup the same again. (provided you don't make it read/write add data and make it read only again).? Does anybody have a procedure already that automatically creates the new partitions let say every month? TIA Jacob A. van Zanen Oracle DBA Quant Systems Europe b.v. Tel : +31 (0) 251 - 268 268 Mobile: +31 (0) 6 51308813 Fax: +31 (0) 251 - 268 269 E-mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Visit our web site at http://www.quantsystems.nl/ http://www.quantsystems.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Partitions of table read only
Unfortunately, in the *database* . - Kirti --- Rachel Carmichael [EMAIL PROTECTED] wrote: all other active transactions in the database? or against that table? if in the database, it will have to wait, this is a testing database and work is going on in it. if against that table, no one else knows anything about that table. As far as any other user in the database knows, it doesn't exist. Nor does the tablespace --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: Rachel, You forgot to kill all other active transactions... ;( - Kirti __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-01555 with Automatic Undo Management mode
of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Partitions of table read only
Will that be all DML activity? All the times? In a datawarehouse? In our data marts most activity is for just 'reading' stuff a lot of stuff locally... Not many active transactions.. So I can make TSs read only almost any time I want to.. - Kirti --- Rachel Carmichael [EMAIL PROTECTED] wrote: ARGH! Well, that pretty much kills the idea of using this for my data warehouse as there is always activity in it. Dang! Okay, I'll try it from my laptop as I can control users there :) --- Daniel Fink [EMAIL PROTECTED] wrote: Rachel, It is not active transactions against that tablespace, it is active transactions. Yup, period! As soon as all the active transactions complete, the tablespace will complete altering itself. Dan Rachel Carmichael wrote: there WERE no active transactions against that tablespace. The steps I took were: as system: 1) create tablespace as an LMT 2) create table within that tablespace 3) attempt to make the tablespace read-only when that hung I logged out (which certainly killed any active transactions against that tablespace!) 4) log back in as / as sysdba 5) attempt to make that tablespace read-only No one else knows about that tablespace, it's brand-new. No one else has quota or access on the table I created. However, for completeness, I just offlined and onlined that tablespace, then tried to make it read only. It's still hanging. Oh yeah, 9.2.0.1 on Linux Rachel __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: HP-Sun Cross Platform Migration - Exp/Imp, CTAS over dblink or ..
You may want to review an article, Cross Platform Migration of a Large Production Database, in the latest issue(2nd Qtr 2003) of SELECT journal (by IOUG). The author details the procedure he followed to migrtate a 300GB production database from HP-UX to IBM AIX. - Kirti --- Goulet, Dick [EMAIL PROTECTED] wrote: Exp/imp of a 200GB database is possible, but I'd think the time required would be the long pole in the tent. I'd say your looking at at least a 4 day weekend at best and only if you used direct mode. Someone has hinted that you can simply move the datafiles from one box to the other. Well I'd not loose the original system before you prove that. My experience with database file from Solaris to HP-UX has been a 100% loss of data. Granted that was on a much earlier version of Oracle (6.0.x). Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Saturday, June 07, 2003 7:29 AM To: Multiple recipients of list ORACLE-L .. Hi all: We are considering migration of an Oracle eBusiness Suite 11.5.8 from HP-UX 11.0 to Solaris9. I'd like to know if anyone has done this and how daunting the task is. Are there any 3rd Party tools which can help out? Coming to the conventional approaches: 1. What do you think of Export/Import of a 200 GB database? 1a. How much time will it take? 1b. Any strategies for cutting that down? 2. How does CTAS over dblink compare to Export/Import? 3. Is there any tool that converts Oracle datafiles on HP-UX 11.0 to Oracle datafiles on Solaris 9. If so we would just need to recreate the control files on the target database and we are done. Any suggestions, pointers, words of wisdom are greatly appreciated... Thanks Regards, Sashi -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sashidhar Kondareddy INET: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: count(*) vs count(0)
Vivek, Your same question on LazyDBA list hasbeen answered correctly. :) - Kirti VIVEK_SHARMA [EMAIL PROTECTED] wrote: Is there a performance equality between COUNT(*) COUNT(0) ?Is it same , indpendent of the Oracle Version ?Forgive the repeat raising , as this issue seems to have come before .Thanks-- Do you Yahoo!? Free online calendar with sync to Outlook(TM).
Re: A new form of ORA-1555
A classic case when AUM does not help prevent 1555 errors ! Query duration is MAXQUERYLENreported inv$undostat view. SCN could be the 'as of SCN' when the query started (not sure, as I could never get my small tests to failwith 1555 when using AUM). What is also interesting is the SMON activity of offlining undo segments. That very well could be the reason for this error. In this case adjusting UNDO_RETENTION MAXQUERYLEN is one option. The other being, correcting this long running query. BTW.. Raj I exchanged e-mails earlier regarding this, as I just wanted to confirm that he was using Auto Undo Dmgt feature ;) He informed me that v$undostat view did report a non-zero count in the SSOLDERRCNT column as expected... so that column works okay. Cheers! - Kirti "Jamadagni, Rajendra" [EMAIL PROTECTED] wrote: A sighting in alert log ... SMON offlining US=102 SMON offlining US=104 Fri Jun 6 08:42:06 2003 ORA-01555 caused by SQL statement below (Query Duration=41895 sec, SCN: 0x0010.c2bd0c24): Fri Jun 6 08:42:06 2003 SELECT ROUND(G_1/:"SYS_B_00") G_1, ROUND(G_2/:"SYS_B_01") G_2, ROUND(G_3/:"SYS_B_02") G_3, ROUND(G_4/:"SYS_B_03") G_4, R OUND(G_5/:"SYS_B_04") G_5, ROUND(A_1/:"SYS_B_05") A_1, ROUND(A_2/:"SYS_B_06") A_2, ROUND(A_3/:"SYS_B_07") A_3, ROUND(A_4 /:"SYS_B_08") A_4, ROUND(A_5/:"SYS_B_09") A_5, LOG_NO, AE, ADVR, AGNC, OFFICE, POB_ID, POB_CREATE_DT, POB_STAT From (SE LECT DECODE(YEAR,:"SYS_B_10",NVL((DECODE(QTR,:"SYS_B_11",NVL(CURR_GOAL,:"SYS_B_12"))),:"SYS_B_13"),:"SYS_B_14") G_1,DECO DE(YEAR,:"SYS_B_15",NVL((DECODE(QTR,:"SYS_B_16",NVL(CURR_GOAL,:"SYS_B_17"))),:"SYS_B_18"),:"SYS_B_19") G_2,DECODE(YEAR,: "SYS_B_20",NVL((DECODE(QTR,:"SYS_B_21",NVL(CURR_GOAL,:"SYS_B_22"))),:"SYS_B_23"),:"SYS_B_24") G_3,DECODE(YEAR,:"SYS_B_25 ",NVL((DECODE(QTR,:"SYS_B_26",NVL(CURR_GOAL,:"SYS_B_27"))),:"SYS_B_28"),:"SYS_B_29") G_4,DECODE(YEAR,:"SYS_B_30",NVL((DE CODE(QTR,:"SYS_B_31",NVL(CURR_GOAL,:"SYS_B_32"))),:"SYS_B_33"),:"SYS_B_34") G_5,DECODE(YEAR,:"SYS_B_35",NVL((DECODE(QTR, :"SYS_B_36",NVL(CURR_ACCRUAL,:"SYS_B_37"))),:"SYS_B_38"),:"SYS_B ~ But I havn't figured out why query duration and SCN is shown any ideas? ... what is the significance? Oracle 9202, RAC TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! * Do you Yahoo!? Free online calendar with sync to Outlook(TM).
Re: A new form of ORA-1555
Acutally, there nothing at the table level to 'enable' it for the FBQ (in 9i R1 and R2). In 9i R2, they introduced a 'flashback' privilege (object and System level). In 9i R1 one needed exec privilege on a package (dbms_flashback) to use FBQ. SMON offlined Undo Segs because those had been idled, had expired undo_retention period, and current number of concurrent transactions dropped. AUS still left online were due to either 1) they had active transactions or 2) unexpired extents or 3) SESSIONS parameter dectated... Dan, you are correct. After AUS gets created it gets dropped only when the Undo TS is dropped. (Also, switching to a new UNDO TS does not drop old AUS from old unto TS.) Whether it comes on-line or not is solely dependent on the transaction load. And that may create the possibility of getting 'Out of Space' error, in case, if I resized the UNDO TS datafiles to a smaller value ;) - Kirti --- Daniel W. Fink [EMAIL PROTECTED] wrote: You do not have to specifically enable a table for flashback query. In 9.2 (Kirti, please correct me if I am wrong), it is automatically enabled, even if you are not using AUM. I believe that this is a new error message format for 9. The solution that is commonly quoted is to increase your undo_retention to cover the amount of time needed to complete the query. However, this is not a guarantee that the query will actually work. If space is needed, unexpired extents will be reused. I think you may be hitting a situation similar to what Rachel encountered several weeks ago. It appears that a large number (at least 104) undo segments have been created. SMON is now offlining them in the hopes of reclaiming space. This brings up a situation posed recently and it was not a condition I had tested. If a large number of segments is created, are they ever dropped or do they continue to exist and consume space (at least 2 extents/128k) in the undo tablespace until it is dropped and a new one created? I THINK that the answer is that once created an undo segment will exist until the tablespace is dropped as there is no 'drop undo segment' command with aum. -- Daniel W. Fink http://www.optimaldba.com Ruth Gramolini wrote: It might have something to do with setting the table to do a Flashback query. Maybe Dan will know. Ruth - Original Message - From: Jamadagni, Rajendra mailto:[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 10:00 AM Subject: A new form of ORA-1555 A sighting in alert log ... SMON offlining US=102 SMON offlining US=104 Fri Jun 6 08:42:06 2003 ORA-01555 caused by SQL statement below (Query Duration=41895 sec, SCN: 0x0010.c2bd0c24): Fri Jun 6 08:42:06 2003 SELECT ROUND(G_1/:SYS_B_00) G_1, ROUND(G_2/:SYS_B_01) G_2, ROUND(G_3/:SYS_B_02) G_3, ROUND(G_4/:SYS_B_03) G_4, R OUND(G_5/:SYS_B_04) G_5, ROUND(A_1/:SYS_B_05) A_1, ROUND(A_2/:SYS_B_06) A_2, ROUND(A_3/:SYS_B_07) A_3, ROUND(A_4 /:SYS_B_08) A_4, ROUND(A_5/:SYS_B_09) A_5, LOG_NO, AE, ADVR, AGNC, OFFICE, POB_ID, POB_CREATE_DT, POB_STAT From (SE LECT DECODE(YEAR,:SYS_B_10,NVL((DECODE(QTR,:SYS_B_11,NVL(CURR_GOAL,:SYS_B_12))),:SYS_B_13),:SYS_B_14) G_1,DECO DE(YEAR,:SYS_B_15,NVL((DECODE(QTR,:SYS_B_16,NVL(CURR_GOAL,:SYS_B_17))),:SYS_B_18),:SYS_B_19) G_2,DECODE(YEAR,: SYS_B_20,NVL((DECODE(QTR,:SYS_B_21,NVL(CURR_GOAL,:SYS_B_22))),:SYS_B_23),:SYS_B_24) G_3,DECODE(YEAR,:SYS_B_25 ,NVL((DECODE(QTR,:SYS_B_26,NVL(CURR_GOAL,:SYS_B_27))),:SYS_B_28),:SYS_B_29) G_4,DECODE(YEAR,:SYS_B_30,NVL((DE CODE(QTR,:SYS_B_31,NVL(CURR_GOAL,:SYS_B_32))),:SYS_B_33),:SYS_B_34) G_5,DECODE(YEAR,:SYS_B_35,NVL((DECODE(QTR, :SYS_B_36,NVL(CURR_ACCRUAL,:SYS_B_37))),:SYS_B_38),:SYS_B ~ But I havn't figured out why query duration and SCN is shown any ideas? ... what is the significance? Oracle 9202, RAC TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
Re: A new form of ORA-1555
I am using AUM in our Test/Acceptance databases with no problems at all. This month it will be rolled out to a couple of production databases. FBQ is another matter altogether :) MUM (manual undo mgmt) was a depreacted option when 9i R1 came out. I won't be surprised if only AUM would be available from the next release (Oracle10i, OracleX or whatever they call it). We will know for sure in just couple more months ([EMAIL PROTECTED] is in Sept this year :) - Kirti --- Rachel Carmichael [EMAIL PROTECTED] wrote: we are using AUM pretty successfully for our production systems. Interestingly enough the one problem we did have was on a test box, and we couldn't repeat it --- Daniel W. Fink [EMAIL PROTECTED] wrote: Mladen, I could not agree more! I seriously pondered not posting this information at all. FBQ is a nice feature, but I would not depend upon it. I'm a conservative and somewhat paranoid DBA and I would not recommend AUM for production systems, though certain very knowledgable and respected members of this list disagree with me. We each have our reasons for our positions and we are both right. -- Daniel W. Fink http://www.optimaldba.com Gogala, Mladen wrote: Well, I've read a lot of that on this list (this is not the first time FBQ is being discussed) but as a conservative and somewhat paranoid DBA, I don't want to try anything that isn't supported with a very new feature like FBQ. The experience taught me a lesson about ora-600 and alike. I really wouldn't want to depend on a feature that is very, very new. I'd much rather use logminer instead. That's just me. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: buffer busy waits, unique index, p3=1?
Interesting.. What version and platform? I have never seen p3=1 for a buffer busy wait. Was the session just hung? Did you by chance monitored SEQ# from v$session_wait? - Kirti --- John Clarke [EMAIL PROTECTED] wrote: Today on a busy production system I saw dozens of sessions waiting on buffer busy waits events. p1 and p2 from v$session_wait were the same and mapped to one of our very large unique indexes on a large table. For all but one of the sessions, p3=130. Using Metalink and Steve Adams's website, it seems p3=130 means that the block is being read by another session and no other suitable block image was found, so we wait until the read is completed, a buffer cache deadlock, or the kernel can't get a buffer in a certain amount of time and assumes a deadlock. For the single, non-p3-130 session, p3=1. I killed the session where p3=1 (it shouldn't have been running anyway) and things went back to normal quickly. My question - what does it mean when p3=1 on buffer busy waits for an index? I must be looking in the wrong documentation, but I can't find the answer anywhere. Thanks. __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
