date:20020412


Try the max function to pick the latest timestamp. I assume the column is of
type date

John

-Original Message-
Sent: 12 April 2002 11:43
To: Multiple recipients of list ORACLE-L

Hi,
I have read up quite a lot before posting this message so please
bear with me if this question is trivial.
 I have table which stores session information of users. I have to develop a
report which gives me the number of times users have logged in ( which is
straightforward ) as well as their last access time.
Since every user has multiple records in the table, I was trying to
find a way to get me just one row per user which returns the latest date,
rather than checking for the latest date in the client logic.
Is there any function in Oracle which would return the latest date ?

Thank You,

Gavin

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gavin D'Mello
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Brainbench Oracle certifications

2002-04-12 Thread Boivin, Patrice J


Never even heard of it.

If it's a valid test (as in - if it measures what it purports to measure)
then it's interesting, but if employers have never heard of it it's useless
in that regard.

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)


 -Original Message-
Sent:   Thursday, April 11, 2002 7:58 PM
To: Multiple recipients of list ORACLE-L
Subject:Re: Brainbench Oracle certifications


One certain company (to remain nameless) is asking employees to obtain
at least 4 Brainbench Certifications this year.  And that company also
uses Brainbench assessment tests for pre-employment screening.  So there
are certain employers taking advantage of Brainbench.  But otherwise, I
haven't found my Brainbench certifications to have much meaning to
potential employers.

Andrey Bronfin wrote:
 
 Dear gurus !
 May i ask for your opinions regarding the value of the Brainbench Oracle
8i
 Administration certifications.
 I understand that it can not be compared t othe OCP, but is there any
value
 at all for it ?
 Thanks a lot in advance.
 Andrey.
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Andrey Bronfin
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suzy Vordos
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Boivin, Patrice J
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Get the Latest Date

2002-04-12 Thread Jan Pruner


SELECT user_id, COUNT(user_id), MAX(last_connect_time) 
FROM user_ses GROUP BY user_id;

JP


On Fri 12. April 2002 12:43, you wrote:
 Hi,
 I have read up quite a lot before posting this message so please
 bear with me if this question is trivial.
  I have table which stores session information of users. I have to develop
 a report which gives me the number of times users have logged in ( which is
 straightforward ) as well as their last access time.
 Since every user has multiple records in the table, I was trying to
 find a way to get me just one row per user which returns the latest date,
 rather than checking for the latest date in the client logic.
 Is there any function in Oracle which would return the latest date
 ?

 Thank You,

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Get the Latest Date

2002-04-12 Thread Gavin D'Mello


Thanks alot Iain

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, April 12, 2002 4:58 PM


 Gavin,

 select username, max(access_time) last_access_time, count(*) no_of_logins
 from session_info_table
 group by username

 should do it

 Iain Nicoll

 -Original Message-
 Sent: Friday, April 12, 2002 11:43 AM
 To: Multiple recipients of list ORACLE-L


 Hi,
 I have read up quite a lot before posting this message so please
 bear with me if this question is trivial.
  I have table which stores session information of users. I have to develop
a
 report which gives me the number of times users have logged in ( which is
 straightforward ) as well as their last access time.
 Since every user has multiple records in the table, I was trying
to
 find a way to get me just one row per user which returns the latest date,
 rather than checking for the latest date in the client logic.
 Is there any function in Oracle which would return the latest date
?

 Thank You,

 Gavin

 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Gavin D'Mello
   INET: [EMAIL PROTECTED]

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

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gavin D'Mello
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Brainbench Oracle certifications

2002-04-12 Thread Thomas, Kevin


I've got a few BB certifications, purely for my own benefit. It doesn't
appear as if many employees (in the UK or Scotland anyway) appear to take
them seriously.

Kev.

-Original Message-
Sent: 12 April 2002 12:58
To: Multiple recipients of list ORACLE-L


Never even heard of it.

If it's a valid test (as in - if it measures what it purports to measure)
then it's interesting, but if employers have never heard of it it's useless
in that regard.

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)


 -Original Message-
Sent:   Thursday, April 11, 2002 7:58 PM
To: Multiple recipients of list ORACLE-L
Subject:Re: Brainbench Oracle certifications


One certain company (to remain nameless) is asking employees to obtain
at least 4 Brainbench Certifications this year.  And that company also
uses Brainbench assessment tests for pre-employment screening.  So there
are certain employers taking advantage of Brainbench.  But otherwise, I
haven't found my Brainbench certifications to have much meaning to
potential employers.

Andrey Bronfin wrote:
 
 Dear gurus !
 May i ask for your opinions regarding the value of the Brainbench Oracle
8i
 Administration certifications.
 I understand that it can not be compared t othe OCP, but is there any
value
 at all for it ?
 Thanks a lot in advance.
 Andrey.
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Andrey Bronfin
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suzy Vordos
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Boivin, Patrice J
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas, Kevin
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: RE: No DBAs needed on AS400

2002-04-12 Thread Boivin, Patrice J


Wow, this is scary thinking.

Look only at expenses, not at the need that needs to be filled.

Here's another example of that:

http://www.vnunet.com/News/1130760

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin  Operations | Admin. et Exploit. des systèmes
Technology Services| Services technologiques
Informatics Branch | Direction de l'informatique 
Maritimes Region, DFO  | Région des Maritimes, MPO

E-Mail: [EMAIL PROTECTED]


 -Original Message-
Sent:   Thursday, April 11, 2002 6:13 PM
To: Multiple recipients of list ORACLE-L
Subject:Re:RE: No DBAs needed on AS400

Hey, just wait around till the Oracle support bill shows up.  No, better
yet,
leave before then.  If he/she is making decisions based strickly on expense
you
might as well be one of the early rats, namely leave the ship before it
sinks!

Dick Goulet

Reply Separator
Author: [EMAIL PROTECTED]
Date:   4/11/2002 11:13 AM


I would ask myself the question, Do I want to be working for someone this
technically clueless?  I foresee a long education process via-a-vis this
guy (I'm assuming that it's a guy) or a never-ending struggle just to get
the basic tools that you need to do your job.



 

   
Mercadante,

  
Thomas FTo: Multiple recipients of list
ORACLE-L  
NDATFM  [EMAIL PROTECTED]

  
@labor.state.cc:

  
ny.us   Subject: RE: No DBAs needed on
AS400  
Sent by: root

  
 

   
 

   
04/11/2002

  
10:18 AM

  
Please

  
respond to

  
ORACLE-L

  
 

   
 

   




Jay,

I would provide a list of functions you perform daily and what would happen
if they are not  completed in a timely manner.

I would also update my resume.  Sounds like a pretty naive director of IT.
Either he/she is making a play to move you out and get a prior buddy in
place, or is a complete idiot, thinking of short-term money savings but is
completely unaware of long-term result.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, April 11, 2002 9:54 AM
To: Multiple recipients of list ORACLE-L


We are going through a merger, and management is looking to eliminate
positions.  Here is a brief summary of my discussion with the new director
of IT:

Director: Back when I we were using an AS400, we didn't need a DBA.
Me: Then you probably were just using files.
Director: No, it was a database.
Me: Could you issue SQL commands?
Director: Yes.  But we didn't need a DBA.  I guess it was just one of
those
mysteries of life.


My thoughts are that he is using the term database in the generic sense
of
the word (our files are our database), or he was using some proprietary
database that doesn't even begin to compare to Oracle.

For those of you who know AS400s, I would appreciate some insight that
would
demonstrate why he needs to keep me as a DBA.

Thanks,
Jay


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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED]

RE: Developer access in test database


Jeff,

We have dba privileges granted to schema owners in devl databases, test
database mimics the prod, so they just have appropriate roles. In prod, no
schema level access, everyone goes in with their own id, and no one has
those ANY privileges.

In next couple of months, we are cutting off developer access to prod. For
fixing bugs and debugging, we create a database (named DAYOLD) that is same
as production database at 5am that day. It's is all automatic, works fine
and all developers are getting adjusted to the fact that prod access is
going away.

Also we have a benefit of this dayold database, we can run and test all
weekly releases on the dayold database, the day before they are applied to
production, so we can catch all errors and get them fixed by developers
before the code release, this is imp because when we do the code release,
most of the developers are probably still in bed that early in the am.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-Original Message-
Sent: Friday, April 12, 2002 8:38 AM
To: Multiple recipients of list ORACLE-L


In our test databases, I have always granted select,insert,update,delete any
table to the developers.

While yesterday, one updated a sys table.

Be non-production, I always felt if it is destroyed can always be recreated.

What table privileges do you grant in test.

Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
(216) 781-4204


***1

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 ESPN at (860) 766-2000 and 
delete this e-mail message from your computer, Thank you.

***1

RE: empty string = null?


Jonathan,

This behavior is partly because ANSI standard states that treatment of NULL
is implementation specific.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!


-Original Message-
Sent: Thursday, April 11, 2002 11:53 PM
To: Multiple recipients of list ORACLE-L


You're kidding! The above is really in the Oracle docs? Oracle treats
zero-length strings as nulls and yet recommends against us doing likewise?
How, pray tell, are we to avoid treating empty strings as nulls, since
that's the way the Oracle software works?



*2

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

*2

Re: Brainbench Oracle certifications

2002-04-12 Thread bill thater


[EMAIL PROTECTED] wrote:

 is there any value to OCP?


here we go again.;-)



-- 
--
Bill Shrek Thater  ORACLE DBA
 [EMAIL PROTECTED]

You gotta program like you don't need the money,
You gotta compile like you'll never get hurt,
You gotta run like there's nobody watching,
It's gotta come from the heart if you want it to work.

Terminal glare:  A look that kills...




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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Developer access in test database

2002-04-12 Thread Mercadante, Thomas F




Jeff,

Developers should only have access to stuff 
they need. This does not include SYS stuff. Basically, do you want 
to be restoring the development database just because somebody was playing with 
something? Further, who will be blamed for the developers not being able 
to do their job while you are restoring the db - the developer who screwed it 
up, or you?

I'm all for giving developers tons of privs 
in the dev database - up to a point. You need to protect yourself from 
them ruining your day.

To answer your question, I grant s,i,u,d to 
the schema tables directly to the user to that they can create stored procs if 
need be. I then migrate stored procs to the dev schema as need 
be.

Hope this helps
Tom Mercadante Oracle Certified 
Professional 

  -Original Message-From: Jeffrey Beckstrom 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, April 12, 2002 8:38 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  Developer access in test database
  In our test databases, I have always granted select,insert,update,delete 
  any table to the developers.
  
  While yesterday, one updated a "sys" table.
  
  Be non-production, I always felt if it is destroyed can always be 
  recreated.
  
  What table privileges do you grant in "test".
  
  Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional 
  Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 
  781-4204

RE: Developer access in test database

2002-04-12 Thread Jeffrey Beckstrom




Like I said this is test. In prod, they only have select 
access.

However, since in test have "any" rights, were able to get a sys 
object. [EMAIL PROTECTED] 4/12/02 9:13:19 AM 
Jeff,We have dba privileges granted to schema owners in 
devl databases, testdatabase mimics the prod, so they just have appropriate 
roles. In prod, noschema level access, everyone goes in with their own id, 
and no one hasthose ANY privileges.In next couple of months, we are 
cutting off developer access to prod. Forfixing bugs and debugging, we 
create a database (named DAYOLD) that is sameas production database at 5am 
that day. It's is all automatic, works fineand all developers are getting 
adjusted to the fact that prod access isgoing away.Also we have a 
benefit of this dayold database, we can run and test allweekly releases on 
the dayold database, the day before they are applied toproduction, so we can 
catch all errors and get them fixed by developersbefore the code release, 
this is imp because when we do the code release,most of the developers are 
probably still in bed that early in the 
am.Raj__Rajendra 
Jamadagni 
MIS, ESPN Inc.Rajendra dot Jamadagni at ESPN dot comAny opinion 
expressed here is personal and doesn't reflect that of ESPN Inc.QOTD: 
Any clod can have facts, but having an opinion is an art!-Original 
Message-Sent: Friday, April 12, 2002 8:38 AMTo: Multiple recipients 
of list ORACLE-LIn our test databases, I have always granted 
select,insert,update,delete anytable to the developers.While 
yesterday, one updated a "sys" table.Be non-production, I always felt if 
it is destroyed can always be recreated.What table privileges do you 
grant in "test".Jeffrey BeckstromDatabase AdministratorGreater 
Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 
44113(216) 781-4204

Re: Get the Latest Date

2002-04-12 Thread Madhusudhanan Sampath


Gavin,

We have a similar situation. We simply maintain two tables - one to maintain 
login-history and another to record the latest-login. The latest-login table 
would be updated by a trigger on the history-table.

A procedure checks both tables and returns approppriate info.
-Madhu



From: Gavin D'Mello [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Get the Latest Date
Date: Fri, 12 Apr 2002 02:43:25 -0800

Hi,
 I have read up quite a lot before posting this message so please
bear with me if this question is trivial.
  I have table which stores session information of users. I have to develop 
a
report which gives me the number of times users have logged in ( which is
straightforward ) as well as their last access time.
 Since every user has multiple records in the table, I was trying 
to
find a way to get me just one row per user which returns the latest date,
rather than checking for the latest date in the client logic.
 Is there any function in Oracle which would return the latest date 
?

Thank You,

Gavin

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gavin D'Mello
   INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Developer access in test database

2002-04-12 Thread Mercadante, Thomas F


Jeff,

I agree with Raj.  Further to what I said before, I have four database -
dev, user test, training and production.  Training and Prod are always at
the same application release level.  Developers have total access in dev;
and query-only access in the rest.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, April 12, 2002 9:13 AM
To: Multiple recipients of list ORACLE-L


Jeff,

We have dba privileges granted to schema owners in devl databases, test
database mimics the prod, so they just have appropriate roles. In prod, no
schema level access, everyone goes in with their own id, and no one has
those ANY privileges.

In next couple of months, we are cutting off developer access to prod. For
fixing bugs and debugging, we create a database (named DAYOLD) that is same
as production database at 5am that day. It's is all automatic, works fine
and all developers are getting adjusted to the fact that prod access is
going away.

Also we have a benefit of this dayold database, we can run and test all
weekly releases on the dayold database, the day before they are applied to
production, so we can catch all errors and get them fixed by developers
before the code release, this is imp because when we do the code release,
most of the developers are probably still in bed that early in the am.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-Original Message-
Sent: Friday, April 12, 2002 8:38 AM
To: Multiple recipients of list ORACLE-L


In our test databases, I have always granted select,insert,update,delete any
table to the developers.

While yesterday, one updated a sys table.

Be non-production, I always felt if it is destroyed can always be recreated.

What table privileges do you grant in test.

Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
(216) 781-4204
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Developer access in test database

2002-04-12 Thread Kimberly Smith




As little as they need. I, as a 
general rule, only grant to the objects they are actually using. One way 
to look at it is you will always know which grants to move over to test and 
prod. Now, the group I work with grant select on any table which I have 
mixed feelings about. With the amount of tables we are dealing with it 
sure makes life easier. Most of them do not need to even update the 
application tables as batch loads take care of that (for the most 
part).

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Jeffrey BeckstromSent: 
  Friday, April 12, 2002 5:38 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Developer access in test 
  database
  In our test databases, I have always granted select,insert,update,delete 
  any table to the developers.
  
  While yesterday, one updated a "sys" table.
  
  Be non-production, I always felt if it is destroyed can always be 
  recreated.
  
  What table privileges do you grant in "test".
  
  Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional 
  Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 
  781-4204

Re:RE: Developer access in test database

2002-04-12 Thread dgoulet


Tom,

