Re: Create database link from inside a stored procedure

On Wed, 29 Mar 2006 11:59:51 +0100, Jeremy wrote:

>
> So assume the procedure is restricted to a user who logs in with a DBA
> (as far as the application is concerned) user (i.e. highest privilege
> the app provides).
>
> The question remains - why is it a "mess" and should not be permitted in
> a production environment? I genuinely want to know and consider that a
> statment such as "DDL should be run by a DBA, not by application code"
> doesn't provide any kind of answer.

1. A lot of DDL involves making decisions about resources that are to be assigned. These decisions usually vary dramatically between sites - involving such factors as 'single or multi instance environment', 'memory', and so on.

It has traditionally been considered bad form to be making decisions that affect resource utiltization blindly, or without interaction with the administrators. After all, it's the admnistrator who is responsible for proper utiltization of resources - or at least, the administrator is the one at the end of the pointing finger.

Im my experience, apps being installed with little or no regard for resource impact, or making gross assump[tions about resource availability, has been one of the greater reasons for 'Oracle Sucks' conclusions. (When in reality it's often simply passing the buck.)

This discussion is part of the greater discussion on resources and administration involving things like "Granting to PUBLIC and PUBLIC synonyms vs security", "Application-based instances vs instance consolidation", "hardware server vs database server consolidation" and the like.

2) All DDL in Oracle introduces an implicit commit before the DDL begins. This can totally break the transaction 'paradigm' that modern databases are supposed to use. It also involves locks and latches that have a serious impact on scalability.

As a result, ANY DDL in a production system, other than at initial setup and for maintenance, is considered a total no-no in an Oracle environment.

A common consideration is the temptation to create temporary tables on the fly a-la SQL Server mode of operation. Doing that simply proves (to me) that the designer has limited (or no) understanding of Oracle - which leads to questioning the validity of the application.

3) Even Oracle uses 'create' scripts (see $ORACLE_HOME/RDBMS/ADMIN) to do any serious DDL and system configuration. Although the source for stored packages, procedures & functions is often wrapped, the base objects are usually visble to admnistrators.

AS compared to Sybrand, I woul not consider your proposal a mess - if it is used intelligently (eg: initial setup only). However, it does cause many shhhudders up and down my spine.

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting [replies] guarantees I won't respond. ***