Relational Database Management System-- vivek singh

# Overview of Oracle Database Fundamentals

## Introduction to Oracle, Triggers, and Wrapping

Oracle Database is a powerful and widely used relational database management system (RDBMS). It provides a robust platform for managing large volumes of data efficiently. Key features of Oracle include its ability to handle complex queries, support for multi-user environments, and a comprehensive suite of security measures.

**Triggers** are procedural code executed automatically in response to certain events on a particular table or view in a database. They are essential for maintaining data integrity and enforcing business rules. **Wrapping** in Oracle refers to the process of encoding PL/SQL source code to protect it from unauthorized viewing and tampering.

## Functions/Responsibilities of a DBA

A Database Administrator (DBA) is responsible for the overall management and maintenance of a database system. Key responsibilities include:

- **Installation and Configuration**: Setting up Oracle databases, including installing the software and configuring database instances.
- **Performance Monitoring**: Continuously monitoring database performance to ensure optimal operation and identifying potential issues.
- **Backup and Recovery**: Implementing and managing backup procedures to protect data against loss and ensuring the ability to recover data in case of hardware failures or other disasters.
- **Security Management**: Ensuring that the database is secure from unauthorized access by managing user permissions and roles.
- **Troubleshooting**: Diagnosing and resolving database issues, including performance bottlenecks, connectivity problems, and data corruption.

## Oracle Product Details

Oracle offers a range of products and editions to meet different needs, from small applications to large enterprise systems. Key products include:

- **Oracle Database Standard Edition**: Suitable for small to medium-sized businesses, offering essential database features at a lower cost.
- **Oracle Database Enterprise Edition**: Provides advanced features, including high availability, performance optimization, and security enhancements, ideal for large enterprises.
- **Oracle Cloud**: Oracle's cloud services, including Autonomous Database, which offers self-driving, self-securing, and self-repairing capabilities.

## Oracle Files, System, and User Processes

Oracle database architecture consists of several key components:

- **Control Files**: These files contain metadata about the database structure, including the database name, timestamps, and pointers to data files and redo log files.
- **Data Files**: Store the actual data within the database.
- **Redo Log Files**: Record all changes made to the data, enabling recovery in case of a failure.
- **System Processes**: Manage tasks such as memory allocation, I/O operations, and process scheduling.
- **User Processes**: Handle interactions between the user applications and the database.

1. Oracle, Trigger and wrapping

2. Table of Content Introduction to Oracle, Trigger and wrapping Functions/responsibilities of DBA Oracle product details Oracle files, System and User process Oracle Memory Protecting data: Oracle backup & recovery Triggers - types, uses, data access for triggers PL/SQL Packages and Wrapping Conclusion of the Unit

3. Trigger A stored program that is fired by default or by some events is called a trigger. A trigger is executed due to the following circumstances listed below: By a DDL (Data Definition Language) statement like DROP, ALTER, or CREATE. By a DML (Data Manipulation Language) statement like UPDATE, INSERT, or DELETE. By a database action like SHUTDOWN, STARTUP, LOGOFF, and LOGON. A trigger can be set on a schema, view, or database that has an event attached.

4. Uses of Triggers Maintaining complex constraints which is either impossible or very difficult via normal constraint(like primary, foreign, unique etc ) applying technique. Recording the changes made on the table. Automatically generating primary key values. Prevent invalid transactions to occur. Granting authorization and providing security to database. Enforcing referential integrity.

5. Parts of a Trigger Triggering Event or Statement:   The statements due to which a trigger occurs is called triggering event or statement. Such statements can be DDL statements, DML statements or any database operation, executing which gives rise to a trigger. Trigger Restriction:  The condition or any limitation applied on the trigger is called trigger restriction. Thus, if such a condition is  TRUE  then trigger occurs otherwise it does not occur. Trigger Action:  The body containing the executable statements that is to be executed when trigger occurs that is with the execution of Triggering statement and upon evaluation of Trigger restriction as  True  is called Trigger Action.

6. Types of Triggers Classification based on the timing BEFORE Trigger: It fires before the specified event has occurred. AFTER Trigger: It fires after the specified event has occurred. INSTEAD OF Trigger: A special type. used when a view is created.(only for DML ) Classification based on the level STATEMENT level Trigger: It fires one time for the specified event statement. ROW level Trigger: It fires for each record that got affected in the specified event. (only for DML)

7. Classification based on the Event DML Trigger: It fires when the DML event is specified (INSERT/UPDATE/DELETE) DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER) DATABASE Trigger: It fires when the database event is specified (LOGON/LOGOFF/STARTUP/SHUTDOWN)

8. Syntax

9. CREATE OR REPLACE TRIGGER is a keyword used to create a trigger and < trigger_name > is user-defined where a trigger can be given a name. BEFORE/AFTER/INSTEAD OF specify the timing of the trigger's occurance . INSTEAD OF is used when a view is created. I NSERT/UPDATE/DELETE specify the DML statement. < table_name > specify the name of the table on which DML statement is to be applied. R EFERENCING is a keyword used to provide reference to old and new values for DML statements. FOR EACH ROW is the clause used to specify row level tigger . WHEN is a clause used to specify condition to be applied and is only applicable for row-level trigger. DECLARE, BEGIN, EXCEPTION, END are the different sections of PL/SQL code block containing variable declaration, executable statements, error handling statements and marking end of PL/SQL block respectively where DECLARE and EXCEPTION part are optional.

10. :NEW and :OLD Clause Oracle has provided two clauses in the RECORD-level trigger to hold these values. We can use these clauses to refer to the old and new values inside the trigger body. :NEW – It holds a new value for the columns of the base table/view during the trigger execution :OLD – It holds old value of the columns of the base table/view during the trigger execution

11. Clause INSERT UPDATE DELETE :NEW VALID VALID INVALID. There is no new value in delete case. :OLD INVALID. There is no old value in insert case VALID VALID

12. Example of before level trigger Create or replace trigger tdelete Before delete on emp1 Begin Raise_application_error (-20002, 'Deletion is not allowed'); End; delete from emp1 where empno =7900;   ORA-20002: Deletion is not allowed ORA-06512: at "HR.TDELETE", line 2 ORA-04088: error during execution of trigger 'HR.TDELETE' 1. delete from emp1 where deptno =10

13. Example of before level trigger + For each row Create or replace trigger tdml Before insert or update or delete On emp1 For each row Begin If updating then Raise_application_error (-20006,'Updation is not allowed'); elsIf inserting then Raise_application_error (-20007,'Insertion is not allowed'); elsIf deleting then Raise_application_error (-20008,'Deletion is not allowed'); End if; End;  

14. Example of before level trigger Create or replace trigger tchange Before insert or update or delete on emp1 Begin Raise_application_error (-20008, 'DML is not allowed'); End; delete from emp1 where empno =7839;   ORA-20008: DML is not allowed ORA-06512: at "HR.TCHANGE", line 2 ORA-04088: error during execution of trigger 'HR.TCHANGE' 1. delete from emp1 2. where empno =7839;

15. Example of after level trigger Create a trigger which will store the data for new employee in income_tax table if salary >3000. Create or replace trigger tstore After insert on emp1 For each row Begin If : new.sal >3000 then Insert into emp1 ( ename,sal ) Values(:new. ename ,: new.sal ); End if; End; insert into emp1 ( ename,sal ) values('VIN',20000);

16. Example of multiple event trigger Create a trigger to insert ename and job always in lowercase.   Create or replace trigger tmultiple before insert or update on emp1 For each row Begin : new.ename :=lower(: new.ename ); : new.job :=lower(: new.job ); End; update emp1 set mgr =7900, job='CLERK’ where empno =7902;

17. C reate a trigger to avoid the user to logon to the database after 3 pm. Create or replace trigger tlogin After logon on database For each row Begin If to_char ( sysdate , 'HH24')>=15 then Raise_application_error (-20009, 'Login is not allowed'); End if; End To disable or enable any trigger Alter trigger<Name> disable; b. Alter trigger<Name> enable; Example -> alter trigger T8 disable; To delete any trigger a. Drop trigger< trigger_name >; Example -> Drop trigger tdelete ;  

