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;

/

 

Generating DDL of a schema using datapump

Here is the method to get entire DDL of a schema in SQL using datapump. In this example, we are trying to get DDL of a schema called MYSCHEMA.

Export entire schema metadata into a dump file. It will be created in directory MYDIR. To this work, first there should be a directory object MYDIR in database.

expdp murty@mydb schemas=MYSCHEMA directory=MYDIR dumpfile=schema_metadata.dmp content=METADATA_ONLY

Now we can extract SQL from the dump file like below.

impdp murty@mydb directory=MYDIR dumpfile=schema_metadata.dmp sqlfile=schema_metadata.sql exclude=STATISTICS

Estimate savings of disk space by shrinking a table

This post shows how to estimate disk space savings of shrinking a table. Here we are using Segment space advisor task.

-- Create a table and make it fragmented

SQL> create table tab1 (a number, b varchar2(10));

Table created.

-- Filling table with 1 million records

begin

  for i in 1 .. 1000000

  loop

     insert into tab1 values(i, 'AAAAAAAAAA');

  end loop;

  commit;

end;

/

SQL> select count(*) from tab1;

  COUNT(*)

----------

   1000000

-- Check the space usage in MB

SQL> select bytes/(1024*1024) from user_segments where segment_name='TAB1';

BYTES/(1024*1024)

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

               23

-- Let us fragment it by deleting 90% of records evenly across table

SQL> delete from tab1 where mod(a, 10) != 1;

900000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tab1;

  COUNT(*)

----------

    100000

-- We see that it still using same space

SQL> select bytes/(1024*1024) from user_segments where segment_name='TAB1';

BYTES/(1024*1024)

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

               23

Here is the script to estimate space savings if we do shrink space.

declare

 tname varchar2(100);

 oid number;

 tid number;

begin

tname:='CHECK_SHRINKABLE_1';

dbms_advisor.create_task (

  advisor_name     => 'Segment Advisor',

  task_id          => tid,

  task_name        => tname);

dbms_advisor.create_object (

  task_name        => tname,

  object_type      => 'TABLE',

  attr1            => 'MURTY',

  attr2            => 'TAB1',

  attr3            => NULL,

  attr4            => NULL,

  attr5            => NULL,

  object_id        => oid);

  dbms_advisor.set_task_parameter(

  task_name        => tname,

  parameter        => 'recommend_all',

  value            => 'TRUE');

dbms_advisor.execute_task(tname);

end;

/

We can use the view USER_ADVISOR_RECOMMENDATIONS view to check result.

SQL> set linesize 110

SQL> select BENEFIT_TYPE from USER_ADVISOR_RECOMMENDATIONS where TASK_NAME='CHECK_SHRINKABLE_1';

BENEFIT_TYPE

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

Enable row movement of the table MURTY.TAB1 and perform shrink, estimated savings is 17512778 bytes.

Let us shrink the table and check the space usage.

SQL> alter table tab1 enable row movement;

Table altered.

SQL> alter table tab1 shrink space;

Table altered.

SQL> select bytes/(1024*1024) from user_segments where segment_name='TAB1';

BYTES/(1024*1024)

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

           2.4375