When is the next post ?

Work has been keeping me very busy ... Recently I was going thru Oracle performance tuning guide. Also found some interesting problems on the job. I am sketching the content. will post soon.

New work laptop

I just started loving the new laptop Dell Latitude E6400. Its looks are awesome !
Will wait for a Mac Book option too. When will the day come ?

Recompiling to validate the INVALID Objects : Oracle

On a DB schema, there are many database objects like tables, views, procedures, packages etc.

Here is a case study...
- You want to alter a table for some reason. As a result of running ALTER statement over the table, all dependend objects like Views, Packages gets invalidated irrespective of the fact that change affects/not affects the dependend objects.
- Eg: Assume a table ..

SQL> create table test_foo
          ( colA varchar2(2)
          , colB varchar2(2)
          , colC varchar2(2)
         );

SQL> create view v_test_foo
           as select colA, colB, colC from test_foo;

SQL> select object_name, object_type, status 
           from user_objects 
           where object_name in ('TEST_FOO', 'V_TEST_FOO');

----------------------------------------
object_name       object_type      Status
-----------------------------------------
V_TEST_FOO    VIEW               VALID
TEST_FOO         TABLE             VALID


SQL> alter table test_foo add colNew varchar(2);


SQL> select object_name, object_type, status 
          from user_objects 
          where object_name in ('TEST_FOO', 'V_TEST_FOO');

----------------------------------------
object_name       object_type      Status
-----------------------------------------
V_TEST_FOO    VIEW               INVALID
TEST_FOO         TABLE             VALID

How do you recompile these ?

I use...
SQL> exec dbms_utility.compile_schema('USERNAME');
-- Substitute an appropriate USERNAME above.

So, how does it behave in Oracle 9i and Oracle 10g ?

The behavior is different in both versions of the Oracle.
Oracle 10g: As you expect, all relevant invalid objects are recompiled and becomes Valid.

Oracle 9i: You should be cautitious.. as VIEWs are not validated.
You have to explicitly compile VIEWs as follows.
ALTER VIEW v_test_foo COMPILE;

Script to Find User Information and More ; Oracle

As a DBA, You might see a lots of users in the DB server.

There are times when you need to find out info about the roles/system pivileges assigned to an user. Following link contain some useful queries to dig information out of the data dictionary tables.

http://www.oracle.com/technology/oramag/code/tips2006/010906.html

Creating PLAN table in Oracle (9i)

If you need to set AUTOTRACE feature ON, you need PLAN tables created for sure. So, how do you create plan tables.

Which script ?
The script for creating Plan table is in $ORACLE_HOME\rdbms\admin\utlxplan.sql.

Where to create ?
Create the PLAN_TABLE by using utlxplan.sql in the schema where you need to enable trace and run the query.

What else is required ?
Also, create the ROLE named PLUSTRACE using a script in sqlplus\admin directory.

Grant this role to the user for whom you have created PLAN_TABLE.

Thats it ! login, run
SQL> set autotrace trace
You should be seeing the query plans.

Evolutionary Database Design - Pramod Sadalage's presentation during Q-Con

http://www.infoq.com/presentations/refactoring-databases

Splendid coverage of Evolutionary Database design with demonstration by Pramod Sadalage, the Agile DB practices guru.