Re: Re: 9i OCP Details

[Tanel Poder]

IIRC = if I remember correctly.
Yes, you have just to sit at these courses. They are not really advanced
courses, especially when the instructor doesn't know, what's he talking
about. That's why I put advanced in double quotes. If you want real advanced
courses, go with Data Server Internals or seminars what Jonathan Lewis or
guys at his level provide.

Tanel.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, June 26, 2003 6:04 AM


> this is a stupid question but what does IIRC mean? not familiar with that
> one.
>
> what do you mean by 'pass' courses. every oracle courses i took you just
sit
> and listen to the instructor?
>
> how good are those advanced courses?
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, June 25, 2003 4:27 PM
>
>
> > Hi!
> >
> > There was about 25 OCMs before I took the exam in Dec 2002. Now there's
50
> > IIRC.
> >
> > At least then the requirements were:
> > Had to be 9i OCP.
> > Had to pass 2 "advanced" courses, I took Advanced PL/SQL and RAC.
> > Had to pass 2 day practicum. Sh*t, this was hard, but I'm glad about it,
> > that shows OCM actually is worth something.
> > There is a description in OCM website about the tasks. Anyway, when
going,
> > refresh your recovery skills ;)
> >
> > It did cost me about $5000 (from my own pocket). I had to travel to
> Chicago
> > from other side of the globe, because they didn't take tests in Europe
> then
> > (it might be the case now as well). If anyone asks was it worth the
> money -
> > I would have spent the money on other stupidities anyway ;)
> >
> > Cheers,
> > Tanel.
> >
> > - Original Message -
> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > Sent: Wednesday, June 25, 2003 10:57 PM
> >
> >
> > > to get OCM you need to
> > >
> > > take 2 advanced classes
> > > then pass an all day comprehensive hands on test right?
> > >
> > > what do they have you do for that test? i heard there are different
> OCMs?
> > btw, how much does it all cost?
> > > >
> > > > From: "Tanel Poder" <[EMAIL PROTECTED]>
> > > > Date: 2003/06/25 Wed PM 02:59:58 EDT
> > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > > > Subject: Re: 9i OCP Details
> > > >
> > > > Hi!
> > > >
> > > > If you're getting this OCP to look more appealing to employers, I'd
go
> > with
> > > > 8i first, then upgrade to 9i.
> > > > On resume it looks better, you seem more like old-timer that way ;)
> > > >
> > > > Tanel.
> > > > OCP 8,8i,9i, OCM.
> > > >
> > > > - Original Message -
> > > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > > > Sent: Wednesday, June 25, 2003 8:29 PM
> > > >
> > > >
> > > > > Senthil
> > > > >So, if I understand your question correctly, you are asking
> whether
> > you
> > > > > should:
> > > > > 1. Take the 8i exams, then the 9i upgrade exam.
> > > > > 2. Take the 9i exams.
> > > > > This will depend to some extent on your background, whether you
> still
> > work
> > > > > with 8i. Whether you can get your manager to spring for a class or
> > two.
> > > > >
> > > > >Myself, I started this path over a year ago (slow learner). At
> that
> > > > time
> > > > > since the 9i study materials weren't readily available, and I
still
> > work
> > > > > with 8i a lot, I chose to go the 8i route.
> > > > >If I were to start today, I think I would just go the 9i route.
I
> > > > expect
> > > > > soon Oracle will announce that in 6 months the 8i path will be
> closed.
> > > > This
> > > > > has already happened with Oracle 8.
> > > > >
> > > > > Dennis Williams
> > > > > DBA, 80%OCP, 100% DBA
> > > > > Lifetouch, Inc.
> > > > > [EMAIL PROTECTED]
> > > > >
> > > > >
> > > > > -Original Message-
> > > > > Sent: Wednesday, June 25, 2003 10:59 AM
> > > > > To: Multiple recipients of list ORACLE-L
> > > > >
> > > > >
> > > > > Hi Group,
> > > > >
> > > > > I wanted to do my 9i OCP. I have not taken any of the 8i OCP
exams.
> > > > > To do 9i Exams directly I need to attend module 3 or 4 or New
> features
> > > > > from any of the Oracle Edu Partners.
> > > > > Shall I take 9i New features and go ahead with the 9i exams.
> > > > >
> > > > > Suggestion are appreciated.
> > > > >
> > > > > Thanks
> > > > > Senthil.
> > > > > --
> > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > > --
> > > > > Author: Senthil Kumar D
> > > > >   INET: [EMAIL PROTECTED]
> > > > >
> > > > > Fat City Network Services-- 858-538-5051
http://www.fatcity.com
> > > > > San Diego, California-- Mailing list and web hosting
> services
> > > >
> > -
> > > > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
> > > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > > (or the

Re: 9i OCP Details

[Gudmundur Bjarni Josepsson]

> If you want real advanced courses go with [...] what Jonathan Lewis 
> or guys at his level provide.

I have to agree on this.  I've had the good fortune to sit in on Lewis's
'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and no course
I ever took from Oracle Education comes close to what these guys offer
(and I've taken quite a few).

Gudmundur

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gudmundur Bjarni Josepsson
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

SHMMAX setting on Sun 4800 box

[Zhai Jingmin]

Hello, List,

Our Sun Fire 4800 server have 8 CPUs and 16G memory.I set the SHMMAX kernel parameter 
to 4294967295 according to installation guide on metalink.The SHMMAX is "max shared 
memory segment size".What's the relationship between SHMMAX and max size of 
SGA?Because our oracle's SGA may exceed 4G,can I set this parameter to 8G,10G?

Any suggestion is appreciated.

Thanks, 

Zhai Jingmin
[EMAIL PROTECTED]
2003-06-26



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Zhai Jingmin
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

how to find folder size in unix

[A.Bahar]

how to find folder size in unix
-- 
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: ADO and bind variables (was RE: Performance improvement required :-))

[Grant Allen]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
> Pardee, Roy E
> Sent: Friday, June 13, 2003 20:10
> To: Multiple recipients of list ORACLE-L
> Subject: ADO and bind variables (was RE: Performance improvement
> required :-))
>
>
> This is interesting--if I use ADO with the ODBC provider (as
> the code does
> below), I get the same results.  But if I use just ADO (that
> is, ms' OLE DB
> provider for oracle (MSDAORA.1)) then I don't get bind vars.
>
> (I'm doing INSERTs in my code, not SELECTs).
>
> I wonder if oracle's native OLE DB provider works any
> differently--I would
> bet that it does...
>
> Cheers,
>
> -Roy

Roy,

I tried with the Oracle provider (OraOLEDB.Oracle.1), and the bind variables
work.  Another reason to stick with the Oracle provider :-)

Ciao
Fuzzy
:-)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Grant Allen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or 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 find folder size in unix

[Zhai Jingmin]

Hello, A.Bahar,

du -sk

=== At 2003-06-26, 02:59:00 you wrote: ===

>how to find folder size in unix
>-- 
>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).

= = = = = = = = = = = = = = = = = = = =


Best regards.
Zhai Jingmin
[EMAIL PROTECTED]
2003-06-26




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Zhai Jingmin
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Re: 9i OCP Details

[rgaffuri]

