RE: session_cached_cursors parameter

[Steve Adams]

Hi Jonathan,

It actually caches the whole instantiation object, not the just the library cache lock 
(which serves as the pointer to
the shared cursor handle in the SGA). That means that it not only saves locating and 
locking the library cache object
again, but it also saves extra instantiation calls as well. Also because the KGL lock 
is retained, there is no
possibility of the shared cursor being aged out and thus not available on a subsequent 
session cursor cache hit.

The memory cost can be non-trivial. I think it is about 240 bytes per populated slot 
per session.

Although you are right that it is searched linearly, my experience suggests that the 
CPU saving of session cursor cache
hits can exceed the CPU cost of unlatched linear searches with values much larger than 
100.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/


-Original Message-
Sent: Saturday, 6 October 2001 4:57
To: Multiple recipients of list ORACLE-L



Since no-one's answered this, I'll propose my theory,
although I'm not sure it's right.

The session_cached_cursors is just an array in the
UGA (session memory), which means it will be stored
in the SGA when using the multi-threaded server.

The function of the cache is to maintain pointers
into the shared pool so that on reuse of a closed
cursor, Oracle has the option of finding the cursor
very rapidly if it is still available, rather than going
through all the processing (and particularly latching)
needed to find if the 'new' SQL is sharable.

I believe the cost is actually a very small increment
in memory, plus a CPU cost for scanning the array,
which means that an array size over about 100 may
put you in the position of losing more CPU than you
would otherwise save.




Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 03 October 2001 22:03


Does anyboby know how this parameter really works?

Does it use PGA memory to cache parsed statements or
it uses SHARED_POOL memory for that.

If the case is the first one, imagine that cursor1 is
flushed out from the Shared Pool, and the session A
has it cached (in its PGA I assume), then, in order to
use it again, what must the session A do?
(it has to place the cursor into the shared pool again
and do a soft parse or that would not be
necessary...??)

thanks


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

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

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

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

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

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

RE: Intermedia Performance Benchmarks anyone ?

[Jack C. Applewhite]

Mario,

Yes.

In 8.1.6.0.0 on Win2k you can't use the SCORE() operator to return the
actual document score produced by the CONTAINS operator when the query is
executed against a Union View.  That prevents us from ordering a result set
by the Score() when we query our 6 Month View - a minor annoyance.

However, SCORE() works just fine against interMedia Text indexed tables -
either "regular" or partitioned.

That's the only other real bug I can remember that we've run up against.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Friday, October 05, 2001 11:23 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]


Excellent doc.

I just wonder if there are only cache related bugs on using interMedia.

Mario Alberto Ramos


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

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

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

RE: Droping System User

[Jacques Kilchoer]

Title: RE: Droping System User





Very good! :-) I remember when I was in school my brother and I had discussed writing a french version of COBOL, e.g.
AJOUTEZ 1 A X DONNANT Y.


Here's another cool Oracle error message for you:
SQL> drop table sys.tab$ ;
drop table sys.tab$
   *
ERREUR à la ligne 1 :
ORA-00701: Objet nécess. pour le démarrage à chaud de la BDD ne peut pas être
modifié


I like to install Oracle in a foreign language, it's entertaining. I have the 8.0.6 client in Italian:


SQL> connect jrk/password@no_such_db
ERRORE:
ORA-12154: TNS: non è stato possibile risolvere il nome del servizio.
Attenzione: Collegamento a ORACLE interrotto.


-Original Message-
From: Guy Hammond [mailto:[EMAIL PROTECTED]]


You know, I would have thought that if Oracle was going to give you messages in French, that you would be able to write SQL in French too, like 

 
SLdI> effacement de sys.user$ où name='SYS'; 
SLdI> commettez;
 
(SQL is "Structuré Langage d'interrogation" in French :0) )

RE: Intermedia Performance Benchmarks anyone ?

[Mario Alberto Ramos Arellano]

Excellent doc.

I just wonder if there are only cache related bugs on using interMedia.

Mario Alberto Ramos

>>> [EMAIL PROTECTED] 04/10/01 16:47 >>>
Martin,

We use interMedia Text to index and query up to about 10-15 million CLOB
documents (up to 5KB each).  We're on 8.1.6.0.0 under Win2k - 2 550MHz CPUs,
2GB RAM, 18 36GB drives.

Because a domain index cannot be partitioned, we have the documents spread
across 5 tables (on 6 drives).  One is a 2 partition table (each partition
on its own drive) containing the current two months of docs, the other 4
hold the 4 prior months' docs.  We can query the entire 6 months of docs via
a Union View on them - even Contains() queries work fine on this view.

When we add a new month's partition, the prior month's partition gets turned
into a table (segment exchange).  The interMedia Text indexes on the
partitioned table and the new prior month are rebuilt.

Lately we've been getting about 3.5 million docs/month and the index rebuild
takes about 7 hours - that's 7 hrs. for the index on the prior month and 7
more hours for the index on the partitioned table, which only contains one
month of docs at that point.

Since we're adding docs every day, we sync the interMedia index every
morning.  Last night we added about 200,000 docs and it took about 3 hours
for the index to resync.  We don't use ctxsrv, but use CTX_DDL.Sync_Index.

When we get over about 4.5 million docs in a table, the resync really slows
down.  The in-memory part still happens at about 150 docs/sec, but when
interMedia writes to disk it slows down a bunch.  What took 3 hours today
will take 10 hours in a couple of weeks.

That's why I plan on spreading the DR$<>$I segment across multiple drives by
spreading the datafiles of its tablespace across those drives.

BTW, that brings up some performance points - be sure you cache the DR$<>$R
segment (use CACHE not CACHE READS, due to bugs in Oracle):

  Alter Table DR$$R Modify LOB (Data) (Cache) ;

Also ensure that your LOBs are out-of-line and stored in their own
segment(s) on drive(s) separate from the "regular" data.  Make sure that
your I_TABLE_CLAUSE, R_TABLE_CLAUSE, and I_INDEX_CLAUSE all specify
tablespaces on their own drives to spread the I/O out even further.  We're
getting 2GB more RAM on a new server, so I plan on caching the 900MB DR$<>$X
segment, which is the index on the DR$<>$I token table.

I've learned a lot about how interMedia Text processes different kinds of
queries by watching disk I/O on Win2k's Performance Monitor while I issue
various "flavors".  Our folks use lots of complex query terms with heavy use
of the Stemmer.  I've gotten them to switch from using tons of ORs to using
the Equivalence operator and we're getting much better results using NEAR
than simple ANDs.  Performance is very good, with CONTAINS queries returning
results in less than a second for terms that are rare in the docs, up to a
minute for terms that are common in lots (e.g. hundreds of thousands) of
docs.

If you're going to do synonym searches, you'd better start looking for a
good thesaurus - the one Oracle ships is pretty limited.  We've not found a
good one for the technical lingo our docs contain, so we don't do ABOUT
queries at this time.

Get familiar with CTX_Query.Explain, it will help you understand things like
what the Stemmer *really* does and how complex queries are parsed.

Hope this helps.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com 
[EMAIL PROTECTED] 
(512)327-9068


-Original Message-
Kendall
Sent: Thursday, October 04, 2001 10:00 AM
To: Multiple recipients of list ORACLE-L


Hello all,

Although I have installed Intermedia as part of my general DBA duties before
I have not experienced any particular requirements on throughput rate or
indexing.

I need some information on being able to deal with large volumes of product
data (e.g. 1 million products in a retail application) and be able to
perform 'intelligent' searches against the metadata (things like
typographical error matching, synonyms etc.) as well as the more usual
parametric search (i.e. advanced search page with lots of metadata specific
fields).

Indexing time and max throughput are also of interest.

Any data based on experience would be appreciated.

Thanks

Martin

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

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

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

RE: Output to Excel

[Mario Alberto Ramos Arellano]

Ramon,

I wrote this little but useful procedure. 
You need to change your table fields only.

HTH
---
procedure test as

fp utl_file.file_type;

cursor c_tabla is
select * from my_table;

v_output_buffer varchar2(200);

begin
fp := utl_file.fopen( '/pdsi/dsk1','t.out','w' );

for x in c_tabla loop

v_output_buffer := x.field1  || '|' || x.field2 || '|' || etc ;

UTL_FILE.PUT_LINE(FP, V_OUTPUT_BUFFER);

end loop;

dbms_output.put_line(' Ya acabe!! ');
utl_file.fclose_all();

exception

WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Ruta erronea');
WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Modo invalido');

WHEN OTHERS THEN

dbms_output.put_line ( ' Otras excepciones y errores: ' || sqlcode || ' - ' || sqlerrm 
);

utl_file.fclose_all();
end;
-

Mario Alberto Ramos

SAlu2
>>> [EMAIL PROTECTED] 04/10/01 22:45 >>>
 FYI:  If you use PL/SQL Developer you can save results sets right as CSV,
really sweet.  I use PL/SQL Developer for 90% of what I do.

And it is $50-$150 / license depending on how many you get, i would compare
it to toad ($750) or navigator ($5,000) any day of the week.

(Sorry quest people).

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 10/4/01 6:10 PM

Hi Jared,

Excuse me for contact you directly not throug the list.

The past week you post an answer to someone trying to write to excel,
I took the example to generate a file comma separated, but getting an
error.

 CREATE OR REPLACE PACKAGE BODY PROCESAR_AGENCIAS AS
 PROCEDURE GENERAR_FACTURAS
  ( PGRUPO IN NUMBER,PCOMPANIAIN NUMBER,
PFECHA_INICIAL IN DATE,  PFECHA_FINAL IN DATE,
PAGENCIA   IN NUMBER ) AS
 CURSOR C_FACTURAS IS
   SELECT F.GRUPO||','||
  F.COMPANIA||','||
  F.TIPO_FACTURA||','||
  F.AGENCIA||','||
  F.FACTURA||','||
  F.CLIENTE||','||
  F.VENDEDOR||','||
  F.DOCUMENTO_COBRO||','||
  F.FECHA||','||
  F.FECHA_PAGO||','||
  F.FECHA_VENCIMIENTO||','||
  F.ESTATUS_COMISION||','||
  F.COMISION_VENDEDOR||','||
  F.MONTO||','||
  F.MONTO_PAGADO||','||
  F.IMPRESA||','
   FROM FACTURAS F
  WHERE
   F.GRUPO   = PGRUPO AND
   F.COMPANIA= PCOMPANIA  AND
   F.AGENCIA = PAGENCIA   AND
   F.FECHA BETWEEN PFECHA_INICIAL AND PFECHA_FINAL;
 CURSOR C_ITEM_FACTURAS IS
   SELECT I.GRUPO||','||
  I.COMPANIA||','||
  I.AGENCIA||','||
  I.TIPO_FACTURA||','||
  I.FACTURA||','||
  I.LOCALIDAD||','||
  I.ARTICULO||','||
  I.SECUENCIA||','||
  I.COSTO||','||
  I.PRECIO_VENTA||','||
  I.CANTIDAD||','||
  I.ITBIS||','||
  I.DESCTO||','
   FROM FACTURAS F, ITEM_FACTURAS I
  WHERE
   F.GRUPO = PGRUPO AND
   F.COMPANIA  = PCOMPANIA  AND
   F.AGENCIA   = PAGENCIA   AND
   F.FECHA BETWEEN   PFECHA_INICIAL AND
 PFECHA_FINAL   AND
   I.GRUPO = F.GRUPOAND
   I.COMPANIA  = F.COMPANIA AND
   I.TIPO_FACTURA  = F.TIPO_FACTURA AND
   I.FACTURA   = F.FACTURA;
   V_ARCHIVO UTL_FILE.FILE_TYPE;
   REGISTRO FACTURAS%ROWTYPE;  * I declare it here
   OUT_REC TYPE REGISTRO;  *

 BEGIN
 -- Loop para el archivo de Facturas
   V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','FACTURAS.TXT', 'W');
   FOR FT IN C_FACTURAS
 LOOP
   UTL_FILE.PUT_LINE(V_ARCHIVO, FT.OUT_REC);
 END LOOP;
   UTL_FILE.FCLOSE(V_ARCHIVO);
 -- Loop para el archivo de Item Facturas
   V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','ITEM_FACTURAS.TXT', 'W');
   FOR IFT IN C_ITEM_FACTURAS
 LOOP
   UTL_FILE.PUT_LINE(V_ARCHIVO, IFT.OUT_REC);
 END LOOP;
   UTL_FILE.FCLOSE(V_ARCHIVO);
 END GENERAR_FACTURAS;
 END PROCESAR_AGENCIAS;
/



PL/SQL: Statement ignored
PLS-00302: component 'OUT_REC' must be declared
PL/SQL: Statement ignored
PLS-00302: component 'OUT_REC' must be declared

How should I Declare it.

I did 

 REGISTRO FACTURAS%ROWTYPE;
 OUT_REC TYPE REGISTRO;

Is there something missing ?


Any help !!

Thanks in Advance,



Ramon E. Estevez
[EMAIL PROTECTED] 
Dominican Republic
809-565-3121



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

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

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

RE: perplexing plan?

[Connor McDonald]

IMHO, some hints are good, some are bad.  For example,
hints like ORDERED and FIRST_ROWS are still giving the
optimizer some "room to move" should newer features
come along later...

If you use (say) INDEX(table,index), then you are
eliminating the benefit of possible future indexes,
and even worse, if you drop or rename the index, then
the hint silently gets ignored.

hth
connor

 --- "Koivu, Lisa" <[EMAIL PROTECTED]> wrote:
> Remco, why do you say don't use hints unless you
> really have to?  
> 
> Lisa Koivu
> Oracle Database Administrator
> Fairfield Resorts, Inc.
> 954-935-4117
> 
> 
> > -Original Message-
> > From:   Doug C [SMTP:[EMAIL PROTECTED]]
> > Sent:   Friday, October 05, 2001 1:51 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject:Re: perplexing plan? 
> > 
> > This was a vendor designed query.   To my knowlege
> - it is complete.   As
> > to the
> > performance problem - it was apparently caused by
> a "tool" that was
> > suppossed to
> > analyze statistics on the some of the associated
> tables and didn't.   It
> > was
> > rectified, and now the query screams.   I'm am
> somewhat anxious to see the
> > new
> > trace of it however.  I'll probably post it
> shortly.
> > 
> > 
> > 
> > 
> > On Fri, 05 Oct 2001 08:20:21 -0800, you wrote:
> > 
> > >Doug,
> > >
> > >Sorting is caused by the "distinct", and is
> probably the cause of your
> > >performance problem. Try to limit the sorting to
> a minimal number of
> > rows,
> > >e.g. by creating a temp table containing all
> (including the multiple
> > copies)
> > >rows and then select the distinct values of that
> table. You could also
> > try :
> > >select distinct * from (select ..) to replace
> the select distinct.
> > >
> > >Another tip: don't you hints unless you really
> have to ...
> > >
> > >HTH,  Remco
> > >
> > >-Oorspronkelijk bericht-
> > >Van: Doug C [mailto:[EMAIL PROTECTED]]
> > >Verzonden: vrijdag 5 oktober 2001 16:30
> > >Aan: Multiple recipients of list ORACLE-L
> > >Onderwerp: perplexing plan? 
> > >
> > >
> > >I'm a little perplexed by this query and it's
> associated plan.  It's also
> > a
> > >big
> > >performance problem.  The problem is the 35
> million row table clearly.
> > But
> > >looking at the plan at the bottom, I'm not sure
> where the sorting is
> > going
> > >on.
> > >Would anyone say the index full scan on the 35
> million row table is being
> > >sorted?  Or does it look more like it's being fed
> to a nested loops
> > query?
> > >
> > >Thanks,
> > >Doug
> > >
> > >SELECT /*+ ORDERED INDEX(S_ S15_IX1)
> INDEX(BUS_FID F15_UK1) INDEX(STREET
> > >A15_IX1) */  SDE.STREET.CFCC,  SDE.STREET.BUS_FID
>  ,S_.eminx,S_.eminy,
> > >  S_.emaxx,S_.emaxy,
> BUS_FID.fid,BUS_FID.numofpts,BUS_FID.entity,
> > >  BUS_FID.points,BUS_FID.rowid
> > >FROM
> > > (SELECT /*+ INDEX(SP_ S15_IX1) */ DISTINCT
> > sp_fid,eminx,eminy,emaxx,emaxy
> > >  FROM SDE.S15 SP_   WHERE SP_.gx >= :1 AND
> SP_.gx <= :2 AND SP_.gy >= :3
> > >  AND SP_.gy <= :4 AND SP_.eminx <= :5 AND
> SP_.eminy <= :6 AND SP_.emaxx
> > >=
> > >:7
> > >  AND SP_.emaxy >= :8) S_
> > >,  SDE.STREET
> > > , SDE.F15 BUS_FID 
> > >WHERE S_.sp_fid =  BUS_FID.fid 
> > >AND S_.sp_fid = SDE.STREET.BUS_FID
> > >
> > >
> > >call count   cpuelapsed   disk   
>   querycurrent
> > >rows
> > >--- --   -- --
> -- --
> > >--
> > >Parse1  0.00   0.00  0   
>   0  0
> > >0
> > >Execute  1  0.00   0.00  0   
>   0  0
> > >0
> > >Fetch   45473.15 475.04 223532  
> 66153503  0
> > >4494
> > >--- --   -- --
> -- --
> > >--
> > >total   47473.15 475.04 223532  
> 66153503  0
> > >4494
> > >
> > >Misses in library cache during parse: 1
> > >Optimizer goal: CHOOSE
> > >Parsing user id: 20
> > >
> > >Rows Row Source Operation
> > >--- 
> ---
> > >   4494  HASH JOIN
> > >   4494NESTED LOOPS
> > >   4495  VIEW
> > >   4495 SORT UNIQUE
> > >   4817   INDEX RANGE SCAN (object id
> 7356)
> > >   4494  TABLE ACCESS BY INDEX ROWID STREET
> > >   8988INDEX UNIQUE SCAN (object id
> 7355)
> > >33065402  TABLE ACCESS BY INDEX ROWID F15
> > >33065403INDEX FULL SCAN (object id 7283)
> > >-- 
> > >Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > >-- 
> > >Author: Doug C
> > >  INET: [EMAIL PROTECTED]
> > >
> > >Fat City Network Services-- (858) 538-5051 
> FAX: (858) 538-5051
> > >San Diego, California-- Public Internet
> access / Mailing Lists
> >
>
>
> > >To REMOVE yourself from this mailing list, send
> an E-Mail message
> > >to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> > >the message BODY, include a line

RE: Oracle 8.1.7 on Solaris vs. Linux

[Gogala, Mladen]

So do I, Jared, so do I.

