Installing and Configuring cx_Oracle on CentOS 6

cx_Oracle module of python is used to connect to Oracle from Python. This works only with python 2.7. Here are the steps that can be used to install python2.7 on CentOS 6 and cx_Oracle module.

Installing python 2.7 as alternate python installation on CentOS 6

wget "https://www.python.org/ftp/python/2.7.13/Python-2.7.13.tgz"
tar -zxvf Python-2.7.13.tgz 
cd Python-2.7.13
./configure 
make
make altinstall 
which python2.7 # To verify installation path

Installing Oracle Instant Client

Download Oracle Instant Client from this link. We need instant client and instant client SDK. To install Oracle Instant client just unzip both the zip files to /opt or any other direcory

Create symbolic link libclntsh.so so it points to correct version.

cd /opt/instantclient_11_2/
ln -s libclntsh.so.11.1 libclntsh.so

Set environment variables

export ORACLE_HOME=/opt/instantclient_11_2
export LD_LIBRARY_PATH=/opt/instantclient_11_2

Installing pip for python 2.7

pip is a package management system used to install python libraries. Here are the steps to install.

wget https://bootstrap.pypa.io/get-pip.py
python2.7 get-pip.py

Installing cx_Oracle module using pip

pip install cx_Oracle

Sample program to test cx_Oracle

import cx_Oracle

connection = cx_Oracle.connect ("username/password@hostname/service")

cursor = connection.cursor ()
cursor.execute ("SELECT 1 a, 'AA' b, sysdate c FROM dual union select 2 a, 'BB' b, sysdate + 1 c from dual")
result = cursor.fetchall ()

for row in result:
    print row[0], row[1], row[2]

cursor.close ()
connection.close ()

Analyze AWS billing detailed line items file, and getting bill details using script

We can configure itemized bill of AWS to be uploaded S3. It can be done from "Billing Management Console" of AWS -> "Preferences", and turning on option "Receive Billing Reports". Now we can do analysis of "Detailed billing report with resources and tags", and get bill amount in INR, and know which resources are getting billed more etc.

Here is the script that provides analysis of itemized bill that's already uploaded to S3.

# This script uses command line utility q - Text as Data
# Download Page: https://harelba.github.io/q/

# This script expects aws - CLI already configured on the system

# Hard coded values first
account_id=xxxxxxxxx #AWS account ID
tax=15  # Service tax + Swacch Bharath + Krishi Kalyan
bucket_name="xxxxxx" #Name of S3 bucket where billing reports are uploaded automatically
month=`date +"%Y-%m"` #Current month

#month=2017-02 #Needed if bill of another month is needed

# Get currency conversion rate from Google
usd=`wget -q -O - "http://www.google.com/finance/converter?a=1&from=USD&to=INR" \
      | grep "currency_converter_result" \
      | sed 's/<[^>]*>//g' \
      | cut -f4 -d ' '`

echo "Conversion Rate: $usd, Tax=$tax% "
echo " "

aws_url="s3://$bucket_name/$account_id-aws-billing-detailed-line-items-with-resources-and-tags-AISPL-$month.csv.zip"

rm -f bill_line_items*

aws s3 cp $aws_url bill_line_items.csv.zip --quiet
unzip -p bill_line_items.csv.zip > bill_line_items.csv

cat bill_line_items.csv | q -T -b -O -H -d "," -w all \
"select \
    \"user:Name\" as Resource_Name, \
    UsageType,\
    strftime('%Y-%m-%d %H:%M', datetime(UsageStartDate,'+5.5 hours')) as Usage_Start_Time, \
    round(Cost*$usd*(1+$tax/100.0),2) as \"Cost+Tax(INR)\"  \
from -  \
where cost<>0 and Rate <> '' \
order by UsageEndDate, Resource_Name"

echo " "

cat bill_line_items.csv | q -T -b -H -d "," -w all \
"select 'Total cost in INR including tax: ' || \
 sum( round(Cost*$usd*(1+$tax/100.0),2)) \
 from - where cost<>0 and Rate <> '' "

Sample output

Conversion Rate: 66.2050, Tax=15% 
 
Resource_Name   UsageType                   Usage_Start_Time    Cost+Tax(INR)
node1       APS3-BoxUsage:t2.large      2017-03-03 16:30    9.06
Hadoop      APS3-BoxUsage:t2.large      2017-03-06 17:30    9.06
Hadoop      APS3-BoxUsage:t2.large      2017-03-06 18:30    9.06
.....
Cloudera1   APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
gw          APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.09
hadoop      APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.23
node1       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
node2       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
node3       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
node4       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
photos_os   APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.09
 
Total cost in INR including tax: 297.81

Working with AWS EC2 instances from aws command line tool

Command to list instances using JMESPath query

