We provide IT Staff Augmentation Services!

Oracle Consultant Resume

Chicago, IL

SUMMARY:

  • More than 10 years of experience with proven ability in the area of Client/Server and Distributed Multi - tier Application Development, Data modeling, Database Warehousing, Database Design, Implementation and Support using Oracle technology and ETL tools on different platforms.
  • Extensive experience in Oracle 12c/11g/10g/9i/8i and hands on experience writing SQL queries and PL/SQL
  • More than 10 years proven experience in PL/SQL that includes Cursors, Ref-cursors, Bulking techniques, Procedures, Functions and Packages, Oracle Supplied Packages, Collections, Exception Handlers, Cursors, Partitioned Tables, Triggers, Materialized Views, Table Indexing, SQL*Loader and using latest features to optimize performance.
  • Familiarity with ETL solutions using Commercial ETL products like Informatica
  • Expertise in Data Conversions/integration/transformation/Migration from Legacy System of various sources (flat files, Oracle, Non Oracle Database, Mainframe) to Oracle Database Using Gateways, PL/SQL procedures, SQL*LOADER, Export/Import, UTL-FILE, Cloning etc.
  • Experience in using Oracle DBA utilities/tools and 3rd party tools like Export, Import, Data pump, OEM, SQL*LOADER, RMAN, SQL*PLUS, SQL Developer, TOAD etc.
  • Experience in Query Optimization, Debugging and Tuning for improving performance of the applications using Explain Plan, SQL Trace, TKPROF, OEM Advisors
  • Good understanding of Relational database design. Experience in Data Mapping, Data Analysis, Data flow Diagram, ERD Diagrams using Excel, Visio, Erwin etc.
  • Extensive experience in all phases of Development Life Cycle including requirement gathering and Analysis, Database design, Database development, Application development, Test, Support, and Best Practices.
  • Experienced with Agile methodology
  • Experience in UNIX SHELL Scripting (Korn), PERL and scheduling using UC4, Control-M, Oracle OEM and Unix CRON for automation.
  • Familiarity with Core Java, JDBC, Hibernate, Java for Oracle
  • Strong programming, debugging and problem solving skills and ability to translate customer requirements into technical solutions by utilizing business insights
  • Experienced in interacting with Business users, Business Analysts, IT leads and developers in analyzing business requirements and translating requirements into functional and technical design specifications.
  • Ability to work within an aggressive and demanding project schedule and environment.
  • Excellent Interpersonal and Communication skills, coupled with strong technical and problem solving capabilities.

TECHNICAL SKILLS:

  • Oracle 12c,11g,10g, 9i, 8i, 8
  • OEM
  • RMAN
  • TOAD
  • SQL/PLUS
  • PL/SQL
  • VISIO, ERWIN
  • STATPACK, AWR
  • SQL Developer
  • DB2 & IMS on Mainframe
  • JCL, VSAM, TSO/ISPF, CICS
  • Core Java, JDBC, Hibernate
  • Access on Windows
  • EXCEL, WORD, POWER POINT
  • SQL Server 2012
  • HP HPUX
  • Solaris
  • IBM Mainframe
  • Linux
  • Windows
  • AIX
  • SQL
  • Unix scripting
  • Java
  • PL/SQL
  • COBOL
  • JCL
  • Informatica
  • SQLPLUS
  • PLSQL

PROFESSIONAL EXPERIENCE:

Confidential, Chicago, IL

ORACLE Consultant

Responsibilities:

  • Participated in developing and implementing new features/enhancements for over 5 quarterly releases for Confidential . Typical enhancement involve adding/modifying additional tables and/or columns, mapping them data source, implement transformation meta-data rules and extraction criteria, develop code(shell scripts, sqlplus sql scripts, plsql pkg/procedures/functions/triggers) and rigorous unit and system testing
  • Converted Confidential Software from Oracle 11g to 12C. Worked with DBAs to sort issues with installation, configuration, and tuning of 12C database on new server. During migration, maintained and synchronized and tested11g and 12c software branches for 5 release cycles. This project lasted for 6months due to installation and server specific issues.
  • Participated in tuning 12c software execution on new server - worked with DBAs to tune database configuration. The typical execution was reduced from 12 hours to 3 hours.
  • Worked with spatial data, Oracle’s sdo functions, resolved issues with different precision/tolerance implemented by 11g and 12c Oracle versions. Converted usage of sdo geom.relate functions to sdo relate. Implemented solutions for different precision found in 12C. Converted Oracle’s WM CONCAT function with LISTAGG function
  • Regression tested the 11g and 12c version of Confidential software by comparing schemas and investigated discrepancies.
  • Designed and implemented a customized tool to compare over half a dozen schemas for regression testing each product release. Created similar tool to regression test 11g and 12c migration
  • Designed and implemented a customized tool to compare GDF extract files (one of products created during the Confidential release). The design involved loading free format report files to a database tables, and then use sql queries to compare data and attribute counts.
  • Involved in design review, code review, unit-testing and system testing each development assignment
  • Internal Support: Create new Jenkins Jobs for the releases. Create new software releases using GIT. Daily monitoring of Jenkins jobs and fixing job failures. Provide internal support to development team by rotation.
  • Used AGILE methodology for development. Participated in quarterly release train and daily Scrum.

Environment: Oracle 12c & 11g, SQL, PLSQL, SQLPLUS, Linux, UNIX SCRIPTING, ANT, AGILE, WORD, EXCEL, JIRA, Jenkins, GIT

Confidential, Chicago, IL

ORACLE Developer

Responsibilities:

  • Provide oracle support to the team for development, migration and deployment of quarterly releases.
  • Set up development database with production objects/software for the development cycle of new release. We have 3 development databases where releases would be developed
  • Migrate development database objects to UAT for QA testing. Use schema compare to synchronize development and UAT databases. We had 3 UAT databases that needed periodic synchronizations with multiple releases being developed and tested in various Development and UAT databases.
  • Create indexes to tune queries. Monitor non-unique indexes for their usage in production so unused indexes can be scrapped.
  • Implemented versioning using SVN/TortoiseSVN. Designed the Subversion repository to manage versioning of oracle code in production, and facilitate development cycle for releases through branches and tags. Created initial load of codebase into main trunk and created branches for development cycle for releases. Created documentation for versioning architecture, processes, common tasks.

Environment: Oracle 11g, PLSQL, SQL, SQLPLUS, TOAD, SQLDeveloper, Subversion, TortoiseSVN

Confidential, Chicago, IL

ORACLE Consultant

Responsibilities:

  • Participated in developing and implementing new features/enhancements for over 6 quarterly releases. Typical enhancement involve adding/modifying additional tables and/or columns, mapping them data source, implement transformation meta-data rules and extraction criteria, develop code etc and rigorous unit and regression testing
  • Created Detail Technical Design documents for each enhancement story for team approval and presented design and code review to the team
  • Streamlined PERL scripts to scan all logs and generate error reports. Streamlined ANT targets to modularize and parallelize code execution
  • Wrote UNIX scripts to scan software for obsolete code for removing them from software
  • Re-engineered the MAP ETL software to create temporary staging layer and removed interdependency among tables and scripts during ETL process. This was a major initiative that lasted for over 6months and I was the sole developer for the initiative. It involved dissecting and splitting up and re-writing over hundred scripts.
  • Re-engineered several modules of the MAP ETL software to de-normalize temporary tables used in staging layer
  • Involved in code review, unit-testing and regression testing all my development assignments
  • Participated in tuning and parallelizing SQL scripts for several ETL domains - I was able to achieve 20 to 50% reduction in execution time for several domains. Run time for our largest product was 18 hours when I joined the team, which was reduced to 14 hours.
  • Used AGILE methodology for development. Participated in quarterly release train, bi-weekly sprint review and daily Scrum.

Environment: Oracle 11g, Linux, PLSQL, SQLPLUS, TOAD, UNIX SCRIPTING, PERL, ANT, AGILE, WORD, EXCEL, JIRA, Mercurial