That's all well  good.  I believe in keeping people be they developers or
end users out of matters that they cannot do anything about or just plain have
no reason to be mucking about in, like SYS.  Now would someone please tell all
of those third party apps folks the same thing.  I've got another third party
application, called Maximo from MRO to install  guess where they want to go
first?  You got it, SYS.  Why, becuase they want their own views buiilt on all
of those deep down tables that SYS owns.  I'm geting pretty p^%ed at these
clowns who are constantly telling me that they have tons of experience  know
what their doing.  It figures though, this one supports MicroSlop Sql too.  GOD,
I really hope that federal court decides to break up Gates  co.

Dick Goulet

Reply Separator
Author: Mercadante; Thomas F [EMAIL PROTECTED]
Date:   4/12/2002 5:33 AM

Jeff,
 
Developers should only have access to stuff they need.  This does not
include SYS stuff.  Basically, do you want to be restoring the development
database just because somebody was playing with something?  Further, who
will be blamed for the developers not being able to do their job while you
are restoring the db - the developer who screwed it up, or you?
 
I'm all for giving developers tons of privs in the dev database - up to a
point.  You need to protect yourself from them ruining your day.
 
To answer your question, I grant s,i,u,d to the schema tables directly to
the user to that they can create stored procs if need be.  I then migrate
stored procs to the dev schema as need be.
 
Hope this helps

Tom Mercadante 
Oracle Certified Professional 

-Original Message-
Sent: Friday, April 12, 2002 8:38 AM
To: Multiple recipients of list ORACLE-L


In our test databases, I have always granted select,insert,update,delete any
table to the developers.
 
While yesterday, one updated a sys table.
 
Be non-production, I always felt if it is destroyed can always be recreated.
 
What table privileges do you grant in test.
 
Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
(216) 781-4204


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


META content=MSHTML 5.50.4912.300 name=GENERATOR/HEAD
BODY style=MARGIN-TOP: 2px; FONT: 10pt Courier New; MARGIN-LEFT: 2px
DIVSPAN class=487251912-12042002Jeff,/SPAN/DIV
DIVSPAN class=487251912-12042002/SPANnbsp;/DIV
DIVSPAN class=487251912-12042002Developers should only have access to stuff 
they need.nbsp; This does not include SYS stuff.nbsp; Basically, do you want 
to be restoring the development database just because somebody was playing with 
something?nbsp; Further, who will be blamed for the developers not being able 
to do their job while you are restoring the db - the developer who screwed it 
up, or you?/SPAN/DIV
DIVSPAN class=487251912-12042002/SPANnbsp;/DIV
DIVSPAN class=487251912-12042002I'm all for giving developers tons of privs 
in the dev database - up to a point.nbsp; You need to protect yourself from 
them ruining your day./SPAN/DIV
DIVSPAN class=487251912-12042002/SPANnbsp;/DIV
DIVSPAN class=487251912-12042002To answer your question, I grant s,i,u,d to 
the schema tables directly to the user to that they can create stored procs if 
need be.nbsp; I then migrate stored procs to the dev schema as need 
be./SPAN/DIV
DIVnbsp;/DIV
DIVSPAN class=487251912-12042002Hope this helps/SPAN/DIV
PFONT face=ArialTom Mercadante/FONT BRFONT face=ArialOracle Certified 
Professional/FONT /P
BLOCKQUOTE dir=ltr style=MARGIN-RIGHT: 0px
  DIV class=OutlookMessageHeader dir=ltr align=leftFONT 
  face=Tahoma-Original Message-BRBFrom:/B Jeffrey Beckstrom 
  [mailto:[EMAIL PROTECTED]]BRBSent:/B Friday, April 12, 2002 8:38 
  AMBRBTo:/B Multiple recipients of list ORACLE-LBRBSubject:/B 
  Developer access in test databaseBRBR/FONT/DIV
  DIVIn our test databases, I have always granted select,insert,update,delete 
  any table to the developers./DIV
  DIVnbsp;/DIV
  DIVWhile yesterday, one updated a sys table./DIV
  DIVnbsp;/DIV
  DIVBe non-production, I always felt if it is destroyed can always be 
  recreated./DIV
  DIVnbsp;/DIV
  DIVWhat table privileges do you grant in test./DIV
  DIVnbsp;/DIV
  DIVJeffrey BeckstromBRDatabase AdministratorBRGreater Cleveland Regional

  Transit AuthorityBR1240 W. 6th StreetBRCleveland, Ohio 44113BR(216) 
  781-4204/DIV/BLOCKQUOTE/BODY/HTML

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')

RE: Brainbench Oracle certifications

2002-04-12 Thread Kimberly Smith


I vote we skip the debate.  There is really not much to add to it as pretty
much everyone
voiced their opinion on that one already:-)

-Original Message-
Sent: Friday, April 12, 2002 6:23 AM
To: Multiple recipients of list ORACLE-L


[EMAIL PROTECTED] wrote:

 is there any value to OCP?


here we go again.;-)



--
--
Bill Shrek Thater  ORACLE DBA
 [EMAIL PROTECTED]

You gotta program like you don't need the money,
You gotta compile like you'll never get hurt,
You gotta run like there's nobody watching,
It's gotta come from the heart if you want it to work.

Terminal glare:  A look that kills...




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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

SQL statement with hints or without hints


Hi all,

We are running a kind of hybrid mode, mainly RBO with some tables analyzed
(mainly for intermedia). Last week we had an interesting situation, when
tuning a huge SQL, we created an index and the query worked fine, but later
in the day developers complained that their queries are running slower.

We finally took out the new index and things were fine after that, but this
brought out few important issues,

1. Developers do not put hints in their SQL statements.
2. They implicitly reply on a set of indexes that have worked for them.
3. Due to reasons of 1 and 2, no new indexes can be created because it will
make their queries run slower.
4. As an effect of 3, new queries that can't make use of these set indexes
will always be slower. Some of these queries can't be rewritten to make use
of the existing indexes due to business logic involved.

My question is, do you, in your organization recommend putting hints in the
SQLs all the time, some times or not at all ??  It doesn't really matter if
you use RBO or CBO.

When the developers questioned me, I told them 'Oracle optimizer is not an
exact science, especially in a mixed RBO/CBO mode, so it is bound to make
some wrong choices and that is precisely why Oracle calls these things as
hints, so we tell the optimizer to do the right thing.

I am in a good mood to write a short note explaining developers why they
(must) use hints in their SQL statements.

Your input is greatly appreciated
TIA
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!



***1

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 ESPN at (860) 766-2000 and 
delete this e-mail message from your computer, Thank you.

***1

RE: V$SESSION

2002-04-12 Thread Mark Leith


1. The user has connected, but not issued a SQL statement.
2. The user has been connected a while, issued a statement some time ago,
but that statement has been flushed via the buffer under the LRU algorithm.

Any more?

HTH

Mark

===
 Mark Leith | T: +44 (0)1905 330 281
 Sales  Marketing  | F: +44 (0)870 127 5283
 Cool Tools UK Ltd  | E: [EMAIL PROTECTED]
===
   http://www.cool-tools.co.uk
   Maximising throughput  performance


-Original Message-
Mengler
Sent: 11 April 2002 21:29
To: Multiple recipients of list ORACLE-L


If there is an entry in V$SESSION, does it imply that user has
a current session within the instance?

If not, how do I determine which users have logged out of the DB?

Under what conditions would I not be able to obtain any SQL from
V$SQLAREA for any given SID?


  1  select username, osuser, sql_text
  2  from v$session ss, v$sqlarea sa
  3  where ss.sid = 861
  4   and  ss.sql_address = sa.address
  5*  and  ss.sql_hash_value = sa.hash_value
[EMAIL PROTECTED] /

no rows selected

[EMAIL PROTECTED] select * from v$session where sid = 861;

SADDR   SIDSERIAL# AUDSID PADDR USER# USERNAME
COMMAND TADDR
 -- -- --  -- --
 -- 
LOCKWAIT STATUS   SERVER   SCHEMA# SCHEMANAME OSUSER
PROCESS
  - -- -- --
- -
MACHINE  TERMINAL
 -
PROGRAM  TYPE   SQL_ADDR
SQL_HASH_VALUE PREV_SQL
 --  ---
--- 
PREV_HASH_VALUE MODULE   MODULE_HASH
---  ---
ACTION   ACTION_HASH
 ---
CLIENT_INFO
FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ#
 ---
- -
ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET
-- --- - - 
AF4518E4861680   47669205 AF289B1C   2494 OPS$RCAMPBEL
0
 INACTIVE DEDICATED   2494 OPS$RCAMPBEL
rcampbel25645
titanpts/200
runform30@titan (TNS interface)  USER   00
0 A54EFF6C
 -2.129E+09 frmula_inq1635528872
from menu_driver   306861083

683581-1
14  13785328 11-APR-02  117


--
Charlie Mengler   Maintenance Warehouse
[EMAIL PROTECTED]  10641 Scripps Summit Ct.
858-831-2229  San Diego, CA 92131
Daylight Savings Time means that everybody gets up 1 hour earlier.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Charlie Mengler
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Developer access in test database

2002-04-12 Thread Deshpande, Kirti

I grant them full access to their own
schema. The production schema owner inTEST databases is still controlled
by DBAs. I do grant them selected '... any ...' privileges as needed, in
theTEST databases excluding 'select any table'. No one gets it. Instead I
grant them select_catalog_role to view SYS owned views etc. In addition, I have
created a TOAD_USER role for them to use TOAD fully without running into any
problems. These things have cut down a lot of very basic questions and calls
from Developers.However, one size does not fit all. This is all dependent on how developement work
is done in your particular environment. In my previous job, no one bothered to
change sys and system passwords and it was a true 'open systems' environment
when it came to TEST/DEV work !!

- Kirti

-Original
Message-From: Kimberly Smith
[mailto:[EMAIL PROTECTED]]Sent: Friday, April 12, 2002 8:58
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
Developer access in test database

As little as they need. I, as
a general rule, only grant to the objects they are actually using. One
way to look at it is you will always know which grants to move over to test
and prod. Now, the group I work with grant select on any table which I
have mixed feelings about. With the amount of tables we are dealing with
it sure makes life easier. Most of them do not need to even update the
application tables as batch loads take care of that (for the most
part).

-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Jeffrey
BeckstromSent: Friday, April 12, 2002 5:38 AMTo:
Multiple recipients of list ORACLE-LSubject: Developer access in
test database
In our test databases, I have always granted
select,insert,update,delete any table to the developers.

While yesterday, one updated a "sys" table.

Be non-production, I always felt if it is destroyed can always be
recreated.

What table privileges do you grant in "test".

Jeffrey BeckstromDatabase AdministratorGreater Cleveland
Regional Transit Authority1240 W. 6th StreetCleveland, Ohio
44113(216) 781-4204

Re:RE: No DBAs needed on AS400

2002-04-12 Thread dgoulet


Tony,

Image may have been a network database, but when HP Turbo'd it they made the
change to the hierarchical model.  Sorry to correct you, but it says so right in
the manual.  In the What is TurboImage section they do acknowledge the
background in Image, though there is no mention of TOTAL or that the
predecessors where network based.  That's a new one on me, so I guess there is
one more item I've learned.  HP also wrapped their ImageSQL stuff around
TurboImage so that one could issue SQL statements against it.  That is how the
Oracle Gateway does it's thing.  Better but not the best.  With a little luck
our install of TurboImage will see the end of day in a month.  Can't wait for
some reason.

Dick Goulet

Reply Separator
Author: Tony Johnson [EMAIL PROTECTED]
Date:   4/11/2002 3:40 PM

Going to show my age here ...

Actually IMAGE is a network database and not a hierarchical one. It is
patterned
after a database called TOTAL that back in the days preceding Oracle,
Sybase,etc.
was installed almost everywhere. While you didnt need a DBA it was good to
have
people that were knowledgable in normalization and many of the same
principles
in use today to create your databases. I cut my 'DBA' teeth on TOTAL and did
some
work with IMAGE many moons ago. A great tool in its time but would be seen
as 
a dinosaur today.

-Original Message-
Sent: Thursday, April 11, 2002 7:58 AM
To: Multiple recipients of list ORACLE-L


Jay,

We still have one of those dinosaurs running here called HP's TurboImage
database.  It also does not need a DBA, actually it does not understand
what a
DBA is.  The database is hierachtical with the constraints set during
creation.  I constantly have fun with the older ManMan developers as we move
them into PeopleSoft.  They have no idea of what's going on under the
covers. 
SQL is a foreign language to them, their all use to TurboImage intrinsics
and a
SQL*Plus look alike tool called Quiz.  It's kind of fun, you have to use a
dataset (they call them databases), report out columns, and then set
conditions.
 Kind of like writing SQL with the from clause first.  This type of
structure
has to be carried into the application programs as well, namely you've got
to
call the dbopen intrinsic before you can use a dataset.  BTW, that's in C
syntax
'dbopen(MANDB.MDATABAS.MMV090)' (the HP3000 MPEi/x directory structure is
kind
of strange).  There is no such thing as rollback or read consistent view and
recovery consists of going back to the last backup, all of which are cold,
and
having everyone re-enter their transactions.  OH, yes, there is no such
thing as
a user.  If you have the ability to loggon to the HP3000, you can use the
database and everything is wide open.  No ideas of security.  Problem with
TurboImage is that to modify a database you have to rebuild it using an HP
utility and then you have to rebuild all of your application programs, or
else
they crash.  Developers do that task as needed and when they mess up, well
all
hell can and does break loose.  Also you need to run these third party
utilities
each night so that there is room for the dataset to grow and you have to fix
broken chains all the time.  There is no concept of an instance either.
Your
application program directly accesses the data files/datasets, so 'impeded'
sessions are a common occurance and if an application messes up it can
require a
system reboot to clear the problems.  Sure you don't need a DBA, but you
sure as
heck need an operator.  Problem is that most operators don't get paid as
well as
a DBA.  If your new CIO is in that mindset I'd recommend polishing your
resume,
cause your gonna need it.

Dick Goulet

Reply Separator
Author: Jay Hostetter [EMAIL PROTECTED]
Date:   4/11/2002 5:54 AM

We are going through a merger, and management is looking to eliminate
positions.
 Here is a brief summary of my discussion with the new director of IT:

Director: Back when I we were using an AS400, we didn't need a DBA.
Me: Then you probably were just using files.
Director: No, it was a database.
Me: Could you issue SQL commands?
Director: Yes.  But we didn't need a DBA.  I guess it was just one of those
mysteries of life.


My thoughts are that he is using the term database in the generic sense of
the
word (our files are our database), or he was using some proprietary
database
that doesn't even begin to compare to Oracle.

For those of you who know AS400s, I would appreciate some insight that would
demonstrate why he needs to keep me as a DBA.

Thanks,
Jay


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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an

Re: Brainbench Oracle certifications


...guess I should have added the emoticon.  I meant it as a response, not a
question...

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, April 12, 2002 8:03 AM


 I vote we skip the debate.  There is really not much to add to it as
pretty
 much everyone
 voiced their opinion on that one already:-)

 -Original Message-
 Sent: Friday, April 12, 2002 6:23 AM
 To: Multiple recipients of list ORACLE-L


 [EMAIL PROTECTED] wrote:

  is there any value to OCP?


 here we go again.;-)



 --
 --
 Bill Shrek Thater  ORACLE DBA
  [EMAIL PROTECTED]
 
 You gotta program like you don't need the money,
 You gotta compile like you'll never get hurt,
 You gotta run like there's nobody watching,
 It's gotta come from the heart if you want it to work.
 
 Terminal glare:  A look that kills...




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

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

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

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

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Controlling access to tables

2002-04-12 Thread Rick_Cale


Hi,

My co-worker is looking for suggestions on how to accomplish the following.

