date:20031001

In 9i you could issue an drop tablespace temp including contents and
datafiles.
(First make sure that this tablespace is not a default temporary
tablespace).

Tanel.



- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 5:54 AM


 What is the Oracle version?

 At 06:24 PM 9/30/2003 -0800, you wrote:
 Hi!!
 I am trying to change the size of my tablespace TEMP, I am not an
 Administrator but we really need to make this tablespace smaller.
 Already the size is 13214 Mgs, and this tablaspace is on a disk that is
 full, so if we can not make it smaller we are going to be in a serius
 trouble ( our Administrator is not here until Monday).

 Wolfgang Breitling
 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: Tanel Poder
  INET: [EMAIL PROTECTED]

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

9i RAC Scripts.// LONG

2003-10-01 Thread Jack van Zanen

Hi All,

I am trying to figure out what and why to monitor in a 9i RAC environment.
Could any of you guys please comment on the following:




V$FALSE_PING 

is an Oracle9i Real Application Clusters view. 
This view displays buffers that may be getting false pings. 
That is, buffers pinged more than 10 times that are protected by the same
lock as another buffer that pinged more than 10 times. 
Buffers identified as getting false pings can be remapped in
GC_FILES_TO_LOCKS to reduce lock collisions. 




Note: 
Setting this parameter to any value other than the default will disable
Cache Fusion processing in Oracle9i Real Application Clusters.  

!Does this mean we should not temper with this???





I guess the number OF ROWS returned BY this query should be AS close to zero
AS possible, Right?
From the rows returned I guess the forced_reads/writes should be as close to
zero as possible, Right ?
 
Select name
,   partition_name
,   kind
,   file#
,   block#
,   status
,   xnc
,   forced_reads
,   forced_writes
From  GV$FALSE_PING



V$CACHE_TRANSFER

This is an Oracle9i Real Application Clusters view. 
The V$CACHE_TRANSFER view is identical to the V$CACHE view but only displays
blocks that have been pinged at least once. 
This view contains information from the block header of each block in the
SGA of the current instance as related to particular database objects.

I guess the number OF ROWS returned BY this query should be AS close to zero
AS possible, Right?
From the rows returned I guess the forced_reads/writes should be as close to
zero as possible, Right ?

select name
,   partition_name  
,   kind
,   file#
,   block#
,   status
,   xnc
,   forced_reads
,   forced_writes
fromgv$cache_transfer;





There are many views based on global cache etc.. That are not really clear
to me what I can use to figure out cache fusion problems.

I did find in the documentation that information could be collected from
v$sysstat about global cache/locks that can be used to calculate certain
response times etc..


TIA


Jack





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

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

2003-10-01 Thread Pete Finnigan

Hi Govindan

Good thought!!, I was going to suggest the same idea, just to go and get
the description of the package / procedure / function from the
dictionary and then grant access to the dictionary views needed. 

One slight flaw with your code though, you have selected from user_%
views but the OP wanted to be able to let another user describe *his*
procedures and packages, you would need to use dba_% views as if the
other person had not been granted access to the OP's procedures then
they wouldn't be in ALL_% for him or in user_% views.

kind regards

Pete

In article [EMAIL PROTECTED], Govindan K
[EMAIL PROTECTED] writes
This was the closest i could get.
 
set pagesize 60;
set linesize 180;
column position noprint;
column sequence noprint;
break on object_type skip 1;
break on package_name skip 1;
break on object_name skip 1;
column object_type format A15 wrap;
column package_nameformat A30 wrap;
column object_name format A30 wrap;
column argument_name   format A30 wrap;
column in_out  format A10 wrap;
column data_type   format A15 wrap;
column default_value   format A10 wrap;
column type_name   format A10 wrap;
column type_subnameformat A10 wrap;
select 
  b.object_type
 ,a.package_name
 ,a.object_name
 ,a.argument_name
 ,a.position
 ,a.sequence
 ,a.in_out
 ,a.data_type
 ,a.default_value
 ,a.type_name
 ,a.type_subname
 from user_arguments a
,user_objects b
where  a.position  0
and b.object_id = a.object_id
order by
 b.object_type
,a.package_name
,a.object_name
, a.position
/
 
Create a procedure which will dbms_output this and grant execute
priviliges on it.

-Original Message-

From: Gary Jackson
Sent: 9/30/2003 9:31:29 AM
To: [EMAIL PROTECTED]

(Reposting from yesterday morning since I had no takers! :) 

Hello, 
I wanted to give another user access to view my procedures  packages
(just 
DESC capability), but it seems that the only way for him to be able to
DESC 
them is for me to grant execute. Is this correct?? (I guess I have never
had this situation before, it just seems surprising if there is no way
to 
grant a read-only privilege). 

Thanks! 

_ 

Author: Gary Jackson 
INET: [EMAIL PROTECTED] 

. 
   

___
Get Your 10MB account for FREE at http://mail.arabia.com !
Access MILLIONS of JOBS NOW!
http://ads.arabia.com/?SHT=text_email_english 

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

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

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

Re: 9i RAC Scripts.// LONG

GC_FILES_TO_LOCKS? Sounds familiar from some other times. Why do you use
hashed (static) locks? I thought that releasable locks are default in 9i?
Is there a reason to revert to the Oracle 7 OPS behvior?
On 2003.10.01 06:13, Jack van Zanen wrote:
Hi All,

I am trying to figure out what and why to monitor in a 9i RAC environment.
Could any of you guys please comment on the following:



V$FALSE_PING
is an Oracle9i Real Application Clusters view.
This view displays buffers that may be getting false pings.
That is, buffers pinged more than 10 times that are protected by the same
lock as another buffer that pinged more than 10 times.
Buffers identified as getting false pings can be remapped in
GC_FILES_TO_LOCKS to reduce lock collisions.


Note:
Setting this parameter to any value other than the default will disable
Cache Fusion processing in Oracle9i Real Application Clusters.
!Does this mean we should not temper with this???



I guess the number OF ROWS returned BY this query should be AS close to zero
AS possible, Right?
From the rows returned I guess the forced_reads/writes should be as close  
to
zero as possible, Right ?

Select name
,   partition_name
,   kind
,   file#
,   block#
,   status
,   xnc
,   forced_reads
,   forced_writes
From  GV$FALSE_PING



V$CACHE_TRANSFER
This is an Oracle9i Real Application Clusters view.
The V$CACHE_TRANSFER view is identical to the V$CACHE view but only displays
blocks that have been pinged at least once.
This view contains information from the block header of each block in the
SGA of the current instance as related to particular database objects.
I guess the number OF ROWS returned BY this query should be AS close to zero
AS possible, Right?
From the rows returned I guess the forced_reads/writes should be as close  
to
zero as possible, Right ?

select name
,   partition_name  
,   kind
,   file#
,   block#
,   status
,   xnc
,   forced_reads
,   forced_writes
fromgv$cache_transfer;



There are many views based on global cache etc.. That are not really clear
to me what I can use to figure out cache fusion problems.
I did find in the documentation that information could be collected from
v$sysstat about global cache/locks that can be used to calculate certain
response times etc..
TIA

Jack





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

RE: Separate Indexes and Data

2003-10-01 Thread Mark Leith




Couldn't you do this with a simple:

select 
owner, table_name
from 
all_tables
where 
tablespace_name= 'index_tbs';

?

Or of 
course use IN for a list of tablespaces?

Or am 
I missing something?


  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of 
  [EMAIL PROTECTED]Sent: 30 September 2003 
  22:45To: Multiple recipients of list ORACLE-LSubject: 
  RE: Separate Indexes and DataGood question Ian. If anyone does have a different backup 
  schedule for index tbs , I would 
  be interested to know how they ensure that the index TBS do not have 
  any data segments in them. 
  Jared 
  


  
  "MacGregor, Ian A." 
[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 
09/30/2003 10:34 AM 
Please respond to ORACLE-L 
  To:   
 Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED] cc:

 Subject:RE: Separate Indexes and 
DataI'd be very interested to know how many people have their index 
  tablespaces on a different backup schedule from their data tablespaces. 
  If so how different? What happens when a media failure 
  occurs and you must restore from backup? You would need to have on hand 
  and apply more redo logs to make the database current. I 
  understand the argument proffered is separating indexes and data can mean that 
  when physical corruption of the file happens to an index tablespace then all 
  one needs do is to offline, drop, drop and rebuild the index tablespace. 
  I admit I have not tried off-lining the tablespace first, but you cannot 
  normally drop a tablespace which is being used to enforce referential 
  integrity. If off-lining the tablespace first does work, I can see 
  someone trying to do the rebuild with the database available and having 
  duplicate records in the parent tables and records without parents in the 
  child tables.On the size of the segments: The paper entitled 
  "How To Start Defragmenting and Start Living" or something like that 
  strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 
  128M, and 4G as I recall. However the paperNever mentioned what to 
  do when an object that used to fit nicely into the 128k extent 
  category now more properly belongs to the 4M category. If you move 
  the data, large holes are left in the other tablespace, and while this 
  does not impact Oracle performance, it does mean that your physical backups 
  are larger than necessary. I am in the process of migrating from uniform 
  to autoallocated extents. This means extents of different sizes share 
  the same tablespace. The extent sizes being multiples of each other. 
  This removes the argument about not having indexes and data in the same 
  tablespaces due to their different sizes. Ian 
  MacGregorStanford Linear Accelerator Center 
  [EMAIL PROTECTED]-Original Message-Sent: Monday, 
  September 29, 2003 8:10 AMTo: Multiple recipients of list 
  ORACLE-LThomas,It *is* a good idea to separate index data 
  from heap data into different tablespaces. But the reason isn't solely to 
  eliminate I/O competition. Even if I/O competition isn't an issue for you (and 
  the OFA Standard doesn't say that it will be), then it's *still* a good idea 
  to separate your index data from your heap data, for reasons 
  including:* Index segments have different backup and recovery 
  requirements than their corresponding heap segments. For example, as Peter 
  mentioned, if you have an index block corruption event, then it's convenient 
  to just offline, kill, and rebuild an index tablespace. If the indexes and 
  data are mixed up in a single tablespace, this is not an option. 
  Anotherexample: If you construct your backup schedule to make media 
  recovery time a constant, then you probably don't need to back up your indexes 
  on the same schedule as you back up your heaps. But unless they're in 
  different tablespaces, this isn't an option either.* Index segments 
  are usually smaller than their corresponding heap segments. Using separate 
  tablespaces allows you to use a smaller extent size to conserve disk storage 
  capacity.I don't think I ever wrote that you need to put indexes and 
  their corresponding tables/clusters on separate disks, but you do need to 
  be*able* to do that if your I/O rates indicate that you should.For 
  the original OFA Standard definition, please see section 3 of the document 
  called "The OFA Standard--Oracle for Open Systems," and section 5 of 
  "Configuring Oracle Server for VLDB," both available for free at 
  www.hotsos.com.Cary MillsapHotsos Enterprises, 
  Ltd.http://www.hotsos.comUpcoming events:- Performance 
  Diagnosis 101: 10/28 Phoenix, 11/19 Sydney- Hotsos Symposium 2004: March 
  7-10 Dallas- Visit www.hotsos.com for schedule 
  details...-Original Message-Thomas DaySent: 
  Monday, September 29, 2003 9:05 AMTo: Multiple recipients of list