hotsos is going to be in town next week and im 'trying' get my boss to send me. slow 
economy. people dont want to spend money on training. :(

hope it doesnt fill up before he makes up his mind. 
> 
> From: "Gudmundur Bjarni Josepsson" <[EMAIL PROTECTED]>
> Date: 2003/06/26 Thu AM 05:49:43 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: 9i OCP Details
> 
> > If you want real advanced courses go with [...] what Jonathan Lewis 
> > or guys at his level provide.
> 
> I have to agree on this.  I've had the good fortune to sit in on Lewis's
> 'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and no course
> I ever took from Oracle Education comes close to what these guys offer
> (and I've taken quite a few).
> 
> Gudmundur
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Gudmundur Bjarni Josepsson
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: how to find folder size in unix

[Michael Boligan]

du -s  will list the number of blocks in the folder.  If you take
this number and multiply it by your OS block size (usually 512 for Unix) you
will get the number of bytes in your folder.  You can also use du -s * to get a
the size of all folders in a directory.

Just to avoid confusion they are generally called directories in Unix.

HTH,
Mike


   

  <[EMAIL PROTECTED]   
 
  onents.com>  To:   Multiple recipients of 
list ORACLE-L <[EMAIL PROTECTED]>   
  Sent by: cc: 

  [EMAIL PROTECTED]Subject:  how to find folder size in 
unix   
   

   

  06/26/2003 06:59 AM  

  Please respond to

  ORACLE-L 

   

   





how to find folder size in unix
--
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).





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Boligan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or 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 find folder size in unix

[Singh Pushpinder]

How about du -k?

HTH
Inder

-Original Message-
Sent: Thursday, June 26, 2003 9:15 AM
To: Multiple recipients of list ORACLE-L



du -s  will list the number of blocks in the folder.  If you take this 
number and multiply it by your OS block size (usually 512 for Unix) you will get the 
number of bytes in your folder.  You can also use du -s * to get a the size of all 
folders in a directory.

Just to avoid confusion they are generally called directories in Unix.

HTH,
Mike


   

  <[EMAIL PROTECTED]   
 
  onents.com>  To:   Multiple recipients of 
list ORACLE-L <[EMAIL PROTECTED]>   
  Sent by: cc: 

  [EMAIL PROTECTED]Subject:  how to find folder size in 
unix   
   

   

  06/26/2003 06:59 AM  

  Please respond to

  ORACLE-L 

   

   





how to find folder size in unix
--
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).





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Boligan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, 
include a line containing: UNSUB ORACLE-L (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: Singh Pushpinder
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or 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 find folder size in unix

[Gogala, Mladen]

Didn't they tell you that the size doesn't matter, it's the magic in the
folder?

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


how to find folder size in unix
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: 9i OCP Details

[Glenn Stauffer]

Gudmundur Bjarni Josepsson wrote:

If you want real advanced courses go with [...] what Jonathan Lewis 
or guys at his level provide.
   

I have to agree on this.  I've had the good fortune to sit in on Lewis's
'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and no course
I ever took from Oracle Education comes close to what these guys offer
(and I've taken quite a few).
 

The only course from Oracle Education that's come close for me is the 
internals course that I took about 3 or 4 years ago.  I haven't seen it 
offered recently.  The instructor was Scott somebody, who was, I was 
told, THE Scott or Scott/Tiger fame.

I second the recommendation for Jonathan Lewis; his Cost-Based 
Optimization course was well taught and I found it very useful.

Craig Shallahamer (www.orapub.com) also offers some worthwhile courses 
and is a very good instructor.

I'll find out about the Hotsos Clinic this August in Denver.  Looking 
forward to it.

Glenn Stauffer

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Glenn Stauffer
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Re: 9i OCP Details

[Stefick Ronald S Civ ESC/HRIDD]

Title: RE: Re: 9i OCP Details





It's well worth the begging.  I went in April and it was a GREAT class.  I suggest you do what you can to get there.


-Scott



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, June 26, 2003 7:15 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Re: 9i OCP Details



hotsos is going to be in town next week and im 'trying' get my boss to send me. slow economy. people dont want to spend money on training. :(

hope it doesnt fill up before he makes up his mind. 
> 
> From: "Gudmundur Bjarni Josepsson" <[EMAIL PROTECTED]>
> Date: 2003/06/26 Thu AM 05:49:43 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: 9i OCP Details
> 
> > If you want real advanced courses go with [...] what Jonathan Lewis
> > or guys at his level provide.
> 
> I have to agree on this.  I've had the good fortune to sit in on 
> Lewis's 'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and 
> no course I ever took from Oracle Education comes close to what these 
> guys offer (and I've taken quite a few).
> 
> Gudmundur
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Gudmundur Bjarni Josepsson
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
> San Diego, California    -- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
> the message BODY, include a line containing: UNSUB ORACLE-L (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: how to find folder size in unix

[Joe Testa]

versus the magic number which in no way is reflected in size.

Gogala, Mladen wrote:

Didn't they tell you that the size doesn't matter, it's the magic in the
folder?
Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L
how to find folder size in unix
 

--
Joseph S Testa
Chief Technology Officer 
Data Management Consulting
p: 614-791-9000
f: 614-791-9001

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Joe Testa
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Good link on Locally Managed Tablespaces

[Farnsworth, Dave]

I want to share this link with the listers.  It is a good article on Oracle locally 
managed tablespaces with some good info for those that are going to migrate to LMT's.

http://databasejournal.com/features/oracle/article.php/10893_2223631_1

Dave

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Farnsworth, Dave
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Dynamic SQL: where do you draw the line?

[Thomas Jeff]

Thanks Arup. and thanks to all who replied to this thread.  

And if anyone is interested, I found a two-part article on SQL Injection and
Oracle 
by Pete Finnigan at:

http://www.securityfocus.com/infocus/1644
http://www.securityfocus.com/infocus/1646





-Original Message-
Sent: Wednesday, June 25, 2003 5:45 PM
To: Multiple recipients of list ORACLE-L

Thomas

In addition to the responses posted by others, regarding the thrashing of
shared pool with zillions of once-used code, there is another serious
security hole - SQL Injection. You mentioned this is ging to be your
eCommerce application database - and will probably attract the hackers like
bees to honey.

In your example, if the parameter p_site_guide is passed a value

X' or 'X'='X

Note there is no single quote before or after the value. The dynamic sql
will, in addition to all the other parameters, accept this as a valid where
clause and rewrite the query as DELETE FROM some_table WHERE ASSET_GUID IN
( '...') AND OBJECT_ID IN (...) AND SITE_GUID = 'X' OR 'X' = 'X';

what do you think the result is going to be?

Your developers are not going to stop at delete only - they will explore
this to SELECTs as well. The seriousness of the problem grows - the
injectied code could have

X' UNION ALL SELECT CUST_NAME, CREDIT_CARD_NUM, EXP_DATE...

Now in addition to the good old data, the hacker also got some more (and
unauthorized) valuable "knowledge".

Try to show them the examples and prove how dangerous the practice of
dynamic sql is.

HTH.

Arup Nanda

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, June 25, 2003 11:04 AM


> I've been fighting an ongoing war with our ecommerce developers, who are
> inordinately fond of writing
> dynamic SQL code that neglects to incorporate bind variables.
Researching
> AskTom I've
> found and utilized different techniques to force bind variables into these
> dynamic SQL queries,
> including the use of application contexts, object types, etc.
>
> However, I'm wondering if I'm making things worse, essentially providing
> them with band-aids, when I should
> be forcing them to change the way they code.
>
> Consider the sample code below (which is a relatively simple example),
which
> is a generic DELETE statement
> generator.
>
> In this situation, the programmers claim the following code is good
> programming practice, promotes
> ease of maintenance, less buggy, and promotes code reusability (their
> definition of reusability is a bit
> different from mine).
>
> I disagreed with them -- not only is this code not reusable at all, with
the
> parsing overhead consequences,
> it's also harder to debug and tune for performance, due to all the
> permutations that needs to be tested.
> My take was that they be far better off writing a simple static DELETE
> statement for each table.
>
> Their rejoinder -- it's not worth writing lots of redundant code at the
> expense of 'minimal' gains in
> performance.Now, this code *could* be rewritten to use the SYS_CONTEXT
> function on the p_object_id
> and p_site_guid to force a bind variable on those two conditions, but the
IN
> condition with respect to the
> p_asset_guid would be more problematic.   However, I don't feel we should
> have to be resorting to such measures
> to get this code to using bind variables.
>
> So, I'm at the point of denying such code to be migrated to production.
I
> recognize that there
> are situations where there is a legitimate need for dynamic SQL, but the
SQL
> has to be written w/o catenating
> literal SQL -- and if it can't -- they need to go back to square one.
>
> Opinions?   I'm curious -- do you have policies/standards with respect to
> dynamic SQL?
>
>
>
> CREATE OR REPLACE PROCEDURE test
> (
> p_Asset_GuidIN VARCHAR2,
> p_Object_Id IN VARCHAR2,
> p_Object_Definition IN VARCHAR2,
> p_Site_Guid IN VARCHAR2,
> p_resultIN OUT VARCHAR2
>
> ) AS
>
>strTableName VARCHAR2(100);
>strWhere VARCHAR2(100);
>strQuery LONG;
>
> BEGIN
>IF p_Object_Definition = 'PRODUCT'
>THEN
>strTableName := ' TNE.GPD_PRODUCT_ASSET ';
>strWhere := ' MODEL_NO ';
>ELSIF p_Object_Definition = 'CARACTERISTIC'
> OR p_Object_Definition = 'CHARACTERISTIC'
>THEN
>strTableName := ' TNE.GPD_CARACTERISTIC_ASSET ';
>strWhere := ' CARACTERISTIC_ID ';
>ELSIF p_Object_Definition = 'CATEGORY'
>THEN
>strTableName := ' TNE.GPD_CATEGORY_ASSET ';
>strWhere := ' CATEGORY_GUID ';
>ELSIF p_Object_Definition = 'VALUE'
>THEN
>strTableName := ' TNE.GPD_VALUE_ASSET ';
>strWhere := ' VALUE_ID ';
>ELSIF p_Object_Definition = 'PRODUCT_NODE'
>THEN
>strTableName := ' TNE.GPD_PRODUCT_NODE_ASSET ';
>strWhere := ' PRODUCT_NODE_GUID ';
>ELSIF p_Object_Definition = 'CARAC_GROUP'
>THEN
>strTableName := ' TNE.GPD_CARAC_GROUP_A

RE: how to find folder size in unix

[A.Bahar]

ha ha ha 

-Original Message-
Sent: Thursday, June 26, 2003 4:00 PM
To: Multiple recipients of list ORACLE-L


Didn't they tell you that the size doesn't matter, it's the magic in the
folder?

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


how to find folder size in unix
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: High-availability database question !!!!

[Hemant K Chitale]

The "That might come as a surprise to some people, but there's
basically no 25/8/370 availability with Oracle unless you either specify
downtime as something else or don't patch/upgrade at all." 
doesn't cut ice [I think I might have mixed metaphors here] with
damagement.
My 8iOPS to 9iRAC upgrade had been in the planning stages for more than a
year, never getting the 4 hours downtime requested.
Finally, the upgrade was scheduled for 10am to 2pm on a Wednesday.
Guess what ?   Tuesday night, 9pm I get a call "the Promis
system is down, manufacturing is down".  Can you come in
and
quickly upgrade the Oracle database to 9iRAC tonight, like, NOW ?  I
did do it in 3.5 hours !
Hemant
At 06:59 AM 25-06-03 -0800, you wrote:
1. RAC can duplicate your
instances/nodes. But you still have only one database.
2. One database means un-availability whenever you have to apply a patch
or upgrade the database. No online patches available. No rolling
upgrades.
3. Dataguard needs to have exactly the same version (down to patch level)
on the two (or more) databases. So that just means two databases down if
you have to patch or upgrade. That might come as a surprise to some
people, but there's basically no 25/8/370 availability with Oracle unless
you either specify downtime as something else or don't patch/upgrade at
all.
4. Logical stuff like replication, logical Dataguard, Shareplex from
Quest and others can get around the
"everyhing-has-to-be-the-same-version" by using logical
replication of some sort - some faster, some slower.
Mogens
Jackson Dumas wrote:

Goodday all

We have cutomer who is having a database which should be available
24x7.
Now we can't get even a maintenance time to do the database. We tried
on several occasion but nothing happens.
Now they want us to provide them with a solution to be able to work on
"another database" if we need to do maintenance. Could you guys
come
up with some ideas what we can offer them. Is there any other
alternatives besides replication and standby database.
Currently the system is running as a package in a cluster environment,
HP-UX, so when we go replication route possibilities are that we might
have to forget about high-availabilty cluster environment due to
things like disk space and memory for switching over during failure.
Neither do the standby database a good option in a situation where
you'll needing a longer downtime.

Please help.

Thanx,

Junior DBA

On Tue, 24 Jun 2003 08:19:18 -0800 
([EMAIL PROTECTED]) wrote:

 

Hi there,

I got a question on log. If my program crashes, can I check some log
   

to see what
 

recent transaction is? It will give me a big help on trouble
   

shooting.
 

Thanks,
Jin
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author:
 INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051
http://www.fatcity.com
San Diego, California    --
Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note
EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You 
may
also send the HELP command for other information (like subscribing).
   


___
LOOK GOOD, FEEL GOOD -
WWW.HEALTHIEST.CO.ZA

Cool Connection, Cool Price, Internet Access for R59 monthly @ WebMail
http://www.webmail.co.za/dialup/
 


Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is : 
http://hkchital.tripod.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

10046 Trace file questions

[Daniel Fink]

I am reviewing a 10046 trace file and have come across several questions (actually 
more like hypothesis that I want to confirm).

1) The first user statement executed waits only on SQL*Net to/from client and does not 
wait on any db file activity. I presume this means that the data was found in the 
buffer cache and did not have to be read on disk.

2) The next user statement is preceeded by a recursive query on view$. The user 
statement accesses a view, so this recursive query is part of the object resolution 
phase of parsing.

3) This 3rd user statement has a substantial number of waits on db file activities. 
This indicates disk access.

4) A subsequent statement has several space management (activity on fet$ and uet$) 
activities. The tablespace/file that is passed in as a bind variable are associated 
with a 'temp' tablespace. However, the tablespace is set up as dictionary managed. 
This indicates that sorting is being done by this operation and that the sort segment 
space management is being tracked in the data dictionary.

Thoughts?begin:vcard 
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard

Re: 9i OCP Details

[Rachel Carmichael]

Scott Gossett taught/teaches the Data Internals class and he is not the
Scott of Scott/tiger


--- Glenn Stauffer <[EMAIL PROTECTED]> wrote:
> Gudmundur Bjarni Josepsson wrote:
> 
> >>If you want real advanced courses go with [...] what Jonathan Lewis
> 
> >>or guys at his level provide.
> >>
> >>
> >
> >I have to agree on this.  I've had the good fortune to sit in on
> Lewis's
> >'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and no
> course
> >I ever took from Oracle Education comes close to what these guys
> offer
> >(and I've taken quite a few).
> >
> >  
> >
> 
> The only course from Oracle Education that's come close for me is the
> 
> internals course that I took about 3 or 4 years ago.  I haven't seen
> it 
> offered recently.  The instructor was Scott somebody, who was, I was 
> told, THE Scott or Scott/Tiger fame.
> 
> I second the recommendation for Jonathan Lewis; his Cost-Based 
> Optimization course was well taught and I found it very useful.
> 
> Craig Shallahamer (www.orapub.com) also offers some worthwhile
> courses 
> and is a very good instructor.
> 
> I'll find out about the Hotsos Clinic this August in Denver.  Looking
> 
> forward to it.
> 
> Glenn Stauffer
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Glenn Stauffer
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 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).

CURSOR_SPACE_FOR_TIME

[Odland, Brad]

We are ran into a bug the other day where oracle dumps core with a ORA-07445
error. We tracked it down to a Context related bug where PL/SQL callout
during heavy shared pool load cause the ora-07745 core dump. The suggested
workarounds are to set CURSOR_SPACE_FOR_TIME to true. This is in 8.1.7.4.0
and is reportedly fixed in 9i.  What are listers experience with this
parameter? On our system it sound like setting this without altering the
size of the shared pool would be potentially dangerous and could cause more
problems that leaving it alone.

This error is not a critical one to business functions and users here. (so
far)


Thanks

Brad Odland
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Odland, Brad
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Re: 9i OCP Details

[DENNIS WILLIAMS]

Totally agree. Make the pitch that this class alone could push your skills
up to the next level. It is a real eye-opener. I'll bet it is much less than
an Oracle Education class. As a bonus I think they usually let you use their
tools for a month. Use your negotiating skills. 



Dennis Williams 
DBA, 80%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

 
 -Original Message-
Sent: Thursday, June 26, 2003 9:19 AM
To: Multiple recipients of list ORACLE-L



It's well worth the begging.  I went in April and it was a GREAT class.  I
suggest you do what you can to get there. 

-Scott 


-Original Message- 

Sent: Thursday, June 26, 2003 7:15 AM 
To: Multiple recipients of list ORACLE-L 


hotsos is going to be in town next week and im 'trying' get my boss to send
me. slow economy. people dont want to spend money on training. :(

hope it doesnt fill up before he makes up his mind. 
> 
> From: "Gudmundur Bjarni Josepsson" <[EMAIL PROTECTED]> 
> Date: 2003/06/26 Thu AM 05:49:43 EDT 
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 
> Subject: Re: 9i OCP Details 
> 
> > If you want real advanced courses go with [...] what Jonathan Lewis 
> > or guys at his level provide. 
> 
> I have to agree on this.  I've had the good fortune to sit in on 
> Lewis's 'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and 
> no course I ever took from Oracle Education comes close to what these 
> guys offer (and I've taken quite a few). 
> 
> Gudmundur 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
  
> -- 
> Author: Gudmundur Bjarni Josepsson 
>   INET: [EMAIL PROTECTED] 
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  
> San Diego, California-- Mailing list and web hosting services 
> - 
> To REMOVE yourself from this mailing list, send an E-Mail message 
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
> the message BODY, include a line containing: UNSUB ORACLE-L (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).

-- 
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: Materialized view

[Stephen Lee]

I'm not sure if you want to improve the query, or the replication process.
If it's the replication process, one other option is 'refresh force'.  Then,
if I understand the docs correctly, you get a fast refresh if it can; and if
refresh fast is not possible, then refresh complete.  That's the theory.
Now whether or not it actually works that way 

For what it's worth, I always use 'refresh force'.

-Original Message-

How can I create materialized view(mv) for the following statement using
'refresh fast' method.
I can use  'refresh complete'   option to create this mv,
but how can I use 'refresh fast' option for the below statement in order to
improve performance in query.
Any workaround ?

select mas.salesrep_id,sum(del.qty*del.price)-sum(del.discount) 
from 
sales_master mas, 
sales_details del 
where 
mas.transaction_id =del.transaction_id 
group by 
mas.salesrep_id. 

Thanks in advance
-- 
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: Index question

[DENNIS WILLIAMS]

Teresita
   1. Do NOT modify Lawson data directly. A lot of the Lawson integrity
checking is built into the Lawson code. You can easily corrupt the data
without realizing it. 
   2. I would discourage you from directly querying the Lawson data. When
you upgrade your Lawson system, you'll have to rework all your programs.
   3. In case you aren't aware of it, there is an email list devoted to
Lawson sites. If you are interested in alternatives to directly querying the
data, others on that list can help you better than I can. Send an email to:
 
[EMAIL PROTECTED]



Dennis Williams 
DBA, 80%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, June 25, 2003 9:04 PM
To: Multiple recipients of list ORACLE-L


Hi!!
 
Let explain more about my situation.
The company that I work for is a chain of stores around some city's on
Mexico, they bought Lawson a system that uses Oracle to manage the data
bases, at first they use SQL Server 2000, but I wasn't enough to manager all
the information.
The structure of the table is all ready done and I have to learn it to do
some reports that Lawson don't have, change or delete information and export
some information to dbf files. Because we was using SQL Server I used Store
procedures to return  the select result to VB recordset and the I pass the
select result to Crystal Report or to a DBF file.
Well  I see that in Oracle the store procedure do not returns the result set
has easy has SQL Server so I use and statement that after execute it returns
me the result in a record set.
 
sQuery = "SELECT COMPANY,LOCATION, R_NAME FROM ICLOCATION " & _
 "WHERE COMPANY=2000 OR COMPANY=2001 OR COMPANY=2002 order by
COMPANY,LOCATION"
Set recRS = New ADODB.Recordset
recRS.Open sQuery, gcnOracle, adOpenForwardOnly, adLockReadOnly, adCmdText
 
or execute a delete or update statement
 
sSQL = "update  /*+ INDEX(itemloc ITLSET2) */ itemloc set average_cost =" &
costo & " where (company='2000') and item= '" & Arti & "'"
gcnOracle.Execute sSQL
gcnOracle.Execute "Commit", dbSQLPassThrough
   
Back to my problem:
In this case the update of the average cost has to be done on the table Item
location ( ITEMLOC)  that have all the item that each location( store) have.
The locations have a company, when we changes the average cost is per
company ( each company represent a different city) 
 
So my boss  execute the update statement 
 
sSQL = "update  itemloc set average_cost =" & costo & " where
(company='2000') and item= '" & Arti & "'"

and she told me that per item  it takes like 10 seconds.
After read the article that I mention she changes the statement to this :
 sSQL = "update  /*+ INDEX(itemloc ITLSET2) */ itemloc set average_cost =" &
costo & " where (company='2000') and item= '" & Arti & "'"
 
ITLSET2 is a index that have company(1), location(2) and Item(3) and it
takes 2 seconds per item, so that is way she is convinced that we have to
uses the /*+ INDEX(itemloc ITLSET2) */ in all of our select, update or
delete statement.
 
I hope you undestant my English and my problem too, because I have to do
some really complicated queries that have like 3 or 5 tables in them, and
using this method will give me some serious complications, maybe in this
case (change of the average cost) is not too dangerous. 
But I have to give her strong statements to change her mind.
 
Thanks for everything friends!!


>>> [EMAIL PROTECTED] 06/25/03 06:10PM >>>

Teresita,

I don't fully understand whether adding or removing a hint caused the
problem but like Stephane said - you should probably stay away from them
for now.  If adding a hint decreased performance then you have proved this
for yourself.

A couple of important points:

* Using an index isn't always faster than scanning the table

* If a database is correctly analyzed then the optimisor can determine when
to use indexes or not automatically

* Hints can force the optimisor to choose a non-optimal execution plan.  If
you are smarter than the optimisor this may be fine but in most cases the
optimisor will make the right decision when all tables are analyzed

* Hints have very specific formatting and object name rules.  If you rename
an index the hint will become invalid and be blissfully ignored - you won't
even know.

* There are some hints which can safely be used but it takes a good
understanding of Oracle first.  My advice would be don't use them.  If you
have a specific problem then post lot's of details here and someone might
suggest trying a hint.

* Until you really understand the implications of using a specific hint it
can be dangerous in terms of performance - Stephane is not lying when he
says you can do more harm than good.  I have many cases where I can "tune"
a query simply by removing the hints which someone included and letting
Oracle do what it does best.

Regards,
  Mark.



 

  "Teresita Castro"

  <[EMAIL PROTECTED]   

RE: 9i OCP Details

[Gogala, Mladen]

You are talking about Scott Gossett jr. I took that
internals course and it was good. It was very, very good.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Thursday, June 26, 2003 10:25 AM
To: Multiple recipients of list ORACLE-L


Gudmundur Bjarni Josepsson wrote:

>>If you want real advanced courses go with [...] what Jonathan Lewis 
>>or guys at his level provide.
>>
>>
>
>I have to agree on this.  I've had the good fortune to sit in on Lewis's
>'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and no course
>I ever took from Oracle Education comes close to what these guys offer
>(and I've taken quite a few).
>
>  
>

The only course from Oracle Education that's come close for me is the 
internals course that I took about 3 or 4 years ago.  I haven't seen it 
offered recently.  The instructor was Scott somebody, who was, I was 
told, THE Scott or Scott/Tiger fame.

I second the recommendation for Jonathan Lewis; his Cost-Based 
Optimization course was well taught and I found it very useful.

Craig Shallahamer (www.orapub.com) also offers some worthwhile courses 
and is a very good instructor.

I'll find out about the Hotsos Clinic this August in Denver.  Looking 
forward to it.

Glenn Stauffer


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Glenn Stauffer
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

explain plan

[Ehresmann, David]

List ,

does anybody have or know where I can get a script that will explain  all of
the columns from the plan_table?  Does anybody have any suggestions for
using ultxpls.sql?

thanks,


David Ehresmann

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ehresmann, David
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: is a process/session rolling back ?

[Hemant K Chitale]

Query on v$transaction --- look at used_ublk and used_urec -- these would
be reducing as the transaction is rolling back. I'm sure you can figure
out how to join v$transaction to v$session.
Hemant
At 07:04 PM 25-06-03 -0800, you wrote:
Hi DBAs,

Can somebody confirm that the statement I am using is
correct or not ?
I use v$sess_io, v$session to check for rolling back
of a session .(I check for consistent gets, consistent
reads.)
If the values are changing in the select, i assume
that the session is using rbs.
but is this correct process toknow that the session is
rolling back ?
Thanks and Regards,
srinivas
__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: kommareddy sreenivasa
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is :  http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: how to find folder size in unix

[Nelson, Allan]

Suppose you have directory structure /diskx/myfolder and you want to
know the size of myfolder in KB.
Do this:

$cd /diskx
$du -sk myfolder

Hope this helps
Allan

-Original Message-
Sent: Thursday, June 26, 2003 9:00 AM
To: Multiple recipients of list ORACLE-L


Didn't they tell you that the size doesn't matter, it's the magic in the
folder?

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message- [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


how to find folder size in unix
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (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: explain plan

[DENNIS WILLIAMS]

David - Here is the one I use. In my environment I just have one developer
at a time using EXPLAIN, so I just have it truncate the table afterward.

select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name
||' '||decode(id,0,'Cost = '||position) "Query Plan"
from plan_table
start with id=0
connect by prior id = parent_id
/   
truncate table plan_table
/ 

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Thursday, June 26, 2003 11:00 AM
To: Multiple recipients of list ORACLE-L


List ,

does anybody have or know where I can get a script that will explain  all of
the columns from the plan_table?  Does anybody have any suggestions for
using ultxpls.sql?

thanks,


David Ehresmann

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ehresmann, David
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
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: SHMMAX setting on Sun 4800 box

[Matthew Zito]

As long as you're running 64-bit solaris and 64-bit oracle, you can set
SHMMAX to be larger than 4G, which will allow your SGA to be larger than
4G.  Keep an eye on your overall memory pressure, though, to make sure
you don't oversubscribe the system.  If you're running Solaris 8/9
and/or using some sort of direct i/o (raw devices, veritas, etc.) you're
good to go at that point.  If you're running 7 or 2.6 (can you even run
2.6 and 7 on the 4800?  I doubt it), you'll want to turn on
priority_paging in /etc/system to keep your sga from getting paged out
in favor of filesystem buffers.

Thanks,
Matt

--
Matthew Zito
GridApp Systems
Email: [EMAIL PROTECTED]
Cell: 646-220-3551
Phone: 212-358-8211 x 359
http://www.gridapp.com

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On 
> Behalf Of Zhai Jingmin
> Sent: Thursday, June 26, 2003 5:34 AM
> To: Multiple recipients of list ORACLE-L
> Subject: SHMMAX setting on Sun 4800 box
> 
> 
> Hello, List,
> 
> Our Sun Fire 4800 server have 8 CPUs and 16G memory.I set the 
> SHMMAX kernel parameter to 4294967295 according to 
> installation guide on metalink.The SHMMAX is "max shared 
> memory segment size".What's the relationship between SHMMAX 
> and max size of SGA?Because our oracle's SGA may exceed 
> 4G,can I set this parameter to 8G,10G?
> 
> Any suggestion is appreciated.
> 
> Thanks, 
> 
> Zhai Jingmin
> [EMAIL PROTECTED]
> 2003-06-26
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Zhai Jingmin
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') 
> and in the message BODY, include a line containing: UNSUB 
> ORACLE-L (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: Matthew Zito
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Constant Date in SQL*Loader Control File

[David Lewandowski]

I can't figure out how to specify a constant date for a column 
in a SQL*Loader control file and can't find any examples.

I tried:
  LOADDATE  CONSTANT "TO_DATE('20030626', 'MMDD')",
but I get this error message in the log file:
  ORA-01858: a non-numeric character was found where a numeric was expected

Can anyone tell me the correct syntax?

David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: David Lewandowski
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or 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 find folder size in unix

[Freeman Robert - IL]

The true magic is in my ability to actually use the folders and the contents
therin in Win XP.

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 6/26/2003 9:49 AM

versus the magic number which in no way is reflected in size.

Gogala, Mladen wrote:

>Didn't they tell you that the size doesn't matter, it's the magic in
the
>folder?
>
>Mladen Gogala
>Oracle DBA
>Phone:(203) 459-6855
>Email:[EMAIL PROTECTED]
>
>
>-Original Message-
>[mailto:[EMAIL PROTECTED]
>Sent: Thursday, June 26, 2003 7:00 AM
>To: Multiple recipients of list ORACLE-L
>
>
>how to find folder size in unix
>  
>

-- 
Joseph S Testa
Chief Technology Officer 
Data Management Consulting
p: 614-791-9000
f: 614-791-9001


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joe Testa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or 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 find folder size in unix

[Gogala, Mladen]

Hey Joe! Long time no see! Welcome back!

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Thursday, June 26, 2003 10:50 AM
To: Multiple recipients of list ORACLE-L


versus the magic number which in no way is reflected in size.

Gogala, Mladen wrote:

>Didn't they tell you that the size doesn't matter, it's the magic in the
>folder?
>
>Mladen Gogala
>Oracle DBA
>Phone:(203) 459-6855
>Email:[EMAIL PROTECTED]
>
>
>-Original Message-
>[mailto:[EMAIL PROTECTED]
>Sent: Thursday, June 26, 2003 7:00 AM
>To: Multiple recipients of list ORACLE-L
>
>
>how to find folder size in unix
>  
>

-- 
Joseph S Testa
Chief Technology Officer 
Data Management Consulting
p: 614-791-9000
f: 614-791-9001


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joe Testa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Re: 9i OCP Details

[Freeman Robert - IL]

/*+ DISCLAIMER */

I have no association with Cary or hotsos (did I get it right this time
Cary?) and I've never been to one of his classes... 

but knowing Cary, having talked to him, having had his help on a few issues
and questions and having been to his presentations, I'd say if you can go,
GO GO GO. 

RF


-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 6/26/2003 10:29 AM

Totally agree. Make the pitch that this class alone could push your
skills
up to the next level. It is a real eye-opener. I'll bet it is much less
than
an Oracle Education class. As a bonus I think they usually let you use
their
tools for a month. Use your negotiating skills. 



Dennis Williams 
DBA, 80%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

 
 -Original Message-
Sent: Thursday, June 26, 2003 9:19 AM
To: Multiple recipients of list ORACLE-L



It's well worth the begging.  I went in April and it was a GREAT class.
I
suggest you do what you can to get there. 

-Scott 


-Original Message- 

Sent: Thursday, June 26, 2003 7:15 AM 
To: Multiple recipients of list ORACLE-L 


hotsos is going to be in town next week and im 'trying' get my boss to
send
me. slow economy. people dont want to spend money on training. :(

hope it doesnt fill up before he makes up his mind. 
> 
> From: "Gudmundur Bjarni Josepsson" <[EMAIL PROTECTED]> 
> Date: 2003/06/26 Thu AM 05:49:43 EDT 
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 
> Subject: Re: 9i OCP Details 
> 
> > If you want real advanced courses go with [...] what Jonathan Lewis 
> > or guys at his level provide. 
> 
> I have to agree on this.  I've had the good fortune to sit in on 
> Lewis's 'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and

> no course I ever took from Oracle Education comes close to what these 
> guys offer (and I've taken quite a few). 
> 
> Gudmundur 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
  
> -- 
> Author: Gudmundur Bjarni Josepsson 
>   INET: [EMAIL PROTECTED] 
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  
> San Diego, California-- Mailing list and web hosting services 
> - 
> To REMOVE yourself from this mailing list, send an E-Mail message 
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
> the message BODY, include a line containing: UNSUB ORACLE-L (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).

-- 
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: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or 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 find folder size in unix

[Orr, Steve]

What's a folder? GUI's? We don't need no stinkin' GUI's!

-Original Message-
Sent: Thursday, June 26, 2003 8:00 AM
To: Multiple recipients of list ORACLE-L


Didn't they tell you that the size doesn't matter, it's the magic in the
folder?

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message- [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


how to find folder size in unix
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Orr, Steve
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Materialized view

[Jamadagni, Rajendra]

Title: RE: Materialized view





don't you need a 'count(*)' for refresh fast?


Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



-Original Message-
From: Stephen Lee [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 26, 2003 11:50 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Materialized view




I'm not sure if you want to improve the query, or the replication process.
If it's the replication process, one other option is 'refresh force'.  Then,
if I understand the docs correctly, you get a fast refresh if it can; and if
refresh fast is not possible, then refresh complete.  That's the theory.
Now whether or not it actually works that way 


For what it's worth, I always use 'refresh force'.


-Original Message-


How can I create materialized view(mv) for the following statement using
'refresh fast' method.
I can use  'refresh complete'   option to create this mv,
but how can I use 'refresh fast' option for the below statement in order to
improve performance in query.
Any workaround ?


select mas.salesrep_id,sum(del.qty*del.price)-sum(del.discount) 
from 
sales_master mas, 
sales_details del 
where 
mas.transaction_id =del.transaction_id 
group by 
mas.salesrep_id. 


Thanks in advance
-- 
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).



*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

RE: explain plan

[Stephen Lee]

Is this what you wanted?

col OPERATION for a60
col OPTIONS for a40
col object_name for a32
select lpad(' ',2*(level-1)) || operation || ' ' || decode(id, 0, 'Cost = '
|| position) "OPERATION", options, object_name from plan_table start with
id = 0 connect by prior id = parent_id
/

> -Original Message-
> 
> List ,
> 
> does anybody have or know where I can get a script that will 
> explain  all of
> the columns from the plan_table?  Does anybody have any 
> suggestions for
> using ultxpls.sql?
> 
> 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).

Re: ORA-01041: internal error. hostdef extension doesn't exist

[Helen J Mitchell]

Does anyone have information about salaries in Denver?  I looked at salary.com and saw 
the median
salary was $72K.  Does anyone have any opinions?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Helen J Mitchell
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: 9i OCP Details

[Pete Sharman]

Well that not too surprising.  Just about everything Scott does (he still
works with Oracle) is very, very good.  Most recent thing I've seen him
working on was the OCM exam, which no doubt explains why it was so hard!

I believe the Internals course morphed into a series of internal courses
(i.e. only Oracle staff can attend) that are three to five days long.  There
are now 8 of them.  They are also presented to customers as one day
seminars, by the same instructors that run the internal versions.  Not too
sure how you locate them, but they may be marketed with a course code
starting with DSI (Data Server Internals).

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-
Gogala, Mladen
Sent: Thursday, June 26, 2003 8:36 AM
To: Multiple recipients of list ORACLE-L

You are talking about Scott Gossett jr. I took that
internals course and it was good. It was very, very good.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Thursday, June 26, 2003 10:25 AM
To: Multiple recipients of list ORACLE-L


Gudmundur Bjarni Josepsson wrote:

>>If you want real advanced courses go with [...] what Jonathan Lewis 
>>or guys at his level provide.
>>
>>
>
>I have to agree on this.  I've had the good fortune to sit in on Lewis's
>'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and no course
>I ever took from Oracle Education comes close to what these guys offer
>(and I've taken quite a few).
>
>  
>

The only course from Oracle Education that's come close for me is the 
internals course that I took about 3 or 4 years ago.  I haven't seen it 
offered recently.  The instructor was Scott somebody, who was, I was 
told, THE Scott or Scott/Tiger fame.

I second the recommendation for Jonathan Lewis; his Cost-Based 
Optimization course was well taught and I found it very useful.

Craig Shallahamer (www.orapub.com) also offers some worthwhile courses 
and is a very good instructor.

I'll find out about the Hotsos Clinic this August in Denver.  Looking 
forward to it.

Glenn Stauffer


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Glenn Stauffer
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).

Re: RE: 9i OCP Details

[rgaffuri]

what office is that internals class taught out of? does anyone else teach it? how many 
days is it? 
> 
> From: "Gogala, Mladen" <[EMAIL PROTECTED]>
> Date: 2003/06/26 Thu AM 11:35:34 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: 9i OCP Details
> 
> You are talking about Scott Gossett jr. I took that
> internals course and it was good. It was very, very good.
> 
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
> 
> 
> -Original Message-
> Sent: Thursday, June 26, 2003 10:25 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Gudmundur Bjarni Josepsson wrote:
> 
> >>If you want real advanced courses go with [...] what Jonathan Lewis 
> >>or guys at his level provide.
> >>
> >>
> >
> >I have to agree on this.  I've had the good fortune to sit in on Lewis's
> >'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and no course
> >I ever took from Oracle Education comes close to what these guys offer
> >(and I've taken quite a few).
> >
> >  
> >
> 
> The only course from Oracle Education that's come close for me is the 
> internals course that I took about 3 or 4 years ago.  I haven't seen it 
> offered recently.  The instructor was Scott somebody, who was, I was 
> told, THE Scott or Scott/Tiger fame.
> 
> I second the recommendation for Jonathan Lewis; his Cost-Based 
> Optimization course was well taught and I found it very useful.
> 
> Craig Shallahamer (www.orapub.com) also offers some worthwhile courses 
> and is a very good instructor.
> 
> I'll find out about the Hotsos Clinic this August in Denver.  Looking 
> forward to it.
> 
> Glenn Stauffer
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Glenn Stauffer
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: Gogala, Mladen
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: explain plan

[Freeman Robert - IL]

If you all are in 9i and you have not used dbms_xplan.display, give it a
try... you will throw scripts like this one away forever!

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 6/26/2003 11:19 AM

David - Here is the one I use. In my environment I just have one
developer
at a time using EXPLAIN, so I just have it truncate the table afterward.

select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name
||' '||decode(id,0,'Cost = '||position) "Query Plan"
from plan_table
start with id=0
connect by prior id = parent_id
/   
truncate table plan_table
/ 

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Thursday, June 26, 2003 11:00 AM
To: Multiple recipients of list ORACLE-L


List ,

does anybody have or know where I can get a script that will explain
all of
the columns from the plan_table?  Does anybody have any suggestions for
using ultxpls.sql?

thanks,


David Ehresmann

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ehresmann, David
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
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: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SHMMAX setting on Sun 4800 box

[Richard Ji]

Matt,

Your SGA can still be larger than 4G even if SHMMAX is not larger than
4G.  When SGA size > SHMMAX, mutilple shared memeory segements will
be allocated.  There used to be a claim that if you can fit the SGA
into a single shared memory segment, ISM will be used.  This might
be true for older version of Solaris, but I have seen ISM being used
even when mutiple shared memory segments are allocated on Solaris 8.

Regards,

Richard

-Original Message-
Sent: Thursday, June 26, 2003 10:55 AM
To: Multiple recipients of list ORACLE-L



As long as you're running 64-bit solaris and 64-bit oracle, you can set
SHMMAX to be larger than 4G, which will allow your SGA to be larger than
4G.  Keep an eye on your overall memory pressure, though, to make sure
you don't oversubscribe the system.  If you're running Solaris 8/9
and/or using some sort of direct i/o (raw devices, veritas, etc.) you're
good to go at that point.  If you're running 7 or 2.6 (can you even run
2.6 and 7 on the 4800?  I doubt it), you'll want to turn on
priority_paging in /etc/system to keep your sga from getting paged out
in favor of filesystem buffers.

Thanks,
Matt

--
Matthew Zito
GridApp Systems
Email: [EMAIL PROTECTED]
Cell: 646-220-3551
Phone: 212-358-8211 x 359
http://www.gridapp.com

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On 
> Behalf Of Zhai Jingmin
> Sent: Thursday, June 26, 2003 5:34 AM
> To: Multiple recipients of list ORACLE-L
> Subject: SHMMAX setting on Sun 4800 box
> 
> 
> Hello, List,
> 
> Our Sun Fire 4800 server have 8 CPUs and 16G memory.I set the 
> SHMMAX kernel parameter to 4294967295 according to 
> installation guide on metalink.The SHMMAX is "max shared 
> memory segment size".What's the relationship between SHMMAX 
> and max size of SGA?Because our oracle's SGA may exceed 
> 4G,can I set this parameter to 8G,10G?
> 
> Any suggestion is appreciated.
> 
> Thanks, 
> 
> Zhai Jingmin
> [EMAIL PROTECTED]
> 2003-06-26
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Zhai Jingmin
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') 
> and in the message BODY, include a line containing: UNSUB 
> ORACLE-L (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: Matthew Zito
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Materialized view

[Stephen Lee]

For fast refresh, you need to have a snapshot log on the master table and
the snapshot must be registered with the master.

Keep in mind, we are only talking about simple, read-only, snapshots here.
There are fancier ways of moving data here and there.  But I would have to
do the RTFM thing before expounding on those.

-Original Message-

don't you need a 'count(*)' for refresh fast? 
Raj 

 
Rajendra dot Jamadagni at nospamespn dot com 
All Views expressed in this email are strictly personal. 
QOTD: Any clod can have facts, having an opinion is an art ! 


-Original Message- 
Sent: Thursday, June 26, 2003 11:50 AM 
To: Multiple recipients of list ORACLE-L 



I'm not sure if you want to improve the query, or the replication process. 
If it's the replication process, one other option is 'refresh force'.  Then,

if I understand the docs correctly, you get a fast refresh if it can; and if

refresh fast is not possible, then refresh complete.  That's the theory. 
Now whether or not it actually works that way  
For what it's worth, I always use 'refresh force'. 
-Original Message- 
How can I create materialized view(mv) for the following statement using 
'refresh fast' method. 
I can use  'refresh complete'   option to create this mv, 
but how can I use 'refresh fast' option for the below statement in order to 
improve performance in query. 
Any workaround ? 
select mas.salesrep_id,sum(del.qty*del.price)-sum(del.discount) 
from 
sales_master mas, 
sales_details del 
where 
mas.transaction_id =del.transaction_id 
group by 
mas.salesrep_id. 
Thanks in advance 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Stephen Lee 
  INET: [EMAIL PROTECTED] 
Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services 
- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 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: 9i OCP Details

[Glenn Stauffer]

Rachel Carmichael wrote:

Scott Gossett taught/teaches the Data Internals class and he is not the
Scott of Scott/tiger
 

Thanks for the correction, indeed a web search found the reference on 
technet to Bruce Scott.  Scott Gossett was the instructor I have; he was 
probably explaining that he wasn't that 'Scott', but I was not paying 
close attention.

Glenn

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Glenn Stauffer
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Constant Date in SQL*Loader Control File

[Koivu, Lisa]

Hi David, 

Here's one of my control files, it works for me...  LOAD_DATE is indeed a date field. 

OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE  ASCNAME_RAW
(
ID  RECNUM,
AN_SITE POSITION(1:2) CHAR 
NULLIF AN_SITE=BLANKS, 
AN_ASSOC_NO POSITION(3:6) CHAR 
NULLIF AN_ASSOC_NO=BLANKS, 
AN_ASSOC_NAME   POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR 
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS, 

LOAD_DATE   SYSDATE   
)

Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117  
Fax:954-935-3639
Cell:954-683-4459


-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L



I can't figure out how to specify a constant date for a column 
in a SQL*Loader control file and can't find any examples.

I tried:
  LOADDATE  CONSTANT "TO_DATE('20030626', 'MMDD')",
but I get this error message in the log file:
  ORA-01858: a non-numeric character was found where a numeric was expected

Can anyone tell me the correct syntax?

David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: David Lewandowski
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Koivu, Lisa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: RE: SHMMAX setting on Sun 4800 box

[chao_ping]

Richard Ji,ÄúºÃ£¡
Hi £¬ how do you know that ISM is used even if multiple shared memory 
segment are used in solaris 8?



=== 2003-06-26 09:24:00 ÄúÔÚÀ´ÐÅÖÐдµÀ£º===

>Matt,
>
>Your SGA can still be larger than 4G even if SHMMAX is not larger than
>4G.  When SGA size > SHMMAX, mutilple shared memeory segements will
>be allocated.  There used to be a claim that if you can fit the SGA
>into a single shared memory segment, ISM will be used.  This might
>be true for older version of Solaris, but I have seen ISM being used
>even when mutiple shared memory segments are allocated on Solaris 8.
>
>Regards,
>
>Richard
>
>-Original Message-
>Sent: Thursday, June 26, 2003 10:55 AM
>To: Multiple recipients of list ORACLE-L
>
>
>
>As long as you're running 64-bit solaris and 64-bit oracle, you can set
>SHMMAX to be larger than 4G, which will allow your SGA to be larger than
>4G.  Keep an eye on your overall memory pressure, though, to make sure
>you don't oversubscribe the system.  If you're running Solaris 8/9
>and/or using some sort of direct i/o (raw devices, veritas, etc.) you're
>good to go at that point.  If you're running 7 or 2.6 (can you even run
>2.6 and 7 on the 4800?  I doubt it), you'll want to turn on
>priority_paging in /etc/system to keep your sga from getting paged out
>in favor of filesystem buffers.
>
>Thanks,
>Matt
>
>--
>Matthew Zito
>GridApp Systems
>Email: [EMAIL PROTECTED]
>Cell: 646-220-3551
>Phone: 212-358-8211 x 359
>http://www.gridapp.com
>
>> -Original Message-
>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On 
>> Behalf Of Zhai Jingmin
>> Sent: Thursday, June 26, 2003 5:34 AM
>> To: Multiple recipients of list ORACLE-L
>> Subject: SHMMAX setting on Sun 4800 box
>> 
>> 
>> Hello, List,
>> 
>> Our Sun Fire 4800 server have 8 CPUs and 16G memory.I set the 
>> SHMMAX kernel parameter to 4294967295 according to 
>> installation guide on metalink.The SHMMAX is "max shared 
>> memory segment size".What's the relationship between SHMMAX 
>> and max size of SGA?Because our oracle's SGA may exceed 
>> 4G,can I set this parameter to 8G,10G?
>> 
>> Any suggestion is appreciated.
>> 
>> Thanks, 
>> 
>> Zhai Jingmin
>> [EMAIL PROTECTED]
>> 2003-06-26
>> 
>> 
>> 
>> -- 
>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> -- 
>> Author: Zhai Jingmin
>>   INET: [EMAIL PROTECTED]
>> 
>> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>> San Diego, California-- Mailing list and web hosting services
>> -
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') 
>> and in the message BODY, include a line containing: UNSUB 
>> ORACLE-L (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: Matthew Zito
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>San Diego, California-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(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).

= = = = = = = = = = = = = = = = = = = =


ÖÂ
Àñ£¡
 
 
chao_ping
[EMAIL PROTECTED]
2003-06-27



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: chao_ping
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line conta

RE: 9i OCP Details

[Rachel Carmichael]

you find the classes (I took them with Scott for 8i) by searching the
Oracle education site, and searching on seminars.

They aren't given often and it's likely that travel will be necessary


--- Pete Sharman <[EMAIL PROTECTED]> wrote:
> Well that not too surprising.  Just about everything Scott does (he
> still
> works with Oracle) is very, very good.  Most recent thing I've seen
> him
> working on was the OCM exam, which no doubt explains why it was so
> hard!
> 
> I believe the Internals course morphed into a series of internal
> courses
> (i.e. only Oracle staff can attend) that are three to five days long.
>  There
> are now 8 of them.  They are also presented to customers as one day
> seminars, by the same instructors that run the internal versions. 
> Not too
> sure how you locate them, but they may be marketed with a course code
> starting with DSI (Data Server Internals).
> 
> 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-
> Gogala, Mladen
> Sent: Thursday, June 26, 2003 8:36 AM
> To: Multiple recipients of list ORACLE-L
> 
> You are talking about Scott Gossett jr. I took that
> internals course and it was good. It was very, very good.
> 
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
> 
> 
> -Original Message-
> Sent: Thursday, June 26, 2003 10:25 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Gudmundur Bjarni Josepsson wrote:
> 
> >>If you want real advanced courses go with [...] what Jonathan Lewis
> 
> >>or guys at his level provide.
> >>
> >>
> >
> >I have to agree on this.  I've had the good fortune to sit in on
> Lewis's
> >'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and no
> course
> >I ever took from Oracle Education comes close to what these guys
> offer
> >(and I've taken quite a few).
> >
> >  
> >
> 
> The only course from Oracle Education that's come close for me is the
> 
> internals course that I took about 3 or 4 years ago.  I haven't seen
> it 
> offered recently.  The instructor was Scott somebody, who was, I was 
> told, THE Scott or Scott/Tiger fame.
> 
> I second the recommendation for Jonathan Lewis; his Cost-Based 
> Optimization course was well taught and I found it very useful.
> 
> Craig Shallahamer (www.orapub.com) also offers some worthwhile
> courses 
> and is a very good instructor.
> 
> I'll find out about the Hotsos Clinic this August in Denver.  Looking
> 
> forward to it.
> 
> Glenn Stauffer
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Glenn Stauffer
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: Gogala, Mladen
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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).


__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California

salary question

[Helen J Mitchell]

Does anyone have information about salaries in Denver?  I looked at salary.com and saw 
the median
salary was $72K.  Does anyone have any opinions?


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Helen J Mitchell
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Index question

[Teresita Castro]

Where I can see the explain Plan?... I have TOAD 
but is a try version and I don't have this option activated,  can I see it 
in another program?
 
And where I have to define this 
option:
 optimizer_mode = Choose  
 timed_statistics = true 
 
Michael:
Sobre tus vacaciones como estaras cerca de Guadalaja, 
te recomiendo visitar estar ciudad, y  tomar el tur del tren Tequita 
express. Yo no he ido a puerto Vallarte pero la gente de por alla es muy 
amigable y servicial, buena suerte !!
>>> [EMAIL PROTECTED] 06/25/03 09:59PM 
>>>
First, Your english is 
excellent or  "Usted habla inglés muy bien. "
 
Assuming you are running 
version 8i or better 
 
Have you analyzed the tables 
you are querying against ?  You may not need
to force a rule as the CBO will 
try to find the quickest way.   It looks like
you are using the RBO by 
default.
 
Do you have optimizer_mode = 
Choose  ?
Do you have timed_statistics = 
true ?
 
Have you run your queries 
through an explain plan ?  If not this will
show you the execution path and 
a relative cost of each statement.
You can manipulate your SQL to 
see different costs as you change the
statement.
 
Also,  Your queries could 
have been run while competing for resources in
one case and maybe not in 
another case.  Run both queries in the same 
environment.  

 
I have found that small tables 
don't need indexes for the most part although this
is not a hard and fast 
rule.   You must go through the process.
 
 Espero que eso lo ayude y 
la buena suerte a usted.   Espero verlo en 
Puerto Vallarta Alguna Vez 
pronto en la playa con un margarita. Hasta Luego.   

 
Miquel.
 

  -Original 
  Message-From: Teresita Castro 
  [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003 
  7:04 PMTo: Multiple recipients of list ORACLE-LSubject: 
  Re: Index question
  Hi!!
   
  Let explain more about my 
  situation.
  The company that I work for is a 
  chain of stores around some city's on Mexico, they bought Lawson a system that 
  uses Oracle to manage the data bases, at first they use SQL Server 2000, but I 
  wasn't enough to manager all the information.
  The structure of the table is all ready done 
  and I have to learn it to do some reports that Lawson don't have, change or 
  delete information and export some information to dbf files. Because we 
  was using SQL Server I used Store procedures to return  the select 
  result to VB recordset and the I pass the select result to Crystal Report 
  or to a DBF file.
  Well  I see that in Oracle the store 
  procedure do not returns the result set has easy has SQL Server so I use and 
  statement that after execute it returns me the result in a record 
  set.
   
  sQuery = "SELECT COMPANY,LOCATION, 
  R_NAME FROM ICLOCATION " & 
  _ "WHERE COMPANY=2000 OR 
  COMPANY=2001 OR COMPANY=2002 order by COMPANY,LOCATION"Set recRS = New 
  ADODB.RecordsetrecRS.Open sQuery, gcnOracle, adOpenForwardOnly, 
  adLockReadOnly, adCmdText
   
  or execute a delete or update 
  statement
   
  sSQL = "update  /*+ INDEX(itemloc 
  ITLSET2) */ itemloc set average_cost =" & costo & " where 
  (company='2000') and item= '" & Arti & "'"gcnOracle.Execute 
  sSQL
  gcnOracle.Execute "Commit", 
  dbSQLPassThrough   
  Back to my problem:
  In this case the update of the average cost 
  has to be done on the table Item location ( ITEMLOC)  that have all the 
  item that each location( store) have. The locations have a company, when 
  we changes the average cost is per company ( each company represent a 
  different city) 
   
  So my boss  execute the update 
  statement 
   
  
  sSQL = "update  itemloc set 
  average_cost =" & costo & " where (company='2000') and item= '" & 
  Arti & "'"
  and she told me that per item  it 
  takes like 10 seconds.
  After read the article that I mention 
  she changes the statement to this :
   sSQL = "update  /*+ 
  INDEX(itemloc ITLSET2) */ itemloc set average_cost =" & costo & " 
  where (company='2000') and item= '" & Arti & "'"
   
  ITLSET2 is a index that have company(1), 
  location(2) and Item(3) and it takes 2 
  seconds per item, so that is way she is convinced that we have to uses the 
  /*+ INDEX(itemloc ITLSET2) */ in all of our select, update or 
  delete statement.
   
  I hope you undestant my English and my problem 
  too, because I have to do some really complicated queries that have 
  like 3 or 5 tables in them, and using this method will give me some 
  serious complications, maybe in this case (change of the average cost) is not 
  too dangerous. 
  But I have to give her strong statements 
  to change her mind.
   
  Thanks for everything 
  friends!!
  >>> [EMAIL PROTECTED] 06/25/03 06:10PM 
  >>>Teresita,I don't fully understand whether adding 
  or removing a hint caused theproblem but like Stephane said - you should 
  probably stay away from themfor now.  If adding a hint decreased 
  performance then you have proved thisfor yourself.A couple of 
  important points:* U

RE: CURSOR_SPACE_FOR_TIME

[Gogala, Mladen]

Do you have the bug number?

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Thursday, June 26, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L


We are ran into a bug the other day where oracle dumps core with a ORA-07445
error. We tracked it down to a Context related bug where PL/SQL callout
during heavy shared pool load cause the ora-07745 core dump. The suggested
workarounds are to set CURSOR_SPACE_FOR_TIME to true. This is in 8.1.7.4.0
and is reportedly fixed in 9i.  What are listers experience with this
parameter? On our system it sound like setting this without altering the
size of the shared pool would be potentially dangerous and could cause more
problems that leaving it alone.

This error is not a critical one to business functions and users here. (so
far)


Thanks

Brad Odland
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Odland, Brad
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: COMMIT's within cursor for loops

[Reginald . W . Bailey]

I prefer to create an inner block using BEGIN and END inside the loop. This
isolates the DML statements. The COMMIT is issued inside the BEGIN and END
block.

RWB



Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management
Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)
[EMAIL PROTECTED]
[EMAIL PROTECTED]



   
  
[EMAIL PROTECTED]  
 
ic.com   To: [EMAIL PROTECTED] 
   
Sent by: cc:   
  
[EMAIL PROTECTED]   Subject: COMMIT's within cursor for 
loops   
ity.com
  
   
  
   
  
06/25/2003 
  
12:40 PM   
  
Please respond 
  
to ORACLE-L
  
   
  
   
  





Hi All,

Can somebody please clear up some issues about issuing commits during
CURSOR
FOR LOOPS

I have done some research within Metalink and the ORACLE-L FAQ but am still
a tad bit confused.

Are the following statements regarding cursors TRUE

1. If you issue a commit within a for cursor loop you release all locks
regardless of whether you're cursor statement has a FOR UPDATE statement in
it or there is a WHERE CURRENT statement within the loop.

2. If you are using OPEN CURSOR...FETCH INTO statements to get the data the
same rule applies.

Thanks,

N.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Nuala Cullen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Constant Date in SQL*Loader Control File

[Ron Rogers]

David,
 Try removing the word "CONSTANT" from the string.
LOADDATE   "TO_DATE('20030626', 'MMDD')",
Ron
>>> [EMAIL PROTECTED] 06/26/03 02:09PM >>>
Hi David, 

Here's one of my control files, it works for me...  LOAD_DATE is indeed
a date field. 

OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE  ASCNAME_RAW
(
ID  RECNUM,
AN_SITE POSITION(1:2) CHAR 
NULLIF AN_SITE=BLANKS, 
AN_ASSOC_NO POSITION(3:6) CHAR 
NULLIF AN_ASSOC_NO=BLANKS, 
AN_ASSOC_NAME   POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR 
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,  
  
LOAD_DATE   SYSDATE   
)

Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117  
Fax:954-935-3639
Cell:954-683-4459


-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L



I can't figure out how to specify a constant date for a column 
in a SQL*Loader control file and can't find any examples.

I tried:
  LOADDATE  CONSTANT "TO_DATE('20030626', 'MMDD')",
but I get this error message in the log file:
  ORA-01858: a non-numeric character was found where a numeric was
expected

Can anyone tell me the correct syntax?

David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: David Lewandowski
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Koivu, Lisa
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).

Re: 10046 Trace file questions

[Jared . Still]

Dan,

1-3 sound very reasonable to me.

4 however I don't quite follow.  Tablespace/file passed in as a bind 
variable?
Can't seem to quite follow the logic in 4 either.It appears that you 
have a
DD managed temp tablespace, and you're seeing sorts take place as 
indicated
by extent movement activity.

Is that correct?

Aren't there waits on the temp tablespace/file itself?

Can you post relevant section of the trace?

Jared






Daniel Fink <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 06/26/2003 08:35 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:10046 Trace file questions


I am reviewing a 10046 trace file and have come across several questions 
(actually more like hypothesis that I want to confirm).

1) The first user statement executed waits only on SQL*Net to/from client 
and does not wait on any db file activity. I presume this means that the 
data was found in the buffer cache and did not have to be read on disk.

2) The next user statement is preceeded by a recursive query on view$. The 
user statement accesses a view, so this recursive query is part of the 
object resolution phase of parsing.

3) This 3rd user statement has a substantial number of waits on db file 
activities. This indicates disk access.

4) A subsequent statement has several space management (activity on fet$ 
and uet$) activities. The tablespace/file that is passed in as a bind 
variable are associated with a 'temp' tablespace. However, the tablespace 
is set up as dictionary managed. This indicates that sorting is being done 
by this operation and that the sort segment space management is being 
tracked in the data dictionary.

Thoughts?



daniel.fink.vcf
Description: Binary data

Re: RE: Index question

[rgaffuri]

go to the rdbms/admin directory in your oracle home(i forget the full path so you will 
have to do a search).

look for the script utlxplan.sql

run it in a directory where you can create a table. this will create the plan table. 

when you want to see the result of a query plan type

set autotrace on

run the query. results will follow.

if you just want the results type

set autotrace traceonly.

to interpret go to otn.oracle.com its in the performance guide, there is also a really 
good explain plan doc on metalink. 
> 
> From: "Teresita Castro" <[EMAIL PROTECTED]>
> Date: 2003/06/26 Thu PM 02:24:46 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: Index question
> 
> Where I can see the explain Plan?... I have TOAD but is a try version and I don't 
> have this option activated,  can I see it in another program?
> 
> And where I have to define this option:
>  optimizer_mode = Choose  
>  timed_statistics = true 
> 
> Michael:
> Sobre tus vacaciones como estaras cerca de Guadalaja, te recomiendo visitar estar 
> ciudad, y  tomar el tur del tren Tequita express. Yo no he ido a puerto Vallarte 
> pero la gente de por alla es muy amigable y servicial, buena suerte !!
> 
> >>> [EMAIL PROTECTED] 06/25/03 09:59PM >>>
> 
> First, Your english is excellent or  "Usted habla inglés muy bien. "
> 
> Assuming you are running version 8i or better 
> 
> Have you analyzed the tables you are querying against ?  You may not need
> to force a rule as the CBO will try to find the quickest way.   It looks like
> you are using the RBO by default.
> 
> Do you have optimizer_mode = Choose  ?
> Do you have timed_statistics = true ?
> 
> Have you run your queries through an explain plan ?  If not this will
> show you the execution path and a relative cost of each statement.
> You can manipulate your SQL to see different costs as you change the
> statement.
> 
> Also,  Your queries could have been run while competing for resources in
> one case and maybe not in another case.  Run both queries in the same 
> environment.  
> 
> I have found that small tables don't need indexes for the most part although this
> is not a hard and fast rule.   You must go through the process.
> 
>  Espero que eso lo ayude y la buena suerte a usted.   Espero verlo en 
> Puerto Vallarta Alguna Vez pronto en la playa con un margarita. Hasta Luego.   
> 
> Miquel.
> 
> -Original Message-
> Sent: Wednesday, June 25, 2003 7:04 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi!!
> 
> Let explain more about my situation.
> The company that I work for is a chain of stores around some city's on Mexico, they 
> bought Lawson a system that uses Oracle to manage the data bases, at first they use 
> SQL Server 2000, but I wasn't enough to manager all the information.
> The structure of the table is all ready done and I have to learn it to do some 
> reports that Lawson don't have, change or delete information and export some 
> information to dbf files. Because we was using SQL Server I used Store procedures to 
> return  the select result to VB recordset and the I pass the select result to 
> Crystal Report or to a DBF file.
> Well  I see that in Oracle the store procedure do not returns the result set has 
> easy has SQL Server so I use and statement that after execute it returns me the 
> result in a record set.
> 
> sQuery = "SELECT COMPANY,LOCATION, R_NAME FROM ICLOCATION " & _
>  "WHERE COMPANY=2000 OR COMPANY=2001 OR COMPANY=2002 order by 
> COMPANY,LOCATION"
> Set recRS = New ADODB.Recordset
> recRS.Open sQuery, gcnOracle, adOpenForwardOnly, adLockReadOnly, adCmdText
> 
> or execute a delete or update statement
> 
> sSQL = "update  /*+ INDEX(itemloc ITLSET2) */ itemloc set average_cost =" & costo & 
> " where (company='2000') and item= '" & Arti & "'"
> gcnOracle.Execute sSQL
> gcnOracle.Execute "Commit", dbSQLPassThrough
>
> Back to my problem:
> In this case the update of the average cost has to be done on the table Item 
> location ( ITEMLOC)  that have all the item that each location( store) have. The 
> locations have a company, when we changes the average cost is per company ( each 
> company represent a different city) 
> 
> So my boss  execute the update statement 
> 
> sSQL = "update  itemloc set average_cost =" & costo & " where (company='2000') and 
> item= '" & Arti & "'"
> 
> and she told me that per item  it takes like 10 seconds.
> After read the article that I mention she changes the statement to this :
>  sSQL = "update  /*+ INDEX(itemloc ITLSET2) */ itemloc set average_cost =" & costo & 
> " where (company='2000') and item= '" & Arti & "'"
> 
> ITLSET2 is a index that have company(1), location(2) and Item(3) and it takes 2 
> seconds per item, so that is way she is convinced that we have to uses the /*+ 
> INDEX(itemloc ITLSET2) */ in all of our select, update or delete statement.
> 
> I hope you undestant my English and my problem too, because I have to do some really 
> comp

RE: RE: SHMMAX setting on Sun 4800 box

[Richard Ji]

Chao,

There are couple of ways to confirm whether ISM is used or not.
The easiest way on Soalris 8 is run pmap on an Oracle process
and you will see "ism" in those shared memory segments.  Below
I set my SHMMAX to 100m and my SGA to 300m+.

0400 10K read/write/exec/shared  [ ism shmid=0x2 ]
04004000 10K read/write/exec/shared  [ ism shmid=0x3 ]
04008000  73728K read/write/exec/shared  [ ism shmid=0x4 ]
0400C000  40456K read/write/exec/shared  [ ism shmid=0x5 ]

The better way and correct way is to do what's suggested by "Solaris
Internal" to use crash utility to determine whether ISM is used.

There is also an article on Steve Adam's web site on how to prove it.

Regards,

Richard Ji
-Original Message-
Sent: Thursday, June 26, 2003 2:17 PM
To: Multiple recipients of list ORACLE-L


Richard Ji,ÄúºÃ£¡
Hi £¬ how do you know that ISM is used even if multiple shared memory 
segment are used in solaris 8?



=== 2003-06-26 09:24:00 ÄúÔÚÀ´ÐÅÖÐдµÀ£º===

>Matt,
>
>Your SGA can still be larger than 4G even if SHMMAX is not larger than
>4G.  When SGA size > SHMMAX, mutilple shared memeory segements will
>be allocated.  There used to be a claim that if you can fit the SGA
>into a single shared memory segment, ISM will be used.  This might
>be true for older version of Solaris, but I have seen ISM being used
>even when mutiple shared memory segments are allocated on Solaris 8.
>
>Regards,
>
>Richard
>
>-Original Message-
>Sent: Thursday, June 26, 2003 10:55 AM
>To: Multiple recipients of list ORACLE-L
>
>
>
>As long as you're running 64-bit solaris and 64-bit oracle, you can set
>SHMMAX to be larger than 4G, which will allow your SGA to be larger than
>4G.  Keep an eye on your overall memory pressure, though, to make sure
>you don't oversubscribe the system.  If you're running Solaris 8/9
>and/or using some sort of direct i/o (raw devices, veritas, etc.) you're
>good to go at that point.  If you're running 7 or 2.6 (can you even run
>2.6 and 7 on the 4800?  I doubt it), you'll want to turn on
>priority_paging in /etc/system to keep your sga from getting paged out
>in favor of filesystem buffers.
>
>Thanks,
>Matt
>
>--
>Matthew Zito
>GridApp Systems
>Email: [EMAIL PROTECTED]
>Cell: 646-220-3551
>Phone: 212-358-8211 x 359
>http://www.gridapp.com
>
>> -Original Message-
>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On 
>> Behalf Of Zhai Jingmin
>> Sent: Thursday, June 26, 2003 5:34 AM
>> To: Multiple recipients of list ORACLE-L
>> Subject: SHMMAX setting on Sun 4800 box
>> 
>> 
>> Hello, List,
>> 
>> Our Sun Fire 4800 server have 8 CPUs and 16G memory.I set the 
>> SHMMAX kernel parameter to 4294967295 according to 
>> installation guide on metalink.The SHMMAX is "max shared 
>> memory segment size".What's the relationship between SHMMAX 
>> and max size of SGA?Because our oracle's SGA may exceed 
>> 4G,can I set this parameter to 8G,10G?
>> 
>> Any suggestion is appreciated.
>> 
>> Thanks, 
>> 
>> Zhai Jingmin
>> [EMAIL PROTECTED]
>> 2003-06-26
>> 
>> 
>> 
>> -- 
>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> -- 
>> Author: Zhai Jingmin
>>   INET: [EMAIL PROTECTED]
>> 
>> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>> San Diego, California-- Mailing list and web hosting services
>> -
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') 
>> and in the message BODY, include a line containing: UNSUB 
>> ORACLE-L (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: Matthew Zito
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>San Diego, California-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(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: UNS

RE: Constant Date in SQL*Loader Control File

[David Lewandowski]

Thanks Lisa.  But regrettably my LOADDATE isn't always SYSDATE.  Do you know

the syntax for an arbitrary date?

Dave

-Original Message-
Sent: Thursday, June 26, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L


Hi David, 

Here's one of my control files, it works for me...  LOAD_DATE is indeed a
date field. 

OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE  ASCNAME_RAW
(
ID  RECNUM,
AN_SITE POSITION(1:2) CHAR 
NULLIF AN_SITE=BLANKS, 
AN_ASSOC_NO POSITION(3:6) CHAR 
NULLIF AN_ASSOC_NO=BLANKS, 
AN_ASSOC_NAME   POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR 
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,

LOAD_DATE   SYSDATE   
)

Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117  
Fax:954-935-3639
Cell:954-683-4459


-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L



I can't figure out how to specify a constant date for a column 
in a SQL*Loader control file and can't find any examples.

I tried:
  LOADDATE  CONSTANT "TO_DATE('20030626', 'MMDD')",
but I get this error message in the log file:
  ORA-01858: a non-numeric character was found where a numeric was expected

Can anyone tell me the correct syntax?

David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: David Lewandowski
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Koivu, Lisa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: David Lewandowski
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Register vs. Load of JDBC driver

[Gogala, Mladen]

Hi List!
I'm not a Java programer so please, forgive me my (blissful) ignorance. My
developers
are experiencing wild differences when connecting using
driverManager.registerDriver vs.
Class.forName (dynamic loading). Does anybody have any clue why is that? Any
articles
about registering vs. loading? 
Thanks!

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Constant Date in SQL*Loader Control File

[Goulet, Dick]

Dave,

I believe if you specify in the .cal file:

loaddate "to_date('20030626', 'MMDD')",

That will default the date to what you want.  Now if the date is sometimes specified 
that's a different story.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 

-Original Message-
Sent: Thursday, June 26, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L



Thanks Lisa.  But regrettably my LOADDATE isn't always SYSDATE.  Do you know

the syntax for an arbitrary date?

Dave

-Original Message-
Sent: Thursday, June 26, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L


Hi David, 

Here's one of my control files, it works for me...  LOAD_DATE is indeed a
date field. 

OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE  ASCNAME_RAW
(
ID  RECNUM,
AN_SITE POSITION(1:2) CHAR 
NULLIF AN_SITE=BLANKS, 
AN_ASSOC_NO POSITION(3:6) CHAR 
NULLIF AN_ASSOC_NO=BLANKS, 
AN_ASSOC_NAME   POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR 
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,

LOAD_DATE   SYSDATE   
)

Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117  
Fax:954-935-3639
Cell:954-683-4459


-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L



I can't figure out how to specify a constant date for a column 
in a SQL*Loader control file and can't find any examples.

I tried:
  LOADDATE  CONSTANT "TO_DATE('20030626', 'MMDD')",
but I get this error message in the log file:
  ORA-01858: a non-numeric character was found where a numeric was expected

Can anyone tell me the correct syntax?

David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: David Lewandowski
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Koivu, Lisa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: David Lewandowski
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Goulet, Dick
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Constant Date in SQL*Loader Control File

[Koivu, Lisa]

Hi David, 

Ron's suggestion worked, I just tried it:

LOAD_DATE   "TO_DATE('010103','MMDDYY')"

Give it a try.

-Original Message-
Sent: Thursday, June 26, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L



Thanks Lisa.  But regrettably my LOADDATE isn't always SYSDATE.  Do you know

the syntax for an arbitrary date?

Dave

-Original Message-
Sent: Thursday, June 26, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L


Hi David, 

Here's one of my control files, it works for me...  LOAD_DATE is indeed a
date field. 

OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE  ASCNAME_RAW
(
ID  RECNUM,
AN_SITE POSITION(1:2) CHAR 
NULLIF AN_SITE=BLANKS, 
AN_ASSOC_NO POSITION(3:6) CHAR 
NULLIF AN_ASSOC_NO=BLANKS, 
AN_ASSOC_NAME   POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR 
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,

LOAD_DATE   SYSDATE   
)

Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117  
Fax:954-935-3639
Cell:954-683-4459


-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L



I can't figure out how to specify a constant date for a column 
in a SQL*Loader control file and can't find any examples.

I tried:
  LOADDATE  CONSTANT "TO_DATE('20030626', 'MMDD')",
but I get this error message in the log file:
  ORA-01858: a non-numeric character was found where a numeric was expected

Can anyone tell me the correct syntax?

David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: David Lewandowski
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Koivu, Lisa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: David Lewandowski
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

"The sender believes that this E-Mail and any attachments were free of any virus, 
worm, Trojan horse, and/or malicious code when sent. This message and its attachments 
could have been infected during transmission.  By reading the message and opening any 
attachments, the recipient accepts full responsibility for taking proactive and 
remedial action about viruses and other defects. The sender's business entity is not 
liable for any loss or damage arising in any way from this message or its attachments."


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Koivu, Lisa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
--

RE: COMMIT's within cursor for loops

[Steve McClure]

>I prefer to create an inner block using BEGIN and END inside the loop. This
>isolates the DML statements. The COMMIT is issued inside the BEGIN and END
>block.
>
>RWB

That allows you to avoid invalidating the cursor established "for update"?
I dont' see how the two are related.  You just created an exception block
within the cursor loop.  Or am i misunderstanding what you are saying here?

Regarding the original question commiting within a cursor for loop.  It is
allowable if you do not create the cursor "for update".  If you created the
cursor using "for update", you will not only NOT retain a lock on the record
set, you will get an error indicating an invalid cursor.

Steve McClure

-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 11:45 AM
To: Multiple recipients of list ORACLE-L



I prefer to create an inner block using BEGIN and END inside the loop. This
isolates the DML statements. The COMMIT is issued inside the BEGIN and END
block.

RWB




Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management
Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)
[EMAIL PROTECTED]
[EMAIL PROTECTED]





[EMAIL PROTECTED]
ic.com   To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]   Subject: COMMIT's within cursor
for loops
ity.com


06/25/2003
12:40 PM
Please respond
to ORACLE-L







Hi All,

Can somebody please clear up some issues about issuing commits during
CURSOR
FOR LOOPS

I have done some research within Metalink and the ORACLE-L FAQ but am still
a tad bit confused.

Are the following statements regarding cursors TRUE

1. If you issue a commit within a for cursor loop you release all locks
regardless of whether you're cursor statement has a FOR UPDATE statement in
it or there is a WHERE CURRENT statement within the loop.

2. If you are using OPEN CURSOR...FETCH INTO statements to get the data the
same rule applies.

Thanks,

N.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Nuala Cullen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).


-- 
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).

RE: Register vs. Load of JDBC driver

[Vergara, Michael (TEM)]

Mladen:

I can't help you with your question, but FatCity also supports a
list called the ODTUG-JAVA-L list.  Those folks might be able to
help.

HTH,
Mike

-Original Message-
Sent: Thursday, June 26, 2003 12:35 PM
To: Multiple recipients of list ORACLE-L


Hi List!
I'm not a Java programer so please, forgive me my (blissful) ignorance. My
developers
are experiencing wild differences when connecting using
driverManager.registerDriver vs.
Class.forName (dynamic loading). Does anybody have any clue why is that? Any
articles
about registering vs. loading? 
Thanks!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vergara, Michael (TEM)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: nfile parameter problem

[Reddy, Madhusudana]

TANEL,
A wonderful demonstration  !!!

we are already using the Java connection pool, DO you think we still have to use MTS 
?? or which is better ??


Dennis,
We really have a big beast ( 1.2T OLTP ) here in terms of hardware resources. Its not 
the problem with memory or CPU or I/O.

John, Thanks for reply, as you pointed more # of files is big problem, but we already 
tried to minimize it , 

What is your point on Java connection pooling (VS) MTS ???

Any pointers will be appreciated !!


Thanks,

Madhu Reddy
X13944


-Original Message-
Sent: Wednesday, June 25, 2003 3:45 PM
To: Multiple recipients of list ORACLE-L


Hi!



I think you should go with some sort of connection pooling and/or MTS.



> My main question to you all is : Is there any way to reduce the # of open
files opened by Oracle processes ??



The issue is, that with dedicated server every process has to open a
datafile if it tries to read a data block which isn't already in buffer
cache. If you got let say 1000 connections with dedicated servers (thus 1000
server processes) and 500 datafiles, the worst case is 500 000 used file
handlers. Of course, this is really the worst case, when every process has
had to read a block from every file.



When going with MTS, you actually have 50 or so processes to serve all 1000
of your connections, thus the need for file handlers is lot smaller. (I
think that in Windows going with dedicated servers isn't a procblem, because
it's single process architecture - threads can share file handlers between
each other, right?)



I wrote this mail in html, because I added my testing about datafiles with
comments here.



Cheers,

Tanel.



--



bash-2.03$ uname -a

SunOS blade.nt 5.8 Generic_108528-09 sun4u sparc SUNW,Sun-Blade-100



bash-2.03$ sqlplus system/[EMAIL PROTECTED]



SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jun 25 21:35:04 2003



(c) Copyright 2000 Oracle Corporation.  All rights reserved.





Connected to:

Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production

With the Partitioning option

JServer Release 8.1.7.1.0 - Production



First, lets check whether I'm using a dedicated server (MTS's can have files
open as a result of other sessions request). Also finding the OS PID to
compare with fuser result later on.



SQL> select server from v$session where sid = (select sid from v$mystat
where rownum = 1);



SERVER

-

DEDICATED



SQL> select p.spid

from v$process p, v$session s

where s.sid = (select sid from v$mystat where rownum = 1)

and p.addr = s.paddr;

  234

SPID

-

29064  -- note my OS PID for this session



Now create a tablespace and a table for testing



SQL> create tablespace test2 datafile '/u01/oradata/TEST817/test2_01.dbf'
size 1m autoextend off;



Tablespace created.



SQL> create table t (a number) tablespace test2;



Table created.



SQL> insert into t values (1);



1 row created.



SQL> commit;



Commit complete.



Now check with fuser, which processes are holding the datafile open



SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf:29064o 390o



My process is there, because I just created the tablespace

Now I take the tablespace offline/online, to make sure it's blocks in buffer
cache are invalidated



SQL> alter tablespace test2 offline;



Tablespace altered.



SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf:



SQL> alter tablespace test2 online;



Tablespace altered.



SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf:29064o 390o



And exit and log on again, to get a new OS process id for example



SQL> exit

Disconnected from Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production

With the Partitioning option

JServer Release 8.1.7.1.0 - Production

bash-2.03$ sqlplus system/[EMAIL PROTECTED]



SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jun 25 21:39:10 2003



(c) Copyright 2000 Oracle Corporation.  All rights reserved.





Connected to:

Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production

With the Partitioning option

JServer Release 8.1.7.1.0 - Production



SQL> select server from v$session where sid = (select sid from v$mystat
where rownum = 1);



SERVER

-

DEDICATED



SQL> select p.spid

from v$process p, v$session s

where s.sid = (select sid from v$mystat where rownum = 1)

and p.addr = s.paddr;

  234

SPID

-

29070 -- new OS PID for my connection



I havent done anything in this session, let's see who have the datafile open



SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf:  390o



Wonder who is it?



SQL> !ps -ef | grep 390

  ora817   390 1  0   Dec 19 ?1:18 ora_dbw0_TEST817

  ora817 29072 29068  0 21:39:44 pts/30:00 /bin/bash -c ps -ef | grep
390

  ora817 29074 29072  0 21:39:44 pts/30:00 /bin/bash -c ps -ef 

RE: Register vs. Load of JDBC driver

[Goulet, Dick]

Mladen,

I'm forwarding this onto our Java GURU.  If he has an answer I believe he'll 
reply.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 

-Original Message-
Sent: Thursday, June 26, 2003 3:35 PM
To: Multiple recipients of list ORACLE-L


Hi List!
I'm not a Java programer so please, forgive me my (blissful) ignorance. My
developers
are experiencing wild differences when connecting using
driverManager.registerDriver vs.
Class.forName (dynamic loading). Does anybody have any clue why is that? Any
articles
about registering vs. loading? 
Thanks!

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Goulet, Dick
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Constant Date in SQL*Loader Control File

[Daniel Fink]

David,
Have you tried removing the " around the TO_DATE call? The " indicate a 
character string and Oracle is unable to translate that string into something 
acceptable for a DATE datatype.


David Lewandowski wrote:
> 
> Thanks Lisa.  But regrettably my LOADDATE isn't always SYSDATE.  Do you know
> 
> the syntax for an arbitrary date?
> 
> Dave
> 
> -Original Message-
> Sent: Thursday, June 26, 2003 1:09 PM
> To: Multiple recipients of list ORACLE-L
> 
> Hi David,
> 
> Here's one of my control files, it works for me...  LOAD_DATE is indeed a
> date field.
> 
> OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
> UNRECOVERABLE
> LOAD DATA
> INFILE 'D:\FTPRoot\vegas\ascname.txt'
> BADFILE 'D:\data\vegas\log\ascname.bad'
> DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
> TRUNCATE
> INTO TABLE  ASCNAME_RAW
> (
> ID  RECNUM,
> AN_SITE POSITION(1:2) CHAR
> NULLIF AN_SITE=BLANKS,
> AN_ASSOC_NO POSITION(3:6) CHAR
> NULLIF AN_ASSOC_NO=BLANKS,
> AN_ASSOC_NAME   POSITION(7:36) CHAR
> NULLIF AN_ASSOC_NAME=BLANKS,
> AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR
> NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,
> 
> LOAD_DATE   SYSDATE
> )
> 
> Lisa Koivu
> Oracle Database Monkey
> Fairfield Resorts, Inc.
> 5259 Coconut Creek Parkway
> Ft. Lauderdale, FL, USA  33063
> Office: 954-935-4117
> Fax:954-935-3639
> Cell:954-683-4459
> 
> -Original Message-
> Sent: Thursday, June 26, 2003 12:40 PM
> To: Multiple recipients of list ORACLE-L
> 
> I can't figure out how to specify a constant date for a column
> in a SQL*Loader control file and can't find any examples.
> 
> I tried:
>   LOADDATE  CONSTANT "TO_DATE('20030626', 'MMDD')",
> but I get this error message in the log file:
>   ORA-01858: a non-numeric character was found where a numeric was expected
> 
> Can anyone tell me the correct syntax?
> 
> David Lewandowski
> Focused Health Solutions
> [EMAIL PROTECTED]
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: David Lewandowski
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: Koivu, Lisa
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: David Lewandowski
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).begin:vcard 
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard

RE: 9i OCP Details

[Pete Sharman]

Quite right.  It was Bruce Scott (didn't he do something at IOUG this
year?) and I believe Tiger is (or presumably was since this was so long
ago and the poor beastie has presumably karked it) his cat.


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-
Rachel Carmichael
Sent: Thursday, June 26, 2003 8:45 AM
To: Multiple recipients of list ORACLE-L

Scott Gossett taught/teaches the Data Internals class and he is not the
Scott of Scott/tiger


--- Glenn Stauffer <[EMAIL PROTECTED]> wrote:
> Gudmundur Bjarni Josepsson wrote:
> 
> >>If you want real advanced courses go with [...] what Jonathan Lewis
> 
> >>or guys at his level provide.
> >>
> >>
> >
> >I have to agree on this.  I've had the good fortune to sit in on
> Lewis's
> >'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and no
> course
> >I ever took from Oracle Education comes close to what these guys
> offer
> >(and I've taken quite a few).
> >
> >  
> >
> 
> The only course from Oracle Education that's come close for me is the
> 
> internals course that I took about 3 or 4 years ago.  I haven't seen
> it 
> offered recently.  The instructor was Scott somebody, who was, I was 
> told, THE Scott or Scott/Tiger fame.
> 
> I second the recommendation for Jonathan Lewis; his Cost-Based 
> Optimization course was well taught and I found it very useful.
> 
> Craig Shallahamer (www.orapub.com) also offers some worthwhile
> courses 
> and is a very good instructor.
> 
> I'll find out about the Hotsos Clinic this August in Denver.  Looking
> 
> forward to it.
> 
> Glenn Stauffer
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Glenn Stauffer
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.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).

