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>BEGINWhat's the big deal with that?
pmdv_work_pkg.create_work
( p_name => 'TICKET #44',
p_description => 'CLEANING UP CJUSTICE SCHEMA' );
END;
/
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.pkbWhen 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:
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
CJUSTICE@TESTING>EXEC pmdv_work_pkg.end_work;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).
COMMIT;
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 BOOLEANCAN_DEPLOY_DDL is used by the trigger, PMDV_BEFORE_DDL to determine whether or not the DDL is allowed. Currently, I have 3 exceptions:
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
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.
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.
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
PROCEDURE END_WORKThis ends your session, captures INVALID and CHANGED objects and clears the context.
Kind of self explanatory. Run this in the same (SQL*PLus) session to extend your window.
PROCEDURE EXTEND_DEPLOYMENT_WINDOW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_TIME_TO_ADD NUMBER IN
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 JOB_LISTENER
PROCEDURE RECONNECTRECONNECT 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.
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_CLIENT_IDENTIFIER VARCHAR2 IN
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:
 |
