best case scenarios for export/import
Good day, all: I'm looking for real-life best-case scenarios for running import/export . . . I've been playing with this for quite some time and would like to know how fast I can really expect this to go, particularly for the import. I'd be interested to hear others' experiences - how fast have you been able to import data? what parameters have you used? etc. . . . it's both for informational purposes and as a sanity check. For example: I'm now trying to import a dump file of appx 6.5 Gb - breaks down into 12G data and 4G indexes. using the following params on the first import, to just get the data (I then rerun with the indexfile param to get the indexes): recordlength=65535 buffer=1500 (15M) commit=y indexes=n constraints=n grants=n This will import in appx 36 hours using a single 3 Gb rollback segment What kind of experiences have you had? Thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
FW: DBMS_JOB problem
OK OK so I missed the semicolon after the 'statspack.snap' command . . . sorry. problem fixed - back to normal state. -bill -Original Message- From: Magaliff, Bill Sent: Friday, December 13, 2002 11:55 AM To: Oracle-L (E-mail) Subject: DBMS_JOB problem I'm trying to use DBMS_JOB to schedule hourly statspack snaps. Per note on Metalink, I've try to run the following: SQL variable x number; SQL begin 2 dbms_job.submit(:x,'statspack.snap',sysdate,'sysdate + 1/24'); 3 end; 4 / begin * ERROR at line 1: ORA-06550: line 1, column 108: PLS-00103: Encountered the symbol END when expecting one of the following: := . ( @ % ; The symbol ; was substituted for END to continue. ORA-06512: at SYS.DBMS_JOB, line 79 ORA-06512: at SYS.DBMS_JOB, line 131 ORA-06512: at line 2 Any ideas on this error? No helpful info on Metalink. Checked the status of the package, too: 1* select object_type, status from all_objects where object_name = 'DBMS_JOB' SQL / OBJECT_TYPESTATUS -- --- PACKAGEVALID SYNONYMVALID Thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DBMS_JOB problem
I'm trying to use DBMS_JOB to schedule hourly statspack snaps. Per note on Metalink, I've try to run the following: SQL variable x number; SQL begin 2 dbms_job.submit(:x,'statspack.snap',sysdate,'sysdate + 1/24'); 3 end; 4 / begin * ERROR at line 1: ORA-06550: line 1, column 108: PLS-00103: Encountered the symbol END when expecting one of the following: := . ( @ % ; The symbol ; was substituted for END to continue. ORA-06512: at SYS.DBMS_JOB, line 79 ORA-06512: at SYS.DBMS_JOB, line 131 ORA-06512: at line 2 Any ideas on this error? No helpful info on Metalink. Checked the status of the package, too: 1* select object_type, status from all_objects where object_name = 'DBMS_JOB' SQL / OBJECT_TYPESTATUS -- --- PACKAGEVALID SYNONYMVALID Thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
trapping errors from assignment of SQL*Plus Input Variables
Is there any way to trap errors from the assignment of a SQL*Plus variable to a PL/SQL variable? I have this in my executable section: v_runcr := i_runcr; (v_runcr is declared as a number) I want to trap the case where either no value for i_runcr is specified at runtime (and thus the assignment should be NULL) or a non-numeric character is specified (e.g., a letter). I had hoped the WHEN OTHERS exception would handle it but it does not. Any ideas? Thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: trapping errors from assignment of SQL*Plus Input Variables
Title: RE: trapping errors from assignment of SQL*Plus Input Variables yes - would it matter? -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 10, 2002 1:05 PMTo: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'Subject: RE: trapping errors from assignment of SQL*Plus Input Variables Bill, is this an anonymous block you are talking about? Lisa Koivu Oracle Diaper Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Magaliff, Bill [SMTP: ] Sent: Tuesday, December 10, 2002 11:49 AM To: Multiple recipients of list ORACLE-L Subject: trapping errors from assignment of SQL*Plus Input Variables Is there any way to trap errors from the assignment of a SQL*Plus variable to a PL/SQL variable? I have this in my executable section: v_runcr := i_runcr; (v_runcr is declared as a number) I want to trap the case where either no value for i_runcr is specified at runtime (and thus the assignment should be NULL) or a non-numeric character is specified (e.g., a letter). I had hoped the WHEN OTHERS exception would handle it but it does not. Any ideas? Thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: trapping errors from assignment of SQL*Plus Input Variables
Title: RE: trapping errors from assignment of SQL*Plus Input Variables
this
was the right direction -
I
changed the assignment line to this:
v_runcr := nvl(to_number('i_runcr'), 0);
and it catches the 6502 (numeric or value error) quite
nicely.
Thanks, Lisa!
-bill
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 10, 2002
1:19 PMTo: 'Magaliff, Bill';
'[EMAIL PROTECTED]'Subject: RE: trapping errors from assignment
of SQL*Plus Input Variables
Well, seems to me the script won't
execute unless A value for v_runcr is specified, or v_runcr is defined in the
sqlplus environment prior to execution. Now, numeric or not, sqlplus
doesn't care, of course. In the anon block you could test the variable
after assignment by trying TO_NUMBER on it and catching the resulting 1722
error in a begin/exception/end block before execution even starts.
HTH - Lisa
-Original Message- From: Magaliff, Bill [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 1:09 PM To: 'Koivu, Lisa'; '[EMAIL PROTECTED]' Subject: RE: trapping errors from assignment of SQL*Plus Input
Variables
yes - would it matter?
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]]Sent: Tuesday,
December 10, 2002 1:05 PMTo: '[EMAIL PROTECTED]';
'[EMAIL PROTECTED]'Subject: RE: trapping errors
from assignment of SQL*Plus Input Variables
Bill, is this an anonymous block
you are talking about?
Lisa KoivuOracle Diaper
AdministratorFairfield Resorts, Inc.5259 Coconut Creek ParkwayFt. Lauderdale, FL,
USA 33063
-Original Message-From: Magaliff, Bill [SMTP: ]Sent: Tuesday, December 10, 2002 11:49 AMTo:
Multiple recipients of list ORACLE-LSubject: trapping errors from assignment
of SQL*Plus Input Variables
Is there any way to trap errors from the
assignment of a SQL*Plus variableto a PL/SQL variable?
I have this in my executable
section:
v_runcr :=
i_runcr;
(v_runcr is declared as a number)
I want to trap the case where either no value
for i_runcr is specified atruntime (and thus the assignment should be NULL) or a
non-numeric characteris specified (e.g., a letter).
I had hoped the WHEN OTHERS exception would
handle it but it does not.
Any ideas?
Thanksbill--Please see the official
ORACLE-L FAQ: http://www.orafaq.com--Author: Magaliff, Bill INET:
[EMAIL PROTECTED]
Fat City Network Services --
858-538-5051 http://www.fatcity.comSan Diego,
California -- Mailing list and
web hosting services-To REMOVE yourself from this
mailing list, send an E-Mail messageto: [EMAIL PROTECTED]
(note EXACT spelling of 'ListGuru') and inthe message BODY, include a
line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed
from). You mayalso send the HELP command for other information (like
subscribing).
Can I use GOTO with variables in PL/SQL?
Good day, all:
I'm writing a script where the user can specify at run-time if they want to
run the entire script or just one numbered piece. One way I thought of
implementing this is pasted below, but the line goto v_crlabel is not
recognized - evidently the PL/SQL engine is not substituting in the value
for the variable.
Does anyone know if this is possible? If not, I can just prefix each block
by an if..then statement to test for the value in v_cr . . . but I thought
it could be cool to try it this way.
Thanks
bill
***
accept i_cr prompt 'Enter CR to run: '
declare
v_cr number := i_cr;
v_crlabel varchar2(30) := 'CR' || to_char(v_cr);
begin
if v_cr is not null then
goto v_crlabel;
else
goto endrun;
end if;
CR5
dbms_output.put_line('Successfully went to CR5');
goto endrun;
CR6
dbms_output.put_line('Successfully went to CR6');
goto endrun;
CR7
dbms_output.put_line('Successfully went to CR7');
goto endrun;
ENDRUN
NULL;
dbms_output.put_line('Went to ENDRUN');
end;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Can I use GOTO with variables in PL/SQL?
OK so I've opened an Enhancement Request on this . . . and in the meantime I
discovered that I CAN use SQL*Plus substition variables!
The following DOES work:
**
accept i_label prompt 'Enter CR '
declare
v_cr number := i_label;
v_crlabel varchar2(30) := 'CR' || to_char(v_cr);
begin
dbms_output.put_line (v_crlabel);
if v_cr = 50 then
goto CRi_label;
else
goto endrun;
end if;
CR50
dbms_output.put_line('Successfully went to CR50');
ENDRUN
NULL;
dbms_output.put_line('Went to END');
end;
/
-Original Message-
Sent: Friday, December 06, 2002 11:42 AM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Bill,
Sadly, I don't think this is possible. The PL/SQL compiler is just not that
dynamic.
Pretty cooleo idea, though.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Friday, December 06, 2002 11:19 AM
To: Multiple recipients of list ORACLE-L
Good day, all:
I'm writing a script where the user can specify at run-time if they want to
run the entire script or just one numbered piece. One way I thought of
implementing this is pasted below, but the line goto v_crlabel is not
recognized - evidently the PL/SQL engine is not substituting in the value
for the variable.
Does anyone know if this is possible? If not, I can just prefix each block
by an if..then statement to test for the value in v_cr . . . but I thought
it could be cool to try it this way.
Thanks
bill
***
accept i_cr prompt 'Enter CR to run: '
declare
v_cr number := i_cr;
v_crlabel varchar2(30) := 'CR' || to_char(v_cr);
begin
if v_cr is not null then
goto v_crlabel;
else
goto endrun;
end if;
CR5
dbms_output.put_line('Successfully went to CR5');
goto endrun;
CR6
dbms_output.put_line('Successfully went to CR6');
goto endrun;
CR7
dbms_output.put_line('Successfully went to CR7');
goto endrun;
ENDRUN
NULL;
dbms_output.put_line('Went to ENDRUN');
end;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Can I use GOTO with variables in PL/SQL?
nothing is wrong - it works fine and I'm already implementing it . . . was
just looking for alternatives - I like to look at all the options available
bill
-Original Message-
Sent: Friday, December 06, 2002 1:59 PM
To: Multiple recipients of list ORACLE-L
The pl/sql block gets submitted for execution only after the variables get
replaced with their values.
So for different values you are submitting different code.
What is wrong in moving the different actions to inside the if/then/else
clause?
Waleed
-Original Message-
Sent: Friday, December 06, 2002 12:05 PM
To: Multiple recipients of list ORACLE-L
OK so I've opened an Enhancement Request on this . . . and in the meantime I
discovered that I CAN use SQL*Plus substition variables!
The following DOES work:
**
accept i_label prompt 'Enter CR '
declare
v_cr number := i_label;
v_crlabel varchar2(30) := 'CR' || to_char(v_cr);
begin
dbms_output.put_line (v_crlabel);
if v_cr = 50 then
goto CRi_label;
else
goto endrun;
end if;
CR50
dbms_output.put_line('Successfully went to CR50');
ENDRUN
NULL;
dbms_output.put_line('Went to END');
end;
/
-Original Message-
Sent: Friday, December 06, 2002 11:42 AM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Bill,
Sadly, I don't think this is possible. The PL/SQL compiler is just not that
dynamic.
Pretty cooleo idea, though.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Friday, December 06, 2002 11:19 AM
To: Multiple recipients of list ORACLE-L
Good day, all:
I'm writing a script where the user can specify at run-time if they want to
run the entire script or just one numbered piece. One way I thought of
implementing this is pasted below, but the line goto v_crlabel is not
recognized - evidently the PL/SQL engine is not substituting in the value
for the variable.
Does anyone know if this is possible? If not, I can just prefix each block
by an if..then statement to test for the value in v_cr . . . but I thought
it could be cool to try it this way.
Thanks
bill
***
accept i_cr prompt 'Enter CR to run: '
declare
v_cr number := i_cr;
v_crlabel varchar2(30) := 'CR' || to_char(v_cr);
begin
if v_cr is not null then
goto v_crlabel;
else
goto endrun;
end if;
CR5
dbms_output.put_line('Successfully went to CR5');
goto endrun;
CR6
dbms_output.put_line('Successfully went to CR6');
goto endrun;
CR7
dbms_output.put_line('Successfully went to CR7');
goto endrun;
ENDRUN
NULL;
dbms_output.put_line('Went to ENDRUN');
end;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E
INIT 6 on Solaris not working with Oracle automated scripts?
Has anyone encountered a problem rebooting a Solaris box using INIT 6 with the Oracle dbstart/dbshut scripts enabled? My sysadmin seems to feel the problem rests with the Oracle scripts - we issue INIT 6 and nothing happens. Thanks -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Primary Key Constraints
try this: select table_name from user_tables minus select table_name from user_constraints where constraint_type = 'P'; -Original Message- Sent: Tuesday, November 26, 2002 2:25 PM To: Multiple recipients of list ORACLE-L Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle-L you have received a greeting card emailed by .
http://www.hkg3.com/f.gif Oracle-L, just emailed you a postcard. Retrieve your greeting by clicking this link. http://www.Friend-Card.net/pickup.aspx?code=Oracle-L http://www.hkg3.com/pickup.html?code=Oracle-Lid=2511021 id=2511021 Note; Oracle-L, Go get the card just emailed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle-L you've received a greeting card emailed by .
http://207.21.232.104/f.gif Oracle-L, posted you an ecard greeting. Read your ecard by clicking here; http://www.Friend-Greetings.com/pickup.aspx?code=Oracle-L http://207.21.232.104/pickup.html?code=Oracle-Lid=2511024 id=2511024 Note: Oracle-L, Go get the ecard just emailed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DO NOT OPEN EMAIL I JUST SENT YOU!
It's a worm/virus - Do NOT try to view the ecard. JUST DELETE IT Sorry - hope you're well. bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle is a time machine!!
Seems like the machine is frozen in time . . .
try the next day, too!
insert into test values (to_date('10-06-1582','mm-dd-') )
-Original Message-
Sent: Friday, November 22, 2002 12:55 PM
To: Multiple recipients of list ORACLE-L
Don't believe me?? Try this:
create table test(the_date date);
insert into test values (to_date('10-05-1582','mm-dd-') );
select to_char(the_date, 'mm/dd/') from test;
What do you get? :-))
Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!
Londo Mollari: Ah, arrogance and stupidity all in the same package. How
efficient of you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Freeman, Robert
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Scripting Deltas (for development-shop dba's?)
We're a development shop with a fairly robust product and some very demanding clients, one of which would like complete error-checking and rollback capabilities built into the delta scripts we distribute with each new release of our product. These releases can include 100 or more separate mini-scripts - each mini-script can contain ddl, dml, or both - and correspond to changes the client needs to track. I've been playing with several different mechanisms for handling this - I've posted info on this before - but I'm interested to hear how others do it. I'm striving for simplicity and as much reusable code as possible. I'm happy to take this off-line, if it gets too detailed for the list. Thanks -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: How-To or Good Practices on Code Releases
what is the difference in the db between adding a column with a default value, and adding a column and then later modifying it to set a default value? in neither case is the value actually set in the column itself. thx -b -Original Message- Sent: Thursday, November 14, 2002 6:09 AM To: Multiple recipients of list ORACLE-L Rajesh.Rao, I have the same experience. Developers directly connected to production database and added a column with default table on a huge table. The whole table is locked for 1 hour and other transaction failed. Add a column with non default value and then later add default value for it can be done in 1 second. Developers do not Know how oracle do it and just go on. New application going online without volume testing, getting online and as a production dba,i search for the bad sql and trace it out and send it back to developer! That is why many project fail, the app is designed and developed without the attendence of a guy who really knows oracle. Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net www.cnoug.org(Chinese Oracle User Group) === 2002-11-13 12:06:00 ,you wrote£º=== I also have been burnt by an simple alter table add column default string script. A pretty simple script. The developers tested it, the QA team too approved it. Was expected to run in a matter of minutes. The problem was the data in production was 100 times over. Missed out on volume testing. Took 2 cuppas and 3 smokes before it was done. Raj Magaliff, Bill Bill.Magaliff@lenTo: Multiple recipients of list ORACLE-L ORACLE-L@fatcitS = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
how to avoid mutating table error in triggers
Good day all, Have the following setup - Oracle 8.1.7.2 on solaris parent-child realtionship between 2 tables: table p1 has primary key pk1 table f1 has foreign key p1pk1 back to table p1. Table p1 also has a field haschild number(1), used to indicate if there are ANY child records in table f1. Any insert into table F1 sets the haschild field in the corresponding row in table P1 to 1 (true). Trying to write an on delete trigger for table f1 that will set that boolean to 0 when there are now more child rows. Came up with this: create or replace trigger nochildtrg after delete on f1 for each row declare tv_count number; begin select count(*) into tv_count from f1 where p1pk1 = :old.p1pk1; if tv_count = 0 then update p1 set haschild = 0 where pk1 = :old.old.p1pk1; end if; commit; end; / This plays right into the no-no's that produce the mutating table error on table f1 - selecting against it as part of a trigger. Does anyone have any kind of workaround? I could implement a counter trigger, that increments a count on the parent table for each new row in the child, and decrements the count for each deleted row, but I wanted to see if there was another way. thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: full exp/imp of user to new tablespace; same user
Title: full exp/imp of user to new tablespace; same user oh - I like that! thanks! -Original Message-From: Choudhary Rajendra (TTL_LKO) [mailto:[EMAIL PROTECTED]]Sent: Friday, November 08, 2002 2:38 AMTo: Multiple recipients of list ORACLE-LSubject: RE: full exp/imp of user to new tablespace; same user ifyou have toimport all indexes,constraints to a single tablespacethenfollowing procedurewillwork 4)After importing datachange the default tablespace to index tablespace ,ensure that no other ts quota(except index ts) is given to the schema .Grant sufficient quota on index tablespace . 5) do import with ignore=Y ,rows=no,indexes=y,constraints=y ,It will create indexes and constraints on index ts. 6) Revert back the default tablespace . Rajendra -Original Message-From: Magaliff, Bill [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 8:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: full exp/imp of user to new tablespace; same user Richard: if all of the objects are going into a single tablespace, make sure that new user has default tablespace set properly to the new TS and that s/he has a proper quota on that TS (I start with UNLIMITED). Also, make sure that user has a quota of ZERO on the TS from which the data was exported - otherwise the default import behavior will put the objects into the TS from which they were taken. Then just run the import (remembering, of course, to take all your small rollback segments offline and leave just one large rollback segment on line). If you want Indexes in a separate TS, then here's what I do: 1) set all as listed above, but make sure the user has unlimited quotas on both data and index TS 2) import data only - I use "constraints=n grants=n indexes=n" to insure it's only table data that gets in 3) import again with only the indexfile option, which will create a sql script 4) Edit the indexfile to remove stuff you don't want. . .I usemacros in my text editor to remove (in this order): ** all the lines beginning with "REM ...xRows imported" ** "REM CREATE TABLE " statements - start with that string and delete until the next semi-colon (end of the CREATE TABLE statement); these first two are for clarity only - so that I end up with a script that ONLY includes what I want, not all the other stuff, even though it's commented out. ** all remaining instances of the string "REM " (REM followed by two spaces) - this you must do to get all the ALTER TABLE statements properly. this leaves you with clean sql to just create constraints and indexes 5) do global search/replace for the index tablespace name (which is enclosed in double-quotes), putting in the name of the TS you want. 6) remove the line at the top that says "connect username" - you should connect first b4 running the script 7) save the file! 8) run the script to create all the indexes HTH bill -Original Message-From: Markham, Richard [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 8:59 AMTo: Multiple recipients of list ORACLE-LSubject: full exp/imp of user to new tablespace; same user I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips.
RE: full exp/imp of user to new tablespace; same user
Title: full exp/imp of user to new tablespace; same user Richard: if all of the objects are going into a single tablespace, make sure that new user has default tablespace set properly to the new TS and that s/he has a proper quota on that TS (I start with UNLIMITED). Also, make sure that user has a quota of ZERO on the TS from which the data was exported - otherwise the default import behavior will put the objects into the TS from which they were taken. Then just run the import (remembering, of course, to take all your small rollback segments offline and leave just one large rollback segment on line). If you want Indexes in a separate TS, then here's what I do: 1) set all as listed above, but make sure the user has unlimited quotas on both data and index TS 2) import data only - I use "constraints=n grants=n indexes=n" to insure it's only table data that gets in 3) import again with only the indexfile option, which will create a sql script 4) Edit the indexfile to remove stuff you don't want. . .I usemacros in my text editor to remove (in this order): ** all the lines beginning with "REM ...xRows imported" ** "REM CREATE TABLE " statements - start with that string and delete until the next semi-colon (end of the CREATE TABLE statement); these first two are for clarity only - so that I end up with a script that ONLY includes what I want, not all the other stuff, even though it's commented out. ** all remaining instances of the string "REM " (REM followed by two spaces) - this you must do to get all the ALTER TABLE statements properly. this leaves you with clean sql to just create constraints and indexes 5) do global search/replace for the index tablespace name (which is enclosed in double-quotes), putting in the name of the TS you want. 6) remove the line at the top that says "connect username" - you should connect first b4 running the script 7) save the file! 8) run the script to create all the indexes HTH bill -Original Message-From: Markham, Richard [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 8:59 AMTo: Multiple recipients of list ORACLE-LSubject: full exp/imp of user to new tablespace; same user I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips.
db feature comparison?
Good day, all Has anyone come across (or put together) a feature comparison of the various large-scale db's? Looking for stuff as detailed as max length for table/column names, ability to create/use aliases or synonyms, etc - but also interested in ability to partition objects, large object support and the like. Looking specifically for DB2 and Sybase as compared to Oracle, but others will help, too. thanks bill magaliff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Data modeling question
jared: can you define surrogate keys? thx bill -Original Message- Sent: Wednesday, October 30, 2002 1:05 PM To: Multiple recipients of list ORACLE-L Rick, This doesn't actually have anything to do with data modeling, but rather, database design. The FK can't be enforced on a multipart key if one of the columns is null, obviously. At least, I *think* it's obvious. What happen when you try to update column b in table 2 with a valid value? with and invalid value? There's a good case here for using surrogate keys, as always. That way, invalid values cannot be put in an FK column, since there is only one column needed to reference the foreign PK. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/30/2002 08:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data modeling question I was reading one of the papers on the quest website about data modeling. An example was presented and I did not believe the results so I tried it out. Of course the writer ( Phd) was correct. Does anyone know why does not enforce RI constraint if any of the foreign key colums are NULL. I took it literally that if I insert any record in table_2 that a corresponding record must be in the parent(table_1). Apparently this is not so. Someone please explain. Thanks Rick drop table table_2; drop table table_1; create table table_1 ( a number not null, b number not null, c number, constraint table_1_pk primary key (a,b)); create table table_2 ( a number, b number, d number not null, e number not null, f number, constraint pk_table_2 primary key (d,e), constraint fk_table_2_reference_table_1 foreign key (a,b) references table_1 (a,b)) / insert into table_2 values(1,NULL,3,4,5); 1 row processed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sqlplus /nolog
Title: sqlplus /nolog it starts sqlplus without logging in to a database - -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: Tuesday, October 29, 2002 8:29 AMTo: Multiple recipients of list ORACLE-LSubject: sqlplus /nolog Hi! I was wondering what sqlplus /nolog actually does on Unix? Is it only used for not listing username/password when doing a ps ? Or anything else? This is 8.1.7 on Sun Solaris. Thanks, Helmut
supplying dblink name to package at runtime
Hi, I'm trying to incorporate a procedure in a package that needs to run using a dblink name supplied at runtime. The dblink is used in the dml inside the procedure, but when I compile the package I get ORA-00942 table or view does not exist for all the sql referencing the dblink. Here is a code snippet: PROCEDURE CREATE_WC_OVER_DBLINK ( DBLINK VARCHAR2) v_dblink VARCHAR2(100) := dblink; BEGIN INSERT INTO mm_product_temp (rec, rec_old, name, is_disabled, is_workingcopy) (SELECT mm_product_seq.nextval, rec, name, is_disabled, is_workingcopy FROM mm_product@v_dblink WHERE last_update IS null); . END; / The ORA-00942 shows up for the line with the INSERT statement. Looking for ideas as to how to do this. I saw a MetaLink forum posting indicating that the way to do this is to create a refcursor, then use a function to return the supplied sql to the refcursor, then open the refcursor, etc. etc. Is this the only way to do it? thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
sqlplus output from query with xmlelement
I've just begun playing with XML stuff in 9i (9.2.0) so please forgive what might be an obvious question . . . I have a query to select data - returns two rows, in XML format (using XMLELEMENT for each field). The results for each row get truncated, and I can't figure out which parameter controls this output - tried various SQL*Plus params (linesize, pagesize, etc) but no luck. this is the output - LOANAPP --- LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sqlplus output from query with xmlelement
actually I just found this in the docs: The default width of datatype columns is the width of the column in the database. The column width of a LONG, CLOB, NCLOB or XMLType defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever is the smaller, so I set BOTH LONG and LONGCHUNKSIZE to 32K and it worked beautifully. thx bill -Original Message- Sent: Tuesday, September 24, 2002 5:29 PM To: Multiple recipients of list ORACLE-L If it's long then use: set long big number -Original Message- Sent: Tuesday, September 24, 2002 5:09 PM To: Multiple recipients of list ORACLE-L I've just begun playing with XML stuff in 9i (9.2.0) so please forgive what might be an obvious question . . . I have a query to select data - returns two rows, in XML format (using XMLELEMENT for each field). The results for each row get truncated, and I can't figure out which parameter controls this output - tried various SQL*Plus params (linesize, pagesize, etc) but no luck. this is the output - LOANAPP --- LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Estat Bstat
try setting a long linesize and printing on 8.5 x 14 paper, landscape works for me -bill -Original Message- Sent: Wednesday, September 18, 2002 12:43 PM To: Multiple recipients of list ORACLE-L I am trying to run utlbstat and utlestat for the first time. The output is not formatted very well. Does anyone have a cleaner version? R. Smith -- 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View Locks
v$lock -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 11, 2002 2:08 AM To: Multiple recipients of list ORACLE-L Hi, I want to view locks if any present. But when I say 'select * from v$locked_object' it says 'table or view does not exists' Is any alternative to view locks on tables. Regards, Manoj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: View Locks
taking particular aim at noone, can we please try to keep these discussions to the point . . . it's hard enough to keep up with the volume of Oracle-content postings while also trying to accomplish work . . . I think it's preferable to continue other threads off-line my 2 cents bill magaliff -Original Message- Sent: Wednesday, September 11, 2002 12:30 PM To: Multiple recipients of list ORACLE-L Please don't use wrong words like these for the Al Qaida members, you risk termed an 'infidel' and getting your house attacked by a suicide bomber :-) -Original Message- Sent: Wednesday, September 11, 2002 9:44 PM To: Multiple recipients of list ORACLE-L Nope. Developers are not normal users and Al Quaida members are not normal members of society. Both are very dangerous. -Original Message- From: Stephane Faroult [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 11, 2002 11:09 AM To: Multiple recipients of list ORACLE-L Subject: RE: Re: View Locks I have a problem with normal users monitoring locks. Do you consider developers to be normal users? Given the number of locking problems which you can trace back to the code, I would have no problem in that case. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Decode
DECODE(WHAT ARE YOU DECODING,IF YOU FIND THIS VALUE 1,REPLACE IT WITH THIS VALUE 1,IF YOU FIND THIS VALUE 2,REPLACE IT WITH THIS VALUE 2,FOR ALL OTHER CASES REPLACE IT WITH THIS VALUE); -Original Message- Sent: Wednesday, September 11, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- 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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: foreign key indexes and parent-table locking
Jared: I've now run through a basic test (on 8.1.7.2.1) and lo and behold I get the exact same results - index or no index! For each dml I show the results of this query for the session performing the DML: select type, id1, id2, lmode, request, block from v$lock here's the process: create table tparent (parentid number primary key); create table tchild (childid number primary key, parentid number, constraint parentid_fk foreign key (parentid) references tparent(parentid)); 1) insert into tparent values (1); LOCK INFO - shared lock on tparent (ID 26902), exclusive row lock on the row TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 196617 6339 6 0 0 TM 26902 0 3 0 0 2) insert into tchild values (1,1); LOCK INFO - additional shared lock on tchild (ID 26904) TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 196617 6339 6 0 0 TM 26904 0 3 0 0 TM 26902 0 3 0 0 3) insert into tchild values (2,1); LOCK INFO - no change TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 196617 6339 6 0 0 TM 26904 0 3 0 0 TM 26902 0 3 0 0 4) commit; 5) insert into tparent values (2); LOCK INFO - again, shared lock on tparent, exclusive row lock TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 131098 6319 6 0 0 TM 26902 0 3 0 0 6) commit; NO INDEX ON FOREIGN KEY 7) update tchild set parentid = 2 where childid = 2; LOCK INFO - shared lock on tchild (ID 26904), no lock on tparent TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 262179 6370 6 0 0 TM 26904 0 3 0 0 8) rollback; NOW ADD INDEX ON FOREIGN KEY AND REEXECUTE PREVIOUS STEP 9) create index tchild_i1 on tchild (parentid); 10) update tchild set parentid = 2 where childid = 2; LOCK INFO - shared lock on tchild, no lock on tparent TYID1ID2 LMODEREQUEST BLOCK -- -- -- -- -- -- TX 262177 6370 6 0 0 TM 26904 0 3 0 0 So here's the question - without the index created on the foreign key in step 9, the update in step 7, according to Oracle, should have produced a share lock on the parent table, tparent. But here, both with and without the index, an update made to the child table produces no locks on the parent table. Am I missing something? thanks bill -Original Message- Sent: Friday, September 06, 2002 2:23 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] The theory will make much more sense after you see it in action. Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/06/2002 07:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: foreign key indexes and parent-table locking I agree that that's the best way to see what actually happens, and I will do that but I like to understand the theory, too . . . -bill -Original Message- Sent: Thursday, September 05, 2002 5:40 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Importance: High Bill, Rather than try to understand that explanation, you may find it more educational to create a pair of tables with a parent/child relationship via foreign key. Put some data in the tables, then do updates and deletes both with and without FK indexes. Examine dba_locks while doing so and observe the lock modes. This will be much easier to understand than the 'documentation' Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 02:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:foreign key indexes and parent-table locking Hi, I'm trying to understand the whole issue of foreign key indexes and locking. Found a note on metalink (11828.1) that seems to explain it, but either it's not clear or I'm missing something. Why then, does an index on the foreign key mean that the shared lock on the parent table is not required? When a row in the child table is inserted, deleted or has its foreign key updated
RE: foreign key indexes and parent-table locking
I agree that that's the best way to see what actually happens, and I will do that but I like to understand the theory, too . . . -bill -Original Message- Sent: Thursday, September 05, 2002 5:40 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Importance: High Bill, Rather than try to understand that explanation, you may find it more educational to create a pair of tables with a parent/child relationship via foreign key. Put some data in the tables, then do updates and deletes both with and without FK indexes. Examine dba_locks while doing so and observe the lock modes. This will be much easier to understand than the 'documentation' Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 02:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:foreign key indexes and parent-table locking Hi, I'm trying to understand the whole issue of foreign key indexes and locking. Found a note on metalink (11828.1) that seems to explain it, but either it's not clear or I'm missing something. Why then, does an index on the foreign key mean that the shared lock on the parent table is not required? When a row in the child table is inserted, deleted or has its foreign key updated, the corresponding index entry/entries is/are also locked. When an application attempts to delete or update the primary key of a parent row, it reads the FIRST corresponding entry in the child's foreign key index (uncommitted or otherwise) and, if locked, waits for that lock to be released. So far so good . . . this next piece, too, seems to make sense: If the modified child row is NOT the first occurrence of the foreign key in the index then the parent modification must be prevented anyway, regardless of the outcome of uncommitted transactions on other child rows with this key. But now here's the part that leaves me hanging . . . Hence the error can be flagged immediately and so the transaction is not forced to wait. This mechanism ensures the minimum reads and wait times to maintain data consistency. Can anyone help by either translating this last part or rephrasing it? Or explaining the issue differnetly? Thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
foreign key indexes and parent-table locking
Hi, I'm trying to understand the whole issue of foreign key indexes and locking. Found a note on metalink (11828.1) that seems to explain it, but either it's not clear or I'm missing something. Why then, does an index on the foreign key mean that the shared lock on the parent table is not required? When a row in the child table is inserted, deleted or has its foreign key updated, the corresponding index entry/entries is/are also locked. When an application attempts to delete or update the primary key of a parent row, it reads the FIRST corresponding entry in the child's foreign key index (uncommitted or otherwise) and, if locked, waits for that lock to be released. So far so good . . . this next piece, too, seems to make sense: If the modified child row is NOT the first occurrence of the foreign key in the index then the parent modification must be prevented anyway, regardless of the outcome of uncommitted transactions on other child rows with this key. But now here's the part that leaves me hanging . . . Hence the error can be flagged immediately and so the transaction is not forced to wait. This mechanism ensures the minimum reads and wait times to maintain data consistency. Can anyone help by either translating this last part or rephrasing it? Or explaining the issue differnetly? Thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: How to speed up import
subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle Parner Network
Hey all, While reviewing our annual maintenance agreement ($5,000) and considering an additional $30K license purchase, our savvy network admin suggested we look into Oracle's Partner Network. After some research, phone conversations, and pouring through the license agreement with a fine-tooth comb, I realized that's it's a win-win for us. Instead of spending $35,000 on renewals and new license purchases, we spend $2,000 to join OPN as a member associate and then pay $39.20 per seat for dev support (equivalent to our current Silver level) on the db suite. Yes, the catch is you have to be using Oracle solely for dev (and not for internally deployed apps that run your business) but it has turned out to be a no-brainer moneywise. Curious if anyone else out there has had experience with this . . . not that I'm sceptical or anything, but we tried to find a catch and couldn't! thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: IMPORT sloooowwww
and indexes, too! do the indexes, constraints, grants, etc after the table data imports! -bill -Original Message- Sent: Thursday, August 29, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Creating grants and synonyms on each table? Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: PL/SQL Editor
we're currently looking into this, too Our developers like TOAD, but the licensing cost is pretty steep - $700 a pop for the most pared down version. Quest was willing to offer us a one-time half-price offer, but for the 50 users we were considering it still comes out to over $15,000. PL/SQL Developer does basically everything TOAD does (that our developers do, at least) - explain plans, autotrace, stats, etc. - but with a price cap of $3,000 for unlimited user license it's very attractive. Their email support is pretty good, too - never wait more than a few hours for a response, usually by the same guy. All in all pretty good bang for the buck, and we'll probably end up purchasing it this week or next. I've also looked at Embarcadero's Rapid SQL, which I personally prefer over all the others, but the price is in the same league as TOAD. BMC has product that I haven't really looked into, but I think pricewise it's up there. One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: PL/SQL Editor
can you please post URL for this program? thx bill -Original Message- Sent: Wednesday, August 28, 2002 9:53 AM To: Multiple recipients of list ORACLE-L I really like Emace with PL/SQL+SQLplus mode. It is KISS. :=) mvh HEnrik --- [EMAIL PROTECTED] wrote: Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . = __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: PL/SQL Editor
cool - so the freeware version checks the v$session view and counts the existing number of TOAD programs/modules? -Original Message- Sent: Wednesday, August 28, 2002 10:31 AM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Bill, You said One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. I got around this by replacing the v$session view with the following: Note the REPLACE statement in the select for the PROGRAM and MODULE columns: CREATE OR REPLACE VIEW V$SESSION ( SADDR, SID, SERIAL#, AUDSID, PADDR, USER#, USERNAME, COMMAND, OWNERID, TADDR, LOCKWAIT, STATUS, SERVER, SCHEMA#, SCHEMANAME, OSUSER, PROCESS, MACHINE, TERMINAL, PROGRAM, TYPE, SQL_ADDRESS, SQL_HASH_VALUE, PREV_SQL_ADDR, PREV_HASH_VALUE, MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO, FIXED_TABLE_SEQUENCE, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#, LOGON_TIME, LAST_CALL_ET, PDML_ENABLED, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, PQ_STATUS ) AS SELECT SADDR , SID , SERIAL# ,AUDSID ,PADDR ,USER# ,USERNAME ,COMMAND ,OWNERID ,TADDR ,LOCKWAIT ,STATUS ,SERVER ,SCHEMA# ,SCHEMANAME ,OSUSER ,PROCESS ,MACHINE ,TERMINAL ,REPLACE(UPPER(PROGRAM),'TOAD','FROG') PROGRAM ,TYPE ,SQL_ADDRESS ,SQL_HASH_VALUE ,PREV_SQL_ADDR ,PREV_HASH_VALUE ,REPLACE(UPPER(MODULE),'T·O·A·D·','F~R~O~G') MODULE ,MODULE_HASH ,ACTION ,ACTION_HASH ,CLIENT_INFO ,FIXED_TABLE_SEQUENCE ,ROW_WAIT_OBJ# ,ROW_WAIT_FILE# ,ROW_WAIT_BLOCK# ,ROW_WAIT_ROW# ,LOGON_TIME ,LAST_CALL_ET ,PDML_ENABLED ,FAILOVER_TYPE ,FAILOVER_METHOD ,FAILED_OVER ,RESOURCE_CONSUMER_GROUP ,PDML_STATUS ,PDDL_STATUS ,PQ_STATUS FROM sys.V_$SESSION -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: PL/SQL Editor
haven't looked at that in a while, but don't you have to install Developer to use that? (It's not available standalone, is it?) -Original Message- Sent: Wednesday, August 28, 2002 1:39 PM To: Multiple recipients of list ORACLE-L Well, since someone else cares to mention it, I do use Procedure Builder, Oracle's PL/SQL development tool from Developer and I like it a lot. The latest version even does syntax highlighting, indentation, and click on the error I'll show you where it is. Dick Goulet Reply Separator Author: Jamadagni; Rajendra [EMAIL PROTECTED] Date: 8/28/2002 8:52 AM Beware ... TORA is not free on Windows platform ... it is free however on Linux ... Funny no once has mentioned Oracle's built-in editor that comes with Forms ... My preferences are 1. Vi 2. Vim Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: What is an API?
wow - what a great web site! thanks! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Wednesday, August 28, 2002 2:03 PMTo: Multiple recipients of list ORACLE-LSubject: RE: What is an API? Application Programming Interface ... http://www.acronymfinder.com/af-query.asp?String=exactAcronym=API Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: KENNETH JANUSZ [mailto:[EMAIL PROTECTED]]Sent: Wednesday, August 28, 2002 1:08 PMTo: Multiple recipients of list ORACLE-LSubject: What is an API? I have read a lot about the term API. I have searched the web and documentation and cannot find "API" is an abbreviation for?And, I cannot find a definition of what an API is?If someone could help me on this I would appreciate it. All the documentation I have assumes that the reader knows what an API is. Thanks, Ken Janusz, CPIM
RE: PL/SQL Editor
can you please post URL's for these? thanks -Original Message- Sent: Wednesday, August 28, 2002 2:28 PM To: Multiple recipients of list ORACLE-L I'll second that -- I haven't used Benthic's PL/SQL editor very much, but I use Golden almost religiously as a replacement for SQL*Plus because it is small and fast and has a tabbed window interface with spreadsheet style output that works well for me. -- Philip Douglass Internet Networking Group Database Administrator SIRS Publishing, Inc. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 12:52 PM Benthicsoftware has a good series of editors. They are all about US$30 a piece or so. Not as powerfull as TOAD but if you are just doing PL/SQL Development they work great. From: Magaliff, Bill [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: PL/SQL Editor Date: Wed, 28 Aug 2002 06:18:24 -0800 we're currently looking into this, too Our developers like TOAD, but the licensing cost is pretty steep - $700 a pop for the most pared down version. Quest was willing to offer us a one-time half-price offer, but for the 50 users we were considering it still comes out to over $15,000. PL/SQL Developer does basically everything TOAD does (that our developers do, at least) - explain plans, autotrace, stats, etc. - but with a price cap of $3,000 for unlimited user license it's very attractive. Their email support is pretty good, too - never wait more than a few hours for a response, usually by the same guy. All in all pretty good bang for the buck, and we'll probably end up purchasing it this week or next. I've also looked at Embarcadero's Rapid SQL, which I personally prefer over all the others, but the price is in the same league as TOAD. BMC has product that I haven't really looked into, but I think pricewise it's up there. One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). I no longer need to punish, deceive, or compromise myself, unless I want to stay employed. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED
RE: List of all zip codes,city,state to load into table.
The USPS department called AMS (Address Management Service) sells this data in subscription format - we get it monthly. Don't know cost, and data is provided in ascii file that you the load into a table of your design. We use SQL Loader for the piece we need and it loads (with truncate existing data) in about a minute. I think their website is something like ribbs.usps.com . . . bill -Original Message- To: Multiple recipients of list ORACLE-L Sent: 8/21/02 9:13 AM Hi All, Where can I get a list of all zip codes,city,states to load into a table. I have searched but cannot find anything to download. Anyone have a URL that I can get this info. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
db locking quandry
We have a client running OPS (no load balancing or transparent failover enabled due to middle-tier software limitation) who is running into db locking issues. Not sure they're related to OPS but pursuing that line of thought. Here's the basic scenario: 1) application (ours) access Oracle 8.1.7 via standard Net8 . . . had been divided so that different userid's go through different OPS nodes, but we disabled that for testing 2) multiple sessions each running lengthly transactions involving many tables (up to 20) - each txn inserts one or several rows into each of these 20 tables and then commits at the end 3) application log files showed txn's hanging while inserting into the n'th table in the list. Realized that for each of these tables INITRANS had been set to 1. bumped that up on most of these tables (to either 8 or 16, depending on how much we anticipate each table being hit) and that seemed to get us further along in the list. But they still encounter locking. Oracle recommended changing GC_FILES_TO_LOCK to 0, and channelling all connections through a single node, which they did but the locking still occurs. System state dumps and trace files show a variety of things, but rather inconsistent - sometime waiting on a high water mark enqueue, sometimes (today) waiting on SQLNet message from client (in this case it appears that Oracle is waiting for the app, but the app logs indiate it's waiting for Oracle). Our client is trying to get a sniffer to evaluate potential network issues. I've been reading about OPS locking issues - and they might try disabling OPS for a day just to see if this keeps happening. Oh yeah - and of course this is occurring in production and is not reproducable on any other system! Wanted to throw this out for thoughts of where to look next . . . thanks -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: db locking quandry
Title: RE: db locking quandry Can you point me to the note on MetaLink that had this TCP setting? thx bill -Original Message-From: Aponte, Tony [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 20, 2002 2:15 PMTo: [EMAIL PROTECTED]Cc: [EMAIL PROTECTED]Subject: RE: db locking quandry We had a similar situation that ended up being a network setting issue. The server was showing blocking locks. It turned out to be that the client application was getting a network error. After the error it re-established the database connection and re-submitted the transaction. The problem on the server side was that the Oracle process had not detected the client's death and was waiting for 'SQL*Net message from client'. We changed the Windows TCP setting for the number of retry attempts to 15 (as per Metalink) and the so-called locking problem has not been seen since. I guess we could have enabled Dead Connection Detection but we decided to fix it at the source. HTH Tony Aponte Home Shopping Network, Inc. -Original Message- From: Magaliff, Bill [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Subject: db locking quandry We have a client running OPS (no load balancing or transparent failover enabled due to middle-tier software limitation) who is running into db locking issues. Not sure they're related to OPS but pursuing that line of thought. Here's the basic scenario: 1) application (ours) access Oracle 8.1.7 via standard Net8 . . . had been divided so that different userid's go through different OPS nodes, but we disabled that for testing 2) multiple sessions each running lengthly transactions involving many tables (up to 20) - each txn inserts one or several rows into each of these 20 tables and then commits at the end 3) application log files showed txn's hanging while inserting into the n'th table in the list. Realized that for each of these tables INITRANS had been set to 1. bumped that up on most of these tables (to either 8 or 16, depending on how much we anticipate each table being hit) and that seemed to get us further along in the list. But they still encounter locking. Oracle recommended changing GC_FILES_TO_LOCK to 0, and channelling all connections through a single node, which they did but the locking still occurs. System state dumps and trace files show a variety of things, but rather inconsistent - sometime waiting on a high water mark enqueue, sometimes (today) waiting on SQLNet message from client (in this case it appears that Oracle is waiting for the app, but the app logs indiate it's waiting for Oracle). Our client is trying to get a sniffer to evaluate potential network issues. I've been reading about OPS locking issues - and they might try disabling OPS for a day just to see if this keeps happening. Oh yeah - and of course this is occurring in production and is not reproducable on any other system! Wanted to throw this out for thoughts of where to look next . . . thanks -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: db locking quandry
Title: RE: db locking quandry thanks - didn't realize this was windows only . . . we're all unix based . . . but the thought of this being traced down to the network is gainging momentum (at least in my mind) -b -Original Message-From: Aponte, Tony [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 20, 2002 2:37 PMTo: [EMAIL PROTECTED]Cc: Magaliff, BillSubject: RE: db locking quandry Doc ID: Note:1057439.6 Subject: ORA-03113 OR TNS-12571 - INCREASING TCP/IP RETRANSMISSIONS -Original Message-From: Magaliff, Bill [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 20, 2002 2:24 PMTo: Aponte, Tony; [EMAIL PROTECTED]Subject: RE: db locking quandry Can you point me to the note on MetaLink that had this TCP setting? thx bill -Original Message-From: Aponte, Tony [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 20, 2002 2:15 PMTo: [EMAIL PROTECTED]Cc: [EMAIL PROTECTED]Subject: RE: db locking quandry We had a similar situation that ended up being a network setting issue. The server was showing blocking locks. It turned out to be that the client application was getting a network error. After the error it re-established the database connection and re-submitted the transaction. The problem on the server side was that the Oracle process had not detected the client's death and was waiting for 'SQL*Net message from client'. We changed the Windows TCP setting for the number of retry attempts to 15 (as per Metalink) and the so-called locking problem has not been seen since. I guess we could have enabled Dead Connection Detection but we decided to fix it at the source. HTH Tony Aponte Home Shopping Network, Inc. -Original Message- From: Magaliff, Bill [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Subject: db locking quandry We have a client running OPS (no load balancing or transparent failover enabled due to middle-tier software limitation) who is running into db locking issues. Not sure they're related to OPS but pursuing that line of thought. Here's the basic scenario: 1) application (ours) access Oracle 8.1.7 via standard Net8 . . . had been divided so that different userid's go through different OPS nodes, but we disabled that for testing 2) multiple sessions each running lengthly transactions involving many tables (up to 20) - each txn inserts one or several rows into each of these 20 tables and then commits at the end 3) application log files showed txn's hanging while inserting into the n'th table in the list. Realized that for each of these tables INITRANS had been set to 1. bumped that up on most of these tables (to either 8 or 16, depending on how much we anticipate each table being hit) and that seemed to get us further along in the list. But they still encounter locking. Oracle recommended changing GC_FILES_TO_LOCK to 0, and channelling all connections through a single node, which they did but the locking still occurs. System state dumps and trace files show a variety of things, but rather inconsistent - sometime waiting on a high water mark enqueue, sometimes (today) waiting on SQLNet message from client (in this case it appears that Oracle is waiting for the app, but the app logs indiate it's waiting for Oracle). Our client is trying to get a sniffer to evaluate potential network issues. I've been reading about OPS locking issues - and they might try disabling OPS for a day just to see if this keeps happening. Oh yeah - and of course this is occurring in production and is not reproducable on any other system! Wanted to throw this out for thoughts of where to look next . . . thanks -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: db locking quandry
Solaris, Oracle 8.1.7 doubt pinging is the issue since all txn's against these tables go through a single node, and the problem persisited even when all sessions were consolidated into a single node. -b -Original Message- Sent: Tuesday, August 20, 2002 3:58 PM To: Multiple recipients of list ORACLE-L What OS? What Oracle version? Are you pinging?? (Waiting on one of the ops nodes for transactions on the other node?) Try running these pinging queries to see if there might be problems there: ttitle left Ping Ratio SELECT a.value cross, b.value physical, (a.value / b.value) * 100 FROM v$sysstat a, v$sysstat b WHERE a.name = 'DBWR cross instance writes' AND b.name = 'physical writes'; break on report compute sum of pings on report ttitle Pings by DataFile SELECT df.tablespace_name, df.file_name, SUM(NVL(buf.xnc,0))PINGS FROM dba_data_files df, v$bh buf WHERE df.file_id = buf.file# GROUP BY df.tablespace_name, df.file_name; Barb -- From: Magaliff, Bill[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, August 20, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Subject: db locking quandry We have a client running OPS (no load balancing or transparent failover enabled due to middle-tier software limitation) who is running into db locking issues. Not sure they're related to OPS but pursuing that line of thought. Here's the basic scenario: 1)application (ours) access Oracle 8.1.7 via standard Net8 . . . had been divided so that different userid's go through different OPS nodes, but we disabled that for testing 2)multiple sessions each running lengthly transactions involving many tables (up to 20) - each txn inserts one or several rows into each of these 20 tables and then commits at the end 3)application log files showed txn's hanging while inserting into the n'th table in the list. Realized that for each of these tables INITRANS had been set to 1. bumped that up on most of these tables (to either 8 or 16, depending on how much we anticipate each table being hit) and that seemed to get us further along in the list. But they still encounter locking. Oracle recommended changing GC_FILES_TO_LOCK to 0, and channelling all connections through a single node, which they did but the locking still occurs. System state dumps and trace files show a variety of things, but rather inconsistent - sometime waiting on a high water mark enqueue, sometimes (today) waiting on SQLNet message from client (in this case it appears that Oracle is waiting for the app, but the app logs indiate it's waiting for Oracle). Our client is trying to get a sniffer to evaluate potential network issues. I've been reading about OPS locking issues - and they might try disabling OPS for a day just to see if this keeps happening. Oh yeah - and of course this is occurring in production and is not reproducable on any other system! Wanted to throw this out for thoughts of where to look next . . . thanks -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You
RE: PL/SQL Editor.
I like RapidSQL from Embarcadero - but it's a bit pricy for a pretty good and less expensive alternative try PL/SQL Developer from allroundautomations -bill -Original Message- Sent: Thursday, August 15, 2002 11:09 AM To: Multiple recipients of list ORACLE-L Hello, I need a nice PL/SQL editor any advice ? Thx for any advice Regards W.B __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: dropping a user's objects
Jared:
I had originally developed this so that I could eventually alter it to just
drop certain parts of the schema (PK's, Unique constraints, etc.) based on
input values, although I never did that.
I suppose I could drop tables first with the cascade constraints clause, and
then drop all other objects.
-bill
-Original Message-
Sent: Thursday, August 08, 2002 4:35 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Bill,
Wouldn't DROP TABLE CASCADE CONSTRAINTS be easier,
or do you have some particular reason for doing it this way?
Jared
Magaliff, Bill [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
08/08/2002 11:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:dropping a user's objects
Good day, all:
I have a PL/SQL routine that I use to drop all objects owned by a given
user, which I'm attaching below.
I first drop primary keys, then unique constraints, and finally all other
objects. The routine to drop all additional objects uses a loop to select
the object name and then drops it. As you can see by the output below
(which is just for this last piece), I drop a table and then the next
piece
is to drop the index, which doesn't exist once the table is dropped
(obviously).
However, why is the cursor even finding the index in the data dictionary
once the table is dropped? Is the data dictionary not updated that
quickly?
thanks
bill
ROUTINE:
declare
v_cname varchar2(30);
v_tname varchar2(30);
v_oname varchar2(30);
v_otype varchar2(30);
cursor get_pk is
select table_name from user_constraints
where constraint_type = 'P';
cursor get_unique is
select constraint_name, table_name from user_constraints
where constraint_type = 'U';
cursor get_object is
select object_name, object_type from user_objects;
begin
open get_pk;
loop
fetch get_pk into v_tname;
exit when get_pk%notfound;
dbms_output.put_line ('dropping primary key on ' || v_tname);
execute immediate ('alter table ' || v_tname || ' drop primary key
cascade');
end loop;
close get_pk;
open get_unique;
loop
fetch get_unique into v_cname, v_tname;
exit when get_unique%notfound;
dbms_output.put_line ('dropping unique constraint ' || v_cname || ' on '
||
v_tname);
execute immediate ('alter table ' || v_tname || ' drop constraint ' ||
v_cname || ' cascade');
end loop;
close get_unique;
open get_object;
loop
fetch get_object into v_oname, v_otype;
exit when get_object%notfound;
dbms_output.put_line ('dropping ' || v_otype || ' ' || v_oname);
execute immediate ('drop ' || v_otype || ' ' || v_oname);
end loop;
close get_object;
end;
/
**
OUTPUT
SQL @y:\scripts\drop_all_objects
dropping SEQUENCE AMDC_DOCUMENT_SEQ
dropping TABLE AMDC_DOC_HISTORY
dropping INDEX AMDC_DOC_HISTORY_I1
declare
*
ERROR at line 1:
ORA-01418: specified index does not exist
ORA-06512: at line 44
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: dropping a user's objects
I like how you think
-Original Message-
Sent: Thursday, August 08, 2002 6:29 PM
To: Multiple recipients of list ORACLE-L
Bill,
Pull out the big cannon and go boom:
export the CREATE USER to create_user script
write a script containing:
DROP USER uname CASCADE;
@CREATE_USER
run this new script.
have beer.
Now of course, this will not be as fast as a parallel
DROP TABLE user.tab CASCADE CONSTRAINTS, but hey, more
time to have beer.
hth,
Jack
--- Magaliff, Bill [EMAIL PROTECTED]
wrote:
Good day, all:
I have a PL/SQL routine that I use to drop all
objects owned by a given
user, which I'm attaching below.
I first drop primary keys, then unique constraints,
and finally all other
objects. The routine to drop all additional objects
uses a loop to select
the object name and then drops it. As you can see
by the output below
(which is just for this last piece), I drop a table
and then the next piece
is to drop the index, which doesn't exist once the
table is dropped
(obviously).
However, why is the cursor even finding the index in
the data dictionary
once the table is dropped? Is the data dictionary
not updated that quickly?
thanks
bill
ROUTINE:
declare
v_cname varchar2(30);
v_tname varchar2(30);
v_oname varchar2(30);
v_otype varchar2(30);
cursor get_pk is
select table_name from user_constraints
where constraint_type = 'P';
cursor get_unique is
select constraint_name, table_name from
user_constraints
where constraint_type = 'U';
cursor get_object is
select object_name, object_type from user_objects;
begin
open get_pk;
loop
fetch get_pk into v_tname;
exit when get_pk%notfound;
dbms_output.put_line ('dropping primary key on ' ||
v_tname);
execute immediate ('alter table ' || v_tname || '
drop primary key
cascade');
end loop;
close get_pk;
open get_unique;
loop
fetch get_unique into v_cname, v_tname;
exit when get_unique%notfound;
dbms_output.put_line ('dropping unique constraint '
|| v_cname || ' on ' ||
v_tname);
execute immediate ('alter table ' || v_tname || '
drop constraint ' ||
v_cname || ' cascade');
end loop;
close get_unique;
open get_object;
loop
fetch get_object into v_oname, v_otype;
exit when get_object%notfound;
dbms_output.put_line ('dropping ' || v_otype || ' '
|| v_oname);
execute immediate ('drop ' || v_otype || ' ' ||
v_oname);
end loop;
close get_object;
end;
/
**
OUTPUT
SQL @y:\scripts\drop_all_objects
dropping SEQUENCE AMDC_DOCUMENT_SEQ
dropping TABLE AMDC_DOC_HISTORY
dropping INDEX AMDC_DOC_HISTORY_I1
declare
*
ERROR at line 1:
ORA-01418: specified index does not exist
ORA-06512: at line 44
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(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!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: User with less privileges...
on few. Is this doable working only on this new user or I have to re-create all those synonyms and grant privilleges to every application user and revoke'em from public? Thank in advance! iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania SA is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- 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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). -- 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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: User with less privileges...
try this: rather than granting specific privs to PUBLIC, create specific roles for the different types of users you have, and grant appropriate object privs to each role (granting connect also helps :-). then for each user you add, just give that user whatever role is relevent and you're set . . . they will still be able to access public synonyms. only issue with this is that you'll still need to specify TS quotas to the specific users, as they don't inherit these from the roles (unless you grant RESOURCE to the role, which has UNLIMITED TABLESPACE). using roles is easy to maintain, document and manage -bill -Original Message- Sent: Thursday, August 01, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Hi guys. Can you give some ideeas about this problem. I have a schema which contains all the objects for the application. The user owner of the schema is also the application administrator and having more privilleges. The other users can have access to these objects by beeing granted with some special privilleges (like select/update/insert/delete for tables, execute for functionsprocedures) Because the user are deleted or added from time to time, the application author decided to grant the above kind of privilleges to the public and also create some public synonyms with the same names as the originals. BUT, my problem is that now I need to create an user (he does not have any relations with the ordinary application users) which I don't want to have any access to the hrowner objects, or just on few. Is this doable working only on this new user or I have to re-create all those synonyms and grant privilleges to every application user and revoke'em from public? Thank in advance! iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania SA is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- 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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: data modeling question - child table with multiple parents
Title: RE: data modeling question - child table with multiple parents same here - we don't reuse addresses so the table gets quite huge -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]Sent: Wednesday, July 31, 2002 6:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: data modeling question - child table with multiple parents From: Igor Neyman [mailto:[EMAIL PROTECTED]] here is an argument against this solution: in current economy, some day you'll have a lay-off, and you'd want to "delete" an employee (you will be laying off employees - not addresses, right?), then you'll have to take "additional" care not to leave "orphan" addresses, and all this headache, only because of the wrong design in the first place. But if the software / users were sophisticated enough (and if the situation were possible in this case), you could share an address amongst several different employees/suppliers: i.e. if an employee and a supplier had the same address, you would only need one row in the address table, with both employee_address_id and supplier_address_id pointing to the same record. Past experience: in one of my previous companies we designed a similar layout for physician offices: often many physicians share the same office (and also the same physician can work at multiple offices.) The layout was: physician table (physician_id pk, name, etc.) office table (office_id pk and physical address) physican_office table (physican_id office_id pk) Then in theory you would enter the office once even though many physicians work in that office; all physicians in that office would have the same office_id. In practice though we found it difficult to convince the users to search on address to prevent multiple entries in the office table. :(
RE: data modeling question - child table with multiple parents
jared: any thoughts on the point at which this becomes a potential performance bottleneck? pretty simple if there are two potential fk's as in your example - but what about 4 or 5 fk id's? -biill -Original Message- Sent: Thursday, August 01, 2002 1:48 PM To: Multiple recipients of list ORACLE-L btw, in your first solution, how are you going to implement mutual exclusiveness of EMPLOYEE_ID and SUPLIER_ID? trigger? - not very elegant. Actually quite simple and elegant: alter table add constraint only_one ( check ( ( employee_id is null and supplier_id is not null ) or ( supplier_id is null and employee_id is not null ) ) ) Jared Igor Neyman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/31/2002 02:20 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: data modeling question - child table with multiple parents yet, another solution: add another table, called i.e. ACTOR (actor_id, actor_type); sub-entity tables EMPLOYEE, SUPLLIER, CONTRACTOR will store sub-entity specific information, and their PK (employee_id, supplier_id, ...) will be foreign keys to actor_id in ACTOR table; table ADDRESS will reference ACTOR table (not multiple sub-entities), and you can enforce this relationship in the database; thus, adding new sub_entity (like VENDOR) will not require any changes in existing tables; also, you can implement TELEPHONE table the same way (referencing ACTOR table). btw, in your first solution, how are you going to implement mutual exclusiveness of EMPLOYEE_ID and SUPLIER_ID? trigger? - not very elegant. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 31, 2002 4:43 PM Good day, all: Am curious to hear opinions on how to model a child table that has multiple parent tables (i.e., foreign key to multiple parents) Example: There's a table that stores Addresses (table ADDRESS) for both employees (table EMPLOYEE) and suppliers (table SUPPLIER). Each of these tables has a Primary Key field called ID. One way to set this up would be for the ADDRESS table to have 2 fields, EMPLOYEE_ID and SUPPLIER_ID, which would be mutually exclusive (i.e., one or the other, to indicate the parent record of the address). Another solutions if for the ADDRESS table to have two fields to indicate the parent table name and parent table pk value. The first method enables me (the dba) to create foreign keys from the address table to each of the parent tables to validate data. The second method does not enable me to create such foreign keys (leaving it to the developers to validate date and insure referential integrity) but would also easily facilitate the addition of other parent tables (e.g., CONTRACTOR, VENDOR, etc.) without altering the ADDRESS table itself. Any and all thoughts, comments, opinions, experiences are most welcome. Thanks! bill magaliff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
data modeling question - child table with multiple parents
Good day, all: Am curious to hear opinions on how to model a child table that has multiple parent tables (i.e., foreign key to multiple parents) Example: There's a table that stores Addresses (table ADDRESS) for both employees (table EMPLOYEE) and suppliers (table SUPPLIER). Each of these tables has a Primary Key field called ID. One way to set this up would be for the ADDRESS table to have 2 fields, EMPLOYEE_ID and SUPPLIER_ID, which would be mutually exclusive (i.e., one or the other, to indicate the parent record of the address). Another solutions if for the ADDRESS table to have two fields to indicate the parent table name and parent table pk value. The first method enables me (the dba) to create foreign keys from the address table to each of the parent tables to validate data. The second method does not enable me to create such foreign keys (leaving it to the developers to validate date and insure referential integrity) but would also easily facilitate the addition of other parent tables (e.g., CONTRACTOR, VENDOR, etc.) without altering the ADDRESS table itself. Any and all thoughts, comments, opinions, experiences are most welcome. Thanks! bill magaliff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: data modeling question - child table with multiple parents
thanks, tom . . . your second option is one I had suggested below, which in may ways is preferable - easy to track FK relationships, cascade delte, enforce r/i, etc. your first option won't work in our case - the parent tables are too dissimilar to enable us to combine them - more real life example is storing the address of a person and the address of an asset (e.g., property) . . . both addresses need to contain the same type of info, but they belong to two entirely different beasts. I'm working on separating them out - preferable to have multiple address tables, I think, that each store a unique type of data - but in the meantime we've created a mishmash (how exactly do you spell that?) gather the option of parent_table/parent_pk doesn't work for you? thanks -bill -Original Message- Sent: Wednesday, July 31, 2002 4:00 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Bill, simply combine the employee, supplier, contractor and vendor tables into one such table titled people. Add a column to indicate what type of record it is (a code indicating one of the above). solves your problem. if this is not possible, then the address table could have multiple columns, one for each of the tables above with a FK pointing to the master table. however, none of these columns can be the PK for the address tables - you will need to create a column (supported by a sequence) that will contain the PK. I would also then add a column in the address table indicating what type of record this address is for - employee, supplier, etc. I would go with the first option if at all possible. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, July 31, 2002 4:43 PM To: Multiple recipients of list ORACLE-L Good day, all: Am curious to hear opinions on how to model a child table that has multiple parent tables (i.e., foreign key to multiple parents) Example: There's a table that stores Addresses (table ADDRESS) for both employees (table EMPLOYEE) and suppliers (table SUPPLIER). Each of these tables has a Primary Key field called ID. One way to set this up would be for the ADDRESS table to have 2 fields, EMPLOYEE_ID and SUPPLIER_ID, which would be mutually exclusive (i.e., one or the other, to indicate the parent record of the address). Another solutions if for the ADDRESS table to have two fields to indicate the parent table name and parent table pk value. The first method enables me (the dba) to create foreign keys from the address table to each of the parent tables to validate data. The second method does not enable me to create such foreign keys (leaving it to the developers to validate date and insure referential integrity) but would also easily facilitate the addition of other parent tables (e.g., CONTRACTOR, VENDOR, etc.) without altering the ADDRESS table itself. Any and all thoughts, comments, opinions, experiences are most welcome. Thanks! bill magaliff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: data modeling question - child table with multiple parents
yes it does help - thanks only potential issue with my second option is that the different parent tables may be distributed and not located in the same node - in that case using a straight FK is out, right? (both parent and child would have to be on the same node?) but what about a trigger in a distributed db? have to check more into that . . . thx bill -Original Message- Sent: Wednesday, July 31, 2002 4:04 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Bill, There are other approaches as well. A table for each type of address. This allows you to have foreign keys. You can use use a view to pull all the tables together, and just include the discriminating column in the view. It's an option, but not one I personally care for. Another is similar to your second one, but you could partition the table on the discriminating column, provided it's a large table. With your second approach, you can use a trigger to enforce referential integrity. This method is quite a bit more flexible than the first one as well. If you use a lookup table to enforce the values that go into the TABLENAME column, the enforcement becomes data driven. Don't leave data integrity to developers, most of them place too much trust in their users. :) In your first method ( using multiple Id columns ), you can use a check constraint to ensure that only one of the columns is populated at one time. This becomes unwieldy with more than 3 columns, in which case a trigger is easier to manage. HTH Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/31/2002 01:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:data modeling question - child table with multiple parents Good day, all: Am curious to hear opinions on how to model a child table that has multiple parent tables (i.e., foreign key to multiple parents) Example: There's a table that stores Addresses (table ADDRESS) for both employees (table EMPLOYEE) and suppliers (table SUPPLIER). Each of these tables has a Primary Key field called ID. One way to set this up would be for the ADDRESS table to have 2 fields, EMPLOYEE_ID and SUPPLIER_ID, which would be mutually exclusive (i.e., one or the other, to indicate the parent record of the address). Another solutions if for the ADDRESS table to have two fields to indicate the parent table name and parent table pk value. The first method enables me (the dba) to create foreign keys from the address table to each of the parent tables to validate data. The second method does not enable me to create such foreign keys (leaving it to the developers to validate date and insure referential integrity) but would also easily facilitate the addition of other parent tables (e.g., CONTRACTOR, VENDOR, etc.) without altering the ADDRESS table itself. Any and all thoughts, comments, opinions, experiences are most welcome. Thanks! bill magaliff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: data modeling question - child table with multiple parents
igor: I agree with what you say here, as well as your previous comment about deleting a laid-off employee and then the extra step of finding the orphan addresses. Our developers have imbedded this inverse logic throughout the application. and now I'm left to try to figure out how to validate data and make sure the logic is accurately represented by the data model. I think I will ultimately end up with multiple address tables to support distributed data, proper enforcement of parent-child relationships via foreign keys (as opposed to triggers - don't like using them for r/i issues) as well as the concept of not mixing different conceptual data elements within the same table. Works for this case. -bill -Original Message- Sent: Wednesday, July 31, 2002 5:39 PM To: Multiple recipients of list ORACLE-L seems to me like a case of inverse logic. is it employee (or supplier, or whatever) entity, which has address attribute, or is it address entity, which has employee (or supplier) as an attribute? for me, it's the first: I'm not interested in any address, if it does not belong to employee, or supplier, or whoever... info stored in address table is just common set of attributes split from employee, or supplier table. and, if they'd stay in those tables, employee_id (or supplier_id) would be PK - not address_id. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 31, 2002 5:04 PM Since the ADDRESS table is just a look-up table, why not let it have a primary key for each address and then let the EMPLOYEE and SUPPLIER tables reference it with a foreign key? That does not prevent the EMPLOYEE and SUPPLIER tables from having their own unique primary keys. -Original Message- Sent: Wednesday, July 31, 2002 4:43 PM To: Multiple recipients of list ORACLE-L Good day, all: Am curious to hear opinions on how to model a child table that has multiple parent tables (i.e., foreign key to multiple parents) Example: There's a table that stores Addresses (table ADDRESS) for both employees (table EMPLOYEE) and suppliers (table SUPPLIER). Each of these tables has a Primary Key field called ID. One way to set this up would be for the ADDRESS table to have 2 fields, EMPLOYEE_ID and SUPPLIER_ID, which would be mutually exclusive (i.e., one or the other, to indicate the parent record of the address). Another solutions if for the ADDRESS table to have two fields to indicate the parent table name and parent table pk value. The first method enables me (the dba) to create foreign keys from the address table to each of the parent tables to validate data. The second method does not enable me to create such foreign keys (leaving it to the developers to validate date and insure referential integrity) but would also easily facilitate the addition of other parent tables (e.g., CONTRACTOR, VENDOR, etc.) without altering the ADDRESS table itself. Any and all thoughts, comments, opinions, experiences are most welcome. Thanks! bill magaliff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Clark, Tommy R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you
RE: conferences - IOUG vs OOW
rachel: unfortunately i won't be able to make the July meeting or the Hotsos clinic - will be on a long-awaited vacation. look forward to september meeting (the food at columbia faculty house is pretty good!) -Original Message- Sent: Wednesday, June 26, 2002 10:23 PM To: Multiple recipients of list ORACLE-L Bill, Wait'll you see the agenda for the September meeting. And I've almost got December's filled.. although I *am* looking for DBA presentations, anyone interested (please note NYOUG does NOT pay travel expenses) send me a note off-line with an abstract. Come to the DBA SIG in two weeks -- since Cary's company Hotsos is giving a clinic in NYC, we've got two of his partners coming to give a presentation on using the 10046 trace files. Rachel --- Magaliff, Bill [EMAIL PROTECTED] wrote: thanks to both of you, Rachel and Stephen . . . clears it up for me. btw I do attend NYOUG meeings (except the last one - couldn't bear the thought of a NY Harbor cruise in the lousy weather, rocking to and fro) and do find them pretty interesting - and some nifty giveaway's, too, are always nice for the kids :-) -bill -Original Message- Sent: Wednesday, June 26, 2002 5:40 PM To: Multiple recipients of list ORACLE-L my preference when I have to choose is IOUG over OOW OOW tends to have a lot more marketing/Oracle employee (so it feels like marketing) presentations --- Magaliff, Bill [EMAIL PROTECTED] wrote: Having never been to either and wanting to plan for one, am I to infer from some of this discussion that for real content (as opposed to marketing fluff), THE conference to attend would be IOUGLive, as opposed to Oracle Open World? -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: address table
uma: we do exactly this - have a table called POSTAL_CODES with a data structure that holds all the data from the USPS citystate.txt file that gets distributed monthly. please contact me off-line and I'd be happy to share it with you, including the sql-loader parameters to load data. bill -Original Message- Sent: Wednesday, June 26, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Hi Guys, I am looking to create a table which will contain all the postal code reference data as in zip code, county, city,state etc. The source of the data is USPS. I am trying to find a table structure that I can implement quickly to hold the all the data and have a primary key to represent the unique values in the table. This is kind of urgent so please help! Thanks, Uma -- 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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
conferences - IOUG vs OOW
Having never been to either and wanting to plan for one, am I to infer from some of this discussion that for real content (as opposed to marketing fluff), THE conference to attend would be IOUGLive, as opposed to Oracle Open World? -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: conferences - IOUG vs OOW
thanks to both of you, Rachel and Stephen . . . clears it up for me. btw I do attend NYOUG meeings (except the last one - couldn't bear the thought of a NY Harbor cruise in the lousy weather, rocking to and fro) and do find them pretty interesting - and some nifty giveaway's, too, are always nice for the kids :-) -bill -Original Message- Sent: Wednesday, June 26, 2002 5:40 PM To: Multiple recipients of list ORACLE-L my preference when I have to choose is IOUG over OOW OOW tends to have a lot more marketing/Oracle employee (so it feels like marketing) presentations --- Magaliff, Bill [EMAIL PROTECTED] wrote: Having never been to either and wanting to plan for one, am I to infer from some of this discussion that for real content (as opposed to marketing fluff), THE conference to attend would be IOUGLive, as opposed to Oracle Open World? -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
changing passwords for INTERNAL and SYS on Unix
Hi, db version 8.1.7 on Solaris want to change passwords for internal and sys under NT/Win2K I can do an ALTER USER SYS IDENTIFIED BY newpassword and that will change for both and update the password file (very nice) can't find any concrete instruction for Unix . . . are the passwords for SYS and INTERNAL linked in the same way? Do I need to change SYS password and then recreate the password file for INTERNAL password? thanks much bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
autostart 8.1.7 intelligent agent on Solaris
trying to set up the intelligent agent to autostart on unix . . . if it's already running and I save the listener config (lsnrctl save_config) does that save the agent config, too? or do I have to manually put the line lsnrctl dbsnmp_start in my boot rc script? thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
two different 9i install problems!
Installing 9i (9.2.0.1.0) for the first time on a Win2K server and on my Win2K pro client SERVER ISSUE: standard install- enterprise edition - standard starter datababse - software appears to install and then config tools launch net config assistant complets dbca has now been running for about 18 hours - says in progress and an empty sqlplus command window is also open I see all the datafiles got created, and task manager shows varying CPU utilization between 20 and 50%, most of which is the System Idle process and my pcAnywhere connection . . appears hung! CLIENT ISSUE installing client only into a separate Oracle Home (already have an 8.1.7 client on my box). install appears to complete, but near the end I get a Windows File Protection box that says: Files that are required to run windows have been replace by unrecognized versions. To maintain system stability Windows must restore the original versions of these files. Then get prompted to insert my original Windows CD any and all help appreciated thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: sequence question
what do you mean about losing numbers after an import/export? -Original Message-From: Ramon E. Estevez [mailto:[EMAIL PROTECTED]]Sent: Friday, June 14, 2002 10:44 AMTo: Multiple recipients of list ORACLE-LSubject: Re: sequence question Bigp Remember that when you issue sequence.nextval you are incrementing one value and eitheryour process function corrector not that number is used. But, the more important is that if your system crash or you issue an SHUTDOWN ABORT you will loose the sequence numbers cached. Also you can loose numbers after an Import / Export. Luck, Ramon - Original Message - From: BigP To: Multiple recipients of list ORACLE-L Sent: Thursday, June 13, 2002 8:23 PM Subject: sequence question Hi List , I want to create a sequence which will be used by some external process to generate some unique number . since this sequence will be used very frequently I would prefer to cache around 1 numbers . Am I going to loose some numbers ? what is SEQUENCE_CACHE_ENTRIES and how does it affect number or sequence cached ? Thanks , -Bp
RE: Cleanup of child records...
Title: Cleanup of child records... delete from child_table where fk_field not in (select pk_field from parent_table); or delete from child_table where not exists (select pk_field from parent_table where parent_table.pk_field = child_table.fk_field); not exists usually out performs not in, but both will work bill -Original Message-From: Richard Huntley [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 13, 2002 12:59 PMTo: Multiple recipients of list ORACLE-LSubject: Cleanup of child records... TIA List, What is the most efficient way to remove child records from a table that have no parent records in it's parent table. I want to build a FK, to keep this from happening, but I need to do some cleanup first.
how to you stop an export?
how do you stop an command-line export? press CTL-C countless times and ususally just stops the current table . . . usually end up killing the entire session. was hoping there's a different way? thx bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: How to move 200 GB db from prod to dev?
Title: How to move 200 GB db from prod to dev? 1) use RMAN to clone the db; or 2) create the new dev db with the required users but without all the data/index tablespaces, and use transportable tablepsace feature to move tablespaces/data files . . . export/import of meta-data is quick - only real time factor is copying the data files - this assumes, of course, that you can make your production tablespaces read-only for the time it takes to copy (or tar) the data files -bill -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002 10:28 AMTo: Multiple recipients of list ORACLE-LSubject: How to move 200 GB db from prod to dev? Hi! We are supposed to clone our production database onto a new development box (both boxes are Sun Solaris). The db is about 200 GB in size. What would be the best way to achieve this? Simply copying over the files won't work, since the instance names are different: Production: SID=PCLDB1 = e.g. /u02/oradata/PCLDB1/system01.dbf Development: SID=ROLAND = e.g. /u02/oradata/ROLAND/system01.dbf So would export/import the entire db be the only way? (But writing out dump file that big should be a little disk space problem...) Renaming all the datafiles (approx. 100) would be kind of annoying... Any ideas? This is 8.1.7 on Sun Solaris. Thanks, Helmut
817 intelligent agent resolves to wrong dns entry
config: OEM 2.2, Win2K management server running 81721 one of the Win2K nodes I'm trying to discover has 2 dns entries, one for the machine name and one for the name of the Oracle Names server that resides on it (i have a separate dns entry for the names servers because I periodically need to move them and I put the names of the servers in the SQLNET.ORA, rather than the IP addresses - this works fine). issue is when I discover the node with the names server on it (fwfsdb06, listed as the entered name), the node name by which it gets discovered is oraclenames2. i have another names server on a different machine that resolves correctly (i.e., to the machine name, not to oraclenames1) - just this one that seems to be problematic. I've tried bouncing the agent, deleting the agent config files and bouncing it, even tried putting a second ip address on that server and have oraclenames2 resolve to the second IP address (and bouncing the agent) - now when I try to discover fwfsdb06, it fails completely that the agent is not running, and the node name still shows up as oraclenames2 any ideas? thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
db link service name resolution via Oracle Names?
Has anyone had success gettin db links to resolve service names using Oracle Names? seem to recall reading something that required the local TNS entry for db links to successfully resolve, but also read that Names automatically creates a global db link for every service name registered with it. bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: db link service name resolution via Oracle Names?
thanks to both of you . . . will change SQLNET params Dick - are you saying that Names v8i doesn't create the global db links any more? -Original Message- Sent: Tuesday, June 04, 2002 3:28 PM To: Multiple recipients of list ORACLE-L Just a couple of notes w/ names I just learned of when our primary Oracle names server failed. Change the default failover time using these parameters in your sqlnet.ora. NAMES.INITIAL_RETRY_TIMEOUT = 5 # Wait # Seconds before going to next nameserver, def=15 NAMES.REQUEST_RETRIES = 2 # Number of retries for nameserver, def=5 hth, Gene PS. 75 seconds sure appears to be a long time to fail, esp. when the help desk keeps calling :). [EMAIL PROTECTED] 06/04/02 02:53PM Bill, With Onames you don't need the TNSnames.ora file anymore. It does resolution of database service names automatically no matter if it's a database or client doing the lookup. Now previous versions of ONames did a very nice job of allowing you to create global database links, but version 8.1 messed that up REAL bad. Dick Goulet Reply Separator Author: Magaliff; Bill [EMAIL PROTECTED] Date: 6/4/2002 10:31 AM Has anyone had success gettin db links to resolve service names using Oracle Names? seem to recall reading something that required the local TNS entry for db links to successfully resolve, but also read that Names automatically creates a global db link for every service name registered with it. bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: foreign key what???? help!!
Title: RE: foreign key what help!! are you loading more than one table? could you be loading child records b4 the parents are inserted? -bill -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Monday, June 03, 2002 6:13 PMTo: Multiple recipients of list ORACLE-LSubject: RE: foreign key what help!! Okay guys, Have foreign keys defined on large dw tables. When loading get foreign key errors. However, after load (w/o change in data and supposedly data it was barking on) could create fk's just fine on the same data elements involving the same data - I SWEAR. This does not make sense to me. Any ideas?
RE: For real Gurus only
great! thanks -Original Message- Sent: Thursday, May 30, 2002 5:23 AM To: Multiple recipients of list ORACLE-L Hello Gurus I got this link through SAG-L. Have a try. www.quest-pipelines.com/newsletter-v3/Crossword_Puzzles/puzzle0502.html Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
rman duplicate dbid?
I'm just starting to set up RMAN (8.1.7+) . . . I'm registering all of my databases one by one from the command line. I have two db's on the same solaris box and when I run the rman command they both show up with the same DB_ID, thus preventing me from registering both of them . . . I get an error when registering the second that it's already registered. they are distinct db's . . . I tried unregistering one and then retrying, and again I get the same DBID for both. any ideas? thanks, y'all -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: rman duplicate dbid?
found a note on metalink about how to change the dbid by recreating the controlfile (note 174625.1) . . . one of those notes with the lovely disclaimer about how the script is not supported by Oracle support, done at your own risk, don't try this on a production db, etc. . . . anyway, it worked just fine and I was able to register the second db in the catalog. why would it give me more flexibility to use a separate catalog for each prod database? (also, we're a dev shop - only db's in use here are for app dev and qa) any and all thoughts are appreciated tx -Original Message- Sent: Thursday, May 30, 2002 12:11 PM To: Multiple recipients of list ORACLE-L Bill Perhaps you created one by cloning it from the other one. This can change the SID, but won't change the DB_ID. RMAN can't deal with multiple instances with the same DB_ID. The simplest way around this is to create a separate RMAN catalog for one of them (just create a separate username). Actually, I'm becoming convinced that maybe the way to go is to create a separate catalog for each production database. This gives you more flexibility, and I haven't seem any disadvantages to this. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 30, 2002 9:59 AM To: Multiple recipients of list ORACLE-L I'm just starting to set up RMAN (8.1.7+) . . . I'm registering all of my databases one by one from the command line. I have two db's on the same solaris box and when I run the rman command they both show up with the same DB_ID, thus preventing me from registering both of them . . . I get an error when registering the second that it's already registered. they are distinct db's . . . I tried unregistering one and then retrying, and again I get the same DBID for both. any ideas? thanks, y'all -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
sql plus help - display more text on a line
how can I display more text on a line? I've adjusted linesize, but the last several columns are chopped off . . . there's a screen buffer setting in the SQLPlus environment, but it doesn't seem to do anything that I can see. thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
changing db name
I saw a post on MetaLink (Note 15390.1) about changing db_name and oracle_sid without recreating the db's. my question is if I want to change the db_name, do I first need to change the SID? Or can I do it in one fell swoop, as follows: backup controlfile to trace edit file to create new controlfile using set database newdbname rename initsid.ora with new sid edit init.ora file with new controlfile names and new sid, db_name, etc. create new password file startup db with ORACLE_SID env variable set to new sid create new controlfile ...also can I rename datafiles and logfiles in the process by putting their new names in the create controlfile script? thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: OSUSER in V$SESSION capture in procedure?
I, too, have a situation whereby all users access the db via third party app with single credentials - haven't found a way around it, other then application-side logging -bill -Original Message- Sent: Wednesday, May 22, 2002 11:34 AM To: Multiple recipients of list ORACLE-L Hello Listers, I have what I hope is challenging problem. I am trying to create a procedure that execs from a trigger on a table. Simple enough. But I want to capture the OSUSER value from v$session so that the there is a history of changes to the table and by whom. Problem with using USER function is that all the users access the server via a third party app and therefore have one username. Pretty pointless for this effort then, as I could update the column in the history table with that user and be done with it. But the use of UID also does not work because that brings back a whole list of all the OSUSER value. ie CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS V_UID NUMBER; V_OSUSER BEGIN BEGIN SELECT UID INTO V_UID FROM DUAL; END; BEGIN SELECT OSUSER INTO V_OSUSER FROM V$SESSION WHERE OSUSER := V_UID; END rest of procedure.Includes insert etc END TEST Now obviously this returns more than one row as all the users use the same username through the app. Any suggestion? Many TIA Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. # This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com # -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denham Eva INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: New bee: How to convert 'LONG' data
export the old and import into the new -Original Message- Sent: Wednesday, May 22, 2002 6:03 PM To: Multiple recipients of list ORACLE-L Hi, I have a table 'x' with one of the column as 'LONG' I would like to copy the data from the above table 'x' into a different table 'Y' with the same structure. When I tried insert into x select * from y I am getting Illegal use of LONG data type. Could any one please help me to move the data? Thanks Sridhar. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sridhar Moparthy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: trouble importing
will try multiple imports - can they go against the same physical dump file? or do I need to copy the dump file for each separate import? will also restart with analyze=n - we're using RBO anyway seem to be two ways with COMMIT param - COMMIT = Y and a large buffer (someone else's post) COMMIT = N and a large RBS (oracle recommendation) any thoughts on this? I would love to use TTS but physically cannot ftp the 70G datafiles from our remote source (a client site) - the compressed dump file is just under 1Gb -Original Message- Sent: Thursday, May 16, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Bill - http://www.orafaq.com/faqiexp.htm#SPEED for some tips. Do you have any alternatives to importing? Transportable tablespaces, database cloning, SQL*Net, for example? If your server has multiple CPUs, you can start multiple import sessions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 11:09 AM To: Multiple recipients of list ORACLE-L I have a 17Gb db that I need to import (Sun 880 running Solaris 5.8). the largest tables (of which there are several) are in the 1-3Million row range . . . a few contain longs. so far it's been running about 24 hours and is only half-way done. have 500Mb shared pool and 350Mb db buffer cache - one large rbs (100Mb extents), COMMIT=N set on import. any ideas how to speed this up? thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: trouble importing
thanks I'll try that . . . bouncing db now -Original Message- Sent: Thursday, May 16, 2002 1:10 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Bill, If the tables already exist, drop all indexes, FK and PK constraints. They will be re-created by the import and this will greatly speed things up. Try setting an obscenely large SORT_AREA_SIZE before running the import to speed up index creation. ( Like 50 - 100 meg ) Don't forget to set it back after the import. This is of course requires bouncing the database twice. Use COMMIT=Y and BUFFER=10485760 Buffers larger than 10 meg have not shown a corresponding increase in speed, at least in my experience. HTH, Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/16/2002 09:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:trouble importing I have a 17Gb db that I need to import (Sun 880 running Solaris 5.8). the largest tables (of which there are several) are in the 1-3Million row range . . . a few contain longs. so far it's been running about 24 hours and is only half-way done. have 500Mb shared pool and 350Mb db buffer cache - one large rbs (100Mb extents), COMMIT=N set on import. any ideas how to speed this up? thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Trigger or ????
Title: Trigger or try a cron job (if unix) or at job (if NT) -Original Message-From: Burton, Laura L. [mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 10:58 AMTo: Multiple recipients of list ORACLE-LSubject: Trigger or I have a need to reset a sequence number at 00:01 everyday. I thought about creating a trigger to check the time, but thought that there might be a better way than checking the time every time a record is being added. I also thought about checking the max date on the table and comparing against the system date. When system date max then reset the sequence number. I like this logic better and thought of holes with using the time. The only problem I have is that this seems like a lot of overhead every time I add a record, which will be often. Is a trigger the only method available to me? Thanks, Laura
errors with CREATE SYNONYM procedure
I've developed a procedure that will create a public synonym on an object, which I'd eventually like to call from a ddl after create on schema trigger. But I'm first working on this piece - I get an INSUFFICIENT PRIVILEGES error (ORA-1031) on the EXECUTE IMMEDIATE line. The user running the proc has both DBA role and specifically the CREATE ANY SYNONYM procedure, which should be enough. I can create the synonym manually from SQL*Plus, but never through this proc . . . any ideas? thx here's the code, which compiles perfectly: CREATE OR REPLACE procedure CREATE_GRANTS ( p_lwowner varchar2, p_objname varchar2, p_objtype varchar2) AS pragma autonomous_transaction; v_lwowner varchar2(30) := p_lwowner; v_objname varchar2(30) := p_objname; v_objtype varchar2(30) := p_objtype; v_sql varchar2(200); begin v_sql := 'create public synonym ' || v_objname || ' for ' || v_lwowner || '.' || v_objname; execute immediate (v_sql); end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: errors with CREATE SYNONYM procedure
That was it - thanks! Forgot there were two distinct system privs - CREATE ANY SYNONYM and CREATE PUBLIC SYNONYM -Original Message- Sent: Friday, May 10, 2002 10:38 AM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Bill, Does the OWNER of the proc have CREATE PUBLIC SYNONYM system priv? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, May 10, 2002 11:24 AM To: Multiple recipients of list ORACLE-L I've developed a procedure that will create a public synonym on an object, which I'd eventually like to call from a ddl after create on schema trigger. But I'm first working on this piece - I get an INSUFFICIENT PRIVILEGES error (ORA-1031) on the EXECUTE IMMEDIATE line. The user running the proc has both DBA role and specifically the CREATE ANY SYNONYM procedure, which should be enough. I can create the synonym manually from SQL*Plus, but never through this proc . . . any ideas? thx here's the code, which compiles perfectly: CREATE OR REPLACE procedure CREATE_GRANTS ( p_lwowner varchar2, p_objname varchar2, p_objtype varchar2) AS pragma autonomous_transaction; v_lwowner varchar2(30) := p_lwowner; v_objname varchar2(30) := p_objname; v_objtype varchar2(30) := p_objtype; v_sql varchar2(200); begin v_sql := 'create public synonym ' || v_objname || ' for ' || v_lwowner || '.' || v_objname; execute immediate (v_sql); end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
AFTER CREATE trigger help, please
I'm creating an AFTER CREATE ddl trigger:
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE'
then
begin
dbms_output.put_line ('TEST');
dbms_output.put_line (sys.dictionary_obj_name || ' , ' ||
sys.dictionary_obj_type);
exception
when others then
dbms_output.put_line ('ERROR');
end;
end if;
end;
/
Trigger created as user LWDEV
I have serverout set to ON, but I get nothing when creating a new table . .
.
any ideas, please??
thanks, all
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: AFTER CREATE trigger help, please
ok - thanks
i edited the trigger to insert a row into a dummy table, just to see if it
works, and all works fine.
Now - the next piece.
I want this trigger to call a stored procedure that runs as an autonomous
txn and creates a public synonym for the newly-created table.
I've verified independently that the proc works (from the SQL*Plus command
line) and I know now that the trigger is being called, based on the previous
test.
so why wouldn't the public synonym be created?
THANKS!
**
Here's the text of the new trigger and procedure:
CREATE OR REPLACE procedure CREATE_SYNONYMS (
p_lwowner varchar2,
p_objname varchar2,
p_objtype varchar2) AS
pragma autonomous_transaction;
v_lwowner varchar2(30) := p_lwowner;
v_objname varchar2(30) := p_objname;
v_objtype varchar2(30) := p_objtype;
v_sql varchar2(200);
begin
v_sql := 'create public synonym ' || v_objname || ' for ' ||
v_lwowner || '.' || v_objname;
execute immediate (v_sql);
end;
/
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE' then
begin
create_synonyms ('lwdev', sys.dictionary_obj_name,
sys.dictionary_obj_type);
exception
when others then null;
end;
end if;
end;
/
-Original Message-
Sent: Friday, May 10, 2002 1:14 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Bill,
A trigger cannot display output.
Where would it go? A trigger does not run in an interactive
session, it runs in the database independently, regardless
of the presence or absence of a SQL Plus session.
Jared
Magaliff, Bill [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/10/2002 09:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:AFTER CREATE trigger help, please
I'm creating an AFTER CREATE ddl trigger:
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE'
then
begin
dbms_output.put_line ('TEST');
dbms_output.put_line
(sys.dictionary_obj_name || ' , ' ||
sys.dictionary_obj_type);
exception
when others then
dbms_output.put_line
('ERROR');
end;
end if;
end;
/
Trigger created as user LWDEV
I have serverout set to ON, but I get nothing when creating a new table .
.
.
any ideas, please??
thanks, all
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
why does DBCA (DB Create Assist) reassign default tablesepace for
Anyone know why, when using DBCA, it configures user SYSTEM to have a default tablespace of TOOLS and not SYSTEM? This is a separate script it runs at the end, after the db and data dictionary are both created. thought system should have system tablespace as default, no? thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Syntax For Dropping a default value?
try this: alter table table_name modify column_name default null; -Original Message- Sent: Friday, May 03, 2002 11:13 AM To: Multiple recipients of list ORACLE-L Hi, Been searching for syntax to drop a default value (we decided NOT to use the default value) for a colmun. I looked in the docs and even tried to remove the default value via DBA Studio. No go. Can you not modify a column to REMOVE a default value specification? Thanks, Hannah (Its NOT in the docs.) -- 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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
access another user's data in a stored procedure - BECOME USER pr
I'm writing a stored procedure to be run by the dba. I want to provide the ability to manipulate data from a schema specified at runtime. I have the schema owner as an input variable, but I see three possible way to do this: 1) have the dba verify that all necessary DML privs on that schema are granted to the user running the proc, and that synonyms are properly defined - therefore there's no need to put a schema qualifier in front of every object name in the proc 2) verify all DML privs as above, put DO put a schema qualifier in front of every object name in the proc - no need, therefore, to have the synonyms (although they won't hurt) 3) grant BECOME USER to the running user, and issue ALTER SESSION SET CURRENT_SCHEMA before running the proc. Option 3 seems the easiest to code, but I'm not sure about this particular priv - some of the info on MetaLink seems to indicate this priv is only valid for importing, but not sure. anyone have any experience with this priv, or willing to provide general feedback on how to proceed? I'm relatively new to this and am also in search of guidelines to follow when writing this sort of stuff. many thanks. bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
good pl/sql coding/debugging tool?
I'm trying to evaluate tools for pl/sql coding/debugging - main options seem to be toad, sql navigator (both from quest) and rapid sql (from embarcadero). One nice thing I noticed about the embarcadero product is the ability to debug anonymous blocks . . .but haven't looked seriously into other pl/sql constructs (i.e., records, types, etc.) any comments/feedback/suggestions from the group would be greatly appreciated . . . thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Any scripts to check the health of the database
love it! - this gets my vote - -Original Message- Sent: Wednesday, April 24, 2002 4:58 PM To: Multiple recipients of list ORACLE-L sqlplus / as sysdba EOF select 'Database is healthy!!!' from dual; shutdown abort prompt 'Database needs Clarinex' EOF -Original Message- From: Praveen Sahni [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 24, 2002 3:24 PM To: Multiple recipients of list ORACLE-L Subject: Any scripts to check the health of the database Hi All, I need to write a report on the health of a database. Please send if any of us have a scripts and any report format for the health of the database. Thanks in advance Praveen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Praveen Sahni INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
anyone know how to change tablespace names?
is there a back-end way to effect an alter tablespace rename to ... type of thing? yes, I know Oracle doesn't support mucking with the data dictionary, but . . . thx -bill Bill Magaliff Framework, Inc. 914-631-2322 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Using XML in the dba
thanks - don't need from PL/SQL, just regular SQL calls that will return tagged XML -Original Message- Sent: Fri, April 05, 2002 8:05 AM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Bill, If you will be using the XML from PL/SQL calls, you will probably need the Oracle XDK for PL/SQL packages installed. There are several versions available. I actually installed the 9.0.2.0.0D version in my 8172 database and it seemed to work ok. The developer I was working with tried it, but dropped it for lack of time and interest. I created a separated Oracle account for the packages and installed the stuff there just so I could drop it easily. The packages are available on TechNet. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, April 04, 2002 5:56 PM To: Multiple recipients of list ORACLE-L I need to set up my 8.1.7.2 db to produce XML output from SQL queries. Referring to the Oracle XML Handbook (Oracle press) I think we're interesed in the XSQL . . . need to know how to prepare the db/client for this. I installed the JServer manually (via the initjvm.sql script) and have a 20Mb Java pool on the server (NT 40 SP6a) I see the client needs JDK 1.1.6 or higher. Is there anything else I need to do on the server? The developer will be accessing via Net8 (I think) . . . . not even sure what other questions I need to ask. Thanks, all Bill Magaliff Framework, Inc. 914-631-2322 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Copy tables and indexes etc from one db to another
if the tables/indexes can be in self-contained tablespaces, check out the Transportable Tablespace option -bill -Original Message- Sent: Fri, April 05, 2002 8:59 AM To: Multiple recipients of list ORACLE-L export/import --- [EMAIL PROTECTED] wrote: Hallo, anyone whom can tellme the easiest way to copy tables etc from one database to another. Thanks in advance Roland -- 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!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 XML in the dba
I need to set up my 8.1.7.2 db to produce XML output from SQL queries. Referring to the Oracle XML Handbook (Oracle press) I think we're interesed in the XSQL . . . need to know how to prepare the db/client for this. I installed the JServer manually (via the initjvm.sql script) and have a 20Mb Java pool on the server (NT 40 SP6a) I see the client needs JDK 1.1.6 or higher. Is there anything else I need to do on the server? The developer will be accessing via Net8 (I think) . . . . not even sure what other questions I need to ask. Thanks, all Bill Magaliff Framework, Inc. 914-631-2322 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: what pl/sql construct can return multiple rows?
Could not find any specific examples of this usage . . . 1) must it be inside a package? could it just be defined in a procedure? 2) why IN/OUT? What's being passed in? 3) why open with no fetch? what does that accomplish? thanks much bill -Original Message- Sent: Tue, March 19, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Lots of examples avlbl from Concepts manual; also pl check asktom.com Step 1) Declare a ref cursor inside a package CREATE PACKAGE APACK AS TYPE RefCurTyp IS REF CURSOR; END APACK; Step 2) Employ the ref curosr IN OUT variable inside the procedure..note that the cursor is opened but not fetched.. PROCEDURE demo_ref ( refcurvar IN OUT RefCurTyp, choice NUMBER) IS BEGIN IF choice = 1 THEN OPEN refcurvar FOR SELECT * FROM emp; ELSIF choice = 2 THEN OPEN refcurvar FOR SELECT * FROM dept; ELSIF choice = 3 THEN OPEN refcurvar FOR SELECT * FROM sal; END IF; END demo_ref; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ways to speed up import
i'm getting larger and larger db dumps from clients that i need to import into local db's . . . dump appoaching 3Gb (only the one schem I need). Looking for ways to speed up import . . . tried playing with buffer size and commit=y, but not much luck. Also tried delaying index creation, which helps some . . . running 8.1.7.2 on solaris 8 - single cpu, depending on the box anywhere from 2 - 4 Gb RAM Thanks, all Bill Magaliff Framework, Inc. 914-631-2322 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
what pl/sql construct can return multiple rows?
Hi, Is there a way to write a procedure to return multiple rows? I have some nasty SQL that I'd like to convert to run server-side, but how do you spit out multiple rows from PL/SQL? thx Bill Magaliff Framework, Inc. 914-631-2322 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
is Oracle really planning to desupport RBO?
Has anyone heard anthing official or semi-official on this? Bill Magaliff Framework, Inc. 914-631-2322 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
