ORACLE PL/SQL

ORACLE PL/SQL CONTENTS

Oracle Architecture (BASIC to Modirate)

• 1. What is Oracle Database Server
• 2. Connecting to Database Instance in Dedicated Server Mode
• 3. Connecting to Database Instance in Shared Server Mode
• 4. Introduction to File Structures in Database
• 5. Introduction to Oracle Memory Structure(SGA, PGA , UGA)
• 6. Oracle Shared Pool, JAVA Pool, Large Pool, Stream Pool, etc.
• 7. Introduction to Oracle’s Processes(Server, backgroud, Slave)
• 8. Oracle Database Server Installing Scenarios
• 9. Hardware Consideration For Installing Oracle Database
• 10. What is a Transaction in Context of RDBMS(Atomicity,Durability,Consistency,Isolation)
• 11. Distributed Transaction
• 12. Autonomous Transaction
• 13. What is Locking?(Optimistic and Pessimistic)
• 14. What is Blocking in Oracle
• 15. Types of DDL Locks in Oracle
• 16. Types of Locks and Latches in Oracle
• 17. How Commit and Rollback Works in Oracle
• 18. Undo Management In Oracle
• 19. Common Performance Issues in Redo Log
• 20. Oracle Storage Terminology
• 21. High water mark and free lists
• 22. Oracle Storage Parameters(PCTFREE , PCTUSED,INITTRANS, MAXTRANS, INTIAL, NEXT, PVTINCREASE)

SQL(Beginers to advance)

• 1. What is SQL and Why learn SQL
• 2. An Introduction to Databases
• 3. DBMS Vs RDBMS and RDBMS Vs ORDBMS
• 4. An Introduction to SQL*Plus
• 5. The SQL Execution Environment
• 6. The WHERE and ORDER BY Clauses
• 7. System defined fuctions
• 8. Reading Syntax Diagrams
• 9. Date and Conversion Functions
• 10. Aggregate Functions, GROUP BY, and HAVING Clauses
• 11. Simple and Complex joins
• 12. Subqueries(Simple, Co-related, Scalar, Inline view)
• 13. Insert, Update, and Delete(DML)
• 14. Create, Alter, and Drop Tables(DDL)
• 15. Indexes, Sequences, and Views
• 16. The Data Dictionary, Scripting, and Reporting
• 17. Users, Privileges, Roles, and Synonyms
• 18. Regular Expressions and Hierarchical Queries
• 19. Advance SQL Concepts ( Analytic Fuction, WITH Clause, Rollup, Cube)

Oracle PL/SQL (Beginer to Advance)

• 1. Overview of PL/SQL
• 2. PL/SQL Language Fundamentals
• 3. PL/SQL Data Types
• 4. Using PL/SQL Control Structure
• 5. Using PL/SQL Collections and Records(Varray,Nested and Associative)
• 6. What is Static SQL and its use
• 7. What is Dynamic SQL and its use
• 8. PL/SQL Subprograms(Procedure, Function)
• 9. Concept of Triggers and there use
• 10. PL/SQL Package (System and User defined)
• 11. Handling PL/SQL Errors
• 12. Tuning PL/SQL Applications for Performance
• 13. How PL/SQL Optimizes Your Programs
• 14. When to Tune PL/SQL Code
• 15. Guidelines for Avoiding PL/SQL Performance Problems
• 16. Collecting Data About User-Defined Identifiers
• 17. Reducing Loop Overhead for DML Statements and Queries with Bulk SQL
• 18. Writing Computation-Intensive PL/SQL Programs
• 19. Tuning Dynamic SQL with EXECUTE IMMEDIATE Statements and Cursor Variables
• 20. Tuning PL/SQL Subprogram Calls with NOCOPY Hint
• 21. Compiling PL/SQL Units for Native Execution
• 22. Performing Multiple Transformations with Pipelined Table Functions

UNIX (Beginer to Modirate)

• 1. Unix Getting Started
• 2. Unix File Management
• 3. Unix Directories
• 4. Unix File Permission Setup
• 5. Unix Environment
• 6. Unix Pipes and Filters
• 7. Unix Processes Management
• 8. Unix Communication(ftp,telnet,finger,ping)
• 9. Unix – The vi Editor
• 10. Unix- What is Shell
• 11. Unix- Using Variables
• 12. Unix- Special Variables
• 13. Unix – Using Arrays
• 14. Unix - Basic Operators(C Shell and Korn Shell)
• 15. Unix – Decision Making(if..else..if,if...elif...else...fi,case,loop etc.)
• 16. Unix – Shell Substitutions
• 17. Unix – Quoting Mechanisms
• 18. Unix – IO Redirections
• 19. Unix – Shell Functions
• 20. Unix - Manpage Help
• 21. Unix - Regular Expressions
• 22. Unix – File System Basics
• 23. Unix – User Administration
• 24. Unix – System Performance
• 25. Unix – System Logging
• 26. Unix – Signals and Traps
• 27. Unix – Useful Commands
• 28. Unix – Builtin Functions

Interview Questions

• 1. Oracle Architecture Interview Question
• 2. SQL Interview Questions
• 3. PL/SQL Interview Questions
• 4. Unix Interview Questions
• 5. OCA-OCP Dump Questions details discussion and solution