1 / 4

Data Files Data Dictionary META (Users, Schema, Storage) Data Structures Access Structures

Hardware Architecture: Physical Structure. Parameter File. Data Files Data Dictionary META (Users, Schema, Storage) Data Structures Access Structures Procedural Objects. Redo Log Files (min 2) i.e. Exception Handling Journal File (Recovery/UNDO)

chika
Download Presentation

Data Files Data Dictionary META (Users, Schema, Storage) Data Structures Access Structures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Hardware Architecture: Physical Structure Parameter File • Data Files • Data Dictionary META • (Users, Schema, Storage) • Data Structures • Access Structures • Procedural Objects • Redo Log Files (min 2) • i.e. Exception Handling • Journal File (Recovery/UNDO) • DB changes (DB, Table, Users) • (SYS will archive Redo) • Archives used for reviews for • Tuning & Capacity planning. • Control Files (min 2) • When DB created • (Name & identifier) • File names (data/redo) • Last DB checkpoint – • Ref Timestamps + Redos • Changes to DB • Backup Info • Log History • Tablespace Names • DB Instance Defaults • DB Resources • Control Files/Archived Log Password File Archived Log File • Hardware Architecture: Logical Structure contains: • Redo Log Files & Control Files. • Number of tablespaces (1-255 OS files each plus storage space to impose structure) • Objects allocated to 1 tablespace only. • System Tablespace • Created when DB installed • via CREATE DATABASE • Inc. Data Dictionary • Inc. System Rollback Segment • Tablespace A • Based on size • Tablespace Types: • TEMP for Sorting (No PERM OBJS) • READ-ONLY inc OBJS can be dropped + • Tablespace B • Based on size • Space Administration • Space Allocation to user • Commands: • DROP TABLESPACE TA i.e. only if empty – unless INCLUDING CONTENTS (privilege reqd.) • RE-SIZE, READ-ONLY, OFFLINE • OFFLINE via: • ALTER TABLESPACE TA (Default NORMAL, IMMEDIATE, TEMPORARY) • NORMAL - “checkpoint” executed (all T/space files) – all data in DB flushed to Disk. • IMMEDIATE – no “checkpoint” & offline. Recovery required when back online. • TEMPORARY – “checkpoint” for online T/space files – offline as in IMMEDIATE. • Advantages of Tablespaces: • If hardware error – only 1 tablespace affected. • Efficiency improvement. • TABLESPACE >> Segments >> Extents >> Oracle Data Blocks. CREATE DATABASE EXAMPLE: CONTROLFILE REUSELOGFILE ‘REDO1.LOG’ SIZE 30MB, ‘REDO1.LOG’ SIZE 30MB, DATAFILE ‘FILE1.ORA’ 250MB; CREATE TABLESPACE TA DATAFILE ‘FILE1.ORA’ 250MB, DATAFILE ‘FILE2.ORA’ 250MB; CREATE TABLESPACE TB DATAFILE ‘FILE3.ORA’ 250MB, DATAFILE ‘FILE4.ORA’ 250MB;

  2. Database Storage Hierarchy Database Tablespace Data file Logical Physical Segment Extent Oracle block OS block

  3. TABLESPACE Structure CREATE USER DGEO IDENTIFIED BY PASSWORD XC DEFAULT TABLESPACE TA TEMPORARY TABLESPACE TA QUOTA UNLIMITED ON TA QUOTA 10MB ON TB; • 4 Objects:- • Data Segments • Index Segments • Rollback Segments • (Transaction Rollback & Recovery) • Temporary Segments (pre-COMMIT) • Nested Tables • Segment creation location depends on • Allocation of Tablspaces to user. • SEGMENT Structure • To minimise fragmentation. • Allocation of storage space. • “NEXT” segments allocated by System when available space insufficient. • PCTINCREASE = growth factor for each subsequent “NEXT” over previous. • EXTENTS allocated when SEG created/extended/altered. • De-allocated when SEG dropped/altered. Segment Creation: STORAGE ( INITIAL 20MB, NEXT 10MB, MINEXTENTS 3, MAXEXTENTS 3, PCTINCREASE 15, FREELISTS 3) DATA BLOCK Structure = Min units of I/O • Segment Data Blocks defined via: • PCTFREE i.e. kept free, when records inserted, for future trans. • PCTUSED i.e. % of data block space to be used before new D/Block. • INITRANS/MAXTRANS i.e. maximum transactions per Data Block • NB: • Insert Area = new records. Free Area = growth for existing records. Key issue for VARCHAR & VARCHAR2 i.e. have variable lengths e.g “Smith” >> “Arrowsmith” on name change. Thus if data block too small – placed in “Free”. • Where “Free” too small – whole record >> new D/Block and chain-linked to avoid index updating. • PCTFREE should be 50-60% if file volatile. • PCTUSED important to optimise space created by PCTFREE or deletions. • If no free D/Blocks system creates “NEXT” Extent. Data Block Header – includes Row & Trans Directory PCTFREE “Free Area” Data Block “Insert Area” Minimum Block Space PCTUSED

  4. Col length Row header Record Header No of columns Column data Row Body i.e. containing columns – each column has its own header stating its column length Cluster index Col length Chain link  etc RECORD Format Record has 2 components: Row Header & Row Body. • Calculating Table Rows that can be stored in a Data Block: • Block Space minus block header and PCTFREE • Block Space remaining / Table Row size = No of Table Rows stored.

More Related