How to Drop All the Indexes in Oracle Table

Hi Guy's,

We can drop all the indexes in a single procedure.


SQL> select table_name,index_name,status from dba_indexes where table_name='SHSHTDTA';

TABLE_NAME     INDEX_NAME                     STATUS
---------- --------------------------------------------------------------------
SHSHTDTA         SHSHTDTA_PK                      VALID
SHSHTDTA         IX_PANIC                                VALID
SHSHTDTA         IX_SHDATE                             VALID
SHSHTDTA       SHSHTDTA_INDX                     VALID


SQL>  BEGIN
      FOR ind IN
      (SELECT index_name FROM user_indexes WHERE table_name='SHSHTDTA' AND index_name NOT IN
      (SELECT unique index_name FROM user_constraints WHERE table_name='SHSHTDTA' AND index_name IS          NOT NULL))
      LOOP
      execute immediate 'DROP INDEX '||ind.index_name;
      END LOOP;
      END;
        /

PL/SQL procedure successfully completed.


SQL> select table_name,index_name,status from dba_indexes where table_name='SHSHTDTA';
no rows selected




Happy Reading...

How to Disable All Constraints in Oracle

Hi Guy's,

Today we will see how to disable all the constraints in oracle table.


SQL> select table_name,constraint_name, status from user_constraints where table_name='SHSHTDTA'

TABLE_NAME   CONSTRAINT_NAME       STATUS
--------------- --------------- --------------------------------------------
SHSHTDTA          SYS_C0053639              ENABLED
SHSHTDTA          SYS_C0053640              ENABLED
SHSHTDTA          SYS_C0053641              ENABLED
SHSHTDTA          SYS_C0053642              ENABLED
SHSHTDTA          SYS_C0053643              ENABLED
SHSHTDTA          SYS_C0053644              ENABLED
SHSHTDTA          SYS_C0053645              ENABLED
SHSHTDTA          SYS_C0053646              ENABLED

8 rows selected.



SQL>   BEGIN
       FOR c IN
       (SELECT c.owner, c.table_name, c.constraint_name FROM user_constraints c, user_tables t WHERE                     c.table_name = t.table_name
       AND c.status = 'ENABLED' AND  t.table_name='EMP' ORDER BY c.constraint_type DESC)
       LOOP
       dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' ||                     c.constraint_name);
       END LOOP;
       END;
    /

PL/SQL procedure successfully completed.




SQL> select constraint_name,status from dba_constraints where table_name='SHSHTDTA';

CONSTRAINT_NAME          STATUS
--------------- --------------------------------------
SYS_C0041058                    DISABLED
SYS_C0041057                    DISABLED
SYS_C0041056                    DISABLED
SYS_C0041055                    DISABLED
SYS_C0041054                    DISABLED
SYS_C0041053                    DISABLED
SYS_C0041052                    DISABLED
SYS_C0041051                    DISABLED
SHSHTDTA_TMP_PK        DISABLED

9 rows selected.



Happy Reading...

How to Delete Duplicate Rows in Oracle

 Hi Guy's,

Today we will see how to eliminate the duplicate records from a table.When tables are designed initially on test environment in some scenarios the primary key is not define, so in these cases there's is a chance of rows duplication.

SQL> select count(*) from table_name;

  COUNT(*)

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

   4814821

SQL> select 

kthscd,

kttrnn,

kttype,

ktdrcd,

ktqty from schema.table_name group by

kthscd,

kttrnn,

kttype,

ktdrcd,

ktqty having count(*)>1;

  

6 rows selected.

 >>>>>>>> We have 6 rows Duplicate here  <<<<<<<<<

  

SQL> delete from schema.table_name where rowid not in (select min(rowid) from schema.table_name group by

kthscd,

kttrnn,

kttype,

ktdrcd,

ktqty);

6 rows deleted.

SQL> select count(*) from table_name

  COUNT(*)

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

   4814815

Happy Reading...

Top Ten Sizes of Tables and Indexes in Oracle

Hi Guy's,

I found very nice scripts from 'DON BURLESON' website which gives the top ten sizes of Tables and Indexes.



SQL> select * from (select owner,segment_name,bytes/1024/1024 meg from dba_segments where segment_type='TABLE' order by bytes/1024/1024 desc) where rownum <= 10;