> -Original Message-
> From: Jared Still [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 05, 2001 11:40 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Oracle 8.1.7 on Solaris vs. Linux
> 
> 
> 
> Yeah, I've used AIX.  I prefer Linux, thank you.  :)
> 
> Jared
> 
> On Thursday 04 October 2001 14:35, Gogala, Mladen wrote:
> > The problem is that Oracle supports Linux on Intel
> > and IBM does not make RS/6000 with Intel CPU. I''m also
> > not sure whether there is a sufficient customer base for
> > Oracle to start supporting the PPC Linux. On the other hand,
> > there is a Unix-like OS called AIX which does support Oracle.
> >
> > > -Original Message-
> > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> > > Sent: Thursday, October 04, 2001 2:25 PM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: Re: Oracle 8.1.7 on Solaris vs. Linux
> > >
> > >
> > >
> > >
> > > I don't have experience with Linux on RS/6000, but I would sure
> > > like to give it a try.
> > >
> > > An RS/6000 is a much faster box than a more expensive Sun box.
> > >
> > > Since IBM supports Linux on their HW, and Oracle supports
> > > their RDBMS on Linux, I would jump at the chance for this 
> combination.
> > >
> > > Jared
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Lord, David -
> > >
> > > C&S" To: Multiple
> > > recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > >  > >
> > > yscsg.com>   Subject: Oracle
> > > 8.1.7 on Solaris vs. Linux
> > > Sent by:
> > >
> > > [EMAIL PROTECTED]
> > >
> > > om
> > >
> > >
> > >
> > >
> > >
> > > 10/04/01 10:05
> > >
> > > AM
> > >
> > > Please respond
> > >
> > > to ORACLE-L
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > List
> > >
> > > Does anyone have any strong opinions or useful reference on the
> > > performance,
> > > reliability and scalability of Oracle (probably 8.1.7) on
> > > Linux (SuSE?) as
> > > against Solaris?
> > >
> > > We're spec'ing up some kit for a new production d/b and 
> the cost of
> > > upgrading one of our existing Suns is so high that it would
> > > probably be
> > > cheaper to buy a new Linux box (I would guess an IBM).  The
> > > database is
> > > going to be around 20Gb & we were looking at ~4 processors
> > > and ~2Gb RAM.
> > >
> > > Regards
> > > David Lord
> > > Senior DBA, Hays Consulting & Solutions
> > >
> > > email: [EMAIL PROTECTED]
> > > Tel..: +44 (0)29 2054 4013
> > > Fax..: +44 (0)29 2069 2464
> > >
> > >
> > >
> > > 
> **
> > > This message (including any attachments) is confidential 
> and may be
> > > legally privileged.  If you are not the intended 
> recipient, you should
> > > not disclose, copy or use any part of it - please delete 
> all copies
> > > immediately and notify the Hays Group Email Helpdesk at
> > > [EMAIL PROTECTED]
> > > Any information, statements or opinions contained in this message
> > > (including any attachments) are given by the author.  They are not
> > > given on behalf of Hays unless subsequently confirmed by 
> an individual
> > > other than the author who is duly authorised to represent Hays.
> > >
> > > A member of the Hays plc group of companies.
> > > Hays plc is registered in England and Wales number 2150950.
> > > Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.
> > > 
> **
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Lord, David - C&S
> > >   INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services-- (858) 538-5051  FAX: 
> (858) 538-5051
> > > San Diego, California-- Public Internet access / 
> Mailing Lists
> > > 
> 
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of 
> 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed 
> from).  You may
> > > also send the HELP command for other information (like 
> subscribing).
> > >
> > >
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author:
> > >   INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services-- (858) 538-5051  FAX: 
> (858) 538-5051
> > > San Diego, California-- Public Internet access / 
> Mailing Lists
> > > 
> 
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of

RE: CTAS use of rollback

[Christopher Spence]

Not exactly.  The select statement can if there are concurrent transactions
and needs for read consistency like any other select statement.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, October 05, 2001 5:00 PM
To: Multiple recipients of list ORACLE-L

Confirmed.

"Nicoll, Iain (Calanais)" wrote:

> Could anyone tell me whether Create table .. as select .. uses rollback.
>
> I initially thought it would (despite being a cross between ddl and dml)
but
> having created a 3.5 million row table and checked the sum of the writes
in
> v$rollstat it had only done ~130k writes between the start of the ctas and
> the end.  It also doesn't create the table initially but just has a
numbered
> object which it seems to rename only at the very end, so if it fails I
would
> have though it would just drop that object and if it completes
successfully
> then a commit would be done because of the ddl aspects of the command.
>
> I tried inserting 10k rows into the same table and this came back with
about
> 25k writes (seemed reasonable if it's only storing the rowid).  Given this
> it doesn't seem to be using rollback (other than recording changes to
> extents etc) but I'd appreciate confirmation.
>
> Iain Nicoll
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Nicoll, Iain (Calanais)
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

--
Thanks,

Yosi


-
Yosi Greenfield
Oracle Certified DBA
[EMAIL PROTECTED]


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

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

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

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

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

Re: Please Help !!!

[Mohammad Rafiq]

Try to drop this index and rebuild it. Rebuilding of bitmap index on a table 
having parallel degree > 1 was having problem in the past. Just try it , it 
might resolve your problem.

Regards

MOHAMMAD RAFIQ



Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Fri, 05 Oct 2001 12:55:58 -0800

RE: perplexing plan?Hi List,


Env :Oracle 8.0.5, HP/UX 11

Action : Tried to rebuild a corrupt partitioned index on a partitioned 
table, partitioned on date, having 230 mil rows.

Problem : The db is continuously generating ORA 600-2126 followed by 
ORA600-4137 and ending in Db crash.  Metalink has not been heplful. All the 
Rollback extents are showing negative nos.

Now I cant get the db to run with Oracle Express.  Even trying to login to 
Express form, crashes the db with the above said ORA600.  Oracle Support has 
asked to rebuild the db.

Is there any other way to do this ??

TIA

Srini


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

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

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

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

RE: getting password request on connect internal

[Miller, Jay]

Well, we ended up rebooting the server and we were then able to start up,
shut down, and start up the instance.  Weird.

-Original Message-
Sent: Friday, October 05, 2001 1:10 PM
To: Multiple recipients of list ORACLE-L


Yep.  In fact I had no problem connecting to another instance running on the
same machine.  And they've been running (with a nightly shutdown) with no
problems for the last 2 weeks.

Jay Miller

-Original Message-
Sent: Friday, October 05, 2001 11:56 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]


are you part of the dba, oinstall group?

>>> [EMAIL PROTECTED] 10/05/01 11:43 AM >>>
I know this was discussed recently but I just did a major clean up of my
1,000+ unread posts so my apologies.

All of a sudden I'm unable to shutdown a database.  This is the alert_log:

Shutting down instance (immediate)
License high water mark = 21
Thu Oct  4 20:30:38 2001
SHUTDOWN: waiting for active calls to complete.



And when I try to connect internal to do a shutdown abort I get this:


Oracle Server Manager Release 3.1.6.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

ORA-03113: end-of-file on communication channel

SVRMGR> connect internal
Password:


I've already gone through every step  in the Oracle Note 69642.1 (Checklist
for Resolving CONNECT INTERNAL PASSWORD Issues) without any results.  Any
ideas?

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

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

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

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

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

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

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

RE: perplexing plan?

[Koivu, Lisa]

Title: RE: perplexing plan? 





I agree with you Don that hints are not always warranted - yes, on a small dev system any execution plan is going to be out of whack anyway.  

But avoiding them in general, I guess with performance problems hints have been the best quick fix, always.  Fresh stats, sometimes.  A missing index, sometimes.  A correct hint 70% of the time rendered a query that screamed.   

Just my .02


Lisa Koivu
Oracle Database Administrator and Terrible Perl Programmer
Fairfield Resorts, Inc.
954-935-4117


"The line ++@_[0] is a cute example of why nonprogrammers think Perl is obscure.  Try showing that line to your grandmother and explaining that you write stuff like that for a living."  -- Perl Black Book


-Original Message-
From:   Don Granaman [SMTP:[EMAIL PROTECTED]]
Sent:   Friday, October 05, 2001 4:36 PM
To: Multiple recipients of list ORACLE-L
Subject:    Re: perplexing plan? 


From a general perspective, I have to agree with Remco.  If hints are over-used, they may be "optimal" only for a specific release of Oracle and/or a narrow range of data volumes and/or in a specific configuration.  Often, they eliminate any other tuning possibilities - create a new index and the application ignores it, the hint is great for 1000 records but is abysmal for 10,000,000, or  partition the data to improve performance and the app still can't take advantage, etc.  This is not to say that hints shouldn't be used, but other factors - like the level of your influence over the developers to change them as need arises - weigh in the determination.

 
As an example, I once spent over six months trying to get an out-sourced application changed to remove a few very harmful hints.  The contract developers had determined, on their small test system, that forcing an access-path sort by use of an otherwise wildly inappropriate index hint and a stopkey was more efficient than using an order by clause.  In production, the reverse was true - the hint caused each submission of one of these statements CPU utilization of 72x and I/O of 6200x of what the order by did.  This was for a query that was submitted by their application 50,000 - 100,000 times a day!  Guess who was constantly called upon to "tune the database" to fix this nightmare...

 
-Don Granaman
[OraSaurus - Honk if you remember UFI!]
 
- Original Message - 


From: Koivu, Lisa  
To: Multiple recipients of list ORACLE-L  
Sent: Friday, October 05, 2001 1:30 PM
Subject: RE: perplexing plan? 



Remco, why do you say don't use hints unless you really have to?  


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

Please Help !!!

[VeniVas]

Title: RE: perplexing plan?





Hi List,
 
 
Env :Oracle 8.0.5, HP/UX 11
 

Action : Tried to rebuild a corrupt partitioned 
index on a partitioned table, partitioned on date, having 230 mil 
rows.
 
Problem : The db is continuously generating ORA 
600-2126 followed by ORA600-4137 and ending in Db crash.  Metalink has not 
been heplful. All the Rollback extents are showing negative nos.
 
Now I cant get the db to run with Oracle 
Express.  Even trying to login to Express form, crashes the db with the 
above said ORA600.  Oracle Support has asked to rebuild the 
db.
 
Is there any other way to do this ??
 
TIA
 
Srini

Re: not enough ITL slots for parallel DML operation

[Jonathan Lewis]

The only time this would be relevant is with
parallel update/delete - in which case you have
to be updating a partitioned table.

For this to take place, each PX slave addresses
a single partition, so the question doesn't apply
to the table partitions or the locally partitioned
indexes.  To cater for global, or globally
partitioned indexes, a very special restriction
comes in - the degree of parallelism is limited
to the smallest value of INITRANS that Oracle
finds on any of the relevant global/globally
partitioned indexes - to address exactly the
question that you have raised.


Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 05 October 2001 20:20


|Someone asked me, "What happens in a parallel DML operation if there
are not
|enough ITL slots defined for a particular block?" I'm not sure. Can
someone
|shed some light?
|
|Thanx,
|
|Alan Martin
|Defense Logistics Information Service
|Battle Creek, Michigan
|

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

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

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

Re: CTAS use of rollback

[Yosi Greenfield]

Confirmed.

"Nicoll, Iain (Calanais)" wrote:

> Could anyone tell me whether Create table .. as select .. uses rollback.
>
> I initially thought it would (despite being a cross between ddl and dml) but
> having created a 3.5 million row table and checked the sum of the writes in
> v$rollstat it had only done ~130k writes between the start of the ctas and
> the end.  It also doesn't create the table initially but just has a numbered
> object which it seems to rename only at the very end, so if it fails I would
> have though it would just drop that object and if it completes successfully
> then a commit would be done because of the ddl aspects of the command.
>
> I tried inserting 10k rows into the same table and this came back with about
> 25k writes (seemed reasonable if it's only storing the rowid).  Given this
> it doesn't seem to be using rollback (other than recording changes to
> extents etc) but I'd appreciate confirmation.
>
> Iain Nicoll
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Nicoll, Iain (Calanais)
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

--
Thanks,

Yosi


-
Yosi Greenfield
Oracle Certified DBA
[EMAIL PROTECTED]


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

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

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

Re: perplexing plan?

[Don Granaman]

Title: RE: perplexing plan?



From a general perspective, I have to agree with 
Remco.  If hints are over-used, they may be "optimal" only for a specific 
release of Oracle and/or a narrow range of data volumes and/or in a specific 
configuration.  Often, they eliminate any other tuning possibilities - 
create a new index and the application ignores it, the hint is great for 1000 
records but is abysmal for 10,000,000, or  partition the data to improve 
performance and the app still can't take advantage, etc.  This is not to say that hints shouldn't be used, but other 
factors - like the level of your influence over the developers to change them as 
need arises - weigh in the determination.
 
As an example, I once spent over six months trying 
to get an out-sourced application changed to remove a few very harmful 
hints.  The contract developers had determined, on their small test 
system, that forcing an access-path sort by use of an otherwise wildly 
inappropriate index hint and a stopkey was more efficient than using an order by 
clause.  In production, the reverse was true - the hint caused each 
submission of one of these statements CPU utilization of 72x and I/O 
of 6200x of what the order by did.  This was for a query that was 
submitted by their application 50,000 - 100,000 times a day!  Guess 
who was constantly called upon to "tune the database" to fix this 
nightmare...
 
-Don Granaman
[OraSaurus - Honk if you remember 
UFI!]
 
- Original Message - 

  From: 
  Koivu, Lisa 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Friday, October 05, 2001 1:30 
  PM
  Subject: RE: perplexing plan? 
  
  Remco, why do you say don't use hints 
  unless you really have to?  
  Lisa Koivu Oracle Database 
  Administrator Fairfield Resorts, Inc. 
  954-935-4117 

not enough ITL slots for parallel DML operation

[Martin, Alan]

Title: not enough ITL slots for parallel DML operation 





Someone asked me, "What happens in a parallel DML operation if there are not enough ITL slots defined for a particular block?" I'm not sure. Can someone shed some light?

Thanx,


Alan Martin
Defense Logistics Information Service
Battle Creek, Michigan

CTAS use of rollback

[Nicoll, Iain (Calanais)]

Could anyone tell me whether Create table .. as select .. uses rollback.  

I initially thought it would (despite being a cross between ddl and dml) but
having created a 3.5 million row table and checked the sum of the writes in
v$rollstat it had only done ~130k writes between the start of the ctas and
the end.  It also doesn't create the table initially but just has a numbered
object which it seems to rename only at the very end, so if it fails I would
have though it would just drop that object and if it completes successfully
then a commit would be done because of the ddl aspects of the command.

I tried inserting 10k rows into the same table and this came back with about
25k writes (seemed reasonable if it's only storing the rowid).  Given this
it doesn't seem to be using rollback (other than recording changes to
extents etc) but I'd appreciate confirmation.

Iain Nicoll
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

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

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

Re: Developer 2000 V. 2.0 vs V.6.0

[DBAtracker]

Thanks, i will check it out but i still need to know if I can install developer 6.0 over developer 2.0 or if I must uninstall 2.0 first. Sometimes uninstalling opens up a whole new can of  worms.
Thanks

Re: Clarification from your Paper "Implementing RAID in Oracle Systems"

[Gaja Krishna Vaidyanatha]

Paul,

Thanks so much. I could not have said it any better.

Cheers,

Gaja

--- Paul Drake <[EMAIL PROTECTED]> wrote:
> thin-wide
> 
> RAID 0 volume, 8 drives wide, 8K deep. (2 of these
> mirrored uses 16 drives)
> Stripe depth = 64K - for a multi_block_read_count =
> 8, db_block_size= 8192 -
> a FTS will grab 1 block off of each member in the
> stripe at a time.
> 
> thick-narrow
> 
> RAID 1 volume, 2 drives, stripe depth 64K, a FTS
> will grab 8 blocks off of 1
> drive at a time.
> 
> 
> The idea is, deeper, wider tends to improve
> throughput for fewer jobs.
> (SAME)
> Thicker, more narrow, tends to allow for more
> distinct files on dedicated
> volumes (OFA)
> 
> The classic tradeoff of (bulk) throughput vs.
> concurrency (few batch jobs
> vs. numerous concurrent users).
> 
> 
> 
> 
> - Original Message -
> To: "Multiple recipients of list ORACLE-L"
> <[EMAIL PROTECTED]>
> Sent: Friday, October 05, 2001 12:55 AM
> 
> 
> 
> EXCERPT :-
> "A scientific comparison needs to be made to
> determine whether it is
> better to create fewer volumes with "thin-wide
> stripes" or more volumes
> with relatively "thicker-narrower" stripes.  This is
> dependent on issues
> such as data/index partitioning, required support
> parallelism for core
> operations and any service-level agreements on high
> or partial
> availability.  While thin-wide stripes are a very
> attractive solution,
> the constraining factors of parallelism,
> availability and data/index
> partitioning, make it not that appealing.  My goal
> is to meet somewhere
> halfway between thin-wide stripes and thick-narrow
> stripes ."
> 
> What do you mean by "thin-wide stripes"  &
> "thicker-narrower" stripes ?
> 


=
Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101
http://www.osborne.com/database_erp/0072131454/0072131454.shtml

__
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gaja Krishna Vaidyanatha
  INET: [EMAIL PROTECTED]

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

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

Re: weekends/holidays

[Don Granaman]

You could determine "weekend" using a date format:

SQL> select to_char(sysdate, 'DAY') from dual;

TO_CHAR(S
-
FRIDAY

This could be used in SQL (CASE or DECODE statement) or a simple function.

Determining a "holiday" is much more ambiguous since the definition varies from
one nation to another and from one company to another (is Valentine's day a
holiday? What about the day after Thanksgiving? ...).  For example, in the US,
there are 9 stock market holidays, 12 federal holidays (I think), but most
companies recognize only 6.  What is typically done is to create some
context-specific HOLIDAY table, populated it manually, and write code.
Context-specific holiday determination could be automated only in code, but the
logic would be rather complex and hardly worth the time.  Besides, which would
you rather routinely update, even if infrequently - data or code?

-Don Granaman
[OraSaurus - Honk if you remember UFI!]

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, October 05, 2001 10:45 AM


> Hi list,
> Is there a function in Oracle that will determine if particular calendar day
> is weekend or a US holiday.  I need to write a function that will check the
> data integrity of my db.  It will check data entered into the system and
> notice any gaps.  I have to go by days(i.e. every day there should be an
> entry/ies unless it is a weekend or holiday.)
> Thank you in advance,
>
> Lyuda Hoska
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

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

RE: Oracle 8.1.7 on Solaris vs. Linux

[Orr, Steve]

Our VALinux 4450's rock... 4 PentiumIII CPU's, 4MB RAM, EMC Symmetrix. No
benchmarks but it "feels" faster on CPU heavy processes. We were going to
benchmark against a Sun 450 but damagement just made the decision without
testing. We have suffered a bit due to bugs that were fixed for Solaris but
not for Linux. However, with the recent 8.1.7.2 patch it appears we are on a
par as regards functionality and are probably outperforming more expensive
Sun servers. Wish there was time to do some benchmarking but VALinux is
"nolonger in the hardware business" so it would be academic. Anyway, long
term it looks like the Intel/Linux combination will make for strong servers.
The better, faster, cheaper phenomenon for PC's is extending to the server
market and Sun, HP, and IBM will have to compete. If only EMC hardware and
Oracle software would follow this trend in like manner...

Steve Orr

-Original Message-
Sent: Thursday, October 04, 2001 12:50 PM
To: Multiple recipients of list ORACLE-L

I would be interested in seeing this as Linux is not the greatest for SMP
support.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 

-Original Message-
Sent: Thursday, October 04, 2001 2:25 PM
To: Multiple recipients of list ORACLE-L

I don't have experience with Linux on RS/6000, but I would sure
like to give it a try.

An RS/6000 is a much faster box than a more expensive Sun box.

Since IBM supports Linux on their HW, and Oracle supports
their RDBMS on Linux, I would jump at the chance for this combination.

Jared

-Original Message-
List

Does anyone have any strong opinions or useful reference on the
performance, reliability and scalability of Oracle (probably 8.1.7) on Linux
(SuSE?) as against Solaris?

We're spec'ing up some kit for a new production d/b and the cost of
upgrading one of our existing Suns is so high that it would probably be
cheaper to buy a new Linux box (I would guess an IBM).  The database is
going to be around 20Gb & we were looking at ~4 processors and ~2Gb RAM.

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

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

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

Re: perplexing plan?

[Jonathan Lewis]

Look like Oracle is doing exactly what it's been told

Step 1 - create an internal temporary table from the inline view
with a sort (unique) for the DISTINCT

Step 2 - for each row in step one, (i.e. nested loop) get the
streets related to the output from step 1

Step 3 - with the row source produced from step 1 and
step 2, generate a hash table, then scan the F15 table
to probe the hash table.  Unfortunately, there is a hint
to use and index when accessing the F15 table, so
Oracle uses a full scan in order to meet the requirements
of the hint and the hash simultaneously.

Ideally you probably want to get better stats on the F15 table
so that Oracle realises that an indexed NL access into F15
is a good idea; or you want to add a USE_NL(F15) hint to
stop the hash join happening.



Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 05 October 2001 15:16


I'm a little perplexed by this query and it's associated plan.  It's
also a big
performance problem.  The problem is the 35 million row table clearly.
But
looking at the plan at the bottom, I'm not sure where the sorting is
going on.
Would anyone say the index full scan on the 35 million row table is
being
sorted?  Or does it look more like it's being fed to a nested loops
query?

Thanks,
Doug




SELECT /*+ ORDERED INDEX(S_ S15_IX1) INDEX(BUS_FID F15_UK1)
INDEX(STREET
  A15_IX1) */  SDE.STREET.CFCC,  SDE.STREET.BUS_FID
,S_.eminx,S_.eminy,
  S_.emaxx,S_.emaxy, BUS_FID.fid,BUS_FID.numofpts,BUS_FID.entity,
  BUS_FID.points,BUS_FID.rowid
FROM
(SELECT /*+ INDEX(SP_ S15_IX1) */ DISTINCT
sp_fid,eminx,eminy,emaxx,emaxy
  FROM SDE.S15 SP_   WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >=
:3
AND
  SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx
>= :7
  AND SP_.emaxy >= :8) S_,  SDE.STREET , SDE.F15 BUS_FID WHERE
S_.sp_fid =
  BUS_FID.fid AND S_.sp_fid = SDE.STREET.BUS_FID


call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.00   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch   45473.15 475.04 223532   66153503  0
4494
--- --   -- -- -- --
--
total   47473.15 475.04 223532   66153503  0
4494

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

Rows Row Source Operation
---  ---
   4494  HASH JOIN
   4494NESTED LOOPS
   4495  VIEW
   4495 SORT UNIQUE
   4817   INDEX RANGE SCAN (object id 7356)
   4494  TABLE ACCESS BY INDEX ROWID STREET
   8988INDEX UNIQUE SCAN (object id 7355)
33065402  TABLE ACCESS BY INDEX ROWID F15
33065403INDEX FULL SCAN (object id 7283)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Doug C
  INET: [EMAIL PROTECTED]

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

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

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

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

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

RE: Developer 2000 V. 2.0 vs V.6.0

[Ken Janusz]

It’s the
Enterprise Edition – fully functional. 
About 2 weeks ago Oracle released it for W/2000.  If you are not a member of OTN you
should join.  It’s free and full of
lots of goodies.

 

http://technet.oracle.com

 

 

Ken

 

-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Friday, October 05, 2001
12:08 PM
To: Multiple recipients of list
ORACLE-L
Subject: Re: Developer 2000 V. 2.0
vs V.6.0

 

hi, 
I am going with personal 8i because I have it. What is the EE edition?

RE: Group by problem

[Larry Elkins]

If I understand correctly, you want the value of count to be 0 when there is
no corresponding row in table b? Here is an example using the DEPT and EMP
tables showing different count approaches. Note that for DEPTNO = 40, there
are no EMP rows:

  1  select d.deptno, count(*), count(d.deptno), count(e.deptno)
  2  from   emp e, dept d
  3  where  d.deptno = e.deptno (+)
  4* group by d.deptno
SQL> /

DEPTNO   COUNT(*) COUNT(D.DEPTNO) COUNT(E.DEPTNO)
-- -- --- ---
10  3   3   3
20  5   5   5
30  6   6   6
40  1   1   0

Notice the differences for DEPTNO = 40. So, is it count(b.column2) that you
are looking for, similar to the count(e.deptno) above?

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Csillag
> Zsolt
> Sent: Friday, October 05, 2001 12:50 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Group by problem
>
>
> Hi,
>
> If I have the following query:
>
> Select a.column1, count(*)
>
> from a, b
>
> where a.column2 = b.column2
>
> It works correctly, it displays the correct number in the count.
>
> However there are records that don't meet the criteria  'where
> a.column2 =
> b.column2'
>
> So I modified the query:
>
> Select a.column1, count(*)
>
> from a, b
>
> where a.column2 = b.column2(+)
>
> Now with (+) it displays every record, but the problem is that
> in case there is no relation, the count displays 1 instead of 0.
> In other cases it is good.
>
> But I can't decide from the program if it's 1 because it had 1
> relation in
> table b, or 1 because it hadn't
> got any relation.
>
> Can you help me work around this problem?
>
>
> Thank you
>
>
>
>
> Zsolt Csillag
> Hungary
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Csillag Zsolt
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

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

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

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

Re: session_cached_cursors parameter

[Jonathan Lewis]

Since no-one's answered this, I'll propose my theory,
although I'm not sure it's right.

The session_cached_cursors is just an array in the
UGA (session memory), which means it will be stored
in the SGA when using the multi-threaded server.

The function of the cache is to maintain pointers
into the shared pool so that on reuse of a closed
cursor, Oracle has the option of finding the cursor
very rapidly if it is still available, rather than going
through all the processing (and particularly latching)
needed to find if the 'new' SQL is sharable.

I believe the cost is actually a very small increment
in memory, plus a CPU cost for scanning the array,
which means that an array size over about 100 may
put you in the position of losing more CPU than you
would otherwise save.




Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 03 October 2001 22:03


Does anyboby know how this parameter really works?

Does it use PGA memory to cache parsed statements or
it uses SHARED_POOL memory for that.

If the case is the first one, imagine that cursor1 is
flushed out from the Shared Pool, and the session A
has it cached (in its PGA I assume), then, in order to
use it again, what must the session A do?
(it has to place the cursor into the shared pool again
and do a soft parse or that would not be
necessary...??)

thanks


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

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

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

RE: perplexing plan?

[Koivu, Lisa]

Title: RE: perplexing plan? 





Remco, why do you say don't use hints unless you really have to?  


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



-Original Message-
From:   Doug C [SMTP:[EMAIL PROTECTED]]
Sent:   Friday, October 05, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Subject:    Re: perplexing plan? 


This was a vendor designed query.   To my knowlege - it is complete.   As to the
performance problem - it was apparently caused by a "tool" that was suppossed to
analyze statistics on the some of the associated tables and didn't.   It was
rectified, and now the query screams.   I'm am somewhat anxious to see the new
trace of it however.  I'll probably post it shortly.





On Fri, 05 Oct 2001 08:20:21 -0800, you wrote:


>Doug,
>
>Sorting is caused by the "distinct", and is probably the cause of your
>performance problem. Try to limit the sorting to a minimal number of rows,
>e.g. by creating a temp table containing all (including the multiple copies)
>rows and then select the distinct values of that table. You could also try :
>select distinct * from (select ..) to replace the select distinct.
>
>Another tip: don't you hints unless you really have to ...
>
>HTH,  Remco
>
>-Oorspronkelijk bericht-
>Van: Doug C [mailto:[EMAIL PROTECTED]]
>Verzonden: vrijdag 5 oktober 2001 16:30
>Aan: Multiple recipients of list ORACLE-L
>Onderwerp: perplexing plan? 
>
>
>I'm a little perplexed by this query and it's associated plan.  It's also a
>big
>performance problem.  The problem is the 35 million row table clearly.  But
>looking at the plan at the bottom, I'm not sure where the sorting is going
>on.
>Would anyone say the index full scan on the 35 million row table is being
>sorted?  Or does it look more like it's being fed to a nested loops query?
>
>Thanks,
>Doug
>
>SELECT /*+ ORDERED INDEX(S_ S15_IX1) INDEX(BUS_FID F15_UK1) INDEX(STREET
>A15_IX1) */  SDE.STREET.CFCC,  SDE.STREET.BUS_FID  ,S_.eminx,S_.eminy,
>  S_.emaxx,S_.emaxy, BUS_FID.fid,BUS_FID.numofpts,BUS_FID.entity,
>  BUS_FID.points,BUS_FID.rowid
>FROM
> (SELECT /*+ INDEX(SP_ S15_IX1) */ DISTINCT sp_fid,eminx,eminy,emaxx,emaxy
>  FROM SDE.S15 SP_   WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3
>  AND SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx >=
>:7
>  AND SP_.emaxy >= :8) S_
>,  SDE.STREET
> , SDE.F15 BUS_FID 
>WHERE S_.sp_fid =  BUS_FID.fid 
>AND S_.sp_fid = SDE.STREET.BUS_FID
>
>
>call count   cpu    elapsed   disk  query    current
>rows
>--- --   -- -- -- --
>--
>Parse    1  0.00   0.00  0  0  0
>0
>Execute  1  0.00   0.00  0  0  0
>0
>Fetch   45    473.15 475.04 223532   66153503  0
>4494
>--- --   -- -- -- --
>--
>total   47    473.15 475.04 223532   66153503  0
>4494
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: 20
>
>Rows Row Source Operation
>---  ---
>   4494  HASH JOIN
>   4494    NESTED LOOPS
>   4495  VIEW
>   4495 SORT UNIQUE
>   4817   INDEX RANGE SCAN (object id 7356)
>   4494  TABLE ACCESS BY INDEX ROWID STREET
>   8988    INDEX UNIQUE SCAN (object id 7355)
>33065402  TABLE ACCESS BY INDEX ROWID F15
>33065403    INDEX FULL SCAN (object id 7283)
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Doug C
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California    -- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com


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


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

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

RE: Q about autoextent tablespaces and performance

[Christopher Spence]

Title: Q about autoextent tablespaces and performance









Accidently hit send before I finished my response...


Anyway, one thing I used to do is give them a CRETAB/CREIDX script and have them run it.

This will allow you to build the table and
indexes ahead of time, and of the size you
desire.  You can also have the script
create an initial setting of the size of the existing table.  Take a look at the cretab.sql script on my site which is what I used to use
when I had to do such things.

 



"Do not criticize someone until you walked a
mile in their shoes, that way when you criticize them, you are a mile a way and
have their shoes."

Christopher R. Spence 
Oracle DBA 
Phone: (978) 322-5744 
Fax:    (707) 885-2275 

Fuelspot 
73 Princeton Street 
North, Chelmsford 01863 
  



-Original Message-
From: Schoen Volker
[mailto:[EMAIL PROTECTED]] 
Sent: Friday, October 05, 2001 1:10 PM
To: Multiple recipients of list
ORACLE-L
Subject: Q about autoextent
tablespaces and performance

 

Hi list, 

I need some opinions about following question.


I have a developer box with about 20 schemas.
Sometimes our customers send us a export of there data which I had to import
into our develop instance. My problem is that I don't now how much space I need
for those imports. My solution is to create tablespaces with about 50 MB and
set autoextent on (localy managed). I import the customers data first time and
tablespace may use 200 mb. After some weeks I get the next export. After
import, tablespace growth to 300 mb.

So now my question, is it better to create a large
tablespace, because of continouges db and hdd blocks, or does this have no
influence or small influence on performance. I prefer to build small
tablespaces with limited autoextent option. Reason is, that my cold backups
were quicker, cause I do not have to reserve a lot of unused space for my
tablespaces.

I have databases on Suse Linux, NT4 and W2K. This
question is for all platform. If there are some platform specific issues,
please let me know.

Hope you understand my question. 

TIA 

Volker Schoen

E-Mail:
mailto:[EMAIL PROTECTED] 
http://www.inplan.de


 

Re: WHERE CURRENT OF Question

[Viktor]

Hi Bill,

I didn't really do much checking, but my guess is that
the 'WHERE CURRENT OF' works here because you're
selecting * in the cursor. 

If you, for instance, select columna_a as supposed to
*
then you will most likely get the error.

My guess is  that this is just how ORACLE locks the
set when you select in the cursor; therefore, you
don't get the error. But,like I said, if you only
select column_a, and then, attempt to update column_b,
you will most likely see the error that you might have
expected.


Regards.

--- Bill Buchan <[EMAIL PROTECTED]> wrote:
> 
> 
> Why does the following work?  I open a cursor with
> FOR UPDATE OF COLUMN_A 
> and then do an update of COLUMNB, WHERE CURRENT OF
> the cursor.  Surely I 
> shouldn't be allowed to do that?
> (Ora 8.1.7.2)
> 
> Any insight appreciated!
> Thanks
> - Bill.
> 
> 
> SQLWKS> create table test_table
>   2> (
>   3> column_anumber,
>   4> column_bnumber
>   5> )
>   6>
> Statement processed.
> SQLWKS> insert into test_table values (1,2)
>   2>
> 1 row processed.
> SQLWKS> insert into test_table values (2,3)
>   2>
> 1 row processed.
> SQLWKS> insert into test_table values (3,4)
>   2>
> 1 row processed.
> SQLWKS> declare
>   2> cursor c1 is select * from
> test_table for update of column_a;
>   3> begin
>   4> for i in c1 loop
>   5> update test_table set
> column_b = column_b * 2
>   6> where current of c1;
>   7> end loop;
>   8> end;
>   9>
> Statement processed.
> SQLWKS> select *
>   2> from test_table
>   3>
> COLUMN_A   COLUMN_B
> -- --
>   1  4
>   2  6
>   3  8
> 3 rows selected.
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Bill Buchan
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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


__
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viktor
  INET: [EMAIL PROTECTED]

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

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

RE: Developer 2000 V. 2.0 vs V.6.0

[Farnsworth, Dave]

enterprise edition

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, October 05, 2001 1:08 
  PMTo: Multiple recipients of list ORACLE-LSubject: Re: 
  Developer 2000 V. 2.0 vs V.6.0hi, I am going with personal 8i because 
  I have it. What is the EE edition? 

Re: weekends/holidays

[Yosi Greenfield]

Lyuda,

For weekends, you can check to_char('&date', 'D'). The 'D'
format returns the day of the week. A 1 or a 7 would indicate
Sunday or Saturday. For holidays, you'd have to write your
own function. You could set up a table of legal holiday dates,
that you would have to populate. Truth be told, there are lots
of holidays, some more observed than others. So you'd really
have to define for yourself which holidays count and which don't.

Then write a function is_holiday to return true if a specific date
is found in the holidays table, or false if its not.

hth,

Yosi


[EMAIL PROTECTED] wrote:

> Hi list,
> Is there a function in Oracle that will determine if particular calendar day
> is weekend or a US holiday.  I need to write a function that will check the
> data integrity of my db.  It will check data entered into the system and
> notice any gaps.  I have to go by days(i.e. every day there should be an
> entry/ies unless it is a weekend or holiday.)
> Thank you in advance,
>
> Lyuda Hoska
>

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

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

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

Re: Problems with patchsets to 8.1.7.0.0 on Windows 2000 - and warning on 8.1.7.2.1 patchset

[Don Granaman]

This problem was easily solved.  All the basics were OK, as stated in original
message.  The problem was that the "setup.exe" that the docs said to run was
simply dysfunctional - as verified by several members of the list.  Just
starting the installer and pointing to the products.jar in the patchset worked.
So did running the executable in the win32 patchset subdirectory.

The next "adventure" was that after applying the 8.1.7.2.1 patch to 8.1.7.0.0
(yes, bypassing 8.1.7.1.x - as per readme's minimal baseline), OEM stopped
working - with "EMSDK-1100: Unable to establish a secure communication channel."
This was due to Bug No. 1946984 - reported in August.   The short story is that
the file "njssl8.dll" in %ORACLE_HOME%\bin is broken in the 8.1.7.2.1 patchset
(for NT/2000 at least).  It is easy to work around if you know this before
applying the patch - simply save this file before applying the patch and copy it
back afterwards.  It is much more of a pain if you discover it afterward.  The
latter requires downloading the 8.1.7.1.1 patchset, extracting this file from it
and replacing the broken file in 8.1.7.2.1.

I have jumped through all the flaming hoops and have it working (again) now.  My
gripe is that this has been known for over a month, so why couldn't Oracle
either:

A) Update or replace the patchset
or
B) at least update the patchset readme
?

-Don Granaman
[OraSaurus - Honk if you remember UFI!]

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, October 05, 2001 9:40 AM


> Hi,
>
> May be you are not using Administrator account.
> Or the patch set is installed after you click on it,
> You can do
> in your oracle_home
> dir /s/p/od
>
> it will sort by date which file is the newest.
>
> Sorry, I just a beginner, can't help much.
> Sinardy


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

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

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

RE: Q about autoextent tablespaces and performance

[Christopher Spence]

Title: Q about autoextent tablespaces and performance









I generally add about 33% to the size of
the uncompressed export.  This gives
me some idea how much space it may take. 
Of course, this depends on how many indexes (as they are not stored in
export) and storage parameters on the database.

 

You could always ask them to give you an
idea (perhaps give them a query to run before exporting) of he size of the data
and indexes. 



"Do not criticize someone until you walked a
mile in their shoes, that way when you criticize them, you are a mile a way and
have their shoes."

Christopher R. Spence 
Oracle DBA 
Phone: (978) 322-5744 
Fax:    (707) 885-2275 

Fuelspot 
73 Princeton Street 
North, Chelmsford 01863 
  



-Original Message-
From: Schoen Volker
[mailto:[EMAIL PROTECTED]] 
Sent: Friday, October 05, 2001 1:10 PM
To: Multiple recipients of list
ORACLE-L
Subject: Q about autoextent
tablespaces and performance

 

Hi list, 

I need some opinions about following question.


I have a developer box with about 20 schemas.
Sometimes our customers send us a export of there data which I had to import
into our develop instance. My problem is that I don't now how much space I need
for those imports. My solution is to create tablespaces with about 50 MB and
set autoextent on (localy managed). I import the customers data first time and
tablespace may use 200 mb. After some weeks I get the next export. After
import, tablespace growth to 300 mb.

So now my question, is it better to create a large
tablespace, because of continouges db and hdd blocks, or does this have no
influence or small influence on performance. I prefer to build small
tablespaces with limited autoextent option. Reason is, that my cold backups
were quicker, cause I do not have to reserve a lot of unused space for my
tablespaces.

I have databases on Suse Linux, NT4 and W2K. This
question is for all platform. If there are some platform specific issues,
please let me know.

Hope you understand my question. 

TIA 

Volker Schoen

E-Mail:
mailto:[EMAIL PROTECTED] 
http://www.inplan.de


 

Re: Installation of designer 6.0 with personal oracle 8.1.6

[DBAtracker]

Hi,
 You have to install developer first.

Re: perplexing plan?

[Doug C]

This was a vendor designed query.   To my knowlege - it is complete.   As to the
performance problem - it was apparently caused by a "tool" that was suppossed to
analyze statistics on the some of the associated tables and didn't.   It was
rectified, and now the query screams.   I'm am somewhat anxious to see the new
trace of it however.  I'll probably post it shortly.




On Fri, 05 Oct 2001 08:20:21 -0800, you wrote:

