RSS

Tag Archives: PLSQL

Oracle PLSQL – Oracle Database Native Language.

Completed training on “Oracle Database 12c R2: Advanced PL/SQL”

Dear Friends,
I am glad to share with you all that I completed the training on demand on “Oracle Database 12c R2: Advanced PL/SQL”.
Thanks to Oracle University and my faculty Brent Dayley for providing this training on demand.
Oracle Advanced PL/SQL in Oracle 12c R2
The training covered important features in Oracle PL/SQL in 12c R2 version. It was well designed and gave a good head-start from the technical perspective.

I would also like to thank Oracle University which provides wonderful platform for such courses.

My detailed resume can be found here.

Cheers!
Vijay

 
Leave a comment

Posted by on January 2, 2020 in Oracle, PL/SQL

 

Tags: , ,

Secured 27th position in Annual Logic Championship for 2015

TrophyDear All,
I am delighted to share with you that I have secured 27th position in Annual Logic Championship for 2015 🙂

The championship was organised by Oracle PL/SQL Challenge. The championship was held on invitation only basis. There were 41 participants from across the world. Below is the result of the championship.

Secured 27th Position in Annual 2015 Logic Championship

Secured 27th Position in Annual 2015 Logic Championship

The number next to the name represents the number of times the player has participated in a championship. Thanks Oracle and PL/SQL Challenge for the public recognition of my accomplishment.

Here is the link to the webpage which shows my accomplishment. The link to my public profile on Oracle PL/SQL Challenge is here.

Cheers!
Vijay

 
Leave a comment

Posted by on March 21, 2016 in General, Oracle, PL/SQL

 

Tags: , , ,

More minds meet at Sangam 2015…

Diary on Sangam’15
21st, 22nd Nov 2015 at Hyderabad International Convention Centre, HITEC City, Hyderabad, India

In the recently concluded two day Oracle conference – Sangam 2015, there were more number of attendees than last year (Sangam 2014). Sangam – An annual conference for Oracle Technologist across India is organised by AIOUG (All India Oracle Users Group) and aptly has tagline of ‘meeting of minds…’.

It was a privilege to attend such a large scale event for third consecutive year. It was an honour to get in touch with the Oracle ACE and Oracle ACE Directors and hear them speak in front of august gathering. There were numerous sessions lined-up as always. I am sharing sessions attended by me during the two days conference in brief.

Day 1: 21st November 2015

Tim Hall – Oracle ACE Director (Day 1 | Day 2) – It was treat to hear him again and his session on Oracle 12c Consolidations and pros/cons was very relevant and updated.

Kamran Agayev – Oracle Certified Master – His session on “Oracle 12c ASM new features” with webetorial was very crisp and provided lot of insights into the world of Oracle 12c for ASM.

Aman Sharma – Oracle ACE – His session on “Oracle RAC Node Eviction” was overwhelming and demonstrated his expertise in the RAC yet again. It was very useful.

Anju Garg – her session on Oracle ACFS High Availability over NFS Service was enthralling and was studded with illustrations. It was good to know that ACFS now supports all types of files.

Day 2: 22nd November 2015

Oracle VM and Oracle Linux – Kamal Dodeja (Sales Consulting Manager) and Ritesh Kumar (Senior Technical Consultant) from Oracle India Pvt. Ltd. – Their session on Oracle Linux and Oracle VM was primarily revolving around the Technical features offered by Oracle Linux and Oracle VM and concluded with the pricing details.
I had previously blogged about installation of Oracle VM, Oracle Linux and Installation of Oracle 12c on Oracle Enterprise Linux which is available here.
One interesting takeaway from this session of theirs was ksplice – which actually provides high availability and reduces downtime. Overview, Downloads and Documentation on ksplice is available at OTN here. It was a strategic acquisition made by Oracle in 2011. This enables important kernel patches to be applied without reboot or without bringing down any application services in Oracle Linux.

Debaditya Chatterjee – Product Manager Oracle USA – Oracle Database In-Memory Options – It was a good review of Oracle In-Memory database which was delivered by Maria Colgan in the last edition of Sangam. His session focused on the what’s and why’s of Oracle In-Memory database.

Satyendra Pasalpudi – Oracle ACE Director – His session on “Oracle Cloud DBA” showcased the next thing from Oracle – Oracle Cloud. The session was mainly for DBAs.

The conference closed with a motivational speech by “Dr. Rajdeep Manwani” – who shared his priceless life experiences about hard-work, patience, perseverance, failure, success and imaginary finishing line in marathon when most of the contestant gives up.

Thanks to AIOUG and Oracle Technology Network (OTN) for organising such events and providing Oracle fraternity in India a splendid opportunity to hear the speakers from elite Oracle community 🙂

Cheers!
Vijay Mahawar

 
Leave a comment

Posted by on November 23, 2015 in DBA, Oracle, PL/SQL

 

