ORACLE ADMIN WORKSHOP.pptx

ORACLE ADMIN WORKSHO P AKINOL A SEUN What is a Database? Any collection of related information e.g Phonebook, shopping list, Todo list A database is like a big electronic notebook where you can store and organize a lot of information. - AKINOL A SEUN - AKINOL A SEUN - AKINOL A SEUN - AKINOL A S...

ORACLE ADMIN WORKSHO P AKINOL A SEUN What is a Database? Any collection of related information e.g Phonebook, shopping list, Todo list A database is like a big electronic notebook where you can store and organize a lot of information. - AKINOL A SEUN - AKINOL A SEUN - AKINOL A SEUN - AKINOL A SEUN - AKINOL A SEUN - AKINOL A SEUN - AKINOL A SEUN - AKINOL A SEUN - AKINOL A SEUN What is Oracle Database? Oracle Database is a special kind of database made by a company called Oracle. - AKINOL A SEUN Oracle Database Architecture: 1. MEMORY 2. PROCESSES 3. STORAGE - AKINOL A SEUN  1. Memory (The City’s Power) System Global Area (SGA): This is like Oracle’s short-term memory. It holds information that Oracle needs quickly while it’s working. It includes: - AKINOL A SEUN  - Buffer Cache: Temporarily holds data blocks that are being read or written. - Shared Pool: Holds SQL statements and execution plans that can be reused to save time. - Redo Log Buffer: Holds a temporary record of all changes made to the database, waiting to be written to the disk. - Just like human short-term memory, the SGA is fast and holds data that Oracle needs immediately to process queries and perform operations. - AKINOL A SEUN  - Long-Term Storage (Hard Disk vs. Long-Term Memory) Oracle’s equivalent to long-term memory would be the physical files stored on the disk. Just like in humans, long- term storage in Oracle is: Slower to access: Fetching data from the disk is slower than reading from memory, just like recalling a memory from long ago can take more time. Larger in capacity: The amount of data that can be stored on the disk (data files, redo log files, etc.) is far greater than what can be held in memory (SGA or PGA). - AKINOL A SEUN  Program Global Area (PGA): The PGA is specific to each user or session and functions like working memory in humans. It is used for things like sorting operations or temporary calculations that are specific to a task the user is performing. - AKINOL A SEUN 2. Processes The processes that handle different tasks like writing data to disk, managing user sessions, handling database recovery, and more. - AKINOL A SEUN - AKINOL A SEUN - AKINOL A SEUN - AKINOL A SEUN 1. DB Writer Process (DBWR): Think of this as the librarian who writes down new information into the books (or database files). 2. Log Writer Process (LGWR): This is the worker who writes down every change that happens in the city, like a diary. 3. Checkpoint Process (CKPT): This worker makes sure that the librarian has saved all the latest information and nothing is lost. - AKINOL A SEUN 1. Tablespaces and Data Files Tablespaces and data files are critical components of Oracle’s physical and logical storage structures. Here's a detailed explanation of how data is logically organized in tablespaces and physically stored in data files: - AKINOL A SEUN Tablespaces Logical Storage Unit: A tablespace is a logical storage structure in an Oracle database that groups together related objects like tables, indexes, and views. It acts as a container to manage where the data is stored physically. - AKINOL A SEUN Segmentation: Within a tablespace, the database objects are divided into segments (like table segments, index segments, etc.), and segments are divided into extents. Extents, in turn, are made up of blocks, which is the smallest unit of storage. - AKINOL A SEUN Types of Tablespaces: System Tablespace: Stores the data dictionary, metadata about the database, and critical information necessary for database functioning. User Tablespaces: Stores user-created objects like tables and indexes. Temporary Tablespaces: Used for temporary storage like sorting operations during SQL execution. Undo Tablespaces: Manages undo records, which are used to roll back transactions and support multi- version read consistency. - AKINOL A SEUN Data Files - Physical Storage Unit: Data files are the physical files that store the data on disk. Each tablespace consists of one or more data files, and these files store the actual data for database objects (tables, indexes, etc.). - Location: Each data file is stored on the operating system of the server, and Oracle automatically manages the data across these files. - Growth: Data files can be set to grow automatically as data is inserted into the database, and their size can be adjusted manually or configured for automatic expansion (auto-extend). - AKINOL A SEUN If you create a tablespace for storing user data, Oracle internally creates a data file (e.g., users01.dbf) where this data will be physically stored. When new data is inserted into a table in this tablespace, it will be stored in this data file. - AKINOL A SEUN 2. Control Files and Redo Logs Control Files and Redo Logs are essential for the integrity, recovery, and functioning of an Oracle database. - AKINOL A SEUN Control Files Critical Metadata: The control file contains critical metadata about the Oracle database. It holds information about the database structure, including the database name, timestamp, data files, redo log files, and backup information. Multiple Copies: Oracle typically maintains multiple copies of the control file on different disks for redundancy. If one control file is corrupted or lost, the others can be used to keep the database running. - AKINOL A SEUN Usage: Control files are used during the startup of the database to verify its consistency and integrity. It keeps track of changes to the physical structure of the database (such as adding new data files or tablespaces). - AKINOL A SEUN Contents of Control File: - Names and locations of data files, redo log files, and tablespaces. - Database name and timestamp when the database was created. - Information about backups and archive logs for recovery purposes. - Checkpoint information: Used to synchronize data files with the database changes to ensure recovery is possible after an incident. - AKINOL A SEUN Redo Logs Transaction Logs: Redo log files keep a record of all changes made to the data. Every time a transaction modifies the database, the changes are written to the redo log files before they are applied to the actual data files. - AKINOL A SEUN - Crash Recovery: In the event of a system crash, redo log files are used to recover any committed transactions that haven’t yet been written to the data files (also known as roll- forward operations). - Cyclic Use: Oracle uses a set of redo log files cyclically. When one log file is full, Oracle switches to the next redo log file in the group. Once all files are full, it goes back to the first one and overwrites the old contents, unless they are archived. - AKINOL A SEUN Types of Redo Logs: 1. Online Redo Logs: These logs are always available for writing as transactions occur. 2. Archived Redo Logs: Once the online redo logs are full, if the database is in ARCHIVELOG mode, the logs are archived (saved) for use in recovery. - AKINOL A SEUN Relationship Between Control Files and Redo Logs: - The control file keeps track of the redo log files and ensures they are properly used in case recovery is needed. - Redo logs provide the transaction history needed to ensure that all committed transactions can be recovered in case of a failure. The control file ensures the database knows the current state and location of these logs. - AKINOL A SEUN How It All Works Together When a user process wants to do something, like add a new piece of information or find an existing one, they send a request to the database processes. The user process then use the memory to quickly find or store the information in the right place (storage). - AKINOL A SEUN - AKINOL A SEUN  Installation and Configuration - AKINOL A SEUN  MODULE 2 - AKINOL A SEUN 2. User and Security Management: Managing users and security is like setting up rules and permissions for who can enter different parts of a building and what they can do there. - AKINOL A SEUN Creating and Managing Users: - User Accounts: Each person (or application) that interacts with the database needs an account. You create these accounts and assign them a username and password. - Profiles: You can create profiles that define the rules for users, such as how long their password lasts or how much time they can spend on the system. - AKINOL A SEUN Roles and Privileges: - Roles: These are like job titles (e.g., "Manager," "Employee"). Each role has certain permissions. Instead of giving permissions to each user one by one, you assign them a role. - System Privileges: These allow users to do certain things at the database level, like creating a new table. - Object Privileges: These allow users to do things with specific objects in the database, like reading or writing data in a specific table. - AKINOL A SEUN Security Policies: - Auditing: Set up auditing to track what users are doing. For example, you might want to log who accessed certain data or who tried to change it. - Encryption: Protect sensitive data by encrypting it so that even if someone unauthorized gets access, they can’t read it. - AKINOL A SEUN Creating a User Account CREATE USER username IDENTIFIED BY password; CREATE USER samuel_doe IDENTIFIED BY securePassword123; GRANT CREATE SESSION TO Samuel_doe; - AKINOL A SEUN 2. Granting Privileges to a User Privileges in Oracle are permissions that allow a user to perform specific actions in the database. There are two main types of privileges: - AKINOL A SEUN - System Privileges: These allow users to perform specific actions at the database level, like creating tables, sessions, or other database objects. - - Object Privileges: These allow users to perform actions on specific database objects like tables, views, procedures, etc. - AKINOL A SEUN Granting System Privileges - To allow a user to perform certain actions like connecting to the database, creating tables, etc., you grant them system privileges using the GRANT command. - GRANT privilege_name TO username; - AKINOL A SEUN Granting System Privileges - GRANT CREATE TABLE TO IT_GROUP; - GRANT SELECT ANY TABLE TO IT_GROUP; - GRANT INSERT ANY TABLE TO IT_GROUP; - GRANT UPDATE ANY TABLE TO IT_GROUP; - GRANT DELETE ANY TABLE TO IT_GROUP; - IT CLASS STOPPED HERE. - AKINOL A SEUN - Grant roles to samuel_doe: To give the user basic privileges to create tables and manage data: - GRANT RESOURCE, CONNECT TO samuel_doe; To give the user full DBA privileges (be careful with this): - GRANT DBA TO samuel_doe; - AKINOL A SEUN - View existing roles and system privileges: You can run these queries to see all available roles and privileges in your Oracle database: View all system privileges: SELECT * FROM DBA_SYS_PRIVS; - View all available roles: SELECT * FROM DBA_ROLES; This way, you can decide which privileges or roles to grant to the user. - AKINOL A SEUN TABLES INSIDE NEWLY CREATED USER If you see many tables under the user samuel_doe, there are a few possible reasons why these tables might exist, even though you did not explicitly create them. Let's explore some common causes: 1. Default Oracle System Tables i. USER_TABLES: Lists tables owned by the user.ALL_TABLES: Lists all ii. tables accessible to the user (even if owned by other - AKINOL A SEUN iii. users).DBA_TABLES: Lists all tables in the entire database (requires DBA privileges). 2. Inherited Access from Common Schemas In Oracle databases, there are several common schemas that store various system- related objects, such as: SYS: The central schema that owns the data dictionary. SYSTEM: Another default administrative account, typically used to manage the database. PUBLIC: A schema that contains objects which are accessible to all users, such as public synonyms and public database links. - AKINOL A SEUN CHECKING USER TABLE To check the tables owned by your current user (in this case, samuel_doe), you can run the following SQL query: SELECT employees FROM user_tables; - AKINOL A SEUN CREATING A TABLE CREATE TABLE employees ( emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(100), hire_date DATE ); If you're using a pluggable database (PDB), make sure you are connected to the right PDB as well. If you're using SQL*Plus on Windows, you can run the following command to clear the screen:HOST CLS; - AKINOL sqlplus A SEUN // CONNECT FROM THE sys/as sysdba COMMAND PROMPT INSERT DATA INTO A TABLE INSERT INTO employees (emp_id, emp_name, hire_date) VALUES (1, 'John Doe', TO_DATE('2023-09-01', 'YYYY-MM-DD')); - AKINOL A SEUN QUERY THE DATA IN THE TABLE SELECT * FROM employees; - AKINOL A SEUN UPDATE THE DATA IN THE TABLE UPDATE employees SET emp_name = 'Samuel Doe' WHERE emp_id = 1; - AKINOL A SEUN DELETE THE DATA IN THE TABLE DELETE FROM employees WHERE emp_id = 1; - AKINOL A SEUN WELL FORMMATED OUTPUT -- Format the columns to display in a straight line COLUMN emp_id FORMAT 99999 HEADING 'Emp ID' COLUMN emp_name FORMAT A30 HEADING 'Employee Name' COLUMN hire_date FORMAT A12 HEADING 'Hire Date' -- Select all employees from the employees table SELECT emp_id, emp_name, TO_CHAR(hire_date, 'YYYY-MM-DD') AS hire_date FROM employees; - AKINOL A SEUN WELL FORMMATED OUTPUT Explanation:COLUMN emp_id FORMAT 99999: This formats the emp_id as a 5-digit number.COLUMN emp_name FORMAT A30: This formats the emp_name as a 30-character string, making sure all names are aligned.COLUMN hire_date FORMAT A12: This formats the hire_date as a string of 12 characters, where the date is displayed in YYYY-MM-DD format. - AKINOL A SEUN - Granting Object Privileges - If you want to grant permissions to specific objects, like tables or views, you can use the GRANT command with object privileges. - THE SYNTAX IS: - GRANT object_privilege ON object_name TO username; - e.g GRANT SELECT, INSERT ON employees TO samuel_doe; - This grants samuel_doe the ability to SELECT and INSERT data into the employees table. - AKINOL A SEUN 3. Assigning Roles A role is a collection of privileges that can be assigned to a user. Instead of granting privileges one by one to users, you can group them into a role and grant the role to a user. This makes privilege management easier. - AKINOL A SEUN Schema Management If you grant samuel_doe more privileges, you can allow them to manage their own schema, including: Creating and dropping tables, views, and indexes. Managing their own data with SELECT, INSERT, UPDATE, and DELETE. To grant all these privileges: GRANT RESOURCE, CONNECT TO samuel_doe; - AKINOLA SEUN Other Administrative If you need samuel_doe to have administrative roles (like Tasks DBA-level access), you can assign them DBA roles, though this should be done with care: GRANT DBA TO samuel_doe; This would give the user full control over the database, including starting and stopping it, creating users, managing storage, etc. - AKINOLA SEUN Summary of What Run SQL queries to explore data (if granted SELECT privilege). You Can Do: Create and manage database objects (tables, views, indexes, etc.) with appropriate privileges. Insert, update, delete data in tables. Check your roles and privileges.Grant additional privileges (if logged in as an admin). Grant additional privileges (if logged in as an admin). if you need more control over the user samuel_doe, you can always add or remove privileges by logging in as a DBA user. - AKINOLA SEUN  EN D - AKINOLA SEUN