>Doug,
>
>Sorting is caused by the "distinct", and is probably the cause of your
>performance problem. Try to limit the sorting to a minimal number of rows,
>e.g. by creating a temp table containing all (including the multiple copies)
>rows and then select the distinct values of that table. You could also try :
>select distinct * from (select ..) to replace the select distinct.
>
>Another tip: don't you hints unless you really have to ...
>
>HTH,  Remco
>
>-Oorspronkelijk bericht-
>Van: Doug C [mailto:[EMAIL PROTECTED]]
>Verzonden: vrijdag 5 oktober 2001 16:30
>Aan: Multiple recipients of list ORACLE-L
>Onderwerp: perplexing plan? 
>
>
>I'm a little perplexed by this query and it's associated plan.  It's also a
>big
>performance problem.  The problem is the 35 million row table clearly.  But
>looking at the plan at the bottom, I'm not sure where the sorting is going
>on.
>Would anyone say the index full scan on the 35 million row table is being
>sorted?  Or does it look more like it's being fed to a nested loops query?
>
>Thanks,
>Doug
>
>SELECT /*+ ORDERED INDEX(S_ S15_IX1) INDEX(BUS_FID F15_UK1) INDEX(STREET
>A15_IX1) */  SDE.STREET.CFCC,  SDE.STREET.BUS_FID  ,S_.eminx,S_.eminy,
>  S_.emaxx,S_.emaxy, BUS_FID.fid,BUS_FID.numofpts,BUS_FID.entity,
>  BUS_FID.points,BUS_FID.rowid
>FROM
> (SELECT /*+ INDEX(SP_ S15_IX1) */ DISTINCT sp_fid,eminx,eminy,emaxx,emaxy
>  FROM SDE.S15 SP_   WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3
>  AND SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx >=
>:7
>  AND SP_.emaxy >= :8) S_
>,  SDE.STREET
> , SDE.F15 BUS_FID 
>WHERE S_.sp_fid =  BUS_FID.fid 
>AND S_.sp_fid = SDE.STREET.BUS_FID
>
>
>call count   cpuelapsed   disk  querycurrent
>rows
>--- --   -- -- -- --
>--
>Parse1  0.00   0.00  0  0  0
>0
>Execute  1  0.00   0.00  0  0  0
>0
>Fetch   45473.15 475.04 223532   66153503  0
>4494
>--- --   -- -- -- --
>--
>total   47473.15 475.04 223532   66153503  0
>4494
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: 20
>
>Rows Row Source Operation
>---  ---
>   4494  HASH JOIN
>   4494NESTED LOOPS
>   4495  VIEW
>   4495 SORT UNIQUE
>   4817   INDEX RANGE SCAN (object id 7356)
>   4494  TABLE ACCESS BY INDEX ROWID STREET
>   8988INDEX UNIQUE SCAN (object id 7355)
>33065402  TABLE ACCESS BY INDEX ROWID F15
>33065403INDEX FULL SCAN (object id 7283)
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Doug C
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com

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

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

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

Group by problem

[Csillag Zsolt]

Hi,

If I have the following query:

Select a.column1, count(*)

from a, b

where a.column2 = b.column2

It works correctly, it displays the correct number in the count.

However there are records that don't meet the criteria  'where a.column2 = 
b.column2'

So I modified the query:

Select a.column1, count(*)

from a, b

where a.column2 = b.column2(+)

Now with (+) it displays every record, but the problem is that
in case there is no relation, the count displays 1 instead of 0.
In other cases it is good.

But I can't decide from the program if it's 1 because it had 1 relation in 
table b, or 1 because it hadn't
got any relation.

Can you help me work around this problem?


Thank you




Zsolt Csillag
Hungary

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

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

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

RE: WHERE CURRENT OF Question

[Larry Elkins]

Bill,

The name of the column(s) specified is only there to specify which table(s)
rows to lock. The SQL manual says it better (and more concisely) than I can:


OF Locks the select rows only for a particular table in a join. The columns
in the OF clause only specify which tables' rows are locked. The specific
columns of the table that you specify are not significant. If you omit this
clause, Oracle locks the selected rows from all the tables in the query.


So, people might specify it if the are selecting from two tables but only
want to lock one. And lots of folks do it as "documentation" of what they
intend to do. And since it's Friday, I will go slightly OT and mention I
that I can't remember which version of Forms, I think an early version of
4.5, the client side PL/SQL parser when compiling would let you specify
*anything* in the OF clause -- e.g. "select...from emp for update of
IM_NOT_A_COLUMN". But, when executing the code, it would croak with an
invalid column error.

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bill Buchan
> Sent: Friday, October 05, 2001 11:40 AM
> To: Multiple recipients of list ORACLE-L
> Subject: WHERE CURRENT OF Question
>
>
>
>
> Why does the following work?  I open a cursor with FOR UPDATE OF COLUMN_A
> and then do an update of COLUMNB, WHERE CURRENT OF the cursor.  Surely I
> shouldn't be allowed to do that?
> (Ora 8.1.7.2)
>
> Any insight appreciated!
> Thanks
> - Bill.
>
>
> SQLWKS> create table test_table
>   2> (
>   3> column_anumber,
>   4> column_bnumber
>   5> )
>   6>
> Statement processed.
> SQLWKS> insert into test_table values (1,2)
>   2>
> 1 row processed.
> SQLWKS> insert into test_table values (2,3)
>   2>
> 1 row processed.
> SQLWKS> insert into test_table values (3,4)
>   2>
> 1 row processed.
> SQLWKS> declare
>   2> cursor c1 is select * from test_table for update
> of column_a;
>   3> begin
>   4> for i in c1 loop
>   5> update test_table set column_b = column_b * 2
>   6> where current of c1;
>   7> end loop;
>   8> end;
>   9>
> Statement processed.
> SQLWKS> select *
>   2> from test_table
>   3>
> COLUMN_A   COLUMN_B
> -- --
>   1  4
>   2  6
>   3  8
> 3 rows selected.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Bill Buchan
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

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

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

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

Re: weekends/holidays

[Connor McDonald]

where to_char(datecol,'DY') not in ('SAT','SUN')
and not exists ( 
 select null
 from list_of_holidays
 where holiday = datecol )

assuming you have a list of holidays in a table

hth
connor

 --- [EMAIL PROTECTED] wrote: > Hi list,
> Is there a function in Oracle that will determine if
> particular calendar day
> is weekend or a US holiday.  I need to write a
> function that will check the
> data integrity of my db.  It will check data entered
> into the system and
> notice any gaps.  I have to go by days(i.e. every
> day there should be an
> entry/ies unless it is a weekend or holiday.)
> Thank you in advance,
> 
> Lyuda Hoska
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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

=
Connor McDonald
http://www.oracledba.co.uk (mirrored at 
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"


Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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

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

Re: Q about autoextent tablespaces and performance

[Connor McDonald]

Since its only a development instance, I don't think
the autoextend is going to be an issue.  Alternatively
you could run 'imp indexfile=y' and add up what you
see in the storage clauses to get an approximate idea
of what space is going to be needed.

hth
connor

 --- Schoen Volker <[EMAIL PROTECTED]> wrote: > Hi
list,
> 
> I need some opinions about following question.
> 
> I have a developer box with about 20 schemas.
> Sometimes our customers send
> us a export of there data which I had to import into
> our develop instance.
> My problem is that I don't now how much space I need
> for those imports. My
> solution is to create tablespaces with about 50 MB
> and set autoextent on
> (localy managed). I import the customers data first
> time and tablespace may
> use 200 mb. After some weeks I get the next export.
> After import, tablespace
> growth to 300 mb.
> 
> So now my question, is it better to create a large
> tablespace, because of
> continouges db and hdd blocks, or does this have no
> influence or small
> influence on performance. I prefer to build small
> tablespaces with limited
> autoextent option. Reason is, that my cold backups
> were quicker, cause I do
> not have to reserve a lot of unused space for my
> tablespaces.
> 
> I have databases on Suse Linux, NT4 and W2K. This
> question is for all
> platform. If there are some platform specific
> issues, please let me know.
> 
> Hope you understand my question.
> 
> TIA
> 
> Volker Schoen
> E-Mail: mailto:[EMAIL PROTECTED]
>  
> http://www.inplan.de  
> 
> 
>  

=
Connor McDonald
http://www.oracledba.co.uk (mirrored at 
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"


Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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

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

RE: ampersand problem

[Regina Harter]

Well, for one thing, you have MOM instead of MON in the date 
format.  Besides that, it might help if you describe what problem you are 
having.

At 12:10 AM 10/5/01 -0800, you wrote:

>sorri i have pasted the wrong one
>here is the correct on
>
>declare
>code number(3):=0;
>edate date;
>begin
>code:=111;
>edate:=to_date('01-jan-2001','dd-mom-');
>dbms_output.put_line('actual data '||code ||','||edate);
>@abc code edate
>dbms_output.put_line('hello');
>end;
>/
>
>
>abc.sql
>
>declare
>my_code number(3);
>my_date date;
>begin
>my_code:=&1;
>my_date:='&2';
>dbms_output.put_line('date in abc '||my_code ||','||my_date);
>end;
>/
>-Original 
>Message-  From:   Swapna_Chinnagangannagari  Sent:   Friday, October 
>05, 2001 12:27 
>PM  To: '[EMAIL PROTECTED]'  Subject:ampersand problem  Why 
>is this code not working for me
>
>declare  code number(3):=0;  edate 
>date;  begin  code:=111;  edate:=to_date('01-01-2001','dd-mm-'); 
>dbms_output.put_line('actual data '||code ||','||edate);  @abc code 
>edate  dbms_output.put_line('hello');  end;  /
>
>abc.sql    declare  my_code number(3);  my_number 
>number(3);  begin  my_code:=&1;  my_number:='&2'; 
>dbms_output.put_line('data in abc '||my_code ||','||my_number);  end;  /

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

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

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

Re: Developer 2000 V. 2.0 vs V.6.0

[DBAtracker]

hi,
I am going with personal 8i because I have it. What is the EE edition?

Re: Opinions wanted for new Oracle Security Handbook

[Thater, William]

Martin, Alan wrote:

> Has anyone read this new book from Oracle Press, Published August 
> 2001, 624 pages, ISBN 0072133252? Opinions - good/bad/indifferent?
>
> Thanx,
> Alan Martin
> Principal Consultant
> Defense Logistics Information Service
> Battle Creek, Michigan
>
the one by marlene?  yup, i thought it was great and i wish i had  it to 
read before i started with 9iAS.  but then i get mentioned in it so 
maybe i'm prejudiced.;-)

-- 
--
Bill "Shrek" Thater  ORACLE DBA
Telergy,Inc.   [EMAIL PROTECTED]

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

Old mail has arrived.




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

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

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

RE: WHERE CURRENT OF Question

[Steve Adams]

Hi Bill,

The FOR UPDATE clause syntax allows for the possibility of column-level locking, but 
Oracle only implements row-level
locking. So the OF  phrase is just ignored.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/


-Original Message-
Sent: Saturday, 6 October 2001 2:40
To: Multiple recipients of list ORACLE-L




Why does the following work?  I open a cursor with FOR UPDATE OF COLUMN_A
and then do an update of COLUMNB, WHERE CURRENT OF the cursor.  Surely I
shouldn't be allowed to do that?
(Ora 8.1.7.2)

Any insight appreciated!
Thanks
- Bill.


SQLWKS> create table test_table
  2> (
  3> column_anumber,
  4> column_bnumber
  5> )
  6>
Statement processed.
SQLWKS> insert into test_table values (1,2)
  2>
1 row processed.
SQLWKS> insert into test_table values (2,3)
  2>
1 row processed.
SQLWKS> insert into test_table values (3,4)
  2>
1 row processed.
SQLWKS> declare
  2> cursor c1 is select * from test_table for update of column_a;
  3> begin
  4> for i in c1 loop
  5> update test_table set column_b = column_b * 2
  6> where current of c1;
  7> end loop;
  8> end;
  9>
Statement processed.
SQLWKS> select *
  2> from test_table
  3>
COLUMN_A   COLUMN_B
-- --
  1  4
  2  6
  3  8
3 rows selected.


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

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

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

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

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

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

RE: SQL Server E-mail List

[Ken Janusz]

Thanks, I've already got this one.

Ken

 -Original Message-
Sent:   Friday, October 05, 2001 9:45 AM
To: Multiple recipients of list ORACLE-L
Subject:RE: SQL Server E-mail List

Ken,

Here is a good list for SQL Server.

http://ls.swynk.com/scripts/lyris.pl?site=swynk.com&page=topic&topic=sqlserv
er&text_mode=&lang=english

Dave

-Original Message-
Sent: Friday, October 05, 2001 7:45 AM
To: Multiple recipients of list ORACLE-L


Does anyone know of a SQL Server e-mail list such as Oracle-L?

Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient Systems, Inc.
Minneapolis, MN
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ken Janusz
  INET: [EMAIL PROTECTED]

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

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

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

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

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

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

Q about autoextent tablespaces and performance

[Schoen Volker]

Title: Q about autoextent tablespaces and performance





Hi list,


I need some opinions about following question.


I have a developer box with about 20 schemas. Sometimes our customers send us a export of there data which I had to import into our develop instance. My problem is that I don't now how much space I need for those imports. My solution is to create tablespaces with about 50 MB and set autoextent on (localy managed). I import the customers data first time and tablespace may use 200 mb. After some weeks I get the next export. After import, tablespace growth to 300 mb.

So now my question, is it better to create a large tablespace, because of continouges db and hdd blocks, or does this have no influence or small influence on performance. I prefer to build small tablespaces with limited autoextent option. Reason is, that my cold backups were quicker, cause I do not have to reserve a lot of unused space for my tablespaces.

I have databases on Suse Linux, NT4 and W2K. This question is for all platform. If there are some platform specific issues, please let me know.

Hope you understand my question.


TIA


Volker Schoen
E-Mail: mailto:[EMAIL PROTECTED]
http://www.inplan.de

RE: getting password request on connect internal

[Miller, Jay]

Yep.  In fact I had no problem connecting to another instance running on the
same machine.  And they've been running (with a nightly shutdown) with no
problems for the last 2 weeks.

Jay Miller

-Original Message-
Sent: Friday, October 05, 2001 11:56 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]


are you part of the dba, oinstall group?

>>> [EMAIL PROTECTED] 10/05/01 11:43 AM >>>
I know this was discussed recently but I just did a major clean up of my
1,000+ unread posts so my apologies.

All of a sudden I'm unable to shutdown a database.  This is the alert_log:

Shutting down instance (immediate)
License high water mark = 21
Thu Oct  4 20:30:38 2001
SHUTDOWN: waiting for active calls to complete.



And when I try to connect internal to do a shutdown abort I get this:


Oracle Server Manager Release 3.1.6.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

ORA-03113: end-of-file on communication channel

SVRMGR> connect internal
Password:


I've already gone through every step  in the Oracle Note 69642.1 (Checklist
for Resolving CONNECT INTERNAL PASSWORD Issues) without any results.  Any
ideas?

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

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

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

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

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

RE: Developer 2000 V. 2.0 vs V.6.0

[Ken Janusz]

Why are
you going with the personal edition? 
You can join OTN and get the EE edition for free?

 

My $0.02,

Ken
Janusz, CPIM

 

-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Friday, October 05, 2001
9:05 AM
To: Multiple recipients of list
ORACLE-L
Subject: Developer 2000 V. 2.0 vs
V.6.0

 

I just
bought a new PC and was going to install Personal Edition 8i  and
Developer 2000 v. 6.0 as I have it on my old computer.  However, being
a little tired at the time, I installed the earlier version of Developer 2000
(v. 2.0) instead of 6.0. I know there are compatability issues between
 Developer 6.0 and Oracle Personal Edition 8.0. 
I tried to uninstall the Developer 2.0 but it didn't seem to work.  I'm
thinking I should be able to just install Developer version  6.0 on top of
the 2.0, effectively just doing an upgrade but I'm wondering if this is ok
before I load 8i (Developer must be loaded before 8i for a proper
installation.)  Can anyone confirm that installing 6.0 will work ok
without first removing Developer 2.0??  Appreciate any comments or suggestions.

Bill Johnson

RE: perplexing plan?

[Christopher Spence]

Your sort unique is to satisfy the DISTINCT against the table S15.

One thing may be a problem is you do a full scan of the index 7283, then
retrieve each and every row but one from the table.  There isn't really any
point of even using this index as it will only slow down the query as it has
to go through the index 33 Million times and read the 33 million rows one by
one.  It would be much more effective to just full table scan against the
table directly.  Perhaps look at using Partitioning and parallel query.

I would try removing the index hint for the 33 Million row table.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, October 05, 2001 10:30 AM
To: Multiple recipients of list ORACLE-L

I'm a little perplexed by this query and it's associated plan.  It's also a
big
performance problem.  The problem is the 35 million row table clearly.  But
looking at the plan at the bottom, I'm not sure where the sorting is going
on.
Would anyone say the index full scan on the 35 million row table is being
sorted?  Or does it look more like it's being fed to a nested loops query?

Thanks,
Doug




SELECT /*+ ORDERED INDEX(S_ S15_IX1) INDEX(BUS_FID F15_UK1) INDEX(STREET
  A15_IX1) */  SDE.STREET.CFCC,  SDE.STREET.BUS_FID  ,S_.eminx,S_.eminy,
  S_.emaxx,S_.emaxy, BUS_FID.fid,BUS_FID.numofpts,BUS_FID.entity,
  BUS_FID.points,BUS_FID.rowid
FROM
 (SELECT /*+ INDEX(SP_ S15_IX1) */ DISTINCT sp_fid,eminx,eminy,emaxx,emaxy
  FROM SDE.S15 SP_   WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3
AND
  SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx >= :7
  AND SP_.emaxy >= :8) S_,  SDE.STREET , SDE.F15 BUS_FID WHERE S_.sp_fid =
  BUS_FID.fid AND S_.sp_fid = SDE.STREET.BUS_FID


call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.00   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch   45473.15 475.04 223532   66153503  0
4494
--- --   -- -- -- --
--
total   47473.15 475.04 223532   66153503  0
4494

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

Rows Row Source Operation
---  ---
   4494  HASH JOIN
   4494NESTED LOOPS
   4495  VIEW
   4495 SORT UNIQUE
   4817   INDEX RANGE SCAN (object id 7356)
   4494  TABLE ACCESS BY INDEX ROWID STREET
   8988INDEX UNIQUE SCAN (object id 7355)
33065402  TABLE ACCESS BY INDEX ROWID F15
33065403INDEX FULL SCAN (object id 7283)
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Doug C
  INET: [EMAIL PROTECTED]

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

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

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

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

RE: dbsnmp agent

[Kimberly Smith]

No jobs.  Events I have but they are the normal check the system type.
I am relatively sure its the one that is checking tablespaces for full
that is causing the issue.  I can trace the Unix session back to an Oracle
session that is connected and checking datafiles and stuff.  And that
session stays active.  I did try limiting the tablespaces its looking at
but the process is still eating a whole CPU.  I got 4 so its not like its
bring the system to a grinding halt but its so different then the way
the other servers are acting.

-Original Message-
Sent: Friday, October 05, 2001 7:35 AM
To: Multiple recipients of list ORACLE-L


Do you have any sceduled events or scheduled jobs that the agent runs on
this box?

Just a thot,
Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, October 04, 2001 5:10 PM


