Senior Oracle Etl/plsql Developer Resume

SUMMARY

Over 6+ years of experience in Application development and Production support using Oracle SQL, PL/SQL, FORMS and REPORTS.

• Expertise in performing Data export, Import and various operations using TOAD, SQL DEVELOPER.
• Extensively worked in Client-Server application development using Oracle 11g/10g/9i, FORMS 10g/6i, REPORTS 10g/6i, Oracle Import and Export Utilities.
• Experience in application development using various advanced features of Oracle like Bulk Collections, Autonomous Transactions, Dynamic SQL, Object types and Records.
• Supported Advance PL/SQL using Cursor, REF Cursor.
• Developed Complex trigger using PL/SQL for data integrity and enforcing business rules.
• Extensive experience in data migration techniques using Oracle External Tables, SQL* LOADER, UTIL file loader and batch processing.
• Experienced in data analysis, data modeling, development, testing and documentation of projects.
• Carried out testing in test database for upgrading/migrating from Oracle 9i to Oracle 10g.
• Experience in developing logical and physical database design, data modeling using Erwin & Informatica (Power Center 6.1).
• Involved in creating mappings, transformations in Informatica, tuning & monitoring workflows.
• Extensively worked on the Extraction, Transformation and Load (ETL) process using PL/SQL to populate the tables in database.
• Experience with DBA activities like creating tablespace, granting and revoking privileges on database objects.
• Improve the performance of the slow SQL queries by implementing indexes, using FORALL and BULK COLLECT.
• Extensively involved in Oracle SQL, PL/SQL tuning in OLTP and DWH applications using STATSPACK, Explain Plan, TKPROF, Hints in Oracle 9i.
• Experience in Installing Software’s and Configuring Databases.
• Experience in UNIX Shell Scripting for batch processing.
• Strong Trouble-shooting, Problem solving, Analytical & Design Skills.
• Comfortable to work with a team or work individually.

SKILLS

RDBMS Oracle 11g/10g/9i
Oracle Tools SQL * Loader, TOAD, SQL Developer, SQL Tool, SQL Navigator, Developer2000 (REPORTS 10g/6i, FORMS 10g/6i), SQL*Plus, Discoverer
ETL Tools Informatica Power Center 8.6.X/6.1, Data stage
Data Modeling Tools Erwin 4.5/4.0/3.5, Designer 2000, Microsoft Visio 2003
Data Analysis Requirement Analysis, Business Analysis, Detail Design, Data Flow Diagrams, Data Definition Table, Business Rules, Data Modeling, Data Warehousing, System Integration.
Programming Lang Unix Shell Scripting, Perl Scripting, XML, C, C++,Java, HTML,XML
Environment Sun Solaris 2.6/2.7/2.8/8.0, Red Hat Linux ,Windows NT/ 95/ 98/ 2000/ XP,MS DOS 6.22

EXPERIENCE

Confidential, FL. MAY ‘11- OCT’12
Sr. Oracle PL/SQL Developer

Confidential, is the largest publicly held personal lines property and casualty insurer in America. All State is traded company which is listed on the New York Stock Exchange Under the trading symbol ALL. It was number 93 on the Fortune 500 list of largest companies in America. The company is reinventing protection and retirement to help households insure a better plan for future. Allstate products include auto, home, life and retirement insurances for consumer’s access.

PROFESSIONAL EXPERIENCE

Confidential, Minneapolis, MN
(PLSQL) Developer May 2012- Till now

Consumer Pricing Initiate (CPI) has several work streams. Confidential, is one of the top 1000 sites in the world and is in the Retailers category. The primary objectives of the Data work stream are to create one common version of Quote and Policy data for Auto. Today in PL (Personal Lines) Applications, policy and quote data reside in multiple data marts, making itdifficult to analyze performance without significant manual intervention to pull all of the data, even prior to analysis. This project is a great example of Operational Excellence, as it takes the basic quote data 'manufacturing' process out of the hands of Marketing and other functions, and creates an automated and centralized source for the quote data. Creating this common source for quote data meansthat leveraging the same data resulting in much more consistency around respective analytic processes and reporting. Additionally, this project willalso result in common definitions of the various data points (ie : responses, quotes, conversions, issues) which will continue to support consistency across functions. In addition to these operational efficiencies, this new data source will include attributes that marketing have not previously had access to, therebyallowing for potentially greater analysis opportunities to understand various program results.

CPI-CAQ (COMMON AUTO QUOTE)
This Project is consolidating 3 data marts in to single data mart using Oracle PLSQL/Informatica

CPI-CAP (COMMON AUTO POLICY)
This Project is consolidating 3 data marts in to single data mart using Oracle PLSQL/Informatica

Responsibilities :

• Involved in gathering elements from different data marts to consolidate in to single data mart.
• Involved in analysis of data marts to understand feasibility and complexity.
• Developed PLSQL Packages/Procedures/Functions to populate data in to stage tables.
• Developed Informatica mappings to move data from stage to target tables.
• Wrote complex SQL queries on oracle tables to pull data for analysis purpose.
• Experience in Working with fact and dimension tables.
• Worked on dimensional modeling.
• Involved in ETL Development using native Oracle tools (SQL*LOADER, Oracle PL/SQL, Informatica).
• Involved in full Software Development Lifecycle (SDLC)
• Perform ETL Testing by validating data.
• Created Tables, Complex views, Materialized views, Force views, synonyms, Indexes.
• Created Partition tables by range using date column to increase the performance.
• Created public database links to access the data from remote databases.
• Scheduling Jobs through Crontab.
• Wrote database triggers for automatic updating the tables.
• Worked on SharePoint portals to share documents (check in/check out) Process.

