Transcript Default Temporary Tablespace

Oracle Architectural
Components
Objectives
 After completing this lesson, you should be
able to do the following:
–
–
Outline the Oracle architecture and its main
components
List the structures involved in connecting a
user to an Oracle Instance
Overview of Primary Components
User
process
Shared Pool
Library
Cache
Server
process
Data Dictionary
Cache
Instance
SGA
Database
Buffer Cache
Redo Log
Buffer
Java Pool
Large Pool
PGA
PMON SMON DBWR LGWR
Parameter
file
Datafiles
Control
files
CKPT
Redo Log
files
Password
file
Database
Others
Archived
Log files
Oracle Server
 An Oracle server:
–
–
Is a database
management system
that provides an open,
comprehensive,
integrated approach to
information
management
Consists of an Oracle
Instance and an Oracle
database
Oracle Server
Oracle Instance
 An Oracle Instance:
–
–
–
Is a means to access an Oracle database
Always opens one and only one database
Consists of memory and background process
structures
Shared Pool
Library
Cache
Data Dictionary
Cache
Instance
SGA
Database
Buffer Cache
Redo Log
Buffer
Java Pool
Large Pool
PMON SMON DBWR LGWR
CKPT
Others
Memory
structures
Background
process structures
Establishing a Connection
and Creating a Session
 Connecting to an Oracle Instance:
–
–
Establishing a user connection
Creating a session
Connection
established
User
process
Database user
Server
process
Session created
Oracle Server
Oracle Database
 An Oracle database:
–
–
Is a collection of data that is treated as a unit
Consists of three file types
Parameter
file
Password
file
Oracle Database
Redo
Log
Datafiles Control
files
files
Archived
Log files
Physical Structure
 The physical structure includes three types
of files:
–
–
–
Control files
Datafiles
Redo log files
Header
Online
Redo Log
files
Datafiles
(includes
Data
Dictionary)
Control
files
Memory Structure
 Oracle’s memory structure consists of two
memory areas known as:
–
–
System Global Area (SGA): Allocated at
instance startup, and is a fundamental
component of an Oracle Instance
Program Global Area (PGA): Allocated when
the server process is started
System Global Area
–
The SGA consists of several memory
structures:




–
Shared Pool
Database Buffer Cache
Redo Log Buffer
Other structures (for example, lock and latch
management, statistical data)
There are two additional memory structures
that can be configured within the SGA:
 Large Pool
 Java Pool
System Global Area
–
–
–
SGA is dynamic
Sized by the SGA_MAX_SIZE parameter
Allocated and tracked in granules by SGA
components
 Contiguous virtual memory allocation
 Granule size based on total estimated
SGA_MAX_SIZE
Shared Pool
–
Used to store:
 Most recently executed SQL statements
 Most recently used data definitions
–
It consists of two key performance-related
memory structures:
 Library Cache
 Data Dictionary Cache
–
Sized by the parameter
SHARED_POOL_SIZE
ALTER SYSTEM SET
SHARED_POOL_SIZE = 64M;
Shared Pool
Library
Cache
Data
Dictionary
Cache
Library Cache
–
–
–
–
Stores information about the most recently
used SQL and PL/SQL statements
Enables the sharing of commonly used
statements
Is managed by a least recently used (LRU)
algorithm
Consists of two structures:
 Shared SQL area
 Shared PL/SQL area
–
Size determined by the Shared Pool sizing
Data Dictionary Cache
–
–
–
–
–
A collection of the most recently used
definitions in the database
Includes information about database files,
tables, indexes, columns, users, privileges,
and other database objects
During the parse phase, the server process
looks at the data dictionary for information to
resolve object names and validate access
Caching data dictionary information into
memory improves response time on queries
and DML
Size determined by the Shared Pool sizing
Database Buffer Cache
–
–
–
–
Stores copies of data blocks that have been
retrieved from the datafiles
Enables great performance gains when you
obtain and update data
Managed through an LRU algorithm
DB_BLOCK_SIZE determines primary block
size
Database Buffer
Cache
Database Buffer Cache
–
Consists of independent sub-caches:
 DB_CACHE_SIZE
 DB_KEEP_CACHE_SIZE
 DB_RECYCLE_CACHE_SIZE