Tags: , , , , , , , ,

Weekly Participation Award for 31 August 2012 (PL/SQL Challenge)

Last updated on November 26th, 2014 at 07:07 am

Its my pleasure to share with you all that I am awarded “Weekly Participation Award for 31 August 2012” by PL/SQL Challenge.
The award is for the week ending 31 August 2012. The prize is O’Reilly Media Oracle eBook of my choice.

Visit my public profile on PL/SQL Challenge for other achievements

Cheers!

 
Leave a comment

Posted by on September 1, 2012 in Oracle, PL/SQL

 

Tags: ,

User Authentication(Part 1/4): Create users in oracle and authenticate by OS

Last updated on November 26th, 2014 at 07:07 am

Password! Password! Password!

Today with so many accounts and passwords for each of those accounts. Password management has become the primary focus for any database management team. DBA are burdened with additional responsibility which can be minimized and done with reduced time and cost.

Oracle provides some really good features for authentication, which are as follows:

1. Basic Authentication:
a. Database user authenticated by password
b. Database user authenticated by OS.
2. Strong Authentication
3. Enterprise User Security
4. Proxy Authentication

This post is Part 1 of 4 in the series on User Authentication in Oracle.

DBA are most needed when the user accounts get locked due to incorrect login attempts. When the user is a privileged user then it becomes utmost important to safeguard them against this unplanned lockout and mitigate the possibility of unproductive business hours.
In large organizations you can imagine the number of calls received by helpdesk and support team for resetting the password and request for unlocking accounts.

We are all familiar with basic user authentication in oracle which is mostly by password.
Following screen shows the user “whizdba” created and authenticated by password.

Basic Authentication - By Password

Database user authenticated by password

This type of authentication is very common and used widely. The advantage with this type of authentication is that database user can be audited, each user has a schema associated to them. On the other side the disadvantage is that the user can connect to only database where the account is created. The user is bind to one database server and any time there is need for the user to connect to different server, location the account has to be migrated or new account has to be created.

Another type of basic authentication provided by oracle is authentication by OS. The steps for setting up the user for this type is:

Step 1: Set the parameter os_authent_prefix. The default parameter value is ops$
Step 2: Create database user ops$whizdba with ‘identified externally’
Step 3: Create os user whizdba on the server where database resides.
Step 4: Set environment variables for the whizdba user in .bash_profile file
Step 5: Test and Connect to the database using OS authentication.

I have tried to demonstrate the above step with an example user “whizdba” as shown below

Step 1: Setting the parameter “os_authent_prefix” to ops$. In my case its already set. You can set it to different value using ALTER SYSTEM command.

Basic Authentication by OS

Step 1: Set parameter os_authent_prefix

Step 2: Create database user ops$whizdba with ‘identified externally’

Create database user "whizdba" identified externally

Step 2: Create database user "whizdba" identified externally

Step 3: Create os user whizdba on the server where database resides.

Step 3: Create os user "whizdba" on the server where database resides.

Step 3: Create os user "whizdba" on the server where database resides.

Step 4: Set environment variables for the whizdba user in .bash_profile file. Basic variables which need to be set are ORACLE_BASE, ORACLE_HOME, ORACLE_SID and PATH to ORACLE bin directory.

Step 4: Set environment variable in .bash_profile file of whizdba

Step 4: Set environment variable in .bash_profile file of whizdba

Step 5: Test and Connect to the database using OS authentication. Externally authenticated user connect to the database as : sqlplus / (as shown in the below screenshot)

Step 5: Test and Connect to user whizdba using OS Authentication

Step 5: Test and Connect to database using OS Authentication

Authenticating oracle database user externally by OS can be used in situation where scripting and batch job is performed by a user heavily. The advantage and disadvantage of this user remains same as that of basic password protected database user. The security concern here is that anyone can connect to the database from remote machine by creating an OS user with the same name as the externally authenticated database user. Hence getting unintended access to the database server. 

This calls for the need for stronger authentication techniques which I would discuss in my next post: User Authentication(Part 2/4): Strong Authentication

 
Leave a comment

Posted by on September 3, 2011 in Oracle

 

Tags: , , ,

Diary on OTN (Oracle Technology Network) – Developer Day Event

Last updated on November 26th, 2014 at 07:07 am

Diary on OTN (Oracle Technology Network) – Developer Day Event held on 14th July2011, New Delhi

Today I happen to attend a OTN(Oracle Technology Network) – Developer Day Event. The event was held in Hotel Intercontinental Eros, New Delhi. It was one day event.

For those of who are not aware of OTN

What is OTN?

Oracle Technology Network is the world’s largest community of application developers, database admins, system admins/developers, and architects using industry-standard technologies in combination with Oracle products.

Millions of members collaborate online to share real-world expertise and best practices about how to best design, build, deploy, manage and optimize applications. OTN is also a provider of free developer workshops across the globe, as well as a sponsor of third-party conferences, user group meetings, and events.

