Ot: Budding dba
Hello, are there any list of 5-10 questions which we can 'generally' ask to judge the 'potential' of a person to be an Oracle dba. These questions may include questions on attitude also. Cyril PS: I am seriously looking at hiring some 6months to 1 year experienced Oracle apps dba for my organisation. So if someone knows anyone (!!!) please forward their cvs to [EMAIL PROTECTED] The positions are based in Bangalore, and of course I won't ask them the same 5-10 questions (!!) __ Great Travel Deals, Airfares, Hotels on http://r.rediff.com/r?www.journeymart.com/rediff/travel.asp&&sign&&jmart -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cyril Thankappan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Ot: Budding dba
Hello, are there any list of 5-10 questions which we can 'generally' ask to judge the 'potential' of a person to be an Oracle dba. These questions may include questions on attitude also. Cyril PS: I am seriously looking at hiring some 6months to 1 year experienced Oracle apps dba. So if someone knows anyone (!!!) please forward their cvs to [EMAIL PROTECTED] The positions are based in Bangalore, and of course I won't ask them the same 5-10 questions (!!) __ Great Travel Deals, Airfares, Hotels on http://r.rediff.com/r?www.journeymart.com/rediff/travel.asp&&sign&&jmart -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cyril Thankappan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 buffer space
AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003 4:49 PMTo: Multiple recipients of list ORACLE-LSubject: log buffer space I am finding tons of "log buffer space" waits in 10046 output . Does it necessarily means I should look for resizing log_buffer ? What else can be done or looked at to reduce these waits . Thanks, ak
Re: Is range partitioning possible on part of varchar2 column ???
Babu,
On a slightly different approach, is it possible to update the column to the
format MON, from the present MON? If so, then there is hope. You
could create the partitions like this
PARTITIONING BY RANGE (REPORT_CYCLE_CD)
(
PARTITION P1998 VALUES LESS THAN ('1999%'),
PARTITION P1999 VALUES LESS THAN ('2000%'),
PARTITION P2000 VALUES LESS THAN ('2001%'),
.
PARTITION PMAX VALUES LESS THAN (maxvalue)
)
Hope this helps.
Arup Nanda
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, March 13, 2003 5:19 PM
> Babu
> I don't think partitions are clearly documented anywhere. Here is some SQL
> that works so you can see how to use a date function. It partitions on two
> columns, but I wanted you to see something that works.
>
>add partition sum_fy_28
> values less than ('FY', to_date('02012003','mmdd'))
> tablespace data_fy_28
>
> -Original Message-
> Sent: Thursday, March 13, 2003 3:14 PM
> To: Multiple recipients of list ORACLE-L
> ??
>
>
> Dear List,
>
> I have a table of size approx 10gig, and I need to partition based on the
> YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
> data in the column of format "MON" . I need to partition the table
based
> on the year , that is, substr(report_cycle_cd, 4,4).
>
> Substr function doesn't seem to be permitted in the partitioning syntax
and
> so am getting errors. Only TO_DATE function seems to be permitted. Since
it
> is not a date column, I would like to know if there is a way to RANGE
> partition the table, instead of HASH partitioning.
>
> Appreciate any suggestions.
>
> Thanks,
> -- Babu
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Janardhana Babu Donga
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Arup Nanda
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: TXNCOUNT in V$UNDOSTAT (9i R2) [ Was -- RE: monitor
Title: RE: monitor transactions over time Make that bug #2506744. Sorry.. - Kirti -Original Message-From: Deshpande, Kirti Sent: Thursday, March 13, 2003 7:20 PMTo: '[EMAIL PROTECTED]'Subject: TXNCOUNT in V$UNDOSTAT (9i R2) [ Was -- RE: monitor transactions over time ] Today, Oracle Support updated my TAR, stating that there won't be a patch released to fix this bug (#2506774) in 9i R2. Suggested workaround is to derive TXNCOUNT by subtracting the numbers from the previous sample period. And when you write one, watch out for those -ve numbers for TXNCOUNT.. :-)) Somebody is watching this list.. seriously ;) Rajendra, you need to put your script on e-bay ;) Regards, - Kirti -Original Message-From: Deshpande, Kirti Sent: Friday, March 07, 2003 9:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: monitor transactions over time From what I know Oracle Development folks have identified the code changes to correct this problem. Just do not when Oracle would issue the patch. Since the bug was logged against 9i R2, patch would be provided. This bug was originally logged in Aug 2002. There was no follow up. The other issue with v$undostat view is that it does not work in Manual Undo Mode. Forget using it while in Manual Undo Management mode to monitor your undo usage to size undo tablespace accordingly. Forget what the documents, white papers say. Some of them are 'syntactically' correct in saying, "This view is available in Automatic and Manual Undo Management mode." Yes, that is true. The view is available in MUM mode. But, it returns one useless row in 9i R1 and nothing in 9i R2. I was told by Oracle Development that it did not work in 9i R1, in MUM mode, so they simply changed it to return nothing in 9i Rel 2. Hmmm... wonder if I followed this principle for some of the bugs in our Applications. ;) I will talk about this, and a few other things, in my Quick Tips Sessions, on AUM and FBQ, at the IOUG Conf next month. - Kirti -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Friday, March 07, 2003 4:44 PMTo: Multiple recipients of list ORACLE-LSubject: RE: monitor transactions over time I wrote a script to fix the problem in 9202, but don't tell Oracle ... we want them to fix the bug. as soon as they know there is a workaround, the priority on the bug will go down. Log a iTar and request a patch ... the bug# is 2506744 Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Ehresmann, David [mailto:[EMAIL PROTECTED]] Sent: Friday, March 07, 2003 5:04 PM To: Multiple recipients of list ORACLE-L Subject: monitor transactions over time List, Does anybody know a way to monitor the number of transactions occurring over time, say 5 minute or 10 minute intervals? I am looking at v$undostat and it appears to have a problem accumulating transactions under txncount when it should report over a 10 minute interval ( metalink doc# 260990.995, query v$undostat) BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT - - -- -- 05-MAR-03 05-MAR-03 38 161519 05-MAR-03 05-MAR-03 24 161468 05-MAR-03 05-MAR-03 1 161227 05-MAR-03 05-MAR-03 4 161075 05-MAR-03 05-MAR-03 71 160881 05-MAR-03 05-MAR-03 6932 160748 05-MAR-03 05-MAR-03 8 160073 05-MAR-03 05-MAR-03 14545 159887 05-MAR-03 05-MAR-03 19588 159010 05-MAR-03 05-MAR-03 2333 157084 05-MAR-03 05-MAR-03 6972 152649 the undo blocks appear correct, but transactions are accumulating. Does anybody know how to use v$transaction or another view to do this? This is 9iRel2 on Unix and the application is geared toward transaction processing. thanks, David Ehresmann
RE: why SAN ? why not external storage ?
Disks are cheap until one asks for them ;) - Kirti -Original Message- Sent: Thursday, March 13, 2003 5:25 PM To: Multiple recipients of list ORACLE-L There are many things I don't get in this life. One of them is the statements about disk storage being an admin nightmare and way too expensive. Aren't disks very cheap these days?! Mogens [EMAIL PROTECTED] wrote: >Rahul, > >This is personal opinion, but it looks to me like your concerned about the >database your creating for the client and may not have the total or corporate >wide view your client has. We're heading down the SAN road not because of any >specific database requirements but because disk storage has become an >administrative nightmare as well as way too expensive. > >Dick Goulet > >Reply Separator >Author: "Arun Annamalai" <[EMAIL PROTECTED]> >Date: 3/13/2003 12:24 PM > >Usaually SAN and NAS is used for several good reasons...the two main are... >1) High availability - When you have your database files on SAN/NAS then you can >bring ur database on another server when the primary goes down. Obviously you >have to use a cluster or Big IP (F5) on the front. >2) reduce redundancy -A unix userid with home directory attached to a paticular >NFS drive on NAS/SAN, will able to see all his files when he logs into other >servers. > >so far I heard "Net App" is low cost including with Raid 5. > >-Arun. >Sr oracle dba > - Original Message - > From: Rahul > To: Multiple recipients of list ORACLE-L > Sent: Wednesday, March 12, 2003 9:38 PM > Subject: Re: why SAN ? why not external storage ? > > > my reasons to recommend an external storage was.. > 1) the database size is 36GB, and according to many documents i have read, SAN >is not cost effevtive unless populated > by a large numbers of drives !!, now for the client the cost is not the >factor.. given the situation.. wouldnt a SAN be an overkill ? > > 2) NO DBA or SYS ADMIN skills to manage the SAN !! > >- Original Message - >From: Tim Gorman >To: Multiple recipients of list ORACLE-L >Sent: Wednesday, March 12, 2003 8:33 PM >Subject: Re: why SAN ? why not external storage ? > > >Can you share some of the reasons related to your decision in choosing a >direct-attach storage (DAS) instead of a SAN? In general, a SAN is a much >smarter choice than DAS. > - Original Message - > From: Rahul > To: Multiple recipients of list ORACLE-L > Sent: Wednesday, March 12, 2003 1:33 AM > Subject: why SAN ? why not external storage ? > > > list, one of our clietns are going to by SAN, the current oracle databases >take around > 36GB of storage i dnt understand there reason to go for SAN, i >sugguested to buy an external storage > box instead. How can i justify my desicion ? (cost of not the factor) > > TIA > rahul > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
TXNCOUNT in V$UNDOSTAT (9i R2) [ Was -- RE: monitor
Title: RE: monitor transactions over time Today, Oracle Support updated my TAR, stating that there won't be a patch released to fix this bug (#2506774) in 9i R2. Suggested workaround is to derive TXNCOUNT by subtracting the numbers from the previous sample period. And when you write one, watch out for those -ve numbers for TXNCOUNT.. :-)) Somebody is watching this list.. seriously ;) Rajendra, you need to put your script on e-bay ;) Regards, - Kirti -Original Message-From: Deshpande, Kirti Sent: Friday, March 07, 2003 9:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: monitor transactions over time From what I know Oracle Development folks have identified the code changes to correct this problem. Just do not when Oracle would issue the patch. Since the bug was logged against 9i R2, patch would be provided. This bug was originally logged in Aug 2002. There was no follow up. The other issue with v$undostat view is that it does not work in Manual Undo Mode. Forget using it while in Manual Undo Management mode to monitor your undo usage to size undo tablespace accordingly. Forget what the documents, white papers say. Some of them are 'syntactically' correct in saying, "This view is available in Automatic and Manual Undo Management mode." Yes, that is true. The view is available in MUM mode. But, it returns one useless row in 9i R1 and nothing in 9i R2. I was told by Oracle Development that it did not work in 9i R1, in MUM mode, so they simply changed it to return nothing in 9i Rel 2. Hmmm... wonder if I followed this principle for some of the bugs in our Applications. ;) I will talk about this, and a few other things, in my Quick Tips Sessions, on AUM and FBQ, at the IOUG Conf next month. - Kirti -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Friday, March 07, 2003 4:44 PMTo: Multiple recipients of list ORACLE-LSubject: RE: monitor transactions over time I wrote a script to fix the problem in 9202, but don't tell Oracle ... we want them to fix the bug. as soon as they know there is a workaround, the priority on the bug will go down. Log a iTar and request a patch ... the bug# is 2506744 Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Ehresmann, David [mailto:[EMAIL PROTECTED]] Sent: Friday, March 07, 2003 5:04 PM To: Multiple recipients of list ORACLE-L Subject: monitor transactions over time List, Does anybody know a way to monitor the number of transactions occurring over time, say 5 minute or 10 minute intervals? I am looking at v$undostat and it appears to have a problem accumulating transactions under txncount when it should report over a 10 minute interval ( metalink doc# 260990.995, query v$undostat) BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT - - -- -- 05-MAR-03 05-MAR-03 38 161519 05-MAR-03 05-MAR-03 24 161468 05-MAR-03 05-MAR-03 1 161227 05-MAR-03 05-MAR-03 4 161075 05-MAR-03 05-MAR-03 71 160881 05-MAR-03 05-MAR-03 6932 160748 05-MAR-03 05-MAR-03 8 160073 05-MAR-03 05-MAR-03 14545 159887 05-MAR-03 05-MAR-03 19588 159010 05-MAR-03 05-MAR-03 2333 157084 05-MAR-03 05-MAR-03 6972 152649 the undo blocks appear correct, but transactions are accumulating. Does anybody know how to use v$transaction or another view to do this? This is 9iRel2 on Unix and the application is geared toward transaction processing. thanks, David Ehresmann
RE: why SAN ? why not external storage ?
-Original Message- Sent: Thursday, March 13, 2003 5:25 PM To: Multiple recipients of list ORACLE-L There are many things I don't get in this life. One of them is the statements about disk storage being an admin nightmare and way too expensive. Aren't disks very cheap these days?! Mogens "Disks" are cheap. Reliable storage isn't, not really, not for large organizations. brian -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Brian Dunbar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log buffer space
Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003 4:49 PMTo: Multiple recipients of list ORACLE-LSubject: log buffer space I am finding tons of "log buffer space" waits in 10046 output . Does it necessarily means I should look for resizing log_buffer ? What else can be done or looked at to reduce these waits . Thanks, ak
Organizational Challenge - Data Management Team
Title: Message All, I would like to open a discussion to solicit information regarding the support structure you utilize in your Data Management department. We currently have a flat end-to-end approach whereby a dba adopts an application and subsequent database in the early planning stages via teaming up with the Data Architect and developers and owns that application all the way through design, development, testing, and ultimately production support. As a smaller group (3-5) dba's this model worked fine, and everyone knew their respective database quite well. As more and more applications (internal and 3rd party) continue to rollover from legacy systems into Oracle solutions, this is proving to be very challenging to provide 24x7 support and related on-call duties spanning three RDBMS platforms (Informix, Oracle, and MS SQL Server). Our challenges are two fold: One, we are (like any shop today) extremely overloaded with work requests, so this makes cross-application training to spread the knowledge nearly impossible to accomplish. Two, with everyone tied to a project, we have no resource with large enough buckets of time to take on new and imperative technologies such as java, replication, high availability, xml as examples that our development teams would like to leverage in the database. We are in the early stages of looking at organization alternatives. We are fortunate in that 90% of the database support is already centralized in our department for the company, so that allows us the ability to minimize every dba learning lessons the hard way. Specifically, we are considering some "role" divisions amongst the DBA's. That is to say a subset dedicated to "engineering" such as implementing and architecting new technologies and related best practices, a second subset for implementation of systems being developed, and a third subset for production support. I would like to hear about the organization structure you are involved with and the pro and cons of a flat structure as compared to a more "role" based structure. Thanks in advance, -Ron- Lead Oracle DBA
RE: Corrected SQL Question...
Unfortunately, it is. - Kirti -Original Message- Sent: Thursday, March 13, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Is this cheating? 1* select a.c1||' '||a.c2||CHR(10)||b.c1||' '||b.c2 RESULTS from crap a, crap b where a.c2 = b.c1 and b.c2 = a.c1 SQL> / RESULTS --- DAL AUS AUS DAL HOU AUS AUS HOU AUS DAL DAL AUS HOU DAL DAL HOU LIT DAL DAL LIT XYZ DAL DAL XYZ AUS HOU HOU AUS DAL HOU HOU DAL LIT HOU HOU LIT XYZ HOU HOU XYZ DAL LIT LIT DAL HOU LIT LIT HOU DAL XYZ XYZ DAL HOU XYZ XYZ HOU 14 rows selected. > -Original Message- > From: Deshpande, Kirti [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 13, 2003 2:24 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Corrected SQL Question... > > > All they wanted was to "pair up" those city codes. > DAL -- AUS followed by AUS -- DAL, > AUS -- HOU followed by HOU -- AUS > etc... > and on separate lines. > So, cross-tab did not have the right format. > > I sent them Jacques Kilchoer's solution (he also sent me a > simplified one, without the UNION), and it was acceptable. > Problem solved, as there are no more questions :) > > - Kirti > > -Original Message- > Sent: Thursday, March 13, 2003 1:46 PM > To: Multiple recipients of list ORACLE-L > > > > Questions I would have for those who wrote the requirements: > Of possible combinations of the form ABC XYZ XYZ ABC, which > do they want? > > As can be seen from the answers sent to the list, there is > more than one set > of responses that give this pattern. If they only want "half" of the > possible patterns, which half is the correct half? > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Deshpande, Kirti > INET: [EMAIL PROTECTED] > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Create rollback segment under ORACLE 9ir2 failed????
And what Pete said does work. Here is a report from my testing of undo mode switching (AUM <-> MUM). Rollback tablespace was already created. SQL> create rollback segment rbs01 tablespace rollback; create rollback segment rbs01 tablespace rollback * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK' SQL> create rollback segment junk tablespace system; Rollback segment created. SQL> create rollback segment rbs01 tablespace rollback; create rollback segment rbs01 tablespace rollback * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK' SQL> alter rollback segment junk online; Rollback segment altered. SQL> create rollback segment rbs01 tablespace rollback; Rollback segment created. SQL> alter rollback segment rbs01 online; Rollback segment altered. SQL> alter rollback segment junk offline; Rollback segment altered. SQL> drop rollback segment junk; Rollback segment dropped. HTH, - Kirti -Original Message- Sent: Thursday, March 13, 2003 5:25 PM To: Multiple recipients of list ORACLE-L Mike The only way this would have worked under 8i is if you had already created a dummy rollback segment in the SYSTEM tablespace. Something like this should work (before or after the CREATE TABLESPACE rollback_space) SQL> connect / as sysdba; SQL> CREATE ROLLBACK SEGMENT dummy; Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Thursday, March 13, 2003 2:30 PM To: Multiple recipients of list ORACLE-L I am create database on ORACLE 9iR2 and fail on create rollback segment. SQL> create tablespace rollback_space datafile 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' size 800M 3 default storage ( 4 initial 256k 5 next 256k 6 pctincrease0 7 minextents 8 8 MAXEXTENTS 4096 9 ); Tablespace created. SQL> SQL> REM * Create rollback segments. SQL> REM * SQL> create rollback segment rollback_1 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_1 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' SQL> create rollback segment rollback_2 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_2 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' Those script used to work under ORACLE 8i. Does anyone know why? Thanks. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Excessive SQL*Net message from client waits
Please ignore my silly comments about 3000 queries. My brain is waking up and realising that 3000 is the number of SQL*Net messages. In essence, ignore my message and listen to Jonathan. - Forwarded by Mark Richard/TRANSURBAN on 14/03/2003 09:56 - Mark Richard To: [EMAIL PROTECTED] 14/03/2003 cc: 08:55Subject: RE: Excessive SQL*Net message from client waits(Document link: Mark Richard) I think you can relatively safely argue that Oracle is spending 90% of it's time waiting for the client (by that a user pressing a button or the application processing some logic) - and therefore even if you make Oracle run infinitely fast you will only improve the application overall by 10%. Perhaps someone else can verify this. Jonathan explained, quite well, why the waits are so high... It the application spawns 10 sessions per user then each session will only be called once per approx. 10 SQL statements. Reducing the number of sessions will reduce the wait time on the report, but won't speed the application up. The stats indicate that the application fired ~3,000 queries in ~10 minutes (if I'm reading it right). That gives a stat of about 5 queries per second - it sounds like there is little you can do at the Oracle end of town. My guess is that the application is doing a lot of "single row per query" type statements when it should be working on a record set. It's a shame, but it looks like an application problem that Oracle can do very little to help out. Regards, Mark. "Karen Morton" <[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> lting.com>cc: Sent by: Subject: RE: Excessive SQL*Net message from client waits [EMAIL PROTECTED] 13/03/2003 22:53 Please respond to ORACLE-L Not like this nor should it be the "top" event always as seems to be the case here I don't believe. And, I know for certain that the client did everything as quickly as possible during the trace. Minimal data entry done and OK buttons clicked without delay...no time out for getting a cup of coffee in between or anything. :) Karen -Original Message- Zanen Sent: Thursday, March 13, 2003 2:24 AM To: Multiple recipients of list ORACLE-L Hi Isn't sql*net message from client always sort of on top, because it just means the rdbms is waiting for the client to send some query/command (user is not typing/clicking/reading fast enough) Jack -Original Message- Sent: donderdag 13 maart 2003 3:19 To: Multiple recipients of list ORACLE-L Hi All, I've got a situation where I've collected trace data and am seeing 90% of total response time is accounted for with the SQL*Net Message From Client event. Individual queries within the trace show minimal CPU time used and no obvious indications of bad SQL being the culprit. I used the Hotsos Profiler (way cool) and here's an example of what it shows: Response Time Component Duration
Re: Create rollback segment under ORACLE 9ir2 failed????
Mike, et.al, Mea Culpa. Please ignore my previous post. I failed to properly context switch from my Automatic Undo mode. IIRC, as of 7.3, the requirement for a second rollback segment in SYSTEM was removed, with minor exceptions. I think the one that is biting you is that a second RBS must be created in SYSTEM if the rollback segment tablespace is locally managed. This is the default for 9i, where the default for 8i was dictionary. Check the allocation_type for the tablespace. If it is not dictionary, drop and recreate the tablespace as dictionary or create the second rbs in system. -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals Pete Sharman wrote: Mike The only way this would have worked under 8i is if you had already created a dummy rollback segment in the SYSTEM tablespace. Something like this should work (before or after the CREATE TABLESPACE rollback_space) SQL> connect / as sysdba; SQL> CREATE ROLLBACK SEGMENT dummy; Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Thursday, March 13, 2003 2:30 PM To: Multiple recipients of list ORACLE-L I am create database on ORACLE 9iR2 and fail on create rollback segment. SQL> create tablespace rollback_space datafile 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' size 800M 3 default storage ( 4 initial 256k 5 next 256k 6 pctincrease0 7 minextents 8 8 MAXEXTENTS 4096 9 ); Tablespace created. SQL> SQL> REM * Create rollback segments. SQL> REM * SQL> create rollback segment rollback_1 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_1 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' SQL> create rollback segment rollback_2 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_2 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' Those script used to work under ORACLE 8i. Does anyone know why? Thanks. __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Create rollback segment under ORACLE 9ir2 failed????
Title: RE: Create rollback segment under ORACLE 9ir2 failed I see other people have already answered your question, but would it be presumptuous of me to ask why you are using ROLLBACK segments instead of an UNDO tablespace? > -Original Message- > From: mike mon [mailto:[EMAIL PROTECTED]] > > I am create database on ORACLE 9iR2 and fail on create > rollback segment. > > SQL> create tablespace rollback_space datafile > 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' > size 800M > 3 default storage ( > 4 initial 256k > 5 next 256k > 6 pctincrease 0 > 7 minextents 8 > 8 MAXEXTENTS 4096 > 9 ); > > Tablespace created. > > SQL> > SQL> REM * Create rollback segments. > SQL> REM * > SQL> create rollback segment rollback_1 tablespace > rollback_space > 2 storage (initial 256K next 256k minextents > 20 optimal 5M); > create rollback segment rollback_1 tablespace > rollback_space > * > ERROR at line 1: > ORA-01552: cannot use system rollback segment for > non-system tablespace > 'ROLLBACK_SPACE' > > > SQL> create rollback segment rollback_2 tablespace > rollback_space > 2 storage (initial 256K next 256k minextents > 20 optimal 5M); > create rollback segment rollback_2 tablespace > rollback_space > * > ERROR at line 1: > ORA-01552: cannot use system rollback segment for > non-system tablespace > 'ROLLBACK_SPACE'
RE: Fixed_date and dbms_job
Title: RE: Fixed_date and dbms_job After calling dbms_job.submit, did you issue a commit? > -Original Message- > From: Kader Ben [mailto:[EMAIL PROTECTED]] > > I'm simulating the date in future with fixed_date. > I wrote procedure to be called every seconde through > dbms_job to increment the fixed_date. > > I did that dbms_job.submit(:job_num, 'myprocedure;', > sysdate, 'sysdate'); > > the dba_jobs table show me the right interval un > next_date: > > > BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL > N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate > > And the sysdate is: > 13-JUN-2003 17:50:06 > > But the job never execute.
RE: Is range partitioning possible on part of varchar2 column ???
No -Original Message- Sent: Thursday, March 13, 2003 5:49 PM To: Multiple recipients of list ORACLE-L ??? Is list partitioning available in 8i? Iam on 8.1.7.4. -- Babu -Original Message- Sent: Thursday, March 13, 2003 1:49 PM To: Multiple recipients of list ORACLE-L ??? Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format "MON" . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 SAN ? why not external storage ?
There are many things I don't get in this life. One of them is the statements about disk storage being an admin nightmare and way too expensive. Aren't disks very cheap these days?! Mogens [EMAIL PROTECTED] wrote: Rahul, This is personal opinion, but it looks to me like your concerned about the database your creating for the client and may not have the total or corporate wide view your client has. We're heading down the SAN road not because of any specific database requirements but because disk storage has become an administrative nightmare as well as way too expensive. Dick Goulet Reply Separator Author: "Arun Annamalai" <[EMAIL PROTECTED]> Date: 3/13/2003 12:24 PM Usaually SAN and NAS is used for several good reasons...the two main are... 1) High availability - When you have your database files on SAN/NAS then you can bring ur database on another server when the primary goes down. Obviously you have to use a cluster or Big IP (F5) on the front. 2) reduce redundancy -A unix userid with home directory attached to a paticular NFS drive on NAS/SAN, will able to see all his files when he logs into other servers. so far I heard "Net App" is low cost including with Raid 5. -Arun. Sr oracle dba - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 9:38 PM Subject: Re: why SAN ? why not external storage ? my reasons to recommend an external storage was.. 1) the database size is 36GB, and according to many documents i have read, SAN is not cost effevtive unless populated by a large numbers of drives !!, now for the client the cost is not the factor.. given the situation.. wouldnt a SAN be an overkill ? 2) NO DBA or SYS ADMIN skills to manage the SAN !! - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 8:33 PM Subject: Re: why SAN ? why not external storage ? Can you share some of the reasons related to your decision in choosing a direct-attach storage (DAS) instead of a SAN? In general, a SAN is a much smarter choice than DAS. - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 1:33 AM Subject: why SAN ? why not external storage ? list, one of our clietns are going to by SAN, the current oracle databases take around 36GB of storage i dnt understand there reason to go for SAN, i sugguested to buy an external storage box instead. How can i justify my desicion ? (cost of not the factor) TIA rahul Usaually SAN and NAS is used for several good reasons...the two main are... 1) High availability - When you have your database files on SAN/NAS then you can bring ur database on another server when the primary goes down. Obviously you have to use a cluster or Big IP (F5) on the front. 2) reduce redundancy -A unix userid with home directory attached to a paticular NFS drive on NAS/SAN, will able to see all his files when he logs into other servers. so far I heard "Net App" is low cost including with Raid 5. -Arun. Sr oracle dba - Original Message - From: mailto:[EMAIL PROTECTED]">Rahul To: mailto:[EMAIL PROTECTED]">Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 9:38 PM Subject: Re: why SAN ? why not external storage ? my reasons to recommend an external storage was.. 1) the database size is 36GB, and according to many documents i have read, SAN is not cost effevtive unless populated by a large numbers of drives !!, now for the client the cost is not the factor.. given the situation.. wouldnt a SAN be an overkill ? 2) NO DBA or SYS ADMIN skills to manage the SAN !! - Original Message - From: mailto:[EMAIL PROTECTED]">Tim Gorman To: mailto:[EMAIL PROTECTED]">Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 8:33 PM Subject: Re: why SAN ? why not external storage ? Can you share some of the reasons related to your decision in choosing a direct-attach storage (DAS) instead of a SAN? In general, a SAN is a much smarter choice than DAS. - Original Message - From: mailto:[EMAIL PROTECTED]">Rahul To: mailto:[EMAIL PROTECTED]">Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 1:33 AM Subject: why SAN ? why not external storage ? list, one of our clietns are going to by SAN, the current oracle databases take around 36GB of storage i dnt understand there reason to go for SAN, i sugguested to buy an external storage box instead. How can i justify my desicion ? (cost of not the factor) TIA rahul -- Please see the official ORACLE-L FAQ:
RE: Create rollback segment under ORACLE 9ir2 failed????
Mike The only way this would have worked under 8i is if you had already created a dummy rollback segment in the SYSTEM tablespace. Something like this should work (before or after the CREATE TABLESPACE rollback_space) SQL> connect / as sysdba; SQL> CREATE ROLLBACK SEGMENT dummy; Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Thursday, March 13, 2003 2:30 PM To: Multiple recipients of list ORACLE-L I am create database on ORACLE 9iR2 and fail on create rollback segment. SQL> create tablespace rollback_space datafile 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' size 800M 3 default storage ( 4 initial 256k 5 next 256k 6 pctincrease0 7 minextents 8 8 MAXEXTENTS 4096 9 ); Tablespace created. SQL> SQL> REM * Create rollback segments. SQL> REM * SQL> create rollback segment rollback_1 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_1 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' SQL> create rollback segment rollback_2 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_2 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' Those script used to work under ORACLE 8i. Does anyone know why? Thanks. __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Pete Sharman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fixed_date and dbms_job
Hi Listers, I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. Could you please give me hint how to resolve this problem? Thanks you, Ben __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Create rollback segment under ORACLE 9ir2 failed????
Mike, Check the setting for undo_management. It needs to be set to MANUAL. mike mon wrote: I am create database on ORACLE 9iR2 and fail on create rollback segment. SQL> create tablespace rollback_space datafile 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' size 800M 3 default storage ( 4 initial 256k 5 next 256k 6 pctincrease0 7 minextents 8 8 MAXEXTENTS 4096 9 ); Tablespace created. SQL> SQL> REM * Create rollback segments. SQL> REM * SQL> create rollback segment rollback_1 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_1 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' SQL> create rollback segment rollback_2 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_2 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' Those script used to work under ORACLE 8i. Does anyone know why? Thanks. __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
log buffer space
I am finding tons of "log buffer space" waits in 10046 output . Does it necessarily means I should look for resizing log_buffer ? What else can be done or looked at to reduce these waits . Thanks, ak
RE: Is range partitioning possible on part of varchar2 column ???
Is list partitioning available in 8i? Iam on 8.1.7.4. -- Babu -Original Message- Sent: Thursday, March 13, 2003 1:49 PM To: Multiple recipients of list ORACLE-L ??? Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format "MON" . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Create rollback segment under ORACLE 9ir2 failed????
I am create database on ORACLE 9iR2 and fail on create rollback segment. SQL> create tablespace rollback_space datafile 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' size 800M 3 default storage ( 4 initial 256k 5 next 256k 6 pctincrease0 7 minextents 8 8 MAXEXTENTS 4096 9 ); Tablespace created. SQL> SQL> REM * Create rollback segments. SQL> REM * SQL> create rollback segment rollback_1 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_1 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' SQL> create rollback segment rollback_2 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_2 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' Those script used to work under ORACLE 8i. Does anyone know why? Thanks. __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Corrected SQL Question...
Igor Neyman wrote: > > Kirti, > > What about solution suggested by Stephane Faroult: > > select * > from (select * > from T > connect by col1 = prior col2 > and col1 > col2) x > where rownum <= (select count(*) from T) > / > > ? > > Igor Neyman, OCP DBA > [EMAIL PROTECTED] > Igor, I can answer that - col1 > col2 worked with the first sample of data, not with the second one. The problem is with the loops in the tree - CONNECT BY doesn't like round-trips from an airport and back! And since you cannot put a subquery in a CONNECT BY, you're toast. I think, though, that you can probably use the tree walk if you do it in PL/SQL with a bulk select in an array. Previous experiments have shown to me that when the exception is raised, the data is returned anyway. Needless to say, it becomes messy :-). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
java script and pl/sql again ???
Hi,
Thank you to all those answering my java script
question.
As I said, all the html and java script are generated
by pl/sql package. I'd like to know can the onClick
method below call a pl/sql procedure? If it cann't, I
suppose onClick needs to call a java script function
first, and in that java script function, how to call a
pl/sql procedure ??? (wl is a function used to
generate html)
Package pkgSth
AS
...
wl(';
...
End;
Thank you!
Janet
__
Do you Yahoo!?
Yahoo! Web Hosting - establish your business online
http://webhosting.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janet Linsy
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Excessive SQL*Net message from client waits
I think you can relatively safely argue that Oracle is spending 90% of it's time waiting for the client (by that a user pressing a button or the application processing some logic) - and therefore even if you make Oracle run infinitely fast you will only improve the application overall by 10%. Perhaps someone else can verify this. Jonathan explained, quite well, why the waits are so high... It the application spawns 10 sessions per user then each session will only be called once per approx. 10 SQL statements. Reducing the number of sessions will reduce the wait time on the report, but won't speed the application up. The stats indicate that the application fired ~3,000 queries in ~10 minutes (if I'm reading it right). That gives a stat of about 5 queries per second - it sounds like there is little you can do at the Oracle end of town. My guess is that the application is doing a lot of "single row per query" type statements when it should be working on a record set. It's a shame, but it looks like an application problem that Oracle can do very little to help out. Regards, Mark. "Karen Morton" <[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> lting.com>cc: Sent by: Subject: RE: Excessive SQL*Net message from client waits [EMAIL PROTECTED] 13/03/2003 22:53 Please respond to ORACLE-L Not like this nor should it be the "top" event always as seems to be the case here I don't believe. And, I know for certain that the client did everything as quickly as possible during the trace. Minimal data entry done and OK buttons clicked without delay...no time out for getting a cup of coffee in between or anything. :) Karen -Original Message- Zanen Sent: Thursday, March 13, 2003 2:24 AM To: Multiple recipients of list ORACLE-L Hi Isn't sql*net message from client always sort of on top, because it just means the rdbms is waiting for the client to send some query/command (user is not typing/clicking/reading fast enough) Jack -Original Message- Sent: donderdag 13 maart 2003 3:19 To: Multiple recipients of list ORACLE-L Hi All, I've got a situation where I've collected trace data and am seeing 90% of total response time is accounted for with the SQL*Net Message From Client event. Individual queries within the trace show minimal CPU time used and no obvious indications of bad SQL being the culprit. I used the Hotsos Profiler (way cool) and here's an example of what it shows: Response Time Component Duration # Calls AvgMin Max -- --- --- -- --- (i) SQL*Net message from client 500.98s 85.1% 2,757 0.181712s 0.00s 5.91s (i) unaccounted-for 23.03s3.9% (i) direct path write22.38s3.8% 1,373 0.016300s 0.00s 0.32s (i) log file sync20.70s3.5% 685 0.030219s 0.00s 0.52s (i) user-mode CPU12.12s2.1% 12,016 0.001009s 0.00s 1.50s (i) direct path read 6.66s1.1% 985 0.006761s 0.00s 0.09s (i) db file sequential read 1.09s0.2% 2,679 0.000407s 0.00s 0.14s (i) db file scattered read0.83s0.1% 2,158 0.000385s 0.00s 0.17s (i) SQL*Net more data to client 0.50s0.1% 1,007 0.000497s 0.00s 0.13s (i) SQL*Net more data from client 0.42s0.1%
RE: Is range partitioning possible on part of varchar2 column ???
Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format "MON" . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBV Cannot Load Module LIBDCE.A - SOLUTION
Thanks Jared and Scott for your replies. Scott: I ran your “lslpp -L |grep -i dce”, but it did not produce any output. Which dbv did produce the expected output. I had opened up a TAR with Oracle this morning, and they set me straight in fairly short order. This problem is due to a bug. Solution was to use dbv from Oracle 7.3.4.5 AIX (which I do not have). So Oracle placed the file on their ftp server for me to download. It works fine now. For those interested, here is the text of the bug: - Abstract: USING DBV ON 7.3.4.5 AIX RESULT IN: COULD NOT LOAD LIBRARY LIBDCE.A[SHR.O] - RDBMS Ver: 7.3.4.5 - Status: 93,Closed, Not Verified by Filer - Fixed In Ver: 7.3.4.5 - O/S: 319 IBM RS 6000 AIX - DIAGNOSTIC ANALYSIS: This is caused by the dce protocol linked in the dbv executable but the needed library is not on the cst system. The 7.3.4.5 AIX patchset is created on a system with dce installed. It is not possible to (re)make the dbv executable because makefiles for dbv are not provided in the rdbms software. - WORKAROUND: Use the dbv executable which is in the AIx patchset 7.3.4.4. This executable does not need the library libdce.a Thanks again for your responses, Sam Bootsma George Brown College [EMAIL PROTECTED] 416-415-5000 x4933 -Original Message- From: Scott Behrens [mailto:[EMAIL PROTECTED] Sent: March 13, 2003 9:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBV Cannot Load Module LIBDCE.A Sam, I've never worked in a DCE environment, but here's a couple of things to verify: 1. If libdce.a is not installed in the first place, is your environment using DCE? I think this is an LPP (a separately licensed product) from IBM for AIX. Try the following from a unix prompt: $ lslpp -L |grep -i dce I think you should get hits like dce.client.rte.pthreads If you don't, then I wonder why dbv thinks it needs it... Try the following just to make sure you're running the dbv you think you are: $ which dbv It should return $ORACLE_HOME/bin/dbv, for example: $ which dbv /u01/app/oracle/product/8.1.7/bin/dbv If it returns the Oracle 'dbv', then you might check the $ORACLE_HOME/rdbms/lib/sysliblist to see if it has an entry for libdce. If so, the product was linked at some point with the DCE Advanced Networking Option (see MetaLink Note 1043700.6 This doc notes the following: NOTE: You may have to manually remove the library reference from "sysliblist". In Oracle 7.3.4, the Installer did not remove the library reference during a deinstallation.) In any case, I would be slow to add PTFs or relink or make other changes in a production environment just for the sake of dbv (if the rest of the environment is stable) until I had a better understanding of the opsys environment and the Oracle installation history. HTH, Scott >>> [EMAIL PROTECTED] 3/12/03 5:15:58 PM >>> Hi all, First, thanks Jared for the info. The libdce.a file does not exist on any of our AIX servers. It just AINT there! I typed libdce.a into google and from there downloaded PTF2,3, and 4 that are supposed contain DCE fixes to this file. Instructions say to rename the old file and replace it with this new file. After the download, I was not able to uncompress the files on my XP workstation. So I ftp'd the file (in binary mode) to our AIX server and used uncompress to expand the files to libdce2.exe, libdce3.exe, libdce4.exe (originally the files were named libdce2.exe.Z, etc.). The .exe extension confused me (I am told .exe is Windows only, not UNIX). I ftp'd the files back to my workstation, and tried running it from DOS. I get an error message "Program too big to fit in memory". However, the exe is only 217,856 bytes (not kilobytes). My next step was to ask my neighbour if he could unzip the original libdce2.exe.Z on his W2K workstation - he was successful! However, he received the same message when he attempted to run the resultant .exe on his workstation. I am relatively new to UNIX (took a course, and did some reading, but not much hands-on). I am told by a more experience colleague that .exe's do not run on UNIX. So now I am at a loss on what to try next. Any ideas? Thanks, Sam Bootsma George Brown College [EMAIL PROTECTED] 416-415-5000 x4933 -Original Message- Sent: March 11, 2003 7:14 PM To: Multiple recipients of list ORACLE-L Just found it: on AIX ( 4.3 at least ) it's LIBPATH [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/11/2003 03:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: DBV Cannot Load Module LIBDCE.A Make sure LD_LIBRARY_PATH includes /usr/lib. At least, I think it's LD_LIBRAR
Re: Is range partitioning possible on part of varchar2 column ??????
No, I think you will have to add a column to store '' separately in order to partition on it. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, March 13, 2003 4:14 PM > Dear List, > > I have a table of size approx 10gig, and I need to partition based on the > YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The > data in the column of format "MON" . I need to partition the table based > on the year , that is, substr(report_cycle_cd, 4,4). > > Substr function doesn't seem to be permitted in the partitioning syntax and > so am getting errors. Only TO_DATE function seems to be permitted. Since it > is not a date column, I would like to know if there is a way to RANGE > partition the table, instead of HASH partitioning. > > Appreciate any suggestions. > > Thanks, > -- Babu > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Janardhana Babu Donga > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is range partitioning possible on part of varchar2 column ???
Babu
I don't think partitions are clearly documented anywhere. Here is some SQL
that works so you can see how to use a date function. It partitions on two
columns, but I wanted you to see something that works.
add partition sum_fy_28
values less than ('FY', to_date('02012003','mmdd'))
tablespace data_fy_28
-Original Message-
Sent: Thursday, March 13, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
??
Dear List,
I have a table of size approx 10gig, and I need to partition based on the
YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
data in the column of format "MON" . I need to partition the table based
on the year , that is, substr(report_cycle_cd, 4,4).
Substr function doesn't seem to be permitted in the partitioning syntax and
so am getting errors. Only TO_DATE function seems to be permitted. Since it
is not a date column, I would like to know if there is a way to RANGE
partition the table, instead of HASH partitioning.
Appreciate any suggestions.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Is async IO configured on HP-UX?
On HP, I believe that async I/O is only supported for raw filesystems. See MetaLink for more details. Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, March 13, 2003 1:36 PM To: Multiple recipients of list ORACLE-L The HP-UX syscall tracing tool is called 'tusc', and it's supposed to be available by visiting ftp://ftp.cup.hp.com/dist/networking/misc/. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Thursday, March 13, 2003 12:36 PM To: Multiple recipients of list ORACLE-L Ethan - it has been a while and we don't have an HP-UX box here, but if memory serves the missing /dev/async is a dead giveaway that your not using async io. to use async io on HP-UX we had to install something called the asyncdisk driver in SAM and then use mknod to create /dev/async directory. could the asyncdisk driver already be installed and you just need the directory? SAM should tell you. if not i think it means a kernel rebuild. -Original Message- Sent: Thursday, March 13, 2003 10:44 AM To: Multiple recipients of list ORACLE-L /dev does not have an async directory, async IO is turned on in the database, how do I determine if async IO is activated on the OS side of the house? Is the missing async directory an indicator? Also, is the database smart enough to figure out that the OS is not using async and make correct call or will I get a timeout on async call then perform standard write? Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: why SAN ? why not external storage ?
Rahul, This is personal opinion, but it looks to me like your concerned about the database your creating for the client and may not have the total or corporate wide view your client has. We're heading down the SAN road not because of any specific database requirements but because disk storage has become an administrative nightmare as well as way too expensive. Dick Goulet Reply Separator Author: "Arun Annamalai" <[EMAIL PROTECTED]> Date: 3/13/2003 12:24 PM Usaually SAN and NAS is used for several good reasons...the two main are... 1) High availability - When you have your database files on SAN/NAS then you can bring ur database on another server when the primary goes down. Obviously you have to use a cluster or Big IP (F5) on the front. 2) reduce redundancy -A unix userid with home directory attached to a paticular NFS drive on NAS/SAN, will able to see all his files when he logs into other servers. so far I heard "Net App" is low cost including with Raid 5. -Arun. Sr oracle dba - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 9:38 PM Subject: Re: why SAN ? why not external storage ? my reasons to recommend an external storage was.. 1) the database size is 36GB, and according to many documents i have read, SAN is not cost effevtive unless populated by a large numbers of drives !!, now for the client the cost is not the factor.. given the situation.. wouldnt a SAN be an overkill ? 2) NO DBA or SYS ADMIN skills to manage the SAN !! - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 8:33 PM Subject: Re: why SAN ? why not external storage ? Can you share some of the reasons related to your decision in choosing a direct-attach storage (DAS) instead of a SAN? In general, a SAN is a much smarter choice than DAS. - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 1:33 AM Subject: why SAN ? why not external storage ? list, one of our clietns are going to by SAN, the current oracle databases take around 36GB of storage i dnt understand there reason to go for SAN, i sugguested to buy an external storage box instead. How can i justify my desicion ? (cost of not the factor) TIA rahul Usaually SAN and NAS is used for several good reasons...the two main are... 1) High availability - When you have your database files on SAN/NAS then you can bring ur database on another server when the primary goes down. Obviously you have to use a cluster or Big IP (F5) on the front. 2) reduce redundancy -A unix userid with home directory attached to a paticular NFS drive on NAS/SAN, will able to see all his files when he logs into other servers. so far I heard "Net App" is low cost including with Raid 5. -Arun. Sr oracle dba - Original Message - From: mailto:[EMAIL PROTECTED]">Rahul To: mailto:[EMAIL PROTECTED]">Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 9:38 PM Subject: Re: why SAN ? why not external storage ? my reasons to recommend an external storage was.. 1) the database size is 36GB, and according to many documents i have read, SAN is not cost effevtive unless populated by a large numbers of drives !!, now for the client the cost is not the factor.. given the situation.. wouldnt a SAN be an overkill ? 2) NO DBA or SYS ADMIN skills to manage the SAN !! - Original Message - From: mailto:[EMAIL PROTECTED]">Tim Gorman To: mailto:[EMAIL PROTECTED]">Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 8:33 PM Subject: Re: why SAN ? why not external storage ? Can you share some of the reasons related to your decision in choosing a direct-attach storage (DAS) instead of a SAN? In general, a SAN is a much smarter choice than DAS. - Original Message - From: mailto:[EMAIL PROTECTED]">Rahul To: mailto:[EMAIL PROTECTED]">Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 1:33 AM Subject: why SAN ? why not external storage ? list, one of our clietns are going to by SAN, the current oracle databases take around 36GB of storage i dnt understand there reason to go for SAN, i sugguested to buy an external storage box instead. How can i justify my desicion ? (cost of not the factor) TIA rahul -- 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.
Re: Corrected SQL Question...
Kirti, What about solution suggested by Stephane Faroult: select * from (select * from T connect by col1 = prior col2 and col1 > col2) x where rownum <= (select count(*) from T) / ? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, March 13, 2003 3:24 PM > All they wanted was to "pair up" those city codes. > DAL -- AUS followed by AUS -- DAL, > AUS -- HOU followed by HOU -- AUS > etc... > and on separate lines. > So, cross-tab did not have the right format. > > I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the UNION), and it was acceptable. > Problem solved, as there are no more questions :) > > - Kirti > > -Original Message- > Sent: Thursday, March 13, 2003 1:46 PM > To: Multiple recipients of list ORACLE-L > > > > Questions I would have for those who wrote the requirements: > Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? > > As can be seen from the answers sent to the list, there is more than one set > of responses that give this pattern. If they only want "half" of the > possible patterns, which half is the correct half? > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Deshpande, Kirti > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: NESTED_TABLE_GET_REFS hint
The Metalink description of this hint seems a little obscure. My experience is that it simply allows you to reference a nested table directly without 'pseudo-joining' it to its rightful parent. Given the funny games (such as using a thoroughly spurious /*+ cardinality() */ hint, and bypassing the constraint on updateable join views) that Oracle plays for its internal purposes, I wouldn't be surprised to see them "cheating" like this on MV updates as well. It doesn't necessarily mean anything. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 13 March 2003 20:18 Hi, I'm currently defining an optimization strategy for a reporting environment. The reporting environment is refreshing 41 materialized views on a weekly basis. Some of the MV take over an 1 hour to refresh. During the MV refresh, in Statspack, I can see that the top SQL are Oracle generated SQL using the NESTED_TABLE_GET_REFS hint. >From metalink : "If this is a DSS system, you probably have a smaller shared pool and use import/export heavily. If so,it might be a good idea to use a bigger shared pool when doing this activity." Anyone has more info on this. TIA Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tél. (514) 925-7187 [EMAIL PROTECTED] -- 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).
Re: Excessive SQL*Net message from client waits
I am not suggesting that sessions are waiting for each other, or reporting each others' wait times. I am simply assuming that if the application design was daft enough to spawn multiple sessions, it probably was clever enough to have the parallel, independent threads of execution making all those sessions work concurrently at the client. Consequently, if the client 'spawns a session which then connects, does something and disconnects', I am assuming that the initial client is waiting for that session to complete and so (from Oracle's perspective) the initial client's session is waiting on (its own, and no-one else's) "SQL*Net message from client". So if the initial client has spawned 9 other sessions, I would (perhaps unfairly) assume that at any one instant only one of them is actually doing anything - which is why on average I would not be surprised to see a 90% SQL*Net wait. Moving away from the SQL*Net bit though, my impression of the other stats was that the user could quite possibly see a significant turn around time between hitting a key and seeing a response. Given a limited number of messages (2,750 I think it was) to and from the client, the volume of direct reads and writes was high, and the number of log file sync waits was very high - with a surprising max wait on log file sync. The application seems to be committing over-enthusiastically - which stresses the log writer and log buffer latching anyway - but there is also a lot of stress on the I/O system from (probably) sorts or hash joins. Perhaps this site has a different data distribution, or set of indexes, that is making some execution paths very expensive, and bring into sharp relief an underlying problem with commit rates. . Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 13 March 2003 20:29 > Jonathon et al, is it really true that every session is waiting on the > others if as each session is spawned, it does its thing (i.e. issues some > set of queries) and then disconnects? There are never two sessions doing > something simultaneously really. The user logs in and only sees and works > with one screen at a time. A session is spawned to do some initialization > stuff...this one sticks around and may see a bit more activity before the > logout...so I can see how this one would have the waits. But the other > spawned sessions connect, do something and disconnect. These spawned > sessions come from various controls on the screen...not different app > occurrences or windows within the app. > -- 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).
RE: Corrected SQL Question...
Is this cheating? 1* select a.c1||' '||a.c2||CHR(10)||b.c1||' '||b.c2 RESULTS from crap a, crap b where a.c2 = b.c1 and b.c2 = a.c1 SQL> / RESULTS --- DAL AUS AUS DAL HOU AUS AUS HOU AUS DAL DAL AUS HOU DAL DAL HOU LIT DAL DAL LIT XYZ DAL DAL XYZ AUS HOU HOU AUS DAL HOU HOU DAL LIT HOU HOU LIT XYZ HOU HOU XYZ DAL LIT LIT DAL HOU LIT LIT HOU DAL XYZ XYZ DAL HOU XYZ XYZ HOU 14 rows selected. > -Original Message- > From: Deshpande, Kirti [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 13, 2003 2:24 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Corrected SQL Question... > > > All they wanted was to "pair up" those city codes. > DAL -- AUS followed by AUS -- DAL, > AUS -- HOU followed by HOU -- AUS > etc... > and on separate lines. > So, cross-tab did not have the right format. > > I sent them Jacques Kilchoer's solution (he also sent me a > simplified one, without the UNION), and it was acceptable. > Problem solved, as there are no more questions :) > > - Kirti > > -Original Message- > Sent: Thursday, March 13, 2003 1:46 PM > To: Multiple recipients of list ORACLE-L > > > > Questions I would have for those who wrote the requirements: > Of possible combinations of the form ABC XYZ XYZ ABC, which > do they want? > > As can be seen from the answers sent to the list, there is > more than one set > of responses that give this pattern. If they only want "half" of the > possible patterns, which half is the correct half? > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Deshpande, Kirti > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.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).
Re: DBXray anyone?
I've got installed - my boss likes BMC so we bought it. I really don't use it too much. It doesn't seem to have much different from OEM - a little better graphics, but since I have my set of scripts that I've come to rely on I really don't use the gui stuff a lot except for my freeware version of TOAD.>>> [EMAIL PROTECTED] 03/13/03 12:00PM >>>Anyone using BMC's DBXRay? Is it a decent product?-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Suzy Vordos INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Is range partitioning possible on part of varchar2 column ??????
Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format "MON" . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or 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 SAN ? why not external storage ?
Usaually SAN and NAS is used for several good reasons...the two main are... 1) High availability - When you have your database files on SAN/NAS then you can bring ur database on another server when the primary goes down. Obviously you have to use a cluster or Big IP (F5) on the front. 2) reduce redundancy -A unix userid with home directory attached to a paticular NFS drive on NAS/SAN, will able to see all his files when he logs into other servers. so far I heard "Net App" is low cost including with Raid 5. -Arun. Sr oracle dba - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 9:38 PM Subject: Re: why SAN ? why not external storage ? my reasons to recommend an external storage was.. 1) the database size is 36GB, and according to many documents i have read, SAN is not cost effevtive unless populated by a large numbers of drives !!, now for the client the cost is not the factor.. given the situation.. wouldnt a SAN be an overkill ? 2) NO DBA or SYS ADMIN skills to manage the SAN !! - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 8:33 PM Subject: Re: why SAN ? why not external storage ? Can you share some of the reasons related to your decision in choosing a direct-attach storage (DAS) instead of a SAN? In general, a SAN is a much smarter choice than DAS. - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 1:33 AM Subject: why SAN ? why not external storage ? list, one of our clietns are going to by SAN, the current oracle databases take around 36GB of storage i dnt understand there reason to go for SAN, i sugguested to buy an external storage box instead. How can i justify my desicion ? (cost of not the factor) TIA rahul
RE: Perl Book
Programming the Perl DBI from Oreilly is really good for your need. -Original Message- From: Farnsworth, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 8:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: Perl Book http://www.oreilly.com/catalog/oracleperl/ Play your cards right and you may even get an autographed copy. ;o) Dave -Original Message- From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 8:04 AM To: Multiple recipients of list ORACLE-L Subject: OTC: Perl Book Looks like I have a need write a Perl program to access a database. Any suggestions on a good book. Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204
Re: High consistent gets , 10046
Vadim, Apologies, I answered the question you didn't ask - viz why does it take so long, rather than the 'what are the CR gets'. Your second suggestion is the correct one. It seems unreasonable, but when you do the "select for update", Oracle seems to go through a load of read- consistency work for the block to roll back the changes made by other transactions. The excess CR gets are accesses to the UNDO blocks need to build the CR image. Strangely, if you just slam in the 'update', rather than 'select for update' this phenomenon does not occur. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 13 March 2003 18:54 > Thank you, Jonathan, > > I'll continue looking for my options to cool down the hot spots. Not sure if > I can go for partitioning since Oracle charges $$$. > > Is it correct that oracle counts looking through the chain > for the correct copy as many CR? Or the reason for these extra CR is access > to undo segments in attemt to reconstruct CR block aged out from cache? > > Thanks > Vadim > > -- 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).
RE: Corrected SQL Question...
All they wanted was to "pair up" those city codes. DAL -- AUS followed by AUS -- DAL, AUS -- HOU followed by HOU -- AUS etc... and on separate lines. So, cross-tab did not have the right format. I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the UNION), and it was acceptable. Problem solved, as there are no more questions :) - Kirti -Original Message- Sent: Thursday, March 13, 2003 1:46 PM To: Multiple recipients of list ORACLE-L Questions I would have for those who wrote the requirements: Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? As can be seen from the answers sent to the list, there is more than one set of responses that give this pattern. If they only want "half" of the possible patterns, which half is the correct half? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Excessive SQL*Net message from client waits
Jonathon et al, is it really true that every session is waiting on the others if as each session is spawned, it does its thing (i.e. issues some set of queries) and then disconnects? There are never two sessions doing something simultaneously really. The user logs in and only sees and works with one screen at a time. A session is spawned to do some initialization stuff...this one sticks around and may see a bit more activity before the logout...so I can see how this one would have the waits. But the other spawned sessions connect, do something and disconnect. These spawned sessions come from various controls on the screen...not different app occurrences or windows within the app. So, what I end up with are 10 separate trace files...one for each session connect period. Doesn't each trace file then only show that specific session's info and big spikes in SQL*Net message waits shouldn't "carry over". I'll certainly try your idea about using netstat while tracing and see what I find. I feel as if I'm being thick-headed about this but I do not see this same behavior at every installation. These high SQL*Net message waits are showing up only at this one client site. Other pratically identical sites do not see this behavior. By practically identical I mean that other comparable sites have different network config. This particular site has it's database server 100 miles away from the users running the client application. WAN vs LAN. Just wish I could find a "good reason" why it's so different. Thanks so much, Karen Morton -Original Message- Lewis Sent: Thursday, March 13, 2003 12:19 PM To: Multiple recipients of list ORACLE-L I'd start by being doubtful about anybody being able to work so fast that the can avoid a high percentage of time in 'sql*net from client' - in fact, it the percentage was low (when the client was a person at a terminal) I would write myself a memo to check whether the client code was executing an extreme number of very small statements behind the scenes (e.g. get all keys for a drop-down, then get each drop down by key one at a time every time the user hit the field). It's always possible that the many layers of code at the client end are taking a surprising However, assuming you have a truly unreasonable loss of time on waiting for client - I would try and isolate the problem by using netstat and top. This can be hard in typical environments, though. Start up the client session - Start netstat running on the server with minimum snapshot time (usually one second). Start top (or similar) running in real time or minimum snapshot time. Start up event 10046 at the session. Then get the client to do something, and watch for: a)the peak in netstat as the request reaches the server. b)the burst from the server as the request is serviced c)the peak in netstat as the reply gets sent d)the delay before it appears on the client screen. It's crude, but simple-minded, and if the client is causing the problem it may prove it quite convincingly. Back it up with the trace file - which will record timestamps of a query coming in and results going out. The biggest problem, usually, is that it simply isn't realistic to get a system so quiet that you can get just one client running all by itself with nothing else going on. In your particular case, I have to sya that I have noticed that Java can use a surprising amount of CPU sometimes. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 13 March 2003 15:54 > Good point, but what if each user only has a single session? > > Not that I've noticed this exact same situation here on one of our > Engineering support databases whose clients are Java, and I'm not wondering > if it has something to do with the application or if I can possibly speed it > up with tweaks to SDU/TDU. I'm just wondering... ;) > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karen Morton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailin
NESTED_TABLE_GET_REFS hint
Hi, I'm currently defining an optimization strategy for a reporting environment. The reporting environment is refreshing 41 materialized views on a weekly basis. Some of the MV take over an 1 hour to refresh. During the MV refresh, in Statspack, I can see that the top SQL are Oracle generated SQL using the NESTED_TABLE_GET_REFS hint. From metalink : "If this is a DSS system, you probably have a smaller shared pool and use import/export heavily. If so,it might be a good idea to use a bigger shared pool when doing this activity." Anyone has more info on this. TIA Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tél. (514) 925-7187 [EMAIL PROTECTED]
Re: Sql*Loader problem...
JL, When you describe the field type in the SQLLoader file set the CHAR(4000) as the field description. You are correct in saying that the default is CHAR(255). As an example; col1 terminated by "," , col2 CHAR(4000) terminated by "," ... Ron >>> [EMAIL PROTECTED] 03/13/03 01:44PM >>> Hi... I would like to receive a bit of your help (I've been looking at Metalink with no luck). I'm having a sql*loader problem when I try to insert records in a table field. The problem is: my data file has a column with MORE THAN 255 characters long, and sql*loader rejects the records that exceed that limit! The data are generated by an app called NetCache. And, for auditing, we need to load the data into the oracle database. I'm having problems with just one column: URL_VISITED varchar2(4000) Any ideas, help would be appreciated!. I need to load a string like this: (A workaround could be SUBSTRac just the first 255 chars ) 'http://www.hotmail.msn.com/cgi-bin/sbox?did=1&t=4zO *OzYH5DI!K5JDg2lw3GV*Z17S74coVgj5bSbi3Idhx6CPvVsK3Eh bHes!GVxjYRKdxmfIAmoF1SvuqW0Vd5rQ$$&p=4wcx4xWmyGq6jd OZdt9566bmc4SziYsk6XKA60n*bkWwcK3UuKf8dzl1ykTjzYsAhK YN8wfQ6ay2rrR1FSRXj*djcyllOvUfV5H8drR6iESnR6PEig6lqH Cv7iH*opeUYbDd4IlZxxwRC6pqrKos5CLOW2iSBE5fp4L2g71b7E LgER0F!Eiw0XdUZsVKN!a17yI*bRtievO7lRyJefsNcjcA$$&js=yes' Regards! JL __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
9IAS connections to database not resolving
About a month ago we had a small group of remote users that started using our 9IAS server to gain access to an online application. Previously these users were like the rest of our remote users using our 6i forms and reports servers. The group of users had to switch to 9ias because tunneling through their particular firewalls was not supported by Oracle's 6i forms and reports servers. Since the users have switched to 9ias I have had lots of trouble managing sessions. Every time a 9ias user prints a report(the sole purpose of the application actually) two database sessions are created. The sessions are not cleaned up. Eventually we either run out of available sessions(max_sessions) or our shared pool fills up. I already have a sqlnet.expire_time set in sqlnet.ora, and have implemented resouce_limit monitoring with an idle_time for our remote users. Neither of these have solved the problem. The reason being that sessions that are killed due to IDLE_TIME are 'sniped', and 'sniped' sessions are never cleaned up. They continue to exist, and consume resources. I have temporarily forced 9ias to connect via dedicated servers. This will allow me to clean up 'sniped' sessions by killing their server processes at the OS level. The above work around is the result of the notes Oracle Support provided. Unfortunately they have not been much help otherwise. Meaning they have not been able or willing to address the problem of 9ias creating the spurious sessions. Sorry for the long tale, but I am hoping someone else out there has run into this before. Our forms/reports configuration is way to generic for us to be the first ones to hit this. Thanks in advance, Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
High current mode buffer gets on insert
I'm looking at a client's tkprof output, showing among other things that the insertion of about 135,000 rows taking 450 seconds of CPU, and with current mode buffer gets numbering almost 800,000. This is a daily warehouse load process, and I know that indexes are left in place during the load. Am I correct in concluding that the high CPU, and especially the current mode block gets numbering over 6 times the number of rows inserted, are due to index updates? Thanks! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Corrected SQL Question...
Questions I would have for those who wrote the requirements: Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? As can be seen from the answers sent to the list, there is more than one set of responses that give this pattern. If they only want "half" of the possible patterns, which half is the correct half? -- 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).
RE: Is async IO configured on HP-UX?
The HP-UX syscall tracing tool is called 'tusc', and it's supposed to be available by visiting ftp://ftp.cup.hp.com/dist/networking/misc/. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Thursday, March 13, 2003 12:36 PM To: Multiple recipients of list ORACLE-L Ethan - it has been a while and we don't have an HP-UX box here, but if memory serves the missing /dev/async is a dead giveaway that your not using async io. to use async io on HP-UX we had to install something called the asyncdisk driver in SAM and then use mknod to create /dev/async directory. could the asyncdisk driver already be installed and you just need the directory? SAM should tell you. if not i think it means a kernel rebuild. -Original Message- Sent: Thursday, March 13, 2003 10:44 AM To: Multiple recipients of list ORACLE-L /dev does not have an async directory, async IO is turned on in the database, how do I determine if async IO is activated on the OS side of the house? Is the missing async directory an indicator? Also, is the database smart enough to figure out that the OS is not using async and make correct call or will I get a timeout on async call then perform standard write? Thanks, Ethan -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be 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: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: db_file_multiblock_read_count + db file scattered read +
I would expect the pread64() calls to map to "db file sequential read" and the readv() calls to map to "db file scattered read". Jeff's "Why are Oracle's Read Events 'Named Backwards'?" explains (www.hotsos.com/catalog). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Ildefonso N Sent: Thursday, March 13, 2003 12:44 PM To: Multiple recipients of list ORACLE-L Thanks Wolfgang, Cary for taking the time to answer my first question. After taking a second look on the hotsos doc, it did in fact address my problem, specifically the third bullet point. I simply missed to apply the pattern, shown in the example for the third point, to my situation. As for pread64/readv, after looking at the whole truss output, I did not find a single read using readv() that exceeded 128K, although, I found some pread64() that were less than 128K, which I am guessing is due to "db file sequential reads". -Original Message- Sent: Wednesday, March 12, 2003 10:14 PM To: Multiple recipients of list ORACLE-L My explanation is that blocks 1473, 2566, 2580, 2590, 2617, 2628, 2648, 2681, 2695, 2702, 2714, 2719, 2748, 2760, 2773, 2794, 2798, 2811, and 2819 were already in the buffer pool and the FTS reads "around" them. At 08:08 PM 3/12/2003 -0800, you wrote: >I have been wondering how to set the optimal value of dfmbc >(db_file_multiblock_read_count), filesystem pagesize/blocksize, db blk >size, in an effort to optimize FTS. > >I have done testing using event 10046 and truss to find the p3 value on the >db file scattered then comparing it with the truss output. It seems that the >value in trace file corelates to the truss value (pread64/readv), but I am >having trouble trying to explain why lines 12 and 21 on both output files >are not using the dfmbc setting of 128 (1M) on the fetch? Also, why is truss >showing pread64 and then readv on lines 24..32? I have read the hotsos doc >"Predicting Multi-Block Read Call Sizes" (by Jeff Holt) to get some >understanding on how dfmbc affects database I/O. It did not answer cover, >though my situation, because the object does not cross extent boundary. > >Any answers would be highly appreciated. Your collective knowledge would >surely help. Any info to other docs or links would also be valuable. > >Here is the output of event 10046, truss and other info of the test env. > > 1 WAIT #3: nam='db file scattered read' ela= 6 p1=10 p2=10 p3=128 > 2 WAIT #3: nam='db file scattered read' ela= 9 p1=10 p2=138 p3=128 > 3 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=266 p3=128 > 4 WAIT #3: nam='db file scattered read' ela= 3 p1=10 p2=394 p3=128 > 5 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=522 p3=128 > 6 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=650 p3=128 > 7 WAIT #3: nam='db file scattered read' ela= 5 p1=10 p2=778 p3=128 > 8 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=906 p3=128 > 9 WAIT #3: nam='db file scattered read' ela= 6 p1=10 p2=1034 p3=128 > 10 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1162 p3=128 > 11 WAIT #3: nam='db file scattered read' ela= 3 p1=10 p2=1290 p3=128 > 12 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=1418 p3=55 > 13 WAIT #3: nam='db file scattered read' ela= 6 p1=10 p2=1474 p3=128 > 14 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1602 p3=128 > 15 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=1730 p3=128 > 16 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1858 p3=128 > 17 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1986 p3=128 > 18 WAIT #3: nam='db file scattered read' ela= 5 p1=10 p2=2114 p3=128 > 19 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=2242 p3=128 > 20 WAIT #3: nam='db file scattered read' ela= 3 p1=10 p2=2370 p3=128 > 21 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=2498 p3=68 > 22 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2567 p3=13 > 23 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2581 p3=9 > 24 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2591 p3=26 > 25 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2618 p3=10 > 26 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2629 p3=19 > 27 WAIT #3: nam='db file scattered read' ela= 9 p1=10 p2=2649 p3=32 > 28 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2682 p3=13 > 29 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2696 p3=6 > 30 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2703 p3=11 > 31 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2715 p3=4 > 32 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2720 p3=28 > 33 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2749 p3=11 > 34 WAIT #3: nam='db file scattered re
RE: a DIFFERENT sql question
ok - i came up with a solution. but in real life i have a lot of amount1's in t1 so it becomes an ugly brute force looking query. anybody have a more elegant solution? 1 select a.category 2 , (select sum(s.amount1) from t1 s where a.category = s.category) as amount1sum 3 , b.type 4 , sum(b.amount2) 5from t1 a 6 , t2 b 7 where a.mykey1 = b.mykey1 8 group by 9 a.category 10* , b.type SQL> / CATEG AMOUNT1SUM TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 8 y 6 AA 8 z 10 BB50 y 27 4 rows selected. SQL> -Original Message- Sent: Thursday, March 13, 2003 10:49 AM To: Multiple recipients of list ORACLE-L since we're having fun with SQL today - here's one that's hurting my brain at the moment. I need to sum columns at 2 different groupings in my resultset. The first select is perfect; the 2nd is where i have trouble; but i know i can do stuff like the 3rd example. How do I get the 2nd one to work? SQL SCRIPT: drop table t1; drop table t2; create table t1 (mykey1 number(5), category varchar2(5), amount1 number(5)); create table t2 (mykey2 number(5), mykey1 number(5), type varchar2(5), amount2 number(5)); insert into t1 values (1, 'AA', 5); insert into t1 values (2, 'AA', 3); insert into t1 values (3, 'BB', 50); insert into t2 values (1, 1, 'x', 1); insert into t2 values (2, 1, 'x', 2); insert into t2 values (3, 1, 'y', 6); insert into t2 values (4, 2, 'x', 4); insert into t2 values (5, 2, 'z', 10); insert into t2 values (6, 2, 'x', 20); insert into t2 values (7, 3, 'y', 12); insert into t2 values (8, 3, 'y', 15); select a.category , a.mykey1 , sum(distinct a.amount1) , b.type , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by a.category , a.mykey1 , a.amount1 , b.type / select a.category -- , a.mykey1 , sum(distinct a.amount1) , b.type , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by a.category -- , a.mykey1 -- , a.amount1 , b.type / select decode(grouping(a.category), 1, 'All', a.category) as category , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job , decode(grouping(b.type), 1, 'All', b.type) as type , count(*) , sum(distinct a.amount1) , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by rollup ( a.category , a.mykey1 , b.type ) order by a.category , a.mykey1 , b.type / here's what i get: SQL> select a.category 2 , a.mykey1 3 , sum(distinct a.amount1) 4 , b.type 5 , sum(b.amount2) 6from t1 a 7 , t2 b 8 where a.mykey1 = b.mykey1 9 group by 10 a.category 11 , a.mykey1 12 , a.amount1 13 , b.type 14 / CATEG MYKEY1 SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- -- - -- AA 1 5 x 3 AA 1 5 y 6 AA 2 3 x 24 AA 2 3 z 10 BB 3 50 y 27 5 rows selected. perfect. but this is the problem query: SQL> select a.category 2 -- , a.mykey1 3 , sum(distinct a.amount1) 4 , b.type 5 , sum(b.amount2) 6from t1 a 7 , t2 b 8 where a.mykey1 = b.mykey1 9 group by 10 a.category 11 -- , a.mykey1 12 -- , a.amount1 13 , b.type 14 / CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 5 y 6 AA 3 z 10 BB50 y 27 4 rows selected. wrong. i want the resultset to look like this: CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 8 y 6 AA 8 z 10 BB50 y 27 then this is cool, but not what i want: SQL> select decode(grouping(a.category), 1, 'All', a.category) as category 2 , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job 3 , decode(grouping(b.type), 1, 'All', b.type) as type 4 , count(*) 5 , sum(distinct a.amount1) 6 , sum(b.amount2) 7from t1 a 8 , t2 b 9 where a.mykey1 = b.mykey1 10 group by rollup 11 ( a.category 12 , a.mykey1 13 , b.type 14 ) 15 order by 16 a.category 17 , a.mykey1 18 , b.typ
Re: fgac tracing
Murali, Perusing the oraus.msg file it appears that the 10730 event will do what you want. There are a lot of interesting events in there. :) Jared "Murali Vallath" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 03/13/2003 06:39 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:fgac tracing I remember seeing someone mention the event number to tracing a session to capture fine grained access control activity. Does anyone remember! Thanks Murali Vallath _ Add photos to your messages 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: Murali Vallath INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Corrected SQL Question...
Title: RE: Corrected SQL Question...
Jacques,
Thanks a bunch.
Elegance was not one of the requirements ;)
Cheers!
-
Kirti
-Original Message-From: Jacques Kilchoer
[mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003
12:53 PMTo: '[EMAIL PROTECTED]'Cc: Deshpande,
KirtiSubject: RE: Corrected SQL Question...
(see answer below)
> -Original Message- >
From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]
> > Here is the test data:
> > SQL> select c1,c2
from cp; > > C1
C2 > --- --- > AUS
DAL > AUS HOU > DAL
AUS > DAL HOU > DAL
LIT > DAL XYZ > HOU
AUS > HOU DAL > HOU
LIT > HOU XYZ > LIT
DAL > > C1
C2 > --- --- > LIT
HOU > XYZ DAL > XYZ
HOU > > 14 rows
selected. > > SQL>
> > Here is what is
required: > > C1
C2 > --- --- > AUS
DAL > DAL AUS > AUS
HOU > HOU AUS > DAL
HOU > HOU DAL > DAL
LIT > LIT DAL > DAL
XYZ > XYZ DAL > HOU
LIT > LIT HOU > HOU
XYZ > XYZ HOU
This is not very elegant, but it works: SQL> select * from cp ; C1 C2
--- --- AUS DAL AUS HOU DAL AUS DAL
HOU DAL LIT DAL XYZ
HOU AUS HOU DAL HOU LIT HOU XYZ LIT
DAL LIT HOU XYZ DAL
XYZ HOU 14 ligne(s)
sélectionnée(s).
SQL> -- desired result SQL>
select 2 least (a.c1,
a.c2) || greatest (a.c1, a.c2) as sort_field, a.c1, a.c2 3 from cp a
4 where not exists
5 (select * from cp b
6 where b.c2 = a.c1 and b.c1 = a.c2 and
a.c1 > b.c1) 7 union 8 select
9 least (c.c1, c.c2) || greatest (c.c1, c.c2) as sort_field,
d.c1, d.c2 10 from
11 cp c, cp d
12 where 13 c.c1 = d.c2 and c.c2 = d.c1 and
c.c1 < d.c1 14 order by 1, 2 ;
SORT_F C1 C2 -- ---
--- AUSDAL AUS DAL AUSDAL DAL
AUS AUSHOU AUS HOU AUSHOU HOU
AUS DALHOU DAL HOU DALHOU HOU
DAL DALLIT DAL LIT DALLIT LIT
DAL DALXYZ DAL XYZ DALXYZ XYZ
DAL HOULIT HOU LIT HOULIT LIT
HOU HOUXYZ HOU XYZ HOUXYZ XYZ
HOU 14 ligne(s) sélectionnée(s).
create table cp (c1 varchar2 (3), c2
varchar2 (3)) ; insert into cp values ('AUS', 'DAL')
; insert into cp values ('AUS', 'HOU') ;
insert into cp values ('DAL', 'AUS') ; insert into cp values ('DAL', 'HOU') ; insert
into cp values ('DAL', 'LIT') ; insert into cp values
('DAL', 'XYZ') ; insert into cp values ('HOU', 'AUS')
; insert into cp values ('HOU', 'DAL') ;
insert into cp values ('HOU', 'LIT') ; insert into cp values ('HOU', 'XYZ') ; insert
into cp values ('LIT', 'DAL') ; insert into cp values
('LIT', 'HOU') ; insert into cp values ('XYZ', 'DAL')
; insert into cp values ('XYZ', 'HOU') ;
commit ; select * from cp ;
-- desired result select
least (a.c1, a.c2) || greatest (a.c1, a.c2) as
sort_field, a.c1, a.c2 from cp a
where not exists
(select * from cp b where
b.c2 = a.c1 and b.c1 = a.c2 and a.c1 > b.c1) union select
least (c.c1, c.c2) || greatest (c.c1, c.c2) as sort_field, d.c1, d.c2
from cp c,
cp d where c.c1 = d.c2 and c.c2 = d.c1 and c.c1 <
d.c1 order by 1, 2 ;
RE: Corrected SQL Question...
Do mean something like this? It would be interesting to see if this could be done with some kind of tree walk. 1* select a.c1, a.c2, b.c1, b.c2 from crap a, crap b where a.c2 = b.c1 and b.c2 = a.c1 SQL> / C1C2C1C2 - - - - DAL AUS AUS DAL HOU AUS AUS HOU AUS DAL DAL AUS HOU DAL DAL HOU LIT DAL DAL LIT XYZ DAL DAL XYZ AUS HOU HOU AUS DAL HOU HOU DAL LIT HOU HOU LIT XYZ HOU HOU XYZ DAL LIT LIT DAL HOU LIT LIT HOU DAL XYZ XYZ DAL HOU XYZ XYZ HOU 14 rows selected. -- 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).
RE: High consistent gets , 10046
Thank you, Anjo. I want to provide more information about this case: - In both cases binding is inside PL/SQL block, bind variable type is correct. this must eliminate reasons 1 and 2 - different bind variables - yes, almost for sure they are different, but this is PK, must make no difference. - contention for cache chain latches is high I'm looking at Cary's article "Why you should focus on LIO... " Looks like this count may only be caused by undo lookups. It still looks too high, since hot blocks must not age out too fast unless Oracle is too aggressive is keeping number of copies low. Not sure how Oracle counts access to different block versions im memory at 10046, assuming as one access. I'm heading to collect more information on cache chain latches and block counts in x$bh and send it out . Thanks again, Vadim -Original Message- Sent: Thursday, March 13, 2003 12:04 PM To: Multiple recipients of list ORACLE-L I can think of three reasons: 1) You are binding with the wrong datatype and you are getting a full table access for the SELECT and then the rowid is remembered for the FOR UPDATE (results in 1 current get). 2) You are implicitly using array fetch in sqlplus, so the number of cr gets will be lower but given the fact that current gets is 1 in both cases, you can ignore this. 3) Different bind variable values. Anjo. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, March 13, 2003 4:24 PM > Dear listers, > > I'm hunting for top LIO consumers to give a relief to our DB cpu and found > something that looks interesting. > > Many plain good queries show up way to high cr when executed in concurrent > environment (50 threads) while perform as predicted when executed from > SQL*PLUS. > > The example below is select by primary key, PK index height is 1. > > Trace taken in concurrent env shows cr=152 > > = > PARSING IN CURSOR #136 len=86 dep=1 uid=65 oct=3 lid=65 tim=1022957016971691 > hv=941708176 ad='61f780e8' > SELECT samp_ver > FROM sub_svc > WHERE sub_svc_id = :b1 >FOR UPDATE > END OF STMT > PARSE #136:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1022957016971679 > EXEC > #136:c=0,e=2185,p=0,cr=152,cu=1,mis=0,r=0,dep=1,og=4,tim=1022957016974087 > FETCH #136:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1022957016974208 > > > When tested from SQL*plus prompt (server is idle), is falls to resonable > cr=3 > > = > PARSING IN CURSOR #3 len=77 dep=1 uid=65 oct=3 lid=65 tim=1023016395834410 > hv=3412082965 ad='6344f6cc' > SELECT samp_ver > FROM sub_svc > WHERE sub_svc_id = :b1 >FOR UPDATE > END OF STMT > PARSE #3:c=0,e=626,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1023016395834397 > EXEC #3:c=0,e=936,p=0,cr=3,cu=1,mis=0,r=0,dep=1,og=4,tim=1023016395835517 > FETCH #3:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1023016395835612 > = > > As you may see, the different is quite essential. > > Does anybody have an idea why is so? > > This is 9.2.0.2 on Solaris > > TIA > > Vadim G > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Gorbounov,Vadim > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be 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: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gorbounov,Vadim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of
RE: Corrected SQL Question...
Title: RE: Corrected SQL Question...
(see answer below)
> -Original Message-
> From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]
>
> Here is the test data:
>
> SQL> select c1,c2 from cp;
>
> C1 C2
> --- ---
> AUS DAL
> AUS HOU
> DAL AUS
> DAL HOU
> DAL LIT
> DAL XYZ
> HOU AUS
> HOU DAL
> HOU LIT
> HOU XYZ
> LIT DAL
>
> C1 C2
> --- ---
> LIT HOU
> XYZ DAL
> XYZ HOU
>
> 14 rows selected.
>
> SQL>
>
> Here is what is required:
>
> C1 C2
> --- ---
> AUS DAL
> DAL AUS
> AUS HOU
> HOU AUS
> DAL HOU
> HOU DAL
> DAL LIT
> LIT DAL
> DAL XYZ
> XYZ DAL
> HOU LIT
> LIT HOU
> HOU XYZ
> XYZ HOU
This is not very elegant, but it works:
SQL> select * from cp ;
C1 C2
--- ---
AUS DAL
AUS HOU
DAL AUS
DAL HOU
DAL LIT
DAL XYZ
HOU AUS
HOU DAL
HOU LIT
HOU XYZ
LIT DAL
LIT HOU
XYZ DAL
XYZ HOU
14 ligne(s) sélectionnée(s).
SQL> -- desired result
SQL> select
2 least (a.c1, a.c2) || greatest (a.c1, a.c2) as sort_field, a.c1, a.c2
3 from cp a
4 where not exists
5 (select * from cp b
6 where b.c2 = a.c1 and b.c1 = a.c2 and a.c1 > b.c1)
7 union
8 select
9 least (c.c1, c.c2) || greatest (c.c1, c.c2) as sort_field, d.c1, d.c2
10 from
11 cp c, cp d
12 where
13 c.c1 = d.c2 and c.c2 = d.c1 and c.c1 < d.c1
14 order by 1, 2 ;
SORT_F C1 C2
-- --- ---
AUSDAL AUS DAL
AUSDAL DAL AUS
AUSHOU AUS HOU
AUSHOU HOU AUS
DALHOU DAL HOU
DALHOU HOU DAL
DALLIT DAL LIT
DALLIT LIT DAL
DALXYZ DAL XYZ
DALXYZ XYZ DAL
HOULIT HOU LIT
HOULIT LIT HOU
HOUXYZ HOU XYZ
HOUXYZ XYZ HOU
14 ligne(s) sélectionnée(s).
create table cp
(c1 varchar2 (3), c2 varchar2 (3)) ;
insert into cp values ('AUS', 'DAL') ;
insert into cp values ('AUS', 'HOU') ;
insert into cp values ('DAL', 'AUS') ;
insert into cp values ('DAL', 'HOU') ;
insert into cp values ('DAL', 'LIT') ;
insert into cp values ('DAL', 'XYZ') ;
insert into cp values ('HOU', 'AUS') ;
insert into cp values ('HOU', 'DAL') ;
insert into cp values ('HOU', 'LIT') ;
insert into cp values ('HOU', 'XYZ') ;
insert into cp values ('LIT', 'DAL') ;
insert into cp values ('LIT', 'HOU') ;
insert into cp values ('XYZ', 'DAL') ;
insert into cp values ('XYZ', 'HOU') ;
commit ;
select * from cp ;
-- desired result
select
least (a.c1, a.c2) || greatest (a.c1, a.c2) as sort_field, a.c1, a.c2
from cp a
where not exists
(select * from cp b
where b.c2 = a.c1 and b.c1 = a.c2 and a.c1 > b.c1)
union
select
least (c.c1, c.c2) || greatest (c.c1, c.c2) as sort_field, d.c1, d.c2
from
cp c, cp d
where
c.c1 = d.c2 and c.c2 = d.c1 and c.c1 < d.c1
order by 1, 2 ;
RE: High consistent gets , 10046
Thank you, Jonathan, I'll continue looking for my options to cool down the hot spots. Not sure if I can go for partitioning since Oracle charges $$$. Is it correct that oracle counts looking through the chain for the correct copy as many CR? Or the reason for these extra CR is access to undo segments in attemt to reconstruct CR block aged out from cache? Thanks Vadim -Original Message- Sent: Thursday, March 13, 2003 12:19 PM To: Multiple recipients of list ORACLE-L If you can check it in real time, you will probably find that you have a very large number of CR copies of the few blocks that are the focus of the concurrent activity. The excess time is likely to be down to a mixture of CPU as Oracle trawls through the chain looking for the correct copy, and latch contention because of the time the latch has to be held whilst the correct copy is being found. 'select for update ...' seems to be particularly prone to this problem - especially if you have an over large db_cache_size, that allows for lots of blocks in state 'FREE'. (even a reasonably size buffer can produce this effect if there is a process elsewhere which is dropping or truncating objects on a regular basis). You may be able to reduce the impact of the problem by spreading out the rows that need to be updated - e.g. by increasing the number of freelists, or hash partitioning the table. If this is a relatively small, static sized, table moving it to a single table hash cluster may help. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 13 March 2003 15:24 > Dear listers, > > I'm hunting for top LIO consumers to give a relief to our DB cpu and found > something that looks interesting. > > Many plain good queries show up way to high cr when executed in concurrent > environment (50 threads) while perform as predicted when executed from > SQL*PLUS. > -- 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: Gorbounov,Vadim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: db_file_multiblock_read_count + db file scattered read +
Thanks Wolfgang, Cary for taking the time to answer my first question. After taking a second look on the hotsos doc, it did in fact address my problem, specifically the third bullet point. I simply missed to apply the pattern, shown in the example for the third point, to my situation. As for pread64/readv, after looking at the whole truss output, I did not find a single read using readv() that exceeded 128K, although, I found some pread64() that were less than 128K, which I am guessing is due to "db file sequential reads". -Original Message- Sent: Wednesday, March 12, 2003 10:14 PM To: Multiple recipients of list ORACLE-L My explanation is that blocks 1473, 2566, 2580, 2590, 2617, 2628, 2648, 2681, 2695, 2702, 2714, 2719, 2748, 2760, 2773, 2794, 2798, 2811, and 2819 were already in the buffer pool and the FTS reads "around" them. At 08:08 PM 3/12/2003 -0800, you wrote: >I have been wondering how to set the optimal value of dfmbc >(db_file_multiblock_read_count), filesystem pagesize/blocksize, db blk >size, in an effort to optimize FTS. > >I have done testing using event 10046 and truss to find the p3 value on the >db file scattered then comparing it with the truss output. It seems that the >value in trace file corelates to the truss value (pread64/readv), but I am >having trouble trying to explain why lines 12 and 21 on both output files >are not using the dfmbc setting of 128 (1M) on the fetch? Also, why is truss >showing pread64 and then readv on lines 24..32? I have read the hotsos doc >"Predicting Multi-Block Read Call Sizes" (by Jeff Holt) to get some >understanding on how dfmbc affects database I/O. It did not answer cover, >though my situation, because the object does not cross extent boundary. > >Any answers would be highly appreciated. Your collective knowledge would >surely help. Any info to other docs or links would also be valuable. > >Here is the output of event 10046, truss and other info of the test env. > > 1 WAIT #3: nam='db file scattered read' ela= 6 p1=10 p2=10 p3=128 > 2 WAIT #3: nam='db file scattered read' ela= 9 p1=10 p2=138 p3=128 > 3 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=266 p3=128 > 4 WAIT #3: nam='db file scattered read' ela= 3 p1=10 p2=394 p3=128 > 5 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=522 p3=128 > 6 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=650 p3=128 > 7 WAIT #3: nam='db file scattered read' ela= 5 p1=10 p2=778 p3=128 > 8 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=906 p3=128 > 9 WAIT #3: nam='db file scattered read' ela= 6 p1=10 p2=1034 p3=128 > 10 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1162 p3=128 > 11 WAIT #3: nam='db file scattered read' ela= 3 p1=10 p2=1290 p3=128 > 12 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=1418 p3=55 > 13 WAIT #3: nam='db file scattered read' ela= 6 p1=10 p2=1474 p3=128 > 14 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1602 p3=128 > 15 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=1730 p3=128 > 16 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1858 p3=128 > 17 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1986 p3=128 > 18 WAIT #3: nam='db file scattered read' ela= 5 p1=10 p2=2114 p3=128 > 19 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=2242 p3=128 > 20 WAIT #3: nam='db file scattered read' ela= 3 p1=10 p2=2370 p3=128 > 21 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=2498 p3=68 > 22 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2567 p3=13 > 23 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2581 p3=9 > 24 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2591 p3=26 > 25 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2618 p3=10 > 26 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2629 p3=19 > 27 WAIT #3: nam='db file scattered read' ela= 9 p1=10 p2=2649 p3=32 > 28 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2682 p3=13 > 29 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2696 p3=6 > 30 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2703 p3=11 > 31 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2715 p3=4 > 32 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2720 p3=28 > 33 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2749 p3=11 > 34 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2761 p3=12 > 35 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2774 p3=20 > 36 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=2795 p3=3 > 37 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=2799 p3=12 > 38 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=2812 p3=7 > 39 WAIT #3: nam='db file scattered read' ela= 5 p1=10 p2=2820 p3=128 > 40 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=2948 p3=128 Wolfgang Breitling
Re: pl/sql and java script ???
Janet, Try the forums at http://java.sun.com or inquire at http://devtrends.oracle.com Darrell >>> [EMAIL PROTECTED] 03/13/03 10:59AM >>> Hi, Our app is strange. :-( We use pl/sql(9i) package to create all the html and java script. I have two drop down boxes on a form, the values for the second box changes dynamically depends on the value of the first box. The values for the boxes are from cursors written in pl/sql. We currently resubmit the form after the first box is clicked. How to handle this without resubmitting the form? How to let java script function read data from pl/sql cursors?? PS: If you know an email list or metalink like resource for J2EE and/or Java script, please let me know!!! Thank you in advance. Janet __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Sql*Loader problem...
Hi... I would like to receive a bit of your help (I've been looking at Metalink with no luck). I'm having a sql*loader problem when I try to insert records in a table field. The problem is: my data file has a column with MORE THAN 255 characters long, and sql*loader rejects the records that exceed that limit! The data are generated by an app called NetCache. And, for auditing, we need to load the data into the oracle database. I'm having problems with just one column: URL_VISITED varchar2(4000) Any ideas, help would be appreciated!. I need to load a string like this: (A workaround could be SUBSTRac just the first 255 chars ) 'http://www.hotmail.msn.com/cgi-bin/sbox?did=1&t=4zO *OzYH5DI!K5JDg2lw3GV*Z17S74coVgj5bSbi3Idhx6CPvVsK3Eh bHes!GVxjYRKdxmfIAmoF1SvuqW0Vd5rQ$$&p=4wcx4xWmyGq6jd OZdt9566bmc4SziYsk6XKA60n*bkWwcK3UuKf8dzl1ykTjzYsAhK YN8wfQ6ay2rrR1FSRXj*djcyllOvUfV5H8drR6iESnR6PEig6lqH Cv7iH*opeUYbDd4IlZxxwRC6pqrKos5CLOW2iSBE5fp4L2g71b7E LgER0F!Eiw0XdUZsVKN!a17yI*bRtievO7lRyJefsNcjcA$$&js=yes' Regards! JL __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is async IO configured on HP-UX?
Ethan - it has been a while and we don't have an HP-UX box here, but if memory serves the missing /dev/async is a dead giveaway that your not using async io. to use async io on HP-UX we had to install something called the asyncdisk driver in SAM and then use mknod to create /dev/async directory. could the asyncdisk driver already be installed and you just need the directory? SAM should tell you. if not i think it means a kernel rebuild. -Original Message- Sent: Thursday, March 13, 2003 10:44 AM To: Multiple recipients of list ORACLE-L /dev does not have an async directory, async IO is turned on in the database, how do I determine if async IO is activated on the OS side of the house? Is the missing async directory an indicator? Also, is the database smart enough to figure out that the OS is not using async and make correct call or will I get a timeout on async call then perform standard write? Thanks, Ethan -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Corrected SQL Question...
Not quite random. Note that the value is field 1 of the first record is the value in field 2 in the second. It looks like they want to pair up the cities if they appear in both columns. i.e. Since Dallas is in column 1 with Austin in Column 2 in one record, and Dallas is in Column 2 with Austin in column 1 in a seperate record, they want those records to follow each other. Could be a cleanup effort ... duplicate but reversed data -Original Message- Sent: Thursday, March 13, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Kirti, Can you explain the required result order? It looks random to me - or like one of the "tests" we were forced to take in High School. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, March 13, 2003 9:31 AM To: Multiple recipients of list ORACLE-L Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL> select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL> Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl Book
http://www.oreilly.com/catalog/oracleperl/ Play your cards right and you may even get an autographed copy. ;o) Dave -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003 8:04 AMTo: Multiple recipients of list ORACLE-LSubject: OTC: Perl Book Looks like I have a need write a Perl program to access a database. Any suggestions on a good book. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204
RE: Corrected SQL Question...
Tom, They wanted to 'pair up' the contents from c1 and c2. Those are supposed to be 3 char Airport codes. DAL-AUS followed by AUS-DAL (or vice-versa). That's all I was told. Thanks. - Kirti -Original Message- Sent: Thursday, March 13, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Kirti, Can you explain the required result order? It looks random to me - or like one of the "tests" we were forced to take in High School. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, March 13, 2003 9:31 AM To: Multiple recipients of list ORACLE-L Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL> select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL> Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Corrected SQL Question...
Kirti, Can you explain the required result order? It looks random to me - or like one of the "tests" we were forced to take in High School. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, March 13, 2003 9:31 AM To: Multiple recipients of list ORACLE-L Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL> select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL> Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Q] need benchmark info. for ORACLE seerver?
There is the website of www.tpc.org HOWEVER (AHA!!), you should be warned that Microsoft will *ALWAYS* spend the necessary cash to put together a big enough cluster to get the highest score. So, I can tell you, without looking at the site for months now, that Microsoft will have the highest score. If you can, try to steer your management away from too much emphasis on benchmarks. A system either has satisfactory performance for your application, or it does not. If the performance is satisfactory, then factors such as long-term stability, ability to upgrade without pain, vendor support, third-party support, technical support, etc. are VERY important and should not get lost in the benchmark shuffle. I've not had to deal with hardware vendors for a few years now. But in the past, Sun's vendor support and third-party support were vastly superior to others even when Sun's benchmarks weren't the best. Sun does a pretty good job of allowing you to move to a later version of the OS without breaking a bunch of stuff (which Windows does NOT do!). > -Original Message- > > We are study on our next ORACLE servers. Before we > can commit the management that SUN/ORACLE server is > the best choice, I need get some benchmarks info to > compare SUN with other systems (NT, LINUX, ..). Does > anyone have information like that? If you have > Benchmark info. include SUN/ORACLE compare with other > systems, it will even better. > > Thanks. > -- 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).
DBXray anyone?
Anyone using BMC's DBXRay? Is it a decent product? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Suzy Vordos INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Excessive SQL*Net message from client waits
I'd start by being doubtful about anybody being able to work so fast that the can avoid a high percentage of time in 'sql*net from client' - in fact, it the percentage was low (when the client was a person at a terminal) I would write myself a memo to check whether the client code was executing an extreme number of very small statements behind the scenes (e.g. get all keys for a drop-down, then get each drop down by key one at a time every time the user hit the field). It's always possible that the many layers of code at the client end are taking a surprising However, assuming you have a truly unreasonable loss of time on waiting for client - I would try and isolate the problem by using netstat and top. This can be hard in typical environments, though. Start up the client session - Start netstat running on the server with minimum snapshot time (usually one second). Start top (or similar) running in real time or minimum snapshot time. Start up event 10046 at the session. Then get the client to do something, and watch for: a)the peak in netstat as the request reaches the server. b)the burst from the server as the request is serviced c)the peak in netstat as the reply gets sent d)the delay before it appears on the client screen. It's crude, but simple-minded, and if the client is causing the problem it may prove it quite convincingly. Back it up with the trace file - which will record timestamps of a query coming in and results going out. The biggest problem, usually, is that it simply isn't realistic to get a system so quiet that you can get just one client running all by itself with nothing else going on. In your particular case, I have to sya that I have noticed that Java can use a surprising amount of CPU sometimes. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 13 March 2003 15:54 > Good point, but what if each user only has a single session? > > Not that I've noticed this exact same situation here on one of our > Engineering support databases whose clients are Java, and I'm not wondering > if it has something to do with the application or if I can possibly speed it > up with tweaks to SDU/TDU. I'm just wondering... ;) > -- 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).
Re: tkprof output
Repeat but changing the 12 to a zero should work. You will find, however, that any cursor that has not closed when you stop tracing will not dump its 'STAT' lines (including execution plan) to the trace file. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 13 March 2003 15:49 > I use sys.dbms_system.set_ev( v_seid ,v_sernum ,10046,12 ,'') to start > tracing on 8.1.6 db . What should I do to stop tracing without exiting out > of session . > > Thanks, > -ak > > -- 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).
RE: fgac tracing
Title: RE: fgac tracing http://www.oracleadvice.com/Tips/FGAC.htm Event 10730 (trace row level security policy predicates) can be used to dump the rewritten SQL to a trace file in the user_dump_dest. Be aware that this only happens when the cursor is reparsed. Oracle does not reparse cursors automatically from version 9.0 onwards so you must remember to execute the dbms_rls.refresh_policy procedure as the table/policy owner before enabling the trace. Set the event as normal with an alter session command: ALTER SESSION SET EVENTS '10730 trace name context forever, level 12'; > -Original Message- > From: Murali Vallath [mailto:[EMAIL PROTECTED]] > > I remember seeing someone mention the event number to tracing > a session to > capture fine grained access control activity. Does anyone remember!
RE: Corrected SQL Question...
I think those solutions should be acceptable. Not sure if they are displaying any more information from the table. I was just given the test table to get the SQL script working Thanks a lot. - Kirti -Original Message- Sent: Thursday, March 13, 2003 11:04 AM To: Multiple recipients of list ORACLE-L I think its easier if you do it cross-tab AUS DAL DAL AUS Is that acceptable? Or just select AUS DAL If it also has a DAL AUS Are either of those metods acceptable? If so, pick one and Ill show you how to do it. > > From: "Deshpande, Kirti" <[EMAIL PROTECTED]> > Date: 2003/03/13 Thu AM 11:19:15 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Corrected SQL Question... > > Okay, let me do this right this time,... (Now that I have my hot tea going;) > > Here is the test data: > > SQL> select c1,c2 from cp; > > C1 C2 > --- --- > AUS DAL > AUS HOU > DAL AUS > DAL HOU > DAL LIT > DAL XYZ > HOU AUS > HOU DAL > HOU LIT > HOU XYZ > LIT DAL > > C1 C2 > --- --- > LIT HOU > XYZ DAL > XYZ HOU > > 14 rows selected. > > SQL> > > Here is what is required: > > C1 C2 > --- --- > AUS DAL > DAL AUS > AUS HOU > HOU AUS > DAL HOU > HOU DAL > DAL LIT > LIT DAL > DAL XYZ > XYZ DAL > HOU LIT > LIT HOU > HOU XYZ > XYZ HOU > > > I think I am clear now... > Sorry about the wrong test data earlier... > > > Thanks, > > - Kirti > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: tkprof output
sys.dbms_system.set_ev( v_seid, v_sernum, 10046, 0, '' ) ^ Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Thursday, March 13, 2003 9:49 AM To: Multiple recipients of list ORACLE-L I use sys.dbms_system.set_ev( v_seid ,v_sernum ,10046,12 ,'') to start tracing on 8.1.6 db . What should I do to stop tracing without exiting out of session . Thanks, -ak - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, March 12, 2003 9:07 AM > An action with dep=n+1 (n>=0) for cursor #k is the recursive child of the > next dep=n action for cursor #k that immediately follows in the trace data. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic 101, Mar 25-27 Oxford > - Hotsos Clinic 101, Apr 8-10 Chicago > > > -Original Message- > Sent: Wednesday, March 12, 2003 10:16 AM > To: Multiple recipients of list ORACLE-L > > Thanks Jonathan, > what is meaning of recursive depth ? I see calls to cdef$, seq$ > tables/views does it hint something . I though procedure is using some > sequence and these are internal calls to generate seq numbers . Is that rite > ? > > -ak > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, March 11, 2003 2:54 PM > > > > > > Any SQL within a pl/sql block is recursive SQL > > (user recursive, rather than SYS recursive) so > > this time could simply be the cost of running > > your application code. > > > > Unfortunately the tkprof output doesn't quote > > the recursive depth of the SQL - however if > > you identify possible suspects, you can check > > back in the raw trace file for lines like: > > PARSING IN CURSOR #N > > there will be a bit in the line like > > dep=n > > If n is not zero, then this is a 'recursive' > > cursor. > > > > Regards > > > > Jonathan Lewis > > http://www.jlcomp.demon.co.uk > > > > Now available One-day tutorials: > > Cost Based Optimisation > > Trouble-shooting and Tuning > > Indexing Strategies > > > > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > > > UK___March 19th > > UK___April 8th > > UK___April 22nd > > > > USA_(FL)_May 2nd > > > > > > Next dates for the 3-day seminar: > > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > > > UK_(Manchester)_May > > USA_(CA, TX)_August > > > > > > The Co-operative Oracle Users' FAQ > > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: 11 March 2003 19:29 > > > > > > > I am running tkprof on event 10046 output and I see at the end . > > elapsed > > > time of 18 secs in recursive calls thats what surprises me . Do you > > know > > > what I should look at next and what can be done to reduce these > > timings > > > > > > Thanks, > > > -ak > > > > > > > > > > > > > > > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS > > > > > > call count cpuelapsed disk querycurrent > > > rows > > > --- -- -- -- -- -- > > -- > > > > > > Parse 26 0.08 0.10 3 26 0 > > > 0 > > > Execute 37 0.04 0.08 0 4 6 > > > 2 > > > Fetch 32 1.13 2.14 5079 5189 70 > > > 30 > > > --- -- -- -- -- -- > > -- > > > > > > total 95 1.25 2.32 5082 5219 76 > > > 32 > > > > > > Misses in library cache during parse: 23 > > > > > > > > > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS > > > > > > call count cpuelapsed disk querycurrent > > > rows > > > --- -- -- -- -- -- > > -- > > > > > > Parse 41 0.03 0.05 0 0 0 > > > 0 > > > Execute541 4.40 12.30 1051 6442 63782 > > > 38712 > > > Fetch 550 3.23 6.31 3977 24298340 > > > 419 > > > --- -- -- -- -- -- > > -- > > > > > > total 1132 7.66 18.66 5028 30740 64122 > > > 39131 > > > > > > Misses in library cache during parse: 9 > > > > > > 164 user SQL statements in session. > > >41 internal SQL statements in session. > > > 205 SQL statements in session. > > > 0 statements EXPLAINed in this session. > > > > > ** > > ** > > > > > > Trace file: ora_28633_ak.trc > > > Trace file compatibility: 8.00.04 > > > Sort opt
RE: Is async IO configured on HP-UX?
How about truss/strace equivalent on HP-UX on dbwr to see whether its using AIO or not. If AIO fails then it will follow by a normal IO call. Richard -Original Message- Sent: Thursday, March 13, 2003 8:44 AM To: Multiple recipients of list ORACLE-L /dev does not have an async directory, async IO is turned on in the database, how do I determine if async IO is activated on the OS side of the house? Is the missing async directory an indicator? Also, is the database smart enough to figure out that the OS is not using async and make correct call or will I get a timeout on async call then perform standard write? Thanks, Ethan -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: fgac tracing
Murali: It is event 10730. You can set that at session level. = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Corrected SQL Question...
Title: Re: Corrected SQL Question... SQL> select A.c1, B.c2 2 from (select col1 c1, rownum r from tbl order by col1) A 3 , (select col2 c2, rownum r from tbl order by col2) b 4 where a.r = b.r 5 union 6 select B.c2, A.c1 7 from (select col1 c1, rownum r from tbl order by col1) A 8 , (select col2 c2, rownum r from tbl order by col2) b 9 where a.r = b.r 10 order by 1 11 / C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL LIT HOU XYZ DAL XYZ HOU At 08:19 AM 3/13/2003 -0800, you wrote: >AUS DAL >AUS HOU >DAL AUS >DAL HOU >DAL LIT >DAL XYZ >HOU AUS >HOU DAL >HOU LIT >HOU XYZ >LIT DAL > >C1 C2 >--- --- >LIT HOU >XYZ DAL >XYZ HOU Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications. Cette communication par courrier électronique est une communication privée à l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'êtes pas le destinataire prévu, vous êtes avisé, par les présentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'êtes pas spécifiquement autorisé à recevoir ce courriel ou si vous croyez l'avoir reçu par erreur, veuillez en aviser l'expéditeur original immédiatement. Nous respectons les demandes similaires qui touchent la confidentialité des communications par courrier électronique.
Re: High consistent gets , 10046
If you can check it in real time, you will probably find that you have a very large number of CR copies of the few blocks that are the focus of the concurrent activity. The excess time is likely to be down to a mixture of CPU as Oracle trawls through the chain looking for the correct copy, and latch contention because of the time the latch has to be held whilst the correct copy is being found. 'select for update ...' seems to be particularly prone to this problem - especially if you have an over large db_cache_size, that allows for lots of blocks in state 'FREE'. (even a reasonably size buffer can produce this effect if there is a process elsewhere which is dropping or truncating objects on a regular basis). You may be able to reduce the impact of the problem by spreading out the rows that need to be updated - e.g. by increasing the number of freelists, or hash partitioning the table. If this is a relatively small, static sized, table moving it to a single table hash cluster may help. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 13 March 2003 15:24 > Dear listers, > > I'm hunting for top LIO consumers to give a relief to our DB cpu and found > something that looks interesting. > > Many plain good queries show up way to high cr when executed in concurrent > environment (50 threads) while perform as predicted when executed from > SQL*PLUS. > -- 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).
Re: Corrected SQL Question...
I think its easier if you do it cross-tab AUS DAL DAL AUS Is that acceptable? Or just select AUS DAL If it also has a DAL AUS Are either of those metods acceptable? If so, pick one and Ill show you how to do it. > > From: "Deshpande, Kirti" <[EMAIL PROTECTED]> > Date: 2003/03/13 Thu AM 11:19:15 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Corrected SQL Question... > > Okay, let me do this right this time,... (Now that I have my hot tea going;) > > Here is the test data: > > SQL> select c1,c2 from cp; > > C1 C2 > --- --- > AUS DAL > AUS HOU > DAL AUS > DAL HOU > DAL LIT > DAL XYZ > HOU AUS > HOU DAL > HOU LIT > HOU XYZ > LIT DAL > > C1 C2 > --- --- > LIT HOU > XYZ DAL > XYZ HOU > > 14 rows selected. > > SQL> > > Here is what is required: > > C1 C2 > --- --- > AUS DAL > DAL AUS > AUS HOU > HOU AUS > DAL HOU > HOU DAL > DAL LIT > LIT DAL > DAL XYZ > XYZ DAL > HOU LIT > LIT HOU > HOU XYZ > XYZ HOU > > > I think I am clear now... > Sorry about the wrong test data earlier... > > > Thanks, > > - Kirti > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Deshpande, Kirti > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.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).
RE: DBV Cannot Load Module LIBDCE.A
Sam, I've never worked in a DCE environment, but here's a couple of things to verify: 1. If libdce.a is not installed in the first place, is your environment using DCE? I think this is an LPP (a separately licensed product) from IBM for AIX. Try the following from a unix prompt: $ lslpp -L |grep -i dce I think you should get hits like dce.client.rte.pthreads If you don't, then I wonder why dbv thinks it needs it... Try the following just to make sure you're running the dbv you think you are: $ which dbv It should return $ORACLE_HOME/bin/dbv, for example: $ which dbv /u01/app/oracle/product/8.1.7/bin/dbv If it returns the Oracle 'dbv', then you might check the $ORACLE_HOME/rdbms/lib/sysliblist to see if it has an entry for libdce. If so, the product was linked at some point with the DCE Advanced Networking Option (see MetaLink Note 1043700.6 This doc notes the following: NOTE: You may have to manually remove the library reference from "sysliblist". In Oracle 7.3.4, the Installer did not remove the library reference during a deinstallation.) In any case, I would be slow to add PTFs or relink or make other changes in a production environment just for the sake of dbv (if the rest of the environment is stable) until I had a better understanding of the opsys environment and the Oracle installation history. HTH, Scott >>> [EMAIL PROTECTED] 3/12/03 5:15:58 PM >>>Hi all,First, thanks Jared for the info.The libdce.a file does not exist on any of our AIX servers. It justAINT there!I typed libdce.a into google and from there downloaded PTF2,3, and 4that are supposed contain DCE fixes to this file. Instructions say torename the old file and replace it with this new file. After the download, I was not able to uncompress the files on my XPworkstation. So I ftp'd the file (in binary mode) to our AIX server andused uncompress to expand the files to libdce2.exe, libdce3.exe,libdce4.exe (originally the files were named libdce2.exe.Z, etc.). The.exe extension confused me (I am told .exe is Windows only, not UNIX). I ftp'd the files back to my workstation, and tried running it from DOS.I get an error message "Program too big to fit in memory". However, theexe is only 217,856 bytes (not kilobytes). My next step was to ask myneighbour if he could unzip the original libdce2.exe.Z on his W2Kworkstation - he was successful! However, he received the same messagewhen he attempted to run the resultant .exe on his workstation. I am relatively new to UNIX (took a course, and did some reading, butnot much hands-on). I am told by a more experience colleague that.exe's do not run on UNIX. So now I am at a loss on what to try next. Any ideas?Thanks,Sam BootsmaGeorge Brown College[EMAIL PROTECTED]416-415-5000 x4933-Original Message-Sent: March 11, 2003 7:14 PMTo: Multiple recipients of list ORACLE-LJust found it: on AIX ( 4.3 at least ) it's LIBPATH[EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]03/11/2003 03:23 PMPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L<[EMAIL PROTECTED]> cc: Subject: Re: DBV Cannot Load Module LIBDCE.AMake sure LD_LIBRARY_PATH includes /usr/lib.At least, I think it's LD_LIBRARY_PATH, been awhilesince doing anything on AIX. In any case, libdce.ashould be in /usr/lib.JaredPS. Google is your friend"Sam Bootsma" <[EMAIL PROTECTED]>Sent by: [EMAIL PROTECTED]03/11/2003 01:44 PMPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: DBV Cannot Load Module LIBDCE.AWe are running Oracle 7.3.4.5.0 on an IBM/AIX RISC System/6000: Version 2.3.4.0.0. When I attempt to run DBV I get the following error messages$ dbv help=yexec(): 0509-036 Cannot load program dbv because of the followingerrors: 0509-150 Dependent module libdce.a(shr.o) could not be loaded. 0509-022 Cannot load module libdce.a(shr.o). 0509-026 System error: A file or directory in the path name doesnot exist.$I do not want to play around with a custom installation to try toinstall missing components because this is a production database and because Iam new here. The regular DBA is on holidays and not available. I did a search for file "libdce" on the original CD for "Oracle7.3.4.0.0 for AIX based systems", but did not find it there. I considered installing Oracle 7.3.4 on my workstation and ftp'ing the files over thenetwork to my workstation. Then doing the database verify. But total size of the data files is 25 GB, and this would unnecessarily clog upour network (even at night).Any ideas on how I can resolve the DBV problem not loading problem? Theshort answer is to find the module libdce.a file and any modulesdependent on this, but how do I do this?Thanks,Sam [EMAIL PROTECTED]
RE: Perl Book
Do you know if this book covers the Perl basics or does it assume you know Perl
Already.
>>> "Larry Hahn" <[EMAIL PROTECTED]> 3/13/03 9:39:02 AM >>>
st1\:*{behavior:url(#default#ieooui) }Jeffrey, I am emailing you direct because my
reply to the list is not going through. I have heard a lot about the following
book:Perl for Oracle DBAs By Andy Duncan, Jared Still I have not read it myself, but
it comes highly recommended to me. Larry HahnDatabase ManagerJournal Sentinel, Inc333
W. State St.Milwaukee, Wi 53201 -Original Message-
Sent: Thursday, March 13, 2003 8:01 AM
To: Multiple recipients of IOUGA-VMS-L
Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
(216) 781-4204
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jeffrey Beckstrom
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Is async IO configured on HP-UX?
/dev does not have an async directory, async IO is turned on in the database, how do I determine if async IO is activated on the OS side of the house? Is the missing async directory an indicator? Also, is the database smart enough to figure out that the OS is not using async and make correct call or will I get a timeout on async call then perform standard write? Thanks, Ethan -- 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).
pl/sql and java script ???
Hi, Our app is strange. :-( We use pl/sql(9i) package to create all the html and java script. I have two drop down boxes on a form, the values for the second box changes dynamically depends on the value of the first box. The values for the boxes are from cursors written in pl/sql. We currently resubmit the form after the first box is clicked. How to handle this without resubmitting the form? How to let java script function read data from pl/sql cursors?? PS: If you know an email list or metalink like resource for J2EE and/or Java script, please let me know!!! Thank you in advance. Janet __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Excessive SQL*Net message from client waits
Title: RE: Excessive SQL*Net message from client waits This is an idle wait event that just means the server is waiting to be given some work from the client. Looks to me like you won't be needing to do any tuning on this database. -Original Message- From: Karen Morton [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 12, 2003 8:19 PM To: Multiple recipients of list ORACLE-L Subject: Excessive SQL*Net message from client waits Hi All, I've got a situation where I've collected trace data and am seeing 90% of total response time is accounted for with the SQL*Net Message From Client event. Individual queries within the trace show minimal CPU time used and no obvious indications of bad SQL being the culprit. I used the Hotsos Profiler (way cool) and here's an example of what it shows: Response Time Component Duration # Calls Avg Min Max -- --- --- -- --- (i) SQL*Net message from client 500.98s 85.1% 2,757 0.181712s 0.00s 5.91s (i) unaccounted-for 23.03s 3.9% (i) direct path write 22.38s 3.8% 1,373 0.016300s 0.00s 0.32s (i) log file sync 20.70s 3.5% 685 0.030219s 0.00s 0.52s (i) user-mode CPU 12.12s 2.1% 12,016 0.001009s 0.00s 1.50s (i) direct path read 6.66s 1.1% 985 0.006761s 0.00s 0.09s (i) db file sequential read 1.09s 0.2% 2,679 0.000407s 0.00s 0.14s (i) db file scattered read 0.83s 0.1% 2,158 0.000385s 0.00s 0.17s (i) SQL*Net more data to client 0.50s 0.1% 1,007 0.000497s 0.00s 0.13s (i) SQL*Net more data from client 0.42s 0.1% 5 0.084000s 0.01s 0.19s (i) db file parallel read 0.11s 0.0% 44 0.002500s 0.00s 0.01s (i) latch free 0.10s 0.0% 30 0.00s 0.00s 0.02s (i) file open 0.01s 0.0% 8 0.001250s 0.00s 0.01s (i) SQL*Net message to client 0.00s 0.0% 2,757 0.00s 0.00s 0.00s -- --- --- -- --- Total 588.93s 100.0% If you want to see the whole profile please check at www.morton-consulting.com/pdfs/sqlnetwaitstrace.pdf. By the way, this single trace is one of 15 that was done and all show the same SQL*Net waits being, on average, 90% or above of the total time. The "network guys" did some testing and came back saying that the network couldn't possibly be the problem (do they ever?). Here's what they said: "There are 0 Symptoms and Diagnoses that occur from Physical layer up to network layer. In running the trace file in loopback mode using the packet generator function - I noticed that the average % of utilization across the network was under 10% utilization. There were only few spikes that reached 10% and only two at 20%. All spikes seen lasted no more than two seconds. In analysis of your packet size distribution, it was a perfect bell curved. In fact, 64 byte sized packets (broadcasts and unicasts) were only 4th in the number of occurrences (very good!). Traffic captured showed the mac layer path clearly through cabinet K and out to the Oracle TNS server. At the network layer, you only had IP traffic between the client and the Oracle TNS server. You had 267 connection layer Symptoms between the Oracle TNS server and the client. 263 of which were Ack Too Long. All were because of the Oracle TNS Server which had very poor Ack times. The client's Ack's averaged 2ms. You had 1 Windows Frozen and 3 Retransmissions. At the session layer you had 2 Diagnoses and 4 Symptoms - all related to the Oracle TNS Server." Another test I did was to do a continuous ping from the server to one of the connected sessions and saved the results to a file. I saw some some minor spikes over the period of 30 minutes or so but nothing significant. The application is not the most efficient in the world (to put it mildly) as it doesn't use bind variables (at all) and changing the app at this point is not an option. The app is in part written in C++ and in part in Delphi. Every SQL statement is built as a concatenated string and then passed to the database. I know bind variable usage would help, particularly with all the hard parsing but if I can't make app changes, then what? Another thing the app does is that for every individual user that logs in, multiple sessions will be spawned. For a given single user who is logged in when you look in v$session, you see as many as 10 sessions for that process. So, in effect, 20 users
RE: Perl Book
I have heard some raving reviews for the book Perl for Oracle DBAs By Andy Duncan, Jared Still August 2002 0-596-00210-6, Order Number: 2106 [Jack van Zanen] -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]Sent: donderdag 13 maart 2003 15:04To: Multiple recipients of list ORACLE-LSubject: OTC: Perl Book Looks like I have a need write a Perl program to access a database. Any suggestions on a good book. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204
Corrected SQL Question...
Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL> select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL> Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: High consistent gets , 10046
I can think of three reasons: 1) You are binding with the wrong datatype and you are getting a full table access for the SELECT and then the rowid is remembered for the FOR UPDATE (results in 1 current get). 2) You are implicitly using array fetch in sqlplus, so the number of cr gets will be lower but given the fact that current gets is 1 in both cases, you can ignore this. 3) Different bind variable values. Anjo. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, March 13, 2003 4:24 PM > Dear listers, > > I'm hunting for top LIO consumers to give a relief to our DB cpu and found > something that looks interesting. > > Many plain good queries show up way to high cr when executed in concurrent > environment (50 threads) while perform as predicted when executed from > SQL*PLUS. > > The example below is select by primary key, PK index height is 1. > > Trace taken in concurrent env shows cr=152 > > = > PARSING IN CURSOR #136 len=86 dep=1 uid=65 oct=3 lid=65 tim=1022957016971691 > hv=941708176 ad='61f780e8' > SELECT samp_ver > FROM sub_svc > WHERE sub_svc_id = :b1 >FOR UPDATE > END OF STMT > PARSE #136:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1022957016971679 > EXEC > #136:c=0,e=2185,p=0,cr=152,cu=1,mis=0,r=0,dep=1,og=4,tim=1022957016974087 > FETCH #136:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1022957016974208 > > > When tested from SQL*plus prompt (server is idle), is falls to resonable > cr=3 > > = > PARSING IN CURSOR #3 len=77 dep=1 uid=65 oct=3 lid=65 tim=1023016395834410 > hv=3412082965 ad='6344f6cc' > SELECT samp_ver > FROM sub_svc > WHERE sub_svc_id = :b1 >FOR UPDATE > END OF STMT > PARSE #3:c=0,e=626,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1023016395834397 > EXEC #3:c=0,e=936,p=0,cr=3,cu=1,mis=0,r=0,dep=1,og=4,tim=1023016395835517 > FETCH #3:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1023016395835612 > = > > As you may see, the different is quite essential. > > Does anybody have an idea why is so? > > This is 9.2.0.2 on Solaris > > TIA > > Vadim G > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Gorbounov,Vadim > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be 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: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OTC: Perl Book
Title: RE: OTC: Perl Book Perl for Oracle DBAs is a good one. I picked one up at RMOUG. It's so good I've put down the Harry Potter book I was reading. --Walt Weaver Bozeman, Montana > -Original Message- > From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] > Sent: Thursday, March 13, 2003 8:44 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: OTC: Perl Book > > > Jared Still, the list owner, has one out: > > Perl for Oracle DBAs from O'Reilly press > > > --- Jeffrey Beckstrom <[EMAIL PROTECTED]> wrote: > > Looks like I have a need write a Perl program to access a database. > > Any suggestions on a good book. > > > > > > > > Jeffrey Beckstrom > > Database Administrator > > Greater Cleveland Regional Transit Authority > > 1240 W. 6th Street > > Cleveland, Ohio 44113 > > (216) 781-4204 > > > > > __ > Do you Yahoo!? > Yahoo! Web Hosting - establish your business online > http://webhosting.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). >
RE: How to migrate the LDAP data into Oracle 8.1.7 database table
How automatic do you need this? One way of doing it is to use ldapsearch to extract the data you need from LDAP, convert the output into a single-line-per-user CSV using Perl (not Korn, not Python, not C -- JUST PERL!), and use SQL*Loader to pipe it into the DB. Something that could be run daily perhaps... Just one possibility. Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Wednesday, March 12, 2003 3:14 PM To: Multiple recipients of list ORACLE-L Hi, We need to bring the LDAP data into oracle tables under 8.1.7(Not 9i) and join with some other tables, which already exist in the Oracle 8.1.7 database to generate reports. I was wondering if we can use some tool to reverse engineer the LDAP data ad get table structures. Any idea. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: tkprof output
sys.dbms_system.set_ev( v_seid ,v_sernum ,10046, 0,'') Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, March 13, 2003 10:49 AM > I use sys.dbms_system.set_ev( v_seid ,v_sernum ,10046,12 ,'') to start > tracing on 8.1.6 db . What should I do to stop tracing without exiting out > of session . > > Thanks, > -ak > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, March 12, 2003 9:07 AM > > > > An action with dep=n+1 (n>=0) for cursor #k is the recursive child of the > > next dep=n action for cursor #k that immediately follows in the trace > data. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Hotsos Clinic 101, Mar 25-27 Oxford > > - Hotsos Clinic 101, Apr 8-10 Chicago > > > > > > -Original Message- > > Sent: Wednesday, March 12, 2003 10:16 AM > > To: Multiple recipients of list ORACLE-L > > > > Thanks Jonathan, > > what is meaning of recursive depth ? I see calls to cdef$, seq$ > > tables/views does it hint something . I though procedure is using some > > sequence and these are internal calls to generate seq numbers . Is that > rite > > ? > > > > -ak > > > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Tuesday, March 11, 2003 2:54 PM > > > > > > > > > > Any SQL within a pl/sql block is recursive SQL > > > (user recursive, rather than SYS recursive) so > > > this time could simply be the cost of running > > > your application code. > > > > > > Unfortunately the tkprof output doesn't quote > > > the recursive depth of the SQL - however if > > > you identify possible suspects, you can check > > > back in the raw trace file for lines like: > > > PARSING IN CURSOR #N > > > there will be a bit in the line like > > > dep=n > > > If n is not zero, then this is a 'recursive' > > > cursor. > > > > > > Regards > > > > > > Jonathan Lewis > > > http://www.jlcomp.demon.co.uk > > > > > > Now available One-day tutorials: > > > Cost Based Optimisation > > > Trouble-shooting and Tuning > > > Indexing Strategies > > > > > > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > > > > > UK___March 19th > > > UK___April 8th > > > UK___April 22nd > > > > > > USA_(FL)_May 2nd > > > > > > > > > Next dates for the 3-day seminar: > > > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > > > > > UK_(Manchester)_May > > > USA_(CA, TX)_August > > > > > > > > > The Co-operative Oracle Users' FAQ > > > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > > > > > > - Original Message - > > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > > Sent: 11 March 2003 19:29 > > > > > > > > > > I am running tkprof on event 10046 output and I see at the end . > > > elapsed > > > > time of 18 secs in recursive calls thats what surprises me . Do you > > > know > > > > what I should look at next and what can be done to reduce these > > > timings > > > > > > > > Thanks, > > > > -ak > > > > > > > > > > > > > > > > > > > > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS > > > > > > > > call count cpuelapsed disk querycurrent > > > > rows > > > > --- -- -- -- -- -- > > > -- > > > > > > > > Parse 26 0.08 0.10 3 26 0 > > > > 0 > > > > Execute 37 0.04 0.08 0 4 6 > > > > 2 > > > > Fetch 32 1.13 2.14 5079 5189 70 > > > > 30 > > > > --- -- -- -- -- -- > > > -- > > > > > > > > total 95 1.25 2.32 5082 5219 76 > > > > 32 > > > > > > > > Misses in library cache during parse: 23 > > > > > > > > > > > > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS > > > > > > > > call count cpuelapsed disk querycurrent > > > > rows > > > > --- -- -- -- -- -- > > > -- > > > > > > > > Parse 41 0.03 0.05 0 0 0 > > > > 0 > > > > Execute541 4.40 12.30 1051 6442 63782 > > > > 38712 > > > > Fetch 550 3.23 6.31 3977 24298340 > > > > 419 > > > > --- -- -- -- -- -- > > > -- > > > > > > > > total 1132 7.66 18.66 5028 30740 64122 > > > > 39131 > > > > > > > > Misses in library cache during parse: 9 > > > > > > > > 164 user SQL statements in session. > > > >41 internal SQL statements in session. > > > > 205 SQL statements in session. > > > > 0 statements EXPLAINed in this session. > > > > > > >
a DIFFERENT sql question
since we're having fun with SQL today - here's one that's hurting my brain at the moment. I need to sum columns at 2 different groupings in my resultset. The first select is perfect; the 2nd is where i have trouble; but i know i can do stuff like the 3rd example. How do I get the 2nd one to work? SQL SCRIPT: drop table t1; drop table t2; create table t1 (mykey1 number(5), category varchar2(5), amount1 number(5)); create table t2 (mykey2 number(5), mykey1 number(5), type varchar2(5), amount2 number(5)); insert into t1 values (1, 'AA', 5); insert into t1 values (2, 'AA', 3); insert into t1 values (3, 'BB', 50); insert into t2 values (1, 1, 'x', 1); insert into t2 values (2, 1, 'x', 2); insert into t2 values (3, 1, 'y', 6); insert into t2 values (4, 2, 'x', 4); insert into t2 values (5, 2, 'z', 10); insert into t2 values (6, 2, 'x', 20); insert into t2 values (7, 3, 'y', 12); insert into t2 values (8, 3, 'y', 15); select a.category , a.mykey1 , sum(distinct a.amount1) , b.type , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by a.category , a.mykey1 , a.amount1 , b.type / select a.category -- , a.mykey1 , sum(distinct a.amount1) , b.type , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by a.category -- , a.mykey1 -- , a.amount1 , b.type / select decode(grouping(a.category), 1, 'All', a.category) as category , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job , decode(grouping(b.type), 1, 'All', b.type) as type , count(*) , sum(distinct a.amount1) , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by rollup ( a.category , a.mykey1 , b.type ) order by a.category , a.mykey1 , b.type / here's what i get: SQL> select a.category 2 , a.mykey1 3 , sum(distinct a.amount1) 4 , b.type 5 , sum(b.amount2) 6from t1 a 7 , t2 b 8 where a.mykey1 = b.mykey1 9 group by 10 a.category 11 , a.mykey1 12 , a.amount1 13 , b.type 14 / CATEG MYKEY1 SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- -- - -- AA 1 5 x 3 AA 1 5 y 6 AA 2 3 x 24 AA 2 3 z 10 BB 3 50 y 27 5 rows selected. perfect. but this is the problem query: SQL> select a.category 2 -- , a.mykey1 3 , sum(distinct a.amount1) 4 , b.type 5 , sum(b.amount2) 6from t1 a 7 , t2 b 8 where a.mykey1 = b.mykey1 9 group by 10 a.category 11 -- , a.mykey1 12 -- , a.amount1 13 , b.type 14 / CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 5 y 6 AA 3 z 10 BB50 y 27 4 rows selected. wrong. i want the resultset to look like this: CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 8 y 6 AA 8 z 10 BB50 y 27 then this is cool, but not what i want: SQL> select decode(grouping(a.category), 1, 'All', a.category) as category 2 , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job 3 , decode(grouping(b.type), 1, 'All', b.type) as type 4 , count(*) 5 , sum(distinct a.amount1) 6 , sum(b.amount2) 7from t1 a 8 , t2 b 9 where a.mykey1 = b.mykey1 10 group by rollup 11 ( a.category 12 , a.mykey1 13 , b.type 14 ) 15 order by 16 a.category 17 , a.mykey1 18 , b.type 19 / CATEG JOB TYPECOUNT(*) SUM(DISTINCTA.AMOUNT1) SUM(B.AMOUNT2) - - - -- -- -- AA1 x 2 5 3 AA1 y 1 5 6 AA1 All3 5 9 AA2 x 2 3 24 AA2 z 1 3 10 AA2 All3 3 34 AAAll All6 8 43 BB3 y 2 50 27 BB3 All2 50 27 BBAll All2 50 27 All All
Re: A SQL Question
Title: Re: A SQL Question SQL> select A.c1, B.c2 2 from (select col1 c1, rownum r from tbl order by col1) A 3 , (select col2 c2, rownum r from tbl order by col2) b 4 where a.r = b.r 5 union all 6 select B.c2, A.c1 7 from (select col1 c1, rownum r from tbl order by col1) A 8 , (select col2 c2, rownum r from tbl order by col2) b 9 where a.r = b.r 10 order by 1 11 / C C - - A B B A C D D C E F F E G H H G At 05:23 AM 3/13/2003 -0800, you wrote: >Hi SQL Developers, > >I have a table as follows: > >Col1 Col2 > >A B >C D >E F >G H >B A >E F >C D >H G > >With a PK on (Col1, Col2). > >How do I write a SQL script to get following result? > >Col1 Col2 > >A B >B A >C D >D C >E F >F E >G H >H G > >Thanks for your help. > >- Kirti Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications. Cette communication par courrier électronique est une communication privée à l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'êtes pas le destinataire prévu, vous êtes avisé, par les présentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'êtes pas spécifiquement autorisé à recevoir ce courriel ou si vous croyez l'avoir reçu par erreur, veuillez en aviser l'expéditeur original immédiatement. Nous respectons les demandes similaires qui touchent la confidentialité des communications par courrier électronique.
redhat as/es/ws
Is there any hint from oracle support on these new offerings: Raleigh, NC -- March 12, 2003 -- Red Hat, Inc. (Nasdaq:RHAT), the world's premier open source and Linux provider, announced the availability of two new enterprise offerings: Red Hat Enterprise Linux ES and Red Hat Enterprise Linux WS. Designed to be 100% compatible with successful Red Hat Enterprise Linux AS (Advanced Server), the new Red Hat solutions enable organizations to deploy complete company-wide Linux solutions, from the corporate workstations to the datacenter. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Corrected SQL Question...
Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL> select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL> Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Excessive SQL*Net message from client waits
Good point, but what if each user only has a single session? Not that I've noticed this exact same situation here on one of our Engineering support databases whose clients are Java, and I'm not wondering if it has something to do with the application or if I can possibly speed it up with tweaks to SDU/TDU. I'm just wondering... ;) Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, March 13, 2003 7:44 AM To: Multiple recipients of list ORACLE-L But if one user spawns 10 sessions, then whilst one session is being 'clicked' the other 9 are idle - no matter how fast the user is being bounced from one to the next - so on average every session is going to be waiting for "SQL*Net message from client" 90% of the time. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 13 March 2003 11:53 > Not like this nor should it be the "top" event always as seems to be the > case here I don't believe. And, I know for certain that the client did > everything as quickly as possible during the trace. Minimal data entry done > and OK buttons clicked without delay...no time out for getting a cup of > coffee in between or anything. :) > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
AW: help -- ora 600
Hi JP With the newest drivers (ojdbc14.jar) for 9.2 it works. Thanks a lot. Regards Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Prem Khanna J [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 13. März 2003 10:44 An: Multiple recipients of list ORACLE-L Betreff: Re: help -- ora 600 Stefan , hope you don't have the right version of classes12.zip in your classpath. if it is not so , u r likely to get ORA-600 even on a simple SELECT. just set that right. HTH. 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Select * from my_table order by col1; -Original Message- Sent: Thursday, March 13, 2003 7:24 AM To: Multiple recipients of list ORACLE-L Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Igor (and all): Yes, our SPAM Cops and their filters are very strict with the wording in the e-mail footers. Unfortunately, FatCity.com uses the footer that gets caught by these filters. When replying to me directly, using list message, you need to remove the old footers from the e-mail. Sorry about this little problem. I will post my Corrected SQL Question again... Thanks. - Kirti -Original Message- Sent: Thursday, March 13, 2003 9:04 AM To: Multiple recipients of list ORACLE-L Kirti, I tried to reply to your direct e-mail, but your mail-server is very strict and considered my message to be "Unsolicited Bulk Email". What I was trying to say is: Oracle-l list behaves very strangely (sometimes), I'm still waiting to see corrected version of your question. And actually I suspected, that the question isn't that simple -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, March 13, 2003 8:23 AM > Hi SQL Developers, > > I have a table as follows: > > Col1 Col2 > > AB > CD > EF > GH > BA > EF > CD > HG > > With a PK on (Col1, Col2). > > How do I write a SQL script to get following result? > > Col1Col2 > > AB > BA > CD > DC > EF > FE > G H > H G > > Thanks for your help. > > - Kirti > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: tkprof output
also how can we check if one particular session is being traced (10046 ) or not . Basically I want to be sure that tracing is stopped for the session and its not fillling up disk space . -ak - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, March 12, 2003 9:07 AM > An action with dep=n+1 (n>=0) for cursor #k is the recursive child of the > next dep=n action for cursor #k that immediately follows in the trace data. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic 101, Mar 25-27 Oxford > - Hotsos Clinic 101, Apr 8-10 Chicago > > > -Original Message- > Sent: Wednesday, March 12, 2003 10:16 AM > To: Multiple recipients of list ORACLE-L > > Thanks Jonathan, > what is meaning of recursive depth ? I see calls to cdef$, seq$ > tables/views does it hint something . I though procedure is using some > sequence and these are internal calls to generate seq numbers . Is that rite > ? > > -ak > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, March 11, 2003 2:54 PM > > > > > > Any SQL within a pl/sql block is recursive SQL > > (user recursive, rather than SYS recursive) so > > this time could simply be the cost of running > > your application code. > > > > Unfortunately the tkprof output doesn't quote > > the recursive depth of the SQL - however if > > you identify possible suspects, you can check > > back in the raw trace file for lines like: > > PARSING IN CURSOR #N > > there will be a bit in the line like > > dep=n > > If n is not zero, then this is a 'recursive' > > cursor. > > > > Regards > > > > Jonathan Lewis > > http://www.jlcomp.demon.co.uk > > > > Now available One-day tutorials: > > Cost Based Optimisation > > Trouble-shooting and Tuning > > Indexing Strategies > > > > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > > > UK___March 19th > > UK___April 8th > > UK___April 22nd > > > > USA_(FL)_May 2nd > > > > > > Next dates for the 3-day seminar: > > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > > > UK_(Manchester)_May > > USA_(CA, TX)_August > > > > > > The Co-operative Oracle Users' FAQ > > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: 11 March 2003 19:29 > > > > > > > I am running tkprof on event 10046 output and I see at the end . > > elapsed > > > time of 18 secs in recursive calls thats what surprises me . Do you > > know > > > what I should look at next and what can be done to reduce these > > timings > > > > > > Thanks, > > > -ak > > > > > > > > > > > > > > > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS > > > > > > call count cpuelapsed disk querycurrent > > > rows > > > --- -- -- -- -- -- > > -- > > > > > > Parse 26 0.08 0.10 3 26 0 > > > 0 > > > Execute 37 0.04 0.08 0 4 6 > > > 2 > > > Fetch 32 1.13 2.14 5079 5189 70 > > > 30 > > > --- -- -- -- -- -- > > -- > > > > > > total 95 1.25 2.32 5082 5219 76 > > > 32 > > > > > > Misses in library cache during parse: 23 > > > > > > > > > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS > > > > > > call count cpuelapsed disk querycurrent > > > rows > > > --- -- -- -- -- -- > > -- > > > > > > Parse 41 0.03 0.05 0 0 0 > > > 0 > > > Execute541 4.40 12.30 1051 6442 63782 > > > 38712 > > > Fetch 550 3.23 6.31 3977 24298340 > > > 419 > > > --- -- -- -- -- -- > > -- > > > > > > total 1132 7.66 18.66 5028 30740 64122 > > > 39131 > > > > > > Misses in library cache during parse: 9 > > > > > > 164 user SQL statements in session. > > >41 internal SQL statements in session. > > > 205 SQL statements in session. > > > 0 statements EXPLAINed in this session. > > > > > ** > > ** > > > > > > Trace file: ora_28633_ak.trc > > > Trace file compatibility: 8.00.04 > > > Sort options: default > > > > > >1 session in tracefile. > > > 164 user SQL statements in trace file. > > > 41 internal SQL statements in trace file. > > > 205 SQL statements in trace file. > > > 167 unique SQL statements in trace file. > > > 5369 lines in trace file. > > > > > > > > > > > > -- > > > Please see the of
RE: Sun=/var/messages HP-UX=???
/var/adm/syslog/syslog.log is the hp-ux equivelant. -Original Message- Sent: Thursday, March 13, 2003 12:59 AM To: Multiple recipients of list ORACLE-L I monitor /var/messages on my Sun boxes, does HP-UX have anytype of OS log files worth monitoring? Thanks, Ethan -- 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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Perl Book
The book is not a tutorial for Perl, but the first chapter includes "What is Perl?", so not *too* much is assumed. The appendices are very good for beginners. containing four 'Essential Guides' to Perl. Jared On Thursday 13 March 2003 06:54, Jeffrey Beckstrom wrote: > Do you know if this book covers the Perl basics or does it assume you know > Perl Already. > > >>> "Larry Hahn" <[EMAIL PROTECTED]> 3/13/03 9:39:02 AM >>> > > Jeffrey, > > I am emailing you direct because my reply to the list is not going through. > > I have heard a lot about the following book: > Perl for Oracle DBAs > By Andy Duncan, Jared Still > > I have not read it myself, but it comes highly recommended to me. > > Larry Hahn > Database Manager > Journal Sentinel, Inc > 333 W. State St. > Milwaukee, Wi 53201 > > > -Original Message- > Sent: Thursday, March 13, 2003 8:01 AM > To: Multiple recipients of IOUGA-VMS-L > > Looks like I have a need write a Perl program to access a database. Any > suggestions on a good book. > > > > Jeffrey Beckstrom > Database Administrator > Greater Cleveland Regional Transit Authority > 1240 W. 6th Street > Cleveland, Ohio 44113 > (216) 781-4204 > --=ED1CD27.57364D29--* This message is coming from the IOUGA VMS > mailing list at resource.to. To unsubscribe, please send an e-mail to > [EMAIL PROTECTED] with UNSUBSCRIBE IOUGA-VMS-L in the body. > Alternatively, you can send a message to [EMAIL PROTECTED], > which will automatically unsubscribe the account from which the e-mail was > sent. You can get a list of the commands and options that this listserv > accepts by sending an e-mail with HELP in the body to [EMAIL PROTECTED] > --=extPartTM-000-dd73ad26-5541-11d7-b2dc-00a0c9dfd5bc-- Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: quoted-printable Content-Description: HTML -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