Confidential, Pittsburgh, PA

PLSQL Developer

Responsibilities:

  • Created a draft for Functional Specification document for the New Data Warehouse.
  • Created Technical Specification Documents for the New Data Warehouse - Dispatchers, Loading, Data Cleaning, Matching, Deduping, Fact Loading, Export, Alert processing, Backout etc.
  • Troubleshoot Deduping process for bugs. Redesigned deduping scripts to reduce processing time
  • Developed scripts to start window services to enable third party loader software
  • Streamlined jobs so output extracts are created in specific client folder, and than zipped and moved to target folder
  • Redesigned patient matching scripts to cut down processing time from several hours to few minutes
  • Created scripts to scan and replace columns with designated values ie remove trailing escape character so csv file does not mess up data load.
  • Developed scripts to deliver delta extracts to the client instead of full table extracts
  • Enhanced file-watcher program to look for certain file masks, and create ready trigger files so they can signal need to kick off certain jobs

Environment: Oracle 11g, Linux, PLSQL, UNIX SCRIPTING, SQLDEVELOPER, MS SQL Server 2012, SQL Server Management Studio (SSMS ), WORD, EXCEL, JIRA

Confidential, Mettawa, IL

PLSQL Developer

Responsibilities:

  • Acted as On-site team leader for the migration team. Worked with off-shore team leader and off-shore technical teams.
  • Designed data migration and transformation specifications for various functional modules of GSD software. Identified tables impacted by all functional modules. Identified data relationships and data mapping to tables, functionality and screens. Identified extract criteria and migration strategies and data transformation rules and presented them to management for user acceptance.
  • Created PLSQL extract, transformation and load templates for quick development cycle.
  • Created all Unix scripts for the project for running migration steps.
  • Developed over 100 PLSQL procedures to accomplish data migration tasks and unix scripts to execute them.
  • Created all Oracle DDL scripts for the project for setting up staging tables, migrating staging tables to application for UIT,SIT,UAT and Production runs.
  • Created Test Plans, worked on defects during SIT and UAT, participated in code and test plan reviews
  • Worked with various stakeholders to create 100s of different adhoc data extracts and reports so they can make necessary steps to facilitate migration of data.
  • Wrote over 100 adhoc sql queries for various test teams (SIT and UAT) to help them validate data migration.
  • Participated in tuning of Oracle instance using AWR reports. Provided several recommendations to management for Oracle database tuning, some of which were implemented.

Environment: Oracle 9i and 10g, IBM AIX 6, PLSQL, KORN UNIX SCRIPTING, PERL, CORE JAVA, JAVA JDBC, SQLDEVELOPER, TOAD, SSH, WORD, EXCEL, MKS, QC, RAD

Confidential, Milwaukee, WI

Developer

Responsibilities:

  • Coding, Unit testing and defect management of backend data interfaces to feed data to Web Dashboards from the databases.
  • Worked on general account data inquiry and various business account inquiry modules for Service delivery Team

Environment: Mainframe, ISPF, DB2, CICS, BMS, VSAM, Oracle 10g, PLSQL

Confidential, Naperville, IL

Sr. Oracle Developer

Responsibilities:

  • Creation of database objects like tables, synonyms, views, database links, indexes, sequences.
  • Create forms and Reports for reporting purpose.
  • Generated and maintained Keys, Constraints and Relationships between the tables for database instances.
  • Involved in database development by creating Oracle PL/SQL Functions, Procedures, Triggers, Packages, Exception Handling, Records and Collections.
  • Optimized critical queries to eliminate Full Table scans and reduce Disk I/O and Sorts.
  • Generated shell scripts for loading data from Flat Files to database tables using SQL*Loader.

Environment: Oracle Database 11g, Solaris, PL/SQL, VISIO, TOAD, SQL Developer, OEM

Confidential, Elgin, IL

Oracle Developer

Responsibilities:

  • Designed and created complex PL/SQL code for Converting/Migrating data from Mainframe, DB2 and Flat files into Oracle database tables for in-house Insurance Applications and Data Marts.
  • Utilized business rules for data analyzing and cleansing.
  • Created and modified database objects i.e. Tables, Indexes, Views, Synonyms, Database Links, Materialized Views, Partitioned Tables
  • Analyzing Data & Modifying Tables, enforcing constraint on tables.
  • Developed and modified PL/SQL packages, functions, procedures, triggers.
  • Oracle Job Scheduling and setup CRON jobs in Unix for scheduling Batch jobs.
  • Wrote UNIX Shell Scripts for automating the daily/weekly/monthly data process
  • Wrote shell scripts to transfer files using FTP to all the UNIX servers at one time for daily data processing.
  • Extract data into Oracle table using SQL*LOADER and Transparent Gateways for DB2.
  • Developed Complex SQL Queries, Dynamic SQL, Unix Scripts
  • Worked on performance tuning issues for SQL queries and Database.
  • Used DBMS PROFILER package for identifying Performance bottlenecks in PL/SQL packages and also for identifying the package behaviors.
  • Used Collections, Bulk Binds to improve performance by minimizing the number of context switches between the PL/SQL and SQL engines.
  • Fine Tuned procedures for the maximum efficiency in various schemas across the database using Oracle Hints, Explain plan and Trace sessions for Cost based optimization
  • Registered PL/SQL packages as concurrent programs which allowed users to execute them on-demand.

Environment: Oracle Database 11g/10g, Solaris, PL/SQL, VISIO, TOAD, SQL Developer, OEM, Mainframe, DB2, VSAM, IMS, JCL

Confidential, Missouri

Oracle Analyst

Responsibilities:

  • Involved in supporting and interfacing with legacy systems for Childcare and Family Services systems.
  • Responsibilities included writing new programs, making enhancements, producing reports, documenting systems, production support, trouble-shooting.
  • Involved in gathering business requirements, and designed the process flow
  • Prepared the Technical Design Document based on the Business Requirement for various modules.
  • Involved in identifying the file formats, and structures from various Applications for processing.
  • Involved in design of the Database table structures related to the various file formats.
  • Writing complex stored procedures/triggers and functions using PL/SQL and optimized for maximum performance.
  • Involved in the Table design and re-structuring of the key Tables.
  • Developed complex procedures to handle errors and exceptions at database level using PL/SQL.
  • Analysis, design and development of Database procedures, functions and packages using PL/SQL as per the business process.
  • Used Ref-cursors, Collections and Bulk Collects for memory intensive processes and to improve traceability.
  • Created Indexes and Materialized Views to improve the overall system performance.
  • Fine Tuned procedures for the maximum efficiency in various schemas across the database using Oracle Hints, Explain plan and Trace sessions for Cost based optimization.
  • Developed Various Shell Scripts for automation & validation.
  • Worked on import (Impdp)/export (Expdp) using Oracle Data pump.

Environment: Oracle Database 10g/9i, HPUX, PL/SQL, VISIO, TOAD, SQL Developer, OEM, Mainframe, DB2, VSAM, IMS, JCL

Confidential, Naperville, IL

Oracle Developer

