Tuesday, January 7, 2014

schema validation scripts and alter session set current_schema ... make me not so grumpy

Believe it or not many DBAs/Developers are unaware of ( well or have forgotten ) how to "switch into" a different schema.

PLSQL has had the option for a long time

alter session set current_schema = SOME_SCHEMA_NAME;

This does not give you full schema owner capabilities ( well depends on what your login session capabilities have ) but can be very useful.  For instance in some kind of script to validate that all the expected objects exist and are at the corrrect version you could use it like this.

set echo off
set feedback on
set heading off
set linesize 168
set serveroutput on size unlimited
set term on

alter session set current_schema = FIRST_SCHEMA_BEING_CHECKED;

BEGIN
  validate_objects.bv_show_valid_messages := TRUE;
  validate_objects.bv_stop_on_error := FALSE;

  dbms_output.put_line(chr(13));
  dbms_output.put_line('===============================================================================');
  dbms_output.put_line(chr(13));
 
  -- a bunch of calls against a validation package ... check that tables exists / views exists / foreign keys exists / indexes exist / packages procedures functions exists / data exists

 
  -- at the end check that all objects are valid in the schema ...  
 
END;
/

No comments:

Post a Comment