These are the commands I put at the beginning of each SQL script prior to running.
SET TIME ON
SET TIMI ON
SET DEF OFF
ALTER SESSION SET NLS_LENGTH_SEMANTICS = ‘CHAR’;
ALTER SESSION SET DDL_LOCK_TIMEOUT=60;
SPOOL “ticket #”_”environment”.lst
SET TIME ON – Provides timestamps for each operation, important for recordkeeping/auditing
SET TIMI ON – Most useful while testing. Which operations are taking a long time? Will this be an issue in production systems during the deploy?
SET DEF OFF – For DML statements, allow any &’s to be interpreted literally
ALTER SESSION SET NLS_LENGTH_SEMANTICS = ‘CHAR’; – Usually you want the VARCHAR2 data types to be CHAR and not BYTE (the default). While it is best to explicitly set this in the DDL commands, this will pick up any that were missed.
ALTER SESSION SET DDL_LOCK_TIMEOUT=60 – If the script tries to execute a DDL command on an object in use it may encounter a lock. This allows the script to retry for up to 60 seconds before timing out.
SPOOL – Write all output to a file. This should be kept as a record of what was done during the script’s run.