Creating and managing tablespaces
Tablespaces are logical storage groups
that can be used
to store logical database constructs,
such as tables and indexes
Logically it stores the database files
A Segment is a set of Extents,
one or more Extents,
allocated for certain logical structures
SAN: Storage area network
inside the database ( ex: table, index)
NAS: network attached storage
An Extent is a set of contiguous NFS: network file system
Oracle Data Blocks ASM: automatic storage
it is much more efficient, management
when allocating space
Oracle data stored in DB blocks
1 Block= 8 K by default
A single Oracle Data Block
contains one or more rows
Prepared By :Khaled AlKhudari Oracle DBA Course
Creating and managing tablespaces
SYSTEM
SYSAUX
TEMP
UNDO
USERS
Prepared By :Khaled AlKhudari Oracle DBA Course
Creating and managing tablespaces
SYSTEM tablespace
Used for core functionality.
Stores the data dictionary ( belong to sys schema).
Oracle create system tablespace automatically when the database is created.
you cannot rename or drop the SYSTEM tablespace.
SYSAUX tablespace
The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace.
It reduces the load on the SYSTEM tablespace.
Oracle create it automatically when the database is created
you cannot rename or drop the SYSAUX tablespace.
TEMP tablespace
are used to manage space for database sort and joining operations and for storing
global temporary tables.
Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE,
SELECT DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS,…
Prepared By :Khaled AlKhudari Oracle DBA Course
Creating and managing tablespaces
UNDO tablespace
is used to roll back, or undo, changes to the database.
• Roll back transactions when a ROLLBACK statement is issued
• Recover the database
• Provide read consistency
USERS tablespace
Stores users objects and data
every database should have a tablespace for permanent user data that is assigned
to users. Otherwise, user objects will be created in the SYSTEM tablespace, which
is not good practice.
In the preconfigured database, USERS is designated as the default tablespace for
all new users.
Note: all these tablespaces exits in the container database and also the pluggable databases .
Prepared By :Khaled AlKhudari Oracle DBA Course
Creating and managing tablespaces
rows/colums
Tablespace
Table A Table B
Row piece
(segment) (segment)
Block 1
Row piece
Table B
If an entire row can be inserted into a single data block,
then Oracle stores the row as one row piece.
Row piece
if all of a row's data cannot be inserted into a single data block or
if an update to an existing row causes the row to outgrow its data block, Block 2
then Oracle stores the row using multiple row pieces
A data block usually contains only one row piece for each row.
When Oracle must store a row in more than one row piece,
it is chained across multiple blocks.
extent
Prepared By :Khaled AlKhudari Oracle DBA Course
Creating and managing tablespaces
Database block content
Contains the segment type ( table or index )
Contains the The block address
Contains the Row directory
The block header grows downward from the top
Enable the block header and the row data to grow
It is the actual data for the rows
Row data grows upward from the bottom
Prepared By :Khaled AlKhudari Oracle DBA Course
Creating and managing tablespaces
About creating tablespace
Creating tablespace is an allocation of space in the DB that can contain schema objects.
We can do this by create tablespace statement or EM express .
We can create 3 types of tablespaces
Prerequisites: the DB must be opened, you must have create tablespace system privilege.
Note: to create sysaux tablespace you must have SYSDBA system privilege
Prepared By :Khaled AlKhudari Oracle DBA Course
Creating and managing tablespaces
About creating tablespace
1- file name and size
You must specify DATAFILE or TEMPFILE clause when you create a tablespace, this will specify the name and
the location of the data file or the temp file.
A tablespace must have at least one data file or temp file.
You must also specify initial file size.
You can include AUTOEXTEND ON clause to automatically extend the file when it is full.
in this case you need to specify increment amount +max size
You can include bigfile or smallfile clause to override the default tablespace type.
A bigfile tablespace contains only one data file or temp file, which can contain up
to approximately 4 billion blocks
A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 data files or temp files,
each of which can contain up to approximately 4 million blocks
Prepared By :Khaled AlKhudari Oracle DBA Course
Creating and managing tablespaces
About creating tablespace
2- Availability
You can also include the online or offline clause to make the tablespace available or not available
online or offline clause can not be used with temporary tablespace .
online is the default .
DBA_tablespaces indicates whether each tablespace online or offline .
3- Block size
you can include the BLOCKSIZE to specify nonstandard block size.
In order to specify this clause, the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter
must be set, and the integer you specify in this clause must correspond with the setting of one
DB_nK_CACHE_SIZE parameter setting.
You cannot specify nonstandard block sizes for a temporary tablespace or if you intend to assign
this tablespace as the temporary tablespace for any users
The default is 8kB
Prepared By :Khaled AlKhudari Oracle DBA Course
Creating and managing tablespaces
About creating tablespace
4- Extent management
you can include EXTENT MANAGEMENT clause to specify how the extents of the tablespace will be
managed.
AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. You
cannot specify AUTOALLOCATE for a temporary tablespace.
UNIFORM
specifies that the tablespace is managed with uniform extents of SIZE bytes.The default SIZE is 1mb.
All extents of temporary tablespaces are of uniform size, so this keyword is optional for a
temporary tablespace.
However, you must specify UNIFORM in order to specify SIZE. You cannot specify UNIFORM for an undo
tablespace.
If you do not specify AUTOALLOCATE or UNIFORM, then the default is UNIFORM for temporary tablespaces
and AUTOALLOCATE for all other types of tablespaces
Prepared By :Khaled AlKhudari Oracle DBA Course
Creating and managing tablespaces
About creating tablespace
5- logging clause
Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs,
and partitions within the tablespace.
The logging_clause lets you specify whether creation of a database object will be logged in the redo log
file (LOGGING) or not (NOLOGGING).
If you omit this clause, then the default is LOGGING.
This clause is not valid for a temporary or undo tablespace.
Oracle recommend using LOGGING.
6- segment management clause
It lets you specify whether Oracle Database should track the used and free space in the segments in the
tablespace using free lists or bitmaps. This clause is not valid for a temporary tablespace.
AUTO :Specify AUTO if you want the database to manage the free space of segments in the tablespace
using a bitmap.
MANUAL :Specify MANUAL if you want the database to manage the free space of segments in the tablespace
using free lists
7- data segment compression ( it is disabled by default )
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-TABLESPACE.html#GUID-51F07BF5-EFAF-4910-9040-C473B86A8BF9
Prepared By :Khaled AlKhudari Oracle DBA Course
Creating and managing tablespaces
Altering and Dropping Tablespaces
When you create a tablespace, it is initially a read/write tablespace.
Use the ALTER TABLESPACE statement to take a tablespace offline or online,
add data files or temp files to it, or make it a read-only tablespace.
A tablespace can be in one of three different statuses or states:
• Read Write
• Read Only
• Offline with one of the following options:
Normal
Temporary
Immediate
Note: System tablespaces may not be taken offline.
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/ALTER-TABLESPACE.html#GUID-CA074861-55D3-4768-8995-43D4DA26365D
Add space to an existing tablespace by either adding data files to the tablespace or changing the size of
an existing data file.
Use the DROP TABLESPACE statement to drop a tablespace and its contents from the database if you no
longer need its content.
Prepared By :Khaled AlKhudari Oracle DBA Course