A user has SUID access on a set of tables while in a application.
Outside the application we only want that user to have select privs only
when using sql*plus.

They could have the application changed to reset privs but are there any
other alternatives.

To summarize in the app have one set a privs everywhere else another set of
privs.

Thanks
Rick


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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL statement with hints or without hints

2002-04-12 Thread Koivu, Lisa


Hi Raj, my two cents, fwiw:

Interesting.  I have worked places where hints were frowned upon (why is
Oracle not doing it's job?  Hey, you, DBA, you fix it.  No, I don't like
your hint.  Fix it)

However I feel the opposite way.  I have also worked in very liberal
environments (make it work! now!).  I truly believe that we, as
developers, know the data better than the optimizer.  Don't get me wrong,
not all sql statements require hints, but there are statements that will
benefit from having them.  Determining which is which is the trick, based
upon application behavior. 

I also think that a hybrid environment requires more assistance via hints
than pure oltp or dss.  Hybrids provide their own unique type of
challenge. 

omg did I just refer to myself as a developer...

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
954-935-4117


 -Original Message-
 From: Jamadagni, Rajendra [SMTP:[EMAIL PROTECTED]]
 Sent: Friday, April 12, 2002 10:03 AM
 To:   Multiple recipients of list ORACLE-L
 Subject:  SQL statement with hints or without hints
 
 Hi all,
 
 We are running a kind of hybrid mode, mainly RBO with some tables analyzed
 (mainly for intermedia). Last week we had an interesting situation, when
 tuning a huge SQL, we created an index and the query worked fine, but
 later
 in the day developers complained that their queries are running slower.
 
 We finally took out the new index and things were fine after that, but
 this
 brought out few important issues,
 
 1. Developers do not put hints in their SQL statements.
 2. They implicitly reply on a set of indexes that have worked for them.
 3. Due to reasons of 1 and 2, no new indexes can be created because it
 will
 make their queries run slower.
 4. As an effect of 3, new queries that can't make use of these set indexes
 will always be slower. Some of these queries can't be rewritten to make
 use
 of the existing indexes due to business logic involved.
 
 My question is, do you, in your organization recommend putting hints in
 the
 SQLs all the time, some times or not at all ??  It doesn't really matter
 if
 you use RBO or CBO.
 
 When the developers questioned me, I told them 'Oracle optimizer is not an
 exact science, especially in a mixed RBO/CBO mode, so it is bound to make
 some wrong choices and that is precisely why Oracle calls these things as
 hints, so we tell the optimizer to do the right thing.
 
 I am in a good mood to write a short note explaining developers why they
 (must) use hints in their SQL statements.
 
 Your input is greatly appreciated
 TIA
 Raj
 __
 Rajendra JamadagniMIS, ESPN Inc.
 Rajendra dot Jamadagni at ESPN dot com
 Any opinion expressed here is personal and doesn't reflect that of ESPN
 Inc.
 
 QOTD: Any clod can have facts, but having an opinion is an art!
   File: ESPN_Disclaimer.txt  
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Koivu, Lisa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Is it possible to remove default (TOAD users)

2002-04-12 Thread Shaw John-P55297


8.1.6 NT TOAD
I have a developer assign a default value to a column, and they don't want
it anymore. I can reset the default to be NULL (the default default so to
speak) but they are able to see the fact that there is a default in TOAD on
that column and of course the other columns don't have a default of NULL on
them so they would like it removed - it makes them nervous. Is there any way
to just get rid of the default showing up without deleting and re-adding the
column? Should I tell them not to meddle anymore?  
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Shaw John-P55297
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Controlling access to tables


Create a role that has the suid privs, assign the role to the user who needs
it, BUT DO NOT MAKE IT DEFAULT. Within the application enable this role for
the user as soon as they log in, so they get all access for that session.
Outside of this, as the role is not default, they won't have access.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!


***1

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 ESPN at (860) 766-2000 and 
delete this e-mail message from your computer, Thank you.

***1

RE: Rule based engine

2002-04-12 Thread Scott . Shafer


Got to www.google.com and search for Oracle 7.3 documentation.

Scott Shafer
San Antonio, TX
210-581-6217


 -Original Message-
 From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
 Sent: Friday, April 12, 2002 2:03 AM
 To:   Multiple recipients of list ORACLE-L
 Subject:  RE: Rule based engine
 
 
 Could you  give me more information on this. I do not have the Oracle 7.3
 doc's
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Solaris 2.8

2002-04-12 Thread Djordje Jankovic


Hi friends and gurus,

Anybody has had (or has heard from friends of) any bad experience with
Oracle 8 running on Solaris 8.  I know that the first supported version on
Solaris 8 is 8.0.5.  If you know of some I would appreciate f you could
share it, and specify which version and patch release.

Thanks a lot.

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

1Z0-007 Exam

2002-04-12 Thread KENNETH JANUSZ




Has anyone taken this exam? If so, I would like your 
feedback.

Thanks,
Ken Janusz, CPIM

Re: Rman, Duplicating without set untill.... cloning with

2002-04-12 Thread Jay Hostetter


My experience has been that RMAN will try to restore the database right up to the 
current point-in-time of the production database.  So if you are on a remote system, 
it will be looking for data in the current redo logs (or logs that were archived since 
the backup), which it can't access.Oracle claims that RMAN will restore the last 
backup, but I think it is trying to bring the duplicated DB in sync with production.  

I am on 8.1.7 on Tru64.  I haven't investigated this enough to prove the theory, so I 
could be wrong.  All I know is that if I don't use SET UNTIL when duplicating a 
database, RMAN complains about missing archivelogs.

Jay

 [EMAIL PROTECTED] 04/12/02 10:13AM 
I duplicate without at set until time using 8.0.6.3.  I follow the
instructions from Metalink.  I duplicate our production databases to another
box and change the names from prd to rpt.   I do clone all of the
tablespaces.

Ruth
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, April 12, 2002 5:58 AM
tablespaces


Hi All,


I'm currently in the middle of a tar with Oracle about duplicating without
set until time/scn According to the doc's it must be possible but when
trying I get an error message about an archive log file that it needs but
is not yet there.
Any of you duplicating without set untill time/scn etc.. w/o problems

if so what version RMAN/Oracle What OS/version
What sequence of actions do you perform?


Second I try to find the rman alternative to cloning just a part of the
database but can't find anything in the Doc's
Anybody

TIA



Jack



===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst 
Young, niet toegestaan. Ernst  Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst  Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst  Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst  Young. Ernst  Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst  Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.

If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst  Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
===




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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing:

protocol.ora

2002-04-12 Thread Sajid Iqbal


Hi

We used to use the protocol.ora file to restrict access to the database,
on oracle 8.0.5

However since moving to Oracle 8.1.6.3  this doesn't work.

Am I missing something or is there something wrong.

TIA

-- 
Sajid Iqbal





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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL statement with hints or without hints


I am not sure, outlines do require CBO (me thinks) and we are not there yet.
Also there is a general resistance around here for that. Now that you have
asked me, I will probably take it up to my manager once we move to CBO
completely (sometime before Universe stops expanding judging by current
support from within our group to migrate to CBO).

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!


-Original Message-
Sent: Friday, April 12, 2002 10:18 AM
To: 'Jamadagni, Rajendra'; '[EMAIL PROTECTED]'


Raj, out of curiosity:  Why are outlines a no-no?  Please elaborate.  I
haven't used them and would be interested in your opinion.

LK


***1

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 ESPN at (860) 766-2000 and 
delete this e-mail message from your computer, Thank you.

***1

Re: Rman, Duplicating without set untill.... cloning with

2002-04-12 Thread Robin Li


I think your theory is correct. I have the same experience, I have to
use SET UNTIL, otherwise RMAN complains about the archivelogs.  Oracle
8.1.7 on IBM AIX.

Robin
Jay Hostetter wrote:
 
 My experience has been that RMAN will try to restore the database right up to the 
current point-in-time of the production database.  So if you are on a remote system, 
it will be looking for data in the current redo logs (or logs that were archived 
since the backup), which it can't access.Oracle claims that RMAN will restore the 
last backup, but I think it is trying to bring the duplicated DB in sync with 
production.
 
 I am on 8.1.7 on Tru64.  I haven't investigated this enough to prove the theory, so 
I could be wrong.  All I know is that if I don't use SET UNTIL when duplicating a 
database, RMAN complains about missing archivelogs.
 
 Jay
 
  [EMAIL PROTECTED] 04/12/02 10:13AM 
 I duplicate without at set until time using 8.0.6.3.  I follow the
 instructions from Metalink.  I duplicate our production databases to another
 box and change the names from prd to rpt.   I do clone all of the
 tablespaces.
 
 Ruth
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Friday, April 12, 2002 5:58 AM
 tablespaces
 
 Hi All,
 
 I'm currently in the middle of a tar with Oracle about duplicating without
 set until time/scn According to the doc's it must be possible but when
 trying I get an error message about an archive log file that it needs but
 is not yet there.
 Any of you duplicating without set untill time/scn etc.. w/o problems
 
 if so what version RMAN/Oracle What OS/version
 What sequence of actions do you perform?
 
 Second I try to find the rman alternative to cloning just a part of the
 database but can't find anything in the Doc's
 Anybody
 
 TIA
 
 Jack
 
 ===
 De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
 uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
 vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
 derden is, behoudens voorafgaande schriftelijke toestemming van Ernst 
 Young, niet toegestaan. Ernst  Young staat niet in voor de juiste en
 volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
 voor tijdige ontvangst daarvan. Ernst  Young kan niet garanderen dat een
 verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
 worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.
 
 Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
 vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
 en het origineel en eventuele kopieën te verwijderen en te vernietigen.
 
 Ernst  Young hanteert bij de uitoefening van haar werkzaamheden algemene
 voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
 algemene voorwaarden worden u op verzoek kosteloos toegezonden.
 =
 The information contained in this communication is confidential and is
 intended solely for the use of the individual or entity to whom it is
 addressed. You should not copy, disclose or distribute this communication
 without the authority of Ernst  Young. Ernst  Young is neither liable for
 the proper and complete transmission of the information contained in this
 communication nor for any delay in its receipt. Ernst  Young does not
 guarantee that the integrity of this communication has been maintained nor
 that the communication is free of viruses, interceptions or interference.
 
 If you are not the intended recipient of this communication please return
 the communication to the sender and delete and destroy all copies.
 
 In carrying out its engagements, Ernst  Young applies general terms and
 conditions, which contain a clause that limits its liability. A copy of
 these terms and conditions is available on request free of charge.
 ===
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Jack van Zanen
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Ruth Gramolini
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet

Re: Exchanging partition takes a lot of times

2002-04-12 Thread paquette stephane


Thanks for replying.

I've seen your post on a similar question on metalink.
I've already tested with the constraints enabled
novalidate using the default exchange mode (with
validation). The exchange is taking 3 seconds. 
We'll go this way since we're replacing completely the
target tables with the staging ones and we're doing
all the test on the staging tables before switching.

Regards




 --- Jonathan Lewis [EMAIL PROTECTED] a
écrit :  
 
 See the book - chapter 12, page 250.
 It's normal behaviour. There is one bizarre
 SQL run if you do the exchange
 without validation and another (usually cheaper)
 if you do it with validation.
 
 This relates to checking primary and unique
 keys, rather than the partitioning constraint.
 
 The solution/workaround is to set the constraints
 to a RELY ENABLE NOVALIDATE. But the last
 time I checked you still got problems with
 partitioned
 tables in involved in parent/child relationships.
 
 
 
 Jonathan Lewis
 http://www.jlcomp.demon.co.uk
 
 Host to The Co-Operative Oracle Users' FAQ
 http://www.jlcomp.demon.co.uk/faq/ind_faq.html
 
 Author of:
 Practical Oracle 8i: Building Efficient Databases
 
 
 -Original Message-
 To: Multiple recipients of list ORACLE-L
 [EMAIL PROTECTED]
 Date: 11 April 2002 17:17
 
 
 I'm testing the exchange partition and it's taking
 90
 seconds to exchange a table containing 700 000 rows
 with a partition containing also 700 000 rows.
 
 I've noticed that SYS is doing a crazy select to
 check
 on the PK of the tables even if I used whitout
 validation in the exchange statement.
 
 I this normal behavior ?
 
 
 =
 Stéphane Paquette
 
 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
 -- 
 Author: Jonathan Lewis
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX:
 (858) 538-5051
 San Diego, California-- Public Internet
 access / Mailing Lists


 To REMOVE yourself from this mailing list, send an
 E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of
 'ListGuru') and in
 the message BODY, include a line containing: UNSUB
 ORACLE-L
 (or the name of mailing list you want to be removed
 from).  You may
 also send the HELP command for other information
 (like subscribing). 

=
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
[EMAIL PROTECTED]

___
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?paquette=20stephane?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Rman, Duplicating without set untill.... cloning with

2002-04-12 Thread Ruth Gramolini


Make sure you build the controlfiles on the clone with noarchivelog.  That
will keep you from having the problem of set until time.

Ruth
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, April 12, 2002 11:59 AM


I think your theory is correct. I have the same experience, I have to
use SET UNTIL, otherwise RMAN complains about the archivelogs.  Oracle
8.1.7 on IBM AIX.

Robin
Jay Hostetter wrote:

 My experience has been that RMAN will try to restore the database right up
to the current point-in-time of the production database.  So if you are on a
remote system, it will be looking for data in the current redo logs (or logs
that were archived since the backup), which it can't access.Oracle
claims that RMAN will restore the last backup, but I think it is trying to
bring the duplicated DB in sync with production.

 I am on 8.1.7 on Tru64.  I haven't investigated this enough to prove the
theory, so I could be wrong.  All I know is that if I don't use SET UNTIL
when duplicating a database, RMAN complains about missing archivelogs.

 Jay

  [EMAIL PROTECTED] 04/12/02 10:13AM 
 I duplicate without at set until time using 8.0.6.3.  I follow the
 instructions from Metalink.  I duplicate our production databases to
another
 box and change the names from prd to rpt.   I do clone all of the
 tablespaces.

 Ruth
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Friday, April 12, 2002 5:58 AM
 tablespaces

 Hi All,

 I'm currently in the middle of a tar with Oracle about duplicating without
 set until time/scn According to the doc's it must be possible but when
 trying I get an error message about an archive log file that it needs but
 is not yet there.
 Any of you duplicating without set untill time/scn etc.. w/o problems

 if so what version RMAN/Oracle What OS/version
 What sequence of actions do you perform?

 Second I try to find the rman alternative to cloning just a part of the
 database but can't find anything in the Doc's
 Anybody

 TIA

 Jack

 ===
 De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
 uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
 vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
 derden is, behoudens voorafgaande schriftelijke toestemming van Ernst 
 Young, niet toegestaan. Ernst  Young staat niet in voor de juiste en
 volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
 voor tijdige ontvangst daarvan. Ernst  Young kan niet garanderen dat een
 verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
 worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

 Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
 vriendelijk doch dringend het e-mailbericht te retourneren aan de
verzender
 en het origineel en eventuele kopieën te verwijderen en te vernietigen.

 Ernst  Young hanteert bij de uitoefening van haar werkzaamheden algemene
 voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
 algemene voorwaarden worden u op verzoek kosteloos toegezonden.
 =
 The information contained in this communication is confidential and is
 intended solely for the use of the individual or entity to whom it is
 addressed. You should not copy, disclose or distribute this communication
 without the authority of Ernst  Young. Ernst  Young is neither liable