How to register for the event?

OTN events happen round the year at different venues across the globe. All Oracle Certified Professional, who have registered at Oracle website (http://events.oracle.com) may receive the email invite at their email with which they have registered (Usually the SSO id)

The nomination/invite for this event is sent from Oracle. After the invite is received the candidate has to register for the event at Oracle website or the link mentioned in the mail. Oracle confirms the registration on the basis of the role of the Oracle Professional and seats available for the event.
On successful confirmation candidate should receive an email at their email id.

About the Event

The event kicked-off by filling up registration form and distribution of agenda followed by breakfast.

Session 1: Development with Oracle DB 11g R2 and Oracle DB 11g XE

This was the first session on latest version of oracle database – Oracle DB 11gR2 and Oracle DB 11g XE.

The session was very exhaustive and gave overview of new features of Oracle DB 11g version from the developer and architect perspective.

Session 2: Oracle SQL Developer 3.0

This session focused on the Oracle query tool – Oracle SQL Developer 3.0 and the new features added to it after version 2.1. It’s a free tool from Oracle with extensive features. It can be downloaded directly from the oracle website without paying any license fee.

Oracle SQL Developer 3.0 is shipped with Oracle DB 11g release.

Some salient features of Oracle SQL Developer 3.0 highlighted in the session were:
1. PLSQL Unit Tester
2. Extended Tuning support
3. DBA Navigator
4. DBMS Scheduler
5. Database Migration
6. Data Miner
7. Schema Browser
8. Query Builder

This session was followed by a well needed tea/coffee break.

Session 3: Oracle Enterprise Manager 11g Database Management Packs for Developers

This session focused on OEM new features in Oracle 11g for developers. The presenter was innovative and mixed the presentation with demo – showing the OEM interface wherever required. The salient features touched were:
1. Real Time PL/SQL Monitoring.
2. SQL Monitoring in real time.

This was followed by one hour lunch break.

Post-Lunch session focused mainly on APEX 4.0, PLSQL enhancement in Oracle Database 11g and Interpreting explain plan.

Session 4: Oracle Application Express 4.0

This session dealt with the Oracle Application Express version 4.0. Oracle Application Express is a database centric web application development tool. It’s a browser based development and deployment tool and supports web 2.0.

The presenter was principal product manager of APEX 4.0 at Oracle and was keen on giving demo of all the features of APEX 4.0. He highlighted features like

1. Websheets
2. Dynamic Actions
3. Plug-ins
4. Team Development
5. Improved charting including Gantts and Maps.
6. Enhanced Interactive Reports

Oracle Application Express 4.0 is shipped with Oracle DB 11g release.

Session 5: PLSQL enhancement in Oracle Database 11g

This session highlighted the new features in Oracle DB 11g for PLSQL perspective.

The features which were presented were:
1. SIMPLE_INTEGER
2. In-lining
3. SQL Query Result Cache – Hints
4. PLSQL Function Based Result Cache
5. Compound Triggers
6. Security Level in DBMS_SQL
7. Fine Grained Audit Control for UTL_TCP and its cousins – Access Control Lists
8. Regexp_Count
9. Creating Disabled Trigger
10. Firing Order of Trigger
11. WHEN OTHERS THEN NULL – PLSQL Warning
12. Sequence.nextval can directly be assigned to a variable in 11g
13. CONTINUE statement
14. Named notation in SQL select is possible in 11g
15. DBMS_Parallel_Execute – Updating huge table with parallel execution
16. PLSQL Hierarchical Profiler

Session 6: Explaining the Explain Plan: Interpreting the execution of Explain Plan

This being the last session there were very few attendees left in the hall. I held the fort and tried to be attentive. The presenter was solution architect at Oracle and covered the topic well in given time slot.

The features highlighted were:
1. Different ways to see explain plan in Oracle
a. Explain Plan command in SQL Plus
b. V$SQL_PLAN
c. DBMS_XPLAN package
2. Criteria for plan to be good
a. Serial execution – Less cost is good.
b. Parallel execution – Faster performance is good.
3. What is cost? Parameters on which we can tune:
a. Cardinality – No. of distinct rows
b. Access Path – Best way
c. Join Order – Correct Data
d. Join Type – Right type of Join
e. Partitioning Pruning
f. Parallelism
4. Updating the statistics using DBMS_STATS and DBMS_STATS.CREATE_EXTENDED_STATS
5. It discussed various kinds of scans viz. Full Table scan, Index unique scan, Index range scan etc.

Overall the event was very informative and gave first hand info on latest technologies in Oracle 11g from developer perspective. I have tried to highlight most of the features showcased in the event.

Hope you find it useful!

 
Leave a comment

Posted by on July 14, 2011 in Oracle

 

Tags: , , , ,

 
snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake