存储结构

 逻辑结构              物理结构

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.dbf11 rows selected.SQL> select tablespace_name,extent_management from dba_tablespaces;TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         LOCAL
SYSAUX                         LOCAL
UNDOTBS1                       LOCAL
TEMP01                         LOCAL
USERS                          LOCALSQL> 

SQL> column FILE_ID default
SQL> select file_id,file_name from dba_data_files;FILE_ID FILE_NAME
---------- ------------------------------------------------------------7 /u02/oradata/CDB1/users01.dbf4 /u02/oradata/CDB1/undotbs01.dbf1 /u02/oradata/CDB1/system01.dbf3 /u02/oradata/CDB1/sysaux01.dbf15 /u02/oradata/CDB1/data01.dbfSQL> 

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.dbf11 rows selected.SQL> select tablespace_name,extent_management from dba_tablespaces;TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         LOCAL
SYSAUX                         LOCAL
UNDOTBS1                       LOCAL
TEMP01                         LOCAL
USERS                          LOCALSQL> 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.dbf4 /u02/oradata/CDB1/undotbs01.dbf1 /u02/oradata/CDB1/system01.dbf3 /u02/oradata/CDB1/sysaux01.dbf15 /u02/oradata/CDB1/data01.dbfSQL> 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.dbf12 rows selected.SQL> !ls -lk /u02/oradata/CDB1/data01.dbf
-rw-r-----. 1 oracle oinstall 98304 Nov 24 14:10 /u02/oradata/CDB1/data01.dbfSQL> 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                         AUTO6 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

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