aws ec2 describe-instances --query "Reservations[*].Instances[*].[InstanceId, Tags[?Key=='Name'].Value|[0], State.Name, PrivateIpAddress, InstanceType]" --output=table

-------------------------------------------------------------------------------
|                              DescribeInstances                              |
+----------------------+------------+----------+-----------------+------------+
|  i-xxxxxxxxxxxxxxxxx |  node0     |  running |  172.31.11.20   |  t2.micro  |
|  i-yyyyyyyyyyyyyyyyy |  node1     |  stopped |  172.31.11.21   |  t2.large  |
+----------------------+------------+----------+-----------------+------------+

Command to start instances

aws ec2 start-instances --instance-ids i-xxxxxxxxxxxxxxxxx i-yyyyyyyyyyyyyyyyy

Command to stop instances

aws ec2 stop-instances --instance-ids i-xxxxxxxxxxxxxxxxx i-yyyyyyyyyyyyyyyyy

Conversion between UTF-16, UTF-8 encoded files on Linux

1. Introduction

The encoding used by Windows for Unicode is UTF-16, to be specific, UTF-16LE (Little Endian). Linux uses UTF-8 to encode Unicode. A file encoded with Unicode can optionally contain a Byte Order Mark(BOM) which is a special magic number at the start of file. Byte Order Mark(BOM) is optional for UTF-8, but mandatory for UTF-16 as per Unicode standard. So, Linux does not use BOM for Unicode files as it uses UTF-8. But Windows applications look for BOM in Unicode encoded file as they use UTF-16.

So in summary, Windows uses UTF-16LE with BOM, and Linux uses UTF-8 without BOM.

To verify type of encoding used for a file, we can use file command on Linux.

$ file Unicode_Windows.txt 
Unicode_Windows.txt: Little-endian UTF-16 Unicode text, with CR line terminators

We can see more details using hexdump also,

$ hexdump -C Unicode_Windows.txt 
00000000  ff fe 24 0c 46 0c 32 0c  41 0c 17 0c 41 0c 0d 00  |..$.F.2.A...A...|
00000010  0a 00                                             |..|
00000012

ff fe is BOM for UTF-16LE, and we can see end of line character as 0d 00 (Carriage Return CR) and 0a 00 (Line Feed LF)

2. Converting from UTF-16 to UTF-8

Above file is created in Windows. To convert it to Linux encoding, there are multiple ways.

2a. Using iconv

$ iconv -f UTF-16LE -t UTF-8 Unicode_Windows.txt > Unicode_Linux1.txt

Let us check this file.

$ file Unicode_Linux1.txt 
Unicode_Linux1.txt: UTF-8 Unicode (with BOM) text, with CRLF line terminators

$ hexdump -C Unicode_Linux1.txt 
00000000  ef bb bf e0 b0 a4 e0 b1  86 e0 b0 b2 e0 b1 81 e0  |................|
00000010  b0 97 e0 b1 81 0d 0a                              |.......|
00000017

This converts to UTF-8, but keeps BOM at the begining of file (ef bb bf). Also we have CR (0d) and LF (0a) characters for end of line. So, convert it to UTF-8 without BOM and CR, here is the command.

$ iconv -f UTF-16LE -t UTF-8 Unicode_Windows.txt | sed 1s/^.//g | sed s/"\r$"//g > Unicode_Linux1.txt

We can verify it using below commands.

$ file Unicode_Linux1.txt 
Unicode_Linux1.txt: UTF-8 Unicode text

$ hexdump -C Unicode_Linux1.txt 
00000000  e0 b0 a4 e0 b1 86 e0 b0  b2 e0 b1 81 e0 b0 97 e0  |................|
00000010  b1 81 0a                                          |...|
00000013

2b. Using dos2unix command

We can also use dos2unix command, which converts file from UTF-16LE to UTF-8, and also removes BOM and CR characters. Here is the example,

$ dos2unix -n Unicode_Windows.txt Unicode_Linux2.txt 
dos2unix: converting file Unicode_Windows.txt to file Unicode_Linux2.txt in Unix format ...

$ file Unicode_Linux2.txt 
Unicode_Linux2.txt: UTF-8 Unicode text

$ hexdump -C Unicode_Linux2.txt 
00000000  e0 b0 a4 e0 b1 86 e0 b0  b2 e0 b1 81 e0 b0 97 e0  |................|
00000010  b1 81 0a                                          |...|
00000013

3. Converting from UTF-8 to UTF-16

Now to convert files from UTF-8 to UTF-16LE on Linux, there is no direct way. The command unix2dos coverts from UTF-8 to UTF-8 only just by adding CR character. Also, unix2dos does not add BOM by default. So, we have to force it with -m option.

$ unix2dos -m -n Unicode_Linux1.txt Unicode_Windows1.txt 
unix2dos: converting file Unicode_Linux1.txt to file Unicode_Windows1.txt in DOS format ...

