表空间的空间管理算法

存储结构

 逻辑结构              物理结构

database

tablespace       --> datafile

segment

extent

oracle             --> os block

block

表空间的空间管理：

DMT(dictionary management tablespace):

LMT(local management tablespace):

SQL> 
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf

11 rows selected.

SQL> select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         LOCAL
SYSAUX                         LOCAL
UNDOTBS1                       LOCAL
TEMP01                         LOCAL
USERS                          LOCAL

SQL> 

SQL> column FILE_ID default
SQL> select file_id,file_name from dba_data_files;

   FILE_ID FILE_NAME
---------- ------------------------------------------------------------
         7 /u02/oradata/CDB1/users01.dbf
         4 /u02/oradata/CDB1/undotbs01.dbf
         1 /u02/oradata/CDB1/system01.dbf
         3 /u02/oradata/CDB1/sysaux01.dbf
        15 /u02/oradata/CDB1/data01.dbf

SQL> 

SQL> 
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf

11 rows selected.

SQL> select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         LOCAL
SYSAUX                         LOCAL
UNDOTBS1                       LOCAL
TEMP01                         LOCAL
USERS                          LOCAL

SQL> create tablespace data01 datafile '/u02/oradata/CDB1/data01.dbf' size 88k;

Tablespace created.

SQL> 

SQL> column FILE_NAME for a60
SQL> 
SQL> column FILE_ID default
SQL> select file_id,file_name from dba_data_files;

   FILE_ID FILE_NAME
---------- ------------------------------------------------------------
         7 /u02/oradata/CDB1/users01.dbf
         4 /u02/oradata/CDB1/undotbs01.dbf
         1 /u02/oradata/CDB1/system01.dbf
         3 /u02/oradata/CDB1/sysaux01.dbf
        15 /u02/oradata/CDB1/data01.dbf

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
/u02/oradata/CDB1/data01.dbf

12 rows selected.

SQL> !ls -lk /u02/oradata/CDB1/data01.dbf
-rw-r-----. 1 oracle oinstall 98304 Nov 24 14:10 /u02/oradata/CDB1/data01.dbf

SQL> alter system dump datafile 6 block min 1 block max 11;

System altered.

SQL> show user
USER is "SYS"
SQL> show parameter background;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/app/oracle/product/19.3.0
                                                 /dbhome_1/rdbms/log
SQL

段（segment）空间管理模式：

段所拥有的数据块中空闲块如何管理

SQL> 
SQL> select tablespace_name,segment_space_management from dba_tablespaces;

TABLESPACE_NAME                SEGMEN
------------------------------ ------
SYSTEM                         MANUAL
SYSAUX                         AUTO
UNDOTBS1                       MANUAL
TEMP01                         MANUAL
USERS                          AUTO
DATA01                         AUTO

6 rows selected.

SQL> 

段内空闲空间的手工管理：使用空闲列表管理段内的空闲块（可以做insert的块）。空闲列表记录在段头，空闲列表指向段内空闲块的地址。

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB1;

Session altered.

SQL> drop table scott.t01 purge;

Table dropped.

SQL> 
SQL> create table scott.t01 (x int,y varchar2(20)) tablespace system;

Table created.

SQL> 

查询哪些块属于t01段：

select file_id,block_id,blocks from dba_extents where segment_name='T01';

 查询空闲列表的数量：

查看t01的段头块：

SQL> select header_file,header_block from dba_segments where segment_name='T01';

将segment header 的数据转储到用户进程的跟踪文件：

alter system dump datafile 1 block 94664

 在表中插入数据之后，重新转储段头块