Re: locally managed autoallocate (was: Separate Indexes and

Actually, 5 blocks wasn't completely hardwired, there was an undocumented
parameter (_walk_insert_threshold or something like that. My notes from  
Scott Gosset's course are largely unreadable. What has hapened to my  
handwriting? )  which was utilized to define the number of blocks that will be  
added to the free list. If I remember corectly, there was a serious bug with
DMT, which was precisely about the FREELIST mechanism which would prevent  
freed blocks from being reused. That was mentioned by somebody else here. I  
believe that the workaround was to set _walk_nsert_threshold to 7. I  
distinctly remember seeing K. Gopalakrishnan's name mentioned in connection
with that, and this is the same symptom that was described by somebody else on  
this list. As I am utilizing LMT's and segment space auto management, I'm  
mostly oblivious to DMT woes. K. Gopalakrishnan (I hope my spelling is  
correct) my shed some more light onto this affair.

On 2003.10.01 00:59, Wolfgang Breitling wrote:
I can't recall right now where I found out about the 3 blocks required for  
automatic space management. Could have been an error message when I tried to  
create a table with a 2 block extent in an ASSM tablespace, or a  
presentation at IOUG, or perhaps even on this list.
The 5 block rule is the documented allocation rule for DMT where Oracle  
rounds requests for segments greater than 5 blocks to the next multiple of 5  
blocks (unless it finds a free segment of exactly the right size or ...).  
Which is why you couldn't implement a uniform extent size policy in DMT with  
extent sizes of exact powers of 2 (64, 128, 256, 1024, ...) to make use of  
the full IO bandwith of the OS (which is generally a power of 2) for full  
scans since they all were not multiples of 5 blocks. Not until the minimum  
extent size option came in Oracle 8 (not to be confused with minextents).  
But then LMTs came in Oracle 8i and retired the entire DMT allocation  
scheme.

At 07:49 PM 9/30/2003 -0800, you wrote:

I repeated your test, with the same result. You, of course, are right.
Interesting, that means that oracle gave up on that 5 blocks rule.
Where did you come accross the fact that automatic space management
requires 3 blocks? That is, I suppose, for freeelists  freelist groups?
I must confess that I assumed that the old 5 blocks rule still holds true,
so I didn't test further. Also, I was testing the problem that I had with
autoallocate and automatic segment management, which turned out to be
a SCSI controller problem. Basically, when I created the tablespace on EIDE
device, it worked as advertised, but when I attempted to do that on a SCSI
disk, it failed. To dispell all doubts, SCSI controller died in 2 days,
causing, of course a system and the database crash. May it rest in peace,
in the place SCSI controllers go when they burn out.
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
 INET: [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).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: locally managed autoallocate (was: Separate Indexes and

2003-10-01 Thread Frits Hoogland

5 is the number of blocks (probably defined in a header file) that is gotten
for creation. 
it could be that the blocksize matters, but haven't seen it any other way
than 5.
after that, the HWM is bumped with 5 blocks too
(_bump_highwater_mark_count) 
_walk_insert_threshold is the number of blocks (5 by default) that oracle
looks at, at the top of the freelist,
for insertion of data.

frits

-Original Message-
Sent: woensdag 1 oktober 2003 14:25
To: Multiple recipients of list ORACLE-L


Actually, 5 blocks wasn't completely hardwired, there was an undocumented
parameter (_walk_insert_threshold or something like that. My notes from  
Scott Gosset's course are largely unreadable. What has hapened to my  
handwriting? )  which was utilized to define the number of blocks that will
be  
added to the free list. If I remember corectly, there was a serious bug with
DMT, which was precisely about the FREELIST mechanism which would prevent  
freed blocks from being reused. That was mentioned by somebody else here. I

believe that the workaround was to set _walk_nsert_threshold to 7. I  
distinctly remember seeing K. Gopalakrishnan's name mentioned in connection
with that, and this is the same symptom that was described by somebody else
on  
this list. As I am utilizing LMT's and segment space auto management, I'm  
mostly oblivious to DMT woes. K. Gopalakrishnan (I hope my spelling is  
correct) my shed some more light onto this affair.


On 2003.10.01 00:59, Wolfgang Breitling wrote:
 I can't recall right now where I found out about the 3 blocks required for

 automatic space management. Could have been an error message when I tried
to  
 create a table with a 2 block extent in an ASSM tablespace, or a  
 presentation at IOUG, or perhaps even on this list.
 The 5 block rule is the documented allocation rule for DMT where Oracle  
 rounds requests for segments greater than 5 blocks to the next multiple of
5  
 blocks (unless it finds a free segment of exactly the right size or ...).

 Which is why you couldn't implement a uniform extent size policy in DMT
with  
 extent sizes of exact powers of 2 (64, 128, 256, 1024, ...) to make use of

 the full IO bandwith of the OS (which is generally a power of 2) for full

 scans since they all were not multiples of 5 blocks. Not until the minimum

 extent size option came in Oracle 8 (not to be confused with minextents).

 But then LMTs came in Oracle 8i and retired the entire DMT allocation  
 scheme.
 
 At 07:49 PM 9/30/2003 -0800, you wrote:
 
 I repeated your test, with the same result. You, of course, are right.
 Interesting, that means that oracle gave up on that 5 blocks rule.
 Where did you come accross the fact that automatic space management
 requires 3 blocks? That is, I suppose, for freeelists  freelist groups?
 I must confess that I assumed that the old 5 blocks rule still holds
true,
 so I didn't test further. Also, I was testing the problem that I had with
 autoallocate and automatic segment management, which turned out to be
 a SCSI controller problem. Basically, when I created the tablespace on
EIDE
 device, it worked as advertised, but when I attempted to do that on a
SCSI
 disk, it failed. To dispell all doubts, SCSI controller died in 2 days,
 causing, of course a system and the database crash. May it rest in peace,
 in the place SCSI controllers go when they burn out.
 
 --
 Mladen Gogala
 Oracle DBA
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Mladen Gogala
  INET: [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).
-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: [EMAIL PROTECTED]

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

UNIX : script help/input

2003-10-01 Thread Johan Muller

Anybody with a quick and dirty (elegant would be nice too), to munge output from a nslookup output file to a delimited file?
'File content:
Server: dns1.mci.comAddress: 199.249.19.1
Name: WCOM-4NXZGAPWY5.mcilink.comAddress: 166.50.73.209
Delimited file should have the following line(s); (using | or whatever asdelimiter):
'166.50.73.209'|'4NXZGAPWY5.mcilink.com'.
Required for both single and multiple records.

TIA

Re: Describe privilege on procedures packages

Hi!

But if this procedure runs in definer rights under schema where the objects
exist, then it should be possible?

Tanel.

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 1:59 PM


 Hi Govindan

 Good thought!!, I was going to suggest the same idea, just to go and get
 the description of the package / procedure / function from the
 dictionary and then grant access to the dictionary views needed.

 One slight flaw with your code though, you have selected from user_%
 views but the OP wanted to be able to let another user describe *his*
 procedures and packages, you would need to use dba_% views as if the
 other person had not been granted access to the OP's procedures then
 they wouldn't be in ALL_% for him or in user_% views.

 kind regards

 Pete

 In article [EMAIL PROTECTED], Govindan K
 [EMAIL PROTECTED] writes
 This was the closest i could get.
 
 set pagesize 60;
 set linesize 180;
 column position noprint;
 column sequence noprint;
 break on object_type skip 1;
 break on package_name skip 1;
 break on object_name skip 1;
 column object_type format A15 wrap;
 column package_nameformat A30 wrap;
 column object_name format A30 wrap;
 column argument_name   format A30 wrap;
 column in_out  format A10 wrap;
 column data_type   format A15 wrap;
 column default_value   format A10 wrap;
 column type_name   format A10 wrap;
 column type_subnameformat A10 wrap;
 select
   b.object_type
  ,a.package_name
  ,a.object_name
  ,a.argument_name
  ,a.position
  ,a.sequence
  ,a.in_out
  ,a.data_type
  ,a.default_value
  ,a.type_name
  ,a.type_subname
  from user_arguments a
 ,user_objects b
 where  a.position  0
 and b.object_id = a.object_id
 order by
  b.object_type
 ,a.package_name
 ,a.object_name
 , a.position
 /
 
 Create a procedure which will dbms_output this and grant execute
 priviliges on it.
 
 -Original Message-
 
 From: Gary Jackson
 Sent: 9/30/2003 9:31:29 AM
 To: [EMAIL PROTECTED]
 
 (Reposting from yesterday morning since I had no takers! :)
 
 Hello,
 I wanted to give another user access to view my procedures  packages
 (just
 DESC capability), but it seems that the only way for him to be able to
 DESC
 them is for me to grant execute. Is this correct?? (I guess I have never
 had this situation before, it just seems surprising if there is no way
 to
 grant a read-only privilege).
 
 Thanks!
 
 _
 
 Author: Gary Jackson
 INET: [EMAIL PROTECTED]
 
 .
 
 
 ___
 Get Your 10MB account for FREE at http://mail.arabia.com !
 Access MILLIONS of JOBS NOW!
 http://ads.arabia.com/?SHT=text_email_english

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

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

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



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

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

RE: COBOL TO ORACLE

2003-10-01 Thread Thomas Day


LOL

Of course the spent a lot on money on veterinarians to inoculate the horses
against the Y2K bug.



   

  Mladen Gogala

  mladen  To:  Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
  @wangtrading.com cc: 

  Subject: RE: COBOL TO ORACLE

  Sent by: 

  ml-errors

   

   

  09/30/2003 05:14 

  PM   

  Please respond   

  to ORACLE-L  

   

   





On Tue, 2003-09-30 at 16:19, Stephane Paquette wrote:
 Like Thomas Day said, Oracle is an rdbms and COBOL a programming
language.


COBOL *** WAS *** a programming language. Horse *** WAS *** basis of
transport. You should have used past tense, Stephane. I'm not really
that partial when it comes to horses, but having suffered COBOL, I would
really leave it in the ancient past, together with Spanish Inquisition
and crucifiction as a viable capital punishment.





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

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

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





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 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: UNIX : script help/input


Anybody with a quick and dirty (elegant would be
nice too), to munge output from a nslookup output
file to a delimited file?

'File content:

Server:  dns1.mci.com
Address:  199.249.19.1

Name:WCOM-4NXZGAPWY5.mcilink.com
Address:  166.50.73.209

Delimited file should have the following line(s); 
(using | or whatever as delimiter):

'166.50.73.209'|'4NXZGAPWY5.mcilink.com'.

Required for both single and multiple records.

 

TIA

awk 'BEGIN{ok=0;}\
 {if (($1 == Address:)  ok)\
 {printf(%s|%s\n, $2, name);ok=0;}\
  else {if ($1 == Name:)\
   {name = $2; ok = 1;}}}' your_file_here

Don't understand your 'single' and 'multiple' records too well but it should get you 
started.

Regards,

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

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

Create Table..As Select: Number formats

2003-10-01 Thread Simon . Anderson

I'm trying to create a table using 'Create Table...As Select...' 

The contents will then be exchanged into a partitioned table, so they need 
to have the same names and datatypes. 

Some of the columns in the created table are populated with zeroes  will 
be updated after the partition exchange, one of the columns is populated 
with a decode that returns a single digit number. 

The problem is that I need particular number formats in these columns - 
the one-digit column should be a number(1,0) and the others should be 
number(12,4). I can't specify column types in create table...as select, so 
how else can I force the columns to a particular format? 


Any ideas much appreciated.

Cheers
Simon Anderson
-- 
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).

vertical serches on a table - how to

2003-10-01 Thread Susan Tay

Hi,
I have a table that I would like to perform a vertical search on. For eg.
Table X
---
IDCOL1 COL2
1 apple orange
1 mango   banana
1 grape pineapple
2 mango   banana
2 guava lemon
I would like to display records that meet the following criteria for *a 
particular ID*.

(COL1=banana)
  OR
(COL1=mango and COL2=banana
AND
COL1=grape and COL2=pineapple
The output should be
ID   COL1COL2
1mango   banana
1grapepineapple
It should not display
ID   COL1COL2
2mango   banana
since ID=2 did not meet the criteria where COL1=grape and COL2=pineapple.

I tried the following SQL but the output is always zero because COL1 can 
never be a mango and a grape and COL2 can never be a
banana and a pineapple at the same time for a particular ID.

select ID, col1, col2
from   tableX
where  (col1='banana')
or ((col1='mango' and col2='banana')
and
   (col1='grape' and col2='pineapple')
   );
Any idea how I can do a vertical search on the table.

Thanks for any help you can provide.

susan

_
Help protect your PC.  Get a FREE computer virus scan online from McAfee. 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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

RE: 9i RAC Scripts.// LONG

2003-10-01 Thread Jack van Zanen


I hope not,

This is from the Oracle documentation (quite a lot is about RAC so to get
the general idea is OK but details are difficult to grasp).
Since the note: said setting that parameter to anything else but the default
would disable cache fusion in Oracle 9i RAC clusters, My question was if we
should temper with this setting. My gut feeling tells me no.
Also if anybody has links to sites that have useful scripts to monitor the
RAC portion of the database, please let me know.

TIA 


Jack



-Original Message-
Sent: Wednesday, October 01, 2003 1:39 PM
To: Multiple recipients of list ORACLE-L


GC_FILES_TO_LOCKS? Sounds familiar from some other times. Why do you use
hashed (static) locks? I thought that releasable locks are default in 9i? Is
there a reason to revert to the Oracle 7 OPS behvior?

On 2003.10.01 06:13, Jack van Zanen wrote:
 Hi All,
 
 I am trying to figure out what and why to monitor in a 9i RAC 
 environment. Could any of you guys please comment on the following:
 
 **
 **


 
 V$FALSE_PING
 
 is an Oracle9i Real Application Clusters view.
 This view displays buffers that may be getting false pings. That is, 
 buffers pinged more than 10 times that are protected by the same lock 
 as another buffer that pinged more than 10 times. Buffers identified 
 as getting false pings can be remapped in GC_FILES_TO_LOCKS to 
 reduce lock collisions.
 
 
 --
 --
 
 Note:
 Setting this parameter to any value other than the default will disable
 Cache Fusion processing in Oracle9i Real Application Clusters.
 
 !Does this mean we should not temper with this???
 
 
 --
 --
 
 
 I guess the number OF ROWS returned BY this query should be AS close 
 to zero AS possible, Right?
 From the rows returned I guess the forced_reads/writes should be as 
 close
 to
 zero as possible, Right ?
 
 Select name
 , partition_name
 , kind
 , file#
 , block#
 , status
 , xnc
 , forced_reads
 , forced_writes
 From  GV$FALSE_PING
 **
 **


 
 V$CACHE_TRANSFER
 
 This is an Oracle9i Real Application Clusters view.
 The V$CACHE_TRANSFER view is identical to the V$CACHE view but only 
 displays blocks that have been pinged at least once. This view 
 contains information from the block header of each block in the SGA of 
 the current instance as related to particular database objects.
 
 I guess the number OF ROWS returned BY this query should be AS close 
 to zero AS possible, Right?
 From the rows returned I guess the forced_reads/writes should be as 
 close
 to
 zero as possible, Right ?
 
 select name
 , partition_name  
 , kind
 , file#
 , block#
 , status
 , xnc
 , forced_reads
 , forced_writes
 from  gv$cache_transfer;
 
 **
 **


 
 
 There are many views based on global cache etc.. That are not really 
 clear to me what I can use to figure out cache fusion problems.
 
 I did find in the documentation that information could be collected 
 from v$sysstat about global cache/locks that can be used to calculate 
 certain response times etc..
 
 
 TIA
 
 
 Jack
 
 
 
 
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Jack van Zanen
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
 the message BODY, include a line containing: UNSUB ORACLE-L (or the 
 name of mailing list you want to be removed from).  You may also send 
 the HELP command for other information (like subscribing).


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

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

Re: UNIX : script help/input

2003-10-01 Thread Joe Testa

I supposed if you send to a file, we can read it using ORACLE to parse 
it w/plsql and then using utl_file write it back out but seems like 
overkill to use oracle for that, but then again this is an oracle list, 
so i'll have to assume thats what you wanted, anyone up for the task :)

joe

Johan Muller wrote:

Anybody with a quick and dirty (elegant would be nice too), to munge 
output from a nslookup output file to a delimited file?

'File content:

Server:  dns1.mci.com
Address:  199.249.19.1
Name:WCOM-4NXZGAPWY5.mcilink.com
Address:  166.50.73.209
Delimited file should have the following line(s);  (using | or 
whatever as delimiter):

'166.50.73.209'|'4NXZGAPWY5.mcilink.com'.

Required for both single and multiple records.

 

TIA

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

STAT from trace

2003-10-01 Thread Henry Poras

I was creating some trace files yesterday and came across one of these
problems that shows up occasionally (then I forget about it).

When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus;
8.1.7.4), I got the STAT line in the trace and the associated 'row source'
information after running tkprof.

When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context
forever, level 8' (or with dbms_system.set_ev), there were no STAT entries
for my query. There were some for some of the recursive queries.

Everything else was identical.

What's up?

Henry

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

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

2003-10-01 Thread Mercadante, Thomas F

Tanel  Kevin,

Thanks for the replies.  Very helpful.  I am using version 9.2.0.3.  You
both confirmed what I thought I should do.

thanks again.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Tuesday, September 30, 2003 5:04 PM
To: Multiple recipients of list ORACLE-L


Hi!

On which version you are?

I would create fairly large extents, 64MB for example. LOBs are stored in
chunks anyway, extent size doesn't matter that much. One issue is, if you
create very large extent size, you might waste some space in LOB index which
is a separate, smaller segment (but is always stored with LOB data segment
in 9i). But your LOBs will work with 64k extent sizes as well, but that way
you might lose some benefit on multiblock direct reads.

Btw, if you use enable storage in row then LOB index entries are always
stored in row, which means for smaller LOBs which don't fit inline, no LOB
index lookup is needed (for large ones I believe there still is, because
large LOBs can't be addressed with small inline inode structure).

If your average lob size is in megabytes, I'd put them into 16k or 32k
tablespaces, away from regular block size and create a different buffer pool
for them - if you are using CACHE type lobs. That way they won't affect
LRU mechanisms for normal data buffers.

Tanel.

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, September 30, 2003 11:24 PM


 All,

 I'm being given a requirement to store a BLOB column in the database.  I'm
 being told that the average size of the file (it's a PDF) is 12,000 K.
I'm
 assuming that I should store this column in a separate tablespace from the
 table data.  If I use an LMT tablespace, what should I use for the uniform
 allocation size?  Should I use 12,000 K or something larger to store one
PDF
 per segment?  Am I all wrong here?

 thanks in advance

 Tom Mercadante
 Oracle Certified Professional

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

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



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

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

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

RE: Create Table..As Select: Number formats

I'm trying to create a table using 'Create
Table...As Select...' 

The contents will then be exchanged into a
partitioned table, so they need 
to have the same names and datatypes. 

Some of the columns in the created table are
populated with zeroes  will 
be updated after the partition exchange, one of the
columns is populated 
with a decode that returns a single digit number. 

The problem is that I need particular number
formats in these columns - 
the one-digit column should be a number(1,0) and
the others should be 
number(12,4). I can't specify column types in
create table...as select, so 
how else can I force the columns to a particular
format? 


Any ideas much appreciated.

Cheers
Simon Anderson

Simon,

   I don't think that there is any problem here. Specifying the number of digits is 
largely cosmetic - consider it as a default mask. It doesn't affect how data is stored 
inside the tables AFAIK.

Regards,

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

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

RE: COBOL TO ORACLE

COBOL isn't dead, it just smells funny. You know what COBOL stands for,
don't you? COBOL=Completely Outdated, Badly Overused Language.
On Tue, 2003-09-30 at 17:24, April Wells wrote:
 COBOL still lives and breathes, though, in many MANY shops (this one
 included).  Just like the Mainframe, it won't go away easily or
 soon... 
 
 I feel your pain, though, I lived through it too... and if I never
 have to figure out where an alter sends the program based on the
 data ever again I will die happy... worse than any goto around!
 
 April Wells
 Oracle DBA/Oracle Apps DBA
 Corporate Systems
 Amarillo Texas
   /\
  /   \
 / \
 \ /
   \/
   \
  \
  \
  \
 Few people really enjoy the simple pleasure of flying a kite
 Adam Wells age 11
 
 
 
 -Original Message-
 From: Mladen Gogala [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, September 30, 2003 4:15 PM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: COBOL TO ORACLE
 
 
 On Tue, 2003-09-30 at 16:19, Stephane Paquette wrote:
  Like Thomas Day said, Oracle is an rdbms and COBOL a programming
 language.
  
 
 COBOL *** WAS *** a programming language. Horse *** WAS *** basis of
 transport. You should have used past tense, Stephane. I'm not really
 that partial when it comes to horses, but having suffered COBOL, I
 would
 really leave it in the ancient past, together with Spanish Inquisition
 and crucifiction as a viable capital punishment.
 
 
 
 
 
 Note:
 This message is for the named person's use only.  It may contain
 confidential, proprietary or legally privileged information.  No
 confidentiality or privilege is waived or lost by any
 mistransmission.  If you receive this message in error, please
 immediately delete it and all copies of it from your system, destroy
 any hard copies of it and notify the sender.  You must not, directly
 or indirectly, use, disclose, distribute, print, or copy any part of
 this message if you are not the intended recipient. Wang Trading LLC
 and any of its subsidiaries each reserve the right to monitor all
 e-mail communications through its networks.
 
 Any views expressed in this message are those of the individual
 sender, except where the message states otherwise and the sender is
 authorized to state them to be the views of any such entity.
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Mladen Gogala
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 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.




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

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

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

RE: vertical serches on a table - how to

Hi,
I have a table that I would like to perform a
vertical search on. For eg.

Table X
---
IDCOL1 COL2
1 apple orange
1 mango   banana
1 grape pineapple
2 mango   banana
2 guava lemon


I would like to display records that meet the
following criteria for *a 
particular ID*.

(COL1=banana)
   OR
(COL1=mango and COL2=banana
AND
COL1=grape and COL2=pineapple


The output should be
ID   COL1COL2
1mango   banana
1grapepineapple

It should not display
ID   COL1COL2
2mango   banana

since ID=2 did not meet the criteria where
COL1=grape and COL2=pineapple.


I tried the following SQL but the output is always
zero because COL1 can 
never be a mango and a grape and COL2 can never be
a
banana and a pineapple at the same time for a
particular ID.

select ID, col1, col2
from   tableX
where  (col1='banana')
or ((col1='mango' and col2='banana')
 and
(col1='grape' and col2='pineapple')
);


Any idea how I can do a vertical search on the
table.

Thanks for any help you can provide.

susan


Susan,

   Took me some time to understand what you meant by 'vertical search'. ANDs and ORs 
in a WHERE clause always apply to the current row under scrutiny. What you mean by 
'vertical' is that you want to filter according to conditions on OTHER rows. This is 
done by a subquery.

Your query could read

select X1.ID, X1.col1, X1.col2
fromtableX X1
where   (X1.col1='banana')
or  ((X1.col1='mango' and X1.col2='banana')
  and EXISTS (select null
  from tableX X2 
  where X2.col1='grape'
and X2.col2='pineapple'
and X2.ID = X1.ID));

Simplifying to the extreme, each different row you handle must be returned by its 
'own' query.

Regards,

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

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

RE: Estimating space needed for UNDO tablespaces

2003-10-01 Thread Kirtikumar Deshpande

Hi Jeff,
 Stealing extents is normal when there is no free space available to grow the active 
undo segment.
If expired extents are getting stolen, I would not worry too much about adding more 
space to the
undo tablespace, but monitor how much undo space the segment takes up. 
If unexpired extents are getting stolen, then you may have to consider either adding 
more space,
or reducing undo retention time. 
If the undo tablespace does not have enough space to accommodate your largest 
transaction, event
after stealing extents, and the data files are not autoexensible, then you will get 
ORA-1650 (I
think, that the error#). And to avoid it, auto undo management still needs to be 
monitored! 

HTH,

- Kirti 


--- Thomas Jeff [EMAIL PROTECTED] wrote:
 Kirti,
 
 Thanks for this information.I've implemented AUM in a number of our
 development
 databases.One of the things I have to do is write up a monitoring policy
 to hand
 to our contracted production DBAs -- guidelines on how to address certain
 scenarios
 and so forth -- otherwise, they will simply resort to adding 'more' of
 whatever they
 presume is in short supply in event of a production crisis.
 
 For example, I'm seeing some steal counts in v$undostat, implying that the
 undo
 tablespace needs more space.   However, from what you are saying, it seems
 that if
 undo_retention is consistently larger then maxquerylen during the period of
 time when 
 the steal counts occur, that maybe the smarter thing to do is simply reduce
 the 
 undo_retention parameter before considering adding more space?
 
 
 Jeff
 
 
 
 -Original Message-
 Sent: Friday, September 26, 2003 11:50 PM
 To: Multiple recipients of list ORACLE-L
 
 
 You can run following query to get an idea of undo generation rate and max
 query length: 
 
 SELECT 
   to_char(min(begin_time),'MM/DD/ HH24:MI:SS') Begin Time,
   to_char(max(end_time),'MM/DD/ HH24:MI:SS') End Time,
   (max(end_time)-min(begin_time))*24*60*60 Seconds,
   sum(undoblks) UndoBlks,
   sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60)
 UndoBlksPerSec,
   max(maxquerylen) MaxQueryLenSecs
  FROM 
 v$undostat;
 
 Unfortunately, the TXNCOUNT column in v$undostat view in Oracle 9.2.0.x is
 screwed up. It does not
 correctly report the transaction count for the sample interval. Instead it
 keeps accumulating. One
 needs to do the math to get the correct count for the desired sample
 interval. It will show the
 time of high transaction activity with related undo generation.
 
 Oracle recommends setting undo_retention to the max(maxquerylen), but use
 your judgement. If data
 loads and queries accessing same tables, do not run at the same time (in DW,
 for example), setting
 undo_retention to a high number (maxquerylen) will simply waste disk space. 
 
 If undo_retention is not set appropriately, you will get ORA-1555, and it
 will be reported in
 alert.log along with the affected SQL statement. The log entry will also
 contain the query time,
 in seconds, before it got aborted due to ORA-1555. 
 
 Also, the above query works only when the database is using AUM. V$undostat
 does not report
 anything in 9.2.0.x when using MUM (manual undo mode). In 9.0.1, it returns
 one useless row when
 using MUM! 
 
 BTW, you can also use the OEM to see the undo generation rate. It is one of
 the few things in OEM
 (standalone mode) I use. 
 
 
 Hth. 
 
 - Kirti 
 
 
 
snip

__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.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).

Re: TEMP Tablespace problem

2003-10-01 Thread Teresita Castro



9.2i [EMAIL PROTECTED] 09/30/03 08:54PM 
What is the Oracle version?At 06:24 PM 9/30/2003 -0800, 
you wrote:Hi!!I am trying to change the size of my tablespace 
TEMP, I am not an Administrator but we really need to make this 
tablespace smaller.Already the size is 13214 Mgs, and this tablaspace is 
on a disk that is full, so if we can not make it smaller we are going to 
be in a serius trouble ( our Administrator is not here until 
Monday).Wolfgang BreitlingCentrex Consulting Corporationhttp://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.comSan Diego, 
California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from). You mayalso send the HELP command for 
other information (like subscribing).

exam

2003-10-01 Thread bulbultyagi

List , thanks to you all I passed my oracle 9i performance tuning exam today
with good marks.
Thank you


-- 
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: STAT from trace

Title: RE: STAT from trace





I get STAT lines no matter how I enable the trace. Make sure you wither close the session or stop the trace so that all pending STAT lines will be written to the file.

Raj


-Original Message-
From: Henry Poras [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 01, 2003 9:34 AM
To: Multiple recipients of list ORACLE-L
Subject: STAT from trace



I was creating some trace files yesterday and came across one of these
problems that shows up occasionally (then I forget about it).


When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus;
8.1.7.4), I got the STAT line in the trace and the associated 'row source'
information after running tkprof.


When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context
forever, level 8' (or with dbms_system.set_ev), there were no STAT entries
for my query. There were some for some of the recursive queries.


Everything else was identical.


What's up?


Henry



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: RE: Separate Indexes and Data

2003-10-01 Thread Connor McDonald

Whilst the vast majority of extents will be the
published ones - 64k, 1m, et al, you occasionally
get variants.

I have only three gripes with auto-allocate:

a) you can't perform the can I extend check on your
tablespaces.  You cannot predict with 100% certainty
what the size of the next extent will be.  You have to
make assumptions.

b) you minimise but not eliminate the fragmentation
problem.  Its rare but its relatively easy to concoct
an example where you have 'n' bytes of free space, but
cannot allocate an extent of size less than 'n' bytes

c) Cynicism.  If there is no problem with 'n' extents
(n  insert high number here) , why does Oracle
implement a solution designed to keep a lid on their
number.  Besides extent map blocks, is there some
serious problem that Oracle is not telling us at the
(say) million extent mark?  If there is, then what is
the problem.  If there is not, then why doesn't Oracle
abandon the concept altogether and just enforce
something similar to what we see in file systems where
every extent is a strict (say) 1m in size.  

