date:20031029

if you have a tablespace with 10 datafiles. can you drop just one datafile? 

 From: Rachel Carmichael [EMAIL PROTECTED]
 Date: 2003/10/29 Wed AM 08:04:25 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Re: How to drop a datafile ?

 export the data (make sure you get all the data)
 drop the tablespace and recreate it
 import the data

 --- Prem Khanna J [EMAIL PROTECTED] wrote:
  Thanx Sinardy  Rachel.

  so,the only way is as what Sinardy said.
  is that so ? 

  Regards,
  Jp.

  29-10-2003 19:44:25, Rachel Carmichael [EMAIL PROTECTED] wrote:
  The doc is right. You cannot drop a single datafile from a
  tablespace.

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

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

 __
 Do you Yahoo!?
 Exclusive Video Premiere - Britney Spears
 http://launch.yahoo.com/promos/britneyspears/
 -- 
 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: [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: Re: How to drop a datafile ?

disregard my question. i saw it in an earlier post. sorry i have several hundred 
emails this morning. i missed it. 

 From: Rachel Carmichael [EMAIL PROTECTED]
 Date: 2003/10/29 Wed AM 08:04:25 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Re: How to drop a datafile ?

 export the data (make sure you get all the data)
 drop the tablespace and recreate it
 import the data

 --- Prem Khanna J [EMAIL PROTECTED] wrote:
  Thanx Sinardy  Rachel.

  so,the only way is as what Sinardy said.
  is that so ? 

  Regards,
  Jp.

  29-10-2003 19:44:25, Rachel Carmichael [EMAIL PROTECTED] wrote:
  The doc is right. You cannot drop a single datafile from a
  tablespace.

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

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

 __
 Do you Yahoo!?
 Exclusive Video Premiere - Britney Spears
 http://launch.yahoo.com/promos/britneyspears/
 -- 
 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: [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: Re: How to drop a datafile ?

2003-10-29 Thread Robertson Lee - lerobe

Just to reiterate what Rachel has already said (further down the page in
your own email) .

The doc is right. You cannot drop a single datafile from a tablespace

-Original Message-
Sent: 29 October 2003 13:14
To: Multiple recipients of list ORACLE-L


if you have a tablespace with 10 datafiles. can you drop just one datafile? 
 
 From: Rachel Carmichael [EMAIL PROTECTED]
 Date: 2003/10/29 Wed AM 08:04:25 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Re: How to drop a datafile ?
 
 export the data (make sure you get all the data)
 drop the tablespace and recreate it
 import the data
 
 
 --- Prem Khanna J [EMAIL PROTECTED] wrote:
  Thanx Sinardy  Rachel.
  
  so,the only way is as what Sinardy said.
  is that so ? 
  
  Regards,
  Jp.
  
  29-10-2003 19:44:25, Rachel Carmichael [EMAIL PROTECTED] wrote:
  The doc is right. You cannot drop a single datafile from a
  tablespace.
  
  
  
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: Prem Khanna J
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
 
 
 __
 Do you Yahoo!?
 Exclusive Video Premiere - Britney Spears
 http://launch.yahoo.com/promos/britneyspears/
 -- 
 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: [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).







**
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

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

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

Question: did you note much I/O when running the sort ??. In AIX it's usefull to make some modifications in paging space behavior in order to reduce I/O contention ad let the ORACLE SGA be in main memory instead on paging space.

Try this:

Apply the latest Maintenance Level of AIX 5

Take a view of paging space activity using AIX tools like lsps, vmstat, topas, etc

run the following command vmtune -p 5 -P 10 -t 10

compare with the view taken before issuing the previous command

Good Luck

Saludos, Cesar D. Delgado P.
IBM de Venezuela
Tlf (58212) 908-8904
mailto:[EMAIL PROTECTED]

sts://[EMAIL PROTECTED]/

Research is what I'm doing when I don't know what I'm doing.
-- Wernher Von Braun (1912-1977)

Robertson Lee - lerobe [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
29/10/2003 06:19 a.m.
Please respond to ORACLE-L

To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:9i RAC on AIX 5L

Hi,

Oracle 9.2.0.3
AIX 5L

Anyone out there suffered serious performance issues with sorts to disk on
this platform. we are using GPFS filesystems. The same query took about 16
minutes on a 8.1.7 database running on a small Tru/64 machine, the query on
the p650/9.2.0.3 machine took over 6 hours for a smaller data-set. Obviously
by doing sorting to memory we have reduced the time significantly but we
really would like to get this performing as the 8i system. Our data will be
of a size that some sorting to disk will be inevitable in the future and we
cannot live with the performance as it is. I have raised a call with Oracle
but as usual, the good people on this list invariably come up with some
useful hints/solutions in the meantime.

Regards

Lee

**
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

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

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

2003-10-29 Thread Robertson Lee - lerobe

--_=_NextPart_001_01C39E23.C16EC754
Content-Type: text/plain

Thanks,
 
Lots of IO but our S.As assure me (alongwith IBM themselves)  that
everything is set up correctly
 
Regards
 
Lee

-Original Message-
Sent: 29 October 2003 13:44
To: Multiple recipients of list ORACLE-L



Hi, 

Question: did you note much I/O when running the sort ??. In AIX it's
usefull to make some modifications in paging space behavior in order to
reduce I/O contention ad let the ORACLE SGA be in main memory instead on
paging space. 

Try this: 

Apply the latest Maintenance Level of AIX 5 

Take a view of paging space activity using AIX tools like lsps, vmstat,
topas, etc 

run the following command vmtune -p 5 -P 10 -t 10 

compare with the view taken before issuing the previous command 

Good Luck 

Saludos, Cesar D. Delgado P.
IBM de Venezuela
Tlf (58212) 908-8904
mailto:[EMAIL PROTECTED]

sts://[EMAIL PROTECTED]/

Research is what I'm doing when I don't know what I'm doing.
-- Wernher Von Braun (1912-1977) 



Robertson Lee - lerobe [EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED] 


29/10/2003 06:19 a.m. 
Please respond to ORACLE-L 



To:Multiple recipients of list ORACLE-L
[EMAIL PROTECTED] 
cc: 
Subject:9i RAC on AIX 5L 




Hi,

Oracle 9.2.0.3
AIX 5L

Anyone out there suffered serious performance issues with sorts to disk on
this platform. we are using GPFS filesystems. The same query took about 16
minutes on a 8.1.7 database running on a small Tru/64 machine, the query on
the p650/9.2.0.3 machine took over 6 hours for a smaller data-set. Obviously
by doing sorting to memory we have reduced the time significantly but we
really would like to get this performing as the 8i system. Our data will be
of a size that some sorting to disk will be inevitable in the future and we
cannot live with the performance as it is. I have raised a call with Oracle
but as usual, the good people on this list invariably come up with some
useful hints/solutions in the meantime.

Regards

Lee






**
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

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

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

  









--_=_NextPart_001_01C39E23.C16EC754
Content-Type: text/html

!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN
HTMLHEAD
META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=US-ASCII


META content=MSHTML 6.00.2600.0 name=GENERATOR/HEAD
BODY
DIVSPAN class=593525113-29102003FONT face=Arial color=#ff 
size=2Thanks,/FONT/SPAN/DIV
DIVSPAN class=593525113-29102003FONT face=Arial color=#ff 
size=2/FONT/SPANnbsp;/DIV
DIVSPAN class=593525113-29102003FONT face=Arial color=#ff size=2Lots 
of IO but our S.As assure me (alongwith IBM themselves) nbsp;that everything is 
set up correctly/FONT/SPAN/DIV
DIVFONT face=Arial color=#ff size=2/FONTnbsp;/DIV
DIVSPAN class=593525113-29102003FONT face=Arial color=#ff 
size=2Regards/FONT/SPAN/DIV
DIVSPAN class=593525113-29102003FONT face=Arial color=#ff 
size=2/FONT/SPANnbsp;/DIV
DIVSPAN class=593525113-29102003FONT face=Arial color=#ff 
size=2Lee/FONT/SPAN/DIV
BLOCKQUOTE
  DIV class=OutlookMessageHeader dir=ltr align=leftFONT face=Tahoma 
  size=2-Original Message-BRBFrom:/B Cesar Delgado 
  [mailto:[EMAIL PROTECTED]BRBSent:/B 29 October 2003 
  13:44BRBTo:/B Multiple recipients of list ORACLE-LBRBSubject:/B 
  Re: 9i RAC on AIX 5LBRBR/FONT/DIVBRFONT face=sans-serif size=2Hi, 
  /FONTBRBRFONT face=sans-serif size=2Question: did you note much I/O 
  when running the sort ??. In AIX it's usefull to make some modifications in 
  paging space behavior in order to reduce I/O contention ad let the ORACLE SGA 
  be in main memory instead on paging space. /FONTBRBRFONT 
  face=sans-serif size=2Try this:/FONT BRBRFONT face=sans-serif 
  size=2Apply the latest

RE: 9I RAC corporate standard.

2003-10-29 Thread Hemant K Chitale

9iRAC on Tru64 is a breeze.  And Tru64 is True Cluster File System.
One of the diminishing breed of people still upgrading databases on Tru64 !
Hemant

At 03:39 PM 28-10-03 -0800, you wrote:
Well, we evaluated 9iRAC on some cheap-o Linux boxes as a proof-of-concept,
with the hardware idea based on
http://www.bradmark.com/site2/products/pdfs/9irac_config.pdf  As far as I'm
concerned, RAC's a major pain, unstable and not yet worth the risk -- for
us.  The idea for us being that we could move a DB or three to this RAC
system with a no-cost OS on commodity hardware giving us HA and some
load-balancing.
I tried installing RHAS 2.1 ($60 duhveloper edition), but it's so old (based
on RH7.2) that it couldn't identify our newish hardware (Intel D845 MB).
Without a clear path to add kernel mods to allow HW identification, I
installed SuSE SLES8.  Yipe!  Never did get far on that one.  Way too many
library/kernel issues to consider it.  I finally ended up testing on RH9
because it could identify our hardware, I have some familiarity with it, and
there are docs on the web (e.g. http://www.puschitz.com) to help get
Oracle9i installed on it.  I didn't have time to try United Linux, although
it does come with a 2.4-19 kernel.
Once that was resolved, I wanted to use a filesystem for Oracle, given the
limitations of RAW on SCSI under Linux (max 15 partitions), so I downloaded
OCFS 1.09.  Well, it wouldn't install because of RH9's newer kernel (it was
only made to work on RHAS2.1).  And when I tried to compile the source, I
got errors.  So I patched the OCFS source with a modified version of a JFS
patch for RH9 and it worked.  Unfortunately though, it didn't perform,
peaking out at about 1.2MB/s peak throughput and I switched to RAW (40-50
times faster).  There's also the ocfstool that you need for monitoring
because OCFS only allows contiguous file extents.  Veritas is supposed to
have a VxFS for Linux as a beta soon...
There's not enough room here for me to go over the software install hell to
get RAC actually on the systems.  And anything Java-based (Installer, DBCA,
OEM, etc.) most of the time flat out refused to run without any errors.  I
thought this was odd considering I didn't have any problem with other
non-Oracle Java programs.  Finally, when I called in a problem to Oracle
Support regarding DBCA, I thought I had a decent tech until I was warned by
him that my SHMMAX kernel setting was too high because it was over physical
RAM.  Also, I've had a helluva time trying to understand the 9iRAC client
setup.  I haven't found any Oracle docs yet that explain it well.
Granted, some/much/all of this is probably because I'm running on an
unsupported version of Linux.  My problem with that is that it shouldn't
freakin matter.  With my luck at getting 9.2.0.4 to run on Gentoo, I just
might try 9iRAC on there...  :)  I would *love* to try 9iRAC on OpenVMS.  It
should be by far the easiest to install and maintain, given the clustering
is builtin to the OS.
Gotta go redo some lvols now...

GL!  You'll need it!

Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA


 -Original Message-
 From: Spears, Brian [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, October 28, 2003 4:24 PM
 To: Multiple recipients of list ORACLE-L
 Subject: 9I RAC corporate standard.



  Hi,  Has anyone started to implement 9I Rac as a corporate
 standard... IE. many or all the apps being deployed on 9I RAC
 clusters?

  We are looking at doing it and wanted to know what other
 people had as experience in doing it or on the  way to attempting it.

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

RE: dynamic sql problem

2003-10-29 Thread Siddharth Haldankar










Thanks for all those who answered

Using authid current_user in package has solved my problem.





With Warm Regards







Siddharth
Haldankar

Zensar Technologies Ltd.

Cisco Systems Inc. 

(Offshore Development Center)

# : 091 020 4128394

[EMAIL PROTECTED]

[EMAIL PROTECTED] 



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Charu
Joshi
Sent: Tuesday, October 28, 2003
5:55 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: dynamic sql problem





Siddharth,











All roles are disabled in any named PL/SQL block
(stored procedure, function, or

trigger) that executes with definer rights.



The SESSION_ROLES view shows all roles that are
currently enabled. If a named

PL/SQL block that executes with definer rights queries
SESSION_ROLES, the query

does not return any rows.



Named PL/SQL blocks that execute with invoker rights
and anonymous PL/SQL

blocks are executed based on privileges granted
through enabled roles.



So the problem might be that you have been
granted 'CREATE TABLE' through a role and not directly.



Regards,





Charu.





-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of Siddharth
Haldankar
Sent: 28 October 2003 17:09
To: Multiple recipients of list
ORACLE-L
Subject: dynamic sql problem

Hi Gurus,



I have problem running Dynamic SQL through a package,
though it runs fine in a unnamed block.



This is the sample code

DECLARE

lv_sql_stmt VARCHAR2(2000);

begin

 lv_sql_stmt := 'create table a_temp (a
number)';

 EXECUTE IMMEDIATE lv_sql_stmt;

end;

/



This runs fine.



But as soon as I put this inside a package I get an
error

PROCEDURE test 

is

lv_sql_stmt VARCHAR2(2000);

begin

 lv_sql_stmt := 'create table a_temp (a
number)';

 EXECUTE IMMEDIATE lv_sql_stmt;

end;



ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at COMMADM.CT_REFRESH_PK, line
415

ORA-06512: at line 1



This line 415 is the execute immediate line.



Any clues why this is acting strangely.



Thanks in advance for your time in answering to my
query





With Warm Regards







Siddharth Haldankar

Zensar
Technologies Ltd.

Cisco
Systems Inc. 

(Offshore
Development Center)

#
: 091 020 4128394

[EMAIL PROTECTED]

[EMAIL PROTECTED]













*
Disclaimer

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*

Visit us at http://www.mahindrabt.com

RE: RE: Date-based query Q

2003-10-29 Thread Stephane Faroult

Same idea as what Iain suggests, dreadful implementation :

SQL select trunc(ukdate) ukdate, count(*) from test
  2  group by trunc(ukdate);

UKDATE   COUNT(*)
-- --
01/01/2003  5
02/01/2003  6
04/01/2003  6

SQL get x
  1  select y.full_ukdate ukdate,
  2 nvl(x.cnt, 0) COUNT(*)
  3  from (select trunc(ukdate) ukdate,
  4   count(*) cnt
  5from test
  6group by trunc(ukdate)) x,
  7   (select a.rn + b.mindate - 1 full_ukdate
  8from (select rownum rn
  9  from all_tab_columns) a,
 10  (select min(ukdate) mindate,
 11  max(ukdate) maxdate
 12   from test) b
 13 where a.rn = b.maxdate - b.mindate + 1) y
 14* where x.ukdate (+) = y.full_ukdate
SQL /

UKDATE   COUNT(*)
-- --
01/01/2003  5
02/01/2003  6
03/01/2003  0
04/01/2003  6

Do you _really_ want that :-) ?

Didn't find analytical functions of much help on this one ...

SF

- --- Original Message --- -
From: Nicoll, Iain [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Wed, 29 Oct 2003 04:44:25

You colud try joining to an in-line view something
like

SELECT :XDATE+(ROWNUM-1) DDATE  
FROM DBA_OBJECTS
WHERE ROWNUM = (:YDATE - :xdate)+1

where dba_objects could be any table with enough
rows to ensure you always
covered the complete range.



-Original Message-
Aidan Whitehall
Sent: 29 October 2003 10:49
To: Multiple recipients of list ORACLE-L


This is probably a no-brainer...

We have some date-based data for which most days
have several records
but where some days have none. I'm COUNT()ing the
number of records for
each day (between day x and day y) and need a
record set that also
includes a row for those days which have no
records:

UkDate  Total
1/1/20035
2/1/20036
3/1/20030
4/1/20036

I could post-process the record set to achieve
this, but is there any
way in 9i to do an aggregate query with an outer
join on a date range
(if that makes sense)?

Someone made the suggestion of creating another
table with a row for
every day under the sun in it, against which you
could inner join the
main query, but I'm not keen on that (that is just
a gut response
though).

Any ideas? Thanks!

-- 
Aidan Whitehall
mailto:[EMAIL PROTECTED]
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd  +44 (0)1695 51775
Queen's Awards Winner 2003
http://www.fairbanks.co.uk/go/awards
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

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

Re: Refresh option for Materialized view , want to use it during refresh - for

2003-10-29 Thread David Boyd

Thanks, Arup.  Your advice is always good.

David

From: Arup Nanda [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Refresh option for Materialized view , want to use it during 
refresh - for
Date: Tue, 28 Oct 2003 12:09:25 -0800

David,

Glad to be of help.

Looking at your list of steps, I see that your questions is whether 
building
an MV on the table T is beneficial. Tables and MVs, on prebuilt table or
not, are stored as segments in the database; so space-sise there is no
difference, nor there is any change in the way the MV/Table is accessed.

There are a few situations where you may want to convert a table to MV. 
They
are:

* Building an MV enables Query Rewrite, where Oracle smartly decides to
rewrite a user query to select from the MV instead of the main tables. This
is not possible on a regular table. The user must explicitly select from 
it.

* If you want to refresh FAST, then MVs are required. You can do a fast
refresh on a table, but you have to write your own procedures for that.
DBMS_MVIEW package does it for you on MVs.
* Your designer software will recognize MV as one and will report it to all
users, who are aware of the fact that it's an MV, useful for queries. A 
mere
table will not be clear on that regard.

Converting a table to MV does not cost any resource, as the change is done
inside the data dictionary only. So, if you are in doubt, you may just
convert the table to MV anyway.
HTH.

Arup Nanda

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 28, 2003 12:04 PM
refresh - for
 Arup,

 I really appreciate your answer in great details.  I got on prebuilt
table
 work.  Thanks a lot for your help.  Here is another question:

 Do you see any advantage to use materialized view on prebuilt table for 
my
 data loading over just simple renaming tables as steps below:

 1. create table t that is always accessed by applications
 2. create table t1 that is a temp table for loading
 3. load data into table t1
 4. rename table t to table t2
 5. rename table t1 to t
 6. rename table t2 to t1
 7. truncate table t1 for next day loading

 David

 From: Arup Nanda [EMAIL PROTECTED]
 Reply-To: [EMAIL PROTECTED]
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Re: Refresh option for Materialized view , want to use it 
during
 refresh - for
 Date: Fri, 24 Oct 2003 18:04:33 -0800

 David,

 Answers to your questions:

 (1) Without knowing your exact needs, I wil offer a few different
 scenarios.
 I am assuming that you are doing a complete refresh every time. The
 following pertain to that.

 Say, your name of the MV is MV1. Here are the steps the first time.

 1. Create table MV1
 2. Create MV MV1 on that table.

 When you want to refresh complete:

 1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink
 (with
 NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp.
 2. Drop MV MV1. This drops the MV but doesn't drop the table.
 3. Drop table MV1.
 4. Rename table MV1_TEMP to MV1.
 5. Recreate MV MV1.
 6. Allow users to proceed as usual.

 Note the time consumed between Steps 2 and 6 are in the order of a few
 seconds. And it's the only time the users will not have access to the 
MV,
 as
 opposed to a full refresh using dbms_mview.refresh approach., which 
will
 lock the MV for the entire duration and generate tons of redo and
rollback.

 Even if you do a incremental refresh, this is still a better approach. 
In
 that case, you don't drop the table during the refresh.

 (2) Yes, the option is available in 8i, at least in 8.1.7. Make sure 
the
 syntax is correct.

 create materialized view MV1
 on prebuilt table
 refresh fast
 as
 select ... from 

 In the article I mentioned, you can find the complete syntax.
 www.proligence.com/downloads.html is the site. It also dscribes a step 
by
 step solution to the issue and compares the common solution with this 
new
 one.

 Hope this helps.

 Arup Nanda

 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Friday, October 24, 2003 12:34 PM
 refresh - for

   Hi Arup,

   This is a very good method.  I would like to use it to modify some 
of
my
   data loading procedures.  Here are my questions:
   1. Do I need to create the table on the step 1 every time when I
refresh
 the
   data If I refresh data once per day?
   2. Is ON PREBUILT TABLE available on Oracle 8i?  When I was trying
the
   method on Oracle 8i, I got missing keyword error on PREBUILT.

   Dave

   Siddharth,

   I will offer a slightly out-of-the-box solution. Please read it
through
   till the end to determine its applicability in your case.

   It seems yours refresh interval is once a day and you don't mind
stale
   data for a max of 24 hours. You also refresh is complete, not
   incremental. So, I would suggest the follwoing approach.

SQL and PL/SQL tuning template document required urgently

2003-10-29 Thread Ranganath K

Hi there,

Does any body have a template for proactive and reactive query tuning which 
can be used as a guideline/report while tuning simple, medium complex and complex SQL 
queries and PL/SQL stored procedures?  If so, can you please forward the same to me 
please?  If not, can anybody suggest as to how to go about doing one?  Any help in 
this regard is very much appreciated.

Thanks and Regards,

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

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

2003-10-29 Thread Mark Leith

How about:

http://www.oreilly.com/catalog/orsqltunpr/

Oracle SQL Tuning Pocket Reference, by one of our esteemed partners - Mark
Gurry.

Mark


-Original Message-
Ranganath K
Sent: 29 October 2003 14:24
To: Multiple recipients of list ORACLE-L


Hi there,

Does any body have a template for proactive and reactive query tuning which
can be used as a guideline/report while tuning simple, medium complex and
complex SQL queries and PL/SQL stored procedures?  If so, can you please
forward the same to me please?  If not, can anybody suggest as to how to go
about doing one?  Any help in this regard is very much appreciated.

Thanks and Regards,

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003

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

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

2003-10-29 Thread Spears, Brian



What hardware did you get 9Irac running on?

Thanks Raj,
Brian Spears
Database Services
[EMAIL PROTECTED]
DC4 (614)577-2677 
DC3 (614)415-1398
 Limitedbrands
 TECHNOLOGY SERVICES
 
 


-Original Message-
Jamadagni, Rajendra
Sent: Tuesday, October 28, 2003 9:39 PM
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]


We don't have a corporate policy per se, but everything we have ('cept couple of dbs 
in the dmz) is RAC. PROD/DEVL/TEST/QA/DEMO etc etc everything same size same config 
(except maybe for scaled down SGA etc).

Raj


**
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.
**5
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: [EMAIL PROTECTED]

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

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

RE: oracle internet directory

2003-10-29 Thread McBain, Neil SITI-ITDIEEE

Helmut,

I suppose I have experience, not good, since I am currently trying to implement it as 
a test on out test server which is 9.2.0.4 on HP-UX 11. I played with OID v2 (Oracle 
8i) and gave up and despite getting OID v3 setup and working, I decided to tidy up the 
multiple Oracle Contexts I had in OID and then realised halfway through it probably 
was not a good idea and true enough it is bust now. I have tried running oidca and 
setting it up again before and after trashing the ODS schema but I still get the 
rather descriptive Java errors of error !! I am now considering trashing and 
rebuilding the instance and starting from scratch but am begining to worry that there 
is more info stored in the file system than I am unaware of which will need to removed 
(i.e. not just ldap.ora). I am willing to try and help but you may be further along 
than I am.

Neil.

-Original Message-
Sent: 29 October 2003 08:19
To: Multiple recipients of list ORACLE-L


Hi!

Does anybody out there have any experience with the setup and implementation
of Oracle Internet Directory in a 9.2 environment?

This is 9.2 on HP-UX 11.

Thanks,
Helmut

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

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

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

Re: Clone db 9.2 on AIX 5L

2003-10-29 Thread Joan Hsieh

I did it yesterday on AIX 5L 9.2.0.4, no problem at all

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

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

2003-10-29 Thread Mercadante, Thomas F

How about this?

SELECT col_date, SUM(COUNT)
FROM (
  SELECT ukdate, COUNT(*) COUNT
  FROM tomtest
  GROUP BY ukdate
  UNION
  SELECT ADD_MONTHS(TO_DATE('12012002','mmdd'),ROWNUM) dba_month,0
  FROM DBA_OBJECTS
  WHERE ROWNUM  13)
GROUP BY ukdate




Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Wednesday, October 29, 2003 9:09 AM
To: Multiple recipients of list ORACLE-L


Same idea as what Iain suggests, dreadful implementation :

SQL select trunc(ukdate) ukdate, count(*) from test
  2  group by trunc(ukdate);

UKDATE   COUNT(*)
-- --
01/01/2003  5
02/01/2003  6
04/01/2003  6

SQL get x
  1  select y.full_ukdate ukdate,
  2 nvl(x.cnt, 0) COUNT(*)
  3  from (select trunc(ukdate) ukdate,
  4   count(*) cnt
  5from test
  6group by trunc(ukdate)) x,
  7   (select a.rn + b.mindate - 1 full_ukdate
  8from (select rownum rn
  9  from all_tab_columns) a,
 10  (select min(ukdate) mindate,
 11  max(ukdate) maxdate
 12   from test) b
 13 where a.rn = b.maxdate - b.mindate + 1) y
 14* where x.ukdate (+) = y.full_ukdate
SQL /

UKDATE   COUNT(*)
-- --
01/01/2003  5
02/01/2003  6
03/01/2003  0
04/01/2003  6

Do you _really_ want that :-) ?

