oracle basic storage concepts and settings
DESCRIPTION
Oracle Basic Storage Concepts and Settings. Objectives. Differentiate between logical and physical structures Create many types of tablespaces Configure and view storage for tablespaces and datafiles Use undo data. Introduction to Storage Structures. Internal structures store: - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/1.jpg)
1
OracleBasic Storage Concepts
and Settings
![Page 2: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/2.jpg)
2
Objectives
Differentiate between logical and physical structures
Create many types of tablespaces Configure and view storage for
tablespaces and datafiles Use undo data
![Page 3: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/3.jpg)
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)
![Page 4: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/4.jpg)
4
Introduction to Storage Structures
Physical structures:•Datafiles•Operating system blocks•Redo log files•Control files
![Page 5: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/5.jpg)
5
Introduction to Storage Structures
Logical Structures:•Data block•Extent•Segment•Schema object•Tablespace
![Page 6: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/6.jpg)
6
Introduction to Storage Structures
Logical data blocks map directly to contiguous operating system blocks in datafiles
![Page 7: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/7.jpg)
7
Tablespaces and Datafiles
CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENTEXTENT MANAGEMENT LOCAL|DICTIONARYLOGGING|NOLOGGINGONLINE|OFFLINESEGMENT SPACE MANAGEMENT MANUAL|AUTO
Add multiple files, separated by commas if neededClause is required for user managed filesClause is optional when using OMFCan be used to specify SIZE of OMF file
Note: additional sub-clauses shown later
![Page 8: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/8.jpg)
8
Tablespaces and Datafiles
CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENTEXTENT MANAGEMENT LOCAL|DICTIONARYLOGGING|NOLOGGINGONLINE|OFFLINESEGMENT 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
![Page 9: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/9.jpg)
9
Tablespaces and Datafiles
CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENTEXTENT MANAGEMENT LOCAL|DICTIONARYLOGGING|NOLOGGINGONLINE|OFFLINESEGMENT SPACE MANAGEMENT MANUAL|AUTO
LOCAL: Default; tablespace manages extent free space in a bitmap inside the tablespaceDICTIONARY: tablespace manages extent free space in the data dictionary
Note: additional sub-clauses shown later
![Page 10: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/10.jpg)
10
Tablespaces and Datafiles
CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENTEXTENT MANAGEMENT LOCAL|DICTIONARYLOGGING|NOLOGGINGONLINE|OFFLINESEGMENT SPACE MANAGEMENT MANUAL|AUTO
LOGGING: Default; all DML, DDL, and mass INSERT commands recorded in redo logNOLOGGING: only DML commands recorded in redo log
![Page 11: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/11.jpg)
11
Tablespaces and Datafiles
CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENTEXTENT MANAGEMENT LOCAL|DICTIONARYLOGGING|NOLOGGINGONLINE|OFFLINESEGMENT SPACE MANAGEMENT MANUAL|AUTO
ONLINE: Default; available for useOFFLINE: not available for use
![Page 12: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/12.jpg)
12
Tablespaces and Datafiles
CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <nn> AUTOEXTEND ON|OFF TEMPORARY|PERMANENTEXTENT MANAGEMENT LOCAL|DICTIONARYLOGGING|NOLOGGINGONLINE|OFFLINESEGMENT SPACE MANAGEMENT MANUAL|AUTO
AUTO: Manages segment free space in a bitmap in the tablespaceMANUAL: Manages segment free space in the data dictionary
![Page 13: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/13.jpg)
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)
![Page 14: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/14.jpg)
14
The DATAFILE Clause DATAFILE '<datafilename>'
SIZE <nn>|REUSE
AUTOEXTEND ON|OFF
MAXSIZE <nn>|UNLIMITED
SIZE <nn>: Specify size in bytes, kilobytes, or megabytes REUSE: File already exists
AUTOEXTEND ON: Allow file to expand when Oracle determines more space is needed AUTOEXTEND OFF: Do not expand file
![Page 15: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/15.jpg)
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
![Page 16: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/16.jpg)
16
The EXTENT MANAGEMENT and 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 tablespaceMANUAL: 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 tablespaceUNIFORM SIZE <nn>: Require the specified extent size for all tables and other objects created in the tablespace
![Page 17: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/17.jpg)
17
The EXTENT MANAGEMENT and 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
![Page 18: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/18.jpg)
18
The EXTENT MANAGEMENT Clause
Example: A new table needing 6 M of space skips over the deallocated extents
Coalesce free space periodically to combine deallocated extents
Example of dictionary-managed datafile
![Page 19: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/19.jpg)
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 15MDEFAULT STORAGE (INITIAL 90M NEXT 15M PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 50);
![Page 20: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/20.jpg)
20
Creating a Locally Managed Tablespace
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
All new tables must use extents of the same size or multiples of the size
![Page 21: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/21.jpg)
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;
![Page 22: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/22.jpg)
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
![Page 23: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/23.jpg)
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>
![Page 24: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/24.jpg)
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
![Page 25: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/25.jpg)
25
Tablespaces With Nonstandard Data Block Size
Example:
CREATE TABLESPACE TBS_2K
DATAFILE '<C:\oracle\oradata\tbs_2k.dbf'
SIZE 4M BLOCKSIZE 2K;
![Page 26: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/26.jpg)
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
![Page 27: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/27.jpg)
27
Configuring and Viewing StorageSyntax:
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
![Page 28: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/28.jpg)
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)
![Page 29: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/29.jpg)
29
Read-only Tablespaces Can only be queried No inserts, updates, or deletes Example:
ALTER TABLESPACE ACCOUNTING READ ONLY;
![Page 30: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/30.jpg)
30
Dropping Tablespaces Syntax:
DROP TABLESPACE <tablespacename>
INCLUDING CONTENTS
AND DATAFILES
CASCADE CONSTRAINTS;
Use when there is data in the tablespace
Use when there are constraints outside the tablespace that reference this tablespace
![Page 31: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/31.jpg)
31
Querying the Data Dictionary For Storage Data
![Page 32: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/32.jpg)
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;
![Page 33: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/33.jpg)
33
Querying the Data Dictionary For Storage Data
Identifying adjacent free sets of blocks
![Page 34: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/34.jpg)
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)
![Page 35: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/35.jpg)
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
![Page 36: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/36.jpg)
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
![Page 37: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/37.jpg)
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
![Page 38: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/38.jpg)
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 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
![Page 39: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/39.jpg)
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
![Page 40: Oracle Basic Storage Concepts and Settings](https://reader036.vdocuments.us/reader036/viewer/2022062310/5681632f550346895dd3acb1/html5/thumbnails/40.jpg)
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