COMP1638 – Managing Database Storage Structures part 2 скачать в хорошем качестве

COMP1638 – Managing Database Storage Structures part 2

5. Tablespace Internal Structure (Very Important for Exams) A tablespace contains: 5.1 Segment A segment is space allocated for one object One table = one segment One index = one segment 📌 Example: CREATE TABLE students (...); 👉 Oracle creates a segment for students 5.2 Extent A segment grows in extents An extent = group of contiguous blocks 📌 When table grows → Oracle allocates new extents 5.3 Data Block (Smallest Unit) Smallest unit Oracle reads/writes Typical size: 8KB 📌 One data block = specific number of bytes on disk Summary Diagram Tablespace └── Segment └── Extent └── Data Blocks 6. Types of Tablespaces 6.1 Permanent Tablespace Stores permanent data User tables Indexes System data 📌 Example: USERS, SYSTEM 6.2 Undo Tablespace Used to: Roll back transactions Provide read consistency Support recovery Enable Flashback features 📌 Only one active undo tablespace per database 🧠 Example: If a transaction fails → Oracle uses UNDO to reverse changes 6.3 Temporary Tablespace Used for: Sorting Joins ORDER BY GROUP BY 📌 Uses tempfiles, not data files 📌 Data disappears automatically after use 7. Tablespace Status A tablespace can be: Online (Read/Write) Default Users can read and write Read Only Users can read but cannot modify Good for historical data Offline No user access Used for maintenance or recovery 📌 Example: Before repairing a data file → set tablespace OFFLINE