for
 the proper and complete transmission of the information contained in this
 communication nor for any delay in its receipt. Ernst  Young does not
 guarantee that the integrity of this communication has been maintained nor
 that the communication is free of viruses, interceptions or interference.

 If you are not the intended recipient of this communication please return
 the communication to the sender and delete and destroy all copies.

 In carrying out its engagements, Ernst  Young applies general terms and
 conditions, which contain a clause that limits its liability. A copy of
 these terms and conditions is available on request free of charge.
 ===

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

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

Re: insert performance

2002-04-12 Thread Paul Baumgartel


No.  CPU utilitization averages 40-60% across 4 CPUs.

--- Mohammed Shakir [EMAIL PROTECTED] wrote:
 No enough inserts to bog down the CPUs?
 
 --- Paul Baumgartel [EMAIL PROTECTED] wrote:
  Thanks, Mohammed and Anjo, for your replies.
  
  Now my question is this: given that the table structures
 (freelists,
  etc.) are identical on the two machines, the init.ora parameters
 are
  identical for the two instances, and the machines themselves are
  nearly
  identical (one has 6 CPUs, one 4, but in neither case are the CPUs
  anywhere near maxed out), what could be causing the discrepancy?
  
  PB
  
  --- Mohammed Shakir [EMAIL PROTECTED] wrote:
   data block waits is the indicator of freelists contention. I have
   never
   seen a freelists contention, even though I have been running 11
   processing doing inserts using 11 CPUs. I have seen library
 cache,
   Shared pool and cache buffer chains waits. I took care of buffer
  busy
   waits and db file sequential reads by increasing freelists,
  initrans
   to
   11 and by partitioning the disks. In my case most of the data was
   writting to the same block by as many as 9 processes. I am
 working
  on
   a
   
   Sparc 4500 Solaris 2.1.6 with Oracle 8.1.6.0 and 8.1.7.2
   
   Library Shared Pool latch contention was found due to Literal or
   non-shared SQL. Check SQL_text in SQLarea to find literal SQL.
  Shared
   Pool contention seems to be due to a very large Shared pool. I
  found
   a
   bug report that indicates that cache buffer chain problem was
 fixed
   in
   8.1.3.4 and 9.0.1.3
   
   Cache buffer chain is also an indicator of high physical and
  logical
   I/O. You can check on that as well.
   
   Hope this helps.
   
   Shakir
   
   --- Paul Baumgartel [EMAIL PROTECTED] wrote:
Greetings!

I am trying to diagnose a performance difference between two
databases
running the same test.  They are similarly configured (same SGA
   size,
etc.), and the servers are identical except for the number of
  CPUs
(server A has 4, server B has 6).

On database A, INSERT performance is about 190 rows/second.

On database B, INSERT performance is over 500 rows/second.

I saw some cache buffers chains, buffer busy, and library cache
   latch
waits on database A while the test was running, as well as redo
  log
sync waits.  The waits didn't seem excessive, though.  I
 checked
   for
checkpoint not complete redo allocation messages in database
  A's
alert log and found none.  The db_block_lru_latches parameter
 is
   set
to
one-half the number of CPUs in both machines.  

I'd much appreciate any suggestions as to what else to check. 
 I
   know
that freelists can be an issue (there are multiple sessions
performing
inserts); how can I check to see if there's freelist
 contention? 
Anything else I should investigate?

Many TIA,





=
Paul Baumgartel, Adept Computer Associates, Inc.
[EMAIL PROTECTED]





__
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858)
  538-5051
San Diego, California-- Public Internet access /
 Mailing
Lists
   
  
 
 
To REMOVE yourself from this mailing list, send an E-Mail
 message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
 and
  in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You
  may
also send the HELP command for other information (like
   subscribing).
   
   
   =
   Mohammed Shakir
   CompuSoft, Inc.
   11 Heather Way
   East Brunswick, NJ 08816-2825
   (732) 672-0464 (Cell)
   (732) 257-6001 (Home)
   
   __
   Do You Yahoo!?
   Yahoo! Tax Center - online filing with TurboTax
   http://taxes.yahoo.com/
   -- 
   Please see the official ORACLE-L FAQ: http://www.orafaq.com
   -- 
   Author: Mohammed Shakir
 INET: [EMAIL PROTECTED]
   
   Fat City Network Services-- (858) 538-5051  FAX: (858)
 538-5051
   San Diego, California-- Public Internet access / Mailing
   Lists
  
 
 
   To REMOVE yourself from this mailing list, send an E-Mail message
   to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
 in
   the message BODY, include a line containing: UNSUB ORACLE-L
   (or the name of mailing list you want to be removed from).  You
 may
   also send the HELP command for other information (like
  subscribing).

Re: bytes per extent

2002-04-12 Thread Ray Stell




There are lots of versions of this around, but this reports 
the sum of the allocated extents.  My problem is that the 
creater of this db made a huge extent and I don't seem to 
have a way to track growth within the extent.  




On Thu, Apr 11, 2002 at 07:13:31AM -0800, Ruth Gramolini wrote:
 Here is a script that I got from another lister.  Hope it is what you're
 looking for.
 
 column tsname format a25 heading 'Tablespace Name'
 column tot format 99,999,999 heading 'Size (K)'
 column fsp Format 99,999,999 heading 'Free (K)'
 column csp Format 999,999 heading 'Free|Extents'
 column msp Format 9,999,999 heading 'Max Free|Ext (K)'
 column pctused Format 999.99 heading '% Used'
 
 column tsno noprint
 compute sum of tot fsp on report
 break on report
 
 select
   fi.tablespace_name  tsname,
   sum(fi.bytes)/1024 tot,
   iv.free/1024  fsp,
   ((SUM(fi.bytes)-iv.free)/SUM(fi.bytes))*100 pctused,
   iv.no_of_exts csp,
   iv.max/1024 msp
 from
   dba_data_files fi,
   (
 select
   t.tablespace_name,
   NVL(MAX(f.bytes),0) max,
   NVL(sum(f.bytes),0) free,
   count(f.bytes) no_of_exts
 from
   sys.dba_free_space f,
   sys.dba_tablespaces t
 where
   t.tablespace_name=f.tablespace_name(+)
   and t.status != 'INVALID'
 group by
   t.tablespace_name
   ) iv
 where
   fi.tablespace_name = iv.tablespace_name
 GROUP BY
   fi.tablespace_name,
   iv.free,
   iv.no_of_exts,
   iv.max
 ORDER BY
   fi.tablespace_name
 /
 
 clear breaks
 clear columns
 clear computes
 
 
 Regards,
 Ruth
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Thursday, April 11, 2002 10:39 AM
 
 
  Is there a query to get the number of bytes used and free
  in an each extent?
  ===
  Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Ray Stell
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
  San Diego, California-- Public Internet access / Mailing Lists
  
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Ruth Gramolini
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).

-- 
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ray Stell
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Rule based engine

2002-04-12 Thread John Kanagaraj


Not really required. This is clearly explained in both Oracle 8i and Orace
9i manuals:

Oracle8i Designing and Tuning for Performance - Chapter 4 The optimizer
Oracle9i Database Performance Guide and Reference - Chapter 8 Using the
Rule-Based Optimizer

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Grace - Getting something we don't deserve
Mercy - NOT getting something we deserve

Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!

** The opinions and statements above are entirely my own and not those of my
employer or clients **


-Original Message-
Sent: Thursday, April 11, 2002 11:33 PM
To: Multiple recipients of list ORACLE-L


Check Oracle 7.3 documentation
-Original Message-
Sent: Friday, April 12, 2002 1:58 AM
To: Multiple recipients of list ORACLE-L



Does anybody know what algorithms a rule based engine uses.?
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: protocol.ora

2002-04-12 Thread Jeremiah Wilton


If you trace the listener you will see that it is looking for
.protocol.ora with a leading period.  This is a documented bug.  Just
copy protocol.ora to .protocol.ora.

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

On Fri, 12 Apr 2002, Sajid Iqbal wrote:

 We used to use the protocol.ora file to restrict access to the database,
 on oracle 8.0.5
 
 However since moving to Oracle 8.1.6.3  this doesn't work.

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Solaris 2.8

2002-04-12 Thread Nguyen, David M


I have Oracle8 running on solaris8 for more than a year and don't see any
problem.


David

-Original Message-
Sent: Friday, April 12, 2002 10:26 AM
To: Multiple recipients of list ORACLE-L


Hi friends and gurus,

Anybody has had (or has heard from friends of) any bad experience with
Oracle 8 running on Solaris 8.  I know that the first supported version on
Solaris 8 is 8.0.5.  If you know of some I would appreciate f you could
share it, and specify which version and patch release.

Thanks a lot.

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nguyen, David M
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: 1Z0-007 Exam










Ken,

If this is the 9i upgrade exam then there was a
discussion on this recently (about 3-4 weeks ago)

Search the archives for a post by Mike Hateley and
responses from Robert Freeman.

If is not the 9i upgrade which exam is it?



John



-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of KENNETH
JANUSZ
Sent: 12 April 2002 16:26
To: Multiple recipients of list
ORACLE-L
Subject: 1Z0-007 Exam



Has anyone
taken this exam? If so, I would like your feedback.



Thanks,

Ken Janusz,
CPIM

Re: SQL statement with hints or without hints




Instructing people to put hints in all of their code is the same as 
usingCBO full-time, because CBO hints automatically enable the CBO. 
Using CBOfull-time and not analyzing everything is asking for 
trouble...Using RBO is unnecessary if you are using Oracle8 v8.0 or 
above. The CBOoutperforms RBO in any situation except queries against 
the data dictionary(because you cannot analyze the data dictionary). 
Please read my paper"Search for Intelligent Life in the CBO" (online 
atwww.evdbt.com/library.htm) 
for an explanation as to why it is crucial to setOPTIMIZER_MODE=CHOOSE (not 
FIRST_ROWS or ALL_ROWS) and use theOPTIMIZER_INDEX_CACHING and (cautiously!) 
the OPTIMIZER_INDEX_COST_ADJparameters. Especially in Oracle8i, the 
CBO chooses dramatically superioraccess plans over the 
RBO...Embedding hints in SQL will eventually cripple your system as 
conditionschange, as will continued use of the RBO. Instructing people 
to embed hintson a wholesale basis is not the correct approach, in my 
opinion. Instead,I'd recommend setting the above-mentioned parameters 
appropriately (read thepaper!), analyzing everything (use the 
GATHER_xxx_STATS procedures in theDBMS_STATS package if using Oracle8i or 
above), and trust the CBO. Themyths about it "not working" or "not 
being trustworthy" have not been truefor years. For situations where 
it doesn't choose an index where you thinkit should (or mistakenly chooses 
one that you think it should't), consideranalyzing involved columns also to 
deal with any possible data skew problemsby creating "histograms".As 
Dr. Evil says, "I'm the boss. I need the info". Give the CBO the 
infoand it will choose the right plan. Like that analogy? I 
do... :-)I know the CBO works because I have made a living out of 
tuning Oracle-basedapplications for the past 6-7 years straight, working for 
Oracle and lateras an independent. Lately (i.e. past 2 years or so), 
SQL tuning has mainlyconsisted of *removing* hints (both the old "pre-CBO" 
hints like "+0" and"||''" as well as CBO hints) and analyzing appropriately 
(i.e. "need theinfo!"). Nothing gets your attention like something 
that affects yourwallet, so I've been betting my hourly income on these 
facts. I would havenoticed if I was wrong by now... :-) 
"Would you like fries with that,ma'am?"Hints should be used only as 
they were originally intended: infrequent useto deal with the 
extremely rare circumstances when the CBO cannot choose thebest path. 
Nothing is perfect, but the CBO in Oracle8i and above is thebest there 
is...- Original Message -From: "Jamadagni, Rajendra" [EMAIL PROTECTED]To: 
"Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Friday, 
April 12, 2002 8:03 AMSubject: SQL statement with hints or without 
hints Hi all, We are running a kind of hybrid 
mode, mainly RBO with some tables analyzed (mainly for intermedia). Last 
week we had an interesting situation, when tuning a huge SQL, we created 
an index and the query worked fine, butlater in the day developers 
complained that their queries are running slower. We finally 
took out the new index and things were fine after that, butthis 
brought out few important issues, 1. Developers do not put hints 
in their SQL statements. 2. They implicitly reply on a set of indexes 
that have worked for them. 3. Due to reasons of 1 and 2, no new indexes 
can be created because itwill make their queries run slower. 
4. As an effect of 3, new queries that can't make use of these set 
indexes will always be slower. Some of these queries can't be rewritten 
to makeuse of the existing indexes due to business logic 
involved. My question is, do you, in your organization recommend 
putting hints inthe SQLs all the time, some times or not at all 
?? It doesn't really matterif you use RBO or 
CBO. When the developers questioned me, I told them 'Oracle 
optimizer is not an exact science, especially in a mixed RBO/CBO mode, 
so it is bound to make some wrong choices and that is precisely why 
Oracle calls these things as "hints", so we tell the optimizer to do the 
right thing." I am in a good mood to write a short note 
explaining developers why they (must) use hints in their SQL 
statements. Your input is greatly appreciated 
TIA Raj 
__ Rajendra 
Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com 
Any opinion expressed here is personal and doesn't reflect that of 
ESPNInc. QOTD: Any clod can have facts, but having an 
opinion is an art!

Re: OEM Error On Unix

2002-04-12 Thread Suzy Vordos



Check Metalink Note:114959.1.  Also check if all of the OEM components
were installed by running the OUI, sounds like something is missing
because on Unix you should have OEM Client, Console, and OMS.

Reddy, Madhusudana wrote:
 
 No I was trying to start the OEM console , which requires OMS, but I do not
 have it on HP Unix server.
 So I have installed OEM on Windows and now able to work with console from my
 m/c.
 
 Thanks for suggestions , but not much help
 Thanks
 Madhu
 
 -Original Message-
 Sent: Thursday, April 11, 2002 5:57 PM
 To: Multiple recipients of list ORACLE-L
 
 Wouldn't it be:  oemapp dbastudio 
 
 Reddy, Madhusudana wrote:
 
  $ /usr/local/bin/sudo find / -name *oem* -print
 
 /usr/local/oracle8i/disk1/stage/Components/oracle.sysman.emcommon/2.2.0.0.0/
  1/DataFiles/Expanded/Scripts/oemapp
  /db01/app/oracle/product/8.1.7/bin/oemapp
  $
 
  The above will confirm that , I do not have oemctrl on my Oracle Client on
  HP Unix machine.
 
  Any idea how to get that stuff , do I need to install anything else here
   like OMS , if so where can I get it
 
  Thanks,
  Madhu
 
  -Original Message-
  Sent: Thursday, April 11, 2002 3:13 PM
  To: Multiple recipients of list ORACLE-L
 
  Try to do
 
  $which oemctrl
 
  or
 
  $find / -name *oem* -print
 
  Someone on the distro might add in comments
 
  David
 
  -Original Message-
  Sent: Thursday, April 11, 2002 1:04 PM
  To: Multiple recipients of list ORACLE-L
 
  I could not see this on HP-UNIX client ( $OH/bin ) where I have installed
  all the Oracle Client ( Administration ),
  Would you let me know the location , where I can find this on UNIX
 
  --Madhu
 
  -Original Message-
  Sent: Thursday, April 11, 2002 12:29 PM
  To: Multiple recipients of list ORACLE-L
 
  Try
 
  oemctrl start oms
 
  -Original Message-
  Sent: Thursday, April 11, 2002 11:26 AM
  To: Multiple recipients of list ORACLE-L
 
  Hell All,
  I am seeing the following error , when I am trying to start OEM console on
  HP Unix. Any Idea , what I am missing and would like to know , what is
 this
  Management server and how to start it on HP Unix .. Thanks in advance
 
   ...OLE_Obj...
 
  Thanks,
  Madhu V Reddy
  Database Support Services
  (952) 324-0392 ( work )
 
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Reddy, Madhusudana
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
  San Diego, California-- Public Internet access / Mailing Lists
  
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Nguyen, David M
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
  San Diego, California-- Public Internet access / Mailing Lists
  
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
 
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Reddy, Madhusudana
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
  San Diego, California-- Public Internet access / Mailing Lists
  
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Nguyen, David M
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
  San Diego, California-- Public Internet access / Mailing Lists
  
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information

