Most of you are aware that Oracle does simple compression when you have a ordered result set by sending the data once along with count for duplicate data. This is prior to Oracle 12c. Starting with Oracle 12c, Oracle supports Advanced Network Compression. Too bad, it is available as part of Advanced Compression option.
Lets forget Oracle for sometime and look at what are the main constraints for networking? It is generally the network bandwidth (more...)
Prior to Oracle 12c truncating tables with dependent children was a pain. We had to disable the constraint before truncating the parent table followed by enabling the constraint. The performance gain achieved using TRUNCATE instead of DELETE almost disappeared if the constraints were enabled with VALIDATE option. If enabling the constraint with NOVALIDATE option was not acceptable, then DELETE seemed to be the only way to go.
Starting with Oracle 12c , Oracle supports truncating (more...)
In Oracle 12c , there are lot of new great RMAN features focused on reducing recovery time; Some of them to provide better DBA experience too .Let us glance at some of them in this blog.
- Support for point in time recovery for tables and partitions. I would rate this as one of best options of Oracle 12c.
- Option to duplicate database with NOOPEN option so that duplicated/cloned database remains in mount state. Prior to (more...)
Let us assume you have a table with ten columns and four B*Tree indexes. If you analyze the space usage; chances are very high that size of all indexes together is nearly equal to more than the size of the table. If you end up creating more indexes to support complex business requirements, the size of all indexes together could be 2-4 times(or more) the size of tables. So what is the big deal, (more...)
Prior to Oracle 12c, VARCHAR2/NVARCHAR2 datatypes allowed storing variable length characters of up to 4000 bytes whereas RAW data type allowed storing variable length binary data of up to 2000 bytes. In Oracle 12c, the maximum size for data types VARCHAR2, NVARCHAR2 and RAW is increased to 32767 bytes. These data types are now referred to as extended data types. Extended data types are implicitly implemented using concept of LOBs; Just (more...)
This blog discusses the setup required to support extended datatypes in Oracle12c; For more information about extended datatypes, please refer to http://twelvec.com/2014/02/08/oracle12c_extended_datatypes/
In nutshell , the following steps are required to enabled extended datatypes. Most of the steps are familiar except for step 3 and 4. In step 3 , we are modifying initialization parameter MAX_STRING_SIZE from STANDARD to EXTENDED. Once changed , this is a irreversible action. Step 4 increases the sizes (more...)
PURGE DBA_RECYCLEBIN command empties the recyclebin at database level i.e. purges the recyclebin for all users in the database. Until 11g , you need to have powerful
SYSDBA system privilege to execute this command. To some extent it made sense as this option was mainly used for upgrades or migration or to apply DST patch. One of focus areas of Oracle12c is separation of duties resulting in new roles like SYSKM for (more...)
Increased scrutiny of Organizations adherence to regulatory guidance like SOX, Hippa & PCI DSS has made audit of critical and privileged actions in the database more likely to be mandatory than optional. Therefore forcing database or software vendors to implement auditing with piecemeal approach. Oracle is no different with various ways to audit & various locations to store them in various formats. For example some of the auditing options available with Oracle 11g (more...)
Starting with Oracle 12c, the default behavior of database is to gathers statistics during bulk loads like CTAS or INSERT SELECT. However for some reason , if you want to go back to pre-12c behavior , you can do so with a new hint NO_GATHER_OPTIMIZER_STATISTICS hint.
See illustration of (more...)
TABLE ACCESS BY INDEX ROWID BATCHED is new execution plan operation that helps improve performance. It is generally used for > or < queries by selecting few ROWIDs from the index and then try to access the rows in blocks. This significantly reducing the number of times Oracle must (more...)