Invisible Indexes in ORACLE 11G

Use CREATE INDEX INVISIBLE command to create invisible indexes on table.

After creating invisible index on given table we have to use ALTER INDEX to make it VISIBLE.

By default INVISIBLE indexes are ignored by optimizer. OPTIMIZER_USE_INVISIBLE_INDEXES parameter need to set to TRUE to allow optimizer to use such indexes.

Let us create a test table and invisible index on it...

SQL> create table invisible_tab (column1 int);
Table created.

SQL> create index inv_idx on invisible_tab (column1) invisible;
Index created.

SQL> select index_name, visibility
  2   from DBA_INDEXES
  3  where index_name = 'INV_IDX';
INDEX_NAME                     VISIBILIT
------------------------------ ---------
INV_IDX                        INVISIBLE
SQL>

Now, let us insert sample data into the test table and observe whether invisible index used or not

SQL> begin
  2      for i in 1..100000 loop
  3          insert into invisible_tab values (i);
  4      end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(*) from invisible_tab;
  COUNT(*)
----------
    100000
SQL> exec dbms_stats.gather_table_stats (USER, 'INVISIBLE_TAB', cascade => TRUE
);
PL/SQL procedure successfully completed.
SQL>

Let us observe the execution plan of the following statement

select * from invisible_tab where column1 = 678;



Now, let us set OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE to use invisible index i.e created

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

SQL> show parameter optimizer_use_invisible_indexes;
NAME                                 TYPE                                   VALUE
------------------------------------ -----------------------------------------
optimizer_use_invisible_indexes      Boolean                  TRUE
SQL>

select * from invisible_tab where column1 = 678;

In place of alter session above, we can also use following command to make use of index by optimizer.

alter index inv_idx visible;

use following command to convert index into invisible state

alter index inv_idx invisible;


The value of compatible parameter should be 11.0.0.0.0 or greater to use this concept

select * from
v$parameter
where name ='compatible'

If the value of compatible parameter is lower than 11.0.0.0.0 then we will get errors as follows

create index emp_idx1 on emp (empno) invisible
ORA-00406: COMPATIBLE parameter needs to be 11.0.0.0.0 or greater
ORA-00722: Feature "ALTER INDEX INVISIBLE, CREATE INDEX INVISIBLE"

 

READ Only Tables in ORACLE 11G

In ORACLE releases before to 11G, we can achieve the read-only concept on table just by granting SELECT privilege to users.

But the owner of the table still have read-write access on the table.

In 11G, We have a flexibility to mark table as READ ONLY.

Lets observe the following scenario:

First, create a test table
create table read_only_test_tab (column1 int);

Now, mark the table as READ ONLY by executing the following command

SQL> alter table read_only_test_tab read only;
Table altered.

SQL> select table_name, read_only from
  2  dba_tables
  3  where table_name = 'READ_ONLY_TEST_TAB';

TABLE_NAME                     READ_ONLY
------------------------------ --------------------------
READ_ONLY_TEST_TAB             YES

SQL>


Now, try executing the DML statements on this table

SQL> insert into read_only_test_tab values(1);
insert into read_only_test_tab values(1)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table
"MAHENDRA"."READ_ONLY_TEST_TAB"

SQL> update read_only_test_tab
  2  set column1 = column1;
update read_only_test_tab
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table
"MAHENDRA"."READ_ONLY_TEST_TAB"
SQL> delete from read_only_test_tab;
delete from read_only_test_tab
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table
"MAHENDRA"."READ_ONLY_TEST_TAB"

SQL>

Now, try with DDL statements on the table

SQL> alter table read_only_test_tab add (column2 int);
alter table read_only_test_tab add (column2 int)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table
"MAHENDRA"."READ_ONLY_TEST_TAB"

SQL> create index read_only_test_tab_idx on read_only_test_tab(column1);
Index created.
From the above , it is confirmed that when the table is in READ ONLY mode we can't perform DML, DDL statements.

Let us convert the table into READ WRITE and then try with DML, DDL statements

SQL> alter table read_only_test_tab read write;
Table altered.

SQL> select table_name, read_only from
  2      dba_tables
  3      where table_name = 'READ_ONLY_TEST_TAB';