Cheers
Connor


 --- MacGregor, Ian A. [EMAIL PROTECTED]
wrote:  My criticism of the defrag paper was that it
did not
 address what to do when a segment grew  large enough
 to belong in a tablespace with a larger uniform
 extent size.  Moving the segment creates  holes in
 its original tablespace which may close only in the
 fullness of time.  Physical backups of the files
 comprising the original tablespace include this
 wasted space, this is compounded by how many days
 backup you keep available, and the number of copies
 of backups. 
 
 You have chosen to get around the segment migration
 problem by using one very large extent size for
 everything.  Don't you find 5M extents wasteful? 
 What is your block size and the median number of
 used blocks for your segments outside of the system
 tablespace?  How many such segments are there?.  
 
 Also many of us use a single backup system to
 support multiple databases.  The number of segments
 outside the system tablespace here is over 125,.
  Making all segments at least 5M in size would have
 a major impact on file sizes, which in turn would
 have a major impact on backup times, and possibly
 the size of the  tape library needed.
 
 I'm interested in the flaws in autoallocate. Does it
 allocate the wrong amount of space?  
 
 
 Ian MacGregor
 [EMAIL PROTECTED]  
 
 -Original Message-
 Sent: Tuesday, September 30, 2003 10:50 AM
 To: Multiple recipients of list ORACLE-L
 
 
 the defrag paper was written back in 1998 I believe.
 Uniform extents were a good solution pre-9i. We use
 them here on our 8i databases. I stick with an
 uniform 5m extent size even though I have tables
 that can fit into 128k extents, but feel that the
 overall time savings by using 1 extent size makes up
 for this.
 
 unfortunately unlike most systems we cannot break up
 our tables into different tablespaces. We use
 transportable tablespaces to batch publish data to
 data marts. New tablespaces mean additional
 transportable tablespaces and more places for stuff
 to go wrong. 
 
 I saw some posts on dejanews recently from some
 pretty experienced DBAs stating that there may be
 'flaws' in auto-allocate leading to poor extent
 sizes that leads to fragmentation. I believe Rachel
 Carmichael made a post on here a few months back
 with the similiar experience(could be wrong). Due to
 even the 'small' chance of flaws in auto-allocate,
 Im thinking of waiting for version 10g before using
 it. Just to be safe. Not worth risking a defrag on a
 production system. 
  
  From: MacGregor, Ian A. [EMAIL PROTECTED]
  Date: 2003/09/30 Tue PM 01:34:28 EDT
  To: Multiple recipients of list ORACLE-L
 [EMAIL PROTECTED]
  Subject: RE: Separate Indexes and Data
  
  I'd be very interested to know how many people
 have their index 
  tablespaces on a different backup schedule from
 their data tablespaces.  If so how different?  What
 happens when a media  failure occurs and you must
 restore from backup?  You would need to have on hand
  and apply more redo logs to make the database
 current.
  
  I understand the argument proffered is separating
 indexes and data can 
  mean that when physical corruption of the file
 happens to an index 
  tablespace then all one needs do is to offline,
 drop, drop and rebuild  
  the index tablespace.  I admit I have not tried
 off-lining the 
  tablespace first, but you cannot normally drop a
 tablespace which is 
  being used to enforce referential integrity.  If
 off-lining the 
  tablespace first does work, I can see someone
 trying to do the rebuild 
  with the database available and having duplicate
 records in the parent 
  tables and records without parents in the child
 tables.
  
  On the size of the segments:  The paper entitled
 How To Start 
  Defragmenting and Start Living  or something like
 that strongly advocated uniform extent sizes, the
 suggestion sizes were 128K, 4M, 128M, and 4G as I

Re: UNIX : script help/input

#!/usr/bin/perl -w
use strict;
use bytes;
my ($NAME,$IP,@LB);
while () {
chomp;
@LB=split /\s+/;
if ($LB[0] =~ /^name:/i) {
   $NAME=$LB[1];
   }
if ($LB[0] =~ /^address:/i) {
   $IP=$LB[1];
   write;
   }
}
format STDOUT=
@ ,@
$NAME,$IP

Re: locally managed autoallocate (was: Separate Indexes and

Btw, I did some testing on ASSM (9.2.0.4) a while ago and it seems there is
only 2 blocks required for ASSM when talking about small number of 5 block
extents. 2 for ASSM + one for header and rest two get formatted for data
when first row is inserted into table (using conventional mode, when doing
direct insert then only these blocks are formatted which get data written
into them).

I don't understand the reasons why there is a 5 block minimum limit on ASSM
tablespace extent size (with smaller size you get an error message when
creating tablespace as you probably did). I't might have something to do
with level-3 bitmap blocks, but I'm still working on it...

Tanel.

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


 However, I get a different result:

 Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
 PL/SQL Release 9.2.0.1.0 - Production
 CORE9.2.0.1.0   Production
 TNS for Linux: Version 9.2.0.1.0 - Production
 NLSRTL Version 9.2.0.1.0 - Production

 5 rows selected.

 SQL
 SQL CREATE TABLESPACE AUTO16K LOGGING
2  DATAFILE '/u01/ORACLE/ora92/auto16k01.dbf' SIZE 51264K REUSE
3  AUTOEXTEND ON NEXT  20480K MAXSIZE  200M BLOCKSIZE 16384
4  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  MANUAL
5  /

 Tablespace created.

 SQL
 SQL create table a (a number) tablespace auto16k;

 Table created.

 SQL
 SQL select owner,segment_name,extent_id,blocks
2  from dba_extents where tablespace_name = 'AUTO16K';

 OWNERSEGMENT_NAME  EXTENT_ID   blks
   -- --
 SCOTTA 0  4

 1 row selected.

 SQL

 4*16K = 64K initial extent.

 Only when i replicate your example exactly, i.e. with space management
 auto, do I get the same result:

 Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
 PL/SQL Release 9.2.0.1.0 - Production
 CORE9.2.0.1.0   Production
 TNS for Linux: Version 9.2.0.1.0 - Production
 NLSRTL Version 9.2.0.1.0 - Production

 5 rows selected.

 SQL
 SQL CREATE TABLESPACE AUTO16K LOGGING
2  DATAFILE '/u01/ORACLE/ora92/auto16K01.dbf' SIZE 51264K REUSE
3  AUTOEXTEND ON NEXT  20480K MAXSIZE  200M BLOCKSIZE 16384
4  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO
5  /

 Tablespace created.

 SQL
 SQL create table a (a number) tablespace auto16k;

 Table created.

 SQL
 SQL select owner,segment_name,extent_id,blocks
2  from dba_extents where tablespace_name = 'AUTO16K';

 OWNERSEGMENT_NAME
  EXTENT_ID   blks
  
 --
--- 
 -- --
 SCOTTA
  0 64

 1 row selected.

 So the 1M initial extent allocation is not due to a 5 block minimum
 allocation rule but due to the fact that automatic space management
 requires 3 blocks plus 1 block for the segment header plus 1 block for
 actual data = 5 blocks, which lifts the request above the 64K threshold
for
 a tablespace with 16K extents.

 At 05:54 PM 9/30/2003 -0800, you wrote:
 Yes, and there is one thing to add:
 If you do not specify INTIAL, the extent allocation starts with  5 blocks
for
 the intial  extent. For 8k, it's 40k, but  in an autoallocating LMT
extent
 cannot be smaller then 64k, so it is the amount of the space allocated.
The
 interesting question is: what happens with blocksize-16k? Will there be
64k
 or two extents of 64k, i.e. 128k?
 Here is the answer:
 
 SQL create tablespace test1
   2  datafile '/data/oradata/data/test101.dbf' size 64M reuse
   3  autoextend on next 64m maxsize 513M
   4  extent management local autoallocate
   5  segment space management auto
   6  blocksize 16k
   7  /
 
 Tablespace created.
 
 SQL create table a (a number) tablespace test1;
 
 Table created.
 
 SQL select owner,segment_name,extent_id,blocks
   2  from dba_extents
   3  where segment_name='A'and tablespace_name='TEST1'
   4  and owner=user
   5  /
 
 OWNER  SEGMENT_NA  EXTENT_ID BLOCKS
 -- -- -- --
 OPS$MGOGALAA   0 64
 
 16k*64=1M. That means that oracle will allocate a full megabyte for the
 initial extent. It cannot take 64k, because it's smaller then 5*16k
 (that number of 5 blocks is hardwired into the RDBMS since time
immemorial)
 and it cannot take two extents because that would, in turn, mean that the
 initial extent is smaller then 5 blocks. Therefore, it takes 1M. Jonathan
 Lewis was right. Here is one tecnique for optimizing the disk consumption
in
 such cases:
 
 SQL drop tablespace test1 including contents and datafiles;
 
 Tablespace dropped.
 
 SQL
 
 
 
 
 On 2003.09.30 20:34, Jacques Kilchoer wrote:
   Ive read the book. PCTINCREASE is basically set to 100% so
   the extent sizes double. Thats 'basically'

Re: TEMP Tablespace problem

2003-10-01 Thread Teresita Castro




This table do not have any file, how I understood this is the 
table space that the queries uses.
They tell me that if I run a query that need mode that the 
actual space it will be made that the TEMP tablespace grow.
 [EMAIL PROTECTED] 09/30/03 10:09PM 
If the TEMP tablespace is a temporary tablespace, i.e. made of 
temp files rather than datafiles, then you can't offline it. It would have 
to be dropped and rebuilt.At 07:34 PM 9/30/2003 -0800, you 
wrote:Maybe you can create another temp tablespace (called temp_new) on 
anotherdisk, assign all users to temp_new, then offline the old temp 
tablespace,drop the old temp tablespace, and finally remove the old temp 
datafilesfrom OS.Wolfgang BreitlingOracle7, 8, 8i, 9i OCP 
DBACentrex Consulting Corporationhttp://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.comSan Diego, 
California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from). You mayalso send the HELP command for 
other information (like subscribing).

RE: vertical serches on a table - how to

2003-10-01 Thread Susan Tay

Stephane,
Apologize for not being clear on my question.
The query you have provided will only return one record, ie.
1 mango  banana.
I need two records to be returned:
1  mangobanana
1  grape pineapple
You're right that by 'vertical', I meant filtering according to conditions 
on OTHER rows but at the same time, I want those filter conditions to be 
displayed as well, which in this case - grape and pineapple.

Any idea.

Thanks.

susan


From: Stephane Faroult [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: vertical serches on a table - how to
Date: Wed, 01 Oct 2003 06:14:32 -0800
Hi,
I have a table that I would like to perform a
vertical search on. For eg.

Table X
---
ID COL1 COL2
1  apple orange
1  mango   banana
1  grape pineapple
2  mango   banana
2  guava lemon


I would like to display records that meet the
following criteria for *a
particular ID*.

(COL1=banana)
OR
(COL1=mango and COL2=banana
AND
COL1=grape and COL2=pineapple


The output should be
ID   COL1COL2
1mango   banana
1grapepineapple

It should not display
ID   COL1COL2
2mango   banana

since ID=2 did not meet the criteria where
COL1=grape and COL2=pineapple.


I tried the following SQL but the output is always
zero because COL1 can
never be a mango and a grape and COL2 can never be
a
banana and a pineapple at the same time for a
particular ID.

select ID, col1, col2
fromtableX
where   (col1='banana')
or  ((col1='mango' and col2='banana')
  and
 (col1='grape' and col2='pineapple')
 );


Any idea how I can do a vertical search on the
table.

Thanks for any help you can provide.

susan

Susan,

   Took me some time to understand what you meant by 'vertical search'. 
ANDs and ORs in a WHERE clause always apply to the current row under 
scrutiny. What you mean by 'vertical' is that you want to filter according 
to conditions on OTHER rows. This is done by a subquery.

Your query could read

select X1.ID, X1.col1, X1.col2
fromtableX X1
where   (X1.col1='banana')
or  ((X1.col1='mango' and X1.col2='banana')
  and EXISTS (select null
  from tableX X2
  where X2.col1='grape'
and X2.col2='pineapple'
and X2.ID = X1.ID));
Simplifying to the extreme, each different row you handle must be returned 
by its 'own' query.

Regards,

Stephane Faroult
Oriole
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
_
Frustrated with dial-up? Get high-speed for as low as $29.95/month 
(depending on the local service providers in your area).  
https://broadband.msn.com

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

AW: Experiences setting OPEN_CURSORS for Java applications

2003-10-01 Thread Stefan Jahnke

Hi

Just wondering: How did you implement the transparancy aspect ?
Interceptor pattern (as in CORBA) ?
Your tool seems to be a very good thing to use during dev-cycle to log
certain aspects you're interested in (maybe log4j might do the job ?).

Stefan

-Ursprüngliche Nachricht-
Von: Craig Munday [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 1. Oktober 2003 02:44
An: Multiple recipients of list ORACLE-L
Betreff: Re: Experiences setting OPEN_CURSORS for Java applications


Tanel,

I've implemented it as a JDBC driver that is installed as a layer between 
your application and the vendor driver that you are using (eg. Oracle, 
Postgress, SQL Server, etc.)

[Java application] - Layer 1
[JDBC Expert] - Layer 2
[Oracle Thin Driver] - Layer 3
 |
network
 |
[Oracle Server] - Layer 4


It does not parse Java source code and is not a code analyzer, however the 
tool will intercept all calls that an application makes on the JDBC API, 
analyze them and forward them onto the vendor driver.  In this way the tool 
is transparent to the application and can be installed or removed without 
modification to the application code.

I would not call it a traffic analyzer because to me that term implies that 
it sits on a network and analyzes network traffic much like an Intrusion 
Detection System might do.

Regards,
Craig Munday.






At 04:11 AM 30/09/2003 -0800, you wrote:
  I've encountered this problem so often that I decided to write a tool
  (called JDBC Expert) that would help us DBAs (and developers) detect
  Statement and ResultSet leaks in Java applications.   I've found this
  tool so useful and effective at finding resource leaks that I insist any
in
  house developed or third party Java applications are tested with it
before
  we release them.

Just interested, how have you implemented it? Is it a code or traffic
analyzer?

Tanel.


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

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


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

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


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

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

RE: locally managed autoallocate (was: Separate Indexes and Data)

 -Original Message-
 From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, September 30, 2003 7:34 PM
 To: Multiple recipients of list ORACLE-L
 Subject: locally managed autoallocate (was: Separate Indexes and Data)

  Ive read the book. PCTINCREASE is basically set to 100% so 
  the extent sizes double. Thats 'basically' how it works. I 
  have seen some posts on dejanews saying it doesnt necessarily 
  work this way and some people are finding large extent sizes 
  with just a few extents and when tables are dropped this is 
  leading to fragmentation. It hasnt happened to me, but the 
  posts on dejanews were from some pretty good posters. So Im 
  playing conservative. We also had one of the contributors 
  here mention issues. 

 I think Jonathan Lewis has explained the algorithm before, 
 but it's also something that we have investigated here.
 The algorithm (ignoring some details) is:
 There will be 4 extent sizes used, 64K, 1M, 8M, 64M
 As long as object allocation is 1M or less, 64K extent sizes are used,
 When object allocation is between 1M and 64M, 1M extent sizes 
 are used.
 When object allocation is between 64M and 1G, 8M extent sizes 
 are used.
 When object allocation is more than 1G, 64M extent sizes are used.

 However, when you initially create the object, the extents 
 are determined by figuring out the space allocated to the 
 newly created object taking into account the INITIAL, NEXT, 
 PCTINCREASE, MINEXTENTS storage parameters. So the object 
 might start off with 1M extents instead of starting off with 
 64K extents. The algorithm is similar to the one outlined 
 above but it is more complicated. The NEXT and PCTINCREASE 
 seem to be ignored after the object is created.
 e.g.
 create table ... tablespace locally_managed_autoallocate
   storage (initial 1M next 512K minextents 15 pctincrease 0) ... ;
 Initial allocation will be 1M + (15 - 1) * 512K = 8M
 When you create the table, you will see eight extents, each 
 of one megabyte.

 There are additional wrinkles, but I don't think the 
 algorithm has bugs.

 I don't think that there really is fragmentation in the 
 sense that an unused extent will remain unused forever. All 
 extents will be in one of the 4 sizes mentioned above, and 
 all are subject to reuse at some point.

Theoritically, perhaps, but what if an existing table needs to auto-extend
at 1M and all that's left in the table is 16 (or whatever) 64K chunks.  I
still maintain that system-managed tablespaces are barely better than DMTs
-- fragmentation is still potentially a problem and needs to be monitored.

On the flip-side, LMT segments need to be watched too in case they are
growing beyond the design of the TS (e.g. more than 1024 or how ever many
extents).  I'd much rather deal with the latter because it's much less
likely to happen unexpectedly in our environment.

Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
-- 
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: TEMP Tablespace problem

2003-10-01 Thread Yechiel Adar




If you can stop the users then simply drop the 
tablespace and remove the datafile, then crate new smaller temp.
If you can not stop the users do:
1) Create newsmall temp;
2) Alter all users to use the new 
temp.
3) Drop temp , after you are sure that none of the 
users is using it.

Yechiel AdarMehish

  - Original Message - 
  From: 
  Teresita Castro 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Wednesday, October 01, 2003 4:19 
  PM
  Subject: Re: TEMP Tablespace 
problem
  9.2i [EMAIL PROTECTED] 09/30/03 
  08:54PM What is the Oracle version?At 06:24 PM 
  9/30/2003 -0800, you wrote:Hi!!I am trying to change the size 
  of my tablespace TEMP, I am not an Administrator but we really need to 
  make this tablespace smaller.Already the size is 13214 Mgs, and this 
  tablaspace is on a disk that is full, so if we can not make it smaller 
  we are going to be in a serius trouble ( our Administrator is not here 
  until Monday).Wolfgang BreitlingCentrex Consulting 
  Corporationhttp://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.comSan Diego, 
  California -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from). You mayalso send the HELP command 
  for other information (like subscribing).

PHP cookbook from Oracle

I received an e-mail from Oracle saying that I won a PHP cookbook from
Oracle (http://otn.oracle.com/products/jdev/temp/rules.htm)  Anyone here get
my PS2?

Now maybe I'll be able to get PhpWiki working against Oracle on Alpha
Linux...  :)

Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
-- 
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: 8i OCP Net8 Exam

2003-10-01 Thread DENNIS WILLIAMS

Faan
   Thanks. Yes I totally agree that the practice exams are very helpful. In
my case I chose Couchman's book of practice exams. Usually my exam score has
been higher than my practice exam scores. My current study method is to
record quiz questions on an audio CD so I can study during the time I'm
trapped in an automobile each day. After the exam, I have also pulled out
these CDs and used them to refresh my knowledge of a subject. It is probably
just my age, but I have trouble remembering everything people expect a DBA
to know immediately, unlike some people on the list. ;-)

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


-Original Message-
Sent: Tuesday, September 30, 2003 5:40 PM
To: Multiple recipients of list ORACLE-L


Dennis,

Do you have good practice exams?

I have found that the best preparation for the OCP exams are good practice
exams.

I have reached the point where I just skim through the material in the
whatever book you use (probably only possible if you have some miles on
the clock as a DBA) and then drive the in depth study from the practice
exams.  This way you are spending your time more focused and find out what
exactly the OCP exam will expect from you vs. what some author would like to
teach you or even what the correct answer is in reality.

Also, many of these questions in the practice exams will appear in the
actual OCP exam which builds your confidence while writing the actual exam.

You will also find that there are several inaccuracies in the OCP exam that
is directly inherited from the incorrect Oracle Education materials.  If you
have a good book then the author will appropriately point this out like Pete
Sharman's Oracle 8i DBA Exam Cram book.  Pete is an expert in this field and
I personally wish that he would bring out his own Sherman guides for all
the OCP exams as Pete has taught as an Oracle Education Instructor for many
years.  He is also a very experienced DBA that can relate what is correct in
the real world to what the OCP exam expects from you, which is the key to
passing the OCP exam.

Good Luck!
Faan

