Transcript Managing table spaces and data files

Extents, segments and blocks
in detail
Database structure
Database
Table spaces
Data file
physical
logical
Segment
O/S block
Extent
Oracle
block
Table spaces
Belongs only to one database
Consists of one or more operating
system files (i.e. data files)
Brought online while db is running
Except for the SYSTEM table space or
one with an active rollback segment,
they can be taken offline while db is
running
Sizing your table spaces
Identify the max size of each record
Estimate the number of rows in each
table at creation/start up

This could be stated as instance volume
Estimate the growth of the tables.


Determine how many years data you are
to hold etc.
What is the increase in records per year.
Storage clauses for table
spaces
Storage parameters





Initial extentsize of the first extent
Next extent
size of second extent
Pctincrease
% increase for each
extent SET TO 0 !!!!
Minextents
min number of extents
Maxextents
max number of extents
How extents work
Segments need one or more extents.
If pctincrease is anything other than 0
then the table can grow out of hand.
50
50
Table 40
size 30
20
in
MB 10
40
30
20
10
0
0
1 2 3 4 5 6 7 8 9 10 11 12 13 14
Pctincrease > 0
1 2 3 4 5 6 7 8 9 10 11 12 13 14
pctincrease = 0
Creating table spaces
Create tablespace products
Datafile ‘d:/oradata/product1.dbf’ size
100M
Default storage (initial 500K
next 500K
pctincrease 0
minextents 1)
Online;
Creates a table space called products which is
allocated 100M of disk space in a dbf file called
product1.dbf (this is what is backed up) each extent
will be 500K in size and will not grow.
Parameters for creating Table
spaces
Table space
Datafile
name given when created
specifies the data file or files
for the table space
Default storage parameters for all objects in
table space
Minimum
ensures extents are multiples
of this
Online/offline start state
Permanent
will hole permanent objects
Temporary
holds temp objects e.g those
used
in sorts etc.
Cont …
Storage parameters influence the segment
storage allocation

Initial
Next
Maxextents
Minextents

Pcincrease



size of first extent
size of second extent
max number allowed
min number allocated when
segment is created
(default = 1)
% by which extent grows
Adding data files to table
space
ALTER TABLESPACE app_data
ADD DATAFILE
‘/data/app03.dbf’
SIZE 200m;
NOTE: you should always specify the full path of file
location or oracle will store them in the default
directory of the database server
Changing size
Instead of adding space by adding files you
can increase the existing file size.
Used to enlarge a tablespace
ALTER DATABASE DATAFILE
‘/data/app02.dbf’
RESIZE 200m;
NEW SIZE
Moving datafiles
The tablespace must be offline
Target datafiles must exist (use OS
commands)
Delete original file after moving
Must not be SYSTEM tablespace files or
have active rollbacks or temporary
segments
ALTER TABLESPACE app_data RENAME
DATAFILE ‘/data/app01.dbf’ TO
‘/newdata/app01.dbf’;
Dropping table spaces
Removes all contents of tablespace from database
If data is in tablespace INCLUDING CONTENTS
option must be used
Does not matter if tablespace is read-only or not
Recommend that take tablespace offline to check
effect before dropping
DROP TABLESPACE app_data INCLUDING
CONTENTS;
Extents
Set of contiguous number of blocks. Each
segment has one or more extents
May NOT span data files but must exist in
one file
Data blocks


One block corresponds to one or more
physical file blocks allocated from physical file
Set at initialisation parameters of file
DB_BLOCK_SIZE
Types of table spaces
SYSTEM




Data Dictionary information
SYSTEM rollback segment
Required in all dbs or db operations
Should not contain user information but is allowed.
Non-SYSTEM




