Transcription
Oracle PL/SQL and DBACertification TrainingOracle PL/SQL and DBA Certification Training1 Page
Table of Contents1.About the Program2.About Intellipaat3.Key Features4.Career Support5.Why take up this course?6.Who should take up this course?7.Program Curriculum8.Project Work9.Certification10.Intellipaat Success Stories11.Contact UsOracle PL/SQL and DBA Certification Training2 Page
About the ProgramOur Oracle PL/SQL certification master's training program lets you gain proficiency inOracle Database. We provide the best online training classes to help you learn SQLcommands, DBA commands, schema management, database structure, integrity, andsecurity. As part of this program, you will work on real-world industry projects.About IntellipaatIntellipaat is one of the leading e-learning training providers with more than 600,000learners across 55 countries. We are on a mission to democratize education as webelieve that everyone has the right to quality education.Our courses are delivered by subject matter experts from top MNCs, and our world-classpedagogy enables learners to quickly learn difficult topics in no time. Our 24/7 technicalsupport and career services will help them jump-start their careers in their dreamcompanies.Oracle PL/SQL and DBA Certification Training3 Page
Key Features28 HRS INSTRUCTOR-LEDTRAINING28 HRS SELF-PACED TRAINING56 REAL-TIMEPROJECT WORKLIFETIME ACCESS24/7 TECHNICAL SUPPORTINDUSTRY-RECOGNIZEDCERTIFICATIONJOB ASSISTANCE THROUGH80 CORPORATE TIE-UPSFLEXIBLE SCHEDULINGOracle PL/SQL and DBA Certification Training4 Page
Career SupportSESSIONS WITH INDUSTRY MENTORSAttend sessions from top industry experts and get guidance on how to boostyour career growthMOCK INTERVIEWSMock interviews to make you prepare for cracking interviews by top employersGUARANTEED INTERVIEWS & JOB SUPPORTGet interviewed by our 400 hiring partnersRESUME PREPARATIONGet assistance in creating a world-class resume from our career services teamOracle PL/SQL and DBA Certification Training5 Page
Why take up this course?Upon the completion of this combo training course, you will be able to apply for highpaying jobs in large MNCs, thanks to the comprehensive training that you will get here.The role of a DBA is crucial in managing huge volumes of data. PL/SQL is a processinglanguage that is used for high-performance transactions, which works seamlessly acrossoperating systems and front-end tools. Together, this combo training will help you work onthe Oracle 11g Database with effortless ease and take on high-paying jobs.Who should take up this course? Software Developers, Database Administrators, and BI and Data WarehousingProfessionalsProject Managers, Architects, and SQL and Analytics ProfessionalsOracle PL/SQL and DBA Certification Training6 Page
Program CurriculumOracle PL/SQL Course ContentINTRODUCTION TO ORACLE SQLWhat is RDBMS? Oracle versions, architecture of the Oracle Database server, and theinstallation of Oracle 12cHands-on Exercise: Install Oracle 12cUSING DDL STATEMENTS TO CREATE & MANAGE TABLESCategorize the main database objects, review the table structure, list the data types thatare available for columns, create a simple table, explain how constraints are created at thetime of table creation, and describe how schema objects workHands-on Exercise: Create a database table Person with two columns (Name and Age)with a constraint on age not greater than 100 and insert records using the insert queryRETRIEVING DATA USING THE SQL SELECT STATEMENTList the capabilities of SQL SELECT statements and execute a basic SELECT statementHands-on Exercise: Use a basic select statement to retrieve all the records in the PersontableRESTRICTING & SORTING DATALimit the rows that are retrieved by a query, sort the rows that are retrieved by a query, anduse the ampersand substitution to restrict and sort the output at the runtimeHands-on Exercise: Write a select query to retrieve records where age is more than 60years, write a select query to sort the records by name, write a select query to sort therecords by age in the descending order, and then use the ampersand substitution torestrict and sort the output at the runtimeGENERAL FUNCTIONSOracle PL/SQL and DBA Certification Training7 Page
General functions in SQL, working with any data type and handling Null values, usingCOALESCE() and the Null function, constructing and executing a SQL query that appliesNUL, NUL1, NUL2 and COALESCE()Hands-on Exercise: Use the NULL function to deal with null values in dataUSING SINGLE-ROW FUNCTIONS TO CUSTOMIZE THE OUTPUTDescribe various types of functions (character, number, date, string, etc.) available in SQLHands-on Exercise: Create a table with columns of types char, number, and date and usecharacter, number, and date functions in SELECT statementsLARGE OBJECT FUNCTIONSLarge object functions: BFILENAME, EMPTY BLOB, and EMPTY CLOB, aggregate orgroup functions: COUNT, COUNT(*), MIN, MAX, SUM, AVG, etc., the GROUP BY clause,and the HAVING clauseHands-on Exercise: Count records based on a condition, use Count(*) to know the countof all records, and find Max, Min, Sum, and AvgOLAP FUNCTIONSVarious OLAP functions, cube, the model clause, and roll up and grouping functionsHands-on Exercise: Working with OLAP commands: Cube, Roll up, etc.USING CONVERSION FUNCTIONS & CONDITIONAL EXPRESSIONSDescribe various types of conversion functions that are available in SQL and conditionalexpressions in a SELECT statementHands-on Exercise: Group data by using the GROUP BY clause and include or excludegrouped rows by using the HAVING clauseDISPLAYING 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 tableusing equijoins and non-equijoins, join a table with itself by using a self-join, view data thatgenerally does not meet a join condition by using outer joins, and generate a Cartesianproduct of all rows from two or more tablesOracle PL/SQL and DBA Certification Training8 Page
USING SUBQUERIES TO SOLVE QUERIESDefine subqueries, describe the types of problems that the subqueries can solve, and listthe types of subqueriesHands-on Exercise: Write single-row and multiple-row subqueriesUSING THE SET OPERATORSDescribe set operators, UNION [ALL], INTERSECT, and MINUS operatorsHands-on Exercise: Use a set operator to combine multiple queries into a single queryand control the order of rows returnedMANIPULATING DATA USING SQLDescribe data manipulation language (DML) statements: insert, update, and deletestatements and control transactionsHands-on Exercise: Insert rows into a table, update rows in a table, and delete rows froma tableDATABASE TRANSACTIONSWhat is a database transaction? Properties of a transaction (Atomic, Consistent, Isolated,Durable – ACID) and avoiding errors/faults in manipulating database records using atransactionHands-on Exercise: Begin a transaction, execute queries to update or insert or deleterecords; if no error, commit the transaction; otherwise, roll back the transaction, and end itCREATING OTHER SCHEMA OBJECTSViews – simple and complex, sequences, indexes, and synonymsHands-on Exercise: Create simple and complex views, retrieve data from views, create,maintain, and use sequences, create and maintain indexes, and create private and publicsynonymsWRITING CURSOR & CONDITIONAL STATEMENTSSQL 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,Oracle PL/SQL and DBA Certification Training9 Page
IF-THAN-ELSIF statement, logic tables, Boolean conditions, iterative controlling loopstatement, and nested loops and labelsHands-on Exercise: Use the Boolean condition on a select query, use a logic table, andcreate nested loops and labelsINTRODUCTION TO EXPLICIT CURSORWriting explicit cursors, about cursors, explicit cursor functions, controlling the explicitcursor, opening the cursor, fetching the data cursor, cursor and records, cursor for loopusing subqueriesHands-on Exercise: Write an explicit cursor, use the cursor function, and fetch the datacursorADVANCED CONCEPTS OF EXPLICIT CURSORAdvanced explicit cursor, the cursor with parameters, the For Update clause, the WhereCurrent clause, and the cursor with subqueriesHands-on Exercise: Use a cursor with a subquery and use the Where Current clause toretrieve dataEXCEPTION HANDLINGHandling exception, handling exception with PL/SQL predefined exceptions, user-definedexceptions, non-predefined errors, the function for trapping exception, trapping userdefined exceptions, and raising application error procedureHands-on Exercise: Use predefined exception, write user-defined exception, generateand handle exceptions, and use a function for trapping an exceptionWRITING SUBPROGRAM, PROCEDURES, & PASSING PARAMETERSOverview of subprograms, PL/SQL subprograms, and procedures, syntax for creating aprocedure, creating a procedure with parameters, example of passing parameters,referencing a public variable from a standalone procedure, and declaring a subprogramHands-on Exercise: Create a parameterized procedure, pass parameters in a procedurecall, and access a public variable from a standalone procedureOracle PL/SQL and DBA Certification Training10 P a g e
CREATING PL/SQL PACKAGEPL/SQL records, using the PL/SQL table method and example, creating the PL/SQL table,packages: objective, overview, components, developing and removing, and advantages,creating the package specification/example, declaring the public construct, public andprivate constructs, invoking the package construct, and guide lines for deploying packagesHands-on Exercise: Create a package and deploy the created packageADVANCED PACKAGE CONCEPTS & FUNCTIONSOverloading, using forward declaration, the one-time only procedure, package functions,the user-defined package function, persistent state of the package function, persistentstate of the package variable, controlling the persistent state of the package cursor, purityend, using supplied package, using native dynamic SQL, execution flow, using the DBMSSQL package, using the DBMS-DDL package, submitting jobs, and interacting withoperating system linksHands-on Exercise: Use the supplied package, use native dynamic SQL, use the DBMSSQL package, use the DBMS-DDL package, and submit a jobINTRODUCTION & WRITING TRIGGERSTriggers: Definition, objectives, and its event types, application and database triggers,business application scenarios for implementing triggers, DML triggers, non-DML triggers,triggers event type and body, creating DML triggers using the create triggers statement,statement-level triggers vs low-level triggers, triggers firing sequence, single-rowmanipulation, creating a DML statement trigger, using old and new qualifiers instead oftriggers, managing triggers using the alter and drop SQL statement, and testing triggersHands-on Exercise: Create a DML statement trigger, use old and new qualifiers, managea trigger using the alter and drop SQL statements, and test the created triggersCOMPOUND TRIGGERSViewing trigger information, user triggers, compound trigger and working with it, compoundtrigger structure for tables, timing-point sections of a table compound trigger, compoundtrigger structure for views, trigger restrictions on mutating tables, compound triggerrestrictions, using a compound trigger to resolve the mutating table error, creating triggerson system events, LOGON and LOGOFF triggers examples, call statements in triggers,benefits of database-event triggers, and the system privileges required to manage triggersOracle PL/SQL and DBA Certification Training11 P a g e
Hands-on Exercise: View a trigger’s information, use a compound trigger structure forviews, use a compound trigger to resolve the mutating table error, create triggers onsystem events, use LOGON and LOGOFF triggers, and use call statements in triggersWORKING WITH DYNAMIC SQLDynamic SQL objectives, execution flow of SQL statements, dynamic SQL with a DDLstatement and an example, working with dynamic SQL, native dynamic SQL (NDS), usingthe executive immediate statement and an example, using native dynamic SQL to compilePL/SQL code, using DBMS-SQL with a DML statement, and using the DBMS-SQLpackage subprograms and the parameterized DML statementHands-on Exercise: Use the executive immediate statement, use native dynamic SQL tocompile PL/SQL code, create DBMS-SQL with a DML statement, and create a DBMS-SQLpackage subprogramADVANCED-LEVEL SCRIPTINGManaging dependencies, objectives, overview of schema object dependencies, direct localdependencies, querying direct object dependencies, displaying direct and indirectdependencies, fine-grained dependency management, changes to synonymdependencies, maintaining valid PL/SQL program units and views, object re-validation,concepts of remote dependencies, setting the remote dependencies mode parameter,recompiling the PL/SQL program unit, packages and dependencies, successful andunsuccessful recompilation, and recompiling proceduresHands-on Exercise: Query direct object dependencies, display direct and indirectdependencies, set the remote dependencies mode parameter, recompile the PL/SQLprogram unit, edit a procedure, and recompile itOracle 12c DBA Course ContentDATABASE INTRODUCTIONWhat is Oracle Database? How does it function? What are its prerequisites? Multiple nonCDB’s share nothing, Oracle Database 12c installation, multitenant architecture, non-CDBOracle system data mixed with user data, multitenant container database, and pluggabledatabaseOracle PL/SQL and DBA Certification Training12 P a g e
ORACLE DATABASE 12C ARCHITECTUREArchitecture of Oracle Server, the key components – Oracle Instance and OracleDatabase, the various file types – data files, control files, and redo log files, benefits ofCDB, easier management of databases, support for Oracle Database Resource Manager,pluggable database (PDB), database instance, contents of CDB and pluggable DB, controlfiles and redo log files, shared Oracle supplied metadata, root container, data dictionary,and the creation of PDBs – seed PDB limit PDBORACLE STORAGE STRUCTURESThe basics of Oracle storage structures, logical storage units like tablespaces, data blocks,extents and segments, database schemas, schema objects, operating system blocks, tablestatement and way to check ‘create table,’ the instance, the CDB, containers, rootcontainers, PDB, local user and common user, local privileges and common privileges,steps to create a container database, creating a container database using SQL Plus, anddatabase configuration assistanceMEMORY & PROCESS ARCHITECTUREOracle instance: background processes and memory structures, Oracle process andmemory architecture, shared pool, redo log buffer, and buffer cache, different methods ofcreating PDB, steps involved in PDB, using PDB SEED, without FILE NAME CONVERT,synchronization, plug a non-CDB to CDB, plug a non-CDB into CDB using DBMS PDB,clone PDBS, plug and un-plug, and plug unplugged CDB in to CDBALERT & TRACE FILESOracle background process errors monitored using trace files and alert logs and how thiscan be deployed for Oracle administration and supportDATABASE STARTUP & USER REQUESTSThe methodology of starting a database instance, initializing parameter files, preparing forstartup, serving user requests, understanding the server process and user process,administrative activities on PDB, new views associated with PDBS, determining which PDByou are in, four functions, opening, closing, and altering the open mode of PDBS, settingup default and temporary table places for a PDB, using the alter system command fromwithin a PDB, and the instance parameter change impactMANAGING DATA WITH ILMOracle PL/SQL and DBA Certification Training13 P a g e
What is automatic data optimization? ILM components, ILM challenges, heat map andADO, DBA heat map segment view, monitoring statistics, creating compression polices,creating storage tiering policy, and preparing for evolution and executionHands-on Exercise: Automatic data optimization, reduction policies, and altering policyDATABASE SECURITYLearning about the powerful Oracle Database security features, data privacy, regulatorycompliance, the ‘create user’ process, altering and dropping users, generating profiles andlimiting resources, auditing, activity monitoring, and blockingDATABASE SCHEMA OBJECTSUnderstanding database schema objects, various types available such as views, tables,clusters, indexes, sequences, database links, packages, and procedures, learning howdata is stored in database tables, and creating temporary tables and external tablesDATABASE RESOURCE MANAGERResource manager and pluggable database, managing resources between PDBs, CDBresource plan basics:share, CDB resource plan basics:Limits, creating a CDB resourceplan, creating a CDB resource plan:SQL example, enabling a CDB resource plan, andmanaging a PDB resource planDEEP DIVE INTO SCHEMA OBJECTSMastering database schema objects, learning about materialized view, deploying usergenerated schema object for generating a sequence, the balanced search tree indexstructure for placing and locating files, learning about data concurrency and dataconsistency in multi-user databases, and the concepts of locking and deadlocksORACLE NETWORK ENVIRONMENTStudy the client/server network environment, configuring the network, connecting to thedatabase, the Oracle Network Environment and database link, starting up an instance,SHOUT DOWN, NOMOUNT, MOUNT, OPEN, PDB, OPEN, and clone PDBSORACLE BACKUP & RECOVERYUnderstanding how Oracle Database ecosystem backup and recovery is deployed,database testing, database standby, media recovery options, backup in the offline mode,CDB backup and recovery, archiving challenges and solutions, in database archiving,Oracle PL/SQL and DBA Certification Training14 P a g e
security and performance, audit architecture, consolidation: unique audit trail, extendedaudit information, data pump audit policy, and new administrative privileges: newadministrative privileges:SYSBACKUP, new administrative privileges:SYSDG, and newadministrative privileges:SYSKMORACLE RECOVERY MANAGER (RMAN)Significance of Oracle Recovery Manager, how it deploys performance, management andrestoration of entire databases, SQL Loader, Oracle Data Pump for importing andexporting, and support for the external tableDATA DICTIONARY & DYNAMIC PERFORMANCE TABLESOracle Data Dictionary contains information related to database privileges, objects, users,and roles, dynamic performance table for identifying instance-level problems, gettingcomplete knowledge to work with Oracle DBA, connecting to access DBA, data dictionaryviews, after CDB creation, alert log file, provisioning new pluggabledatabases NOARCHIVELOG mode, media failure, flashback CDB, and data dictionaryviewsDATABASE TUNINGWhat is database tuning? Skills needed to lower response time, increase throughput fortime-sensitive applications, how to optimize the storage space, deploying SQL scripts,index statistics and selectivity, how to tune the database, and chained row and lock inOracleDATABASE TUNING (CONT.)In-depth understanding of Oracle Database tuning, shared pool tuning, how to improve theperformance with the help of the data dictionary, tuning the data dictionary, implementingthe PL/SQL code, and reusing the code and database bufferOracle PL/SQL and DBA Certification Training15 P a g e
Project WorkOracle PL/SQL Projects Performing DML Operations on a Database Streamlining Banking Data with PL/SQL Creating a Detailed Patient Report Working on a University DatasetOracle DBA Projects Tuning of the Database Building an Airline Ticketing Website HR Management Powered by Oracle DatabaseOracle PL/SQL and DBA Certification Training16 P a g e
CertificationAfter the completion of the course, you will get a certificate from Intellipaat.Oracle PL/SQL and DBA Certification Training17 P a g e
Success StoriesKevin K WadaThank you very much for your top-class service. A special mentionshould be made for your patience in listening to my queries and givingme a solution, which was exactly what I was looking for. I am giving youa 10 on 10!Sampson BasoahThe Intellipaat team helped me in selecting the perfect course that suitsmy profile. The whole course was practically oriented, and the trainerswere always ready to answer any question. I found this course to beimpactful. Thank you.Sugandha SinhaIntellipaat's course instructors were excellent and well-versed with theirconcepts. The support team solved all my queries within the promised24 hours. They explained all topics and concepts well, and the coursematerial was updated and included videos, exercises, etc. I would highlyrecommend Intellipaat to those who wish to excel in the IT field.John WilsonThis is probably one of the best courses I have ever done. I think the course isdoing an excellent job in giving a comprehensive understanding of each topic.Monika DumitracheI enjoyed every session conducted by the trainer. He was very active and keptthe class lively. His explanations and hold on the subject were outstanding.The support was excellent as well. Also, the training material was vast,making sure to cover all possible topics related to SQL and PL/SQL.Oracle PL/SQL and DBA Certification Training18 P a g e
CONTACT USINTELLIPAAT SOFTWARE SOLUTIONS PVT. LTD.BangaloreAMR Tech Park 3, Ground Floor, Tower B,Hongasandra Village, Bommanahalli,Hosur Road, Bangalore – 560068USA1219 E. Hillsdale Blvd. Suite 205,Foster City, CA 94404If you have any further queries or just want to have a conversation with us, then do call us.IND: 91-7022374614 US: 1-800-216-8930Oracle PL/SQL and DBA Certification Training19 P a g e
Oracle PL/SQL and DBA Certification Training 7 P a g e Program Curriculum Oracle PL/SQL Course Content INTRODUCTION TO ORACLE SQL What is RDBMS? Oracle versions, architecture of the Oracle Database server, and the installation of Oracle 12c Hands-on Exercise: Install Oracle 12c
