Oracle Plsql/etl Developer Resume
Providence, RI
PROFESSIONAL SUMMARY
- Over 7+ years of experience in analysis, design, documentation and development of custom applications using Oracle SQL and PL/SQL, working on various versions including Oracle Database 8i/9i/10g and 11g.
- Experience with all phases of the Software Development Life Cycle(SDLC) including analysis, Physical and Logical Design, Code Development, Testing, Implementing and Maintenance.
- Extensive knowledge of Relational Database Management Systems (RDBMS).
- Proficient in developing SQL scripts, PL/SQL Packages, Procedures, Functions, Exceptions, Views, Triggers, Grants and Synonyms.
- Experience in query level performance tuning using Explain Plan.
- Experience in loading high volume data and Performance Tuning using Partitioning and Indexes for fine tuning and optimizing SQL and PL/SQL queries.
- Extensive experience of Oracle tools like TOAD, SQL*PLUS, and PL/SQL Developer.
- Experience in data loading from other databases and text files into Oracle database using SQL*Loader, Import/Export, Bulk Collect.
- Involved in developing various data models using MS VISIO and Erwin.
- Extensive experience in Exception Handling.
- Experience in working with DB Links and Data Migration between different environments.
- Experience in UNIX Shell Scripting and Knowledge of Crontab in UNIX environment for scheduling routine tasks.
- Experience with both Online Analytical Processing and Online Transactional Processing.
- Hands on experience with REF Cursors and Dynamic SQL.
- Experience in releasing the enhancements and code fixes into various environments through the process of Release Deployment.
- Knowledge of Data Warehousing concepts like Star Schema and Snowflake Schema, and Data Marts used in relational and dimensional modeling.
- Good experience in developing ETL (Extraction, Transformation and Loading) procedures and Data Conversion Scripts using Pre - Stage, Stage, Pre-Target and Target tables.
- Designed and Developed Type 2 Slowly Changing Dimension (SCD) model using star schemas.
- Knowledge of DBMS SCHEDULER, DBMS JOB, DBMS DATAPUMP, and UTL FILE packages.
- Good understanding of the concept of Normalization.
- Experienced with agile and waterfall methodology.
- Strong interpersonal skills, ability to interact with people at all levels. Good communication and presentation skills.
- Excellent written and oral communication skills with a result-oriented attitude.
TECHNICAL SKILLS
Databases: Oracle 11g/10g/9i, MS-Access XP, SQL Server
Environment: UNIX (Sun Solaris 5.8/5.9, HP-UX 11/11i, IBM AIX 5.2/5.3), Red Hat Enterprise Linux 3, Windows 2003/2000/XP/98
Languages: SQL, PL/SQL, Shell Programming, C, C++, XML, HTML, T-SQL.
Tools: TOAD 9.0/9.6, SQL*Plus, PL/SQL Developer 7.1.5, Oracle Enterprise Manager, Vi Editor, Putty, MS VISIO 2000/2007, SQL*Loader, TKPROF, SQL*Navigator v.4.5, Informatica 9.5.1, Microsoft Visual Studio.
PROFESSIONAL EXPERIENCE
Confidential, Providence, RI
Oracle PLSQL/ETL Developer
Responsibilities
- Involved in different phases of SDLC for all the new modules including Physical and Logical Design, Code Development, Testing, Implementing and Maintenance.
- Data loading from other databases and text files into Oracle database using SQL*Loader.
- Designed and Developed Type 2 Slowly Changing Dimension (SCD) model.
- Extensively involved in writing SQL scripts queries and sub-queries using TOAD 9.6.
- Involved in creating Logical and Physical Models using ERWIN 4.0and Power Designer.
- Developed Packages, Functions, and Stored Procedures using PL/SQL and maintained the scripts for various data feeds.
- Created Indexes and Sequences for faster retrieval of the product information and enhance the database performance.
- Used Database Triggers for saving previous data when insertion, updation and deletion on the tables.
- Used session partitions, dynamic cache memory and index caches for improving Performance of Informatica Server.
- Wrote DDL Scripts to create new database objects like tables, views, and synonyms.
- Wrote extensive Exception handling procedures to handle exceptions.
- Applicable knowledge of granting privileges on schema objects for other users to be able to share content.
- Worked with advanced features of PL/SQL like record types and Dynamic SQL.
- Used overloading feature of procedures, functions inside package.
- Did query performance tuning using joins, indexes, partitions and hints appropriately.
- Used the Crontab in UNIX for Automatic Tasks like loading data into Tables, Importing and Exporting data.
- Wrote UNIX shell scripts to transfer and manage data within the database.
- Involved in debugging, Tuning and Unit Testing of the PL/SQL code.
- Used Informatica Power Center 8.6 for extraction, transformation and load (ETL) of data in the data warehouse.
- Extensive experience of promoting the new initiatives, enhancements and code fixes to various DEV and QA environments through the Deployment Process using UNIX platform.
- Maintaining different versions for DDL extract of schemas and applications files (version control) using Tortoise SVN.
- Attended daily walkthroughs, and status meetings.
Environment: Oracle11g, HP-UX 11/11i, ERWIN 4.0, TOAD 9.6,Tortoise SVN, Informatica Power Center 8.6.
Confidential, NY
PLSQL/ETL Developer
Responsibilities
- Involved in creating Logical and Physical Models using MS VISIO 2007.
- Helped in designing and building data models using ER studio.
- Analyzed big data for genomics using PERL scripts.
- Proficiently developed new Packages, Functions, and Stored Procedures using PL/SQL as well as modified the existing ones as per the user requirement.
- Extensively created Tables, Indexes, Views, and Sequences for the new modules.
- Experience in Exception handling and experienced in agile environment.
- Created synonyms and granted privileges on schema objects for other users to be able to share and access content.
- Worked extensively with advanced features of PL/SQL like record and table types.
- Coordinated with the offshore support team in resolving critical issues during the Production Run.
- Worked with DBAs to analyze performance as well as during table space and schema lock issues.
- Involved in periodic Performance Tuning and Unit Testing of the PL/SQL code by using appropriate joins, indexes, and partitions.
- Analyzed the tables periodically to improve query response time.
- Performed Data Migration between different environments using DB Links
- Used other features like PL/SQL Wrapper, Hints, Materialized Views, and ERR$ Tables.
- Created standalone procedures using Dynamic SQL and cursors to migrate data within the same database.
- Worked with HTML development for the part of a project.
- Implemented SQL Cache dependency techniques in .NET for performance optimization.
- Extensively used TOAD for development and administration of various relational databases objects.
- Strong experience in Data Warehousing and ETL using Informatica Power Center 8.6.
- Proficiently used, created, and maintained various codes and SQL scripts on different branches of ClearCase (Version Control).
- Experience with Extreme Programming using Agile Methodologies.
Environment: Oracle10g, UNIX (Sun Solaris 5.8/5.9), TOAD 9.6, MS Visio 2007, HTML, Clear case, .NET.
Confidential, San Francisco, ca
Oracle PL/SQL Developer
Responsibilities:
- Gathering required data, data analysis and documentation of the plan
- Gathering data elements needed for analysis of the data, decoding flat file data and loading into database tables using SQL*Loader
- Responsible for data mapping from legacy system to Oracle
- Created database objects like tables, synonyms, sequences, views
- Designing and modifying of Database tables, indexes, triggers, packages, functions and procedures etc.
- Managed and resolved design issues during development.
- Involved in application tuning of database by defining the indexes tuning SQL queries.
- Optimized and enhanced the existing procedures and SQL statements for the better performance EXPLAIN PLAN, DBMS PROFILER, SQL TRACE to tune SQL queries.
- Wrote SQL*Loader script file for reading data from text file and loading into oracle table.
- Used Export/Import utility for backup and transferring data from one database to another.
- Used Cursors, Exception Handling, Throwing Exceptions, and Nested Loops, and also used the Pragma Exception Init in order to associate our own created error messages with the error codes.
- Involved in table redesigning with implementation of partition table and partition indexes to make database faster and easier to maintain.
- Reverse engineered database into Erwin repository.
- Designed and developed backend PL/SQL packages in database layer, stored procedures, functions and triggers using SQL*Plus.
- Created stored sub programs with dynamic SQL at both client and server side.
Environment: Oracle 11g/10g, SQL*Plus, PL/SQL, SQL*Loader, TOAD, SQL Developer, PERL, UNIX cron Scripting.
Confidential, Houston, Texas
PL/SQL Developer
Responsibilities
- Analyzed data to check for Data Integrity and Referential Integrity when loaded to source-staging tables.
- Created tables to capture errors and data that failed Validation Checks.
- Developed tables with partitions and created indexes, constraints, triggers, synonyms, database links, table spaces, roles etc in Staging and Destination schemas.
- Used direct path loading via SQL*Loader/ SQL INSERT statement, create table as select statements, table spaces, parallelized SQL statement execution where appropriate tables.
- Developed a package with stored procedures, functions and database triggers for transferring the date from Source to warehouse.
- Developed dynamic SQL used in procedures and function for the data extraction and loading process.
- Created dynamic procedures to drop and create Index for the Staging environment to facilitate faster loading of data.
- Used Toad Modeler to create the architect.
- Used Toad for creating PL/SQL (trigger, sequence, stored procedure).
- Used UTI FILE PACKAGES for writing DBMS OUTPUT messages to file.
- Collected all impacted modules from all developers and prepare the deployment script for production release.
- Developed Shell scripts to automate execution of SQL scripts to check incoming data with master tables, insert the valid and invalid data into error tables.
- Involved in the database administration and creating objects such as database, users, and schema and table spaces.
Environment: Oracle 10g/9i, SQL, PL/SQL, SQL*Loader, MS Access, UNIX Shell script, TOAD, UNIX, Windows XP/2000.owb 10g.
Confidential
PLSQL Developer
Responsibilities
- Involved in the design and development of user interface (GUI) in Forms 4.5 and table design using Oracle.
- Developed various Business and ad-hoc Reports.
- Created Data Flow Diagram, Entity Relationship Diagram from Designer.
- Involved in Development, Testing, Implementation and Training.
- Extensively involved in writing SQL queries (Sub queries and Join conditions).
- Created Database Triggers in PL/SQL to accommodate for data integrity and additional Referential Integrity.
- Migration from Oracle 9i to Oracle 10g.
- Developed KornShell Scripts to ingest data on a daily basis from external source (flat file).
- Involved in developing UNIX shell scripts required for maintaining and processing job.
- Involved in Package modifications to reflect the changes.
- Importing/Exporting data from/to different databases using utilities like SQL*Loader, Import/Export.
- Involved in export and import at schema level.
- Developed forms and reports using Forms 9i/10g and Reports 9i/10g.
- Created Object Types in PL/SQL.
- Performed customization on existing Forms appearance, validation logic, and behavior and incorporating new functionalities for control purpose.
- Responsible for Analysis, Design, Development/Customization and Testing/debugging of the reports.
Environment: Oracle 9i, Sun Solaris 2.5, Server Manager, SQL* Plus, SQL*Loader and Developer 2000.