PS: There are no questions on the IA in the Net8 Exam for 8i

-Original Message-
Sent: Tuesday, September 30, 2003 2:50 PM
To: Multiple recipients of list ORACLE-L


Thanks everyone for your input on this topic. Now if I can just get
motivated. ;-)

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


-Original Message-
Sent: Tuesday, September 30, 2003 3:40 PM
To: Multiple recipients of list ORACLE-L


I also don't recall it being mentioned. Think
naming,cman,mts,dispatchers etc. 

Niall 

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On 
 Behalf Of DENNIS WILLIAMS
 Sent: 29 September 2003 22:35
 To: Multiple recipients of list ORACLE-L
 Subject: 8i OCP Net8 Exam
 
 
 Can anyone recall whether the Oracle Intelligent Agent 
 figured on the Oracle8i OCP Network Administration exam? 
 Couchman's practice exams have quite a few questions on 
 Intelligent Agent, but when I check the official Test Content 
 Checklist on Oracle's Education website, it isn't directly 
 mentioned. Being the lazy slob I am, wouldn't want to study extra.
 
 Dennis Williams
 DBA, 80%OCP, 100% DBA
 Lifetouch, Inc.
 [EMAIL PROTECTED] 
 -- 
 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: 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: 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

Re: STAT from trace

Hi!

This is the problem, that everything else was identical. If you executed
exactly the same query again, it didn't get hard parsed anymore, thus no
STAT lines were generated. Either flush shared pool or just add some bogus
comment using /* */ into your query to get parsing and STAT lines.

Tanel.
- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 4:34 PM


 I was creating some trace files yesterday and came across one of these
 problems that shows up occasionally (then I forget about it).

 When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus;
 8.1.7.4), I got the STAT line in the trace and the associated 'row source'
 information after running tkprof.

 When I ran my query using ALTER SESSION SET EVENTS '10046 trace name
context
 forever, level 8' (or with dbms_system.set_ev), there were no STAT entries
 for my query. There were some for some of the recursive queries.

 Everything else was identical.

 What's up?

 Henry

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

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



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

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

Re: 8i OCP Net8 Exam

2003-10-01 Thread Richard Foote

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


 Dennis,

 Do you have good practice exams?

 I have found that the best preparation for the OCP exams are good practice
exams.

 I have reached the point where I just skim through the material in the
whatever book you use (probably only possible if you have some miles on
the clock as a DBA) and then drive the in depth study from the practice
exams.  This way you are spending your time more focused and find out what
exactly the OCP exam will expect from you vs. what some author would like to
teach you or even what the correct answer is in reality.

 Also, many of these questions in the practice exams will appear in the
actual OCP exam which builds your confidence while writing the actual exam.


It's comments such as these which are unfortunately all so common that
really hits home what an laughable, sad and sorry affair the whole OCP
program really is. I have this vision of people poring over example
questions, desperately trying to memorise as many questions as possible,
desperately trying to forget what is correct is reality for fear of not
getting the required 65% multiple questions correct. Occasionally, they'll
glance at the Inside OCP section of the Oracle Magazine and gain
confidence in getting the jest of the complex concepts (and yes, further
sample questions) it covers in each edition.

And once they've passed and got that precious certificate, they're of course
qualified to look after that banks enterprise database because they're
Oracle approved Oracle Certified *Professionals*. And when the database runs
like a dog, they'll open up their notebooks and decide is it:

A) The Buffer Cache Hit Ratio is less than 90%
B) The Library Cache Hit Ratio is less than 90%
C) The DD Cache Hit Ratio is less than 90%
D) The cleaning lady has accidentally pulled out the wrong plug
E) Something else

Good grief !!

Now I too have spent many years teaching with Oracle Education and I'm
Oracle 7, 8, 8i and 9i OCP (instructors were obliged to get certified) so
I know a fair bit about the process. And I've seen students leave my
classroom with 5 days Oracle experience behind them pass their OCP DBA Admin
exam the following Tuesday (guess I was a good teacher :)

Anyone see a problem ?

At the time I kinda justified it as selling them water in that it doesn't
really harm them, achieves nothing but at least they think it's doing them
some good. Don't get me wrong, the training they received was excellent,
it's the OCP bit that is fluff. But really, at the end of the day, having
such an atrocious so called professional program ends up hurting the
individual as they've paid a lot of money (for the exams) for very little
benefit, it hurts organisations in that there's no *guarantee* of hiring
anything closely resembling an Oracle Professional as the bar is so low it
drags along the ground and really it ends up hurting Oracle Corp. as well.

The *only* thing it does have going for it is that it motivates some people
to getting training and investigating parts of Oracle they may otherwise not
have much to do with.

But I've always thought giving away free David Bowie Cds at training courses
was a better way to go :)

Richard






-- 
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: Table not reusing deleted space

2003-10-01 Thread Daniel Fink

Leng,

I recall a similar scenario some months ago. It had to do with the average row size 
(quite large) and the block size. The average row size was just under 1/2 of the block 
size, so the chances of a new row finding a spot in an existing block was slim. Add in 
that there is a limit (5 I think) of blocks on the freelist that a transaction will 
attempt to allocate space in before it says I can't find a block with enough free 
space so I'm going to
allocate a new extent.. I think we looked at dba_tables.avg_row_len and 
dba_tables.avg_space_freelist_blocks. (?) A quick calculation (1048576k /20) 
indicates that your average row length is over 5k. If you have 8k blocks, this means 
an average of 1 row per block (perhaps less depending on the variance in row length).

Daniel Fink

Kaing, Leng wrote:

 Hello everyone,

 Env: 8.1.7.4, SunOs 5.8 64 Bit

 We seem to hitting bug 1262161. The bug seems to imply that tables with triggers 
 behind them do not reuse blocks on the freelist. We have a table that should only 
 use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It 
 is subject to high inserts, deletes and updates. But the resultant number of rows is 
 around 200K rows. The insert is just a normal insert, no APPEND hint is used. 
 Updates do not really expand the rows.

 We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse 
 the deleted space.

 In trying to prove this error in our environment I've created 5 test scenarios but 
 was never able to reproduce the problem. It only exists on our production database. 
 I'm stumped. Has anyone encountered this problem?

 Or can someone explain to me why our production database is not reusing the space 
 deleted and placed back on the free list? I should also add that the table in 
 question is a master table of a snapshot.

 TIA,

 Leng.

 --
 Leng Kaing
 Email: [EMAIL PROTECTED]
 Phone: +61-3-9203-7589
 Mobile: +61-417-371-348
begin:vcard 
n:Fink;Daniel
x-mozilla-html:FALSE
org:Sun Microsystems, Inc.
adr:;;
version:2.1
title:Lead, Database Services
x-mozilla-cpt:;9168
fn:Daniel  W. Fink
end:vcard

Re: Create Table..As Select: Number formats

Hi!

 I'm trying to create a table using 'Create Table...As Select...'

 The contents will then be exchanged into a partitioned table, so they need
 to have the same names and datatypes.

Actually they do not have to have same column names, only the datatypes and
column order has to be the same (at least in 9.2).


 Some of the columns in the created table are populated with zeroes  will
 be updated after the partition exchange, one of the columns is populated
 with a decode that returns a single digit number.

 The problem is that I need particular number formats in these columns -
 the one-digit column should be a number(1,0) and the others should be
 number(12,4). I can't specify column types in create table...as select, so
 how else can I force the columns to a particular format?

I tried to use CAST function, it didn't give an error, but column remained
just normal number, so it didn't help.

I suggest you to create the table structure first and then use insert APPEND
to populate the table (you can also use nologging if you like).

Tanel.




 Any ideas much appreciated.

 Cheers
 Simon Anderson
 -- 
 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: Tanel Poder
  INET: [EMAIL PROTECTED]

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

RE: Create Table..As Select: Number formats

2003-10-01 Thread Simon . Anderson

I'm trying to create a table using 'Create
Table...As Select...' 
...

   I don't think that there is any problem here. Specifying the number of 
digits is largely cosmetic - consider it asa default mask. It doesn't 
affect how data is stored inside the tables AFAIK.

 Regards,
 
 Stephane Faroult
 Oriole

The problem is when I try to exchange the newly created table into the 
partitioned table - the designers (in their infinite wisdom) have 
specified number formats for that table. 

alter table daily_total exchange partition jun_02 with table dt_temp 
including indexes

gives me the error:

*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Unless I get the column types to match exectly - I can't ask for a change 
in table design to remove the awkward formatting until I've at least tried 
to get the format to work in the 'Create Table...As Select..'

Cheers
Simon Anderson

-- 
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: TEMP Tablespace problem




Where did you look for this file? Use v$tempfile or 
dba_temp_files.

Anyway, your case is a good reason why not to 
enable autoextend in temp and rbs tablespaces without extra care.

If you got DBA access to your database, you 
could:
1) create temporary tablespace 
new_temp
2) alter database default temporary tablespace 
new_temp
3) drop tablespace temp including contents and 
datafiles (might take time)
4) create temporary tablespace temp 
...
5) alter database default temporary tablespace 
temp
6) drop tablespace new_temp including contents and 
datafiles

If you don't have DBA access nor OS access, then 
you have to wait until your administrator comes back or hack yourself into 
serverroom to get physical access to your server.

Tanel.


  - Original Message - 
  From: 
  Teresita Castro 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Wednesday, October 01, 2003 5:39 
  PM
  Subject: Re: TEMP Tablespace 
problem
  
  This table do not have any file, how I understood this is 
  the table space that the queries uses.
  They tell me that if I run a query that need mode that the 
  actual space it will be made that the TEMP tablespace grow.
   [EMAIL PROTECTED] 09/30/03 
  10:09PM If the TEMP tablespace is a temporary tablespace, i.e. 
  made of temp files rather than datafiles, then you can't offline it. It 
  would have to be dropped and rebuilt.At 07:34 PM 9/30/2003 -0800, 
  you wrote:Maybe you can create another temp tablespace (called 
  temp_new) on anotherdisk, assign all users to temp_new, then offline 
  the old temp tablespace,drop the old temp tablespace, and finally 
  remove the old temp datafilesfrom OS.Wolfgang 
  BreitlingOracle7, 8, 8i, 9i OCP DBACentrex Consulting 
  Corporationhttp://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.comSan Diego, 
  California -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from). You mayalso send the HELP command 
  for other information (like subscribing).

Re: exam

Good for you! :)

Cheers,
Tanel.

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 5:24 PM


 List , thanks to you all I passed my oracle 9i performance tuning exam
today
 with good marks.
 Thank you
 

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

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



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

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

RE: exam

2003-10-01 Thread Naveen Nahata

Congrats!

How did you find the exam, Easy? Tough? Ambigous?

Regards
Naveen

-Original Message-
From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 7:55 PM
To: Multiple recipients of list ORACLE-L
Subject: exam


List , thanks to you all I passed my oracle 9i performance 
tuning exam today
with good marks.
Thank you


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



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

Re: TEMP Tablespace problem

2003-10-01 Thread Teresita Castro




I have Oracle 9.2i 
I already run the query and the tablespace TEMP have 
CONTENTS=TEMPORARY AND EXTENT_MAN=LOCAL
Can I run this line to fix the size of my 
tablespace?

SQL alter database tempfile 
'/data/oradata/system/temp01.dbf' resize 128M;

I mean is the same or I have to change 
something?
 [EMAIL PROTECTED] 09/30/03 10:04PM 
Teresita, spelling the word "administrator" with the capital 
letteris a good thing. Furthermore, if you have version 9i, you can do 
thefollowing, little known, thing:SQL alter database tempfile 
'/data/oradata/system/temp01.dbf' resize 128M;Database 
altered.If it's Oracle 8i, you can actually have a permanent, 
dictionary based tablespace acting as a temporary tablespace. In 
version 9, however, youcan't. Here is the query to find out what do you 
have: 1 select 
tablespace_name,contents,extent_management 2 from 
dba_tablespaces 3* order by contentsSQL 
/TABLESPACE_NAME 
CONTENTS EXTENT_MAN-- - 
--SYSTEM 
PERMANENT 
LOCALDRSYS 
PERMANENT 
LOCALEXAMPLE 
PERMANENT 
LOCALINDX 
PERMANENT 
LOCALUSERS 
PERMANENT 
LOCALXDB 
PERMANENT 
LOCALTOOLS 
PERMANENT 
LOCALTEMP 
TEMPORARY 
LOCALUNDOTBS1 
UNDO LOCAL9 rows 
selected.SQLIf the column contents reads "TEMPORARY" for the 
given tablespace, you candrop it and recreate it without any harm. Note that 
in 8i you don't have "UNDO" tablespaces and your system tablespace 
cannot be LMT.On 2003.09.30 22:24, Teresita Castro wrote: 
Hi!! I am trying to change the size of my tablespace TEMP, I am not 
an Administrator but we really need to make this tablespace 
smaller. Already the size is 13214 Mgs, and this tablaspace is on a disk 
that is  full, so if we can not make it smaller we are 
going to be in a serius trouble  ( our Administrator is 
not here until Monday).-- Mladen GogalaOracle DBA-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mladen 
Gogala INET: [EMAIL PROTECTED]Fat City Network 
Services -- 858-538-5051 http://www.fatcity.comSan Diego, 
California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from). You mayalso send the HELP command for 
other information (like subscribing).

RE: Off Topic: PC Firewall Recommendation

2003-10-01 Thread Gene Sais



I second that 
https://grc.com website. It is a great 
resource for testing your vulnerabilities! 
[EMAIL PROTECTED] 09/30/03 06:54PM Whatever you use go to https://grc.com/x/ne.dll?bh0bkyd2 
and http://grc.com/lt/leaktest.htm 
for testing your firewall product and make sure that the basic stuff is 
configured correctly.Also want to check out the URL http://grc.com/lt/scoreboard.htm 
about various PC firewall products "leaking" and possible issues which certain 
versions.-f-Original Message-Sent: Tuesday, 
September 30, 2003 3:10 PMTo: Multiple recipients of list 
ORACLE-L-- snip If you're feeling frisky, 
consider replacing the router/firewall with a PC(with 2 nics) running BSD or 
Linux. You can also find distros tweaked toact as a firewall/router - 
that's what I've done.-- snip The Linux distro I 
used was www.smoothwall.com (which is 
the similar towww.ipcop.com ) and I 
found that it worked very well. even on a sub 100MHzPC with 16MB RAM. Of 
course I had to load a customized module for IPSecbypass to allow me to 
connect to work using a Cisco VPN client.-Original 
Message-Sent: Monday, September 29, 2003 1:05 PMTo: Multiple 
recipients of list ORACLE-LKENNETH JANUSZ [mailto:[EMAIL PROTECTED] on Monday, September 
29, 2003 9:05AM said; I have a Dell 8200 with XP Prof. 
SP1. I would like recommendations as to a good firewall for this 
machine. XPhas a firewall but it is not the greatest.ZoneAlarm 
on the desktop - free version or pay to upgrade to the 
proversion.Assuming you have a home network, you also want to buy a 
DSL/Cable router -which has it's own firewall built in.If you're 
feeling frisky, consider replacing the router/firewall with a PC(with 2 
nics) running BSD or Linux. You can also find distros tweaked toact as 
a firewall/router - that's what I've done.FWIW, a friend of mine had his 
XP system plugged directly into his RRconnection. Friend said he 
didn't need a firewall or router (I'm not intothat security crap, I just 
want to play games). Friend has now had toreformat his box (and lost 
work) because his box was rooted, blasted andfubared within days of hooking 
it to the cable connection w/out a firewall.YMMV.~brian-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Brian 
Dunbar INET: [EMAIL PROTECTED]Fat City Network 
Services -- 858-538-5051 http://www.fatcity.comSan Diego, 
California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from). You mayalso send the HELP command for 
other information (like subscribing).-- Please see the official ORACLE-L 
FAQ: http://www.orafaq.net-- 
Author: Suri, Deepak INET: [EMAIL PROTECTED]Fat City Network 
Services -- 858-538-5051 http://www.fatcity.comSan Diego, 
California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from). You mayalso send the HELP command for 
other information (like subscribing).-- Please see the official 
ORACLE-L FAQ: http://www.orafaq.net-- 
Author: Faan DeSwardt INET: [EMAIL PROTECTED]Fat City 
Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, 
California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from). You mayalso send the HELP command for 
other information (like subscribing).

RE: locally managed autoallocate (was: Separate Indexes and Data)

 -Original Message-
 From: Jesse, Rich 
 Sent: Wednesday, October 01, 2003 9:49 AM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: locally managed autoallocate (was: Separate Indexes and
 Data)

 Theoritically, perhaps, but what if an existing table needs 
 to auto-extend
 at 1M and all that's left in the table is 16 (or whatever) 

(blush)  Obviously, that's supposed to say left in the tableSPACE.

 64K chunks.  I
 still maintain that system-managed tablespaces are barely 
 better than DMTs
 -- fragmentation is still potentially a problem and needs to 
 be monitored.

 On the flip-side, LMT segments need to be watched too in case they are
 growing beyond the design of the TS (e.g. more than 1024 or 
 how ever many
 extents).  I'd much rather deal with the latter because it's much less
 likely to happen unexpectedly in our environment.

 Rich

Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
-- 
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: exam

Title: RE: exam





Congratulations ... so what was your Hit ratio ... 


Raj
-Original Message-
From: [EMAIL PROTECTED] 
Subject: exam


List , thanks to you all I passed my oracle 9i performance tuning exam today with good marks.
Thank you



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: Off Topic: PC Firewall Recommendation

I don't need to test my vulnerabilities. I know my vulnerabilities are
working well.

On Wed, 2003-10-01 at 11:09, Gene Sais wrote:
 I second that https://grc.com website.  It is a great resource for
 testing your vulnerabilities!
 
  [EMAIL PROTECTED] 09/30/03 06:54PM 
 Whatever you use go to https://grc.com/x/ne.dll?bh0bkyd2 and
 http://grc.com/lt/leaktest.htm for testing your firewall product and
 make sure that the basic stuff is configured correctly.
 
 Also want to check out the URL http://grc.com/lt/scoreboard.htm about
 various PC firewall products leaking and possible issues which
 certain versions.
 
 -f
 
 -Original Message-
 Sent: Tuesday, September 30, 2003 3:10 PM
 To: Multiple recipients of list ORACLE-L
 
 
 -- snip 
 
 If you're feeling frisky, consider replacing the router/firewall with
 a PC
 (with 2 nics) running BSD or Linux.  You can also find distros tweaked
 to
 act as a firewall/router - that's what I've done.
 
 -- snip 
 
 The Linux distro I used was www.smoothwall.com (which is the similar
 to
 www.ipcop.com ) and I found that it worked very well. even on a sub
 100MHz
 PC with 16MB RAM. Of course I had to load a customized module for
 IPSec
 bypass to allow me to connect to work using a Cisco VPN client.
 
 
 
 -Original Message-
 Sent: Monday, September 29, 2003 1:05 PM
 To: Multiple recipients of list ORACLE-L
 
 
 KENNETH JANUSZ [mailto:[EMAIL PROTECTED] on Monday, September 29, 2003
 9:05
 AM said;
 
 
  I have a Dell 8200 with XP Prof. SP1.
 
  I would like recommendations as to a good firewall for this
 machine.  XP
 has a firewall but it is not the greatest.
 
 ZoneAlarm on the desktop - free version or pay to upgrade to the pro
 version.
 Assuming you have a home network, you also want to buy a DSL/Cable
 router -
 which has it's own firewall built in.
 
 If you're feeling frisky, consider replacing the router/firewall with
 a PC
 (with 2 nics) running BSD or Linux.  You can also find distros tweaked
 to
 act as a firewall/router - that's what I've done.
 
 FWIW, a friend of mine had his XP system plugged directly into his RR
 connection.  Friend said he didn't need a firewall or router (I'm not
 into
 that security crap, I just want to play games).  Friend has now had to
 reformat his box (and lost work) because his box was rooted, blasted
 and
 fubared within days of hooking it to the cable connection w/out a
 firewall.
 YMMV.
 
 ~brian
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Brian Dunbar
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Suri, Deepak
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (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: Faan DeSwardt
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).