Re: Rman, Duplicating without set untill.... cloning with

2002-04-12 Thread Jay Hostetter


Ruth,

  When I startup nomount my clone, no files exist, except the init.ora.  I then 
duplicate using:

run {
set until time to_date('040120020214','mmddhh24mi');
allocate auxiliary channel ch1 type disk;
set newname for datafile 1 to '/u03/oradata/BSCSD/system01.dbf';
...yadda yadda...
duplicate target database to BSCSD
logfile
group 1 ('/u03/oradata/BSCSD/redo1a.log','/u04/oradata/BSCSD/redo1b.log') size 25m,
group 2 ('/u03/oradata/BSCSD/redo2a.log','/u04/oradata/BSCSD/redo2b.log') size 25m,
group 3 ('/u03/oradata/BSCSD/redo3a.log','/u04/oradata/BSCSD/redo3b.log') size 25m;
}

  The manual states that duplicate creates the control files.  Can you tell me more 
about creating the controlfiles in noarchivelog?

Thanks,
Jay

 [EMAIL PROTECTED] 04/12/02 12:14PM 
Make sure you build the controlfiles on the clone with noarchivelog.  That
will keep you from having the problem of set until time.

Ruth
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, April 12, 2002 11:59 AM


I think your theory is correct. I have the same experience, I have to
use SET UNTIL, otherwise RMAN complains about the archivelogs.  Oracle
8.1.7 on IBM AIX.

Robin
Jay Hostetter wrote:

 My experience has been that RMAN will try to restore the database right up
to the current point-in-time of the production database.  So if you are on a
remote system, it will be looking for data in the current redo logs (or logs
that were archived since the backup), which it can't access.Oracle
claims that RMAN will restore the last backup, but I think it is trying to
bring the duplicated DB in sync with production.

 I am on 8.1.7 on Tru64.  I haven't investigated this enough to prove the
theory, so I could be wrong.  All I know is that if I don't use SET UNTIL
when duplicating a database, RMAN complains about missing archivelogs.

 Jay

  [EMAIL PROTECTED] 04/12/02 10:13AM 
 I duplicate without at set until time using 8.0.6.3.  I follow the
 instructions from Metalink.  I duplicate our production databases to
another
 box and change the names from prd to rpt.   I do clone all of the
 tablespaces.

 Ruth
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Friday, April 12, 2002 5:58 AM
 tablespaces

 Hi All,

 I'm currently in the middle of a tar with Oracle about duplicating without
 set until time/scn According to the doc's it must be possible but when
 trying I get an error message about an archive log file that it needs but
 is not yet there.
 Any of you duplicating without set untill time/scn etc.. w/o problems

 if so what version RMAN/Oracle What OS/version
 What sequence of actions do you perform?

 Second I try to find the rman alternative to cloning just a part of the
 database but can't find anything in the Doc's
 Anybody

 TIA

 Jack

 ===
 De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
 uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
 vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
 derden is, behoudens voorafgaande schriftelijke toestemming van Ernst 
 Young, niet toegestaan. Ernst  Young staat niet in voor de juiste en
 volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
 voor tijdige ontvangst daarvan. Ernst  Young kan niet garanderen dat een
 verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
 worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

 Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
 vriendelijk doch dringend het e-mailbericht te retourneren aan de
verzender
 en het origineel en eventuele kopieën te verwijderen en te vernietigen.

 Ernst  Young hanteert bij de uitoefening van haar werkzaamheden algemene
 voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
 algemene voorwaarden worden u op verzoek kosteloos toegezonden.
 =
 The information contained in this communication is confidential and is
 intended solely for the use of the individual or entity to whom it is
 addressed. You should not copy, disclose or distribute this communication
 without the authority of Ernst  Young. Ernst  Young is neither liable
for
 the proper and complete transmission of the information contained in this
 communication nor for any delay in its receipt. Ernst  Young does not
 guarantee that the integrity of this communication has been maintained nor
 that the communication is free of viruses, interceptions or interference.

 If you are not the intended recipient of this communication please return
 the communication to the sender and delete and destroy all copies.

 In carrying out its engagements, Ernst  Young applies general terms and
 conditions, which contain a clause that limits its liability. A copy of
 these terms and conditions

RE: Developer access in test database


Suzy,
You are much too generous.
I always have found that asking for a change control form delays things by a
few days!!
John

-Original Message-
Sent: 12 April 2002 18:43
To: Multiple recipients of list ORACLE-L


I generally give developers access only to the schema's they are
developing for.  In some cases, they might also get select_catalog_role.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suzy Vordos
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Developer access in test database

2002-04-12 Thread Suzy Vordos



I generally give developers access only to the schema's they are
developing for.  In some cases, they might also get select_catalog_role.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suzy Vordos
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL statement with hints or without hints


I worked at one consultancy/software house where you were not supposed to
use hints under any circumstance. This was because they wanted to make the
code truly portable (I believe SQL Server ignores Oracle hints as if they
were comments anyway, correct me if I am wrong).
I found this very frustrating and kept fixing code using hints to achieve
good performance to prove that it was achievable.
However I firmly believe that Oracle provided hints and therefore you should
use them if they improve performance.

I think it is harder to argue against them than it is to support their use.

John

-Original Message-
Sent: 12 April 2002 16:05
To: Multiple recipients of list ORACLE-L

Hi Raj, my two cents, fwiw:

Interesting.  I have worked places where hints were frowned upon (why is
Oracle not doing it's job?  Hey, you, DBA, you fix it.  No, I don't like
your hint.  Fix it)

However I feel the opposite way.  I have also worked in very liberal
environments (make it work! now!).  I truly believe that we, as
developers, know the data better than the optimizer.  Don't get me wrong,
not all sql statements require hints, but there are statements that will
benefit from having them.  Determining which is which is the trick, based
upon application behavior.

I also think that a hybrid environment requires more assistance via hints
than pure oltp or dss.  Hybrids provide their own unique type of
challenge.

omg did I just refer to myself as a developer...

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
954-935-4117


 -Original Message-
 From: Jamadagni, Rajendra [SMTP:[EMAIL PROTECTED]]
 Sent: Friday, April 12, 2002 10:03 AM
 To:   Multiple recipients of list ORACLE-L
 Subject:  SQL statement with hints or without hints

 Hi all,

 We are running a kind of hybrid mode, mainly RBO with some tables analyzed
 (mainly for intermedia). Last week we had an interesting situation, when
 tuning a huge SQL, we created an index and the query worked fine, but
 later
 in the day developers complained that their queries are running slower.

 We finally took out the new index and things were fine after that, but
 this
 brought out few important issues,

 1. Developers do not put hints in their SQL statements.
 2. They implicitly reply on a set of indexes that have worked for them.
 3. Due to reasons of 1 and 2, no new indexes can be created because it
 will
 make their queries run slower.
 4. As an effect of 3, new queries that can't make use of these set indexes
 will always be slower. Some of these queries can't be rewritten to make
 use
 of the existing indexes due to business logic involved.

 My question is, do you, in your organization recommend putting hints in
 the
 SQLs all the time, some times or not at all ??  It doesn't really matter
 if
 you use RBO or CBO.

 When the developers questioned me, I told them 'Oracle optimizer is not an
 exact science, especially in a mixed RBO/CBO mode, so it is bound to make
 some wrong choices and that is precisely why Oracle calls these things as
 hints, so we tell the optimizer to do the right thing.

 I am in a good mood to write a short note explaining developers why they
 (must) use hints in their SQL statements.

 Your input is greatly appreciated
 TIA
 Raj
 __
 Rajendra JamadagniMIS, ESPN Inc.
 Rajendra dot Jamadagni at ESPN dot com
 Any opinion expressed here is personal and doesn't reflect that of ESPN
 Inc.

 QOTD: Any clod can have facts, but having an opinion is an art!
   File: ESPN_Disclaimer.txt 
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Koivu, Lisa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL statement with hints or without hints


Thanks Tim,

At-least now I know I shouldn't tell them to start putting hints. This was
my problem to tell them or not to tell them. This still doesn't solve the
problem for me though, as everyone is still reluctant to move to CBO. And
this is exactly where it gets getting complicated.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!


***1

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 ESPN at (860) 766-2000 and 
delete this e-mail message from your computer, Thank you.

***1

RE: Solaris 2.8


David,
Solaris 2.8 (why they call it Version 8 and specify it as 2.8 I don't know)
is in use for production at a lot of sites. I am sure there are bugs and
problems with it the same as any other O/S but generally I think it is a
safe bet to install Oracle on Solaris 2.8 .
Just ensure you check Metalink for the correct/supported version of Oracle.

John

-Original Message-
Sent: 12 April 2002 18:53
To: Multiple recipients of list ORACLE-L

I have Oracle8 running on solaris8 for more than a year and don't see any
problem.


David

-Original Message-
Sent: Friday, April 12, 2002 10:26 AM
To: Multiple recipients of list ORACLE-L


Hi friends and gurus,

Anybody has had (or has heard from friends of) any bad experience with
Oracle 8 running on Solaris 8.  I know that the first supported version on
Solaris 8 is 8.0.5.  If you know of some I would appreciate f you could
share it, and specify which version and patch release.

Thanks a lot.

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nguyen, David M
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

consistent reads during inserts

2002-04-12 Thread Paul Baumgartel


Hi, all.  Sorry to be such a pest, but I'd like to ask another question
about my insert performance problem.

An example of the trace output for the two machines is shown below. 
There are many other traces containing the same insert statements, and
the values are very similar:

Machine 1--acceptable performance



INSERT INTO TLMPCSV (
SRT_SCH_SYS_NR,BCD_LBL_REF_TE,LD_REF_NR,SVC_TYP_CD,
  REC_INS_TS )
VALUES
 ( :b1,:b2,:b3,NVL(:b4,'000'),SYSDATE  )


call count   cpuelapsed   disk  querycurrent   
rows
--- --   -- -- -- -- 
--
Parse0  0.00   0.00  0  0  0   
   0
Execute  10975 11.37  15.88  1907 131437   
   10975
Fetch0  0.00   0.00  0  0  0   
   0
--- --   -- -- -- -- 
--
total10975 11.37  15.88  1907 131437   
   10975

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67  (APP730LM01)   (recursive depth: 1)

Rows Execution Plan
---  ---
  0  INSERT STATEMENT   GOAL: CHOOSE

Machine 2--unacceptable performance



INSERT INTO TLMPCSV (
SRT_SCH_SYS_NR,BCD_LBL_REF_TE,LD_REF_NR,SVC_TYP_CD,
  REC_INS_TS )
VALUES
 ( :b1,:b2,:b3,NVL(:b4,'000'),SYSDATE  )


call count   cpuelapsed   disk  querycurrent   
rows
--- --   -- -- -- -- 
--
Parse0  0.00   0.00  0  0  0   
   0
Execute  11010 12.40  55.78  0   3903 134549   
   11010
Fetch0  0.00   0.00  0  0  0   
   0
--- --   -- -- -- -- 
--
total11010 12.40  55.78  0   3903 134549   
   11010

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 65  (APP730LM01)   (recursive depth: 1)

Rows Execution Plan
---  ---
  0  INSERT STATEMENT   GOAL: CHOOSE


Notice that, in each case, approximately 11,000 executions of the
insert statement used about 12 or so seconds of CPU time, got about
130,000 buffers in current mode, and caused little or no disk activity.
 The ELAPSED time, though, is 15 seconds vs. 55 seconds.  The only
other statistic that differs is query, which is about 900 on the fast
machine, and 4000 on the slow machine.  Query is defined as number of
buffers gotten for consistent read.  

So, I'm wondering why an insert needs buffers in consistent read mode,
and, as a follow-up, if my assumption is correct that consistent read
buffers are always obtained from a rollback segment.  Finally, would
any of you draw the conclusion that the difference in elapsed time
between these two is due to the difference in number of consistent
reads?

Thanks!




=
Paul Baumgartel, Adept Computer Associates, Inc.
[EMAIL PROTECTED]





__
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

UTL_FILE limitation

2002-04-12 Thread Kieran Murray


Hi, 
I'm trying to load a table with 37 columns, from a flat file. I'm getting
ORA-06502 error after about 400 rows have been added to the file. When I
read read from other tables this works fine (although they don't have as
many columns).  I've tried using the overloaded UTL_FILE.FOPEN procedure
(setting max_linesize to 32767) but to no avail.  The strange thing is, if I
run a wc -c on any of the lines created they average about 170 bytes, which
doesn't even come close to the 1023 bytes limit.  Would anyone know of a
workaround?

Cheers, 
Kieran Murray
Norkom Technologies,
43 Upper Mount Street,
Dublin 2, Ireland

P.S. relevant commands are :
fileid := utl_file.fopen(file_dir,file_name,'w',32767);
v_data := table data which is tab-delimited;
utl_file.put_line(fileid, v_data);
utl_file.fclose(fileid);

Table description is:
CREATE TABLE CDM_RESULTS_FILE ( 
  CUS_IDNUMBERNOT NULL, 
  BAN   NUMBERNOT NULL, 
  CTN   VARCHAR2 (12), 
  CNAME1VARCHAR2 (100), 
  CNAME2VARCHAR2 (100), 
  CMTITLE   VARCHAR2 (50), 
  CMADD1VARCHAR2 (50), 
  CMADD2VARCHAR2 (50), 
  CMCNTYVARCHAR2 (50), 
  CMCITYVARCHAR2 (50), 
  CMZIP VARCHAR2 (50), 
  CMSTATE   VARCHAR2 (50), 
  CATTN VARCHAR2 (50), 
  CWPH  VARCHAR2 (50), 
  CWPHEXVARCHAR2 (50), 
  CHPH  VARCHAR2 (50), 
  COTHPHON  VARCHAR2 (50), 
  SCORE NUMBER, 
  SCORE10   NUMBER, 
  SCORE20   NUMBER, 
  SCORE50   NUMBER, 
  SCORE100  NUMBER, 
  REASON1   NUMBER, 
  REASON2   NUMBER, 
  REASON3   NUMBER, 
  REASON4   NUMBER, 
  REASON5   NUMBER, 
  REASON6   NUMBER, 
  REASON7   NUMBER, 
  REASON8   NUMBER, 
  REASON9   NUMBER, 
  REASON10  NUMBER, 
  MCABA NUMBER, 
  MSRATANUMBER, 
  MSRTA NUMBER, 
  MSRACANUMBER, 
  MROAM NUMBER)




The information contained in this e-mail transmission is confidential
and may be privileged. It is intended only for the addressee(s) stated 
above.  If you are not an addressee, any use, dissemination, distribution,
publication, or copying of the information contained in this e-mail is 
strictly prohibited. If you have received this e-mail in error, please
immediately notify our IT Department by telephone at 353-1-6769333 
or e-mail [EMAIL PROTECTED] and delete the e-mail from your 
system.

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Rman, Duplicating without set untill.... cloning with

2002-04-12 Thread Ruth Gramolini


I always rebuild the controlfiles for the clone and make it noarchivelog.  I
am still on 8.0.6.3 so I can't duplicate the database, I have to fool rman
be renaming the clone to the orginal and restore.  First I so a backup
controlfile to trace on the clone, then I set name to the orginal and make
it noarchivlog.  I restore the to the renamed clone and then rename it back.

I know it's confusing...
Ruth
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, April 12, 2002 2:33 PM


Ruth,

  When I startup nomount my clone, no files exist, except the init.ora.  I
then duplicate using:

run {
set until time to_date('040120020214','mmddhh24mi');
allocate auxiliary channel ch1 type disk;
set newname for datafile 1 to '/u03/oradata/BSCSD/system01.dbf';
...yadda yadda...
duplicate target database to BSCSD
logfile
group 1 ('/u03/oradata/BSCSD/redo1a.log','/u04/oradata/BSCSD/redo1b.log')
size 25m,
group 2 ('/u03/oradata/BSCSD/redo2a.log','/u04/oradata/BSCSD/redo2b.log')
size 25m,
group 3 ('/u03/oradata/BSCSD/redo3a.log','/u04/oradata/BSCSD/redo3b.log')
size 25m;
}

  The manual states that duplicate creates the control files.  Can you tell
me more about creating the controlfiles in noarchivelog?

Thanks,
Jay

 [EMAIL PROTECTED] 04/12/02 12:14PM 
Make sure you build the controlfiles on the clone with noarchivelog.  That
will keep you from having the problem of set until time.

Ruth
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, April 12, 2002 11:59 AM


I think your theory is correct. I have the same experience, I have to
use SET UNTIL, otherwise RMAN complains about the archivelogs.  Oracle
8.1.7 on IBM AIX.

Robin
Jay Hostetter wrote:

 My experience has been that RMAN will try to restore the database right up

to the current point-in-time of the production database.  So if you are on a
remote system, it will be looking for data in the current redo logs (or logs
that were archived since the backup), which it can't access.Oracle
claims that RMAN will restore the last backup, but I think it is trying to
bring the duplicated DB in sync with production.

 I am on 8.1.7 on Tru64.  I haven't investigated this enough to prove the
theory, so I could be wrong.  All I know is that if I don't use SET UNTIL
when duplicating a database, RMAN complains about missing archivelogs.

 Jay

  [EMAIL PROTECTED] 04/12/02 10:13AM 
 I duplicate without at set until time using 8.0.6.3.  I follow the
 instructions from Metalink.  I duplicate our production databases to
another
 box and change the names from prd to rpt.   I do clone all of the
 tablespaces.

 Ruth
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Friday, April 12, 2002 5:58 AM
 tablespaces

 Hi All,

 I'm currently in the middle of a tar with Oracle about duplicating without
 set until time/scn According to the doc's it must be possible but when
 trying I get an error message about an archive log file that it needs but
 is not yet there.
 Any of you duplicating without set untill time/scn etc.. w/o problems

 if so what version RMAN/Oracle What OS/version
 What sequence of actions do you perform?

 Second I try to find the rman alternative to cloning just a part of the
 database but can't find anything in the Doc's
 Anybody

 TIA

 Jack

 ===
 De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
 uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
 vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
 derden is, behoudens voorafgaande schriftelijke toestemming van Ernst 
 Young, niet toegestaan. Ernst  Young staat niet in voor de juiste en
 volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
 voor tijdige ontvangst daarvan. Ernst  Young kan niet garanderen dat een
 verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
 worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

 Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
 vriendelijk doch dringend het e-mailbericht te retourneren aan de
verzender
 en het origineel en eventuele kopieën te verwijderen en te vernietigen.

 Ernst  Young hanteert bij de uitoefening van haar werkzaamheden algemene
 voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
 algemene voorwaarden worden u op verzoek kosteloos toegezonden.
 =
 The information contained in this communication is confidential and is
 intended solely for the use of the individual or entity to whom it is
 addressed. You should not copy, disclose or distribute this communication
 without the authority of Ernst  Young. Ernst  Young is neither liable
for
 the proper and complete transmission of the information contained in this

RE: SQL statement with hints or without hints

2002-04-12 Thread John Kanagaraj


Raj,

Keep in mind that the CBO will be defaulted whenever the following is
present:

· Partitioned tables and indexes
· Index-organized tables
· Reverse key indexes
· Function-based indexes
· SAMPLE clauses in a SELECT statement
· Parallel execution and parallel DML (i.e. presence of DEGREE on
Tables/Indexes)
· Star transformations
· Star joins
· Extensible optimizer
· Query rewrite (materialized views)
· Progress meter
· Hash joins
· Bitmap indexes and bitmap join indexes
· Partition Views
· Index skip scans

*And*, when this now-invoked CBO detects objects without statistics, it
'guesstimeates' them based on some ridiculous defaults, leading to
absolutely horrible paths. Methinks that is what you are observing 

Looking at the subject lines for papers at IOUG, this subject is going to be
dealt with severely :)

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Grace - Getting something we don't deserve
Mercy - NOT getting something we deserve

Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!

