Oracle_ Advanced Features (4) Database storage structure

Database storage structure
Divided into: physical storage structure and logical storage structure.
The physical structure and logical structure are separated, and the storage of physical data does not affect access to the logical structure.
1. Physical storage structure
Database files
OS Block
2. Logical storage structure
Tablespace table Space
Segment segment
Extend expansion Zone
DB block data block (8k)
2.1 Creating a table space
Create tablespace <ts_name> datafile ' <file> ' size <n> reuse autoextend on next <n>;
Example:
Create tablespace ts_finds datafile ' E:\app\oradata\orcl\ts_finds01. DBF ' Size 100M
Reuse autoextend on next 10M;

2.2 Modifying table Spaces
--Add data files
Alter tablespace <ts_name> add datafile ' <file> ' size <n>
Reuse Autoextend on Next <n>;
Example:
Alter tablespace ts_finds add datafile ' E:\app\oradata\orcl\ts_finds02. DBF ' Size 10M
Reuse autoextend on next 10M;
--Extend the size of existing data files
ALTER DATABASE datafile ' <file> ' resize <n>;
Example:
ALTER DATABASE DataFile ' E:\APP\ORADATA\ORCL\TS_FINDS02. DBF ' Resize 50M;

2.3 Deleting a table space
Drop tablespace <ts_name> including contents;
Example:
Drop tablespace ts_finds including contents;

3. Relationship
Tablespace-->data File
Segment
Extent--
DB Block-->os block

A tablespace can have multiple data file (up to 64K), a data file can only belong to one tablespace;
A tablespace can have multiple segment, a segment can only belong to one tablespace;
A segment can have multiple extent, a extend can only belong to one segment;
A extent can have more than one DB block (typically 8), and a DB block can only belong to one extent;
A DB block can have more than one OS block (db block is 8k,os block is generally 2k), an OS block belongs to only one DB block;
A data file can store multiple segment, and a segment can be stored in multiple data file;
A data file contains multiple extent, and a extent can belong to only one data file.

4. Data dictionary
SELECT * from Dba_tablespaces;
SELECT * from Dba_data_files;
SELECT * from Dba_temp_files;
SELECT * from dba_segments where segment_name= ' EMP ';
SELECT * from dba_extents where segment_name= ' EMP ';
SELECT * FROM Dba_extents where file_id=4

--Emptying the Recycle Bin
Purge RecycleBin;

5. Database files
Database files include: Data files, temporary files, control files, online redo log files.
A data file is a physical file on disk that is created by the Oracle database,
and contains data structures such as tables and indexes.
A temporary data file is a data file that is part of a temporary tablespace and is used primarily for sorting operations.
A control file is a root file that tracks the physical components of a database.
An online redo log file is a set of files that contains a record of the changes made to the data.
The data is written to these files in the Oracle proprietary format and cannot be read by other programs.

6. Data files
A data file is a database file used to store specific data records such as tables and indexes.
The data file header contains metadata for the data file, such as the data file size and checkpoint SCN.
Each file header contains an absolute file number and a relative file number.
An absolute file number uniquely identifies a data file within the database.
Relative file numbers uniquely identify data files within a tablespace.
When the Oracle database has just created a data file, the allocated disk space is formatted, but does not contain any user data.
However, the database retains this space and will be used in future to hold data segments in its associated tablespace.
As data grows in the tablespace, the Oracle database allocates extents to the segments with the free space in the data file.
The extents are either already used or contain segment data, or they are idle, which can be reused.
DataFile
DataFile Header
Extend used
Extend free
Extend free (previously used, now released)

7. Control files
A database control file is a binary file that is associated with only one database.
Each database has a unique control file (if there are multiple words, the content is the same, in order to back up).
A control file is a root file that the Oracle database uses to locate database files and to manage the state of the database in general.
SELECT * FROM V$database
The control file contains the following information:
Database name and database unique identifier (DBID)
Timestamp of database creation
Information about data files, online redo log files, and archived redo log files
Table Space Information
Rman Backup
Control files are used for database startup and recovery.
During database usage, the Oracle database continuously reads and writes the control files,
And as long as the database is open, the control file must be available so that it can be written.
The Oracle database enables multiple identical control files to be opened and written to the same database.
By multiplexing control files on different disks, the database can be redundant to avoid a single point of failure.
If the control file becomes unavailable, it encounters a failure when the DB instance attempts to access the corrupted control file.
When other copies of the current control file exist, you can reload the database and open it without the need for media recovery.
If all the control files for the database are lost, the instance fails and media recovery is required.

Only the database can modify the information in the control file.
Query V$database Gets the database name and DBID.

