Some Oracle Query Tricks

----Get factorial of number using query:
----------------------------------------------->
with t as (select level as l from dual connect by level <=&value)
select EXP (SUM (LN (l))) MULTIPLY from t


----Get ORACLE_HOME path using query:
----------------------------------------------->
select substr(file_spec, 1, instr(file_spec, '\', -1, 2) -1) ORACLE_HOME from dba_libraries where library_name = 'DBMS_SUMADV_LIB';

Duplicate Index / Redundant Index Oracle

what is duplicate index ?
This is when table has multiple indexes defined on the same columns. The indexes may have with different names.
for example :
first index is created on columns : index1(a,b,c)
second index is created on columns : index2(a,b)
so as above "a" and "b" columns are mutual of each other and place in same order, therefore second index is duplicate of first:
note: index3(a,b) and index4(b,a), both have different definition.

How to identify duplicate index (oracle):
source::::
http://www.dba-oracle.com/t_detecting_duplicate_indexes.htm
select /*+ rule */
   a.table_owner,
   a.table_name,
   a.index_owner,
   a.index_name,
   column_name_list,
   column_name_list_dup,
   dup duplicate_indexes,
   i.uniqueness,
   i.partitioned,
   i.leaf_blocks,
   i.distinct_keys,
   i.num_rows,
   i.clustering_factor
from
  (
   select
      table_owner,
      table_name,
      index_owner,
      index_name,
      column_name_list_dup,
      dup,
      max(dup) OVER
       (partition by table_owner, table_name, index_name) dup_mx
   from
      (
       select
          table_owner,
          table_name,
          index_owner,
          index_name,
          substr(SYS_CONNECT_BY_PATH(column_name, ','),2) 
          column_name_list_dup,
          dup
       from
          (
          select
            index_owner,
            index_name,
            table_owner,
            table_name,
            column_name,
            count(1) OVER
             (partition by
                 index_owner,
                 index_name) cnt,
             ROW_NUMBER () OVER
               (partition by
                  index_owner,
                  index_name
                order by column_position) as seq,
             count(1) OVER
               (partition by
                  table_owner,
                  table_name,
                  column_name,
                  column_position) as dup
   from
      sys.dba_ind_columns
   where
      index_owner not in ('SYS', 'SYSTEM'))
where
   dup!=1
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
)) a,
(
select
   table_owner,
   table_name,
   index_owner,
   index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list
from
(
select index_owner, index_name, table_owner, table_name, column_name,
count(1) OVER ( partition by index_owner, index_name) cnt,
ROW_NUMBER () OVER ( partition by index_owner, index_name order by column_position) as seq
from sys.dba_ind_columns
where index_owner not in ('SYS', 'SYSTEM'))
where seq=cnt
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
) b, dba_indexes i
where
    a.dup=a.dup_mx
and a.index_owner=b.index_owner
and a.index_name=b.index_name
and a.index_owner=i.owner
and a.index_name=i.index_name
order by
   a.table_owner, a.table_name, column_name_list_dup;
  
  
  
  
  
For a Particular Schema:
  
  
select /*+ rule */
 a.table_name, a.index_name, column_name_list, column_name_list_dup, dup duplicate_indexes,
 i.uniqueness, i.partitioned, i.leaf_blocks, i.distinct_keys, i.num_rows, i.clustering_factor
from
  (select
  table_name, index_name,
  column_name_list_dup, dup,
  max(dup) OVER (partition by table_name, index_name) dup_mx
   from
      (select
   table_name, index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2)  column_name_list_dup, dup
       from
   (select
    index_name, table_name, column_name,
    count(1) OVER (partition by index_name) cnt,
    ROW_NUMBER () OVER (partition by index_name order by column_position) as seq,
    count(1) OVER (partition by table_name, column_name, column_position) as dup
   from
    user_ind_columns
   )
  where
   dup!=1
   start with seq=1
   connect by prior seq+1=seq
   and prior index_name=index_name
  )) a,
(
select
   table_name, index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list
from
 ( select index_name, table_name, column_name,
  count(1) OVER ( partition by index_name) cnt,
  ROW_NUMBER () OVER ( partition by index_name order by column_position) as seq
  from user_ind_columns
 )
where seq=cnt
start with seq=1
connect by prior seq+1=seq
and prior index_name=index_name
) b, user_indexes i
where
    a.dup=a.dup_mx
and a.index_name=b.index_name
and a.index_name=i.index_name
order by
   a.table_name, column_name_list_dup;  

Sort Characters (of datatype char, varchar) in order of month name

When we generally do order by .. when it is string or varchar/nvarchar datatype then oracle or any db server will do order in way, that occurance of charater is priority.

for example :
A, B, C, D, E

In case if integer numbers are saved in Varchar database type then it will do the order as below:
1, 10, 11, 2, 22, 3, 4, 5, 55


In case if integer numbers are saved in Int database type then it will do the order as below:
1, 2, 3, 4, 5, 10, 11, 22, 55

Create table having datatype varchar2 or any of yours choice:

create table month_sort (mon1 varchar2(3));
insert into month_sort values ('MAR');
insert into month_sort values ('JUN');
insert into month_sort values ('MAY');
insert into month_sort values ('FEB');
insert into month_sort values ('JAN');
insert into month_sort values ('DEC');
insert into month_sort values ('SEP');
insert into month_sort values ('OCT');

::: this is a character sorting
select * from month_sort order by 1;

::: this is a character sorting in order of month name
select mon1 ,row_number() over (partition by  to_date(mon1,'mon') order by  to_date(mon1,'mon')) from month_sort;

Execute macro for each sheet in a workbook

Put this code in macro, it will fill "test" in each sheet of workbook. 

Sub cycle()

    Dim wk As Worksheet
    For Each wk In ThisWorkbook.Worksheets
    wk.Activate
    With wk.Range("I5")
    .FormulaR1C1 = "test"
    End With
    Next wk

End Sub

Export and Import tablespace on same database

export the single tablespace
expdp system/orcl directory=DATA_PUMP_DIR dumpfile=testtablespace.dmp logfile=testtablespace.log tablespaces=users


export the single tablespace 
expdp system/orcl directory=DATA_PUMP_DIR dumpfile=testtablespace.dmp logfile=testtablespace.log tablespaces=users,test


export tablespace with-out index (10x faster if we include index)**
expdp system/orcl directory=DATA_PUMP_DIR dumpfile=testtablespace.dmp logfile=testtablespace.log tablespaces=users,test exclude=indexes*

Drop the all users related to same tablespace.
If you try to import the dump, without dropping the users, then impdp fails with error
"ORA-39151. "All dependent metadata and data will be skipped due to table_exists_action of skip"




solution to above error "table_exists_action=replace"
impdp system/orcl directory=DATA_PUMP_DIR dumpfile=testtablespace.dmp logfile=testtablespace.log tablespaces=users table_exists_action=replace
or
impdp system/orcl directory=DATA_PUMP_DIR dumpfile=testtablespace.dmp logfile=testtablespace.log tablespaces=users,test  table_exists_action=replace
or
impdp system/orcl directory=DATA_PUMP_DIR dumpfile=testtablespace.dmp logfile=testtablespace.log tablespaces=users,test exclude=indexes  table_exists_action=replace


 
Note - ** soon script will provided to collect all inde.
       * collect all index to some source, either you will lost your indexes.