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来报告问题。

©️2020 CSDN 皮肤主题: 撸撸猫 设计师:设计师小姐姐 返回首页