RE: COMMIT's within cursor for loops

[Ron Thomas]

"Thou Shalt Not Fetch Across Commits" was ingrained in me many, many moons ago.  It is 
poor practice
and can cause multitudes of problems.

Just my 2 cents.  YMMV.

Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
Each new user of a new system uncovers a new class of bugs. -- Kernighan


   
   
  [EMAIL PROTECTED]
   
  .com To:   [EMAIL PROTECTED] 

  Sent by: cc: 
   
  [EMAIL PROTECTED]Subject:  RE: COMMIT's within cursor 
for loops 
  .com 
   
   
   
   
   
  06/26/2003 01:49 
   
  PM   
   
  Please respond to
   
  ORACLE-L 
   
   
   
   
   




>I prefer to create an inner block using BEGIN and END inside the loop. This
>isolates the DML statements. The COMMIT is issued inside the BEGIN and END
>block.
>
>RWB

That allows you to avoid invalidating the cursor established "for update"?
I dont' see how the two are related.  You just created an exception block
within the cursor loop.  Or am i misunderstanding what you are saying here?

Regarding the original question commiting within a cursor for loop.  It is
allowable if you do not create the cursor "for update".  If you created the
cursor using "for update", you will not only NOT retain a lock on the record
set, you will get an error indicating an invalid cursor.