Note:
This message is for the named person's use only.  It may contain confidential, 
proprietary or legally privileged information.  No confidentiality or privilege is 
waived or lost by any mistransmission.  If you receive this message in error, please 
immediately delete it and all copies of it from your system, destroy any hard copies 
of it and notify the sender.  You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. Wang Trading LLC

Re: TEMP Tablespace problem

Yes you can.
On Wed, 2003-10-01 at 11:29, Teresita Castro wrote:
 I have Oracle 9.2i 
 I already run the query and the tablespace TEMP have
 CONTENTS=TEMPORARY AND EXTENT_MAN=LOCAL
 Can I run this line to fix the size of my tablespace?
  
 SQL alter database tempfile '/data/oradata/system/temp01.dbf' resize
 128M;
  
 I mean is the same or I have to change something?
 
  [EMAIL PROTECTED] 09/30/03 10:04PM 
 Teresita, spelling the word administrator with the capital letter
 is a good  thing. Furthermore, if you have version 9i, you can do the
 following, little known, thing:
 
 SQL alter database tempfile '/data/oradata/system/temp01.dbf' resize
 128M;
 
 Database altered.
 
 
 If it's Oracle 8i, you can actually have a permanent, dictionary
 based  
 tablespace acting as a temporary tablespace. In version 9, however,
 you
 can't. Here is the query to find out what do you have:
   1  select tablespace_name,contents,extent_management
   2  from dba_tablespaces
   3* order by contents
 SQL /
 
 TABLESPACE_NAMECONTENTS  EXTENT_MAN
 -- - --
 SYSTEM PERMANENT LOCAL
 DRSYS  PERMANENT LOCAL
 EXAMPLEPERMANENT LOCAL
 INDX   PERMANENT LOCAL
 USERS  PERMANENT LOCAL
 XDBPERMANENT LOCAL
 TOOLS  PERMANENT LOCAL
 TEMP   TEMPORARY LOCAL
 UNDOTBS1   UNDO  LOCAL
 
 9 rows selected.
 
 SQL
 
 If the column contents reads TEMPORARY for the given tablespace, you
 can
 drop it and recreate it without any harm. Note that in 8i you don't
 have  
 UNDO tablespaces and your system tablespace cannot be LMT.
 
 
 On 2003.09.30 22:24, Teresita Castro wrote:
  Hi!!
  I am trying to change the size of my tablespace TEMP, I am not an
  Administrator but we really need to make this tablespace smaller.
  Already the size is 13214 Mgs, and this tablaspace is on a disk that
 is  
  full,
  so if we can not make it smaller we are going to be in a serius
 trouble  
  ( our
  Administrator is not here until Monday).
 
 
 -- 
 Mladen Gogala
 Oracle DBA
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Mladen Gogala
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 




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

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

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

Re: Huge optimization costs with 9.2

Kirti,

I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
performance is good. After upgrade, one query run time from 2 min to 12
hours. Of course, I re-analyzed all tables and indexes. The explain plan
changed from hash join to nested-loop. All the parameters are same. So I
have to put optimized_feature_enable=8.1.7 to make run normal as usual.
I hate to disable the new feature, but no choose.

Joan

Kirtikumar Deshpande wrote:
 
 Were tables/indexes anlayzed after the upgrade?
 
 - Kirti
 
 --- Jeff Landers [EMAIL PROTECTED] wrote:
  Hello All
 
  Version  OS:
  Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3.
 
  Problem:
  We've captured the sql text and optimization plans for critical sql
  prior to upgrading to 9.2.   After  the upgrade we have noticed
  that the cost associated with every sql statement  is now HUGE
  compared to its 9.0.1.4 counterpart.   Per the statistics being captured
  via traces,
  these statement are noticeably slower per execution.
 
  Anyone experiencing/experienced the same problem with 9.2?
 
  Thank you in advance.
 
 
 
 __
 Do you Yahoo!?
 Yahoo! SiteBuilder - Free, easy-to-use web site design software
 http://sitebuilder.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joan Hsieh
  INET: [EMAIL PROTECTED]

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

RE: STAT from trace

2003-10-01 Thread Henry Poras

Title: RE: STAT from trace



Thanks. I tried both disabling the trace and quitting from the session. 
No luck with 10046, just sql_trace.

Henry

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of Jamadagni, 
  RajendraSent: Wednesday, October 01, 2003 10:25 AMTo: 
  Multiple recipients of list ORACLE-LSubject: RE: STAT from 
  trace
  I get STAT lines no matter how I enable the trace. Make sure 
  you wither close the session or stop the trace so that all pending STAT lines 
  will be written to the file.
  Raj 
  -Original Message- From: Henry 
  Poras [mailto:[EMAIL PROTECTED]] 
  Sent: Wednesday, October 01, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Subject: STAT from trace 
  I was creating some trace files yesterday and came across one 
  of these problems that shows up occasionally (then I 
  forget about it). 
  When I ran my query using ALTER SESSION SET sql_trace=true 
  (sqlplus; 8.1.7.4), I got the STAT line in the trace 
  and the associated 'row source' information after 
  running tkprof. 
  When I ran my query using ALTER SESSION SET EVENTS '10046 
  trace name context forever, level 8' (or with 
  dbms_system.set_ev), there were no STAT entries for my 
  query. There were some for some of the recursive queries. 
  Everything else was identical. 
  What's up? 
  Henry

RE: 8i OCP Net8 Exam

2003-10-01 Thread DENNIS WILLIAMS

Richard - My apologies that concern for passing the exam has caused some of
us to exchange tips that you find offensive. And I truly admire those who
have been able to just walk in the exams and pass. And I had similar gripes
against the exams until I was felt the need to pass the exams. I understand
that concerns like yours have caused Oracle to create the OCM. Have you
taken a look at that?

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


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


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


 Dennis,

 Do you have good practice exams?

 I have found that the best preparation for the OCP exams are good practice
exams.

 I have reached the point where I just skim through the material in the
whatever book you use (probably only possible if you have some miles on
the clock as a DBA) and then drive the in depth study from the practice
exams.  This way you are spending your time more focused and find out what
exactly the OCP exam will expect from you vs. what some author would like to
teach you or even what the correct answer is in reality.

 Also, many of these questions in the practice exams will appear in the
actual OCP exam which builds your confidence while writing the actual exam.


It's comments such as these which are unfortunately all so common that
really hits home what an laughable, sad and sorry affair the whole OCP
program really is. I have this vision of people poring over example
questions, desperately trying to memorise as many questions as possible,
desperately trying to forget what is correct is reality for fear of not
getting the required 65% multiple questions correct. Occasionally, they'll
glance at the Inside OCP section of the Oracle Magazine and gain
confidence in getting the jest of the complex concepts (and yes, further
sample questions) it covers in each edition.

And once they've passed and got that precious certificate, they're of course
qualified to look after that banks enterprise database because they're
Oracle approved Oracle Certified *Professionals*. And when the database runs
like a dog, they'll open up their notebooks and decide is it:

A) The Buffer Cache Hit Ratio is less than 90%
B) The Library Cache Hit Ratio is less than 90%
C) The DD Cache Hit Ratio is less than 90%
D) The cleaning lady has accidentally pulled out the wrong plug
E) Something else

Good grief !!

Now I too have spent many years teaching with Oracle Education and I'm
Oracle 7, 8, 8i and 9i OCP (instructors were obliged to get certified) so
I know a fair bit about the process. And I've seen students leave my
classroom with 5 days Oracle experience behind them pass their OCP DBA Admin
exam the following Tuesday (guess I was a good teacher :)

Anyone see a problem ?

At the time I kinda justified it as selling them water in that it doesn't
really harm them, achieves nothing but at least they think it's doing them
some good. Don't get me wrong, the training they received was excellent,
it's the OCP bit that is fluff. But really, at the end of the day, having
such an atrocious so called professional program ends up hurting the
individual as they've paid a lot of money (for the exams) for very little
benefit, it hurts organisations in that there's no *guarantee* of hiring
anything closely resembling an Oracle Professional as the bar is so low it
drags along the ground and really it ends up hurting Oracle Corp. as well.

The *only* thing it does have going for it is that it motivates some people
to getting training and investigating parts of Oracle they may otherwise not
have much to do with.

But I've always thought giving away free David Bowie Cds at training courses
was a better way to go :)

Richard






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

RE: exam

2003-10-01 Thread DENNIS WILLIAMS

 Congratulations. Do you feel you learned anything from this exam?



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

 
-Original Message- 

List , thanks to you all I passed my oracle 9i performance tuning exam today
with good marks. 
Thank you 

-- 
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: Oracle db using IBM FAStT disk storage questions

Hi Ron,

We tested out a FAStT-900 a few months ago.  I was happy with the
performance results, although we were testing it to replace an HP AutoRAID
12H, so I'd imagine *any* other storage solution would have been better.  :)

Unfortunately, our test 900 wasn't able to be hooked up to an HP PA-RISC
server like we have for production because of questions about a viable HBA
for HP's K-series servers.  So, our test box was a dual 2.4Ghz P-IV running
(ugh) WinTuke with 1GB RAM.  If you want a Windoze server to connect to the
FAStT, be VERY certain of your logical layout BEFORE making it production,
as it seemed even minor changes required at least one reboot for Windohs to
recognize the new layout.

Without going into detail, I found that I could expect about 4500 PIO/s
aggregate thruput with a negated FAStT cache.  At this point, the server's
CPUs were about 40-50% loaded.  While PIOs peaked at about 2/s, and were
able to ride at about 6000/s for a while, when I imposed a different query
in an attempt to flush the FAStT cache the PIO/s dropped to about 4500/s
sustained.  Additional queries did not affect the aggregate IO rate up to
the point where server CPU became a bottleneck.  I attributed the peaks and
higher sustained rates on fewer queries to the FAStT's caching.  It seems
reasonable and I didn't have time to prove it (we only had the box for a
week).

That's the high-level view.  HTH!  GL!

Rich

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


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


Is anyone using Oracle database with IBM disk storage FAStT? 
I was wondering if you had any performance problems, pitfalls and any- 
other stories that you might want to inform me about?  Before we commit 
ourselves in purchasing this SAN your info would be appreciated.
 
We are a PeopleSoft shop running their Higher Education products 8.
Running on RS6000 H-80 with AIX 4.3.3, soon going to 5.2. Oracle version
9.0.2.3.
 
You can email me (address below) directly or to the list.
 
Thanks
 
Ron
-- 
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: Off Topic: PC Firewall Recommendation

2003-10-01 Thread Nuno Souto

- Original Message - 

 I don't need to test my vulnerabilities. I know my vulnerabilities are
 working well.

Hehehe!  As we'd say in my local newsgroup:
Check yuor settings!

  I second that https://grc.com website.  It is a great resource for
  testing your vulnerabilities!

Too true.
But the best solution by far if you have a high speed cable or ADSL 
connection is to get hold of one of those Netgear or Dlink firewall/router 
boxes.  Not only do you get peace of mind that works with ANY box connected
to it (be it Windoze or Lunix), but you also get a hub and shared connect 
thrown into the bargain.

And it survives the grc.com test.  Add an anonymous proxie for your browsing 
and you're as safe as you can get.  Except for e-maul.

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

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

2003-10-01 Thread Bob Metelsky

How about

for /L %i IN (1,1,250) do start /min sqlplus user/[EMAIL PROTECTED]
@script.sql

Id try it with less than 250 as it can kill the machine opening 250
windows!
;-)

to run form a batch file you need to %%i the variable
hth
bob


If on windows, type
   start /min sqlplus user/[EMAIL PROTECTED] @script.sql
..255 times on your windows command prompt.

Tanel.


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, September 25, 2003 7:54 PM


I want to run some sql scripts and i need to open 255 session at
same
time and
these session will run somw sql scripts.

HOW CAN I OPEN  255 session at the same time ?
may be a tool , may be shell script.

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

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



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

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

Joan, what is the difference in the plans? What specific feature
made the difference? Are the values of
optimizer_index_cost_adj and optimizer_index_caching same on both
versions? How about histograms? What is with
db_file_multiblock_read_count,sort_area_size and hash_area_size? Is
everything same as in 8i? May be setting of those parameters can be
tweaked to your benefit?

On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote:
 Kirti,
 
 I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
 performance is good. After upgrade, one query run time from 2 min to 12
 hours. Of course, I re-analyzed all tables and indexes. The explain plan
 changed from hash join to nested-loop. All the parameters are same. So I
 have to put optimized_feature_enable=8.1.7 to make run normal as usual.
 I hate to disable the new feature, but no choose.
 
 Joan
 
 Kirtikumar Deshpande wrote:
  
  Were tables/indexes anlayzed after the upgrade?
  
  - Kirti
  
  --- Jeff Landers [EMAIL PROTECTED] wrote:
   Hello All
  
   Version  OS:
   Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3.
  
   Problem:
   We've captured the sql text and optimization plans for critical sql
   prior to upgrading to 9.2.   After  the upgrade we have noticed
   that the cost associated with every sql statement  is now HUGE
   compared to its 9.0.1.4 counterpart.   Per the statistics being captured
   via traces,
   these statement are noticeably slower per execution.
  
   Anyone experiencing/experienced the same problem with 9.2?
  
   Thank you in advance.
  
  
  
  __
  Do you Yahoo!?
  Yahoo! SiteBuilder - Free, easy-to-use web site design software
  http://sitebuilder.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).
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net




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

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

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

RE: RE: Create Table..As Select: Number formats

:31

I'm trying to create a table using 'Create
Table...As Select...' 
...

   I don't think that there is any problem here.
Specifying the number of 
digits is largely cosmetic - consider it asa
default mask. It doesn't 
affect how data is stored inside the tables AFAIK.

 Regards,
 
 Stephane Faroult
 Oriole

The problem is when I try to exchange the newly
created table into the 
partitioned table - the designers (in their
infinite wisdom) have 
specified number formats for that table. 

alter table daily_total exchange partition jun_02
with table dt_temp 
including indexes

gives me the error:

*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER
TABLE EXCHANGE PARTITION

Unless I get the column types to match exectly - I
can't ask for a change 
in table design to remove the awkward formatting
until I've at least tried 
to get the format to work in the 'Create Table...As
Select..'

Cheers
Simon Anderson


Simon,

  Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when 
you have a NULL in a UNION, which must be explicitly cast with a to_number(), 
to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT 
... ?

Regards,

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

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

RE: Huge optimization costs with 9.2

2003-10-01 Thread Govind.Arumugam

Joan,

Can you post the query with the plan in 8.1.7 and 9.2;  We ran into certain types of 
queries that had totally different execution plans and got work-arounds.

Thanks,
Govind

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


Kirti,

I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
performance is good. After upgrade, one query run time from 2 min to 12
hours. Of course, I re-analyzed all tables and indexes. The explain plan
changed from hash join to nested-loop. All the parameters are same. So I
have to put optimized_feature_enable=8.1.7 to make run normal as usual.
I hate to disable the new feature, but no choose.

Joan

Kirtikumar Deshpande wrote:
 
 Were tables/indexes anlayzed after the upgrade?
 
 - Kirti
 
 --- Jeff Landers [EMAIL PROTECTED] wrote:
  Hello All
 
  Version  OS:
  Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3.
 
  Problem:
  We've captured the sql text and optimization plans for critical sql
  prior to upgrading to 9.2.   After  the upgrade we have noticed
  that the cost associated with every sql statement  is now HUGE
  compared to its 9.0.1.4 counterpart.   Per the statistics being captured
  via traces,
  these statement are noticeably slower per execution.
 
  Anyone experiencing/experienced the same problem with 9.2?
 
  Thank you in advance.
 
 
 
 __
 Do you Yahoo!?
 Yahoo! SiteBuilder - Free, easy-to-use web site design software
 http://sitebuilder.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joan Hsieh
  INET: [EMAIL PROTECTED]

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

I don't think that you can reduce temp datafile much below bytes_used value
in v$temp_space_header. I could reduce the file few kilobytes, but not much
compared to it's size. In order to reduce bytes_used, you need to bounce
instance (if there isn't any nifty tricks for releasing temp segment
otherwise).

Tanel.

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 6:34 PM


 Yes you can.
 On Wed, 2003-10-01 at 11:29, Teresita Castro wrote:
  I have Oracle 9.2i
  I already run the query and the tablespace TEMP have
  CONTENTS=TEMPORARY AND EXTENT_MAN=LOCAL
  Can I run this line to fix the size of my tablespace?
 
  SQL alter database tempfile '/data/oradata/system/temp01.dbf' resize
  128M;
 
  I mean is the same or I have to change something?
 
   [EMAIL PROTECTED] 09/30/03 10:04PM 
  Teresita, spelling the word administrator with the capital letter
  is a good  thing. Furthermore, if you have version 9i, you can do the
  following, little known, thing:
 
  SQL alter database tempfile '/data/oradata/system/temp01.dbf' resize
  128M;
 
  Database altered.
 
 
  If it's Oracle 8i, you can actually have a permanent, dictionary
  based
  tablespace acting as a temporary tablespace. In version 9, however,
  you
  can't. Here is the query to find out what do you have:
1  select tablespace_name,contents,extent_management
2  from dba_tablespaces
3* order by contents
  SQL /
 
  TABLESPACE_NAMECONTENTS  EXTENT_MAN
  -- - --
  SYSTEM PERMANENT LOCAL
  DRSYS  PERMANENT LOCAL
  EXAMPLEPERMANENT LOCAL
  INDX   PERMANENT LOCAL
  USERS  PERMANENT LOCAL
  XDBPERMANENT LOCAL
  TOOLS  PERMANENT LOCAL
  TEMP   TEMPORARY LOCAL
  UNDOTBS1   UNDO  LOCAL
 
  9 rows selected.
 
  SQL
 
  If the column contents reads TEMPORARY for the given tablespace, you
  can
  drop it and recreate it without any harm. Note that in 8i you don't
  have
  UNDO tablespaces and your system tablespace cannot be LMT.
 
 
  On 2003.09.30 22:24, Teresita Castro wrote:
   Hi!!
   I am trying to change the size of my tablespace TEMP, I am not an
   Administrator but we really need to make this tablespace smaller.
   Already the size is 13214 Mgs, and this tablaspace is on a disk that
  is
   full,
   so if we can not make it smaller we are going to be in a serius
  trouble
   ( our
   Administrator is not here until Monday).
  
 
  -- 
  Mladen Gogala
  Oracle DBA
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: Mladen Gogala
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
 




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

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

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



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel

RE: RE: vertical serches on a table - how to

Stephane,
Apologize for not being clear on my question.

The query you have provided will only return one
record, ie.
1 mango  banana.

I need two records to be returned:
1  mangobanana
1  grape pineapple

You're right that by 'vertical', I meant filtering
according to conditions 
on OTHER rows but at the same time, I want those
filter conditions to be 
displayed as well, which in this case - grape and
pineapple.

Any idea.

Thanks.

susan


In that case you keep something looking vaguely like the condition in your initial 
query but add a subquery to check that both conditions are satisfied :

select X1.ID, X1.col1, X1.col2
from tableX X1
where  (X1.col1='banana')
or (((X1.col1='mango' and X1.col2='banana')
or (X1.col1='grape' and X1.col2='pineapple'))
   and 2 = (select count(*)
from tableX X2
where ((X2.col1='mango'
   and X2.col2='banana')
   or (X2.col1='grape'
   and X2.col2='pineapple'))
   and X2.ID = X1.ID)));

 No guarantee on the proper number of parentheses.

