date:20030530

Cause: The synonym used is based on a table, view, or synonym that no longer exists.

Action: Replace the synonym with the name of the object it references or re-create the 
synonym so that it refers to a valid table, view, or synonym.


looks to me like one of the data dictionary views that toad is trying to use no longer 
exists or after the data dictionary synonym was created the object no longer exists. 
wierd, i thought synonyms were deleted when their corresponding objects where dropped. 

best way to fidn the problem is to an anti-join on the synonyms to the data dictionary 
views. you will need to use a wildcard for the v$ views. 

im curious about this. please post what you find. 
 
 From: Satya Prakash Viswanath [EMAIL PROTECTED]
 Date: 2003/05/29 Thu AM 12:09:48 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Problem with TOAD
 
 Hi List,
 
 I am facing a strange problem with TOAD (ver 7.3.0.0).
 
 I am not able to log onto my Oracle database server ( 9.2.0.1.0) thru
 TOAD. I am getting ORA-00980: synonym translation is no longer valid.
 I do not know how this correlates with this error while connecting. I am
 able to connect to any other database thru TOAD.
 I am able to connect to my database using SQL* PLUS but my comfort level
 with TOAD is high that I do not want to revert to SQL * PLUS. ;)
 
 The things that I will list out are :
 1. Tried running the TOADPREP.sql from SQL*PLUS. The script goes thru
 fine without any problems.
 2. Nothing has happnened to my PC/Database since the last time I logged
 in to my machine. Very normal behavior. 
 3. I can desribe all my toad specific tables from SQL* PLUS. 
 4. The strange thing now (after running TOADPREP.sql) is that i can
 access all user tables but not dba tables(dynamic performance views) 
 5. Stopped and restarted the servies but to no effect
 6. Even reinstalled TOAD but to no effect.
 7. I have checked the trace files, seems fine to me. 
 
 Even though this issue is specific to TOAD, plz help me on this. 
 I am in touch with the support also but I cannot wait for 1 day for
 their reply. 
 Any pointers?
 
 Regards,
 Satya Prakash
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Satya Prakash Viswanath
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 

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

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

RE: dbms_stats broken

2003-05-30 Thread Goulet, Dick




Interesting, I use dbms_stats with gather stale in 8.1.7.4  it 
behaves beautifully.

Dick GouletSenior Oracle DBAOracle Certified 8i DBA 