8. Online Redo Log
An online redo log consists of two or more pre-allocated files that store changes that occur in the database.
Online redo logs are used to record changes to data files.
The database maintains an online redo log file to prevent data loss.
Specifically, after an instance fails,
Online redo log files enable the Oracle database to recover data that has been committed but not yet written to the data file.
The Oracle database writes each transaction synchronously to the redo log buffer and then writes it to the online redo log.
The only purpose of the Oracle database to use online redo logs is to recover.

The online redo log has a redo thread.
An online redo log contains two or more online redo log files.
The Oracle database uses only one online redo log file at a time to store records written from the redo log buffers.
The online redo log file that is being written by the log writer (LGWR) process is called the current online redo log file.
Log switchover occurs when the database stops writing to an online redo log file and begins writing to another.
The switchover occurs when the current online redo log file is full and must continue to write.
You can also force log switching manually.
The log writer writes the online redo log file in a circular fashion.
When a log writer fills the last available online redo log file, the process then writes to the first one.
Used so repeatedly.

The online redo log file contains a redo record. Redo records consist of a set of change vectors,
Each vector describes a change to a block of data.
For example, an update to the SAL in the EMP table will generate a redo record,
Describes changes to the data segment block of the table, the rollback segment data block, and the transaction table for the rollback segment.
The redo log records all metadata related to the change, including the following:
Changed SCN and timestamp
Transaction ID of the change transaction
SCN and timestamp when the transaction was committed (if it was committed)
Action type of changes made
The name and type of the data segment being modified
SELECT * from V$logfile;
SELECT * from V$log;
--Manually force log switching
alter system switch logfile;

9, db block data blocks
A logical block of data corresponds to a specific number of bytes of physical disk space, such as 8 KB.
The Oracle database manages the logical storage space in a database data file, as a block of data, also known as an Oracle block or page.
A data block is the smallest storage unit that an Oracle database can use and allocate.

9.1 Data blocks and operating system blocks
At the physical level, the database data stored in the disk file consists of the operating system block.
The operating system block is the smallest unit of data that the operating system can read or write.
The logical separation of data blocks from the operating system block has the following meanings:
The application does not need to determine the physical address of the data on the disk.
Database data can be stored and backed up on multiple physical disks.

9.2 Data Block size default 8K,
The Db_block_size initialization parameter sets the data block size of the database when it is created.
You cannot change the block size of a database unless you re-create the database.
If the size of the data block and the operating system block is different, the chunk size must be an integer multiple of the operating system block size.
SELECT * from V$parameter where lower (name) = ' Db_block_size ';

9.3 Data Block Format
Each block has a format or internal structure that enables the database to track the data and free space in the block.
9.3.1 Chunk Cost (block)
The Oracle database uses block overhead to manage the block itself. Block overhead cannot be used to store user data.
The block cost contains general information about the block, including the disk address and segment type.
For a transaction management block, its size contains both active and historical transaction information.
Some parts of the block overhead are fixed in size, but the total size is variable.
On average, the block overhead totals around 84 to 107 bytes.

9.3.2 Line Format
The row data portion of a block contains the actual data, such as a table row or index key entry.
Just as each block has an internal format, each row also has a row format that enables the database to track the data in the row.
The Oracle database stores rows in a variable-length record format.
Rows are contained in one or more row fragments. Each row fragment has a wardrobe and column data.

Outfit
The outfit takes at least 3 bytes.
The Oracle database uses a costume to manage the row fragments stored in the block.
The data block typically contains only one row fragment per row.
If the entire row can be inserted into a block of data, the Oracle database stores the row as a row fragment.
However, if all row data cannot be inserted into a single block, or an update causes an existing row to not fit in the original block,
The database stores the row as multiple row fragments.
The Oracle database can store only 255 of the columns in a single row fragment.
Therefore, if you insert a row in a table with 1000 columns, the database creates 4 row fragments, usually linking multiple blocks.

Column data
The column data section after the row header stores the actual data in the row.
Row fragments typically store columns in the order in which they are listed in the CREATE TABLE statement, but this order is not always guaranteed.
For example, the long type column is always at the end.
For each column in a row fragment, the Oracle database stores column lengths and column data independently. The space required depends on the data type.
If the data type of a column is variable-length, the space required to hold a value may grow and shrink when its data is updated.
Each row has a slot in the row directory of the header of the data block. The slot points to the beginning of the line.

9.3.3 ROWID format
An Oracle database uniquely identifies a row using a rowid.
Internally, ROWID is a structure that holds the information needed to access rows in a database.
A rowid is not physically stored in the database,
Instead, it derives from the files and blocks that store the data.
This type of ROWID uses a 64-digit encoding of the physical address of each row.
The encoded characters are A-Z, A-Z, 0-9, +, and/.
abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz0123456789+/
An extension ROWID is displayed in a four-segment format, OOOOOOFFFBBBBBBRRR,
This format is divided into the following components
The Oooooo data Object number identifies the segment. Each segment in the database is assigned a data object number. Schema objects in the same paragraph have the same data object number.
FFF tablespace Relative data file number identifying the data file that contains the row
The BBBBBB data block number identifies the block that contains the row. Block numbers are relative to their data files, not their table spaces. Therefore, two rows with the same block number can reside in different data files in the same tablespace.
The RRR line number identifies the row in the block.
Select rowid,e.* from Scott.emp e;
Select rowid,e.* from Scott.dept e;

SELECT * from dba_extents where segment_name= ' DEPT ';
SELECT * from dba_extents where segment_name= ' EMP ';
cx= 2*64+23=151 =144
CH =2*64+7=135 =128

Select
Dbms_rowid.rowid_object (ROWID) obj#,
DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) rfile#,
Dbms_rowid.rowid_block_number (ROWID) block#,
Dbms_rowid.rowid_row_number (ROWID) row#,
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO (rowID, ' SCOTT ', ' EMP ') file#
From Scott.emp;

9.4 Spatial management of data blocks
As the database fills the data blocks from the bottom up, the free space between the row data and the block size is gradually reduced.
During an update, when a trailing null value is changed to a non-null value, the available space becomes less.
The database manages the free space in the data block to optimize performance and avoid wasted space.
Percentage of free space in the 9.4.1 data block
The Pctfree storage parameter sets the minimum percentage of free space that is reserved for updating existing rows.
Pctfree is important for preventing row migrations and avoiding wasted space.
The database continually adds rows to the block, causing the row data to grow upward in the direction of the head, while the size itself continues to grow downward in the row data direction.
Set the Pctfree storage parameter to ensure that at least 10% of the data blocks are idle.

Insert statement and update (from small to large) statement will occupy space;
The DELETE statement and the update (from large to small) statement will free up space.
Freed space will result in disk fragmentation.
The Oracle database automatically and transparently merges the available space in the data block only if the following conditions are met.
The INSERT or UPDATE statement attempts to use a block that contains enough free space to accommodate the new row fragment.
The free space has been fragmented so that the row fragment cannot be inserted into a contiguous region in the block.

CREATE TABLE EMP
(
EMPNO Number (4) is not NULL,
Ename VARCHAR2 (10),
JOB VARCHAR2 (9),
MGR Number (4),
HireDate DATE,
SAL number (7,2),
COMM number (7,2),
DEPTNO Number (2)
)
Tablespace USERS
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64K
Next 1M
Minextents 1
Maxextents Unlimited
);

10, Extend expanded exhibition area
An extension is a set of logically contiguous blocks of data that are assigned to store specific types of information.
A extend is generally composed of 8 (128/1024) DB blocks.
An expansion area is a logical unit of database storage allocation, consisting of multiple contiguous blocks of data.
Blocks of data in an extent are logically contiguous, but may be physically distributed.

10.1 Assigning an expansion area
By default, when a data segment is created, the database allocates an initial extents for the data segment.
An extension area is always included in a data file.
If the initial extents are full and more space is required, the database automatically assigns a delta extents to the segment.
An incremental expansion area is a subsequent extension that is created for the segment.
The allocation algorithm depends on whether the table space is managed locally or by the dictionary.
For a local management table space, the database searches a bitmap of a data file to find adjacent blocks of data that are available.
If there is not enough space in the data file, the database is found in a different data file.
The extents of a segment are always in the same table space, but may be in different data files.
New blocks allocated to the extents, the old data is not emptied, only when the Oracle database starts to use the blocks,
will be formatted as needed.

10.2 Releasing the expansion area
When you delete a table or index by using the drop command, the extents of the table or index segment are returned to the table space.
Truncated table truncate TABLE EMP;
Truncate differs from Delete:
Truncate belongs to the DDL language, and delete belongs to the DML language;
Truncate do not remember Redo_log,delete kee Redo_log;
Truncate can only be used to delete full table data, delete can be deleted according to where condition;
Truncate FAST, delete can only be used for a small amount (100,000 rows) of data deletion;
Truncate data cannot be recovered after deletion, delete can be restored;
Truncate is to reclaim the original extend and redistribute a new extend,delete without affecting the original extend.

10.3 Storage parameters for the expansion area
Each segment is defined by the storage parameters represented by the extents.
These parameters control how the Oracle database allocates free space for a segment.
Tablespace USERS
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64K
Next 1M
Minextents 1
Maxextents Unlimited
);

11. Segment Section
A segment is a set of extents that is assigned to a particular database object, such as a table.
The data for the EMP table is stored in its own data segment, and each index of the table is stored in its own index segment.
Each database object that consumes storage space consists of a single segment.
Each segment belongs to and belongs to only one table space.
All extents of a segment are stored in the same table space.
In a table space, a segment can include extents in multiple data files.
A single extent will never span multiple data files.

11.1 User Segments
A single data segment in a database is used to store data for a user object, such as tables, indexes.
By default, the database is created with deferred segments, and only the database metadata is updated when tables and indexes are created.
When a user inserts the first row into a table or partition, the database creates tables and index segments, and so on.
Deferred segment creation enables the system to avoid unnecessary use of database resources.
A table segment and its index segments can be in a table space that is not the same.

11.2 Temporary segment
A temporary segment is a sort operation that is created by the system and used for querying.
The temporary segment is in a temporary tablespace and there should be a temporary data file.
During a user session, the Oracle database assigns a temporary segment to the query as needed and deletes it when the query is complete.
In addition to the space management operations that are logged on the temporary segment, changes to the temporary segment are not recorded in the online redo log.
The database creates a temporary segment in the temporary tablespace that is assigned to the user.
The default storage characteristics of a tablespace determine the extent characteristics in a temporary segment.

12. Tablespace Table Space
A table space is a logical storage container for segments.
A segment is a database object that occupies storage space, such as tables and indexes.
At the physical level, the tablespace stores data in one or more data files or temporary files.
The database must have system and sysaux table spaces.
Users need to create at least one tablespace to store application data.

Table Space Type:
Local Management table space (default)
The database uses bitmaps in the tablespace itself to manage the extents.
Therefore, the local management table space requires a portion of the reserved table space for the bitmap.
In a table space, a database can use automatic segment space management (ASSM) or manual segment space Management (MSSM) to manage segments.
Dictionary Management table Space
The database uses a data dictionary to manage the extents.
12.1 Local Management table space
The Local Management table space maintains a bitmap in the data file header to track available space and used space in the data file body.
Each bit corresponds to a set of blocks. When space is allocated or disposed, the Oracle database changes the bitmap value to reflect the new state of the block.
Local Management table Space benefits:
Avoid using a data dictionary to manage extents
If consuming or releasing an extension causes space to be consumed or freed in a data dictionary table or an undo segment, a recursive operation occurs in the dictionary-managed tablespace.
Automatic tracking of contiguous free space
In this way, the database eliminates the need to merge idle extents.
Automatically determine the size of the local management extents
All extents in the local management table space can have the same size and override the object storage options.
Oracle strongly recommends that you use automatic segment space management for local management table spaces.
By default, the Oracle database sets all newly created user table spaces to the local management table space.
The Dictionary management table space is obsolete and is no longer used.

12.2 system table Space
The system tablespace is a necessary administrative table space that is included when the database is created.
The Oracle database uses system to manage the database.
The system table space includes the following information, all owned by the SYS user
Data dictionary
Tables and views that contain database management information
Compiled storage objects, such as triggers, procedures, and packages
By default, the Oracle database sets all newly created user table spaces to the local management table space.
You cannot create a dictionary management tablespace (this is obsolete) in a database where system manages table spaces locally.

12.3 Sysaux Table Space
The Sysaux table space is a secondary tablespace for the system table space.
The Sysaux table space provides a centralized location for database metadata that does not reside in the system tablespace.
It reduces the number of table spaces created by default in the seed database and user-defined database.

12.4 UNDOTBS1 Table Space
An undo tablespace is a local management table space that is reserved for system-managed undo data.
Similar to other persistent table spaces, the undo tablespace also contains data files.
The undo blocks in these files are grouped into extents.
The newly installed Oracle database automatically creates an undo tablespace.

The undo retention period is the minimum amount of time that an Oracle database tries to keep before overwriting the old undo data.
It is important to undo a reservation because a long-running query might require an old block of data before mirroring to provide read consistency.
In addition, some Oracle flashback capabilities may also depend on the availability of revoked data.
You should keep the old undo data as long as possible.
After a transaction commits, undoing the data is no longer required for rollback or recovery transactions.
If the undo table space has enough space for the new transaction, the database may retain the old undo data.
When the free space is low, the database begins overwriting the old undo data for the committed transaction.

12.5 Temp temporary table space
The temporal tablespace contains only transient data that exists during the session. A persistent mode object cannot reside in a temporary table space.
The database temporary tablespace data is stored in a temporary file.
Temporary tablespace can increase the concurrency of multiple sort operations that do not fit in memory.
These table spaces also improve the efficiency of space management in the sequencing process.

Oracle_ Advanced Features (4) Database storage structure