oracle之二表和表空间的关系

表和表空间的关系

建一个使用缺省值的表空间
SQL> create tablespace a datafile '/u01/data/urpdb/a01.dbf' size 10m;

利用oracle提供的dbms_metadata.get_ddl包看看缺省值都给的是什么？

SQL> set serverout on;
SQL>
declare
aa varchar2(2000);
begin
select dbms_metadata.get_ddl('TABLESPACE','A') into aa FROM dual;
dbms_output.put_line(aa);
end;
/

结果：
CREATE TABLESPACE "A" DATAFILE
'/u01/oradata/timran11g/a01.dbf' SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE
8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
PL/SQL 过程已成功完成。

关注最后一行，两个重要信息是:(1)区本地管理且自动分配空间，(2)段自动管理。
// dbms_metadata.get_ddl也可以查看表，('TABLE','EMP','SCOTT')替换('TABLESPACE','B')试试。

SQL>
create tablespace b datafile '/u01/oradata/timran11g/b01.dbf' size 10m
extent management local uniform size 128k //区按照固定大小128K分配，后期不可修改
segment space management manual

同上，调dbms_metadata.get_ddl包看oracle对该语句的ddl操作是：

CREATE TABLESPACE "B" DATAFILE
'/u01/oradata/timran11g/a01.dbf' SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE
8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072 SEGMENT SPACE MANAGEMENT MANUAL

最后一行信息是：区本地管理且统一分配128K, 段手动管理。如果在建表时使用缺省说明，则该表将服从其表空间的这些定义

11.1.3 删除表空间
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

数据库OPEN下不能删除的表空间是
select * from database_properties;
1）system 2）active undo tablespace 3） default temporary tablespace 4）default tablespace

数据库OPEN下不能offine的表空间是
1）system 2）active undo tablespace 3） default temporary tablespace

查看表空间空闲大小

09:47:04 SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDOTBS1 98.4375
SYSAUX 14.625
USERS 48.1875
SYSTEM 1.875
EXAMPLE 31.25

11.1.4 大文件（bigfile）表空间（默认small file）

1）small file，在一个表空间可以建立多个数据文件，datafile的最大容量为(2^22-1)个block，即4194303个block，
而当前数据库的block大小是8k，也就是说最大的文件大小是32G
2）bigfile ：在一个表空间只能建立一个数据文件（使用标准block ，datafile maxsize 可以 32T），可以简化对数据文件管理

09:54:49 SQL> create bigfile tablespace big_tbs datafile '/u01/data/urpdb/bigtbs01.dbf' size 100m;

试图在该表空间下增加一个数据文件会报错

09:55:01 SQL> alter tablespace big_tbs add datafile '/u01/oradata/timran11g/bigtbs02.dbf' size 100m;
alter tablespace big_tbs add datafile '/u01/oradata/timran11g/bigtbs02.dbf' size 100m
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace

查看大文件表空间：
09:55:46 SQL> select name,bigfile from v$tablespace;

NAME BIG
------------------------------ ---
SYSTEM NO
UNDOTBS1 NO
SYSAUX NO
USERS NO
TEMP NO
EXAMPLE NO
TBS_16K NO
BIG_TBS YES

