Friday, October 16, 2015


There might be a situation where executing some DDL in pluggable database may cause the following error:

ORA-65040: operation not allowed from within a pluggable database

This error could occur if a tablespace is being dropped from within PDB and this tablespace is a former default tablespace having some of the system objects. Even system objects cannot be moved with simple alter statements from within PDBs.

So in order to move these objects from within PDBs, you should be using procedure dbms_pdb.exec_as_oracle_script which is undocumented so far.

For example:

exec dbms_pdb.exec_as_oracle_script('alter table . move tablespace ');

From My Oracle Support, Doc ID 1943303.1 lists:

--   This procedure enables execution of certain restricted statements (most DDLs) on metadata-linked objects, from within a PDB.

Monday, October 12, 2015

Got Published in AUSOUG's Foresight Online Spring 2015

AUSOUG's Foresight Online Spring 2015 Edition is the premier publication by Australian Oracle User Group.

Following are highlights of this edition:

  • President's Message
  • DBA Article: Automated Testing of Oracle BPM Suite 12c Processes with SOAP UI - Peter Kemp, State Revenue Office, Victoria
  • DBA Article: Best Practices for Oracle on Pure Storage
  • Apps Article: Performance Review Data Capture - Brad Sayer, More4Apps
  • DBA / Dev Article: Database Developers – Feeling Left Out of Agile? - D Nowrood, Dell Software
  • Apps Article:  Cost-effective alternative to Oracle Discoverer and BI Suite - Wilhelm Hamman, Excel4apps
  • DBA Article: DBA101 - characterset corruption - Paul Guerin, HP
  • Quick Tips 1: Five Reasons to Upgrade to APEX 5.0 - Scott Wesley, Sage Computing Services
  • Quick Tips 2: Last Successful login time in 12c - Fahd Mirza Chughtai, The Pythian Group

Sharding in Oracle 12c Database

Sharding for Oracle DBAs is still pretty much an alien or pretty new concept. In the realms of big data, this term is being used quite extensively though.

What is Sharding in simple words:

Sharding is partitioning. Horizontal partitioning to be exact.

Sharding means partitioning a table rows on basis of some criteria and storing that partitioned rows of table (i.e. a shard) on different database servers. These database servers are cheap low commodity servers.

The benefits include smaller data to manage, smaller backups, faster reads, and faster response time for the queries.

Just like existing partitioning option in the Oracle database, there are generally three kinds of sharding:

Range Sharding
List Sharding
Hash Sharding

The news out there on social media is that Oracle 12c next version is coming up with Sharding option. That is pretty exciting and let's see what they come up in this regard.

Friday, September 18, 2015

ORA-01917: user or role 'PDB_DBA' does not exist

I manually created a container database (CDB) in my Linux 6.4 based virutal machine. After creating it, I tried to create a pluggable database but got following error:

Wednesday, September 9, 2015

Amazon S3 to Glacier - Cloud ILM

Falling in love with Kate Upton is easy but more easier is to be swept off your feet by information lifecycle management (ILM) in the Amazon Web Services (AWS). Simple, easily-configurable, fast, reliable, cost effective and proven are the words which describe it.

Pythian has been involved with ILM for a long time. With various flavors of databases and systems, Pythian has been overseeing creation, alteration, and flow of data for a long time until it becomes obsolete. That is why AWS's ILM resonates perfectly well with Pythian's expertise.

Amazon S3 is an object store for short term storage, whereas Amazon Glacier is their cloud archiving offering or storage for long term. Rules can be defined on the information to specify and automate its lifecycle.

Following screenshot shows the rules being configured on objects from S3 bucket to Glacier and then permanent deletion. 90 days after creation if an object, it will be moved to Glacier, and then after 1 year, it will be permanently deleted. Look at the graphical representation of lifecycle as how intuitive it is.

Sunday, September 6, 2015

Last Successful login time in 12c

One cool small yet valuable feature in Oracle 12c is the display of 'Last Successful login time'. If authentication is from the OS level, then it isn't shown. A small demo is as follows:

Friday, September 4, 2015

\d in Vertica

A quick neat way to list down important and oft-needed information like names of databases, schemas, users, tables, projections etc. We can also use patterns with the '\d' to narrow down the results. Let's see it in action:

Friday, August 28, 2015

Creating User Schema Table and Projections in Vertica

Vertica is a an exciting database with some real nifty features. Projections is a ground breaking unique feature of Vertica which dramatically increases performance benefits in terms of querying and space benefits in terms of compression.

Thursday, August 6, 2015

Mongostat ; A Nifty Tool for Mongo DBA

One of the main Mongodb DBA's task is to monitor the usage of Mongodb system and it's load distribution. This could be needed for proactive monitoring, troubleshooting during performance degradation, root cause analysis, or capacity planning.

Mongostat is a nifty tool which comes out of the box with Mongodb which provides wealth of information in a nicely and familiar formatted way. If you have used vmstat, iostat etc on Linux; Mongostat should seem very familiar.

Tuesday, July 14, 2015

Shift Command in Shell Script in AIX and Linux

Shell in Unix never ceases to surprise. Stumbled upon 'shift 2' command in AIX few hours ago and it's very useful.

'Shift n' command shifts the parameters passed to a shell script by 'n' numbers to the left.

For example:

if you have a shell script which takes 3 parameters like:

./ arg1 arg2 arg3

and you use shift 2 in your shell script, then the values of arg1 and arg2 will be lost and the value of arg3 will get assigned to arg1.

For example:

if you have a shell script which takes 2 parameters like:

./mytest arg1 and arg2

and you use shift 2, then values of both arg1 and arg2 will be lost.

Following is a working example of shift command in AIX:


testsrv>chmod a+x


echo "Before Shift: $1 and $2 => SID=$SID and BACKUPTYPE=$BACKUP_TYPE"
shift 2
echo "After Shift: $1 and $2 => SID=$SID and BACKUPTYPE=$BACKUP_TYPE"

testsrv>./ orc daily

Before Shift: orc and daily => SID=orc and BACKUPTYPE=daily
After Shift:  and  => SID=orc and BACKUPTYPE=daily

Note that the values of arguments passed has been shifted to left, but the values of variables has remained intact.