Monitoring any table operation from your own schema in Oracle database SQL> conn mir/mir123; Connected. SQL> select table_name from user_tables; TABLE_NAME ———— TEST1 SQL> conn sys/sys as sysdba Connected. SQL> alter table mir.test1 monitoring; Table… Read more..

How to delete duplicate rows from the table in oracle database Now create new table & insert some records init with duplicate: SQL> create table test_del_dup_rows(eno number(20),name varchar2(20)); Table created. SQL> insert into test_del_dup_rows values(1,’mir’); 1 row created.

Change the table with Read only in oracle database by using trigger In this below scenario, Create a table & insert some data in it before restrict then create a trigger to restrict any insert/update/delete statement on table SQL> conn mir/mir123; Connected SQL> create table test_read_only(eno… Read more..

To set up Autotrace by using the PLUSTRACE ROLE in Oracle Database Follow the step by step procedure as shown below: SQL> conn sys/sys as sysdba Connected. SQL> set autotrace traceonly; SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled SP2-0611:… Read more..

Reorganizing or Shrink table/index in Oracle 11gR2 DB A Simple demonstration to perform shrinking the object in Oracle Database 11gR2 [oracle@testdb ~]$ !sq SQL> conn mir/**** Connected. SQL> create table shrink_test as select ‘Oracle_shrink_method’ as product, mod(rownum,5) as… Read more..

Manually Corrupting Oracle Data Block & Recover using BLOCKRECOVER The below-given example is for the testing & learning purpose only, don’t use the production databases. I have manually corrupted the data block for my own testing ENV & verified Create the separate tablespace & the new schema/user to perform… Read more..