> I have the dbsnmp agent running on a few boxes.  One of them is acting a
> little weird.  It is eating up a fair amount of CPU.  If I compare this
box
> to one of my others this is what the difference would be
>
> Box with no issues
> Oracle 8.1.7.1 32 bit
> HPUX 11i 64 bit
> 3 databases
> Not that many datafiles even taking into account the 3 databases.
>
> Box with issues
> Oracle 8.1.7.1 32 bit
> HPUX 11  64 bit
> 1 databases
> many datafiles (113 which means I have 113 tablespaces)
>
> I am thinking its due to the fact that its trying to monitor for
tablespace
> full
> and is having an issue because of this.  In realitity it should have paged
> me because
> they are kept near 100% full.  Yet it never has.
>
> Does anyone have a similiar setup with OEM monitoring tablespaces and
having
> a lot
> of tablespaces.  If so, can you determine that you have a process that is
> eating near
> 100% of a CPU?
>
> Thanks,
>
>
> 
> Kimberly Smith
> GMD Fujitsu
> Database Administrator
> (503) 669-6050
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Kimberly Smith
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

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

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

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

RE: Looking for a SQL Server DBA

[Ken Janusz]

Your txt file would not open for me.  Something about binaries not allowed.
So, you will have to send it to me in Word format.

Ken

 -Original Message-
Sent:   Friday, October 05, 2001 8:35 AM
To: Multiple recipients of list ORACLE-L
Subject:Re: Looking for a SQL Server DBA

 << File: ATT04073.txt >> Hi Ken. I'm currently on a short-term contract.
Looking for
something interesting to do. I do not have a Word version of my
resume with me, but this text version is a start. I do not have
years of SQL server, but have worked with advanced SQL Server
technologies such as replication and Data Transformation Services.
Here is a link promoting a project I worked on. I handled much of
the backend for the project, which was, and most likely still is, at
the pilot stage in a hotel here in Minneapolis:

http://www.microsoft.com/mobile/enterprise/casestudies/cs-carlson.asp

Thanks,

Jeff

>>> [EMAIL PROTECTED] 10/05/01 08:50AM >>>
My company is looking to hire a full-time, permanent SQL Server
DBA.  That
person's focus will be on converting databases to SQL Server and
then
providing post conversion support.  This position is for our office
in
Minneapolis, MN.  No relocation expenses.

If you know of anyone who might be interested in such a position,
please
have them contact me.

Thanks,
Ken Janusz, CPIM
Oracle Database Conversion Lead
Sufficient Systems, Inc.
Minneapolis, MN

P.S.:  I have been looking for a local SQL SERVER user group and an
e-mail
list that works with no success.

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

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

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

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

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

RE: Output to Excel

[Ramon Estevez]

Kevin don't worry, I understood

Thanks, that was the solution !!

Thanks

Ramon E. Estevez
[EMAIL PROTECTED] 
809-565-3121


-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Thomas, Kevin
Enviado el: Friday, 05 October, 2001 11:15 AM
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Output to Excel


Ramon,

I would add an alias onto the columns you are referencing in your cursors so
that they are called something like DATA. This way when you come to
reference FT later on you can use FT.DATA as the item to ouput to your file.

You appear to be referencing OUT_REC at line 65 incorrectly. OUT_REC is a
record containing items of data. As you have opened the cursor on line 63
with the statement FOR FT IN C_FACTURAS, you have now assigned all the
values that will be returned from the cursors into FT. In order to output
the details that are now held within FT, line 65 should read something like:

UTL_FILE.PUT_LINE(V_ARCHIVO, FT.DATA);
You really don't need OUT_REC anymore as you never move data into it.

I hope this makes sense, I'm never very good at explaining things... ;o)

Regards,
Kev.

__

Kevin Thomas
Technical Analyst
Deregulation Services
Calanais Ltd.
(2nd Floor East - Weirs Building)
Tel: 0141 568 2377
Fax: 0141 568 2366
http://www.calanais.com

-Original Message-
Sent: 05 October 2001 15:06
To: Multiple recipients of list ORACLE-L


Thomas,

I keep getting the error

LINE/COL ERROR
 -
65/4 PL/SQL: Statement ignored
65/36PLS-00302: component 'OUT_REC' must be declared
72/4 PL/SQL: Statement ignored
72/37PLS-00302: component 'OUT_REC' must be declared
SQL>

Any suggestion,

Gracias

Ramon Estevez
Dominican Republic
[EMAIL PROTECTED]


  1   CREATE OR REPLACE PACKAGE BODY PROCESAR_AGENCIAS AS
  2   PROCEDURE GENERAR_FACTURAS
  3( PGRUPO IN NUMBER,PCOMPANIAIN NUMBER,
  4  PFECHA_INICIAL IN DATE,  PFECHA_FINAL IN DATE,
  5  PAGENCIA   IN NUMBER ) AS
  6   CURSOR C_FACTURAS IS
  7 SELECT F.GRUPO||','||
  8F.COMPANIA||','||
  9F.TIPO_FACTURA||','||
 10F.AGENCIA||','||
 11F.FACTURA||','||
 12F.CLIENTE||','||
 13F.VENDEDOR||','||
 14   -- ZONA||','||
 15F.DOCUMENTO_COBRO||','||
 16F.FECHA||','||
 17F.FECHA_PAGO||','||
 18F.FECHA_VENCIMIENTO||','||
 19F.ESTATUS_COMISION||','||
 20F.COMISION_VENDEDOR||','||
 21F.MONTO||','||
 22F.MONTO_PAGADO||','||
 23F.IMPRESA||','
 24   -- ITBIS||','||
 25   -- DESCTO||','
 26 FROM FACTURAS F
 27WHERE
 28 F.GRUPO   = PGRUPO AND
 29 F.COMPANIA= PCOMPANIA  AND
 30 F.AGENCIA = PAGENCIA   AND
 31 F.FECHA BETWEEN PFECHA_INICIAL AND PFECHA_FINAL;
 32   CURSOR C_ITEM_FACTURAS IS
 33 SELECT I.GRUPO||','||
 34I.COMPANIA||','||
 35I.AGENCIA||','||
 36I.TIPO_FACTURA||','||
 37I.FACTURA||','||
 38I.LOCALIDAD||','||
 39I.ARTICULO||','||
 40I.SECUENCIA||','||
 41I.COSTO||','||
 42I.PRECIO_VENTA||','||
 43I.CANTIDAD||','||
 44I.ITBIS||','||
 45I.DESCTO||','
 46 FROM FACTURAS F, ITEM_FACTURAS I
 47WHERE
 48 F.GRUPO = PGRUPO AND
 49 F.COMPANIA  = PCOMPANIA  AND
 50 F.AGENCIA   = PAGENCIA   AND
 51 F.FECHA BETWEEN   PFECHA_INICIAL AND
 52   PFECHA_FINAL   AND
 53 I.GRUPO = F.GRUPOAND
 54 I.COMPANIA  = F.COMPANIA AND
 55 I.TIPO_FACTURA  = F.TIPO_FACTURA AND
 56 I.FACTURA   = F.FACTURA;
 57 V_ARCHIVO UTL_FILE.FILE_TYPE;
 58 REGISTRO  FACTURAS%ROWTYPE;
 59 OUT_REC   REGISTRO%TYPE;
 60   BEGIN
 61   -- Loop para el archivo de Facturas
 62 V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','FACTURAS.TXT', 'W');
 63 FOR FT IN C_FACTURAS
 64   LOOP
 65 UTL_FILE.PUT_LINE(V_ARCHIVO, FT.OUT_REC);
 66   END LOOP;
 67 UTL_FILE.FCLOSE(V_ARCHIVO);
 68   -- Loop para el archivo de Item Facturas
 69 V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','ITEM_FACTURAS.TXT',
'W');
 70 FOR IFT IN C_ITEM_FACTURAS
 71   LOOP
 72 UTL_FILE.PUT_LINE(V_ARCHIVO, IFT.OUT_REC);
 73   END LOOP;
 74 UTL_FILE.FCLOSE(V_ARCHIVO);
 75   END GENERAR_FACTURAS;
 76*  END PROCESAR_AGENCIAS;
SQL> /

Warning: Package Body created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE BODY PROCESAR_AGENCIAS:

LINE/COL ERROR
 -
65/4 PL/SQL: Statement ignored
65/36PLS-00302: component 'OUT_REC' must be declared
72/4 PL/SQL: Statement ignored
72/37PLS-0

Re: Unix batch job

[Wendy Y]

Can you schedule it from Crontab?
try "man crontab" in UNIX.

HTH

Wendy


--- [EMAIL PROTECTED] wrote:
> Here is one simple example:
> ==
> SUCCESS=0
> STARTTIME=`date +'%d/%m/%Y-%H:%M:%S'`
> BATCHEXECUABALE ARGUMENT1 ARGUMENT2  >>OUTPUT_FILE
> EXITSTATUS="£?"
> ENDTIME=`date +'%d/%m/%Y-%H:%M:%S'`
> if ^ "£{EXITSTATUS}" = "£{SUCCESS}" ]
> then
> echo "BATCHEXECUABALE Succeeded"
> else
> echo "BATCHEXECUABALE failed with exit code
> £{EXITSTATUS}"
> fi
> echo "BATCHEXECUABALE £{EXITSTATUS} £{STARTTIME}
> £{ENDTIME}" >> BATCH_RUNLOG
> ===
> 
> HTH.
> Umesh
> --( Forwarded letter 1 follows
> )-
> Date: Thu, 04 Oct 2001 22:55:17 -0800
> To: [EMAIL PROTECTED]
> Sender: [EMAIL PROTECTED]
> Reply-To: [EMAIL PROTECTED]
> Reply-Copies-To: [EMAIL PROTECTED]
> 
> 
> Hallo,
> 
> Anyone whom can give me a good example on how to
> write in a unix script if I want to run sqlloader
> every Sunday at 6 o clock pm?
> Please give me an example.
> Thanksin advance.
> 
> Roland Sköldblom
> 
> 
> 
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author:
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).
> 
> 
> 
>
---
> 
> The contents of this e-mail are confidential to the
> ordinary user
> of the e-mail address to which it was addressed and
> may also be
> privileged. If you are not the addressee of this
> e-mail you should
> not copy, forward, disclose or otherwise use it or
> any part of it
> in any form whatsoever. If you have received this
> e-mail in error
> please notify us by telephone or e-mail the sender
> by replying to
> this message, and then delete this e-mail and other
> copies of it
> from your computer system. Thank you.
> 
> We reserve the right to monitor all e-mail
> communications through
> our network.
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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


__
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Wendy Y
  INET: [EMAIL PROTECTED]

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

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

Re: getting password request on connect internal

[Gene Sais]

are you part of the dba, oinstall group?

>>> [EMAIL PROTECTED] 10/05/01 11:43 AM >>>
I know this was discussed recently but I just did a major clean up of my
1,000+ unread posts so my apologies.

All of a sudden I'm unable to shutdown a database.  This is the alert_log:

Shutting down instance (immediate)
License high water mark = 21
Thu Oct  4 20:30:38 2001
SHUTDOWN: waiting for active calls to complete.



And when I try to connect internal to do a shutdown abort I get this:


Oracle Server Manager Release 3.1.6.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

ORA-03113: end-of-file on communication channel

SVRMGR> connect internal
Password:


I've already gone through every step  in the Oracle Note 69642.1 (Checklist
for Resolving CONNECT INTERNAL PASSWORD Issues) without any results.  Any
ideas?

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

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

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

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

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

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

WHERE CURRENT OF Question

[Bill Buchan]

Why does the following work?  I open a cursor with FOR UPDATE OF COLUMN_A 
and then do an update of COLUMNB, WHERE CURRENT OF the cursor.  Surely I 
shouldn't be allowed to do that?
(Ora 8.1.7.2)

Any insight appreciated!
Thanks
- Bill.


SQLWKS> create table test_table
  2> (
  3> column_anumber,
  4> column_bnumber
  5> )
  6>
Statement processed.
SQLWKS> insert into test_table values (1,2)
  2>
1 row processed.
SQLWKS> insert into test_table values (2,3)
  2>
1 row processed.
SQLWKS> insert into test_table values (3,4)
  2>
1 row processed.
SQLWKS> declare
  2> cursor c1 is select * from test_table for update of column_a;
  3> begin
  4> for i in c1 loop
  5> update test_table set column_b = column_b * 2
  6> where current of c1;
  7> end loop;
  8> end;
  9>
Statement processed.
SQLWKS> select *
  2> from test_table
  3>
COLUMN_A   COLUMN_B
-- --
  1  4
  2  6
  3  8
3 rows selected.


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

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

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

Opinions wanted for new Oracle Security Handbook

[Martin, Alan]

Title: Opinions wanted for new Oracle Security Handbook





Has anyone read this new book from Oracle Press, Published August 2001, 624 pages, ISBN 0072133252? Opinions - good/bad/indifferent?

Thanx,
Alan Martin
Principal Consultant
Defense Logistics Information Service
Battle Creek, Michigan

RE: perplexing plan?

[Daemen, Remco]

Doug,

Sorting is caused by the "distinct", and is probably the cause of your
performance problem. Try to limit the sorting to a minimal number of rows,
e.g. by creating a temp table containing all (including the multiple copies)
rows and then select the distinct values of that table. You could also try :
select distinct * from (select ..) to replace the select distinct.

Another tip: don't you hints unless you really have to ...

HTH,  Remco

-Oorspronkelijk bericht-
Van: Doug C [mailto:[EMAIL PROTECTED]]
Verzonden: vrijdag 5 oktober 2001 16:30
Aan: Multiple recipients of list ORACLE-L
Onderwerp: perplexing plan? 


I'm a little perplexed by this query and it's associated plan.  It's also a
big
performance problem.  The problem is the 35 million row table clearly.  But
looking at the plan at the bottom, I'm not sure where the sorting is going
on.
Would anyone say the index full scan on the 35 million row table is being
sorted?  Or does it look more like it's being fed to a nested loops query?

Thanks,
Doug

SELECT /*+ ORDERED INDEX(S_ S15_IX1) INDEX(BUS_FID F15_UK1) INDEX(STREET
A15_IX1) */  SDE.STREET.CFCC,  SDE.STREET.BUS_FID  ,S_.eminx,S_.eminy,
  S_.emaxx,S_.emaxy, BUS_FID.fid,BUS_FID.numofpts,BUS_FID.entity,
  BUS_FID.points,BUS_FID.rowid
FROM
 (SELECT /*+ INDEX(SP_ S15_IX1) */ DISTINCT sp_fid,eminx,eminy,emaxx,emaxy
  FROM SDE.S15 SP_   WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3
  AND SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx >=
:7
  AND SP_.emaxy >= :8) S_
,  SDE.STREET
 , SDE.F15 BUS_FID 
WHERE S_.sp_fid =  BUS_FID.fid 
AND S_.sp_fid = SDE.STREET.BUS_FID


call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.00   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch   45473.15 475.04 223532   66153503  0
4494
--- --   -- -- -- --
--
total   47473.15 475.04 223532   66153503  0
4494

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

Rows Row Source Operation
---  ---
   4494  HASH JOIN
   4494NESTED LOOPS
   4495  VIEW
   4495 SORT UNIQUE
   4817   INDEX RANGE SCAN (object id 7356)
   4494  TABLE ACCESS BY INDEX ROWID STREET
   8988INDEX UNIQUE SCAN (object id 7355)
33065402  TABLE ACCESS BY INDEX ROWID F15
33065403INDEX FULL SCAN (object id 7283)
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Doug C
  INET: [EMAIL PROTECTED]

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

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

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

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

RE: Output to Excel

[Thomas, Kevin]

Ramon,

I would add an alias onto the columns you are referencing in your cursors so
that they are called something like DATA. This way when you come to
reference FT later on you can use FT.DATA as the item to ouput to your file.

You appear to be referencing OUT_REC at line 65 incorrectly. OUT_REC is a
record containing items of data. As you have opened the cursor on line 63
with the statement FOR FT IN C_FACTURAS, you have now assigned all the
values that will be returned from the cursors into FT. In order to output
the details that are now held within FT, line 65 should read something like:

UTL_FILE.PUT_LINE(V_ARCHIVO, FT.DATA);
You really don't need OUT_REC anymore as you never move data into it.

I hope this makes sense, I'm never very good at explaining things... ;o)

Regards,
Kev.

__

Kevin Thomas
Technical Analyst
Deregulation Services
Calanais Ltd.
(2nd Floor East - Weirs Building)
Tel: 0141 568 2377
Fax: 0141 568 2366
http://www.calanais.com

-Original Message-
Sent: 05 October 2001 15:06
To: Multiple recipients of list ORACLE-L


Thomas,

I keep getting the error

LINE/COL ERROR
 -
65/4 PL/SQL: Statement ignored
65/36PLS-00302: component 'OUT_REC' must be declared
72/4 PL/SQL: Statement ignored
72/37PLS-00302: component 'OUT_REC' must be declared
SQL>

Any suggestion,

Gracias

Ramon Estevez
Dominican Republic
[EMAIL PROTECTED]


  1   CREATE OR REPLACE PACKAGE BODY PROCESAR_AGENCIAS AS
  2   PROCEDURE GENERAR_FACTURAS
  3( PGRUPO IN NUMBER,PCOMPANIAIN NUMBER,
  4  PFECHA_INICIAL IN DATE,  PFECHA_FINAL IN DATE,
  5  PAGENCIA   IN NUMBER ) AS
  6   CURSOR C_FACTURAS IS
  7 SELECT F.GRUPO||','||
  8F.COMPANIA||','||
  9F.TIPO_FACTURA||','||
 10F.AGENCIA||','||
 11F.FACTURA||','||
 12F.CLIENTE||','||
 13F.VENDEDOR||','||
 14   -- ZONA||','||
 15F.DOCUMENTO_COBRO||','||
 16F.FECHA||','||
 17F.FECHA_PAGO||','||
 18F.FECHA_VENCIMIENTO||','||
 19F.ESTATUS_COMISION||','||
 20F.COMISION_VENDEDOR||','||
 21F.MONTO||','||
 22F.MONTO_PAGADO||','||
 23F.IMPRESA||','
 24   -- ITBIS||','||
 25   -- DESCTO||','
 26 FROM FACTURAS F
 27WHERE
 28 F.GRUPO   = PGRUPO AND
 29 F.COMPANIA= PCOMPANIA  AND
 30 F.AGENCIA = PAGENCIA   AND
 31 F.FECHA BETWEEN PFECHA_INICIAL AND PFECHA_FINAL;
 32   CURSOR C_ITEM_FACTURAS IS
 33 SELECT I.GRUPO||','||
 34I.COMPANIA||','||
 35I.AGENCIA||','||
 36I.TIPO_FACTURA||','||
 37I.FACTURA||','||
 38I.LOCALIDAD||','||
 39I.ARTICULO||','||
 40I.SECUENCIA||','||
 41I.COSTO||','||
 42I.PRECIO_VENTA||','||
 43I.CANTIDAD||','||
 44I.ITBIS||','||
 45I.DESCTO||','
 46 FROM FACTURAS F, ITEM_FACTURAS I
 47WHERE
 48 F.GRUPO = PGRUPO AND
 49 F.COMPANIA  = PCOMPANIA  AND
 50 F.AGENCIA   = PAGENCIA   AND
 51 F.FECHA BETWEEN   PFECHA_INICIAL AND
 52   PFECHA_FINAL   AND
 53 I.GRUPO = F.GRUPOAND
 54 I.COMPANIA  = F.COMPANIA AND
 55 I.TIPO_FACTURA  = F.TIPO_FACTURA AND
 56 I.FACTURA   = F.FACTURA;
 57 V_ARCHIVO UTL_FILE.FILE_TYPE;
 58 REGISTRO  FACTURAS%ROWTYPE;
 59 OUT_REC   REGISTRO%TYPE;
 60   BEGIN
 61   -- Loop para el archivo de Facturas
 62 V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','FACTURAS.TXT', 'W');
 63 FOR FT IN C_FACTURAS
 64   LOOP
 65 UTL_FILE.PUT_LINE(V_ARCHIVO, FT.OUT_REC);
 66   END LOOP;
 67 UTL_FILE.FCLOSE(V_ARCHIVO);
 68   -- Loop para el archivo de Item Facturas
 69 V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','ITEM_FACTURAS.TXT',
