Unix batch job
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).
ampersand problem
Title: 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;
/
RE: ampersand problem
Title: RE: ampersand problem
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;
/
Unix batch job
Here is one simple example:
==
SUCCESS=0
STARTTIME=`date +'%d/%m/%Y-%H:%M:%S'`
BATCHEXECUABALE ARGUMENT1 ARGUMENT2 INPUT_FILE 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).
RE: OT : Is Oracle HRMS 11i 11.5.1 supported in Unix OS 2.8 ?
Hi, I've found the answer. Regds, ChorLing -Original Message- From: CHAN Chor Ling Catherine (CSC) Sent: Friday, October 05, 2001 12:10 PM To: Multiple recipients of list ORACLE-L Subject:OT : Is Oracle HRMS 11i 11.5.1 supported in Unix OS 2.8 ? Hi Gurus, Does anyone know whether oracle HRMS 11i 11.5.1 is supported in Unix OS 2.8. We are currently using Unix OS 2.6 but is thinking of moving to another Unix machine that only supports OS 2.8. Any advice ? Regds, Chorling -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) 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: CHAN Chor Ling Catherine (CSC) 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
I believe the error to be where you have:
OUT_REC TYPE REGISTRO;
it should be: OUT_REC REGISTRO%TYPE;
-Original Message-
Sent: 04 October 2001 23:10
To: Multiple recipients of list ORACLE-L
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: Thomas, Kevin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Max number of sessions
Title: Max number of sessions Yuval, If you mean to estimate how you canpush the database to it's limit, I saw some demos the other day ofa simulation tool by a company called Simulus. The tool allows you to alteraspects such as H/W config,data and parameters. This may be useful to you. Regards, Mike. -Original Message-From: Yuval Arnon [mailto:[EMAIL PROTECTED]]Sent: 04 October 2001 19:16To: Multiple recipients of list ORACLE-LSubject: Max number of sessions Hi, I would like to find out how to compute/estimate the maximum number of sessions a db can handle. Preferably based on the init.ora params sessions and processes, size of sga, size of ram, the /etc/system params etc. This is for a db running using MTS. TIA Yuval.
orapwd utility
Hi lists, Can anynbody tell me this orapwd utility. is there any relation tothis password utility and grant a user SYSDBA/SYSOPER? if i need to grant to user SYSDBA , do i need to create password file. regards sriniva -- 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: Intermedia Performance Benchmarks anyone ?
Jack, Thanks for your time on this. Most revealing and useful for what I have ahead of me Kind regards from the UK. Martin -Original Message- Sent: 04 October 2001 22:47 To: Multiple recipients of list ORACLE-L 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$YourIndexName$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
RE: Output to Excel
Ooops,
Again, if you are actually declaring OUT_REC to be the same as REGISTRO, and
REGISTRO is the same as FACTURAS then you could also do: OUT_REC
FACTURAS%ROWTYPE;
Cheers,
Kev.
hit any user to continue
__
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 10:15
To: Multiple recipients of list ORACLE-L
I believe the error to be where you have:
OUT_REC TYPE REGISTRO;
it should be: OUT_REC REGISTRO%TYPE;
-Original Message-
Sent: 04 October 2001 23:10
To: Multiple recipients of list ORACLE-L
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: Thomas, Kevin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Loss of Redo Log file monitoring.
Hi list, Does anybody know of a way to monitor for the loss of redo log files *within* SQL or PL/SQL? Anybody have any code they would like to share? Much appreciated if some bright spark has this.. Cheers Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Output to Excel
Err,
Actually correcting myself, that should be: OUT_REC REGISTRO%ROWTYPE;
K.
hit any user to continue
__
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 10:15
To: Multiple recipients of list ORACLE-L
I believe the error to be where you have:
OUT_REC TYPE REGISTRO;
it should be: OUT_REC REGISTRO%TYPE;
-Original Message-
Sent: 04 October 2001 23:10
To: Multiple recipients of list ORACLE-L
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: Thomas, Kevin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas, Kevin
INET: [EMAIL
RE: Loss of Redo Log file monitoring.
namaskar ! Mark, not sure ..but, u could check for the STATUS in v$log... or if one of the mirror has failed it would appear in alert.log -Rahul -- From: Mark Leith[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, October 05, 2001 4:55 PM To: Multiple recipients of list ORACLE-L Subject: Loss of Redo Log file monitoring. Hi list, Does anybody know of a way to monitor for the loss of redo log files *within* SQL or PL/SQL? Anybody have any code they would like to share? Much appreciated if some bright spark has this.. Cheers Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul 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: orapwd utility
ORAPWD utility is used to create password file for verification of dba users through password file. when u create pasword file through it, dba users INTERNAL an SYS are automatically added and verified through this file. similarly when u grant SYSDBA to any user, it is also added to this file. but this needs remote_login_passwordfile parameter to be set as EXCLUSIVE. hope this helps.. Saurabh - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 05, 2001 3:15 PM Hi lists, Can anynbody tell me this orapwd utility. is there any relation tothis password utility and grant a user SYSDBA/SYSOPER? if i need to grant to user SYSDBA , do i need to create password file. regards sriniva -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Saurabh Sharma 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).
Using Intermedia and Oracle 8i for Web site search engine back-en
Hi, I am not very familiar with Intermedia. We have a web site for our intranet and it is getting too big to know everything where it is. It just occured to me how nice to make a HTML/XML frontend for a search functionality and let Oracle Intermedia search the MS Word and HTML file. Can Intermedia search external files? I would like to avoid loading all files ont the website into CLOB or BLOB columns. TIA, Tamas Szecsy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Szecsy Tamas 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).
Program field in v$session
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).
RE: Database will not shutdown in Normal or IMMEDIATE mode
Most people agree that when shutdown immediate takes a long time, it is either transactions being rolled back, or sort segments being cleaned up. It could also be a number of other things, but most of those things are done by PMON and SMON. One thing I will recommend, for this reason and for many others, is to turn on PMON and SMON logging to trace. This is easy to do, and there are no real reasons not to do it. once this tracing is turned on, you can watch what those processes are doing during a shutdown immediate. It may not speed up the process, but at least you won't be in the dark. % oerr ora 10246 10246, 0, print trace of PMON actions to trace file // *Cause: // *Action: % oerr ora 10500 10500, 0, turn on traces for SMON // *Cause: // *Action: //Level: =5 trace instance recovery // 5 trace posting of SMON In the initialization file: event = 10500 trace name context forever, level 6 event = 10246 trace name context forever True, checkpoint/abort/restrict/immediate usually works well, but in some environments (such as the original poster's), it won't. Also true, as pointed out by someone, you can take a cold backup of an aborted database. You have to understand what you are doing, and also back up the online logs. If you want to perform additional media recovery (apply more logs) after restore, you can't just fire the sucker up, because it will just crash recover and open on its own, which will make it impossible to apply the additional logs. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Thu, 4 Oct 2001, Kimberly Smith wrote: Do you have the dbsnmp agent running? I certain versions of Oracle it just would not go away. Not sure which versions though. It is gone in 8i though. -Original Message- 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backup Strategy
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).
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).
RE: Backup Strategy
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 access / Mailing
SQL Server E-mail List
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: Program field in v$session
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).
RE: Database will not shutdown in Normal or IMMEDIATE mode
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: Backup Strategy
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 its false. Your really not
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).
Re: ORA-24323 ORA-03113 ORA-01034 ORA-27101 errors
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 okanTo: Multiple recipients of list ORACLE-L @cimen.org [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).
Looking for a SQL Server DBA
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: Restricted mode
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).
Re: Restricted mode
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: Database will not shutdown in Normal or IMMEDIATE mode
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
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: SQL Server E-mail List
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: Output to Excel
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] mailto:[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-- Public
Re: ORA-24323 ORA-03113 ORA-01034 ORA-27101 errors
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: Problems with patchsets to 8.1.7.0.0 on Windows 2000
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: Restricted mode
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: Plan Table
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
Youcan issue a command svrmgrlalter system enable restricted session; - this moves the db into restricted mode. - but this will not affect the existing logged users. svrmgrlalter 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: Backup Strategy
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).
Installation of designer 6.0 with personal oracle 8.1.6
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).
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).
RE: OPS Internal Secrets? WAS:: RE: Michael Jenkins (Nextel)
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??? slobber. 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: dbsnmp agent
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: Droping System User
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!
getting password request on connect internal
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
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] mailto:[EMAIL PROTECTED]
809-565-3121
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Thomas, Kevin
Enviado el: Friday, 05 October,
RE: getting password request on connect internal - more info
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: SQL Server E-mail List
Ken, Here is a good list for SQL Server. http://ls.swynk.com/scripts/lyris.pl?site=swynk.compage=topictopic=sqlserv ertext_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).
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: Installation of designer 6.0 with personal oracle 8.1.6
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).
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 - CS To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] David.Lord@ha cc: 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 - CS 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 this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the
RE: perplexing plan?
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: Plan Table
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).
PL/SQL Web Toolkit
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: 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-00302: component 'OUT_REC' must be declared
SQL
Ramon E. Estevez
[EMAIL PROTECTED] mailto:[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
RE: perplexing plan?
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).
Opinions wanted for new Oracle Security Handbook
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
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).
Re: getting password request on connect internal
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).
Re: Unix batch job
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 INPUT_FILE
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: Output to Excel
Kevin don't worry, I understood
Thanks, that was the solution !!
Thanks
Ramon E. Estevez
[EMAIL PROTECTED] mailto:[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/37
RE: Looking for a SQL Server DBA
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: dbsnmp agent
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: perplexing plan?
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: Developer 2000 V. 2.0 vs V.6.0
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: getting password request on connect internal
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).
Q about autoextent tablespaces and performance
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: SQL Server E-mail List
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.compage=topictopic=sqlserv ertext_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).
RE: WHERE CURRENT OF Question
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 column_name 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: Opinions wanted for new Oracle Security Handbook
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: 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: ampersand problem
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: Q about autoextent tablespaces and performance
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] mailto:[EMAIL PROTECTED] http://www.inplan.de 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: weekends/holidays
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: WHERE CURRENT OF Question
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: SQL Manual snippet 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. End snippet 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).
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).
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 containing: UNSUB ORACLE-L (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: Installation of designer 6.0 with personal oracle 8.1.6
Hi, You have to install developer first.
RE: Q about autoextent tablespaces and performance
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: Problems with patchsets to 8.1.7.0.0 on Windows 2000 - and warning on 8.1.7.2.1 patchset
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: weekends/holidays
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: WHERE CURRENT OF Question
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: Q about autoextent tablespaces and performance
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: perplexing plan?
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: session_cached_cursors parameter
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: Group by problem
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: Developer 2000 V. 2.0 vs V.6.0
Its 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. Its 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: perplexing plan?
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: Oracle 8.1.7 on Solaris vs. Linux
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: weekends/holidays
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: Clarification from your Paper Implementing RAID in Oracle Systems
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: Developer 2000 V. 2.0 vs V.6.0
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
CTAS use of rollback
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).
not enough ITL slots for parallel DML operation
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
Re: perplexing plan?
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. Thecontract developers had determined, on their small test system, that forcing an access-pathsort 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 submissionof one of these statements CPU utilizationof 72x and I/O of6200xof 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" tofix 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
Re: CTAS use of rollback
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: not enough ITL slots for parallel DML operation
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).
Please Help !!!
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: perplexing plan?
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 mailto:[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] 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
Re: Please Help !!!
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).