SF


 Hi,
 I have a table that I would like to perform a
 vertical search on. For eg.
 
 Table X
 ---
 ID  COL1 COL2
 1   apple orange
 1   mango   banana
 1   grape pineapple
 2   mango   banana
 2   guava lemon
 
 
 I would like to display records that meet the
 following criteria for *a
 particular ID*.
 
 (COL1=banana)
  OR
 (COL1=mango and COL2=banana
 AND
 COL1=grape and COL2=pineapple
 
 
 The output should be
 ID   COL1COL2
 1mango   banana
 1grapepineapple
 
 It should not display
 ID   COL1COL2
 2mango   banana
 
 since ID=2 did not meet the criteria where
 COL1=grape and COL2=pineapple.
 
 
 I tried the following SQL but the output is
always
 zero because COL1 can
 never be a mango and a grape and COL2 can never
be
 a
 banana and a pineapple at the same time for a
 particular ID.
 
 select ID, col1, col2
 from tableX
 where(col1='banana')
 or   ((col1='mango' and col2='banana')
and
   (col1='grape' and col2='pineapple')
   );
 
 
 Any idea how I can do a vertical search on the
 table.
 
 Thanks for any help you can provide.
 
 susan
 

Susan,

Took me some time to understand what you meant
by 'vertical search'. 
ANDs and ORs in a WHERE clause always apply to the
current row under 
scrutiny. What you mean by 'vertical' is that you
want to filter according 
to conditions on OTHER rows. This is done by a
subquery.

Your query could read

select X1.ID, X1.col1, X1.col2
from   tableX X1
where  (X1.col1='banana')
or ((X1.col1='mango' and X1.col2='banana')
 and EXISTS (select null
   from tableX X2
   where X2.col1='grape'
 and X2.col2='pineapple'
 and X2.ID = X1.ID));

Simplifying to the extreme, each different row you
handle must be returned 
by its 'own' query.

Regards,

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

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

RE: Estimating space needed for UNDO tablespaces

2003-10-01 Thread Thomas Day


Try these queries.

/*
Rows returned below mean that UNDO_RETENTION needs to be increased
*/
select * from v$undostat where UNXPSTEALCNT  0 or SSOLDERRCNT  0;
/*
Rows returned below mean that space needs to be added to the undo
tablespace.  All space
in the tablespace was used and no free space was available when requested
*/
select * from v$undostat where NOSPACEERRCNT  0;




   

  Kirtikumar   

  DeshpandeTo:  Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
  kirtikumar_desh cc: 

  pandeSubject: RE: Estimating space needed 
for UNDO tablespaces   
  @yahoo.com  

  Sent by: 

  ml-errors

   

   

  10/01/2003 10:14 

  AM   

  Please respond   

  to ORACLE-L  

   

   





Hi Jeff,
 Stealing extents is normal when there is no free space available to grow
the active undo segment.
If expired extents are getting stolen, I would not worry too much about
adding more space to the
undo tablespace, but monitor how much undo space the segment takes up.
If unexpired extents are getting stolen, then you may have to consider
either adding more space,
or reducing undo retention time.
If the undo tablespace does not have enough space to accommodate your
largest transaction, event
after stealing extents, and the data files are not autoexensible, then you
will get ORA-1650 (I
think, that the error#). And to avoid it, auto undo management still needs
to be monitored!

HTH,

- Kirti


--- Thomas Jeff [EMAIL PROTECTED] wrote:
 Kirti,

 Thanks for this information.I've implemented AUM in a number of our
 development
 databases.One of the things I have to do is write up a monitoring
policy
 to hand
 to our contracted production DBAs -- guidelines on how to address certain
 scenarios
 and so forth -- otherwise, they will simply resort to adding 'more' of
 whatever they
 presume is in short supply in event of a production crisis.

 For example, I'm seeing some steal counts in v$undostat, implying that
the
 undo
 tablespace needs more space.   However, from what you are saying, it
seems
 that if
 undo_retention is consistently larger then maxquerylen during the period
of
 time when
 the steal counts occur, that maybe the smarter thing to do is simply
reduce
 the
 undo_retention parameter before considering adding more space?


 Jeff



 -Original Message-
 Sent: Friday, September 26, 2003 11:50 PM
 To: Multiple recipients of list ORACLE-L


 You can run following query to get an idea of undo generation rate and
max
 query length:

 SELECT
   to_char(min(begin_time),'MM/DD/ HH24:MI:SS') Begin Time,
   to_char(max(end_time),'MM/DD/ HH24:MI:SS') End Time,
   (max(end_time)-min(begin_time))*24*60*60 Seconds,
   sum(undoblks) UndoBlks,
   sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60)
 UndoBlksPerSec,
   max(maxquerylen) MaxQueryLenSecs
  FROM
 v$undostat;

 Unfortunately, the TXNCOUNT column in v$undostat view in Oracle 9.2.0.x
is
 screwed up. It does not
 correctly report the transaction count for the sample interval. Instead
it
 keeps accumulating. One
 needs to do the math to get the correct count for the desired

Oracle db using IBM FAStT disk storage questions

2003-10-01 Thread Ron Cetnar









Is anyone using Oracle database with IBM disk storage FAStT? 

I was wondering if you had any performance problems, pitfalls
and any- 

other stories that you might want to
inform me about? Before we commit 

ourselves in purchasing this SAN your info
would be appreciated.



We are a PeopleSoft shop running their Higher Education
products 8.

Running on RS6000 H-80 with AIX 4.3.3, soon going to 5.2. Oracle version 9.0.2.3.



You can email me (address below) directly or to the list.



Thanks



Ron





*** 
Ron Cetnar 
Supervising Programmer/Analyst/Oracle DBA 
ITS - University Applications Development 

State University of New York at Albany 
MSC 100 
  1400 Washington Ave 
 Albany, NY 1 

Email: [EMAIL PROTECTED] 
Work: (518) 437-4535 
Fax:
(518) 437-4540 

***

Re: Huge optimization costs with 9.2

Execution plans would be helpful.
If optimizer_index_* parameters are unset, CBO tends to prefer full table
access more, which doesn't seem to be your case (but exectution plans are
needed in order to be sure in that).

As Mladen asked about histograms - do you use bind variables in your
queries? In 8i CBO can't peek bind variable values during hard parse, but in
9i it can, this feature in combination with histograms might cause execution
plan change...

Did you do the analyzing in 9i exactly the same way and with same tools than
in 8i?

Tanel.

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 7:09 PM


 Joan, what is the difference in the plans? What specific feature
 made the difference? Are the values of
 optimizer_index_cost_adj and optimizer_index_caching same on both
 versions? How about histograms? What is with
 db_file_multiblock_read_count,sort_area_size and hash_area_size? Is
 everything same as in 8i? May be setting of those parameters can be
 tweaked to your benefit?

 On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote:
  Kirti,
 
  I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
  performance is good. After upgrade, one query run time from 2 min to 12
  hours. Of course, I re-analyzed all tables and indexes. The explain plan
  changed from hash join to nested-loop. All the parameters are same. So I
  have to put optimized_feature_enable=8.1.7 to make run normal as usual.
  I hate to disable the new feature, but no choose.
 
  Joan
 
  Kirtikumar Deshpande wrote:
  
   Were tables/indexes anlayzed after the upgrade?
  
   - Kirti
  
   --- Jeff Landers [EMAIL PROTECTED] wrote:
Hello All
   
Version  OS:
Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3.
   
Problem:
We've captured the sql text and optimization plans for critical sql
prior to upgrading to 9.2.   After  the upgrade we have noticed
that the cost associated with every sql statement  is now HUGE
compared to its 9.0.1.4 counterpart.   Per the statistics being
captured
via traces,
these statement are noticeably slower per execution.
   
Anyone experiencing/experienced the same problem with 9.2?
   
Thank you in advance.
   
   
  
   __
   Do you Yahoo!?
   Yahoo! SiteBuilder - Free, easy-to-use web site design software
   http://sitebuilder.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).
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net




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

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

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



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

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

Re: COBOL TO ORACLE

2003-10-01 Thread Tim Gorman

Wonderful race, the Romans.  Just super!  sniff!


on 9/30/03 3:34 PM, Jesse, Rich at [EMAIL PROTECTED] wrote:

 Crucifixion is a perfectly viable form of punishment, but only for the first
 offense.
 
 Best thing the Romans ever done for us.  Oh, yeah.  If we didn't have
 crucifixion, this country would be in a right bloody mess.
 
 
 Rich Jesse,
 People's Front of Judea
 
 -Original Message-
 From: Mladen Gogala [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, September 30, 2003 4:15 PM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: COBOL TO ORACLE
 
 
 On Tue, 2003-09-30 at 16:19, Stephane Paquette wrote:
 Like Thomas Day said, Oracle is an rdbms and COBOL a
 programming language.
 
 
 COBOL *** WAS *** a programming language. Horse *** WAS *** basis of
 transport. You should have used past tense, Stephane. I'm not really
 that partial when it comes to horses, but having suffered
 COBOL, I would
 really leave it in the ancient past, together with Spanish Inquisition
 and crucifiction as a viable capital punishment.

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

RE: Huge optimization costs with 9.2

2003-10-01 Thread Guang Mei

Hi Joan:

Is your hash_area_size parameter the same in both situations?

Guang

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


Kirti,

I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
performance is good. After upgrade, one query run time from 2 min to 12
hours. Of course, I re-analyzed all tables and indexes. The explain plan
changed from hash join to nested-loop. All the parameters are same. So I
have to put optimized_feature_enable=8.1.7 to make run normal as usual.
I hate to disable the new feature, but no choose.

Joan

Kirtikumar Deshpande wrote:

 Were tables/indexes anlayzed after the upgrade?

 - Kirti

 --- Jeff Landers [EMAIL PROTECTED] wrote:
  Hello All
 
  Version  OS:
  Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3.
 
  Problem:
  We've captured the sql text and optimization plans for critical sql
  prior to upgrading to 9.2.   After  the upgrade we have noticed
  that the cost associated with every sql statement  is now HUGE
  compared to its 9.0.1.4 counterpart.   Per the statistics being captured
  via traces,
  these statement are noticeably slower per execution.
 
  Anyone experiencing/experienced the same problem with 9.2?
 
  Thank you in advance.
 
 

 __
 Do you Yahoo!?
 Yahoo! SiteBuilder - Free, easy-to-use web site design software
 http://sitebuilder.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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Joan Hsieh
  INET: [EMAIL PROTECTED]

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

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

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

Re: Huge optimization costs with 9.2

2003-10-01 Thread Wolfgang Breitling

One of the undocumented init.ora parameters that changed from 8 to 9 is 
_UNNEST_SUBQUERY  (from false to true). You could try if that is the 
culprit. Of course, since it is an undocumented parameter, get the blessing 
from Oracle support before using it in a production database.

At 10:09 AM 10/1/2003, you wrote:
Joan, what is the difference in the plans? What specific feature
made the difference? Are the values of
optimizer_index_cost_adj and optimizer_index_caching same on both
versions? How about histograms? What is with
db_file_multiblock_read_count,sort_area_size and hash_area_size? Is
everything same as in 8i? May be setting of those parameters can be
tweaked to your benefit?
On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote:
 Kirti,

 I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
 performance is good. After upgrade, one query run time from 2 min to 12
 hours. Of course, I re-analyzed all tables and indexes. The explain plan
 changed from hash join to nested-loop. All the parameters are same. So I
 have to put optimized_feature_enable=8.1.7 to make run normal as usual.
 I hate to disable the new feature, but no choose.
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: RE: Separate Indexes and Data

2003-10-01 Thread MacGregor, Ian A.

Part of the problem is self-inflicted.  We currently use separate tablespaces for each 
 major project.  For instance:  chemical inventory  gets its own data and index 
tablespaces,  dosimeter data gets the same,  network configuration data as well.  For 
many projects once the design has matured new segment creation is rare.   The holes  
remain.  Also data segments cannot be moved willy-nilly, users do not like getting 
unusable index errors.  There are also tables which cannot be easily moved such as 
tables with longs.  These were created before LOBs were available.  Moving data also 
entails a certain amount of risk. Inside a project, we let developers create the 
tables and indexes which are specific to that project.  Very few actually create 
indexes in their proper tablespaces.  Corrective action creates more  holes.  This is 
one reason why I am looking at index and data segments in the same tablespace.   

Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]   
-Original Message-
Sent: Tuesday, September 30, 2003 4:25 PM
To: Multiple recipients of list ORACLE-L


But those holes of exactly the right size for new objects to fit into. Since you'll 
presumably move it once it gets about 1,000 extents or so that isn't a huge amount of 
space that's being wasted.



Jay Miller
Sr. Oracle DBA


-Original Message-
Sent: Tuesday, September 30, 2003 4:45 PM
To: Multiple recipients of list ORACLE-L


My criticism of the defrag paper was that it did not address what to do when a segment 
grew  large enough to belong in a tablespace with a larger uniform extent size.  
Moving the segment creates  holes in its original tablespace which may close only in 
the fullness of time.  Physical backups of the files comprising the original 
tablespace include this wasted space, this is compounded by how many days backup you 
keep available, and the number of copies of backups. 
-- 
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: MacGregor, Ian A.
  INET: [EMAIL PROTECTED]

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

Title: RE: Huge optimization costs with 9.2





yeah ... and setting _unnest_subquery=true also gave WRONG results when you used a aggregate function in a sub-query without a group by clause. 

That was a bug ... 


Raj


-Original Message-
From: Wolfgang Breitling [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 01, 2003 12:55 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Huge optimization costs with 9.2



One of the undocumented init.ora parameters that changed from 8 to 9 is 
_UNNEST_SUBQUERY (from false to true). You could try if that is the 
culprit. Of course, since it is an undocumented parameter, get the blessing 
from Oracle support before using it in a production database.


At 10:09 AM 10/1/2003, you wrote:
Joan, what is the difference in the plans? What specific feature
made the difference? Are the values of
optimizer_index_cost_adj and optimizer_index_caching same on both
versions? How about histograms? What is with
db_file_multiblock_read_count,sort_area_size and hash_area_size? Is
everything same as in 8i? May be setting of those parameters can be
tweaked to your benefit?

On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote:
  Kirti,
 
  I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
  performance is good. After upgrade, one query run time from 2 min to 12
  hours. Of course, I re-analyzed all tables and indexes. The explain plan
  changed from hash join to nested-loop. All the parameters are same. So I
  have to put optimized_feature_enable=8.1.7 to make run normal as usual.
  I hate to disable the new feature, but no choose.


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



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: RE: Create Table..As Select: Number formats

2003-10-01 Thread Simon . Anderson

Thanks to everyone who responded to this thread - 

Option A is now to persuade the designers to remove the Number formatting 
from the parttioned table,
Option B is to pre-create the working table and populate it with Truncate 
and Insert /* Append */

Option B will be slower, I think, due to the extra redo/undo generated 
despite my best efforts to persuade it otherwise.  I've done enough tests 
with changing the format of the number columns to convince myself that 
that the calculated Number columns are the only remaining issue.

Cheers
Simon Anderson

I'm trying to create a table using 'Create
Table...As Select...' 
...
  Are you sure that it comes from the NUMBER() columns? Reminds me of the 
problem when you have a NULL in a UNION, 
 which must be explicitly cast with a to_number(), to_date() or 
to_char(). Might it come from some NULL in your CREATE  TABLE AS SELECT 
... ?
 
 Regards,
 
 Stephane Faroult
 Oriole

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

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

Re: RE: Create Table..As Select: Number formats

 Thanks to everyone who responded to this thread -

 Option A is now to persuade the designers to remove the Number formatting
 from the parttioned table,
 Option B is to pre-create the working table and populate it with Truncate
 and Insert /* Append */

 Option B will be slower, I think, due to the extra redo/undo generated
 despite my best efforts to persuade it otherwise.  I've done enough tests
 with changing the format of the number columns to convince myself that
 that the calculated Number columns are the only remaining issue.

No, option B is as fast as CTAS (as long as you don't have any indexes on
the table).
Just make sure that your append hint works...

Tanel.



 Cheers
 Simon Anderson

 I'm trying to create a table using 'Create
 Table...As Select...'
 ...
   Are you sure that it comes from the NUMBER() columns? Reminds me of the
 problem when you have a NULL in a UNION,
  which must be explicitly cast with a to_number(), to_date() or
 to_char(). Might it come from some NULL in your CREATE  TABLE AS SELECT
 ... ?
 
  Regards,
 
  Stephane Faroult
  Oriole

 -- 
 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: Tanel Poder
  INET: [EMAIL PROTECTED]

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

Re: UNIX : script help/input

2003-10-01 Thread Johan Muller


Mladen,

It worked! 

Heartfelt thank you from the evangelized perl crowd (now watch the list-owner grin).Mladen Gogala [EMAIL PROTECTED] wrote:
#!/usr/bin/perl -wuse strict;use bytes;my ($NAME,$IP,@LB);while () {chomp;@LB=split /\s+/;if ($LB[0] =~ /^name:/i) {$NAME=$LB[1];}if ($LB[0] =~ /^address:/i) {$IP=$LB[1];write;}}format STDOUT=@ ,@$NAME,$IP

Re: blocksize on AIX

 I've read on ixora.com.au and other sites that the optimal block size for
 AIX is 4K, because JFS pages are 4K also.

Isn't JFS block size changeable?


 Has anyone of you ever experienced performance problems on AIX due to a
 larger blocksize? What exactly are the 'read ahead' issues and cpu
problems
 regarding double buffering etc. Are there any alternatives, other than
 moving to raw volumes?

I think read ahead problems on 4k file system with 8k block size might mean
that some operating systems will do readahead caching when they see more
than one consecutive block requested from file system. So, every oracle
single-block read will actually result in much larger read by OS. This isn't
that bad when Oracle itself is doing big multiblock reads, but for single
block reads it might cause performance problems.

Tanel.




 HTH,
 Hans

 _
 MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl

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

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



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

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

Re: Huge optimization costs with 9.2

Wolfgang, you're a genuine cornucopia of useful knowledge. This is
another email of yours that I'll have to save for later as people's
exhibit S.
On Wed, 2003-10-01 at 12:54, Wolfgang Breitling wrote:
 One of the undocumented init.ora parameters that changed from 8 to 9 is 
 _UNNEST_SUBQUERY  (from false to true). You could try if that is the 
 culprit. Of course, since it is an undocumented parameter, get the blessing 
 from Oracle support before using it in a production database.

 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net




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

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

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

Re: workarea_size_policy=auto and performance efficiency [was: Re:

2003-10-01 Thread Tim Gorman

Richard,

Thanks for the detailed explanation!

As a C programmer of some 20 years, I can only assume that Oracle has done
away with the use of the malloc(), free(), etc UNIX library calls and is
now calling the UNIX system call brk() directly?

It was the underlying heap-extent management in the standard malloc
library, which is of course outside of Oracle's control, which made the
SORT_AREA_RETAINED_SIZE parameter largely ineffective for deallocating
workarea memory back to the OS.

Because if Oracle is continuing to call malloc() and free(), then I can
only say that the Oracle RDBMS certainly *thinks* it is releasing memory (as
it did in the past), but it really isn't.

Any idea if this is the case?  Kind of obscure, I know, but it is this chain
of reasoning that has allowed a reasonable explanation of the
ineffectiveness of the SORT_AREA_RETAINED_SIZE parameter in performing its
documented purpose in the past.

Thanks in advance!

-Tim

on 9/30/03 6:49 AM, Richard Foote at [EMAIL PROTECTED] wrote:

 Hi Tim,
 
 I would suggest there are two key advantages to using automatic workspace
 management.
 
 The first and perhaps most important is that yes, unlike the manual method
 by which sessions cling onto memory, automatic workspace management can
 deallocate the tuneable portion of the PGAs (those previously set with
 *_AREA_SIZE parameters) when no longer required. This means that the overall
 memory consumption used during peak periods (when memory is possibly a
 problem) is likely to be less as the average memory used per session is
 likely to be lower due to this deallocation process (although it does
 somewhat depend on both the size and concurrency of these operations). On a
 key production database at my current site, the vast majority of the
 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite
 most having a substantially larger pga_max_mem due to previous workspace
 activity (as evidenced in v$process). This overall reduction in memory
 consumption is measurable at between 1-2G which for us was significant as we
 were pushing our memory limit previously.
 
 Secondly, as memory is more effectively returned, Oracle/we can be both more
 generous and more flexible in how much memory each session temporarily
 consumes. With manual tuning, after setting the (say) SAS to (say) 10M, what
 if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest there
 are quotas in how much a particular session can consume depending on
 workload (eg. 5% limit for serial operations, etc.), the maximum memory that
 can be safely consumed by a session could be somewhat higher. If too many
 operations require a onepass/multipass executions, then the P_A_T should
 obviously be reviewed. However although the P_A_T setting kinda provides a
 safety net for memory consumption, if you have few concurrent, largish
 workarea operations, you could set the P_A_T to be somewhat higher than
 perhaps desirable (if reached) knowing it won't in fact be reached because
 of the low concurrency of these operations. This then increases the maximum
 memory capacity for each session in a controlled manner, knowing that this
 memory won't be hogged by the sessions. As I mentioned before, we now
 experience no disks sorts whatsoever.
 
 In our environment, automatic workspace management has been ideal. We have a
 large number of sessions most of which perform workspace operations at some
 stage but not concurrently in any significant numbers. Thereby, we have
 managed to both improve the efficiency of workspace operations by allowing
 sessions to acquire the necessary memory as required while at the same time
 dramatically reducing overall memory consumption.
 
 Best of both worlds !!
 
 Cheers
 
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Tuesday, September 30, 2003 7:39 AM
 
 
 Richard,
 
 I take it that your two points are...shall we say...enhancement requests,
 not current functionality?  :-)
 
 Following up on the discussion of space-efficiency and tabling (for the
 moment) my questions about the performance-efficiency side of things.
 Yes, there certainly is an element of performance-efficiency to
 space-efficiency if it keeps you from swapping...
 
 ..anyway...
 
 Using WORKAREA_SIZE_POLICY = MANUAL, only the sort workarea has ever even
 pretended to give memory back for the duration of the session, depending on
 the relationship between SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE.  The
 hash and bitmap workareas have never had this functionality, as near as I
 can tell.
 
 So, I think that you're absolutely correct that sessions using
 WORKAREA_SIZE_POLICY = MANUAL will allocate the memory and hold onto it for
 a long time, essentially until they disconnect.  Is this correct?
 
 Is WORKAREA_SIZE_POLICY = AUTO any different?  From what I've gathered, the
 P_A_T algorithms only occur upon allocation of workarea memory.  Is there
 any additional logic

Re: UNIX : script help/input

On Wed, 2003-10-01 at 13:24, Johan Muller wrote:
 Mladen,
  
 It worked! 
  

Did you have any doubts? That's precisely what perl is good for.





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

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

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

Re: RE: Create Table..As Select: Number formats

 No, option B is as fast as CTAS (as long as you don't have any indexes on
 the table).
 Just make sure that your append hint works...

Also you have to specify NOLOGGING on table or tablespace level when doing
insert /*+ APPEND */ or use NOLOGGING hint if you're on 9i.

Tanel.


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

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

FW: createing test sessions

2003-10-01 Thread Bob Metelsky

Our exchange hiccupped...



How about

for /L %i IN (1,1,250) do start /min sqlplus user/[EMAIL PROTECTED]
@script.sql

Id try it with less than 250 as it can kill the machine opening 250
windows!
;-)

