Welcome to Oracle DBA
Database or simply DB is an organized and structured collection of information which can be to efficiently retrieved, updated and analyzed. A simple VB code, C, excel or notepad cannot manage large number of users and files. Also easy extraction, backup and restoration of data is not possible with this approach. To counter this limitation Oracle has come up with Oracle database or Oracle RDBMS.
Why Oracle DBA ?
Automatic Storage
Clusterware
Data Aggregation
Data Guard
Course Content - Oracle DBA
Introduction to Oracle and SQL
- Overview of Oracle Database 12c and related products
- Overview of relational database management concepts and terminologies
- Introduction to SQL and its development environments
- Introduction to Schema and Schema Objects
- The HR schema and the tables used in this course
- Oracle Database documentation and additional resources
Retrieve Data using the SQL SELECT Statement
- List the capabilities of SQL SELECT statements
- Generate a report of data from the output of a basic SELECT statement
- Use arithmetic expressions and NULL values in the SELECT statement
- Invoke Column aliases
- Concatenation operator, literal character strings, alternative quote operator, and the DISTINCT Keyword
- Display the table structure using the DESCRIBE command
- Using Dual table
Restricting and Sorting Data
- Write queries with a WHERE clause to limit the output retrieved
- Describe the comparison operators, logical operators and relational operators
- Describe the rules of precedence for comparison and logical operators
- Usage of character string literals in the WHERE clause
- Write queries with an ORDER BY clause
- Sort the output in descending and ascending order
- Sorting by column alias, column position and multiple columns
SQL * PLUS Commands
- Formatting Data Using SQL * PLUS Commands
- Setting Pagesize and Linesize
- Formatting Columns
- Generating Scripts for Query Automation
- Using Substitution and Fixed Variables
- Editing SQL Statements
- Connecting and Disconnecting From Database, Exiting SQL* PLUS
Usage of Single-Row Functions to Customize Output
- List the differences between single row and multiple row functions
- Manipulate strings using character functions
- Manipulate numbers with the ROUND, TRUNC, and MOD functions
- Perform arithmetic with date
- Manipulate dates with the DATE functions
Conversion Functions and Conditional Expressions
- Describe implicit and explicit data type conversion
- Describe the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
- Nesting multiple functions
- Using General Functions to Process NULL Values
- Apply the NVL, NULLIF, and COALESCE functions to data
- Usage of conditional IF THEN ELSE logic in a SELECT statement
Aggregated Data Using the Group Functions
- Usage of the aggregation functions in SELECT statements to produce meaningful reports
- Describe the AVG, SUM, COUNT,MIN and MAX function
- How to handle Null Values in a group function?
- Divide the data in groups by using the GROUP BY clause
- Exclude groups of data by using the HAVING clause
Display Data From Multiple Tables
- Write SELECT statements to access data from more than one table
- Join Tables Using SQL:1999 Syntax
- View data that does not meet a join condition by using outer joins
- Join a table to itself by using a self join
- Creating Cartesian Product
- Using Natural joins
Usage of Sub queries to Solve Queries
- Use a Subquery to Solve a Problem
- Single-Row Subqueries
- Group Functions in a Subquery
- Multiple-Row Subqueries
- Use the ANY and ALL Operator in Multiple-Row Subqueries
- Use the EXISTS and NOT EXISTS Operator
- Understanding Correlated Sub query and Inline Views
DDL Statements to Create and Manage Tables
- Categorize Database Objects
- Create Tables
- Describe the data types
- Understanding Constraints
- Types of Constraints
- Enabling and Disabling Constraints
- Create a table using a subquery
- How to alter a table?
- How to drop a table?
- Flashback Queries
Data Manipulation
- Add New Rows to a Table
- Change the Data in a Table
- Use the DELETE Statements
- Performing Conditional UPSERT using MERGE
- What is Transaction Control Language
- How to save and discard changes with the COMMIT and ROLLBACK statements
- Creating Savepoints
- Implement Read Consistency
Control User Access using DCL statements
- Understanding difference between system privileges and object privileges
- Creating a user
- Granting privileges to user
- Create and Grant Privileges to a Role
- Changing password, locking and unlocking users
- Revoking a Privilege
Other Schema Objects
- Create, modify, and retrieve data from a view
- Perform Data manipulation language (DML) operations on a view
- How to drop a view?
- Views with check options, with read only options
- Creating a Force View
- Create, use and modify a sequence
- Using Nextval and Currval Pseudocolumns in Sequence
- Create and drop indexes
- Types of Indexes
- Create and drop synonyms
- Types of Synonym
- Understanding Pseudo columns
- Types of Pseudo columns
- Using Rowid,Rownum
- Using Sysdate and Systimestamp Pseudo columns
SQL * LOADER
- Understanding data loading concepts
- Input Data Files
- Creating Control Files
- Bad files
- From Data File to Database Tables
REGULAR EXPRESSIONS
- Understanding Regular Expressions Functions
- Using Meta characters with Regular Expressions
- Finding Patterns Using REGEXP_INSTR Functions
- Performing Basic search with REGEXP_LIKE Functions
- Using REGEXP_SUBSTR To Extract Part Of String
- Replacing a Pattern Using REGEXP_REPLACE Functions
- Implementing REGEXP_COUNT Function
Analyzing Data
- Using Analytic Functions
- Using the Ranking Functions
- Using the LAG() and LEAD() Functions
- Using the FIRST and LAST Value Functions
- Using the PIVOT and UNPIVOT Clauses
- Performing Top-N Queries
- Using WITH Clause
ROLLUP and CUBE Clause
- Understanding about Group Processing
- Simple Group By
- Using Roll Up and Cube
- Using Grouping Functions and Grouping Sets
- Hierarchical Queries
- Understanding Hierarchical Queries
- Using CONNECT BY and Start With Clause
- Using LEVEL Pseudo column
- Working with Connect By Prior and Connect By Level
SET Operators
- Describe the SET operators
- Use a SET operator to combine multiple queries into a single query
- Describe the UNION, UNION ALL, INTERSECT, and MINUS Operators
- Use the ORDER BY Clause in Set Operations
Objects with Data Dictionary Views
- Explain the Data Dictionary
- Using Dictionary Views
- User_objects, Dba_objects, All_objects Views
- Query the Dictionary Views for Table, Column, Constraints Information
- Query the Dictionary Views for Sequence, Synonym, View and Index
Introduction to PL/SQL
- PL/SQL Overview
- List the benefits of PL/SQL Subprograms
- Overview of the Types of PL/SQL blocks
- Create a Simple Anonymous Block
- Generate the Output from a PL/SQL Block
PL/SQL Identifiers
- List the different Types of Identifiers in a PL/SQL subprogram
- Usage of the Declarative Section to Define Identifiers
- Use of variables to store data
- Scalar Data Types
- %TYPE Attribute
- Bind Variables
- Sequences in PL/SQL Expressions
Write Executable Statements
- Basic PL/SQL Block Syntax Guidelines
- How to comment code?
- SQL Functions in PL/SQL
- Data Type Conversion
- Nested Blocks
- Operators in PL/SQL
Interaction with the Oracle Server
- SELECT Statements in PL/SQL to Retrieve data
- Data Manipulation in the Server Using PL/SQL
- The SQL Cursor concept
- Learn to use SQL Cursor Attributes to Obtain Feedback on DML
- How to save and discard transactions?
Control Structures
- Conditional processing Using IF Statements
- Conditional Processing Using IF ELSE Statements
- Conditional processing Using CASE Statements
- Simple Loop Statement
- While Loop Statement
- For Loop Statement
- The Continue, Exit and Goto Statements
Explicit Cursors
- Understand Cursors and its types
- Declare the Cursor
- How to open the Cursor?
- Fetching data from the Cursor
- How to close the Cursor?
- Cursor FOR loop
- Explicit Cursor Attributes
- FOR UPDATE OF Clause and WHERE CURRENT OF Clause
Exception Handling
- Understanding Error and Exceptions
- Handle Exceptions with PL/SQL
- Trap Predefined Oracle Server Errors
- Trap Non-Predefined Oracle Server Errors
- Trap User-Defined Exceptions
- Propagate Exceptions
- RAISE_APPLICATION_ERROR Procedure
Stored Procedures and Functions
- What are Stored Procedures and Functions?
- Differentiate between anonymous blocks and subprograms
- Create a Simple Procedure
- Passing Parameters to a procedure
- Create a Simple Function
- Issues with Boolean
- Addressing Compilation Errors
- Overloading Concepts in Procedure and Function
- Execute a Simple Procedure
- Execute a Simple Function
Packages
- Describe Packages
- What are the components of a Package?
- Develop a Package
- How to enable visibility of a Package’s Components?
- Create the Package Specification and Body using the SQL CREATE Statement and SQL
Developer
- Invoke the Package Constructs
- Forward Declaration in a Package
- Listing the advantages of Packages
- View the PL/SQL Source Code using the Data Dictionary
Triggers
- Purpose of Triggers
- Identify the Trigger Event, Types, Body and Firing(Timing)
- Business Application Scenarios for Implementing Triggers
- Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
- Differences between Statement Level Triggers and Row Level Triggers
- Create Instead of and Disabled Triggers
- Use Pragma Autonomous_transaction in triggers
- How to Manage, Test and Remove Triggers?
Implement Oracle-Supplied Packages in Application Development
- DBMS_OUTPUT Package
- Use the UTL_FILE Package to Interact with Operating System Files
- Usage of Composite Data Types
- Creating Collection Types
- Associative Arrays (INDEX BY Tables)
- INDEX BY Table Methods
- INDEX BY Table of Records
- Create and Use Nested Tables
- Describe and use Varrays
- PL/SQL Records
- The %ROWTYPE Attribute
- Using Array Types
Multitable Inserts and Global Temporary Table
- Listing Types of Multitable insert statements
- Using Insert all and Insert First statement
- Using conditional and unconditional insert statements
- What is Global Temp Table
- Using On Commit Delete Rows
- Using On Commit Preserve Rows
Dynamic SQL and Ref Cursors
- Understanding Dynamic SQL Concepts
- Using Execute Immediate Keyword
- Using Clause in Dynamic SQL
- Ref Cursor (dynamic cursors)
- Using Ref Cursors to Return Values
- Strong Ref Cursor
- Week Ref Cursor
Bulk Collection and Bulk Binding
- Using Bulk Collections
- Using Limit in Bulk Collection
- Bulk Binding DML Statements using FORALL
- Trapping Bulk Exceptions
Materialized View
- Understanding Materialized view
- Differentiate View from Materialized View
- Using Different Refresh modes
- Creating Logs for Refresh
Performance Tuning
- Approaches to Resolve Performance Issues
- Using Explain Plan to interpret output
- Using set Auto trace command
- Adding Index on columns for Faster Retrieval
- Passing Hints to the Optimizer
- Concepts of Table Partition Oracle Architecture
- Overview of Oracle Architecture
- How Oracle Handles Query Execution with Background Process
Other Stuffs
- UNIX Commands
- Unix Shell Scripting Basics
- Working Knowledge on EXCEL
- Resume Preparation Tips
- Interview Question Discussions
We can help you choose the right university and guide you in completing the application process.
Kanagha Divya
Database Administrator
Manirathnam
Java Developer
Aruna
Android Developer
Lakshmanan
Dot Net Developer
Kanagha Divya
Database Administrator
Manirathnam
Java Developer
Aruna
Android Developer
Lakshmanan
Dot Net Developer
Kanagha Divya
Database Administrator
Manirathnam
Java Developer
Aruna
Android Developer
Lakshmanan
Dot Net Developer
Infycle Technologies
Let Profession Search You