11.2 SEGMENT(段） AUTO MANA

11.2.1 SEGMENT（段）的特点：

1）表空间在逻辑上可以对应多个段，物理上可以对应多个数据文件，一个段比较大时可以跨多个数据文件。
2）创建一个表，ORACLE为表创建一个（或多个）段，在一个段中保存该表的所有表数据(表数据不能跨段）。
3）段中至少有一个初始区。当这个段数据增加使区（extent）不够时，将为这个段分配新的后续区。

段管理有两种方式：

1）自动管理方式(ASSM(Auto Segment Space Management))[10G以上] --采用位图管理段的存储空间

简单说就是每个段的段头都有一组位图（5个位图），位图描述每个块的满度，根据满度的不同将每个块登记到相应的位图上，位图自动跟踪每个块的使用空间，这5个位图的满度按如下定义：满度100%，75%、50%、25%和0%，比如块大小为8k，你要插入一行是3k的表行，那么oracle就给你在满度50%的位图上找个登记的块。

ASSM的前提是EXTENT MANAGEMENT LOCAL，在ORACLE9I以后，缺省状态为自动管理方式，ASSM废弃pctused属性。

2）手工管理方式(MSSM(Manual Segment Space Management)) --采用FREELIST(空闲列表）管理段的存储空间

这是传统的方法，现在仍然在使用，未被淘汰，保留pctfree和pctused属性，这些概念后面介绍block时再讨论。

考点：段的管理方式只有在创建表空间时设置，已经确定不能改变

另外，ASSM的前提是EXTENT MANAGEMENT LOCAL

11.2.2 表和段（segment)的关系

一般来讲一个单纯的表就分配一个段，但往往表没那么单纯，比如表上经常会有主键约束，那么就会有索引，索引有索引段，还有分区表，每个分区会有独立的段，再有就是oracle的大对象, 如果你的表里引用blob,clob,那么这个表就又被分出多个段来。

SQL> conn / as sysdba
SQL> create user tim identified by tim;
SQL> grant connect,resource to tim;

SQL> conn tim/tim
SQL> select * from user_segments;

未选定行

SQL> create table t1 (id int);

SQL> select segment_name from user_segments;

SEGMENT_NAME
---------------------------------------------------------------------------------
T1

SQL> create table t2 (id int constraint pk_t2 primary key, b blob, c clob);

SQL> select segment_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE
-----------------------------------------------------------------------------------------------
PK_T2 INDEX
SYS_IL0000071160C00003$$ LOBINDEX
SYS_LOB0000071160C00003$$ LOBSEGMENT
SYS_IL0000071160C00002$$ LOBINDEX
SYS_LOB0000071160C00002$$ LOBSEGMENT
T2 TABLE
T1 TABLE

注：Oracle11gR2又增加了一个新的初始化参数DEFERRED_SEGMENT_CREATION(仅适用未分区的heap table）, 此参数设为TRUE后，create table后并不马上分配segment, 当第一个insert语句后才开始分配segment。这对于应用程序的部署可能有些好处。(PPT-II-476-478)

也可以使局部设置改变这一功能(覆盖DEFERRED_SEGMENT_CREATION)，在create table语句时加上SEGMENT CREATION子句指定。如：

create table scott.t1(id int,name char(10)) SEGMENT CREATION IMMEDIATE TABLESPACE TB1
或
create table scott.t1(id int,name char(10)) SEGMENT CREATION DEFERRED; //缺省在11gR2

11.3 EXTENT（区）

11.3.1 EXTENT（区）的特点：

区是ORACLE进行存储空间分配的最小单位。是由一系列逻辑上连续的Oracle数据块组成的逻辑存储结构。段中第一个区叫初始区，随后分配的区叫后续区。

11.3.2 区的管理方式：

1）字典管理：在数据字典中管理表空间的区空间分配。Oracle 8i以前只有通过uet$和fet$的字典管理。
缺点：某些在字典管理方式下的存储分配有时会产生递归操作，并且容易产生碎片，从而影响了系统的性能，现在已经淘汰了。

2）本地管理：在每个数据文件中使用位图管理空间的分配。表空间中所有区（extent)的分配信息都保存在该表空间对应的数据文件的头部。
优点：速度快，存储空间的分配和回收只是简单地改变数据文件中的位图，而不像字典管理方式还需要修改数据库。无碎片，更易于DBA维护。

11.3.3 表和区（extent）的关系：

当建立表的时候建立段，然后自动分配相应的extent（1个或者多个），亦可以手工提前分配extent（用于需大量插入数据的表）

11.3.4 实验：查看段的初始区分配情况

sys:
SQL> create tablespace test datafile '/u01/data/urpdb/test01.dbf' size 10m;
SQL> create table scott.t1 tablespace test as select * from scott.dept;
SQL> col segment_name for a20;
SQL> select segment_name,file_id,extent_id,bytes from dba_extents where segment_name='T1';

SEGMENT_NAME FILE_ID EXTENT_ID BYTES
-------------------- ---------- ---------- ----------
T1 6 0 65536

可以看到段T1的初始区ID为0，大小为 65536 bytes;

向表段中自插表数据，看Oracle为该段分配更多的区

SQL> insert into scott.t11 select * from scott.t11;
已创建2048行。

SQL> select segment_name,file_id,extent_id,bytes from dba_extents where segment_name='T1';

SEGMENT_NAME FILE_ID EXTENT_ID BYTES
-------------------- ---------- ---------- ----------
T1 6 0 65536
T1 6 1 65536
T1 6 2 65536

此时看到随着数据的插入，T1段动态扩展为三个区；

SQL> delete scott.t11;

已删除4096行。

SQL> select segment_name,file_id,extent_id,bytes from dba_extents where segment_name='T1';

此时表段的数据已经删除，但所有extent依然健在，无法回收T1段的所有区,

可以要求一个预分配的所需空间（但要注意，所需要的空间一定是在表空间可达到的size范围内）：

alter table scott.t1 allocate extent (datafile '/u01/data/urpdb/test01.dbf' size 8m);

SQL> select segment_name,extent_id,file_id,bytes from dba_extents where segment_name='T1';

SEGMENT_NAME EXTENT_ID FILE_ID BYTES
-------------------- ---------- ---------- ----------
T1 0 6 65536
T1 1 6 65536
T1 2 6 65536
T1 3 6 1048576
T1 4 6 1048576
T1 5 6 1048576
T1 6 6 1048576
T1 7 6 1048576

回收free extent, 使用deallocate, 注意：只能收回从未使用的extent。

SQL> alter table scott.t1 deallocate unused;

表已更改。

SQL> select segment_name,extent_id,file_id,bytes from dba_extents where segment_name='T1';

SEGMENT_NAME EXTENT_ID FILE_ID BYTES
-------------------- ---------- ---------- ----------
T1 0 6 65536
T1 1 6 65536
T1 2 6 65536

顺便提一句：如何查看一个表所对应的数据文件及表空间呢？

抓住上面dba_extents中的file_id字段（user_extents里没有这个字段）,然后；

SQL> select file_id,file_name,tablespace_name from dba_data_files;

select a.file_id,a.file_name,a.tablespace_name,b.EXTENT_ID,b.bytes from ba_data_files a,dba_extents b
where a.file_id=b.file_id and b.file_id=9;

11.4 BLOCK(数据块）

11.4.1 BLOCK(数据块）的特点：

BLOCK是Oracle进行存储空间IO操作的最小单位，BLOCK的管理方法是区的管理和段管理的具体体现：

1、自动管理方式如创建表空间时为本地管理方式，并且将段的存储空间方式设置为AUTO（即ASSM），该表空间的所有块均采用位图自动管理方式。这是系统默认的。

2、空闲列表方式(MSSM) 引入FREELIST概念，以及PCTFREE和PCTUSED两个参数控制可用存储区的大小，避免行迁移现象的发生。这两个参数可在创建表空间时设置，也可在建立数据库的模式对象（表，索引）中设置。模式对象中设置的优先级比表空间的要高。就是说；如表和索引中没有设置，则按表空间的设置，如表空间也没设置，则按自动管理方式管理块。

data block ：oracle 11g 标准块：8k，支持2-32k，有block header 、free space 、data 组成

数据块头部：table directory[该block数据哪个表] / ROW directory[该块记录的行的位置] / ITL

ITL：事务槽，可以有多个ITL以支持并发事务，每当一个事务要更新数据块里的数据时，必须先得到一个ITL槽，然后将当前事务ID，事务所用的undo数据块地址，SCN号，当前事务是否区提交等信息写到ITL槽里。
initrans ：初始化事务槽的个数，表默认1, index 默认为2；
maxtrans：最大的事务槽个数（默认255）

ROW DIR: 行目录, 指向空闲行起始和结束的偏移量。

考点：使块头增加的可能情况是，row entries增加，增加更多的ITL空间。

空闲列表方式的数据块的管理：

freelist：空闲列表中登记了可以插入数据的可用块，位置在段头，插入表行数据时首先查找该列表。

pctfree：用来为一个块保留的空间百分比，以防止在今后的更新操作中增加一列或多列值的长度。达到该值，从freelist清除该块信息。

pctused：一个块的使用水位的百分比，这个水位将使该块返回到可用列表中去等待更多的插入操作。达到该值，该块信息加入freelist
。这个参数在ASSM下不使用。

行链接：指一行存储在多个块中的情况，这是因为该行的长度超过了一个块的可用空间大小，即行链接是跨越多块的行。INSERT

行迁移：指一个数据行由于update语句导致当前块被重新定位到另一个块（那里有充足的空间）中，但在原始块中保留一个指针的情形(PPT-II-470)。原始块中的指针是必需的，因为索引的ROWID项仍然指向原始位置。行迁移是update语句当pctfree空间不足时引起的，它与insert和delete语句无关（考点）。

如何能够知道发生了行链接或行迁移？

查看dba_tables的AVG_ROW_LEN列和CHAIN_CNT列，当CHAIN_CNT有值时，看AVG_ROW_LEN，它表示行的平均长度（byte），如果AVG_ROW_LEN<块大小，那么行是迁移行，如果>块大小，那么是链接行。

SQL> create table t1 (c1 varchar2(20));

SQL>
begin
for i in 1..1000 loop
insert into t1 values(null);
end loop;
end;
/

分析t1表确定无行迁移

SQL> analyze table t1 compute statistics;
SQL> select pct_free,pct_used,avg_row_len,chain_cnt
from user_tables where table_name='T1';

PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT
---------- ---------- ----------- ----------
10 3 0

填充这些空列，再分析t1,有了行迁移
SQL> update t1 set c1='timran is my name';
SQL> analyze table t1 compute statistics;
SQL> select pct_free,pct_used,avg_row_len,chain_cnt from user_tables where table_name='T1';

PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT
---------- ---------- ----------- ----------
10 26 865

move表，再分析t1,行迁移消失。思考：段重组对于行链接有效吗？
SQL> alter table t1 move;
SQL> analyze table t1 compute statistics;
SQL> select pct_free,pct_used,avg_row_len,chain_cnt from user_tables where table_name='T1';

PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT
---------- ---------- ----------- ----------
10 21 0

考点：对于大部分表的而言，应该用DBMS_STATS包中的过程分析表，但要查看行链接或行迁移信息，只能通过ANALYZE命令检测。

11.4.2 表和数据块（block）的关系

1）什么是高水位线？

高水位线（high-water mark，HWM）

在数据库中，如果把表想象成从左到右依次排开的一系列块，高水位线就是曾经包含了数据的最右边的块。原则上HWM只会增大, 即使将表中的数据全部删除，HWM也不会降低。

HWM不是好事，使用全表扫描时通常要读出HWM以下的所有数据块（尽管该表中可能仅有少量数据），这将白白耗费大量IO资源。

2）两个解决办法可降低HWM：

2.1）移动表，move方法, 将表从一个表空间移动到另一个表空间（也可以在本表空间内move)。

语法：alter table t1 move [tablespace users];

优点：可以清除数据块中的碎片,降低高水位线。
缺点：move需要额外（一倍）的空间。
move过程中会锁表，其他用户不能在该表上做DML或DDL操作。
move之后，相关索引都不可用了，表上的索引需要重建（考点）。

2.2）收缩表，shrink 也叫段重组，表收缩的底层实现的是通过匹配的INSERT和DELETE操作。

它分两个不同的阶段：压缩阶段和降低HWM阶段。(PPT-II-491)

语法：alter table t2 shrink space [cascade][compact];

两个前提：1）表所在的表空间段管理是ASSM方式,因为位图方法才记录有关块实际的满度信息(考点）。2）表上启用了row movement。

你发出alter table t2 shrink space compact; 那么只完成了第一阶段。这是压缩阶段。在业务高峰时可以先完成第一阶段

高峰过后，再次alter table t2 shrink space; 因压缩阶段工作大部分已完成，将很快进入第二阶段，DML操作会有短暂的锁等待发生。

测试：

create tablespace timran datafile '/u01/data/urpdb/timran01.dbf' size 100m;
create table scott.t2 tablespace timran as select * from dba_objects;

scott：
select max(rownum) from t2;
select table_name, blocks, empty_blocks, num_rows from user_tables where table_name='T2';
analyze table t2 compute statistics;
delete t2 where rownum<=40000;
commit;

analyze table t2 compute statistics for table;
select table_name, blocks, num_rows from user_tables where table_name='T2';

这时候，num_rows已经减掉了40000条，但 blocks 并没有减少，说明HWM没有下降。

做shrink

alter table t2 enable row movement; //使能行移动

进行第一步----压缩阶段
alter table t2 shrink space compact;
analyze table t2 compute statistics for table;
select table_name, blocks, num_rows from user_tables where table_name='T2'; //HWM不会降低。

进行第二步----降低HWM阶段
alter table t2 shrink space;
analyze table t2 compute statistics for table;
select table_name, blocks, num_rows from user_tables where table_name='T2'; //HWM已经降低。

考点：
1，表收缩操作生成undo和redo数据，索引可以得到维护。
2，收缩分为两个阶段第一阶段是压缩阶段，第二阶段是降低HWM阶段。SHRINK不占用额外的空间。
3，可以单独完成第一阶段，即SHRINK SPACE COMPACT 此阶段不降低HWM，DML操作几乎不受影响。
4，可以级联相关的段一起收缩，即SHRINK SPACE CASCADE。
5，段必须ASSM管理方式，且使能行移动，否则不能收缩，如果不满足这两个前提，MOVE就是重组表的唯一方式。
6，不能收缩MSSM管理，或有LONG列表或是有refresh_on_commit物化视图的表。

11.5 临时表空间

11.5.1 temporary tablespace用途:

用于排序，可以建立多个临时表空间，但默认的临时表空间只能有一个，default temporary tablespace不能offline和drop。如果未指定默
认的临时表空间,oracle 将会使用system作为临时表空间(非本地管理），只有temp表空间是nologing。

09:00:53 SQL> alter tablespace temp add tempfile '/u01/oradata/timran11g/temp01.dbf' size 120m reuse;

这等于在原地重置了临时表空间。

09:01:14 SQL> select file_id,file_name,tablespace_name from dba_temp_files;

FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /u01/oradata/timran11g/temp01.dbf TEMP

09:01:17 SQL> col name for a60;
09:01:19 SQL> select file#,name ,bytes/1024/1024 from v$tempfile;

FILE# NAME BYTES/1024/1024
---------- -------------------------------------------------- ---------------
1 /u01/oradata/timran11g/temp01.dbf 100

11.5.2 建立临时表空间temp2，增加或删除tempfile。
09:04:18 SQL> create temporary tablespace temp2 tempfile '/u01/oradata/timran11g/temp02.dbf' size 10m;
09:05:00 SQL> alter tablespace temp2 add tempfile '/u01/oradata/timran11g/temp03.dbf' size 5m;

SQL> select file_id,file_name,tablespace_name from dba_temp_files;

FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------------------------------------- ------------------------------
1 /u01/oradata/timran11g/temp01.dbf TEMP
2 /u01/oradata/timran11g/temp02.dbf TEMP2
3 /u01/oradata/timran11g/temp03.dbf TEMP2

将temp2里删掉一个tempfile。

SQL> alter tablespace temp2 drop tempfile '/u01/oradata/timran11g/temp03.dbf';

09:06:52 SQL> col PROPERTY_VALUE for a30
09:06:59 SQL> col description for a40
09:07:04 SQL> select * from database_properties;

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
.....

27 rows selected.

11.5.4 指定用户使用临时表空间

20:55:00 SQL> alter user scott temporary tablespace temp2;

//注意，与default profile不同，删除了temp2，scott的temporary不会转回到temp。
11.5.5 切换默认的临时表空间

09:07:05 SQL> alter database default temporary tablespace temp2;
09:07:34 SQL> select * from database_properties;

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP2 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
...

09:07:41 SQL>

11.5.6 建立临时表空间组 (10g新特性）

在很多情况下，会有多个session 使用同一个用户名去访问Oracle，而临时表空间又是基于用户的，那么可以建立一个临时表空间组，
组中由若干临时表空间构成，从而可以提高单个用户多个会话使用表空间的效率。

1)临时表空间组无法显式创建，组是通过第一个临时表空间分配时自动创建。

09:07:41 SQL> alter tablespace temp tablespace group tmpgrp;

09:09:33 SQL> alter tablespace temp2 tablespace group tmpgrp;

09:09:38 SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMPGRP TEMP
TMPGRP TEMP2

2)将临时表空间组设成默认临时表空间，实现负载均衡。

09:09:52 SQL> alter database default temporary tablespace tmpgrp;

Database altered.

09:10:10 SQL> select * from database_properties;

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TMPGRP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
=====================================================
=====================================================
3)要移除表空间组时，该组不能是缺省的临时表空间。

SQL>alter database default temporary tablespace temp;

05:38:11 SQL> alter tablespace temp tablespace group '';
05:38:16 SQL> alter tablespace temp2 tablespace group '';

4)当组内所有临时表空间被移除时，组也被自动删除。

05:38:23 SQL> select * from dba_tablespace_groups;
no rows selected

SQL> drop tablespace temp2 including contents and datafiles;

考点：某个tempfile坏掉使得default temporary tablespace不能正常工作，数据库不会crash, 解决的办法是add一个新的tempfile，然后
再drop掉坏的tempfile.（default temporary tablespace不能offline，但temporary file可以offline）

11.6 如何调整表空间的尺寸（表空间的大小等同它下的数据文件大小之和）

当发生表空间不足的问题时常用的3个解决办法：

1）增加原有数据文件大小（resize）

alter database datafile '/u01/oradata/timran11g/timran01.dbf' resize 10m;

2）增加一个数据文件（add datafile）

alter tablespace timran add datafile '/u01/oradata/timran11g/timran02.dbf' size 20m;

3）设置表空间自动增长（autoextend)

alter database datafile '/u01/oradata/timran11g/timran01.dbf' autoextend on next 10m maxsize 500m;

例:
SQL> create tablespace timran datafile '/u01/oradata/timran11g/timran01.dbf' size 5m;
05:46:08 SQL> create table scott.test1 (id int) tablespace timran;
05:47:12 SQL> insert into scott.test1 values(1);
05:47:15 SQL> insert into scott.test1 select * from scott.test1;
05:47:23 SQL> /
05:47:23 SQL> /
32768 rows created.
05:47:23 SQL> /
insert into scott.test1 select * from scott.test1
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST1 by 8 in tablespace TIMRAN

//用第一种方法扩充表空间
05:47:23 SQL> alter database datafile '/u01/oradata/timran11g/timran01.dbf' resize 10m;

05:48:18 SQL> insert into scott.test1 select * from scott.test1;

05:48:25 SQL> /

131072 rows created.

05:48:26 SQL> /
insert into scott.test1 select * from scott.test1
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST1 by 128 in tablespace TIMRAN

//用第二种方法扩充表空间：

05:48:57 SQL> alter tablespace timran add datafile '/u01/oradata/timran11g/timran02.dbf' size 20m;

05:49:04 SQL> insert into scott.test1 select * from scott.test1;

05:49:13 SQL> /

524288 rows created.

05:49:14 SQL> /
insert into scott.test1 select * from scott.test1
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST1 by 128 in tablespace TIMRAN

//用第三种方法扩充表空间：

05:49:15 SQL> alter database datafile '/u01/oradata/timran11g/timran01.dbf' autoextend on next 10m maxsize 500m;

05:49:33 SQL> insert into scott.test1 select * from scott.test1;

05:49:37 SQL> drop tablespace timran including contents and datafiles;

11.7 可恢复空间分配 Oracle的Resumable（可恢复）功能 (PPT-II-502)

当我们往一个表里面插入大量数据时，如果某条insert语句因表空间的空间不足（没有开启自动扩展），会报 ORA-01653:无法扩展空间的
错误，该条SQL语句会中断，浪费了时间及数据库资源。为防范这个问题，Oracle设计了resumable。在resumable开启的情况下，如果
Oracle执行某条SQL申请不到空间了，比如数据表空间，undob表空间，temporary空间等，则会将该事务的语句挂起（suspended)，等
你把空间扩展后，Oracle又会使该insert语句继续进行。

可以通过两个级别设置resumable

system级别：初始化参数RESUMABLE_TIMEOUT非0，这将使数据库中所有session使用可恢复的空间分配
session级别：alter session enable|disable resumable [TIMEOUT]; 这将为当前session设置可恢复的空间分配

因为resumable是有资源消耗代价的, 所以session级的resumable是比较实际的：

注意TIMEOUT的用法，单位为秒, 进一步要理解初始化参数RESUMABLE_TIMEOUT的含义

RESUMABLE_TIMEOUT=0, enable session时应该指定TIMEOUT。否则使用缺省值7200秒。
RESUMABLE_TIMEOUT<>0，enable session时可以省略TIMEOUT，此时指定TIMEOUT会覆盖掉参数RESUMABLE_TIMEOUT值。

举例：

session 1:

1）建个小表空间，固定2m大小，然后建个表属于这个表空间

SQL> create tablespace small datafile '/u01/data/urpdb/small01.dbf' size 2m;
SQL> create table scott.test(n1 char(1000)) tablespace small;

2）向这个表插入数据，表空间满了，使for语句没有完成循环，2000条语句整体失败。
SQL>
begin
for i in 1..2000 loop
insert into scott.test values('this is test');
end loop;
commit;
end;
/

begin
*
第 1 行出现错误:
ORA-01653: 表 SCOTT.TEST 无法通过 128 (在表空间 SMALL 中) 扩展
ORA-06512: 在 line 3

SQL> select count(*) from scott.test;

COUNT(*)
----------
0

3）使能 resumable功能
SQL> alter session enable resumable;

4）再重复第2）步，会话被挂起；

session 2:

5）查看视图的有关信息

SQL> select session_id,sql_text,error_number from dba_resumable;

SESSION_ID SQL_TEXT ERROR_NUMBER
---------- -------------------------------------------------- ------------
136 INSERT INTO SCOTT.TEST VALUES('this is test') 1653

SQL> select sid,event,seconds_in_wait from v$session_wait where sid=136;

SID EVENT SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------------
136 statement suspended, wait error to be cleared 1

6）加扩表空间，看到session1里挂起的会话得以继续并成功完成了2000条语句的插入。

SQL> alter tablespace small add datafile '/u01/oradata/timran11g/small02.dbf' size 4m;

SQL> select count(*) from scott.test;

COUNT(*)
----------
2000

7）查看EM告警日志报告了以上信息。验证结束后可以disable resumable, 并删除small表空间及数据文件。

session 1:

SQL> alter session disable resumable;
SQL> drop tablespace small including contents and datafiles;

考点：
1.下列三种情况可引起resumable
a)表空间上限超出
b)extents到达最大值
c)quota超出。

2.enable resumable可以在一个session中多次挂起执行的语句,直到disable resumable。
3.DBMS_RESUMABLE.SET_SESSION_TIMEOUT 可以延长当前session的TIMEOUT，并立即有效。