18. Functions/responsibilities of DBA Already Covered in Unit -1 Slide Number -> 36,37,38

19. Oracle product details Oracle Product Detail

20. Oracle File System The Oracle Database has been commonly used to store files closely associated with database applications including CAD, medical images, invoice images, documents, etc. Database File System (DBFS) leverages the features of the database to store files, and the strengths of the database in efficiently managing relational data, to implement a standard file system interface for files stored in the database. Data files: These files hold the actual data in the database. Redo log files: These files are used to hold the changes made in the database. Redo log files can be utilized during the database recovery process to retrieve the original information. Control files: It is a binary file that holds database status-related information like Database Name, Data File, and Redo Log file Names, File Locations, and Log Sequence Number.

21. Oracle User Process A database user who needs to request information from the database must first make connection with the Oracle Server. the connection is requested using a database interface tool, such as SQL*Plus, and beginning the user process. The user process does not interact directly with the Oracle Server. Rather it generates calls through the user program interface (UPI), which creates a session and start a server process.

22. Oracle Memory Memory management involves maintaining optimal sizes for the Oracle Database instance memory structures as demands on the database change. The memory structures that must be managed are the system global area (SGA) and the instance program global area (instance PGA). 1. Automatic Memory Management : oracle Database can manage the SGA memory and instance PGA memory completely automatically. Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as  automatic memory management . With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.

23. 2. Manual Memory Management : If you prefer to exercise more direct control over the sizes of individual memory components, you can disable automatic memory management and configure the database for manual memory management. There are a few different methods available for manual memory management. Some of these methods retain some degree of automation. The methods therefore vary in the amount of effort and knowledge required by the DBA. These methods are: Automatic shared memory management - for the SGA Manual shared memory management - for the SGA Automatic PGA memory management - for the instance PGA Manual PGA memory management - for the instance PGA

24. Oracle backup & recovery The purpose of a backup and recovery strategy is to protect the database against data loss and reconstruct the database after data loss. Typically, backup administration tasks include the following: Planning and testing responses to different kinds of failures Configuring the database environment for backup and recovery Setting up a backup schedule Monitoring the backup and recovery environment Troubleshooting backup problems Recovering from data loss if the need arises

25. Reasons of Backup Data Protection : Physical backups are copies of the physical files used in storing and recovering a database. These files include data files, control files, and archived redo logs. Ultimately, every physical backup is a copy of files that store database information to another location, whether on disk or on offline storage media such as tape. Logical backups contain logical data such as tables and stored procedures. You can use Oracle Data Pump to export logical data to binary files, which you can later import into the database. The Data Pump command-line clients expdp and impdp use the DBMS_DATAPUMP and DBMS_METADATA PL/SQL packages.

26. Media Failures A media failure is a physical problem with a disk that causes a failure of a read from or write to a disk file that is required to run the database. Any database file can be vulnerable to a media failure. The appropriate recovery technique following a media failure depends on the files affected and the types of backup available. User Errors User errors occur when, either due to an error in application logic or a manual mistake, data in a database is changed or deleted incorrectly. User errors are estimated to be the greatest single cause of database downtime.

27. Application Errors Sometimes a software malfunction can corrupt data blocks. In a physical corruption, which is also called a media corruption, the database does not recognize the block at all: the checksum is invalid, the block contains all zeros, or the header and footer of the block do not match. If the corruption is not extensive, then you can often repair it easily with block media recovery.

28. PL/SQL Packages PL/SQL package is a group of related functions, procedures, types, cursors, etc. PL/SQL package is like a library once written stored in the Oracle database and can be used by many applications. A PL/SQL package has two parts: package specification and package body. A package specification is the public interface of your applications. The public means the stored function, procedures, types, etc., are accessible from other applications. A package body contains the code that implements the package specification.

