Letting the Data Dictionary do some of the leg work

Let's say that you have a column that is on several tables and you need to modify the length of that column. One method of doing that would be to figure out which tables have the column and manually write a shit pot load of alter statements to do what needs to be done. Now, let's suppose you are a shade or two brighter than Corky from Life Goes On. You are going to let the data dictionary do the work for you. Oracle comes with a plethora of tables, views, synonyms and what not that is chock fucking full of information. They are broken up based on item type and user access type. If you are curious, check public synonyms in your schema or if you have read access to SYS, check out the views that look like one of the following:

ALL_%
DBA_%
USER_%

Where the % is some string like TABLES, PROCEDURES, etc. Let's focus on USER_% first. You connect to Oracle as a user and the SYS views that begin with USER_ are the ones that you own. They exist in your schema - the one you logged in with anyway. So, USER_TABLES will list the tables you own, USER_PROCEDURES will list the procedures you own, and so on and so forth. Whereas, the SYS views with ALL_ are the ones that you have access to read, write, execute, etc. The DBA's and a lucky few with elevated privileges will have access to DBA_ views and can see everything in the database. Therefore, DBA_TABLES will list every stinking table in the entire goddamn database.

Now, here is an amazing little trick that will save you a Honey Boo Boo's mom sized chunk of work. You can write queries against those tables where the results are SQL commands. Let's say you have 3 procedures entitled MYPROC1, MYPROC2, and MYPROC3. Now let's suppose you want to compile all of them. You would write select 'ALTER PROCEDURE ' || procedure_name || ' COMPILE;'  as alterallmyprocs from USER_PROCEDURES; and execute that bad boy. Your results will look like:

ALTERALLMYPROCS
ALTER PROCEDURE MYPROC1 COMPILE;
ALTER PROCEDURE MYPROC2 COMPILE;
ALTER PROCEDURE MYPROC3 COMPILE;

So, you can paste those results into a sql window, execute as script and Oracle will compile all of your procedures. Pretty handy right? Well, imagine that you have 25 schemas with similar structures and there are 100 tables in each. Every one of those tables has a column called LAST_UPDATER that tracks via the application which user last updated the record. And now, let's suppose that the USERNAME column is getting enlarged so all of these LAST_UPDATER columns would need to expand to contain the enlarged USERNAME. Ignore for the moment that ideally you would be using the corresponding user id instead of the actual username. This is for instructional purposes, capisce? Would you really want to write the 2500 required statements by hand? Well, maybe you are a masochist. Or maybe you are a contractor. Or maybe you like to sandbag and appear industrious while actually slacking. In those cases, and for you Corky's out there, continue to work hard, not smart. I'll be using sql writing sql to get the same result in a fraction of the time. Just for fun, here is the sql writing sql statement to perform the above task:
select 'alter table ' || owner || '.' || table_name || ' modify ( ' || column_name || ' varchar2(256) );' it from all_tab_cols where column_name in ('USERNAME', 'LAST_UPDATER') and owner like '%MYAPP%';

I was recently tasked with some Year End reports. I needed to run a query in every production database for a specific app or set of apps and I used this technique to meet the narrow deadline. Of course the underlying requirement is that I am able to log into a schema created for me but given read access everywhere for purposes just like this.

Of course, with great power comes great responsibility. Use this for the good of all mankind. Do not fuck with people using this. Do not write something like select 'delete from ' || owner || '.' || table_name || ';' it from all_tables where owner = 'ThatDickThatAggravatesTheFuckOutOfMeAndFoolishlyGaveMePrivsOnHisTables';
Or even worse: select 'delete from ' || owner || '.' || table_name || ' where mod(' || column_name || ', 13) = 0;' from all_cons_columns where constraint_name like '%PK%' and column_name like '%ID%' and owner = 'ThatDickThatAggravatesTheFuckOutOfMeAndFoolishlyGaveMePrivsOnHisTables';
The former is going to delete everything from tables that do not get 'Child Record Found' errors but the latter is wicked nefarious. It will delete every row whose primary key id is evenly divisible by 13 and there are no child record conflicts. Sure, I could work out how to eliminate those conflicts but I don't want to make it that easy for you to really fuck with someone.

All kidding aside, properly used, this can save a ton of time and heartache. Start small, pay attention to syntax, and test, test, test!