'W');
 70 FOR IFT IN C_ITEM_FACTURAS
 71   LOOP
 72 UTL_FILE.PUT_LINE(V_ARCHIVO, IFT.OUT_REC);
 73   END LOOP;
 74 UTL_FILE.FCLOSE(V_ARCHIVO);
 75   END GENERAR_FACTURAS;
 76*  END PROCESAR_AGENCIAS;
SQL> /

Warning: Package Body created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE BODY PROCESAR_AGENCIAS:

LINE/COL ERROR
 -
65/4 PL/SQL: Statement ignored
65/36PLS-00302: component 'OUT_REC' must be declared
72/4 PL/SQL: Statement ignored
72/37PLS-00302: component 'OUT_REC' must be declared
SQL>

Ramon E. Estevez
[EMAIL PROTECTED] 
809-565-3121


-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Thomas, Kevin
Enviado el: Friday, 05 October, 2001 4:15 AM
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Output to Excel


I believe the error to be w

PL/SQL Web Toolkit

[Vadim Gorbounov]

Hi, listers, 

I've got task to parse urls in the database. There is a good toolkit - see
subj (packages, starting with owa_*). It was shipped with WebDB before, and
was free, now it seems to be a compounent of iAS. 
The question is, if anybody is using this product, is separate license
required?

Thank you,
Vadim Gorbounov
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Vadim Gorbounov
  INET: [EMAIL PROTECTED]

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

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

RE: Plan Table

[Amar Kumar Padhi]

Title: RE: Plan Table



correction utlxplan.sql 
and not utlexp.sql.
Thankyou 
Kirti.
rgds amar 
 

  -Original Message-From: Amar Kumar Padhi 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, October 05, 2001 6:00 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Plan Table
  some options: Check if grants are 
  given to public to use this table. Check if the public 
  synonym on the table exists. Check if the table 
  belongs to the Oracle version you are running. Structure differs from older 
  versions. Re-run your utlexp.sql script to recreate 
  the structure. 
  rgds amar 
  -Original Message- From: Hamid 
  Alavi [mailto:[EMAIL PROTECTED]] 
  Sent: Friday, September 28, 2001 2:50 AM To: Multiple recipients of list ORACLE-L Subject: Plan Table 
  hi list, I get the following error msg 
  when I try to run explain plan: ORA-02404: specified 
  plan table not found but when i checked i can see 
  PLAN_TABLE in list of my tables 
  Hamid Alavi Office 818 737-0526 
  Cell    818 402-1987 
  The information contained in this message and any attachments 
  is intended only for the use of the individual or 
  entity to which it is addressed, and may contain 
  information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in 
  error, you are prohibited from copying, distributing, 
  or using the information. Please contact the sender 
  immediately by return e-mail and delete the original 
  message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi   INET: [EMAIL PROTECTED] 
  Fat City Network Services    -- (858) 
  538-5051  FAX: (858) 538-5051 San Diego, 
  California    -- Public Internet access 
  / Mailing Lists  
  To REMOVE yourself from this mailing list, send an E-Mail 
  message to: [EMAIL PROTECTED] (note EXACT spelling 
  of 'ListGuru') and in the message BODY, include a line 
  containing: UNSUB ORACLE-L (or the name of mailing 
  list you want to be removed from).  You may also 
  send the HELP command for other information (like subscribing). 

RE: perplexing plan?

[Koivu, Lisa]

Title: RE: perplexing plan? 





Doug, it's your distinct in the subquery that's causing the sort.  Do you need to have distinct in there? 


By the way, why do you have index hints in the outer query?  Are you only sending us half the query?  


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



-Original Message-
From:   Doug C [SMTP:[EMAIL PROTECTED]]
Sent:   Friday, October 05, 2001 10:30 AM
To: Multiple recipients of list ORACLE-L
Subject:    perplexing plan? 


I'm a little perplexed by this query and it's associated plan.  It's also a big
performance problem.  The problem is the 35 million row table clearly.  But
looking at the plan at the bottom, I'm not sure where the sorting is going on.
Would anyone say the index full scan on the 35 million row table is being
sorted?  Or does it look more like it's being fed to a nested loops query?


Thanks,
Doug





SELECT /*+ ORDERED INDEX(S_ S15_IX1) INDEX(BUS_FID F15_UK1) INDEX(STREET
  A15_IX1) */  SDE.STREET.CFCC,  SDE.STREET.BUS_FID  ,S_.eminx,S_.eminy,
  S_.emaxx,S_.emaxy, BUS_FID.fid,BUS_FID.numofpts,BUS_FID.entity,
  BUS_FID.points,BUS_FID.rowid
FROM
 (SELECT /*+ INDEX(SP_ S15_IX1) */ DISTINCT sp_fid,eminx,eminy,emaxx,emaxy
  FROM SDE.S15 SP_   WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3
AND
  SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx >= :7
  AND SP_.emaxy >= :8) S_,  SDE.STREET , SDE.F15 BUS_FID WHERE S_.sp_fid =
  BUS_FID.fid AND S_.sp_fid = SDE.STREET.BUS_FID



call count   cpu    elapsed   disk  query    current
rows
--- --   -- -- -- --
--
Parse    1  0.00   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch   45    473.15 475.04 223532   66153503  0
4494
--- --   -- -- -- --
--
total   47    473.15 475.04 223532   66153503  0
4494


Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20


Rows Row Source Operation
---  ---
   4494  HASH JOIN
   4494    NESTED LOOPS
   4495  VIEW
   4495 SORT UNIQUE
   4817   INDEX RANGE SCAN (object id 7356)
   4494  TABLE ACCESS BY INDEX ROWID STREET
   8988    INDEX UNIQUE SCAN (object id 7355)
33065402  TABLE ACCESS BY INDEX ROWID F15
33065403    INDEX FULL SCAN (object id 7283)
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Doug C
  INET: [EMAIL PROTECTED]


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

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

Re: Oracle 8.1.7 on Solaris vs. Linux

[Jared Still]

Yeah, I've used AIX.  I prefer Linux, thank you.  :)

Jared

On Thursday 04 October 2001 14:35, Gogala, Mladen wrote:
> The problem is that Oracle supports Linux on Intel
> and IBM does not make RS/6000 with Intel CPU. I''m also
> not sure whether there is a sufficient customer base for
> Oracle to start supporting the PPC Linux. On the other hand,
> there is a Unix-like OS called AIX which does support Oracle.
>
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> > Sent: Thursday, October 04, 2001 2:25 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Oracle 8.1.7 on Solaris vs. Linux
> >
> >
> >
> >
> > I don't have experience with Linux on RS/6000, but I would sure
> > like to give it a try.
> >
> > An RS/6000 is a much faster box than a more expensive Sun box.
> >
> > Since IBM supports Linux on their HW, and Oracle supports
> > their RDBMS on Linux, I would jump at the chance for this combination.
> >
> > Jared
> >
> >
> >
> >
> >
> >
> > "Lord, David -
> >
> > C&S" To: Multiple
> > recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >  >
> > yscsg.com>   Subject: Oracle
> > 8.1.7 on Solaris vs. Linux
> > Sent by:
> >
> > [EMAIL PROTECTED]
> >
> > om
> >
> >
> >
> >
> >
> > 10/04/01 10:05
> >
> > AM
> >
> > Please respond
> >
> > to ORACLE-L
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > List
> >
> > Does anyone have any strong opinions or useful reference on the
> > performance,
> > reliability and scalability of Oracle (probably 8.1.7) on
> > Linux (SuSE?) as
> > against Solaris?
> >
> > We're spec'ing up some kit for a new production d/b and the cost of
> > upgrading one of our existing Suns is so high that it would
> > probably be
> > cheaper to buy a new Linux box (I would guess an IBM).  The
> > database is
> > going to be around 20Gb & we were looking at ~4 processors
> > and ~2Gb RAM.
> >
> > Regards
> > David Lord
> > Senior DBA, Hays Consulting & Solutions
> >
> > email: [EMAIL PROTECTED]
> > Tel..: +44 (0)29 2054 4013
> > Fax..: +44 (0)29 2069 2464
> >
> >
> >
> > **
> > This message (including any attachments) is confidential and may be
> > legally privileged.  If you are not the intended recipient, you should
> > not disclose, copy or use any part of it - please delete all copies
> > immediately and notify the Hays Group Email Helpdesk at
> > [EMAIL PROTECTED]
> > Any information, statements or opinions contained in this message
> > (including any attachments) are given by the author.  They are not
> > given on behalf of Hays unless subsequently confirmed by an individual
> > other than the author who is duly authorised to represent Hays.
> >
> > A member of the Hays plc group of companies.
> > Hays plc is registered in England and Wales number 2150950.
> > Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.
> > **
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Lord, David - C&S
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> > San Diego, California-- Public Internet access / Mailing Lists
> > 
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> > San Diego, California-- Public Internet access / Mailing Lists
> > 
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from

RE: Installation of designer 6.0 with personal oracle 8.1.6

[Christopher Spence]

I had a lot of problems mucking with designer, but I was told at the time to
Install Designer first, hope that helps, really the rest is just following
directions and crossing your fingers.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, October 05, 2001 10:06 AM
To: Multiple recipients of list ORACLE-L

Hi all ,
I want to install designer 6.0 on my laptop with personal oracle 8.1.6.
I haven't got any documents which states that designer can be installed with
personal oracle .
I need help to install it with personal oracle .
Thanks in advance ..

--- Brajesh

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

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

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

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

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

Developer 2000 V. 2.0 vs V.6.0

[DBAtracker]

I just bought a new PC and was going to install Personal Edition 8i  and Developer 2000 v. 6.0 as I have it on my old computer.  However, being a little tired at the time, I installed the earlier version of Developer 2000 (v. 2.0) instead of 6.0. I know there are compatability issues between  Developer 6.0 and Oracle Personal Edition 8.0.
I tried to uninstall the Developer 2.0 but it didn't seem to work.  I'm thinking I should be able to just install Developer version  6.0 on top of the 2.0, effectively just doing an upgrade but I'm wondering if this is ok before I load 8i (Developer must be loaded before 8i for a proper installation.)  Can anyone confirm that installing 6.0 will work ok without first removing Developer 2.0??  Appreciate any comments or suggestions. 
Bill Johnson

RE: SQL Server E-mail List

[Farnsworth, Dave]

Ken,

Here is a good list for SQL Server.

http://ls.swynk.com/scripts/lyris.pl?site=swynk.com&page=topic&topic=sqlserv
er&text_mode=&lang=english

Dave

-Original Message-
Sent: Friday, October 05, 2001 7:45 AM
To: Multiple recipients of list ORACLE-L


Does anyone know of a SQL Server e-mail list such as Oracle-L?

Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient Systems, Inc.
Minneapolis, MN
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ken Janusz
  INET: [EMAIL PROTECTED]

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

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

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

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

RE: getting password request on connect internal - more info

[Miller, Jay]

Oops.
Oracle 8.1.6.3
Solaris 2.6

-Original Message-
Sent: Friday, October 05, 2001 10:03 AM
To: '[EMAIL PROTECTED]'


I know this was discussed recently but I just did a major clean up of my
1,000+ unread posts so my apologies.

All of a sudden I'm unable to shutdown a database.  This is the alert_log:

Shutting down instance (immediate)
License high water mark = 21
Thu Oct  4 20:30:38 2001
SHUTDOWN: waiting for active calls to complete.



And when I try to connect internal to do a shutdown abort I get this:


Oracle Server Manager Release 3.1.6.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

ORA-03113: end-of-file on communication channel

SVRMGR> connect internal
Password:


I've already gone through every step  in the Oracle Note 69642.1 (Checklist
for Resolving CONNECT INTERNAL PASSWORD Issues) without any results.  Any
ideas?

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

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

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

RE: Output to Excel

[Mercadante, Thomas F]

Ramon,

You need to add an alias to each of the cursors to declare the column
OUT_REC.

Like:

  6   CURSOR C_FACTURAS IS
  7 SELECT F.GRUPO||','||
  8F.COMPANIA||','||
  9F.TIPO_FACTURA||','||
 10F.AGENCIA||','||
 11F.FACTURA||','||
 12F.CLIENTE||','||
 13F.VENDEDOR||','||
 14   -- ZONA||','||
 15F.DOCUMENTO_COBRO||','||
 16F.FECHA||','||
 17F.FECHA_PAGO||','||
 18F.FECHA_VENCIMIENTO||','||
 19F.ESTATUS_COMISION||','||
 20F.COMISION_VENDEDOR||','||
 21F.MONTO||','||
 22F.MONTO_PAGADO||','||
 23F.IMPRESA||','   OUT_REC  <==
 24   -- ITBIS||','||
 25   -- DESCTO||','
 26 FROM FACTURAS F
 27WHERE
 28 F.GRUPO   = PGRUPO AND
 29 F.COMPANIA= PCOMPANIA  AND
 30 F.AGENCIA = PAGENCIA   AND
 31 F.FECHA BETWEEN PFECHA_INICIAL AND PFECHA_FINAL;

Hope this helps

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, October 05, 2001 10:06 AM
To: Multiple recipients of list ORACLE-L


Thomas,

I keep getting the error

LINE/COL ERROR
 -
65/4 PL/SQL: Statement ignored
65/36PLS-00302: component 'OUT_REC' must be declared
72/4 PL/SQL: Statement ignored
72/37PLS-00302: component 'OUT_REC' must be declared
SQL>

Any suggestion,

Gracias

Ramon Estevez
Dominican Republic
[EMAIL PROTECTED]


  1   CREATE OR REPLACE PACKAGE BODY PROCESAR_AGENCIAS AS
  2   PROCEDURE GENERAR_FACTURAS
  3( PGRUPO IN NUMBER,PCOMPANIAIN NUMBER,
  4  PFECHA_INICIAL IN DATE,  PFECHA_FINAL IN DATE,
  5  PAGENCIA   IN NUMBER ) AS
  6   CURSOR C_FACTURAS IS
  7 SELECT F.GRUPO||','||
  8F.COMPANIA||','||
  9F.TIPO_FACTURA||','||
 10F.AGENCIA||','||
 11F.FACTURA||','||
 12F.CLIENTE||','||
 13F.VENDEDOR||','||
 14   -- ZONA||','||
 15F.DOCUMENTO_COBRO||','||
 16F.FECHA||','||
 17F.FECHA_PAGO||','||
 18F.FECHA_VENCIMIENTO||','||
 19F.ESTATUS_COMISION||','||
 20F.COMISION_VENDEDOR||','||
 21F.MONTO||','||
 22F.MONTO_PAGADO||','||
 23F.IMPRESA||','
 24   -- ITBIS||','||
 25   -- DESCTO||','
 26 FROM FACTURAS F
 27WHERE
 28 F.GRUPO   = PGRUPO AND
 29 F.COMPANIA= PCOMPANIA  AND
 30 F.AGENCIA = PAGENCIA   AND
 31 F.FECHA BETWEEN PFECHA_INICIAL AND PFECHA_FINAL;
 32   CURSOR C_ITEM_FACTURAS IS
 33 SELECT I.GRUPO||','||
 34I.COMPANIA||','||
 35I.AGENCIA||','||
 36I.TIPO_FACTURA||','||
 37I.FACTURA||','||
 38I.LOCALIDAD||','||
 39I.ARTICULO||','||
 40I.SECUENCIA||','||
 41I.COSTO||','||
 42I.PRECIO_VENTA||','||
 43I.CANTIDAD||','||
 44I.ITBIS||','||
 45I.DESCTO||','
 46 FROM FACTURAS F, ITEM_FACTURAS I
 47WHERE
 48 F.GRUPO = PGRUPO AND
 49 F.COMPANIA  = PCOMPANIA  AND
 50 F.AGENCIA   = PAGENCIA   AND
 51 F.FECHA BETWEEN   PFECHA_INICIAL AND
 52   PFECHA_FINAL   AND
 53 I.GRUPO = F.GRUPOAND
 54 I.COMPANIA  = F.COMPANIA AND
 55 I.TIPO_FACTURA  = F.TIPO_FACTURA AND
 56 I.FACTURA   = F.FACTURA;
 57 V_ARCHIVO UTL_FILE.FILE_TYPE;
 58 REGISTRO  FACTURAS%ROWTYPE;
 59 OUT_REC   REGISTRO%TYPE;
 60   BEGIN
 61   -- Loop para el archivo de Facturas
 62 V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','FACTURAS.TXT', 'W');
 63 FOR FT IN C_FACTURAS
 64   LOOP
 65 UTL_FILE.PUT_LINE(V_ARCHIVO, FT.OUT_REC);
 66   END LOOP;
 67 UTL_FILE.FCLOSE(V_ARCHIVO);
 68   -- Loop para el archivo de Item Facturas
 69 V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','ITEM_FACTURAS.TXT',
'W');
 70 FOR IFT IN C_ITEM_FACTURAS
 71   LOOP
 72 UTL_FILE.PUT_LINE(V_ARCHIVO, IFT.OUT_REC);
 73   END LOOP;
 74 UTL_FILE.FCLOSE(V_ARCHIVO);
 75   END GENERAR_FACTURAS;
 76*  END PROCESAR_AGENCIAS;
SQL> /

Warning: Package Body created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE BODY PROCESAR_AGENCIAS:

LINE/COL ERROR
 -
65/4 PL/SQL: Statement ignored
65/36PLS-00302: component 'OUT_REC' must be declared
72/4 PL/SQL: Statement ignored
72/37PLS-00302: component 'OUT_REC' must be declared
SQL>

Ramon E. Estevez
[EMAIL PROTECTED] 
809-565-3121


-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Thomas, Kevin
Enviado el: Friday, 05 Oc

getting password request on connect internal

[Miller, Jay]

I know this was discussed recently but I just did a major clean up of my
1,000+ unread posts so my apologies.

All of a sudden I'm unable to shutdown a database.  This is the alert_log:

Shutting down instance (immediate)
License high water mark = 21
Thu Oct  4 20:30:38 2001
SHUTDOWN: waiting for active calls to complete.



And when I try to connect internal to do a shutdown abort I get this:


Oracle Server Manager Release 3.1.6.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

ORA-03113: end-of-file on communication channel

SVRMGR> connect internal
Password:


I've already gone through every step  in the Oracle Note 69642.1 (Checklist
for Resolving CONNECT INTERNAL PASSWORD Issues) without any results.  Any
ideas?

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

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

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

SOS - Please help

[VeniVas]

Env :Oracle 8.0.5, HP/UX 11
 

Action : Tried to rebuild a corrupt partitioned 
index on a partitioned table, partitioned on date, having 230 mil 
rows.
 
Problem : The db is continuously generating ORA 
600-2126 followed by ORA600-4137 and ending in Db crash.  Metalink has not 
been heplful. All the Rollback extents are showing negative nos.
 
Now I cant get the db to run with Oracle 
Express.  Even trying to login to Express form, crashes the db with the 
above said ORA600.  Oracle Support has asked to rebuild the 
db.
 