Didn't find analytical functions of much help on this one ...

SF

- --- Original Message --- -
From: Nicoll, Iain [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Wed, 29 Oct 2003 04:44:25

You colud try joining to an in-line view something
like

SELECT :XDATE+(ROWNUM-1) DDATE  
FROM DBA_OBJECTS
WHERE ROWNUM = (:YDATE - :xdate)+1

where dba_objects could be any table with enough
rows to ensure you always
covered the complete range.



-Original Message-
Aidan Whitehall
Sent: 29 October 2003 10:49
To: Multiple recipients of list ORACLE-L


This is probably a no-brainer...

We have some date-based data for which most days
have several records
but where some days have none. I'm COUNT()ing the
number of records for
each day (between day x and day y) and need a
record set that also
includes a row for those days which have no
records:

UkDate  Total
1/1/20035
2/1/20036
3/1/20030
4/1/20036

I could post-process the record set to achieve
this, but is there any
way in 9i to do an aggregate query with an outer
join on a date range
(if that makes sense)?

Someone made the suggestion of creating another
table with a row for
every day under the sun in it, against which you
could inner join the
main query, but I'm not keen on that (that is just
a gut response
though).

Any ideas? Thanks!

-- 
Aidan Whitehall
mailto:[EMAIL PROTECTED]
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd  +44 (0)1695 51775
Queen's Awards Winner 2003
http://www.fairbanks.co.uk/go/awards
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

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

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

the only book out there that is any good is High Performance Tuning by Guy Harrison. 
Ignore the part where he says that a cursor with an update in the loop can be faster 
than using an update with where exists. That is inaccurate.

the rest is solid. 

unforunately its not that simple. How you tune depends on the type of system you have. 
If your in an OLTP system with alot of concurrency, you tune heavily for Logical I/Os 
even if the query takes longer to run in isolation. If your doing batch queries, then 
you tune more for response time and less for logical I/Os.

its just not that simple. 
 
 From: Ranganath K [EMAIL PROTECTED]
 Date: 2003/10/29 Wed AM 09:24:25 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: SQL and PL/SQL tuning template document required urgently
 
 Hi there,
 
   Does any body have a template for proactive and reactive query tuning which 
 can be used as a guideline/report while tuning simple, medium complex and complex 
 SQL queries and PL/SQL stored procedures?  If so, can you please forward the same to 
 me please?  If not, can anybody suggest as to how to go about doing one?  Any help 
 in this regard is very much appreciated.
 
 Thanks and Regards,
 
 Ranganath
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Ranganath K
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (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).

64-bit LInux

2003-10-29 Thread Jerome Roa

Anybody used 64 bit linux? Can you send me your hardware specs?

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

8.0.5 standard edition

2003-10-29 Thread Farnsworth, Dave

By any chance does anyone know where I can download this??  Long story but I need to 
downgrade a database from 8.0.5 enterprise to standard edition. The admins can't find 
the standard CD.  I have been looking on oracles site but have not found it yet.   I 
guess I wouldn't expect to since it is a bit outdated.

Thanks,

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

When would we see optimizer_mode=NONE in V$SQLAREA ?

2003-10-29 Thread Hemant K Chitale



I noticed in an 9.2 instance that a number of entries in V$SQL,  V$SQLAREA
showed up with OPTIMIZER_MODE=NONE   [there were others with CHOOSE]
I can understand that it might be NONE if someone has done an ANALYZE or 
DBMS_STATS
or executed DDL and the SQLs are invalidated.
But do you normally see a number of entries in V$SQL like that ?

[I had approx 20% of the entries].

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: comparison HP-san vs netapp

2003-10-29 Thread Jeroen van Sluisdam










Dick, Mathew



Thanks
for your responses. It helped making clear our choice in convincing mng

We
will go for an HP-Eva3000 san solution. We have only a 100Mb network and this

makes clear for all cost-minded people we have to make additional investments
such

that a netapp solution is more expensive compared
to the eva.



Jeroen





-Oorspronkelijk
bericht-
Van: Goulet, Dick
[mailto:[EMAIL PROTECTED] 
Verzonden: Thursday, October 23,
2003 19:04
Aan: Multiple recipients of list
ORACLE-L
Onderwerp: RE: comparison HP-san
vs netapp





Jeroen,











 NetApp depends on TCP/IP to use their products. Now that's
NOT a bad thing, but you need to isolate the file traffic from your general
network. With a SAN your using normal disk io channels into the switch,
which effectively isolates file activity from the network. It's your
choice, but having to use NFS for everything can become
one heck of a bottleneck.









Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 



-Original
Message-
From: Jeroen van Sluisdam
[mailto:[EMAIL PROTECTED]
Sent: Thursday, October 23, 2003
10:49 AM
To: Multiple recipients of list
ORACLE-L
Subject: comparison HP-san vs
netapp



Hi,



I need urgently a qualitative comparison between an SAN
(based on eva3000)

and netapp F825 environment concerning oracle.

We have been tallking to suppliers now for weeks and suddenly
a manager comes

up with a netapps alternative and we have a deadline to
decide already weeks ago.

Anybody with real good links or shortlist of conclusions,
criteria on this?



Thnx in advance,



Jeroen

RE: Re: SQL and PL/SQL tuning template document required urgently

2003-10-29 Thread Stephane Faroult

Ryan,

  'can be faster' is rarely inaccurate. It all depends.

SF

- --- Original Message --- -
From: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Wed, 29 Oct 2003 06:49:33

the only book out there that is any good is High
Performance Tuning by Guy Harrison. Ignore the part
where he says that a cursor with an update in the
loop can be faster than using an update with where
exists. That is inaccurate.

the rest is solid. 

unforunately its not that simple. How you tune
depends on the type of system you have. If your in
an OLTP system with alot of concurrency, you tune
heavily for Logical I/Os even if the query takes
longer to run in isolation. If your doing batch
queries, then you tune more for response time and
less for logical I/Os.

its just not that simple. 

 From: Ranganath K [EMAIL PROTECTED]
 Date: 2003/10/29 Wed AM 09:24:25 EST
 To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
 Subject: SQL and PL/SQL tuning template document
required urgently

 Hi there,

   Does any body have a template for proactive
and reactive query tuning which can be used as a
guideline/report while tuning simple, medium
complex and complex SQL queries and PL/SQL stored
procedures?  If so, can you please forward the same
to me please?  If not, can anybody suggest as to
how to go about doing one?  Any help in this regard
is very much appreciated.

 Thanks and Regards,

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

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

RE: dba interview questions

2003-10-29 Thread Paul Baumgartel

What was all of that about swallows, anyway?

And Bambi, didn't your last name formerly end with an s?

It's great to have you back on the list, in any event.


--- Bellow, Bambi [EMAIL PROTECTED] wrote:
 Kirti --
  
 I've had that experience too.  I generally start a telephone
 interview
 hopping around the person's resume and asking questions about
 individual
 jobs... what did they do here... what do they mean by that there
 what
 was the environment over there... was management supportive in that
 job over
 there... now let's talk about the technical side of things... because
 there's no way to tell whether you are talking to the person who's
 going in
 for the F2F over the phone...
  
 Notice that nowhere in there is any reference to swallows.  That
 comes later
 in the interview when you want to discern whether this is established
 knowledge or just crammed 30 minutes before the phone is supposed to
 ring.
  
 Bambi.
 
 -Original Message-
 Sent: Tuesday, October 28, 2003 4:35 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Better still, sometimes 'X' takes the questions while 'Y' the
 candidate just
 stands next to him
 because 'X' can answer but Y does not. 
 
 Tel.int means you (most probably) have not seen the guys
 face..correct??
 
 By the time Visa gets approved (if overseas candidate), 'Y' ensures
 that he
 'gains' some experience.
 
 GovindanK
 
 -Original Message- 
 Sent: 10/24/2003 7:45:29 AM
 To: [EMAIL PROTECTED]
 
 Oh! Well. I have not seen Tom's book yet. 
 But still, when the candidate is explaining this stuff to you, there
 are
 plenty of opps to
 question him/her to find out if he/she really knows fundamental
 things..
 During one phone interview, we could clearly hear the paper shuffle
 in the
 background, while the
 candidate asked us to repeat the question (a couple of times) to
 'make sure'
 he understood it
 correctly before answering (reading?)it :) 
 We stopped phone interview process after this!! 
 
 - Kirti
 
 --- [EMAIL PROTECTED] wrote:
  that question is diagrammed and answered in tom kytes new book. :)
 im
 waiting to get asked it. 
  
  there is a new ault book out on interview questions. I dont think
 they are
 very tough. I think
  situational questions are better. Have a development DB set up with
 things
 for the applicant to
  do. 
  
  I find that most employers ask the same easy questions.
 Particularly
 developer questions 
  
  
  --- system manager wrote:
  Dear List, Can anyone send me a list of dba interview questions?
  
  Thanks,
  
  
 
 ___
 Get Your 10MB account for FREE at http://mail.arabia.com !
  http://ads.arabia.com/?SHT=text_email_english Access MILLIONS of
 JOBS
 NOW!
 
 


__
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

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

RE: oracle internet directory

2003-10-29 Thread Paul Baumgartel


I have OID (with replication) up and running smoothly on Windows 2000 /
Oracle 9.2.0.4.  Of course, I've forgotten how I did it, but your
questions may jog my memory.

 
 -Original Message-
 Sent: 29 October 2003 08:19
 To: Multiple recipients of list ORACLE-L
 
 
 Hi!
 
 Does anybody out there have any experience with the setup and
 implementation
 of Oracle Internet Directory in a 9.2 environment?
 
 This is 9.2 on HP-UX 11.
 
 Thanks,
 Helmut
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Daiminger, Helmut
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (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: McBain, Neil  SITI-ITDIEEE
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).


=
Paul Baumgartel
Transcentive, Inc.
www.transcentive.com

__
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

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

RE: SQL and PL/SQL tuning template document required urgently

2003-10-29 Thread DENNIS WILLIAMS

Ranganath
   Since you mentioned proactive and reactive query tuning, I think the
philosophy with which one approaches the tuning exercise means everything.
Wrong philosophy and you spend your time spinning your wheels. All of us
have only a limited amount of time to devote, so the best approach will make
the best use of that time. 
   Get Optimizing Oracle Performance by Cary Millsap. It doesn't take long
to read the important parts. Implement Cary's approach to locate the queries
where you will get the most bang for the buck. Then use books like Guy
Harrison's (Ryan's suggestion) for pointers on making those queries perform
better.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, October 29, 2003 8:24 AM
To: Multiple recipients of list ORACLE-L


Hi there,

Does any body have a template for proactive and reactive query
tuning which can be used as a guideline/report while tuning simple, medium
complex and complex SQL queries and PL/SQL stored procedures?  If so, can
you please forward the same to me please?  If not, can anybody suggest as to
how to go about doing one?  Any help in this regard is very much
appreciated.

Thanks and Regards,

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

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

2003-10-29 Thread Browett, Darren

I couldn't agree more, after the pains of an initial install (i.e. steep
learning curve), 
I was able to create/re-create a test environment in 1-2 days.  That
included moving hardware around,
setting up the SAN, installing the O/S, patching it, installing 9.2.0.1
rac, then patching that to 9.2.0.4, and
finally copy a database from my production system to the new test
platform.

Darren

-Original Message-
Sent: Wednesday, October 29, 2003 5:55 AM
To: Multiple recipients of list ORACLE-L



9iRAC on Tru64 is a breeze.  And Tru64 is True Cluster File System.
One of the diminishing breed of people still upgrading databases on
Tru64 !

Hemant


At 03:39 PM 28-10-03 -0800, you wrote:
Well, we evaluated 9iRAC on some cheap-o Linux boxes as a
proof-of-concept,
with the hardware idea based on
http://www.bradmark.com/site2/products/pdfs/9irac_config.pdf  As far as
I'm
concerned, RAC's a major pain, unstable and not yet worth the risk --
for
us.  The idea for us being that we could move a DB or three to this RAC
system with a no-cost OS on commodity hardware giving us HA and some
load-balancing.

I tried installing RHAS 2.1 ($60 duhveloper edition), but it's so old
(based
on RH7.2) that it couldn't identify our newish hardware (Intel D845
MB).
Without a clear path to add kernel mods to allow HW identification, I
installed SuSE SLES8.  Yipe!  Never did get far on that one.  Way too
many
library/kernel issues to consider it.  I finally ended up testing on
RH9
because it could identify our hardware, I have some familiarity with
it, and
there are docs on the web (e.g. http://www.puschitz.com) to help get
Oracle9i installed on it.  I didn't have time to try United Linux,
although
it does come with a 2.4-19 kernel.

Once that was resolved, I wanted to use a filesystem for Oracle, given
the
limitations of RAW on SCSI under Linux (max 15 partitions), so I
downloaded
OCFS 1.09.  Well, it wouldn't install because of RH9's newer kernel (it
was
only made to work on RHAS2.1).  And when I tried to compile the source,
I
got errors.  So I patched the OCFS source with a modified version of a
JFS
patch for RH9 and it worked.  Unfortunately though, it didn't perform,
peaking out at about 1.2MB/s peak throughput and I switched to RAW
(40-50
times faster).  There's also the ocfstool that you need for monitoring
because OCFS only allows contiguous file extents.  Veritas is supposed
to
have a VxFS for Linux as a beta soon...

There's not enough room here for me to go over the software install
hell to
get RAC actually on the systems.  And anything Java-based (Installer,
DBCA,
OEM, etc.) most of the time flat out refused to run without any errors.
I
thought this was odd considering I didn't have any problem with other
non-Oracle Java programs.  Finally, when I called in a problem to
Oracle
Support regarding DBCA, I thought I had a decent tech until I was
warned by
him that my SHMMAX kernel setting was too high because it was over
physical
RAM.  Also, I've had a helluva time trying to understand the 9iRAC
client
setup.  I haven't found any Oracle docs yet that explain it well.

Granted, some/much/all of this is probably because I'm running on an
unsupported version of Linux.  My problem with that is that it
shouldn't
freakin matter.  With my luck at getting 9.2.0.4 to run on Gentoo, I
just
might try 9iRAC on there...  :)  I would *love* to try 9iRAC on
OpenVMS.  It
should be by far the easiest to install and maintain, given the
clustering
is builtin to the OS.

Gotta go redo some lvols now...

GL!  You'll need it!

Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA



  -Original Message-
  From: Spears, Brian [mailto:[EMAIL PROTECTED]
  Sent: Tuesday, October 28, 2003 4:24 PM
  To: Multiple recipients of list ORACLE-L
  Subject: 9I RAC corporate standard.
 
 
 
   Hi,  Has anyone started to implement 9I Rac as a corporate
  standard... IE. many or all the apps being deployed on 9I RAC
  clusters?
 
   We are looking at doing it and wanted to know what other
  people had as experience in doing it or on the  way to attempting
it.
 
   If so, what hardware platform are you using? HP Itanium or
  Linux boxes etc?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
   INET: [EMAIL PROTECTED]

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

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web

sqlplus prompt question in 9i

2003-10-29 Thread Guang Mei

Hi:

With Oracle 8i, I always modified $ORACLE_HOME/sqlplus/admin/glogin.sql, and
added

set termout off
col site_name noprint new_value site_name_new
select 'SQL ' site_name from dual;
selectuser
   || substr(proc.program, instr(proc.program,'@'),
 instr(proc.program,' ') - instr(proc.program,'@'))
   || '-SQL ' site_name
from v$process proc
where proc.pid = 2;
set sqlprompt 'site_name_new'
set termout on

so that when a user launches sqlplus, it would show something at prompt like

[EMAIL PROTECTED]

instead of

SQL

I found that doing this in 9i will prevent me starting up my instance, when
I use

sqlplus '/as sysdba'

In 8i, I always used svrmgrl to bounce db so there was no problem with
modified glogin.sql.

Has anyone found a work-around in 9i so that sqlplus prompt displays
username and hostname when launched? I know there is a new
_CONNECT_IDENTIFIER in 9i, but that's not good enough.

TIA.

Guang

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

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

RE: Perm job opening in MA

2003-10-29 Thread Igor Neyman

LOL!
Mladen, I think you are missed on off-topic list -:)

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-Original Message-
Mladen Gogala
Sent: Tuesday, October 28, 2003 8:54 PM
To: Multiple recipients of list ORACLE-L

Don't get me wrong, I've recently changed positions and am not
interested,
but what are phone skills? I know how to use a phone, and I can do it
in  
yoga position with one hand tied behind my back. I've never used a phone
under  
water or in space. I use it on a regular basis while commuting or in  
restaurants. It helps tremendously with finding a free seat. As for the

communication over the phone, you should hear my inventive use of the
English  
language when I'm talking to telemarketers. Creative assumptions about
their  
ancestry and its position on the evolution tree and sexual preferences
of their parents are the most common opener after which  I usually take
the  
poor soul to the place where no telemarketer has gone before. Do I have
the  
right idea about the phone skills or you have in mind some extremely  
innovative use of phone which would be inappropriate for a good catholic
like  
me?

On 2003.10.28 20:09, John Spencer wrote:
 I hope I am not breaching any rules, but I would like to make it
public that
 I am currently trying to fill a temp to perm position for a Sr level
 Oracle/customer support person in Massachusetts. This person must have
 strong Oracle and Sun Solaris skills and some Java (J2EE and Java
beans)
 experience. Must have excellent phone skills and the ability to work
with
 customers on installs and other issues. Experience must include stored
 procedures and triggers.
 
 Local candidates only please. Please reply directly to me at
 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
 
 Thanks again for your time.
 
 Regards,
 John Spencer
 Sr. Staffing Consultant
 ProStart Inc.
 603-893-7772 ext 45
 603-893-7704 fax
 mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
 
 


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

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


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

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

Re: oracle internet directory

2003-10-29 Thread Paul Bennett




Hi Helmut:

I have setup and configured OID for 8.1.7, 9.0.1.4 and 9.2.0.4. Also had to 
do a conversion from 8.1.7 to 9.2.0.4. The conversion was a real nightmare. 
Major bug with the bulkload.sh. If you need it I have a step by step conversion 
doc. Everything I have done has been on AIX 4.3.3 and 5.1.

1. Is this a stand alone OID or is it part of 9iAS infrastructure or 
OCS?

2. Whathave you done up to this point? If you are at 9.2.0.4 do not 
use the DBCAOID template tocreate the database. There is a nasty bug 
with theseed database. It installs a 9.2.0.1 OID database and then you 
have upgrade the schema to 9.2.0.4.Create a non-OID database and use OIDCA 
to create the schema for OID.

3. what problems are you having?

Paul Bennett

 [EMAIL PROTECTED] 10/29/03 02:19AM 
Hi!Does anybody out there have any experience with the 
setup and implementationof Oracle Internet Directory in a 9.2 
environment?This is 9.2 on HP-UX 11.Thanks,Helmut-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: 
Daiminger, Helmut INET: [EMAIL PROTECTED]Fat City 
Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, 
California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from). You mayalso send the HELP command for 
other information (like subscribing).
-

This email transmission and any documents, files or previous

email messages attached to it may contain information that is

confidential or legally privileged. If you are not the intended

recipient, you are hereby notified that any disclosure, copying,

printing, distributing or use of this transmission is strictly

prohibited. If you have received this transmission in error,

please immediately notify the sender by telephone or return

email and delete the original transmission and its attachments

without reading or saving in any manner.



The Evangelical Lutheran Good Samaritan Society.


-

RE: ** database configuration assistant scripts

2003-10-29 Thread A Joshi

Jacques,
 Yes. Thank you.Please send the scripts for both versions. You can send them to me directly or to the list. Your help is greatly apreciated. Jacques Kilchoer [EMAIL PROTECTED] wrote:
If you're interested I can send you the sample batch files and scripts I use to create databases on Windows. I have sample scripts for 8.1.7 and 9.2-Original Message-A JoshiI am using database configuration assistant to create a NT database. Is there a way I can get the scripts used in the creation? I clicked on the template but do not know where the template goes. Thank you
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears

Re: sqlplus prompt question in 9i


You could just use login.sql instead, on a per user basis.

If you don't want login.sql to be used, just edit or unset SQLPATH.

Maybe other options available for this in 9i. A perusal of the sqlplus
manual may prove useful.

Jared







