Helpful SQL Scripts

There may come a time for database developers to perform bulk operations e.g. compile all the stored procedures or rebuild all the indices. This might be required esp. when you are supporting a product and have to upgrade it.

I had to do this with help of oracle XE instance with only a web interface and here is the solution I developed.

Recompiling All Procedures:

begin
declare
cursor c_proc is
select object_name from user_objects where object_type = 'PROCEDURE';
v_sql varchar2(512);

begin
for c_proc_rec in c_proc LOOP
v_sql := 'ALTER PROCEDURE 'c_proc_rec.object_name' COMPILE';
execute immediate v_sql;
END LOOP;

end;
end;

Rebuilding all indexes:
This might be required after inserting thousands of rows as part of product upgrade. Here is the script that I used.

begin
begin
declare
cursor c_idx is
select index_name from user_indexes where index_type = 'NORMAL' and uniqueness = 'NONUNIQUE';
v_sql varchar2(512);

begin
for c_idx_rec in c_idx LOOP
v_sql := 'Alter index 'c_idx_rec.index_name' rebuild';

execute immediate v_sql;
END LOOP;
end;
end;

Hope this helps!

Oracle Invalid Objects

Here is a quick query for Oracle users to identify all invalid objects:

SELECT owner,object_name,object_type
FROM dba_objects WHERE status='INVALID'

Tech Jokes

Here they go... :-) Hope you like 'em!
----------
1. An SQL query walks into a bar and sees two tables. It walks up to them and says "Can I join you?" Tables say, "Sure, if you have the key?"

2. A Session Bean walks into a bar and asks for a Jack Daniels on the rocks. Bartender says, "I'm afraid I can't serve you any alcohol", and the Session Bean says, "why not? Are you discriminating against Session Beans?" The bartender replies "well, look at the state you're in".