Oracle

1

• Oracle
• Basic Storage Concepts
• and Settings

2
Objectives

• Differentiate between logical and physical
  structures
• Create many types of tablespaces
• Configure and view storage for tablespaces and
  datafiles
• Use undo data

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)

4
Introduction to Storage Structures

• Physical structures
• Datafiles
• Operating system blocks
• Redo log files
• Control files

5
Introduction to Storage Structures

• Logical Structures
• Data block
• Extent
• Segment
• Schema object
• Tablespace

6
Introduction to Storage Structures
Logical data blocks map directly to contiguous
operating system blocks in datafiles
7
Tablespaces and Datafiles
CREATE TABLESPACE lttablespacenamegt DATAFILE
ltfilenamegt SIZE ltnngt AUTOEXTEND ONOFF
TEMPORARYPERMANENT EXTENT MANAGEMENT
LOCALDICTIONARY LOGGINGNOLOGGING ONLINEOFFLINE
SEGMENT SPACE MANAGEMENT MANUALAUTO

• 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

8
Tablespaces and Datafiles
CREATE TABLESPACE lttablespacenamegt DATAFILE
ltfilenamegt SIZE ltnngt AUTOEXTEND ONOFF
TEMPORARYPERMANENT EXTENT MANAGEMENT
LOCALDICTIONARY LOGGINGNOLOGGING ONLINEOFFLINE
SEGMENT SPACE MANAGEMENT MANUALAUTO

• TEMPORARY This option used only when creating
  temporary tablespaces that are dictionary-managed.
  
• PERMANENT Default option stores permanent
  objects such as tables and indexes

9
Tablespaces and Datafiles
CREATE TABLESPACE lttablespacenamegt DATAFILE
ltfilenamegt SIZE ltnngt AUTOEXTEND ONOFF
TEMPORARYPERMANENT EXTENT MANAGEMENT
LOCALDICTIONARY LOGGINGNOLOGGING ONLINEOFFLINE
SEGMENT SPACE MANAGEMENT MANUALAUTO

• 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

10
Tablespaces and Datafiles
CREATE TABLESPACE lttablespacenamegt DATAFILE
ltfilenamegt SIZE ltnngt AUTOEXTEND ONOFF
TEMPORARYPERMANENT EXTENT MANAGEMENT
LOCALDICTIONARY LOGGINGNOLOGGING ONLINEOFFLINE
SEGMENT SPACE MANAGEMENT MANUALAUTO

• LOGGING Default all DML, DDL, and mass INSERT
  commands recorded in redo log
• NOLOGGING only DML commands recorded in redo log

11
Tablespaces and Datafiles
CREATE TABLESPACE lttablespacenamegt DATAFILE
ltfilenamegt SIZE ltnngt AUTOEXTEND ONOFF
TEMPORARYPERMANENT EXTENT MANAGEMENT
LOCALDICTIONARY LOGGINGNOLOGGING ONLINEOFFLINE
SEGMENT SPACE MANAGEMENT MANUALAUTO

• ONLINE Default available for use
• OFFLINE not available for use

12
Tablespaces and Datafiles
CREATE TABLESPACE lttablespacenamegt DATAFILE
ltfilenamegt SIZE ltnngt AUTOEXTEND ONOFF
TEMPORARYPERMANENT EXTENT MANAGEMENT
LOCALDICTIONARY LOGGINGNOLOGGING ONLINEOFFLINE
SEGMENT SPACE MANAGEMENT MANUALAUTO
AUTO Manages segment free space in a bitmap in
the tablespace MANUAL Manages segment free space
in the data dictionary
13
Implementing OMF With Tablespaces

• DB_CREATE_FILE_DEST must be set
• In initltsidgt.ora
• Or, during session
• CREATE TABLESPACE command
• Omit DATAFILE clause in
• Or, include DATAFILE clause but only include SIZE
  clause (omit file name)

14
The DATAFILE Clause

• DATAFILE 'ltdatafilenamegt'
• SIZE ltnngtREUSE
• AUTOEXTEND ONOFF
• MAXSIZE ltnngtUNLIMITED

• AUTOEXTEND ON Allow file to expand when Oracle
  determines more space is needed
• AUTOEXTEND OFF Do not expand file

• SIZE ltnngt Specify size in bytes, kilobytes, or
  megabytes
• REUSE File already exists

15
The DATAFILE Clause