$ file Unicode_Windows1.txt 
Unicode_Windows1.txt: UTF-8 Unicode (with BOM) text, with CRLF line terminators

$ hexdump -C Unicode_Windows1.txt 
00000000  ef bb bf e0 b0 a4 e0 b1  86 e0 b0 b2 e0 b1 81 e0  |................|
00000010  b0 97 e0 b1 81 0d 0a                              |.......|
00000017

To covert it to UTF-16LE, we have to use iconv command after using unix2dos.

$ iconv -f UTF-8 -t UTF-16LE Unicode_Windows1.txt > Unicode_Windows2.txt

$ file Unicode_Windows2.txt 
Unicode_Windows2.txt: Little-endian UTF-16 Unicode text, with CR line terminators

$ hexdump -C Unicode_Windows2.txt
00000000  ff fe 24 0c 46 0c 32 0c  41 0c 17 0c 41 0c 0d 00  |..$.F.2.A...A...|
00000010  0a 00                                             |..|
00000012

Instead of using unix2dos, we can directly use sed command to add BOM and CR like below and covert to UTF-16.

$ sed 1s/^/"\xef\xbb\xbf"/g Unicode_Linux1.txt | sed s/$/"\r"/g | iconv -f UTF-8 -t UTF-16LE > Unicode_Windows3.txt 
 
$ file Unicode_Windows3.txt 
Unicode_Windows3.txt: Little-endian UTF-16 Unicode text, with CR line terminators

$ hexdump -C Unicode_Windows3.txt 
00000000  ff fe 24 0c 46 0c 32 0c  41 0c 17 0c 41 0c 0d 00  |..$.F.2.A...A...|
00000010  0a 00                                             |..|
00000012

Fixing execution plans using SQL plan baselines

This post shows how to use SQL plan baselines to fix execution plan of a query. This can be used as a replacement for stored outlines.

For this demonstration we create 2 tables each with a row and collect statistics on them.

create  table tab1 (a number, b varchar2(10));
insert into tab1 values(10, 'AA');
create index tab1_i1 on tab1(a);

create  table tab2 (a number, c varchar2(10));
create index tab2_i1 on tab2(a);
insert into tab2 values(10, 'BBB');

