12c new features « All about Database Administration, Tips & Tricks

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

12c Database : View database patches from the sql prompt

From 12c onwards, you can view the database home patch list from sql prompt itself . A new package dbms_qopatch has been introduced to accomplish this.

Few cool sub routines in this package are get_opatch_install, lsinventory, opatch_bugs etc.

SQL> set longchunksize 1000 SQL> select DBMS_QOPATCH.get_opatch_install_info() from dual DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO() ———————————————————————– OracleHome-5958ed72-136c-4adc-a078-e3ec7081a6e8 oracle_home oneoff /u01/app/oracle/product/12.1.0.1/db_1 oracle_home /u01/app/oraInventory […]

12c Database : Session level (private)statistics for global temporary tables

Often, when the global temporary tables are in use in batch processing, we have lot of problems regarding plan stability.

For example

A session inserting 1 row in a global temporary table based on some other table joins Another session which were apparently doing the same insert but will try to insert 1000 rows […]

12c Database : Reporting mode for statistics jobs and compare statistics jobs

Before to 12c, we have a problem to revert back to a question, how much time does the statistics operation will run?

This is common problem, as we cannot anticipate the time and its depend on the various factors that will take place at time of statistics job execution.

To accomplish this we will have […]

12c Database : Monitor Database operations using EM Express

Previous to 12c, when you want to perform an monitoring the Session you will have to turn the trace etc or trace with application/module/program/sql_id etc.

But, what if you want to monitor specific operation for that session not whole or application. Kind of set of operations you want to peform not all with in that […]

12c Database : SQL Plan Directives

From 12c onwards, Oracle optimizer maintains the directives that influence the optimizer to take appropriate decision while executing the statement or estimation.

As you read from previous posts, Optimizer maintains the execution statistics now,they can be used to create a directive for future executions. These directives can be used by optimizer and determine which optimization […]

12c Database : Adaptive Query Optimization

Before to 12c, all the sql tuning features like ACS, SQL Profiles, SQL Outlines, SPM etc are for proactive tuning and plan stability. There is no way around to decide and change the optimizer decision on fly during the execution of a sql statement.

Since 12c, Oracle introduced the set of capabilities called "adaptive query […]

12c Database : Asynchronous index maintainence

Earlier to 12c, when an partition is truncated with update global index clause the operation will take longer to complete since it has to perform the operation on index also to remove corresponding keys in index partition/index and then rebuild entire partition. But that is history,

Asynchronous global index maintainence is the feature to clear […]

12c Database : Interval Partitioning enhancements – 2

From 12c, interval partitions has been enhanced to use reference partitioning, With this you can create a parent/child table with references in them using interval partitioning.

The following is the test case.

Create table EMPLOYEE ( EMPLOYEE_ID number primary key, EMPLOYEE_NAME varchar(25), JOINING_DATE date) PARTITION BY RANGE (JOINING_DATE) INTERVAL (NUMTOYMINTERVAL(1,’YEAR’)) ( PARTITION P_2013_06 VALUES LESS […]

12c Database : Reference Partitioning Enhancement – Part 1

From 12c, you can truncate partitions from a referenced partition with cascade option.

The following is the test case.

Create table EMPLOYEE ( EMPLOYEE_ID number primary key, EMPLOYEE_NAME varchar(25), JOINING_DATE date) PARTITION BY RANGE (JOINING_DATE)( PARTITION P_2013_06 VALUES LESS THAN (to_date(’30-06-2013′,’dd-mm-yyyy’)) NOCOMPRESS, PARTITION P_2013_07 VALUES LESS THAN (to_date(’31-07-2013′,’dd-mm-yyyy’)) NOCOMPRESS, PARTITION P_2013_08 VALUES LESS THAN (to_date(’31-08-2013′,’dd-mm-yyyy’)) […]

12c Database : New DBMS_PART package to cleanup the partition maintenance activities

In 12c, Oracle introduced package to manage the global indexes orphan entry clean up and clearing up the online partition movement stuff. Here it is

PROCEDURE CLEANUP_GIDX- To clean up the global indexes , this runs daily at 2.00AM via scheduler job automatically PROCEDURE CLEANUP_GIDX_INTERNAL – To clean up the internal tables PROCEDURE CLEANUP_ONLINE_OP […]

All about Database Administration, Tips & Tricks

New Features for DBA’s

Subscribe to Posts by Email

Subscriber Count

Disclaimer

Recent Posts

Categories

Archives

Pages