Is there any other way to do this ??
 
TIA
 
Srini

RE: Droping System User

[Guy Hammond]

Title: RE: Droping System User



You 
know, I would have thought that if Oracle was going to give you messages in 
French, that you would be able to write SQL in French too, like 

 
SLdI> effacement de sys.user$ où 
name='SYS'; 
SLdI> commettez;
 
(SQL 
is "Structuré Langage d'interrogation" in French :0) )
 
g
 

  -Original Message-From: Jacques Kilchoer 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 04, 2001 
  10:30 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Droping System User
  > -Original Message- > 
  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
  > > Dropping the SYSTEM user is 
  not really all that traumatic. > > In most cases it's just a DBA account that may own some pieces 
  you'd > rather not lose, but dropping SYSTEM will 
  not be noticed by the users > of the 
  database. 
  That's the impression I get. When I looked at the objects 
  owned by SYSTEM in my test database before the drop, none of them seemed 
  vital.
  On the other hand, dropping sys would probably be bad, but I 
  wasn't able to do that (insufficient privileges.) Trying to drop sys tables like sys.user$ caused an ORA-00701. 
  So since I'm on a quest to wound this database, I tried 
  this: 
  SQL> delete from sys.user$ where name = 'SYS' ; 
  1 ligne supprimée. SQL> commit 
  ; Validation effectuée. 
  So far no ill effects! 

Re: dbsnmp agent

[Ruth Gramolini]

Do you have any sceduled events or scheduled jobs that the agent runs on
this box?

Just a thot,
Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, October 04, 2001 5:10 PM


> I have the dbsnmp agent running on a few boxes.  One of them is acting a
> little weird.  It is eating up a fair amount of CPU.  If I compare this
box
> to one of my others this is what the difference would be
>
> Box with no issues
> Oracle 8.1.7.1 32 bit
> HPUX 11i 64 bit
> 3 databases
> Not that many datafiles even taking into account the 3 databases.
>
> Box with issues
> Oracle 8.1.7.1 32 bit
> HPUX 11  64 bit
> 1 databases
> many datafiles (113 which means I have 113 tablespaces)
>
> I am thinking its due to the fact that its trying to monitor for
tablespace
> full
> and is having an issue because of this.  In realitity it should have paged
> me because
> they are kept near 100% full.  Yet it never has.
>
> Does anyone have a similiar setup with OEM monitoring tablespaces and
having
> a lot
> of tablespaces.  If so, can you determine that you have a process that is
> eating near
> 100% of a CPU?
>
> Thanks,
>
>
> 
> Kimberly Smith
> GMD Fujitsu
> Database Administrator
> (503) 669-6050
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Kimberly Smith
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

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

RE: OPS Internal Secrets? WAS:: RE: Michael Jenkins (Nextel)

[Henry Poras]

Oooh!! Gives new meaning to having the right sin-tax.

Henry

-Original Message-
Sent: Thursday, October 04, 2001 6:42 PM
To: Multiple recipients of list ORACLE-L


Wow!!!.including everything on the so-called "SQL*Sluts"??? . 

On topic Oracle Question:

Using oracle JDeveloper, is there any way to use a method "Class.Method" 
without having to load the *entire* Class?



-Original Message-
Sent: Thursday, October 04, 2001 6:18 PM
To: Multiple recipients of list ORACLE-L


The top-secret diary of Lawrence Ellison.

-Original Message-
Sent: Thursday, October 04, 2001 4:50 PM
To: Multiple recipients of list ORACLE-L


Sounds interesting!

What, pray tell, are in "the documents"?


Wow...internal OPS secrets?



-Original Message-
Sent: Thursday, October 04, 2001 3:51 PM
To: Multiple recipients of list ORACLE-L


Michael  -

This is Brian McGraw, from the OPS class.  Please email me - I found the
documents that we discussed.

Brian
--
--
| Brian McGraw -- Oracle DBA |
| Central Alabama Oracle Users Group |
||
| mailto:[EMAIL PROTECTED]  |
| http://bmcgraw.home.mindspring.com |
--


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

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

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

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

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

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

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

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

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

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

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

RE: Backup Strategy

[Guy Hammond]

That is a good point, it is important to consider who will be doing the
recovery. If it is a relatively small and static database, and the
recovery will be performed by an Unix admin (i.e. not a specialist DBA)
then export/cold backup does make a lot of sense. The main issue to
consider is $$$. If the organization won't lose any $$$ from the
database being down for as long as it takes to recover from a cold
backup, then the simplest technique is sufficient.


Cheers,

g
 

-Original Message-
Sent: Thursday, October 04, 2001 10:11 PM
To: Multiple recipients of list ORACLE-L


Well for 1 reason, Cold backups are restored using OS cmds, no need for
Oracle recovery, whereas, Hot Backups require OS cmds + Oracle recovery.
One exception is pt in time recoveries.  I do have 1 db that will be
web-enabled, therefore 24x7.  So guess what I have to do, Hot Backups.
I am not against them, just prefer cold.   Anyone have hot backup
scripts?  Lisa, you are the script ninja, got 1 of those scripts lying
aorund.  Thanks :)

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

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

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

Re: Looking for a SQL Server DBA

[Jared Still]

That pesky  button got ya, didn't it?  ;)

Jared

On Friday 05 October 2001 07:35, Jeff Wiegard wrote:
> Hi Ken. I'm currently on a short-term contract. Looking for
> something interesting to do. I do not have a Word version of my
> resume with me, but this text version is a start. I do not have
> years of SQL server, but have worked with advanced SQL Server
> technologies such as replication and Data Transformation Services.
> Here is a link promoting a project I worked on. I handled much of
> the backend for the project, which was, and most likely still is, at
> the pilot stage in a hotel here in Minneapolis:
>
> http://www.microsoft.com/mobile/enterprise/casestudies/cs-carlson.asp
>
> Thanks,
>
> Jeff
>
> >>> [EMAIL PROTECTED] 10/05/01 08:50AM >>>
>
> My company is looking to hire a full-time, permanent SQL Server
> DBA.  That
> person's focus will be on converting databases to SQL Server and
> then
> providing post conversion support.  This position is for our office
> in
> Minneapolis, MN.  No relocation expenses.
>
> If you know of anyone who might be interested in such a position,
> please
> have them contact me.
>
> Thanks,
> Ken Janusz, CPIM
> Oracle Database Conversion Lead
> Sufficient Systems, Inc.
> Minneapolis, MN
>
> P.S.:  I have been looking for a local SQL SERVER user group and an
> e-mail
> list that works with no success.


Content-Type: text/plain; charset="us-ascii"; name="Attachment: 1"
Content-Transfer-Encoding: 7bit
Content-Description: 

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

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

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

weekends/holidays

[lhoska]

Hi list,
Is there a function in Oracle that will determine if particular calendar day
is weekend or a US holiday.  I need to write a function that will check the
data integrity of my db.  It will check data entered into the system and
notice any gaps.  I have to go by days(i.e. every day there should be an
entry/ies unless it is a weekend or holiday.)
Thank you in advance,

Lyuda Hoska

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

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

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

Re: Looking for a SQL Server DBA

[Jeff Wiegard]

You know, I'm using some Groupwise mail software, and I had two
choices: Reply to sender and reply to everyone in the group.
Apparently, neither does what I wanted ...

Now hundreds of strangers know what I've been up to 

>>> [EMAIL PROTECTED] 10/05/01 09:35AM >>>
Hi Ken. I'm currently on a short-term contract. Looking for
something interesting to do. I do not have a Word version of my
resume with me, but this text version is a start. I do not have
years of SQL server, but have worked with advanced SQL Server
technologies such as replication and Data Transformation Services.
Here is a link promoting a project I worked on. I handled much of
the backend for the project, which was, and most likely still is,
at
the pilot stage in a hotel here in Minneapolis:

http://www.microsoft.com/mobile/enterprise/casestudies/cs-carlson.asp


Thanks,

Jeff

>>> [EMAIL PROTECTED] 10/05/01 08:50AM >>>
My company is looking to hire a full-time, permanent SQL Server
DBA.  That
person's focus will be on converting databases to SQL Server and
then
providing post conversion support.  This position is for our
office
in
Minneapolis, MN.  No relocation expenses.

If you know of anyone who might be interested in such a position,
please
have them contact me.

Thanks,
Ken Janusz, CPIM
Oracle Database Conversion Lead
Sufficient Systems, Inc.
Minneapolis, MN

P.S.:  I have been looking for a local SQL SERVER user group and
an
e-mail
list that works with no success.

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

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

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

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

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

RE: ORA-24323 & ORA-03113 & ORA-01034 & ORA-27101 errors

[ALEMU Abiy]

This is a problem related to NET8 parameter files (tnsnames or listener).
Check the parameters in these files.

-Message d'origine-
De : Okan CIMEN [mailto:[EMAIL PROTECTED]]
Envoyé : vendredi 5 octobre 2001 14:35
À : Multiple recipients of list ORACLE-L
Objet : ORA-24323 & ORA-03113 & ORA-01034 & ORA-27101 errors


Hello,

I get strange errors from my DB server. Here are the outputs;

$ sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 5 14:24:07 2000
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Enter user-name: internal
Connected to an idle instance.
SQL> startup force
ORA-24323: value not allowed
ORA-03113: end-of-file on communication channel

When I try to log on as any user I get these messages;
SQL> connect anyuser
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

I am still connected to the server as a user but when I do ps -ef | grep
ora_ , I get nothing. Can someone please tell me what path I shall take?

Regards

Okan




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

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

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

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

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

Re: Looking for a SQL Server DBA

[Jeff Wiegard]

Hi Ken. I'm currently on a short-term contract. Looking for
something interesting to do. I do not have a Word version of my
resume with me, but this text version is a start. I do not have
years of SQL server, but have worked with advanced SQL Server
technologies such as replication and Data Transformation Services.
Here is a link promoting a project I worked on. I handled much of
the backend for the project, which was, and most likely still is, at
the pilot stage in a hotel here in Minneapolis:

http://www.microsoft.com/mobile/enterprise/casestudies/cs-carlson.asp

Thanks,

Jeff

>>> [EMAIL PROTECTED] 10/05/01 08:50AM >>>
My company is looking to hire a full-time, permanent SQL Server
DBA.  That
person's focus will be on converting databases to SQL Server and
then
providing post conversion support.  This position is for our office
in
Minneapolis, MN.  No relocation expenses.

If you know of anyone who might be interested in such a position,
please
have them contact me.

Thanks,
Ken Janusz, CPIM
Oracle Database Conversion Lead
Sufficient Systems, Inc.
Minneapolis, MN

P.S.:  I have been looking for a local SQL SERVER user group and an
e-mail
list that works with no success.

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

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

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

perplexing plan?

[Doug C]

I'm a little perplexed by this query and it's associated plan.  It's also a big
performance problem.  The problem is the 35 million row table clearly.  But
looking at the plan at the bottom, I'm not sure where the sorting is going on.
Would anyone say the index full scan on the 35 million row table is being
sorted?  Or does it look more like it's being fed to a nested loops query?

Thanks,
Doug




SELECT /*+ ORDERED INDEX(S_ S15_IX1) INDEX(BUS_FID F15_UK1) INDEX(STREET
  A15_IX1) */  SDE.STREET.CFCC,  SDE.STREET.BUS_FID  ,S_.eminx,S_.eminy,
  S_.emaxx,S_.emaxy, BUS_FID.fid,BUS_FID.numofpts,BUS_FID.entity,
  BUS_FID.points,BUS_FID.rowid
FROM
 (SELECT /*+ INDEX(SP_ S15_IX1) */ DISTINCT sp_fid,eminx,eminy,emaxx,emaxy
  FROM SDE.S15 SP_   WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3
AND
  SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx >= :7
  AND SP_.emaxy >= :8) S_,  SDE.STREET , SDE.F15 BUS_FID WHERE S_.sp_fid =
  BUS_FID.fid AND S_.sp_fid = SDE.STREET.BUS_FID


call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.00   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch   45473.15 475.04 223532   66153503  0
4494
--- --   -- -- -- --
--
total   47473.15 475.04 223532   66153503  0
4494

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

Rows Row Source Operation
---  ---
   4494  HASH JOIN
   4494NESTED LOOPS
   4495  VIEW
   4495 SORT UNIQUE
   4817   INDEX RANGE SCAN (object id 7356)
   4494  TABLE ACCESS BY INDEX ROWID STREET
   8988INDEX UNIQUE SCAN (object id 7355)
33065402  TABLE ACCESS BY INDEX ROWID F15
33065403INDEX FULL SCAN (object id 7283)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Doug C
  INET: [EMAIL PROTECTED]

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

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

Installation of designer 6.0 with personal oracle 8.1.6

[Oracle DBA]

Hi all ,
I want to install designer 6.0 on my laptop with personal oracle 8.1.6.
I haven't got any documents which states that designer can be installed with
personal oracle .
I need help to install it with personal oracle .
Thanks in advance ..

--- Brajesh

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

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

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

Re: Backup Strategy

[Joe Testa]

Jeremiah, you must evidently not know Jared based on your reply.  He
didnt detail the step but gave an overall theory.  I know his scripts
wouldnt take anything on blind faith(unless of course he's the ONLY one
who does DBA work on that database, then if he's as anal as me, he still
wouldnt trust himself) ;)

joe
Jeremiah Wilton wrote:
> 
> On Thu, 4 Oct 2001, Gene Sais wrote:
> 
> > 2) Cold backups follow the KISS principle.  Shutdown db, tar, dump,
> > cpio, dd, etc. the datafiles, redo logs, ctl files, oracle
> > filesystems, etc. to tape, Startup db, Done.  Take the tape to same or
> > another server and restore, No Oracle cmds required (filesystems
> > being the same).
> 
> Seems like if you just tar everything up without querying the database
> for the file locations, you stand a chance of missing files.  You are
> relying on the good will of all involved to put new datafiles under
> the mountpoints you are backing up.  If someone makes a mistake and
> puts a new datafile in a different place, it can result in that file
> not getting backed up.  All backup scripts, hot and cold, should be
> obtaining the file locations from the database instance.  You are not
> really saving anything by avoiding "oracle commands."
> 
> Jared wrote:
> 
> > As for cold backups, and I'm sure you've heard this already, they're
> > only really needed after you open a database with 'resetlogs' .
> > Otherwise it is not necessary to use a cold backup.
> 
> What?  you haven't recovered past resetlogs before?
> 
> 8-)
> 
> --
> Jeremiah Wilton
> http://www.speakeasy.net/~jwilton
> 

-- 
Joe Testa  
Performing Remote DBA Services, need some backup DBA support?
For Sale: Oracle-dba.com domain, its not going cheap but feel free to
ask :)
IM: n8xcthome or joen8xct
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Testa
  INET: [EMAIL PROTECTED]

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

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

RE: Restricted mode

[Tatireddy, Shrinivas (MED, Keane)]

Youcan issue a command

svrmgrl>alter system enable restricted session;  

- this moves the db into restricted mode.
- but this will not affect the existing logged users.

svrmgrl>alter system disable restricted session;

-- removes the disable mode and allows the users to log in

srinivas

-Original Message-
Sent: Friday, October 05, 2001 10:35 AM
To: Multiple recipients of list ORACLE-L



Is there a way to change a database from being up in restricted mode
to open for all without shutting down and restarting?

Thanks


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

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

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

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

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

RE: Plan Table

[Amar Kumar Padhi]

Title: RE: Plan Table






some options:
Check if grants are given to public to use this table.
Check if the public synonym on the table exists.
Check if the table belongs to the Oracle version you are running. Structure differs from older versions.
Re-run your utlexp.sql script to recreate the structure.



rgds
amar