exec dbms_stats.gather_table_stats (ownname=>'MURTY', tabname=>'TAB1', estimate_percent=>10, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', cascade=>true);
exec dbms_stats.gather_table_stats (ownname=>'MURTY', tabname=>'TAB2', estimate_percent=>10, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', cascade=>true);

Here is the query for which we will change execution plan using SQL plan baselines.

select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

The execution plan shows nested loop join

SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3684952675

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    13 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |         |     1 |    13 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | TAB1    |     1 |     6 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | TAB2_I1 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("TAB1"."A"="TAB2"."A")

17 rows selected.

If the parameter optimizer_capture_sql_plan_baselines is set to true, after multiple executions of the query, it will create a baseline automatically.

SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3684952675

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    13 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |         |     1 |    13 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | TAB1    |     1 |     6 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | TAB2_I1 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("TAB1"."A"="TAB2"."A")

Note
-----
   - SQL plan baseline "SQL_PLAN_2ppbwajdruq05fc48919a" used for this statement

21 rows selected.

If the parameter optimizer_capture_sql_plan_baselines is set to false, we can create a SQL plan baseline manually using SQL_ID of statement.

SQL> select sql_id, sql_text from v$sql where sql_text like 'select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a';

SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
bqn8dqudd4ajf
select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a

set serveroutput on
declare
  i pls_integer;
BEGIN
  i := dbms_spm.load_plans_from_cursor_cache(sql_id => 'bqn8dqudd4ajf');
  dbms_output.put_line('Plans Loaded: ' || i);
END;
/
Plans Loaded: 1

PL/SQL procedure successfully completed.

Details of SQL Plan baselines can be viewed from dictionary view dba_sql_plan_baselines

SQL> select SQL_HANDLE, SQL_TEXT, PLAN_NAME from dba_sql_plan_baselines where PLAN_NAME='SQL_PLAN_2ppbwajdruq05fc48919a';

SQL_HANDLE                     SQL_TEXT                                                                         PLAN_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SQL_2ad57c545b7d5805           select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a                SQL_PLAN_2ppbwajdruq05fc48919a

And more details can be get like below using sql_handle

SQL>  select * from table(dbms_xplan.display_sql_plan_baseline( sql_handle=>'SQL_2ad57c545b7d5805', format=>'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_2ad57c545b7d5805
SQL text: select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_2ppbwajdruq05fc48919a         Plan id: 4232614298
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3684952675

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   2 |   NESTED LOOPS               |         |
|   3 |    TABLE ACCESS FULL         | TAB1    |
|   4 |    INDEX RANGE SCAN          | TAB2_I1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |
------------------------------------------------

23 rows selected.

Now we will try to force sort merge join for the same SQL using hint USE_MERGE

SQL> explain plan for select /*+ USE_MERGE(tab1 tab2) */ tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1351227119

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |     1 |    13 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1    |     1 |     6 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | TAB1_I1 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |     1 |     7 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | TAB2    |     1 |     7 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("TAB1"."A"="TAB2"."A")
       filter("TAB1"."A"="TAB2"."A")

18 rows selected.

Execute once to get details in v$sql_plan, and get SQL_ID

SQL> select /*+ USE_MERGE(tab1 tab2) */ tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

         A B          C
---------- ---------- ----------
        10 AA         BBB
        
SQL> select distinct SQL_ID from v$sql_plan where PLAN_HASH_VALUE=1351227119;

SQL_ID
-------------
fqjpqc2t0byr0

Now we will add the plan that uses sort merge join to SQL plan baseline. So, without using any hint, the execution of query uses sort merge join. Here we are using sql_handle of baseline created for original query, but sql_id and plan_hash_value of hinted query.

set serveroutput on
declare
  i pls_integer;
BEGIN
  i := dbms_spm.load_plans_from_cursor_cache(sql_id => 'fqjpqc2t0byr0', plan_hash_value => 1351227119, sql_handle => 'SQL_2ad57c545b7d5805');
  dbms_output.put_line('Plans Loaded: ' || i);
END;
/
Plans Loaded: 1

PL/SQL procedure successfully completed.

Baseline of original query now shows 2 plans.

SQL> select * from table(dbms_xplan.display_sql_plan_baseline( sql_handle=>'SQL_2ad57c545b7d5805', format=>'basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_2ad57c545b7d5805
SQL text: select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_2ppbwajdruq05e3fe5496         Plan id: 3825095830
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1351227119

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  MERGE JOIN                  |         |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1    |
|   3 |    INDEX FULL SCAN           | TAB1_I1 |
|   4 |   SORT JOIN                  |         |
|   5 |    TABLE ACCESS FULL         | TAB2    |
------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_2ppbwajdruq05fc48919a         Plan id: 4232614298
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE 
--------------------------------------------------------------------------------

Plan hash value: 3684952675

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   2 |   NESTED LOOPS               |         |
|   3 |    TABLE ACCESS FULL         | TAB1    |
|   4 |    INDEX RANGE SCAN          | TAB2_I1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |
------------------------------------------------

41 rows selected.

But still we see nested loop join plan only for the query.

SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3684952675

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    13 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |         |     1 |    13 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | TAB1    |     1 |     6 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | TAB2_I1 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("TAB1"."A"="TAB2"."A")

Note
-----
   - SQL plan baseline "SQL_PLAN_2ppbwajdruq05fc48919a" used for this statement

21 rows selected.

We will make sort-merge join plan as fixed, so it always uses that fixed execution plan.

declare
  i  pls_integer;
begin
  i := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_2ad57c545b7d5805', plan_name=>'SQL_PLAN_2ppbwajdruq05e3fe5496', attribute_name=>'FIXED', attribute_value=>'YES');
  dbms_output.put_line('Plans altered: ' || i);
end;
/
Plans altered: 1

PL/SQL procedure successfully completed.

Details of baseline now show that sort-merge plan as fixed.

SQL> select * from table(dbms_xplan.display_sql_plan_baseline( sql_handle=>'SQL_2ad57c545b7d5805', format=>'basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_2ad57c545b7d5805
SQL text: select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_2ppbwajdruq05e3fe5496         Plan id: 3825095830
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1351227119

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  MERGE JOIN                  |         |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1    |
|   3 |    INDEX FULL SCAN           | TAB1_I1 |
|   4 |   SORT JOIN                  |         |
|   5 |    TABLE ACCESS FULL         | TAB2    |
------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_2ppbwajdruq05fc48919a         Plan id: 4232614298
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3684952675

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   2 |   NESTED LOOPS               |         |
|   3 |    TABLE ACCESS FULL         | TAB1    |
|   4 |    INDEX RANGE SCAN          | TAB2_I1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |
------------------------------------------------

41 rows selected.

So, we can get execution plan with sort-merge without any hints.

SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
Plan hash value: 1351227119

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |     1 |    13 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1    |     1 |     6 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | TAB1_I1 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |     1 |     7 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | TAB2    |     1 |     7 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("TAB1"."A"="TAB2"."A")
       filter("TAB1"."A"="TAB2"."A")

Note
-----
   - SQL plan baseline "SQL_PLAN_2ppbwajdruq05e3fe5496" used for this statement

22 rows selected.

If we do not like to use plans from SQL Plan baseline, we can set optimizer_use_sql_plan_baselines parameter as false. Also, if we want to drop baseline, we can do it like below.

declare
  i  pls_integer;
begin
  i :=  dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_2ad57c545b7d5805',plan_name=>NULL);
  dbms_output.put_line('Plans removed: ' || i);
end;
/
Plans removed: 2

PL/SQL procedure successfully completed.

Working with snapshot clones of containers stored on Btrfs filesystem

1. To clone a container, we use lxc-clone command. The option -B btrfs creates a Btrfs volume which later can be used for snapshot clones. Snapshot clones are similar to linked clones of Virtualbox.

# lxc-clone -B btrfs c1 c2

2. We can use -s option to create a snapshot volume.

# lxc-clone -s -B btrfs  c2 c3

Here are some useful Btrfs commands to get more information about underlying volumes of Btrfs.

1. Listing subvolumes

# btrfs subvolume list /vm
ID 257 gen 187 top level 5 path vm
ID 260 gen 168 top level 257 path c2/rootfs
ID 261 gen 168 top level 257 path c3/rootfs

2. Getting details of a subvolume

# btrfs subvolume show /vm/c2/rootfs
/vm/c2/rootfs
    Name:                  rootfs
    uuid:                  68628679-d0ae-724a-be01-5ba74e2c00b1
    Parent uuid:           -
    Creation time:         2015-11-24 17:34:33
    Object ID:             260
    Generation (Gen):      168
    Gen at creation:       161
    Parent:                257
    Top Level:             257
    Flags:                 -
    Snapshot(s):
                           vm/c3/rootfs

3. Enable quotas and see disk usage of subvolumes

# btrfs quota enable /vm

# btrfs qgroup show  /vm
qgroupid rfer      excl      
-------- ----      ----      
0/5      16384     16384     
0/257    827707392 827707392 
0/260    825425920 7421952   
0/261    825425920 7421952   
0/263    825425920 7421952

Here rfer column shows total bytes of data referred by subvolume, and excl column shows bytes of data exclusive for the volume that is not shared with other volumes.

Working with LXC containers

1. We use lxc-create command to create a new container. But it installs OS in container from the CentOS repositories available on Internet. But, to use a local repository, we can set repo environment variable for this.

# export repo="http://127.0.0.1/centos7_1503"

2. Create a container with below command, here c1 is container name.

# lxc-create -t centos -n c1

3. Commands to stop, start, and attach to console of container are

# lxc-stop -n c1
# lxc-start -n c1
# lxc-attach -n c1

4. When we stop container using lxc-stop command, it send SIGPWR signal to container. But, CentsOS 7 does not handle SIGPWR correctly to poweroff. So, here is the fix we have execute in container OS.

[root@c1 ~]# cd /usr/lib/systemd/system
[root@c1 ~]# ln -s poweroff.target sigpwr.target

5. LXC container creation script for CentOS does Minimal Install. To have a good working set of packages, configure yum and install Base group of packages.

Add following lines to file /etc/yum.repos.d/local.repo

[local]
name=local_centos7_1503
baseurl=http://192.168.2.1/centos7_1503
gpgcheck=0

Install Base group

[root@c1 ~]# yum groupinstall Base

Installing LXC from source code on a CentOS 7 host, and configuring

1. Before beginning to install LXC, the pre-requisite package libcap-devel should be installed. This assumes Yum configured on host already.

# yum install libcap-devel

2. Unzip LXC source code.

# tar -zxvf lxc-1.1.5.tar.gz
# cd lxc-1.1.5

3. Install with following commands. By default, LXC will be installed in /usr/local directory. So, we have to mention correct directory names by specifying options for ./configure command.

# ./autogen.sh
# ./configure --enable-capabilities --prefix=/usr --sysconfdir=/etc --localstatedir=/var
# make
# make install

4. Add following configuration information in /etc/sysconfig/lxc-net (new file) to configure networking for LXC host.

LXC_BRIDGE="lxcbr0"
USE_LXC_BRIDGE="true"

LXC_ADDR="192.168.2.1"
LXC_NETMASK="255.255.255.0"
LXC_DHCP_RANGE="192.168.2.70,192.168.2.99"

5. Add following line in /root/.bash_profile and /etc/init.d/lxc-net files to make LXC libraries available for startup script and environment.

# export LD_LIBRARY_PATH=/usr/lib

6. Start the service lxc-net and make it autostart during system boot.

# service lxc-net start
# chkconfig lxc-net on

7. Verify if lxcbr0 interface is showing up in ifconfig output.

# ifconfig
....
lxcbr0: flags=4163  mtu 1500
        inet 192.168.2.1  netmask 255.255.255.0  broadcast 0.0.0.0
....

8. Add following line in /etc/lxc/lxc.conf (new file) to specify where all containers' root file systems should be stored.

lxc.lxcpath = /vm

Filtering inner (null-supplying) table in outer join

Let us consider below tables to demonstrate filtering inner table in outer join

SQL> select * from t1;

         A          B
---------- ----------
         1         10
         2         20
         3         30

3 rows selected.

SQL> select * from t2;

         B          C
---------- ----------
        20        200
        30        300
        40        400

3 rows selected.

This is regular outer join where t1 is outer (row preserving) table and t2 is inner (null supplying) table.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         2         20         20        200
         3         30         30        300
         1         10

3 rows selected.

Filter predicate in WHERE clause: It filters from result like below, after performing join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b where t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300

1 row selected.

Filter predicate in join condition: It filters rows from inner (null supplying) table t2, before performing join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b and t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300
         2         20
         1         10

3 rows selected.

Here are the equivalent SQLs in Oracle's dialect.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1, t2 where t1.b = t2.b(+) and t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300

1 row selected.

Observe (+) in filter predicate of t2 below, which actually filters rows before join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1, t2 where t1.b = t2.b(+) and t2.c(+)=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300
         2         20
         1         10

3 rows selected.

Unzip multi part zip archive on Linux

This works with zip command version 3.0 or above (available in RHEL 6 onwards). Here is an example that unzips multi part zip archive of an Informatica software.

# ls -l dac_win_11g_infa_linux_64bit_951*
-rw-r--r-- 1 root root 2097152000 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.z01
-rw-r--r-- 1 root root 2097152000 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.z02
-rw-r--r-- 1 root root 2097152000 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.z03
-rw-r--r-- 1 root root 1060733887 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.zip

Note: there is a hyphen before and after 's' in below command
# zip -s- dac_win_11g_infa_linux_64bit_951.zip --out inf2.zip
 copying: 951HF2_Client_Installer_win32-x86.zip
 copying: 951HF2_Server_Installer_linux-x64.tar
 copying: DAC11gInstaller.zip
 copying: Infa951Docs.zip
 copying: Oracle_All_OS_Prod.key

# unzip inf2.zip
Archive:  inf2.zip
 extracting: 951HF2_Client_Installer_win32-x86.zip  
  inflating: 951HF2_Server_Installer_linux-x64.tar  
 extracting: DAC11gInstaller.zip     
 extracting: Infa951Docs.zip         
  inflating: Oracle_All_OS_Prod.key 

Getting all users and roles that can access objects in a schema

To list all users and roles who can access objects in a schema, we can use following query. This will be useful when there is a need of migrating a schema from one database to another.

with
users_roles as(
        (select grantee, granted_role from dba_role_privs) union
        (select username, username from dba_users) union
        (select role, role from dba_roles)
),
direct_grantees as (
        select distinct grantee 
        from dba_tab_privs 
        where owner in ('SCHEMA_USER')
),
all_grantees as (
        select distinct grantee
        from users_roles
        start with 
                granted_role in (select * from direct_grantees)
        connect by nocycle prior grantee = granted_role
)
select grantee 
from all_grantees 
-- Filter as needed
-- For example, get only users not roles
where grantee in (select username from dba_users);

Automated spool file name generation

There was requirement recently, where spool file of SQLPlus has to be generated with an automatic name that consists of database name with timestamp (for example, db001_2014-12-26_04-54-13.log). Here is the script that generates spool file name dynamically.

set termout off
set feedback off
undefine spoolfile
column spoolfile new_value spoolfile noprint
select sys_context('userenv', 'db_name') || '_' || to_char(sysdate, 'YYYY-MM-DD_HH24-MI-SS') || '.log' as spoolfile from dual;
set termout on
set feedback on

set echo on
spool &spoolfile

select sysdate from dual;

spool off

Scripts to start and stop Oracle Business Intelligence (OBIEE) on Linux

Tested for OBIEE version: 11.1.1.7, and Linux version: OEL 6

Environment variables:

export MW_HOME=/u01/bi/mw_home
export DOMAIN_HOME=$MW_HOME/user_projects/domains/bifoundation_domain
export WL_HOME=$MW_HOME/wlserver_10.3
export ORACLE_INSTANCE=$MW_HOME/instances/instance1
export PATH=$WL_HOME/server/bin:$DOMAIN_HOME/bin:$ORACLE_INSTANCE/bin:$PATH
export ORACLE_HOME=$MW_HOME/Oracle_BI1

The scripts asks username and password for weblogic while starting/stopping. We can store them in below files so they will not be asked.
$DOMAIN_HOME/servers/AdminServer/security/boot.properties
$DOMAIN_HOME/servers/bi_server1/security/boot.properties

Contents to add in the above mentioned files:

username=weblogic
password=xxxxx

Though we are storing passwords in clear text, they will be encrypted automatically after weblogic started.

Start script:

 
export MW_HOME=/u01/bi/mw_home
export DOMAIN_HOME=$MW_HOME/user_projects/domains/bifoundation_domain
export WL_HOME=$MW_HOME/wlserver_10.3
export ORACLE_INSTANCE=$MW_HOME/instances/instance1
export PATH=$WL_HOME/server/bin:$DOMAIN_HOME/bin:$ORACLE_INSTANCE/bin:$PATH
export BI_LOG_DIR=/u01/bi/logdir
export ORACLE_HOME=$MW_HOME/Oracle_BI1

#################################################################
date

echo "Starting Weblogic"

nohup sh $DOMAIN_HOME/bin/startWebLogic.sh >> $BI_LOG_DIR/wls_start.log &
echo "Log: $BI_LOG_DIR/wls_start.log"

c=0
msg="<Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>"

while [ $c -eq 0 ]
do
 sleep 1
 c=`tail -1 $BI_LOG_DIR/wls_start.log | grep "$msg"  | wc -l`
done

echo "Weblogic started"

#################################################################
date 

echo "Starting Node manager"

nohup sh  $WL_HOME/server/bin/startNodeManager.sh >> $BI_LOG_DIR/startNodeManager.log &
echo "Log: $BI_LOG_DIR/startNodeManager.log"

c=0
msg="INFO: Secure socket listener started on port"

while [ $c -eq 0 ]
do
 sleep 1
 c=`tail -1 $BI_LOG_DIR/startNodeManager.log | grep "$msg"  | wc -l`
done

echo "Node manager started"

#################################################################
date

echo "Starting Managed server"

nohup sh $DOMAIN_HOME/bin/startManagedWebLogic.sh bi_server1 >> $BI_LOG_DIR/start_bi_server1.log &
echo "Log: $BI_LOG_DIR/start_bi_server1.log"

c=0
msg="<Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>"

while [ $c -eq 0 ]
do
 sleep 1
 c=`tail -1 $BI_LOG_DIR/start_bi_server1.log | grep "$msg"  | wc -l`
done

echo "Managed server started"

#################################################################
date

echo "Starting OPMN"

$ORACLE_INSTANCE/bin/opmnctl startall
opmnctl status

echo "OPMN started"

#################################################################

Stop script:

export MW_HOME=/u01/bi/mw_home
export DOMAIN_HOME=$MW_HOME/user_projects/domains/bifoundation_domain
export WL_HOME=$MW_HOME/wlserver_10.3
export ORACLE_INSTANCE=$MW_HOME/instances/instance1
export PATH=$WL_HOME/server/bin:$DOMAIN_HOME/bin:$ORACLE_INSTANCE/bin:$PATH
export BI_LOG_DIR=/u01/bi/logdir
export ORACLE_HOME=/u01/bi/mw_home/Oracle_BI1

$ORACLE_INSTANCE/bin/opmnctl stopall
$DOMAIN_HOME/bin/stopManagedWebLogic.sh bi_server1 
$DOMAIN_HOME/bin/stopWebLogic.sh 

ps -ef | grep weblogic.NodeManager | grep -v grep

p=`ps -ef | grep weblogic.NodeManager | grep -v grep | tr -s ' ' |  cut -f 2 -d ' '`


if [ -n "$p" ] 
then
 echo "Killing pid $p"
 kill -9 $p
fi

Writing INTERVAL expression of DBMS_JOB for exactly timed schedules

On heavily loaded database with lot of jobs scheduled, sometimes we need to time jobs to run at exactly specific intervals in a day. Examples
1. Running a job at every 30 minutes, and timing exactly at 00:10, 00:40, 1:10, 1:40...
2. Running a job at every 6 hours, and timing exactly at 3:00, 9:00, 15:00 ...

Even if a specific instance of job runs at an random time, the next run should fall into timed schedule. It means, for a job that runs at every 6 hours starting with 3:00 (3:00, 9:00, 15:00..), if it's run at 4:30, next run should be at scheduled time 9:00 (not at 4:30+6 i.e 10:30).

Here is how we can derive interval property of a job to get next scheduled date and time.

 
date_time_of_next_scheduled_run = today_date + time_of_next_scheduled_run

time_of_next_scheduled_run = time_of_last_scheduled_run + interval_between_runs

time_of_last_scheduled_run = time_of_first_scheduled_run + (number_of_times_run_completed_today * interval_between_runs) 

number_of_times_run_completed_today = floor((present_time_of_day - time_of_first_scheduled_run)/interval_between_runs)

By substituting all the above, we can get an expression for interval

 
date_time_of_next_scheduled_run =   today_date 
                                  + time_of_first_scheduled_run 
                                  + floor(
                                          (present_time_of_day - time_of_first_scheduled_run)
                                          /interval_between_runs
                                         ) 
                                       * interval_between_runs 
                                   + interval_between_runs

For example, a schedule for every 30 minutes, and timing at 00:10, 00:40, 1:10, 1:40...,

 
today_date = trunc(sysdate)
time_of_first_scheduled_run (in seconds) = 00:10 = 10*60
interval_between_runs (in seconds) = 30*60
present_time_of_day (in seconds) = to_char(d,'SSSSS')

date_time_of_next_scheduled_run = 
trunc(sysdate) +                             
  (   10*60                                  
    + floor(                                 
            (to_char(d,'SSSSS')-(10*60))     
            /(30*60)                         
            )                                
         *(30*60)                            
     + (30*60)                               
   )/(24*60*60)                              
   

We can test if this working by sample PL/SQL program which prints next scheduled time for every minute.

 
declare 
  d date; 
begin 
  for i in 0 .. 150
  loop 
    d := to_date('2014-05-15 00:00', 'YYYY-MM-DD HH24:MI') + i/(24*60); 
    dbms_output.put(to_char(d, 'YYYY-MM-DD HH24:MI') || ' - '); 
    
    dbms_output.put_line( to_char(
          trunc(d) + ( 10*60 + floor( (to_char(d,'SSSSS')-(10*60)) /(30*60) ) *(30*60) + (30*60) )/(24*60*60)  
     , 'YYYY-MM-DD HH24:MI')); 
    
  end loop; 
end;
/​

2014-05-15 00:00 - 2014-05-15 00:10
2014-05-15 00:01 - 2014-05-15 00:10
...
2014-05-15 00:09 - 2014-05-15 00:10
2014-05-15 00:10 - 2014-05-15 00:40
2014-05-15 00:11 - 2014-05-15 00:40
...
2014-05-15 00:39 - 2014-05-15 00:40
2014-05-15 00:40 - 2014-05-15 01:10
2014-05-15 00:41 - 2014-05-15 01:10
...
2014-05-15 01:09 - 2014-05-15 01:10
2014-05-15 01:10 - 2014-05-15 01:40
2014-05-15 01:11 - 2014-05-15 01:40
...

Other examples of interval expression

Schedule for every 6 hours starting with 3:00 (3:00, 9:00, 15:00..)

trunc(sysdate) + ( 3*60*60 + floor( (to_char(sysdate,'SSSSS')-(3*60*60)) /(6*60*60) ) *(6*60*60) + (6*60*60) )/(24*60*60)

Schedule for every 1 hour starting with 0:00 (0:00, 1:00, 2:00, 3:00..)

trunc(sysdate) + (  floor( (to_char(sysdate,'SSSSS')) /(1*60*60) ) *(1*60*60) + (1*60*60) )/(24*60*60)

My style (CSS) for blogs

Here is sample HTML code with CSS inside, which I am planning to use as template for future blog posts. This is initial one, and may be enhanced as needed.

<html>

<head>
<style type="text/css">

pre {
 white-space: pre-wrap;
 font-family: Ubuntu Mono, Courier New, monospace;
 color: #000000;
 background-color: #dddddd;
 border-style:dashed;
 border-width:thin;
 margin-top:1px;margin-bottom:25px;margin-right:50px;margin-left:25px;
 }

body {
 font-family: Droid Sans, Verdana, Sans-Serif;
 color: #000000;
 background-color: #ffffff;
 line-height:125%
 }

</style>
</head>

<body>

<p> This is a sample text paragraph 1. </p>

<p> This is a sample text paragraph 2. </p>

<pre>
This is a sample code line 1
  This is a sample code line 2 with indentation
</pre>

<p> This is a sample text paragraph 3. </p>

</body>

</html>

Loading LOB data using SQL Loader

When there is LOB data at client side, SQL loader can be used to load into Oracle tables. Here are the steps to do it.

Create table with LOB column(s), if it is not already there.

create table tab1
( 
  id   number(5),
  text varchar2(10),
  dt   date,
  doc  clob
);

Here is the CSV file with data. Instead of LOB data, there will be respective file names that contain LOB data.

$ cat data.csv 
1,AAA,2014-05-01,doc1.txt
2,BBB,2014-05-02,doc2.txt

$ cat doc1.txt 
Text of doc1

$ cat doc2.txt 
Text of doc2

The control file of SQL Loader looks like below. There will be FILLER column for file names.

$ cat load.ctl 
LOAD DATA 
INFILE 'data.csv'
   APPEND INTO TABLE tab1
   FIELDS TERMINATED BY ','
   (
    id        CHAR(5),
    text      CHAR(10),
    dt        DATE "YYYY-MM-DD" ":dt",
    file_name FILLER CHAR(100),
    doc       LOBFILE(file_name) TERMINATED BY EOF
    )

The output on screen looks like below when sqlldr command is executed.

$ sqlldr userid=murty/xxxx@mm1 control=load.ctl 

SQL*Loader: Release 12.1.0.1.0 - Production on Tue May 13 10:13:26 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 2

Table TAB1:
  2 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.

We can verify if the data is loaded successfully

SQL> select * from tab1;

 ID TEXT       DT
---------- ---------- ---------
DOC
--------------------------------------------------------------------------------
  1 AAA       01-MAY-14
Text of doc1

  2 BBB       02-MAY-14
Text of doc2

The same procedure can be used for BLOB data also.

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;

/