Monday, 4 May 2015

Difference between SPM and SQL Profiles



Difference between SPM and SQL Profiles :-

Below are some valid points regarding the differences of the above :-

SPM baselines :

# Enforce specific plans and Guarantee plan stability
# Contain multiple plans
# Can record new plans for potential improvement
# Only matches a whole SQL statement in its entirety
# Only accepted plans are used

A SQL Profile is generated by SQL Tuning Advisor and:

# Does not enforce a specific plan
# Does not guarantee plan stability
# Does not contains a plan at all, whereas SPM can contain multiple plans
# Is a collection of directives (generated by STA based on empirical information) which can be applied across many SQL statements to influence the optimizer to hopefully produce a better plan.
# Because no plans are recorded, if there is a change that results in a new, better, plan, then that plan would be lost if the change was backed out.
# Can be configured to match part of multiple SQL statement (ie can apply to SQL strings which contain all the same characters)
# If there is already a SQL plan baseline in place when a SQL Profile is created, the new plan is added to the baseline

Wednesday, 22 April 2015

Webinar of Going LIVE with 12C FAR SYNC and DATA GUARD

Few days back i got the opportunity to give a webinar for All India Oracle Users Group North India, where i gave a webinar for GOING LIVE with Oracle 12C FAR SYNC and 12C DATA GUARD, It is also published on YOUTUBE , you can view that from the below link :-

Going LIVE with FAR SYNC 12C and DATA GUARD

Happy Learning

OTN Tour India 2015, OTN yathra

OTN Tour India 2015, OTN yathra was recently finished and now All India Oracle Users Group (AIOUG) has published my presentation on its website. I was a speaker for 2 topics which were SQL Plan Management (Including what's New in 12C) and Understanding Histograms (Including what's New in 12C) , Below are the links from where you can view or download,

Understaning Histograms (Including whats new in 12c)

SQL Plan Management (Including whats new in 12C)

I am also putting some pictures of the event :-





Tuesday, 10 March 2015

Tracing a session with a client identifier


Below is the Methodology to trace a session with Client Identifiers, I have created a special trigger also to generate client_identifier for a user with DBMS_SESSION package.

SQL> create table sys.trigger_test  (cid varchar2(1000), sdate date, loggin varchar2(1000));

Table created.

CREATE OR REPLACE TRIGGER trig_logon_scott_v1
  AFTER LOGON
  ON SCOTT.SCHEMA
BEGIN
DBMS_SESSION.SET_IDENTIFIER('hr');
END;
/

Trigger created.

CREATE OR REPLACE TRIGGER set_trace_logon_test1
AFTER LOGON ON SCOTT.SCHEMA
WHEN (lower(sys_context('USERENV', 'CLIENT_IDENTIFIER')) like '%hr%')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12 : 10053 trace name context forever, level 1''';
EXECUTE IMMEDIATE 'alter session set "_fix_control"="6765823:off"';
insert into sys.trigger_test values (sys_context('USERENV', 'CLIENT_IDENTIFIER'),sysdate,'LOGON');
commit;
END set_trace_logon_test1;
/

Trigger created.

[oracle@node4 ~]$
[oracle@node4 ~]$ sqlplus scott

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 11 12:00:55 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


Checking and confirming at the trace location

[oracle@node4 trace]$  ls -ltr |tail -2
-rw-r----- 1 oracle oinstall 131747 Mar 11 12:00 prd_ora_6289_From_Trigger.trm
-rw-r----- 1 oracle oinstall 477686 Mar 11 12:00 prd_ora_6289_From_Trigger.trc

Sunday, 22 February 2015

OTN Conference (OTN Yathra) in 2015 held at India

The great event OTN Yathra which happens annually and is going on right now in India, I also got a chance as a Speaker to present on few topics which were Understanding Histograms (Including what's new in 12C) and SQL Plan Management (Including what's new in 12C), So i had 2 sessions for 2 hours, First i presented on 13th Feb @ Gurgaon where the first event of this conference was held as a start of this conference, the second event where i presented was on 17th Feb @ Chandigarh. Below are few of the pictures when i was presenting the sessions. I would like to thank All India Oracle Users Group (AIOUG) and OTN for giving me the opportunity to speak at this great big event.




Tuesday, 4 November 2014

Predefined Roles for Oracle 12C


When we run standard scripts that are part of database creation, Oracle 12C automatically defines few roles which are listed below

Predefined Roles for Oracle 12C

ADM_PARALLEL_EXECUTE_TASK
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
AUDIT_ADMIN
AUDIT_VIEWER
AUTHENTICATEDUSER
CAPTURE_ADMIN
CDB_DBA, CONNECT
CSW_USR_ROLE
CTXAPP
CWM_USER
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
DBA
DBFS_ROLE
DELETE_CATALOG_ROLE
EJBCLIENT
EM_EXPRESS_ALL
EM_EXRESS_BASIC
EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
GLOBAL_AQ_USER_ROLE
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_ROLE
HS_ADMIN_SELECT_ROLE
IMP_FULL_DATABASE
JAVADEBUGPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVAUSERPRIV
JAVA_ADMIN
JAVA_DEPLOY
JMXSERVER
LBAC_DBA
LOGSTDBY_ADMINISTRATOR
OEM_ADVISOR
OEM_MONITOR
OLAP_DBA
OLAP_USER
OLAP_XS_ADMIN
OPTIMIZER_PROCESSING_RATE
ORDADMIN
PDB_DBA
PROVISIONER
RECOVERY_CATALOG_OWNER
RESOURCE
SCHEDULER_ADMIN
SELECT_CATALOG_ROLE
SPATIAL_CSW_ADMIN
SPATIAL_WFS_ADMIN
WFS_USR_ROLE
WM_ADMIN_ROLE
XDBADMIN
XDB_SET_INVOKER
XDB_WEBSERVICES
XDB_WEBSERVICES_OVER_HTTP
XDB_WEBSERVICES_WITH_PUBLIC
XS_CACHE_ADMIN
XS_NSATTR_ADMIN
XS_RESOURCE
XS_SESSION_ADMIN

SYS user table USER$ in Oracle Database

SYS.USER$ Internal table in Oracle Database

# PTIME provides the date the password was last changed.
# LCOUNT provides the number of failed logins.
# CTIME provides the date the user was created.
# LTIME provides the date the user was last locked.

In the case of LTIME column the value will not be set to null if the user will be unlocked.

Below is an example of how to query user$ table :-

select ctime,ltime,ptime from user$ where name = 'SCOTT';

In Oracle database 12C the SELECT ANY DICTIONARY Privilege does not allows users to access some SYS Data Dictionary Tables like for example the sys schema tables USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS, DEFAULT_PWD$, ENC$, LINK$, and USER$. So thats a new enhancement for 12C security, there are lots of them and i will be publishing them too soon on this blog on Label Oracle Security.