** The opinions and statements above are entirely my own and not those of my
employer or clients **


 -Original Message-
 From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]
 Sent: Friday, April 12, 2002 8:38 AM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: SQL statement with hints or without hints
 
 
 I am not sure, outlines do require CBO (me thinks) and we are 
 not there yet.
 Also there is a general resistance around here for that. Now 
 that you have
 asked me, I will probably take it up to my manager once we move to CBO
 completely (sometime before Universe stops expanding judging 
 by current
 support from within our group to migrate to CBO).
 
 Raj
 __
 Rajendra JamadagniMIS, ESPN Inc.
 Rajendra dot Jamadagni at ESPN dot com
 Any opinion expressed here is personal and doesn't reflect 
 that of ESPN Inc.
 
 QOTD: Any clod can have facts, but having an opinion is an art!
 
 
 -Original Message-
 Sent: Friday, April 12, 2002 10:18 AM
 To: 'Jamadagni, Rajendra'; '[EMAIL PROTECTED]'
 
 
 Raj, out of curiosity:  Why are outlines a no-no?  Please 
 elaborate.  I
 haven't used them and would be interested in your opinion.
 
 LK
 
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL statement with hints or without hints (LONG)

2002-04-12 Thread Toepke, Kevin M




 Using RBO is unnecessary if you are 
using Oracle8 v8.0 or above. The CBO outperforms RBO in any 
situation except queries against the data dictionary (because you cannot 
analyze the data dictionary). 

Never 
say never and Never say always.

I have 
found the above statement to be true except in one case, and that involves a bug 
that was introduced somewhere in the 8.1.5 tree and_almost_fixed in 
8.1.7.1. It is particularly nasty in the 8.1.6 tree and it appears to 
befixed in 9.0.1.2. I don't have the bug #, but the situation is 
follows:

1) You are joining multiple large tables 
together
 
-- The more  larger the 
tablesyou are joining, the worse the effects
2) One or more of the join columns is in the SELECT 
list
3) You are ordering by  1 of the join 
columns.
 
-- this can be an ORDER BY, GROUP BY or 
DISTINCT clause or even a UNION, INTERSECT or MINUS! Anything that causes a sort 
to occur on the join column

The 
CBO will choose to do SORT/MERGE joins (with full table scans) when any other 
join method is more efficient. 

Through normal hintingyou CAN NOT get the CBO to use nested loops 
with index range scans (that's part of the bug). If you specify the INDEX_ASC() 
and USE_NL() hints, the CBO will do an FAST FULL SCAN on the index on the 
specified index. 

If you 
move the unsorted query into an inline view and sort outside the inline view, 
you can get a near-optimal execution path -- and hinting works properly. 
However, if you just use the /*+ RULE */ hint, you will get better 
performancethan with the inline view method.

How do 
you determine if you are running into this bug? There are several ways, but 
thebest way is to run your query without your "sort" operation. If the 
query runs significantly faster without the sort than with, you may be hitting 
this bug.

An 
example:
 SELECT e.empno, e.deptid, dept.name, 
d.dependent_name
 FROM emp e, dept, dependent 
d
 WHERE e.deptid = dept.id
 AND emp.empno = d.empno
 ORDER BY e.empno, e.deptid;
Inline 
view method
 SELECT /*+ NO_MERGE(x) */ *
 FROM (
 
SELECT e.empno, e.deptid, dept.name, 
d.dependent_name
 FROM emp e, dept, 
dependent d
 
WHERE e.deptid = 
dept.id
 
AND emp.empno = d.empno) 
x

 ORDER BY empno, deptid;
Rule Hint:

 SELECT /*+ RULE */ e.empno, e.deptid, dept.name, 
d.dependent_name
 FROM emp e, dept, dependent 
d
 WHERE e.deptid = dept.id
 AND emp.empno = d.empno
 ORDER BY e.empno, e.deptid;

Even with this bug around, I would still 
highly recommend the CBO over the RBO. You just have to know the 
exceptions.

Caver

Re: Developer access in test database

2002-04-12 Thread Suzy Vordos



Guess I should clarify... developers only get access to dev, and for
test  prod they get nothing.  I've encountered very few with a
need/desire for select_catalog_role, and they have to ask first :)

John Hallas wrote:
 
 Suzy,
 You are much too generous.
 I always have found that asking for a change control form delays things by a
 few days!!
 John
 
 -Original Message-
 Sent: 12 April 2002 18:43
 To: Multiple recipients of list ORACLE-L
 
 I generally give developers access only to the schema's they are
 developing for.  In some cases, they might also get select_catalog_role.
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Suzy Vordos
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: John Hallas
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suzy Vordos
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: UTL_FILE limitation

2002-04-12 Thread Koivu, Lisa


Kieran, I have to wonder... why are you using utl_file instead of
sql*loader?  

Lisa Koivu
Oracle Database Tank
Fairfield Resorts, Inc.
954-935-4117


 -Original Message-
 From: Kieran Murray [SMTP:[EMAIL PROTECTED]]
 Sent: Friday, April 12, 2002 2:13 PM
 To:   Multiple recipients of list ORACLE-L
 Subject:  UTL_FILE limitation
 
 Hi, 
 I'm trying to load a table with 37 columns, from a flat file. I'm getting
 ORA-06502 error after about 400 rows have been added to the file. When I
 read read from other tables this works fine (although they don't have as
 many columns).  I've tried using the overloaded UTL_FILE.FOPEN procedure
 (setting max_linesize to 32767) but to no avail.  The strange thing is, if
 I
 run a wc -c on any of the lines created they average about 170 bytes,
 which
 doesn't even come close to the 1023 bytes limit.  Would anyone know of a
 workaround?
 
 Cheers, 
 Kieran Murray
 Norkom Technologies,
 43 Upper Mount Street,
 Dublin 2, Ireland
 
 P.S. relevant commands are :
 fileid := utl_file.fopen(file_dir,file_name,'w',32767);
 v_data := table data which is tab-delimited;
 utl_file.put_line(fileid, v_data);
 utl_file.fclose(fileid);
 
 Table description is:
 CREATE TABLE CDM_RESULTS_FILE ( 
   CUS_IDNUMBERNOT NULL, 
   BAN   NUMBERNOT NULL, 
   CTN   VARCHAR2 (12), 
   CNAME1VARCHAR2 (100), 
   CNAME2VARCHAR2 (100), 
   CMTITLE   VARCHAR2 (50), 
   CMADD1VARCHAR2 (50), 
   CMADD2VARCHAR2 (50), 
   CMCNTYVARCHAR2 (50), 
   CMCITYVARCHAR2 (50), 
   CMZIP VARCHAR2 (50), 
   CMSTATE   VARCHAR2 (50), 
   CATTN VARCHAR2 (50), 
   CWPH  VARCHAR2 (50), 
   CWPHEXVARCHAR2 (50), 
   CHPH  VARCHAR2 (50), 
   COTHPHON  VARCHAR2 (50), 
   SCORE NUMBER, 
   SCORE10   NUMBER, 
   SCORE20   NUMBER, 
   SCORE50   NUMBER, 
   SCORE100  NUMBER, 
   REASON1   NUMBER, 
   REASON2   NUMBER, 
   REASON3   NUMBER, 
   REASON4   NUMBER, 
   REASON5   NUMBER, 
   REASON6   NUMBER, 
   REASON7   NUMBER, 
   REASON8   NUMBER, 
   REASON9   NUMBER, 
   REASON10  NUMBER, 
   MCABA NUMBER, 
   MSRATANUMBER, 
   MSRTA NUMBER, 
   MSRACANUMBER, 
   MROAM NUMBER)
 
 
 
 
 The information contained in this e-mail transmission is confidential
 and may be privileged. It is intended only for the addressee(s) stated 
 above.  If you are not an addressee, any use, dissemination, distribution,
 publication, or copying of the information contained in this e-mail is 
 strictly prohibited. If you have received this e-mail in error, please
 immediately notify our IT Department by telephone at 353-1-6769333 
 or e-mail [EMAIL PROTECTED] and delete the e-mail from your 
 system.
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Kieran Murray
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Koivu, Lisa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Solaris 2.8

2002-04-12 Thread bill thater


[EMAIL PROTECTED] wrote:

 David,
 Solaris 2.8 (why they call it Version 8 and specify it as 2.8 I don't know)
 is in use for production at a lot of sites. I am sure there are bugs and
 problems with it the same as any other O/S but generally I think it is a
 safe bet to install Oracle on Solaris 2.8 .
 Just ensure you check Metalink for the correct/supported version of Oracle.


well, i'm running 7.3.4, 8.1.5, 8.1.6 and 8.1.7 on it with no problems 
and have been for a while.



-- 
--
Bill Shrek Thater  ORACLE DBA
 [EMAIL PROTECTED]

You gotta program like you don't need the money,
You gotta compile like you'll never get hurt,
You gotta run like there's nobody watching,
It's gotta come from the heart if you want it to work.

Error 13: Illegal brain function. Process terminated.




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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Yet another rman question

2002-04-12 Thread Freeman, Robert


 
Another (and hopefully final) Rman question for the community...
If you decided NOT to use Rman and you opted for either another 
product, can you share with me why you decided not to use Rman.
Was it the complexity, lack of documentation, or a really good
software salesman?
 
 
Robert G. Freeman - Oracle8i OCP
Oracle DBA Technical Lead
CSX Midtier Database Administration
Author Oracle9i New Features, Mastering Oracle8i

 

The Cigarette Smoking Man: Anyone who can appease a man's conscience can
take his freedom away from him.

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL statement with hints or without hints

2002-04-12 Thread Cherie_Machler



Raj,

I don't remember what version you said you were using but you might want to
investigate
stored outlines.   If you have a full-size test environment, you could save
away the current
RBO-based execution plans, switch to CBO and gradually get rid of your
RBO-based stored outlines by tuning statements one at a time.

Theoretically, that should work.   I haven't tried it myself, though.

Theoretically, it's a way to be on cost-based but continue to use some of
the the rule-based execution plans until your comfortable with the new
stuff.

Cherie Machler
Oracle DBA
Gelco Information Network


   
  
Jamadagni,
  
Rajendra To: Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
Rajendra.Jamadagni   cc:  
  
@espn.comSubject: RE: SQL statement with 
hints or without hints 
Sent by:   
  
[EMAIL PROTECTED]   
  
   
  
   
  
04/12/02 01:18 PM  
  
Please respond to  
  
ORACLE-L   
  
   
  
   
  




Thanks Tim,

At-least now I know I shouldn't tell them to start putting hints. This was
my problem to tell them or not to tell them. This still doesn't solve the
problem for me though, as everyone is still reluctant to move to CBO. And
this is exactly where it gets getting complicated.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN
Inc.

QOTD: Any clod can have facts, but having an opinion is an art!
(See attached file: ESPN_Disclaimer.txt)




ESPN_Disclaimer.txt
Description: Binary data

Re: IOUG Get-together

2002-04-12 Thread Stephane Faroult


John Kanagaraj wrote:
 
 
 If I have missed anyone - apologies, and please let me know! Charlie, can I
 request you to think about a nice place to meet later?
 

Apologies accepted. If anybody is interested in how to make their
applications search smartly information managed by a LDAP server (_NOT_
specifically OID which indeed I have never used) on a familiar example
which I have OpenLdaped for the occasion, they are welcome on Tuesday at
4:15. Especially young attractive women. But don't feel put off if you
don't fit the description :-).
 
-- 
Regards,

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL statement with hints or without hints

2002-04-12 Thread Mohammad Rafiq


Raj,
I shall comment on no 3.

1. Developers do not put hints in their SQL statements.
2. They implicitly reply on a set of indexes that have worked for them.
3. Due to reasons of 1 and 2, no new indexes can be created because it will 
make their queries run slower.

I had gone through the same situation 1 year back after adding a new index 
on Oracle Financial Application to expedite or compliment a badly wirtten 
code and that code written fine but one of AR application using forms screen 
  to add cash receipts become so slow (taking 10 minutes to move from a 
field instead of couple of seconds) and we have no option except to drop 
that index and things become normal but we have no option to change that 
form or put any hint. So under some situations we become helpless. Although 
I have been adding customized indexes to improve performance of certain 
Oracle Financials Reports like Accrual Rebuild REconcilation which was 
taking 30 hours and after adding 6 indexes the time went to 1.5 hours.


Regards
Rafiq




Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Fri, 12 Apr 2002 06:03:28 -0800

Hi all,

We are running a kind of hybrid mode, mainly RBO with some tables analyzed
(mainly for intermedia). Last week we had an interesting situation, when
tuning a huge SQL, we created an index and the query worked fine, but later
in the day developers complained that their queries are running slower.

We finally took out the new index and things were fine after that, but this
brought out few important issues,

1. Developers do not put hints in their SQL statements.
2. They implicitly reply on a set of indexes that have worked for them.
3. Due to reasons of 1 and 2, no new indexes can be created because it will
make their queries run slower.
4. As an effect of 3, new queries that can't make use of these set indexes
will always be slower. Some of these queries can't be rewritten to make use
of the existing indexes due to business logic involved.

My question is, do you, in your organization recommend putting hints in the
SQLs all the time, some times or not at all ??  It doesn't really matter if
you use RBO or CBO.

When the developers questioned me, I told them 'Oracle optimizer is not an
exact science, especially in a mixed RBO/CBO mode, so it is bound to make
some wrong choices and that is precisely why Oracle calls these things as
hints, so we tell the optimizer to do the right thing.

I am in a good mood to write a short note explaining developers why they
(must) use hints in their SQL statements.

Your input is greatly appreciated
TIA
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

 ESPN_Disclaimer.txt 




MOHAMMAD RAFIQ


_
Chat with friends online, try MSN Messenger: http://messenger.msn.com

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: bytes per extent

2002-04-12 Thread Browett, Darren


I am assumming the table is contained within one extent.

If you did an analyze table, wouldn't that show the amount of data (num_rows
* avg_row_len)
within the table (extent) which could be used to monitor growth.




-Original Message-
Sent: April 12, 2002 10:04 AM
To: Multiple recipients of list ORACLE-L




There are lots of versions of this around, but this reports 
the sum of the allocated extents.  My problem is that the 
creater of this db made a huge extent and I don't seem to 
have a way to track growth within the extent.  




On Thu, Apr 11, 2002 at 07:13:31AM -0800, Ruth Gramolini wrote:
 Here is a script that I got from another lister.  Hope it is what you're
 looking for.
 
 column tsname format a25 heading 'Tablespace Name'
 column tot format 99,999,999 heading 'Size (K)'
 column fsp Format 99,999,999 heading 'Free (K)'
 column csp Format 999,999 heading 'Free|Extents'
 column msp Format 9,999,999 heading 'Max Free|Ext (K)'
 column pctused Format 999.99 heading '% Used'
 
 column tsno noprint
 compute sum of tot fsp on report
 break on report
 
 select
   fi.tablespace_name  tsname,
   sum(fi.bytes)/1024 tot,
   iv.free/1024  fsp,
   ((SUM(fi.bytes)-iv.free)/SUM(fi.bytes))*100 pctused,
   iv.no_of_exts csp,
   iv.max/1024 msp
 from
   dba_data_files fi,
   (
 select
   t.tablespace_name,
   NVL(MAX(f.bytes),0) max,
   NVL(sum(f.bytes),0) free,
   count(f.bytes) no_of_exts
 from
   sys.dba_free_space f,
   sys.dba_tablespaces t
 where
   t.tablespace_name=f.tablespace_name(+)
   and t.status != 'INVALID'
 group by
   t.tablespace_name
   ) iv
 where
   fi.tablespace_name = iv.tablespace_name
 GROUP BY
   fi.tablespace_name,
   iv.free,
   iv.no_of_exts,
   iv.max
 ORDER BY
   fi.tablespace_name
 /
 
 clear breaks
 clear columns
 clear computes
 
 
 Regards,
 Ruth
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Thursday, April 11, 2002 10:39 AM
 
 
  Is there a query to get the number of bytes used and free
  in an each extent?
  ===
  Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Ray Stell
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
  San Diego, California-- Public Internet access / Mailing Lists
  
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Ruth Gramolini
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).

-- 
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ray Stell
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Browett, Darren
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like

RE: SQL statement with hints or without hints

2002-04-12 Thread Larry Elkins


Tim,

Have you ever found yourself needing to use the DBMS_STATS.SET_XXX_STATS
routines to get your desired plans? And if so, did you do this on a trial
and error basis, or did you use something like a 10053 trace to see inside
the CBO's head to help you determine what values to use?

Just curious. Something I've never done but am contemplating for a couple of
cases.

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
-Original Message-
Sent: Friday, April 12, 2002 1:03 PM
To: Multiple recipients of list ORACLE-L


Instructing people to put hints in all of their code is the same as using
CBO full-time, because CBO hints automatically enable the CBO.  Using CBO
full-time and not analyzing everything is asking for trouble...

Using RBO is unnecessary if you are using Oracle8 v8.0 or above.  The CBO
outperforms RBO in any situation except queries against the data dictionary
(because you cannot analyze the data dictionary).  Please read my paper
Search for Intelligent Life in the CBO (online at
www.evdbt.com/library.htm) for an explanation as to why it is crucial to set
OPTIMIZER_MODE=CHOOSE (not FIRST_ROWS or ALL_ROWS) and use the
OPTIMIZER_INDEX_CACHING and (cautiously!) the OPTIMIZER_INDEX_COST_ADJ
parameters.  Especially in Oracle8i, the CBO chooses dramatically superior
access plans over the RBO...

Embedding hints in SQL will eventually cripple your system as conditions
change, as will continued use of the RBO.  Instructing people to embed hints
on a wholesale basis is not the correct approach, in my opinion.  Instead,
I'd recommend setting the above-mentioned parameters appropriately (read the
paper!), analyzing everything (use the GATHER_xxx_STATS procedures in the
DBMS_STATS package if using Oracle8i or above), and trust the CBO.  The
myths about it not working or not being trustworthy have not been true
for years.  For situations where it doesn't choose an index where you think
it should (or mistakenly chooses one that you think it should't), consider
analyzing involved columns also to deal with any possible data skew problems
by creating histograms.

As Dr. Evil says, I'm the boss.  I need the info.  Give the CBO the info
and it will choose the right plan.  Like that analogy?  I do...  :-)

I know the CBO works because I have made a living out of tuning Oracle-based
applications for the past 6-7 years straight, working for Oracle and later
as an independent.  Lately (i.e. past 2 years or so), SQL tuning has mainly
consisted of *removing* hints (both the old pre-CBO hints like +0 and
||'' as well as CBO hints) and analyzing appropriately (i.e. need the
info!).  Nothing gets your attention like something that affects your
wallet, so I've been betting my hourly income on these facts.  I would have
noticed if I was wrong by now...  :-)  Would you like fries with that,
ma'am?

Hints should be used only as they were originally intended:  infrequent use
to deal with the extremely rare circumstances when the CBO cannot choose the
best path.  Nothing is perfect, but the CBO in Oracle8i and above is the
best there is...


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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: empty string = null?

2002-04-12 Thread MacGregor, Ian A.




The 
promise/threat to treat empty strings differently from nulls has been in the 
documentaion since at least Oracle 7.3.

Ian 
MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]

  -Original Message-From: Khedr, Waleed 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, April 11, 2002 6:33 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  empty string = null?
  I feel I had the same dream. Anyway this from Oracle 9i 
  doc:Nulls"If a column in a row has no value, then the column is 
  said to be null, or to contain a null. Nulls can appear in columns of any 
  datatype that are not restricted by NOTNULL or PRIMARY KEY integrity 
  constraints. Use a null when the actual value is not known or when a value 
  would not be meaningful.Do not use null to represent a value of zero, 
  because they are not equivalent. (Oracle currently treats a 
  character value with a length of zero as null. However, thismay not 
  continue to be true in future releases, and Oracle recommends that you do not 
  treat empty strings the same as nulls.) Any arithmetic expression 
  containing anull always evaluates to null. For example, null added to 10 
  is null. In fact, all operators (except concatenation) return null when given 
  a null operand."Regards,Waleed-Original 
  Message-From: Jonathan Gennick [mailto:[EMAIL PROTECTED]]Sent: 
  Thursday, April 11, 2002 8:44 PMTo: Multiple recipients of list 
  ORACLE-LSubject: empty string = null?Oracle has always treated 
  empty strings (e.g.: '') as nulls.As I sit here and think about this, I 
  can convince myselfthat I read somewhere that Oracle9i provided an option 
  totreat empty strings not as nulls, but truly as emptystrings. Is this 
  the case, or is my mind manufacturingmemories?Jonathan Gennick --- 
  Brighten the corner where you aremailto:[EMAIL PROTECTED]http://Gennick.com * http://MichiganWaterfalls.com *http://ValleySpur.com--Please see the official 
  ORACLE-L FAQ: http://www.orafaq.com--Author: Jonathan 
  Gennick INET: [EMAIL PROTECTED]Fat City Network 
  Services -- (858) 538-5051 FAX: (858) 538-5051San 
  Diego, California -- Public Internet 
  access / Mailing 
  ListsTo 
  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).

Re: bytes per extent

2002-04-12 Thread Ray Stell


On Fri, Apr 12, 2002 at 12:13:25PM -0800, Browett, Darren wrote:
 I am assumming the table is contained within one extent.
 
 If you did an analyze table, wouldn't that show the amount of data (num_rows
 * avg_row_len)
 within the table (extent) which could be used to monitor growth.



Yep, but I don't want an estimate, I want the byte count.

great metalink notes I found:

Note:116565.1 

Note:116923.1 






 -Original Message-
 Sent: April 12, 2002 10:04 AM
 To: Multiple recipients of list ORACLE-L
 
 
 
 
 There are lots of versions of this around, but this reports 
 the sum of the allocated extents.  My problem is that the 
 creater of this db made a huge extent and I don't seem to 
 have a way to track growth within the extent.  
 
 
 
 
 On Thu, Apr 11, 2002 at 07:13:31AM -0800, Ruth Gramolini wrote:
  Here is a script that I got from another lister.  Hope it is what you're
  looking for.
  
  column tsname format a25 heading 'Tablespace Name'
  column tot format 99,999,999 heading 'Size (K)'
  column fsp Format 99,999,999 heading 'Free (K)'
  column csp Format 999,999 heading 'Free|Extents'
  column msp Format 9,999,999 heading 'Max Free|Ext (K)'
  column pctused Format 999.99 heading '% Used'
  
  column tsno noprint
  compute sum of tot fsp on report
  break on report
  
  select
fi.tablespace_name  tsname,
sum(fi.bytes)/1024 tot,
iv.free/1024  fsp,
((SUM(fi.bytes)-iv.free)/SUM(fi.bytes))*100 pctused,
iv.no_of_exts csp,
iv.max/1024 msp
  from
dba_data_files fi,
(
  select
t.tablespace_name,
NVL(MAX(f.bytes),0) max,
NVL(sum(f.bytes),0) free,
count(f.bytes) no_of_exts
  from
sys.dba_free_space f,
sys.dba_tablespaces t
  where
t.tablespace_name=f.tablespace_name(+)
and t.status != 'INVALID'
  group by
t.tablespace_name
) iv
  where
fi.tablespace_name = iv.tablespace_name
  GROUP BY
fi.tablespace_name,
iv.free,
iv.no_of_exts,
iv.max
  ORDER BY
fi.tablespace_name
  /
  
  clear breaks
  clear columns
  clear computes
  
  
  Regards,
  Ruth
  - Original Message -
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Sent: Thursday, April 11, 2002 10:39 AM
  
  
   Is there a query to get the number of bytes used and free
   in an each extent?
   ===
   Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
   --
   Please see the official ORACLE-L FAQ: http://www.orafaq.com
   --
   Author: Ray Stell
 INET: [EMAIL PROTECTED]
  
   Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
   San Diego, California-- Public Internet access / Mailing Lists
   
   To REMOVE yourself from this mailing list, send an E-Mail message
   to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
   the message BODY, include a line containing: UNSUB ORACLE-L
   (or the name of mailing list you want to be removed from).  You may
   also send the HELP command for other information (like subscribing).
  
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  -- 
  Author: Ruth Gramolini
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
  San Diego, California-- Public Internet access / Mailing Lists
  
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
 
 -- 
 ===
 Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Ray Stell
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Browett, Darren
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists

RE: SQL statement with hints or without hints


Thanks Cherie and Rafiq,

I am on 8061, but probably directly jump to 9.2 (whenever that becomes
available that is, so far I have heard many dates).

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!


***1

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 ESPN at (860) 766-2000 and 
delete this e-mail message from your computer, Thank you.

***1

Archival Freeze

2002-04-12 Thread Rajesh . Rao



Hi Fellow DBAs,

I and a fellow DBA are currently debating about how insufficient space in
the archive destination freezes up the DB. He claims that as soon as the
ARCH process is unable to write to the disk, the db freezes. I am of the
opinion that it does not. It will only report an error (Any idea, what the
error code is?). It will only freeze when it cycles thru the rest of the
redolog groups, and then when it tries to switch log to the one which has
not been archived. That's why the error message is cannot allocate new
log.

Whats your say? He's also on this list.

Raj




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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

No DBAs needed on AS400

2002-04-12 Thread Brad Weiner




My view of the AS/400 will always be 
framed by one project I heard about.The Pepsi Bottling Group in the mid 
80s, was looking for a solution that would tie all of their branch offices 
around the country to the Bottling Group headquarters in Purchase NY. IBM of 
course sold them an AS/400 solution telling them, as they grew and needed more 
CPU all they had to do wasgo was buy another AS/400 and plug it in 
nextto the others. Also they wouldn't need any pricey systems programmers. 


Pepsi set up a few sites and discovered that 
the maximum (at the time) number of AS/400s that could sit side by side and 
function as one CPU was three. After that the overhead needed to keep them 
communicating and in syncwas more than the added CPU. Although they didn't 
need any system programmers after the initial install, they discovered that they 
needed 50% more communications programmers (and more expensive than system 
programmers). And finally they found out that if they wanted to customize a 
system due to unique regional conditions, it was far more difficult than any 
other system they had ever used. They ended up dumping the whole AS/400 system 
and replacing it with HP minicomputers, which was successful enough for HP to 
feature that Pepsi system in marketing literature (no, they didn't mention the 
AS/400).

I agree with most, it's time to look for a new 
job. This new boss will be nothing but headaches for you and by the time they 
realize what a mistake they made, you'll be long gone (and they'll find a way to 
blame you).

Brad Weiner