Guang Mei [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/29/2003 08:24 AM
Please respond to ORACLE-L


To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:sqlplus prompt question in 9i


Hi:

With Oracle 8i, I always modified $ORACLE_HOME/sqlplus/admin/glogin.sql, and
added

set termout off
col site_name noprint new_value site_name_new
select 'SQL ' site_name from dual;
select  user
|| substr(proc.program, instr(proc.program,'@'),
 instr(proc.program,' ') - instr(proc.program,'@'))
|| '-SQL ' site_name
from v$process proc
where proc.pid = 2;
set sqlprompt 'site_name_new'
set termout on

so that when a user launches sqlplus, it would show something at prompt like

[EMAIL PROTECTED]

instead of

SQL

I found that doing this in 9i will prevent me starting up my instance, when
I use

sqlplus '/as sysdba'

In 8i, I always used svrmgrl to bounce db so there was no problem with
modified glogin.sql.

Has anyone found a work-around in 9i so that sqlplus prompt displays
username and hostname when launched? I know there is a new
_CONNECT_IDENTIFIER in 9i, but that's not good enough.

TIA.

Guang

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

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

the original poster sent me an email stating that he doesnt want a book or 
documentation, he just wants some sort of short cut template. i replied that if one 
exists its garbage. He didnt respond. 

A side note, it seems to be in vogue to recommend advanced books like carrie millsap's 
and tom kytes to beginners. This isnt appropriate. Beginners should start with 
beginning material and work their way up. Starting with advanced material will just 
confuse them. 


 
 From: DENNIS WILLIAMS [EMAIL PROTECTED]
 Date: 2003/10/29 Wed AM 10:49:25 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: SQL and PL/SQL tuning template document required urgently
 
 Ranganath
Since you mentioned proactive and reactive query tuning, I think the
 philosophy with which one approaches the tuning exercise means everything.
 Wrong philosophy and you spend your time spinning your wheels. All of us
 have only a limited amount of time to devote, so the best approach will make
 the best use of that time. 
Get Optimizing Oracle Performance by Cary Millsap. It doesn't take long
 to read the important parts. Implement Cary's approach to locate the queries
 where you will get the most bang for the buck. Then use books like Guy
 Harrison's (Ryan's suggestion) for pointers on making those queries perform
 better.
 
 Dennis Williams
 DBA
 Lifetouch, Inc.
 [EMAIL PROTECTED] 
 
 -Original Message-
 Sent: Wednesday, October 29, 2003 8:24 AM
 To: Multiple recipients of list ORACLE-L
 
 
 Hi there,
 
   Does any body have a template for proactive and reactive query
 tuning which can be used as a guideline/report while tuning simple, medium
 complex and complex SQL queries and PL/SQL stored procedures?  If so, can
 you please forward the same to me please?  If not, can anybody suggest as to
 how to go about doing one?  Any help in this regard is very much
 appreciated.
 
 Thanks and Regards,
 
 Ranganath
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Ranganath K
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (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: [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: dbms_system.ksdwrt


Yes, it works on 7.3.4, tested it just now.

Jared








Pete Finnigan [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/29/2003 05:09 AM
Please respond to ORACLE-L


To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:dbms_system.ksdwrt


Hi everyone,

Does anyone know if this previously undocumented procedure and its
associates for outputting timestamp etc were available on Oracle 7. I
have searched and could not find any indications soundly for or against
this. I do not have access to 7.3.4 at present to check myself. 

I need to write a one off piece of code for version 7.3.4 that needs to
be in pl/sql and cannot use a table or utl_file for output, pipes are a
possibility but i would prefer to use something simpler. I cannot use
dbms_output because of the million byte boundary (unless anyone knows
how to get it to output more than one million bytes?) so I thought of
kdswrt as a possible simple alternative. There could be an issue with
trace file size though.

thanks,

kind regards

Pete
-- 
Pete Finnigan
email:[EMAIL PROTECTED]
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

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

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

it means you need to be able to handle stupid questions without losing your temper. 
the best people to ask on this are the hardware support guys. They get the true 
'gems', when it comes to stupid questions. They should provide training to oracle 
dba's on the topic. 

I was at a conference resently and I saw a video of a performance analyst helping a 
client. The performance analyst grabbed the keyboard and beat the client senseless. 
not exactly the kind of solution they are looking for... 

it also means you know how to speak english and explain complex material to lay 
people. Its kind of like going to the doctor because you have a head ache and the 
doctor recites somethingy ou might hear on ER. Not real helpful to a lay person. 
 
 From: Igor Neyman [EMAIL PROTECTED]
 Date: 2003/10/29 Wed AM 11:24:28 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: Perm job opening in MA
 
 LOL!
 Mladen, I think you are missed on off-topic list -:)
 
 Igor Neyman, OCP DBA
 [EMAIL PROTECTED]
 
 
 
 -Original Message-
 Mladen Gogala
 Sent: Tuesday, October 28, 2003 8:54 PM
 To: Multiple recipients of list ORACLE-L
 
 Don't get me wrong, I've recently changed positions and am not
 interested,
 but what are phone skills? I know how to use a phone, and I can do it
 in  
 yoga position with one hand tied behind my back. I've never used a phone
 under  
 water or in space. I use it on a regular basis while commuting or in  
 restaurants. It helps tremendously with finding a free seat. As for the
 
 communication over the phone, you should hear my inventive use of the
 English  
 language when I'm talking to telemarketers. Creative assumptions about
 their  
 ancestry and its position on the evolution tree and sexual preferences
 of their parents are the most common opener after which  I usually take
 the  
 poor soul to the place where no telemarketer has gone before. Do I have
 the  
 right idea about the phone skills or you have in mind some extremely  
 innovative use of phone which would be inappropriate for a good catholic
 like  
 me?
 
 On 2003.10.28 20:09, John Spencer wrote:
  I hope I am not breaching any rules, but I would like to make it
 public that
  I am currently trying to fill a temp to perm position for a Sr level
  Oracle/customer support person in Massachusetts. This person must have
  strong Oracle and Sun Solaris skills and some Java (J2EE and Java
 beans)
  experience. Must have excellent phone skills and the ability to work
 with
  customers on installs and other issues. Experience must include stored
  procedures and triggers.
  
  Local candidates only please. Please reply directly to me at
  [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
  
  Thanks again for your time.
  
  Regards,
  John Spencer
  Sr. Staffing Consultant
  ProStart Inc.
  603-893-7772 ext 45
  603-893-7704 fax
  mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
  
  
 
 
 -- 
 Mladen Gogala
 Oracle DBA
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Mladen Gogala
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Igor Neyman
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

-- 
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 drop a datafile ?


That won't actually do what you're implying.  Checking the docs would prove helpful.

From the SQL ref:

If the database is in NOARCHIVELOG mode, you must specify the DROP clause to take a datafile offline. However, the DROP clause does not remove the datafile from the database. To do that, you must drop the tablespace in which the datafile resides. Until you do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE.
If the database is in ARCHIVELOG mode, Oracle ignores the DROP keyword.

Jared








Venu Gopal [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/29/2003 05:09 AM
Please respond to ORACLE-L


To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: How to drop a datafile ?



Well, you an actually move all your objects from this datafile into a
different datafile (read it as different TS) and then offline drop the
datafile.
This will ensure that users don't get the error 'xxx.dbf file is
currently inacessible' message.

Cheers!
Venu

-Original Message-
Prem Khanna J
Sent: Wednesday, October 29, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L

Thanx Sinardy  Rachel.

so,the only way is as what Sinardy said.
is that so ? 

Regards,
Jp.

29-10-2003 19:44:25, Rachel Carmichael [EMAIL PROTECTED] wrote:
The doc is right. You cannot drop a single datafile from a tablespace.



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

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


**Disclaimer

Information contained in this E-MAIL being proprietary to Wipro Limited is 
'privileged' and 'confidential' and intended for use only by the individual
 or entity to which it is addressed. You are notified that any use, copying 
or dissemination of the information contained in the E-MAIL in any manner 
whatsoever is strictly prohibited.

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

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

Re: sqlplus prompt question in 9i

2003-10-29 Thread Ron Thomas


As a further FYI-

We do not allow anything to be placed in glogin.sql.  It can screw up anything 
supplied by oracle,
ie, upgrade scripts.  A local login.sql is the best way to go.

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


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

  Sent by: cc: 
   
  [EMAIL PROTECTED]Subject:  Re: sqlplus prompt question 
in 9i
  .com 
   
   
   
   
   
  10/29/2003 10:14 
   
  AM   
   
  Please respond to
   
  ORACLE-L 
   
   
   
   
   




You could just use login.sql instead, on a per user basis.

If you don't want login.sql to be used, just edit or unset SQLPATH.

Maybe other options available for this in 9i.  A perusal of the sqlplus
manual may prove useful.

Jared





Guang Mei [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 10/29/2003 08:24 AM
 Please respond to ORACLE-L


To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:sqlplus prompt question in 9i


Hi:

With Oracle 8i, I always modified $ORACLE_HOME/sqlplus/admin/glogin.sql,
and
added

set termout off
col site_name noprint new_value site_name_new
select 'SQL ' site_name from dual;
selectuser
   || substr(proc.program, instr(proc.program,'@'),
 instr(proc.program,' ') - instr(proc.program,'@'))
   || '-SQL ' site_name
from v$process proc
where proc.pid = 2;
set sqlprompt 'site_name_new'
set termout on

so that when a user launches sqlplus, it would show something at prompt
like

[EMAIL PROTECTED]

instead of

SQL

I found that doing this in 9i will prevent me starting up my instance,
when
I use

sqlplus '/as sysdba'

In 8i, I always used svrmgrl to bounce db so there was no problem with
modified glogin.sql.

Has anyone found a work-around in 9i so that sqlplus prompt displays
username and hostname when launched? I know there is a new
_CONNECT_IDENTIFIER in 9i, but that's not good enough.

TIA.

Guang

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

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

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

rewriting query without using UNION

2003-10-29 Thread Linda Wang

Hi,
I wonder if there's a better way of writing the query below. Basically, I 
would like to return employee records where employee name='JOSE' + all 
employees in deptno=50. My query can have multiple 'OR' criterias where the 
next criteria maybe returning all employees with salary6 in addition to 
the above two criterias. Building the query with multiple UNIONs will 
definitely degrade the query performance. Is there a better way of rewriting 
the query?

Thanks!

linda

select e.id, e.name, d.deptname from emp e, dept d
where e.deptno=d.deptno and e.name='JOSE'
union
select e.id, e.name, d.deptname from emp e, dept d
where e.deptno=d.deptno and d.deptno=50;
_
Want to check if your PC is virus-infected?  Get a FREE computer virus scan 
online from McAfee.
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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


On 10/29/2003 12:29:35 PM, [EMAIL PROTECTED] wrote:

 I was at a conference resently and I saw a video of a performance 
 analyst helping a client. The performance analyst grabbed the keyboard and beat the 
 client senseless. 
 not exactly the kind of solution they are looking for... 
 

It's outrageous that the performance analyst was damaging company's hardware
like that. I understand their feelings.

Mladen Gogala
Oracle DBA



Note:
This message is for the named person's use only.  It may contain confidential, 
proprietary or legally privileged information.  No confidentiality or privilege is 
waived or lost by any mistransmission.  If you receive this message in error, please 
immediately delete it and all copies of it from your system, destroy any hard copies 
of it and notify the sender.  You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to 
monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where 
the message states otherwise and the sender is authorized to state them to be the 
views of any such entity.

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

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

2003-10-29 Thread DENNIS WILLIAMS

Ryan - Thanks for updating us on the status with the user.

As to what we recommend to novice users, as a practical matter, unless the
poster makes their experience or lack thereof clear, I just have to make an
assumption. In this case, since the poster was talking about guidelines and
templates, my assumption was that the poster was setting policy for his/her
site, but I perhaps I made a different assumption based on few facts than
you made.
   As to Cary's book being too advanced for beginners, here is my opinion:
First, Cary has made great efforts to ensure the bulk of his book is very
clear for even managers. And he makes it clear you don't need to understand
Part 2 in order to benefit. Second, if anyone, especially a beginner, takes
the wrong approach to tuning, they can waste many months of effort with few
results (beyond imagined progress). I feel Cary's book offers the best
approach to tuning available today. But an alternative I could suggest is
Christopher Lawson's Oracle Performance Tuning. He offers a nice balance
between the philosophy of tuning as well as practical tips for tuning
specific queries. 
   Sams Publishing puts a User Level rating on their books. Maybe we
should ask O'Reilly to do the same. How about that Jonathan?

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, October 29, 2003 11:24 AM
To: Multiple recipients of list ORACLE-L
urgently


the original poster sent me an email stating that he doesnt want a book or
documentation, he just wants some sort of short cut template. i replied that
if one exists its garbage. He didnt respond. 

A side note, it seems to be in vogue to recommend advanced books like carrie
millsap's and tom kytes to beginners. This isnt appropriate. Beginners
should start with beginning material and work their way up. Starting with
advanced material will just confuse them. 


 
 From: DENNIS WILLIAMS [EMAIL PROTECTED]
 Date: 2003/10/29 Wed AM 10:49:25 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: SQL and PL/SQL tuning template document required urgently
 
 Ranganath
Since you mentioned proactive and reactive query tuning, I think the
 philosophy with which one approaches the tuning exercise means everything.
 Wrong philosophy and you spend your time spinning your wheels. All of us
 have only a limited amount of time to devote, so the best approach will
make
 the best use of that time. 
Get Optimizing Oracle Performance by Cary Millsap. It doesn't take
long
 to read the important parts. Implement Cary's approach to locate the
queries
 where you will get the most bang for the buck. Then use books like Guy
 Harrison's (Ryan's suggestion) for pointers on making those queries
perform
 better.
 
 Dennis Williams
 DBA
 Lifetouch, Inc.
 [EMAIL PROTECTED] 
 
 -Original Message-
 Sent: Wednesday, October 29, 2003 8:24 AM
 To: Multiple recipients of list ORACLE-L
 
 
 Hi there,
 
   Does any body have a template for proactive and reactive query
 tuning which can be used as a guideline/report while tuning simple, medium
 complex and complex SQL queries and PL/SQL stored procedures?  If so, can
 you please forward the same to me please?  If not, can anybody suggest as
to
 how to go about doing one?  Any help in this regard is very much
 appreciated.
 
 Thanks and Regards,
 
 Ranganath
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Ranganath K
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (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: [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

RE: Date-based query Q

2003-10-29 Thread Aidan Whitehall

Thanks for everyone's help with this one, btw.

In the end I bit the bullet and added a dates table.


-- 
Aidan Whitehall mailto:[EMAIL PROTECTED]
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd  +44 (0)1695 51775
Queen's Awards Winner 2003 http://www.fairbanks.co.uk/go/awards


This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk

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

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

Re: Perm job opening in MA

2003-10-29 Thread AK

ha ha ha ha ha ha ha  ;)

-ak

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 28, 2003 5:54 PM


 Don't get me wrong, I've recently changed positions and am not interested,
 but what are phone skills? I know how to use a phone, and I can do it in
 yoga position with one hand tied behind my back. I've never used a phone
under
 water or in space. I use it on a regular basis while commuting or in
 restaurants. It helps tremendously with finding a free seat. As for the
 communication over the phone, you should hear my inventive use of the
English
 language when I'm talking to telemarketers. Creative assumptions about
their
 ancestry and its position on the evolution tree and sexual preferences
 of their parents are the most common opener after which  I usually take
the
 poor soul to the place where no telemarketer has gone before. Do I have
the
 right idea about the phone skills or you have in mind some extremely
 innovative use of phone which would be inappropriate for a good catholic
like
 me?

 On 2003.10.28 20:09, John Spencer wrote:
  I hope I am not breaching any rules, but I would like to make it public
that
  I am currently trying to fill a temp to perm position for a Sr level
  Oracle/customer support person in Massachusetts. This person must have
  strong Oracle and Sun Solaris skills and some Java (J2EE and Java beans)
  experience. Must have excellent phone skills and the ability to work
with
  customers on installs and other issues. Experience must include stored
  procedures and triggers.
 
  Local candidates only please. Please reply directly to me at
  [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
 
  Thanks again for your time.
 
  Regards,
  John Spencer
  Sr. Staffing Consultant
  ProStart Inc.
  603-893-7772 ext 45
  603-893-7704 fax
  mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
 
 
 

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

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

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

Automatic Undo Management Memory management in 9i

2003-10-29 Thread Murali_Pavuloori/Claritas


Fellow Listers,

Could you please share your experience with Automatic Undo Management and
Automatic Memory Management. Would you recommend it?


One of the Sr. DBAs here suggested not to implement automatic memory
management in 9.2.0.3 but wants to implement it in 9.2.0.4. His suggestion
that things would have been fixed in newer version of oracle does'nt seem
right to me.

I have RTFM ed and seems simple for AUM ...as with memory management, I am
a little hesitant and would like to consider your experiences.

Thanks in advance.

Murali.



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

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

RE: Clone db 9.2 on AIX 5L

2003-10-29 Thread John Blake


Thankyou all who have responded to this...
as it turns out the parameters for
shared_pool /large_pool were to large for the receiving machine after I
decreased the values to a minimal size I was able to recreate the instance.
Thanks again
John
-Original Message-
Joan Hsieh
Sent: Wednesday, October 29, 2003 9:49 AM
To: Multiple recipients of list ORACLE-L


I did it yesterday on AIX 5L 9.2.0.4, no problem at all

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

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

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

Neither Tom Kyte's nor Cary Millsap's books are too advanced for beginners.

In the case of Tom's book, they may have to ponder things a bit, and actually try
the code for themselves to get a good understanding. So what? That's how
you learn.

Regarding Cary's book, what's so hard about it? Beginner's could do much
worse by reading some of the claptrap pablum that is available, but that will
just make it harder for them to understand the good stuff when they see it.

Also, re the comments about the math in Cary's book: What's the big deal?
So what if you don't understand the math? There isn't really that much of it,
and you can undestand the material even if you can't follow the proof.

Jared

Oracle - steroids for your brain.

[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/29/2003 09:24 AM
Please respond to ORACLE-L

To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: RE: SQL and PL/SQL tuning template document required urgently

the original poster sent me an email stating that he doesnt want a book or documentation, he just wants some sort of short cut template. i replied that if one exists its garbage. He didnt respond.

A side note, it seems to be in vogue to recommend advanced books like carrie millsap's and tom kytes to beginners. This isnt appropriate. Beginners should start with beginning material and work their way up. Starting with advanced material will just confuse them.

From: DENNIS WILLIAMS [EMAIL PROTECTED]
Date: 2003/10/29 Wed AM 10:49:25 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: SQL and PL/SQL tuning template document required urgently

Ranganath
Since you mentioned proactive and reactive query tuning, I think the
philosophy with which one approaches the tuning exercise means everything.
Wrong philosophy and you spend your time spinning your wheels. All of us
have only a limited amount of time to devote, so the best approach will make
the best use of that time.
Get Optimizing Oracle Performance by Cary Millsap. It doesn't take long
to read the important parts. Implement Cary's approach to locate the queries
where you will get the most bang for the buck. Then use books like Guy
Harrison's (Ryan's suggestion) for pointers on making those queries perform
better.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]

-Original Message-
Sent: Wednesday, October 29, 2003 8:24 AM
To: Multiple recipients of list ORACLE-L

Hi there,

Does any body have a template for proactive and reactive query
tuning which can be used as a guideline/report while tuning simple, medium
complex and complex SQL queries and PL/SQL stored procedures? If so, can
you please forward the same to me please? If not, can anybody suggest as to
how to go about doing one? Any help in this regard is very much
appreciated.

Thanks and Regards,

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

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

Re: rewriting query without using UNION

I don't see why would query with multiple unions necessarily degrade performance,
but here is another way for writing your query:

select e.id, e.name, d.deptname from emp e, dept d
where e.deptno=d.deptno and ( e.name='JOSE' or d.deptno=50)
/

That would be a union of all employees from the department with deptno=50 plus
the ones called JOSE.


On 10/29/2003 12:54:26 PM, Linda Wang wrote:
 Hi,
 I wonder if there's a better way of writing the query below. Basically, I 
 would like to return employee records where employee name='JOSE' + all 
 employees in deptno=50. My query can have multiple 'OR' criterias where the 
 next criteria maybe returning all employees with salary6 in addition to 
 the above two criterias. Building the query with multiple UNIONs will 
 definitely degrade the query performance. Is there a better way of rewriting 
 the query?
 
 Thanks!
 
 linda
 
 select e.id, e.name, d.deptname from emp e, dept d
 where e.deptno=d.deptno and e.name='JOSE'
 union
 select e.id, e.name, d.deptname from emp e, dept d
 where e.deptno=d.deptno and d.deptno=50;
 
 _
 Want to check if your PC is virus-infected?  Get a FREE computer virus scan 
 online from McAfee.
 http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Linda Wang
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

Mladen Gogala
Oracle DBA



Note:
This message is for the named person's use only.  It may contain confidential, 
proprietary or legally privileged information.  No confidentiality or privilege is 
waived or lost by any mistransmission.  If you receive this message in error, please 
immediately delete it and all copies of it from your system, destroy any hard copies 
of it and notify the sender.  You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to 
monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where 
the message states otherwise and the sender is authorized to state them to be the 
views of any such entity.

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

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

i guess i jumped to conclusions, but i took it from the level of his questions. 'basic 
template' = looking for shortcut = what people look for when they are new to something 
and want to get started quick = not always a bad idea. 

 From: DENNIS WILLIAMS [EMAIL PROTECTED]
 Date: 2003/10/29 Wed PM 12:59:34 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: RE: SQL and PL/SQL tuning template document required urgently

 Ryan - Thanks for updating us on the status with the user.

 As to what we recommend to novice users, as a practical matter, unless the
 poster makes their experience or lack thereof clear, I just have to make an
 assumption. In this case, since the poster was talking about guidelines and
 templates, my assumption was that the poster was setting policy for his/her
 site, but I perhaps I made a different assumption based on few facts than
 you made.
As to Cary's book being too advanced for beginners, here is my opinion:
 First, Cary has made great efforts to ensure the bulk of his book is very
 clear for even managers. And he makes it clear you don't need to understand
 Part 2 in order to benefit. Second, if anyone, especially a beginner, takes
 the wrong approach to tuning, they can waste many months of effort with few
 results (beyond imagined progress). I feel Cary's book offers the best
 approach to tuning available today. But an alternative I could suggest is
 Christopher Lawson's Oracle Performance Tuning. He offers a nice balance
 between the philosophy of tuning as well as practical tips for tuning
 specific queries. 
Sams Publishing puts a User Level rating on their books. Maybe we
 should ask O'Reilly to do the same. How about that Jonathan?

 Dennis Williams
 DBA
 Lifetouch, Inc.
 [EMAIL PROTECTED] 

 -Original Message-
 Sent: Wednesday, October 29, 2003 11:24 AM
 To: Multiple recipients of list ORACLE-L
 urgently

 the original poster sent me an email stating that he doesnt want a book or
 documentation, he just wants some sort of short cut template. i replied that
 if one exists its garbage. He didnt respond. 

 A side note, it seems to be in vogue to recommend advanced books like carrie
 millsap's and tom kytes to beginners. This isnt appropriate. Beginners
 should start with beginning material and work their way up. Starting with
 advanced material will just confuse them. 

  From: DENNIS WILLIAMS [EMAIL PROTECTED]
  Date: 2003/10/29 Wed AM 10:49:25 EST
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Subject: RE: SQL and PL/SQL tuning template document required urgently

  Ranganath
 Since you mentioned proactive and reactive query tuning, I think the
  philosophy with which one approaches the tuning exercise means everything.
  Wrong philosophy and you spend your time spinning your wheels. All of us
  have only a limited amount of time to devote, so the best approach will
 make
  the best use of that time. 
 Get Optimizing Oracle Performance by Cary Millsap. It doesn't take
 long
  to read the important parts. Implement Cary's approach to locate the
 queries
  where you will get the most bang for the buck. Then use books like Guy
  Harrison's (Ryan's suggestion) for pointers on making those queries
 perform
  better.

  Dennis Williams
  DBA
  Lifetouch, Inc.
  [EMAIL PROTECTED] 

  -Original Message-
  Sent: Wednesday, October 29, 2003 8:24 AM
  To: Multiple recipients of list ORACLE-L

  Hi there,

  Does any body have a template for proactive and reactive query
  tuning which can be used as a guideline/report while tuning simple, medium
  complex and complex SQL queries and PL/SQL stored procedures?  If so, can
  you please forward the same to me please?  If not, can anybody suggest as
 to
  how to go about doing one?  Any help in this regard is very much
  appreciated.

  Thanks and Regards,

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

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

Re: rewriting query without using UNION

from basic set theory:

union = OR
intersect = AND

 select e.id, e.name, d.deptname from emp e, dept d
 where e.deptno=d.deptno and e.name='JOSE'
or d.deptno = 50;
 


 
 From: Linda Wang [EMAIL PROTECTED]
 Date: 2003/10/29 Wed PM 12:54:26 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: rewriting query without using UNION
 
 Hi,
 I wonder if there's a better way of writing the query below. Basically, I 
 would like to return employee records where employee name='JOSE' + all 
 employees in deptno=50. My query can have multiple 'OR' criterias where the 
 next criteria maybe returning all employees with salary6 in addition to 
 the above two criterias. Building the query with multiple UNIONs will 
 definitely degrade the query performance. Is there a better way of rewriting 
 the query?
 
 Thanks!
 
 linda
 
 select e.id, e.name, d.deptname from emp e, dept d
 where e.deptno=d.deptno and e.name='JOSE'
 union
 select e.id, e.name, d.deptname from emp e, dept d
 where e.deptno=d.deptno and d.deptno=50;
 
 _
 Want to check if your PC is virus-infected?  Get a FREE computer virus scan 
 online from McAfee.
 http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Linda Wang
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (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: rewriting query without using UNION

2003-10-29 Thread Rudy Zung

select e.ID, e.NAME, d.DEPTNAME
   from EMP  e,
DEPT d
   where e.DEPTNO = d.DEPTNO and
 (e.NAME   = 'JOSE' or
  d.DEPTNO = 50);


-Original Message-
Sent: Wednesday, October 29, 2003 12:54 PM
To: Multiple recipients of list ORACLE-L


Hi,
I wonder if there's a better way of writing the query below. Basically, I 
would like to return employee records where employee name='JOSE' + all 
employees in deptno=50. My query can have multiple 'OR' criterias where the 
next criteria maybe returning all employees with salary6 in addition to

the above two criterias. Building the query with multiple UNIONs will 
definitely degrade the query performance. Is there a better way of rewriting

the query?

Thanks!

linda

select e.id, e.name, d.deptname from emp e, dept d
where e.deptno=d.deptno and e.name='JOSE'
union
select e.id, e.name, d.deptname from emp e, dept d
where e.deptno=d.deptno and d.deptno=50;

_
Want to check if your PC is virus-infected?  Get a FREE computer virus scan 
online from McAfee.
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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

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

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

2003-10-29 Thread John Spencer

Title: RE: RE: Perm job opening in MA





Ryan,
Thank you for that accurate explanation as to what excellent phone skills are. I just got to my email and have not had a chance to respond. I appreciate your assistance.

Regards,
John


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
Sent: Wednesday, October 29, 2003 12:30 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: RE: Perm job opening in MA


it means you need to be able to handle stupid questions without losing your temper. the best people to ask on this are the hardware support guys. They get the true 'gems', when it comes to stupid questions. They should provide training to oracle dba's on the topic. 

I was at a conference resently and I saw a video of a performance analyst helping a client. The performance analyst grabbed the keyboard and beat the client senseless. 

not exactly the kind of solution they are looking for... 


it also means you know how to speak english and explain complex material to lay people. Its kind of like going to the doctor because you have a head ache and the doctor recites somethingy ou might hear on ER. Not real helpful to a lay person. 

 
 From: Igor Neyman [EMAIL PROTECTED]
 Date: 2003/10/29 Wed AM 11:24:28 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: Perm job opening in MA
 
 LOL!
 Mladen, I think you are missed on off-topic list -:)
 
 Igor Neyman, OCP DBA
 [EMAIL PROTECTED]
 
 
 
 -Original Message-
 Mladen Gogala
 Sent: Tuesday, October 28, 2003 8:54 PM
 To: Multiple recipients of list ORACLE-L
 
 Don't get me wrong, I've recently changed positions and am not
 interested,
 but what are phone skills? I know how to use a phone, and I can do it
 in 
 yoga position with one hand tied behind my back. I've never used a phone
 under 
 water or in space. I use it on a regular basis while commuting or in 
 restaurants. It helps tremendously with finding a free seat. As for the
 
 communication over the phone, you should hear my inventive use of the
 English 
 language when I'm talking to telemarketers. Creative assumptions about
 their 
 ancestry and its position on the evolution tree and sexual preferences
 of their parents are the most common opener after which I usually take
 the 
 poor soul to the place where no telemarketer has gone before. Do I have
 the 
 right idea about the phone skills or you have in mind some extremely 
 innovative use of phone which would be inappropriate for a good catholic
 like 
 me?
 
 On 2003.10.28 20:09, John Spencer wrote:
  I hope I am not breaching any rules, but I would like to make it
 public that
  I am currently trying to fill a temp to perm position for a Sr level
  Oracle/customer support person in Massachusetts. This person must have
  strong Oracle and Sun Solaris skills and some Java (J2EE and Java
 beans)
  experience. Must have excellent phone skills and the ability to work
 with
  customers on installs and other issues. Experience must include stored
  procedures and triggers.
  
  Local candidates only please. Please reply directly to me at
  [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
  
  Thanks again for your time.
  
  Regards,
  John Spencer
  Sr. Staffing Consultant
  ProStart Inc.
  603-893-7772 ext 45
  603-893-7704 fax
  mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
  
  
 
 
 -- 
 Mladen Gogala
 Oracle DBA
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Mladen Gogala
 INET: [EMAIL PROTECTED]
 
 Fat City Network Services -- 858-538-5051 http://www.fatcity.com
 San Diego, California -- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from). You may
 also send the HELP command for other information (like subscribing).
 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Igor Neyman
 INET: [EMAIL PROTECTED]
 
 Fat City Network Services -- 858-538-5051 http://www.fatcity.com
 San Diego, California -- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from). You may
 also send the HELP command for other information (like subscribing).
 


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

2G trace files

I'm tracing a session with 10046 event level 8.  Here's the method I
use:

sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE );
sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,
  'timed_statistics', true);
/* Max dump file size is 2G */
sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
  'max_dump_file_size', 2147483647);
sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');
 
If I set max_dump_file_size greater than 2G I get an error.  But with
trace level 8, I'm easily overrunning this limit.  How do you guys get
around this?

BTW - Just got Optimizing Oracle Performance last night and if I didn't
have to work so much I would have read it through by now.  Maybe I'll
take tomorrow off.

Richard Quintin, DBA 
Information Systems  Computing, DBMS 
Virginia Tech 
-- 
When the character of a man is not clear to you, look at his friends.
-- Japanese Proverb
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Quintin, Richard
  INET: [EMAIL PROTECTED]

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

2003-10-29 Thread Jesse, Rich

The biggest problem I have with Cary's (and Jeff's!) book is patience.
There's constant mention of which I'll show you in Chapter 6 and so forth
and it pains me to keep on reading from where I am so's I don't get too
distracted and forget the important concepts I was just reading.  :)  I also
have so many sticky tabs in the first three chapters that I can't see my
bookmark anymore.

But the upside is it puts my 3-week old to sleep as I read it to her...  :D

Rich

Rich Jesse   System/Database Administrator 
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA 

-Original Message-
Sent: Wednesday, October 29, 2003 12:15 PM
To: Multiple recipients of list ORACLE-L



Neither Tom Kyte's nor Cary Millsap's books are too advanced for beginners. 

In the case of Tom's book, they may have to ponder things a bit, and
actually try 
the code for themselves to get a good understanding.  So what?  That's how 
you learn. 

Regarding Cary's book, what's so hard about it?  Beginner's could do much 
worse by reading some of the claptrap pablum that is available, but that
will 
just make it harder for them to understand the good stuff when they see it. 

Also, re the comments about the math in Cary's book:  What's the big deal? 
So what if you don't understand the math?  There isn't really that much of
it, 
and you can undestand the material even if you can't follow the proof. 

Jared 

Oracle - steroids for your brain. 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

Re: ** database configuration assistant scripts

2003-10-29 Thread Chip

When creating a custom database, DBCA has an option to save the database 
creation scripts.

Have Fun :)

A Joshi wrote:

Hi,
  I am using database configuration assistant to create a NT database. 
Is there a way I can get the scripts used in the creation? I clicked 
on the template but do not know where the template goes. Thank you

Do you Yahoo!?
Exclusive Video Premiere - Britney Spears 
http://launch.yahoo.com/video/?1093432fs=1redirectURL=http://launch.yahoo.com/promos/britneyspears/ 




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

RE: 2G trace files

2003-10-29 Thread Jamadagni, Rajendra

set mdfs=unlimited 

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: Wednesday, October 29, 2003 1:49 PM
To: Multiple recipients of list ORACLE-L


I'm tracing a session with 10046 event level 8.  Here's the method I
use:

sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE );
sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,
  'timed_statistics', true);
/* Max dump file size is 2G */
sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
  'max_dump_file_size', 2147483647);
sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');
 
If I set max_dump_file_size greater than 2G I get an error.  But with
trace level 8, I'm easily overrunning this limit.  How do you guys get
around this?

