Why upgrade to Oracle Database version 12C

Oracle database version 12C is by far the most important Oracle release in the last 10 years. Its advanced capabilities promote better performance, increased scalability, and easier data management. For the enterprise, this translates into significant cost savings, reduced risk, and increased flexibility. The Oracle 12C means different things to different people. It all depends on which areas you are looking at, as there are improvements in many areas.

Below are some of the top mose notable  features in the 12C database.

Top 12 Features of Oracle 12C Summarized.
01. Pluggable Databases Through Database Consolidation:

Oracle is doing every thing to jump into the cloud bandwagon. With 12C, Oracle is trying to address the problem of Multitenancy through this feature. There is a radical change and a major change in the core database architecture through the introduction of Container Databases also called CBD and Pluggable Databases (PDB). The memory and process is owned by the Container Database. The container holds the metadata where the PDBs hold the user data. You can create upto 253 PDBs including the seed PDB.

In a large setup, it is common to see 20 or 30 different instances running in production environment. With these many instances, it is a maintenance nightmare as all these instances have to be separately

•RAC Enabled
•Backed up and
•Data Guarded.

With Pluggable Databases feature, you just have to do all this for ONE single instance. Without this feature, prior to 12C, you would have to create separate schemas and there is always a thread of security how much ever the isolation we build into it. There are problems with namespace conflicts, there is always going to be one public synonym that you can create. With PDBs you can have a separate HR or Scott schema for each PDB, separate Emp, Dept Tables and separate public synonyms. Additionally, 2 PDBs can talk to each other through the regular DB Link feature. There is no high startup cost of creating a database any more. Instead of one instance per day, the shift is into one instance per many databases. For the developer community, you can be oblivious of all this and still continue to use the PDBs as if it were a traditional database, but for the DBAs the world would look like it has changed a lot.

Another cool feature is, you can allocate a CPU percentage for each PDB.

Another initiative being, it has announced a strategic tieup with salesforce.com during the first week of July 2013.


02. Redaction Policy:

This is one of the top features in Oracle 12C. Data Redaction in simple terms means, masking of data. You can setup a Data Redaction policy, for example SSN field in a Employee table can be masked. This is called redaction. From Sql Develop you can do this by going to the table: Employee->Right click on Security Policy->click on New->click on Redaction Policy->Enter SSN. When you do a select * from employee, it will show that the SSN is masked.
The new data masking will use a package called DBMS_REDACT. It is the extension to the FGAC and VPD present in earlier versions. By doing this, whoever needs to view the data will be able to see it where as the other users will not be able to view it.


03. Top N Query and Fetch and offset Replacement to Rownum:

With the release of Oracle Database 12c, Oracle has introduced this new SQL syntax to simplify fetching the first few rows. The new sql syntax “Fetch First X Rows only” can be used.


04. Adaptive Query Optimization and Online Stats Gathering:

With this feature, it helps the optimizer to make runtime adjustments to execution plan which leads to better stats. For statements like CTAS (Create Table As Select) and IAS (Insert As Select), the stats is gathered online so that it is available immediately.


05. Restore a Table easily through RMAN:

Earlier if you had to restore a particular table, you had to do all sorts of things like restoring a tablespace and or do Export and Import. The new restore command in RMAN simplifies this task.


06. Size Limit on Varchar2, NVarchar2, Raw Data Types increased:

The previous limit on these data types was 4K. In 12C, it has been increased to 32,767 bytes. Upto 4K, the data is stored inline. I am sure everyone will be happy with this small and cute enhancement.


07. Inline PL/SQL Functions and Procedures:

The in line feature is extended in Oracle 12C. In addition to Views, we can now have PL/SQL Procedures and Functions as in line constructs. The query can be written as if it is calling a real stored procedure, but however the functions do not actually exist in the database. You will not be able to find them in ALL_OBJECTS. I think this will be a very good feature for the developers to explore as there is no code that needs to be compiled.


08. Generated as Identity/Sequence Replacement:

You can now create a col with ‘generated as identity’ clause. Thats it. Doing this is equivalent to creating a separate sequence and doing a sequence.nextval for each row. This is another handy and a neat feature which will help developer community. This is also called No Sequence Auto Increment Primary Key.


09. Multiple Indexes on a Single Column:

Prior to 12C, a column cant be in more than one index. In 12C, you can include a column in B-tree index as well as a Bit Map index. But, please note that only one index is usable at a given time.


10. Online Migration of Table Partition or Sub Partition:

You can very easily migrate a partition or sub partition from one tablespace to another. Similar to how the online migration was achieved for a non-partitioned table in prior releases, a table partition or sub partition can be moved to another tablespace online or offline. When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.


11. Temporary UNDO:

Prior to 12C, undo records generated by TEMP Tablespace is stored in the undo tablespace. With Temp undo feature in 12C, temp undo records can be stored in temporary table instead of UNDO TS. The benefit is – reduced undo tablespace and reduced redo log space used.

SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0; –disables the hard limit


12. In Database Archiving:

This feature enables archiving rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression but are not visible to the application. These records are skipped during FTS (Full Table Scan).


Other Features:

Advanced Replication and Streams are Dead. It is being replaced with Oracle Golden Gate.

Invisible Columns. You can now have a invisible column in a table. When a column is defined as invisible, the column won’t appear in generic queries

PGA Aggregate Limit setting:

In 12c, you can set a hard limit on PGA by enabling the automatic PGA management, which requires PGA_AGGREGATE_LIMIT parameter settings. Therefore, you can now set the hard limit on PGA by setting the new parameter to avoid excessive PGA usage.

DDL Logging:

The DDL statements will automatically get recorded in xml/log file if ENABLE_DDL_LOGGING is set to True. ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE

Oracle Database – It’s Popularity and Merits!!

There are many other databases used by different organizations and for different purposes. But today, the most widely used databases are Oracle and MS SQL Server. These are considered as the best and the most popular in terms of transactions and the organizations that utilize them in most of the database projects.

Effective and efficient database management solutions are granted to the users through its products such as Oracle 8i, 9i up to 11g. Organizations who are looking for long term database management solutions find Oracle as the best because of its durability, trustworthy, crash free, its effectiveness etc thereby putting less load on the server.

Why is Oracle Popular?

Developed by the world’s largest and the most respectful enterprise solution provider and software company, Oracle database has become widely popular with its variety of database products that are also compatible with all the other versions. Its feature of product interconnection and compatibility with every version in front of clients is considered to be as the most impressive one.

The Numerous Advantages Of Oracle Database Include:

Satisfaction Of Customers

Oracle databases, being backward compatible gives the system the opportunity of upgrading their systems. Moreover, it also provides efficient and low cost updates. It can be said that the biggest merit of using Oracle database is its belief in customer satisfaction. The customer’s satisfaction is considered as the target, according to which in new versions of Oracle databases new impressive features are introduced.

It’s functionality

Being used in mostly all the corporation level applications, Oracle database’s practical usage, is the feature many organizations count on. As a pioneer database in the banking industry, it is used by world’s top 10 banks, thereby making the operations as smooth as possible. Providing a combination of high-level technology and integrated business solutions depicts its best functionality and thus making it perfect for all those corporations that have to handle large amounts of data efficiently.


Whenever stuck with demanding tasks, it is Oracle that delivers excellent performance proving its reliability. Oracle databases, passed the ACID tests for databases (a test that ensures data integrity), also proves that these are the best databases that are considered highly reliable when it is about the integrity of data storage.

Flashback Technology

For any system, the most important thing is not to lose the stored data at any cost. And if in case the data is lost then Oracle’s flashback technology gives you the advantage of efficient recovery of the data that is either deleted or lost. All the human errors are eliminated with the help of flashback technology and the database recovery time is also increased. All in all, with this process an organization or company will experience smoothness in management and as well in the administrative process.

Exploring the Qualities of an SQL Expert!!

In order to deal with today’s business cycles leading to effective business solutions one should be having a proper grip on their database that would assist in avoiding the risks following a number of potential problems. In case of SQL Server environment or MySQL Remote Database Administration a little bit of streamlining is required that would make the overall understanding and managing easier.

Here we have elaborated some top qualities of an SQL server DBA expert with whose help the overall risk of crises may reduce.

Creates a Customized Maintenance Plan

When working regularly or being part of the team leading a very important project of a database development company, the database cannot be just set into production. There are chances that the indexes might become fragmented over time, I/O subsystems can get corrupted and much more.

So, to effectively deal with such situation an SQL expert will have a comprehensive maintenance plan. Furthermore, a customized plan is far better than a generic that usually do not address all of the needs.

Maintains Good Terms With the Developers

There is always tension observed between the DBA and the development team as the two usually get mistaken on each other’s priorities and concerns regarding SQL Server design’s development guidelines.

When being an SQL expert if you proactively engage with your development team then all in all the job would become smoother and it would be easy for you to take decisions and execute the plans effectively. You may also plan to conduct design reviews with mutual understanding with your development team.

Monitors and Maintains Performance

A good SQL server expert would always take care about the ways in which he/she can maximize and monitor the performance. He must opt certain steps such as:

  • Have a firm grip on the performance baseline that will help keep a check on how much the performance has changed.
  • Utilizing the watts and queues methodology, it will become easy to identify the problems
  • With system primitives, performance counters and wait statistics performance will be monitored. This way, be it MySQL server administration or SQL Server administration the concerned person will be able to record the data that is involved in the performance.
  • Establishing a maintenance plan etc

Ensure Security of Your System

A proficient SQL server DBA would have a clear eye upon all the security problems, that are enough in preventing any untimely risky issues having a chance to occur unexpectedly.