Minimum Dependent Object Recompilation After Online Table Redefinition

Starting from Oracle 11g, If you add a column to a table, then no need to recompile its dependent objects.

Let us see what happens to a view when we add a column to a table in Oracle 9i:

 C:\Documents and Settings\hnaik>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Dec 21 04:15:41 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table t (n number,v varchar2(10));

Table created.

SQL> insert into t values (1,'H');

1 row created.

SQL> create view tv as select * from t;

View created.

SQL> select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SQL> alter table t add (m number);

Table altered.

SQL> select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       INVALID

SQL>

Now let us see what happens to a view when we add a column to a table in Oracle 11g:

C:\Documents and Settings\hnaik>sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Dec 21 15:54:25 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@orcl11g>create table t (n number,v varchar2(10));

Table created.

SCOTT@orcl11g>insert into t values (1,'H');

1 row created.

SCOTT@orcl11g>create view tv as select * from t;

View created.

SCOTT@orcl11g>select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SCOTT@orcl11g>alter table t add (m number);

Table altered.

SCOTT@orcl11g>select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SCOTT@orcl11g>

Reference:
Oracle® Database New Features Guide 11g Release 1 (11.1)

Invisible Index

Invisible index is a new feature of Oracle 11g. If we create an invisible index Oracle optimizer will not use it. This can be used to test a query without effecting the application. We can also make an index invisible/visible by using ALTER INDEX index_name [visible/invisible].

Syntax:

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index­_name INVISIBLE;
ALTER INDEX index­_name VISIBLE;

Examples:

SYSTEM@orcl11g>show parameter optimizer_use_invisible_indexes 
NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_use_invisible_indexes boolean FALSE



SYSTEM@orcl11g>desc test

Name Null? Type

----------------------------------------------------- -------- ------------

N NUMBER

NAME VARCHAR2(10)



SYSTEM@orcl11g>create index idx1 on test(n) invisible;

Index created.



SYSTEM@orcl11g>select owner,index_name,visibility from dba_indexes

2 where visibility = 'INVISIBLE';



OWNER INDEX_NAME VISIBILIT

------------------------------ ------------------------------ ---------

SYSTEM IDX1 INVISIBLE



SYSTEM@orcl11g>select * from test where n=5;



N NAME

---------- ----------

5 H



Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020



--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEST | 1 | 20 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------



SYSTEM@orcl11g>alter index idx1 visible;

Index altered.



SYSTEM@orcl11g>select * from test where n=5;



N NAME

---------- ----------

5 H



Execution Plan

----------------------------------------------------------

Plan hash value: 1175071277



------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 20 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX1 | 1 | | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------------

Use 1: Adding an index

You think adding an index will improve a query but you are not sure. Just create an invisible index. Test the query and decide.

Use 2: Dropping an index

You want to drop an index but don’t know how it will affect the application. Make that index invisible and analyze the performance. If it is not affecting the performance of the application, you can drop it.

Oradebug

Oradebug utility can be used to trace a user session. To trace a session its OS process ID is required which can be obtained by joining v$session and v$process views as shown below:

SQL> select s.username,S.sid,S.serial#,spid

 2  from v$session s, v$process p

 3  where s.username=p.username

 4  and s.paddr=p.addr

 5  and s.username='HNAIK';



USERNAME                              SID    SERIAL# SPID

------------------------------ ---------- ---------- ------------

HNAIK                                 154          4 5548

Now login to the database as SYSDBA: 

C:\Documents and Settings\hnaik> sqlplus / as sysdba



SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 29 14:37:40 2009



Copyright (c) 1982, 2005, Oracle.  All rights reserved.





Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Start the tracing:

SQL> oradebug setospid 5548

Oracle pid: 20, Windows thread id: 5548, image: ORACLE.EXE (SHAD)



SQL> oradebug event 10046 trace name context forever,level 8;

Statement processed.

Stop the tracing:

SQL> oradebug event 10046 trace name context off;

Following command identifies the trace file that is created….

SQL> oradebug tracefile_name

c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_5548.trc

Oracle Documentation Library

Below page contains documents of all the versions of Oracle Database:

http://www.oracle.com/technology/documentation/index.html

--

Basic Grid Control commands

[oracle@naikh]export OMS_HOME=/u01/oracle/product/oms10g
[oracle@naikh]export AGENT_HOME=/u01/oracle/product/agent10g

Starting Grid Control Services

$AGENT_HOME/bin/emctl start agent
$OMS_HOME/opmn/bin/opmnctl startall


Stopping Grid Control Services

$AGENT_HOME/bin/emctl stop agent
$OMS_HOME/opmn/bin/opmnctl stoptall


Status of the Grid Control Services

$AGENT_HOME/bin/emctl status agent
$OMS_HOME/bin/emctl status oms
$OMS_HOME/opmn/bin/opmnctl status

Rebooting RAC servers

For some maintenance activity, if RAC servers need to be rebooted then the steps are:

1. Check the status of all the services
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

2. Stop the database
[oracle@naikh] srvctl stop database -d orcl

3. Stop nodeapps
[oracle@naikh] srvctl stop nodeapps hostname1
[oracle@naikh] srvctl stop nodeapps hostname2

4. Reboot the servers
Ask System admin to reboot the both the servers

5. Once the server is up check the status of all the services
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

6. Start the nodeapps if not automatically started.
[oracle@naikh] srvctl start nodeapps hostname1
[oracle@naikh] srvctl start nodeapps hostname2

7. Start the database if not automatically started.
[oracle@naikh] srvctl start database -d orcl

8. Make sure all the services are active
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

RMAN Recovery Catalog

This article gives the steps to create the recovery catalog.

1. Identify the database where catalog needs to be created.

Here I am using rcvcat as the catalog database

2. Create the tablespace to store the catalog information.

SQL> CREATE TABLESPACE CATALOG_DATA
DATAFILE '/u01/oradata/rvcvat/catalog_data_01.dbf'
SIZE 500M;

3. Create the user that is going to be the catalog owner

SQL> CREATE USER RMAN
IDENTIFIED BY RMAN
DEFAULT TABLESPACE CATALOG_DATA
TEMPORARY TABLESPACE TEMP;

4. Grant the roles and the privileges

SQL> GRANT RESOURCE TO RMAN;
SQL> GRANT CONNECT TO RMAN;

SQL> GRANT RECOVERY_CATALOG_OWNER TO RMAN;

SQL> ALTER USER RMAN DEFAULT ROLE ALL;
SQL> ALTER USER RMAN QUOTA UNLIMITED ON RMAN_DATA;

5. Create the catalog

[oracle@naikh]$ rman catalog rman/rman@rvccat
RMAN> create catalog;

6. Register the target database

[oracle@naikh]$ export ORACLE_SID=orcl

[oracle@naikh]$ rman target / catalog rman/rman@rvccat

RMAN> register database;