-Original Message-
From: Hamid Alavi [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 28, 2001 2:50 AM
To: Multiple recipients of list ORACLE-L
Subject: Plan Table



hi list,
I get the following error msg when I try to run explain plan:
ORA-02404: specified plan table not found
but when i checked i can see PLAN_TABLE in list of my tables




Hamid Alavi
Office 818 737-0526
Cell    818 402-1987


The information contained in this message and any attachments is intended
only for the use of the individual or entity to which it is addressed, and
may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from
disclosure under applicable law. If you have received this message in error,
you are prohibited from copying, distributing, or using the information.
Please contact the sender immediately by return e-mail and delete the
original message from your system.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hamid Alavi
  INET: [EMAIL PROTECTED]


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

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

RE: Restricted mode

[Christopher Spence]

Alter system enable restricted session;
Alter system disable restricted session;

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, October 05, 2001 9:35 AM
To: Multiple recipients of list ORACLE-L


Is there a way to change a database from being up in restricted mode
to open for all without shutting down and restarting?

Thanks


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

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

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

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

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

RE: Problems with patchsets to 8.1.7.0.0 on Windows 2000

[Sinard Xing]

Hi,

May be you are not using Administrator account.
Or the patch set is installed after you click on it,
You can do
in your oracle_home
dir /s/p/od

it will sort by date which file is the newest.


Sorry, I just a beginner, can't help much.


Sinardy


-Original Message-
Granaman
Sent: Wednesday, 3 October 2001 2:29 AM
To: Multiple recipients of list ORACLE-L


I have Oracle 8.1.7.0.0 running on a Windows 2000 Pro (sp2).  I tried to
install
the 8.1.7.2.1 patchset according to the "readme" directions (shutdown all
Oracle* services first, etc.), but when I try to run "setup.exe", nothing
happens.  The icon flashes momentarily after being double-clicked, but
nothing
ever actually runs.  I tried it a number of times.  Then I tried it with an
older 8.1.7.1.1 patchset - and the exactly same thing happened.  These
patchsets
are for "Windows NT", but I assumed they would work for 2000 as well.  (Am I
wrong in this assumption?)  Does anyone have any experience with this and/or
workarounds?

-Don Granaman
[OraSaurus - Honk if you remember UFI!]

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

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

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

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

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

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

Re: ORA-24323 & ORA-03113 & ORA-01034 & ORA-27101 errors

[C.S.Venkata Subramanian]

ORA-24323: value not allowed
For this Chk ur init.ora file. there may be some values that are not supported by the 
version of Oracle u r using. U r connected as internal user. Rectify the first error, 
then all the errors will go away.

HTH
Regards
Venkat
--

On Fri, 05 Oct 2001 04:35:19  
 Okan CIMEN wrote:
>Hello,
>
>I get strange errors from my DB server. Here are the outputs;
>
>$ sqlplus
>SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 5 14:24:07 2000
>(c) Copyright 2000 Oracle Corporation.  All rights reserved.
>Enter user-name: internal
>Connected to an idle instance.
>SQL> startup force
>ORA-24323: value not allowed
>ORA-03113: end-of-file on communication channel
>
>When I try to log on as any user I get these messages;
>SQL> connect anyuser
>Enter password:
>ERROR:
>ORA-01034: ORACLE not available
>ORA-27101: shared memory realm does not exist
>SVR4 Error: 2: No such file or directory
>
>I am still connected to the server as a user but when I do ps -ef | grep
>ora_ , I get nothing. Can someone please tell me what path I shall take?
>
>Regards
>
>Okan
>
>
>
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Okan CIMEN
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>


Make a difference, help support the relief efforts in the U.S.
http://clubs.lycos.com/live/events/september11.asp
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: C.S.Venkata Subramanian
  INET: [EMAIL PROTECTED]

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

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

RE: Output to Excel

[Ramon Estevez]

Thomas,

I keep getting the error

LINE/COL ERROR
 -
65/4 PL/SQL: Statement ignored
65/36PLS-00302: component 'OUT_REC' must be declared
72/4 PL/SQL: Statement ignored
72/37PLS-00302: component 'OUT_REC' must be declared
SQL>

Any suggestion,

Gracias

Ramon Estevez
Dominican Republic
[EMAIL PROTECTED]


  1   CREATE OR REPLACE PACKAGE BODY PROCESAR_AGENCIAS AS
  2   PROCEDURE GENERAR_FACTURAS
  3( PGRUPO IN NUMBER,PCOMPANIAIN NUMBER,
  4  PFECHA_INICIAL IN DATE,  PFECHA_FINAL IN DATE,
  5  PAGENCIA   IN NUMBER ) AS
  6   CURSOR C_FACTURAS IS
  7 SELECT F.GRUPO||','||
  8F.COMPANIA||','||
  9F.TIPO_FACTURA||','||
 10F.AGENCIA||','||
 11F.FACTURA||','||
 12F.CLIENTE||','||
 13F.VENDEDOR||','||
 14   -- ZONA||','||
 15F.DOCUMENTO_COBRO||','||
 16F.FECHA||','||
 17F.FECHA_PAGO||','||
 18F.FECHA_VENCIMIENTO||','||
 19F.ESTATUS_COMISION||','||
 20F.COMISION_VENDEDOR||','||
 21F.MONTO||','||
 22F.MONTO_PAGADO||','||
 23F.IMPRESA||','
 24   -- ITBIS||','||
 25   -- DESCTO||','
 26 FROM FACTURAS F
 27WHERE
 28 F.GRUPO   = PGRUPO AND
 29 F.COMPANIA= PCOMPANIA  AND
 30 F.AGENCIA = PAGENCIA   AND
 31 F.FECHA BETWEEN PFECHA_INICIAL AND PFECHA_FINAL;
 32   CURSOR C_ITEM_FACTURAS IS
 33 SELECT I.GRUPO||','||
 34I.COMPANIA||','||
 35I.AGENCIA||','||
 36I.TIPO_FACTURA||','||
 37I.FACTURA||','||
 38I.LOCALIDAD||','||
 39I.ARTICULO||','||
 40I.SECUENCIA||','||
 41I.COSTO||','||
 42I.PRECIO_VENTA||','||
 43I.CANTIDAD||','||
 44I.ITBIS||','||
 45I.DESCTO||','
 46 FROM FACTURAS F, ITEM_FACTURAS I
 47WHERE
 48 F.GRUPO = PGRUPO AND
 49 F.COMPANIA  = PCOMPANIA  AND
 50 F.AGENCIA   = PAGENCIA   AND
 51 F.FECHA BETWEEN   PFECHA_INICIAL AND
 52   PFECHA_FINAL   AND
 53 I.GRUPO = F.GRUPOAND
 54 I.COMPANIA  = F.COMPANIA AND
 55 I.TIPO_FACTURA  = F.TIPO_FACTURA AND
 56 I.FACTURA   = F.FACTURA;
 57 V_ARCHIVO UTL_FILE.FILE_TYPE;
 58 REGISTRO  FACTURAS%ROWTYPE;
 59 OUT_REC   REGISTRO%TYPE;
 60   BEGIN
 61   -- Loop para el archivo de Facturas
 62 V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','FACTURAS.TXT', 'W');
 63 FOR FT IN C_FACTURAS
 64   LOOP
 65 UTL_FILE.PUT_LINE(V_ARCHIVO, FT.OUT_REC);
 66   END LOOP;
 67 UTL_FILE.FCLOSE(V_ARCHIVO);
 68   -- Loop para el archivo de Item Facturas
 69 V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','ITEM_FACTURAS.TXT',
'W');
 70 FOR IFT IN C_ITEM_FACTURAS
 71   LOOP
 72 UTL_FILE.PUT_LINE(V_ARCHIVO, IFT.OUT_REC);
 73   END LOOP;
 74 UTL_FILE.FCLOSE(V_ARCHIVO);
 75   END GENERAR_FACTURAS;
 76*  END PROCESAR_AGENCIAS;
SQL> /

Warning: Package Body created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE BODY PROCESAR_AGENCIAS:

LINE/COL ERROR
 -
65/4 PL/SQL: Statement ignored
65/36PLS-00302: component 'OUT_REC' must be declared
72/4 PL/SQL: Statement ignored
72/37PLS-00302: component 'OUT_REC' must be declared
SQL>

Ramon E. Estevez
[EMAIL PROTECTED] 
809-565-3121


-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Thomas, Kevin
Enviado el: Friday, 05 October, 2001 4:15 AM
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Output to Excel


I believe the error to be where you have:

OUT_REC TYPE REGISTRO;

it should be: OUT_REC REGISTRO%TYPE;


Is there something missing ?


Any help !!

Thanks in Advance,



Ramon E. Estevez
[EMAIL PROTECTED]
Dominican Republic
809-565-3121



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

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

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Pu

RE: SQL Server E-mail List

[Smith, Ron L.]

http://www.swynk.com/faq/sql/sqlserverfaq.asp

-Original Message-
Sent: Friday, October 05, 2001 7:45 AM
To: Multiple recipients of list ORACLE-L


Does anyone know of a SQL Server e-mail list such as Oracle-L?

Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient Systems, Inc.
Minneapolis, MN
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ken Janusz
  INET: [EMAIL PROTECTED]

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

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

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

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

RE: Restricted mode

[Deshpande, Kirti]

Yes, issue following command as sys or internal.

alter system disable restricted session; 

HTH,

- Kirti Deshpande 
  Verizon Information Services
   http://www.superpages.com

> -Original Message-
> From: Joe LaCascio [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, October 05, 2001 8:35 AM
> To:   Multiple recipients of list ORACLE-L
> Subject:  Restricted mode
> 
> 
> Is there a way to change a database from being up in restricted mode
> to open for all without shutting down and restarting?
> 
> Thanks
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Joe LaCascio
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

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

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

RE: Database will not shutdown in Normal or IMMEDIATE mode

[Cale, Rick T (Richard)]

Thanks everyone with the DROP TABLE ... and SHUTDOWN replies.  This morning
is much better.
I was able to DROP tables I needed.  The shutdown problem would not occur
even after the DROP 
finally succeeded.  I was in a position to do a cold boot of the server and
viola I was able to 
shutdown and do backups. It is a beautiful sunny day in east TN.

Thanks again,
Rick


-Original Message-
Sent: Friday, October 05, 2001 9:20 AM
To: Multiple recipients of list ORACLE-L


Rick,

By now, I hope your database is back in working order.

My guess as to why you could not shut down your database is that the "Drop
Table" command was still running.

I've seen these long-running DDL commands get started and just stick around
until they finish, even if you killed the sqlplus session.

Just a guess.  Hope this morning brings a better day to you.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, October 04, 2001 5:25 PM
To: Multiple recipients of list ORACLE-L


Hi All,

It is one of those days.
Anyway Oracle 8.0.5 / Win 4.0.

I am trying to shutdown instance in Normal mode but just hangs.  I am able
to shutdown abort and restart. Heck I even rebooted server
in case files were locked.  There are no errors in alert log

Any ideas why I cannot shutdown in NORMAL or IMMEDIATE.
No other users/sessions on the system.  I have done this many times but not
working today.  I  need to do this because I want to do
an offline backup.

Thanks
Rick
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cale, Rick T (Richard)
  INET: [EMAIL PROTECTED]

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

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

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

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

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

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

Re: Restricted mode

[nlzanen1]

Hi


Alter system disable restricted session;


Jack




Joe LaCascio <[EMAIL PROTECTED]>@fatcity.com on 05-10-2001 15:35:17

Please respond to [EMAIL PROTECTED]

Sent by:  [EMAIL PROTECTED]


To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL)


Is there a way to change a database from being up in restricted mode
to open for all without shutting down and restarting?

Thanks


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

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

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




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

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

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

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

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





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

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

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

Re: Restricted mode

[Rachel Carmichael]

alter system disable restricted session

took 2 clicks to find the exact syntax in the manual..


--- Joe LaCascio <[EMAIL PROTECTED]> wrote:
> 
> Is there a way to change a database from being up in restricted mode
> to open for all without shutting down and restarting?
> 
> Thanks
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Joe LaCascio
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

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

Looking for a SQL Server DBA

[Ken Janusz]

My company is looking to hire a full-time, permanent SQL Server DBA.  That
person's focus will be on converting databases to SQL Server and then
providing post conversion support.  This position is for our office in
Minneapolis, MN.  No relocation expenses.

If you know of anyone who might be interested in such a position, please
have them contact me.

Thanks,
Ken Janusz, CPIM
Oracle Database Conversion Lead
Sufficient Systems, Inc.
Minneapolis, MN

P.S.:  I have been looking for a local SQL SERVER user group and an e-mail
list that works with no success.

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

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

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

Re: ORA-24323 & ORA-03113 & ORA-01034 & ORA-27101 errors

[tday6]

Your LISTENER is up but it's not pointing at a working instance/database.
I'm not sure what you're doing to resolve SQLNet or Net 8 but I would look
in listener.ora and see what the SID value is.




   

Okan CIMEN 

  <[EMAIL PROTECTED]>

Sent by: rootcc:   

 Subject: ORA-24323 & ORA-03113 & 
ORA-01034 &  
 ORA-27101 errors  

10/05/2001 

08:35 AM   

Please 

respond to 

ORACLE-L   

   

   





Hello,

I get strange errors from my DB server. Here are the outputs;

$ sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 5 14:24:07 2000
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Enter user-name: internal
Connected to an idle instance.
SQL> startup force
ORA-24323: value not allowed
ORA-03113: end-of-file on communication channel

When I try to log on as any user I get these messages;
SQL> connect anyuser
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

I am still connected to the server as a user but when I do ps -ef | grep
ora_ , I get nothing. Can someone please tell me what path I shall take?

Regards

Okan




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

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

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


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

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

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

Restricted mode

[Joe LaCascio]

Is there a way to change a database from being up in restricted mode
to open for all without shutting down and restarting?

Thanks


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

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

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

RE: Backup Strategy

[Christopher Spence]

First thing with cold backups is the obvious, you will loose data since your
last backup.  You need to be running archive mode to recover up to the point
of failure, or to recover a single data file.  With cold backups, if you
loose a data file, (say one out of 30) you need to recover the entire
database.  With hot backups and archive logs, you can recover the database,
and have the database up at the time as well.

Referring to not being comfortable using hot backups.  There is absolutely
no risk to doing hot backups while the files are in use, Oracle writes full
blocks to the redo logs while in backup mode.  This means, say you have a
data file with 1000 blocks.  You modify 100 rows during the backup, normally
Oracle will just store the old, new, and command information when you make
modifications, but seeing as your data file is in backup mode, it stores the
entire block.  As you modified the blocks while the backup is running, the
file you backed up is considered corrupted.  As you would expect running
backups while the database is up.  But seeing as oracle saved the entire
block it modified, it can just over lay the block into the data file (blocks
most likely) and "build" a good data file.  This is the recovery process,
and it is tried and true.

For any production instance where data loss is not acceptable (I generally
frown on any data loss) and you need to recover within a time limit, hot
backups are the way to go.  You don't need to shut down your database, you
will not loose data on a failure, you can recover just single data files if
a drive fails rather than entire database having to go down to be repaired.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Thursday, October 04, 2001 6:26 PM
To: Multiple recipients of list ORACLE-L

I see 2 advantages of cold over hot backups:

1) Archive log mode not required for cold backups.  Dev & Test DB's do not
need archive space.

2) Cold backups follow the KISS principle.  Shutdown db, tar, dump, cpio,
dd, etc. the datafiles, redo logs, ctl files, oracle filesystems, etc. to
tape, Startup db, Done.  Take the tape to same or another server and
restore, No Oracle cmds required (filesystems being the same).  Now for Hot
Backups, we have to add a step.  Not a big step :)  We need the arcs and
need to recover the db.

Gene
* Still not convinced backing up closed files are not safer/better than open
files :) *

>>> <[EMAIL PROTECTED]> 10/04/01 04:59PM >>>


OK, I'll bite, what OS commands?

As for cold backups, and I'm sure you've heard this already, they're only
really needed after you open a database with 'resetlogs' .  Otherwise it
is not necessary to use a cold backup.

Jared



 

"Gene Sais"

<[EMAIL PROTECTED]   To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
ach.fl.us>  cc:

Sent by:Subject: RE: Backup Strategy

[EMAIL PROTECTED]

 

 

10/04/01 02:10 PM

Please respond to

ORACLE-L

 

 





Well for 1 reason, Cold backups are restored using OS cmds, no need for
Oracle recovery, whereas, Hot Backups require OS cmds + Oracle recovery.
One exception is pt in time recoveries.  I do have 1 db that will be
web-enabled, therefore 24x7.  So guess what I have to do, Hot Backups.  I
am not against them, just prefer cold.   Anyone have hot backup scripts?
Lisa, you are the script ninja, got 1 of those scripts lying aorund.
Thanks :)

Gene

>>> [EMAIL PROTECTED] 10/04/01 04:00PM >>>
Why is better Gene?  What is it about the files being closed
that gives you the trust factor?  I don't use RMAN here either
but its more because my backup method works wonders and I just
don't need those extra features that RMAN provides.

There are a lot of sites out there that cannot afford to have
the database come down even for 5 minutes so you might want
to spend some time getting the warm fuzzies over hot backups.
I swear, they work.

-Original Message-
Sent: Thursday, October 04, 2001 12:25 PM
To: Multiple recipients of list ORACLE-L


I personally prefer cold backups over hot.  Always better when the files
are
closed.  But hey, this is coming from someone who still doesn't trust Rman
:)

Gene

>>> [EMAIL PROTECTED] 10/04/01 12:40PM >>>
I agree with your export statement but I must question this one.  I cannot
think of a single reason to get a cold backup over a hot backup.  I can
think of reasons for cold backups but if I was doing hot backups already I
would not shutdown my database just to get a cold.  There is a myth out
there that hot backups are not as reliable as cold backups and 

RE: Database will not shutdown in Normal or IMMEDIATE mode

[Mercadante, Thomas F]

Rick,

By now, I hope your database is back in working order.

My guess as to why you could not shut down your database is that the "Drop
Table" command was still running.

I've seen these long-running DDL commands get started and just stick around
until they finish, even if you killed the sqlplus session.

Just a guess.  Hope this morning brings a better day to you.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, October 04, 2001 5:25 PM
To: Multiple recipients of list ORACLE-L


Hi All,

It is one of those days.
Anyway Oracle 8.0.5 / Win 4.0.

I am trying to shutdown instance in Normal mode but just hangs.  I am able
to shutdown abort and restart. Heck I even rebooted server
in case files were locked.  There are no errors in alert log

Any ideas why I cannot shutdown in NORMAL or IMMEDIATE.
No other users/sessions on the system.  I have done this many times but not
working today.  I  need to do this because I want to do
an offline backup.

Thanks
Rick
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cale, Rick T (Richard)
  INET: [EMAIL PROTECTED]

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

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

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

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

RE: Program field in v$session

[Christopher Spence]

DBMS_APPLICATION_INFO

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, October 05, 2001 7:40 AM
To: Multiple recipients of list ORACLE-L

This column is 64 characters in length - if for example the shortcut to a
program is longer than
this, it can't all be stored, so is there any way of getting this info?

Thanks,

Steven H.

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

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

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

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

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

SQL Server E-mail List

[Ken Janusz]

Does anyone know of a SQL Server e-mail list such as Oracle-L?

Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient Systems, Inc.
Minneapolis, MN
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ken Janusz
  INET: [EMAIL PROTECTED]

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

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

RE: Backup Strategy

[Cherie_Machler]

Gene,

Are you going to be using RMAN?  Is this on UNIX?   What version of
database?

Thanks,

Cherie


   
   
"Gene Sais"
   
<[EMAIL PROTECTED]   To: Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>   
ach.fl.us>  cc:
   
Sent by:Subject: RE: Backup Strategy   
   
[EMAIL PROTECTED]   
   
   
   
   
   
10/04/01 04:10 PM  
   
Please respond to  
   
ORACLE-L   
   
   
   
   
   




Well for 1 reason, Cold backups are restored using OS cmds, no need for
Oracle recovery, whereas, Hot Backups require OS cmds + Oracle recovery.
One exception is pt in time recoveries.  I do have 1 db that will be
web-enabled, therefore 24x7.  So guess what I have to do, Hot Backups.  I
am not against them, just prefer cold.   Anyone have hot backup scripts?
Lisa, you are the script ninja, got 1 of those scripts lying aorund.
Thanks :)

Gene

>>> [EMAIL PROTECTED] 10/04/01 04:00PM >>>
Why is better Gene?  What is it about the files being closed
that gives you the trust factor?  I don't use RMAN here either
but its more because my backup method works wonders and I just
don't need those extra features that RMAN provides.

There are a lot of sites out there that cannot afford to have
the database come down even for 5 minutes so you might want
to spend some time getting the warm fuzzies over hot backups.
I swear, they work.

-Original Message-
Sent: Thursday, October 04, 2001 12:25 PM
To: Multiple recipients of list ORACLE-L


I personally prefer cold backups over hot.  Always better when the files
are
closed.  But hey, this is coming from someone who still doesn't trust Rman
:)

Gene

>>> [EMAIL PROTECTED] 10/04/01 12:40PM >>>
I agree with your export statement but I must question this one.  I cannot
think of a single reason to get a cold backup over a hot backup.  I can
think of reasons for cold backups but if I was doing hot backups already I
would not shutdown my database just to get a cold.  There is a myth out
there that hot backups are not as reliable as cold backups and its false.
Your really not saving anything time wise if there is a crash (unless of
course all your disks crash as you are bring up the database).

Weekly cold backups are a good plan.


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

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

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

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

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

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet ac

ORA-24323 & ORA-03113 & ORA-01034 & ORA-27101 errors

[Okan CIMEN]

Hello,

I get strange errors from my DB server. Here are the outputs;

$ sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 5 14:24:07 2000
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Enter user-name: internal
Connected to an idle instance.
SQL> startup force
ORA-24323: value not allowed
ORA-03113: end-of-file on communication channel

When I try to log on as any user I get these messages;
SQL> connect anyuser
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

I am still connected to the server as a user but when I do ps -ef | grep
ora_ , I get nothing. Can someone please tell me what path I shall take?

Regards

Okan




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

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

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

RE: Backup Strategy

[Jeremiah Wilton]

On Thu, 4 Oct 2001, Gene Sais wrote:

> 2) Cold backups follow the KISS principle.  Shutdown db, tar, dump,
> cpio, dd, etc. the datafiles, redo logs, ctl files, oracle
> filesystems, etc. to tape, Startup db, Done.  Take the tape to same or
> another server and restore, No Oracle cmds required (filesystems
> being the same).

Seems like if you just tar everything up without querying the database
for the file locations, you stand a chance of missing files.  You are
relying on the good will of all involved to put new datafiles under
the mountpoints you are backing up.  If someone makes a mistake and
puts a new datafile in a different place, it can result in that file
not getting backed up.  All backup scripts, hot and cold, should be
obtaining the file locations from the database instance.  You are not
really saving anything by avoiding "oracle commands."

Jared wrote:

> As for cold backups, and I'm sure you've heard this already, they're
> only really needed after you open a database with 'resetlogs' .
> Otherwise it is not necessary to use a cold backup.

What?  you haven't recovered past resetlogs before?

8-)

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

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

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

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