Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Who Says Oracle does not listen

RE: Who Says Oracle does not listen

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Fri, 18 Jul 2003 16:07:48 -0400
Message-Id: <25956.338520@fatcity.com>


Sure, I would.
But I can't wait till Oracle "turns around". My scripts are executed by our "field" engineers, who know next to nothing about Oracle, and the only thing they can do is to check log files for error messages (and even this is done automatically).

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On Behalf Of Goulet, Dick
Sent: Friday, July 18, 2003 3:44 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Who Says Oracle does not listen

Igor,

        True enough, but wouldn't you like it as part and parcel of the command?

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
Sent: Friday, July 18, 2003 4:35 PM
To: Multiple recipients of list ORACLE-L

To avoid those errors in my scripts I'm checking data dictionary for the "existence" of the object (fortunately, dynamic sql helps here):

REM Dropping synonym
DECLARE lCounter integer;
begin
SELECT COUNT(*) INTO lSyn

        FROM dba_synonyms WHERE synonym_name = 'PRCPV_REPORT_INFO' AND OWNER = 'PUBLIC';
IF (lSyn = 1) THEN

        EXECUTE IMMEDIATE 'drop PUBLIC SYNONYM PRCPV_Report_Info'; END IF;
end;
/

or:
REM Adding column
DECLARE lCounter integer;
begin
SELECT count(*) INTO lCounter

	FROM DBA_TAB_COLUMNS
	WHERE table_name = 'PRCP_MENU'
	  AND column_name = 'MENU_NAME'
	  AND owner = 'IPN_DBA';
IF (lCounter = 0) THEN
	EXECUTE IMMEDIATE 'ALTER TABLE prcp_menu ADD menu_name
VARCHAR2(50) NULL';
END IF;
end;
/

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Goulet, Dick
Sent: Friday, July 18, 2003 3:04 PM
To: Multiple recipients of list ORACLE-L

<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_top.
gif>
<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_top
.gif>
Update TAR Go to End
<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_bott
om.gif>
<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_bot
tom.gif>

<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_top.
gif>
<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_top
.gif>
TAR Number 3169102.996 Open Date 17-JUL-03 19:18:03 Support Identifier 1208611 Name Richard Goul Priority 4 Last Update 18-JUL-03 15:22:33 Product Oracle Server - Enterprise Edition Product Version 9.2.0.1.0 Platform HP-UX PA-RISC (64-bit) Detailed Status Soft Close TAR Reference n/a BUG Reference n/a

Abstract
<http://metalink.oracle.com/images/black.gif>
DROP OBJECT NO_FAIL
<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_bott
om.gif>
<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_bot
tom.gif>

<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_top.
gif>
<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_top
.gif>
Resolution History
<http://metalink.oracle.com/images/black.gif>

17-JUL-03 19:18:03 GMT ### Selected Industry ###
Discrete Manufacturing

### Reason current product functionality is insufficient. ### Today if you issue a "drop table <my_table>;" command and the table does not
exist you get an error back.

### Detailed description of the Enhancement Request. ### It would be great if we had an addition to the drop object command, something
like "drop table <my_table> no_fail;" which would always return "Table dropped"
whether it existed or not.

### How the product can be changed to achieve the desired result. ### Have no idea.

### Reasons to consider the Enhancement Request. ### When one creates scripts you run them many times with "whenever sqlerror exit"
or else have to look in a log file for errors. Many times not being able to
drop an object is an error, but many times, like when your running a script for
the first time, it isn't. Run any of the CAT scripts that you provide, you
have a marathon time scanning the script for real errors, like "could not
extend" and have to filter these nuisance errors.

### Business impact if the Enhancement is not considered. ### Well, many a DBA will continue to waste time reviewing these errors for no
purpose.

### Enhancement is affecting an implementation milestone. ### NO

### Description of the business flow that is affected by this Enhancement ###
Creating a database, installing any package third party or not, ETL processes,
etc....

Contact me via : E-mail -> dgoulet_at_vicr.com

17-JUL-03 19:19:19 GMT TAR has been assigned to an analyst -- Sending email.

17-JUL-03 19:46:59 GMT PROBLEM


Today if you issue a "drop table <my_table>;" command and the table does not
exist you get an error back.

It would be great if we had an addition to the drop object command, something
like "drop table <my_table> no_fail;" which would always return "Table dropped"
whether it existed or not.

PROBLEM VERIFICATION


Is this what you are requesting?

This should be incorporated in our cat*.sql scripts so the output log file doesn't
show all these ignorable errors/warnings and dba only has to look for 'real' errors.

17-JUL-03 19:47:26 GMT Email Update button has been pressed -- Sending email.

17-JUL-03 20:15:38 GMT New info : That's a part thereof. The Cat scripts are an example. Allow me if
you please:
This is from the CATREP.SQL script:
drop synonym dbms_offline_snapshot
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist What would be the enhancement, general in nature not just the cat* scripts,
would be to have an option on the command that allows it to not fail. Namely
in this particular case the command would be "drop synonym dbms_offline_snapshot nofail;" which would have return "Synonym dropped.".

17-JUL-03 21:20:03 GMT This would have to be filed as an enhancement request and code won't be changed until after 10i as it is close to release.

The thing is that these scripts are generic, they were created to be run on any version, environment, configuration, component, etc database for any user. If you don't have replication instsalled, it will give the error on all replication objects. Same with spatial or intermedia or adv security option, etc. It has to cover them all whether you have these components installed or not.

Generally the only errors we are concerned with in the output file are the ones that cause the entire script to hang or abort.

We totally agree with you in support as we also have to read through all this output whenever a customer has an installation/upgrade problem and uploads the cat*.sql output files. We have to go through and identify any errors that may cause problems in the database.

17-JUL-03 21:20:17 GMT Email Update button has been pressed -- Sending email.

18-JUL-03 14:45:20 GMT New info : Well, I did file this as an enhancement request, so having it in a
future version was the desired end result. If it can be included in a patch to
10i or part of 11G, as I understand it's going to be called, then great. Mission accomplished.

<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_bott
om.gif>
<http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_bot
tom.gif>

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Goulet, Dick
  INET: DGoulet_at_vicr.com

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


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Goulet, Dick
  INET: DGoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Received on Fri Jul 18 2003 - 15:07:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US