to run form a batch file you need to %%i the variable
hth
bob


If on windows, type
   start /min sqlplus user/[EMAIL PROTECTED] @script.sql
..255 times on your windows command prompt.

Tanel.


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, September 25, 2003 7:54 PM


I want to run some sql scripts and i need to open 255 session at
same
time and
these session will run somw sql scripts.

HOW CAN I OPEN  255 session at the same time ?
may be a tool , may be shell script.

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

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



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

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

Yes, they are same. Basically I didn't change any parameter after
upgrade.

Guang Mei wrote:
 
 Hi Joan:
 
 Is your hash_area_size parameter the same in both situations?
 
 Guang
 
 -Original Message-
 Joan Hsieh
 Sent: Wednesday, October 01, 2003 11:55 AM
 To: Multiple recipients of list ORACLE-L
 
 Kirti,
 
 I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
 performance is good. After upgrade, one query run time from 2 min to 12
 hours. Of course, I re-analyzed all tables and indexes. The explain plan
 changed from hash join to nested-loop. All the parameters are same. So I
 have to put optimized_feature_enable=8.1.7 to make run normal as usual.
 I hate to disable the new feature, but no choose.
 
 Joan
 
 Kirtikumar Deshpande wrote:
 
  Were tables/indexes anlayzed after the upgrade?
 
  - Kirti
 
  --- Jeff Landers [EMAIL PROTECTED] wrote:
   Hello All
  
   Version  OS:
   Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3.
  
   Problem:
   We've captured the sql text and optimization plans for critical sql
   prior to upgrading to 9.2.   After  the upgrade we have noticed
   that the cost associated with every sql statement  is now HUGE
   compared to its 9.0.1.4 counterpart.   Per the statistics being captured
   via traces,
   these statement are noticeably slower per execution.
  
   Anyone experiencing/experienced the same problem with 9.2?
  
   Thank you in advance.
  
  
 
  __
  Do you Yahoo!?
  Yahoo! SiteBuilder - Free, easy-to-use web site design software
  http://sitebuilder.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).
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Joan Hsieh
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Guang Mei
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joan Hsieh
  INET: [EMAIL PROTECTED]

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

Re: STAT from trace

2003-10-01 Thread Daniel Fink



Henry,
What happens if you issue another query after the query of interest?
(something like "select 1 from dual;") STATshould be emitted when
the cursor is closed.
Daniel

Henry Poras wrote:
Thanks.
I tried both disabling the trace and quitting from the session. No luck
with 10046, just sql_trace.Henry

-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Jamadagni, Rajendra
Sent: Wednesday, October 01,
2003 10:25 AM
To: Multiple recipients of
list ORACLE-L
Subject: RE: STAT from trace

I get STAT lines no matter how I enable the trace. Make sure
you wither close the session or stop the trace so that all pending STAT
lines will be written to the file.
Raj
-Original Message-
From: Henry Poras [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 01, 2003 9:34 AM
To: Multiple recipients of list ORACLE-L
Subject: STAT from trace
I was creating some trace files yesterday and came across
one of these
problems that shows up occasionally (then I forget about
it).
When I ran my query using ALTER SESSION SET sql_trace=true
(sqlplus;
8.1.7.4), I got the STAT line in the trace and the associated
'row source'
information after running tkprof.
When I ran my query using ALTER SESSION SET EVENTS '10046
trace name context
forever, level 8' (or with dbms_system.set_ev), there
were no STAT entries
for my query. There were some for some of the recursive
queries.
Everything else was identical.
What's up?
Henry


begin:vcard 
n:Fink;Daniel
x-mozilla-html:FALSE
org:Sun Microsystems, Inc.
adr:;;
version:2.1
title:Lead, Database Services
x-mozilla-cpt:;9168
fn:Daniel  W. Fink
end:vcard

Re: Huge optimization costs with 9.2

Malden,

They all same. I didn't change any parameters after upgrade. The
difference in the plans are one used all hash join vs nested loop to
join tables. The histograms are all same. db_file_multiblock_read_count
is 8, sort_area_size is 1mb, hash_area_size is 40096.
optimize_index_caching and optimizer_index_cost is 0/100 all same as 8i.
The interesting part is after I changed optimize_feature_enable to
8.1.7. They produced the same explain plan.

Joan

Mladen Gogala wrote:
 
 Joan, what is the difference in the plans? What specific feature
 made the difference? Are the values of
 optimizer_index_cost_adj and optimizer_index_caching same on both
 versions? How about histograms? What is with
 db_file_multiblock_read_count,sort_area_size and hash_area_size? Is
 everything same as in 8i? May be setting of those parameters can be
 tweaked to your benefit?
 
 On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote:
  Kirti,
 
  I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
  performance is good. After upgrade, one query run time from 2 min to 12
  hours. Of course, I re-analyzed all tables and indexes. The explain plan
  changed from hash join to nested-loop. All the parameters are same. So I
  have to put optimized_feature_enable=8.1.7 to make run normal as usual.
  I hate to disable the new feature, but no choose.
 
  Joan
 
  Kirtikumar Deshpande wrote:
  
   Were tables/indexes anlayzed after the upgrade?
  
   - Kirti
  
   --- Jeff Landers [EMAIL PROTECTED] wrote:
Hello All
   
Version  OS:
Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3.
   
Problem:
We've captured the sql text and optimization plans for critical sql
prior to upgrading to 9.2.   After  the upgrade we have noticed
that the cost associated with every sql statement  is now HUGE
compared to its 9.0.1.4 counterpart.   Per the statistics being captured
via traces,
these statement are noticeably slower per execution.
   
Anyone experiencing/experienced the same problem with 9.2?
   
Thank you in advance.
   
   
  
   __
   Do you Yahoo!?
   Yahoo! SiteBuilder - Free, easy-to-use web site design software
   http://sitebuilder.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).
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
 
 Note:
 This message is for the named person's use only.  It may contain confidential, 
 proprietary or legally privileged information.  No confidentiality or privilege is 
 waived or lost by any mistransmission.  If you receive this message in error, please 
 immediately delete it and all copies of it from your system, destroy any hard copies 
 of it and notify the sender.  You must not, directly or indirectly, use, disclose, 
 distribute, print, or copy any part of this message if you are not the intended 
 recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to 
 monitor all e-mail communications through its networks.
 Any views expressed in this message are those of the individual sender, except where 
 the message states otherwise and the sender is authorized to state them to be the 
 views of any such entity.
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Mladen Gogala
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joan Hsieh
  INET: [EMAIL PROTECTED]

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

Re: Huge optimization costs with 9.2

this is the explain plan for the 9i, sorry it is long sql.

Rows Row Source Operation
---  ---
  1  LOAD AS SELECT  (cr=14674449 r=2275 w=1831 time=787991194 us)
  42647   NESTED LOOPS OUTER (cr=14673991 r=2273 w=0 time=5081221102 us)
  42647NESTED LOOPS OUTER (cr=6448712 r=2187 w=0 time=1730062983 us)
  42647 NESTED LOOPS OUTER (cr=3262940 r=1395 w=0 time=1075194825
us)
  42647  NESTED LOOPS OUTER (cr=2917318 r=1375 w=0 time=973480801
us)
  42647   TABLE ACCESS FULL PR_IDENTITY (cr=1207 r=1205 w=0
time=1452575 us)
   3766   VIEW PUSHED PREDICATE  (cr=2916111 r=170 w=0
time=971571531 us)
   3766HASH JOIN  (cr=2916111 r=170 w=0 time=971416648 us)
   3766 NESTED LOOPS  (cr=50183 r=25 w=0 time=759193 us)
   3766  TABLE ACCESS BY INDEX ROWID PR_MED (cr=46415 r=25 w=0
time=651677 us)
   3766   INDEX UNIQUE SCAN PR_MED_UNIQUE_TRUNK (cr=42649 r=24
w=0 time=503807 us)(object id 51394)
   3766  TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=3768 r=0
w=0 time=46636 us)
   3766   INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0
time=15519 us)(object id 51375)
14182756 VIEW  (cr=2865928 r=145 w=0 time=942647916 us)
14182756  UNION-ALL  (cr=2865928 r=145 w=0 time=931367819 us)
14182756   HASH JOIN  (cr=598795 r=145 w=0 time=243380379 us)
14182756NESTED LOOPS  (cr=587497 r=145 w=0 time=195899818
us)
14182756 VIEW  (cr=583730 r=145 w=0 time=124765499 us)
14182756  UNION-ALL  (cr=583730 r=145 w=0 time=112440519 us)
  0   HASH JOIN  (cr=15064 r=0 w=0 time=1416201 us)
  0TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0
w=0 time=903383 us)
  0TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us)
14182756   HASH JOIN  (cr=568666 r=145 w=0 time=86101027 us)
 105448TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0
w=0 time=552179 us)
14182756TABLE ACCESS FULL PR_MED (cr=553602 r=145 w=0
time=26292679 us)
14182756 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0
w=0 time=26817559 us)(object id 51357)
  90384TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0
time=228394 us)
  0   NESTED LOOPS  (cr=2255835 r=0 w=0 time=665712789 us)
50935150HASH JOIN  (cr=2252068 r=0 w=0 time=429854587 us)
  90384 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0
time=216366 us)
50935150 HASH JOIN  (cr=2240770 r=0 w=0 time=232393166 us)
50935150  TABLE ACCESS FULL PR_SIS (cr=2229472 r=0 w=0
time=52143346 us)
  71554  TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES (cr=11298
r=0 w=0 time=353694 us)
  0INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 w=0
time=115423379 us)(object id 51357)
  0   HASH JOIN  (cr=11298 r=0 w=0 time=900827 us)
  0TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0
time=289225 us)
  0MERGE JOIN CARTESIAN (cr=0 r=0 w=0 time=0 us)
  0 TABLE ACCESS FULL PR_AFFILIATE (cr=0 r=0 w=0 time=0
us)
  0 BUFFER SORT (cr=0 r=0 w=0 time=0 us)
  0  INDEX FULL SCAN PR_ADMIN_TYPES_PK (cr=0 r=0 w=0
time=0 us)(object id 51357)
481  VIEW PUSHED PREDICATE  (cr=345622 r=20 w=0 time=101230049
us)
481   HASH JOIN  (cr=345622 r=20 w=0 time=101019065 us)
481NESTED LOOPS OUTER (cr=43553 r=5 w=0 time=422888 us)
481 TABLE ACCESS BY INDEX ROWID PR_AFFILIATE (cr=43130 r=5
w=0 time=376132 us)
481  INDEX UNIQUE SCAN PR_AFFILIATE_PK (cr=42649 r=4 w=0
time=292929 us)(object id 51360)
421 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=423 r=0
w=0 time=4703 us)
421  INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0
time=1925 us)(object id 51375)
 231361VIEW  (cr=302069 r=15 w=0 time=95276432 us)
 231361 UNION-ALL  (cr=302069 r=15 w=0 time=95016018 us)
  0  HASH JOIN  (cr=3848 r=0 w=0 time=417296 us)
  0   NESTED LOOPS  (cr=3848 r=0 w=0 time=351280 us)
  0VIEW  (cr=3848 r=0 w=0 time=350781 us)
  0 UNION-ALL  (cr=3848 r=0 w=0 time=349902 us)
  0  HASH JOIN  (cr=1924 r=0 w=0 time=222856 us)
  0   TABLE ACCESS FULL PR_DEPARTMENTS (cr=1924 r=0 w=0
time=155040 us)
  0   TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us)
  0  HASH JOIN  (cr=1924 r=0 w=0 time=123298 us)
  0   TABLE ACCESS FULL PR_DEPARTMENTS (cr=1924 r=0 w=0
time=72769 us)
  0   TABLE ACCESS FULL PR_MED (cr=0 r=0 w=0 time=0 us)
  0INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=0 r=0 w=0
time=0 us)(object id 51357)
  0   TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=0 r=0 w=0 time=0
us)
  0  NESTED LOOPS  (cr=288120 r=0 w=0 time=89293562 us)
6505525   HASH JOIN

Re: UNIX : script help/input




Thank God for Perl and Mladen ;)

Tanel.

- Original Message - 

  From: 
  Johan Muller 
  
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Wednesday, October 01, 2003 8:24 
  PM
  Subject: Re: UNIX : script 
  help/input
  
  
  Mladen,
  
  It worked! 
  
  Heartfelt thank you from the evangelized perl crowd (now watch the 
  list-owner grin).Mladen Gogala [EMAIL PROTECTED] 
  wrote:
  #!/usr/bin/perl 
-wuse strict;use bytes;my ($NAME,$IP,@LB);while () 
{chomp;@LB=split /\s+/;if ($LB[0] =~ /^name:/i) 
{$NAME=$LB[1];}if ($LB[0] =~ /^address:/i) 
{$IP=$LB[1];write;}}format 
STDOUT=@ 
,@$NAME,$IP

Re: STAT from trace

2003-10-01 Thread Wolfgang Breitling

That's not true. The STAT lines in the 10046 trace have nothing to do with 
parsing. But you must close the cursor for them to be written to the trace. 
If you are using sqlplus, either close the trace or the session, otherwise 
sqlplus keeps the cursor for the most recent sql open.

Here is an exctract from a trace where I execute the same sql twice in a 
row. You can even see the alter session command.

PARSING IN CURSOR #1 len=84 dep=0 uid=31 oct=3 lid=31 tim=926359 
hv=1785040157 ad='313afe4'
select t1.d1, t1.d2, t2.d1, t2.d2
from t1, t2
where t2.fk1=t1.pk1
  and t2.d2 = 499
END OF STMT
PARSE #1:c=200,e=258,p=25,cr=401,cu=6,mis=1,r=0,dep=0,og=4,tim=926361
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=926362
FETCH #1:c=0,e=2,p=2,cr=2,cu=0,mis=0,r=0,dep=0,og=4,tim=926364
STAT #1 id=1 cnt=0 pid=0 pos=0 obj=0 op='HASH JOIN '
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=134295 op='TABLE ACCESS BY INDEX ROWID T2 '
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=134372 op='INDEX RANGE SCAN '
STAT #1 id=4 cnt=0 pid=1 pos=2 obj=134293 op='TABLE ACCESS FULL T1 '
=
PARSING IN CURSOR #1 len=53 dep=0 uid=31 oct=47 lid=31 tim=926366 
hv=3759542639 ad='316aac4'
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
END OF STMT
PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=926367
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=926368
*** 2003-10-01 09:39:52.448
=
PARSING IN CURSOR #1 len=69 dep=0 uid=31 oct=42 lid=31 tim=928350 
hv=589283212 ad='30f70b8'
alter session set events '10046 trace name context forever, level 8'
END OF STMT
PARSE #1:c=2,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=928351
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=928351
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
*** 2003-10-01 09:40:02.682
WAIT #1: nam='SQL*Net message from client' ela= 1021 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
=
PARSING IN CURSOR #1 len=53 dep=0 uid=31 oct=47 lid=31 tim=929375 
hv=3759542639 ad='316aac4'
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
END OF STMT
PARSE #1:c=2,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929376
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
APPNAME mod='01@ c:\tmp\tst.sql' mh=764572216 act='' ah=4029777240
EXEC #1:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=929377
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
=
PARSING IN CURSOR #1 len=84 dep=0 uid=31 oct=3 lid=31 tim=929380 
hv=1785040157 ad='313afe4'
select t1.d1, t1.d2, t2.d1, t2.d2
from t1, t2
where t2.fk1=t1.pk1
  and t2.d2 = 499
END OF STMT
PARSE #1:c=3,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929382
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929383
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #1:c=1,e=1,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=4,tim=929384
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=0 pid=0 pos=0 obj=0 op='HASH JOIN '
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=134295 op='TABLE ACCESS BY INDEX ROWID T2 '
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=134372 op='INDEX RANGE SCAN '
STAT #1 id=4 cnt=0 pid=1 pos=2 obj=134293 op='TABLE ACCESS FULL T1 '
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
=
PARSING IN CURSOR #1 len=53 dep=0 uid=31 oct=47 lid=31 tim=929388 
hv=3759542639 ad='316aac4'
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
END OF STMT
PARSE #1:c=2,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929389
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
EXEC #1:c=2,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=929391
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
*** 2003-10-01 09:40:14.219
WAIT #1: nam='SQL*Net message from client' ela= 1135 p1=1413697536 p2=1 p3=0
=
PARSING IN CURSOR #1 len=57 dep=0 uid=31 oct=42 lid=31 tim=930528 
hv=1975444627 ad='30f59a0'
 alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=2,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=930530
EXEC

RE: UNIX : script help/input




Funny 
.. I am currently sitting in a Perl class, so I can actually read what MG has 
written. I'll be soon Perl-literate ...

Raj

  -Original Message-From: Tanel Poder 
  [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 01, 2003 
  1:35 PMTo: Multiple recipients of list ORACLE-LSubject: 
  Re: UNIX : script help/input
  Thank God for Perl and Mladen ;)
  
  Tanel.
  
  - Original Message - 
  
From: 
Johan 
Muller 
To: Multiple recipients of list ORACLE-L 

Sent: Wednesday, October 01, 2003 8:24 
PM
Subject: Re: UNIX : script 
help/input


Mladen,

It worked! 

Heartfelt thank you from the evangelized perl crowd (now watch the 
list-owner grin).Mladen Gogala [EMAIL PROTECTED] 
wrote:
#!/usr/bin/perl 
  -wuse strict;use bytes;my ($NAME,$IP,@LB);while () 
  {chomp;@LB=split /\s+/;if ($LB[0] =~ /^name:/i) 
  {$NAME=$LB[1];}if ($LB[0] =~ /^address:/i) 
  {$IP=$LB[1];write;}}format 
  STDOUT=@ 
  ,@$NAME,$IP
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: Huge optimization costs with 9.2