BTW - Just got Optimizing Oracle Performance last night and if I didn't
have to work so much I would have read it through by now.  Maybe I'll
take tomorrow off.

Richard Quintin, DBA 
Information Systems  Computing, DBMS 
Virginia Tech 


**
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.
**5
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: [EMAIL PROTECTED]

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

Re: 2G trace files


Sounds like an OS limit.

What OS is this on?







Quintin, Richard [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/29/2003 10:49 AM
Please respond to ORACLE-L


To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:2G trace files


I'm tracing a session with 10046 event level 8. Here's the method I
use:

  sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE );
  sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,
   'timed_statistics', true);
  /* Max dump file size is 2G */
  sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
   'max_dump_file_size', 2147483647);
  sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');
 
If I set max_dump_file_size greater than 2G I get an error. But with
trace level 8, I'm easily overrunning this limit. How do you guys get
around this?

BTW - Just got Optimizing Oracle Performance last night and if I didn't
have to work so much I would have read it through by now. Maybe I'll
take tomorrow off.

Richard Quintin, DBA 
Information Systems  Computing, DBMS 
Virginia Tech 
-- 
When the character of a man is not clear to you, look at his friends.
-- Japanese Proverb
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Quintin, Richard
 INET: [EMAIL PROTECTED]

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

2003-10-29 Thread Rachel Carmichael

As an author, let me just add that it pains US to have to say which
I'll show you in ... 

Books are laid out in some sort of order although readers don't always
read from chapter 1 straight through to the end. If we were to go off
on every tangent so as not to say see such and thus later, a reader
who was sampling by looking things up in the index or table of
contents, would be totally lost.

Plus the thread of the lesson would get hopelessly tangled. 

It's a fine line between explaining everything right now and saying
later. 

--- Jesse, Rich [EMAIL PROTECTED] wrote:
 The biggest problem I have with Cary's (and Jeff's!) book is
 patience.
 There's constant mention of which I'll show you in Chapter 6 and so
 forth
 and it pains me to keep on reading from where I am so's I don't get
 too
 distracted and forget the important concepts I was just reading.  :) 
 I also
 have so many sticky tabs in the first three chapters that I can't see
 my
 bookmark anymore.
 
 But the upside is it puts my 3-week old to sleep as I read it to
 her...  :D
 
 Rich
 
 Rich Jesse   System/Database Administrator 
 [EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA 
 
 -Original Message-
 Sent: Wednesday, October 29, 2003 12:15 PM
 To: Multiple recipients of list ORACLE-L
 
 
 
 Neither Tom Kyte's nor Cary Millsap's books are too advanced for
 beginners. 
 
 In the case of Tom's book, they may have to ponder things a bit, and
 actually try 
 the code for themselves to get a good understanding.  So what? 
 That's how 
 you learn. 
 
 Regarding Cary's book, what's so hard about it?  Beginner's could do
 much 
 worse by reading some of the claptrap pablum that is available, but
 that
 will 
 just make it harder for them to understand the good stuff when they
 see it. 
 
 Also, re the comments about the math in Cary's book:  What's the big
 deal? 
 So what if you don't understand the math?  There isn't really that
 much of
 it, 
 and you can undestand the material even if you can't follow the
 proof. 
 
 Jared 
 
 Oracle - steroids for your brain. 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Jesse, Rich
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

Re[3]: SQL and PL/SQL tuning template document required urgently

2003-10-29 Thread Jonathan Gennick

Wednesday, October 29, 2003, 12:59:34 PM, you wrote:
DWSams Publishing puts a User Level rating on their books. Maybe we
DW should ask O'Reilly to do the same. How about that Jonathan?

I don't know that we've ever thought of doing that, and I
think the practice would be frought with problems. People
aren't so easily pigeonholed. Just in terms of beginner, I
can think of:

* New to Oracle, experienced with other databases
* New to the task of tuning SQL, but an expert tuner at the
operating/system level
* New to databases and to tuning, but a quick-learner with a
solid grounding in computer science
* Clueless

We could put a user-rating on a book, but there's just no
way to account for all the variables such as those I've just
listed. Tags such as beginner, intermediate, and so
forth are over-simplifications. Actually, such tags are
aggregates that hide detailgrin. Better, I think, to just
describe a book as accurately as possible and let readers
make up their own minds.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]

Join the Oracle-article list and receive one
article on Oracle technologies per month by 
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, 
or send email to [EMAIL PROTECTED] and 
include the word subscribe in either the subject or body.

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

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

for a fair contracting rate, I could offer training? since Im apparently a renowned 
expert on the subject. 

 From: John Spencer [EMAIL PROTECTED]
 Date: 2003/10/29 Wed PM 01:44:33 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: RE: Perm job opening in MA

 Ryan,
 Thank you for that accurate explanation as to what excellent phone skills
 are. I just got to my email and have not had a chance to respond. I
 appreciate your assistance.

 Regards,
 John

 -Original Message-
 Sent: Wednesday, October 29, 2003 12:30 PM
 To: Multiple recipients of list ORACLE-L

 it means you need to be able to handle stupid questions without losing your
 temper. the best people to ask on this are the hardware support guys. They
 get the true 'gems', when it comes to stupid questions. They should provide
 training to oracle dba's on the topic. 

 I was at a conference resently and I saw a video of a performance analyst
 helping a client. The performance analyst grabbed the keyboard and beat the
 client senseless. 
 not exactly the kind of solution they are looking for... 

 it also means you know how to speak english and explain complex material to
 lay people. Its kind of like going to the doctor because you have a head
 ache and the doctor recites somethingy ou might hear on ER. Not real helpful
 to a lay person. 

  From: Igor Neyman [EMAIL PROTECTED]
  Date: 2003/10/29 Wed AM 11:24:28 EST
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Subject: RE: Perm job opening in MA

  LOL!
  Mladen, I think you are missed on off-topic list -:)

  Igor Neyman, OCP DBA
  [EMAIL PROTECTED]

  -Original Message-
  Mladen Gogala
  Sent: Tuesday, October 28, 2003 8:54 PM
  To: Multiple recipients of list ORACLE-L

  Don't get me wrong, I've recently changed positions and am not
  interested,
  but what are phone skills? I know how to use a phone, and I can do it
  in  
  yoga position with one hand tied behind my back. I've never used a phone
  under  
  water or in space. I use it on a regular basis while commuting or in  
  restaurants. It helps tremendously with finding a free seat. As for the

  communication over the phone, you should hear my inventive use of the
  English  
  language when I'm talking to telemarketers. Creative assumptions about
  their  
  ancestry and its position on the evolution tree and sexual preferences
  of their parents are the most common opener after which  I usually take
  the  
  poor soul to the place where no telemarketer has gone before. Do I have
  the  
  right idea about the phone skills or you have in mind some extremely  
  innovative use of phone which would be inappropriate for a good catholic
  like  
  me?

  On 2003.10.28 20:09, John Spencer wrote:
   I hope I am not breaching any rules, but I would like to make it
  public that
   I am currently trying to fill a temp to perm position for a Sr level
   Oracle/customer support person in Massachusetts. This person must have
   strong Oracle and Sun Solaris skills and some Java (J2EE and Java
  beans)
   experience. Must have excellent phone skills and the ability to work
  with
   customers on installs and other issues. Experience must include stored
   procedures and triggers.

   Local candidates only please. Please reply directly to me at
   [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]

   Thanks again for your time.

   Regards,
   John Spencer
   Sr. Staffing Consultant
   ProStart Inc.
   603-893-7772 ext 45
   603-893-7704 fax
   mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]

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

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

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

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

Re: RE: SQL and PL/SQL tuning template document required urgently

jared you have been doing this for what 10-15 years now? take someone with little to 
know background in this field and little to no technical experience. 

tom kytes book is rough and dense from their perspective. soemtimes people forget how 
far removed they are from the true beginner. There is a big difference between what is 
easy for you and what is easy for a beginner. 

I think that is why alot of senior people get frustrated when training newbies. They 
really dont know anything and it REALLY is that hard for them. 

 
 From: [EMAIL PROTECTED]
 Date: 2003/10/29 Wed PM 01:14:37 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Re: RE: SQL and PL/SQL tuning template document required urgently
 
 Neither Tom Kyte's nor Cary Millsap's books are too advanced for 
 beginners.
 
 In the case of Tom's book, they may have to ponder things a bit, and 
 actually try
 the code for themselves to get a good understanding.  So what?  That's how
 you learn.
 
 Regarding Cary's book, what's so hard about it?  Beginner's could do much
 worse by reading some of the claptrap pablum that is available, but that 
 will
 just make it harder for them to understand the good stuff when they see 
 it.
 
 Also, re the comments about the math in Cary's book:  What's the big deal?
 So what if you don't understand the math?  There isn't really that much of 
 it,
 and you can undestand the material even if you can't follow the proof.
 
 Jared
 
 Oracle - steroids for your brain.
 
 
 
 
 
 
 [EMAIL PROTECTED]
 Sent by: [EMAIL PROTECTED]
  10/29/2003 09:24 AM
  Please respond to ORACLE-L
 
  
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 cc: 
 Subject:Re: RE: SQL and PL/SQL tuning template document required 
 urgently
 
 
 the original poster sent me an email stating that he doesnt want a book or 
 documentation, he just wants some sort of short cut template. i replied 
 that if one exists its garbage. He didnt respond. 
 
 A side note, it seems to be in vogue to recommend advanced books like 
 carrie millsap's and tom kytes to beginners. This isnt appropriate. 
 Beginners should start with beginning material and work their way up. 
 Starting with advanced material will just confuse them. 
 
 
  
  From: DENNIS WILLIAMS [EMAIL PROTECTED]
  Date: 2003/10/29 Wed AM 10:49:25 EST
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Subject: RE: SQL and PL/SQL tuning template document required urgently
  
  Ranganath
 Since you mentioned proactive and reactive query tuning, I think the
  philosophy with which one approaches the tuning exercise means 
 everything.
  Wrong philosophy and you spend your time spinning your wheels. All of us
  have only a limited amount of time to devote, so the best approach will 
 make
  the best use of that time. 
 Get Optimizing Oracle Performance by Cary Millsap. It doesn't take 
 long
  to read the important parts. Implement Cary's approach to locate the 
 queries
  where you will get the most bang for the buck. Then use books like Guy
  Harrison's (Ryan's suggestion) for pointers on making those queries 
 perform
  better.
  
  Dennis Williams
  DBA
  Lifetouch, Inc.
  [EMAIL PROTECTED] 
  
  -Original Message-
  Sent: Wednesday, October 29, 2003 8:24 AM
  To: Multiple recipients of list ORACLE-L
  
  
  Hi there,
  
 Does any body have a template for proactive and reactive 
 query
  tuning which can be used as a guideline/report while tuning simple, 
 medium
  complex and complex SQL queries and PL/SQL stored procedures?  If so, 
 can
  you please forward the same to me please?  If not, can anybody suggest 
 as to
  how to go about doing one?  Any help in this regard is very much
  appreciated.
  
  Thanks and Regards,
  
  Ranganath
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: Ranganath K
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (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

RE: 2G trace files

2003-10-29 Thread Tim Fleury

Set the dump file size to unlimited.

-Original Message-
Sent: Wednesday, October 29, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L


I'm tracing a session with 10046 event level 8.  Here's the method I
use:

sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE );
sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,
  'timed_statistics', true);
/* Max dump file size is 2G */
sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
  'max_dump_file_size', 2147483647);
sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');
 
If I set max_dump_file_size greater than 2G I get an error.  But with
trace level 8, I'm easily overrunning this limit.  How do you guys get
around this?

BTW - Just got Optimizing Oracle Performance last night and if I didn't
have to work so much I would have read it through by now.  Maybe I'll
take tomorrow off.

Richard Quintin, DBA 
Information Systems  Computing, DBMS 
Virginia Tech 
-- 
When the character of a man is not clear to you, look at his friends.
-- Japanese Proverb
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Quintin, Richard
  INET: [EMAIL PROTECTED]

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

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

ORA-4031 error help.

2003-10-29 Thread Avnish.Rastogi

Hello List, Need some help in resolving ORA-4031 error message. We are using Lawson 
and for last few days users are getting ORA-4031 error 2-3 times a day in LAWSON log 
files but there is no error message in alert log file or any trace file. Both shared 
pool and large pool is set to 1GB. Below is the current init.ora file. We are on 
Oracle 9202 and AIX 5.1, using MTS.

Thanks




# Miscellaneous
COMPATIBLE=9.2.0
DB_NAME=LAWSON
DB_FILES=1500
GLOBAL_NAMES=TRUE
DB_BLOCK_SIZE=8192
DB_CACHE_SIZE=1792M
DB_KEEP_CACHE_SIZE=16M
LARGE_POOL_SIZE=1024M
SHARED_POOL_SIZE=1024M
SGA_MAX_SIZE = 5G
DB_FILE_MULTIBLOCK_READ_COUNT=8
CONTROL_FILE_RECORD_KEEP_TIME=45
CURSOR_SHARING=SIMILAR
OPEN_CURSORS=750 # From Lawson--Raised from 500 to 750 10/24/03
BACKGROUND_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/bdump
CORE_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/cdump
USER_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/udump
TIMED_STATISTICS=TRUE
CONTROL_FILES=(/appl/lawdb/oracle/data/db01/LAWSON/contrl_LAWSON_01.ctl,
   /appl/lawdb/oracle/data/db02/LAWSON/contrl_LAWSON_02.ctl,
   /appl/lawdb/oracle/data/db03/LAWSON/contrl_LAWSON_03.ctl,
   /appl/lawdb/oracle/data/db04/LAWSON/contrl_LAWSON_04.ctl,
   /appl/lawdb/oracle/data/db05/LAWSON/contrl_LAWSON_05.ctl)

# Archive
LOG_ARCHIVE_DEST=/appl/lawdb/oracle/archive_logs/LAWSON/
LOG_ARCHIVE_DUPLEX_DEST=/appl/lawdb/oracle/archive_logs_2/LAWSON/
LOG_ARCHIVE_FORMAT=ARC_LAWSON_%S.%T
LOG_ARCHIVE_START=TRUE
# LOG_ARCHIVE_TRACE = 1

# Distributed, Replication and Snapshot
DB_DOMAIN=PHSOR.ORG

# Pools
JAVA_POOL_SIZE=0

# Processes and Sessions
# PROCESSES=800 Increased value per vendor JMK 6/09/03
PROCESSES=1000
SESSIONS=1140
ENQUEUE_RESOURCES=8000
TRANSACTION_AUDITING=FALSE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAST_START_MTTR_TARGET=1200
SORT_AREA_SIZE=0
HASH_AREA_SIZE=0
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=undo
UNDO_RETENTION = 10800
PGA_AGGREGATE_TARGET=1G
WORKAREA_SIZE_POLICY = AUTO
JOB_QUEUE_PROCESSES = 10
LOG_BUFFER = 8192000# To reduce 'log file parallel write' wait event in 
v$system_event
CURSOR_SPACE_FOR_TIME   = TRUE
SERVICE_NAMES=lawson_ax3202a
LOCAL_LISTENER=lawson_ax3202a
# Network Registration
INSTANCE_NAME=LAWSON
DISK_ASYNCH_IO = FALSE
BACKUP_TAPE_IO_SLAVES=TRUE
PARALLEL_THREADS_PER_CPU = 6
PARALLEL_MAX_SERVERS = 6
PARALLEL_MIN_SERVERS = 1
DISPATCHERS=(ADDRESS=(PROTOCOL=TCP)(HOST=provicon)(PORT=5000))(DISPATCHERS=1)
MAX_DISPATCHERS = 3
SHARED_SERVERS = 10
MAX_SHARED_SERVERS = 50


DISCLAIMER:
This message is intended for the sole use of the individual to whom it is addressed, 
and may contain information that is privileged, confidential and exempt from 
disclosure under applicable law. If you are not the addressee you are hereby notified 
that you may not use, copy, disclose, or distribute to anyone the message or any 
information contained in the message. If you have received this message in error, 
please immediately advise the sender by reply email and delete this message.
-- 
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: 2G trace files

2003-10-29 Thread Paul Drake

Richard,

Are you sure that you are targeting your diagnostic efforts appropriately?

If all users are using dedicated servers, then each trace file should only have the info for one session. If your interval for a statspack report is an entire week, its going to be pretty tough to find the particular query that you're looking for. Are you tracing all sessions, all the time?

The feds don't put up routine checkpoints on federal interstates just to check for expired registration. It would bottleneck the entire system and generate too much info to be processed effectively.

From the Heisenburg angle, I would think that generating such a large amount of trace would clearly be impacting the server's I/O subsystem in a big way.

Pd
"Quintin, Richard" [EMAIL PROTECTED] wrote:
I'm tracing a session with 10046 event level 8. Here's the method Iuse:sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE );sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,'timed_statistics', true);/* Max dump file size is 2G */sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,'max_dump_file_size', 2147483647);sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');If I set max_dump_file_size greater than 2G I get an error. But withtrace level 8, I'm easily overrunning this limit. How do you guys getaround this?BTW - Just got Optimizing Oracle Performance last night and if I didn'thave to work so much I would have read it through by now. Maybe I'lltake tomorrow off.Richard Quintin, DBA Information Systems  Computing, DBMS Virginia Tech -- "Whe!
n the
 character of a man is not clear to you, look at his friends."-- Japanese Proverb-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Quintin, RichardINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears

Re: 9I RAC corporate standard.

 I tried installing RHAS 2.1 ($60 duhveloper edition), but it's so old
(based
 on RH7.2) that it couldn't identify our newish hardware (Intel D845 MB).

Just for the record, Redhat Enterprise Linux 3 is available now.

Tanel.


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

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

In case there is doubt, I was just kidding. Please, don't make me feel guilty.
I picked on the excellent phone skills because phone is such a ubiquitous device 
that everybody knows how to use it. Actually, in my 42 years of walking through this
valley of tears, I cannot recollect ever meeting someone who doesn't know how to 
use a phone. Asking for excellent phone skills is like asking for a good driver.
Have you ever actually met anyone who claims not to be an expert driver? Most of
us have an attitude that the roads are full of goofballs (now I am putting this 
mildly) who are preventing us from driving the way it should be done. It's the same
with excellent phone skills. What do you think that the reply would be if you asked 
someone whether she or he has a good phone skills? BTW, when I was talking about 
creative use of English language, I wasn't kidding. The word that I particularly like
is ignoranus, which means ignorant a**hole.

On 10/29/2003 02:19:52 PM, [EMAIL PROTECTED] wrote:
 for a fair contracting rate, I could offer training? since Im apparently a renowned 
 expert on the subject. 
  
  From: John Spencer [EMAIL PROTECTED]
  Date: 2003/10/29 Wed PM 01:44:33 EST
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Subject: RE: RE: Perm job opening in MA
  
  Ryan,
  Thank you for that accurate explanation as to what excellent phone skills
  are. I just got to my email and have not had a chance to respond. I
  appreciate your assistance.
  
  Regards,
  John
  
  -Original Message-
  Sent: Wednesday, October 29, 2003 12:30 PM
  To: Multiple recipients of list ORACLE-L
  
  it means you need to be able to handle stupid questions without losing your
  temper. the best people to ask on this are the hardware support guys. They
  get the true 'gems', when it comes to stupid questions. They should provide
  training to oracle dba's on the topic. 
  
  I was at a conference resently and I saw a video of a performance analyst
  helping a client. The performance analyst grabbed the keyboard and beat the
  client senseless. 
  not exactly the kind of solution they are looking for... 
  
  it also means you know how to speak english and explain complex material to
  lay people. Its kind of like going to the doctor because you have a head
  ache and the doctor recites somethingy ou might hear on ER. Not real helpful
  to a lay person. 
   
   From: Igor Neyman [EMAIL PROTECTED]
   Date: 2003/10/29 Wed AM 11:24:28 EST
   To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
   Subject: RE: Perm job opening in MA
   
   LOL!
   Mladen, I think you are missed on off-topic list -:)
   
   Igor Neyman, OCP DBA
   [EMAIL PROTECTED]
   
   
   
   -Original Message-
   Mladen Gogala
   Sent: Tuesday, October 28, 2003 8:54 PM
   To: Multiple recipients of list ORACLE-L
   
   Don't get me wrong, I've recently changed positions and am not
   interested,
   but what are phone skills? I know how to use a phone, and I can do it
   in  
   yoga position with one hand tied behind my back. I've never used a phone
   under  
   water or in space. I use it on a regular basis while commuting or in  
   restaurants. It helps tremendously with finding a free seat. As for the
   
   communication over the phone, you should hear my inventive use of the
   English  
   language when I'm talking to telemarketers. Creative assumptions about
   their  
   ancestry and its position on the evolution tree and sexual preferences
   of their parents are the most common opener after which  I usually take
   the  
   poor soul to the place where no telemarketer has gone before. Do I have
   the  
   right idea about the phone skills or you have in mind some extremely  
   innovative use of phone which would be inappropriate for a good catholic
   like  
   me?
   
   On 2003.10.28 20:09, John Spencer wrote:
I hope I am not breaching any rules, but I would like to make it
   public that
I am currently trying to fill a temp to perm position for a Sr level
Oracle/customer support person in Massachusetts. This person must have
strong Oracle and Sun Solaris skills and some Java (J2EE and Java
   beans)
experience. Must have excellent phone skills and the ability to work
   with
customers on installs and other issues. Experience must include stored
procedures and triggers.

Local candidates only please. Please reply directly to me at
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]

Thanks again for your time.

Regards,
John Spencer
Sr. Staffing Consultant
ProStart Inc.
603-893-7772 ext 45
603-893-7704 fax
mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]


   
   
   -- 
   Mladen Gogala
   Oracle DBA
   -- 
   Please see the official ORACLE-L FAQ: http://www.orafaq.net
   -- 
   Author: Mladen Gogala
 INET: [EMAIL PROTECTED]
   
   Fat City Network Services-- 858-538-5051 http://www.fatcity.com
   San Diego, California

Re: ORA-4031 error help.

2003-10-29 Thread Jeremiah Wilton

Well, you neet to check the full error, because otherwise there's no
way to tell if you are running low on shared or large pool.

The view that shows space usage in both places in v$sgastat.  I
suggest you start looking there.  Maybe your third-party application
doesn't use bind variables and is bloating the shared pool.  You could
verify this by observing that the sqlarea component of the shared pool
is very large as seen in v$sgastat. If this is the case then you might
consider testing with cursor_sharing=force.

You could also count different versions of similar SQL from the
application by grouping sql_text in v$sqlarea by the first 30
characters or so.  This assumes your problem is shared pool sqlarea
bloat.  You could just be runnning out of space for MTS session heaps
in the large pool.  You have to look at v$sgastat first.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Wed, 29 Oct 2003, [EMAIL PROTECTED] wrote:

 Hello List, Need some help in resolving ORA-4031 error message. We
 are using Lawson and for last few days users are getting ORA-4031
 error 2-3 times a day in LAWSON log files but there is no error
 message in alert log file or any trace file. Both shared pool and
 large pool is set to 1GB. Below is the current init.ora file. We are
 on Oracle 9202 and AIX 5.1, using MTS.

 # Miscellaneous
 COMPATIBLE=9.2.0
 DB_NAME=LAWSON
 DB_FILES=1500
 GLOBAL_NAMES=TRUE
 DB_BLOCK_SIZE=8192
 DB_CACHE_SIZE=1792M
 DB_KEEP_CACHE_SIZE=16M
 LARGE_POOL_SIZE=1024M
 SHARED_POOL_SIZE=1024M
 SGA_MAX_SIZE = 5G
 DB_FILE_MULTIBLOCK_READ_COUNT=8
 CONTROL_FILE_RECORD_KEEP_TIME=45
 CURSOR_SHARING=SIMILAR
 OPEN_CURSORS=750 # From Lawson--Raised from 500 to 750 10/24/03
 BACKGROUND_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/bdump
 CORE_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/cdump
 USER_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/udump
 TIMED_STATISTICS=TRUE
 CONTROL_FILES=(/appl/lawdb/oracle/data/db01/LAWSON/contrl_LAWSON_01.ctl,
/appl/lawdb/oracle/data/db02/LAWSON/contrl_LAWSON_02.ctl,
/appl/lawdb/oracle/data/db03/LAWSON/contrl_LAWSON_03.ctl,
/appl/lawdb/oracle/data/db04/LAWSON/contrl_LAWSON_04.ctl,
/appl/lawdb/oracle/data/db05/LAWSON/contrl_LAWSON_05.ctl)

 # Archive
 LOG_ARCHIVE_DEST=/appl/lawdb/oracle/archive_logs/LAWSON/
 LOG_ARCHIVE_DUPLEX_DEST=/appl/lawdb/oracle/archive_logs_2/LAWSON/
 LOG_ARCHIVE_FORMAT=ARC_LAWSON_%S.%T
 LOG_ARCHIVE_START=TRUE
 # LOG_ARCHIVE_TRACE = 1

 # Distributed, Replication and Snapshot
 DB_DOMAIN=PHSOR.ORG

 # Pools
 JAVA_POOL_SIZE=0

 # Processes and Sessions
 # PROCESSES=800 Increased value per vendor JMK 6/09/03
 PROCESSES=1000
 SESSIONS=1140
 ENQUEUE_RESOURCES=8000
 TRANSACTION_AUDITING=FALSE
 REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
 FAST_START_MTTR_TARGET=1200
 SORT_AREA_SIZE=0
 HASH_AREA_SIZE=0
 UNDO_MANAGEMENT=AUTO
 UNDO_TABLESPACE=undo
 UNDO_RETENTION = 10800
 PGA_AGGREGATE_TARGET=1G
 WORKAREA_SIZE_POLICY = AUTO
 JOB_QUEUE_PROCESSES = 10
 LOG_BUFFER = 8192000# To reduce 'log file parallel write' wait event in 
 v$system_event
 CURSOR_SPACE_FOR_TIME   = TRUE
 SERVICE_NAMES=lawson_ax3202a
 LOCAL_LISTENER=lawson_ax3202a
 # Network Registration
 INSTANCE_NAME=LAWSON
 DISK_ASYNCH_IO = FALSE
 BACKUP_TAPE_IO_SLAVES=TRUE
 PARALLEL_THREADS_PER_CPU = 6
 PARALLEL_MAX_SERVERS = 6
 PARALLEL_MIN_SERVERS = 1
 DISPATCHERS=(ADDRESS=(PROTOCOL=TCP)(HOST=provicon)(PORT=5000))(DISPATCHERS=1)
 MAX_DISPATCHERS = 3
 SHARED_SERVERS = 10
 MAX_SHARED_SERVERS = 50

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

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

They really dont know anything and it REALLY is that hard for them.

Hence the lies on their resumes. ;)

OK, enought cynicism. For now.

Maybe it is hard. I guess I just don't agree that they shouldn't just dive in and sink or swim.

And yes, I've been doing this a while, but there are still things that I find difficult. Often this
is due to poorly written software and/or documentation, usually the latter, sometimes the
former, and on occasion both. In those cases I don't persist unless absolutely necessary.

Whether or not to tackle a tough subject head depends on how badly you need to, I guess.

eg. I once had OAS 4.0 working on Windows NT.

Jared

[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/29/2003 11:20 AM
Please respond to ORACLE-L

To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: RE: SQL and PL/SQL tuning template document required urgently

jared you have been doing this for what 10-15 years now? take someone with little to know background in this field and little to no technical experience.

tom kytes book is rough and dense from their perspective. soemtimes people forget how far removed they are from the true beginner. There is a big difference between what is easy for you and what is easy for a beginner.

I think that is why alot of senior people get frustrated when training newbies. They really dont know anything and it REALLY is that hard for them.

From: [EMAIL PROTECTED]
Date: 2003/10/29 Wed PM 01:14:37 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: RE: SQL and PL/SQL tuning template document required urgently

Neither Tom Kyte's nor Cary Millsap's books are too advanced for
beginners.

In the case of Tom's book, they may have to ponder things a bit, and
actually try
the code for themselves to get a good understanding. So what? That's how
you learn.

Regarding Cary's book, what's so hard about it? Beginner's could do much
worse by reading some of the claptrap pablum that is available, but that
will
just make it harder for them to understand the good stuff when they see
it.

Also, re the comments about the math in Cary's book: What's the big deal?
So what if you don't understand the math? There isn't really that much of
it,
and you can undestand the material even if you can't follow the proof.

Jared

Oracle - steroids for your brain.

[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/29/2003 09:24 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: RE: SQL and PL/SQL tuning template document required urgently

the original poster sent me an email stating that he doesnt want a book or
documentation, he just wants some sort of short cut template. i replied
that if one exists its garbage. He didnt respond.

A side note, it seems to be in vogue to recommend advanced books like
carrie millsap's and tom kytes to beginners. This isnt appropriate.
Beginners should start with beginning material and work their way up.
Starting with advanced material will just confuse them.

Ranganath
Since you mentioned proactive and reactive query tuning, I think the
philosophy with which one approaches the tuning exercise means
everything.
Wrong philosophy and you spend your time spinning your wheels. All of us
have only a limited amount of time to devote, so the best approach will
make
the best use of that time.
Get Optimizing Oracle Performance by Cary Millsap. It doesn't take
long
to read the important parts. Implement Cary's approach to locate the
queries
where you will get the most bang for the buck. Then use books like Guy
Harrison's (Ryan's suggestion) for pointers on making those queries
perform
better.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]

-Original Message-
Sent: Wednesday, October 29, 2003 8:24 AM
To: Multiple recipients of list ORACLE-L

Hi there,

Does any body have a template for proactive and reactive
query
tuning which can be used as a guideline/report while tuning simple,
medium
complex and complex SQL queries and PL/SQL stored procedures? If so,
can
you please forward the same to me please? If not, can anybody suggest
as to
how to go about doing one? Any help in this regard is very much
appreciated.

Thanks and Regards,

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

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

RE: 2G trace files

 If I set max_dump_file_size greater than 2G I get an error.

Perhaps I should be more specific:

sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
  'max_dump_file_size', 'unlimited');
gives:
ORA-06502: PL/SQL: numeric or value error: character to number
conversion error

sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
  'max_dump_file_size', 2147483647 + 1);
gives:
ORA-01426: numeric overflow


