Sql Data

Rajnish Kumar Gupta

cell 973-***-**** *******.******@*****.***

Professional Summary:

Overall 13 years of IT experience in Data warehousing and Data Migration projects, primarily as specialist in performing Analysis, Design, Development, Testing, Implementation, Migration, Deployment, Enhancement and Maintenance ETL solutions in Production and Non-Production Environments.

Extensively used tools such as Toad, SQL*Plus, SQL*Developer, SQL*Loader in many development projects in companies like UBS, KPMG, AT&T, eBay, Marriott, CISCO, Openwave.

Well conversant in translating business requirements into creation/customization of database schema objects: tables, synonyms, views, indexes, constraints, triggers, materialized views and Table Partitions.

Experienced in oracle PL/SQL advanced concepts such as Cursors, Ref cursors, Dynamic SQL, BULK Processing, Exception Handling and Collections such as Varrays, Nested tables and associative arrays.

Have good experience in using DBLinks, data loading/unloading (SQL*Loader), import, Export and UTL_FILE utilities.

Extensive experience in scheduling Oracle PL/SQL Batch jobs using control M, Autosys and DBMS JOBS Scheduler.

Expertise in Query Optimization (Nested Loop Join, Hash Joins, Inline views), debugging and Tuning for improving performance of the applications.

Extensively involved in using HINTS to alter the execution plan and to make the queries run faster for business needs.

Extensively involved in using Explain plan, Auto trace, TKPROF to improve performance for SQL/PLSQL queries.

Troubleshooting of Performance and Tuning problems in Oracle database using tools like AWR, ASH, ADDM, SATSPACK, OEM Grid Control and manual scripts.

Tremendous experience in data/object migration, data mapping and data cleanup projects using Oracle PL/SQL.

Extensive working experience on OLTP and OLAP systems.

Work on Essbase multidimensional database management system

Expertise in Database modeling (Erwin 7.0/4.1), Normalization/De-Normalization and database design.

Extensive working experience in writing shell scripting (Bourne shell, Korn shell) using Unix/Linux/AIX.

Implemented 10g RAC/11g RAC with ASM files system using Oracle clusterware.

Hands on experience on all phases of software development life cycle (SDLC).

Experience in production support and trouble shooting of database applications.

Ability to work independently with little supervision and work efficiently under pressure.

Strong communication, interpersonal, organizational, presentation skills, highly motivated, team player, quick learner. Willingness to learn new technologies, openness to new ideas.

Bachelor in Software Technology (BST) four years full time from Graduate School of Business and Administration, Delhi (Approved by VISVA BHARATI a Central University, W.B)

Tools

SQL*Plus, SQL Developer 1.2, Toad 10.6/9.6, Oracle forms 10g/9i/6i and reports 10g/9i/6i, Crystal reports 8.5/9/10,SAP BO Reporting, SQL Server Reporting Services (SSRS), Discoverer 9i/4i, Xml/Bi Publisher.

Databases

Oracle 11g/10g/9i/8i, SQL Server 2005,MY SQL, Ms-Access.

Languages

SQL, PL/SQL, C, C++, Java, VBA, HTML, UNIX Scripting.

Web Technologies

HTML, XML, Java Script, AJAX.

ETL Tools

Informatica Power Center,

Operating System

Windows 7/Vista/XP/03/2000/NT, UNIX( sun Solaris), LINUX

Other Tools

Data Modeling (Erwin 4.0), HP Quality Center, autosys

Oracle Utilities

SQL*LOADER, UTL_FILE, Export/Import functionality, Data Pump, FND_LOAD

Version Control Software

GIT, Clear case, VSS and PVCS

Applications

MS Office(MS Word, Excel, Access, Power point) and MS Visio

Methodologies

Waterfall Model, AGILE Methodology

Client : ADP Parsippany nj

Role : Sr. software developer(PL/SQL).

Duration : Sep 2018 – March 2019

Created store procedure and package to validate schema object in data warehouse to ensure all object persist in DB according to golden set description, for that created 20 procedure each procedure check data form golden set table and compare object description from database. If any discrepancy found then insert into table, using shell scripts generate report and send to DL.

Created procedure to implement business rules

Implemented local index to improve performance.

Re designed exiting package for parallel processing and batter manageability

fine tune SQL for batter performance.

created synonym for application schema.

Performed code review for offshore development team

Used GIT to check in all code.

Environment: Oracle 12c, SQL, PL/SQL, SQL*Plus, Data Warehouse TOAD 9.0, UNIX

Client : UBS Investment Bank Nashville TN

Role : Data Analyst