Responsibilities:

  • Created Objects for Integration project and Data Warehouse - Fact tables, dimension tables, table partitions, sub-partitions, normal and bitmap indexes, summary tables, materialized views, stored packages, functions and procedures with parallel queries, rebuilding local indexes
  • Converting/Migrating data from Mainframe, Flat files into Oracle database tables for Data warehousing.
  • Created PL/SQL based ETL processes to transfer data from Mainframe legacy sources to staging area and from staging area to Oracle Database.
  • Prepared shell scripts and CRON jobs to run in batch for Extracting Data.
  • Creating database objects And Developing and modifying PL/SQL packages, functions, procedures, triggers.
  • Wrote over 400 stored procedures, functions and triggers to keep data in Oracle database synchronized with Mainframe DB2 database
  • Extract data into Oracle tables using SQL*LOADER and Transparent Gateways for DB2
  • Used SQL*Loader, UTL FILE package and External Table concepts for loading data into the Database tables through an automated process.
  • Used External Tables to load test data from CSV files to the database tables from Front end
  • Migrate oracle schemas and data among multiple development servers. Keep all databases synchronized during development
  • Writing Unix Shell Scripts to load the data as per the Business functionalities.
  • Provide adequate documentation on unit testing.
  • Created Unix Shell Scripts for archiving tables for a particular run & automated scripts to RUN STATS(ANALYZE) on various tables & create reports on the same.
  • Developed a generic tool to Use UTL FILE Package to create processed data file for the clients.
  • Migrated large tables using Oracle Data pump for import/export.
  • Used Database triggers for maintaining audits.
  • Created Indexes and Materialized Views to improve the overall system performance.
  • Writing and tuning Complex SQL Queries.
  • Data Analysis, Data Mapping, Data Modeling, ERD using Excel Spreadsheets, Visio etc.
  • Improved query performance by query optimization - tracing the query execution plan (explain plan), OEM.

Environment: Oracle Database 10g/9i, PL/SQL, TOAD, OEM, VISIO, Solaris, IBM Mainframe, DB2, VSAM, JCL, CICS.

Confidential, Chicago, IL

Oracle PL/SQL Developer

Responsibilities:

  • Worked as a technical consultant to support Oracle Development and integrate external source of data into Oracle Databases residing on UNIX platform.
  • Created Custom Stored Procedures, Function and SQL Scripts to load data into Oracle Database from different sources. Clean and transform data to provide quality information, and manage metadata.
  • Created/updated Data Extract procedures to transfer data from external and legacy sources to staging area and from staging area to data marts
  • Tested and took measures for maximum performance utilizing optimization hints, processors, storages and query optimization.
  • Utilized business rules for data analyzing and cleansing.
  • Created Complex SQL Queries.
  • SQL tuning for performance improvement. Used EXPLAIN PLAN, OEM Advisors
  • Design Data Conversion/Interface with the use of SQL*Loader and EXPORT/IMPORT.
  • Actively participated with the team for the purpose of defining project scope, sizing & technical design to establish technical direction regarding technology, platforms & development tools, development standard and guide lines.

Environment: Oracle 8 and Oracle 8i on Solaris Platform, PL/SQL, OEM, TOAD, Unix Scripting, MS EXEL, DB2, COBOL, CICS, JCL

Confidential

Oracle Developer

Responsibilities:

  • Worked as a technical consultant to support installation of the proprietary Marketing software at various client sites.
  • The proprietary software used Oracle Datamart as a backend database. I provided programming support to the clients for developing backend Datamart thru programming interfaces and data migration from client applications and mainframe databases to the Oracle Datamart
  • Created staging area for accumulating periodical data from various data sources for Data mart creation, Extracting data from different source, Transformation data in the Staging area and load data into the Oracle Database.
  • Actively participated with the client development teams for the purpose of defining size, scope, and technical design of Datamarts, technology, platforms, development tools and development guidelines.
  • Writing Complex SQL script, packages, procedures, functions and database triggers.
  • Used aggregate routines for views, summary tables, materialized views, reports etc.
  • Applied Query Optimization, Debugging and Tuning for improving performance of the applications using Explain Plan, SQL Trace, TKprof, OEM, Hints.
  • Code for Fact and Dimension table population using SQL, PL/SQL.
  • Data Analysis, Data Modeling using ERWIN and VISIO.
  • Use SQL Loader and External Table for data Load.
  • Designing and modifying of Database tables, keys, indexes, grants, triggers, packages, functions and procedures etc.
  • Created partitioned tables, partitioned indexes for manageability and scalability of the Datamarts.
  • Created PL/SQL based processes to transfer data from OLTP sources to the Datamarts.
  • Developed Unix scripts for automation of Batch processes

Environment: Oracle Database 8/8i, Solaris, HPUX, Linux, AIX, PL/SQL, Erwin, TOAD, SQL*LOADER

Hire Now