Oracle PL-SQL, DBA Training
![Intellipaat](https://static.mytechlogy.com/cache/120eb50138d5e4ddec9e3d84afbf6e36_w30_h30_cp_sc.gif)
Intellipaat
Course Summary
Our Oracle PL/SQL certification master training program lets you gain proficiency in Oracle database. We provide the best online training classes to help you learn SQL commands, DBA commands, schema management, database structure, integrity, security. Work on real world industry projects.
-
+
Course Description
About Oracle PL-SQL, DBA Training Course
This is a Combo Training Course that provides you full proficiency in working with Oracle Database. This includes understanding the PL/SQL, subprogram, section and syntax query, DML and advanced DML with scripting. Study Oracle DBA Architecture, database structures, security schema objects and more.
What you will learn in this Training Course?
- Introduction to Oracle 11g database
- Manage, design, build database applications
- PL/SQL sections – Declaration, Execution and Exception Handling
- Major SQL command groups namely DDL, DML, DCL, TCL
- Oracle DBA roles and responsibilities
- Database integrity constraints, schema management and database structural definitions
- Learn about security, data backup and recovery
- Install, configure and manage the Oracle Cloud
Who should take this Training Course?
- Software Developers, Database Administrators, BI and Data Warehousing professionals
- Project Managers, Architects, SQL and analytics professionals
What are the prerequisites for taking this Training Course?
Having a prior knowledge of SQL is beneficial but not mandatory.Why should you take this Oracle PL/SQL, DBA Combo Training Course?
Upon completion of this Combo Training Course you will be able to apply for high-paying jobs in large MNCs thanks to the comprehensive training that you will get through this Combo Course. The role of the DBA is crucial in managing huge volumes of data. PL/SQL is a processing language that is used for high-performance transaction that works seamlessly across OS and front-end tools. Together this Combo Training will help you work on the Oracle 11g database with effortless ease and take up high-paying jobs.
-
+
Course Syllabus
Oracle PL SQL Course Content
Introduction to Oracle SQLWhat is RDBMS, Oracle versions, Architecture of Oracle Database Server, Installation of Oracle 12cHands-on Exercise – Install Oracle 12c
Using DDL Statements to Create and Manage TablesCategorize the main database objects, Review the table structure, List the data types that are available for columns, Create a simple table, Explain how constraints are created at the time of table creation, Describe how schema objects workHands-on Exercise – Create a database table Person with two column (Name, Age) with constraint on age not greater than 100, Insert records using insert query
Retrieving Data Using the SQL SELECT StatementList the capabilities of SQL SELECT statements, Execute a basic SELECT statement
Hands-on Exercise – Use a basic select statement to retrieve all the records in the Person table
Restricting and Sorting DataLimit the rows that are retrieved by a query, Sort the rows that are retrieved by a query, Ampersand substitution to restrict and sort output at runtimeHands-on Exercise – Write a select query to retrieve records where age is more than 60 yrs, Write a select query to sort the records by name, Write a select query to sort the records by age in descending order, Use ampersand substitution to restrict and sort output at runtime
General functionsThe general functions in SQL, working with any data type and handling Null values, using COALESCE() and Null function, Constructing and executing SQL query that applies the NUL, NUL1, NUL2 and COALESCE()Hands-on Exercise – Use NULL function to deal with null values in data
Using Single-Row Functions to Customize OutputDescribe various types of functions (character, number, date, string etc.) available in SQLHands-on Exercise – Create a table with columns of type char, number and date, Use character, number, and date functions in SELECT statements
Large Object FunctionsLarge object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB, Aggregate or Group functions – COUNT, COUNT(*),MIN,MAX,SUM,AVG,etc…,Group BY Clause, HAVING ClauseHands-on Exercise – Count records based on a condition, Use Count(*) to know the count of all records, Find Max, Min, Sum, Avg
OLAP FunctionsThe various OLAP functions, cube, model clause, roll up and grouping functionsHands-on Exercise – Working with OLAP commands – Cube, Roll Up, etc.
Using Conversion Functions and Conditional ExpressionsDescribe various types of conversion functions that are available in SQL, Conditional expressions in a SELECT statementHands-on Exercise – Group data by using the GROUP BY clause, Include or exclude grouped rows by using the HAVING clause
Displaying Data from Multiple TablesJoins, Inner Join, Outer Join, Left Join, Right Join, Equijoins and Non-equijoinsHands-on Exercise – Write SELECT statements to access data from more than one table using equijoins and non equijoins, Join a table to itself by using a self-join, View data that generally does not meet a join condition by using outer joins, Generate a Cartesian product of all rows from two or more tables
Using Subqueries to Solve QueriesDefine subqueries, Describe the types of problems that the subqueries can solve, List the types of subqueriesHands-on Exercise – Write single-row and multiple-row subqueries
Using the Set OperatorsDescribe set operators, UNION [ALL], INTERSECT, MINUS OperatorsHands-on Exercise – Use a set operator to combine multiple queries into a single query Control the order of rows returned
Manipulating Data using SQLDescribe data manipulation language (DML) statement, Insert, Update, Delete Statements, Control transactionsHands-on Exercise – Insert rows into a table, Update rows in a table, Delete rows from a table
Database TransactionsWhat is a database transaction, Properties of a transaction (Atomic, Consistent, Isolated, Durable – ACID), Avoiding error/fault in manipulating database records using transactionHands-on Exercise – Begin a transaction, Execute queries to update or insert or delete records, If no error, commit the transaction Else roll back the transaction and end it
Creating Other Schema ObjectsViews – simple and complex, Sequences, Index, SynonymHands-on Exercise – Create simple and complex views, Retrieve data from views, Create, maintain, and use sequences, Create and maintain indexes, Create private and public synonyms
Writing Cursor and Conditional StatementSQL Cursor, SQL Cursor Attributes., Controlling PL/SQL flow of executions, IF Statement, Simple IF Statement, IF-THAN-ELSE Statement Execution Flow, IF-THAN-ELSE Statement, IF-THAN-ELSIF Statement, Logic Tables, Boolean Conditions., Iterative controlling loop statement, Nested Loops and Labels.Hands-on Exercise – Use Boolean condition on a select query, Use logic table, Create nested loops and labels
Introduction to Explicit CursorWriting Explicit cursors, About cursors, Explicit cursor functions, Controlling explicit cursor, Opening the cursor, Fetching Data cursor, Cursor and records, Cursor for loop using sub queriesHands-on Exercise – Write an explicit cursor, Use cursor function, Fetch data cursor
Advance concepts of explicit CursorAdvance Explicit cursor, Cursor with parameters, For update Clause, Where current of Clause, Cursor with sub queriesHands-on Exercise – Use cursor with subquery, Use Where Current clause to retrieve data
Exception HandlingHandling Exception, Handling Exception with PL/SQLPredefined Exceptions, User Defined Exceptions, Non-Predefined Error, Function for trapping Exception, Trapping user-defined Exception, Raise Application Error ProcedureHands-on Exercise – Use Predefined exception, Write user defined exception, Generate and handle exception, Use a function for trapping an exception
Writing Subprogram, Procedure and passing parametersOverview of subprograms, PL/SQL Subprograms, What is Procedure, Syntax for creating Procedure, Creating Procedure with parameter, Example of Passing parameters, Referencing a public variable from a standalone procedure, Declaring SubprogramHands-on Exercise – Create a parameterized procedure, Pass parameters in a procedure call, Access a public variable from a standalone procedure
Creating PL/SQL PackagePL/SQL records, Using Pl/SQL Table method and example, Creating PL/SQL Table, Packages – Objective, overview, component, developing, removing, advantages, Creating the package specification/example, Declaring Public construct, Public and private construct, Invoking package construct, Guide lines for deploying packagesHands-on Exercise – Create a package, Deploy the created package
Advance Package Concepts and functionsOverloading, Using forward declaration, One time only procedure, Package functions, User define package function, Persistent state of package function, Persistent state of package variable, Controlling the persistent state of package cursor, Purity end, Using supplied package, Using native dynamic SQL, Execution flow, Using DBMS-SQL package, Using DBMS-DDL package, Submitting jobs, Interacting with operating system linksHands-on Exercise – Use supplied package, Use native dynamic SQL, Use DBMS-SQL package, Use DBMS-DDL package, Submit a job
Introduction and writing TriggersTriggers – Definition, objective and its event type, Application & database triggers, Business application scenarios for implementing triggers, Define DML triggers, Define Non – DML triggers, Triggers event type & body, Creating DML triggers using the create triggers statement, Define statement level triggers v/s low level triggers, Triggers firing sequence: single row manipulation, Creating a DML statement triggers, Using old and new qualifiers, Old and new qualifiers, Instead of triggers, Managing triggers using the alter & drop SQL statement, Testing triggersHands-on Exercise – Create a DML statement trigger, Use old and new qualifiers, Manage a trigger using the alter & drop SQL statement, Test the created triggers
Compound TriggersViewing trigger information, Describe user triggers, What is a compound trigger and working with it, Compound trigger structure for tables, Timing-point sections of a table compound trigger, Compound trigger structure for views, Trigger restrictions on mutating tables, Compound trigger restrictions, Using a compound trigger to resolve the mutating table error, Creating triggers on system events, LOGON and LOGOFF triggers example, Call statements in triggers, Benefits of database-event triggers, System privileges required to manage triggersHands-on Exercise – View a trigger’s information, Use a compound trigger structure for views, Use a compound trigger to resolve the mutating table error, Create triggers on system events, Use LOGON and LOGOFF triggers, Call statements in triggers
Working with Dynamic SQLDynamic SQL-objectives, Describe execution flow of SQL statements, Dynamic SQL with a DDL statement-example, Working with dynamic SQL, Native Dynamic SQL(NDS), Using the executive immediate statement-example, Using native dynamic SQL to compile PL/SQL code, Using DBMS-SQL with a DML statement, Using the DBMS-SQL package subprograms, Parameterized DML statementHands-on Exercise – Use the executive immediate statement, Use native dynamic SQL to compile PL/SQL code, Create DBMS-SQL with a DML statement, Create a DBMS-SQL package subprograms
Advance level- ScriptingManaging Dependencies, Objectives, overview of schema object dependencies, Direct local dependencies, Querying direct object dependencies, Displaying direct and indirect dependencies, Fine-Grained dependency management, Changes to synonym dependencies, Maintaining valid PL/SQL program units and views, Object re-validation, Concepts of remote dependencies, Setting the remote dependencies mode parameter, Recompiling PL/SQL program unit, Packages and Dependencies, Successful and unsuccessful recompilation, Recompiling proceduresHands-on Exercise – Query direct object dependencies, Display direct and indirect dependencies, Set the remote dependencies mode parameter, Recompile PL/SQL program unit, Edit a procedure and recompile it
Oracle DBA Course Content
Database IntroductionWhat is the Oracle database? How it functions? Installation of Oracle database.
Oracle Database ArchitectureThe Architecture of Oracle server, the key components – Oracle Instance and Oracle Database, the various file types – data files, control files, redo log files.
Oracle Storage StructuresThe basics of Oracle Storage structures, logical storage units like tablespaces, data blocks, extents and segments, database Schemas, Schema Objects, Operating System blocks, table statement and way to check “create tableâ€.
Memory & Process ArchitectureThe Oracle Instance consists of background processes and memory structures, learn about the Oracle process and memory architecture, shared pool, redo log buffer, and buffer cache.
Alert & Trace filesThe Oracle background process errors can be monitored using the Trace Files and Alert Logs, learn how this can be deployed for Oracle Administration and support.
Database Startup & User RequestsThe methodology of starting a database instance, initializing parameter files, preparing for startup, serving user requests, understanding of the Server process and user process.
Database SecurityLearn about the powerful Oracle database security features, data privacy, regulatory compliance, “create user†process, altering and dropping users, generating profiles and limiting resources, auditing, activity monitoring and blocking.
Database Schema ObjectsUnderstanding of Database Schema objects, the various types available like views, tables, clusters, indexes, sequence, database links, packages and procedures, learning how data is stored in database tables, creating of temporary tables and external tables.
Deep dive into Schema ObjectsMastering the database Schema objects, learning Materialized View, deploying user-generated schema object for generating sequence, the balanced search tree index structure for placing and locating files, learn about data concurrency and data consistency in multi-user databases, the concepts of Locking and Deadlocks.
Oracle Network EnvironmentStudy the client/server Network Environment, configuring the network, connecting to the database, the Oracle Network Environment and database link.
Oracle Backup & RecoveryUnderstand how Oracle database ecosystem backup and recovery is deployed, the database testing, database standby, Media recovery options, backup in offline mode.
Oracle Recovery Manager (RMAN)Learn the significance of Oracle Recovery Manager, how it deploys performance, management and restoration of entire databases, learn about SQL Loader, Oracle Data Pump for import and export and support for External Table.
Data Dictionary & Dynamic Performance TablesThe Oracle Data Dictionary contains information related to database privileges, objects, users, and roles, learn Dynamic Performance Table for identifying instance-level problems, get complete knowledge to work with Oracle DBA.
Database TuningIntroduction to database tuning, ensuring higher throughput, lowering response time of time-critical applications, optimizing the storage space, SQL scripts, deploying Index Selectivity, Index Statistics and Table Statistics for tuning the database, understanding Oracle Chained Rows and Locks.
Database Tuning ContinuedDeep diving into Oracle Database Tuning, shared pool tuning, improving performance using Data Dictionary, tuning of Data Dictionary, deploying PL/SQL code, code reuse, database buffer.
Oracle PL SQL ProjectProject – DML
Topics – In this project you will learn about the Data Manipulation Language (DML), understand all about the DML triggers which are a special type of stored procedures that are automatically executed when DML operation is performed. You will learn about the various DML operations like INSERT, UPDATE, or DELETE which when fired on a Table or View can activate a DML Trigger. This project involves working with database and application Triggers, deploying the Triggers for various business applications and testing of the DML Triggers.
Oracle DBA ProjectProject – Database Tuning
– This project is involved with working on the Oracle Database for deploying the database performance tuning which includes optimizing and homogenizing the database performance. It is associated with query tuning but also includes DBMS selection, and configuration of the database environment. Some of the tasks that you will perform are–
- Space usage monitoring
- SQL scripts monitoring
- Database & SQL tuning
- Table & Index Statistics
- Chained Rows & Locks