Data Redaction is one of the new features of 12c actually, and also it's become available in 11gR2 with 11.2.0.4. Data Redaction is in Advanced Security option of enterprise edition.

What Data Redaction does is basically masking the data on the fly based on the type and expression given. Let's do a demonstration:

First thing to do is to create a policy. Policies are created on tables and a table can have only one policy. If you try to add a policy to table with a policy, you get ORA-28069. Policy can be defined for only a single column at a time but you can add columns to the policy by altering it.

Above command creates a policy on table CUSTOMERS in schema DEMO and it masks column CNAME by using regular expressions. Masked values will contain first 3 letters of the name (each name if customer has a middle name) and 3 "*"s.

There are 6 types of redaction; full, partial, regexp, random and none. You can find detail information in Oracle documentation. Please not the expression parameter. What we're saying here is apply this policy to users who are not sys. By default polices applied against users except sys and object owner. So if you also want table owner gets masked values or you want to redact data based on application, such an expression must be used. Expression is a mandatory parameter, you can set it as '1=1' if you have no rule to apply.

By altering the policy we've created, we added another column of the table to be masked. Here we used built-in redaction function. This function set all date values to 01-Jan-1970. There are couple of more pre-defined functions such for SSN, e-mail zip code.

This time we add CID column to the policy and redacted it fully. So masked values will be displayed as 0. To change a columns redaction type, alter policy again by setting altering action to MODIFY_COLUMN:

Finally, if you need to drop a policy:

Data redaction is not a complicated way of defining security policies however what I see as a downside is one-to-one relationship between columns and policies: It is not allowed to add another policy/expression for a column, you get ORA-28060 error. So what you need is to create a rule base which has different masking types for different roles on the very same table, you need another tool. Otherwise you have practical data masking tool.

SQL Developer may not be the perfect IDE, but it doesn't mean that it has not got any tricks at all. One of those features I'd like mention about can ease data transfer. SQL Developer excepts hints to format output. Here are the outputs for some of those hints; csv, loader, fixed and insert. As names implie; CSV gives rows with columns separated by commas, loader gives data formatted for SQL Loader, fixed returns columns separated by fixed spaces and insert creates insert commands.

select /*csv*/ * from scott.dept;

"DEPTNO","DNAME","LOC"
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"

select /*loader*/ * from scott.dept;

10|"ACCOUNTING"|"NEW YORK"|
20|"RESEARCH"|"DALLAS"|
30|"SALES"|"CHICAGO"|
40|"OPERATIONS"|"BOSTON"|

select /*fixed*/ * from scott.dept;

"DEPTNO"    "DNAME"           "LOC"                        
"10"        "ACCOUNTING"      "NEW YORK"                   
"20"        "RESEARCH"        "DALLAS"                     
"30"        "SALES"           "CHICAGO"                    
"40"        "OPERATIONS"      "BOSTON"                      

select /*insert*/ * from scott.dept;
REM INSERTING into scott.dept
SET DEFINE OFF;
Insert into scott.dept (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into scott.dept (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into scott.dept (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into scott.dept (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');

It's also possible to get output in html or xml format which can really be handy for a quick report. As you can see below, html format includes even a search box to make searches in content.

Please note that hints are used in lower case and no space left between the hint and *s.

Lately I noticed that free disk space on my 12c Cloud Control server was reducing for some amount. When I investigated, I've figured it out that there were daily incidents of BEA-337 errors with around 40 MB of log  files in /u01/app/Oracle/Middleware/oms12c/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/adr/diag /ofm/GCDomain/EMGC_OMS1/incident directory.

As you can see, incidents was caused due to a stuck thread watch rule. It was triggered because of "Refresh From My Oracle Support" job which is running more than 600 seconds. First thing I tried was rescheduling the job. From OEM, I navigated to jobs and found the job through advanced search by selecting ”Refresh From My Oracle Support” from the job type drop down and rescheduling it to some less busy time.

However it didn't work out the problem. So I decided to increase the parameter controlling stuck thread watch rule which is StuckThreadMaxTime. It's default value is 600 (seconds) and my job takes 13.5 minutes to complete. 

To alter the parameter:

1. Log on to WebLogic administration console through EMGC_OMS1 target on OMS or directly navigating to https://<host>:7200/console 

2. Click on EMGC_OMS1 under GCDomain->Environment -> Servers. Find "Stuck Thread Max Time" setting on Configuration/Tuning tab. I've increased it to 1200 seconds to cover job duration.

3. Save the configuration and then restart EMGC_OMS1 server from WebLogic administration console or from server console.

This workaround did the job.

As you are all probably aware, Oracle released new database version named 12c a few days ago, on 25th of June to be exact. It's the time of reading and researching new features, testing and developing. So I'm starting with this entry and installing a single instance. I intend to keep writing as I try more complex installations and upgrades.

OK, lets's start. I'll install a single instance 12c database on Oracle Linux 6.4 64-bit. As storage, NAS will be used, so no ASM therefore no GI will be installed. First step after the Linux installation is completing operating system configuration. Easiest way of achieving this is installing pre-install package. In 12c documentation it's said to run the command below as root to install package:

$ yum install oracle-rdbms-server-12cR1-preinstall

However mentioned package cannot be found on ULN. Instead install previous version's pre-install package. It does the job as well:

$ yum install oracle-rdbms-server-11gR2-preinstall

Create installation directory and grant required permissions:

$ mkdir /u01
$ chown oracle:oinstall /u01
$ chmod 775 /u01

As oracle, which is created by pre-install package, run the installer from the directory where you unzipped two installation zip files:

$ ./runInstaller

Graphical installation starts:


Skip updates


You can choose to create a database right after software installation or you can choose to install software first then run DBCA to create the database.


Select system class:


Select GI option:


I choose to follow advanced installation path to see what is there:


Select language(s):


Select database edition to install:


Specify installation paths:


Specify inventory directory:


Select database type:


Specify database name. As a new feature, you can choose to create a multi-tenant database.


Configure database by specifying memory options, character set and sample schema installation:


Specify storage, I set the path of mount point of NAS disk:


Specify Cloud Control details if you'll manage the instance through it:


Choose to use FRA and set path -or ASM- for FRA:


Set passwords for built-in users:


Set system groups:


Investigate summary and if there's anything you need to chance:


Installation starts. Run the root scripts when prompted:


12c DBCA

If you choose to install software only during installation or installation failed to create the database - as I've experienced - you can run database configuration assistant (DBCA) which is located under ORACLE_HOME/bin:

$ cd /u01/app/oracle/product/12.1.0/dbhome_1/bin
$ ./dbca

Graphical installation starts:


I choose to follow advanced installation path to see what is there:


Select the template of database type:


Set database names including container database:


Choose to configure database with EM Database Express (previous version's dbconsole) or with Cloud Control:


Set passwords of built-in user:


Set listener configuration, leave it as default:


Set storage specifications:


Set database options:


Set initialization parameters for memory management, number of Oracle processes, character set and connection mode:






After pre-requisite check installation starts. When finished, necessary information is displayed:


Enjoy and discover your first 12c database...

Recently I faced with a performance complaint for a query. Query was simple as it could be, just a "select * from some_table". When I digged out the problem with my fellow developer, we discovered that almost all time was spent while the data adapter fills the data table. Table had a little more than 155K rows and was size of 31 MB. Yet, it took more than 5 minutes to complete for data adapter to fill data table.

The cause of the problem was the number of round trips client need to accomplish to get all the rows from database. If you can reduce the number of round trips, you can increase the fetch size so that in each turn command object will fetch more rows from database. Here is how it's impletemented: