Reference partitioning example

create table orders (
  order_id    number not null,
  order_date  date not null,
  customer_id number not null
 ) tablespace tbs1
partition by range (order_date) (
  partition orders_y2011 values less than (to_date('01-JAN-2012', 'dd-mon-yyyy')) tablespace tbs1,
  partition orders_y2012 values less than (to_date('01-JAN-2013', 'dd-mon-yyyy')) tablespace tbs1,
  partition orders_y2013 values less than (to_date('01-JAN-2014', 'dd-mon-yyyy')) tablespace tbs1
 );
  
alter table orders add constraint orders_pk primary key(order_id) using index tablespace tbs1;

create index orders_ix1 on orders(customer_id) 
tablespace tbs1
local
(
  partition orders_ix1_y2011 tablespace tbs1,
  partition orders_ix1_y2012 tablespace tbs1,
  partition orders_ix1_y2013 tablespace tbs1
);

-- 1. Referential constraint must be defined while creating table.
-- 2. PARTITION BY REFERENCE clause should be used for reference partitioning
--    and it should mention referential constraint name.
create table order_items (
  order_id    number not null,
  product_id  number not null,
  price       number,
  quantity    number,
  constraint order_items_fk foreign key (order_id) references orders
) tablespace tbs1
partition by reference (order_items_fk) (
  partition order_items_y2011 tablespace tbs1,
  partition order_items_y2012 tablespace tbs1,
  partition order_items_y2013 tablespace tbs1
);

alter table order_items add constraint order_items_pk primary key(order_id, product_id) using index tablespace tbs1;

create index order_items_ix1 on order_items(order_id)  
tablespace tbs1 
local
(
  partition order_items_ix1_y2011 tablespace tbs1,
  partition order_items_ix1_y2012 tablespace tbs1,
  partition order_items_ix1_y2013 tablespace tbs1
);
 
-- While adding partition to parent table, we can use 
-- 1. DEPENDENT TABLES clause to specify names of child partitions
-- 2. In UPDATE INDEXES clause, we can give indexes of parent and child tables. 
alter table orders 
  add partition orders_y2014 
     values less than (to_date('01-JAN-2015', 'dd-mon-yyyy')) 
     tablespace tbs1
  dependent tables (
     order_items (partition order_items_y2014 tablespace tbs1) 
     )
  update indexes (
     orders_ix1 (partition orders_ix1_y2014 tablespace tbs1),
     order_items_ix1 (partition order_items_ix1_y2014 tablespace tbs1)
     )
;

-- Dropping parent partition will drop child table partition(s), and corresponding index partitions
alter table orders drop partition orders_y2011 update global indexes;

Decrypting Oracle database DB link password (Versions <= 11.2.0.2)

-- Run as SYS user
set serveroutput on

declare
 db_link_password varchar2(100);
begin

 db_link_password := '0560A31A6EFEC902B9286FFC981F4C9A92F8470D406ADEA670';

 dbms_output.put_line ('Plain password: ' ||
                          -- Convert RAW to varchar2
                          utl_raw.cast_to_varchar2 (

                              dbms_crypto.decrypt (

                               -- from 19th char to end, it is encrypted source
                               substr (db_link_password, 19) ,

                               -- Type of encryption
                               dbms_crypto.DES_CBC_PKCS5 ,

                               -- From 3rd to 16th char, it is key
                               substr (db_link_password, 3, 16)

                              )
                          )
                      );

end;
/

Plain password: Forget12

Dropping Database link in another schema

Oracle database does not allow to drop database link owned by another user. As a DBA user, to drop database link in another schema, here are steps to follow.

1. Create a procedure in another schema in which database link exists
2. The procedure should have statement to drop database link
3. Execute the procedure
4. Drop the procedure

These steps can be written as PL/SQL anonymous block like below. 

declare

          schema_name varchar2(10) := 'MURTY';

     -- db_link_name should be same as value of DB_LINK column in DBA_DB_LINKS view

     db_link_name varchar2(100) := 'remote.xyz.com';

     random_proc_name varchar2(100);

     cnt number;

begin

     schema_name := upper(schema_name);

     db_link_name := upper(db_link_name);

        -- A random procedure to make sure the name of procedure should not conflict with existing procedures

     random_proc_name := schema_name || '.drdl_' || dbms_random.string('U', 20);

     select count(*) into cnt from dba_db_links where owner=schema_name and db_link=db_link_name;

 

           if ( cnt != 1 ) then

          raise_application_error(-20001, 'DB Link does not exist');

     end if;

    

          -- 1. Create a procedure in another schema in which database link exists

          execute immediate

               ' create procedure ' || random_proc_name || ' as ' ||

               ' begin ' ||

               -- 2. The procedure should have statement to drop database link

               ' execute immediate ''drop database link ' || db_link_name ||' ''; ' ||

               ' end;';

 

           begin

          -- 3. Execute the procedure

               execute immediate 'begin ' || random_proc_name || '; end;';

          exception when others then

                    execute immediate 'drop procedure ' || random_proc_name;

                     raise;

          end;

           -- 4. Drop the procedure

           execute immediate 'drop procedure ' || random_proc_name;

end;

/