2003-10-01 Thread Dave Phillips

Could it be that the optimizer uses different algorithms since 9.2 value
would default max permutations to 2000 instead of the 8 
--
Oracle9i Database Reference

OPTIMIZER_MAX_PERMUTATIONS
Parameter type
 Integer
 
Default value
 If OPTIMIZER_FEATURES_ENABLE is set to 9.0.0 or higher, then 2000 

If OPTIMIZER_FEATURES_ENABLE is set to 8.1.7 or lower, then 8
 
Parameter class
 Dynamic: ALTER SESSION
 
Range of values
 4 to 8


David Phillips
Support DBA
Gasper Corporation
[EMAIL PROTECTED]
BAARF member #30


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

Yes, they are same. Basically I didn't change any parameter after
upgrade.

Guang Mei wrote:
 
 Hi Joan:
 
 Is your hash_area_size parameter the same in both situations?
 
 Guang
 
 -Original Message-
 Joan Hsieh
 Sent: Wednesday, October 01, 2003 11:55 AM
 To: Multiple recipients of list ORACLE-L
 
 Kirti,
 
 I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
 performance is good. After upgrade, one query run time from 2 min to
12
 hours. Of course, I re-analyzed all tables and indexes. The explain
plan
 changed from hash join to nested-loop. All the parameters are same. So
I
 have to put optimized_feature_enable=8.1.7 to make run normal as
usual.
 I hate to disable the new feature, but no choose.
 
 Joan
 
 Kirtikumar Deshpande wrote:
 
  Were tables/indexes anlayzed after the upgrade?
 
  - Kirti
 
  --- Jeff Landers [EMAIL PROTECTED] wrote:
   Hello All
  
   Version  OS:
   Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3.
  
   Problem:
   We've captured the sql text and optimization plans for critical
sql
   prior to upgrading to 9.2.   After  the upgrade we have noticed
   that the cost associated with every sql statement  is now HUGE
   compared to its 9.0.1.4 counterpart.   Per the statistics being
captured
   via traces,
   these statement are noticeably slower per execution.
  
   Anyone experiencing/experienced the same problem with 9.2?
  
   Thank you in advance.
  
  
 
  __
  Do you Yahoo!?
  Yahoo! SiteBuilder - Free, easy-to-use web site design software
  http://sitebuilder.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).
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Joan Hsieh
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Guang Mei
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joan Hsieh
  INET: [EMAIL PROTECTED]

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

RE: RE: vertical serches on a table - how to

2003-10-01 Thread Susan Tay

Stephane,
Fantastic! Appreciate the help.
Thanks!

susan

From: Stephane Faroult [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: RE: vertical serches on a table - how to
Date: Wed, 01 Oct 2003 08:04:31 -0800
Stephane,
Apologize for not being clear on my question.

The query you have provided will only return one
record, ie.
1 mango  banana.

I need two records to be returned:
1  mangobanana
1  grape pineapple

You're right that by 'vertical', I meant filtering
according to conditions
on OTHER rows but at the same time, I want those
filter conditions to be
displayed as well, which in this case - grape and
pineapple.

Any idea.

Thanks.

susan

In that case you keep something looking vaguely like the condition in your 
initial query but add a subquery to check that both conditions are 
satisfied :

select X1.ID, X1.col1, X1.col2
from tableX X1
where  (X1.col1='banana')
or (((X1.col1='mango' and X1.col2='banana')
or (X1.col1='grape' and X1.col2='pineapple'))
   and 2 = (select count(*)
from tableX X2
where ((X2.col1='mango'
   and X2.col2='banana')
   or (X2.col1='grape'
   and X2.col2='pineapple'))
   and X2.ID = X1.ID)));
 No guarantee on the proper number of parentheses.

SF

 Hi,
 I have a table that I would like to perform a
 vertical search on. For eg.

 Table X
 ---
 ID COL1 COL2
 1  apple orange
 1  mango   banana
 1  grape pineapple
 2  mango   banana
 2  guava lemon

 I would like to display records that meet the
 following criteria for *a
 particular ID*.

 (COL1=banana)
 OR
 (COL1=mango and COL2=banana
 AND
 COL1=grape and COL2=pineapple

 The output should be
 ID  COL1COL2
 1   mango   banana
 1   grapepineapple

 It should not display
 ID  COL1COL2
 2   mango   banana

 since ID=2 did not meet the criteria where
 COL1=grape and COL2=pineapple.

 I tried the following SQL but the output is
always
 zero because COL1 can
 never be a mango and a grape and COL2 can never
be
 a
 banana and a pineapple at the same time for a
 particular ID.

 select ID, col1, col2
 from tableX
 where   (col1='banana')
 or  ((col1='mango' and col2='banana')
   and
  (col1='grape' and col2='pineapple')
  );

 Any idea how I can do a vertical search on the
 table.

 Thanks for any help you can provide.

 susan

Susan,

Took me some time to understand what you meant
by 'vertical search'.
ANDs and ORs in a WHERE clause always apply to the
current row under
scrutiny. What you mean by 'vertical' is that you
want to filter according
to conditions on OTHER rows. This is done by a
subquery.

Your query could read

select X1.ID, X1.col1, X1.col2
from tableX X1
where  (X1.col1='banana')
or   ((X1.col1='mango' and X1.col2='banana')
   and EXISTS (select null
 from tableX X2
 where X2.col1='grape'
   and X2.col2='pineapple'
   and X2.ID = X1.ID));

Simplifying to the extreme, each different row you
handle must be returned
by its 'own' query.

Regards,

Stephane Faroult
Oriole
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
_
Frustrated with dial-up? Get high-speed for as low as $29.95/month 
(depending on the local service providers in your area).  
https://broadband.msn.com

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

2003-10-01 Thread Jared Still

Sure, you could do that.

It just doesn't seem like a good way to deal with
the possibility of an index tablespace possibly
having data segments in it when backing up only
data segment tablespaces.

Unless you have *really* large databases with very
generous restore time requirements , I don't see the
 point in trying to do this anyway.

Jared

On Wed, 2003-10-01 at 05:14, Mark Leith wrote:
 Couldn't you do this with a simple:
 
 select owner, table_name
 from all_tables
 where tablespace_name = 'index_tbs';
 
 ?
 
 Or of course use IN for a list of tablespaces?
 
 Or am I missing something?
 
   -Original Message-
   From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
 [EMAIL PROTECTED]
   Sent: 30 September 2003 22:45
   To: Multiple recipients of list ORACLE-L
   Subject: RE: Separate Indexes and Data
 
 
 
   Good question Ian.  If anyone does have a different backup schedule for
 index tbs , I
would be interested to know how they ensure that the index TBS do not
 have any
   data segments in them.
 
   Jared
 
 
 
MacGregor, Ian A. [EMAIL PROTECTED]
 Sent by: [EMAIL PROTECTED]
  09/30/2003 10:34 AM
  Please respond to ORACLE-L
 
 
 To:Multiple recipients of list ORACLE-L
 [EMAIL PROTECTED]
 cc:
 Subject:RE: Separate Indexes and Data
 
 
 
   I'd be very interested to know how many people have their index
 tablespaces on a different backup schedule from their data tablespaces.  If
 so how different?  What happens when a media  failure occurs and you must
 restore from backup?  You would need to have on hand  and apply more redo
 logs to make the database current.
 
   I understand the argument proffered is separating indexes and data can
 mean that when physical corruption of the file happens to an index
 tablespace then all one needs do is to offline, drop, drop and rebuild  the
 index tablespace.  I admit I have not tried off-lining the tablespace first,
 but you cannot normally drop a tablespace which is being used to enforce
 referential integrity.  If off-lining the tablespace first does work, I can
 see someone trying to do the rebuild with the database available and having
 duplicate records in the parent tables and records without parents in the
 child tables.
 
   On the size of the segments:  The paper entitled How To Start
 Defragmenting and Start Living  or something like that strongly advocated
 uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I
 recall.  However the paper
   Never mentioned what to do when an object that used  to fit nicely into
 the 128k extent category now  more properly belongs to the 4M category.  If
 you move the  data, large holes are left in the other tablespace, and while
 this does not impact Oracle performance, it does mean that your physical
 backups are larger than necessary.  I am in the process of migrating from
 uniform to autoallocated extents.  This means extents of different sizes
 share the same tablespace.  The extent sizes being multiples of each other.
 This removes the argument about not having indexes and data in the same
 tablespaces due to their different sizes.
 
   Ian MacGregor
   Stanford Linear Accelerator Center
   [EMAIL PROTECTED]
 
   -Original Message-
   Sent: Monday, September 29, 2003 8:10 AM
   To: Multiple recipients of list ORACLE-L
 
 
   Thomas,
 
   It *is* a good idea to separate index data from heap data into different
 tablespaces. But the reason isn't solely to eliminate I/O competition. Even
 if I/O competition isn't an issue for you (and the OFA Standard doesn't say
 that it will be), then it's *still* a good idea to separate your index data
 from your heap data, for reasons including:
 
   * Index segments have different backup and recovery requirements than
 their corresponding heap segments. For example, as Peter mentioned, if you
 have an index block corruption event, then it's convenient to just offline,
 kill, and rebuild an index tablespace. If the indexes and data are mixed up
 in a single tablespace, this is not an option. Another
   example: If you construct your backup schedule to make media recovery time
 a constant, then you probably don't need to back up your indexes on the same
 schedule as you back up your heaps. But unless they're in different
 tablespaces, this isn't an option either.
 
   * Index segments are usually smaller than their corresponding heap
 segments. Using separate tablespaces allows you to use a smaller extent size
 to conserve disk storage capacity.
 
   I don't think I ever wrote that you need to put indexes and their
 corresponding tables/clusters on separate disks, but you do need to be
   *able* to do that if your I/O rates indicate that you should.
 
   For the original OFA Standard definition, please see section 3 of the
 document called The OFA Standard--Oracle for Open Systems, and section 5
 of Configuring Oracle Server for VLDB,

RE: 8i OCP Net8 Exam

2003-10-01 Thread Faan DeSwardt

Richard,

I agree that the OCP is a laughing matter among experienced DBA's but to those that 
are pressurized by their non-technical management, especially HR, to obtain 
certification for various reasons, there is very little choice than to get it or get 
out...:-(

I can definitely see that certification programs that model the latest 9i OCM will be 
the future for reliable and most importantly, credible certification.  The big dilemma 
with this certification model is limited availability world wide and unacceptable high 
cost when compared to the traditional certification model...guess time will tell which 
model will suffice...perhaps a hybrid of the 2 models?  Will be interesting to follow 
the certification trend from other vendors in the enterprise space e.g. Veritas, EMC, 
etc.

I agree that it is more important to know the correct answer in the real world where 
the rubber meets the road, but as Carry M. stated so elegantly in an earlier post that 
a wise DBA will know several i.e. the answer that the OCP exam expects, the answer 
your manager expects, the answer your customer expects, the answer that is technically 
correct, etc...;-)

-f

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


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


 Dennis,

 Do you have good practice exams?

 I have found that the best preparation for the OCP exams are good practice
exams.

 I have reached the point where I just skim through the material in the
whatever book you use (probably only possible if you have some miles on
the clock as a DBA) and then drive the in depth study from the practice
exams.  This way you are spending your time more focused and find out what
exactly the OCP exam will expect from you vs. what some author would like to
teach you or even what the correct answer is in reality.

 Also, many of these questions in the practice exams will appear in the
actual OCP exam which builds your confidence while writing the actual exam.


It's comments such as these which are unfortunately all so common that
really hits home what an laughable, sad and sorry affair the whole OCP
program really is. I have this vision of people poring over example
questions, desperately trying to memorise as many questions as possible,
desperately trying to forget what is correct is reality for fear of not
getting the required 65% multiple questions correct. Occasionally, they'll
glance at the Inside OCP section of the Oracle Magazine and gain
confidence in getting the jest of the complex concepts (and yes, further
sample questions) it covers in each edition.

And once they've passed and got that precious certificate, they're of course
qualified to look after that banks enterprise database because they're
Oracle approved Oracle Certified *Professionals*. And when the database runs
like a dog, they'll open up their notebooks and decide is it:

A) The Buffer Cache Hit Ratio is less than 90%
B) The Library Cache Hit Ratio is less than 90%
C) The DD Cache Hit Ratio is less than 90%
D) The cleaning lady has accidentally pulled out the wrong plug
E) Something else

Good grief !!

Now I too have spent many years teaching with Oracle Education and I'm
Oracle 7, 8, 8i and 9i OCP (instructors were obliged to get certified) so
I know a fair bit about the process. And I've seen students leave my
classroom with 5 days Oracle experience behind them pass their OCP DBA Admin
exam the following Tuesday (guess I was a good teacher :)

Anyone see a problem ?

At the time I kinda justified it as selling them water in that it doesn't
really harm them, achieves nothing but at least they think it's doing them
some good. Don't get me wrong, the training they received was excellent,
it's the OCP bit that is fluff. But really, at the end of the day, having
such an atrocious so called professional program ends up hurting the
individual as they've paid a lot of money (for the exams) for very little
benefit, it hurts organisations in that there's no *guarantee* of hiring
anything closely resembling an Oracle Professional as the bar is so low it
drags along the ground and really it ends up hurting Oracle Corp. as well.

The *only* thing it does have going for it is that it motivates some people
to getting training and investigating parts of Oracle they may otherwise not
have much to do with.

But I've always thought giving away free David Bowie Cds at training courses
was a better way to go :)

Richard






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

RE: STAT from trace

2003-10-01 Thread Henry Poras

Tried
that. Also queried on open_cursors in a parallel session.

Henry

-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of Daniel FinkSent:
Wednesday, October 01, 2003 1:59 PMTo: Multiple recipients of list
ORACLE-LSubject: Re: STAT from traceHenry,
What happens if you issue another query after the query of interest?
(something like "select 1 from dual;") STATshould be emitted when the
cursor is closed.
Daniel
Henry Poras wrote:
Thanks. I tried both disabling
the trace and quitting from the session. No luck with 10046, just
sql_trace.Henry

-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Jamadagni, Rajendra Sent: Wednesday, October 01, 2003 10:25
AM To: Multiple
recipients of list ORACLE-L Subject: RE: STAT from trace
I get STAT lines no matter how I enable the
trace. Make sure you wither close the session or stop the trace so that
all pending STAT lines will be written to the file.
Raj
-Original Message- From:
Henry Poras [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 01, 2003 9:34 AM
To: Multiple recipients of list ORACLE-L
Subject: STAT from trace
I was creating some trace files yesterday and came across
one of these problems that shows up occasionally
(then I forget about it).
When I ran my query using ALTER SESSION SET
sql_trace=true (sqlplus; 8.1.7.4), I got the STAT
line in the trace and the associated 'row source' information after running tkprof.
When I ran my query using ALTER SESSION SET EVENTS '10046
trace name context forever, level 8' (or with
dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive
queries.
Everything else was identical.
What's up?
Henry

RE: Huge optimization costs with 9.2

2003-10-01 Thread Govind.Arumugam

Joan,

Can you post the query in question?

Thanks,
GOvind

-Original Message-
Joan Hsieh
Sent: Wednesday, October 01, 2003 2:00 PM
To: Multiple recipients of list ORACLE-L


Malden,

They all same. I didn't change any parameters after upgrade. The
difference in the plans are one used all hash join vs nested loop to
join tables. The histograms are all same. db_file_multiblock_read_count
is 8, sort_area_size is 1mb, hash_area_size is 40096.
optimize_index_caching and optimizer_index_cost is 0/100 all same as 8i.
The interesting part is after I changed optimize_feature_enable to
8.1.7. They produced the same explain plan.

Joan

Mladen Gogala wrote:
 
 Joan, what is the difference in the plans? What specific feature
 made the difference? Are the values of
 optimizer_index_cost_adj and optimizer_index_caching same on both
 versions? How about histograms? What is with
 db_file_multiblock_read_count,sort_area_size and hash_area_size? Is
 everything same as in 8i? May be setting of those parameters can be
 tweaked to your benefit?
 
 On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote:
  Kirti,
 
  I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
  performance is good. After upgrade, one query run time from 2 min to 12
  hours. Of course, I re-analyzed all tables and indexes. The explain plan
  changed from hash join to nested-loop. All the parameters are same. So I
  have to put optimized_feature_enable=8.1.7 to make run normal as usual.
  I hate to disable the new feature, but no choose.
 
  Joan
 
  Kirtikumar Deshpande wrote:
  
   Were tables/indexes anlayzed after the upgrade?
  
   - Kirti
  
   --- Jeff Landers [EMAIL PROTECTED] wrote:
Hello All
   
Version  OS:
Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3.
   
Problem:
We've captured the sql text and optimization plans for critical sql
prior to upgrading to 9.2.   After  the upgrade we have noticed
that the cost associated with every sql statement  is now HUGE
compared to its 9.0.1.4 counterpart.   Per the statistics being captured
via traces,
these statement are noticeably slower per execution.
   
Anyone experiencing/experienced the same problem with 9.2?
   
Thank you in advance.
   
   
  
   __
   Do you Yahoo!?
   Yahoo! SiteBuilder - Free, easy-to-use web site design software
   http://sitebuilder.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).
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
 
 Note:
 This message is for the named person's use only.  It may contain confidential, 
 proprietary or legally privileged information.  No confidentiality or privilege is 
 waived or lost by any mistransmission.  If you receive this message in error, please 
 immediately delete it and all copies of it from your system, destroy any hard copies 
 of it and notify the sender.  You must not, directly or indirectly, use, disclose, 
 distribute, print, or copy any part of this message if you are not the intended 
 recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to 
 monitor all e-mail communications through its networks.
 Any views expressed in this message are those of the individual sender, except where 
 the message states otherwise and the sender is authorized to state them to be the 
 views of any such entity.
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Mladen Gogala
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joan Hsieh
  INET: [EMAIL PROTECTED]

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

RE: COBOL TO ORACLE

2003-10-01 Thread babette.turnerunderwood

Then perhaps you can help me . . .

We are suffering through a Pro*Cobol / Oracle on the mainframe implementation for a 
newly developed in house application.

We have a mainframe with 1.7GB - 2GB REAL memory. 
4 CPU machine - we have two logical CPUs in our LPAR
We have 8 instances running (each with at least 300MB SGA)
We are having major performance problems when people start doing a lot of work on 
these instances.

The Mainframe SYSADMIN insists that there is NO swapping, NO paging occuring. There is 
no problem because each instance can have 2GB VIRTUAL memory and this is fine and 
dandy because this is how the mainframe works.

Did you ever use Oracle on a mainframe? 
Did you come up with guidelines for minimum requirements?
(like in the UNIX install guides / NT install guides, you need X RAM, etc)

Thanks
Babette

-Original Message-
Sent: 2003-09-30 5:15 PM
To: Multiple recipients of list ORACLE-L


On Tue, 2003-09-30 at 16:19, Stephane Paquette wrote:
 Like Thomas Day said, Oracle is an rdbms and COBOL a programming language.
 

COBOL *** WAS *** a programming language. Horse *** WAS *** basis of
transport. You should have used past tense, Stephane. I'm not really
that partial when it comes to horses, but having suffered COBOL, I would
really leave it in the ancient past, together with Spanish Inquisition
and crucifiction as a viable capital punishment.





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

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

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

2003-10-01 Thread Jared Still


:)

On Wed, 2003-10-01 at 10:24, Johan Muller wrote:
 Mladen,
  
 It worked! 
  
 Heartfelt thank you from the evangelized perl crowd (now watch the list-owner grin).
 
 Mladen Gogala [EMAIL PROTECTED] wrote:
 #!/usr/bin/perl -w
 use strict;
 use bytes;
 my ($NAME,$IP,@LB);
 while () {
 chomp;
 @LB=split /\s+/;
 if ($LB[0] =~ /^name:/i) {
 $NAME=$LB[1];
 }
 if ($LB[0] =~ /^address:/i) {
 $IP=$LB[1];
 write;
 }
 }
 format STDOUT=
 @ ,@
 $NAME,$IP


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

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

RE: COBOL TO ORACLE