DBA: Poor Man's Data Vault

Nothing against women, so don't start with me. By "Man" I mean (hu)Man. Though there are plenty out there that would argue against any DBA, in any way, resembling a human.

Back in March I started this project on Google Code. Here I am 4 months later and I've finally gotten around to it.

What is it?
Version 1.0, or better, Version 0.1, will simply require you to set a context prior to deployment. All that means is this:

CJUSTICE@TESTING>BEGIN
  pmdv_work_pkg.create_work
    ( p_name => 'TICKET #44', 
      p_description => 'CLEANING UP CJUSTICE SCHEMA' );
END;
/

What's the big deal with that?
Well, it depends. The idea was originally sparked by watching my DBAs deploy my projects at a previous job. After deployment, they would run this script, enter the ticket number, start and stop time and it would spool off this report. I never looked at the report, but guessed that it had something to do with Security or Auditing. So I made the grand leap to assume it was all the objects that were affected by the deployment. It was all an assumption though as I was, on occasion, asked to provide details on objects that I deployed by the Security team.

What does it do?
It's pretty simple actually. One trigger, ON DATABASE BEFORE DDL is set up. That calls a package with the relevant exceptions, and determines whether the context has been set (if the other exceptions are not met). If all the exceptions are met and the context is set, you can deploy your changes (DDL). If not, you get a pretty error message like this:

CJUSTICE@TESTING>@packages/pmdv_work_pkg.pkb
CREATE OR REPLACE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: PMDV:  DEPLOYMENTS NOT ALLOWED!
ORA-06512: at line 14

When you first create your work (order), the start date and end date are set (end_date defaults to 30 minutes, but time can be added if necessary). Also, all INVALID objects are captured prior to deployment. Once you are finished, you issue the following commands:

CJUSTICE@TESTING>EXEC pmdv_work_pkg.end_work;

COMMIT;

When you issue the END_WORK command, the INVALID objects are captured again (DURING), the work record updated, then any changed objects during the deployment window are captured (from DBA_ALL_OBJECTS.LAST_DDL_TIME).

For those of you worried about putting such a thing in production, I am also providing a trigger (AFTER DDL) and a table to capture the DDL in your environment. That way you can note the exceptions and add them to the code before ever throwing such a thing in production. Not that I think many of you would do that...especially without more rigorous testing at the very least.

Testing is my next step. I know, it's backwards. I just want this out the door. I want someone to look at it and say, "Hey Chet, that's a piece of crap." or "You're nuts if you think I would ever put this on a production system!"

I hear you. Testing will be done. Test cases will also be provided so that you can verify the results as well. Hopefully, if enough of you nit-picking, anal-retentive DBAs take a look at it and critique it (good and bad), I can make something of it. Something that would be useful to everyone. So go crazy on it please.

Here's the package spec for PMDV_WORK_PKG:

FUNCTION CAN_DEPLOY_DDL RETURNS BOOLEAN
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_ORA_SYSEVENT   VARCHAR2  IN
 P_ORA_LOGIN_USER  VARCHAR2  IN
 P_ORA_INSTANCE_NUM  NUMBER   IN
 P_ORA_DATABASE_NAME  VARCHAR2  IN
 P_ORA_DICT_OBJ_NAME  VARCHAR2  IN
 P_ORA_DICT_OBJ_TYPE  VARCHAR2  IN
 P_ORA_DICT_OBJ_OWNER  VARCHAR2  IN

CAN_DEPLOY_DDL is used by the trigger, PMDV_BEFORE_DDL to determine whether or not the DDL is allowed. Currently, I have 3 exceptions:
1.  If the ORA_LOGIN_USER = SYS. DDL OK.
2.  If the ORA_LOGIN_USER IS NULL. I noticed this behavior on a 10g instance. DDL OK.
3.  By default Oracle does not allow objects to be created in the SYS schema. Unless your system is compromised in some way, it is reasonably safe to assume that if the ORA_DICT_OBJ_OWNER = SYS, DDL is OK.
4.  PMDV_CONTEXT is set. DDL is OK.

If you see anything in that list that jumps out at you, please let me know. I absolutely do not want to introduce something into the wild that could be harmful.

FUNCTION CREATE_WORK RETURNS NUMBER
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_NAME    VARCHAR2  IN
 P_DESCRIPTION   VARCHAR2  IN
 P_ESTIMATED_TIME  DATE   IN     DEFAULT

This gets it all started. It accepts 2 strings, they can be anything, there is no validation check on them. P_ESTIMATE_TIME defaults to 30 minutes. You can either create the work (order) with the default, it give yourself an hour, or however much time you need really.

PROCEDURE END_WORK

This ends your session, captures INVALID and CHANGED objects and clears the context.

PROCEDURE EXTEND_DEPLOYMENT_WINDOW
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TIME_TO_ADD   NUMBER   IN

Kind of self explanatory. Run this in the same (SQL*PLus) session to extend your window.

PROCEDURE JOB_LISTENER

When you issue the CREATE_WORK statement, the PMDV_LISTENER job is enabled and runs once a minute to check if your session has expired. If you finish in 5 minutes, you don't necessarily have to issue the END_WORK call. The job will expire the work (order) after the allotted time.

PROCEDURE RECONNECT
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_CLIENT_IDENTIFIER  VARCHAR2  IN

RECONNECT allows you to open up another session and re-enable the context (i.e. allow you to deploy). You can find this information in PMDV_WORK.CLIENT_IDENTIFIER column.

Since I'm using DBMS_SESSION.UNIQUE_SESSION_ID, you can only have 1 deployment per SQL*Plus session. All you need to do to get around that is DISCONNECT and login again.

For the love of Pete, give me some feedback. Is this something you could ever use? Am I silly (well, you don't have to answer that specifically) for thinking this up? Any and all comments will be most appreciated. I've spent a significant amount of time over the last couple of weeks getting this together...hopefully to some end.

Where do I find this?
The code can be found here. I'll be wrapping it up all nice and pretty over the next couple of days. I'll create a download package as well. The wiki page can be found here.

Oh yeah, did I mention, I built an Apex front end for it?

Here's what that looks like.

The Work (Order) Screen:


And the Work (Order) Details Screen: