OCA/OCP Oracle 数据库12c考试指南读书笔记：第15章: Oracle Storage
·
表的行数据在块中的存储
在最底层，行存放于block中，之上还有其它层次。
Oracle数据存储模式
segment是有类型的，是schema 对象，如表，索引等。一个segment只能位于一个表空间。
PL/SQL，视图，序列不是segment。
Oracle block是数据库I/O的基本单位。这意味着，即使只改变了某一行的某一列，整个block都需要更新。
Datafile格式化为Oracle block，连续的数字编号，Oracle block的大小在表空间中是一致的，默认8K。
ORCLCDB> show parameter DB_BLOCK_SIZE;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
block在datafile中是唯一编码。
为管理方便，block被组织为extent，extent是数据文件中连续编号的block。
segment包含1到多个extent。
datafile有多个OS block组成。OS block是文件系统I/O的基本单位，通常为512字节。
Oracle Block构建在OS block之上，因此要避免前者比后者小。
Segments, Extents, Blocks和Rows
数据存于segment，DBA_SEGMENTS描述了这些信息。
orclpdb1> select segment_type, count(1) from dba_segments group by segment_type order by segment_type;
SEGMENT_TYPE COUNT(1)
------------------ ----------
CLUSTER 10
INDEX 975
INDEX PARTITION 83
LOB PARTITION 3
LOBINDEX 443
LOBSEGMENT 443
NESTED TABLE 16
ROLLBACK 1
SYSTEM STATISTICS 1
TABLE 707
TABLE PARTITION 22
TYPE2 UNDO 10
12 rows selected.
典型的类型包括：
- Table
- Index：排序的键值对，并包含指针（ROWID）指向指向行的物理位置。
- TYPE2 UNDO：存放修改前的数据，用于回退
- TABLE PARTITION：分区。每一个分区可位于不同表空间
- INDEX PARTITION：如果表分区，通常索引也分区
- LOB*: 如果列定义为LOB类型，列中仅存放地址，指向另外的segment
- CLUSTER：包含多个表的segment，和分区的作用相反。
- NESTED TABLE：存放自定义列
每一个segment包含一到多个extent，最初是1个，extent填满后，在表空间中再分配1个。
orclpdb1> create table test(c1 date) segment creation immediate;
Table created.
orclpdb1> select tablespace_name, file_id, extent_id, block_id, blocks, bytes from dba_extents where owner='SYS' and segment_name='TEST';
TABLESPACE_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSTEM 9 0 35176 8 65536
orclpdb1> alter table test allocate extent;
Table altered.
orclpdb1> select tablespace_name, file_id, extent_id, block_id, blocks, bytes from dba_extents where owner='SYS' and segment_name='TEST';
TABLESPACE_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSTEM 9 0 35176 8 65536
SYSTEM 9 1 35184 8 65536
orclpdb1> select tablespace_name,file_name from dba_data_files where file_id=9;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
SYSTEM /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
指定分配extent所使用的文件：
alter table tablename ALLOCATE EXTENT STORAGE (DATAFILE 'filename')
DBA_TABLES中可查询表所在的表空间。
Oracle Block包含头区和数据区，头区包含行的列表，锁信息，数据区包含实际数据，从底向上增长。头区和数据区间是剩余空间。
探索存储架构
物理结构：
orclpdb1> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/control01.ctl
/opt/oracle/oradata/ORCLCDB/control02.ctl
orclpdb1> select member, bytes from v$logfile join v$log using (group#);
MEMBER BYTES
-------------------------------------------------- ----------
/opt/oracle/oradata/ORCLCDB/redo03.log 209715200
/opt/oracle/oradata/ORCLCDB/redo02.log 209715200
/opt/oracle/oradata/ORCLCDB/redo01.log 209715200
orclpdb1> select t.name tsname, d.name filename, d.bytes from v$tablespace t join v$datafile d using(ts#) union all select t.name,d.name, d.bytes from v$tablespace t join v$tempfile d using(ts#);
TSNAME FILENAME BYTES
------------------------------ -------------------------------------------------- ----------
SYSTEM /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 304087040
SYSAUX /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 377487360
UNDOTBS1 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf 104857600
USERS /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf 361758720
TEMP /opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf 100663296
orclpdb1> select tablespace_name, t.contents, d.file_name, d.bytes from dba_tablespaces t join dba_data_files d using(tablespace_name) union all select tablespace_name, t.contents, d.file_name, d.bytes from dba_tablespaces t join dba_temp_files d using(tablespace_name);
TABLESPACE_NAME CONTENTS FILE_NAME BYTES
------------------------------ --------------------- -------------------------------------------------- ----------
SYSTEM PERMANENT /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 304087040
SYSAUX PERMANENT /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 377487360
UNDOTBS1 UNDO /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf 104857600
USERS PERMANENT /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf 361758720
TEMP TEMPORARY /opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf 100663296
创建表并查看其存储：
orclpdb1> create table system.test as select * from all_objects;
Table created.
orclpdb1> select tablespace_name from dba_tables where owner='SYSTEM' and table_name='TEST';
TABLESPACE_NAME
------------------------------
SYSTEM
orclpdb1> select tablespace_name, segment_type from dba_segments where owner='SYSTEM' and segment_name='TEST';
TABLESPACE_NAME SEGMENT_TYPE
------------------------------ ------------------
SYSTEM TABLE
orclpdb1> select file_name, extent_id, block_id from dba_data_files join dba_extents using(file_id) where owner='SYSTEM' and segment_name='TEST';
FILE_NAME EXTENT_ID BLOCK_ID
-------------------------------------------------- ---------- ----------
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 0 35176
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 1 35184
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 2 35192
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 3 36480
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 4 36488
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 5 36496
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 6 36504
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 7 36512
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 8 36520
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 9 36528
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 10 36536
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 11 36544
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 12 36552
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 13 36560
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 14 36568
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 15 36576
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 16 36608
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 17 36736
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 18 36864
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 19 36992
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 20 37120
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 21 37248
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 22 37376
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 23 37504
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 24 37632
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf 25 37760
26 rows selected.
orclpdb1> alter table system.test move tablespace sysaux;
Table altered.
orclpdb1> select file_name, extent_id, block_id from dba_data_files join dba_extents using(file_id) where owner='SYSTEM' and segment_name='TEST';
FILE_NAME EXTENT_ID BLOCK_ID
-------------------------------------------------- ---------- ----------
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 0 43192
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 1 43200
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 2 43208
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 3 43216
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 4 43224
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 5 43232
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 6 43240
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 7 43248
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 8 43256
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 9 43392
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 10 43400
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 11 43408
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 12 43416
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 13 43424
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 14 43432
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 15 43440
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 16 43520
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 17 43648
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 18 43776
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 19 43904
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 20 44032
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 21 44160
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 22 44288
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 23 44416
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 24 44544
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 25 44672
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf 26 44800
27 rows selected.
orclpdb1> SELECT tablespace_name, extent_id, bytes/1024 kb from dba_extents where segment_name = 'TEST';
TABLESPACE_NAME EXTENT_ID KB
------------------------------ ---------- ----------
SYSAUX 0 64
SYSAUX 1 64
SYSAUX 2 64
SYSAUX 3 64
SYSAUX 4 64
SYSAUX 5 64
SYSAUX 6 64
SYSAUX 7 64
SYSAUX 8 64
SYSAUX 9 64
SYSAUX 10 64
SYSAUX 11 64
SYSAUX 12 64
SYSAUX 13 64
SYSAUX 14 64
SYSAUX 15 64
SYSAUX 16 1024
SYSAUX 17 1024
SYSAUX 18 1024
SYSAUX 19 1024
SYSAUX 20 1024
SYSAUX 21 1024
SYSAUX 22 1024
SYSAUX 23 1024
SYSAUX 24 1024
SYSAUX 25 1024
SYSAUX 26 1024
27 rows selected.
orclpdb1> drop table system.test;
Table dropped.
创建和管理表空间
SYSTEM, SYSAUX, TEMP和UNDO是必需的表空间。
相关视图：DBA_TABLESPACES, DBA_DATA_FILES, DBA_TEMP_FILES和DBA_FREE_SPACE
创建表空间
例如：
create smallfile tablespace TESTTS
datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/testts.dbf'
size 100M autoextend on next 10M maxsize 200M
logging
default nocompress
online
extent management local autoallocate
segment space management auto;
以上命令，绝大多数是默认选项。smallfile表示表空间可由多个datafile组成，对应的是bigfile。
datafile可以指定多个。autoextend 表示最初的100M满后可自动扩展。
segment操作会产生redo，是默认选项。
又一例：
create tablespace gltabs datafile
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/gltab01.dbf' size 100m,
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/gltab02.dbf' size 100m
uniform size 4m;
Tablespace created.
orclpdb1> select t.name name, t.bigfile, d.name dname, d.bytes from v$tablespace t join v$datafile d using (ts#) where t.name='GLTABS';
NAME BIG DNAME BYTES
---------- --- -------------------------------------------------- ----------
GLTABS NO /opt/oracle/oradata/ORCLCDB/ORCLPDB1/gltab01.dbf 104857600
GLTABS NO /opt/oracle/oradata/ORCLCDB/ORCLPDB1/gltab02.dbf 104857600
-- 临时表空间不是datafile，而是tempfile，信息在v$tempfile中
orclpdb1> select t.name tname, d.name dname, d.bytes, d.create_bytes from v$tablespace t join v$tempfile d using (ts#);
TNAME DNAME BYTES CREATE_BYTES
---------- -------------------------------------------------- ---------- ------------
TEMP /opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf 100663296 37748736
练习：
orclpdb1> create tablespace newtbs datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/newtbs.dbf' size 10m extent management local autoallocate segment space management auto;
Tablespace created.
orclpdb1> create table newtab(c1 date) tablespace newtbs;
Table created.
orclpdb1> select extent_id, bytes from dba_extents where owner='SYSTEM' and segment_name='NEWTAB';
no rows selected
orclpdb1> select extent_id, bytes from dba_extents where owner='SYS' and segment_name='NEWTAB';
EXTENT_ID BYTES
---------- ----------
0 65536
orclpdb1> alter table newtab allocate extent;
Table altered.
orclpdb1> alter tablespace newtbs offline;
Tablespace altered.
orclpdb1> delete newtab;
delete newtab
*
ERROR at line 1:
ORA-00376: file 16 cannot be read at this time
ORA-01110: data file 16: '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/newtbs.dbf'
orclpdb1> drop newtab;
drop newtab
*
ERROR at line 1:
ORA-00950: invalid DROP option
orclpdb1> drop table newtab;
Table dropped.
orclpdb1> alter tablespace newtbs online;
Tablespace altered.
orclpdb1> alter tablespace newtbs read only;
Tablespace altered.
orclpdb1> drop table newtab;
drop table newtab
*
ERROR at line 1:
ORA-00942: table or view does not exist
orclpdb1> alter tablespace newtbs read write;
Tablespace altered.
orclpdb1> drop tablespace newtbs including contents and datafiles;
Tablespace dropped.
修改表空间
包括更名，在线或离线，只读或读写，扩容。
更名表空间或数据文件
表空间可在线更名：
alter tablespace oldname rename newname
数据文件更名时，第一种方式是将表空间离线：
alter tablespace ... offline
host mv oldname newname
alter database rename file 'oldname' to 'newname';
alter tablespace ... online
另一种可以在线改：
alter database move datafile 'oldname' to 'newname';
在线与离线
alter tablespace tablespacename offline [normal | immediate | temporary]
alter tablespace tablespacename online
只读
对于备份特别有用。
alter tablespace tablespacename read only
DML不可操作，但可以drop。
扩容
表空间扩容可通过增加datadile或扩容datafile。
alter database datafile 'filename' resize ...
alter tablespace ... add datafile 'filename' size ...
alter database datafile 'filename' autoextend on next 100m maxsize 4g
删除表空间
drop tablespace tablespace_name including contents and datafiles;
contents指其中的对象。
Extent管理
表空间设定，适用于其中所有segment。有两种方法：dictionary management 和local management. 但只建议使用local management。
local management使用位图来表示空间是否分配。
extent的大小有uniform size和autoallocate两种形式，uniform size表示分配的extent的大小，越大需要的位图就越小。autoallocate是建议的，表示前16个extend是8 block（64K），之后是128 block（1M）。
Segment空间管理
表空间设定，适用于其中所有segment。两种方式：手工与自动。只建议使用自动。
每一个segment都有五组位图表示其block的空间使用情况，分布表示满，0~25%满，25-50满，50-75满，75-100满。这样，插入数据时，根据数据大小找相应的block。
orclpdb1> select tablespace_name, segment_space_management from dba_tablespaces;
TABLESPACE_NAME SEGMEN
------------------------------ ------
SYSTEM MANUAL
SYSAUX AUTO
UNDOTBS1 MANUAL
TEMP MANUAL
USERS AUTO
Oracle Managed Files（OMF）
OMF使DBA无需了解文件系统细节。
启用OMF需要启用以下所有或部分参数：
DB_CREATE_FILE_DEST -- datafile和online redo log文件
-- online redo log文件，优先级比DB_CREATE_FILE_DEST高
DB_CREATE_ONLINE_LOG_DEST_1
DB_CREATE_ONLINE_LOG_DEST_2
DB_CREATE_ONLINE_LOG_DEST_3
DB_CREATE_ONLINE_LOG_DEST_4
DB_CREATE_ONLINE_LOG_DEST_5
DB_RECOVERY_FILE_DEST -- archive redo log文件和RMAN备份文件
例如：
orclpdb1> alter system set db_create_file_dest='/u01/oradata';
System altered.
orclpdb1> create tablespace newtbs;
Tablespace created.
orclpdb1> select file_name, bytes, autoextensible, increment_by, maxbytes from dba_data_files where tablespace_name = 'NEWTBS';
FILE_NAME
----------------------------------------------------------------------------------------------------
BYTES AUT INCREMENT_BY MAXBYTES
---------- --- ------------ ----------
/u01/oradata/ORCLCDB/94B31C5BDD3F055EE0530100007FAE63/datafile/o1_mf_newtbs_gtchvyv7_.dbf
104857600 YES 12800 3.4360E+10
orclpdb1> alter tablespace newtbs add datafile size 10m;
Tablespace altered.
orclpdb1> select file_name, bytes, autoextensible, increment_by, maxbytes from dba_data_files where tablespace_name = 'NEWTBS';
FILE_NAME
----------------------------------------------------------------------------------------------------
BYTES AUT INCREMENT_BY MAXBYTES
---------- --- ------------ ----------
/u01/oradata/ORCLCDB/94B31C5BDD3F055EE0530100007FAE63/datafile/o1_mf_newtbs_gtchvyv7_.dbf
104857600 YES 12800 3.4360E+10
/u01/oradata/ORCLCDB/94B31C5BDD3F055EE0530100007FAE63/datafile/o1_mf_newtbs_gtcj1ntz_.dbf
10485760 NO 0 0
orclpdb1> drop tablespace newtbs including contents and datafiles;
Tablespace dropped.
自动空间管理
空间管理分3个层面：表空间，segment和block。
segment空间分配
datafile维护一个位图，指示block是未分配还是已分配给extent。当segment满时，根据这些位图就可以分配新的extent。segment初建立时，分配一个extent。
segment是对象的容器，对象也可以不存于segment中。原因是由于segment可以延迟分配。
orclpdb1> show parameter DEFERRED_SEGMENT_CREATION;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
延迟分配默认开启，只有当真正有数据时才分配。可以修改此设定：
create table ... segment creation deferred;
create table ... segment creation immediate;
SYS和SYSTEM创建的对象不支持延迟分配。
自动segment空间管理
segment有extend组成，插入数据时，必须决定放在哪个block中。这是由前面提到的五个位图决定的。
对于变长列如VARCHAR2或NULL，需特殊考虑，特别是UPDATE时。block会预留10%空间（PCTFREE 设置）用于行扩展。如果不足10%则会认为block已用满。如果行变大（update），而空间不足，则行必须迁移到其它block，称为row migration，此时ROWID不变。好处是索引不变，坏处是多了I/O开销。
若行数据大于block，则称为chained row。
通过压缩节省空间
压缩可节省空间，可些许提高读取性能。压缩分为：
- 基本表压缩 - 默认，实际是重复数据删除技术（block内部，不会跨block），当使用direct load插入时
- 高级行压缩 - 需要许可
- 混合列压缩(HCC) - 只在某些存储层提供，如Exadata
压缩类型在建表时确定，后续可修改，但不会影响已有的数据。除非表重新组织，如alter table ... move
例如：
create table ... compress [basic];
create table ... row store compress advanced;
主动监控管理表空间用量
orclpdb1> select object_name, warning_operator,warning_value, critical_operator, critical_value from dba_thresholds where metrics_name='Tablespace Space Usage';
OBJECT_NAME WARNING_OPER WARNING_VALUE CRITICAL_OPE CRITICAL_VALUE
---------------- ------------ ------------------------------ ------------ --------------------
TEMP DO NOT CHECK <SYSTEM-GENERATED THRESHOLD> DO_NOT_CHECK 0
UNDOTBS1 DO NOT CHECK <SYSTEM-GENERATED THRESHOLD> DO_NOT_CHECK 0
UNDOTBS1_TEMP DO NOT CHECK <SYSTEM-GENERATED THRESHOLD> DO_NOT_CHECK 0
-- Oracle维护表空间使用历史，存在AWR中
orclpdb1> select rtime, name, tablespace_usedsize from v$tablespace v join dba_hist_tbspc_space_usage d on (v.ts#=d.tablespace_id) order by name, rtime desc;
RTIME NAME TABLESPACE_USEDSIZE
------------------------- ---------- -------------------
10/15/2019 21:00:56 SYSAUX 44856
10/15/2019 20:00:21 SYSAUX 44856
10/15/2019 19:46:17 SYSAUX 44856
10/15/2019 18:00:49 SYSAUX 44856
10/15/2019 17:00:16 SYSAUX 44856
10/15/2019 16:00:53 SYSAUX 43312
10/15/2019 15:00:59 SYSAUX 43312
10/15/2019 14:00:38 SYSAUX 43312
10/15/2019 13:06:01 SYSAUX 43040
10/15/2019 12:00:50 SYSAUX 43032
10/15/2019 11:00:14 SYSAUX 43032
10/15/2019 10:00:42 SYSAUX 43032
10/15/2019 08:31:51 SYSAUX 43032
10/14/2019 23:00:50 SYSAUX 43024
10/14/2019 22:00:38 SYSAUX 43024
10/14/2019 21:00:58 SYSAUX 43024
10/14/2019 20:13:30 SYSAUX 43024
10/14/2019 19:41:45 SYSAUX 43024
10/14/2019 18:00:02 SYSAUX 43016
10/14/2019 17:00:27 SYSAUX 43016
10/14/2019 16:00:02 SYSAUX 43016
10/14/2019 15:00:37 SYSAUX 42848
10/14/2019 14:00:11 SYSAUX 42848
10/14/2019 13:17:55 SYSAUX 42848
10/14/2019 12:00:44 SYSAUX 42848
10/14/2019 11:00:19 SYSAUX 42848
10/14/2019 10:00:56 SYSAUX 42840
10/14/2019 09:00:31 SYSAUX 42840
10/14/2019 08:18:14 SYSAUX 42840
10/13/2019 21:00:46 SYSAUX 42840
10/13/2019 20:00:23 SYSAUX 42576
10/13/2019 18:30:48 SYSAUX 42568
10/13/2019 11:00:17 SYSAUX 42552
10/13/2019 10:13:59 SYSAUX 42552
10/13/2019 08:49:53 SYSAUX 42552
10/12/2019 20:41:47 SYSAUX 41848
10/12/2019 18:00:03 SYSAUX 41840
10/12/2019 17:00:30 SYSAUX 41120
10/15/2019 21:00:56 SYSTEM 36496
10/15/2019 20:00:21 SYSTEM 36496
10/15/2019 19:46:17 SYSTEM 36496
10/15/2019 18:00:49 SYSTEM 36488
10/15/2019 17:00:16 SYSTEM 36488
10/15/2019 16:00:53 SYSTEM 36456
10/15/2019 15:00:59 SYSTEM 36456
10/15/2019 14:00:38 SYSTEM 36456
10/15/2019 13:06:01 SYSTEM 36456
10/15/2019 12:00:50 SYSTEM 36456
10/15/2019 11:00:14 SYSTEM 36456
10/15/2019 10:00:42 SYSTEM 36456
10/15/2019 08:31:51 SYSTEM 36456
10/14/2019 23:00:50 SYSTEM 36456
10/14/2019 22:00:38 SYSTEM 36456
10/14/2019 21:00:58 SYSTEM 36448
10/14/2019 20:13:30 SYSTEM 36448
10/14/2019 19:41:45 SYSTEM 36448
10/14/2019 18:00:02 SYSTEM 36448
10/14/2019 17:00:27 SYSTEM 36448
10/14/2019 16:00:02 SYSTEM 36448
10/14/2019 15:00:37 SYSTEM 36448
10/14/2019 14:00:11 SYSTEM 36448
10/14/2019 13:17:55 SYSTEM 36448
10/14/2019 12:00:44 SYSTEM 36448
10/14/2019 11:00:19 SYSTEM 36448
10/14/2019 10:00:56 SYSTEM 36448
10/14/2019 09:00:31 SYSTEM 36448
10/14/2019 08:18:14 SYSTEM 36448
10/13/2019 21:00:46 SYSTEM 36448
10/13/2019 20:00:23 SYSTEM 36320
10/13/2019 18:30:48 SYSTEM 36320
10/13/2019 11:00:17 SYSTEM 36320
10/13/2019 10:13:59 SYSTEM 36320
10/13/2019 08:49:53 SYSTEM 36320
10/12/2019 20:41:47 SYSTEM 34936
10/12/2019 18:00:03 SYSTEM 34936
10/12/2019 17:00:30 SYSTEM 34528
10/15/2019 21:00:56 TEMP 0
10/15/2019 20:00:21 TEMP 0
10/15/2019 19:46:17 TEMP 0
10/15/2019 18:00:49 TEMP 0
10/15/2019 17:00:16 TEMP 0
10/15/2019 16:00:53 TEMP 0
10/15/2019 15:00:59 TEMP 0
10/15/2019 14:00:38 TEMP 0
10/15/2019 13:06:01 TEMP 0
10/15/2019 12:00:50 TEMP 0
10/15/2019 11:00:14 TEMP 0
10/15/2019 10:00:42 TEMP 0
10/15/2019 08:31:51 TEMP 0
10/14/2019 23:00:50 TEMP 0
10/14/2019 22:00:38 TEMP 0
10/14/2019 21:00:58 TEMP 0
10/14/2019 20:13:30 TEMP 0
10/14/2019 19:41:45 TEMP 0
10/14/2019 18:00:02 TEMP 0
10/14/2019 17:00:27 TEMP 0
10/14/2019 16:00:02 TEMP 0
10/14/2019 15:00:37 TEMP 0
10/14/2019 14:00:11 TEMP 0
10/14/2019 13:17:55 TEMP 0
10/14/2019 12:00:44 TEMP 0
10/14/2019 11:00:19 TEMP 0
10/14/2019 10:00:56 TEMP 0
10/14/2019 09:00:31 TEMP 0
10/14/2019 08:18:14 TEMP 0
10/13/2019 21:00:46 TEMP 0
10/13/2019 20:00:23 TEMP 0
10/13/2019 18:30:48 TEMP 0
10/13/2019 11:00:17 TEMP 0
10/13/2019 10:13:59 TEMP 0
10/13/2019 08:49:53 TEMP 0
10/12/2019 20:41:47 TEMP 0
10/12/2019 18:00:03 TEMP 0
10/12/2019 17:00:30 TEMP 0
10/15/2019 21:00:56 UNDOTBS1 3472
10/15/2019 20:00:21 UNDOTBS1 3472
10/15/2019 19:46:17 UNDOTBS1 3472
10/15/2019 18:00:49 UNDOTBS1 3472
10/15/2019 17:00:16 UNDOTBS1 3528
10/15/2019 16:00:53 UNDOTBS1 3528
10/15/2019 15:00:59 UNDOTBS1 3528
10/15/2019 14:00:38 UNDOTBS1 3928
10/15/2019 13:06:01 UNDOTBS1 4512
10/15/2019 12:00:50 UNDOTBS1 4512
10/15/2019 11:00:14 UNDOTBS1 4512
10/15/2019 10:00:42 UNDOTBS1 4512
10/15/2019 08:31:51 UNDOTBS1 4512
10/14/2019 23:00:50 UNDOTBS1 4512
10/14/2019 22:00:38 UNDOTBS1 4512
10/14/2019 21:00:58 UNDOTBS1 4512
10/14/2019 20:13:30 UNDOTBS1 4512
10/14/2019 19:41:45 UNDOTBS1 4512
10/14/2019 18:00:02 UNDOTBS1 4512
10/14/2019 17:00:27 UNDOTBS1 4512
10/14/2019 16:00:02 UNDOTBS1 4512
10/14/2019 15:00:37 UNDOTBS1 5360
10/14/2019 14:00:11 UNDOTBS1 5360
10/14/2019 13:17:55 UNDOTBS1 5360
10/14/2019 12:00:44 UNDOTBS1 5360
10/14/2019 11:00:19 UNDOTBS1 5360
10/14/2019 10:00:56 UNDOTBS1 5360
10/14/2019 09:00:31 UNDOTBS1 5840
10/14/2019 08:18:14 UNDOTBS1 5840
10/13/2019 21:00:46 UNDOTBS1 5840
10/13/2019 20:00:23 UNDOTBS1 6096
10/13/2019 18:30:48 UNDOTBS1 6096
10/13/2019 11:00:17 UNDOTBS1 6096
10/13/2019 10:13:59 UNDOTBS1 6096
10/13/2019 08:49:53 UNDOTBS1 6096
10/12/2019 20:41:47 UNDOTBS1 5456
10/12/2019 18:00:03 UNDOTBS1 5456
10/12/2019 17:00:30 UNDOTBS1 12672
10/15/2019 21:00:56 USERS 39392
10/15/2019 20:00:21 USERS 39392
10/15/2019 19:46:17 USERS 39392
10/15/2019 18:00:49 USERS 39392
10/15/2019 17:00:16 USERS 39392
10/15/2019 16:00:53 USERS 39392
10/15/2019 15:00:59 USERS 39392
10/15/2019 14:00:38 USERS 39392
10/15/2019 13:06:01 USERS 39392
10/15/2019 12:00:50 USERS 39392
10/15/2019 11:00:14 USERS 39392
10/15/2019 10:00:42 USERS 39392
10/15/2019 08:31:51 USERS 39392
10/14/2019 23:00:50 USERS 39392
10/14/2019 22:00:38 USERS 39392
10/14/2019 21:00:58 USERS 39392
10/14/2019 20:13:30 USERS 39392
10/14/2019 19:41:45 USERS 39392
10/14/2019 18:00:02 USERS 39392
10/14/2019 17:00:27 USERS 39392
10/14/2019 16:00:02 USERS 39392
10/14/2019 15:00:37 USERS 39392
10/14/2019 14:00:11 USERS 39392
10/14/2019 13:17:55 USERS 39392
10/14/2019 12:00:44 USERS 39392
10/14/2019 11:00:19 USERS 39392
10/14/2019 10:00:56 USERS 39392
10/14/2019 09:00:31 USERS 39392
10/14/2019 08:18:14 USERS 39392
10/13/2019 21:00:46 USERS 39392
10/13/2019 20:00:23 USERS 39392
10/13/2019 18:30:48 USERS 39392
10/13/2019 11:00:17 USERS 39392
10/13/2019 10:13:59 USERS 39392
10/13/2019 08:49:53 USERS 39392
10/12/2019 20:41:47 USERS 39392
10/12/2019 18:00:03 USERS 39392
10/12/2019 17:00:30 USERS 22840
190 rows selected.
使用segment advisor
Segment Advisor生产是否需要reorg segment以释放空间的建议。
例如插入大量数据后，然后删除。尽管目前表是空的，但分配的空间明天可能会再需要。Segment Advisor会考虑这些历史情况。Segment Advisor作为自动任务每日运行，并只选择部分segment进行分析，如最忙的，增长最快的，空间使用报警的。
DBMS_SPACE.ASA_RECOMMENDATIONS包含最近一次运行后的建议。
orclpdb1> select segment_name, segment_type, recommendations from table(dbms_space.asa_recommendations);
no rows selected
使用segment shrink重申表或索引空间
当行删除时，其空间可重用，例如插入新数据。但可能会浪费空间，这是可以通过MOVE操作重新避免，但MOVE操作需要锁定表，并且索引需重建。此时可以考虑shrink操作。
alter table table_name enable row movement; -- shrink的前提，因为ROWID会改变；另一前提是自动segment管理
alter table table_name shrink space [compact] [cascade];
其中compact是第一阶段，即将行从末端移到顶端，此时无需加锁。第二阶段可以在维护期间做。
可恢复空间分配
空间不足会导致很多错误，可恢复空间分配是指当空间不够时，操作会挂起而不是失败，错误解决后操作可继续。所有可恢复的会话可查询DBA_RESUMABLE。
查看：
orclpdb1> show parameter resumable;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resumable_timeout integer 0
启用：
-- 会话级别
alter session enable resumable [timeout <seconds> ]
-- 系统级别
alter system set resumable_timeout=60
Data Pumper也可设置RESUMABLE=Y参数。
如果不知道问题所在，设置可恢复空间分配意义不大。
可以设置AFTER SUSPEND ON DATABASE trigger来报告问题。
更多推荐
1
0- 0
已为社区贡献2条内容
所有评论(0)