Oracle Developer

End Client: New York City Department of Social ServicesJob Title: Oracle DeveloperDuration: 36-60 MonthsStart Date: ASAPLocation: Brooklyn NY 11238 (Hybrid)Position Type: ContractInterview Type: WebcamPROJECT NAME: OCSS Strategic ModernizationProject Overview : The project deals with Strategic Modernization of OCSS Web, Conversion of Mainframe processes into OCSS Web, this is a .NET Framework, based web application. Case Management System for CLU (Centralized Locate Unit) to manage their case load efficiently. Case Management for Enforcement helps identify work assignments for the Enforcement team and streamline referral assignment. It will also track aging of requests and changes in priorities. Parent Support Program (PSP) Participant Tracking System is to find the most effective way to assist the PSP Resources Coordinator to easily track NCPs who are participating in Parent Support Program when client shows up for his/her appointment and provides timely progress reports back to the court. Child Support Snapshot (CSS) gives Community Based Organizations (CBOs) the tool to assist the NCP (noncustodial parent) with their Child Support case. Automated Mailing System (AMS) is to automate the current manual process of printing and mailing court documents and inserts to the Custodial Parent (CP)/Assignor and Noncustodial Parent (NCP)/ Respondent in non-e-Filed support cases. OCSE Web legacy (CSTrack). These projects involve designing specifications for processes to fetch and process data from Court Messages, designing and implementing custom applications to utilize the retrieved data, generating reports, and ensuring the timely execution of scheduled jobs.Tasks & Duties:Design and develop Database design and architectureDesign scalable and efficient database architecture based on business requirementsDevelop logical and physical data models (ER diagrams, schema design)Define tables, indexes, constraints, and relationships for optimal performanceNormalize and denormalize data structures as needed for performance and usabilityEstablish data standards, naming conventionsDesign and monitor OCSSWeb monthly data refresh processSchedule business process in SQL Agent jobs and Oracle Scheduled JobsDesign and program ETL processesDesign and develop ETL process for data integrationExtract data from heterogeneous sources (databases, files,)Transform data using business rules, validations, and cleansing logicLoad data into target systemsImplement error handling, logging, and recovery mechanismsSchedule and automate ETL jobs using SQL Agent Jobs tool and Oracle Scheduled JobsCollaborate with Business users to understand data requirementsDatabase query performance turningAnalyze slow-running queries and identify performance bottlenecksOptimize SQL queries (joins, subqueries, execution paths)Review and interpret execution plans to improve query efficiencyDesign and maintain effective indexing strategiesImplement query rewriting and refactoring techniquesIdentify and resolve locking, blocking, and concurrency issuesCollaborate with developers to enforce query best practicesMaintain performance tuning documentation and guidelinesFile transfer between shared folders/Database servers and FTP serversEstablish secure SFTP connections to source and target systemsTransfer files between servers while ensuring data integritySchedule and automate file transfers using scriptsMonitor and verify successful completion of file transfersImplement error handling, retry mechanisms, and loggingMaintain transfer documentation, including file paths and schedulesCoordinate with business users to validate transferred dataTroubleshoot connectivity or transfer failures promptlyArchive and manage historical transferred files for audit and compliance purposesDatabase design/architecture/process documentationCreate and maintain database architecture documentationDocument logical and physical data models (ER diagrams, schemas)Maintain data models version on TFS (Team Foundation Server)Define database standards, naming conventions, and best practicesDocument tables, relationships, constraints, and indexing strategiesMaintain version control and track schema changesCollaborate with developers and DBAs to keep documentation up to dateRequired Skills :A minimum of 4 years of experience with the following skill sets:Databases: Oracle 19c,12c, 11g, 10g, 9i, Microsoft SQL Server 2016/2008/2005, IBM InformixSpecial tools: SQL*Plus, SQL Loader, Toad, SQL Developer, Enterprise manager, SFTP/FTP, SQL Server Management Studio, Jira Software, MS Visual Studio 2016/2008/2005, PuTTY/MobaXterm for UnixProficiency in PL/SQL, T-SQL languagesETL - SQL Loader, SQL Server Export/Import, SQL Server Business Intelligence tools – SSIS/SSRS, Oracle External tablesScheduling Database jobs – SQL Agent jobs – Oracle Scheduled jobsVersion control – Database objects and documentation – TFS (Team Foundation Server)Benefits & Perks :Comprehensive Health Insurance Coverage for employees and eligible dependents.Employee Referral Bonus Program with rewards for successful candidate referrals.Opportunities for career growth, professional development, and certifications.Flexible and collaborative work environment.Access to challenging projects with leading public sector and enterprise customers.Opportunity to work with Vivid's diverse public sector client base, contributing to meaningful digital transformation initiatives.Exposure to innovative technologies and industry-leading solutions.