Duration : Jan 2017 – Sep 2018

Worked with Business System Analysts and Database Administrators (DBA's) to understand and implement functional and non-functional requirements into the project.

Analyze the functional & technical requirements of the project.

Create functional and technical documents.

Perform code review and make changes/improvements as required.

Working on 11gR2 environment and Create new tables, views, materialized views, Packages, Stored Procedures and functions as per the requirements.

Involved in various phases of System Development Life Cycle (SDLC) such as analysis, design, development, testing, implementation and support with exposure in database.

Loading data into database tables using SQL*loader and ETL from flat files.

Developed back end interfaces using PL/SQL Packages, Cursors, Stored Procedures, Functions, Triggers, Collections API's.

Ability to deal with trouble shoot technical issues and problems, monitor progress against plan, and communicate status to Leads.

Created tables, Correlated sub queries, Nested Queries, Views, Materialized views, Indexes for Business Application Development.

Provided technical assistance for development and maintenance of AutoSys and related scheduling solutions.

Support and work on Defining and Building the Essbase Cube and Running the Essbase Load Process

Ability to develop comprehensive test plans, scripts and cases and perform unit and comprehensive testing with system developers to ensure overall functionality and technical quality of deliverables.

Designed Database Triggers for audit and validation purpose.

Involved in optimizing database performance by analyzing database objects, creating indexes, creating materialized views etc. Performed SQL tuning using Explain Plan, Tkprof, Hints and indexes.

Used Exception Handling to handle runtime & user defined exceptions.

Debug HTML for get SQL to resolve performance issues

Provided on call support, solved the user issues and made necessary changes.

Plan production deployment activities, ITSM processes, raise RFCs and coordinate with various teams like DBAs, Unix etc.

Resolving Production severity Issues in time and meeting the SLA.

All Scripts are automated using UNIX scripting to run the jobs as per schedule.

Environment: Oracle 11g, SQL, PL/SQL, SQL*Plus, Data Warehouse TOAD 9.0, UNIX, Autosys,BO

Client : Cisco jasper San jose ca

Role : Lead Oracle pl\sql Developer

Duration : May 2016 – DEC 2016

Work with Billing DB team to tune procedures and packages

Figure out which SQL taking long time by putting long into procedure

Used Global temporary table to improve performance

Used merge statement to improve performance

Created new index to improve performance

Used pipeline function to improve PGA utilization

Convert PLSQL code into SQL, many place to reduce context switching between pl sql and SQL engine

Used parallel hint for parallel processing

Removed older oracle hint and place new hint

Used exist clause in place of IN clause in SQL to improve performance

Environment: Oracle 11g, SQL, PL/SQL, SQL*Plus, TOAD 9.0, UNIX,GSNOW,JIRA

Client : eBay, San Jose, California

Role : Tech Lead (Oracle pl\sql unix)

Duration : August 2015 – April 2016

Responsibilities:

Support MyHR application

Designed archival process(write Pl SQL store procedure using FORLL )

Wrote store procedure for Automate and design year end process

Developed PL SQL package for Report using Ref cursor and dynamic SQL

Debug exiting PL SQL code for Resolve data integration issues

Generated trace file to identify bottleneck

Created index on table to get better performance

Implemented transparent data encryption(TDE)

Closely worked with ETL TEAM

Load data form XLM file to Oracle database table

Unix Shell scripting for data integration

Debug and Fix day to day production issues

Worked on ALM tickets( enhancement tickets)

Took care of deployment (code checking checkout on GIT)

Release management on plutora for monthly release

Environment: Oracle 11g, SQL, PL/SQL, SQL*Plus, TOAD 9.0, UNIX,java

Client : Marriott Ownership Resorts,Inc (MORI), Orlando, Florida

Role : Tech Lead (Oracle pl\sql unix) and Transition manager

Duration : Jul 2013 – August 2015

Responsibilities:

Created various SQL and PL/SQL scripts for verification of the required functionalities.

Analyzed the Business requirements of the project by studying the interface Control document.

Involved in database development by creating Oracle PL/SQL Functions, Procedures, Triggers, and Packages.

Worked with different types of sub queries (Inline, correlated) for retrieval of data from multiple tables.

Created database objects like Tables, Views, Materialized views, Procedures, Packages using Oracle tools like SQL* Plus.

Extensively used FORALL and BULK COLLECT to fetch large volumes of data from table.

Create and run Batch files on regular basis.

Created and Scheduled Jobs from time to time.

Created Records, Tables, Collections (Nested Tables and Varrays) for improving performance by reducing context switching.

Created number of database Triggers according to business rules using PL/SQL.

Used PL/SQL tables, PRAGMA AUTONOMUOS_TRANSACTION to avoid mutating problem in database trigger.

Wrote code Exception handling to trouble-shoot PL/SQL code.

Performance Tuning of Databases, Oracle instances, and Applications. Finding Performance bottlenecks using STATSPACK, EXPLAINPLAN, SQLTRACE, TKPROF, AWR, ADDM. Involved in code reviews of other team members.

Involved in Technical Documentation, Unit testing, Integration testing and writing test plans.

Installation, Configuration and Administration of Oracle 11g RAC and Oracle 10g RAC.

Implementation of High Availability solutions with Oracle 11g RAC, 10g RAC, Standby Database (Active Data Guard), Replication.

Process activity & directly reporting to sr.Delivery Manager

Debug HTML for get SQL to resolve performance issues

Developed report using crystal reporting tools and support existing report.

Designed, prototyped and wrote coding standards for full ADA Section 508 and WCAG 2.0 AA compliant applications

Co-ordinated with team and took team meetings and resolved conflicts

Worked as transition manager .

Environment: Oracle 11g/9i, SQL, PL/SQL, SQL*Plus, TOAD 9.0, UNIX

Client : KPMG, Pune India

Role : Tech Lead (Oracle pl\sql unix)

Duration : May 2012 – Jun 2013

Responsibilities:

Risk Management and Finance methodologies

Created Custom Packages, Procedures, and functions according to the client’s requirement.

Created database objects like tables, views, procedures, packages using Oracle tools like SQL* Plus, PL/SQL and TOAD.

Modified existing codes and developed PL/SQL packages to perform Certain Specialized functions/enhancement on oracle Application.

Worked on performance tuning and used Explain Plan, TKprof and SQL trace for tuning the queries.

Created Indexes and hints for faster retrieval of the customer information and enhance the database performance.

Involved in loading the data loads coming from flat files into staging tables in the Oracle Data base using SQL* Loader and Kron shell scripts.

Used PL/SQL Wrap Utility to encrypt PL/SQL source code and to make the code portable to any platform.

Used version control PVCS which can automatically make copies of work on a drive or machine other than your development drive.

Worked with Bulk Collects to improve the performance of multi-row queries by reducing the context switching.

Used the pragma Exception_Init to name the system exceptions that are raised by the program.

Created Kron shell scripts for automating some production tasks.

Worked on forms that were already developed for fixing the bugs and also involved in changing the functionality of the forms based on the client’s requirement.

Designed, developed and tested Reports according to the client requirements

Environment: Oracle 10g/9i, SQL, PL/SQL, SQL*Plus, TOAD, UNIX

Client : Idea cellular Pvt Ltd Pune india

Role : Tech Lead(Oracle pl\sql unix)

Duration : Oct2011-May2012

Responsibilities:

Responsible for Analysis design and implementation of automation script (data extraction, correction,upload then generate report and FTP) ETL process using shell script

Created new Tables, Indexes, and Sequences wherever is needed.

Designed and Created several Customized Reports using Shell script and Sql.

Developed Procedures and Triggers for Oracle Forms and Reports.

Created PL/SQL Stored Procedures, Functions, Triggers, Packages and Dynamic SQL for implementing business logics.

Developed PL/SQL packages using indexes, joins, bulk collects and Plsql Tables.

Developed control files for SQL*Loader and PL/SQL programs for loading and validating the data into the Database.

Created UNIX Shell scripts for automating the execution process.

Created PL/SQL scripts to extract the data from the operational database into simple flat text files using UTL_FILE package.

Used Exception Handling extensively for the ease of debugging and displaying the error messages in the application.

Implemented features including materialized views for better performance of summary tables, Autonomous Transactions, Coding Dynamic SQL Statements

Performance Tuning of Databases, Oracle instances, and Applications. Finding Performance bottlenecks using STATSPACK, EXPLAINPLAN, SQLTRACE, TKPROF, AWR, ADDM.

Coordinated with DBA in improving Database Performance and involved in SQL Tuning by creating indexes, rebuilding Indexes using Explain Plan, SQL Trace and TKPROF Tools.

Used Autosys corntab for scheduling jobs.

Wrote algorithms to perform arithmetic operation to automate the process as per the business need.

Unit testing various PL/SQL blocks and GUI testing the user interface screens.

Involved in developing test plans and test cases based on high-level and detailed designs.

Environment: Oracle 10g/9i, SQL, PL/SQL, SQL*Plus, Data Warehouse, TOAD 9.0, UNIX

Client : AT&T Pune india

Role : Sr Technical associate

Duration : APR 2009-Oct 2011

Responsibilities:

Interacted and coordinated with client to Understand functionalities of existing legacy system And propose system.

Designed database for ACTP and Explain ER diagram(data modeling) to development team

Wrote complex sql and tune the same

Proposed solution to gain response time for https://datacommerce.sbc.com/dcomm-ui/browseMain.do

Deleted large amount of data from DB under different constraint like inactive and older product & carts

Fine tune existing query to gain performance using proper index and hint.

Implemented data upload process for https://datacommerce.sbc.com/dcomm-ui/browseMain.do

Reduced product search time up to 2 sec.

Wrote algorithms to automate the process as per the business need.

Environment: Oracle 10g/9i, SQL, PL/SQL, SQL*Plus, TOAD 9.0, AIX,SVN

Client : OPENWAVE Pune india

Role : Sr Technical associate (Pl\SQL unix)

Duration : DEC-2008-APR 2009

Responsibilities:

My responsibility as a database developer was to reduce reports response time as per SLA for that We did following works

Designed aggregate table in such a way that table could store different time interval data.

Designed procedure, package to populate data into aggregate table.

Created one top level procedure in which we implemented below logic for different procedure execution.

Different time interval procedure will execute once, in its time interval at the end of interval.

Loaded old data into aggregate table we have below option

Option 1:

Run aggregate data procedure with’ F’ parameter value Aggregate data (‘F’);

This will truncate data from all six aggregate tables and aggregate log table and reload all data from min date to max date from the corresponding fact and session tables.

Catch up old data while system was down for few days

TOP level procedure has the functionality of catch up old data if system is down for few days it will pick up the last execution date from log aggregate table and process the data till system date

(Start date=Last execution date +1 from log table, to date=system date)

Logic for full and incremental update

Procedure with name as AGGREGATE_DATA (FORI VARCHAR2) top level procedure is created which can be used for updating the full data and partial data (incremental) into the aggregate tables. This procedure takes different parameters like F (for full) and I (for incremental). Based on the input provided to the procedure, it can update the data in aggregate tables.

Setup development environment (installation of oracle client BO business object on Linux) and created RPM for database change as a part of main line

Environment: data ware house Oracle 10g/9i, SQL, Data Warehouse, PL/SQL, SQL*Plus, TOAD 9.0, UNIX

Client : Techmahindra Pune india

Role : Sr Technical Associate (Pl\SQL unix)

Duration : APR2008-DEC2008

Responsibilities:

Created database object tables, views, Materialized views, procedures, packages using Oracle tools like PL/SQL, sql * plus.

Involved in database development by creating Functions, Procedures, Triggers, and Packages using TOAD for accessing, inserting, modifying and deleting data in the database.

Used Ref cursors and Collections for accessing complex data resulted from joining of large number of tables.

Worked in creation of Sequences for automatic generation of students registering.

Created indexes on tables for optimizing stored procedure queries.

Participated in Performance Tuning of SQL queries using Explain Plan to improve the performance of the applications.

Worked extensively on exception handling for handling errors using system defined exceptions and user defined exceptions.

Created Email package using oracle supplied UTL_SMTP Package was used to send emails

Designed the forms for front end interface for the users using Forms 6i.

Developed number of Reports like parameterized reports, cross tab reports, and sub reports according to the client requirement using Reports 6i.

Created Unix Shell Scripts to automate the execution process.

Documented all the technical work for future references and customizations.

Database integration, data mapping between different applications.

Database migration from PS8 to PS9(data mapping and data move form source to destination)

Environment: Oracle 9i, SQL, PL/SQL, SQL*Plus, TOAD

Client : Andslight pune india

Role : Database developer

Duration: July2005-April2008

Responsibilities:

Created various SQL and PL/SQL scripts for verification of the required functionalities.

Created Triggers, Procedures, Functions, Packages to provide backend security and data consistency.

Worked in creation of views for hiding of actual Tables and Queries.

Created PL/SQL functions to facilitate business functions and calculations.

Involved in coding and testing various Packages, Procedures and Functions.

Was responsible to maintain the dependencies of all Procedures, Function, Packages, Views and Triggers.

Implemented complex business rules with the use of integrity constraints and Database Triggers.

Developed Packages to validate the business rules on the data while inserting, updating data into tables.

Tuned and Optimized the SQL statements using Explain Plan and Sql trace utilities.

Environment: Oracle 9i, SQL, PL/SQL, PL/SQL Developer, unix

Professional Summary:

Education Qualifications:

Technical Skills:

Experience Summary:

Contact this candidate