28 February 2011

DBA Tidbits - How to run a SQL statement

So last week, I had a developer ask me to run a sql statement in Production to create a package/stored procedure.  Well, the first time it didn't work.

Me:  Hi there Developer Person!  Um, sorry, I am getting errors when I try and run your sql.  Are you SURE I should be running this against (schema/database)?

Developer: {Sounds of rustling and typing.} OH WAIT. No, that shouldn't be run against (old schema/old database) that should be run against  (schema/database)!  Sorreeee!!

Me: OkayDokee! Thanks!  (Whereupon I run the sql again and this time get another error... object doesn't exist!... what?)

I go and look at the code.  The Developer Person has prefaced all the tables and objects with the schema name until he got to the bottom.  Where he missed one object.  Since I am logged in as me, and not as the schema owner, this piece remains an unknown.  Hmm. 

Questions:
Do I change the code?  (ANSWER: NO NO NO NO)

Do I email the developer back and ask him to change the code? (ANSWER: YES!)

Do I ask my Sr. DBAs lurking around in cubicles about this? (ANSWER: YES YES YES)

What is the appropriate way to run code?

Log in as the schema owner; run the code.  Best practice is have the schema prefaced before the objects! Why? Well, if you are logged in as anyone other than the schema owner, then you might end up creating objects in your own dba schema.  And then get busy and forget to drop them and then have all kinds of stuff with your name all over everywhere.  That would be bad housekeeping.

However, having said that don't forget about SOX.  (Sarbanes-Oxley) In some shops if a SOX application exists, then one SHOULD run as the dba and not the schema owner.  Because in a lot of reports, that schema owner might be excluded from an audit.  And then the code is excluded from an audit. That would be bad.

Second tidbit of the day.  Before you run any sql, save it into a text file somewhere you can run it. Maybe, if you are highly organized, in a nice folder somewhere labeled in an organizational way.  Then, run the following:

SQL> spool on
SQL> spool C:\MyOutput.txt
SQL> C:\HighlyOrganizedFolderName\mysqlfileIwanttorun.sql
SQL>spool off

This assumes that you are running sqlplus off of your desktop.  If you are logged into a unix box and run a sql statement from there, you are going to get a nice textfile that is named C:HighlyOrganizedFolderNamemysqlfileIwanttorun.sql
It's not going to end up on your C: Drive. Why not? Because you are logged in to a unix host!

25 February 2011

RTFM - Schemas and Users

Scene:  Me in my cube, arms crossed over chest, client in corner looking at all my O'Reilly DBA Books, Sr. DBA in front of me...

Me: My client didn't want any of the stuff of the schema in there, so I saved the ddl of the schema owner and did a drop user {name here} cascade; but now I can't see the schema...

Sr. DBA: Yes, that's because you dropped everything. Now you have to run the ddl that you saved for all the objects.

Me: Um.... I saved the ddl for the schema owner...and then ran that...

Sr. DBA: YOU MEAN YOU DIDN'T SAVE ANYTHING ELSE ?

Me: Er... I reran the ddl for the schema owner... doesn't that make the ... um... exoskeleton of the schema come back? Like, all the stuff but... empty?

Sr. DBA: [silence]

Me: Uh, I guess not.

Sr. DBA: [thought balloon = omg, how did she get here]

LUCKY FOR ME, the client really didn't want any of the objects. And now I have a better understanding of schemas and users. And clients. And Sr DBAs.



I feel better after reading this.