Steve McClure

-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 11:45 AM
To: Multiple recipients of list ORACLE-L



I prefer to create an inner block using BEGIN and END inside the loop. This
isolates the DML statements. The COMMIT is issued inside the BEGIN and END
block.

RWB




Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management
Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)
[EMAIL PROTECTED]
[EMAIL PROTECTED]





[EMAIL PROTECTED]
ic.com   To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]   Subject: COMMIT's within cursor
for loops
ity.com


06/25/2003
12:40 PM
Please respond
to ORACLE-L







Hi All,

Can somebody please clear up some issues about issuing commits during
CURSOR
FOR LOOPS

I have done some research within Metalink and the ORACLE-L FAQ but am still
a tad bit confused.

Are the following statements regarding cursors TRUE

1. If you issue a commit within a for cursor loop you release all locks
regardless of whether you're cursor statement has a FOR UPDATE statement in
it or there is a WHERE CURRENT statement within the loop.

2. If you are using OPEN CURSOR...FETCH INTO statements to get the data the
same rule applies.

Thanks,

N.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Nuala Cullen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
---

SQL*Loader Help -- Multiple rows into single column

[Bob Robert]

Gurus,

I have a special scenario to load data into tables
with SQL*Loader.

My SQL Loader data is not fixed format. It changes
from time to time. But there is a good pattern about
the data. Data which starts with letter "R" 
should go to table1 and data which starts with letter
"Z" should go to table2. Table1 data is always fixed
format where as table2 is kind of tricky. I would like
to load data which starts with letter "Z" into table2
as a single row.

For Example: (see my data at the bottom)
Tom is having 3 lines of data
Bob and Sam is having 4 lines of data
Joe is having 5 lines of data

Right now as per my SQL Loader Control file
(sql_load.ctl), all the data which starts with Z goes
into different rows (Tom --> 3 rows, Bob and Sam --> 4
rows, Joe --> 5 rows).

I would like to load four rows into table1 (it is
fine) and four rows into table2 (I am getting 16
rows).

I hope I explained properly.

FYI
Please take look at the following scripts.


-- Create Tables Script Start (sql_load.sql)
-- Sequence
create sequence table1_seq increment by 1 start with
1;
create sequence table2_seq increment by 1 start with
1;

-- Tables
create table table1
(serial_no number(5),
 name varchar2(10),
 amount number(4))
/

create table table2
(shipment_no number(5),
 details varchar2(1000))
/
-- Create Tables Script End (sql_load.sql)

-- SQL Loader Control file Start (sql_load.ctl)
options (rows=1, errors=1)
load data
infile 'c:\sql_load.txt'
badfile 'c:\sql_load.bad'
discardfile 'c:\sql_load.disc'
replace 
-- load table1
  into table table1
  when (1:1) = 'R' 
(serial_no position(1:1) "table1_seq.nextval", 
 name position(2:6) char,
 amount position(6:10) char)
-- load table2
   into table table2
   when (1:1) = 'Z' 
(shipment_no position(1:1) "table2_seq.nextval", 
 details position(2:81) char)
-- SQL Loader Control file End (sql_load.ctl)

-- SQL Loader Data File Start (sql_load.txt)
RTom  400
ZName: Tom
ZShip_To: New York
ZBill_To: Trenton

RBob  300
ZName: Bob
ZShip_To: Chicago
ZBill_To: Detroit
ZNotes: Best Customer Award

RSam  500
ZName: Sam
ZShip_To: Troy
ZBill_To: Dallas
ZNotes: Average Customer Award

RJoe  200
ZName: Joe
ZShip_To: Erie
ZBill_To: San Fransisco
ZNotes: Best Customer Award
ZSpecial Notes: Include Customer
-- SQL Loader Data File End (sql_load.txt)

Thanks,
Bob

__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bob Robert
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

redo log maintenance

[Koivu, Lisa]

Title: redo log maintenance






I'm reading through the 9.2 admin guide (yawn) and it says that the database must be mounted but not open to add, drop or rename redo logs.  I have done this many times with the database up and running, with no problems.  Why would it say this in the doco, or is the doco just plain wrong (again)?  It doesn't let you do anything to redo logs that aren't archived or are currently being used, what's the harm?

I don't get all the emails from the list - in fact I get none most of the time.  So please be sure to include my personal email address when responding. 

Thanks


Lisa Koivu

Oracle Database Monkey

Fairfield Resorts, Inc.


Orlando Office: 407-248-4277

Ft. Lauderdale Office: 954-935-4117  

Fax:    954-935-3639

Cell:    954-683-4459



"The sender believes that this E-Mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission.  By reading the message and opening any attachments, the recipient accepts full responsibility for taking proactive and remedial action about viruses and other defects. The sender's business entity is not liable for any loss or damage arising in any way from this message or its attachments."
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Koivu, Lisa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Constant Date in SQL*Loader Control File

[David Lewandowski]

That's it.  Thanks Ron and everyone else for your help.  Dave

-Original Message-
Sent: Thursday, June 26, 2003 1:51 PM
To: Multiple recipients of list ORACLE-L


David,
 Try removing the word "CONSTANT" from the string.
LOADDATE   "TO_DATE('20030626', 'MMDD')",
Ron
>>> [EMAIL PROTECTED] 06/26/03 02:09PM >>>
Hi David, 

Here's one of my control files, it works for me...  LOAD_DATE is indeed
a date field. 

OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE  ASCNAME_RAW
(
ID  RECNUM,
AN_SITE POSITION(1:2) CHAR 
NULLIF AN_SITE=BLANKS, 
AN_ASSOC_NO POSITION(3:6) CHAR 
NULLIF AN_ASSOC_NO=BLANKS, 
AN_ASSOC_NAME   POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR 
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,  
  
LOAD_DATE   SYSDATE   
)

Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117  
Fax:954-935-3639
Cell:954-683-4459


-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L



I can't figure out how to specify a constant date for a column 
in a SQL*Loader control file and can't find any examples.

I tried:
  LOADDATE  CONSTANT "TO_DATE('20030626', 'MMDD')",
but I get this error message in the log file:
  ORA-01858: a non-numeric character was found where a numeric was
expected

Can anyone tell me the correct syntax?

David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: David Lewandowski
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Koivu, Lisa
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: David Lewandowski
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Index question

[Johnson, Michael]

Teresita,
 
Usted tiene un nombre hermoso.  Yo nunca he estado a Guadalajara, pero oigo es 
muy agradable. La mayor parte de mi tiempo en México ha sido de Tiajuana 
completamente al sur a Zijuantinajo. El País hermoso y muy entibiar a gente 
Italia mucho más apreciando. Mi próximo viaje a México estará a Cabo San Lucas 
probablemente en agosto. Espero llegar a Guadalajara algún día. 
 
As for your current problem 
with the index..
 

First run a script called 
utlxplan.sql (spelling)  from the $ORACLE_HOME/rdbms/admin directory under 
the schema you wish to execute this query out of then 
 
 
The  optimizer_mode = 
Choose   ,  timed_statistics = true  parameters 
can be set in your
init*.ora file, but your can see the current setting of 
these values through ...
 
SQL>   select value from v$parameter where 
name like 'optimizer%'
  or 
name = 'timed_statistics';
 
The query will also 
return several optimizer values which may be needed for assessment 

later 
on.  
 
If you cannot bounce ( 
restart) the instance then consider setting these parameters using the command 
...
 
SQL>  Alter 
session set . 
 
Then 

 
SQL>  set 
autotrace traceonly
also 
consider
SQL>  set timing 
on
for a relative cost on 
how much time the query takes
 
and then 
Execute 
your SQL statement which will output an  explain plan for your 
viewing pleasure.
 
Lots of information on 
metalink on how to use and interpret the explain 
plan.
 
Lets stop here and 
report back what your output is on the explain 
plan.
 
CAUTION:   I 
never use 3rd party products.   I like to go straight to the data 
dictionary to find out whats going on with the database.    Not 
to say 3rd party products are bad or anything, its just my style thats 
all.
 
 
Espero que esto lo ayude 
fuera.   
 
Miquel.
 
 
 

  -Original Message-From: Teresita Castro 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, June 26, 2003 
  11:25 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Index question
  Where I can see the explain Plan?... I have 
  TOAD but is a try version and I don't have this option activated,  can I 
  see it in another program?
   
  And where I have to define this 
  option:
   optimizer_mode = Choose  
   timed_statistics = true 
   
  Michael:
  Sobre tus vacaciones como estaras cerca de Guadalaja, 
  te recomiendo visitar estar ciudad, y  tomar el tur del tren Tequita 
  express. Yo no he ido a puerto Vallarte pero la gente de por alla es muy 
  amigable y servicial, buena suerte !!
  >>> 
  [EMAIL PROTECTED] 06/25/03 09:59PM >>>
  First, Your english is 
  excellent or  "Usted habla inglés muy bien. "
   
  Assuming you are running 
  version 8i or better 
   
  Have you analyzed the tables 
  you are querying against ?  You may not need
  to force a rule as the CBO 
  will try to find the quickest way.   It looks 
  like
  you are using the RBO by 
  default.
   
  Do you have optimizer_mode = 
  Choose  ?
  Do you have timed_statistics 
  = true ?
   
  Have you run your queries 
  through an explain plan ?  If not this will
  show you the execution path 
  and a relative cost of each statement.
  You can manipulate your SQL 
  to see different costs as you change the
  statement.
   
  Also,  Your queries 
  could have been run while competing for resources in
  one case and maybe not in 
  another case.  Run both queries in the same 
  environment.  
  
   
  I have found that small 
  tables don't need indexes for the most part although this
  is not a hard and fast 
  rule.   You must go through the process.
   
   Espero que eso lo ayude 
  y la buena suerte a usted.   Espero verlo en 
  Puerto Vallarta Alguna Vez 
  pronto en la playa con un margarita. Hasta Luego.   
  
   
  Miquel.
   
  
-Original Message-From: Teresita Castro 
[mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 
2003 7:04 PMTo: Multiple recipients of list 
ORACLE-LSubject: Re: Index question
Hi!!
 
Let explain more about my 
situation.
The company that I work for is a 
chain of stores around some city's on Mexico, they bought Lawson a system 
that uses Oracle to manage the data bases, at first they use SQL Server 
2000, but I wasn't enough to manager all the information.
The structure of the table is all ready done 
and I have to learn it to do some reports that Lawson don't have, change or 
delete information and export some information to dbf files. Because we 
was using SQL Server I used Store procedures to return  the select 
result to VB recordset and the I pass the select result to Crystal 
Report or to a DBF file.
Well  I see that in Oracle the store 
procedure do not returns the result set has easy has SQL Server so I use and 
statement that after execute it returns me the result in a record 
set.
 
sQuery = "SELECT COMPANY,LOCATION, 
R_NAME FROM ICLOCATION " & 
_ "WHERE COMPANY=2000 OR 
CO

RE: nfile parameter problem

[DENNIS WILLIAMS]

Madhu
   I'm no expert on this, just another DBA wrestling with the same issues.
In my mind, the Java connection pool does the same function as MTS, in
concept at least. It takes many users and concentrates them to just a few
connections.
   In other words, let's suppose you have an overall pool of 10,000 users.
You service them with a Java pool of 1,000 connections. Then you have MTS
configured with 100 servers. My question is why not just configure the Java
pool for 100 connections and skip MTS? Maybe there is an excellent reason
and I'm too dumb to see it.
   In your specific situation, since you have a big beast of a server, my
dumb question is: why should you need to resort to MTS to avoid the
limitation in the number of files you could have open? It just struck me as
maybe there was an O.S. kernel tuning parameter that needed tweaked. Again,
this isn't based on any knowledge, just a gut feeling. Myself I would tend
to lean on the system administrators to "fix their problem" at least as a
start.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Thursday, June 26, 2003 3:00 PM
To: Multiple recipients of list ORACLE-L


TANEL,
A wonderful demonstration  !!!

we are already using the Java connection pool, DO you think we still have to
use MTS ?? or which is better ??


Dennis,
We really have a big beast ( 1.2T OLTP ) here in terms of hardware
resources. Its not the problem with memory or CPU or I/O.

John, Thanks for reply, as you pointed more # of files is big problem, but
we already tried to minimize it , 

What is your point on Java connection pooling (VS) MTS ???

Any pointers will be appreciated !!


Thanks,

Madhu Reddy
X13944


-Original Message-
Sent: Wednesday, June 25, 2003 3:45 PM
To: Multiple recipients of list ORACLE-L


Hi!



I think you should go with some sort of connection pooling and/or MTS.



> My main question to you all is : Is there any way to reduce the # of open
files opened by Oracle processes ??



The issue is, that with dedicated server every process has to open a
datafile if it tries to read a data block which isn't already in buffer
cache. If you got let say 1000 connections with dedicated servers (thus 1000
server processes) and 500 datafiles, the worst case is 500 000 used file
handlers. Of course, this is really the worst case, when every process has
had to read a block from every file.



When going with MTS, you actually have 50 or so processes to serve all 1000
of your connections, thus the need for file handlers is lot smaller. (I
think that in Windows going with dedicated servers isn't a procblem, because
it's single process architecture - threads can share file handlers between
each other, right?)



I wrote this mail in html, because I added my testing about datafiles with
comments here.



Cheers,

Tanel.



--



bash-2.03$ uname -a

SunOS blade.nt 5.8 Generic_108528-09 sun4u sparc SUNW,Sun-Blade-100



bash-2.03$ sqlplus system/[EMAIL PROTECTED]



SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jun 25 21:35:04 2003



(c) Copyright 2000 Oracle Corporation.  All rights reserved.





Connected to:

Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production

With the Partitioning option

JServer Release 8.1.7.1.0 - Production



First, lets check whether I'm using a dedicated server (MTS's can have files
open as a result of other sessions request). Also finding the OS PID to
compare with fuser result later on.



SQL> select server from v$session where sid = (select sid from v$mystat
where rownum = 1);



SERVER

-

DEDICATED



SQL> select p.spid

from v$process p, v$session s

where s.sid = (select sid from v$mystat where rownum = 1)

and p.addr = s.paddr;

  234

SPID

-

29064  -- note my OS PID for this session



Now create a tablespace and a table for testing



SQL> create tablespace test2 datafile '/u01/oradata/TEST817/test2_01.dbf'
size 1m autoextend off;



Tablespace created.



SQL> create table t (a number) tablespace test2;



Table created.



SQL> insert into t values (1);



1 row created.



SQL> commit;



Commit complete.



Now check with fuser, which processes are holding the datafile open



SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf:29064o 390o



My process is there, because I just created the tablespace

Now I take the tablespace offline/online, to make sure it's blocks in buffer
cache are invalidated



SQL> alter tablespace test2 offline;



Tablespace altered.



SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf:



SQL> alter tablespace test2 online;



Tablespace altered.



SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf:29064o 390o



And exit and log on again, to get a new OS process id for example



SQL> exit

Disconnected from Oracle8i Enterprise Edition Release 8.1

RE: Register vs. Load of JDBC driver

[Richard Ji]

What sort of differences?  Is the DriverManager method
slower than Class.forName()?

-Original Message-
Sent: Thursday, June 26, 2003 3:35 PM
To: Multiple recipients of list ORACLE-L


Hi List!
I'm not a Java programer so please, forgive me my (blissful) ignorance. My
developers
are experiencing wild differences when connecting using
driverManager.registerDriver vs.
Class.forName (dynamic loading). Does anybody have any clue why is that? Any
articles
about registering vs. loading? 
Thanks!

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: SQL*Loader Help -- Multiple rows into single column

[DENNIS WILLIAMS]

Bob
   If you were on Unix, I would suggest you have a script that split your
input file into multiple files using one of the Unix utilities, then
executed SQL*Loader against each of those. Since you are on Windows, perhaps
someone has a suggestion that will apply there. I know there are Windows
versions of the Unix utilities floating around.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Thursday, June 26, 2003 3:40 PM
To: Multiple recipients of list ORACLE-L


Gurus,

I have a special scenario to load data into tables
with SQL*Loader.

My SQL Loader data is not fixed format. It changes
from time to time. But there is a good pattern about
the data. Data which starts with letter "R" 
should go to table1 and data which starts with letter
"Z" should go to table2. Table1 data is always fixed
format where as table2 is kind of tricky. I would like
to load data which starts with letter "Z" into table2
as a single row.

For Example: (see my data at the bottom)
Tom is having 3 lines of data
Bob and Sam is having 4 lines of data
Joe is having 5 lines of data

Right now as per my SQL Loader Control file
(sql_load.ctl), all the data which starts with Z goes
into different rows (Tom --> 3 rows, Bob and Sam --> 4
rows, Joe --> 5 rows).

I would like to load four rows into table1 (it is
fine) and four rows into table2 (I am getting 16
rows).

I hope I explained properly.

FYI
Please take look at the following scripts.


-- Create Tables Script Start (sql_load.sql)
-- Sequence
create sequence table1_seq increment by 1 start with
1;
create sequence table2_seq increment by 1 start with
1;

-- Tables
create table table1
(serial_no number(5),
 name varchar2(10),
 amount number(4))
/

create table table2
(shipment_no number(5),
 details varchar2(1000))
/
-- Create Tables Script End (sql_load.sql)

-- SQL Loader Control file Start (sql_load.ctl)
options (rows=1, errors=1)
load data
infile 'c:\sql_load.txt'
badfile 'c:\sql_load.bad'
discardfile 'c:\sql_load.disc'
replace 
-- load table1
  into table table1
  when (1:1) = 'R' 
(serial_no position(1:1) "table1_seq.nextval", 
 name position(2:6) char,
 amount position(6:10) char)
-- load table2
   into table table2
   when (1:1) = 'Z' 
(shipment_no position(1:1) "table2_seq.nextval", 
 details position(2:81) char)
-- SQL Loader Control file End (sql_load.ctl)

-- SQL Loader Data File Start (sql_load.txt)
RTom  400
ZName: Tom
ZShip_To: New York
ZBill_To: Trenton

RBob  300
ZName: Bob
ZShip_To: Chicago
ZBill_To: Detroit
ZNotes: Best Customer Award

RSam  500
ZName: Sam
ZShip_To: Troy
ZBill_To: Dallas
ZNotes: Average Customer Award

RJoe  200
ZName: Joe
ZShip_To: Erie
ZBill_To: San Fransisco
ZNotes: Best Customer Award
ZSpecial Notes: Include Customer
-- SQL Loader Data File End (sql_load.txt)

Thanks,
Bob

__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bob Robert
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.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: redo log maintenance

[Gogala, Mladen]

Title: redo log maintenance



Doco 
is plain wrong. I've been adding, dropping and switching logfiles ever since the 
version 6 and
I have 
never needed to shut down the database for it.
 
Mladen Gogala Oracle DBA Phone:(203) 459-6855 
Email:[EMAIL PROTECTED] 

  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, June 26, 
  2003 5:00 PMTo: Multiple recipients of list 
  ORACLE-LSubject: redo log maintenance
  I'm reading through the 9.2 admin guide (yawn) and 
  it says that the database must be mounted but not open to add, drop or rename 
  redo logs.  I have done this many times with the database up and running, 
  with no problems.  Why would it say this in the doco, or is the doco just 
  plain wrong (again)?  It doesn't let you do anything to redo logs that 
  aren't archived or are currently being used, what's the harm?
  I don't get all the emails from the list - in fact 
  I get none most of the time.  So please be sure to include my personal 
  email address when responding. 
  Thanks 
  Lisa Koivu Oracle Database Monkey Fairfield 
  Resorts, Inc. 
  Orlando Office: 407-248-4277 Ft. Lauderdale Office: 954-935-4117  Fax:    954-935-3639 Cell:    954-683-4459 
  "The sender 
  believes that this E-Mail and any attachments were free of any virus, worm, 
  Trojan horse, and/or malicious code when sent. This message and its 
  attachments could have been infected during transmission.  By reading the 
  message and opening any attachments, the recipient accepts full responsibility 
  for taking proactive and remedial action about viruses and other defects. The 
  sender's business entity is not liable for any loss or damage arising in any 
  way from this message or its attachments."-- Please see 
  the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Koivu, Lisa INET: 
  [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 
  http://www.fatcity.com San Diego, California -- Mailing list and web hosting 
  services - 
  To REMOVE yourself from this mailing list, send an E-Mail message to: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message 
  BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list 
  you want to be removed from). You may also send the HELP command for other 
  information (like subscribing).

RE: Index question

[Gabriel Aragon]

Download the Oracle 9i client for Windows, you'll need
an Oracle Technet account but you can get one for free
immediately, then choose the scratchpad tool, it
generates the explain plan and also you can get a
complete report.. and other tools for oracle dba..

Gabriel


--- Teresita Castro <[EMAIL PROTECTED]>
wrote:
> Where I can see the explain Plan?... I have TOAD but
> is a try version and I don't have this option
> activated,  can I see it in another program?
> 
> And where I have to define this option:
>  optimizer_mode = Choose  
>  timed_statistics = true 
> 
> Michael:
> Sobre tus vacaciones como estaras cerca de
> Guadalaja, te recomiendo visitar estar ciudad, y 
> tomar el tur del tren Tequita express. Yo no he ido
> a puerto Vallarte pero la gente de por alla es muy
> amigable y servicial, buena suerte !!
> 
> >>> [EMAIL PROTECTED] 06/25/03 09:59PM
> >>>
> 
> First, Your english is excellent or  "Usted habla
> inglés muy bien. "
> 
> Assuming you are running version 8i or better 
> 
> Have you analyzed the tables you are querying
> against ?  You may not need
> to force a rule as the CBO will try to find the
> quickest way.   It looks like
> you are using the RBO by default.
> 
> Do you have optimizer_mode = Choose  ?
> Do you have timed_statistics = true ?
> 
> Have you run your queries through an explain plan ? 
> If not this will
> show you the execution path and a relative cost of
> each statement.
> You can manipulate your SQL to see different costs
> as you change the
> statement.
> 
> Also,  Your queries could have been run while
> competing for resources in
> one case and maybe not in another case.  Run both
> queries in the same 
> environment.  
> 
> I have found that small tables don't need indexes
> for the most part although this
> is not a hard and fast rule.   You must go through
> the process.
> 
>  Espero que eso lo ayude y la buena suerte a usted. 
>  Espero verlo en 
> Puerto Vallarta Alguna Vez pronto en la playa con un
> margarita. Hasta Luego.   
> 
> Miquel.
> 
> -Original Message-
> Sent: Wednesday, June 25, 2003 7:04 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi!!
> 
> Let explain more about my situation.
> The company that I work for is a chain of stores
> around some city's on Mexico, they bought Lawson a
> system that uses Oracle to manage the data bases, at
> first they use SQL Server 2000, but I wasn't enough
> to manager all the information.
> The structure of the table is all ready done and I
> have to learn it to do some reports that Lawson
> don't have, change or delete information and export
> some information to dbf files. Because we was using
> SQL Server I used Store procedures to return  the
> select result to VB recordset and the I pass the
> select result to Crystal Report or to a DBF file.
> Well  I see that in Oracle the store procedure do
> not returns the result set has easy has SQL Server
> so I use and statement that after execute it returns
> me the result in a record set.
> 
> sQuery = "SELECT COMPANY,LOCATION, R_NAME FROM
> ICLOCATION " & _
>  "WHERE COMPANY=2000 OR COMPANY=2001 OR
> COMPANY=2002 order by COMPANY,LOCATION"
> Set recRS = New ADODB.Recordset
> recRS.Open sQuery, gcnOracle, adOpenForwardOnly,
> adLockReadOnly, adCmdText
> 
> or execute a delete or update statement
> 
> sSQL = "update  /*+ INDEX(itemloc ITLSET2) */
> itemloc set average_cost =" & costo & " where
> (company='2000') and item= '" & Arti & "'"
> gcnOracle.Execute sSQL
> gcnOracle.Execute "Commit", dbSQLPassThrough
>
> Back to my problem:
> In this case the update of the average cost has to
> be done on the table Item location ( ITEMLOC)  that
> have all the item that each location( store) have.
> The locations have a company, when we changes the
> average cost is per company ( each company represent
> a different city) 
> 
> So my boss  execute the update statement 
> 
> sSQL = "update  itemloc set average_cost =" & costo
> & " where (company='2000') and item= '" & Arti & "'"
> 
> and she told me that per item  it takes like 10
> seconds.
> After read the article that I mention she changes
> the statement to this :
>  sSQL = "update  /*+ INDEX(itemloc ITLSET2) */
> itemloc set average_cost =" & costo & " where
> (company='2000') and item= '" & Arti & "'"
> 
> ITLSET2 is a index that have company(1), location(2)
> and Item(3) and it takes 2 seconds per item, so that
> is way she is convinced that we have to uses the /*+
> INDEX(itemloc ITLSET2) */ in all of our select,
> update or delete statement.
> 
> I hope you undestant my English and my problem too,
> because I have to do some really complicated queries
> that have like 3 or 5 tables in them, and using this
> method will give me some serious complications,
> maybe in this case (change of the average cost) is
> not too dangerous. 
> But I have to give her strong statements to change
> her mind.
> 
> Thanks for everything friends!!
> 
> 
> >>> [EMAIL PROTECTED] 06/25/03 06:10PM >>>
> 

