Oracle Database

-- Create tablespace
CREATE TABLESPACE users_data
DATAFILE '/u01/app/oracle/oradata/users01.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 50M
MAXSIZE 500M;

CREATE OR REPLACE PROCEDURE update_employee_salary (
    p_emp_id    IN NUMBER,
    p_amount    IN NUMBER
)
IS
BEGIN
    UPDATE employees
    SET salary = salary + p_amount
    WHERE employee_id = p_emp_id;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20001, 'Error updating salary');
END update_employee_salary;

CREATE OR REPLACE PACKAGE employee_mgmt AS
    -- Package specification
    PROCEDURE hire_employee(
        p_first_name IN VARCHAR2,
        p_last_name  IN VARCHAR2,
        p_email      IN VARCHAR2,
        p_job_id     IN VARCHAR2
    );
    
    FUNCTION get_salary(p_emp_id IN NUMBER) 
    RETURN NUMBER;
END employee_mgmt;

-- RMAN backup commands
BACKUP DATABASE PLUS ARCHIVELOG;

-- Recovery commands
RECOVER DATABASE UNTIL TIME '2023-12-31 23:59:59'
   AUXILIARY DESTINATION '/u01/app/oracle/recovery';

-- Create user
CREATE USER app_user
IDENTIFIED BY "StrongP@ssw0rd"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;

-- Grant roles and privileges
GRANT connect, resource TO app_user;
GRANT SELECT ON hr.employees TO app_user;

-- Create role
CREATE ROLE app_developer;
GRANT CREATE TABLE, CREATE VIEW TO app_developer;

-- Enable auditing
AUDIT SELECT TABLE, UPDATE TABLE
BY SESSION
WHENEVER SUCCESSFUL;

-- Analyze execution plan
EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(4) */ 
    e.last_name,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Check database status
SELECT instance_name, status, database_status
FROM v$instance;

-- Monitor session activity
SELECT username, program, status, event
FROM v$session
WHERE type != 'BACKGROUND';