- Over 6+ Years of Software Development Life Cycle experience in Software Design, Development and Implementation of Oracle, SQL, PL/SQL applications.
- Extensive experience in writing SQL, Stored procedures, Functions, Packages, Database Triggers, Shell-Scripts and SQL Loader scripts.
- Good understanding of Oracle Data Dictionary and Normalization Techniques.
- Expertise in distributed query performance tuning by using EXPLAIN PLAN, SQL Trace and TKPROF Utilities, hints provided by Oracle.
- Expertise in performing Data export, Import and various operations using TOAD.
- Very strong in SQL Query Tuning, Database Triggers, Stored Procedures, reducing time for night batch jobs by reviewing PL/SQL code, modifying database parameters.
- Extensively worked in Client-Server application development using Oracle 11g/10g/9i/8i Forms 9i/6i, Reports 10g/6i/6.0, SQL, PL/SQL, Oracle 9iDS, Oracle Import and Export Utilities.
- Experience in SQL performance tuning using Cost-Based Optimization (CBO).
- Experience in SQL performance using Rule Based Optimization (RBO).
- Experience in application development using various advanced features of Oracle like Collections, Bulk Collections, Table Functions, Autonomous Transactions, Dynamic SQL, Object types and Records.
- Extensive experience in data migration techniques using Oracle External Tables, SQL* Loader, Import/Export, bulk and batch processing.
- Experienced in data analysis, data modeling, development, testing and documentation of Projects.
- Expertise in configuring, administering, and troubleshooting Web logic domains, clusters and servers in distributed environments for SOA.
- Experience good Knowledge/experience on Oracle EBS or any ERP.
- Experience on system study, design, development, post implementation maintenance and support of Oracle EBS
- Oracle Application - E-Business Suite R12 and 11i.
- Reorganized databases and sized database objects to meet standards and improve performance 30%
- Extensively Worked on Extraction, Transformation and Load (ETL) process using PL/SQL to populate the tables in OLTP and OLAP Data warehouse Environment
- Strong Data Warehousing ETL experience of using Informatica 9.1/8.6.1/8.5/8.1/7.1 Power Center Client tools - Mapping Designer, Repository manager, Workflow Manager/Monitor and Server tools – Informatica Server, Repository Server manager.
Middleware: Oracle Fusion Middleware 10g/11g (Oracle SOA Suite, Oracle Web logic, OSB, BAM).
- Oracle EBS experience in a functional role supporting the following modules (finance, supply chain, quality, projects and manufacturing)
- Wrote unit test cases for some critical SOA composites.
- Extensive testing ETL experience using Informatica 9.1/8.6.1/8.58.1/7.1/6.2/5.1 (Power Center/ Power Mart) (Designer, Workflow Manager, Workflow Monitor and Server Manager) Teradata and Business Objects.
- Experience with DBA activities like creating users, granting and revoking privileges on database objects and taking back Confidential .
- Improve the performance of the slow SQL queries by implementing proper Index, using FORALL and BULK COLLECT.
- Experience in Installing Software's and Configuring Databases.
- Experience in UNIX Shell Scripting.
- Strong Trouble-shooting, Problem solving, Analytical & Design Skills.
- Possess good communication skills and quick learning ability.
Oracle: Oracle 8i/9i/10g/11g
Oracle Tools: Toad, SQL*Loader, PL/SQL Developer, SQL Developer, SQL Tool, SQL Navigator, Developer 2000 (Reports 6i/9i/10g, Forms 6i/9i/10g), SQL*Plus
Platforms: Windows (7, Vista, XP, 2000, 98, NT), UNIX, Red Hat Linux
Data Modeling Tools: Erwin 3.5/4.0/4.5, Designer 2000, Microsoft Visio 2003, Oracle Designer 10.1.2.3, Data Analysis
Database Tools: Toad, SQL*Loader, PL/SQL Developer, SQL Developer, SQL Tool, SQL Navigator, Developer 2000 (Reports 6i/9i/10g, Forms 6i/9i/10g), SQL*PlusEXPERIENCE:
Confidential, Charlotte, NC.
Oracle PL/SQL Developer:
The current project is basically to integrate the LaSalle and the countrywide banks that Confidential has acquired. On a broad perspective, it's the migration of the entire customer databases of these two banks to Central database. Work involved writing Stored Procedures, Triggers and monitoring performance to ensure efficient data retrieval and generate forms and reports.
- Actively participated in gathering Business Requirements and System Specification from system users.
- Responsible for Analyzing requirements, designing, maintaining and updating data models
- Analyze the current data management procedures in practice and suggest ways for
- Automating the process or improving the existing system.
- Developed stored procedures to pull the data from the distributed systems.
- Created tables, synonyms, sequences, views.
- Manage RAC environment for Day to Day Database administrator, Performance Tuning.
- Migrate over 3 TB data from Across the Platform ( From Solaris to AIX)
- Strong trouble shooting, problem solving, analytical and design skills.
- Involved in all phases of database development, from needs assessment to QA/QC, design, and support.
- Worked in 24x7 production environment and provided on-call and day-to-day support.
- Handled database server tuning, application tuning, maintaining documentation, capacity planning and database security
- Proven track record meeting with management and executives as the subject matter expert in SOA
- Expertise in installation and configuration of Oracle and large databases on Unix Operating System.
- Participated in code review and design discussions with SOA architects.
- Built SOA composites using BPEL/OSB/Mediator components that exposed some EBS sales order processing functions as web services accessible by all technology platforms in the enterprise through SOAP/WSDL interface.
- Involved in Developing and handling the PL/SQL Packages, Procedures and Database Triggers.
- Optimized and modified the triggers, complex store functions, user base data type etc.
- Involved in tuning of SQL queries by using quest central tools and manual by EXPLAIN PLAN.
- Developed payment register FORM and Invoice register FORM using 6i.
- Developed data entry REPORTS in REPORTS 6i.
- Experience in SQL performance tuning using Cost-Based
- Effectively made use of Table Functions, Indexes, Table Partitioning, Collections, Analytical functions, Materialized Views, Query Re-Write and Transportable
- Created Tables, Views, Constraints, Index (B Tree, Bitmap and function based)
- Involved in unit testing and integrated testing.
- Extensively used SQL*Loader to load the data from the flat files to the database and validated the data with the PL/SQL procedures.
- Defined database requirements and designed data model.
- Tuned complex stored procedures for faster execution and developed database structures according to the requirements.
- Schedule Backup and recovery from database.
- Integrated the mailing system to alert various group on the status of data pulls and data processing.
- Informatica Power Center 9.1/8.6/8.5/8.1/7.1 (Designer, Workflow Manager, Workflow Monitor, Repository manager and Informatica Server)
- Worked on Informatica Power Center tools- Designer, Repository Manager, Workflow Manager, and Workflow Monitor.
- Designed and developed user interfaces using Oracle Forms 6i.
- Worked on different oracle Reports 6i.
Confidential, Erie, PA
Oracle PL/SQL Developer:
- Coordinated with the front end design team to provide them with the necessary stored procedures and packages and the necessary insight into the data.
- 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.
- Wrote Unix Shell Scripts to process the files on daily basis like renaming the file, extracting date from the file, unzipping the file and remove the junk characters from the file before loading them into the base tables.
- Involved in the continuous enhancements and fixing of production problems.
- Generated server side PL/SQL scripts for data manipulation and validation and materialized views for remote instances.
- Developed PL/SQL triggers and master tables for automatic creation of primary keys.
- Created PL/SQL stored procedures, functions and packages for moving the data from staging area to data mart.
- Worked on developing and debugging informatica mappings, mapplets, Sessions and workflows.
- Created scripts to create new tables, views, queries for new enhancement in the application using TOAD.
- Created indexes on the tables for faster retrieval of the data to enhance database performance.
- Involved in data loading using PL/SQL and SQL*Loader calling UNIX scripts to download and manipulate files.
- Performed SQL and PL/SQL tuning and Application tuning using various tools like EXPLAIN PLAN, SQL*TRACE, TKPROF and AUTOTRACE.
- Extensively involved in using hints to direct the optimizer to choose an optimum query execution plan.
- Used Bulk Collections for better performance and easy retrieval of data, by reducing context switching between SQL and PL/SQL engines.
- Created PL/SQL scripts to extract the data from the operational database into simple flat text files using UTL FILE package.
- Creation of database objects like tables, views, materialized views, procedures and packages using oracle tools like Toad, PL/SQL Developer and SQL* plus.
- Partitioned the fact tables and materialized views to enhance the performance.
- Extensively used bulk collection in PL/SQL objects for improving the performing.
- 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.
- Extensively used the advanced features of PL/SQL like Records, Tables, Object types and Dynamic SQL.
- Handled errors using Exception Handling extensively for the ease of debugging and displaying the error messages in the application.
Oracle PL/SQL Developer:
- Developed and implemented dozens of automated batch processing jobs, running in numerous development, test and production environments.
- Created and Configured Workflows and Sessions to transport the data to target warehouse Oracle tables using Informatica Workflow Manager.
- Involved in importing the production data to work environment through TOAD.
- Involved in handling the changes in compiling scripts according to the database changes.
- Effectively used Triggers to track the user profile updates and to send the messages to downstream systems
- Export/import using Data Pump, creating indexes, synonyms, sizing tables, refreshing Materialized view.
- Developing back-end PL/SQL packages, building UNIX shell scripts for data migration and batch processing.
- Process Tuning of various Nightly jobs that we rarely analyzed for the use BULK collect and optimizing
- parameters and adjusting hint and index by reviewing the Trace files,
- Extensively used Materialized views and DB links for remote instances to retrieve data from other databases Worked with DBA in making enhancements to physical DB schema and coordinated with DBA in creating and managing Tables, indexes, table spaces, synonyms, db links and privileges.
- Maintained stored definitions, transformation rules and targets definitions using Informatica.
- Used various transformations like Filter, Expression, Sequence Generator, Update Strategy,
- Handled PL/SQL compile-time, run-time errors, debugging of Stored Procedures for business logic modification, and responding to system events through triggers.
Environment: Oracle 8i/9i/10g/11g, Oracle Forms 6i/9i, Reports6i/9i, UNIX Shell Scripts, PL/SQL, Toad, PL/SQL Developer, SQL Optimizer, Putty.
Confidential, Libertyville, IL
Oracle PL/SQL Developer:
The project involved maintaining information and ensuring efficient data retrieval as required for core decision making. Work involved writing Stored Procedures, Triggers and monitoring performance. The system will send the data in the form of files. The data from the files will be extracted and transformed using PL/SQL procedures. The Oracle database will act as target database.
- Analyzed the Functional Specs provided by the Architect and created Technical Specs documents for all the mappings.
- Worked as part of the development team, along with the Systems Analysts/Business Analysts.
- Analyzed and fixed the data discrepancies between various systems and identified bad data caused due the data migration from various legacy systems.
- Created PL/SQL Stored Procedures, Functions, Packages, and Triggers; extensively used Cursors, REF cursors, User-defined object types, Records, forms and reports in PL/SQL programming
- Developed several stored procedures and functions using advanced Oracle concepts such as Bulk Binds, Bulk Collects Ref Cursors to improve performance.
- Involved in fine tuning the existing packages for better performance and providing on-going support to existing applications and troubleshooting serious errors when occurred.
- Modified and debugged several PL/SQL programs for performance tuning using techniques like EXPLAIN PLAN, TKPROF and DBMS PROFILER
- Created B Tree indexes, Function based indexes, Bit Mapped Indexes on Tables, columns to minimize query time and achieve better performance
- Improved performance by creating Unique and Range Indexes based on the cost returned by EXPLAIN PLAN.
Environment: Oracle 11g/10g/9i, Oracle Forms 10g, Reports10g, PL/SQL, SQL Server, UNIX Shell Scripting, Toad, ER-Win Windows XP, .Net.
- Designed the tables according the requirements
- Wrote Cron-Jobs for downloading the source files from SFTP.
- Upload flat files to database using ETL tools like Informatica and SQL Loader.
- Developed procedures to check validity of data according the business rules and fix them.
- Write Script for data updating
- Daily Report Generation of Telephone Migration Project Status
- Production Support and Quality Control
- Oracle objects creation scripts (DDL) for database development to Test and Production environments
- Oracle PL/SQL design and tuning of stored procedures
- ETL (Extraction Transformation Load) of data from Oracle tables to flat files and from flat files to Oracle tables
- Prepared test environment for QA team to test the developed application from end-end
- PERL script conversion to Oracle PL/SQL package
- Developed tools to populate test data for Quality Assurance team
- Developed a framework for extensible automated production support in PL/SQL
- Developed a framework to build restart ability in the system.
- Single point of contact for development and technical enhancements
- Documented the user guide for the system.
Environment: Windows, Oracle 9i, UNIX, Toad 11.0.