From: "Jay Hostetter" [EMAIL PROTECTED]Date: 
Thu, 11 Apr 2002 08:50:58 -0400Subject: No DBAs needed on 
AS400We are going through a merger, and management is looking to 
eliminate =positions. Here is a brief summary of my discussion with 
the new director =of IT:Director: "Back when I we were using an 
AS400, we didn't need a DBA."Me: "Then you probably were just using 
files."Director: "No, it was a database."Me: "Could you issue SQL 
commands?"Director: "Yes. But we didn't need a DBA. I guess it 
was just one of =those mysteries of life."My thoughts are that 
he is using the term "database" in the generic sense =of the word (our 
"files" are our database), or he was using some proprietar=y database that 
doesn't even begin to compare to Oracle.For those of you who know 
AS400s, I would appreciate some insight that =would demonstrate why he needs 
to keep me as a DBA.Thanks,Jay

RE: SQL statement with hints or without hints

2002-04-12 Thread John Kanagaraj


Hi Jonathan,

Yes - my phrasing was incorrect. The list was off the Tuning/Perf Guide, but
I should have phrased it correctly.

John

 -Original Message-
 From: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
 Sent: Friday, April 12, 2002 2:18 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Re: SQL statement with hints or without hints
 
 
 
 I think you have to be a little careful how
 you phrase your comment here.
 
 In your list there are some things which FORCE
 the use of CBO, even if the optimizer_mode is
 set to RULE; and there are some features which
 are activated ONLY IF the cost based optimizer
 is invoked.
 
 Off the top of my head, the following force CBO when
 they appear in an SQL statement
 
 · Partitioned tables and indexes
 · Index-organized tables
 · SAMPLE clauses in a SELECT statement
 · Parallel tables/indexes
 
 and the following are only recognised and used when
 the CBO has kicked in
 
 · Reverse key indexes
 · Function-based indexes
 · Query rewrite (materialized views)
 · Progress meter
 · Hash joins
 · Bitmap indexes and bitmap join indexes
 · Star transformations
 · Partition Views
 · Index skip scans
 
 and on these two I pass:
 · Star joins
 · Extensible optimizer
 
 
 Jonathan Lewis
 http://www.jlcomp.demon.co.uk
 
 Author of:
 Practical Oracle 8i: Building Efficient Databases
 
 Next Seminar - Australia - July/August
 http://www.jlcomp.demon.co.uk/seminar.html
 
 Host to The Co-Operative Oracle Users' FAQ
 http://www.jlcomp.demon.co.uk/faq/ind_faq.html
 
 
 
 -Original Message-
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Date: 12 April 2002 19:46
 
 
 Raj,
 
 Keep in mind that the CBO will be defaulted whenever the following is
 present:
 
 · Partitioned tables and indexes
 · Index-organized tables
 · Reverse key indexes
 · Function-based indexes
 · SAMPLE clauses in a SELECT statement
 · Parallel execution and parallel DML (i.e. presence of DEGREE on
 Tables/Indexes)
 · Star transformations
 · Star joins
 · Extensible optimizer
 · Query rewrite (materialized views)
 · Progress meter
 · Hash joins
 · Bitmap indexes and bitmap join indexes
 · Partition Views
 · Index skip scans
 
 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Jonathan Lewis
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Archival Freeze

2002-04-12 Thread Koivu, Lisa


Raj, either way you are in deep doo doo until the problem is solved.  The
error code is ORA-257 - Archiver error.  Connect internal only, until freed.
From metalink:

ORA-00257: archiver error. Connect internal only, until freed. 
Cause: The archiver process received an error while trying to archive a log.
Unless the problem is resolved soon, the database will stop executing
transactions. 
Action: By far the most likely cause of the error is the archive destination
device is out of space. Check archiver trace file for detail description of
the problem. 


Have you tried it?  Watch the timestamps on your redologs and that will
probably answer your question. 

Lisa Koivu
Oracle Database Tank
Fairfield Resorts, Inc.
954-935-4117


 -Original Message-
 From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
 Sent: Friday, April 12, 2002 5:13 PM
 To:   Multiple recipients of list ORACLE-L
 Subject:  Archival Freeze
 
 
 Hi Fellow DBAs,
 
 I and a fellow DBA are currently debating about how insufficient space in
 the archive destination freezes up the DB. He claims that as soon as the
 ARCH process is unable to write to the disk, the db freezes. I am of the
 opinion that it does not. It will only report an error (Any idea, what the
 error code is?). It will only freeze when it cycles thru the rest of the
 redolog groups, and then when it tries to switch log to the one which has
 not been archived. That's why the error message is cannot allocate new
 log.
 
 Whats your say? He's also on this list.
 
 Raj
 
 
 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: 
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Koivu, Lisa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL statement with hints or without hints

2002-04-12 Thread Brian_P_MacLean



H, 9.2.  Did you mean 9.0.2?

As we all know 9i is really 9.0.1  I think we will see 9.1, if for no
reason other than we had an 8.1.  But I'm already seeing/hearing about 10i
(aka The Beast Master, Wiz Bang, All Knowing, All Seeing, Ai, Dilly
Dally).  But 9.2, never, I take odd's on that.

Brian P. MacLean
Oracle DBA, OCP8i



   
  
Jamadagni,
  
Rajendra To: Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
Rajendra.Jamadagni   cc:  
  
@espn.comSubject: RE: SQL statement with 
hints or without hints 
Sent by:   
  
[EMAIL PROTECTED]   
  
   
  
   
  
04/12/02 01:38 PM  
  
Please respond to  
  
ORACLE-L   
  
   
  
   
  




Thanks Cherie and Rafiq,

I am on 8061, but probably directly jump to 9.2 (whenever that becomes
available that is, so far I have heard many dates).

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN
Inc.

QOTD: Any clod can have facts, but having an opinion is an art!
(See attached file: ESPN_Disclaimer.txt)




ESPN_Disclaimer.txt
Description: Binary data

Re: Archival Freeze - Painful lessons learned

2002-04-12 Thread Brian_P_MacLean



As Daffy Duck says - http://www.dailywav.com/0700/dcorrect.wav

The archiver freaks first, and will keep trying to write/recover/looking
for space. But the database will not hang until all the redo logs are full
and none remain that have been archived (ie: all redo logs need archiving).

The error in the alert.log is some combination of:

ORA-00255, error archiving log %s of thread %s, sequence # %s
ORA-00270, error creating archive log %s
ORA-19504, failed to create file \%s\
ORA-27040, skgfrcre: create error, unable to create file
ARCH: Archival stopped, error occurred. Will continue retrying
SVR4 Error: 5: I/O error

Why do I have a list of the above errors you ask?  Because I've been there
(painful lessons learned), and these are just some of the errors I look for
in my db status script.

Brian P. MacLean
Oracle DBA, OCP8i



   
 
Rajesh.Rao@jpm 
 
chase.comTo: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
Sent by: cc:   
 
[EMAIL PROTECTED]   Subject: Archival Freeze  
 
om 
 
   
 
   
 
04/12/02 02:13 
 
PM 
 
Please respond 
 
to ORACLE-L
 
   
 
   
 





Hi Fellow DBAs,

I and a fellow DBA are currently debating about how insufficient space in
the archive destination freezes up the DB. He claims that as soon as the
ARCH process is unable to write to the disk, the db freezes. I am of the
opinion that it does not. It will only report an error (Any idea, what the
error code is?). It will only freeze when it cycles thru the rest of the
redolog groups, and then when it tries to switch log to the one which has
not been archived. That's why the error message is cannot allocate new
log.

Whats your say? He's also on this list.

Raj




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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL statement with hints or without hints


Brian,

We are production on 9012, can't get 9013 compiled on Aix 64 bit though. We
will be getting 9.2 (9i release 2) soon. But then it all depends on how you
define 'soon'.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!


-Original Message-
Sent: Friday, April 12, 2002 6:04 PM
To: Multiple recipients of list ORACLE-L



H, 9.2.  Did you mean 9.0.2?

As we all know 9i is really 9.0.1  I think we will see 9.1, if for no reason
other than we had an 8.1.  But I'm already seeing/hearing about 10i (aka
The Beast Master, Wiz Bang, All Knowing, All Seeing, Ai, Dilly
Dally).  But 9.2, never, I take odd's on that.



*2

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

*2

Re: Archival Freeze - Painful lessons learned

2002-04-12 Thread Brian_P_MacLean



After reading the other postings I better add this one too;^) (If ya didn't
learn something new today, it's because ya didn't do anything today).

ORA-00257,  archiver error. Connect internal only, until freed.

Brian P. MacLean
Oracle DBA, OCP8i



   

Brian P

MacLean  To: [EMAIL PROTECTED]  

 cc: [EMAIL PROTECTED]   

04/12/02 02:19   Subject: Re: Archival Freeze - Painful 
lessons learned(Document link: 
PM   Brian P MacLean)  

   

   




As Daffy Duck says - http://www.dailywav.com/0700/dcorrect.wav

The archiver freaks first, and will keep trying to write/recover/looking
for space. But the database will not hang until all the redo logs are full
and none remain that have been archived (ie: all redo logs need archiving).

The error in the alert.log is some combination of:

ORA-00255, error archiving log %s of thread %s, sequence # %s
ORA-00270, error creating archive log %s
ORA-19504, failed to create file \%s\
ORA-27040, skgfrcre: create error, unable to create file
ARCH: Archival stopped, error occurred. Will continue retrying
SVR4 Error: 5: I/O error

Why do I have a list of the above errors you ask?  Because I've been there
(painful lessons learned), and these are just some of the errors I look for
in my db status script.

Brian P. MacLean
Oracle DBA, OCP8i



   
 
Rajesh.Rao@jpm 
 
chase.comTo: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
Sent by: cc:   
 
[EMAIL PROTECTED]   Subject: Archival Freeze  
 
om 
 
   
 
   
 
04/12/02 02:13 
 
PM 
 
Please respond 
 
to ORACLE-L
 
   
 
   
 





Hi Fellow DBAs,

I and a fellow DBA are currently debating about how insufficient space in
the archive destination freezes up the DB. He claims that as soon as the
ARCH process is unable to write to the disk, the db freezes. I am of the
opinion that it does not. It will only report an error (Any idea, what the
error code is?). It will only freeze when it cycles thru the rest of the
redolog groups, and then when it tries to switch log to the one which has
not been archived. That's why the error message is cannot allocate new
log.

Whats your say? He's also on this list.

Raj




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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).






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

RE: 1Z0-007 Exam

2002-04-12 Thread Reddy, Madhusudana




Its not a Upgrade exam.. in fact its the first exam in Oracle 9i OCP core 
series

  -Original Message-From: John Hallas 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, April 12, 2002 
  12:58 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: 1Z0-007 Exam
  
  Ken,
  If 
  this is the 9i upgrade exam then there was a discussion on this recently 
  (about 3-4 weeks ago)
  Search 
  the archives for a post by Mike Hateley and responses from Robert 
  Freeman.
  If 
  is not the 9i upgrade which exam is it?
  
  John
  
  -Original 
  Message-From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of KENNETH JANUSZSent: 12 April 2002 16:26To: Multiple recipients of list 
  ORACLE-LSubject: 1Z0-007 
  Exam
  
  Has anyone 
  taken this exam? If so, I would like your feedback.
  
  Thanks,
  Ken Janusz, 
  CPIM

OCP Question

2002-04-12 Thread Duk Lee




Does OCP really help? I am thinking about gettting OCP, 
yet still skeptical about it. I am a web engineer, and have very little dba 
"work" experience. I know PL/SQL and have worked with MS SQL 2000, mySQL, etc. 
And have been messing around with Oracle 9.02 on my linux box. also been 
studying with numerous oracle books. I am not really worried about getting OCP. 
What I am worrying about it that "will itreallyhelp me to get a job 
as Junior-level Oracle DBA?"

Thanks.

Re: OCP Question

2002-04-12 Thread Joe Raube


Here we go again :-)

--- Duk Lee [EMAIL PROTECTED] wrote:
 Does OCP really help? I am thinking about gettting OCP, yet still
 skeptical
 about it. I am a web engineer, and have very little dba work
 experience. I
 know PL/SQL and have worked with MS SQL 2000, mySQL, etc. And have
 been
 messing around with Oracle 9.02 on my linux box. also been studying
 with
 numerous oracle books. I am not really worried about getting OCP.
 What I am
 worrying about it that will it really help me to get a job as
 Junior-level
 Oracle DBA?
  
 Thanks.
 


__
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Raube
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: OCP Question

2002-04-12 Thread Kimberly Smith




If you 
play around with a database while studying for the OCP it can help in that 
way. It will most likely get you past some HR folks but the more folks 
that get out there that have an OCP but only got the tests from memorization 
will start to give the OCP a bad name (ok for some of you out there, worse 
name). Then it will not get you even through the door. Your PL/SQL 
and SQL 2000 experience will probably be the bigger help in landing a junior 
position though (assuming that is actual work experience).

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Duk LeeSent: Friday, 
  April 12, 2002 4:43 PMTo: Multiple recipients of list 
  ORACLE-LSubject: OCP Question
  Does OCP really help? I am thinking about gettting 
  OCP, yet still skeptical about it. I am a web engineer, and have very little 
  dba "work" experience. I know PL/SQL and have worked with MS SQL 2000, mySQL, 
  etc. And have been messing around with Oracle 9.02 on my linux box. also been 
  studying with numerous oracle books. I am not really worried about getting 
  OCP. What I am worrying about it that "will itreallyhelp me to get 
  a job as Junior-level Oracle DBA?"
  
  Thanks.

RE: OCP Question

2002-04-12 Thread Duk Lee

Title: RE: OCP Question





Meaning ... been discussed millions of times already? :-| Should I just search the archieve?


-Original Message-
From: Joe Raube [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 12, 2002 5:23 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: OCP Question



Here we go again :-)


--- Duk Lee [EMAIL PROTECTED] wrote:
 Does OCP really help? I am thinking about gettting OCP, yet still
 skeptical
 about it. I am a web engineer, and have very little dba work
 experience. I
 know PL/SQL and have worked with MS SQL 2000, mySQL, etc. And have
 been
 messing around with Oracle 9.02 on my linux box. also been studying
 with
 numerous oracle books. I am not really worried about getting OCP.
 What I am
 worrying about it that will it really help me to get a job as
 Junior-level
 Oracle DBA?
 
 Thanks.
 



__
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Raube
 INET: [EMAIL PROTECTED]


Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

RE: OCP Question

2002-04-12 Thread Duk Lee




INDEX 
ORACLE-L

Re: SQL statement with hints or without hints


I would suggest analyzing everything anyway.  If any of the conditions occur
which John K was so kind to document earlier (thanks John!), they'll be on
CBO whether they like it or not, whether they know it or not.

I think you can prove to them that they are using CBO by looking in
V$SQLAREA where there is an OPTIMIZER or OPTIMIZER_MODE column (don't have a
book or database to look at right now).  That way, RBO won't get the credit
for doing what CBO is doing...

Convincing folks to let go of RBO, in spite of the terrible job it has been
doing, is still very problematic...

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, April 12, 2002 12:18 PM


 Thanks Tim,

 At-least now I know I shouldn't tell them to start putting hints. This was
 my problem to tell them or not to tell them. This still doesn't solve the
 problem for me though, as everyone is still reluctant to move to CBO. And
 this is exactly where it gets getting complicated.

 Raj
 __
 Rajendra Jamadagni  MIS, ESPN Inc.
 Rajendra dot Jamadagni at ESPN dot com
 Any opinion expressed here is personal and doesn't reflect that of ESPN
Inc.

 QOTD: Any clod can have facts, but having an opinion is an art!


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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL statement with hints or without hints (LONG)