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