[Oracle19C 数据库管理] 管理存储与表空间
存储概览
存储的架构
Control File：储存了数据物理存储的信息。存在多个副本来避免单点故障。没有控制文件，数据库无法打开。
DATA File： 存储用户与应用的信息，以及元数据与数据字典。
Online Redo Log Files: 用于数据库实例恢复。数据库crash的时候，可以使用redo log来恢复数据库。
Initialization Parameter File: 初始化参数文件，定义了实例启动的选项。
Password File: 允许用户使用SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM,SYSTEM 角色来进行远程登陆， 并执行管理工作。
Backup File：备份文件用于恢复数据库，磁盘故障或用户错误造成的数据文件损坏，可以使用backup file恢复数据库。
Archived Redo log file: 存储过往的redo log,用于数据恢复。
Trace File: 服务器和后台进程会关联相应的Trace跟踪文件。如果进程遇到内部错误，进程抛出错误到Trace跟踪文件。
Alert Log File: Alert告警文件中包含了一些特殊的Trace文件条目,按照时间编排的信息或者错误。
DDL Log File: 当初始化参数ENABLE_DDL_LOGGING为TRUE时， 进行DDL操作的记录。
存储的逻辑与物理结构
TableSpace:数据库逻辑上被分为多个表空间。表空间逻辑上组织了所有的段结构来简化管理工作。表空间将逻辑数据与物理存储结合起来。
Data File:一个或者多个数据文件属于表空间，他们用于存储表空间的所有段数据。
Data Block： Oracle存储数据到数据块中，数据块的大小是2k, 4k, 8k, 16k, 32k。标准的是8k，每个表空间创建的时候可以指定数据块大小。
Extent： 数据区， 连续的数据块构成了数据区，数据区中的数据块逻辑上连续，物理上可能存在于不同的磁盘(例如条带化的raid0)。
Segment: 数据库对象的逻辑存储叫做段，一个段是一组数据区Extent的合集，数据段构成了一个数据库对象。
- 数据段： 表的数据存储在段结构下的数据区中。分区表的每个分区都是一个段。 Oracle默认使用延时创建， 创建表的时候不会立即创建段，直到插入数据时才会真正创建段结构。
- 索引段： 每个索引都有一个索引段来存储她的信息。对于分区索引，每个分区都是一个索引段。
- Undo段： Oracle为每个实例创建一个UNDO表空间。这个表空间中有多个UNDO段来临时存储UNDO信息，Undo段用来保证读取一致性，以及database recovery和ROLLBACK都需要UNDO段。
- 临时段： 临时段Temporary Segment是Oracle创建，用来为SQL语句执行的时候提供临时存储空间。SQL执行完毕后，会将占用的临时段的区交还给Oracle。
- 不是段结构的数据库对象： 视图、Package包、触发器都不是段结构，他们不会像段结构一样被分配磁盘。他们会以记录的形式存储在数据库的元数据Metadata段中。
Oracle数据库动态的分配空间，当一个段中的区用完后，oracle会为他增加额外的区。一个段中的区可能不会连续的存储在一个硬盘上，它们可能存储在不同的磁盘上。
段、区、块
数据段段存储在一个表空间中。
数据段是数据区Extent的集合。
数据区是数据/UNDO块的集合。
数据/UNDO块映射到磁盘的物理块上。
一个段包含一个或者多个区。一个区由多个连续的数据块组成，一个区只能存储在一个数据文件中。 数据块是数据库的最小IO单元。
数据库请求数据块时，OS会将数据库映射到磁盘块上。用户不需要知道数据库文件的物理地址，数据文件可以条带或者镜像存储在不同磁盘上。
表空间与数据文件
左边是small file表空间，创建一个或者多个文件来存储表空间中的所有逻辑对象。表空间采用8k的数据块，一个区包含了8个数据块。
右边是big file表空间，一个表空间只有一个文件，如果数据块是32k时，最大128T，如果8K时，最大32G。
多租户数据库中的默认表空间
-
System表空间： 存储了数据字典以及关于数据库管理工作的表。所有的数据都存储在SYS schema中。只能被SYS用户或者其他被授予特权的用户访问。在CDB$ROOT CONTAINER中，他存储了Oracle提供的元数据。 在PDB Container中，存储了用户的元数据。
-
SYSAUX表空间： SYSAUX是SYSTEM的辅助表空间，减少SYSTEM的负载。ADR报表就存储在SYSAUX表空间。
-
TEMP表空间： Temp表空间存储的数据库对象的生命周期仅限于会话。Temp表空间中的对象存储在临时文件中。当执行SQL的时候会使用临时表空间，例如进行数据排序或者创建索引的时候。就像每个用户都可以分配默认数据表空间，也可以为每个用户分配默认临时表空间。
-
UNDO表空间： 但实例CDB中，CDB$ROOT中只有一个活动的UNDO表空间。可选的，建议在每个PDB中使用一个本地UNDO表空间。
-
USERS表空间： 用来存储用户对象或者数据。当没有为用户指定默认表空间时，USERS就是用户存储数据的默认表空间。USERS表空间在创建CDB$ROOT的时候被默认创建。
SYSTEM和SYSAUX表空间创建时，默认是small file表空间，可以创建Big file表空间。他们都不能置于Read Only。 数据库开启状态下，SYSTEM表空间必须是ONLINE的。SYSAUX可以Offline来进行表空间恢复。
数据如何被存储的
一个表空间是段的集合。
一个表是段结构，它包含了多个行，行数据存储在数据块中，可能是一个数据块，当容量不够时，一行数据存储在多个数据块中。
逻辑上连续的数据库构成了数据区。
数据块的结构
数据块包含了数据块头部、行数据、空闲空间。
数据块头部包括了：
- 段的类型(表或索引等)
- 数据块地址
- 表目录
- 行目录
- 事务槽： 每个23 bytes， 对行进行修改时需要用到事物槽。
数据块头部增大时，会向下增长。
行数据增长将会从下向上增长。
可用空间在数据块的中间部分，数据块头部和数据的增长将会占用可用空间。
延时段创建 Deferred Segment Creation
默认情况下，初始化参数DEFERRED_SEGMENT_CREATION=TRUE。
创建段类型的数据库对象的时候(例如数据表)，不会立即创建段结构，而是先在数据字典中保存定义，直到DML的时候，才会创建。
创建一个应用的时候可能会创建上千个表，其中大部分可能不会使用，延时段创建的特性节省了磁盘空间和部署的时间。
可以通过修改初始化参数DEFERRED_SEGMENT_CREATION来调整延时段创建。可以ALTER SYSTEM也可以ALTER SESSION来修改此初始化参数。
也可以在创建表的时候，通过SEGMENT CREATION IMMEDIATE | DEFERRED(默认值) 来设置延时段创建的特性。子句的优先级比初始化参数高。
表的创建只是在数据字典添加了对表的定义
SQL> CREATE TABLE TEST1 (ID NUMBER)
2 SEGMENT CREATION DEFERRED;
Table created.
查询这个表的区，并没有创建
SQL> SELECT * FROM USER_EXTENTS WHERE SEGMENT_NAME='TEST001';
no rows selected
插入数据时真正创建段。
SQL> INSERT INTO TEST1 VALUES(1);
在查询就能查到段结构的区EXTENT了。
SQL> SELECT * FROM USER_EXTENTS WHERE SEGMENT_NAME='TEST1';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------------------------------
TEST1 TABLE SYSAUX 0 65536 8
监控表空间的用量
MMON后台进程在表空间达到85%的时候触发警告信息Warning。到97%的时候，触发关键告警信息Critical Alert。当使用量问题处理后，这些告警信息会被清除掉。
Oracle服务器在进行定期空间管理操作的时候跟踪磁盘的用量，这些信息由MMON后台进程进行汇总。到达警戒线的时候，Alert告警信息将会被处罚。
表空间置于READ ONLY或者OFFLINE的时候，不会触发告警。
临时表空间，警戒线对应到所有Session的当前用量。
UNDO表空间，当一个区Extent不在存有active或者未过期的undo时，这个区可以被重新使用。所以UNDO表空间将保存active或者未过期的UNDO的区用来计算使用量。
创建表空间
可以使用CREATE TABLESPACE创建表空间。
三种表空间类型：
- 永久表空间 保存了永久存储的schema对象。
- UNDO表空间 在自动UNDO管理模式下，UNDO表空间用来存储UNDO数据。
- 临时表空间 存储基于session的临时数据。
数据库必须置于Open状态下才能够创建表空间。需要有CREATE TABLESPACE权限。创建SYSAUX表空间，需要SYSDBA系统权限。
CREATE TABLESPACE的子句
-
DATAFILE | TEMPFILE 指定文件的位置，文件名以及初始化大小。
-
ONLINE | OFFLINE 指定表空间在创建后是否立即可用或者不可用。
-
BLOCKSIZE 指定表空间数据块大小。
-
EXTENT MANAGEMENT 指定表空间如何管理区EXTENT， 以及在哪里储存分配的和未分配的区的元数据。
AUTOALLOCATE: 表空间中任何对象的区的大小由系统管理，用户无法指定EXTENT SIZE。对于临时表空间，不能指定AUTOALLOCATE。
UNIFORM: 表空间中的任何对象的区的大小都是一致的，手工指定区的大小。默认是1MB。不能为UNOD表空间指定UNIFORM。
不指定AUTOALLOCATE 或者 UNIFORM, 临时表空间是UNIFORM， 其他类型的表空间默认是AUTOALLOCATE。
EXTENT MANAGEMENT还要制定在哪里存放分配的和未分配的区的元数据。LOCAL=在表空间中存储。 DICTIONARY=在数据字典中存储。 -
LOGGING 指定默认的日志属性，包括： 表、索引、物化视图、物化视图日志、分区表。它可以控制某种DML操作是否记录到REDO LOG中（LOGGING | NOLOGGING）。这个子句不能为临时表空间或者UNDO表空间指定。可以使用FORCE LOGGING来让表空间进入强制日志模式，所有更改都会被记录，这个子句覆盖NOLOGGING。
-
SEGMENT SPACE MANAGEMENT: 指定Oracle在段中使用MANUAL或者AUTO来跟踪使用或空闲空间。
AUTO: ORACLE使用位图bitmaps来管理段的空闲空间。位图描述了段中每个数据块的状态，以及每个数据块的空闲空间用来插入数据。这种形式的空间管理称作自动段空间管理AUTOMATIC SEGMENT SPACE MANAGEMENT ASSM。
MANUAL: 使用free list来管理段的空闲空间。Free list是一个数据块的列表，这些数据库有空闲空间可以添加数据。
CDB中创建永久表空间
在非CDB数据库中，所有的表空间属于一个数据库。
在CDB数据库中，有一组表空间属于CDBROOT，每个PDB有自己的表空间。
多租户架构中，需要在对应的container中创建表空间。虽然不是强制，但建议将PDB的表空间都放在一个单独的文件夹中。
CREATE TABLESPACE tbs_cdb_users
DATAFILE '/u01/app/oracle/oradata/TESTCDB/tbs_cdb_users.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
创建临时表空间
CREATE TEMPORARY TABLESPACE TEMP_DEMO
TEMPFILE '/u01/app/oracle/oradata/TESTCDB/temp_demo01.dbf' SIZE 100M AUTOEXTEND ON；
定义默认永久表空间
-
为CDB设置默认永久表空间
更改CDBROOT的默认永久表空间，需要具有ALTER DATABASE权限的用户来操作。
ALTER DATABASE DEFAULT TABLESPACE tbs_cdb_users; -
为PDB设置默认永久表空间
修改PDB的默认永久表空间，需要连接到PDB后，使用ALTER PLUGGABLE DATABASE或者ALTER DATABASE来操作，允许ALTER DATABASE是为了向后兼容性。
conn sys/oracle@pdb1 as sysdba
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE tbs_pdb1_users;
定义默认临时表空间
在CDBROOT级别设置CDB级别的默认临时表空间。可能有多个临时表空间，但只能有一个是默认的。
每个CDB或者PDB，只能有一个默认临时表空间或者表空间组。
需要进入PDB，为其设置默认临时表空间。
-
为CDB设置临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE CDB_TEMP1; -
为PDB设置默认临时表空间
conn system/oracle@pdb1 as sysdba
ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE PDB1_TEMP1;
修改和删除表空间
ALTER TABLESPACE可以设置ONLINE, OFFLINE。添加datafile或者tempfile到表空间中，或者让表空间只读。
表空间的三种状态：
- READ WRITE
- READ ONLY
- OFFLINE(NORMAL, IMMEDIATE, TEMPORARY)
表空间置于READ ONLY的时候，当前的transaction可以正常完成，不会接受新的DML。
SYSTEM和SYSAUX不能被设置为READ ONLY。SYSTEM表空间不能设置为OFFLINE。
表空间设置为OFFLINE以后将临时无法使用。
将表空间置于OFFLINE，可以使用以下参数：
- NORMAL: 确保所有的数据永久存储后，再将表空间OFFLINE。
- TEMPORARY
- IMMEDIATE: ORACLE数据库不做任何的checkpoint，就将数据库OFFLINE。将数据库ONLINE时需要进行恢复操作。不能在NOARCHIVELOG模式的数据库上进行IMMEDIATE操作。
增加表空间的容量的方法：
- 添加额外的数据文件。
- 增大数据文件的容量。
向表空间添加数据文件
ALTER TABLESPACE TBS1
ADD DATAFILE '/u01/app/oracle/oradata/TESTCDB/pdb1tbs1_2.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 100K MAXSIZE 2G
TBS1是表空间名。初始化大小100M， 自动扩展空间，每次100K，文件最大2G。
修改数据文件的容量
ALTER DATABASE
DATAFILE '/u01/app/oracle/oradata/TESTCDB/pdb1/tbs1_1.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
修改数据文件自动扩容，每次100M，最大32G。
ALTER DATABASE
DATAFILE '/u01/app/oracle/oradata/TESTCDB/pdb1/tbs1_1.dbf' RESIZE 500M;
修改数据文件容量为500M。
使用DROP TABLESPACE删除表空间，也可以同时删除他的文件。
删除表空间时，CONTROL FILE中的文件指向也将删除掉。
使用OMF(ORACLE MANAGED FILES),删除表空降会将底层的文件同时在操作系统删除。
如果不是使用的OMF，需要手动的去操作系统删除文件。
当表空间中的段还有没有commit的transcation时，无法删除表空间。
当表空间是默认表空间时，无法进行删除，需要先使用ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE进行转移。
DROP TABLESPACE 表空间名 -> 删除表空间，但不删除文件。
DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES -> 删除表空间，也删除对应的物理文件。
查看表空间的信息
- 表空间信息： CDB_TABLESPACES, DBA_TABLESPACES, V$TABLESPACE
- 数据文件信息： CDB_DATA_FILES, DBA_DATA_FILES, V$DATAFILE
- 临时文件信息： CDB_TEMP_FILES, DBA_TEMP_FILES, V$TEMPFILE
- 表空间的表： ALL_TABLES.
- 表空间的索引： ALL_INDEXES.
例： 查看DBA_TABLESPACES中PDB1中的表空间信息
SQL> select tablespace_name, status, contents, allocation_type, bigfile,
extent_management, segment_space_management from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS ALLOCATIO BIG EXTENT_MAN SEGMEN
-------------------- --------- --------------------- --------- --- ---------- ------
SYSTEM ONLINE PERMANENT SYSTEM NO LOCAL MANUAL
SYSAUX ONLINE PERMANENT SYSTEM NO LOCAL AUTO
UNDOTBS1 ONLINE UNDO SYSTEM NO LOCAL MANUAL
TEMP ONLINE TEMPORARY UNIFORM NO LOCAL MANUAL
USERS ONLINE PERMANENT SYSTEM NO LOCAL AUTO
TBS_CDB_USERS ONLINE PERMANENT SYSTEM NO LOCAL AUTO
CDB_TEMP1 ONLINE TEMPORARY UNIFORM NO LOCAL MANUAL
7 rows selected.
ALLOCATION_TYPE=SYSTEM， 区大小由系统管理，无法指定区大小。ALLOCATION_TYPE=UNIFORM， 用户可以指定区大小。
EXTENT_MANAGE=LOCAL 本地管理表空间，不使用数据字典。
SEGMENT_SPACE_MANAGEMENT=AUTO 位图跟踪数据块空闲空间。 MANUAL为FREE LIST跟踪数据块的空闲空间。
例： 通过DBA_SEGMENTS查看HR拥有的表空间中的段
SQL> select segment_name, segment_type, tablespace_name, blocks
2 from dba_segments where OWNER='HR';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS
------------------------------ -------------------- -------------------- ----------
LOCATIONS TABLE SYSAUX 8
DEPARTMENTS TABLE SYSAUX 8
EMPLOYEES TABLE SYSAUX 8
JOB_HISTORY TABLE SYSAUX 8
JOBS TABLE SYSAUX 8
TEST1 TABLE SYSAUX 8
REGIONS TABLE SYSAUX 8
LOC_ID_PK INDEX SYSAUX 8
EMP_EMAIL_UK INDEX SYSAUX 8
EMP_EMP_ID_PK INDEX SYSAUX 8
JHIST_EMP_ID_ST_DATE_PK INDEX SYSAUX 8
例： 查看V$TABLESPACE
SQL> SELECT TS#, NAME, BIGFILE, CON_ID FROM V$TABLESPACE
2 ORDER BY CON_ID, TS#;
TS# NAME BIG CON_ID
---------- ------------------------------ --- ----------
0 SYSTEM NO 1
1 SYSAUX NO 1
2 UNDOTBS1 NO 1
3 TEMP NO 1
4 USERS NO 1
6 TBS_CDB_USERS NO 1
8 CDB_TEMP1 NO 1
0 SYSTEM NO 2
1 SYSAUX NO 2
2 UNDOTBS1 NO 2
3 TEMP NO 2
0 SYSTEM NO 3
1 SYSAUX NO 3
2 UNDOTBS1 NO 3
3 TEMP NO 3
5 USERS NO 3
6 TBS1 NO 3
7 PDB1_TEMP NO 3
8 TBS_PDB1_USERS NO 3
11 TEMP_DEMO NO 3
12 PDB1_TEMP1 NO 3
13 TEST_DATA NO 3
例： 查看DBA_DATA_FILES关于表空间的文件信息。
SQL> SELECT FILE_NAME, ONLINE_STATUS, TABLESPACE_NAME,BYTES/1000000 "SIZE(MB)",
2 USER_BYTES/1000000 "AVAIL(MB)", AUTOEXTENSIBLE, INCREMENT_BY
3 FROM DBA_DATA_FILES;
FILE_NAME ONLINE_ TABLESPACE_NAME SIZE(MB) AVAIL(MB) AUT INCREMENT_BY
------------------------------------------------------------ ------- -------------------- ---------- ---------- --- ------------
/u01/app/oracle/oradata/TESTCDB/pdb1/system01.dbf SYSTEM SYSTEM 293.60128 292.552704 YES 1280
/u01/app/oracle/oradata/TESTCDB/pdb1/sysaux01.dbf ONLINE SYSAUX 356.51584 355.467264 YES 1280
/u01/app/oracle/oradata/TESTCDB/pdb1/undotbs01.dbf ONLINE UNDOTBS1 104.8576 103.809024 YES 640
/u01/app/oracle/oradata/TESTCDB/pdb1/users01.dbf ONLINE USERS 5.24288 4.194304 YES 160
/u01/app/oracle/oradata/TESTCDB/pdb1/tbs1_1.dbf ONLINE TBS1 104.8576 103.809024 NO 0
/u01/app/oracle/oradata/TESTCDB/pdb1/tbs_pdb1_users.dbf ONLINE TBS_PDB1_USERS 104.8576 103.809024 NO 0
/u01/app/oracle/oradata/TESTCDB/pdb1/omf/TESTCDB/F1AEED99C08 ONLINE TEST_DATA 104.8576 103.809024 YES 12800
F7AABE0538488A8C0A2F6/datafile/o1_mf_test_dat_kwtnj9ww_.dbf
BYTES: 表空间文件的大小。
USER_BYTES： 表空间文件中可供用户使用的空间。
AUTOEXTENSIBLE: 是否能够自动增长
INCREMENT_BY: 自动增长的大小
例： 通过ALL_TABLES查看HR Schema拥有的表以及占用的表空间。
SQL> select table_name, tablespace_name, owner from all_tables where owner='HR';
TABLE_NAME TABLESPACE_NAME OWNER
-------------------- -------------------- --------------------
DEPARTMENTS SYSAUX HR
EMPLOYEES SYSAUX HR
JOBS SYSAUX HR
JOB_HISTORY SYSAUX HR
LOCATIONS SYSAUX HR
REGIONS SYSAUX HR
TEST001 SYSAUX HR
TEST1 SYSAUX HR
TEST_PDB SYSAUX HR
COUNTRIES HR
例： 通过ALL_INDEXES查看hr用户所拥有的索引
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, OWNER
2 FROM ALL_INDEXES
3 WHERE OWNER='HR';
INDEX_NAME INDEX_TYPE TABLE_NAME OWNER
------------------------------ --------------------------- -------------------- --------------------
REG_ID_PK NORMAL REGIONS HR
COUNTRY_C_ID_PK IOT - TOP COUNTRIES HR
LOC_ID_PK NORMAL LOCATIONS HR
DEPT_ID_PK NORMAL DEPARTMENTS HR
JOB_ID_PK NORMAL JOBS HR
EMP_EMAIL_UK NORMAL EMPLOYEES HR
EMP_EMP_ID_PK NORMAL EMPLOYEES HR
JHIST_EMP_ID_ST_DATE_PK NORMAL JOB_HISTORY HR
EMP_DEPARTMENT_IX NORMAL EMPLOYEES HR
EMP_JOB_IX NORMAL EMPLOYEES HR
EMP_MANAGER_IX NORMAL EMPLOYEES HR
例：通过CDB_USERS查看用户的默认表空间和临时表空间。
SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CON_ID
FROM CDB_USERS;
USERNAME DEFAULT_TA TEMPORARY_ CON_ID
---------- ---------- ---------- ----------
SYS SYSTEM TEMP 3
SYSTEM SYSTEM TEMP 3
XS$NULL SYSTEM TEMP 3
LBACSYS SYSTEM TEMP 3
OUTLN SYSTEM TEMP 3
DBSNMP SYSAUX TEMP 3
APPQOSSYS SYSAUX TEMP 3
GSMCATUSER SYSAUX TEMP 3
MDDATA SYSAUX TEMP 3
SYSBACKUP SYSAUX TEMP 3
DBSFWUSER SYSAUX TEMP 3
例： 通过DATABASE_PROPERTIES查看系统的默认永久表空间和默认临时表空间。
select property_name, property_value from database_properties
2 where property_name like 'DEFAULT%TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_TEMP_TABLESPACE TEMP
例： 查看表空间的使用量
select tablespace_name, sum(bytes)/1024/1024 from dba_segments
group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
SYSTEM 269.9375
SYSAUX 320.25
UNDOTBS1 0
例： 通过dba_extents查看某个表空间的磁盘使用。
SQL> select owner, segment_name, tablespace_name, sum(bytes)/1024/1024 as "Size(M)"
from dba_extents where owner='PDBADMIN' group by owner, segment_name, tablespace_name;
OWNER SEGMENT_NA TABLESPACE Size(M)
---------- ---------- ---------- ----------
PDBADMIN EMP TEST001 12.0625
在SQLDEVELOPER中可以看到，dba_extents视图中显示了每个段所包含的区以及区的空间使用情况，每个区大小是8个8k的数据块。
实施OMF(ORACLE MANAGED FILES)
OMF消除了用户管理数据文件的必要。数据库可以包含OMF的数据也可以包含由用户管理的数据文件。
为以下初始化参数设定值：
- DB_CREATE_FILE_DEST: 为datafile和tempfile指定默认管理目录。
- DB_CREATE_ONLINE_LOG_DEST_n： 指定REDO LOG和CONTROL FILE的目录。
- DB_RECOVERY_FILE_DEST: 为快速恢复区指定目录。
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/TESTCDB/pdb1/omf'
CREATE TABLESPACE TEST_DATA;
OMF的文件初始大小100M，并会自动增长(SYSTEM, SYSAUX同样如此)。
扩大数据库
- 创建一个新的表空间
- 向现有的smallfile表空间中添加文件。
- 增大数据文件的大小。
- 让数据文件动态增长空间。
移动和重命名在线数据文件
数据库是开启状态并且能够访问数据文件时，可以将数据文件在线进行重命名或者移动位置。数据移动过程中，DDL和DML都可以正常执行。
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/TESTCDB/pdb1/users01.dbf'
TO '/home/oracle/users01.dbf'
以上语句执行后，/u01/app/oracle/oradata/TESTCDB/pdb1/users01.dbf将会移动到/home/oracle/users01.dbf，然后从旧的位置删除文件。
可以使用KEEP子句在旧的位置保留数据文件。
在SQLDeveloper中管理表空间
点击View-DBA打开DBA管理面板。
添加一个到PDB1的连接，用户为PDBADMIN，服务名为PDB1(注册在tnsnames.ora中)
Storage-> TableSpace可以图形化查看表空间的信息。
选择TBS_PDB1_USERS表空间，进入DataFiles标签页，点击Action，选择Edit便可以更改数据文件的大小。
选择TBS_PDB1_USERS表空间，进入DataFiles标签页，点击Action，选择Add Datafile便可以向表空间中添加数据文件。
数据空间管理
数据块空间管理
通过Automatic Segment Space Management(位图)， 每个数据块分为四种状态：
- FS1 空闲空间在0-25%
- FS2 空闲空间在25-50%
- FS3 空闲空间在50-75%
- FS4 空闲空间在75-100%
数据块的空闲状态是自动维护的，基于数据块的空闲状态和插入数据行的长度，数据行的插入操作将会分配到空间适合的数据块中，处于“Full”状态的数据块将不会进行插入操作。
上图中，一开始块处于FS3， 进行插入操作后，当他达到PCTFree时，即为Full状态(PCTFREE默认为10%)， 将不会再对这个块进行数据插入操作。当空间回落到FS2时，它将会再次可以插入数据。
行链接(Row Chaining)与行迁移(Row Migration)
有两种情况造成数据过大而无法使用一个数据块进行保存：
-
行链接
当行第一次插入数据块时，如果行的大小超过了数据块的剩余空间，行将会插入到新的数据块中。 Oracle数据库服务器存储了旧块指向一个或者多个新块的链接。
行链接通常发生在插入LONG或者LONG RAW数据类型。 -
行迁移
如果已经存在的行更新后整个行的占用空间变大，数据块无法容纳时，会将整行迁移到另外一个数据块中，在元数据块保留了一个到新数据块的指针。
行链接和行迁移都会造成性能降低，因为IO增多，要访问多个数据块。
Unusable索引
当索引变为Unusable的时候，索引段会被删除，但定义还存在，可以在将来重新创建。
ALTER INDEX <INDEX_NAME> UNUSABLE
ALTER INDEX <INDEX_NAME> REBUILD
SQL> create index idx_jobs_job_title on jobs (job_title);
Index created.
SQL> alter index idx_jobs_job_title unusable;
Index altered.
SQL> alter index idx_jobs_job_title rebuild;
Index altered.
临时表
临时表的数据生命周期仅限于Transaction或者会话。
临时表分为两种类型：
- Global: 临时表的定义可以在所有的会话都能看到，表数据仅限于会话中。表定义存储在磁盘上。
- Private: 表定义仅限于创建它的会话。表定义存储在内存中。
在对临时表进行INSERT或者CREATE TABLE AS SELECT时，会真正创建段。
ROLLBACK操作，表定义仍然存在。
创建Global临时表
CREATE GLOBAL TEMPORARY TABLE <临时表名> (字段定义)
[ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS];
ON COMMIT DELETE ROWS: 面向过程transcation-specified(默认值)， commit后删除数据
ON COMMIT PRESERVE ROWS： 面向会话session-specified， commit后保留数据。
SQL> CREATE GLOBAL TEMPORARY TABLE trans_buffer_area (
2 id number,
3 name varchar2(20));
SQL> INSERT INTO trans_buffer_area values (1, 'l');
1 row created.
SQL> select * from trans_buffer_area;
ID NAME
---------- --------------------
1 l
SQL> commit;
Commit complete.
SQL> select * from trans_buffer_area;
no rows selected
创建Private临时表
CREATE PRIVATE TEMPORARY TABLE <ORA$PTT_临时表名> (字段定义)
[ON COMMIT PRESERVE DEFINITION];
默认情况下， 临时表遇到ROLLBACK或者COMMIT,会被DROP掉。
如果添加上ON COMMIT PRESERVE DEFINITION，则遇到ROLLBACK或者COMMIT会保留表和它的数据。
创建一个PRIVATE临时表并不会提交事务，虽然他是DDL，但他只发生在本地的会话，不影响数据库。
多个会话可以有同样的PRIVATE临时表名，但PRIVATE临时表的定义是在各个会话内部的。
PRIATE临时表的数据和定义，在会话或者Transaction结束后被舍弃。
数据压缩
ORACLE建议对数据库采用压缩技术来降低存储成本。
Oracle服务器可以使用压缩来消除一个数据块中重复的数据。如果数据表有大量冗余信息，使用压缩技术会降低存储的使用，也减少数据库缓冲区的内存占用。压缩对于数据库应用来说时透明的。
数据库压缩只适用于堆表。 COMPRESS关键字压缩数据库。NOCOMPRESS关键字禁用压缩。NOCOMPRESS是默认值。
Basic压缩
Basic table compress适用于direct-path插入数据，压缩比10倍。
direct-path插入操作包括：
- SQL*Loader
- CREATE TABLE AS SELECT
- Parallel Insert
- Insert with Append hint
CREATE TABLE ... COMPRESS BASIC
适合于数据仓库使用。
Advanced压缩
Advanced row compress适用于所有的DML语句，压缩比2-4倍。
CREATE TABLE ... ROW STORE COMPRESS ADVANCED
适合OLTP联机事务操作。
在数据块的开始部分存储存在重复的数据，后续数据引用这些数据，而不消耗空间去做实际的存储。
对于超过255列的表，不能用BASIC和ADVANCED压缩。
不能对BASIC压缩的表删除列，但可以对ADVANCED压缩的表删除列。
向一个启用了压缩的表中的数据块插入数据时，首先使用的未压缩的格式存放。当容量到达PCTFREE时，便会触发压缩。压缩后数据降低到PCTFREE以下，再次插入输入，如果达到PCTFREE，会再次触发压缩。
压缩消除了因为DELETE语句造成的空数据块， 最大化数据块中的连续空闲空间。
使用COMPRESS和ROW STORE COMPRESS BASIC的表， PCTFREE=0来最大化压缩效果。
使用ROW STORE COMPRESS ADVANCED的表，PCTFREE使用PCTFREE=10来最大化压缩效果并同时支持DML语句。
EMP启用了Advanced压缩
SQL> alter table emp row store compress advanced;
Table altered.
当前使用了74256个数据块
SQL> select segment_name, blocks from user_segments where segment_name='EMP';
SEGMENT_NA BLOCKS
---------- ----------
EMP 74256
当前有7002780条记录
SQL> select count(1) from emp;
COUNT(1)
----------
7002780
插入214条记录
SQL> INSERT INTO emp SELECT * FROM hr.employees;
107 rows created.
SQL> INSERT INTO emp SELECT * FROM hr.employees;
107 rows created.
表的记录数增加。
SQL> select count(1) from emp;
COUNT(1)
----------
7002994
使用的块没有增加
SQL> select segment_name, blocks from user_segments where segment_name='EMP';
SEGMENT_NA BLOCKS
---------- ----------
EMP 74256
数据库使用量警报
可以为表空间设置阈值发送告警(CRITICAL, WARNING)， 阈值可以是百分比也可以按照字节数进行设置。
DBMS_SERVER_ALERT Package包含了获取与设置这两个值的方法。当表空间达到阈值时，适当的告警将被触发。
理想的设置应当是Warning告警发生后，在变为Critical前，有足够的时间去解决空间问题，但也足够晚报警来避免空间还不是问题的时候就不断地影响管理员。
设置阈值的语句
切换到表空间所在的PDB
运行一下语句，替换<表空间>为要设置监控的表空间名，这里设置的55报warning， 70报critical。
exec DBMS_SERVER_ALERT.SET_THRESHOLD(metrics_id => dbms_server_alert.tablespace_pct_full, warning_operator => dbms_server_alert.operator_ge, warning_value => '55', critical_operator => dbms_server_alert.operator_ge, critical_value => '70', observation_period => 1, consecutive_occurrences => 1, instance_name => 'TESTCDB', object_type => dbms_server_alert.object_type_tablespace, object_name=> <表空间>)
设置后，可以通过DBA_THRESHOLDS进行查看。
SELECT warning_value, critical_value FROM dba_thresholds WHERE metrics_name='Tablespace Space Usage' and object_name='TEST001';
WARNING_VALUE CRITICAL_VALUE
-------------------- --------------------
55 70
数据增长超过55%后，可以在DBA_OUTSTANDING_ALERTS看到WARNING警告。
每十分钟MMON后台进程进行一次更新。如果没有刷新到警告信息，需要等待一下。
SQL> SELECT object_name, reason FROM dba_outstanding_alerts ;
OBJECT_NAM REASON
---------- ------------------------------------------------------------
TEST001 Tablespace [TEST001@PDB1] is [55 percent] full
数据增长超过70%后，可以在DBA_OUTSTANDING_ALERTS中看到CRITICAL警告。
SQL> SELECT object_name, reason FROM dba_outstanding_alerts ;
OBJECT_NAM REASON
---------- ------------------------------------------------------------
TEST001 Tablespace [TEST001@PDB1] is [75 percent] full
解决空间不足的方法：
- 添加数据文件、扩大数据文件或设置为自动增长来增大表空间。
- 对表空间的对象进行shrink操作。
- 对于auto-extendable数据文件的磁盘进行释放磁盘空间。
- 降低UNDO_RETENTION.
Shrink收缩段来获取空间
收缩段可以在线就地执行，不需要额外的数据库空间。
收缩只支持Auto Segment Space Management管理的段(位图)，不支持Free list管理的段，但以下ASSM管理的段也不能被收缩：
- Cluster的表。
- 包含Long类型字段的表。
- Table with on-commit materialized views.
- Table with Rowid based materialized views.
- IOT Mapping table
- Table with function-based indexes.
段收缩的本质是移动数据来降低高水位线(High Water Make)。
收缩操作有两个阶段，第一个阶段是Compaction，移动数据行到左侧未使用的空间，但不移动高水位线。数据都移动完，第二阶段便开始移动高水位线。
ALTER TABLE <段名> SHRINK SPACE [COMPACT];
如果在收缩过程中有长时间查询的操作，可以先执行ALTER TABLE <段名> SHRINK SPACE COMPACT先完成第一阶段的工作，工作的进度会保存在位图中， 之后在闲时再次执行ALTER TABLE <段名> SHRINK SPACE来完成第二阶段的操作。
收缩后数据块更加连续，可以提升全表扫描的效率和索引的性能。可以提升DML的性能。
提升空间利用率，更多的空间可以被充分使用。
收缩会使数据的Rowid变化，索引数据表的索引也得到重新构建。
降低了发生行迁移的数量。
默认是不允许移动行的，需要打开这个功能
SQL> ALTER TABLE EMPLOYEES ENABLE ROW MOVEMENT;
Table altered.
SQL> ALTER TABLE EMPLOYEES SHRINK SPACE COMPACT;
Table altered.
SQL> ALTER TABLE EMPLOYEES SHRINK SPACE;
Table altered.
Resumable Space Allocation可恢复空间分配
Oracle服务器可以在磁盘空间分配发生错误时，可以暂停当前Transaction而不直接返回错误，处理完空间问题后，再次返回transaction继续进行。
被Resumable Space Allocatioin影响的语句叫做Resumable Statement。
一个Resumable Statement在以下情况下会发生中断：
- 没有空间
- 达到最大的区
- 使用空间超过磁盘配额
暂停的默认最大时间间隔是2小时， 可以暂停多次。
进入Resumable模式，需要以下任一操作：
- RESUMABLE_TIMEOUT初始化参数不为0(单位：秒)
- ALTER SESSION ENABLE RESUMABLE [TIMEOUT XXXX]
SQL> conn pdbadmin/oracle@pdb1
Connected.
SQL> CREATE TABLESPACE TEST001
2 DATAFILE '/u01/app/oracle/oradata/TESTCDB/pdb1/test001.dbf' SIZE 10M;
Tablespace created.
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 300;
Session altered.
SQL> CREATE TABLE EMP (ID NUMBER, NAME CHAR(2000)) TABLESPACE TEST001;
Table created.
SQL> INSERT INTO EMP VALUES (1, 'A');
1 row created.
SQL> INSERT INTO EMP SELECT * FROM EMP;
1 row created.
SQL> INSERT INTO EMP SELECT * FROM EMP;
2 rows created.
省略...
SQL> INSERT INTO EMP SELECT * FROM EMP;
1024 rows created.
SQL> INSERT INTO EMP SELECT * FROM EMP;
INSERT INTO EMP SELECT * FROM EMP;
===此处空间不够，暂停===
另外开一个SESSION，给TEST001表空间增加了一个10M的数据文件.
SQL> alter tablespace test001
2 add datafile '/u01/app/oracle/oradata/TESTCDB/pdb1/test002.dbf' SIZE 10M;
Tablespace altered.
===恢复执行===
2048 rows created.
可以通过USER_RESUMABLE或者DBA_RESUMABLE查看当前阻塞的会话的报错信息。
SQL> select NAME, timeout, sql_text ,error_msg from dba_resumable;
NAME TIMEOUT SQL_TEXT ERROR_MSG
-------------------- ---------- ---------------------------------------------------------------------------------
User PDBADMIN(105), 300 INSERT INTO EMP SELECT * FROM EMP ORA-01653: unable to extend table PDBADMIN.
Session 404, Instance1 EMP by 128 in tablespace TEST001
支持Resumable的操作
- SELECT查询结果超出了临时表空间的大小。
- DML： INSERT, UPDATE, DELETE
- 以下DDL:
- CREATE TABLE AS SELECT
- CREATE INDEX
- ALTER INDEX ... REBUILD
- ALTER TABLE MOVE PARTITION
- ALTER TABLE SPLIT PARTITION
- ALTER INDEX REBUILD PARTITION
- ALTER INDEX SPLIT PARTITION
- CREATE MATERIALIZED VIEW
posted on 2023-01-15 21:43 LeoZhangJing 阅读(1078) 评论(0) 编辑 收藏 举报