TABLE_NAME                     READ_ONLY
------------------------------ --------------------
READ_ONLY_TEST_TAB             NO

SQL> insert into read_only_test_tab values (1);
1 row created.

SQL>
SQL> alter table read_only_test_tab add (column2 int);
Table altered.

The value of compatible parameter should be 11.0.0.0.0 or greater to use this concept

select * from

v$parameter

where name ='compatible'

If the value of compatible parameter is lower than 11.0.0.0.0 then we will get errors as follows

SQL> ALTER TABLE ro_tab READ ONLY;

ALTER TABLE ro_tab READ ONLY

*

ERROR at line 1:

ORA-00406: COMPATIBLE parameter needs to be 11.0.0.0.0 or greater

ORA-00722: Feature "ALTER TABLE READ ONLY, ALTER TABLE READ WRITE"

 

Creating Trigger in ENABLE/DISABLE Mode in ORACLE 11G

Starting from ORACLE 11G, we can create triggers in DISABLE mode. Prior to ORACLE 11G, the triggers we create on table by default would be in ENABLE mode.

First, create a test table

create table enable_disable_test_tab (col1 int);

Now, create table in DISABLE mode as follows:

create trigger enable_disable_test_trg
before insert on
enable_disable_test_tab
for each row
disable
begin
dbms_output.put_line ('From enable_disable_test_trg trigger ');
end;
Now, create table in ENABLE mode as follows:

create trigger enable_disable_test_after_trg
after insert on
enable_disable_test_tab
for each row
enable
begin
dbms_output.put_line ('From enable_disable_test_after_trg trigger ');
end;


Now, lets query the DBA_TRIGGERS table to query the status of triggers.

select trigger_name, trigger_type, status
 from dba_triggers
where trigger_name in ( 'ENABLE_DISABLE_TEST_TRG', 'ENABLE_DISABLE_TEST_AFTER_TRG');

output:
-------


This is how we can create triggers in ENABLE/DISABLE mode.

Use the following command to enable trigger

alter trigger ENABLE_DISABLE_TEST_TRG ENABLE

Use the following command to disable the trigger

alter trigger ENABLE_DISABLE_TEST_TRG DISABLE

For more details, Refer Here

 

COMPOUND Triggers in Oracle 11G

Let see how to use compound triggers in ORACLE 11G onwards...

Before start with compound trigger, first we will go through the existing trigger flow prior to ORACLE 11G.

Before 11G:
---------------

Create a test table and before statement, before row level, after row level, after statement triggers on the test table created.

SQL> create table triggertest_tab (col1 int);
Table created.
SQL> create or replace trigger triggertest_tab_before_stmt
  2  before insert on triggertest_tab
  3  begin
  4  dbms_output.put_line ('Message From before insert stmt level trigger');
  5  end;
  6  /
Trigger created.
SQL> create or replace trigger triggertest_tab_before_row
  2  before insert on triggertest_tab
  3  for each row
  4  begin
  5  dbms_output.put_line ('Message From before insert row level trigger');
  6  end;
  7  /
Trigger created.
SQL> create or replace trigger triggertest_tab_after_row
  2  after insert on triggertest_tab
  3  for each row
  4  begin
  5  dbms_output.put_line ('Message From after insert row level trigger');
  6  end;
  7  /
Trigger created.
SQL> create or replace trigger triggertest_tab_after_stmt
  2  after insert on triggertest_tab
  3  begin
  4  dbms_output.put_line ('Message From after insert stmt level trigger');
  5  end;
  6
  7  /
Trigger created.


Now, try insert a record into test table

SQL> set serveroutput on;

SQL> insert into triggertest_tab values(1);
Message From before insert stmt level trigger
Message From before insert row level trigger
Message From after insert row level trigger
Message From after insert stmt level trigger
1 row created.

This is how the existing functionality in prior to ORACLE 11G. We have to define triggers at various level separately.

From ORACLE 11G Onwards:
Now, From ORACLE 11G onwards, we can use compound trigger functionality to club one or more triggers into single compound trigger.


Lets create test table and write a compound trigger to have before statement, before row, after row, after statement triggers.

SQL> create table compound_test_tab (col1 int);

Table created.