Environment:
C, C++, UNIX Shell Scripting, Forms , PL/SQL, Oracle 10g,11g. Informatica-8.6, SQL, PLSQL, Toad.

Confidential, Tulsa, Oklahoma
Senior Oracle ETL/PLSQL Developer Oct 2010- Apr 2011

Confidential, is headquartered in Tulsa, Oklahoma. Driven by the mission, "Value Every Time", the Company\'s brands, Dollar Rent A Car and Thrifty Car Rental, serve value-conscious travelers in over 80 countries. Scope of the project is to create an EDW for 4 business units available and provide BI functionality to their sites. Project is divided into multiple interfaces like Location, Product, Product Hierarchy, Store Plans, Merch Plans, and Allocation. Source is called JESTA for all the interfaces is Oracle Database with Front end Forms. Data from JESTA is Moved into Views and Data is Populated into ODS from the Views. Data from ODS is sent to EDW and EP (Enterprise Planning).

• Developed PL/SQL Packages, Procedures and Functions accordance with Business Requirements for loading data into database tables.
• Created Materialized Views and partitioning tables for performance reasons.
• Worked on various backend Procedures and Functions using PL/SQL..
• Developed UNIX shell scripts to perform a nightly refresh of the test system from Production databases.
• Coordinate with the front end design team to provide them with the necessary stored package and procedures and necessary insight into the data.
• Developed Informatica mappings to move data from stage to target tables
• Involved in all phases of the SDLC for Designing & giving recommended approach to satisfy the requirements.
• Development of C modules for activation, deactivation and modification of these plans in Clarify Front End
• Created SQL*Loader scripts to load data into the temporary staging tables
• Designing Tables, Constraints, Views, and Indexes etc. in coordination with the application development team
• Used TOAD, PL/SQL developer tools for faster application design and developments
• Developed procedures using Dynamic SQL..
• Developed database objects including tables, Indexes, views, sequences, packages,triggers and procedures to troubleshoot any database problems
• Tuned complex Stored Procedures for faster execution and Developed database structures, according to the requirements
• Created Materialized Views and partitioning tables for performance reasons.
• Resolving the issues on priority basis.
• Responsible for Analyzing and Implementing the Change Requests .
• Involved in handling the changes in compiling scripts according to the database changes.

Environment:

Confidential, Houston TX Feb 10 to Oct 10
Oracle Developer (SQL, PLSQL, UNIX)

Through its innovative Relationship Developer Intervention (RDI) Program, RDIConnect Gained a worldwide reputation for Designing Family - based programs. Currently, RDI Connect provides program for an entire range of developmental Difficulties

Accomplishments:

• Manage SQL server database hosted on the IIS remote server
• Develop backend modules (procedures, functions and triggers) meeting business requirements using SQL and PL/SQL
• Designing and building of business reports using SQL Server Reporting Services, SSRS 2005
• Scheduling and running jobs in the background, and taking regular backups of the systems and recover data during failures
• Involved in the extraction of data from different sources like OLTP server, flat files, excel and legacy databases
• Design and develop mappings with required transformations and load the data into the warehouse server for analysis using Informatica Power Center 8.6.1, IDE (Informatica Data Explorer), IDQ (Informatica Data Quality)
• Writing test cases for the code analysis and bug issues before deployment on to the production server
• Investigating and solving payment, registration, customer and business related backend issues
• Customization and maintenance of company's web site built using Telligent API 5.6 hosted on the remote server IIS 6/7.

Environment

Confidential, Albuquerque, NM Oct 09 to Jan 10

Programmer Analyst II

• Installation, Creation and support of Oracle 8i database environments.
• Involved in the maintenance and enhancement of the application.
• Maintaining Logical and Physical structure of the database.
• Creating tablespaces, tables, views, scripts for automatic operations of the database activities.
• Coded various stored procedures, packages and triggers to incorporate business logic into the application.
• Used the technical document to design the database tables.
• Data conversion from flat file to intermediate tables using SQL*Loader,Data mapping
• Created control files for SQL*Loader.
• Gather Database and User Level Statistics and Analyze Overall System Performance.
• Designed/developed tables, views, various SQL queries, stored procedures, functions.
• Monitor database and system backups, developed Oracle Stored procedures, functions, packages, and triggers that pull data for reports.
• Develop UNIX shell scripts to administer the system.
• Extensively worked with Dynamic SQL, Composite data types & Global Temporary Tables.
• Responsible for purging and archiving the production databases.
• Used UTL_JOB to automate the PL/SQL procedures and packages.
• Involved in Interacting with users, error fixing by enhancing PL/SQL packages, stored procedures & functions.
• Data conversion from flat file to intermediate tables using SQL*Loader and also created control files for SQL*Loader.

Environment:

Confidential, Vizag AP Aug'07 - Sept'09
Oracle PL/SQL Developer,

Responsibilities: