SUMMARY:

• Proficient in PL/SQL Programming - Stored Procedures, Functions, Packages, SQL tuning and creation of Oracle Objects - Tables, Views, Materialized Views, Triggers, Sequences, Synonyms­­, Database Links and User Defined Data Types.
• Well versed with advanced features like Table Partitioning, Ref Cursor, Collections, Analytical functions, Query Re-Write, Transportable table spaces, Records, Exceptions, Bulk Bind and Dynamic SQL.
• Good experience of Performance Analysis and SQL Query Tuning, while designing scalable application from scratch and maintaining existing ones, using Query Optimizer, Indexes, Execution Plans, Hints, TK-Prof and Explain Plan.
• Involved in all phases of SDLC with timely delivery against aggressive deadlines in Retail, Heal­­­thcare, Manufacturing and E-Commerce.
• Proficient in Oracle 11g/10g/9i/8i/7.3, TOAD, SQL*Plus, SQL*Loader and PL/SQL Developer.
• Expertise in implementing ETL (Extract, Transform & Load) for supporting Data Extraction, Data Transformation and Data Loading.
• Experienced in using ETL tools like Informatica (Power Center) Designer, Repository Manager, Administration console and Workflow Manager.
• Expertise working in UNIX/LINUX environment and Shell Scripting (Korn/C/Bourne).
• Experience in developing applications using Oracle Forms 11g/10g/6i and Reports 11g/10g/6i.
• Expertise in Performance Tuning, Performance Monitoring and Optimization using Oracle Hints, Explain Plans, TKPROF, AUTOTRACE and Table Partitioning.
• Extensively worked on monitoring tools like Control-M and Autosys.
• Worked in fast paced AGILE environment and attended daily SCRUM meetings.
• Experience in migration and conversion from MS Access/SQL Server to Oracle.
• Design, development and deployment of Data Warehouse solutions and COGNOS tool suite (Framework Manager, Report Studio, Query Studio, Analysis Studio, Transformer and Power Play).
• Involved in various projects related to Data Modeling, System/Data Analysis, Design and Development for both OLTP and Data Warehouse environments including Star Schema, Snowflake Schema and Normalization/De-Normalization.
• Expertise in creating new C/C++, Pro*C Batch Programs with embedded SQLs, PL/SQL procedure calls, arrays and Dynamic SQL.
• Experience in migration of source code using version control systems like Subversion (SVN), Revision Control System (RCS) and Microsoft Visual Source Safe.
• Excellent Team Player to work in conjunction with testers (QA and offshore), developers and other team members in validation and testing of complex project scenarios and in maintenance of Quality Standards of products.
• Strong Analytical and Problem Solving skills, Multi-Tasking abilities with proven experience in utilizing people and process knowledge to assist enterprises in making critical decision.

TECHNICAL SKILLS:

Databases: Oracle 11g/10g/9i/8i, SQL Server 2008/2005/2000, Sybase, DB2, MS Access

Languages: SQL, PL/SQL, Unix Shell Scripting (Korn/C/Bourne), Pro*C, Java, J2EE, C, C++

Web Technologies: JSP, Java Script, HTML, XML, HTTP

Operating System: UNIX, LINUX, Windows NT/XP/Vista/98/95, Red Hat (RH5 to RHEL 6), MS DOS, IBM AIX, Sun Solaris

Reporting Tools: Business Objects Developer Suite 5.1, Business Objects XI/XIR2/XIR3.1, COGNOS Suite, COGNOS Report Net, Crystal Reports, Oracle Reports 10g/9i/6i

Oracle Utilities: SQL*Plus, SQL*Loader, SQL Developer, TOAD

Other Tools: Informatica 9.1.6/8/6/1/8.5.1 (Power Center), Erwin 8.1/7.3, Visio 2007, Oracle Forms and Reports, APEX, Golden Gate, Autosys, Subversion, Revision Control System, BMC Control-M

WORK EXPERIENCE:

Confidential, MA

Sr. Oracle PL\SQL Developer

Responsibilities:

• Created and used Table Partitions and Sub partitions to further improve the performance while loading daily feeds into tables.
• Implemented Sub partitions, CTAS (Create Table as Select), Exchange partition to load data into target tables.
• Used techniques like direct path load, for loading the data faster and created filters in Control files to pre-process data, as part of performance tuning.
• Rebuilt large indexes and index partitions using parallel nologging mode.
• Designed and developed several complex database procedures, packages. Extensively used features like Cursors, autonomous transactions, distributed transactions, exception handling, Dynamic, pl/sql tables, bulk load methods, optimizer Hints, Cursor variables and returning of multi record set from procedures and functions
• Used Bulk collect and Forall in stored procedures to improve the performance and make the application to run faster.
• Partitioned Tables using Range Partitioning, List Partitioning and created local indexes to increase the performance and to make the database objects more manageable
• Gathered the business requirement from clients and translated the business details into technical requirements.
• Partitioned Tables using Range Partitioning, List Partitioning and created local indexes to increase the performance and to make the database objects more manageable
• Extensively worked on BULK COLLECTS, BULK INSERTS, and BULK UPDATES & BULK DELETES for loading, updating and deleting huge data.
• Used Oracle Analytical functions such as RANK, DENSE RANK, LEAD, LAG, LISTAGG & ROW NUMBER Functions for sorting data.
• Used Bind Variables while writing Dynamic SQL to improve performance.
• AUTONOMOUS TRANSACTION PRAGMA is used in the procedures to log the status when error occurs.
• Involved in performance tuning using HINTS, EXPLAIN PLAN.
• Involved in writing complex SQL Queries to implement the business requirements.
• Used SQL* Loader and UTL FILE Packages for data conversions and interfaces.
• Expertise in handling System Analysis and Business Analysis related issues when the application is in earlier stages.
• Implemented Data loading process using UNIX Korn shell scripts resolved application issues in UNIX production environment. Read and interpreted UNIX logs
• Involved in client interaction, handling change requests and implementing the application on the client side.
• Implemented various automated UNIX shell scripts to invoke PL/SQL anonymous blocks, Stored Procedures/Functions/Packages
• Implemented various UNIX scripts to generate the data in txt format
• Created Cursors and Ref cursors as a part of the procedure to retrieve the selected data.
• Coordinated with the business analysts for requirements analysis.
• Used techniques like direct path load, external tables for loading the data faster.
• Developed technical design documents by reviewing functional requirement documents.
• Involved in preparing the test scenarios and performed unit tests on PL/SQL programs.

Environment: Oracle 10g/11g, PL/SQL Developer, SQL*Plus, TOAD 10.1, SQL*Loader, Windows Server 2008/Windows 7, UNIX, Control M

Sr. Oracle Developer

Responsibilities:

• Generated DDL scripts and created and modified database objects like Tables, Views, Sequences, Functions, Procedures, Indexes, Packages, Records and Collections using TOAD.
• Involved in performance tuning using Oracle Hints and Explain Plan for existing Views and Queries.
• Improved the performance of Materialized View Refresh from 4 hrs to 25 min using WITH clause/MATERIALIZE HINT, Indexes and Bulk Processing.
• Provide quality operations support for production environment. Work with QA and provide support to provide timely technical resolutions for defects.
• Gathered the business requirement from clients and translated the business details into technical requirements.
• Enhanced a module's performance in a preexisting product by 2800% faster (reducing runtime from 16 hours to 40 mins) adopting a more efficient data accessing path than the old one.
• Used Bind Variables while writing Dynamic SQL to improve performance.
• AUTONOMOUS TRANSACTION PRAGMA is used in the procedures to log the status when error occurs.
• Created relevant staging tables to load data, identified the business validation rules. Created SQL Loader scripts using UNIX shell scripting and PL/SQL
• Tuned sql queries based on recommendations from OEM. Sql tuning performed with the help of AWR reports.
• Monitored the Applications and database using OEM and OAM.
• Build several Unix Shell scripts for PLSQL programs to schedule them on Control M
• Involved in the LOGICAL & PHYSICAL DATA MODELING of the CSPORTAL application.
• Involved in performance tuning using HINTS, EXPLAIN PLAN.
• Involved in writing complex SQL Queries to implement the business requirements.
• Used SQL* Loader and UTL FILE Packages for data conversions and interfaces.
• Used PLSQL to Extract Transform and Load (ETL) the data into Data Warehouse and Developed PL/SQL scripts in accordance with the necessary Business rules and procedures.
• Expertise in handling System Analysis and Business Analysis related issues when the application is in earlier stages.
• Involved in client interaction, handling change requests and implementing the application on the client side.
• Created various UNIX Shell Scripts for scheduling various data cleansing scripts and loading process. Maintained the batch processes using Unix Shell Scripts.
• Created Cursors and Ref cursors as a part of the procedure to retrieve the selected data.
• Coordinated with the business analysts for requirements analysis.
• Used techniques like direct path load , external tables for loading the data faster.
• Developed technical design documents by reviewing functional requirement documents in an agile environment.
• Involved in preparing the test scenarios and performed unit tests on PL/SQL programs.
• Involved in creating various utility scripts to generate log files in UNIX using shell scripts

Environment: Oracle 10g/11g, TOAD 12.6, SQL*Loader, Windows Server 2008/Windows 7, UNIX, IBM Rational Synergy 7.1, Eclipse IDE

Sr.PL/SQL Developer

Responsibilities:

• Create Detail design document based on the business requirement.
• Work with application development teams throughout the development life cycle.
• Lead or participate in Proof-of-Concept projects.
• Stay current with emerging industry trends and ability to take initiative and ownership of tasks.
• Ability to monitor batch jobs and explain complex design items through documents and white board session.
• Maintain the Hierarchical Data in the Enterprise, Accounts, Sites and Sub divisions.
• Extensively work on UNIX SHELL scripting for loading companies data into tables and ALTER the table structures according to the requirements.
• Maintain the data for QUILL Web activity which captures every single click of the user.
• Responsible for Partitioning & Sub - Partitioning Large Tables into smaller tables using Range Partitioning and List Partitioning of the data.
• Worked on MATERIALIZED VIEWS with COMPLETE, FORCE refreshes to improve the Performance of the application by making local copies of remote Tables by using both Automatic and Manual Refreshes.
• Performance Tuning of complex SQL queries using Explain Plan to improve the performance of the application.
• Extensively worked on BULK COLLECTS, BULK INSERTS, and BULK UPDATES & BULK DELETES for loading, updating and deleting huge data.
• Used Oracle Analytical functions such as RANK, DENSE RANK, LEAD, LAG, LISTAGG & ROW NUMBER Functions for sorting data.
• Involved in writing complex hierarchical queries using CONNECT BY PRIOR clauses.

Environment: Oracle 11g, TOAD for Oracle 10.5, SQL Tuning, SQL*Loader, MS Excel, MS Word.

Oracle PLSQL Developer

Responsibilities:

• Interacting with the clients to understand the requirements and Designing module wise Oracle 11g database structure and its implementation on the client server.
• Coordinated with the front-end design team to provide them with the necessary stored procedures and packages and the necessary insight into the data.
• Created the test scripts and complex queries to test the application and to quality assurance of the data.
• Wrote complex SQL queries using joins, sub queries and inline views to retrieve data from the database.
• Worked on SQL*Loader to load data from flat files obtained from various facilities every day.
• Created and modified several UNIX Shell Scripts according to the changing needs of the project and client requirements.
• Implemented Data ingestion process using UNIX Korn shell scripts resolved application issues in UNIX production environment. Read and interpreted UNIX logs
• Implemented various UNIX scripts to generate the data in CSV and txt format
• Wrote custom support modules for implementation using PL/SQL, Unix Shell Scripts.
• Involved in developing and scheduling using cron jobs to load the data into the database and export/import mechanism for data migration for bulk upload
• Involved in writing the database triggers to furnish the audit details
• Used various LOVS (List of Values) and record groups at various points in runtime.
• Created Various Form Level, Block Level and Field Level Triggers.
• Developed Master Detail reports using tabular and Group above reports.
• Troubleshooted performance issues and bugs within packages, forms, and reports using dbms output, Forms debugger, Explain plan.
• Analyzed, designed and developed Oracle forms 10g queries, layout, report triggers, parameter form, data models etc.
• Extensively involved in using hints to direct the optimizer to choose an optimum query execution plan.
• Created indexes on the tables for faster retrieval of the data to enhance database performance.
• Used Bulk Collections for better performance and easy retrieval of data, by reducing context switching between SQL and PL/SQL engines.
• Strong knowledge of Star Schema, Snow Flake Schema, Dimension and Fact table .
• Developed Unix Shell Scripts to automate backend jobs, load data into the database.
• Created PL/SQL scripts to extract the data from the operational database into simple flat text files using UTL FILE package.
• Extensively involved in performance tuning using Explain Plan, DBMS PROFILER and Optimized SQL queries, created Materialized views for better performance.
• Partitioned the fact tables and materialized views to enhance the performance.
• Created records, tables, collections (nested tables and arrays) for improving Query performance by reducing context switching.
• Used Pragma Autonomous Transaction to avoid mutating problem in database trigger.
• Provided Tier3 Application support with 24/7 production support.
• Extracted data from different sources like Oracle, Flat files, External files and transformed the data based on Business requirements and loading into Oracle target database.

Environment: Oracle 11g, SQL * Plus, TOAD, SQL*Loader, SQL Developer, Shell Scripts, UNIX Shell Scripting, Windows XP, Oracle Form Builder 10g and Report Builder 10g.

Sr. Oracle Developer

Responsibilities:

• Created heavy stored procedures for financial transactions by using procedural parameters & working with packages, blocks & exception handling.
• Designed and Developed Oracle database Tables, Views, Indexes, and Synonyms with proper privileges.
• Extensively mapped data and migrated to a new built instance using various developer tools and prepared conversion mapping documents for reference.
• Extensively developed PL/SQL Packages, Stored procedures, Triggers, and finely tuned them for performance.
• Developed PLSQL packages for generating various feeds from oracle database using UTL FILE utility in the client’s secure data transmission server.
• Extensively used PLSQL tables and Bulk loader programs for processing data and loading into oracle.
• Developed UNIX scripts for transmitting files from the secure server to the customer specified server, using various FTP batch processes.
• Create Execute and Optimize SQL queries while providing an interactive community to support the user experience by using TOAD.
• Used SQL*Loaders to download the data into database from the text files and spread sheets.
• Extracted data from the XML files transmitted to the bank and loaded them efficiently into oracle temp tables for day to day customer financial transaction reconciliation.
• Developed UNIX scripts for sending mails to the bank and the business team, when the external feeds to the bank are received.
• Mostly involved in code reviewing and performance tuning with optimization techniques in PL/SQL.
• Worked on creating Materialized Views to maintain copy of the remote data at local node.
• Solving the complicated run time errors while running the process along with DBA team members.
• Involved in supporting/fixing production defects and day to day business transaction issues.
• Used SQL Navigator, a popular tool which increases user productivity and application code quality to manage database objects, develop and debug PL/SQL and create, execute and optimize SQL queries.
• Involved in the generation of User Interface using oracle forms by extensively creating forms as per the client requirements.
• Developed various reports from scratch utilizing Oracle Reports Builder for day to day validations.
• Created various adhoc reports as of when required for the production analysis and issue fixing.
• Constant interaction with Java Developers for tuning of SQL statements, embedding them in Java code using SQL and for unit testing and other test sequence comparisons
• Create database partitions on the tables to improve the performance
• Compare database versions and produce a merged version.
• As the databases are compared, the user would be prompted to intervene in the database merge and make choices about which of the two competing versions would be put into the output version

Environment:: Oracle Database 10g/11g, Toad, SQL*Plus, SQL*LOADER, UNIX, Forms11g, Reports 10g, Rational Clear Case.

Jr. PL/SQL Developer

Responsibilities:

• Used DTS Packages as ETL tool to load data into the staging tables.
• Developed control files, SQL*Loader scripts, Oracle import & export scripts for data loading, migration and backup of data.
• Used Oracle JDeveloper to support JA Built complex queries using SQL and wrote stored procedures using PL/SQL in various API’s like Java, .Net and Hierarchical databases like Oracle and Access.
• Experience with Database Query Tuning and Performance Tuning of Reports.
• Participated in system analysis and data modeling, which included creating tables, views, indexes, synonyms, triggers, functions, procedures, cursors and packaged. Created programming code using advanced concepts of Records, Collections and Dynamic SQL.
• Provided timely Production Support by quickly analyzing the root cause and by providing alternatives to resolve issues.
• Involved in designing Business Object reports.
• Used advanced Bulk technologies (FOR ALL, BULK COLLECT) to improve performance.
• Developed numerous forms and reports and utilities using C, Pro*C.
• Involved in automation project with a heavy exposure to UNIX Shell Scripting.

Environment: s: Java, Oracle 8i/9i, SQL, PL/SQL, SQL Navigator, Crystal Reports, SQL*Loader, UNIX, Pro*C, Oracle Forms & Reports 6i, Business Objects, TOAD.