–
Can be dynamically resized
–
DB_CACHE_ADVICE set to gather statistics for
predicting different cache size behavior
Statistics displayed by V$DB_CACHE_ADVICE
ALTER SYSTEM SET DB_CACHE_SIZE = 96M;
–
Redo Log Buffer
–
–
–
–
–
Records all changes made to the database
data blocks
Primary purpose is recovery
Changes recorded within are called redo
entries
Redo entries contain information to
reconstruct
or redo changes
Size defined by LOG_BUFFER
Redo Log
Buffer
Large Pool
–
–
–
An optional area of memory in the SGA
Relieves the burden placed on the Shared
Pool
Used for:




–
–
Session memory (UGA) for the Shared Server
I/O server processes
Backup and restore operations or RMAN
Parallel execution message buffers
- PARALLEL_AUTOMATIC_TUNING set to
TRUE
Does not use an LRU list
Sized by LARGE_POOL_SIZE
Java Pool
–
–
–
Services parsing requirements for Java
commands
Required if installing and using Java
Sized by JAVA_POOL_SIZE parameter
Program Global Area
–
–
–
–
Memory reserved for each
user process connecting to
an Oracle database
Allocated when a process is
created
Deallocated when the
process is terminated
Used by only one process
PGA
Server
process
User
process
Process Structure
 Oracle takes advantage of various types of
processes:
–
–
–
User process: Started at the time a database
user requests connection to the Oracle server
Server process: Connects to the Oracle
Instance and is started when a user
establishes a session
Background processes: Started when an
Oracle Instance is started
User Process
–
–
–
A program that requests interaction with the
Oracle server
Must first establish a connection
Does not interact directly with the Oracle server
Server
process
User
process
Database user
Connection
established
Server Process
–
–
–
A program that directly interacts with the Oracle
server
Fulfills calls generated and returns results
Can be Dedicated or Shared Server
Connection
established
User
process
Database user
Server
process
Session created
Oracle server
Background Processes
 Maintains and enforces relationships
between physical and memory structures
–
Mandatory background processes:
 DBWn
 LGWR
–
PMON
SMON
CKPT
Optional background processes:
 ARCn
RECO
 CJQ0
 Dnnn
 LCKn
LMDn
LMON
Pnnn
QMNn
Snnn
Database Writer (DBWn)

Instance
SGA
Database
Buffer
Cache
DBWn
Datafiles Control
files
Database
Redo
Log
files
DBWn writes when:
– Checkpoint occurs
– Dirty buffers reach
threshold
– There are no free
buffers
– Timeout occurs
– RAC ping request is
made
– Tablespace OFFLINE
– Tablespace READ ONLY
– Table DROP or
TRUNCATE
– Tablespace BEGIN
BACKUP
Log Writer (LGWR)
 LGWR writes:
Instance
SGA
Redo Log
Buffer
DBWn LGWR
Datafiles Control
files
Database
Redo
Log
files
–
–
–
–
–
At commit
When one-third full
When there is 1
MB of redo
Every three
seconds
Before DBWn
writes
System Monitor (SMON)
Instance
SGA
SMON
Datafiles Control
files
Database
Redo
Log
files

Responsibilities:
– Instance recovery
 Rolls forward
changes in redo
logs
 Opens database for
user access
 Rolls back
uncommitted
transactions
– Coalesces free space
– Deallocates temporary
segments
Process Monitor (PMON)
Instance
SGA
 Cleans up after
 failed processes by:
–
–
PMON
–
–
PGA area
Rolling back the
transaction
Releasing locks
Releasing other
resources
Restarting dead
dispatchers
Checkpoint (CKPT)
 Responsible for:
Instance
SGA
–
–
DBWn LGWR CKPT
–
Datafiles Control
files
Database
Redo
Log
files
Signaling DBWn at
checkpoints
Updating datafile
headers with
checkpoint
information
Updating control
files with
checkpoint
information
Archiver (ARCn)
–
–
–
Optional background process
Automatically archives online redo logs when
ARCHIVELOG mode is set
Preserves the record of all changes made to the
database
Datafiles Control
files
Redo
Log
files
ARCn
Archived
Redo Log
files
Logical Structure
–
–
Dictates how the physical space of a database is
used
Hierarchy consisting of tablespaces, segments,
extents, and blocks
Tablespace
Datafile
Segment
Extent
Segment
Blocks
Tablespaces and Datafiles
 Oracle stores data logically in tablespaces
and physically in datafiles.
–
Tablespaces:
 Can belong to only one database at a time
 Consist of one or more datafiles
 Are further divided into logical units of storage
–
Datafiles:
Database
Tablespace
 Can belong to only one
tablespace and one database
Datafiles
 Are a repository for schema
object data
Types of Tablespaces
–
SYSTEM tablespace
 Created with the database
 Contains the data dictionary
 Contains the SYSTEM undo segment
–
Non-SYSTEM tablespace
 Separate segments
 Eases space administration
 Controls amount of space allocated to a user
Creating Tablespaces
 A tablespace is created using the command:
 CREATE TABLESPACE
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;
Space Management in
Tablespaces
–
Locally managed tablespace:
 Free extents managed in the tablespace
 Bitmap is used to record free extents
 Each bit corresponds to a block or group of
blocks
 Bit value indicates free or used
–
Dictionary-managed tablespace:
 Free extents are managed by the data
dictionary
 Appropriate tables are updated when extents
are allocated or deallocated
Locally Managed Tablespaces
–
–
Reduced contention on data dictionary tables
No undo generated when space allocation or
deallocation occurs
No coalescing required
–
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Dictionary-Managed
Tablespaces
–
–
–
Extents are managed in the data dictionary
Each segment stored in the tablespace can have
a different storage clause
Coalescing required
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf'
SIZE 500M EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE
(initial 1M NEXT 1M PCTINCREASE 0);
Undo Tablespace
–
–
–
–
Used to store undo segments
Cannot contain any other objects
Extents are locally managed
Can only use the DATAFILE and EXTENT
MANAGEMENT clauses
CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M;
Temporary Tablespaces
–
–
–
Used for sort operations
Cannot contain any permanent objects
Locally managed extents recommended
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
Default Temporary Tablespace
–
–
–
Specifies a database-wide default temporary
tablespace
Eliminates using SYSTEM tablespace for
storing temporary data
Can be created by using:
 CREATE DATABASE
- Locally managed
 ALTER DATABASE
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE temp;
Creating a Default Temporary
Tablespace
–
During database creation:
CREATE DATABASE DBA01
LOGFILE
GROUP 1 ('/$HOME/ORADATA/u01/redo01.log') SIZE 100M,
GROUP 2 ('/$HOME/ORADATA/u02/redo02.log') SIZE 100M,
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/$HOME/ORADATA/u01/system01.dbf' SIZE 325M
UNDO TABLESPACE undotbs
DATAFILE '/$HOME/ORADATA/u02/undotbs01.dbf' SIZE 200
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/$HOME/ORADATA/u03/temp01.dbf' SIZE 4M
CHARACTER SET US7ASCII
Creating a Default Temporary
Tablespace
–
After database creation:
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE default_temp2;
–
To find the default temporary tablespace for
the database query DATABASE_PROPERTIES
SELECT * FROM DATABASE_PROPERTIES;
Restrictions on Default
Temporary Tablespace
 Default temporary tablespaces cannot be:
–
–
–
Dropped until after a new default is made
available
Taken offline
Altered to a permanent tablespace
Read Only Tablespaces
–
Use the following command to place a
tablespace in read only mode
ALTER TABLESPACE userdata READ ONLY;
 Causes a checkpoint
 Data available only for read operations
 Objects can be dropped from tablespace
Taking a Tablespace Offline
–
–
Not available for data access
Tablespaces that cannot be taken offline:
 SYSTEM tablespace
 Tablespaces with active undo segments
 Default temporary tablespace
–
To take a tablespace offline:
ALTER TABLESPACE userdata OFFLINE;
– To bring a tablespace online:
ALTER
TABLESPACE userdata ONLINE;
Changing Storage Settings
–
Using ALTER TABLESPACE command to
change storage settings:
ALTER TABLESPACE userdata MINIMUM EXTENT 2M;
ALTER TABLESPACE userdata
DEFAULT STORAGE (INITIAL 2M NEXT 2M
MAXEXTENTS 999);
–
Storage settings for locally managed
tablespaces cannot be altered.
Resizing a Tablespace
 A tablespace can be resized by:
–
Changing the size of a datafile:
 Automatically using AUTOEXTEND
 Manually using ALTER TABLESPACE
–
Adding a datafile using ALTER TABLESPACE
Enabling Automatic Extension
of Datafiles
–
Can be resized automatically with the
following commands:
 CREATE DATABASE
 CREATE TABLESPACE
 ALTER TABLESPACE … ADD DATAFILE
–
Example:
–
Query the DBA_DATA_FILES view to
determine whether AUTOEXTEND is enabled.
CREATE TABLESPACE user_data
DATAFILE
'/u01/oradata/userdata01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
Manually Resizing a Datafile
–
Manually increase or decrease a datafile size
using ALTER DATABASE
–
Resizing a datafile adds more space without
adding more datafiles
Manual resizing of a datafile reclaims unused
space in database
Example:
–
–
ALTER DATABASE
DATAFILE '/u03/oradata/userdata02.dbf'
RESIZE 200M;
Adding Datafiles to a
Tablespace
Increases the space allocated to a tablespace
by adding additional datafiles
– ADD DATAFILE clause is used to add a
datafile
– Example:
ALTER
TABLESPACE user_data
–
ADD DATAFILE '/u01/oradata/userdata03.dbf'
SIZE 200M;
Methods for Moving Datafiles
–
ALTER TABLESPACE
 Tablespace must be offline
 Target datafiles must exist
ALTER TABLESPACE userdata RENAME
DATAFILE '/u01/oradata/userdata01.dbf'
TO '/u02/oradata/userdata01.dbf';
–
Steps to rename a datafile:
 Take the tablespace offline.
 Use an OS command to move or copy the
files.
 Execute the ALTER TABLESPACE RENAME
DATAFILE command.
 Bring the tablespace online.
Methods for Moving Datafiles
–
ALTER DATABASE
 Database must be mounted
 Target datafile must exist
ALTER DATABASE RENAME
FILE '/u01/oradata/system01.dbf'
TO '/u03/oradata/system01.dbf';
Dropping Tablespaces
–
Cannot drop a tablespace if it:
 Is the SYSTEM tablespace
 Has active segments
–
–
–
INCLUDING CONTENTS drops the segments
INCLUDING CONTENTS AND DATAFILES
deletes datafiles
CASCADE CONSTRAINTS drops all referential
integrity constraints
DROP TABLESPACE userdata
INCLUDING CONTENTS AND DATAFILES;
Managing Tablespaces Using
OMF
–
Define the DB_CREATE_FILE_DEST
parameter in one of the following ways:
 Initialization parameter file
 Set dynamically using ALTER SYSTEM
command
ALTER SYSTEM SET
db_create_file_dest = '/u01/oradata/dba01';
–
When creating the tablespace:
 Datafile is automatically created and located in
DB_CREATE_FILE_DEST
 Default size is 100 MB
 AUTOEXTEND is set to UNLIMITED
Managing Tablespaces with
OMF
–
Creating an OMF tablespace:
CREATE TABLESPACE text_data DATAFILE SIZE 20M;
–
Adding an OMF datafile to an existing
tablespace:
ALTER TABLESPACE text_data ADD DATAFILE;
– Dynamically changing default file location:
ALTER
SYSTEM SET
db_create_file_dest = '/u01/oradata/dba01';
–
Dropping a tablespace includes deleting OS
files:
Obtaining Tablespace Information

Obtaining tablespace and datafile information can be obtained by
querying the following:
– Tablespaces:
 DBA_TABLESPACES
 V$TABLESPACE
–
Datafile information:
 DBA_DATA_FILES
 V$DATAFILE
–
Temp file information:
 DBA_TEMP_FILES
 V$TEMPFILE