On Wed, 2003-10-29 at 14:24, Tim Fleury wrote:
 Set the dump file size to unlimited.
 
 -Original Message-
 Sent: Wednesday, October 29, 2003 10:49 AM
 To: Multiple recipients of list ORACLE-L
 
 
 I'm tracing a session with 10046 event level 8.  Here's the method I
 use:
 
 sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE );
 sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,
   'timed_statistics', true);
 /* Max dump file size is 2G */
 sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
   'max_dump_file_size', 2147483647);
 sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');
  
 If I set max_dump_file_size greater than 2G I get an error.  But with
 trace level 8, I'm easily overrunning this limit.  How do you guys get
 around this?
 
 BTW - Just got Optimizing Oracle Performance last night and if I didn't
 have to work so much I would have read it through by now.  Maybe I'll
 take tomorrow off.
 
 Richard Quintin, DBA 
 Information Systems  Computing, DBMS 
 Virginia Tech 
 -- 
 When the character of a man is not clear to you, look at his friends.
 -- Japanese Proverb
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Quintin, Richard
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
 message BODY, include a line containing: UNSUB ORACLE-L (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
Richard Quintin, DBA 
Information Systems  Computing, DBMS 
Virginia Tech 
-- 
[Long hair] is considered bohemian, which may be why I grew it, but I
keep it long because I love the way it feels, part cloak, part fan, part
mane, part security blanket. -- Marge Piercy
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Quintin, Richard
  INET: [EMAIL PROTECTED]

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

Re: 2G trace files

Yes.  I'm tracing a single session.  What made you think I wasn't?  What
does statspack have to do with this?  2G is not such a large amount...  

On Wed, 2003-10-29 at 14:44, Paul Drake wrote:
 Richard,
  
 Are you sure that you are targeting your diagnostic efforts
 appropriately?
  
 If all users are using dedicated servers, then each trace file should
 only have the info for one session. If your interval for a statspack
 report is an entire week, its going to be pretty tough to find the
 particular query that you're looking for. Are you tracing all
 sessions, all the time?
  
 The feds don't put up routine checkpoints on federal interstates just
 to check for expired registration. It would bottleneck the entire
 system and generate too much info to be processed effectively.
  
 From the Heisenburg angle, I would think that generating such a large
 amount of trace would clearly be impacting the server's I/O subsystem
 in a big way.
  
 Pd
 
 Quintin, Richard [EMAIL PROTECTED] wrote:
 I'm tracing a session with 10046 event level 8. Here's the
 method I
 use:
 
 sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#,
 TRUE );
 sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,
 'timed_statistics', true);
 /* Max dump file size is 2G */
 sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
 'max_dump_file_size', 2147483647);
 sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');
 
 If I set max_dump_file_size greater than 2G I get an error.
 But with
 trace level 8, I'm easily overrunning this limit. How do you
 guys get
 around this?
 
 BTW - Just got Optimizing Oracle Performance last night and if
 I didn't
 have to work so much I would have read it through by now.
 Maybe I'll
 take tomorrow off.
 
 Richard Quintin, DBA 
 Information Systems  Computing, DBMS 
 Virginia Tech 
 -- 
 Whe! n the character of a man is not clear to you, look at
 his friends.
 -- Japanese Proverb
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Quintin, Richard
 INET: [EMAIL PROTECTED]
 
 Fat City Network Services -- 858-538-5051
 http://www.fatcity.com
 San Diego, California -- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail
 message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
 and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (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!?
 Exclusive Video Premiere - Britney Spears
Richard Quintin, DBA 
Information Systems  Computing, DBMS 
Virginia Tech 
-- 
If you haven't forgiven yourself something, how can you forgive
others? -- Dolores Huerta
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Quintin, Richard
  INET: [EMAIL PROTECTED]

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

bitmap index not used

2003-10-29 Thread Josh Collier

Hi,

I have a puzzle.

A bitmap index on a varchar2(25) column. table has 7131413 rows, of which
7125290 are null for the column in question, the rest of the rows are unique
values. There is a histogram on this column, it has 2 buckets.

A select statement using this column in the where clause generates two
plans, one which uses the index and one which doesn't. The deciding factor
is the length of the string in the filter.

select * from table where microchip_number = 'avid1'
this produces a plan which uses the bitmap

select * from table where microchip_number = 'avid12' (and any additional
characters in the string ) 
this produces a plan which uses a full table scan.

I am stumped about how to figure out why the optimizer makes a choice based
upon the length of the string in the predicate.

Much obliged,

Josh

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

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

Re: dbms_system.ksdwrt

2003-10-29 Thread Pete Finnigan

Thanks very much for testing it Jared,

Much appreciated

Pete

In article [EMAIL PROTECTED]
disys.com, [EMAIL PROTECTED] writes
Yes, it works on 7.3.4, tested it just now.

Jared
-- 
Pete Finnigan
email:[EMAIL PROTECTED]
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

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

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

2003-10-29 Thread John Kanagaraj

Avnish,

4031 (as well as 0155 and 1652) are considered 'user' errors and will NOT be
logged in the alert.log by default. You could add the following into your
init.ora to capture them: (Make sure that you keep *all* event lines
together, including previous ones in the init file, otherwise only the last
set is considered):

event=1555 trace name errorstack level 3
event=4031 trace name errorstack level 3
event=1652 trace name processstate level 10

I also see that you are at 9202 and I do know that there are *lots* of
shared pool related errors below 9204. I would suggest an upgrade first...

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

God's word wrapped in great music - 24x7x365 at http://www.klove.com

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

-Original Message-
From: Jeremiah Wilton [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 29, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: ORA-4031 error help.


Well, you neet to check the full error, because otherwise there's no
way to tell if you are running low on shared or large pool.

The view that shows space usage in both places in v$sgastat.  I
suggest you start looking there.  Maybe your third-party application
doesn't use bind variables and is bloating the shared pool.  You could
verify this by observing that the sqlarea component of the shared pool
is very large as seen in v$sgastat. If this is the case then you might
consider testing with cursor_sharing=force.

You could also count different versions of similar SQL from the
application by grouping sql_text in v$sqlarea by the first 30
characters or so.  This assumes your problem is shared pool sqlarea
bloat.  You could just be runnning out of space for MTS session heaps
in the large pool.  You have to look at v$sgastat first.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Wed, 29 Oct 2003, [EMAIL PROTECTED] wrote:

 Hello List, Need some help in resolving ORA-4031 error message. We
 are using Lawson and for last few days users are getting ORA-4031
 error 2-3 times a day in LAWSON log files but there is no error
 message in alert log file or any trace file. Both shared pool and
 large pool is set to 1GB. Below is the current init.ora file. We are
 on Oracle 9202 and AIX 5.1, using MTS.

 # Miscellaneous
 COMPATIBLE=9.2.0
 DB_NAME=LAWSON
 DB_FILES=1500
 GLOBAL_NAMES=TRUE
 DB_BLOCK_SIZE=8192
 DB_CACHE_SIZE=1792M
 DB_KEEP_CACHE_SIZE=16M
 LARGE_POOL_SIZE=1024M
 SHARED_POOL_SIZE=1024M
 SGA_MAX_SIZE = 5G
 DB_FILE_MULTIBLOCK_READ_COUNT=8
 CONTROL_FILE_RECORD_KEEP_TIME=45
 CURSOR_SHARING=SIMILAR
 OPEN_CURSORS=750 # From Lawson--Raised from 500 to 750 10/24/03
 BACKGROUND_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/bdump
 CORE_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/cdump
 USER_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/udump
 TIMED_STATISTICS=TRUE
 
CONTROL_FILES=(/appl/lawdb/oracle/data/db01/LAWSON/contrl_LAWS
ON_01.ctl,

/appl/lawdb/oracle/data/db02/LAWSON/contrl_LAWSON_02.ctl,

/appl/lawdb/oracle/data/db03/LAWSON/contrl_LAWSON_03.ctl,

/appl/lawdb/oracle/data/db04/LAWSON/contrl_LAWSON_04.ctl,

/appl/lawdb/oracle/data/db05/LAWSON/contrl_LAWSON_05.ctl)

 # Archive
 LOG_ARCHIVE_DEST=/appl/lawdb/oracle/archive_logs/LAWSON/
 LOG_ARCHIVE_DUPLEX_DEST=/appl/lawdb/oracle/archive_logs_2/LAWSON/
 LOG_ARCHIVE_FORMAT=ARC_LAWSON_%S.%T
 LOG_ARCHIVE_START=TRUE
 # LOG_ARCHIVE_TRACE = 1

 # Distributed, Replication and Snapshot
 DB_DOMAIN=PHSOR.ORG

 # Pools
 JAVA_POOL_SIZE=0

 # Processes and Sessions
 # PROCESSES=800 Increased value per vendor JMK 6/09/03
 PROCESSES=1000
 SESSIONS=1140
 ENQUEUE_RESOURCES=8000
 TRANSACTION_AUDITING=FALSE
 REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
 FAST_START_MTTR_TARGET=1200
 SORT_AREA_SIZE=0
 HASH_AREA_SIZE=0
 UNDO_MANAGEMENT=AUTO
 UNDO_TABLESPACE=undo
 UNDO_RETENTION = 10800
 PGA_AGGREGATE_TARGET=1G
 WORKAREA_SIZE_POLICY = AUTO
 JOB_QUEUE_PROCESSES = 10
 LOG_BUFFER = 8192000# To reduce 'log file parallel 
write' wait event in v$system_event
 CURSOR_SPACE_FOR_TIME   = TRUE
 SERVICE_NAMES=lawson_ax3202a
 LOCAL_LISTENER=lawson_ax3202a
 # Network Registration
 INSTANCE_NAME=LAWSON
 DISK_ASYNCH_IO = FALSE
 BACKUP_TAPE_IO_SLAVES=TRUE
 PARALLEL_THREADS_PER_CPU = 6
 PARALLEL_MAX_SERVERS = 6
 PARALLEL_MIN_SERVERS = 1
 
DISPATCHERS=(ADDRESS=(PROTOCOL=TCP)(HOST=provicon)(PORT=5000))
(DISPATCHERS=1)
 MAX_DISPATCHERS = 3
 SHARED_SERVERS = 10
 MAX_SHARED_SERVERS = 50

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

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

RE: 9I RAC corporate standard.

2003-10-29 Thread Jesse, Rich

H...interesting...

http://ftp.redhat.com/pub/redhat/linux/enterprise/3/en/os/i386/SRPMS/

Me and a coworker are pondering rolling our own RHASESLESELSESES3
package for RAC testing...


Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA


 -Original Message-
 From: Tanel Poder [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, October 29, 2003 1:25 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Re: 9I RAC corporate standard.
 
 
  I tried installing RHAS 2.1 ($60 duhveloper edition), but 
 it's so old
 (based
  on RH7.2) that it couldn't identify our newish hardware 
 (Intel D845 MB).
 
 Just for the record, Redhat Enterprise Linux 3 is available now.
 
 Tanel.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

Re: bitmap index not used

Which version of the database? Do you have any  special setup? Any optimizer parameters
on either session or system level?
On 10/29/2003 03:09:39 PM, Josh Collier wrote:
 Hi,
 
 I have a puzzle.
 
 A bitmap index on a varchar2(25) column. table has 7131413 rows, of which
 7125290 are null for the column in question, the rest of the rows are unique
 values. There is a histogram on this column, it has 2 buckets.
 
 A select statement using this column in the where clause generates two
 plans, one which uses the index and one which doesn't. The deciding factor
 is the length of the string in the filter.
 
 select * from table where microchip_number = 'avid1'
   this produces a plan which uses the bitmap
 
 select * from table where microchip_number = 'avid12' (and any additional
 characters in the string ) 
   this produces a plan which uses a full table scan.
 
 I am stumped about how to figure out why the optimizer makes a choice based
 upon the length of the string in the predicate.
 
 Much obliged,
 
 Josh
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Josh Collier
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

Mladen Gogala
Oracle DBA



Note:
This message is for the named person's use only.  It may contain confidential, 
proprietary or legally privileged information.  No confidentiality or privilege is 
waived or lost by any mistransmission.  If you receive this message in error, please 
immediately delete it and all copies of it from your system, destroy any hard copies 
of it and notify the sender.  You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to 
monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where 
the message states otherwise and the sender is authorized to state them to be the 
views of any such entity.

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

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

2003-10-29 Thread Avnish.Rastogi

Full error message is 

ORA-04031: unable to allocate 4032 bytes of shared memory (large pool,unknown 
object,session heap,frame segment))

I am already monitoing both shared pool and large pool free memory every 30 minutes 
and there is no issue with that. As I mentioned below Oracle is not displaying any 
error message or trace file.

-Original Message-
Sent: Wednesday, October 29, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L


Well, you neet to check the full error, because otherwise there's no
way to tell if you are running low on shared or large pool.

The view that shows space usage in both places in v$sgastat.  I
suggest you start looking there.  Maybe your third-party application
doesn't use bind variables and is bloating the shared pool.  You could
verify this by observing that the sqlarea component of the shared pool
is very large as seen in v$sgastat. If this is the case then you might
consider testing with cursor_sharing=force.

You could also count different versions of similar SQL from the
application by grouping sql_text in v$sqlarea by the first 30
characters or so.  This assumes your problem is shared pool sqlarea
bloat.  You could just be runnning out of space for MTS session heaps
in the large pool.  You have to look at v$sgastat first.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Wed, 29 Oct 2003, [EMAIL PROTECTED] wrote:

 Hello List, Need some help in resolving ORA-4031 error message. We
 are using Lawson and for last few days users are getting ORA-4031
 error 2-3 times a day in LAWSON log files but there is no error
 message in alert log file or any trace file. Both shared pool and
 large pool is set to 1GB. Below is the current init.ora file. We are
 on Oracle 9202 and AIX 5.1, using MTS.

 # Miscellaneous
 COMPATIBLE=9.2.0
 DB_NAME=LAWSON
 DB_FILES=1500
 GLOBAL_NAMES=TRUE
 DB_BLOCK_SIZE=8192
 DB_CACHE_SIZE=1792M
 DB_KEEP_CACHE_SIZE=16M
 LARGE_POOL_SIZE=1024M
 SHARED_POOL_SIZE=1024M
 SGA_MAX_SIZE = 5G
 DB_FILE_MULTIBLOCK_READ_COUNT=8
 CONTROL_FILE_RECORD_KEEP_TIME=45
 CURSOR_SHARING=SIMILAR
 OPEN_CURSORS=750 # From Lawson--Raised from 500 to 750 10/24/03
 BACKGROUND_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/bdump
 CORE_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/cdump
 USER_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/udump
 TIMED_STATISTICS=TRUE
 CONTROL_FILES=(/appl/lawdb/oracle/data/db01/LAWSON/contrl_LAWSON_01.ctl,
/appl/lawdb/oracle/data/db02/LAWSON/contrl_LAWSON_02.ctl,
/appl/lawdb/oracle/data/db03/LAWSON/contrl_LAWSON_03.ctl,
/appl/lawdb/oracle/data/db04/LAWSON/contrl_LAWSON_04.ctl,
/appl/lawdb/oracle/data/db05/LAWSON/contrl_LAWSON_05.ctl)

 # Archive
 LOG_ARCHIVE_DEST=/appl/lawdb/oracle/archive_logs/LAWSON/
 LOG_ARCHIVE_DUPLEX_DEST=/appl/lawdb/oracle/archive_logs_2/LAWSON/
 LOG_ARCHIVE_FORMAT=ARC_LAWSON_%S.%T
 LOG_ARCHIVE_START=TRUE
 # LOG_ARCHIVE_TRACE = 1

 # Distributed, Replication and Snapshot
 DB_DOMAIN=PHSOR.ORG

 # Pools
 JAVA_POOL_SIZE=0

 # Processes and Sessions
 # PROCESSES=800 Increased value per vendor JMK 6/09/03
 PROCESSES=1000
 SESSIONS=1140
 ENQUEUE_RESOURCES=8000
 TRANSACTION_AUDITING=FALSE
 REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
 FAST_START_MTTR_TARGET=1200
 SORT_AREA_SIZE=0
 HASH_AREA_SIZE=0
 UNDO_MANAGEMENT=AUTO
 UNDO_TABLESPACE=undo
 UNDO_RETENTION = 10800
 PGA_AGGREGATE_TARGET=1G
 WORKAREA_SIZE_POLICY = AUTO
 JOB_QUEUE_PROCESSES = 10
 LOG_BUFFER = 8192000# To reduce 'log file parallel write' wait event in 
 v$system_event
 CURSOR_SPACE_FOR_TIME   = TRUE
 SERVICE_NAMES=lawson_ax3202a
 LOCAL_LISTENER=lawson_ax3202a
 # Network Registration
 INSTANCE_NAME=LAWSON
 DISK_ASYNCH_IO = FALSE
 BACKUP_TAPE_IO_SLAVES=TRUE
 PARALLEL_THREADS_PER_CPU = 6
 PARALLEL_MAX_SERVERS = 6
 PARALLEL_MIN_SERVERS = 1
 DISPATCHERS=(ADDRESS=(PROTOCOL=TCP)(HOST=provicon)(PORT=5000))(DISPATCHERS=1)
 MAX_DISPATCHERS = 3
 SHARED_SERVERS = 10
 MAX_SHARED_SERVERS = 50

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

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


DISCLAIMER:
This message is intended for the sole use of the individual to whom it is addressed, 
and may contain information that is privileged, confidential and exempt from 
disclosure under applicable law. If you are not the addressee you are hereby notified 
that you may not use, copy, disclose, or distribute to anyone the message or any 
information contained in the message. If

RE: RE: SQL and PL/SQL tuning template document required urgently

2003-10-29 Thread Cary Millsap

The structure we chose for this thing was that we wanted a marginally
technical decision-maker to be able to read Parts I and III without
giving up. Therefore, any time there was an opportunity for a technical
tangent, the rule was explain it (later) in the reference section.

I figured Part II was too much to cache in one's head anyway, so I
didn't worry so much about making this part flow. I expected that
Parts I and III would be full-scanned by a wide audience, and that Part
II would be index range-scanned by a narrower audience. :)


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
- SQL Optimization 101: 12/8-12 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...


-Original Message-
Rachel Carmichael
Sent: Wednesday, October 29, 2003 1:19 PM
To: Multiple recipients of list ORACLE-L
urgently

As an author, let me just add that it pains US to have to say which
I'll show you in ... 

Books are laid out in some sort of order although readers don't always
read from chapter 1 straight through to the end. If we were to go off
on every tangent so as not to say see such and thus later, a reader
who was sampling by looking things up in the index or table of
contents, would be totally lost.

Plus the thread of the lesson would get hopelessly tangled. 

It's a fine line between explaining everything right now and saying
later. 

--- Jesse, Rich [EMAIL PROTECTED] wrote:
 The biggest problem I have with Cary's (and Jeff's!) book is
 patience.
 There's constant mention of which I'll show you in Chapter 6 and so
 forth
 and it pains me to keep on reading from where I am so's I don't get
 too
 distracted and forget the important concepts I was just reading.  :) 
 I also
 have so many sticky tabs in the first three chapters that I can't see
 my
 bookmark anymore.
 
 But the upside is it puts my 3-week old to sleep as I read it to
 her...  :D
 
 Rich
 
 Rich Jesse   System/Database Administrator 
 [EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA 
 
 -Original Message-
 Sent: Wednesday, October 29, 2003 12:15 PM
 To: Multiple recipients of list ORACLE-L
 
 
 
 Neither Tom Kyte's nor Cary Millsap's books are too advanced for
 beginners. 
 
 In the case of Tom's book, they may have to ponder things a bit, and
 actually try 
 the code for themselves to get a good understanding.  So what? 
 That's how 
 you learn. 
 
 Regarding Cary's book, what's so hard about it?  Beginner's could do
 much 
 worse by reading some of the claptrap pablum that is available, but
 that
 will 
 just make it harder for them to understand the good stuff when they
 see it. 
 
 Also, re the comments about the math in Cary's book:  What's the big
 deal? 
 So what if you don't understand the math?  There isn't really that
 much of
 it, 
 and you can undestand the material even if you can't follow the
 proof. 
 
 Jared 
 
 Oracle - steroids for your brain. 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Jesse, Rich
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/
-- 
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: Cary Millsap
  INET: [EMAIL PROTECTED]

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

Re: RE: SQL and PL/SQL tuning template document required urgently

not sure its about sink or swim. I think its more productive for them to start with 
basic stuff and get grounded in that first. Saves time. Saves frustration and they 
learn faster. 

 From: [EMAIL PROTECTED]
 Date: 2003/10/29 Wed PM 02:54:25 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Re: RE: SQL and PL/SQL tuning template document required urgently

  They really dont know anything and it REALLY is that hard for them. 

 Hence the lies on their resumes.  ;)

 OK, enought cynicism.  For now. 

 Maybe it is hard.  I guess I just don't agree that they shouldn't just 
 dive in and sink or swim.

 And yes, I've been doing this a while, but there are still things that I 
 find difficult.  Often this
 is due to poorly written software and/or documentation, usually the 
 latter, sometimes the
 former, and on occasion both.  In those cases I don't persist unless 
 absolutely necessary.

 Whether or not to tackle a tough subject head depends on how badly you 
 need to, I guess.

 eg.  I once had OAS 4.0 working on Windows NT. 

 Jared

 [EMAIL PROTECTED]
 Sent by: [EMAIL PROTECTED]
  10/29/2003 11:20 AM
  Please respond to ORACLE-L

 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 cc: 
 Subject:Re: RE: SQL and PL/SQL tuning template document required 
 urgently

 jared you have been doing this for what 10-15 years now? take someone with 
 little to know background in this field and little to no technical 
 experience. 

 tom kytes book is rough and dense from their perspective. soemtimes people 
 forget how far removed they are from the true beginner. There is a big 
 difference between what is easy for you and what is easy for a beginner. 

 I think that is why alot of senior people get frustrated when training 
 newbies. They really dont know anything and it REALLY is that hard for 
 them. 

  From: [EMAIL PROTECTED]
  Date: 2003/10/29 Wed PM 01:14:37 EST
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Subject: Re: RE: SQL and PL/SQL tuning template document required 
 urgently

  Neither Tom Kyte's nor Cary Millsap's books are too advanced for 
  beginners.

  In the case of Tom's book, they may have to ponder things a bit, and 
  actually try
  the code for themselves to get a good understanding.  So what?  That's 
 how
  you learn.

  Regarding Cary's book, what's so hard about it?  Beginner's could do 
 much
  worse by reading some of the claptrap pablum that is available, but that 

  will
  just make it harder for them to understand the good stuff when they see 
  it.

  Also, re the comments about the math in Cary's book:  What's the big 
 deal?
  So what if you don't understand the math?  There isn't really that much 
 of 
  it,
  and you can undestand the material even if you can't follow the proof.

  Jared

  Oracle - steroids for your brain.

  [EMAIL PROTECTED]
  Sent by: [EMAIL PROTECTED]
   10/29/2003 09:24 AM
   Please respond to ORACLE-L

  To: Multiple recipients of list ORACLE-L 
 [EMAIL PROTECTED]
  cc: 
  Subject:Re: RE: SQL and PL/SQL tuning template document 
 required urgently

  the original poster sent me an email stating that he doesnt want a book 
 or 
  documentation, he just wants some sort of short cut template. i replied 
  that if one exists its garbage. He didnt respond. 

  A side note, it seems to be in vogue to recommend advanced books like 
  carrie millsap's and tom kytes to beginners. This isnt appropriate. 
  Beginners should start with beginning material and work their way up. 
  Starting with advanced material will just confuse them. 

   From: DENNIS WILLIAMS [EMAIL PROTECTED]
   Date: 2003/10/29 Wed AM 10:49:25 EST
   To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
   Subject: RE: SQL and PL/SQL tuning template document required urgently

   Ranganath
  Since you mentioned proactive and reactive query tuning, I think 
 the
   philosophy with which one approaches the tuning exercise means 
  everything.
   Wrong philosophy and you spend your time spinning your wheels. All of 
 us
   have only a limited amount of time to devote, so the best approach 
 will 
  make
   the best use of that time. 
  Get Optimizing Oracle Performance by Cary Millsap. It doesn't 
 take 
  long
   to read the important parts. Implement Cary's approach to locate the 
  queries
   where you will get the most bang for the buck. Then use books like Guy
   Harrison's (Ryan's suggestion) for pointers on making those queries 
  perform
   better.

   Dennis Williams
   DBA
   Lifetouch, Inc.
   [EMAIL PROTECTED] 

   -Original Message-
   Sent: Wednesday, October 29, 2003 8:24 AM
   To: Multiple recipients of list ORACLE-L

   Hi there,

  Does any body have a template for proactive and 
 reactive 
  query
   tuning which can

RE: RE: SQL and PL/SQL tuning template document required urgently

2003-10-29 Thread Walt Weaver

I tried reading a Harry Potter book backwards once. It was fun, and
actually made a bit of sense.

--Walt Weaver
  Bozeman, Montana

On Wed, 2003-10-29 at 12:19, Rachel Carmichael wrote:
 As an author, let me just add that it pains US to have to say which
 I'll show you in ... 
 
 Books are laid out in some sort of order although readers don't always
 read from chapter 1 straight through to the end. If we were to go off
 on every tangent so as not to say see such and thus later, a reader
 who was sampling by looking things up in the index or table of
 contents, would be totally lost.
 
 Plus the thread of the lesson would get hopelessly tangled. 
 
 It's a fine line between explaining everything right now and saying
 later. 
 
 --- Jesse, Rich [EMAIL PROTECTED] wrote:
  The biggest problem I have with Cary's (and Jeff's!) book is
  patience.
  There's constant mention of which I'll show you in Chapter 6 and so
  forth
  and it pains me to keep on reading from where I am so's I don't get
  too
  distracted and forget the important concepts I was just reading.  :) 
  I also
  have so many sticky tabs in the first three chapters that I can't see
  my
  bookmark anymore.
  
  But the upside is it puts my 3-week old to sleep as I read it to
  her...  :D
  
  Rich
  
  Rich Jesse   System/Database Administrator 
  [EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA 
  
  -Original Message-
  Sent: Wednesday, October 29, 2003 12:15 PM
  To: Multiple recipients of list ORACLE-L
  
  
  
  Neither Tom Kyte's nor Cary Millsap's books are too advanced for
  beginners. 
  
  In the case of Tom's book, they may have to ponder things a bit, and
  actually try 
  the code for themselves to get a good understanding.  So what? 
  That's how 
  you learn. 
  
  Regarding Cary's book, what's so hard about it?  Beginner's could do
  much 
  worse by reading some of the claptrap pablum that is available, but
  that
  will 
  just make it harder for them to understand the good stuff when they
  see it. 
  
  Also, re the comments about the math in Cary's book:  What's the big
  deal? 
  So what if you don't understand the math?  There isn't really that
  much of
  it, 
  and you can undestand the material even if you can't follow the
  proof. 
  
  Jared 
  
  Oracle - steroids for your brain. 
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: Jesse, Rich
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
 
 
 __
 Do you Yahoo!?
 Exclusive Video Premiere - Britney Spears
 http://launch.yahoo.com/promos/britneyspears/
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Walt Weaver
  INET: [EMAIL PROTECTED]

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

RE: 2G trace files

2003-10-29 Thread Tim Fleury

I believe that is a limitation with that procedure.

If you are tracing your own session, use

Alter session set max_dump_file_size=unlimited;

Otherwise set it at the system level during your trace

Alter system set max_dump_file_size=unlimited;

If necessary, reset it after your large trace has completed.

-Original Message-
Sent: Wednesday, October 29, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L


 If I set max_dump_file_size greater than 2G I get an error.

Perhaps I should be more specific:

sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
  'max_dump_file_size', 'unlimited');
gives:
ORA-06502: PL/SQL: numeric or value error: character to number
conversion error

sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
  'max_dump_file_size', 2147483647 + 1);
gives:
ORA-01426: numeric overflow


On Wed, 2003-10-29 at 14:24, Tim Fleury wrote:
 Set the dump file size to unlimited.
 
 -Original Message-
 Sent: Wednesday, October 29, 2003 10:49 AM
 To: Multiple recipients of list ORACLE-L
 
 
 I'm tracing a session with 10046 event level 8.  Here's the method I
 use:
 
 sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE
);
 sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,
   'timed_statistics', true);
 /* Max dump file size is 2G */
 sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
   'max_dump_file_size', 2147483647);
 sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');
  
 If I set max_dump_file_size greater than 2G I get an error.  But with 
 trace level 8, I'm easily overrunning this limit.  How do you guys get

 around this?
 
 BTW - Just got Optimizing Oracle Performance last night and if I 
 didn't have to work so much I would have read it through by now.  
 Maybe I'll take tomorrow off.
 
 Richard Quintin, DBA
 Information Systems  Computing, DBMS 
 Virginia Tech 
 -- 
 When the character of a man is not clear to you, look at his
