1 / 40

Chapter 6 Basic Storage Concepts and Settings

Learn about logical and physical storage structures, tablespace and datafile configurations, implementation of storage structures, and more.

jcharles
Download Presentation

Chapter 6 Basic Storage Concepts and Settings

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. Chapter 6 Basic Storage Concepts and Settings Oracle9i Database Administrator: Implementation and Administration

  2. Objectives • Differentiate between logical and physical structures • Create many types of tablespaces • Configure and view storage for tablespaces and datafiles • Use undo data Oracle9i Database Administrator: Implementation and Administration

  3. Introduction to Storage Structures Internal structures store: • The structure of every table, view, and other objects you create • The data you load into your tables and other objects • Information about the structure of tables, views, etc. (metadata) Oracle9i Database Administrator: Implementation and Administration

  4. Introduction to Storage Structures Physical structures: • Datafiles • Operating system blocks • Redo log files • Control files Oracle9i Database Administrator: Implementation and Administration

  5. Introduction to Storage Structures Logical Structures: • Data block • Extent • Segment • Schema object • Tablespace Oracle9i Database Administrator: Implementation and Administration

  6. Introduction to Storage Structures Logical data blocks map directly to contiguous operating system blocks in datafiles Oracle9i Database Administrator: Implementation and Administration

  7. Tablespaces and Datafiles CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENT EXTENT MANAGEMENT LOCAL|DICTIONARY LOGGING|NOLOGGING ONLINE|OFFLINE SEGMENT SPACE MANAGEMENT MANUAL|AUTO • Add multiple files, separated by commas if needed • Clause is required for user managed files • Clause is optional when using OMF • Can be used to specify SIZE of OMF file Note: additional sub-clauses shown later Oracle9i Database Administrator: Implementation and Administration

  8. Tablespaces and Datafiles CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENT EXTENT MANAGEMENT LOCAL|DICTIONARY LOGGING|NOLOGGING ONLINE|OFFLINE SEGMENT SPACE MANAGEMENT MANUAL|AUTO • TEMPORARY: This option used only when creating temporary tablespaces that are dictionary-managed. • PERMANENT: Default option; stores permanent objects such as tables and indexes Oracle9i Database Administrator: Implementation and Administration

  9. Tablespaces and Datafiles CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENT EXTENT MANAGEMENT LOCAL|DICTIONARY LOGGING|NOLOGGING ONLINE|OFFLINE SEGMENT SPACE MANAGEMENT MANUAL|AUTO • LOCAL: Default; tablespace manages extent free space in a bitmap inside the tablespace • DICTIONARY: tablespace manages extent free space in the data dictionary Note: additional sub-clauses shown later Oracle9i Database Administrator: Implementation and Administration

  10. Tablespaces and Datafiles CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENT EXTENT MANAGEMENT LOCAL|DICTIONARY LOGGING|NOLOGGING ONLINE|OFFLINE SEGMENT SPACE MANAGEMENT MANUAL|AUTO • LOGGING: Default; all DML, DDL, and mass INSERT commands recorded in redo log • NOLOGGING: only DML commands recorded in redo log Oracle9i Database Administrator: Implementation and Administration

  11. Tablespaces and Datafiles CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENT EXTENT MANAGEMENT LOCAL|DICTIONARY LOGGING|NOLOGGING ONLINE|OFFLINE SEGMENT SPACE MANAGEMENT MANUAL|AUTO • ONLINE: Default; available for use • OFFLINE: not available for use Oracle9i Database Administrator: Implementation and Administration

  12. Tablespaces and Datafiles CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENT EXTENT MANAGEMENT LOCAL|DICTIONARY LOGGING|NOLOGGING ONLINE|OFFLINE SEGMENT SPACE MANAGEMENT MANUAL|AUTO AUTO: Manages segment free space in a bitmap in the tablespace MANUAL: Manages segment free space in the data dictionary Oracle9i Database Administrator: Implementation and Administration

  13. Implementing OMF With Tablespaces DB_CREATE_FILE_DEST must be set: • In init<sid>.ora • Or, during session CREATE TABLESPACE command: • Omit DATAFILE clause in • Or, include DATAFILE clause but only include SIZE clause (omit file name) Oracle9i Database Administrator: Implementation and Administration

  14. The DATAFILE Clause DATAFILE '<datafilename>' SIZE <nn>|REUSE AUTOEXTEND ON|OFF MAXSIZE <nn>|UNLIMITED • AUTOEXTEND ON: Allow file to expand when Oracle determines more space is needed • AUTOEXTEND OFF: Do not expand file • SIZE <nn>: Specify size in bytes, kilobytes, or megabytes • REUSE: File already exists Oracle9i Database Administrator: Implementation and Administration

  15. The DATAFILE Clause DATAFILE '<datafilename>' SIZE <nn>|REUSE AUTOEXTEND ON|OFF MAXSIZE <nn>|UNLIMITED Used with AUTOEXTEND ON: • MAXSIZE <nn>: Specify maximum file size in bytes, kilobytes, or megabytes • UNLIMITED: File can grow to limits of operating system Oracle9i Database Administrator: Implementation and Administration

  16. The EXTENT MANAGEMENTand SEGMENT SPACE MANAGEMENT Clauses Syntax for: EXTENT MANAGEMENT LOCAL: EXTENT MANAGEMENT LOCAL|DICTIONARY AUTOALLOCATE|UNIFORM SIZE <nn> SEGMENT SPACE MANAGEMENT MANUAL|AUTO MINIMUM EXTENT <nn> DEFAULT STORAGE (INITIAL <nn> NEXT <nn> PCTINCREASE <nn> MINEXTENTS <nn> MAXEXTENTS <nn>) • AUTO: Manages segment free space in a bitmap in the tablespace • MANUAL: Manages segment free space in the data dictionary • AUTOALLOCATE: Allow the system to manage extent size for all tables and other objects created in the tablespace • UNIFORM SIZE <nn>: Require the specified extent size for all tables and other objects created in the tablespace Oracle9i Database Administrator: Implementation and Administration

  17. The EXTENT MANAGEMENTand SEGMENT SPACE MANAGEMENT Clauses Syntax for dictionary-managed tablespace: EXTENT MANAGEMENT LOCAL|DICTIONARY SEGMENT SPACE MANAGEMENT MANUAL|AUTO AUTOALLOCATE|UNIFORM SIZE <nn> MINIMUM EXTENT <nn> DEFAULT STORAGE (INITIAL <nn> NEXT <nn> PCTINCREASE <nn> MINEXTENTS <nn> MAXEXTENTS <nn>) Minimum extent size allowed for any object created in the tablespace Default storage settings for any object created in the tablespace without a STORAGE clause of its own Oracle9i Database Administrator: Implementation and Administration

  18. The EXTENT MANAGEMENT Clause Example of dictionary-managed datafile Example: A new table needing 6 M of space skips over the deallocated extents Coalesce free space periodically to combine deallocated extents Oracle9i Database Administrator: Implementation and Administration

  19. Creating a Dictionary-Managed Tablespace • Example: CREATE TABLESPACE USER_TEST DATAFILE 'D:\oracle\data\user_test01.dbf' SIZE 250M AUTOEXTEND ON EXTENT MANAGEMENT DICTIONARY MINIMUM EXTENT 15M DEFAULT STORAGE (INITIAL 90M NEXT 15M PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 50); Oracle9i Database Administrator: Implementation and Administration

  20. Creating a Locally Managed Tablespace All new tables must use extents of the same size or multiples of the size Example of locally managed datafile Example: A new table needing a 10M extent would use the free space from two 5 M deallocated extents Deallocated extents are automatically coalesced Oracle9i Database Administrator: Implementation and Administration

  21. Creating a Locally Managed Tablespace • Example: CREATE TABLESPACE USER_AUTO DATAFILE '<C:oracle\oradata\user_auto01.dbf' SIZE 20M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; Oracle9i Database Administrator: Implementation and Administration

  22. Segment Types and Their Uses • Data segment: for tables, object tables, triggers • Index segment: for indexes • Temporary segment: for sorting operations and temporary tables • Rollback segment: for undo data • LOB segment: for LOB data stored separately from the rest of the table’s data Oracle9i Database Administrator: Implementation and Administration

  23. Temporary Tablespaces • Oracle recommends creating locally managed temporary tablespaces • Used for temporary segments • Command syntax: CREATE TEMPORARY TABLESPACE <tablespacename> TEMPFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE <nn> Oracle9i Database Administrator: Implementation and Administration

  24. Tablespaces With Nonstandard Data Block Size • Can be 2 K, 4 K, 8 K, 16 K, or 32 K • Requires a special memory cache that matches the block size • Create cache by setting the appropriate initialization parameter: • DB_2K_CACHE_SIZE • DB_4K_CACHE_SIZE • DB_8K_CACHE_SIZE • DB_16K_CACHE_SIZE • DB_32K_CACHE_SIZE Oracle9i Database Administrator: Implementation and Administration

  25. Tablespaces With Nonstandard Data Block Size Example: CREATE TABLESPACE TBS_2K DATAFILE '<C:\oracle\oradata\tbs_2k.dbf' SIZE 4M BLOCKSIZE 2K; Oracle9i Database Administrator: Implementation and Administration

  26. Configuring and Viewing Storage • Changes you can make to a tablespace: • LOGGING/NOLOGGING • PERMANENT/TEMPORARY • READ ONLY/READ WRITE • Coalesce contiguous storage space • Add new datafile • Rename a datafile • Change size of a datafile Oracle9i Database Administrator: Implementation and Administration

  27. Configuring and Viewing Storage Syntax: ALTER TABLESPACE <tablespacename> ADD|RENAME DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF REUSE DEFAULT STORAGE (INITIAL <nn> NEXT <nn> PCTINCREASE <nn> MINEXTENTS <nn> MAXEXTENTS <nn>) MINIMUM EXTENT <nn> COALESCE Oracle9i Database Administrator: Implementation and Administration

  28. Configuring and Viewing Storage Taking a tablespace offline: • Stops it from being accessed by users Methods for taking a tablespace offline: • NORMAL: default • TEMPORARY: for damaged datafiles • IMMEDIATE: for damaged disk (media) Oracle9i Database Administrator: Implementation and Administration

  29. Read-only Tablespaces • Can only be queried • No inserts, updates, or deletes • Example: ALTER TABLESPACE ACCOUNTING READ ONLY; Oracle9i Database Administrator: Implementation and Administration

  30. Dropping Tablespaces • Syntax: DROP TABLESPACE <tablespacename> INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; Use when there are constraints outside the tablespace that reference this tablespace Use when there is data in the tablespace Oracle9i Database Administrator: Implementation and Administration

  31. Querying the Data Dictionary For Storage Data Oracle9i Database Administrator: Implementation and Administration

  32. Querying the Data Dictionary For Storage Data Example: Find adjacent free extents that should be coalesced SELECT BLOCK_ID, BLOCK_ID+BLOCKS NEXT_BLOCK_ID, BLOCKS FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'USERS' ORDER BY BLOCK_ID; Oracle9i Database Administrator: Implementation and Administration

  33. Querying the Data Dictionary For Storage Data Identifying adjacent free sets of blocks Oracle9i Database Administrator: Implementation and Administration

  34. Overview of Undo Data • Undo data is made of undo blocks • Contain before images of data blocks • Assist in read-consistency • Two methods for managing: • Manual: the old way (with manually created redo segments) • Automatic: the new way (with undo tablespace) Oracle9i Database Administrator: Implementation and Administration

  35. Implementing Automatic Undo Management Requirements: • Set initialization parameters: • UNDO_MANAGEMENT=AUTO • UNDO_TABLESPACE=<tablespacename> • Create UNDO tablespace: • While creating the database • Later with CREATE UNDO TABLESPACE command Oracle9i Database Administrator: Implementation and Administration

  36. Implementing Automatic Undo Management Optional initialization parameters: • UNDO_RETENTION: time in seconds that undo data is saved after commit (default is 900 seconds) • UNDO_SURPRESS_ERRORS: defines error handling when transitioning from manual to automatic undo management Oracle9i Database Administrator: Implementation and Administration

  37. Chapter Summary • Database structures are divided into logical and physical groups • Physical structures include datafiles, control files, and redo log files • Logical structures include tablespaces, extents, and data blocks • A tablespace always has at least one datafile where its data is stored • Locally managed tablespaces use a bitmap to track extent free space Oracle9i Database Administrator: Implementation and Administration

  38. Chapter Summary • When using OMF, omit the DATAFILE clause in the CREATE TABLESPACE command • AUTOEXTEND ON allows a datafile to grow as needed • Dictionary-managed tablespaces: • Use the data dictionary to track extent free space • Don’t automatically coalesce free space • Locally managed tablespaces: • Automatically coalesce free space • Keep all extents the same size or a variable system-controlled size Oracle9i Database Administrator: Implementation and Administration

  39. Chapter Summary • Types of segments: data, index, temporary, and rollback • Temporary tablespaces store data while sorting, and temporary tables • Tablespaces can be ONLINE or OFFLINE • A READ ONLY tablespace is not included in regular backups or recoveries Oracle9i Database Administrator: Implementation and Administration

  40. Chapter Summary • Dropping a tablespace with INCLUDING CONTENTS destroys all its data • Data dictionary views for tablespaces include DBA_TABLESPACES and DBA_FREE_EXTENTS • Undo data gives users read consistency • Automatic undo management uses an UNDO tablespace Oracle9i Database Administrator: Implementation and Administration

More Related