29. Components of Packages Package Specification Package Body Package Specification Package specification consists of a declaration of all the public variables, cursors, objects, procedures, functions, and exception. Below are few characteristics of the Package specification. The elements which are all declared in the specification can be accessed from outside of the package. Such elements are known as a public element. The package specification is a standalone element that means it can exist alone without package body. Whenever a package has referred an instance of the package is created for that particular session. After the instance is created for a session, all the package elements that are initiated in that instance are valid until the end of the session.

30. Syntax of Package Specification Creating a package specification Create [or replace] Package <Name> Is < Procedre Definition>; < Procedre Definition>; <Function Definition>; <Variable> <Exception>; END [Package Name];

31. Package Body It consists of the definition of all the elements that are present in the package specification. It can also have a definition of elements that are not declared in the specification, these elements are called private elements and can be called only from inside the package. It should contain definitions for all the subprograms/cursors that have been declared in the specification. It can also have more subprograms or other elements that are not declared in specification. These are called private elements. It is a dependable object, and it depends on package specification.

32. Package Body Create [or replace] Package body <Name> Is/AS < Procedre Definition> Begin <statements> End; < Procedre Definition> Begin <statements> End; <Function Definition> Begin <statements> End;   <Variable> <Exception>; END [Package Name];  

33. Example Package Specification CREATE OR REPLACE PACKAGE math_operations AS -- Procedure to add two numbers with an output parameter PROCEDURE add_numbers (x NUMBER, y NUMBER, result OUT NUMBER); -- Function to multiply two numbers FUNCTION multiply_numbers (x NUMBER, y NUMBER) RETURN NUMBER; END math_operations ;

34. Package Body CREATE OR REPLACE PACKAGE BODY math_operations AS -- Implementation of the add_numbers procedure PROCEDURE add_numbers (x NUMBER, y NUMBER, result OUT NUMBER) IS BEGIN result := x + y; END add_numbers ; -- Implementation of the multiply_numbers function FUNCTION multiply_numbers (x NUMBER, y NUMBER) RETURN NUMBER IS BEGIN RETURN x * y; END multiply_numbers ; END math_operations ;

35. PL/SQL block to test the math_operations package DECLARE -- Declare variables to store results sum_result NUMBER; product_result NUMBER; BEGIN -- Call the procedure and pass output parameter math_operations.add_numbers (5, 7, sum_result ); -- Display the result of the add_numbers procedure DBMS_OUTPUT.PUT_LINE('Sum Result: ' || sum_result ); -- Call the function and retrieve the result product_result := math_operations.multiply_numbers (3, 4); -- Display the result of the multiply_numbers function DBMS_OUTPUT.PUT_LINE('Product Result: ' || product_result ); END;

36. Example: Create a PL/SQL package specification for employee_operations CREATE OR REPLACE PACKAGE employee_operations AS -- Procedure to calculate annual salary PROCEDURE calculate_annual_salary ( monthly_salary NUMBER, annual_salary OUT NUMBER); -- Function to get the name and job of an employee FUNCTION get_full_name ( first_name VARCHAR2, job VARCHAR2) RETURN VARCHAR2; END employee_operations ;

37. Create the body of the employee_operations package CREATE OR REPLACE PACKAGE BODY employee_operations AS PROCEDURE calculate_annual_salary ( monthly_salary NUMBER, annual_salary OUT NUMBER) IS BEGIN annual_salary := monthly_salary * 12; END calculate_annual_salary ; FUNCTION get_full_name ( first_name VARCHAR2, job VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN first_name || ' ' || job; END get_full_name ; END employee_operations ;

38. DECLARE annual_salary_result NUMBER; full_name_result VARCHAR2(100); BEGIN employee_operations.calculate_annual_salary (5000, annual_salary_result ); DBMS_OUTPUT.PUT_LINE('Annual Salary: ' || annual_salary_result ); full_name_result := employee_operations.get_full_name ('Chetan', ‘Manager'); DBMS_OUTPUT.PUT_LINE(‘Name & Job: ' || full_name_result ); END;

39. Thankyou