OCTOBER 10, 2022
DBA HOMEWORK
MOSSA FOUAD MANSOUR
1- difference between locally and dictionary
managed tablespace: (LMT VS DMT)
The LMT is implemented by adding the extent management local clause to the
tablespace definition syntax. Unlike the older dictionary managed tablespaces (DMTs),
LMTs automate extent management and keep the Oracle DBA from being able to
specify the next storage parameter to govern extent sizes. The only exception to this rule
is when NEXT is used with minextents at table creation time.
In a dictionary managed tablespace (DMT), the data dictionary stores the free space
details. While the free blocks list is managed in the segment heard of each table, inside the
tablespace), the Free space is recorded in the [Link]$ table, while used space in the [Link]$
table.
But with high DML-rate busy tablespaces the data dictionary became a I/O bottleneck and the
movement of the space management out of the data dictionary and into the tablespace have two
benefits. First, the tablespace become independent and can be transportable (transportable
tablespaces). Second, locally managed tablespaces remove the O/O contention away from the
SYS tablespace.
2- Tablespace types:
There are three types of tablespaces:
Permanent:
You use permanent tablespaces to store your user and application data. Oracle Database uses
permanent tablespaces to store permanent data, such as system data. Each user is assigned a default
permanent tablespace.
Undo:
A database running in automatic undo management mode transparently creates and manages undo
data in the undo tablespace. Oracle Database uses undo data to roll back transactions, to provide
read consistency, to help with database recovery, and to enable features such as Oracle Flashback
Query. A database instance can have only one active undo tablespace.
Temporary:
Temporary tablespaces are used for storing temporary data, as would be created when SQL
statements perform sort operations. An Oracle database gets a temporary tablespace when the
database is created. You would create another temporary tablespace if you were creating a
temporary tablespace group. Under typical circumstances, you do not have to create additional
temporary tablespaces. If you have an extremely large database, then you might configure
additional temporary tablespaces.
3- automatic storage management:
is a feature provided by Oracle that helps automate and manage database storage, This
feature was launched with the first release of Oracle Database 10.
Automated Storage Manager facilitates handling of Oracle database files by using tools
to manage hard disks and file systems directly, thus allowing database administrators to
control disks using the SQL that database administrators are familiar with in Oracle
environments. In addition, it obviates database administrators from the need to learn to
manage the file systems of different operating systems.
Advantages:
1- Data distribution and disk multiplication
2- The ability to redistribute or move data when changing storage
drives without stopping the database.
3- It makes a multiplicity matrix for independent disks of seven types
and is known as RED
4- Support multiple ways to access storage hardware to avoid outages
and provide load balancing.
5- There are no hotspots for reading or writing from disks/storage