Rollback segments
Temporary segments
Application data
Application indexes
PCTFREE & PCTUSED
pctused
pctfree
Block empty
Records inserted
Inserts are stopped when pctfree is
met
Pctfree and pctused control the
free space available for inserts.
Pctused does not apply to
indexes
Records deleted and amount of
free space in block increases
Amount of free space in block falls
below the pctused and block
becomes available for inserts and
is added to segments free list, t
remains on free list until block
reaches pctfree again
PCTfree
Setting low means less space wasted in block
but may not have enough room for future
updates
If not enough space to update the entire row
is migrated to another block
Migration is a serious performance problem
10% is normally sufficient but if chaining
(migration) is common it should be
monitored.
Calculation of PCTfree
Final record length – initial record length
Final record length
Express above as a %
As final and initial record lengths in
most systems are approximate then
above formula will only be approximate
and should be rounded to next integer
Automatic segment space
management
This is new in oracle 9i and the benefits include
(according to oracle)




Ease of use
Better space initialisation
Better concurrency handling
Better performance
It users bitmaps in the data file header to map the
blocks, their state and how much amount of data is
in each blocks
It replaces freelist, freelist groups and pctused.
Set in create tablespace command (add option
SEGMENT SPACE MANAGEMENT AUTO)
Extents
An extent is a logical storage unit made up
from contiguous data blocks
First allocated when segment is created and
then added when all blocks in segment are
full
Oracle can manage the extent allocation
through DD or locally
The header block of each segment contains a
directory of extents in that segment
Allocating extents
•Oracle allocates an extent when an object is first
created or when all blocks in a segment are full
•For locally managed tablespaces space for the next
extent is allocated by
•Searching each data files bitmap making up the
tablespace for the contiguous space required
•Exceptions raised if none of the files have enough
free space.
Allocating extents
For dictionary managed tablespaces, space for the next extent is allocated by
1.
Oracle searches the tablespaces for a free extent with an exact match
(or match +1)
2.
If no match occurs, Oracles searches free blocks again for a free extent
larger than required value
1.
If one found and it is less or equal to 5 blocks bigger than
requested size then it will allocate all blocks in free space to
segment
2.
If size greater then 5 blocks then only exact requested size is
allocated
3.
If step 2 fails then oracle coalesces free space in tablespace and repeats
step 2
4.
If step 3 fails then if auto extendible is on oracle will try to extend file
and repeat step 2
5.
If oracle can not extend file or auto extendible if not set then an
exception is raised.
Querying extent information
Query the extent information using:
•DBA_EXTENTS - This will list the extents allocated in the
db for all segments
•DBA_FREE_SPACE - This will list information about free
extents in each tablespace. This will also give and indication
of how badly a tablespace is fragmented
Select tablespace_name, count(*) cnt, max(bytes)/1024 maxbytes, min
(bytes)/1024 minbytes, avg (bytes)/1024 avgbytes,sum (bytes)/1024
sumbytes
From dba_free_space
Group by tablespace_name;
Segments
A logical storage unit made up of one or more extents
Every object in DB that requires space to store data is
allocated a segment
If no segment storage parameters are specified when
segment created then default tablespace parameters are
used, (or db server defaults)
Changes to storage parameters only effect new extents
nor extents in existing segments.
Querying segment information
Use:
DBA_SEGMENTS – this includes information on
parameter and type of segment
V$SORT_SEGMENT – this view contains information
about every sort segment in temporary tablespaces
Note: in DBA_SEGMENTS lob segments are listed as
LOBINDEX for indexes and LOBSEGMENT for data
Managing undo segments
Undo segments record old values of data that were
changed by a transaction
They are used to provide read consistent images of the
data and to rollback uncommitted transactions when
requested by user or at instance recovery.
Create UNDO TABLESPACE <name>
DATAFILE ‘undo.dbf’ size 30M
The undo spaces can be dropped by not until all
transactions using them are complete and committed.
Must be large enough to handle the workload of all
concurrent transactions.
Querying UNDO
You can query undo information using
DBA_ROLLBACK_SEGS – provides information bout
undo segments (on and offline)
V$ROLLNAME – lists all the undo segments online use
USN column to link to V$ROLLSTAT
V$ROLLSTAT – this lists undo statistics
V$UNDOSTAT – view collects 10 minute snapshots that
reflect the performance of the undo tablespaces
Sample question
What is row migration?
1. A single row spread across multiple blocks
2. Move a table from one tablespace to another
3. Storing a row in a different block when there is not
enough room in the current block for the row to
expand
4. Deleting a row and adding it back to the same
table.