Can a view affect ALTER TABLE commands

alter-tableoracleupgradeview

I provide a database system to a number of customers. The database is installed locally on Oracle.

We periodically upgrade the database structure (either adding new fields, renaming old fields, changing things around), as the product grows.

One of my customers wishes to add their own custom views to the database. They've assured me they will add the view to a different schema to the live schema. However, I am concerned that the inclusion of a view might affect future upgrade scripts.

If a view references a table that is to be altered, will the presence of the view prevent the table from being altered?

Best Answer

No, it shouldn't prevent the table from being altered. Though, if you drop the underlying table a view depends on, or alter/remove the columns from the table the view uses, the view can become invalid. You can check what views are invalid in your system with the following query:

sql> select name from dba_objects where object_type = 'VIEW' and status = 'INVALID';

You should fix the problems before the view can be made VALID. The view can be compiled manually:

sql> alter view foo compile;

Or you can allow the system automatically compile it when someone attempts to query the view. However manual compilation is the recommended way because it reduces the execution time of the first query. Usually views and other invalid objects (procedures, triggers etc.) are recompiled in bulk using a supplied script utlrp and this is a routine task for a DBA.

If you want to create the view but the base table doesn't yet exist, you can use the option FORCE:

sql> create force view foo as select * from moo;

APPROACH #1

The easiest thing to do would be to revoke ALTER, DROP, and CREATE from all users except root

UPDATE mysql.user SET alter_priv='N',drop_priv='N',create_priv='N' WHERE user <> 'root';
UPDATE mysql.db   SET alter_priv='N',drop_priv='N',create_priv='N' WHERE user <> 'root';
FLUSH PRIVILEGES;

APPROACH #2

You said that you do not want to issue

SET sql_log_bin = 0;

on the client side. A very adventurous approach would be to use a Stored Procedure to do your ALTER TABLE commands. You could then apply SET sql_log_bin = 0; in the Stored Procedure. In effect, that happens on the server side.

DELIMITER $$

DROP PROCEDURE IF EXISTS `Local_Alter_Table` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Local_Alter_Table`
(
    SQLStatement VARCHAR(2048)
)
BEGIN

    SET sql_log_bin = 0;

    SET @sql = SQLStatement;
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

    SET sql_log_bin = 1;

END $$

DELIMITER ;

Then, require users to run

CALL Local_Alter_Table('ALTER TABLE ...');

GIVE IT A TRY !!!

Best Answer

Related Solutions

Oracle – How to Add ENUM Attribute to Existing Table Using ALTER

Mysql – Prevent replication of ALTER commands

APPROACH #1

APPROACH #2

Related Question