Oracle的存储架构

来源：互联网发布：怎样安装windows 编辑：程序博客网时间：2024/06/10 09:43

DATABASE-->TABLESPACES-->SEGMENTS-->EXENTS-->BLOCKS

11.1 TABLESPACE(表空间）分类

PERMANENT 永久表空间
UNDO 撤销表空间
TEMPORARY 临时表空间

11.1.1 表空间的管理方式：

重点是段的管理方式和区的管理方式是在建立表空间时确定的。

段管理方式有AUTO和MANUAL两种，区管理方式有本地管理和字典管理（已淘汰）两种。

03:32:36 SQL> select tablespace_name,contents ,extent_management,segment_space_management from dba_tablespaces;

TABLESPACE_NAME CONTENTS EXTENT_MANSEGMEN
------------------------------ --------- ---------- ------
SYSTEM PERMANENT DICTIONARY MANUAL
SYSAUX PERMANENT LOCAL AUTO
TEMP TEMPORARY LOCAL MANUAL
USERS PERMANENT LOCAL AUTO
EXAMPLE PERMANENT LOCAL AUTO
UNDO_TBS01 UNDO LOCAL MANUAL
TMP01 TEMPORARY LOCAL MANUAL
TBS_16K PERMANENT LOCAL AUTO
BIG_TBS PERMANENT LOCAL AUTO
TEST PERMANENT DICTIONARY MANUAL

注意两点：
1）如果system表空间是数据字典管理，其他表空间可以是数据字典管理或local管理（默认）
2）字典管理可以转换成本地管理，但是对于系统表空间，要求执行一些附加步骤，比较麻烦。

execute dbms_space_admin.tablespace_migragte_to_local('tablespacename');

11.1.2 表和表空间的关系

建一个使用缺省值的表空间
SQL> create tablespace a datafile '/u01/oradata/timran11g/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','B') 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
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 删除表空间

数据库OPEN下不能删除的表空间是
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，在一个表空间可以建立多个数据文件
2）bigfile ：在一个表空间只能建立一个数据文件（8k的block ，datafile maxsize 可以 32T），可以简化对数据文件管理

09:54:49 SQL> create bigfile tablespace big_tbs datafile '/u01/oradata/timran11g/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(段）

11.2.1 SEGMENT（段）的特点：

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

段管理有两种方式：

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

简单说就是每个段的段头都有一组位图（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时再讨论。

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/oradata/timran11g/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.t1 select * from scott.t1;
已创建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.t1;

已删除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/oradata/timran11g/test01.dbf' size 5m);

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;

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 组成

数据块头部：

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

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

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

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

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

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

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

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

行迁移：指一个数据行由于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<块大小，那么行是迁移行，如果>块大小，那么是链接行。
查看发生迁移或连接的行，使用analyze table xx list chained rows; 但命令需要将结果插入chained_rows表，创建此表用utlchained.sql或utlchn1.sql脚本。参见sql referenve中的analyze命令。

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/oradata/timran11g/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';

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

11.5.3 查看默认的临时表空间

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/oradata/timran11g/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，并立即有效。

0 0