Senior Oracle Pl/sql Developer Resume
Fort Worth, TX
SUMMARY:
- Around 11 Y ears of Extensive working experience as an Oracle PL/SQL Developer with proven ability in the area of Client/Server and Distributed Multi - tier Application Development, Data modeling, Design, Development, Data processing, System Analysis, Deployment and Support with Oracle technology on different platforms.
- Expert working experience in Oracle PL/SQL Development using various Oracle key components such as Stored Procedures, Functions, Packages, DB Triggers, Views, Materialized Views, DBlinks, Exception handling, Oracle Supplied Packages, Collections, PL/SQL Types, External Tables, Merge Statements, Autonomous transactions, Global Temporary Tables (GTT), Bulk Load, Cursor, Ref Cursors, Partitioned Tables, Dynamic SQL, SQL*Loader, Data Pump, UTL FILE, Database Links etc.
- Expertise in Creating and Maintaining Database objects like Tables, Views, Indexes, Constraints, Materialized Views, Synonyms, and Sequences.
- Proficient in writing and tuning Complex SQL statements, complex joins co-related sub-queries and SQL statements with Analytical Functions (ROW NUMBER, RANK, DENSE RANK, Lag, Lead, Connect by level etc).
- Experience in writing D ynamic SQL Statements using EXECUTE IMMEDIATE and DBMS SQL.
- Worked in extensively on Query Level Performance tuning using Explain Plan, SQL Trace and TKPROF utilities to pin point time consuming SQL’s and tuned them by creating indexes and forcing specific plans.
- Working knowledge on using new partitioning techniques like Interval, Reference and Extended Composite (List-List, List-Range) Partitioning.
- Expertise in loading data from XML/FLAT files into Oracle Database Tables using SQL*LOADER and External Tables.
- Proficient in writing SQL statements with Window Aggregate Functions using ROWS or RANGE clause.
- Expertise in Oracle Data Pump commands like EXPDP and IMPDP for D ata Migration.
- Extensive experience in writing ANSI SQL functions, Stored Procedures within Composite Data type .
- Extensive working experience in Terabyte environment (8+ Terabytes).
- Extensive experience in developing multi-threaded database processes using Oracle packages like DBMS SCHEDULER, DBMS STATS.
- Strong experience with Oracle Data Warehouse, ETL Process, Data analysis for ODS, Online Transactional Processing (OLTP) and Data Warehouse logical/physical, relational and multi-dimensional modeling (Star Schema, Snowflake Schema), O ptimization, Partitioning, Archiving and capacity planning.
- Expertise in all phases of Software Development Life Cycle (SDLC) and Best Practices (Oracle CDM) and Development Methodologies/Guidelines.
- Good understanding of RDBMS, Oracle Database architecture, designs and having performed DBA duties, such as Table Partitioning, Export /Import and Data Pump.
- Experience in using Oracle concepts Like Table Partitioning, Optimizer hints and Materialized Views (Snapshots).
- Experience in code migrations from Development to QA, QA to UAT (Stage) and Stage to Production Environment.
- Assisted DBA in performing various DDL (Create, Drop, Rename, and Alter) works like Creation of Schemas, Tables, Indexes, Materialized Views, and Partitioning tables.
- Good understanding in the areas of Users, Roles, Privileges, Schema and Object Management & Session monitoring.
- Expertise in Transaction Management like Commit, Rollback in Oracle Database.
- Extensive Experience in using Oracle 11g/10g/9i/8i and tools like Toad, SQL*PLUS, SQL Developer, Advanced SQL Optimizer.
- Expert working Knowledge of UNIX Shell Scripting and Scheduling Cron Jobs for Automation and tools like WINSCP, PUTTY, FTP/SFTP, and CONNECT DIRECT etc.
- Responsible for Query Optimization, troubleshooting, debugging, problem solving and Tuning for improving performance of the applications.
- Expert in writing complex batch processes in PL/SQL and used Autosys for Job Scheduling.
- Have experience in Relational and Dimensional Data modeling, Normalization, Demoralization, Data Architecture, Planning, Testing, Data Migration and Data Conversion .
- Expertise in Data Mapping, Transformation and Loading data in a complex and high-volume environment.
- Experienced in Database Administrative duties like Grants, Loads, and Backup/Recovery & Performance.
- Responsible for writing Functional Requirement Specifications (FRS) and User Requirement Specifications (URS).
- Report on Health Checks including performance and availability, Error Exceptions, and Production Support Issues on a regular basis.
- Excellent communication skills, problem solving and logical methodology. Work well in the team environment, Self-motivated, quick learner, able to work well under tight deadlines and rapidly changing priorities.
TECHNICAL SKILLS:
Database Technology: Database 11g, 10g, 9i, MS SQL Server 2000/2005/2008 , Ms Access
Database Management System: Database design, Development and maintenance.
Database Tools Toad: (10.6.1), SQL Developer 4.0.1/3.2/3.1/3.0/2.1 , SQL *PLUS, SQL*Loader, VI Editor, Putty, ERWIN, FoxPro, Autosys, Subversion, CVS, Teradata SQL Assistant, VersionOne, TKPROF, EXPLAIN PLAN, DB LINKS, Export, Import, Oracle Warehouse builder (OWB), Oracle Forms & Reports, Oracle Data Integrator (ODI), Oracle Enterprise Manager (OEM).
Data Modeling: Erwin 4.1/3.5.2, ER/Studio, Dimensional Data Modeling, Star Schema Modeling, Snowflake modeling, Toad, SQL Developer Data Modeler version 4.1.3, Physical and logical data modeling
Scripting Shell: (Bash Shell), HTML, XML, SQL
Operating Systems: LINUX (Red Hat Linux), UNIX, AIX 5LV 6.1/5.3/5.2/5.1/4.3.3 , MS DOS and Windows 8.1/7/NT/XP/VISTA.
PROFESSIONAL EXPERIENCE:
Confidential, Fort Worth, TX
Senior Oracle PL/SQL Developer
Responsibilities:
- 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.
- Created scripts to create new tables, views, queries for new enhancement in the application using TOAD.
- Created indexe s 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.
Environment: Java, Oracle 11g, SQL * Plus, SQL*Loader, SQL Developer, Shell Scripts, UNIX, Windows XP
Confidential, Jacksonville, FL
Senior Oracle PL/SQL Developer
Responsibilities:
- Participated in business requirements and solutions to develop, implement, and enhance existing Diamond Application using Oracle SQL & PL/SQL.
- Created detailed technical specifications for entire systems (made up of several subsystems) and identified integration points for Claim Adjustment Innovation consisting of Home Plan and Host Plan.
- Involved in developing new programs that span platforms to replace existing legacy applications and also created new Application Programming Interfaces (APIs).
- Wrote pseudocode for Claim Adjustment Innovations and also reviewed code, technical specifications developed by other Developers. Ensured sufficient quality and compliance of documentation to architectural standards.
- Implemented and improved application performance based on the client’s requirements and also developed database scripts for data loading and conversion process.
- Reported missing elements found in system and functional requirements and explained impacts on subsystem to team members.
- Designed and modified database tables, keys, indexes, views, type objects, grants, triggers, Exception handling methods, P ackages, F unctions and P rocedures etc.
- Wrote Complex PL/SQL scripts using Cursors, Ref-cursors, Oracle Supplied Packages, Packages, Procedures, Functions, RAISE, UTL FILE packages for DML & DDL operations as per Tickets.
- Improved query performance by query optimization-tracing the query execution plan using EXPLAIN PLAN, SQL scripts to monitor the performance.
- Developed and modified existing PL/SQL Packages, functions and procedures.
- As a key member of team participated in application design sessions, writing technical specifications and also coordinated with offshore team and provided post implementation support.
- Responsible for debugging, troubleshooting, problem solving and tuning for improving performance.
- Participated in unit testing and maintaining of planned and unplanned activities for internally developed applications and debugging program errors.
Environment: Oracle 12c 12.1.0.2, SQL, PL/SQL, SQL*Plus, TOAD 12.10, SVN, Notepad++, ANSI SQL, XML, Windows 10, WINSCP .
Confidential, San Antonio, TX
Oracle Developer
Responsibilities:
- Developed and Involved in maintaining Databases, User Interfaces, and Code associated with Defense Civilian Personnel HR data for Department of Defense (DOD).
- Modified PL/SQL Programs to utilize Secure Views for merging of multiple DCPDS Oracle HRMS databases into one consolidated database.
- Familiarity with Oracle HR, GHR schemas, and Oracle R12 EBS HR Applications.
- Wrote SQL scripts using FND API’s for modification and deletion of Oracle Concurrent Programs.
- Used parametric Cursors and Ref cursors for performing DML operations involving multiple records.
- Wrote PL/SQL scripts for disabling Oracle Related GHR backend process for Oracle Concurrent Program.
- Analyzed the data, extracted from the different source systems based on the business rules.
- Wrote and developed T ables, Secure Views, Functions, Triggers, Procedures and Packages using SQL and PL/SQL for Concurrent Program.
- Wrote complex SQL Queries using Join Conditions, Group by functions, Sub Queries, Correlated Sub queries, Nested Queries and Inline Views to retrieve data from the database.
- Performed debugging, troubleshooting, problem solving and tuning for improving performance of the backend application programs.
- Modified database objects like tables, views, Indexes, Synonyms, Sequences, Constraints, table spaces, functions, stored procedures, packages etc.
- Used Shell Scripts for searching Oracle Forms, Oracle Reports and Oracle Library from different users on Secure CRT.
- Increased performance by implementing business logic using stored procedures.
- Worked with DBA in performing various DDL (Create, Drop, Rename and Alter).
- Used Exception handling methods along with RAISE APPLICATION ERROR in order to associate our own created exception names.
- Used PVCS for version management and WOTS for ticket tracking.
- Attended meetings with the project team to report on status, issues, time estimates and prototype demonstrations.
- Collaborated with other team members to coordinate development and testing in order to meet deadlines.
Environment: Oracle 12c 12.1.0.2 EBS, SQL, PL/SQL, SQL*Plus, Oracle SQL Developer 4.1.3, PVCS 8.5.3.0( Software Configuration Management tool ) , UltraEdit 64-bit, Notepad++, HTML, ANSI SQL, XML, Windows 2000/2003/8/10 , Secure CRT 8.1, WOTS, Vi Editor, Unix sun Solaris (5.1.0), Unix Shell Scripts.
Confidential, Albany, NY.
Oracle Developer
Responsibilities:
- Understood the Business Requirements from the client and translated the business detail into technical design and specification.
- Developed PL/SQL Packages for web-based Java Application for HCS (Health Commerce System) Portal, using permission structure within the application and the portal access.
- Wrote and developed T ables, Views, Materialized Views, Cursors, Functions, Triggers, Procedures and Packages using SQL and PL/SQL for HCS (Health Commerce System) Application.
- Wrote SQL Queries using Join Conditions, Group by functions, Sub Queries, Correlated Sub queries, Nested Queries and Inline Views to retrieve data from the database.
- Loaded the flat file data into database table by creating multiple SQL*Loader control scripts.
- Modified database objects like tables, views, Indexes, Synonyms, Sequences, Constraints, table spaces, functions, stored procedures, packages etc.
- Increased performance by implementing business logic using stored procedures.
- Assisted with DBA in performing various DDL (Create, Drop, Rename, and Alter).
- Used exception handling methods along with RAISE APPLICATION ERROR in order to associate our own created exception names.
- Involved in Database Performance Tuning using Partitioning and Indexes.
- Enhanced the existing UNIX Scripts to run batch processes, oracle background processes using JOBS & achieve better performance and throughout put.
- Used Crontab in UNIX and scheduler in Windows for automatic Tasks (Analyzing tables, loading tables, Export/Import).
- Created/modified Oracle objects like user defined data types and using SQL Developer Data Modeler data modeling tools to design and maintain Conceptual, Logical and Physical Database models.
- Responsible for Data Migration using Oracle tools Expdp, Impdp and RMAN.
- Used RedMine for ticketing systems.
- Participated in meetings with Business Analyst (BA), Quality Assurance (QA) on a regular basis to discuss progress on any production support issues.
Environment: Oracle 11g, SQL, PL/SQL, SQL*Plus, Oracle SQL developer version 4.1.3, SQL*Loader, RedMine, HTML, ANSI SQL, XML, Windows 2000/2003/XP/7/8, FTP, Export, Import, Crontab, ETL, SQL Developer Data Modeler version 4.1.3, RMAN, Unix sun Solaris (5.1.0), Unix shell scripts.
Confidential, Wayne, NJ.
Oracle Database Developer
Responsibilities:
- Gathered Business Requirements from the client and translated the business detail into technical design and specification.
- Creation of Database, table spaces, schema objects and stored procedures.
- Developed various backend application programs such as Tables, Views, Materialized Views, Functions, Triggers, Procedures and Packages using SQL and PL/SQL.
- Wrote SQL statements with Window Aggregate Functions using ROWS or RANGE clause.
- Wrote ANSI SQL, PL/SQL procedures and functions to migrate data from the source legacy systems to the Target system (Oracle database).
- Extracted and transformed source data from different database like Oracle, SQL Server and DB2 and flat file into oracle.
- Responsible for troubleshooting, debugging, problem solving and tuning for improving performance of the backend application programs.
- Wrote complex SQL queries using Join Conditions, Group by functions, Sub Queries, Correlated Sub queries, Nested Queries and Inline Views to retrieve data from the database.
- Used parametric Cursors and Ref cursors for performing DML operations involving multiple records.
- Loaded the flat file data into database table by creating multiple SQL*Loader control scripts.
- Applied business logic using stored procedures to increase performance.
- Used Plan table, explain plan and TKPROF for Tuning the SQL statements by creating indexes. Generated session level trace and used the TKPROF utility to get the report for the trace file.
- Created many executable programs running on UNIX operating system and wrote UNIX Shell Scripts for automating the process.
- Involved in Database Performance Tuning using Oracle Hints, Explain plan, TKPROF, Partitioning and Indexes.
- Implemented business logic using ANSI SQL functions, stored procedures within Composite Data type .
- Used exception handling methods along with RAISE APPLICATION ERROR in order to associate our own created exception names.
- Improved the existing UNIX scripts to run batch processes, oracle background processes using JOBS & achieve better performance and throughout put.
- Responsible to report on Error Exceptions, Health Checks including performance and availability report on Production Support Issues, Document Process for monitoring and report out to DBO Data Quality.
- Using the Crontab in UNIX and scheduler in Windows for automatic Tasks (Analyzing tables, loading tables, Export/Import).
- Worked on Oracle databases to manage data for development, training and production support.
- Created/modified logical and physical data models using Erwin with normalization of relations.
- Responsible for Data Migration using Oracle tools Expdp, Impdp and RMAN.
- Used Autosys for Job scheduling and monitoring.
- Involved in creating target database users and modules using Oracle Warehouse Builder (OWB)
- Created stored procedure and function in PL/SQL to increase the performance of Forms on web server.
- Meet with IT on a regular basis to discuss progress on any production support issues.
Environment: Oracle 10g/11g, PL/SQL, TOAD (10.6.1), SQL*Plus, SQL developer, SQL*Loader, OWB, HTML, ANSI SQL, XML, Windows 2000/2003/XP, FTP, Developer 2000, Export, Import, Crontab, VersionOne, Autosys, ETL, Erwin, RMAN, Unix sun Solaris (5.1.0), Unix shell scripts.
Confidential, Portland, ME.
Oracle PL/SQL Developer
Responsibilities:
- Deployed business rules into PL/SQL code for data analyzing, transforming and cleansing.
- Created PL/SQL & SQL scripts for the ETL Converting/Migrating data from Mainframe, Oracle, and XML, Flat files into Oracle database tables for Data warehousing and BI purpose.
- Written Bash Shell scripts those were used for staging, transformation and loading of the data into base tables.
- Used EXTERNAL Tables, Utl file and SQL*LOADER for the purpose of loading flat file And XML data.
- Responsible for Data Migration using Oracle Data Pump (Export & Import utilities).
- Used aggregate routines for views, summary tables, materialized views, reports etc.
- Created partitioned tables, partitioned indexes for manageability and scalability of application.
- Used Collections, Bulk Binds to improve performance by minimizing the number of context switches between the PL/SQL and SQL engines.
- Monitoring and optimizing the Database Performances through Database Tuning and Application tuning using DBMS STATS, Explain Plan, SQL*Trace, TKPROF.
- Improved query performance by query optimization - tracing the query execution plan.
- Created composite SQL script using Dynamic SQL, Analytical function.
- Wrote PL/SQL code for Oracle Job Scheduling and Maintain Batch jobs.
- Created data warehouse objects - table partitions, sub-partitions, normal and bitmap indexes, summary tables, materialized views, stored packages, functions and procedures with parallel queries, and rebuild indexes for partition exchange loading.
- Wrote complex SQL queries using Join Conditions, Group by functions, Sub queries, Correlated Sub queries, Nested queries and Inline Views to retrieve data from the database.
- Used Oracle Utility SQL*Loader and External Table Concept in order to facilitate the Bulk Upload Mechanism, which involved loads from Excel Sheets using GUI and Flat Files from Various source systems.
- Implemented various Error/Exception handling methods using system defined exceptions and user defined exceptions.
- Created/modified logical and physical data models using Erwin with normalization of relations.
- Prepared/updated shell scripts for ETL to run in batch using Crontab.
- Wrote SQL*Loader scripts to migrate the data from the text files, CSV files etc. and populated the intermediate tables.
- Worked on Data Backup and Data Recovery using RMAN.
- Worked in creating target database users and modules using Oracle Warehouse Builder (OWB).
- Wrote Unix Shell Scripts to load the data as per the Business functionalities.
- Involved in the maintenance and support of existing applications with the users.
Environment: Oracle 10g, 11g, SQL, PL/SQL, SQL*Plus, TOAD (9.0.1), OWB, ETL, ERWIN, SFTP, Mainframe, XML, Expdp, Impdp, RMAN, Crontab, MS Access, Windows XP Professional clients, SQL*Loader, DBMS JOB, DB LINKS, UNIX.
Confidential
Oracle Developer
Responsibilities:
- Participated in analysis, design, development, and testing of the application.
- Involved in Creation of tables, Join conditions, correlated sub queries, nested queries, views, sequences, and synonyms for the business application development.
- Wrote new PL/SQL Packages, modified existing code perform Certain Specialized functions for enhancement on oracle Application.
- Created Complex SQL script, Dynamic SQL, Analytical function, Bulk Collect.
- Created Packages, Stored Procedures, Function and SQL Scripts to load data into warehouse from different sources.
- Worked extensively on tuning of mappings/sessions using session partitioning and lookup strategies to address bottlenecks.
- Developed stored procedures to return Ref cursors, PL/SQL Table/Record for Application.
- Created partitioned tables, partitioned indexes for manageability and scalability of the application.
- Used Collections, Bulk Binds to improve performance by minimizing the number of context switches between the PL/SQL and SQL engines.
- Expertise in tuning SQL queries on large partition databases for optimal performance.
- Translated business requirements into creation and alteration of database objects: tables, indexes, constraints, triggers and stored procedures.
- Created PL/SQL packages, stored procedures and functions that extensively used PL/SQL cursors, user defined object types, exception handling to implement automation process.
- Involved in performance tuning for various oracle-based application using various methods like SQL Trace, EXPLAIN PLAN, TKPROF etc.
- Experienced in using Oracle Utility programs like SQL*Loader, SQL Developer and External Tables.
- Strong knowledge in using views, Materialized views, Export and Import Utilities provided in Oracle TOAD.
Environment: Oracle 8i, 9i, SQL, PL/SQL, Reports, UNIX, MS Access, Windows XP Professional clients, Reports, SQL Developer 3.2/3.1, SQL*Plus, FoxPro, Visual FoxPro, Statistical Package for the Social Sciences (SPSS) 10.0/9.0.