SQL> create or replace trigger compound_trigger
  2  for insert
  3  on compound_test_tab
  4  compound trigger
  5      before statement is
  6          begin
  7              dbms_output.put_line ('Message From before insert stmt level trigger');
  8          end before statement;
  9      before each row is
 10          begin
 11              dbms_output.put_line ('Message From before insert row level trigger');
 12          end before each row;
 13      after each row is
 14          begin
 15              dbms_output.put_line ('Message From after insert row level trigger');
 16          end after each row;
 17      after statement is
 18          begin
 19              dbms_output.put_line ('Message From after insert stmt level trigger');
 20          end after statement;
 21  end compound_trigger;
 22  /
Trigger created.
SQL>
SQL>

Now, try insert into test table

SQL> insert into compound_test_tab values(1);
Message From before insert stmt level trigger
Message From before insert row level trigger
Message From after insert row level trigger
Message From after insert stmt level trigger
1 row created.

SQL>

This is how we can club multiple trigger levels into single compound trigger.

For more details, Refer Here

How to recover a dropped table in ORACLE

How to recover a dropped table in ORACLE

Use flashback table command to recover dropped table from ORACLE.

Lets observe the following example to recover a table:

First create a sample table:

SQL> create table test_recycle (column1 int);
Table created.

Now, lets check the contents of recyclebin

SQL> show recyclebin;

You may find the dropped objects after executing the above command or it may give no output which means none of the objects is dropped after last purge of recyclebin.

Now, drop the table we created

SQL> drop table test_recycle;
Table dropped.

Check the contents of recyclebin again

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST_RECYCLE     BIN$LkAZkkSCRV6VeiHhANUiHg==$0 TABLE        2013-11-24:11:32:58

See now, the table is in recyclebin. Which means we can recover this table using FLASHBACK TABLE command as follows

SQL> flashback table test_recycle to before drop;
Flashback complete.

Now, check whether table is recovered or not

SQL> desc test_recycle;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COLUMN1                                            NUMBER(38)


This is how we can recover table...


ORA-38305: object not in RECYCLE BIN

This error is because we are trying to recover a table/object which is not in recyclebin.

Lets observe the following example:

Create a test table:
SQL> create table test_purge (column1 int);
Table created.
Now, drop the table using following command which will permanently drop the table from database

SQL> drop table test_purge purge;
Table dropped.

View the contents of recyclebin:
SQL> show recyclebin;

Object wont found in recyclebin

Now, try to recover the table using FLASHBACK BEFORE DROP, which will give the error as follows

SQL> flashback table test_purge to before drop;
flashback table test_purge to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN


 

How to Install Oracle Database 11GR2 in Windows7

How to Install Oracle Database 11GR2 in Windows7

Step1: Download the Oracle database from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Step2: In step-1, you are going to download 2 zip files. Make sure you extract both the zip files into same location/folder on Windows.

Step3: Click on setup.exe which starts the database installation

Step4:  Uncheck the checkbox as shown in below screen, if you dont want to receive security alerts


Step5: Ignore the below warning, Click on YES to proceed further.



Step6:  Choose Create and Configure a database option and click next



Step7: Select Desktop Class if you are installing the Oracle on your personal laptop/desktop and click next



Step8: Accept the default paths and SID info as shown in below screen and click next


Step9:Installation do some memory checks and after sometime you could see the below screen


Step10: Click FINISH in above screen and installation starts


Step11: Note that step-10 take time to install the database and after few minutes you will get below window (DBCA) which creates and configures the database



Step-12: Installation completes and the below screen will display


Step13: Click on Password Management to change password of system accounts like sys, system, scott etc....

Step14:After the installation, you can access enterprise managet by using following link
https://localhost:1158/em





Step15: Congratulations, Oracle Database is now ready to use :)

Errors while running the reports 6i, reports 10g from sun solaris


REP-0004: Warning: Unable to open user preference file.
REP-3000: Internal error starting Oracle Toolkit.
REP-3000: Internal error starting Oracle Toolkit.
Xlib: connection to "160.95.49.111:1.0" refused by server
Xlib: Client is not authorized to connect to Server
REP-3000: Internal error starting Oracle Toolkit.


Fix:

Some times xvfb services on sun solaris server were not recongnized when we try to run reports 6i and/or reorts 10g.

We have to restart xvfb services on server to fix this error.