friends.
 -- Japanese Proverb
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Quintin, Richard
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
 the message BODY, include a line containing: UNSUB ORACLE-L (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
Richard Quintin, DBA 
Information Systems  Computing, DBMS 
Virginia Tech 
-- 
[Long hair] is considered bohemian, which may be why I grew it, but I
keep it long because I love the way it feels, part cloak, part fan, part
mane, part security blanket. -- Marge Piercy
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Quintin, Richard
  INET: [EMAIL PROTECTED]

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

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

Re: sqlplus prompt question in 9i

2003-10-29 Thread Joan Hsieh

I had same problem with 9i, what I did is at the begining of the
dbstarup or stop script, I mv the glogin.sql to _old, at end of scripts
I mv back to the original name.

Joan

Guang Mei wrote:
 
 Hi:
 
 With Oracle 8i, I always modified $ORACLE_HOME/sqlplus/admin/glogin.sql, and
 added
 
 set termout off
 col site_name noprint new_value site_name_new
 select 'SQL ' site_name from dual;
 selectuser
|| substr(proc.program, instr(proc.program,'@'),
  instr(proc.program,' ') - instr(proc.program,'@'))
|| '-SQL ' site_name
 from v$process proc
 where proc.pid = 2;
 set sqlprompt 'site_name_new'
 set termout on
 
 so that when a user launches sqlplus, it would show something at prompt like
 
 [EMAIL PROTECTED]
 
 instead of
 
 SQL
 
 I found that doing this in 9i will prevent me starting up my instance, when
 I use
 
 sqlplus '/as sysdba'
 
 In 8i, I always used svrmgrl to bounce db so there was no problem with
 modified glogin.sql.
 
 Has anyone found a work-around in 9i so that sqlplus prompt displays
 username and hostname when launched? I know there is a new
 _CONNECT_IDENTIFIER in 9i, but that's not good enough.
 
 TIA.
 
 Guang
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Guang Mei
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (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: Joan Hsieh
  INET: [EMAIL PROTECTED]

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

At least it has a decent kernel now coming with it (and probably soon
supported by Oracle) - 2.4.21.

Pls let me know on your success installing it, I'll probably start
experimenting with the 64bit version on couple of Itanium boxes (connected
to Symmetrix ;) soon.

Tanel.

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, October 29, 2003 10:19 PM


 H...interesting...

 http://ftp.redhat.com/pub/redhat/linux/enterprise/3/en/os/i386/SRPMS/

 Me and a coworker are pondering rolling our own RHASESLESELSESES3
 package for RAC testing...


 Rich

 Rich Jesse   System/Database Administrator
 [EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA


  -Original Message-
  From: Tanel Poder [mailto:[EMAIL PROTECTED]
  Sent: Wednesday, October 29, 2003 1:25 PM
  To: Multiple recipients of list ORACLE-L
  Subject: Re: 9I RAC corporate standard.
 
 
   I tried installing RHAS 2.1 ($60 duhveloper edition), but
  it's so old
  (based
   on RH7.2) that it couldn't identify our newish hardware
  (Intel D845 MB).
 
  Just for the record, Redhat Enterprise Linux 3 is available now.
 
  Tanel.
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Jesse, Rich
   INET: [EMAIL PROTECTED]

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



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

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

Re: When would we see optimizer_mode=NONE in V$SQLAREA ?




Hi!

Yep, when SQL is invalidated, it's optimizer mode 
goes to "none", as far as I've seen. It's the same with PL/SQL stored procs, 
when their dependencies change, or when the procedure is loaded but not executed 
due to incorrect parameter number or types, etc, the optimizer_mode remains 
"none".

Alter,analyze and validate commands have 
optimizer_mode setting of the session during the operation runs, but as soon 
they finish, their corresponding SQL areas are invalidated, thus optimizer_mode 
goes back to none (with an exception of validate index command). Create and drop 
statements seem not to be cached at all, but that's perfectly reasonable, 
because aDROP will clear the corresponding object out anyway andone 
can't really *reuse* aCREATE statement, because corresponding object has 
to be dropped before, causing dependent library cachestructures 
invalidated.

Btw, in v$sqlarea, there is also one more "option" 
for optimizer_mode - "MULTIPLE CHILDS PRESENT" which states that you should 
go to v$sql to check individual optimizer modes (I prefer v$sql over v$sqlarea 
anyway due performance reasons and better granularity...)

For conclusion, this is an example of 8.1.7.1 
Portal database with a lot of NONE-s:

SQL select optimizer_mode, count(*) 
from v$sql group by optimizer_mode;

OPTIMIZER_ 
COUNT(*)-- 
--CHOOSE 
1467NONE 
1261RULE 
5

Tanel.

- Original Message - 
From: "Hemant K Chitale" [EMAIL PROTECTED]
To: "Multiple recipients of list ORACLE-L" 
[EMAIL PROTECTED]
Sent: Wednesday, October 29, 2003 5:04 
PM
Subject: When would we see optimizer_mode=NONE in 
V$SQLAREA ?
   I noticed in an 9.2 instance that a number of 
entries in V$SQL, V$SQLAREA showed up with 
OPTIMIZER_MODE=NONE [there were others with CHOOSE]  
I can understand that it might be NONE if someone has done an ANALYZE or 
 DBMS_STATS or executed DDL and the SQLs are 
invalidated. But do you normally see a number of entries in V$SQL like 
that ?  [I had approx 20% of the entries].  
 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: ORA-4031 error help.

2003-10-29 Thread Tim Fleury

Make sure you are pinning your large and often executed packages,
triggers, procedures, etc in the shared pool (should be done at
startup).  That will help eliminate fragmentation.

select 'execute dbms_shared_pool.keep('||chr(39)||
owner||'.'||name||chr(39)||','||chr(39)||
 
decode(type,'TRIGGER','R','SEQUENCE','Q','P')||chr(39)||');'||chr(10)
  from v$db_object_cache
 where type in ('PACKAGE','PROCEDURE','TRIGGER','FUNCTION')
   and owner='SYS'
   and kept='NO'
 order by sharable_mem desc
/

This can be done for your application schema as well.

-Original Message-
[mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 29, 2003 11:39 AM
To: Multiple recipients of list ORACLE-L


Hello List, Need some help in resolving ORA-4031 error message. We are
using Lawson and for last few days users are getting ORA-4031 error 2-3
times a day in LAWSON log files but there is no error message in alert
log file or any trace file. Both shared pool and large pool is set to
1GB. Below is the current init.ora file. We are on Oracle 9202 and AIX
5.1, using MTS.

Thanks




# Miscellaneous
COMPATIBLE=9.2.0
DB_NAME=LAWSON
DB_FILES=1500
GLOBAL_NAMES=TRUE
DB_BLOCK_SIZE=8192
DB_CACHE_SIZE=1792M
DB_KEEP_CACHE_SIZE=16M
LARGE_POOL_SIZE=1024M
SHARED_POOL_SIZE=1024M
SGA_MAX_SIZE = 5G
DB_FILE_MULTIBLOCK_READ_COUNT=8 CONTROL_FILE_RECORD_KEEP_TIME=45
CURSOR_SHARING=SIMILAR OPEN_CURSORS=750 # From Lawson--Raised from 500
to 750 10/24/03
BACKGROUND_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/bdump
CORE_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/cdump
USER_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/udump
TIMED_STATISTICS=TRUE
CONTROL_FILES=(/appl/lawdb/oracle/data/db01/LAWSON/contrl_LAWSON_01.ctl
,
 
/appl/lawdb/oracle/data/db02/LAWSON/contrl_LAWSON_02.ctl,
 
/appl/lawdb/oracle/data/db03/LAWSON/contrl_LAWSON_03.ctl,
 
/appl/lawdb/oracle/data/db04/LAWSON/contrl_LAWSON_04.ctl,
 
/appl/lawdb/oracle/data/db05/LAWSON/contrl_LAWSON_05.ctl)

# Archive LOG_ARCHIVE_DEST=/appl/lawdb/oracle/archive_logs/LAWSON/
LOG_ARCHIVE_DUPLEX_DEST=/appl/lawdb/oracle/archive_logs_2/LAWSON/
LOG_ARCHIVE_FORMAT=ARC_LAWSON_%S.%T
LOG_ARCHIVE_START=TRUE
# LOG_ARCHIVE_TRACE = 1

# Distributed, Replication and Snapshot
DB_DOMAIN=PHSOR.ORG

# Pools
JAVA_POOL_SIZE=0

# Processes and Sessions
# PROCESSES=800 Increased value per vendor JMK 6/09/03 PROCESSES=1000
SESSIONS=1140 ENQUEUE_RESOURCES=8000 TRANSACTION_AUDITING=FALSE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAST_START_MTTR_TARGET=1200
SORT_AREA_SIZE=0
HASH_AREA_SIZE=0
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=undo
UNDO_RETENTION = 10800
PGA_AGGREGATE_TARGET=1G
WORKAREA_SIZE_POLICY = AUTO
JOB_QUEUE_PROCESSES = 10
LOG_BUFFER = 8192000# To reduce 'log file parallel write' wait event
in v$system_event
CURSOR_SPACE_FOR_TIME   = TRUE
SERVICE_NAMES=lawson_ax3202a
LOCAL_LISTENER=lawson_ax3202a
# Network Registration
INSTANCE_NAME=LAWSON
DISK_ASYNCH_IO = FALSE
BACKUP_TAPE_IO_SLAVES=TRUE
PARALLEL_THREADS_PER_CPU = 6
PARALLEL_MAX_SERVERS = 6
PARALLEL_MIN_SERVERS = 1
DISPATCHERS=(ADDRESS=(PROTOCOL=TCP)(HOST=provicon)(PORT=5000))(DISPATCH
ERS=1)
MAX_DISPATCHERS = 3
SHARED_SERVERS = 10
MAX_SHARED_SERVERS = 50


DISCLAIMER:
This message is intended for the sole use of the individual to whom it
is addressed, and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If you are
not the addressee you are hereby notified that you may not use, copy,
disclose, or distribute to anyone the message or any information
contained in the message. If you have received this message in error,
please immediately advise the sender by reply email and delete this
message.
-- 
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: Tim Fleury
  INET: [EMAIL PROTECTED]

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

RE: bitmap index not used

2003-10-29 Thread Josh Collier

version 8.1.7.4
optimizer parameters are default. 

-Original Message-
Sent: Wednesday, October 29, 2003 12:34 PM
To: Multiple recipients of list ORACLE-L


Which version of the database? Do you have any  special setup? Any optimizer
parameters
on either session or system level?
On 10/29/2003 03:09:39 PM, Josh Collier wrote:
 Hi,
 
 I have a puzzle.
 
 A bitmap index on a varchar2(25) column. table has 7131413 rows, of which
 7125290 are null for the column in question, the rest of the rows are
unique
 values. There is a histogram on this column, it has 2 buckets.
 
 A select statement using this column in the where clause generates two
 plans, one which uses the index and one which doesn't. The deciding factor
 is the length of the string in the filter.
 
 select * from table where microchip_number = 'avid1'
   this produces a plan which uses the bitmap
 
 select * from table where microchip_number = 'avid12' (and any additional
 characters in the string ) 
   this produces a plan which uses a full table scan.
 
 I am stumped about how to figure out why the optimizer makes a choice
based
 upon the length of the string in the predicate.
 
 Much obliged,
 
 Josh
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Josh Collier
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

Mladen Gogala
Oracle DBA



Note:
This message is for the named person's use only.  It may contain
confidential, proprietary or legally privileged information.  No
confidentiality or privilege is waived or lost by any mistransmission.  If
you receive this message in error, please immediately delete it and all
copies of it from your system, destroy any hard copies of it and notify the
sender.  You must not, directly or indirectly, use, disclose, distribute,
print, or copy any part of this message if you are not the intended
recipient. Wang Trading LLC and any of its subsidiaries each reserve the
right to monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender,
except where the message states otherwise and the sender is authorized to
state them to be the views of any such entity.

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

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

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

2003-10-29 Thread Daniel Harron

How often does the error occur?  How many sessions are connected when
the error occurs?  What is the status of the shared pool reserved?  If
you flush the SGA does the error clear for a period?

Also, in 8i there used to be a bug that required setting
_db_handles_cached=0

Regards,

-Daniel

-- 
Daniel Harron
Database Management
IPsoft, Inc.
[EMAIL PROTECTED]
http://www.ip-soft.net/
Phone: 888.IPSOFT8
Fax: 801.681.7664
 

-Original Message-
[EMAIL PROTECTED]
Sent: Wednesday, October 29, 2003 3:39 PM
To: Multiple recipients of list ORACLE-L


Full error message is 

ORA-04031: unable to allocate 4032 bytes of shared memory (large
pool,unknown object,session heap,frame segment))

I am already monitoing both shared pool and large pool free memory every
30 minutes and there is no issue with that. As I mentioned below Oracle
is not displaying any error message or trace file.

-Original Message-
Sent: Wednesday, October 29, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L


Well, you neet to check the full error, because otherwise there's no way
to tell if you are running low on shared or large pool.

The view that shows space usage in both places in v$sgastat.  I suggest
you start looking there.  Maybe your third-party application doesn't use
bind variables and is bloating the shared pool.  You could verify this
by observing that the sqlarea component of the shared pool is very large
as seen in v$sgastat. If this is the case then you might consider
testing with cursor_sharing=force.

You could also count different versions of similar SQL from the
application by grouping sql_text in v$sqlarea by the first 30 characters
or so.  This assumes your problem is shared pool sqlarea bloat.  You
could just be runnning out of space for MTS session heaps in the large
pool.  You have to look at v$sgastat first.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Wed, 29 Oct 2003, [EMAIL PROTECTED] wrote:

 Hello List, Need some help in resolving ORA-4031 error message. We are

 using Lawson and for last few days users are getting ORA-4031 error 
 2-3 times a day in LAWSON log files but there is no error message in 
 alert log file or any trace file. Both shared pool and large pool is 
 set to 1GB. Below is the current init.ora file. We are on Oracle 9202 
 and AIX 5.1, using MTS.

 # Miscellaneous
 COMPATIBLE=9.2.0
 DB_NAME=LAWSON
 DB_FILES=1500
 GLOBAL_NAMES=TRUE
 DB_BLOCK_SIZE=8192
 DB_CACHE_SIZE=1792M
 DB_KEEP_CACHE_SIZE=16M
 LARGE_POOL_SIZE=1024M
 SHARED_POOL_SIZE=1024M
 SGA_MAX_SIZE = 5G
 DB_FILE_MULTIBLOCK_READ_COUNT=8 CONTROL_FILE_RECORD_KEEP_TIME=45
 CURSOR_SHARING=SIMILAR
 OPEN_CURSORS=750 # From Lawson--Raised from 500 to 750 10/24/03
 BACKGROUND_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/bdump
 CORE_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/cdump
 USER_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/udump
 TIMED_STATISTICS=TRUE

CONTROL_FILES=(/appl/lawdb/oracle/data/db01/LAWSON/contrl_LAWSON_01.ctl
,

/appl/lawdb/oracle/data/db02/LAWSON/contrl_LAWSON_02.ctl,

/appl/lawdb/oracle/data/db03/LAWSON/contrl_LAWSON_03.ctl,

/appl/lawdb/oracle/data/db04/LAWSON/contrl_LAWSON_04.ctl,

/appl/lawdb/oracle/data/db05/LAWSON/contrl_LAWSON_05.ctl)

 # Archive LOG_ARCHIVE_DEST=/appl/lawdb/oracle/archive_logs/LAWSON/
 LOG_ARCHIVE_DUPLEX_DEST=/appl/lawdb/oracle/archive_logs_2/LAWSON/
 LOG_ARCHIVE_FORMAT=ARC_LAWSON_%S.%T
 LOG_ARCHIVE_START=TRUE
 # LOG_ARCHIVE_TRACE = 1

 # Distributed, Replication and Snapshot
 DB_DOMAIN=PHSOR.ORG

 # Pools
 JAVA_POOL_SIZE=0

 # Processes and Sessions
 # PROCESSES=800 Increased value per vendor JMK 6/09/03 PROCESSES=1000
 SESSIONS=1140
 ENQUEUE_RESOURCES=8000
 TRANSACTION_AUDITING=FALSE
 REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
 FAST_START_MTTR_TARGET=1200
 SORT_AREA_SIZE=0
 HASH_AREA_SIZE=0
 UNDO_MANAGEMENT=AUTO
 UNDO_TABLESPACE=undo
 UNDO_RETENTION = 10800
 PGA_AGGREGATE_TARGET=1G
 WORKAREA_SIZE_POLICY = AUTO
 JOB_QUEUE_PROCESSES = 10
 LOG_BUFFER = 8192000# To reduce 'log file parallel write' wait
event in v$system_event
 CURSOR_SPACE_FOR_TIME   = TRUE
 SERVICE_NAMES=lawson_ax3202a
 LOCAL_LISTENER=lawson_ax3202a
 # Network Registration
 INSTANCE_NAME=LAWSON
 DISK_ASYNCH_IO = FALSE
 BACKUP_TAPE_IO_SLAVES=TRUE
 PARALLEL_THREADS_PER_CPU = 6
 PARALLEL_MAX_SERVERS = 6
 PARALLEL_MIN_SERVERS = 1

DISPATCHERS=(ADDRESS=(PROTOCOL=TCP)(HOST=provicon)(PORT=5000))(DISPATCH
ERS=1)
 MAX_DISPATCHERS = 3
 SHARED_SERVERS = 10
 MAX_SHARED_SERVERS = 50

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

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

Re: ** database configuration assistant scripts

2003-10-29 Thread A Joshi

Chip,
 I am not able to find the option. Can you tell in detail. I am doing a custom database creation on NT version 9.2. Thanks for your help.Chip [EMAIL PROTECTED] wrote:
When creating a custom database, DBCA has an option to save the database creation scripts.Have Fun :)A Joshi wrote: Hi, I am using database configuration assistant to create a NT database.  Is there a way I can get the scripts used in the creation? I clicked  on the template but do not know where the template goes. Thank you  Do you Yahoo!? Exclusive Video Premiere - Britney Spears  -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: ChipINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, Califor!
nia --
 Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears

RE: 2G trace files - solved...sort of

Thanks to everyone who responded.  It turns out initialization parameter
max_dump_file_size was set to *20M* and the trace files were getting cut
off at that point.

I didn't look at the ls closely enough and thought it was getting cut
off at 2G.  Apparently the init parameter overrides the session
parameter

On Wed, 2003-10-29 at 15:09, Quintin, Richard wrote:
  If I set max_dump_file_size greater than 2G I get an error.
 
 Perhaps I should be more specific:
 
 sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
   'max_dump_file_size', 'unlimited');
 gives:
 ORA-06502: PL/SQL: numeric or value error: character to number
 conversion error
 
 sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
   'max_dump_file_size', 2147483647 + 1);
 gives:
 ORA-01426: numeric overflow
 
 
 On Wed, 2003-10-29 at 14:24, Tim Fleury wrote:
  Set the dump file size to unlimited.
  
  -Original Message-
  Sent: Wednesday, October 29, 2003 10:49 AM
  To: Multiple recipients of list ORACLE-L
  
  
  I'm tracing a session with 10046 event level 8.  Here's the method I
  use:
  
  sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE );
  sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,
'timed_statistics', true);
  /* Max dump file size is 2G */
  sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
'max_dump_file_size', 2147483647);
  sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');
   
  If I set max_dump_file_size greater than 2G I get an error.  But with
  trace level 8, I'm easily overrunning this limit.  How do you guys get
  around this?
  
  BTW - Just got Optimizing Oracle Performance last night and if I didn't
  have to work so much I would have read it through by now.  Maybe I'll
  take tomorrow off.
  
  Richard Quintin, DBA 
  Information Systems  Computing, DBMS 
  Virginia Tech 
  -- 
  When the character of a man is not clear to you, look at his friends.
  -- Japanese Proverb
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: Quintin, Richard
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
  message BODY, include a line containing: UNSUB ORACLE-L (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
 Richard Quintin, DBA 
 Information Systems  Computing, DBMS 
 Virginia Tech 
 -- 
 [Long hair] is considered bohemian, which may be why I grew it, but I
 keep it long because I love the way it feels, part cloak, part fan, part
 mane, part security blanket. -- Marge Piercy
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
Richard Quintin, DBA 
Information Systems  Computing, DBMS 
Virginia Tech 
-- 
If you would persuade, you must appeal to interest rather than
intellect. -- Benjamin Franklin
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Quintin, Richard
  INET: [EMAIL PROTECTED]

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