• DATAFILE 'ltdatafilenamegt'
• SIZE ltnngtREUSE
• AUTOEXTEND ONOFF
• MAXSIZE ltnngtUNLIMITED

• Used with AUTOEXTEND ON
• MAXSIZE ltnngt Specify maximum file size in bytes,
  kilobytes, or megabytes
• UNLIMITED File can grow to limits of operating
  system

16
The EXTENT MANAGEMENT and SEGMENT SPACE
MANAGEMENT Clauses

• Syntax for EXTENT MANAGEMENT LOCAL
• EXTENT MANAGEMENT LOCALDICTIONARY
• AUTOALLOCATEUNIFORM SIZE ltnngt
• SEGMENT SPACE MANAGEMENT MANUALAUTO
• MINIMUM EXTENT ltnngt
• DEFAULT STORAGE (INITIAL ltnngt NEXT ltnngt
  PCTINCREASE ltnngt
• MINEXTENTS ltnngt MAXEXTENTS ltnngt)

• 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 ltnngt Require the specified extent
  size for all tables and other objects created in
  the tablespace

17
The EXTENT MANAGEMENT and SEGMENT SPACE
MANAGEMENT Clauses

• Syntax for dictionary-managed tablespace
• EXTENT MANAGEMENT LOCALDICTIONARY
• SEGMENT SPACE MANAGEMENT MANUALAUTO
• AUTOALLOCATEUNIFORM SIZE ltnngt
• MINIMUM EXTENT ltnngt
• DEFAULT STORAGE
• (INITIAL ltnngt NEXT ltnngt PCTINCREASE ltnngt
• MINEXTENTS ltnngt MAXEXTENTS ltnngt)

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
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
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)

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
21
Creating a Locally Managed Tablespace

• Example
• CREATE TABLESPACE USER_AUTO
• DATAFILE 'ltCoracle\oradata\user_auto01.dbf'
• SIZE 20M AUTOEXTEND OFF
• EXTENT MANAGEMENT LOCAL AUTOALLOCATE
• SEGMENT SPACE MANAGEMENT AUTO

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 tables data

23
Temporary Tablespaces

• Oracle recommends creating locally managed
  temporary tablespaces
• Used for temporary segments
• Command syntax
• CREATE TEMPORARY TABLESPACE lttablespacenamegt
• TEMPFILE ltfilenamegt SIZE ltnngt AUTOEXTEND ONOFF
• EXTENT MANAGEMENT LOCAL UNIFORM SIZE ltnngt

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

25
Tablespaces With Nonstandard Data Block Size

• Example
• CREATE TABLESPACE TBS_2K
• DATAFILE 'ltC\oracle\oradata\tbs_2k.dbf'
• SIZE 4M BLOCKSIZE 2K

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

27
Configuring and Viewing Storage

• Syntax
• ALTER TABLESPACE lttablespacenamegt
• ADDRENAME DATAFILE ltfilenamegt
• SIZE ltnngt AUTOEXTEND ONOFF REUSE
• DEFAULT STORAGE (INITIAL ltnngt NEXT ltnngt
• PCTINCREASE ltnngt
• MINEXTENTS ltnngt MAXEXTENTS ltnngt)
• MINIMUM EXTENT ltnngt
• COALESCE

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)

29
Read-only Tablespaces

• Can only be queried
• No inserts, updates, or deletes
• Example
• ALTER TABLESPACE ACCOUNTING READ ONLY

30
Dropping Tablespaces

• Syntax
• DROP TABLESPACE lttablespacenamegt
• 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
31
Querying the Data Dictionary For Storage Data
32
Querying the Data Dictionary For Storage Data

• Example
• Find adjacent free extents that should be
  coalesced
• SELECT BLOCK_ID, BLOCK_IDBLOCKS NEXT_BLOCK_ID,
  BLOCKS
• FROM DBA_FREE_SPACE
• WHERE TABLESPACE_NAME 'USERS'
• ORDER BY BLOCK_ID

33
Querying the Data Dictionary For Storage Data
Identifying adjacent free sets of blocks
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)

35
Implementing Automatic Undo Management

• Requirements
• Set initialization parameters
• UNDO_MANAGEMENTAUTO
• UNDO_TABLESPACElttablespacenamegt
• Create UNDO tablespace
• While creating the database
• Later with CREATE UNDO TABLESPACE command

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

37
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

38
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
• Dont automatically coalesce free space
• Locally managed tablespaces
• Automatically coalesce free space
• Keep all extents the same size or a variable
  system-controlled size

39
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

40
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