SQL> select * from (select owner,segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' order by bytes/1024/1024 desc) where rownum <= 10;




Happy Reading...

Oracle Alert-log and Trace Files Locations

SQL> show parameter background_dump_dest

NAME                                          TYPE                     VALUE
------------------------------------ ----------- ------------------------------------------------------
background_dump_dest                 string      c:\app\Moin\diag\rdbms\kkprod\kkprod\trace


SQL> select u_dump.value || '/' || db_name.value || '_ora_' || v$process.spid ||
     nvl2(v$process.traceid, '_' || v$process.traceid, null )||'.trc' "Trace File"
     from v$parameter u_dump cross join v$parameter db_name cross join v$process
     join v$session on v$process.addr = v$session.paddr where u_dump.name = 'user_dump_dest'
     and db_name.name = 'db_name' and v$session.audsid=sys_context('userenv','sessionid');

Trace File
--------------------------------------------------------------------------------
c:\app\Moin\diag\rdbms\kkprod\kkprod\trace/kkprod_ora_4244.trc
c:\app\Moin\diag\rdbms\kkprod\kkprod\trace/kkprod_ora_1280.trc



Thank's For Learning....

ORA-00205 Restoring Controlfile from RMAN When Autobackup of controlfile is On

>> An ORA-00205 error occurs when the system cannot find the Controlfile.
>> This i tested on my test environment i just removed controlfile and startup the database.
>> Autobackup of controlfile is configured in RMAN.


SQL> startup

ORACLE instance started.
Total System Global Area    7.4826E+10 bytes
Fixed Size                            2263008 bytes
Variable Size                       7.3283E+10 bytes
Database Buffers                 1342177280 bytes
Redo Buffers                       199049216 bytes
ORA-00205: error in identifying control file, check alert log for more info


>> Check Autobackup is ON or Not.

RMAN> show all;
RMAN configuration parameters for database with db_unique_name KKPROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\MOIN\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFKKPROD.ORA'; # default

RMAN> restore controlfile from autobackup;

Starting restore at 22-JAN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
recovery area destination: +DATA1
database name (or database unique name) used for search: KKPROD
channel ORA_DISK_1: AUTOBACKUP +data1/KKPROD/AUTOBACKUP/2018_01_22/s_966079326.377.966079329 found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP +data1/KKPROD/AUTOBACKUP/2018_01_22/s_966079326.377.966079329
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATA/kkprod/controlfile/current.262.962995555
output file name=+DATA1/kkprod/controlfile/current.256.966094519
Finished restore at 22-JAN-18



>> Now mount the database, Controlfile is read in MOUNT state.

RMAN> sql 'alter database mount';

>> Now start to recover database, Recover is the process of applying the changes to the database till point of failure.
   these changes are recorded in online redolog and archivelogs .


RMAN>  recover database;

Starting recover at 22-JAN-18
Starting implicit crosscheck backup at 22-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
Finished implicit crosscheck backup at 22-JAN-18
Starting implicit crosscheck copy at 22-JAN-18
using channel ORA_DISK_1
Crosschecked 5 objects
Finished implicit crosscheck copy at 22-JAN-18
searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +data1/KKPROD/AUTOBACKUP/2018_01_22/s_966079326.377.966079329
File Name: +data1/KKPROD/AUTOBACKUP/2018_01_21/s_966011357.381.966011359
File Name: +data1/KKPROD/AUTOBACKUP/2018_01_18/s_965739076.383.965739079
File Name: +data1/KKPROD/AUTOBACKUP/2018_01_02/s_964349888.503.964349893
File Name: +data1/KKPROD/ARCHIVELOG/2018_01_22/thread_1_seq_1.376.966083591
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file +DATA1/kkprod/archivelog/2018_01_22/thread_1_seq_1.376.966083591
archived log for thread 1 with sequence 2 is already on disk as file +DATA/kkprod/onlinelog/group_2.260.963052729
archived log file name=+DATA1/kkprod/archivelog/2018_01_22/thread_1_seq_1.376.966083591 thread=1 sequence=1
archived log file name=+DATA/kkprod/onlinelog/group_2.260.963052729 thread=1 sequence=2
media recovery complete, elapsed time: 00:00:01
Finished recover at 22-JAN-18


RMAN> sql 'alter database open resetlogs';


>> Opening database with resetlogs because database will not apply an archivedlog to a
     datafile unless the RESETLOGS SCN and Timestamp matches.

   * Oracle creates new redologs after resetlogs issued and also reset logsequence number to '1'.
   * After resetlogs Oracle updates all current datafiles,redologs and all archivelogs
     with NEW-SCN and Time-stamp.
 

RMAN> exit

SQL> select instance_name,status from v$instance;

       INSTANCE_NAME       STATUS
      ---------------- -----------------------------
           kkprod                          OPEN


SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/kkprod/controlfile/current.262.962995555
+DATA1/kkprod/controlfile/current.256.966094519



Enjoy Reading...