-Original Message-From: Jamadagni, Rajendra 
[mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 
11:15 PMTo: Multiple recipients of list ORACLE-LSubject: 
RE: dbms_stats broken
Hmmm 
... I haven't tried with stale option, but gather-schema works. 


I know 
.. I know... this isnot the answer you were looking for 
...

Raj

  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 
  2003 10:25 PMTo: Multiple recipients of list 
  ORACLE-LSubject: dbms_stats broken
  
  Hello everyone, 
  
  
  Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and 
  it just isn't working in 8.1.7.4. 
  This is documented on Metalink. I'd love to hear from someone else if 
  this is fixed in 9.2 and if it can be reliably used. 
  
  
  Thank 
  you
  Lisa 
  Monkey.

RE: Function Based Index - Not Used ???

2003-05-30 Thread Hallas, John, Tech Dev

Naveen , these parameters look to be already set
Doesn't FBI come in with 8.1.7 and Prem is on 8.16 and a comapatible of 8.10 is in 
place

Failing that, as my second guess, 2 privileges need to be assigned to the user 
(QUERY_REWRITE and CREATE INDEX). The 2nd one is obviously in place to allow the index 
to be created but I don't know about the first

The final point is whether the name column is not null , again I assume not but it is 
a possibility


John

-Original Message-
Sent: 29 May 2003 08:50
To: Multiple recipients of list ORACLE-L


Don't remember but you need a few parameters set appropriately for the FB
indexes to work.

I guess one of the parameters is QUERY_REWRITE_ENABLED which should be set to
TRUE and you also need to set QUERY_REWRITE_INTEGRITY to some appropriate
value.

Regards
Naveen

 

 -Original Message-
 From: Prem Khanna J [mailto:[EMAIL PROTECTED]
 Sent: Thursday, May 29, 2003 12:25 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Function Based Index - Not Used ???
 
 
 Guys,
 
 create table Tab1 ( name varchar2(100),age int,state 
 varchar2(100),country varchar2(100));
 
 insert into tab1 values ('SCOTT',25,'TN','India');
 
 I have 20,00,000 records like above.
 
 create index idx1 on tab1 (upper(name));
 
 analyze table tab1 compute statistics;
 analyze index idx1 compute statistics;
 
 select age from tab1 where upper(name)='SCOTT'; --- this 
 will return around 50 records.
 
 -
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=2458 
 Card=262146 Bytes=2097168)
   10   TABLE ACCESS (FULL) OF 'TAB1' (Cost=2458 
 Card=262146 Bytes=2097168)
 
 --- INIT.ORA --
 optimizer_mode=choose
 compatible=8.1.0
 query_rewrite_enabled=true
 query_rewrite_integrity=trusted
 ---
 It's Oracle 8.1.6/Win2K.
 
 When i add a HINT, IDX1 is used.Why is it so ?
 Why this SELECT does not use index IDX1 w/o a hint ? 
 
 TIA.
 Jp.
 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Prem Khanna J
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 


DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged. 
Before opening attachments please check them for viruses and defects. MindTree 
Consulting Private Limited (MindTree) will not be responsible for any viruses or 
defects or any forwarded attachments emanating either from within MindTree or outside. 
If you have received this message by mistake please notify the sender by return  
e-mail and delete this message from your system. Any unauthorized use or dissemination 
of this message in whole or in part is strictly prohibited.  Please note that e-mails 
are susceptible to change and MindTree shall not be liable for any improper, untimely 
or incomplete transmission.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Naveen Nahata
  INET: [EMAIL PROTECTED]

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

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

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

Re: Which method is more efficient

2003-05-30 Thread Richard Foote


- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, May 29, 2003 3:14 AM


 1. to totally eliminate redo, load your staging records into a global temp
table. it has absolutely no redo and is very fast.

Hi

The above is not quite true. Global temp tables *do* generate redo (albeit
indirectly) and potentially quite a bit of it. The point to make here is
that changes to GTT generate undo and this undo makes changes to undo
segments and these changes subsequently generate redo. So depending on the
type of DML (eg. deletes) and the volume of changes, you can end up
generating quite a bit of the redo.

Not as much as a non-GTT but enough to certainly invalidate the above
statement.

Cheers

Richard Foote


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

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

Re: is this DBA's only mailing list?

best place for forms and reports are the odtug listservs at fatcity

there is one called Developer 2000 and one called Designer 2000

 From: Ajay K. Garg [EMAIL PROTECTED]
 Date: 2003/05/29 Thu AM 08:45:40 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: is this DBA's only mailing list?

 HI everyone

 I recently joined the list, and I think it is meant only for DBAs. Is that true?
 Also, can anyone suggest any similar mailing list meant for Oracle developers (that 
 is those working with SQL, PL/SQL, Forms, Oracle apps, etc.) and those who are not 
 DBAs?

 Thanks in advance
 Ajay K. Garg

HI everyone

I recently joined the list, and I think it is meant 
only for DBAs. Is that true?
Also, can anyone suggest any similar mailing list 
meant for Oracle developers (that is those working with SQL, PL/SQL, Forms, 
Oracle apps, etc.) and those who are not DBAs?

Thanks in advance
Ajay K. Garg

Re: dbms_stats broken

2003-05-30 Thread Glenn Stauffer

Koivu, Lisa wrote:

Hello everyone,

Is anyone using dbms_stats and gather stale or gather auto in 9.2? Im 
trying to use dbms_stats gather schema stats with the stale option and 
it just isnt working in 8.1.7.4. This is documented on Metalink. Id 
love to hear from someone else if this is fixed in 9.2 and if it can 
be reliably used.

Thank you

Lisa Monkey.

I run this sql out of a python program against a 9.2.0.2.0 database on a 
Compaq Tru64 server. Works like a charm. The 'gather stale' option works 
as well.

sql = BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(
ownname = '%s',
options = 'GATHER AUTO',
estimate_percent = dbms_stats.auto_sample_size,
method_opt = 'for all columns size auto',
cascade = true); END; % schema
--Glenn Stauffer

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

RE: is this DBA's only mailing list?

2003-05-30 Thread TOMPKINS, MARGARET




Ajay,
 The Oracle Development Tools 
User Group (ODTUG) has about a dozen different list serves and many are focused 
on development. Check out the web site www.odtug.com and you can register for those 
lists with a few mouse clicks. Good Luck! Maggie
Respectfully, Maggie Tompkins - DCII Integration Technology Services Organization - Kansas 
City Defense Finance 
and Accounting Service 816-926-1117 (DSN 465); 
[EMAIL PROTECTED]  
Back to the Beach! - ODTUG 
Conference Loews Miami Beach Hotel, June 22-27 
Visit www.odtug.com for details. 
-Original Message-From: Ajay K. Garg 
[mailto:[EMAIL PROTECTED]Sent: Thursday, May 29, 2003 7:46 
AMTo: Multiple recipients of list ORACLE-LSubject: is this 
DBA's only mailing list?
HI everyone

I recently joined the list, and I think it is meant 
only for DBAs. Is that true?
Also, can anyone suggest any similar mailing list 
meant for Oracle developers (that is those working with SQL, PL/SQL, Forms, 
Oracle apps, etc.) and those who are not DBAs?

Thanks in advance
Ajay K. Garg

RE: is this DBA's only mailing list?

2003-05-30 Thread Pardee, Roy E

There are also dev-specific lists tho:
  Developer 2000: ODTUG-DEV2K-L, and
  Designer 2000: ODTUG-DES2K-L.

Send an e-mail w/the text (for instance)
  SUB ODTUG-DEV2K-L
To the address
   [EMAIL PROTECTED]
to subscribe.

HTH,

-Roy

Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487 

-Original Message-
Sent: Thursday, May 29, 2003 6:40 AM
To: Multiple recipients of list ORACLE-L


Yes, we even let developers use this list.  Heck, if you want to throw out a
perl question cuz I bet you'll get an answer.

;o)

Dave
-Original Message-
Sent: Thursday, May 29, 2003 7:46 AM
To: Multiple recipients of list ORACLE-L


HI everyone

I recently joined the list, and I think it is meant only for DBAs. Is that
true?
Also, can anyone suggest any similar mailing list meant for Oracle
developers (that is those working with SQL, PL/SQL, Forms, Oracle apps,
etc.) and those who are not DBAs?

Thanks in advance
Ajay K. Garg
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pardee, Roy E
  INET: [EMAIL PROTECTED]

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

RE: Function Based Index - Not Used ???

FBI is supported on 8.1.6 (that is what I use them on), but you may be right
(Babout the compatible setting.
(B
(BDennis Williams
(BDBA, 80%OCP, 100% DBA
(BLifetouch, Inc.
(B[EMAIL PROTECTED] 
(B
(B
(B-Original Message-
(BSent: Thursday, May 29, 2003 7:46 AM
(BTo: Multiple recipients of list ORACLE-L
(B
(B
(BNaveen , these parameters look to be already set
(BDoesn't FBI come in with 8.1.7 and Prem is on 8.16 and a comapatible of 8.10
(Bis in place
(B
(BFailing that, as my second guess, 2 privileges need to be assigned to the
(Buser (QUERY_REWRITE and CREATE INDEX). The 2nd one is obviously in place to
(Ballow the index to be created but I don't know about the first
(B
(BThe final point is whether the name column is not null , again I assume not
(Bbut it is a possibility
(B
(B
(BJohn
(B
(B-Original Message-
(BSent: 29 May 2003 08:50
(BTo: Multiple recipients of list ORACLE-L
(B
(B
(BDon't remember but you need a few parameters set appropriately for the FB
(Bindexes to work.
(B
(BI guess one of the parameters is QUERY_REWRITE_ENABLED which should be set
(Bto
(BTRUE and you also need to set QUERY_REWRITE_INTEGRITY to some appropriate
(Bvalue.
(B
(BRegards
(BNaveen
(B
(B 
(B
(B -Original Message-
(B From: Prem Khanna J [mailto:[EMAIL PROTECTED]
(B Sent: Thursday, May 29, 2003 12:25 PM
(B To: Multiple recipients of list ORACLE-L
(B Subject: Function Based Index - Not Used ???
(B 
(B 
(B Guys,
(B 
(B create table Tab1 ( name varchar2(100),age int,state 
(B varchar2(100),country varchar2(100));
(B 
(B insert into tab1 values ('SCOTT',25,'TN','India');
(B 
(B I have 20,00,000 records like above.
(B 
(B create index idx1 on tab1 (upper(name));
(B 
(B analyze table tab1 compute statistics;
(B analyze index idx1 compute statistics;
(B 
(B select age from tab1 where upper(name)='SCOTT'; --- this 
(B will return around 50 records.
(B 
(B -
(B   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=2458 
(B Card=262146 Bytes=2097168)
(B   10   TABLE ACCESS (FULL) OF 'TAB1' (Cost=2458 
(B Card=262146 Bytes=2097168)
(B 
(B --- INIT.ORA --
(B optimizer_mode=choose
(B compatible=8.1.0
(B query_rewrite_enabled=true
(B query_rewrite_integrity=trusted
(B ---
(B It's Oracle 8.1.6/Win2K.
(B 
(B When i add a HINT, IDX1 is used.Why is it so ?
(B Why this SELECT does not use index IDX1 w/o a hint ? 
(B 
(B TIA.
(B Jp.
(B 
(B 
(B -- 
(B Please see the official ORACLE-L FAQ: http://www.orafaq.net
(B -- 
(B Author: Prem Khanna J
(B   INET: [EMAIL PROTECTED]
(B 
(B Fat City Network Services-- 858-538-5051 http://www.fatcity.com
(B San Diego, California-- Mailing list and web hosting services
(B -
(B To REMOVE yourself from this mailing list, send an E-Mail message
(B to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
(B the message BODY, include a line containing: UNSUB ORACLE-L
(B (or the name of mailing list you want to be removed from).  You may
(B also send the HELP command for other information (like subscribing).
(B 
(B 
(B
(B
(BDISCLAIMER:
(BThis message (including attachment if any) is confidential and may be
(Bprivileged. Before opening attachments please check them for viruses and
(Bdefects. MindTree Consulting Private Limited (MindTree) will not be
(Bresponsible for any viruses or defects or any forwarded attachments
(Bemanating either from within MindTree or outside. If you have received this
(Bmessage by mistake please notify the sender by return  e-mail and delete
(Bthis message from your system. Any unauthorized use or dissemination of this
(Bmessage in whole or in part is strictly prohibited.  Please note that
(Be-mails are susceptible to change and MindTree shall not be liable for any
(Bimproper, untimely or incomplete transmission.
(B-- 
(BPlease see the official ORACLE-L FAQ: http://www.orafaq.net
(B-- 
(BAuthor: Naveen Nahata
(B  INET: [EMAIL PROTECTED]
(B
(BFat City Network Services-- 858-538-5051 http://www.fatcity.com
(BSan Diego, California-- Mailing list and web hosting services
(B-
(BTo REMOVE yourself from this mailing list, send an E-Mail message
(Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
(Bthe message BODY, include a line containing: UNSUB ORACLE-L
(B(or the name of mailing list you want to be removed from).  You may
(Balso send the HELP command for other information (like subscribing).
(B
(B-- 
(BPlease see the official ORACLE-L FAQ: http://www.orafaq.net
(B-- 
(BAuthor: Hallas, John, Tech Dev
(B  INET: [EMAIL PROTECTED]
(B
(BFat City Network Services-- 858-538-5051 http://www.fatcity.com
(BSan Diego, California-- Mailing list and

RE: SAP Hands SAP DB over to MySQL

Okay, I've got a question for you people knowledgeable in SAP. Who would
trust their financial and payroll data to MySQL? I'm not saying this to
knock MySQL. It is just that your financial and payroll data is among your
most valuable data in the corporation. Only recently was a transaction
capability added to MySQL, and that was more of an add-on. I manage
several databases and my company even makes modest use of MySQL. But of
those databases, the financial and payroll data would be the LAST database I
would convert to something like MySQL. I'm just curious what is the big
incentive in MySQL is.

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


-Original Message-
Sent: Wednesday, May 28, 2003 5:50 PM
To: Multiple recipients of list ORACLE-L


Maybe it is just so they can continue to say they're not a database
company
(insert sound of condescension) to emphasize their focus on applications 
excellence in the veiled jabs they continue to make at Oracle.  On the
other
hand, I can't imagine they would give up development control because they do

make a specialized version of SAP DB for object oriented programming
(they say they couldn't find a product that worked correctly...).  

Kip  

|I dunno.  Though both want to make a profit ( and rightly so ) SAP
|doesn't seem to have the same mercenary mentality that MS has.

|Jared


|Orr, Steve [EMAIL PROTECTED]
|Sent by: [EMAIL PROTECTED]
| 05/28/2003 11:52 AM
| Please respond to ORACLE-L

 
|To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
|cc:
|Subject:RE: SAP Hands SAP DB over to MySQL


|Reminiscent of the M$/Sybase partnership?


|-Original Message-
|Sent: Wednesday, May 28, 2003 11:40 AM
|To: '[EMAIL PROTECTED]'


|At http://www.sapdb.org/7.4/pdf/sapdb_letter.pdf SAP offers
clarification:

|SAP:
|Contrary to erroneous press reports, SAP AG has not given up any rights
|concerning the SAP DB code base nor handed over or even sold SAP DB to
|MySQL AB.

|SAP:
|SAP AG remains responsible for ongoing development and support.

|CNet:
|MySQL will take over most of the development of SAP DB.



|-Original Message-
|Sent: Wednesday, May 28, 2003 12:01 PM
|To: Multiple recipients of list ORACLE-L
|Importance: High


|The past few months I've been wondering when MySQL would start
|putting pressure on Oracle in the same way that Linux is putting
|pressure on MS.

|Maybe sooner than you think:

|http://news.com.com/2100-1012_3-1010522.html?tag=fd_top


|Jared

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

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

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

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




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

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

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

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

[Q] Benchmark rate and ORACLE database?

2003-05-30 Thread mike mon

I am compare the SUN server performance with ORACLE
database.  Can anyone
tell me  following 4 values which one  most import for
ORACLE database:

CINT2000
CFP2000
CINT2000 rates
CFP2000 rates


Thanks.


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: mike mon
  INET: [EMAIL PROTECTED]

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

RE: dbms_stats broken

2003-05-30 Thread Jesse, Rich

Hey Lisa (Monkey?),

Yes, I had a problem with the stale option, too, at least under 8.1.6.  It
would bomb out with no more rows or something like that.  Under 8.1.7.4 on
Solaris, I no longer had that problem but I did have some poorer explain
plans than with good ol' ANALYZE.  See the Burned by DBMS_STATS **AGAIN**
thread starting on 04/07/2003.


Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA

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


Hello everyone, 
 
Is anyone using dbms_stats and gather stale or gather auto in 9.2?  I'm
trying to use dbms_stats gather schema stats with the stale option and it
just isn't working in 8.1.7.4.  This is documented on Metalink.  I'd love to
hear from someone else if this is fixed in 9.2 and if it can be reliably
used. 
 
Thank you
Lisa Monkey.
 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

RE: is this DBA's only mailing list?

2003-05-30 Thread Naveen Nahata




I'll 
quote the list manager Jared's comments which he made in a mail long 
back

"This 
is an Oracle mailing List"

So 
youshould be allowed topost anything related to 
Oracle.

Regards
Naveen

  -Original Message-From: Ajay K. Garg 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, May 29, 2003 6:16 
  PMTo: Multiple recipients of list ORACLE-LSubject: is 
  this DBA's only mailing list?
  HI everyone
  
  I recently joined the list, and I think it is 
  meant only for DBAs. Is that true?
  Also, can anyone suggest any similar mailing list 
  meant for Oracle developers (that is those working with SQL, PL/SQL, Forms, 
  Oracle apps, etc.) and those who are not DBAs?
  
  Thanks in advance
  Ajay K. Garg
  DISCLAIMER:This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return  e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited.  Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission.

RE: is this DBA's only mailing list?

Ajay - While most of us are DBAs, we also deal with SQL and PL/SQL a lot.
When the developers get stumped, who do you think they ask? Their DBA of
course. I don't work with Forms, but I've seen plenty of Forms questions
asked and answered here. There are people here who work with the Oracle
Apps, so can probably answer the operational type of questions. And there
are a lot of people on this list that prefer to be called developers rather
than DBAs.



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

-Original Message-
Sent: Thursday, May 29, 2003 7:46 AM
To: Multiple recipients of list ORACLE-L


HI everyone
 
I recently joined the list, and I think it is meant only for DBAs. Is that
true?
Also, can anyone suggest any similar mailing list meant for Oracle
developers (that is those working with SQL, PL/SQL, Forms, Oracle apps,
etc.) and those who are not DBAs?
 
Thanks in advance
Ajay K. Garg
 

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

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

Re: Re: Which method is more efficient

so GTT's help reducing redo when you do inserts and deletes? but not for deletes?
there was a post by tom kyte on dejanews from a few years back where someone asked the
redo question and he recommended GTTs.

here is the link... did I misread it?

http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=89r5a7%249q3%241%40nnrp1.deja.comrnum=1prev=/groups%3Fq%3Doracle%2Bglobal%2Btemp%2B%2Bkyte%2Bredo%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch

From: Richard Foote [EMAIL PROTECTED]
Date: 2003/05/29 Thu AM 08:45:46 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Which method is more efficient

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, May 29, 2003 3:14 AM

1. to totally eliminate redo, load your staging records into a global temp
table. it has absolutely no redo and is very fast.

The above is not quite true. Global temp tables *do* generate redo (albeit
indirectly) and potentially quite a bit of it. The point to make here is
that changes to GTT generate undo and this undo makes changes to undo
segments and these changes subsequently generate redo. So depending on the
type of DML (eg. deletes) and the volume of changes, you can end up
generating quite a bit of the redo.

Not as much as a non-GTT but enough to certainly invalidate the above
statement.

Cheers

Richard Foote

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

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

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

RE: dbms_stats broken

2003-05-30 Thread Goulet, Dick

Darrell,

I put dbms_stats into production, on 8.1.7.4, over two months ago  have not 
had a problem.  Here is what I did to compute the stats automatically.  (BTW: this 
runs under system)

create procedure compute_daily_stats is
begin
  for a in (select distinct table_owner from dba_tab_modifications) loop
DBMS_STATS.GATHER_SCHEMA_STATS(a.table_owner,
   NULL,FALSE,
   'FOR ALL indexed COLUMNS SIZE 1',
   NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER 
STALE','DEFAULT');
  end loop;
end;
/

declare
  jb integer;
begin
  dbms_job.submit(jb, 
'compute_daily_stats;',trunc(sysdate+1)+(6/24),'trunc(sysdate+1)+(6/24)',FALSE);
  dbms_output.put_line('Job is '||jb);
end;
/

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 

-Original Message-
Sent: Thursday, May 29, 2003 12:30 AM
To: Multiple recipients of list ORACLE-L


Lisa,

Wow, you might be saving me from peril right now.  I have tested this
with a small set of tables with no problems (in and 8.1.7.4 instance). 
I'm preparing to go 'schema' wide in the next week or so for further
testing prior to implementing in production.
I'd be very interested in more details of your problems in 8.1.7.4 and
of course I'll post reports of testing to the list as well.

For starters, here is the code I use to obtain a list of 'stale'
qualified tables:
( For proper credit, I think I got this from asktom.oracle.com)


set serverout on size 9

declare
l_objList dbms_stats.objecttab;
begin
dbms_stats.gather_schema_stats
( ownname = '1',
options = 'LIST STALE',
objlist = l_objlist );
for i in 1 .. l_objlist.count
loop
--dbms_output.put_line( l_objlist(i).objtype );
dbms_output.put_line( l_objlist(i).objname );
end loop;
end;
/


And the code to gather stats:

set serverout on size 99000

begin
dbms_stats.gather_schema_stats(
ownname='1',
options='GATHER STALE',
cascade=TRUE,
degree=8,
granularity='ALL',
method_opt='FOR ALL INDEXED COLUMNS SIZE 1'
);
end;
/


Thanks,
Darrell


 [EMAIL PROTECTED] 05/28/03 09:24PM 
Hello everyone, 
 
Is anyone using dbms_stats and gather stale or gather auto in 9.2? 
I'm
trying to use dbms_stats gather schema stats with the stale option and
it just isn't working in 8.1.7.4.  This is documented on Metalink. 
I'd
love to hear from someone else if this is fixed in 9.2 and if it can
be
reliably used. 
 
Thank you
Lisa Monkey.
 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

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

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

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

Export Long Dies with no error

2003-05-30 Thread Smith, Ron L.

I am running an Oracle 8.1.7 export on a 2G table which contains a Long
column.  The export runs for several hours and then just dies with no error
anywhere.  I tried to use direct=y but I get an error about character sets.
Any ideas?

Thanks!
Ron Smith
If you are not the intended recipient of this e-mail message, any use,
distribution or copying of the message is prohibited.  Please let me know
immediately by return e-mail if you have received this message by mistake,
then delete the e-mail message. Thank you.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Smith, Ron L.
  INET: [EMAIL PROTECTED]

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

oh it wasnt none, it was cut in half. I skimmed it. 

my bad

 From: Richard Foote [EMAIL PROTECTED]
 Date: 2003/05/29 Thu AM 08:45:46 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Re: Which method is more efficient

 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Thursday, May 29, 2003 3:14 AM

  1. to totally eliminate redo, load your staging records into a global temp
 table. it has absolutely no redo and is very fast.

 Hi

 The above is not quite true. Global temp tables *do* generate redo (albeit
 indirectly) and potentially quite a bit of it. The point to make here is
 that changes to GTT generate undo and this undo makes changes to undo
 segments and these changes subsequently generate redo. So depending on the
 type of DML (eg. deletes) and the volume of changes, you can end up
 generating quite a bit of the redo.

 Not as much as a non-GTT but enough to certainly invalidate the above
 statement.

 Cheers

 Richard Foote

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

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

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

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

RE: dbms_stats broken

2003-05-30 Thread Jankovic, Djordje




I 
haven't used it, but here is what we are doing: we created a simple package 
which loops through dba_tab_modifications and finds itself (the same way as 
oracle would) which tables are stale: where the sum of inserts, updates and 
deletes is more than 10% of the number of rows, and than run dbms_stats for 
those only. That way you have a bit more flexibility (you can for example 
exclude some tables), makeestimate or calculate decisions on the table 
basis, etc.

Djordje

  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 
  2003 10:25 PMTo: Multiple recipients of list 
  ORACLE-LSubject: dbms_stats broken
  
  Hello everyone, 
  
  
  Is anyone using dbms_stats and gather stale or gather auto in 9.2? Im trying to use dbms_stats gather schema stats with the stale option and 
  it just isnt working in 8.1.7.4. 
  This is documented on Metalink. Id love to hear from someone else if 
  this is fixed in 9.2 and if it can be reliably used. 
  
  
  Thank 
  you
  Lisa 
  Monkey.

RE: Diff between 7.x,8,9

You can find the Oracle documentation at technet.oracle.com. This is free,
but you must register. Each new version of the Oracle documentation has a
section titled New Features. 

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


-Original Message-
Sent: Thursday, May 29, 2003 2:45 AM
To: Multiple recipients of list ORACLE-L






hai
iam new to Dba can any  one tell me the differences between oracle versions
thanks in advances
manjunath
hcl infosystems ltd

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

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

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

RE: exp, dbms_stats, RMAN and rollback segments

Oh yeah, for the export consistent=N

-Original Message-
Sent: Thursday, May 29, 2003 9:14 AM
To: '[EMAIL PROTECTED]'


A certain alignment of the planets occurred creating a good ole ORA-01555 error... A 
user level export received the snapshot too old error and terminated. Concurrent to 
this was an RMAN backup and DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on 
the same schema being backed up via the user level export. There was no other end user 
access to the schema data. Since exp got the error I assume it was reading from the 
rollback segments but why? I'm suspecting dbms_stats. We have ample RBS. Is there any 
significant undo generated by dbms_stats or RMAN which could create this problem? 

(Of course we need to improve our job scheduling but that's another issue, the timing 
of the user level export is application driven and out of our control). 


Befuddled in Bozeman,
Walt and Steve
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Orr, Steve
  INET: [EMAIL PROTECTED]

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

Online index creation on 9.2

2003-05-30 Thread Stephen Lee


On 9.2.0.3, I have experienced a problem with online index creation.  When
creating indexes on what I suppose most would consider to be large tables
(with nasty, sharp, pointy teeth and a bad disposition), when Oracle is
doing its initial data gathering, sorting, slicing, and dicing in
preparation for the actual creation of the index, it appears that normal
access to the table -- and this includes selects which do not modify data --
becomes severely reduced to the point that the application is no longer
usable.  When Oracle finishes with its fact finding mission and begins to
build the index (as evidence by the appearance of a segment with a number
for a name), everything is OK again.  We never had this problem with
8.1.7.X.

Is this a new feature of 9.2.0.3?  Is there some new magic parameter that
needs to be set?  Are we wearing the wrong grass skirts and/or incorrectly
performing our ritual sacrifice ceremonies?
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  INET: [EMAIL PROTECTED]

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

RE: skip scan index

Thanks, Wolfgang! I really hope to meet both you and Cary one of these days.

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


-Original Message-
Sent: Wednesday, May 28, 2003 11:05 PM
To: Multiple recipients of list ORACLE-L


I was about to post the results of my test which also did prove you wrong. 
I ran the test with a 10046 level 8 trace to show the individual index 
block reads which nicely show why it is called a skip scan, but since you 
already proved yourself wrong there is no need.

BTW, as of Oracle 9 you don't necessarily need to restart the database to 
reset the pools. This should do the trick:

ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
alter system flush shared_pool;


At 06:08 PM 5/28/2003 -0800, you wrote:
Here is the idea:
Index test_skip1 is located in the tablespace INDX which has
one file, FILE#=5

I restart the database, execute your query, then see V$FILESTAT for blocks 
read.
(select PHYBLKRD from v$filestat where file#=5;)

Then restart the database, execute query asking for a fast full scan and
see
how many blocks do get read. If the number is the same, then the 
conclusion is inevitable.
So, here we go:



SQL set autotrace on explain
SQL select /*+ index_ss(test_skip1 ) */
c1,c2
from test_skip1 where c2 = 100;
   23
 C1 C2
-- --
  1100
  2100


Execution Plan
--
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=302 Bytes=78
   52)

10   INDEX (SKIP SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=11 Car
   d=302 Bytes=7852)

SQL select PHYBLKRD from v$filestat where file#=5;

   PHYBLKRD
--
 10

---DATABASE RESTART---


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning option
JServer Release 9.2.0.3.0 - Production

SQL set autotrace on
SQL select /*+ index_ffs(t test_skip1_pk ) */
c1,c2
from test_skip1 t where c2 = 100;
   23
 C1 C2
-- --
  1100
  2100


Execution Plan
--
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=302 Bytes=785
   2)

10   INDEX (FAST FULL SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=4
Card=302 Bytes=7852)





Statistics
--
 300  recursive calls
   0  db block gets
 777  consistent gets
 724  physical reads
   0  redo size
 464  bytes sent via SQL*Net to client
 503  bytes received via SQL*Net from client
   4  SQL*Net roundtrips to/from client
   6  sorts (memory)
   0  sorts (disk)
   2  rows processed

SQL select PHYBLKRD from v$filestat where file#=5;

   PHYBLKRD
--
722


That means that fast full scan will read 722 blocks where skip scan will 
read only 10,
which means that you were right and I was wrong. Obviously, my metodology 
was incorrect
or 9.2.0.1 database that I've tested it on has had a bad bug, which would 
really be
surprising and unusual. Anyway, you are right. That, in turn, implies that 
oracle
indexes are not classic B*Tree structures as I was lead to believe but are 
spiked with
an unknown liquor. Thanks for helping me clarify this.


Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

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

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

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

SQL Loader Concatenate date and time

2003-05-30 Thread Bob Metelsky

All
Im need  to concatenate my log_date and log_time field (the 2
physical records into one logical record).
 I can find how to do it with a fixed length file but my case is a
delimited file. Looking at the docs, it seems the continueif is used for
delimited dtaa, I cant seem to get the syntax to work

any ideas would be welcome ;-)
thanks!
Bob

LOAD DATA 
INFILE 'F:\528log.txt'
BADFILE 'F:\bad.txt'
truncate
INTO TABLE log
FIELDS TERMINATED BY WHITESPACE  OPTIONALLY enclosed by ''
TRAILING(
log_id INTEGER,
log_date DATE 'DD-MON-',
log_time char,
vpn_type char,
interface char,
name char,
type char,
action char,
service char,
source char,
destination char,
protocol char,
port char,
service2 char,
log_user char,
message char)

example source row data
283700 28May2003 16:28:12 fff eth-sfp1c0 fff Log Accept
nbdatagram 10.54.4.1 10.54.255.255 udp 23 nbdatagram  


Example oracle row data
283700  05/28/2003 00:00:00 16:28:12fff eth-s4fp1c0
fff Log Accept  nbdatagram  10.54.4.1   10.54.255.255
udp 23  nbdatagram  
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bob Metelsky
  INET: [EMAIL PROTECTED]

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

RE: is this DBA's only mailing list?




So, 
that means that our posts may be about the oracle of Delphi, the early 
leader
in the 
executive decision support area?
Idis 
redibis nunquam in bello peribis.

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

  -Original Message-From: Naveen Nahata 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, May 29, 2003 
  10:10 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: is this DBA's only mailing list?
  I'll 
  quote the list manager Jared's comments which he made in a mail long 
  back
  
  "This is an Oracle mailing List"
  
  So 
  youshould be allowed topost anything related to 
  Oracle.
  
  Regards
  Naveen
  
-Original Message-From: Ajay K. Garg 
[mailto:[EMAIL PROTECTED]Sent: Thursday, May 29, 2003 6:16 
PMTo: Multiple recipients of list ORACLE-LSubject: is 
this DBA's only mailing list?
HI everyone

I recently joined the list, and I think it is 
meant only for DBAs. Is that true?
Also, can anyone suggest any similar mailing 
list meant for Oracle developers (that is those working with SQL, PL/SQL, 
Forms, Oracle apps, etc.) and those who are not DBAs?

Thanks in advance
Ajay K. Garg

  
  DISCLAIMER:This message (including attachment if any) is confidential 
  and may be privileged. Before opening attachments please check them for 
  viruses and defects. MindTree Consulting Private Limited (MindTree) will not 
  be responsible for any viruses or defects or any forwarded attachments 
  emanating either from within MindTree or outside. If you have received this 
  message by mistake please notify the sender by return e-mail and delete this 
  message from your system. Any unauthorized use or dissemination of this 
  message in whole or in part is strictly prohibited. Please note that e-mails 
  are susceptible to change and MindTree shall not be liable for any improper, 
  untimely or incomplete transmission.

RE: Which method is more efficient

Jared - Thanks. Wow, I learn a new trick each day on this list!

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


-Original Message-
Sent: Wednesday, May 28, 2003 6:15 PM
To: Multiple recipients of list ORACLE-L


There are easier ways to test redo generation than mucking
about with logminer.


Update only the column that changes and check redo generation:

15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1

USERNAMESID NAME VALUE
--   -
JKSTILL  10 redo size0

1 row selected.

461 rows updated.

USERNAMESID NAME VALUE
--   -
JKSTILL  10 redo size  117,128

1 row selected.

Update all columns, only a single column has actually changed:


USERNAMESID NAME VALUE
--   -
JKSTILL  10 redo size0


1 row selected.

461 rows updated.


USERNAMESID NAME VALUE
--   -
JKSTILL  10 redo size  226,908

1 row selected.


Updating just the changed field is clearly more efficient.  I didn't test 
a comparison
between multiple columns, updating 1 at a time versus all at once, change 
only
columns with changed data.

Doing so would require all redo and rollback overhead N number of times 
rather
than just once, N being the number of changed columns.

Below are the scripts used for testing.

Jared


=


-- create test table
create table redo_test
as select
   OWNER
   ,TABLE_NAME
   ,TABLESPACE_NAME
   ,CLUSTER_NAME
   ,IOT_NAME
   ,PCT_FREE
   ,PCT_USED
   ,INI_TRANS
   ,MAX_TRANS
   ,INITIAL_EXTENT
   ,NEXT_EXTENT
   ,MIN_EXTENTS
   ,MAX_EXTENTS
   ,PCT_INCREASE
   ,FREELISTS
   ,FREELIST_GROUPS
   ,LOGGING
   ,BACKED_UP
   ,NUM_ROWS
   ,BLOCKS
   ,EMPTY_BLOCKS
   ,AVG_SPACE
   ,CHAIN_CNT
   ,AVG_ROW_LEN
from dba_tables
nologging
/


=

-- redo.sql
-- check redo size

col sid format 999 head 'SID'
col name format a40
col value format ,999,999,999 head 'VALUE'
col username format a10 head 'USERNAME'

break on username skip 1 on sid skip 1

select
   sess.username,
   stat.sid,
   name.name name,
   stat.value
from v$sesstat stat, v$statname name, v$session sess
where
   stat.sid = sess.sid
   and stat.sid = (
  select s.sid
  from v$session s, v$process p
  where p.addr = s.paddr
 and userenv('SESSIONID') = s.audsid
   )
   and stat.statistic# = name.statistic#
   and name.name like 'redo size'
order by name



=

-- test 1


@redo

update redo_test
set tablespace_name = reverse(tablespace_name)
/

@redo


rollback;


=
-- test 2



@redo

update redo_test
set
   owner = owner
   , tablespace_name = reverse(tablespace_name)
   , table_name = table_name
   , pct_used = pct_used
   , pct_free = pct_free
   , ini_trans = ini_trans
   , max_trans = max_trans
   , initial_extent = initial_extent
   , next_extent = next_extent
   , min_extents = min_extents
   , max_extents = max_extents
   , pct_increase = pct_increase
   , freelists = freelists
   , num_rows = num_rows
   , blocks = blocks
   , empty_blocks = empty_blocks
   , avg_space = avg_space
   , chain_cnt = chain_cnt
   , avg_row_len = avg_row_len
/

@redo


rollback;


=







DENNIS WILLIAMS [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 05/28/2003 09:59 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: Which method is more efficient


Bryan - If this is a critical issue, I would try it both ways on a test
database and use log miner to examine the amount of redo that is 
generated.
My recollection is that you will find that the redo record records the
before and after data for each field. So just updating all fields may
generate significantly more redo. But don't trust my recollection on this
issue, test it yourself.

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


-Original Message-
Sent: Wednesday, May 28, 2003 10:50 AM
To: Multiple recipients of list ORACLE-L


Hello everyone,

I have a question for the group of which

RE:

2003-05-30 Thread antonio . belloni


Hi Niall,

We´ve already tried this.

Anyway , thanks for your help.

Antonio


   
  
  Niall Litchfield   
  
  [EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
  .pipex.com   cc:
  
  Sent by:  Subject:  RE:  
  
  [EMAIL PROTECTED]
   
   
  
   
  
  28/05/2003 21:14 
  
  Please respond to
  
  ORACLE-L 
  
   
  
   
  




caveat
Yeah like I know forms or 9iAS, right
/caveat

Have you tried a UNC path eg \\serverb\sharename\form_name.fmx in place
of the drive mapping. In general for an app server you wouldn't
generally wish to rely on a logged in users environment.

Cheers

Niall

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
 [EMAIL PROTECTED]
 Sent: 28 May 2003 16:30
 To: Multiple recipients of list ORACLE-L
 Subject:


 We have two servers: Server A running iAS ; Server B running
 IDS 9 and serving files.

 We map one drive from server B to server A and we are trying
 to access the forms on the remote driver:

http://host:port/forms90/f90servlet?form=f:\form_name.fmx

But we receive an error saying that the servlet could not read the form
file.

If we copy the file from f:\ to a local drive , ie , c:\temp , the form
can be executed succesfully.

We are aware of a known bug issue in the old OAS that prevent using
forms apps running on remote drivers . but we think that it could (and
should) be fixed on iAS. Does anyone facing the same problem or trying
to do something similar on IAS ??

Both server are running Win2K.

Thanks in advance,
Antonio Belloni


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

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

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

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







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

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

10i

2003-05-30 Thread Freeman Robert - IL

Got my first look at the 10i beta last night. I can't tell you much about it
except to say that looking at some of the new stuff


h

;-)


While I'm certain many of the new features will not work perfectly for
several releases afterwards, they look very cool!

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

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

RE: dbms_stats broken

2003-05-30 Thread Hand, Michael T

Lisa,

I'm still using Analyze so I have no direct experience with this package.
That being said, would the SET_TABLE_STATS work for the partitions that
haven't been loaded yet?

Mike Hand
Polaroid Corp.


-Original Message-
Sent: Thursday, May 29, 2003 10:46 AM
To: Multiple recipients of list ORACLE-L


Hi Darrell  Kirti - 

It was late last night when I was looking at this.  It seems I should be
using GATHER EMPTY instead of GATHER STALE.  However this will put
statistics on partitions with no rows.

When I load new partitions tomorrow (they are empty prior to the daily
load), the number of rows inserted wouldn't reach 10% change.  It would take
over a week to reach 10% change in order for GATHER STALE to pick up on this
and re-analyze these partitions.  I don't want statistics saying there are 0
records in a partition that is indeed loaded.  

I guess GATHER STALE would be much more useful if the 10% threshold could be
modified, and/or the threshold could be partition specific.  

And Kirti, the bugs I have seen are 1192012, 1890016, 2157655.  I thought I
was running into 1890016.  

Looks like I'll have to code around this after all. Darn it all.  I really
am a monkey. Sheesh

Lisa


-Original Message-
Sent: Thursday, May 29, 2003 12:30 AM
To: Multiple recipients of list ORACLE-L


Lisa,

Wow, you might be saving me from peril right now.  I have tested this
with a small set of tables with no problems (in and 8.1.7.4 instance). 
I'm preparing to go 'schema' wide in the next week or so for further
testing prior to implementing in production.
I'd be very interested in more details of your problems in 8.1.7.4 and
of course I'll post reports of testing to the list as well.

For starters, here is the code I use to obtain a list of 'stale'
qualified tables:
( For proper credit, I think I got this from asktom.oracle.com)


set serverout on size 9

declare
l_objList dbms_stats.objecttab;
begin
dbms_stats.gather_schema_stats
( ownname = '1',
options = 'LIST STALE',
objlist = l_objlist );
for i in 1 .. l_objlist.count
loop
--dbms_output.put_line( l_objlist(i).objtype );
dbms_output.put_line( l_objlist(i).objname );
end loop;
end;
/


And the code to gather stats:

set serverout on size 99000

begin
dbms_stats.gather_schema_stats(
ownname='1',
options='GATHER STALE',
cascade=TRUE,
degree=8,
granularity='ALL',
method_opt='FOR ALL INDEXED COLUMNS SIZE 1'
);
end;
/


Thanks,
Darrell


 [EMAIL PROTECTED] 05/28/03 09:24PM 
Hello everyone, 
 
Is anyone using dbms_stats and gather stale or gather auto in 9.2? 
I'm
trying to use dbms_stats gather schema stats with the stale option and
it just isn't working in 8.1.7.4.  This is documented on Metalink. 
I'd
love to hear from someone else if this is fixed in 9.2 and if it can
be
reliably used. 
 
Thank you
Lisa Monkey.
 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

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

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

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

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

RE: [Q] Benchmark rate and ORACLE database?

Oracle doesn't do heavy maths so integer benchmarks should matter the most.
My advice is not to rely on standardized benchmarks but to ask the vendor
for references and then check those references as step one and then organize
a
little benchmark of your own that would use typical transaction(s) of your
company as a measuring unit. Once upon a time, when the world was much, much
younger, there used to exist a thingy called MIPS, which stood for
Marketing
Invention for Pushing Sales or Millions Instructions Per Second, I'm not
sure
any more. Test was conducted by the program called dhrystone, later made a
part
of the program suite called linpack. The trick with that benchmark was
that
dhrystone, in it's compiled form, took 3.85k of memory. You guessed it, DEC
came
out with 4k CPU caches and the program was executed entirely from the CPU
cache,
thus falsely declaring VAX boxes to be several times faster then they really
were.
To further own the benchmarking process, DEC came up with VUP (no, it's
not a
dog, it stands for Vax Units of Performance) which was a measure how many
times
is the measured system faster then VAX 782 (or MicroVAXII, later).
Naturally, DEC
used to own the process. DEC no longer exists, people do not remember VAXBI,
LAT,
DECNET, SET HOST, SET DEFAULT, VT220 or even the KED/EDT/EVE editors. DEC
was
notorious for benchmark cheating. Today, there is an independent
organization
for CPU benchmarks (www.specbench.org) and another one for relational
benchmarks
(www.tpc.org). Both are funded by vendors and notorious for the lack of
objectivity. CPU benchmarks are no longer the primary concern. People do, as
they should, conduct their own benchmarks to gage the needed machine
capacity.


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


-Original Message-
Sent: Thursday, May 29, 2003 9:20 AM
To: Multiple recipients of list ORACLE-L


I am compare the SUN server performance with ORACLE
database.  Can anyone
tell me  following 4 values which one  most import for
ORACLE database:

CINT2000
CFP2000
CINT2000 rates
CFP2000 rates


Thanks.


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: mike mon
  INET: [EMAIL PROTECTED]

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

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

RE: dbms_stats broken

Hi Darrell  Kirti - 

It was late last night when I was looking at this.  It seems I should be using GATHER 
EMPTY instead of GATHER STALE.  However this will put statistics on partitions with no 
rows.

When I load new partitions tomorrow (they are empty prior to the daily load), the 
number of rows inserted wouldn't reach 10% change.  It would take over a week to reach 
10% change in order for GATHER STALE to pick up on this and re-analyze these 
partitions.  I don't want statistics saying there are 0 records in a partition that is 
indeed loaded.  

I guess GATHER STALE would be much more useful if the 10% threshold could be modified, 
and/or the threshold could be partition specific.  

And Kirti, the bugs I have seen are 1192012, 1890016, 2157655.  I thought I was 
running into 1890016.  

Looks like I'll have to code around this after all. Darn it all.  I really am a 
monkey. Sheesh

Lisa


-Original Message-
Sent: Thursday, May 29, 2003 12:30 AM
To: Multiple recipients of list ORACLE-L


Lisa,

Wow, you might be saving me from peril right now.  I have tested this
with a small set of tables with no problems (in and 8.1.7.4 instance). 
I'm preparing to go 'schema' wide in the next week or so for further
testing prior to implementing in production.
I'd be very interested in more details of your problems in 8.1.7.4 and
of course I'll post reports of testing to the list as well.

For starters, here is the code I use to obtain a list of 'stale'
qualified tables:
( For proper credit, I think I got this from asktom.oracle.com)


set serverout on size 9

declare
l_objList dbms_stats.objecttab;
begin
dbms_stats.gather_schema_stats
( ownname = '1',
options = 'LIST STALE',
objlist = l_objlist );
for i in 1 .. l_objlist.count
loop
--dbms_output.put_line( l_objlist(i).objtype );
dbms_output.put_line( l_objlist(i).objname );
end loop;
end;
/


And the code to gather stats:

set serverout on size 99000

begin
dbms_stats.gather_schema_stats(
ownname='1',
options='GATHER STALE',
cascade=TRUE,
degree=8,
granularity='ALL',
method_opt='FOR ALL INDEXED COLUMNS SIZE 1'
);
end;
/


Thanks,
Darrell


 [EMAIL PROTECTED] 05/28/03 09:24PM 
Hello everyone, 
 
Is anyone using dbms_stats and gather stale or gather auto in 9.2? 
I'm
trying to use dbms_stats gather schema stats with the stale option and
it just isn't working in 8.1.7.4.  This is documented on Metalink. 
I'd
love to hear from someone else if this is fixed in 9.2 and if it can
be
reliably used. 
 
Thank you
Lisa Monkey.
 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

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

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

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

RE: Function Based Index - Not Used ???

2003-05-30 Thread Cunningham, Gerald

Wow, there sure are a lot of Scott's in India...

-Original Message-
Sent: Thursday, May 29, 2003 9:30 AM
To: Multiple recipients of list ORACLE-L


JP,

In the EXPLAIN PLAN, it says Card=262146, indicating that the query
expects to retrieve over a quarter-million rows.  Is that in fact
correct?

If so, the CBO is making the correct decision to perform a FULL table
scan. What was the comparison of elapsed times between the two plans,
the one being the FULL table scan and the other being the indexed scan?
I bet the FULL table scan query finished much more quickly...

You've got everything configured correctly -- simply a lousy index.  The
CBO has to be coerced into using the index because it is not the best
plan to use.

Hope this helps...

-Tim

on 5/28/03 11:54 PM, Prem Khanna J at [EMAIL PROTECTED] wrote:

 Guys,
 
 create table Tab1 ( name varchar2(100),age int,state 
 varchar2(100),country varchar2(100));
 
 insert into tab1 values ('SCOTT',25,'TN','India');
 
 I have 20,00,000 records like above.
 
 create index idx1 on tab1 (upper(name));
 
 analyze table tab1 compute statistics;
 analyze index idx1 compute statistics;
 
 select age from tab1 where upper(name)='SCOTT'; --- this will return 
 around 50 records.
 
 -
 0  SELECT STATEMENT Optimizer=CHOOSE (Cost=2458 Card=262146
Bytes=2097168)
 10   TABLE ACCESS (FULL) OF 'TAB1' (Cost=2458 Card=262146
Bytes=2097168)
 
 --- INIT.ORA --
 optimizer_mode=choose
 compatible=8.1.0
 query_rewrite_enabled=true
 query_rewrite_integrity=trusted
 ---
 It's Oracle 8.1.6/Win2K.
 
 When i add a HINT, IDX1 is used.Why is it so ?
 Why this SELECT does not use index IDX1 w/o a hint ?
 
 TIA.
 Jp.
 

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

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


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

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

RE: Problem with TOAD

2003-05-30 Thread Jesse, Rich

You will want to repost this to [EMAIL PROTECTED]  Many users as well as
the TOAD development team are on this list.

Or, since it's v7.3, you've obviously purchased TOAD (right?!?) and could
try http://www.quest.com/support/

HTH!  GL!


Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA


 -Original Message-
 From: Satya Prakash Viswanath [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, May 28, 2003 11:10 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Problem with TOAD 
 
 
 Hi List,
 
 I am facing a strange problem with TOAD (ver 7.3.0.0).
 
 I am not able to log onto my Oracle database server ( 9.2.0.1.0) thru
 TOAD. I am getting ORA-00980: synonym translation is no 
 longer valid.
 I do not know how this correlates with this error while 
 connecting. I am
 able to connect to any other database thru TOAD.
 I am able to connect to my database using SQL* PLUS but my 
 comfort level
 with TOAD is high that I do not want to revert to SQL * PLUS. ;)
 
 The things that I will list out are :
 1. Tried running the TOADPREP.sql from SQL*PLUS. The script goes thru
 fine without any problems.
 2. Nothing has happnened to my PC/Database since the last 
 time I logged
 in to my machine. Very normal behavior. 
 3. I can desribe all my toad specific tables from SQL* PLUS. 
 4. The strange thing now (after running TOADPREP.sql) is that i can
 access all user tables but not dba tables(dynamic performance views) 
 5. Stopped and restarted the servies but to no effect
 6. Even reinstalled TOAD but to no effect.
 7. I have checked the trace files, seems fine to me. 
 
 Even though this issue is specific to TOAD, plz help me on this. 
 I am in touch with the support also but I cannot wait for 1 day for
 their reply. 
 Any pointers?
 
 Regards,
 Satya Prakash
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

RE: [Q] Benchmark rate and ORACLE database?

2003-05-30 Thread Thater, William

used to own the process. DEC no longer exists, people do not remember VAXBI,
LAT,
DECNET, SET HOST, SET DEFAULT, VT220 or even the KED/EDT/EVE editors. DEC

hey, i resemble that remark... i even have all my DCL scripts and
customizations for edt and eve.;-)
--
Bill Shrek Thater   Oracle DBA
   [EMAIL PROTECTED]
--
When a man's knowledge is deep, he speaks well of an enemy. Instead of
seeking revenge, he extends unexpected generosity. He turns insult into
humor, ... and astonishes his adversary who finds no reason not to trust
him. - Baltasar Gracian 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thater, William
  INET: [EMAIL PROTECTED]

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

Capacity Planner question

2003-05-30 Thread Tierstein, Leslie

Has anybody used the Capacity Planner (SYSMAN) part of OEM? We are trying to
monitor usage of the Application Server, and have the repository set up to
do so. However, I can't quite figure out what busy servers means. The
number of busy servers is always very low, and doesn't seem to correlate
with the number of web server processes (ifweb60), reflected as PIDs in the
database activity. Is a server busy just when it is, at the precise second
of sampling, responding to a user's request? That's what it's starting to
seem like. And if this is the case, how do I get a good idea of what the
actual load on the application server is?

I realize this is a DB list, but maybe somebody has some expertise since it
is, after all, an OEM-related question. (Do real DBAs use OEM?) 

Thanks for any help you can offer,
Leslie
(not a DBA)
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tierstein, Leslie
  INET: [EMAIL PROTECTED]

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

2003-05-30 Thread Rodrigues, Bryan

Yes, I am in archive log mode and I have had 2 occurances of filling up the
archive log filesystem, but I don't want to change one problem for another.

Bryan

-Original Message-
Sent: Wednesday, May 28, 2003 3:20 PM
To: Multiple recipients of list ORACLE-L


have you run it? isnt that alot slower? you have alot of context switches
also. for every record to update, you then switch to SQL. 

what kind of efficiency improvement are you going for? Speed or cutting down
on redo? Are you in archivelog mode and dont want to blow up your archives? 
 
 From: Rodrigues, Bryan [EMAIL PROTECTED]
 Date: 2003/05/28 Wed PM 02:40:25 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: RE: Which method is more efficient
 
 The fields that are changed are determined by 
 1) A loop would start until all records in parts change table are done
 2) Select a part record from the part changes table
 3) Select the same part from the existing part table
 4) Compare the value in the parts changes table against the corresponding
 field in the part table 5) After comparing all fields in the records,
create
 record in a seperate work table with the values populated with null if the
 field values matched and the new value if the values did not.
 6) This loop would continue until all parts are done.
 7) After any records in the work table where all fields (outside of part
 number) are null are deleted.
 
 This process normally will decrease the number of records to be processed
 after this point by 75%.
 
 Hope that helps,
 
 Bryan
 
 
 -Original Message-
 Sent: Wednesday, May 28, 2003 1:21 PM
 To: Multiple recipients of list ORACLE-L
 
 
 oh i missed part of it. the question is how do you figure out which fields
 have changed? if you have to do an anti-join on each field, then do an
 update of every field. 
 
 the question is how will you determine which fields have changed? 
  
  From: DENNIS WILLIAMS [EMAIL PROTECTED]
  Date: 2003/05/28 Wed PM 12:59:51 EDT
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Subject: RE: Which method is more efficient
  
  Bryan - If this is a critical issue, I would try it both ways on a test
  database and use log miner to examine the amount of redo that is
 generated.
  My recollection is that you will find that the redo record records the
  before and after data for each field. So just updating all fields may
  generate significantly more redo. But don't trust my recollection on
this
  issue, test it yourself.
  
  Dennis Williams
  DBA, 80%OCP, 100% DBA
  Lifetouch, Inc.
  [EMAIL PROTECTED] 
  
  
  -Original Message-
  Sent: Wednesday, May 28, 2003 10:50 AM
  To: Multiple recipients of list ORACLE-L
  
  
  Hello everyone,
  
  I have a question for the group of which method is more efficient. 
  
  To set the stage my company has a process to load part changes from
 vendors
  into the tables in an 8.1.7.4 Oracle database with archiving on and this
  database has a standby database at disaster recovery site, so nologging
is
  not an option. 
  
  There is a discussion going on as to which method is more effective for
  updating the information in a table. In looking at effectiveness, I am
  looking at reducing the amount of redo information produced and having
the
  database do the least amount of work.
  
  1)  Method 1 is to update the information only for the fields that have
  changed, 1 field at a time.
  2)  Method 2 is to update the information for all the fields in the
  record whether they have changed or not, 1 record at a time.
  
  The size of the record is 1843 bytes and the distribution of field
sizes:
   2 fields varchar2(240).
   1 field varchar2(150)
  15 fields varchar2(50)
  1 field varchar2(3)
  2 fields varchar2(20)
  4 fields varchar2(40)
  3 fields varchar2(1)
  2 fields varchar2(25)
  2 fields number(10,2)
  1 field number(13,2)
  1 field number(1)
  1 field number
  1 field varchar2(6)
  1 field number (17,2)
  1 field varchar2(4)
  3 fields that are date.
  
  In the past couple of months the average number of fields changed per
 record
  was 3 to 4 fields per record.
  
  Thanks for your help,
  
  Bryan Rodrigues
  Oracle DBA
  Elcom, Inc.
  
  
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: Rodrigues, Bryan
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: DENNIS

RE: skip scan index

2003-05-30 Thread Pete Sharman

You know, of all the impressive things I've ever heard Cary Millsap say (and
there have been a heck of a lot), the one that stands out the most in my
memory is a series of quotes from his keynote at the 2003 Hotsos Symposium:

An experiment that disproves a conclusion is a success.
Knowledge in your head is less valuable than knowledge you share.
Show your work
Show all your data
 Even when it contradicts your conclusion
No: ***especially*** when it contradicts your conclusion.

And a quote from Richard Feynman in that same presentation:

Details that could throw doubt on your interpretation must be given, if you
know them.  You must do the best you can-if you know anything wrong at all,
or possibly wrong-to explain it.

Mladen, you're a star.  If only we could all be open about the times we are
wrong as you've been!

Pete
Controlling developers is like herding cats.
Kevin Loney, Oracle DBA Handbook
Oh no, it's not.  It's much harder than that!
Bruce Pihlamae, long-term Oracle DBA
 


-Original Message-
Sent: Wednesday, May 28, 2003 7:08 PM
To: Multiple recipients of list ORACLE-L


Here is the idea: 
Index test_skip1 is located in the tablespace INDX which has one file,
FILE#=5

I restart the database, execute your query, then see V$FILESTAT for blocks
read. (select PHYBLKRD from v$filestat where file#=5;)

Then restart the database, execute query asking for a fast full scan and see

how many blocks do get read. If the number is the same, then the conclusion
is inevitable. So, here we go:



SQL set autotrace on explain
SQL select /*+ index_ss(test_skip1 ) */
c1,c2
from test_skip1 where c2 = 100;
  23
C1 C2
-- --
 1100
 2100


Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=302 Bytes=78
  52)

   10   INDEX (SKIP SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=11 Car
  d=302 Bytes=7852)

SQL select PHYBLKRD from v$filestat where file#=5;

  PHYBLKRD
--
10

---DATABASE RESTART---


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning option
JServer Release 9.2.0.3.0 - Production

SQL set autotrace on
SQL select /*+ index_ffs(t test_skip1_pk ) */
c1,c2
from test_skip1 t where c2 = 100;
  23
C1 C2
-- --
 1100
 2100


Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=302 Bytes=785
  2)

   10   INDEX (FAST FULL SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=4
   Card=302 Bytes=7852)





Statistics
--
300  recursive calls
  0  db block gets
777  consistent gets
724  physical reads
  0  redo size
464  bytes sent via SQL*Net to client
503  bytes received via SQL*Net from client
  4  SQL*Net roundtrips to/from client
  6  sorts (memory)
  0  sorts (disk)
  2  rows processed

SQL select PHYBLKRD from v$filestat where file#=5;

  PHYBLKRD
--
   722


That means that fast full scan will read 722 blocks where skip scan will
read only 10, which means that you were right and I was wrong. Obviously, my
metodology was incorrect or 9.2.0.1 database that I've tested it on has had
a bad bug, which would really be 
surprising and unusual. Anyway, you are right. That, in turn, implies that
oracle 
indexes are not classic B*Tree structures as I was lead to believe but are
spiked with an unknown liquor. Thanks for helping me clarify this.



On 2003.05.28 18:29 Khedr, Waleed wrote:
 It's like any other execution plan, good in certain data distributions 
 and bad in others.
 
 But I do not think it's correct that skip scan requires reading the 
 whole index (it's even clear in this test).
 
 Waleed
 
 -Original Message-
 Sent: Wednesday, May 28, 2003 5:30 PM
 To: Multiple recipients of list ORACLE-L
 
 
 OK. I don't have the 9i instance that I can use for testing right now, 
 but tonight, at home, I'll give you the counter example. The bottom 
 line is that the only way to execute a skip scan with a B*Tree index 
 is to go and read it whole. No other way.
 
 Mladen Gogala
 Oracle DBA
 Phone:(203) 459-6855
 Email:[EMAIL PROTECTED]
 
 
 -Original Message-
 Sent: Wednesday, May 28, 2003 4:40 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Not true, try this:
 
 create table test_skip1 ( c1 number,c2 number, primary key (c1,c2));
 
 begin
  for i in 1..10 loop
   insert into test_skip1 values (1,i);
   insert into test_skip1 values (2,i);
  end loop;
  end;
 
 
 alter session set sql_trace = true;
 
 select --+ index_ss(test_skip1, )
  c1,c2
  from test_skip1
  where c2 = 100;
 
  select blocks from dba_segments where segment_name = 'SYS_C0038241'

Re: Online index creation on 9.2

2003-05-30 Thread Jeremiah Wilton

What are the wait events of the slowed sessions?

What is the correspondence with v$session_longops phases for the
rebuild?

Really, the answer to 75% of the questions in this group is look at
v$session_wait.

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

On Thu, 29 May 2003, Stephen Lee wrote:

 
 On 9.2.0.3, I have experienced a problem with online index creation.  When
 creating indexes on what I suppose most would consider to be large tables
 (with nasty, sharp, pointy teeth and a bad disposition), when Oracle is
 doing its initial data gathering, sorting, slicing, and dicing in
 preparation for the actual creation of the index, it appears that normal
 access to the table -- and this includes selects which do not modify data --
 becomes severely reduced to the point that the application is no longer
 usable.  When Oracle finishes with its fact finding mission and begins to
 build the index (as evidence by the appearance of a segment with a number
 for a name), everything is OK again.  We never had this problem with
 8.1.7.X.
 
 Is this a new feature of 9.2.0.3?  Is there some new magic parameter that
 needs to be set?  Are we wearing the wrong grass skirts and/or incorrectly
 performing our ritual sacrifice ceremonies?

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

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

Re: Export Long Dies with no error

2003-05-30 Thread Joe Frohne

Hi,

I can't speculate as to why the export dies, but you can correct the
direct=y character set error by setting the NLS_LANG variable in your user
profile to whatever character set it is trying to export from.

--
Joe Frohne
Rawson Oaks Consulting
http://www.rawsonoaks.com
[EMAIL PROTECTED] or [EMAIL PROTECTED]


 I am running an Oracle 8.1.7 export on a 2G table which contains a Long
 column.  The export runs for several hours and then just dies with no
 error
 anywhere.  I tried to use direct=y but I get an error about character
 sets.
 Any ideas?

 Thanks!
 Ron Smith
 If you are not the intended recipient of this e-mail message, any use,
 distribution or copying of the message is prohibited.  Please let me know
 immediately by return e-mail if you have received this message by mistake,
 then delete the e-mail message. Thank you.
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Smith, Ron L.
   INET: [EMAIL PROTECTED]

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






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

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

RE: is this DBA's only mailing list?




DBAs 
only. No trespassing! Trespassers will be shot at. Do not go beyond 
this
point 
if you're not a DBA. We do not want developers to learn or secrets. That 
might
give 
them an idea that we're mere mortals.

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

  -Original Message-From: Ajay K. Garg 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, May 29, 2003 8:46 
  AMTo: Multiple recipients of list ORACLE-LSubject: is 
  this DBA's only mailing list?
  HI everyone
  
  I recently joined the list, and I think it is 
  meant only for DBAs. Is that true?
  Also, can anyone suggest any similar mailing list 
  meant for Oracle developers (that is those working with SQL, PL/SQL, Forms, 
  Oracle apps, etc.) and those who are not DBAs?
  
  Thanks in advance
  Ajay K. Garg

RE: Which method is more efficient

2003-05-30 Thread Jamadagni, Rajendra

Title: RE: Which method is more efficient





Jared,


Agreed, but what about the resources needs to find _which_ column changed ?? Would that offset the extra redo generated? Heck, I'd just generate the update statements based on two tables to _only_ update the changed columns. It is pretty easy, if both tables have _same_ columns ...

Raj

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



-Original Message-
Sent: Wednesday, May 28, 2003 6:15 PM
To: Multiple recipients of list ORACLE-L



There are easier ways to test redo generation than mucking about with logminer.


Update only the column that changes and check redo generation:
15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1


[ much stuff deleted ]



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

x$bh.dbablk values (repost)

2003-05-30 Thread Daniel W. Fink

I am reposting this in the hopes that someone can help me with this puzzler.

I am working on determining which objects have 'hot blocks'.
In two different sessions, I issue select count(*) from random_data 
where rowid_rownum in (1,2,3); repeatedly to see what happens with the 
touch count (x$bh.tch).

In another session, I look for the blocks related to this object by 
issuing the statement:
select x.owner, x.segment_name, x.segment_type, b.dbarfil, b.dbablk, 
b.class, b.state, sum(b.tc
from dba_extents x, x$bh b
where b.dbarfil = x.file_id
and b.dbablk between x.block_id and (x.block_id + blocks - 1)
and x.owner = 'BCA'
group by x.owner, x.segment_name, x.segment_type, b.dbarfil, b.dbablk, 
b.class, b.state;

and the output is
OWNER  SEGMENT_NAME  SEGMENT_TYPE   DBARFIL 
DBABLK  CLASS  STATE SUM(B.TCH)
-- - --- -- 
-- -- -- ---
BCAHWM_DATA  TABLE   12  
12809  4  3  2
BCAFRAG_DATA TABLE   12  
19209  4  3  2
BCAPLAN_TABLETABLE   12  
25609  4  3  2
BCARANDOM_DATA   TABLE   12   
6409  4  1  5
BCARANDOM_DATA   TABLE   12  
12729  1  1  0
BCARANDOM_DATA   TABLE   12  
12730  1  1  0
BCARANDOM_DATA   TABLE   12  
12731  1  1  0
BCARANDOM_DATA   TABLE   12  
12732  1  1  0
BCARANDOM_DATA   TABLE   12  
12733  1  1  0
BCARANDOM_DATA   TABLE   12  
12745  1  1  0
BCARANDOM_DATA   TABLE   12  
12746  1  1  0
BCARANDOM_DATA   TABLE   12  
12747  1  1  0
BCARANDOM_DATA   TABLE   12  
12748  1  1  0
BCARANDOM_DATA   TABLE   12  
12749  1  1  0
BCARANDOM_DATA   TABLE   12  
12750  1  1  0
BCARANDOM_DATA   TABLE   12  
12751  1  1  0
BCARANDOM_DATA   TABLE   12  
12752  1  1  0
BCARANDOM_DATA   TABLE   12  
12753  1  1  0
BCARANDOM_DATA   TABLE   12  
12754  1  1  0
BCARANDOM_DATA   TABLE   12  
12755  1  1  0
BCAIX_SD_ROWNUM  INDEX   13   
1929  4  3  2
BCAPK_FD_REC_NO  INDEX   13   
8993  4  3  2
BCAPK_HD_REC_NO  INDEX   13   
8969  4  3  2
BCAPK_RD_REC_NO  INDEX   13   
3977  4  3  2
BCAPK_SD_REC_NO  INDEX   13  
9  4  3  2
BCAIX_RD_SMALL_RNINDEX   13   
3985  4  3  2
BCASEQUENTIAL_DATA   TABLE   12  
9  4  3  2

The oddity is that I have restarted the instance and have only issued 
queries against the random_data table. Since I am the only user on the 
system, I know that no other sessions are accessing the objects.
The interesting bit in all this is that the blocks other than 
random_data listed in x$bh are the segment headers.

select segment_name, file_id, block_id, blocks, block_id+blocks-1
from dba_extents
where owner = 'BCA'
 and extent_id = 0
order by file_id, block_id;
SEGMENT_NAME FILE_ID   BLOCK_ID BLOCKS 
BLOCK_ID+BLOCKS-1
- -- -- -- 
-
SEQUENTIAL_DATA   12  9  
816
RANDOM_DATA   12   6409  8  
6416
HWM_DATA  12  12809  8 
12816
FRAG_DATA 12  19209  8 
19216
PLAN_TABLE12  25609  8 
25616
PK_SD_REC_NO  13  9  
816

Re: Unbreakable, my buns. Upgrade to 9.2/w2k

2003-05-30 Thread Yechiel Adar

Title: Unbreakable, my buns. Upgrade to 9.2/w2k



For my OID testing I decided to go with 
9.2.0.3.
I installed9.2.0.1 (software only) and the upgraded 
to 9.2.0.3 with no problems.
Since I also documented the process so I will have all the 
steps down when I will do it in production I did it twice.
Since I wanted to avoid upgrading the OID database I built 
it only after I upgraded the software.

Yechiel AdarMehish

  - Original Message - 
  From: 
  Koivu, Lisa 
  To: Multiple recipients of list ORACLE-L 
  Sent: Wednesday, May 28, 2003 6:25 
  PM
  Subject: Unbreakable, my buns. Upgrade to 
  9.2/w2k
  
  For those of you who have upgraded to 9.2 on 
  w2k: 
  How many of you have ended up with such a hosed 
  server that you had to completely wipe Oracle off of it and start with a fresh 
  install (9.2)? It was spectacular! I ended up in this situation 
  and I sure hope it's something I did or didn't do. I sure don't have 
  time for this when I upgrade my production databases. How I long for 
  those days wading through problems in unix... it was so much easier and more 
  enjoyable... I will say this was definately a learning experience. 
  
  And Tom Mercadante, your document worked like a 
  charm, and saved my a$$... Muchas Gracias! 
  Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 
  Coconut Creek Parkway Ft. Lauderdale, FL, 
  USA 33063 Office: 954-935-4117 
  Fax: 954-935-3639 
  Cell: 954-683-4459 
  -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 
  Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services -- 
  858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and 
  web hosting services 
  - To 
  REMOVE yourself from this mailing list, send an E-Mail message to: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message 
  BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list 
  you want to be removed from). You may also send the HELP command for other 
  information (like subscribing).

RE: dbms_stats broken

2003-05-30 Thread John Kanagaraj

Lisa et al,

Am I missing something or did someone ever mention that for STALE to work,
one needs to set MONITORING on required objects? Straight from the Fine
Manual:

Enabling Automated Statistics Gathering 
The GATHER STALE option only gathers statistics for tables that have stale
statistics and for which you have enabled the MONITORING attribute. To
enable monitoring for tables, use the MONITORING keyword of the CREATE TABLE
and ALTER TABLE statements, as described in Designating Tables for
Monitoring and Automated Statistics Gathering on page 8-9.

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

Great, uplifting music (and best of all commercial-free!) -
http://www.klove.com

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


 -Original Message-
 From: Darrell Landrum [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, May 28, 2003 9:30 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Re: dbms_stats broken
 
 
 Lisa,
 
 Wow, you might be saving me from peril right now.  I have tested this
 with a small set of tables with no problems (in and 8.1.7.4 
 instance). 
 I'm preparing to go 'schema' wide in the next week or so for further
 testing prior to implementing in production.
 I'd be very interested in more details of your problems in 8.1.7.4 and
 of course I'll post reports of testing to the list as well.
 
 For starters, here is the code I use to obtain a list of 'stale'
 qualified tables:
 ( For proper credit, I think I got this from asktom.oracle.com)
 
 
 set serverout on size 9
 
 declare
 l_objList dbms_stats.objecttab;
 begin
 dbms_stats.gather_schema_stats
 ( ownname = '1',
 options = 'LIST STALE',
 objlist = l_objlist );
 for i in 1 .. l_objlist.count
 loop
 --dbms_output.put_line( l_objlist(i).objtype );
 dbms_output.put_line( l_objlist(i).objname );
 end loop;
 end;
 /
 
 
 And the code to gather stats:
 
 set serverout on size 99000
 
 begin
 dbms_stats.gather_schema_stats(
 ownname='1',
 options='GATHER STALE',
 cascade=TRUE,
 degree=8,
 granularity='ALL',
 method_opt='FOR ALL INDEXED COLUMNS SIZE 1'
 );
 end;
 /
 
 
 Thanks,
 Darrell
 
 
  [EMAIL PROTECTED] 05/28/03 09:24PM 
 Hello everyone, 
  
 Is anyone using dbms_stats and gather stale or gather auto in 9.2? 
 I'm
 trying to use dbms_stats gather schema stats with the stale option and
 it just isn't working in 8.1.7.4.  This is documented on Metalink. 
 I'd
 love to hear from someone else if this is fixed in 9.2 and if it can
 be
 reliably used. 
  
 Thank you
 Lisa Monkey.
  
  
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Darrell Landrum
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

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

RMAN - Remote vs Local Backups

2003-05-30 Thread Walter K

Hi,

Can anyone think of a reason(s) why oneWOULD want to backup a database from a box other than the database box itself? Are there any advantages to this kind of configuration?

For example:

Box-A (production db server)
Box-B (rman db server)

A cron job runs on Box-B which backups upthe databasefrom Box-A.


Thanks in advance!

-w

RE: [Q] Benchmark rate and ORACLE database?

Mladen - Excellent points. When someone points out the vendor with the
highest benchmark results, I ask Jeff Gordon is a consistent NASCAR winner
and he drives a Chevrolet, does this mean that you will be buying a Chevy
next?. To me the published benchmark results have about as much to do with
database choice as NASCAR has to do with automobile choice.

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


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


Oracle doesn't do heavy maths so integer benchmarks should matter the most.
My advice is not to rely on standardized benchmarks but to ask the vendor
for references and then check those references as step one and then organize
a
little benchmark of your own that would use typical transaction(s) of your
company as a measuring unit. Once upon a time, when the world was much, much
younger, there used to exist a thingy called MIPS, which stood for
Marketing
Invention for Pushing Sales or Millions Instructions Per Second, I'm not
sure
any more. Test was conducted by the program called dhrystone, later made a
part
of the program suite called linpack. The trick with that benchmark was
that
dhrystone, in it's compiled form, took 3.85k of memory. You guessed it, DEC
came
out with 4k CPU caches and the program was executed entirely from the CPU
cache,
thus falsely declaring VAX boxes to be several times faster then they really
were.
To further own the benchmarking process, DEC came up with VUP (no, it's
not a
dog, it stands for Vax Units of Performance) which was a measure how many
times
is the measured system faster then VAX 782 (or MicroVAXII, later).
Naturally, DEC
used to own the process. DEC no longer exists, people do not remember VAXBI,
LAT,
DECNET, SET HOST, SET DEFAULT, VT220 or even the KED/EDT/EVE editors. DEC
was
notorious for benchmark cheating. Today, there is an independent
organization
for CPU benchmarks (www.specbench.org) and another one for relational
benchmarks
(www.tpc.org). Both are funded by vendors and notorious for the lack of
objectivity. CPU benchmarks are no longer the primary concern. People do, as
they should, conduct their own benchmarks to gage the needed machine
capacity.


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


-Original Message-
Sent: Thursday, May 29, 2003 9:20 AM
To: Multiple recipients of list ORACLE-L


I am compare the SUN server performance with ORACLE
database.  Can anyone
tell me  following 4 values which one  most import for
ORACLE database:

CINT2000
CFP2000
CINT2000 rates
CFP2000 rates


Thanks.


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: mike mon
  INET: [EMAIL PROTECTED]

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

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

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

RE: HELP - Microsoft Access error SQLSetConnectAttr failed error.

2003-05-30 Thread Paula_Stankus

Title: RE: HELP - Microsoft Access error SQLSetConnectAttr failed error.





However, I was able to independently through odbctest and tnsping confirm that the odbc driver working and the alias working through Oracle's Net8. I also noticed with the odbctest that the user had a very small and limited amount of tables to view from all_tables. Could that be the reason that we are getting this error from Access?

Thanks,
Paula

RE: lengthy URL's

Someone else told me it was blocked.

Personally, I would take it up with my security admin.

tinyurl.com simply redirects your browser, it isn't a
back door to anything, and has no objectionable content.

If a tinyurl url points to a URL that is caught by your firewall,
that is an entirely different matter.

Jared






Thater, William [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 05/29/2003 06:19 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: lengthy URL's




 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, May 28, 2003 6:15 PM
 To: Multiple recipients of list ORACLE-L
 Subject: lengthy URL's
 
 
 Just a slightly OT post here.
 
 It isn't unusual when writing to this list to come up with 
 a few URL's to include in an email. 
 
 The problem at times is that the URL's can easily be
 200 characters in length, meaning that those reading
 your email must cut and paste the URL's to make use 
 of them.
 
 An easy solution to this is to make use of www.tinyurl.com

and find it blocked her at work.;-)

--
Bill Shrek Thater   Oracle DBA
   [EMAIL PROTECTED]
--
The Truth is realized in an instant; the Act is practiced step by step. -
Zen saying
www.mailfiler.com [JS-5BKXSC4]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thater, William
  INET: [EMAIL PROTECTED]

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




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

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

RE: 10i

Robert - Does this mean we can expect a new book Oracle10i New Features by
that respected Oracle author Robert Freeman coincident with the 10i release?
I'm ready!

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


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


Got my first look at the 10i beta last night. I can't tell you much about it
except to say that looking at some of the new stuff


h

;-)


While I'm certain many of the new features will not work perfectly for
several releases afterwards, they look very cool!

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

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

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

RE: SAP Hands SAP DB over to MySQL

2003-05-30 Thread Kip . Bryant

Everybody is under cost pressures these days, right?  Recently my boss required
that I attend a few webinars hosted by Microsoft to hear their line on TCO
because he didn't want the choice of our next platform to be a technical
decision (we're on Tru64 unix).  I don't think I'll be encouraged to consider
MySQL, postgre sql, linux, etc anytime soon for mission critical apps but who
knows?  

SAP has been sparring with Oracle for at least the past 10 years.  SAP's 
success has helped Oracle sell their product which I guess is irksome to SAP
since they see Oracle as a competitor.  At one point, SAP was encouraging new 
customers to select Informix.  Now it seems to be DB2 and/or SAP DB...  At 
sapteched this year, their analysis of the database market was that it was at 
a point where features had become a commodity but commodity pricing hadn't 
happened yet...but that it would.  They were calling Oracle/IBM/Microsoft the
dominant options and then comparing them with SAP DB.  I don't recall any talk 
about MySQL, postgresql...

Kip

|Okay, I've got a question for you people knowledgeable in SAP. Who would
|trust their financial and payroll data to MySQL? I'm not saying this to
|knock MySQL. It is just that your financial and payroll data is among your
|most valuable data in the corporation. Only recently was a transaction
|capability added to MySQL, and that was more of an add-on. I manage
|several databases and my company even makes modest use of MySQL. But of
|those databases, the financial and payroll data would be the LAST database I
|would convert to something like MySQL. I'm just curious what is the big
|incentive in MySQL is.

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


|-Original Message-
|Sent: Wednesday, May 28, 2003 5:50 PM
|To: Multiple recipients of list ORACLE-L


|Maybe it is just so they can continue to say they're not a database
|company
|(insert sound of condescension) to emphasize their focus on applications
|excellence in the veiled jabs they continue to make at Oracle.  On the
|other
|hand, I can't imagine they would give up development control because they do

|make a specialized version of SAP DB for object oriented programming
|(they say they couldn't find a product that worked correctly...).

|Kip

||I dunno.  Though both want to make a profit ( and rightly so ) SAP
||doesn't seem to have the same mercenary mentality that MS has.

||Jared


||Orr, Steve [EMAIL PROTECTED]
||Sent by: [EMAIL PROTECTED]
|| 05/28/2003 11:52 AM
|| Please respond to ORACLE-L

 
||To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
||cc:
||Subject:RE: SAP Hands SAP DB over to MySQL


||Reminiscent of the M$/Sybase partnership?


||-Original Message-
||Sent: Wednesday, May 28, 2003 11:40 AM
||To: '[EMAIL PROTECTED]'


||At http://www.sapdb.org/7.4/pdf/sapdb_letter.pdf SAP offers
|clarification:

||SAP:
||Contrary to erroneous press reports, SAP AG has not given up any rights
||concerning the SAP DB code base nor handed over or even sold SAP DB to
||MySQL AB.

||SAP:
||SAP AG remains responsible for ongoing development and support.

||CNet:
||MySQL will take over most of the development of SAP DB.



||-Original Message-
||Sent: Wednesday, May 28, 2003 12:01 PM
||To: Multiple recipients of list ORACLE-L
||Importance: High


||The past few months I've been wondering when MySQL would start
||putting pressure on Oracle in the same way that Linux is putting
||pressure on MS.

||Maybe sooner than you think:

||http://news.com.com/2100-1012_3-1010522.html?tag=fd_top


||Jared

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

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

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

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




||--
||Please see

RE: Which method is more efficient

dunno about that.  I was making the assumption that *which* columns
changed was already known.

This would require testing by someone familiar with the data.

Jared






Jamadagni, Rajendra [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 05/29/2003 10:24 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: Which method is more efficient


Jared, 
Agreed, but what about the resources needs to find _which_ column changed 
?? Would that offset the extra redo generated? Heck, I'd just generate the 
update statements based on two tables to _only_ update the changed 
columns. It is pretty easy, if both tables have _same_ columns ...
Raj 
 
Rajendra dot Jamadagni at nospamespn dot com 
All Views expressed in this email are strictly personal. 
QOTD: Any clod can have facts, having an opinion is an art ! 

-Original Message- 
Sent: Wednesday, May 28, 2003 6:15 PM 
To: Multiple recipients of list ORACLE-L 

There are easier ways to test redo generation than mucking about with 
logminer. 
Update only the column that changes and check redo generation: 
15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1 
[  much stuff deleted  ] 

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: SAP Hands SAP DB over to MySQL

2003-05-30 Thread Goulet, Dick

Dennis,

We're making very restricted use of MySql, but a greater use of PostGreSql 
which has atomic transactions already.  I would say the two biggest reasons for 
converting are $$$  and speed.  Otherwise if $$$ are the only consideration go with 
PostGreSql, it's totally free.  MySql has a very modest cost today, bet it will get 
bigger in the near future.  And BTW, all of our Financial, human resource, payroll, 
etc (read that as not outside the firewall) data is inside Oracle.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 

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


Okay, I've got a question for you people knowledgeable in SAP. Who would
trust their financial and payroll data to MySQL? I'm not saying this to
knock MySQL. It is just that your financial and payroll data is among your
most valuable data in the corporation. Only recently was a transaction
capability added to MySQL, and that was more of an add-on. I manage
several databases and my company even makes modest use of MySQL. But of
those databases, the financial and payroll data would be the LAST database I
would convert to something like MySQL. I'm just curious what is the big
incentive in MySQL is.

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


-Original Message-
Sent: Wednesday, May 28, 2003 5:50 PM
To: Multiple recipients of list ORACLE-L


Maybe it is just so they can continue to say they're not a database
company
(insert sound of condescension) to emphasize their focus on applications 
excellence in the veiled jabs they continue to make at Oracle.  On the
other
hand, I can't imagine they would give up development control because they do

make a specialized version of SAP DB for object oriented programming
(they say they couldn't find a product that worked correctly...).  

Kip  

|I dunno.  Though both want to make a profit ( and rightly so ) SAP
|doesn't seem to have the same mercenary mentality that MS has.

|Jared


|Orr, Steve [EMAIL PROTECTED]
|Sent by: [EMAIL PROTECTED]
| 05/28/2003 11:52 AM
| Please respond to ORACLE-L

 
|To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
|cc:
|Subject:RE: SAP Hands SAP DB over to MySQL


|Reminiscent of the M$/Sybase partnership?


|-Original Message-
|Sent: Wednesday, May 28, 2003 11:40 AM
|To: '[EMAIL PROTECTED]'


|At http://www.sapdb.org/7.4/pdf/sapdb_letter.pdf SAP offers
clarification:

|SAP:
|Contrary to erroneous press reports, SAP AG has not given up any rights
|concerning the SAP DB code base nor handed over or even sold SAP DB to
|MySQL AB.

|SAP:
|SAP AG remains responsible for ongoing development and support.

|CNet:
|MySQL will take over most of the development of SAP DB.



|-Original Message-
|Sent: Wednesday, May 28, 2003 12:01 PM
|To: Multiple recipients of list ORACLE-L
|Importance: High


|The past few months I've been wondering when MySQL would start
|putting pressure on Oracle in the same way that Linux is putting
|pressure on MS.

|Maybe sooner than you think:

|http://news.com.com/2100-1012_3-1010522.html?tag=fd_top


|Jared

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

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

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

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




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

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

RE: exp, dbms_stats, RMAN and rollback segments

HELP...
Has anyone encountered rollback problems while running dbms_stats?


-Original Message-
Sent: Thursday, May 29, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L


Oh yeah, for the export consistent=N

-Original Message-
Sent: Thursday, May 29, 2003 9:14 AM
To: '[EMAIL PROTECTED]'


A certain alignment of the planets occurred creating a good ole ORA-01555 error... A 
user level export received the snapshot too old error and terminated. Concurrent to 
this was an RMAN backup and DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on 
the same schema being backed up via the user level export. There was no other end user 
access to the schema data. Since exp got the error I assume it was reading from the 
rollback segments but why? I'm suspecting dbms_stats. We have ample RBS. Is there any 
significant undo generated by dbms_stats or RMAN which could create this problem? 

(Of course we need to improve our job scheduling but that's another issue, the timing 
of the user level export is application driven and out of our control). 


Befuddled in Bozeman,
Walt and Steve
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Orr, Steve
  INET: [EMAIL PROTECTED]

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

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

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

Re: 10i

2003-05-30 Thread Ron Thomas


TEASE!

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


   
 
  [EMAIL PROTECTED]
 
  Sent by: To:   [EMAIL PROTECTED] 
  
  [EMAIL PROTECTED] cc:
  
   Subject:  10i   
 
   
 
  05/29/2003 09:54 
 
  AM   
 
  Please respond to
 
  ORACLE-L 
 
   
 
   
 




Got my first look at the 10i beta last night. I can't tell you much about it
except to say that looking at some of the new stuff


h

;-)


While I'm certain many of the new features will not work perfectly for
several releases afterwards, they look very cool!

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

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





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

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

RE: dbms_stats

Hi John, 

Yes, monitoring was set.  I wouldn't see anything in *tab_modifications if monitoring 
wasn't set. 

Here's a new twist.  What percentage are you comfortable with for valid estimates?  I 
attended a seminar given by Jonathan Lewis a few weeks ago, and he stated that 
adequate statistics can be gathered using 1% sample.  That was great news to me, who 
has time for huge estimates?  OK, so I ran dbms_stats.gather_schema_stats using GATHER 
EMPTY and estimate_percent set to 1.  The rowcounts are way wrong. 

(FACP-LISA)SELECT COUNT(*) 
  2  FROM VEGAS_MART 
  3  PARTITION (MAY_28_2003);

  COUNT(*)
--
603826

(FACP-LISA)select num_rows 
  2  from dba_tab_partitions
  3  where table_name = 'VEGAS_MART'
  4  and partition_name = 'MAY_28_2003';

  NUM_ROWS
--
595500

(FACP-LISA)

And Jonathan if you happen to read this email, if I am mis-stating what you stated in 
class please correct me.  I am on 8.1.7.4 and that may be the difference. 



-Original Message-
Sent: Thursday, May 29, 2003 1:36 PM
To: Multiple recipients of list ORACLE-L


Lisa et al,

Am I missing something or did someone ever mention that for STALE to work,
one needs to set MONITORING on required objects? Straight from the Fine
Manual:

Enabling Automated Statistics Gathering 
The GATHER STALE option only gathers statistics for tables that have stale
statistics and for which you have enabled the MONITORING attribute. To
enable monitoring for tables, use the MONITORING keyword of the CREATE TABLE
and ALTER TABLE statements, as described in Designating Tables for
Monitoring and Automated Statistics Gathering on page 8-9.

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

Great, uplifting music (and best of all commercial-free!) -
http://www.klove.com

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


 -Original Message-
 From: Darrell Landrum [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, May 28, 2003 9:30 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Re: dbms_stats broken
 
 
 Lisa,
 
 Wow, you might be saving me from peril right now.  I have tested this
 with a small set of tables with no problems (in and 8.1.7.4 
 instance). 
 I'm preparing to go 'schema' wide in the next week or so for further
 testing prior to implementing in production.
 I'd be very interested in more details of your problems in 8.1.7.4 and
 of course I'll post reports of testing to the list as well.
 
 For starters, here is the code I use to obtain a list of 'stale'
 qualified tables:
 ( For proper credit, I think I got this from asktom.oracle.com)
 
 
 set serverout on size 9
 
 declare
 l_objList dbms_stats.objecttab;
 begin
 dbms_stats.gather_schema_stats
 ( ownname = '1',
 options = 'LIST STALE',
 objlist = l_objlist );
 for i in 1 .. l_objlist.count
 loop
 --dbms_output.put_line( l_objlist(i).objtype );
 dbms_output.put_line( l_objlist(i).objname );
 end loop;
 end;
 /
 
 
 And the code to gather stats:
 
 set serverout on size 99000
 
 begin
 dbms_stats.gather_schema_stats(
 ownname='1',
 options='GATHER STALE',
 cascade=TRUE,
 degree=8,
 granularity='ALL',
 method_opt='FOR ALL INDEXED COLUMNS SIZE 1'
 );
 end;
 /
 
 
 Thanks,
 Darrell
 
 
  [EMAIL PROTECTED] 05/28/03 09:24PM 
 Hello everyone, 
  
 Is anyone using dbms_stats and gather stale or gather auto in 9.2? 
 I'm
 trying to use dbms_stats gather schema stats with the stale option and
 it just isn't working in 8.1.7.4.  This is documented on Metalink. 
 I'd
 love to hear from someone else if this is fixed in 9.2 and if it can
 be
 reliably used. 
  
 Thank you
 Lisa Monkey.
  
  
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Darrell Landrum
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

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

RE: skip scan index

2003-05-30 Thread Jamadagni, Rajendra

Title: RE: skip scan index





Mladen,


Come to Hotsos 2004 ... I can meet you too (unless you are attending 06/09 CTOUG meeting).


Raj

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



-Original Message-
From: Gogala, Mladen [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 29, 2003 12:00 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: skip scan index



Thanks, Wolfgang! I really hope to meet both you and Cary one of these days.


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



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

RE: RE: Which method is more efficient

ok so your goal is not speed. its reducing redo, correct? 
so you dont care if its slow? 

try using a global temp table. that may cut down your redo significantly. 

 From: Rodrigues, Bryan [EMAIL PROTECTED]
 Date: 2003/05/29 Thu PM 12:44:52 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: RE: Which method is more efficient

 Yes, I am in archive log mode and I have had 2 occurances of filling up the
 archive log filesystem, but I don't want to change one problem for another.

 Bryan

 -Original Message-
 Sent: Wednesday, May 28, 2003 3:20 PM
 To: Multiple recipients of list ORACLE-L

 have you run it? isnt that alot slower? you have alot of context switches
 also. for every record to update, you then switch to SQL. 

 what kind of efficiency improvement are you going for? Speed or cutting down
 on redo? Are you in archivelog mode and dont want to blow up your archives? 

  From: Rodrigues, Bryan [EMAIL PROTECTED]
  Date: 2003/05/28 Wed PM 02:40:25 EDT
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Subject: RE: RE: Which method is more efficient

  The fields that are changed are determined by 
  1) A loop would start until all records in parts change table are done
  2) Select a part record from the part changes table
  3) Select the same part from the existing part table
  4) Compare the value in the parts changes table against the corresponding
  field in the part table 5) After comparing all fields in the records,
 create
  record in a seperate work table with the values populated with null if the
  field values matched and the new value if the values did not.
  6) This loop would continue until all parts are done.
  7) After any records in the work table where all fields (outside of part
  number) are null are deleted.

  This process normally will decrease the number of records to be processed
  after this point by 75%.

  Hope that helps,

  Bryan

  -Original Message-
  Sent: Wednesday, May 28, 2003 1:21 PM
  To: Multiple recipients of list ORACLE-L

  oh i missed part of it. the question is how do you figure out which fields
  have changed? if you have to do an anti-join on each field, then do an
  update of every field. 

  the question is how will you determine which fields have changed? 

   From: DENNIS WILLIAMS [EMAIL PROTECTED]
   Date: 2003/05/28 Wed PM 12:59:51 EDT
   To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
   Subject: RE: Which method is more efficient

   Bryan - If this is a critical issue, I would try it both ways on a test
   database and use log miner to examine the amount of redo that is
  generated.
   My recollection is that you will find that the redo record records the
   before and after data for each field. So just updating all fields may
   generate significantly more redo. But don't trust my recollection on
 this
   issue, test it yourself.

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

   -Original Message-
   Sent: Wednesday, May 28, 2003 10:50 AM
   To: Multiple recipients of list ORACLE-L

   Hello everyone,

   I have a question for the group of which method is more efficient. 

   To set the stage my company has a process to load part changes from
  vendors
   into the tables in an 8.1.7.4 Oracle database with archiving on and this
   database has a standby database at disaster recovery site, so nologging
 is
   not an option. 

   There is a discussion going on as to which method is more effective for
   updating the information in a table. In looking at effectiveness, I am
   looking at reducing the amount of redo information produced and having
 the
   database do the least amount of work.

   1)Method 1 is to update the information only for the fields that have
   changed, 1 field at a time.
   2)Method 2 is to update the information for all the fields in the
   record whether they have changed or not, 1 record at a time.

   The size of the record is 1843 bytes and the distribution of field
 sizes:
2 fields varchar2(240).
1 field varchar2(150)
   15 fields varchar2(50)
   1 field varchar2(3)
   2 fields varchar2(20)
   4 fields varchar2(40)
   3 fields varchar2(1)
   2 fields varchar2(25)
   2 fields number(10,2)
   1 field number(13,2)
   1 field number(1)
   1 field number
   1 field varchar2(6)
   1 field number (17,2)
   1 field varchar2(4)
   3 fields that are date.

   In the past couple of months the average number of fields changed per
  record
   was 3 to 4 fields per record.

   Thanks for your help,

   Bryan Rodrigues
   Oracle DBA
   Elcom, Inc.

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

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

RE: dbms_stats broken

Hi Mike, 

Yes, I could use it... but the GATHER EMPTY option set stats on the empty partitions 
correctly.  I'm more concerned about having old stats on a partition that is now 
loaded.  

Thanks

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


Lisa,

I'm still using Analyze so I have no direct experience with this package.
That being said, would the SET_TABLE_STATS work for the partitions that
haven't been loaded yet?

Mike Hand
Polaroid Corp.


-Original Message-
Sent: Thursday, May 29, 2003 10:46 AM
To: Multiple recipients of list ORACLE-L


Hi Darrell  Kirti - 

It was late last night when I was looking at this.  It seems I should be
using GATHER EMPTY instead of GATHER STALE.  However this will put
statistics on partitions with no rows.

When I load new partitions tomorrow (they are empty prior to the daily
load), the number of rows inserted wouldn't reach 10% change.  It would take
over a week to reach 10% change in order for GATHER STALE to pick up on this
and re-analyze these partitions.  I don't want statistics saying there are 0
records in a partition that is indeed loaded.  

I guess GATHER STALE would be much more useful if the 10% threshold could be
modified, and/or the threshold could be partition specific.  

And Kirti, the bugs I have seen are 1192012, 1890016, 2157655.  I thought I
was running into 1890016.  

Looks like I'll have to code around this after all. Darn it all.  I really
am a monkey. Sheesh

Lisa


-Original Message-
Sent: Thursday, May 29, 2003 12:30 AM
To: Multiple recipients of list ORACLE-L


Lisa,

Wow, you might be saving me from peril right now.  I have tested this
with a small set of tables with no problems (in and 8.1.7.4 instance). 
I'm preparing to go 'schema' wide in the next week or so for further
testing prior to implementing in production.
I'd be very interested in more details of your problems in 8.1.7.4 and
of course I'll post reports of testing to the list as well.

For starters, here is the code I use to obtain a list of 'stale'
qualified tables:
( For proper credit, I think I got this from asktom.oracle.com)


set serverout on size 9

declare
l_objList dbms_stats.objecttab;
begin
dbms_stats.gather_schema_stats
( ownname = '1',
options = 'LIST STALE',
objlist = l_objlist );
for i in 1 .. l_objlist.count
loop
--dbms_output.put_line( l_objlist(i).objtype );
dbms_output.put_line( l_objlist(i).objname );
end loop;
end;
/


And the code to gather stats:

set serverout on size 99000

begin
dbms_stats.gather_schema_stats(
ownname='1',
options='GATHER STALE',
cascade=TRUE,
degree=8,
granularity='ALL',
method_opt='FOR ALL INDEXED COLUMNS SIZE 1'
);
end;
/


Thanks,
Darrell


 [EMAIL PROTECTED] 05/28/03 09:24PM 
Hello everyone, 
 
Is anyone using dbms_stats and gather stale or gather auto in 9.2? 
I'm
trying to use dbms_stats gather schema stats with the stale option and
it just isn't working in 8.1.7.4.  This is documented on Metalink. 
I'd
love to hear from someone else if this is fixed in 9.2 and if it can
be
reliably used. 
 
Thank you
Lisa Monkey.
 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

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

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

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

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

RE: 10i

2003-05-30 Thread Ron Rogers

Dennis,
 What he probable means is the OCP upgrade exam will be just like the
first OCP exams, Multiple and expensive... looking at some of the new
stuff
Ron

 [EMAIL PROTECTED] 05/29/03 01:24PM 
Robert - Does this mean we can expect a new book Oracle10i New
Features by
that respected Oracle author Robert Freeman coincident with the 10i
release?
I'm ready!

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


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


Got my first look at the 10i beta last night. I can't tell you much
about it
except to say that looking at some of the new stuff


h

;-)


While I'm certain many of the new features will not work perfectly for
several releases afterwards, they look very cool!

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

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

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

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

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

RE: RMAN - Remote vs Local Backups

2003-05-30 Thread Steve McClure




Just a 
thought. Possibly to assist in recovery of the database box. If you 
have a complete media failure on the DB host, you greatly simplify the recovery 
process if your RMAN database is somewhere safe.

Steve 
McClure

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of Walter KSent: 
  Thursday, May 29, 2003 9:55 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RMAN - Remote vs Local 
Backups
  Hi,
  
  Can anyone think of a reason(s) why oneWOULD want to backup a 
  database from a box other than the database box itself? Are there any 
  advantages to this kind of configuration?
  
  For example:
  
  Box-A (production db server)
  Box-B (rman db server)
  
  A cron job runs on Box-B which backups upthe databasefrom 
  Box-A.
  
  
  Thanks in advance!
  
  -w

RE: SAP Hands SAP DB over to MySQL

The incentive?  $$$, and lots of it.

Larry E talked folks into trusting Oracle at it's inception, and I'll bet 
it
was quite a bit less robust then than MySQL is now.

Give MySQL a couple years and see what happens. 

It may never be appropriate for certain apps, but for small to medium
plain vanilla RDBMS, it may be a good fit.

Jared






DENNIS WILLIAMS [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 05/29/2003 07:49 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: SAP Hands SAP DB over to MySQL


Okay, I've got a question for you people knowledgeable in SAP. Who would
trust their financial and payroll data to MySQL? I'm not saying this to
knock MySQL. It is just that your financial and payroll data is among your
most valuable data in the corporation. Only recently was a transaction
capability added to MySQL, and that was more of an add-on. I manage
several databases and my company even makes modest use of MySQL. But of
those databases, the financial and payroll data would be the LAST database 
I
would convert to something like MySQL. I'm just curious what is the big
incentive in MySQL is.

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


-Original Message-
Sent: Wednesday, May 28, 2003 5:50 PM
To: Multiple recipients of list ORACLE-L


Maybe it is just so they can continue to say they're not a database
company
(insert sound of condescension) to emphasize their focus on applications 
excellence in the veiled jabs they continue to make at Oracle.  On the
other
hand, I can't imagine they would give up development control because they 
do

make a specialized version of SAP DB for object oriented programming
(they say they couldn't find a product that worked correctly...). 

Kip 

|I dunno.  Though both want to make a profit ( and rightly so ) SAP
|doesn't seem to have the same mercenary mentality that MS has.

|Jared


|Orr, Steve [EMAIL PROTECTED]
|Sent by: [EMAIL PROTECTED]
| 05/28/2003 11:52 AM
| Please respond to ORACLE-L

 
|To: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
|cc:
|Subject:RE: SAP Hands SAP DB over to MySQL


|Reminiscent of the M$/Sybase partnership?


|-Original Message-
|Sent: Wednesday, May 28, 2003 11:40 AM
|To: '[EMAIL PROTECTED]'


|At http://www.sapdb.org/7.4/pdf/sapdb_letter.pdf SAP offers
clarification:

|SAP:
|Contrary to erroneous press reports, SAP AG has not given up any rights
|concerning the SAP DB code base nor handed over or even sold SAP DB to
|MySQL AB.

|SAP:
|SAP AG remains responsible for ongoing development and support.

|CNet:
|MySQL will take over most of the development of SAP DB.



|-Original Message-
|Sent: Wednesday, May 28, 2003 12:01 PM
|To: Multiple recipients of list ORACLE-L
|Importance: High


|The past few months I've been wondering when MySQL would start
|putting pressure on Oracle in the same way that Linux is putting
|pressure on MS.

|Maybe sooner than you think:

|http://news.com.com/2100-1012_3-1010522.html?tag=fd_top


|Jared

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

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

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

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




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

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

Re: RE: Which method is more efficient

how would you do that? if you dont know which columns have changed values? you would 
have to compare every column? how else can you do it? 

 From: Jamadagni, Rajendra [EMAIL PROTECTED]
 Date: 2003/05/29 Thu PM 01:24:43 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: Which method is more efficient

 Jared,

 Agreed, but what about the resources needs to find _which_ column changed ??
 Would that offset the extra redo generated? Heck, I'd just generate the
 update statements based on two tables to _only_ update the changed columns.
 It is pretty easy, if both tables have _same_ columns ...

 Raj

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

 -Original Message-
 Sent: Wednesday, May 28, 2003 6:15 PM
 To: Multiple recipients of list ORACLE-L

 There are easier ways to test redo generation than mucking about with
 logminer.

 Update only the column that changes and check redo generation:
 15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1

 [  much stuff deleted  ]

 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

Title: RE: Which method is more efficient

Jared,

Agreed, but what about the resources needs to find _which_ column changed ?? Would that offset the extra redo generated? Heck, I'd just generate the update statements based on two tables to _only_ update the changed columns. It is pretty easy, if both tables have _same_ columns ...

Raj

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

-Original Message-
Sent: Wednesday, May 28, 2003 6:15 PM
To: Multiple recipients of list ORACLE-L

There are easier ways to test redo generation than mucking about with logminer.

Update only the column that changes and check redo generation:
15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1

[ much stuff deleted ]

RE: exp, dbms_stats, RMAN and rollback segments

2003-05-30 Thread Ganesh Raja

A Large Load or Update Before the Exp ... 

Delyed Block Clean out is one Reason that really pops out.

HTH

Best Regards,
Ganesh R
DID : +65-6215-8413
HP  : +65-9067-8474
===
Live to learn... forget... and learn again.
===


-Original Message-
Sent: Friday, May 30, 2003 12:15 AM
To: Multiple recipients of list ORACLE-L


A certain alignment of the planets occurred creating a good ole
ORA-01555 error... A user level export received the snapshot too old
error and terminated. Concurrent to this was an RMAN backup and
DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on the same
schema being backed up via the user level export. There was no other end
user access to the schema data. Since exp got the error I assume it was
reading from the rollback segments but why? I'm suspecting dbms_stats.
We have ample RBS. Is there any significant undo generated by dbms_stats
or RMAN which could create this problem? 

(Of course we need to improve our job scheduling but that's another
issue, the timing of the user level export is application driven and out
of our control). 


Befuddled in Bozeman,
Walt and Steve
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Orr, Steve
  INET: [EMAIL PROTECTED]

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


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

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

2003-05-30 Thread Tim Gorman

Well

How about if you are performing BCV snapshots of the storage
under Box-A (prod server) and then mounting those BCVs on
Box-B, mounting (not opening!) the database on Box-B, and
performing RMAN backups?

That would remove any resource consumption from the
all-important Box-A as far as backups are concerned.  Of
course, archived redo log backups should continue to be
performed from Box-A, but the datafile backups can be
performed from Box-B.  I'm not sure, but perhaps Box-B would
even be able to perform the re-silvering operation to
re-mirror the BCV volumes back into the main RAID-1 set,
thus truly relieving Box-A of the burdens of backups...

It would be highly advisable to use a centralized recovery
catalog database in this situation (instead of NOCATALOG
mode).  Since both database images on both servers will have
the same DBID, the recovery catalog database should record
both types of backups (datafile on Box-B and everything else
on Box-A) as if they were all performed from Box-A...

Just an idea...

-Tim

 Hi,
  
 Can anyone think of a reason(s) why one WOULD want to
 backup a database from a box other than the database box
 itself? Are there any advantages to this kind of
 configuration?
  
 For example:
  
 Box-A (production db server)
 Box-B (rman db server)
  
 A cron job runs on Box-B which backups up the database
 from Box-A.
  
  
 Thanks in advance!
  
 -w
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

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

C++ Issues

2003-05-30 Thread Post, Ethan

I am trying to track this down for a buddy, any ideas, I know nothing about
C++.

Thanks,
Ethan




Our application currently has presentation programs written in Microsoft
Visual C++ that read Oracle Version 7.3.4 databases.  We have Oracle
Professional/2000 installed on 1 machine.  The Pro*C/C++ pre-compiler
provided by Oracle is the method we use to pre-compile our program.  This
pre-compile converts the EXEC SQL commands into C++ calls to incorporate the
Oracle Database functions into the programs.  Other methods I have found in
research is to use OCI or ODBC calls.  However everything I look at
indicates that we would need to rewrite our applications to utilize
additional include libraries as well as modify our  SQL calls to wrap them
with the appropriate language elements.

We wish to fully utilize the functionality of the Microsoft Visual C++
Professional Edition environment to allow each developer to use their own
machine to compile and unit test these programs.  However, I am unable to
get the pre compile process to work.  Pro C will not run on our machines if
it is not installed.  We get a Incorrect environment variable.  Please
reinstall Pro*C/C++ message version error if we try to run the copy that is
on the compiler machine from our machine.  However, I can not install it
because Oracle 7.3.4 does not support Windows 2000 and the installation
process abends whenever I try to run it.  

What we are looking for is a method to compile, debug, and unit test using
C++ on our machine without getting rid of Pro*C/C++ and yet utilize each
developer's machine more to remove the load from our compiler machine.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Post, Ethan
  INET: [EMAIL PROTECTED]

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

Re: RMAN - Remote vs Local Backups

2003-05-30 Thread Ron Rogers

-w
A simple disk crash containing the database and the RMAN data woulf
wipe you out. If bax-a crashed you could use box-b to recover your
data.
Ron

 [EMAIL PROTECTED] 05/29/03 12:54PM 
Hi,
 
Can anyone think of a reason(s) why one WOULD want to backup a database
from a box other than the database box itself? Are there any advantages
to this kind of configuration?
 
For example:
 
Box-A (production db server)
Box-B (rman db server)
 
A cron job runs on Box-B which backups up the database from Box-A.
 
 
Thanks in advance!
 
-w
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Rogers
  INET: [EMAIL PROTECTED]

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

Re: x$bh.dbablk values (repost)

You are running into the classical problem with monitoring an experiment: 
To what extent (no pun intended) does the monitoring change the experiment.

Referencing dba_extent pulls in the segment headers of all segments owned 
by 'BCA'. When you interrogate x$bh have nothing else in your sql. Dump the 
result set somewhere and then get the information from dba_extents so that 
you can match dbarfil and dbablk to a segment. I usually use excel and its 
vlookup function to do that.

At 09:19 AM 5/29/2003 -0800, you wrote:
I am reposting this in the hopes that someone can help me with this puzzler.

I am working on determining which objects have 'hot blocks'.
In two different sessions, I issue select count(*) from random_data where 
rowid_rownum in (1,2,3); repeatedly to see what happens with the touch 
count (x$bh.tch).

In another session, I look for the blocks related to this object by 
issuing the statement:
select x.owner, x.segment_name, x.segment_type, b.dbarfil, b.dbablk, 
b.class, b.state, sum(b.tc
from dba_extents x, x$bh b
where b.dbarfil = x.file_id
and b.dbablk between x.block_id and (x.block_id + blocks - 1)
and x.owner = 'BCA'
group by x.owner, x.segment_name, x.segment_type, b.dbarfil, b.dbablk, 
b.class, b.state;
[snip]

The oddity is that I have restarted the instance and have only issued 
queries against the random_data table. Since I am the only user on the 
system, I know that no other sessions are accessing the objects.
The interesting bit in all this is that the blocks other than random_data 
listed in x$bh are the segment headers.

select segment_name, file_id, block_id, blocks, block_id+blocks-1
from dba_extents
where owner = 'BCA'
 and extent_id = 0
order by file_id, block_id;
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: HELP - Microsoft Access error SQLSetConnectAttr failed error.

2003-05-30 Thread Pardee, Roy E

Need more info:

What are you doing when the error occurs?  If it's vb code, can you post it?
Can you connect from the erroring machine via sql*plus?
Can you link a table via the odbc connection  open it interactively?
Does it make a difference if you define a new odbc data source  link
through that?
Is the error consistent accross client pcs?
Which odbc driver are you using--oracle's or ms'?

Cheers,

-Roy

Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487 
-Original Message-
Sent: Thursday, May 29, 2003 10:50 AM
To: Multiple recipients of list ORACLE-L


However, I was able to independently through odbctest and tnsping confirm
that the odbc driver working and the alias working through Oracle's Net8.  I
also noticed with the odbctest that the user had a very small and limited
amount of tables to view from all_tables.  Could that be the reason that we
are getting this error from Access?
Thanks, 
Paula 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pardee, Roy E
  INET: [EMAIL PROTECTED]

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

RE: SAP Hands SAP DB over to MySQL

2003-05-30 Thread Boivin, Patrice J

They don't want the choice to hardware, OS and software to be a technical
decision?

Why not?  TCO is part of technical decision-making, is it not?

Sometimes they decide before input from below then try to force a solution
on the tech people.

In that kind of world, the firm which is best at marketing to non-technical
people wins.

Patrice.

-Original Message-
Sent: Thursday, May 29, 2003 2:25 PM
To: Multiple recipients of list ORACLE-L


Everybody is under cost pressures these days, right?  Recently my boss
required
that I attend a few webinars hosted by Microsoft to hear their line on TCO
because he didn't want the choice of our next platform to be a technical
decision (we're on Tru64 unix).  I don't think I'll be encouraged to
consider
MySQL, postgre sql, linux, etc anytime soon for mission critical apps but
who
knows?  

SAP has been sparring with Oracle for at least the past 10 years.  SAP's 
success has helped Oracle sell their product which I guess is irksome to SAP
since they see Oracle as a competitor.  At one point, SAP was encouraging
new 
customers to select Informix.  Now it seems to be DB2 and/or SAP DB...  At

sapteched this year, their analysis of the database market was that it was
at 
a point where features had become a commodity but commodity pricing hadn't 
happened yet...but that it would.  They were calling Oracle/IBM/Microsoft
the
dominant options and then comparing them with SAP DB.  I don't recall any
talk 
about MySQL, postgresql...

Kip

|Okay, I've got a question for you people knowledgeable in SAP. Who would
|trust their financial and payroll data to MySQL? I'm not saying this to
|knock MySQL. It is just that your financial and payroll data is among your
|most valuable data in the corporation. Only recently was a transaction
|capability added to MySQL, and that was more of an add-on. I manage
|several databases and my company even makes modest use of MySQL. But of
|those databases, the financial and payroll data would be the LAST database
I
|would convert to something like MySQL. I'm just curious what is the big
|incentive in MySQL is.

|Dennis Williams
|DBA, 80%OCP, 100% DBA
|Lifetouch, Inc.
|[EMAIL PROTECTED]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Boivin, Patrice J
  INET: [EMAIL PROTECTED]

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

RE: skip scan index

2003-05-30 Thread Cary Millsap

Title: RE: skip scan index









I just put the Hotsos Symposium 2004
announcement on our web page yesterday. The event will be held March 710
in Dallas. Its early yet, but we already have speaker commitments
from Tom Kyte, Jonathan Lewis, Mogens Nørgaard, and me. Well add many more speakers in the coming months.
Ill keep the announcement updated as we move forward. See http://www.hotsos.com for details.





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

Upcoming events:
- Hotsos Clinic101
in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney
- Visit www.hotsos.com for schedule
details...



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni,
Rajendra
Sent: Thursday, May 29, 2003 1:16
PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: skip scan index



Mladen,


Come to
Hotsos 2004 ... I can meet you too (unless you are attending 06/09 CTOUG
meeting). 

Raj



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



-Original
Message- 
From: Gogala, Mladen [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, May 29, 2003 12:00
PM 
To: Multiple recipients of list
ORACLE-L 
Subject: RE: skip scan index




Thanks,
Wolfgang! I really hope to meet both you and Cary one of these days.


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

RE: skip scan index

Thanks, Pete.

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


-Original Message-
Sent: Thursday, May 29, 2003 1:30 PM
To: Multiple recipients of list ORACLE-L


You know, of all the impressive things I've ever heard Cary Millsap say (and
there have been a heck of a lot), the one that stands out the most in my
memory is a series of quotes from his keynote at the 2003 Hotsos Symposium:

An experiment that disproves a conclusion is a success.
Knowledge in your head is less valuable than knowledge you share.
Show your work
Show all your data
 Even when it contradicts your conclusion
No: ***especially*** when it contradicts your conclusion.

And a quote from Richard Feynman in that same presentation:

Details that could throw doubt on your interpretation must be given, if you
know them.  You must do the best you can-if you know anything wrong at all,
or possibly wrong-to explain it.

Mladen, you're a star.  If only we could all be open about the times we are
wrong as you've been!

Pete
Controlling developers is like herding cats.
Kevin Loney, Oracle DBA Handbook
Oh no, it's not.  It's much harder than that!
Bruce Pihlamae, long-term Oracle DBA
 


-Original Message-
Sent: Wednesday, May 28, 2003 7:08 PM
To: Multiple recipients of list ORACLE-L


Here is the idea: 
Index test_skip1 is located in the tablespace INDX which has one file,
FILE#=5

I restart the database, execute your query, then see V$FILESTAT for blocks
read. (select PHYBLKRD from v$filestat where file#=5;)

Then restart the database, execute query asking for a fast full scan and see

how many blocks do get read. If the number is the same, then the conclusion
is inevitable. So, here we go:



SQL set autotrace on explain
SQL select /*+ index_ss(test_skip1 ) */
c1,c2
from test_skip1 where c2 = 100;
  23
C1 C2
-- --
 1100
 2100


Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=302 Bytes=78
  52)

   10   INDEX (SKIP SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=11 Car
  d=302 Bytes=7852)

SQL select PHYBLKRD from v$filestat where file#=5;

  PHYBLKRD
--
10

---DATABASE RESTART---


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning option
JServer Release 9.2.0.3.0 - Production

SQL set autotrace on
SQL select /*+ index_ffs(t test_skip1_pk ) */
c1,c2
from test_skip1 t where c2 = 100;
  23
C1 C2
-- --
 1100
 2100


Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=302 Bytes=785
  2)

   10   INDEX (FAST FULL SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=4
   Card=302 Bytes=7852)





Statistics
--
300  recursive calls
  0  db block gets
777  consistent gets
724  physical reads
  0  redo size
464  bytes sent via SQL*Net to client
503  bytes received via SQL*Net from client
  4  SQL*Net roundtrips to/from client
  6  sorts (memory)
  0  sorts (disk)
  2  rows processed

SQL select PHYBLKRD from v$filestat where file#=5;

  PHYBLKRD
--
   722


That means that fast full scan will read 722 blocks where skip scan will
read only 10, which means that you were right and I was wrong. Obviously, my
metodology was incorrect or 9.2.0.1 database that I've tested it on has had
a bad bug, which would really be 
surprising and unusual. Anyway, you are right. That, in turn, implies that
oracle 
indexes are not classic B*Tree structures as I was lead to believe but are
spiked with an unknown liquor. Thanks for helping me clarify this.



On 2003.05.28 18:29 Khedr, Waleed wrote:
 It's like any other execution plan, good in certain data distributions 
 and bad in others.
 
 But I do not think it's correct that skip scan requires reading the 
 whole index (it's even clear in this test).
 
 Waleed
 
 -Original Message-
 Sent: Wednesday, May 28, 2003 5:30 PM
 To: Multiple recipients of list ORACLE-L
 
 
 OK. I don't have the 9i instance that I can use for testing right now, 
 but tonight, at home, I'll give you the counter example. The bottom 
 line is that the only way to execute a skip scan with a B*Tree index 
 is to go and read it whole. No other way.
 
 Mladen Gogala
 Oracle DBA
 Phone:(203) 459-6855
 Email:[EMAIL PROTECTED]
 
 
 -Original Message-
 Sent: Wednesday, May 28, 2003 4:40 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Not true, try this:
 
 create table test_skip1 ( c1 number,c2 number, primary key (c1,c2));
 
 begin
  for i in 1..10 loop
   insert into test_skip1 values (1,i);
   insert into test_skip1 values (2,i);
  end loop;
  end;

RE: exp, dbms_stats, RMAN and rollback segments

2003-05-30 Thread Kirtikumar Deshpande

Steve,
 You may have to dig a little further...  
 What happened to those table(s) in that schema prior to starting the export? Heavy 
DML, may be?
 
 This could be a case of 'delayed block cleanout'. Export triggered the cleanout and 
wanted to
access the rollback segments. 

 If no table data was modified after export started reading that table, then there is 
no need to
read RBS info (except for the DBC case, IMO). 

- Kirti  

--- Orr, Steve [EMAIL PROTECTED] wrote:
 Oh yeah, for the export consistent=N
 
 -Original Message-
 Sent: Thursday, May 29, 2003 9:14 AM
 To: '[EMAIL PROTECTED]'
 
 
 A certain alignment of the planets occurred creating a good ole ORA-01555 error... A 
 user level
 export received the snapshot too old error and terminated. Concurrent to this was an 
 RMAN backup
 and DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on the same schema being 
 backed up
 via the user level export. There was no other end user access to the schema data. 
 Since exp got
 the error I assume it was reading from the rollback segments but why? I'm suspecting 
 dbms_stats.
 We have ample RBS. Is there any significant undo generated by dbms_stats or RMAN 
 which could
 create this problem? 
 
 (Of course we need to improve our job scheduling but that's another issue, the 
 timing of the
 user level export is application driven and out of our control). 
 
 
 Befuddled in Bozeman,
 Walt and Steve
 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kirtikumar Deshpande
  INET: [EMAIL PROTECTED]

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

Tablespace management.

2003-05-30 Thread Thomas Day


After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as
the options for tablespace management.  Does anyone have any bad
experiences with these?  AUTOALLOCATE seems to come up with extents that
are much smaller than I want and MANUAL segment management requires the use
of FREELISTs (and I know that there are problems with freelists freeing up
space correctly, especially in a parallel environment).

I can't find any basis for making a decision between UNDO and ROLLBACK
SEGMENTS.  Does anyone have any experience or recommendations about UNDO
usage?

The database will be a materialize view replication of a transaction master
that is being used for decision support and has a 15 minute update/refresh
cycle.  Basically, people can run queries against the snapshot without
impacting the master.


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

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

RE: lengthy URL's

2003-05-30 Thread Thater, William

[EMAIL PROTECTED] scribbled on the wall in glitter crayon at Thursday,
May 29, 2003 2:05 PM:

 Someone else told me it was blocked.
 
 Personally, I would take it up with my security admin.

who is the same as my exchange admin.  no thanks, his head is still reeling
from the last encounter.;-)

--
Bill Shrek Thater   Oracle DBA
   [EMAIL PROTECTED]
--
If you don't know where you're going, any road will take you there.
www.mailfiler.com [JS-5BKXSC4]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thater, William
  INET: [EMAIL PROTECTED]

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

RE: dbms_stats

I wouldn't call 1.4%  [ (603826-595500)/603826 ] way wrong. Actually, for 
a 1% sample I find that pretty good. The problem I found with low sampling 
percentages is if you have skewed column values. If some values occur very 
often and others rather seldom, a 1% sample may only encounter the 
frequently occurring values and none of the infrequently occurring ones and 
come up with a really way off estimate for num_distinct.

At 10:05 AM 5/29/2003 -0800, you wrote:
Hi John,

Yes, monitoring was set.  I wouldn't see anything in *tab_modifications if 
monitoring wasn't set.

Here's a new twist.  What percentage are you comfortable with for valid 
estimates?  I attended a seminar given by Jonathan Lewis a few weeks ago, 
and he stated that adequate statistics can be gathered using 1% 
sample.  That was great news to me, who has time for huge estimates?  OK, 
so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and 
estimate_percent set to 1.  The rowcounts are way wrong.

(FACP-LISA)SELECT COUNT(*)
  2  FROM VEGAS_MART
  3  PARTITION (MAY_28_2003);
  COUNT(*)
--
603826
(FACP-LISA)select num_rows
  2  from dba_tab_partitions
  3  where table_name = 'VEGAS_MART'
  4  and partition_name = 'MAY_28_2003';
  NUM_ROWS
--
595500
(FACP-LISA)

And Jonathan if you happen to read this email, if I am mis-stating what 
you stated in class please correct me.  I am on 8.1.7.4 and that may be 
the difference.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Which method is more efficient

2003-05-30 Thread Rodrigues, Bryan

Jared,

That is correct the fields that are changed is known before going to this
point.

Bryan

P.S. I just want to say thank you for all the responses to this question. We
are still talking about the issue, but I am thankful for the input.


-Original Message-
Sent: Thursday, May 29, 2003 2:10 PM
To: Multiple recipients of list ORACLE-L


dunno about that.  I was making the assumption that *which* columns
changed was already known.

This would require testing by someone familiar with the data.

Jared






Jamadagni, Rajendra [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 05/29/2003 10:24 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: Which method is more efficient


Jared, 
Agreed, but what about the resources needs to find _which_ column changed 
?? Would that offset the extra redo generated? Heck, I'd just generate the 
update statements based on two tables to _only_ update the changed 
columns. It is pretty easy, if both tables have _same_ columns ...
Raj 

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

-Original Message- 
Sent: Wednesday, May 28, 2003 6:15 PM 
To: Multiple recipients of list ORACLE-L 

There are easier ways to test redo generation than mucking about with 
logminer. 
Update only the column that changes and check redo generation: 
15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1 
[  much stuff deleted  ] 

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

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

RE: is this DBA's only mailing list?

2003-05-30 Thread David . Schmoldt





We do not want developers to learn or 
secrets. That might
give them an idea that we're mere 
mortals.

Yeah, 
I've learned from reading this list that not all DBAs are the super-smart gods 
they set themselves up to be. Some are actually not too bright, based on 
things I've seen here. The Peter Principle lives! 
:-)

Dave

RE: skip scan index

Hey, with all that praise being heaped on you for publicizing your 
wrongness, who would ever want to be right?
:-)

At 10:51 AM 5/29/2003 -0800, you wrote:
Thanks, Pete.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or 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 - Remote vs Local Backups

Walter - As RMAN was introduced in Oracle8i, that was the ideal. I think
Oracle viewed RMAN as a high-level feature that would help you manage the
backups for large server farms. They emphasized that the catalog was the way
to go. With the catalog on another box, if the server was toasted, you could
slide another system into that spot and with a couple of RMAN commands you
could have that up and going again. Obviously if you use the catalog method
on the box you are backing up, you must have a second instance, and even
then you introduce more vulnerabilities than the configuration where the
catalog is on another server.
 With Oracle9i, Oracle added many of the features that were only
available in the catalog method to the control-file method. According to my
Oracle Education Instructor John Hibbard who is pretty plugged into these
things, Oracle is trying to emphasize that the catalog method may not suit
everyone's situation and the control file method may best suit your needs.
As others on this list have pointed out, not all conference speakers have
gotten that message.



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

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


Hi,
 
Can anyone think of a reason(s) why one WOULD want to backup a database from
a box other than the database box itself? Are there any advantages to this
kind of configuration?
 
For example:
 
Box-A (production db server)
Box-B (rman db server)
 
A cron job runs on Box-B which backups up the database from Box-A.
 
 
Thanks in advance!
 
-w

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

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

Re: RMAN - Remote vs Local Backups

2003-05-30 Thread Darrell Landrum

Sure, several:

1) Centralized backup management, maybe there will eventually be a
Box-C which can also be backed up by Box-B.

2) Maybe Box-B is a Veritas master media server or the equivilent with
other backup software.

3) Most important: Box-A has a 'crash and burn', you don't lose your
RMAN catalog too.



 [EMAIL PROTECTED] 05/29/03 11:54AM 
Hi,
 
Can anyone think of a reason(s) why one WOULD want to backup a database
from a box other than the database box itself? Are there any advantages
to this kind of configuration?
 
For example:
 
Box-A (production db server)
Box-B (rman db server)
 
A cron job runs on Box-B which backups up the database from Box-A.
 
 
Thanks in advance!
 
-w
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

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

RE: SAP Hands SAP DB over to MySQL

Thanks, Kip, Jared, Dick - that makes more sense. I now recall that Lawson
also ships a free (non-relational) database with its software. Some sites
use it for their first year conversion activities (and some much longer). It
is much faster than Oracle or the other relational databases. When the
customer would spend the big bucks to buy Oracle they would expect it to be
faster and were very shocked. When I worked for Lawson one of my duties was
to try to reason with the customer on this. The Sybase interface developer
Bob sat across the aisle from me and he had a very belligerent customer that
wouldn't understand this. Day after day he would harangue Bob. One day he
called up and icily said that the Oracle salesman had agreed to apply his
Sybase license fee toward Oracle. When he hung up Bob smiled sweetly and
said he's all yours now.

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


-Original Message-
Sent: Thursday, May 29, 2003 1:05 PM
To: Multiple recipients of list ORACLE-L


The incentive?  $$$, and lots of it.

Larry E talked folks into trusting Oracle at it's inception, and I'll bet 
it
was quite a bit less robust then than MySQL is now.

Give MySQL a couple years and see what happens. 

It may never be appropriate for certain apps, but for small to medium
plain vanilla RDBMS, it may be a good fit.

Jared






DENNIS WILLIAMS [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 05/29/2003 07:49 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: SAP Hands SAP DB over to MySQL


Okay, I've got a question for you people knowledgeable in SAP. Who would
trust their financial and payroll data to MySQL? I'm not saying this to
knock MySQL. It is just that your financial and payroll data is among your
most valuable data in the corporation. Only recently was a transaction
capability added to MySQL, and that was more of an add-on. I manage
several databases and my company even makes modest use of MySQL. But of
those databases, the financial and payroll data would be the LAST database 
I
would convert to something like MySQL. I'm just curious what is the big
incentive in MySQL is.

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


-Original Message-
Sent: Wednesday, May 28, 2003 5:50 PM
To: Multiple recipients of list ORACLE-L


Maybe it is just so they can continue to say they're not a database
company
(insert sound of condescension) to emphasize their focus on applications 
excellence in the veiled jabs they continue to make at Oracle.  On the
other
hand, I can't imagine they would give up development control because they 
do

make a specialized version of SAP DB for object oriented programming
(they say they couldn't find a product that worked correctly...). 

Kip 

|I dunno.  Though both want to make a profit ( and rightly so ) SAP
|doesn't seem to have the same mercenary mentality that MS has.

|Jared


|Orr, Steve [EMAIL PROTECTED]
|Sent by: [EMAIL PROTECTED]
| 05/28/2003 11:52 AM
| Please respond to ORACLE-L

 
|To: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
|cc:
|Subject:RE: SAP Hands SAP DB over to MySQL


|Reminiscent of the M$/Sybase partnership?


|-Original Message-
|Sent: Wednesday, May 28, 2003 11:40 AM
|To: '[EMAIL PROTECTED]'


|At http://www.sapdb.org/7.4/pdf/sapdb_letter.pdf SAP offers
clarification:

|SAP:
|Contrary to erroneous press reports, SAP AG has not given up any rights
|concerning the SAP DB code base nor handed over or even sold SAP DB to
|MySQL AB.

|SAP:
|SAP AG remains responsible for ongoing development and support.

|CNet:
|MySQL will take over most of the development of SAP DB.



|-Original Message-
|Sent: Wednesday, May 28, 2003 12:01 PM
|To: Multiple recipients of list ORACLE-L
|Importance: High


|The past few months I've been wondering when MySQL would start
|putting pressure on Oracle in the same way that Linux is putting
|pressure on MS.

|Maybe sooner than you think:

|http://news.com.com/2100-1012_3-1010522.html?tag=fd_top


|Jared

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

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

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

|Fat City Network Services-- 858-538-5051

RE: dbms_stats

2003-05-30 Thread John Kanagaraj

Lisa,

Have a look at ML Note:44961.1 (and maybe Note:114671.1 could also help).
The 1% sample may work for larger tables - I am not sure

John


 -Original Message-
 From: Koivu, Lisa [mailto:[EMAIL PROTECTED]
 Sent: Thursday, May 29, 2003 11:05 AM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: dbms_stats
 
 
 Hi John, 
 
 Yes, monitoring was set.  I wouldn't see anything in 
 *tab_modifications if monitoring wasn't set. 
 
 Here's a new twist.  What percentage are you comfortable with 
 for valid estimates?  I attended a seminar given by Jonathan 
 Lewis a few weeks ago, and he stated that adequate statistics 
 can be gathered using 1% sample.  That was great news to me, 
 who has time for huge estimates?  OK, so I ran 
 dbms_stats.gather_schema_stats using GATHER EMPTY and 
 estimate_percent set to 1.  The rowcounts are way wrong. 
 
 (FACP-LISA)SELECT COUNT(*) 
   2  FROM VEGAS_MART 
   3  PARTITION (MAY_28_2003);
 
   COUNT(*)
 --
 603826
 
 (FACP-LISA)select num_rows 
   2  from dba_tab_partitions
   3  where table_name = 'VEGAS_MART'
   4  and partition_name = 'MAY_28_2003';
 
   NUM_ROWS
 --
 595500
 
 (FACP-LISA)
 
 And Jonathan if you happen to read this email, if I am 
 mis-stating what you stated in class please correct me.  I am 
 on 8.1.7.4 and that may be the difference. 
 
 
 
 -Original Message-
 Sent: Thursday, May 29, 2003 1:36 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Lisa et al,
 
 Am I missing something or did someone ever mention that for 
 STALE to work,
 one needs to set MONITORING on required objects? Straight 
 from the Fine
 Manual:
 
 Enabling Automated Statistics Gathering 
 The GATHER STALE option only gathers statistics for tables 
 that have stale
 statistics and for which you have enabled the MONITORING attribute. To
 enable monitoring for tables, use the MONITORING keyword of 
 the CREATE TABLE
 and ALTER TABLE statements, as described in Designating Tables for
 Monitoring and Automated Statistics Gathering on page 8-9.
 
 John Kanagaraj
 Oracle Applications DBA
 DBSoft Inc
 (W): 408-970-7002
 
 Great, uplifting music (and best of all commercial-free!) -
 http://www.klove.com
 
 ** The opinions and statements above are entirely my own and 
 not those of my
 employer or clients **
 
 
  -Original Message-
  From: Darrell Landrum [mailto:[EMAIL PROTECTED]
  Sent: Wednesday, May 28, 2003 9:30 PM
  To: Multiple recipients of list ORACLE-L
  Subject: Re: dbms_stats broken
  
  
  Lisa,
  
  Wow, you might be saving me from peril right now.  I have 
 tested this
  with a small set of tables with no problems (in and 8.1.7.4 
  instance). 
  I'm preparing to go 'schema' wide in the next week or so for further
  testing prior to implementing in production.
  I'd be very interested in more details of your problems in 
 8.1.7.4 and
  of course I'll post reports of testing to the list as well.
  
  For starters, here is the code I use to obtain a list of 'stale'
  qualified tables:
  ( For proper credit, I think I got this from asktom.oracle.com)
  
  
  set serverout on size 9
  
  declare
  l_objList dbms_stats.objecttab;
  begin
  dbms_stats.gather_schema_stats
  ( ownname = '1',
  options = 'LIST STALE',
  objlist = l_objlist );
  for i in 1 .. l_objlist.count
  loop
  --dbms_output.put_line( l_objlist(i).objtype );
  dbms_output.put_line( l_objlist(i).objname );
  end loop;
  end;
  /
  
  
  And the code to gather stats:
  
  set serverout on size 99000
  
  begin
  dbms_stats.gather_schema_stats(
  ownname='1',
  options='GATHER STALE',
  cascade=TRUE,
  degree=8,
  granularity='ALL',
  method_opt='FOR ALL INDEXED COLUMNS SIZE 1'
  );
  end;
  /
  
  
  Thanks,
  Darrell
  
  
   [EMAIL PROTECTED] 05/28/03 09:24PM 
  Hello everyone, 
   
  Is anyone using dbms_stats and gather stale or gather auto in 9.2? 
  I'm
  trying to use dbms_stats gather schema stats with the stale 
 option and
  it just isn't working in 8.1.7.4.  This is documented on Metalink. 
  I'd
  love to hear from someone else if this is fixed in 9.2 and if it can
  be
  reliably used. 
   
  Thank you
  Lisa Monkey.
   
   
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: Darrell Landrum
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web 
 hosting services
  
 -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
  
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author:

RE: Tablespace management.

Thomas - Oracle recommended Local and Uniform, so that is what I use for
everything. It has worked out great. I even use autoextend and that hasn't
bitten me but a couple of times. This is on 8.1.6 and 9.2.

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


-Original Message-
Sent: Thursday, May 29, 2003 2:25 PM
To: Multiple recipients of list ORACLE-L



After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as
the options for tablespace management.  Does anyone have any bad
experiences with these?  AUTOALLOCATE seems to come up with extents that
are much smaller than I want and MANUAL segment management requires the use
of FREELISTs (and I know that there are problems with freelists freeing up
space correctly, especially in a parallel environment).

I can't find any basis for making a decision between UNDO and ROLLBACK
SEGMENTS.  Does anyone have any experience or recommendations about UNDO
usage?

The database will be a materialize view replication of a transaction master
that is being used for decision support and has a 15 minute update/refresh
cycle.  Basically, people can run queries against the snapshot without
impacting the master.


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

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

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

RE: 10i

2003-05-30 Thread Boivin, Patrice J

I thought the database was going to manage itself!

Now I'm disappointed, I thought they were going to implement just one nice
big GUI button that does everything.

Patrice.

-Original Message-
Sent: Thursday, May 29, 2003 4:01 PM
To: Multiple recipients of list ORACLE-L


Dennis,
 What he probable means is the OCP upgrade exam will be just like the
first OCP exams, Multiple and expensive... looking at some of the new
stuff
Ron

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

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

RE: C++ Issues

The executable is looking for the oracle-related variables in registry.
Export registry and import it into another box and it will work.

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


-Original Message-
Sent: Thursday, May 29, 2003 2:52 PM
To: Multiple recipients of list ORACLE-L


I am trying to track this down for a buddy, any ideas, I know nothing about
C++.

Thanks,
Ethan




Our application currently has presentation programs written in Microsoft
Visual C++ that read Oracle Version 7.3.4 databases.  We have Oracle
Professional/2000 installed on 1 machine.  The Pro*C/C++ pre-compiler
provided by Oracle is the method we use to pre-compile our program.  This
pre-compile converts the EXEC SQL commands into C++ calls to incorporate the
Oracle Database functions into the programs.  Other methods I have found in
research is to use OCI or ODBC calls.  However everything I look at
indicates that we would need to rewrite our applications to utilize
additional include libraries as well as modify our  SQL calls to wrap them
with the appropriate language elements.

We wish to fully utilize the functionality of the Microsoft Visual C++
Professional Edition environment to allow each developer to use their own
machine to compile and unit test these programs.  However, I am unable to
get the pre compile process to work.  Pro C will not run on our machines if
it is not installed.  We get a Incorrect environment variable.  Please
reinstall Pro*C/C++ message version error if we try to run the copy that is
on the compiler machine from our machine.  However, I can not install it
because Oracle 7.3.4 does not support Windows 2000 and the installation
process abends whenever I try to run it.  

What we are looking for is a method to compile, debug, and unit test using
C++ on our machine without getting rid of Pro*C/C++ and yet utilize each
developer's machine more to remove the load from our compiler machine.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Post, Ethan
  INET: [EMAIL PROTECTED]

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

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

RE: dbms_stats

Hi Wolfgang, 

In the grand scheme of things, that probably isn't awful.  However, if the analyze 
can't get the row count right (how easy is that?) then how can I trust it to get the 
rest of the statistics correct?  Just my two cents.

Thanks for your reply.
Lisa

-Original Message-
Sent: Thursday, May 29, 2003 3:25 PM
To: Multiple recipients of list ORACLE-L


I wouldn't call 1.4%  [ (603826-595500)/603826 ] way wrong. Actually, for 
a 1% sample I find that pretty good. The problem I found with low sampling 
percentages is if you have skewed column values. If some values occur very 
often and others rather seldom, a 1% sample may only encounter the 
frequently occurring values and none of the infrequently occurring ones and 
come up with a really way off estimate for num_distinct.

At 10:05 AM 5/29/2003 -0800, you wrote:
Hi John,

Yes, monitoring was set.  I wouldn't see anything in *tab_modifications if 
monitoring wasn't set.

Here's a new twist.  What percentage are you comfortable with for valid 
estimates?  I attended a seminar given by Jonathan Lewis a few weeks ago, 
and he stated that adequate statistics can be gathered using 1% 
sample.  That was great news to me, who has time for huge estimates?  OK, 
so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and 
estimate_percent set to 1.  The rowcounts are way wrong.

(FACP-LISA)SELECT COUNT(*)
   2  FROM VEGAS_MART
   3  PARTITION (MAY_28_2003);

   COUNT(*)
--
 603826

(FACP-LISA)select num_rows
   2  from dba_tab_partitions
   3  where table_name = 'VEGAS_MART'
   4  and partition_name = 'MAY_28_2003';

   NUM_ROWS
--
 595500

(FACP-LISA)

And Jonathan if you happen to read this email, if I am mis-stating what 
you stated in class please correct me.  I am on 8.1.7.4 and that may be 
the difference.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

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

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

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

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

RE: is this DBA's only mailing list?

heretic!





[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 05/29/2003 12:31 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: is this DBA's only mailing list?


 We do not want developers to learn or secrets. That might
give them an idea that we're mere mortals.
 
Yeah, I've learned from reading this list that not all DBAs are the 
super-smart gods they set themselves up to be.  Some are actually not too 
bright, based on things I've seen here. The Peter Principle lives!   :-)
 
Dave
 


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

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

RE: 10i

2003-05-30 Thread April Wells

Title: RE: 10i





Don't know about manage itself. Is supposed to find its own patches, download them and patch itself!


HEY... if it has one big GUI button... and it is a GREEN button (Like SQL Navigator) ... our developers will LOVE it!



April Wells
Oracle DBA/Oracle Apps DBA
Corporate Systems
Amarillo Texas


You will recognize your own path when you come upon it, because you will suddenly have all the energy and imagination you will ever need.

~ Jerry Gillies ~




-Original Message-
From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 29, 2003 3:00 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: 10i



I thought the database was going to manage itself!


Now I'm disappointed, I thought they were going to implement just one nice
big GUI button that does everything.


Patrice.


-Original Message-
Sent: Thursday, May 29, 2003 4:01 PM
To: Multiple recipients of list ORACLE-L



Dennis,
What he probable means is the OCP upgrade exam will be just like the
first OCP exams, Multiple and expensive... looking at some of the new
stuff
Ron


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


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



The information contained in this communication, including attachments, is strictly 
confidential and for the intended use of the addressee only; it may also contain 
proprietary, price sensitive, or legally privileged information. Notice is hereby given that 
any disclosure, distribution, dissemination, use, or copying of the information by anyone 
other than the intended recipient is strictly prohibited and may be illegal. If you have 
received this communication in error, please notify the sender immediately by reply e-mail, 
delete this communication, and destroy all copies.
 

Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to 
this e-mail has been swept for viruses. We specifically disclaim all liability and will 
accept no responsibility for any damage sustained as a result of software viruses and advise 
you to carry out your own virus checks before opening any attachment.

RE: dbms_stats broken

2003-05-30 Thread Jesse, Rich

I looked at bug 1890016 on MetalClink and I'm confused.

Did you run ANALYZE COMPUTE before running DBMS_STATS like the bug's test
case?  I thought this was a no-no???  If so, I'm wondering if it would it
help to ANALYZE DELETE then rerun DBMS_STATS w/EMPTY, insert/update/delete
test rows, force an update of DBA_TAB_MONITORING (via SHUTDOWN NORMAL or the
new 9i method that escapes me), then try DBMS_STATS w/STALE option.

Just a thought...


Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA


 -Original Message-
 From: Koivu, Lisa [mailto:[EMAIL PROTECTED]
 Sent: Thursday, May 29, 2003 9:46 AM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: dbms_stats broken 
 
 
 Hi Darrell  Kirti - 
 
 It was late last night when I was looking at this.  It seems 
 I should be using GATHER EMPTY instead of GATHER STALE.  
 However this will put statistics on partitions with no rows.
 
 When I load new partitions tomorrow (they are empty prior to 
 the daily load), the number of rows inserted wouldn't reach 
 10% change.  It would take over a week to reach 10% change in 
 order for GATHER STALE to pick up on this and re-analyze 
 these partitions.  I don't want statistics saying there are 0 
 records in a partition that is indeed loaded.  
 
 I guess GATHER STALE would be much more useful if the 10% 
 threshold could be modified, and/or the threshold could be 
 partition specific.  
 
 And Kirti, the bugs I have seen are 1192012, 1890016, 
 2157655.  I thought I was running into 1890016.  
 
 Looks like I'll have to code around this after all. Darn it 
 all.  I really am a monkey. Sheesh
 
 Lisa
 
 
 -Original Message-
 Sent: Thursday, May 29, 2003 12:30 AM
 To: Multiple recipients of list ORACLE-L
 
 
 Lisa,
 
 Wow, you might be saving me from peril right now.  I have tested this
 with a small set of tables with no problems (in and 8.1.7.4 
 instance). 
 I'm preparing to go 'schema' wide in the next week or so for further
 testing prior to implementing in production.
 I'd be very interested in more details of your problems in 8.1.7.4 and
 of course I'll post reports of testing to the list as well.
 
 For starters, here is the code I use to obtain a list of 'stale'
 qualified tables:
 ( For proper credit, I think I got this from asktom.oracle.com)
 
 
 set serverout on size 9
 
 declare
 l_objList dbms_stats.objecttab;
 begin
 dbms_stats.gather_schema_stats
 ( ownname = '1',
 options = 'LIST STALE',
 objlist = l_objlist );
 for i in 1 .. l_objlist.count
 loop
 --dbms_output.put_line( l_objlist(i).objtype );
 dbms_output.put_line( l_objlist(i).objname );
 end loop;
 end;
 /
 
 
 And the code to gather stats:
 
 set serverout on size 99000
 
 begin
 dbms_stats.gather_schema_stats(
 ownname='1',
 options='GATHER STALE',
 cascade=TRUE,
 degree=8,
 granularity='ALL',
 method_opt='FOR ALL INDEXED COLUMNS SIZE 1'
 );
 end;
 /
 
 
 Thanks,
 Darrell
 
 
  [EMAIL PROTECTED] 05/28/03 09:24PM 
 Hello everyone, 
  
 Is anyone using dbms_stats and gather stale or gather auto in 9.2? 
 I'm
 trying to use dbms_stats gather schema stats with the stale option and
 it just isn't working in 8.1.7.4.  This is documented on Metalink. 
 I'd
 love to hear from someone else if this is fixed in 9.2 and if it can
 be
 reliably used. 
  
 Thank you
 Lisa Monkey.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

Re: SQL Loader Concatenate date and time

2003-05-30 Thread Jonathan Gennick

Hello Bob,

My first thought is to try something like:

...
log_date CHAR
  TO_DATE(:log_date || ' ' || :log_time,'ddMon hh:mi:ss',
log_time FILLER char,
...

I may not have the syntax just right, and I can't take time
to test it until later this evening (shouldn't be reading
ORACLE-L now anywaygrin), but I'm fairly certain this
approach can be made to work.

Best regards,

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

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


Thursday, May 29, 2003, 11:54:44 AM, you wrote:
BM All
BM Im need  to concatenate my log_date and log_time field (the 2
BM physical records into one logical record).
BM  I can find how to do it with a fixed length file but my case is a
BM delimited file. Looking at the docs, it seems the continueif is used for
BM delimited dtaa, I cant seem to get the syntax to work

BM any ideas would be welcome ;-)
BM thanks!
BM Bob

BM LOAD DATA 
BM INFILE 'F:\528log.txt'
BM BADFILE 'F:\bad.txt'
BM truncate
BM INTO TABLE log
BM FIELDS TERMINATED BY WHITESPACE  OPTIONALLY enclosed by ''
BM TRAILING(
BM log_id INTEGER,
BM log_date DATE 'DD-MON-',
BM log_time char,
BM vpn_type char,
BM interface char,
BM name char,
BM type char,
BM action char,
BM service char,
BM source char,
BM destination char,
BM protocol char,
BM port char,
BM service2 char,
BM log_user char,
BM message char)

BM example source row data
BM 283700 28May2003 16:28:12 fff eth-sfp1c0 fff Log Accept
BM nbdatagram 10.54.4.1 10.54.255.255 udp 23 nbdatagram  


BM Example oracle row data
BM 283700  05/28/2003 00:00:00 16:28:12fff eth-s4fp1c0
BM fff Log Accept  nbdatagram  10.54.4.1   10.54.255.255
BM udp 23  nbdatagram  
BM -- 
BM Please see the official ORACLE-L FAQ: http://www.orafaq.net

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

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

Re: C++ Issues

2003-05-30 Thread Jeff Herrick


Ethan

Get him to install the 8.1.7 Client (Programmer) option on the Win2K
Visual Studio machines. I noticed some slight source changes needed
going from the 7.3.4 Proc*C version...i.e. EXEC SQL DECLARE's are
needed everywhere nowbut they should be able to pre-process,
compile and link. the question would be whether or not the
8.1.7 NET8 DLL's are backward compatible with the 7.3.4 DLL's
that they will be running against...my _guess_ is that they would
be ok

Cheers

Jeff Herrick

psIf they are going to switch don't go to OCI or ODBC...get
  them to use Oracle Objects for OLE


On Thu, 29 May 2003, Post, Ethan wrote:

 I am trying to track this down for a buddy, any ideas, I know nothing about
 C++.

 Thanks,
 Ethan

 
 

 Our application currently has presentation programs written in Microsoft
 Visual C++ that read Oracle Version 7.3.4 databases.  We have Oracle
 Professional/2000 installed on 1 machine.  The Pro*C/C++ pre-compiler
 provided by Oracle is the method we use to pre-compile our program.  This
 pre-compile converts the EXEC SQL commands into C++ calls to incorporate the
 Oracle Database functions into the programs.  Other methods I have found in
 research is to use OCI or ODBC calls.  However everything I look at
 indicates that we would need to rewrite our applications to utilize
 additional include libraries as well as modify our  SQL calls to wrap them
 with the appropriate language elements.

 We wish to fully utilize the functionality of the Microsoft Visual C++
 Professional Edition environment to allow each developer to use their own
 machine to compile and unit test these programs.  However, I am unable to
 get the pre compile process to work.  Pro C will not run on our machines if
 it is not installed.  We get a Incorrect environment variable.  Please
 reinstall Pro*C/C++ message version error if we try to run the copy that is
 on the compiler machine from our machine.  However, I can not install it
 because Oracle 7.3.4 does not support Windows 2000 and the installation
 process abends whenever I try to run it.

 What we are looking for is a method to compile, debug, and unit test using
 C++ on our machine without getting rid of Pro*C/C++ and yet utilize each
 developer's machine more to remove the load from our compiler machine.
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Post, Ethan
   INET: [EMAIL PROTECTED]

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


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

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

RE: exp, dbms_stats, RMAN and rollback segments

Hi Kirti,

Sounds like you have the same suspicions as do we. So far as we know there isn't any 
heavy duty DML before the export and there isn't any other activity on the schema 
other than RMAN and DBMS_STATS. We don't understand how RMAN or DBMS_STATS ***could*** 
be the culprit but we've seen WEIRDNESS before.

We host our webapp and upgrades are customer driven. (Shivers and shudders!) Upgrades 
often entail database changes so the application shuts out end user access and kicks 
off an export before changing tables, munging data, updating the code, etc. This 
process has been fairly automagic and the export went along about 5 minutes before it 
crapped out. The only known difference between this upgrade run and other successful 
ones is that dbms_stats was running. So now I've copied the data into our test 
environment in an effort to duplicate the error and walk through our code. Luckily 
it's not written in Perl so it won't be too hard to read. :-)

Also, Walt found some delayed block cleanout issues on Metalink associated with 
analyzing indexes. The DBMS_STATS.GATHER_TABLE_STATS(...) routine in question is using 
the cascade option so I'm beginning to smell smoke. There are a few docs on Metalink 
about how ORA-01555's can occur even when NO updates are being performed but 
statistics are being gathered. WEIRDNESS!! See DocID's 367016.995; 17730.996; 45895.1; 
89633.996; 61552.1. UNFORTUNATELY... the solutions proposed are not very appealing. 

Meanwhile we're trying to convince others that Oracle is better than MySQL even though 
MySQL continuously updates its optimizer statistics and doesn't have problems like 
this.  :-(  And proposing a blockout period when customers are not allowed to upgrade 
while maintenance operations are going on would not be very well received. Especially 
since it's not an issue with MySQL and how we keep talking about how Oracle is a 
better 24X7 solution for our 24X7 webapp. Arghh!

Big sigh while contemplating a lot of tedious work and getting nowhere... And I had to 
get up at 2:30 A.M. this morning to help fix the outage. 

Whine, whine, whine...
Steve



-Original Message-
Sent: Thursday, May 29, 2003 11:51 AM
To: [EMAIL PROTECTED]
Cc: Orr, Steve


Steve,
 You may have to dig a little further...  
 What happened to those table(s) in that schema prior to starting the export? Heavy 
DML, may be?
 
 This could be a case of 'delayed block cleanout'. Export triggered the cleanout and 
wanted to
access the rollback segments. 

 If no table data was modified after export started reading that table, then there is 
no need to
read RBS info (except for the DBC case, IMO). 

- Kirti  

--- Orr, Steve [EMAIL PROTECTED] wrote:
 Oh yeah, for the export consistent=N
 
 -Original Message-
 Sent: Thursday, May 29, 2003 9:14 AM
 To: '[EMAIL PROTECTED]'
 
 
 A certain alignment of the planets occurred creating a good ole ORA-01555 error... A 
 user level
 export received the snapshot too old error and terminated. Concurrent to this was an 
 RMAN backup
 and DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on the same schema being 
 backed up
 via the user level export. There was no other end user access to the schema data. 
 Since exp got
 the error I assume it was reading from the rollback segments but why? I'm suspecting 
 dbms_stats.
 We have ample RBS. Is there any significant undo generated by dbms_stats or RMAN 
 which could
 create this problem? 
 
 (Of course we need to improve our job scheduling but that's another issue, the 
 timing of the
 user level export is application driven and out of our control). 
 
 
 Befuddled in Bozeman,
 Walt and Steve
 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Orr, Steve
  INET: [EMAIL PROTECTED]

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

RE: dbms_stats broken

2003-05-30 Thread Darrell Landrum

I haven't read the note yet, but what Rich mentions here may explain why
I haven't encountered any problems.
I started out testing with copies of prod tables which had no stats at
all.  So their first stats were gathered with gather_shema_stats.

 [EMAIL PROTECTED] 05/29/03 03:14PM 
I looked at bug 1890016 on MetalClink and I'm confused.

Did you run ANALYZE COMPUTE before running DBMS_STATS like the bug's
test
case?  I thought this was a no-no???  If so, I'm wondering if it would
it
help to ANALYZE DELETE then rerun DBMS_STATS w/EMPTY,
insert/update/delete
test rows, force an update of DBA_TAB_MONITORING (via SHUTDOWN NORMAL
or the
new 9i method that escapes me), then try DBMS_STATS w/STALE option.

Just a thought...


Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI
USA


 -Original Message-
 From: Koivu, Lisa [mailto:[EMAIL PROTECTED] 
 Sent: Thursday, May 29, 2003 9:46 AM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: dbms_stats broken 
 
 
 Hi Darrell  Kirti - 
 
 It was late last night when I was looking at this.  It seems 
 I should be using GATHER EMPTY instead of GATHER STALE.  
 However this will put statistics on partitions with no rows.
 
 When I load new partitions tomorrow (they are empty prior to 
 the daily load), the number of rows inserted wouldn't reach 
 10% change.  It would take over a week to reach 10% change in 
 order for GATHER STALE to pick up on this and re-analyze 
 these partitions.  I don't want statistics saying there are 0 
 records in a partition that is indeed loaded.  
 
 I guess GATHER STALE would be much more useful if the 10% 
 threshold could be modified, and/or the threshold could be 
 partition specific.  
 
 And Kirti, the bugs I have seen are 1192012, 1890016, 
 2157655.  I thought I was running into 1890016.  
 
 Looks like I'll have to code around this after all. Darn it 
 all.  I really am a monkey. Sheesh
 
 Lisa
 
 
 -Original Message-
 Sent: Thursday, May 29, 2003 12:30 AM
 To: Multiple recipients of list ORACLE-L
 
 
 Lisa,
 
 Wow, you might be saving me from peril right now.  I have tested
this
 with a small set of tables with no problems (in and 8.1.7.4 
 instance). 
 I'm preparing to go 'schema' wide in the next week or so for further
 testing prior to implementing in production.
 I'd be very interested in more details of your problems in 8.1.7.4
and
 of course I'll post reports of testing to the list as well.
 
 For starters, here is the code I use to obtain a list of 'stale'
 qualified tables:
 ( For proper credit, I think I got this from asktom.oracle.com)
 
 
 set serverout on size 9
 
 declare
 l_objList dbms_stats.objecttab;
 begin
 dbms_stats.gather_schema_stats
 ( ownname = '1',
 options = 'LIST STALE',
 objlist = l_objlist );
 for i in 1 .. l_objlist.count
 loop
 --dbms_output.put_line( l_objlist(i).objtype );
 dbms_output.put_line( l_objlist(i).objname );
 end loop;
 end;
 /
 
 
 And the code to gather stats:
 
 set serverout on size 99000
 
 begin
 dbms_stats.gather_schema_stats(
 ownname='1',
 options='GATHER STALE',
 cascade=TRUE,
 degree=8,
 granularity='ALL',
 method_opt='FOR ALL INDEXED COLUMNS SIZE 1'
 );
 end;
 /
 
 
 Thanks,
 Darrell
 
 
  [EMAIL PROTECTED] 05/28/03 09:24PM 
 Hello everyone, 
  
 Is anyone using dbms_stats and gather stale or gather auto in 9.2? 
 I'm
 trying to use dbms_stats gather schema stats with the stale option
and
 it just isn't working in 8.1.7.4.  This is documented on Metalink. 
 I'd
 love to hear from someone else if this is fixed in 9.2 and if it can
 be
 reliably used. 
  
 Thank you
 Lisa Monkey.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED] 

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

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

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

RE: SAP Hands SAP DB over to MySQL

I knew a guy name Dawson who worked for Lawson which was keen on Informix.

-Original Message-
Sent: Thursday, May 29, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L


Thanks, Kip, Jared, Dick - that makes more sense. I now recall that Lawson
also ships a free (non-relational) database with its software. Some sites
use it for their first year conversion activities (and some much longer). It
is much faster than Oracle or the other relational databases. When the
customer would spend the big bucks to buy Oracle they would expect it to be
faster and were very shocked. When I worked for Lawson one of my duties was
to try to reason with the customer on this. The Sybase interface developer
Bob sat across the aisle from me and he had a very belligerent customer that
wouldn't understand this. Day after day he would harangue Bob. One day he
called up and icily said that the Oracle salesman had agreed to apply his
Sybase license fee toward Oracle. When he hung up Bob smiled sweetly and
said he's all yours now.

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


-Original Message-
Sent: Thursday, May 29, 2003 1:05 PM
To: Multiple recipients of list ORACLE-L


The incentive?  $$$, and lots of it.

Larry E talked folks into trusting Oracle at it's inception, and I'll bet 
it
was quite a bit less robust then than MySQL is now.

Give MySQL a couple years and see what happens. 

It may never be appropriate for certain apps, but for small to medium
plain vanilla RDBMS, it may be a good fit.

Jared






DENNIS WILLIAMS [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 05/29/2003 07:49 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: SAP Hands SAP DB over to MySQL


Okay, I've got a question for you people knowledgeable in SAP. Who would
trust their financial and payroll data to MySQL? I'm not saying this to
knock MySQL. It is just that your financial and payroll data is among your
most valuable data in the corporation. Only recently was a transaction
capability added to MySQL, and that was more of an add-on. I manage
several databases and my company even makes modest use of MySQL. But of
those databases, the financial and payroll data would be the LAST database 
I
would convert to something like MySQL. I'm just curious what is the big
incentive in MySQL is.

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


-Original Message-
Sent: Wednesday, May 28, 2003 5:50 PM
To: Multiple recipients of list ORACLE-L


Maybe it is just so they can continue to say they're not a database
company
(insert sound of condescension) to emphasize their focus on applications 
excellence in the veiled jabs they continue to make at Oracle.  On the
other
hand, I can't imagine they would give up development control because they 
do

make a specialized version of SAP DB for object oriented programming
(they say they couldn't find a product that worked correctly...). 

Kip 

|I dunno.  Though both want to make a profit ( and rightly so ) SAP
|doesn't seem to have the same mercenary mentality that MS has.

|Jared


|Orr, Steve [EMAIL PROTECTED]
|Sent by: [EMAIL PROTECTED]
| 05/28/2003 11:52 AM
| Please respond to ORACLE-L

 
|To: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
|cc:
|Subject:RE: SAP Hands SAP DB over to MySQL


|Reminiscent of the M$/Sybase partnership?


|-Original Message-
|Sent: Wednesday, May 28, 2003 11:40 AM
|To: '[EMAIL PROTECTED]'


|At http://www.sapdb.org/7.4/pdf/sapdb_letter.pdf SAP offers
clarification:

|SAP:
|Contrary to erroneous press reports, SAP AG has not given up any rights
|concerning the SAP DB code base nor handed over or even sold SAP DB to
|MySQL AB.

|SAP:
|SAP AG remains responsible for ongoing development and support.

|CNet:
|MySQL will take over most of the development of SAP DB.



|-Original Message-
|Sent: Wednesday, May 28, 2003 12:01 PM
|To: Multiple recipients of list ORACLE-L
|Importance: High


|The past few months I've been wondering when MySQL would start
|putting pressure on Oracle in the same way that Linux is putting
|pressure on MS.

|Maybe sooner than you think:

|http://news.com.com/2100-1012_3-1010522.html?tag=fd_top


|Jared

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

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

RE: dbms_stats