Re: redo log maintenance

[Ron Rogers]

Lisa,
 I do not have the pleasure of migrating to 9.2 yet, In a few months
maybe on my play box. Could it be that the docs are refering to the AUTO
UNDO function that is availabe in 9i?
 I, like you have offlined, dropped and rebuilt redo logs on the fly
without any problems.
Ron
>>> [EMAIL PROTECTED] 06/26/03 05:00PM >>>

I'm reading through the 9.2 admin guide (yawn) and it says that the
database must be mounted but not open to add, drop or rename redo logs. 
I have done this many times with the database up and running, with no
problems.  Why would it say this in the doco, or is the doco just plain
wrong (again)?  It doesn't let you do anything to redo logs that aren't
archived or are currently being used, what's the harm?
I don't get all the emails from the list - in fact I get none most of
the time.  So please be sure to include my personal email address when
responding. 
Thanks 
Lisa Koivu 
Oracle Database Monkey 
Fairfield Resorts, Inc. 
Orlando Office: 407-248-4277 
Ft. Lauderdale Office: 954-935-4117  
Fax:954-935-3639 
Cell:954-683-4459 "The sender believes that this E-Mail and any
attachments were free of any virus, worm, Trojan horse, and/or malicious
code when sent. This message and its attachments could have been
infected during transmission.  By reading the message and opening any
attachments, the recipient accepts full responsibility for taking
proactive and remedial action about viruses and other defects. The
sender's business entity is not liable for any loss or damage arising in
any way from this message or its attachments."
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net --
Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City
Network Services -- 858-538-5051 http://www.fatcity.com San Diego,
California -- Mailing list and web hosting services
- To
REMOVE yourself from this mailing list, send an E-Mail message to:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (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).

RE: redo log maintenance

[Goulet, Dick]

Title: redo log maintenance



Lets 
just say that the docs are erroring on the safe side.  The reason is that 
if you try to do any of these tasks with the database open you could be 
affecting (negatively) an active log file.  While I agree with both of you 
that you can make these mods while the DB is open doing so while the DB is quite 
is the best time.
 
Dick GouletSenior Oracle DBAOracle Certified 8i DBA 

-Original Message-From: Gogala, Mladen 
[mailto:[EMAIL PROTECTED]Sent: Thursday, June 26, 2003 5:25 
PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
redo log maintenance
Doco 
is plain wrong. I've been adding, dropping and switching logfiles ever since the 
version 6 and
I have 
never needed to shut down the database for it.
 
Mladen Gogala Oracle DBA Phone:(203) 459-6855 
Email:[EMAIL PROTECTED] 

  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, June 26, 
  2003 5:00 PMTo: Multiple recipients of list 
  ORACLE-LSubject: redo log maintenance
  I'm reading through the 9.2 admin guide (yawn) and 
  it says that the database must be mounted but not open to add, drop or rename 
  redo logs.  I have done this many times with the database up and running, 
  with no problems.  Why would it say this in the doco, or is the doco just 
  plain wrong (again)?  It doesn't let you do anything to redo logs that 
  aren't archived or are currently being used, what's the harm?
  I don't get all the emails from the list - in fact 
  I get none most of the time.  So please be sure to include my personal 
  email address when responding. 
  Thanks 
  Lisa Koivu Oracle Database Monkey Fairfield 
  Resorts, Inc. 
  Orlando Office: 407-248-4277 Ft. Lauderdale Office: 954-935-4117  Fax:    954-935-3639 Cell:    954-683-4459 
  "The sender 
  believes that this E-Mail and any attachments were free of any virus, worm, 
  Trojan horse, and/or malicious code when sent. This message and its 
  attachments could have been infected during transmission.  By reading the 
  message and opening any attachments, the recipient accepts full responsibility 
  for taking proactive and remedial action about viruses and other defects. The 
  sender's business entity is not liable for any loss or damage arising in any 
  way from this message or its attachments."-- Please see 
  the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Koivu, Lisa INET: 
  [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 
  http://www.fatcity.com San Diego, California -- Mailing list and web hosting 
  services - 
  To REMOVE yourself from this mailing list, send an E-Mail message to: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message 
  BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list 
  you want to be removed from). You may also send the HELP command for other 
  information (like subscribing).

Re: SQL*Loader Help -- Multiple rows into single column

[Jared . Still]

Are you on 9i?

If so, setup the sql_load.txt file as an external table, and you
can then use SQL and/ora PL/SQL to load your table the
way you would like.

Don't think you can do what you're asking directly from sqlldr.

Jared






Bob Robert <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 06/26/2003 01:39 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:SQL*Loader Help -- Multiple rows into single column


Gurus,

I have a special scenario to load data into tables
with SQL*Loader.

My SQL Loader data is not fixed format. It changes
from time to time. But there is a good pattern about
the data. Data which starts with letter "R" 
should go to table1 and data which starts with letter
"Z" should go to table2. Table1 data is always fixed
format where as table2 is kind of tricky. I would like
to load data which starts with letter "Z" into table2
as a single row.

For Example: (see my data at the bottom)
Tom is having 3 lines of data
Bob and Sam is having 4 lines of data
Joe is having 5 lines of data

Right now as per my SQL Loader Control file
(sql_load.ctl), all the data which starts with Z goes
into different rows (Tom --> 3 rows, Bob and Sam --> 4
rows, Joe --> 5 rows).

I would like to load four rows into table1 (it is
fine) and four rows into table2 (I am getting 16
rows).

I hope I explained properly.

FYI
Please take look at the following scripts.


-- Create Tables Script Start (sql_load.sql)
-- Sequence
create sequence table1_seq increment by 1 start with
1;
create sequence table2_seq increment by 1 start with
1;

-- Tables
create table table1
(serial_no number(5),
 name varchar2(10),
 amount number(4))
/

create table table2
(shipment_no number(5),
 details varchar2(1000))
/
-- Create Tables Script End (sql_load.sql)

-- SQL Loader Control file Start (sql_load.ctl)
options (rows=1, errors=1)
load data
infile 'c:\sql_load.txt'
badfile 'c:\sql_load.bad'
discardfile 'c:\sql_load.disc'
replace 
-- load table1
  into table table1
  when (1:1) = 'R' 
(serial_no position(1:1) "table1_seq.nextval", 
 name position(2:6) char,
 amount position(6:10) char)
-- load table2
   into table table2
   when (1:1) = 'Z' 
(shipment_no position(1:1) "table2_seq.nextval", 
 details position(2:81) char)
-- SQL Loader Control file End (sql_load.ctl)

-- SQL Loader Data File Start (sql_load.txt)
RTom  400
ZName: Tom
ZShip_To: New York
ZBill_To: Trenton

RBob  300
ZName: Bob
ZShip_To: Chicago
ZBill_To: Detroit
ZNotes: Best Customer Award

RSam  500
ZName: Sam
ZShip_To: Troy
ZBill_To: Dallas
ZNotes: Average Customer Award

RJoe  200
ZName: Joe
ZShip_To: Erie
ZBill_To: San Fransisco
ZNotes: Best Customer Award
ZSpecial Notes: Include Customer
-- SQL Loader Data File End (sql_load.txt)

Thanks,
Bob

__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bob Robert
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.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: COMMIT's within cursor for loops

[Jared . Still]

Indeed.  Here's how I like to handle that type of thing.

-- not tested. may not compile, but the concept is there.

declare

  cursor eupd ( rowid_in rowid )
  is
  select *
  from emp e
  where rowid = rowid_in;

  eupdrec eupd%rowtype;

  commit_count integer := 1;
  commit_interval integer := 1;

begin

  for erec in select e.rowid, e.*  from emp e;
  loop

 -- do lots of calculations or something here
open eupd(erec.rowid);
fetch eupd into eupdrec;

update emp
set something = something_else
where current of  eupd;

 close eupd;

 commit_count := commit_count + 1;
 
 if commit_count >= commit_interval then
 commit_count := 1;
 commit;
 end if;

  end loop;

  commit;

end;

No fetch across commits, no ORA-1555's.

Jared

 
 





"Steve McClure" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 06/26/2003 12:49 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: COMMIT's within cursor for loops


>I prefer to create an inner block using BEGIN and END inside the loop. 
This
>isolates the DML statements. The COMMIT is issued inside the BEGIN and 
END
>block.
>
>RWB

That allows you to avoid invalidating the cursor established "for update"?
I dont' see how the two are related.  You just created an exception block
within the cursor loop.  Or am i misunderstanding what you are saying 
here?

Regarding the original question commiting within a cursor for loop.  It is
allowable if you do not create the cursor "for update".  If you created 
the
cursor using "for update", you will not only NOT retain a lock on the 
record
set, you will get an error indicating an invalid cursor.

Steve McClure

-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 11:45 AM
To: Multiple recipients of list ORACLE-L



I prefer to create an inner block using BEGIN and END inside the loop. 
This
isolates the DML statements. The COMMIT is issued inside the BEGIN and END
block.

RWB




Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management
Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)
[EMAIL PROTECTED]
[EMAIL PROTECTED]





[EMAIL PROTECTED]
ic.com   To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]   Subject: COMMIT's within 
cursor
for loops
ity.com


06/25/2003
12:40 PM
Please respond
to ORACLE-L







Hi All,

Can somebody please clear up some issues about issuing commits during
CURSOR
FOR LOOPS

I have done some research within Metalink and the ORACLE-L FAQ but am 
still
a tad bit confused.

Are the following statements regarding cursors TRUE

1. If you issue a commit within a for cursor loop you release all locks
regardless of whether you're cursor statement has a FOR UPDATE statement 
in
it or there is a WHERE CURRENT statement within the loop.

2. If you are using OPEN CURSOR...FETCH INTO statements to get the data 
the
same rule applies.

Thanks,

N.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Nuala Cullen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).


-- 
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 hosti

RE: Register vs. Load of JDBC driver

[Gogala, Mladen]

Approximately 10 times slower.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Thursday, June 26, 2003 5:15 PM
To: Multiple recipients of list ORACLE-L


What sort of differences?  Is the DriverManager method
slower than Class.forName()?

-Original Message-
Sent: Thursday, June 26, 2003 3:35 PM
To: Multiple recipients of list ORACLE-L


Hi List!
I'm not a Java programer so please, forgive me my (blissful) ignorance. My
developers
are experiencing wild differences when connecting using
driverManager.registerDriver vs.
Class.forName (dynamic loading). Does anybody have any clue why is that? Any
articles
about registering vs. loading? 
Thanks!

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: salary question

[Johnson, Michael]

Why not ask for more like a $100K ?  Your worth it, right ?

-Original Message-
Sent: Thursday, June 26, 2003 11:25 AM
To: Multiple recipients of list ORACLE-L



Does anyone have information about salaries in Denver?  I looked at
salary.com and saw the median
salary was $72K.  Does anyone have any opinions?


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Helen J Mitchell
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Johnson, Michael 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Perl for Oracle DBAs - dbup.pl

[Dwayne Cox]

Hi,

I apologize in advance for the length of this post.

I have been wrestling with this for a couple days now and I regreewt 
having to toss in the towel but I need help.

I am trying to setup dbup.pl from the Perl for Oracle DBAs book (highly 
recommended, I must say).  As long as the databases are running, 
everything looks good (the log files show everything is ok).  I get an 
error from the script whenever I take a database down  as opposed to an 
email or pager.

Here is the section of the file that seems to be causing my woes (if 
necessary, I could send the rest):

%addresses = (
'dbamgr' => { pager => '',
  emailWork => '[EMAIL PROTECTED]',
  emailHome => '[EMAIL PROTECTED]' },
'opsmgr' => { pager => '',
  emailWork => '[EMAIL PROTECTED]',
  emailHome => '[EMAIL PROTECTED]' },
'corpdba' => { pager => '',
 emailWork => '[EMAIL PROTECTED]',
 emailHome => '[EMAIL PROTECTED]' },
);
[EMAIL PROTECTED] = ('opsmgr','dbamgr');

%onCallList = ( 'default' => 'corpdba' );

Here is the error:

$ Use of uninitialized value in hash element at /usr/bin/dbup.pl line 
597 (#1)
(W uninitialized) An undefined value was used as if it were already
defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
To suppress this warning assign a defined value to your variables.

To help you figure out what was undefined, perl tells you what operation
you used the undefined value in.  Note, however, that perl optimizes 
your program and the operation displayed in the warning may not 
necessarily appear literally in your program.  For example, "that $foo" 
is usually optimized into "that " . $foo, and the warning will refer to
the concatenation (.) operator, even though there is no . in your
program.

Use of uninitialized value in concatenation (.) or string at 
/usr/bin/dbup.pl
line 311 (#1)
Use of uninitialized value in hash element at /usr/bin/dbup.pl line 317 (#1)
Use of uninitialized value in hash element at /usr/bin/dbup.pl line 319 (#1)
Use of uninitialized value in hash element at /usr/bin/dbup.pl line 320 (#1)
Use of uninitialized value in join or string at /usr/bin/dbup.pl line 
525 (#1)
No recipient!Use of uninitialized value in join or string at 
/usr/bin/dbup.pl line 339 (#1)
Use of uninitialized value in concatenation (.) or string at 
/usr/bin/dbup.pl
line 363 (#1)
No recipient!Use of uninitialized value in concatenation (.) or string 
at /usr/bin/dbup.pl
line 388 (#1)

--
Dwayne Cox
Corporate Database Administrator
Info Tech, Inc.
5700 SW 34th Street, Suite 1235
Gainesville, FL  32608
email: [EMAIL PROTECTED]
phone: 352.381.4521 fax: 352.381.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dwayne Cox
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or 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 for Oracle DBAs - dbup.pl

[Jared . Still]

Dwayne,

Offhand, I would say that the problem is due to the pager attribute 
being empty in the %addresses hash.

Try putting a value in it.

Also, what is line 597 in dbup.pl?  Yours is likely
a few lines different than mine.

Please show it in context. ie. with surrounding lines as well.

Jared





Dwayne Cox <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 06/26/2003 04:49 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Perl for Oracle DBAs - dbup.pl


Hi,

I apologize in advance for the length of this post.

I have been wrestling with this for a couple days now and I regreewt 
having to toss in the towel but I need help.

I am trying to setup dbup.pl from the Perl for Oracle DBAs book (highly 
recommended, I must say).  As long as the databases are running, 
everything looks good (the log files show everything is ok).  I get an 
error from the script whenever I take a database down  as opposed to an 
email or pager.

Here is the section of the file that seems to be causing my woes (if 
necessary, I could send the rest):

%addresses = (
 'dbamgr' => { pager => '',
   emailWork => '[EMAIL PROTECTED]',
   emailHome => '[EMAIL PROTECTED]' },

 'opsmgr' => { pager => '',
   emailWork => '[EMAIL PROTECTED]',
   emailHome => '[EMAIL PROTECTED]' },

 'corpdba' => { pager => '',
  emailWork => '[EMAIL PROTECTED]',
  emailHome => '[EMAIL PROTECTED]' },
);

[EMAIL PROTECTED] = ('opsmgr','dbamgr');

%onCallList = ( 'default' => 'corpdba' );


Here is the error:

$ Use of uninitialized value in hash element at /usr/bin/dbup.pl line 
597 (#1)
(W uninitialized) An undefined value was used as if it were already
defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
To suppress this warning assign a defined value to your variables.

To help you figure out what was undefined, perl tells you what operation
you used the undefined value in.  Note, however, that perl optimizes 
your program and the operation displayed in the warning may not 
necessarily appear literally in your program.  For example, "that $foo" 
is usually optimized into "that " . $foo, and the warning will refer to
the concatenation (.) operator, even though there is no . in your
program.

Use of uninitialized value in concatenation (.) or string at 
/usr/bin/dbup.pl
 line 311 (#1)
Use of uninitialized value in hash element at /usr/bin/dbup.pl line 317 
(#1)
Use of uninitialized value in hash element at /usr/bin/dbup.pl line 319 
(#1)
Use of uninitialized value in hash element at /usr/bin/dbup.pl line 320 
(#1)
Use of uninitialized value in join or string at /usr/bin/dbup.pl line 
525 (#1)
No recipient!Use of uninitialized value in join or string at 
/usr/bin/dbup.pl line 339 (#1)
Use of uninitialized value in concatenation (.) or string at 
/usr/bin/dbup.pl
 line 363 (#1)
No recipient!Use of uninitialized value in concatenation (.) or string 
at /usr/bin/dbup.pl
 line 388 (#1)


-- 
Dwayne Cox
Corporate Database Administrator
Info Tech, Inc.
5700 SW 34th Street, Suite 1235
Gainesville, FL  32608

email: [EMAIL PROTECTED]
phone: 352.381.4521 fax: 352.381.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Dwayne Cox
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Re: RE: SHMMAX setting on Sun 4800 box

[Zhai Jingmin]

Thanks all ,this is a great list!



=== At 2003-06-26, 10:17:00 you wrote: ===

>Richard Ji,ÄúºÃ£¡
>Hi £¬ how do you know that ISM is used even if multiple shared memory 
> segment are used in solaris 8?
>   
>   
>
>>Matt,
>>
>>Your SGA can still be larger than 4G even if SHMMAX is not larger than
>>4G.  When SGA size > SHMMAX, mutilple shared memeory segements will
>>be allocated.  There used to be a claim that if you can fit the SGA
>>into a single shared memory segment, ISM will be used.  This might
>>be true for older version of Solaris, but I have seen ISM being used
>>even when mutiple shared memory segments are allocated on Solaris 8.
>>
>>Regards,
>>
>>Richard
>>
>>-Original Message-
>>Sent: Thursday, June 26, 2003 10:55 AM
>>To: Multiple recipients of list ORACLE-L
>>
>>
>>
>>As long as you're running 64-bit solaris and 64-bit oracle, you can set
>>SHMMAX to be larger than 4G, which will allow your SGA to be larger than
>>4G.  Keep an eye on your overall memory pressure, though, to make sure
>>you don't oversubscribe the system.  If you're running Solaris 8/9
>>and/or using some sort of direct i/o (raw devices, veritas, etc.) you're
>>good to go at that point.  If you're running 7 or 2.6 (can you even run
>>2.6 and 7 on the 4800?  I doubt it), you'll want to turn on
>>priority_paging in /etc/system to keep your sga from getting paged out
>>in favor of filesystem buffers.
>>
>>Thanks,
>>Matt
>>
>>--
>>Matthew Zito
>>GridApp Systems
>>Email: [EMAIL PROTECTED]
>>Cell: 646-220-3551
>>Phone: 212-358-8211 x 359
>>http://www.gridapp.com
>>
>>> -Original Message-
>>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On 
>>> Behalf Of Zhai Jingmin
>>> Sent: Thursday, June 26, 2003 5:34 AM
>>> To: Multiple recipients of list ORACLE-L
>>> Subject: SHMMAX setting on Sun 4800 box
>>> 
>>> 
>>> Hello, List,
>>> 
>>> Our Sun Fire 4800 server have 8 CPUs and 16G memory.I set the 
>>> SHMMAX kernel parameter to 4294967295 according to 
>>> installation guide on metalink.The SHMMAX is "max shared 
>>> memory segment size".What's the relationship between SHMMAX 
>>> and max size of SGA?Because our oracle's SGA may exceed 
>>> 4G,can I set this parameter to 8G,10G?
>>> 
>>> Any suggestion is appreciated.
>>> 
>>> Thanks, 
>>> 
>>> Zhai Jingmin
>>> [EMAIL PROTECTED]
>>> 2003-06-26
>>> 
>>> 
>>> 
>>> -- 
>>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>> -- 
>>> Author: Zhai Jingmin
>>>   INET: [EMAIL PROTECTED]
>>> 
>>> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>>> San Diego, California-- Mailing list and web hosting services
>>> -
>>> To REMOVE yourself from this mailing list, send an E-Mail message
>>> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') 
>>> and in the message BODY, include a line containing: UNSUB 
>>> ORACLE-L (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: Matthew Zito
>>  INET: [EMAIL PROTECTED]
>>
>>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>>San Diego, California-- Mailing list and web hosting services
>>-
>>To REMOVE yourself from this mailing list, send an E-Mail message
>>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>>the message BODY, include a line containing: UNSUB ORACLE-L
>>(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).
>
>= = = = = = = = = = = = = = = = = = = =
>   
>
>ÖÂ
>Àñ£¡
> 
>
>chao_ping
>[EMAIL PROTECTED]
>2003-06-27
>
>
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: chao_ping
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>San Diego, California-- Mailing list and web hosting services
>-

Re: nfile parameter problem

[Tanel Poder]

Hii,

I'm gonna send a reply, which i sent privately before:

> Why is the dedicated server for Java pooled connections ??
> and why not MTS even with JPCs ??

Because MTS was invented during the golden age of client-server applications
where every single user interface opened up its own connection (or several)
to the database. The more connections you got, the more memory you had to
have allocated. Some boxes simply ran out of memory while 95% of actual
server processes were idle. Thus, here came the idea of sharing the server
processes (note that in 9i you have shared_servers parameter instead of
mts_server_processes).
So there's a tradeoff, instead of having 1000 server processes using huge
amounts of memory, you actually got 50 shared servers with lot's less
memory. But you also have some CPU overhead, you need to have dispatchers,
which coordinate the traffic between clients and server processes.

On the other hand, in (my) theory MTS might use even less CPU in OLTP
configurations where lot's of small requests are executed, because one
Oracle server process might be able to execute several client requests, thus
using up all of the 100 ms timeslice given to this process, instead of
yielding and causing OS level context switches. But I haven't tested it,
others might enlighten us.

But since MTS includes a lot of "new" code, dedicated servers would be more
stable. And since your application server supports connection pooling (thus
allowing reducing number of Oracle connections), it'd be safer to use only
one pooling mechanism, instead of two. (JCP vs. JCP+MTS). The simpler the
better.

Now I'm going to drink some wine with my friend, so I'm unable to respond
until tomorrow.


Cheers,
Tanel.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, June 26, 2003 11:56 PM


> Madhu
>I'm no expert on this, just another DBA wrestling with the same issues.
> In my mind, the Java connection pool does the same function as MTS, in
> concept at least. It takes many users and concentrates them to just a few
> connections.
>In other words, let's suppose you have an overall pool of 10,000 users.
> You service them with a Java pool of 1,000 connections. Then you have MTS
> configured with 100 servers. My question is why not just configure the
Java
> pool for 100 connections and skip MTS? Maybe there is an excellent reason
> and I'm too dumb to see it.
>In your specific situation, since you have a big beast of a server, my
> dumb question is: why should you need to resort to MTS to avoid the
> limitation in the number of files you could have open? It just struck me
as
> maybe there was an O.S. kernel tuning parameter that needed tweaked.
Again,
> this isn't based on any knowledge, just a gut feeling. Myself I would tend
> to lean on the system administrators to "fix their problem" at least as a
> start.
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Thursday, June 26, 2003 3:00 PM
> To: Multiple recipients of list ORACLE-L
>
>
> TANEL,
> A wonderful demonstration  !!!
>
> we are already using the Java connection pool, DO you think we still have
to
> use MTS ?? or which is better ??
>
>
> Dennis,
> We really have a big beast ( 1.2T OLTP ) here in terms of hardware
> resources. Its not the problem with memory or CPU or I/O.
>
> John, Thanks for reply, as you pointed more # of files is big problem, but
> we already tried to minimize it ,
>
> What is your point on Java connection pooling (VS) MTS ???
>
> Any pointers will be appreciated !!
>
>
> Thanks,
>
> Madhu Reddy
> X13944
>
>
> -Original Message-
> Sent: Wednesday, June 25, 2003 3:45 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi!
>
>
>
> I think you should go with some sort of connection pooling and/or MTS.
>
>
>
> > My main question to you all is : Is there any way to reduce the # of
open
> files opened by Oracle processes ??
>
>
>
> The issue is, that with dedicated server every process has to open a
> datafile if it tries to read a data block which isn't already in buffer
> cache. If you got let say 1000 connections with dedicated servers (thus
1000
> server processes) and 500 datafiles, the worst case is 500 000 used file
> handlers. Of course, this is really the worst case, when every process has
> had to read a block from every file.
>
>
>
> When going with MTS, you actually have 50 or so processes to serve all
1000
> of your connections, thus the need for file handlers is lot smaller. (I
> think that in Windows going with dedicated servers isn't a procblem,
because
> it's single process architecture - threads can share file handlers between
> each other, right?)
>
>
>
> I wrote this mail in html, because I added my testing about datafiles with
> comments here.
>
>
>
> Cheers,
>
> Tanel.
>
>
>
> --
>
>
>
> bash-2.03$ uname -a
>
> SunOS blade.nt 5.8 Generic_108528-09 sun4u sparc SUNW

Re: redo log maintenance

[Tanel Poder]

Title: redo log maintenance



Hi!
 
You won't be affecting any current redolog 
negatively, you simply get an error, but the normal work continues as 
usual...
 
tnl.
 

  - Original Message - 
  From: 
  Goulet, Dick 
  
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Friday, June 27, 2003 12:49 
AM
  Subject: RE: redo log maintenance
  
  Lets 
  just say that the docs are erroring on the safe side.  The reason is that 
  if you try to do any of these tasks with the database open you could be 
  affecting (negatively) an active log file.  While I agree with both of 
  you that you can make these mods while the DB is open doing so while the DB is 
  quite is the best time.
   
  Dick GouletSenior Oracle DBAOracle Certified 8i DBA 
  
  -Original Message-From: Gogala, Mladen 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, June 26, 2003 5:25 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  redo log maintenance
  Doco 
  is plain wrong. I've been adding, dropping and switching logfiles ever since 
  the version 6 and
  I 
  have never needed to shut down the database for it.
   
  Mladen Gogala Oracle DBA Phone:(203) 
  459-6855 Email:[EMAIL PROTECTED] 
  
-Original Message-From: Koivu, Lisa 
[mailto:[EMAIL PROTECTED]Sent: Thursday, June 26, 
2003 5:00 PMTo: Multiple recipients of list 
ORACLE-LSubject: redo log maintenance
I'm reading through the 9.2 admin guide (yawn) 
and it says that the database must be mounted but not open to add, drop or 
rename redo logs.  I have done this many times with the database up and 
running, with no problems.  Why would it say this in the doco, or is 
the doco just plain wrong (again)?  It doesn't let you do anything to 
redo logs that aren't archived or are currently being used, what's the 
harm?
I don't get all the emails from the list - in 
fact I get none most of the time.  So please be sure to include my 
personal email address when responding. 
Thanks 
Lisa Koivu Oracle Database Monkey Fairfield 
Resorts, Inc. 
Orlando Office: 407-248-4277 Ft. Lauderdale Office: 954-935-4117  
Fax:    
954-935-3639 Cell:    
954-683-4459 
"The sender 
believes that this E-Mail and any attachments were free of any virus, worm, 
Trojan horse, and/or malicious code when sent. This message and its 
attachments could have been infected during transmission.  By reading 
the message and opening any attachments, the recipient accepts full 
responsibility for taking proactive and remedial action about viruses and 
other defects. The sender's business entity is not liable for any loss or 
damage arising in any way from this message or its 
attachments."-- Please see the official ORACLE-L FAQ: 
http://www.orafaq.net -- Author: Koivu, Lisa INET: 
[EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 
http://www.fatcity.com San Diego, California -- Mailing list and web hosting 
services 
- To 
REMOVE yourself from this mailing list, send an E-Mail message to: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message 
BODY, include a line containing: UNSUB ORACLE-L (or 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 for Oracle DBAs - dbup.pl

[Dwayne Cox]

Hi Jared,

Kudos on such a great book!

Below is my entire file.  I did try it with the pager field filled
but I had the same result.
Again, pardon the long email.  And thanks for taking a look at this.

Here is the function containing line 597 of dbup.pl:  

 sub getDba {
   my $self = shift;
   # date format MMDD
   my ($currDate) = @_;
   #print "Currdate: $currDate\n";

   my @lt = localtime(time);
   $currDate ||= Date::Format::strftime('%Y%m%d', @lt);
   my $currHour = Date::Format::strftime('%H',@lt);
   my $cutOffHour = 12;
   my $dateKey;
   my $prevDate;
   for my $date ( sort keys %{$self} ){
   #print "date key: $date\n";
   $dateKey = $prevDate;
   if ( $currDate le $date ) {
   if ( $cutOffHour le $currHour and $currDate eq 
$date ) {
   $dateKey = $date;
   };
   last;
   }
   #last if  $currDate < $date;
   $prevDate = $date;
   }

 #use Data::Dumper;
   #print Dumper ( $self );
   #print "found datekey: $dateKey\n";
   return $self->{$dateKey};<  line 597
  
}  
  

package uptime;

  my dbup.conf -

# this line is required
package dbup;
use PDBA;
use vars qw( $dateFormat $ignoreFile
%parms %uptime %addresses
%onCallList @supervisors );
# format of date in ignoreFile
$dateFormat = "%Y/%m/%d-%H:%M:%S";
$ignoreFile = 'dbignore.conf';
%parms = ( mailServer => 'mail.infotechfl.com',
  fromAddress => '[EMAIL PROTECTED]',
   # how often to check database connectivity
   # measured in seconds
   # low value used for testing
   #connectInterval => 30,
  connectInterval => 120,
   # avoid hangs - timeout is in seconds
  connectionTimeOut => 10,
   # hours are 0 - 23
   # these are the hours to page immediately without
   # retrying the connection.  outside of these hours,
   # retry a configurable number of times before paging
  hoursToPageImmediate => [0..23],
   # use a limited range for testing during the day
   #hoursToPageImmediate => [19..20],
   # don't page DBA during lunch. :)
   #hoursToPageImmediate => [6..11, 13..18],
   # how many times to retry a connection when the time
   # is outside the range of hoursToPage
  maxConnectRetries => 3,
   # *everything* is logged
   # will use PDBA_HOME
   #logfile => qq{$ENV{PDBA_HOME}/logs/dbup.log},
  logfile => PDBA->pdbaHome() . q{/logs/dbup.log},
);

%uptime = (
   pvprod => { machine => 'polar',
   upDays => [1..5],
   upHours => [0..23],
   username => 'system',
   alertLevel => 3 },
   pvtest => { machine => 'polar',
   upDays => [1..5],
   upHours => [6..18],
   username => 'system',
   alertLevel => 2 },
   pvtrn => { machine => 'polar',
  upDays => [1..5],
  upHours => [6..18],
  username => 'system',
  alertLevel => 2 },
alp2 => { machine => 'batchdb',
  upDays => [1..5],
  upHours => [6..18],
  username => 'system',
  alertLevel => 2 },
);
%addresses = (
   'dbamgr' => { pager => '[EMAIL PROTECTED]',
 emailWork => '[EMAIL PROTECTED]',
 emailHome => '[EMAIL PROTECTED]' },
   'opsmgr' => { pager => '[EMAIL PROTECTED]',
 emailWork => '[EMAIL PROTECTED]',
 emailHome => '[EMAIL PROTECTED]' },
   'corpdba' => { pager => '[EMAIL PROTECTED]',
emailWork => '[EMAIL PROTECTED]',
emailHome => '[EMAIL PROTECTED]' },
);
[EMAIL PROTECTED] = ('opsmgr','dbamgr');

%onCallList = ( 'default' => 'corpdba' );

[EMAIL PROTECTED] wrote:

Dwayne,

Offhand, I would say that the problem is due to the pager attribute 
being empty in the %addresses hash.

Try putting a value in it.

Also, what is line 597 in dbup.pl?  Yours is likely
a few lines different than mine.
Please show it in context. ie. with surrounding lines as well.

Jared



 



--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dwayne Cox
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information

Re: High-availability database question !!!!

[Mogens Nørgaard]

You mean the Danish Promis system? You're running that?

Hemant K Chitale wrote:
 
 The "That might come as a surprise to some people, but there's basically
no 25/8/370 availability with Oracle unless you either specify downtime as
something else or don't patch/upgrade at all." 
 doesn't cut ice [I think I might have mixed metaphors here] with damagement.
  
 My 8iOPS to 9iRAC upgrade had been in the planning stages for more than
a year, never getting the 4 hours downtime requested.
 Finally, the upgrade was scheduled for 10am to 2pm on a Wednesday.
 Guess what ?   Tuesday night, 9pm I get a call "the Promis system is down,
manufacturing is down".  Can you come in and
 quickly upgrade the Oracle database to 9iRAC tonight, like, NOW ?  I did
do it in 3.5 hours !
  
 Hemant
  
 At 06:59 AM 25-06-03 -0800, you wrote:
 
  1. RAC can duplicate your instances/nodes.
But you still have only one database.
 2. One database means un-availability whenever you have to apply a patch 
or upgrade the database. No online patches available. No rolling upgrades.
 3. Dataguard needs to have exactly the same version (down to patch level) 
on the two (or more) databases. So that just means two databases down if you
have to patch or upgrade. That might come as a surprise to some people, but
there's basically no 25/8/370 availability with Oracle unless you either
specify downtime as something else or don't patch/upgrade at all.
 4. Logical stuff like replication, logical Dataguard, Shareplex from Quest
and others can get around the "everyhing-has-to-be-the-same-version" by using
logical replication of some sort - some faster, some slower.

 Mogens

 Jackson Dumas wrote:
 

 
  Goodday all

We have cutomer who is having a database which should be available
24x7.
Now we can't get even a maintenance time to do the database. We tried
on several occasion but nothing happens.
Now they want us to provide them with a solution to be able to work on
"another database" if we need to do maintenance. Could you guys
come
up with some ideas what we can offer them. Is there any other
alternatives besides replication and standby database.
Currently the system is running as a package in a cluster environment,
HP-UX, so when we go replication route possibilities are that we might
have to forget about high-availabilty cluster environment due to
things like disk space and memory for switching over during failure.
Neither do the standby database a good option in a situation where
you'll needing a longer downtime.

Please help.

Thanx,

Junior DBA

On Tue, 24 Jun 2003 08:19:18 -0800 
([EMAIL PROTECTED]) wrote:

 
  
  
 
Hi there,

I got a question on log. If my program crashes, can I check some log
   

  
  
 
  to see what
 
  
  
 
recent transaction is? It will give me a big help on trouble
   

  
  
 
  shooting.
 
  
  
 
Thanks,
Jin
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author:
 INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051
http://www.fatcity.com
San Diego, California    --
Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note
EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You 
may
also send the HELP command for other information (like subscribing).
   

  
  
 
  ___
LOOK GOOD, FEEL GOOD -
WWW.HEALTHIEST.CO.ZA

Cool Connection, Cool Price, Internet Access for R59 monthly @ WebMail
http://www.webmail.co.za/dialup/
 
  

  
 
   Hemant K Chitale
 Oracle 9i Database Administrator Certified Professional
 My personal web site is :  http://hkchital.tripod.com
  
 --  Please see the official ORACLE-L FAQ: http://www.orafaq.net --  Author:
Hemant K Chitale   INET: [EMAIL PROTECTED]  Fat City Network Services
   -- 858-538-5051 http://www.fatcity.com San Diego, California--
Mailing list and web hosting services - 
To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED]
(note EXACT spelling of 'ListGuru') and in the message BODY, include a line
containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed
from).  You may also send the HELP command for other information (like subscribing). 
 

OEM 9i - saving/copying reports between repositories

[Kaing, Leng]

G'day all,

I've create a number of custom reports in one of our OMS and would copy them into 
another OMS. How does one go about doing this? Where are the reports stored? Is there 
a file that we can copy around? 

Or if we look at the problem in another way - OEM itself comes with a number of 
predefined reports. What if I accidentally removed it from my OMS? How do I get it 
back? Can we export and import OEM reports somehow? Or is there a file we can tell the 
OMS about? I don't really want to export and import the whole OEM schema. I just want 
certain reports to be copied from one OMS into another.

TIA,

Leng.



--
Leng Kaing
Email: [EMAIL PROTECTED]
Phone: +61-3-9203-7589
Mobile: +61-417-371-348

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kaing, Leng
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: 9i OCP Details

[Mogens Nørgaard]

It must have been either Scott Gossett or Scott Heisey. But not that 
Scott. The two other Scott's are still with Oracle Education, I think.

Glenn Stauffer wrote:

Gudmundur Bjarni Josepsson wrote:

If you want real advanced courses go with [...] what Jonathan Lewis 
or guys at his level provide.
  


I have to agree on this.  I've had the good fortune to sit in on Lewis's
'Optimising Oracle' and Cary Millsap's 'Hotsos Clinic 101' and no course
I ever took from Oracle Education comes close to what these guys offer
(and I've taken quite a few).
 

The only course from Oracle Education that's come close for me is the 
internals course that I took about 3 or 4 years ago.  I haven't seen 
it offered recently.  The instructor was Scott somebody, who was, I 
was told, THE Scott or Scott/Tiger fame.

I second the recommendation for Jonathan Lewis; his Cost-Based 
Optimization course was well taught and I found it very useful.

Craig Shallahamer (www.orapub.com) also offers some worthwhile courses 
and is a very good instructor.

I'll find out about the Hotsos Clinic this August in Denver.  Looking 
forward to it.

Glenn Stauffer




--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).