oracle存储架构

最新推荐文章于 2024-05-13 07:20:48 发布

柠檬

最新推荐文章于 2024-05-13 07:20:48 发布

阅读量317

点赞数

分类专栏： oracle 数据库

本文链接：https://blog.csdn.net/weixin_43475880/article/details/119455165

版权

oracle 同时被 2 个专栏收录

99 篇文章 1 订阅

订阅专栏

数据库

34 篇文章 1 订阅

订阅专栏

database数据库--tablespaces表空间--segments段--exents区--blocks块

本节非常重要！！！！

一、表空间

permanent 永久表空间 system表空间

undo 撤销表空间

temporary 临时表空间

1.1表空间管理方式

段的管理方式和区的管理方式是建立在表空间时确定的，后续不可更改

段管理方式有auto、manual两种，区管理方式有本地管理和字典管理（已淘汰）两种

查询当前表空间段区及其管理方式

SQL> select tablespace_name,contents,extent_management,segment_space_management from dba_tablespaces;

TABLESPACE_NAME                CONTENTS              EXTENT_MAN SEGMEN
------------------------------ --------------------- ---------- ------
SYSTEM                         PERMANENT             LOCAL      MANUAL
SYSAUX                         PERMANENT             LOCAL      AUTO
UNDOTBS1                       UNDO                  LOCAL      MANUAL
TEMP                           TEMPORARY             LOCAL      MANUAL
USERS                          PERMANENT             LOCAL      AUTO
A                              PERMANENT             LOCAL      AUTO
B                              PERMANENT             LOCAL      MANUAL
BIG_TBS                        PERMANENT             LOCAL      AUTO

8 rows selected.

注：

如果system表空间时数据字典管理，其他表空间可以是数据字典管理也可以是本地管理（默认）

（只有system表空间为字典方式管理，其他表空间才有机会被设置为字典管理；若system表空间是本地管理，其他表空间一定是本地管理）

字典管理可以转换成本地管理，但是对于系统表空间，要执行一些附加步骤(步骤如下)

execute dbms_space_admin.tablespace_migragte_to_local('tablespace');

1.2表和表空间的关系

以默认方式创建表空间

SQL> create tablespace a datafile '/u01/app/oracle/oradata/SINGLE04/a01.dbf' size 10m;

Tablespace created.

调oracle提供的dbms_metadata.get_ddl包查看具体的缺省值

SQL> set serverout on;

SQL> declare
  2  aa varchar2(2000);
  3  begin
  4  select dbms_metadata.get_ddl('TABLESPACE','A') into aa from dual;
  5  dbms_output.put_line(aa);
  6  end;
  7  /

  CREATE TABLESPACE "A" DATAFILE
  '/u01/app/oracle/oradata/SINGLE04/a01.dbf' SIZE 10485760
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO

PL/SQL procedure successfully completed.

解释：本地方式管理区，默认自动分配；自动方式管理段。

上面的表空间一经确认，就会固定，不可以后期更改

当我不按默认方式创建时

SQL> create tablespace b datafile '/u01/app/oracle/oradata/SINGLE04/b01.dbf' size 10m extent management local uniform size 128k segment space management manual;

Tablespace created.

128k是个区，

auto模式下，一开始一个区的大小是64k，8个块8个块的一次分配，越往后可能越大。

uniform模式下，指定好就按128k给一个区

注：建表时可以指定区段管理方式，若使用缺省值则该表将服从其所属表空间的区段管理方式

创建表时指定表空间

SQL> create table test0809(id int) tablespace a;

Table created.

给用户授权操作表空间的权限

SQL> grant unlimited tablespace to tim;

Grant succeeded.

1.3删除表空间

下面四种表空间open下不能删

system表空间
active undo表空间
默认临时表空间（可以有好几个，但是只有一个是激活状态的）
默认表空间

下面3中表空间open下不能offline

system
active undo表空间
默认临时表空间

查看表空间空闲大小

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

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SYSTEM                                       7.3125
SYSAUX                                      32.5625
UNDOTBS1                                     281.75
B                                                 9
A                                                 9
USERS                                        2.3125

注：sysaux就是sys

1.4 大文件与小文件

小文件：一个表空间里面可以有多个数据文件，便于扩充

大文件：只能建立一个数据文件，需要使用标准快（8k的block，datafile maxsize可以是32T）

rman备份放在不同的通道。

简化管理：一个表空间对应一个数据文件

SQL> create bigfile tablespace big_tbs datafile '/u01/app/oracle/oradata/SINGLE04/bigtbs01.dbf' size 100m;

Tablespace created.

检查是否创建成功

SQL> select name,bigfile from v$tablespace;

NAME                           BIG
------------------------------ ---
SYSAUX                         NO
SYSTEM                         NO
UNDOTBS1                       NO
USERS                          NO
TEMP                           NO
A                              NO
B                              NO
BIG_TBS                        YES

向大文件表空间增加一个数据文件

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

二、段segment

2.1特点：

表空间可以对应多个段，物理上对应多个数据文件，当一个段比较大的时候可以对应多个数据文件
创建一个模式对象（一个用户下面创建了一个表）时，oracle为这个对象创建一个（或多个）段，在这个段中保存该对象的所有数据（对象不可以跨段），段表一一对应
段中至少有一个初始区。当段数据增加使区不够用时，将为这个段配新的区

解释：段可以简单理解成表，当一个段足够大时，该数据文件剩余空间不够，可以写完后在新的数据文件去书写，于是这个表就在两个数据文件上。一个段里面有多个区，一个区里面有多个块

段管理方式

1）自动管理方式（asm）。auto

每个段的段头都有一组位图（5个位图），位图描述每个块的满度，根据满度的不同将每个块对应到相应的位图上，位图自动跟踪每个块的使用空间，这5个位图的满度如下定义：一个位图位于满块，其余4个位图分别用于75%-100%，50%-75%，25%-50%，0-25%。比如块大小为8k，若要插入一行是3k的表行，那么oracle就给对应到满度为25%-50%的位图指定的块上去

2）手动管理方式MSSM Manual Segment Space Management manual

采用空闲列表（freelist）管理段的存储空间

传统方法，仍在使用，未被淘汰，保留pctfree和pctused属性。pctfree中数据可以被插入。

段管理方式只在创建表空间时设置，一经确定不能改变。

缺省状态为自动管理方式，

assm的前提是extent management local

2.2表和段的关系

一般而言，一个单纯的表，分配一个段。但是表上经常会有主键约束，那么就会有索引，索引有索引段，还有分区表，每个分区会有独立的段，再有就是oracle的大对象，若是表里面引用blob，clob，那这个表就又被分区多个段来。

创建新用户，并赋予权限

SQL> conn / as sysdba
Connected.

SQL> create user tim identified by tim;

User created.

SQL> grant connect,resource to tim;

Grant succeeded.

查看参数deferred_segment_creation

SQL> show parameter DEFERRED_SEGMENT_CREATION;

NAME                                 TYPE       VALUE
------------------------------------ -----------------------------------------
deferred_segment_creation            boolean    TRUE

注：该参数值为true时，create table并不会马上分配segment，当第一个insert语句才开始分配segment。

赋予用户tim插入表数据的权限，并连接tim用户

SQL> grant unlimited tablespace to tim;

Grant succeeded.

SQL> conn tim/tim;
Connected.

创建简单表，插入数据，并查看所创建段

SQL> create table test080902 (x int);

Table created.

SQL> insert into test080902 values (1);

1 row created.

SQL> select segment_name from user_segments;

SEGMENT_NAME
----------------------------------------------------------------------
TEST080902

创建复杂表，插入数据，并查看生成段

SQL> create table test080903 (x int primary key,b blob,c clob);

Table created.

SQL> insert into test080903 values(1,'2','3');

1 row created.

SQL> select segment_name from user_segments;

SEGMENT_NAME
----------------------------------------------------------------------
SYS_C007542 #创建主键约束产生的段
SYS_IL0000073272C00002$$
SYS_IL0000073272C00003$$
SYS_LOB0000073272C00002$$
SYS_LOB0000073272C00003$$
TEST080902
TEST080903

每创建索引时，会增加一个段，每创建一个clob对象或blob时，会增加两个段。

注：

也可以使用局部设置改变插入数据才创建段的功能，在create table时指定segment creation子句指定。如：

create table tim.t1(x int,name char(10) segment creation immediate tablespace a)

create table tim.t2(x int) segment creation deffered;

三、区

3.1特点

区是oracle进行存储空间分配的最小单位。是由一系列逻辑上连续的oracle数据块组成的逻辑存储结构。段中第一个区叫初始区，随后分配的区叫后续区。当段中所有的区空间使用完后，oracle自动为该段分配新的区。

3.2区管理方式

1）字典管理：在数据字典中管理表空间的区空间分配。

缺点：某些在字典管理方式下的存储分配有时会产生递归操作，并且容易产生碎片，从而影响系统性能。已经淘汰

2）本地管理：

在每个数据文件中使用位图管理空间的分配。表空间中所有区（extent）的分配信息都保存在该表空间对应的数据文件的头部。

优点：速度快，存储空间的分配和回收只简单改变数据文件中的位图，而不像字典管理需要修改数据库。无碎片，易于维护。

3.3 表和区的关系

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

3.4查看段的初始区分配情况

1）创建表空间和表

SQL> create tablespace c datafile '/u01/app/oracle/oradata/SINGLE04/c01.dbf' size 10m;

Tablespace created.

SQL> create table tim.test080904 tablespace c as select * from v$session;

Table created.

2）查看区的分配情况

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

SEGMENT_NAME            FILE_ID  EXTENT_ID      BYTES
-------------------- ---------- ---------- ----------
TEST080904                    9          0      65536

可以看到初始区id为0，所属数据文件号为9，大小65536bytes

3）向表中插入更多的数据并查看分区情况

SQL> INSERT into tim.test080904 select * from tim.test080904;

57 rows created.

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

SEGMENT_NAME            FILE_ID  EXTENT_ID      BYTES
-------------------- ---------- ---------- ----------
TEST080904                    9          0      65536
TEST080904                    9          1      65536

可以发现该插入操作产生了一个新的区，区id为1

4）删除表空间并查看分区情况

SQL> delete tim.test080904;

114 rows deleted.

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

SEGMENT_NAME            FILE_ID  EXTENT_ID      BYTES
-------------------- ---------- ---------- ----------
TEST080904                    9          0      65536
TEST080904                    9          1      65536

可知表删除操作不能删除表空间中的分区情况

5）可以预分配表空间中的区，但是size不超过表空间的剩余可用空间

SQL> alter table tim.test080904 allocate extent (datafile '/u01/app/oracle/oradata/SINGLE04/c01.dbf' size 5m);

Table altered.

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

SEGMENT_NAME            FILE_ID  EXTENT_ID      BYTES
-------------------- ---------- ---------- ----------
TEST080904                    9          0      65536
TEST080904                    9          1      65536
TEST080904                    9          2    1048576
TEST080904                    9          3    1048576
TEST080904                    9          4    1048576
TEST080904                    9          5    1048576
TEST080904                    9          6    1048576

可以发现区自动分配，每个区size = 1m

6）回收未使用的extent

SQL> alter table tim.test080904 deallocate unused;

Table altered.

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

SEGMENT_NAME            FILE_ID  EXTENT_ID      BYTES
-------------------- ---------- ---------- ----------
TEST080904                    9          0      65536
TEST080904                    9          1      65536

检查发现刚刚分配的，未使用过的区已经被回收

7）查看该表对应的数据文件和表空间

SQL> select file_id,file_name,tablespace_name from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         1 /u01/app/oracle/oradata/SINGLE04/system01.dbf      SYSTEM
         3 /u01/app/oracle/oradata/SINGLE04/sysaux01.dbf      SYSAUX
         2 /u01/app/oracle/oradata/SINGLE04/undotbs01.dbf     UNDOTBS1
         7 /u01/app/oracle/oradata/SINGLE04/users01.dbf       USERS
         5 /u01/app/oracle/oradata/SINGLE04/a01.dbf           A
         4 /u01/app/oracle/oradata/SINGLE04/b01.dbf           B
         8 /u01/app/oracle/oradata/SINGLE04/bigtbs01.dbf      BIG_TBS
         9 /u01/app/oracle/oradata/SINGLE04/c01.dbf           C

dab_data_files中的file_id和dba_extents中的file_id保持一致，是物理上的数据文件的编号

四、数据块block

4.1特点

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

自动管理方式若区为本地管理，段为auto增加（ASSM），该表空间的所有快均是位图自动管理方式。系统默认
空闲列表方式（MSSM）引入freelist，用pctfree，pctused两个参数控制可用存储区的大小，避免行迁移发生。这两个参数可在创建表空间时指定，也可以在建立数据库模式对象（表，索引）时设置。模式对象设置的优先级高于表空间设置。即：若表和索引中没有设置，则按表空间的设置，否则按系统默认的自动管理方式管理块

data block：block header+free space+data，标准8k，支持2-32k

block header

ITL:事务槽，可以有多个ITL以支持并发事务，每当一个事务需要修改数据块里面的数据时，必须先得到一个ITL，一遍存储当前事务的id，事务所用的undo数据块地址，scn，当前事务是否提交等信息。

initrans：初始化事务槽个数，表默认1，index默认2

maxtrans：最大的事物槽个数（默认255）

row dir：行目录，指向空闲起始和结束的偏移量

注：使块头增加的可能情况是，row enries增加，并发率变高，增加更多的ITL空间

空闲表方式的数据块管理

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

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

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

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

行迁移：指一个数据行由于update语句导致当前块被重新定位到另外一个块（那里有充足空间），但在原始块中保留一个指针的情形。原始块中的指针是必须的，因为索引的row id指向原位置。行迁移是update语句引起，与insert和delete无关。

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

查看dba_tables的AVG_ROW_LEN列和CHAIN_CNT列，当CHAIN_CNT有值时，说明发生了行迁移或行链接，看AVG_ROW_LEN,他表示行的平均长度，单位bytes，如果AVG_ROW_LEN《块，则行迁移，若》块大小，则行链接。

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

Table created.

SQL> begin
  2  for i in 1..1000 loop
  3  insert into test0810 values(i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> analyze table test0810 compute statistics;

Table analyzed.

SQL> select pct_free,pct_used,avg_row_len,chain_cnt from user_tables where table_name='TEST0810';

  PCT_FREE   PCT_USED AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ----------- ----------
        10         40           7          0

SQL> update test0810 set c1='timran is my name';

1000 rows updated.

SQL> analyze table  test0810 compute statistics;

Table analyzed.

SQL> select pct_free,pct_used,avg_row_len,chain_cnt from user_tables where table_name='TEST0810';

  PCT_FREE   PCT_USED AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ----------- ----------
        10         40          26        863

SQL> alter table test0810 move;

Table altered.

SQL> analyze table test0810 compute statistics;

Table analyzed.

SQL> select pct_free,pct_used,avg_row_len,chain_cnt from user_tables where table_name='TEST0810';

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

对于大部分目的而言，应该用dbms_stats包中的过程分析表，但是若要查看行链接或者行迁移信息，只能通过analyze命令检测

4.2 表和块的关系

1）什么是高水位线？

高水位线：high water mark HWM

在数据库中，高水位线是曾经包含了最新数据的块。原则上HWM只会增大，及时将表中的数据全部删除，HWM也不会降低。（若数据块是以堆的方式插入数据，那么最新的数据将在堆的最高点，HWM是堆曾经的最高点）

使用全表扫描时需要读出HWM以下的所有数据块，即使表中仅有少量的数据块，这将占用大量IO资源。

2)解决方法如下:

2.1）异动表，move法，将表从一个表空间移动到另一个表空间（也可以在本表空间内move），可以清楚表里面的碎片。

alter table test0810 move [tablespace users];

优点：可以清除表中的碎片，降低高水位线。

缺点：move需要额外一倍的空间

move过程会锁表，其他用户不可以在该表上做ddl和dml操作。

move之后，索引不可用，需要重新创建索引

2.2）收缩表 shrink ，也叫段重组。底层实现是通过insert和delete操作。

两个阶段：压缩阶段+ddl阶段

alter table test0810 shrink space [cascade][compact]

前提：

段管理方式采用assm，因为只有assm使用位图管理才会记录快满度信息
表启用了row movement

步骤：

执行下面语句，只完成了第一阶段-----收缩阶段，可以在业务高峰时完成这个任务

alter table test0810 shrink space compact

高峰期后，执行下面语句；因压缩阶段大部分工作已经完成，很快进入第二阶段，dml操作会有短暂的锁等待时间。

alter table test0810 shrink space

实践

sys创建表空间和创建表

SQL> create tablespace e datafile '/u01/app/oracle/oradata/SINGLE04/e01.dbf' size 100m;

Tablespace created.

SQL> create table tim.test081002 tablespace e as select * from dba_objects;

Table created.

连接tim用户，查询blocks

SQL> conn tim/tim;
Connected.
SQL> select max(rownum) from test081002;

MAX(ROWNUM)
-----------
      72441

SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table_name='TEST081002';

TABLE_NAME   BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------- -------------- ----------
TEST081002   1439   0              72441

SQL> analyze table test081002 compute statistics;

Table analyzed.

删除40000行数据并查询块大小，发现数据块并没有变化

SQL> delete test081002 where rownum<40000;

39999 rows deleted.

SQL> commit;

Commit complete.

SQL> select table_name,blocks,num_rows from user_tables where table_name='TEST081002';

TABLE_NAME     BLOCKS      NUM_ROWS
------------   ---------- ----------
TEST081002     1439        32442

做shrink。第一阶段，使行能移动，并压缩数据，检查发现此时数据块大小依旧不会变化

SQL> alter table test081002 enable row movement;

Table altered.

SQL> alter table test081002 shrink space compact;

Table altered.

SQL> analyze table test081002 compute statistics for table;

Table analyzed.

SQL> select table_name,blocks,num_rows from user_tables where table_name='TEST081002';

TABLE_NAME        BLOCKS   NUM_ROWS
------------      ---------- ----------
TEST081002        1439      32442

第二阶段：DDL命令阶段

SQL> alter table test081002 shrink space;

Table altered.

SQL> analyze table test081002 compute statistics for table;

Table analyzed.

SQL> select table_name,blocks,num_rows from user_tables where table_name='TEST081002';

TABLE_NAME             BLOCKS   NUM_ROWS
---------------------- -------- ----------
TEST081002             642      32442

此时检查发现数据块大小已经从1439变成642。至此，成功shrink表

考点

表收缩生成undo和redo，索引可以得到维护
收缩分为压缩阶段和降低HWM阶段。shrink操作不占用额外的空间。
可以单独完成第一阶段，此阶段只压缩，不降低HWM，故此时DML操作几乎不受影响
可以级联相关的段一起压缩，即shrink space cascade
段必须assm管理，必须使行能移动。若不满足这两个条件，只能使用move重组表。
mssm管理或有long列表或是有refresh_on_commit物化视图的表

重要表总结：dba_tablespaces\dba_segments\dba_tables\dba_extents\dba_data_files(表和数据文件怎么结合的)

五、临时表空间

5.1 用途

用于排序，temp和undo类似，有多个，但是只有一个是激活状态的，default temporary tablespace不能offline和drop。如果未指定默认的临时表空间，oracle将会使用system作为临时表空间，只有temp表空间时nologin。

注意：

不要用system表空间作为临时表空间
实体都是datafile，在磁盘上，为了排序而存在，若是pga排不下，此时需要用temp表空间来排序

查询表空间

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
A                              ONLINE
B                              ONLINE
BIG_TBS                        ONLINE
C                              ONLINE
E                              ONLINE

当添加临时表空间，若是在dba_temp_files中没出现，则需要reuse temp文件，具体语句如下。

alter tablespace temp add tempfile '/u01/app/oracle/oradata/SINGLE04/temp01.dbf' size 100m reuse;

此时再次查询dba_temp_files,就会发现东西已经存在了

SQL> select file_id,file_name,tablespace_name from dba_temp_files;

   FILE_ID FILE_NAME                                TABLESPACE_NAME
---------- ---------------------------------------- -------------------------
         1 /u01/app/oracle/oradata/SINGLE04/temp01. TEMP
           dbf

在v$tempfile中查看tempfile详细信息

SQL> select file#,name,bytes/1024/1024 from v$tempfile;

     FILE# NAME                                               BYTES/1024/1024
---------- -------------------------------------------------- ---------------
         1 /u01/app/oracle/oradata/SINGLE04/temp01.dbf                     32

5.2 建立临时表空间

创建临时表空间，删除临时表空间

SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/SINGLE04/temp02.dbf' size 10m;

Tablespace created.

SQL> alter tablespace temp2 add tempfile '/u01/app/oracle/oradata/SINGLE04/temp03.dbf' size 5m;

Tablespace altered.

SQL> select file_id,file_name,tablespace_name from dba_temp_files;

   FILE_ID FILE_NAME                                TABLESPACE_NAME
---------- ---------------------------------------- -------------------------
         1 /u01/app/oracle/oradata/SINGLE04/temp01. TEMP
           dbf

         2 /u01/app/oracle/oradata/SINGLE04/temp02. TEMP2
           dbf

         3 /u01/app/oracle/oradata/SINGLE04/temp03. TEMP2
           dbf

SQL> alter tablespace temp2 drop tempfile '/u01/app/oracle/oradata/SINGLE04/temp03.dbf';                 

Tablespace altered.

SQL> select file_id,file_name,tablespace_name from dba_temp_files;

   FILE_ID FILE_NAME                                TABLESPACE_NAME
---------- ---------------------------------------- -------------------------
         1 /u01/app/oracle/oradata/SINGLE04/temp01. TEMP
           dbf

         2 /u01/app/oracle/oradata/SINGLE04/temp02. TEMP2
           dbf

临时表空间若删除了，下次起机时oracle会重新创建temp表空间。

5.3 查看默认临时表空间

SQL> select * from database_properties where property_name like '%TABLESPACE';

PROPERTY_NAME                 PROPERTY_VALUE  DESCRIPTION
--------------------          --------------- ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE  USERS           Name of default permanent tablespace


DEFAULT_TEMP_TABLESPACE       TEMP            Name of default temporary tablespace

此视图规定了缺省临时表空间和缺省永久表空间，缺省undo看参数确定缺省值

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

SQL> alter user tim temporary tablespace TEMP2;

User altered.

指定用户的临时表空间组
SQL> alter user tim temporary tablespace tmpgrp;

User altered.

检查是否指定成功
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TIM';

USERNAME   DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE
---------- -------------------- --------------------
TIM        USERS                TMPGRP

可以创建用户的时候指定

create user hr identified hr default tablespace c profile [] temporary []

公共临时表，私有临时表，缺省使用公共的，也可以让某个用户单独使用某个私有的临时表空间

查询临时表空间。

5.5切换默认的临时表空间

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> select * from database_properties where property_name like '%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE   USERS                Name of default permanent tablespace
DEFAULT_TEMP_TABLESPACE        TEMP2                Name of default temporary tablespace

5.6建立临时表空间

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

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

SQL> alter tablespace temp tablespace group tmpgrp;

Tablespace altered.

SQL> alter tablespace temp2 tablespace group tmpgrp;

Tablespace altered.

SQL> select * from dba_tablespace_groups;

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

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

SQL> alter database default temporary tablespace tmpgrp;

Database altered.

SQL> select * from database_properties where property_name like '%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE   USERS                Name of default permanent tablespace
DEFAULT_TEMP_TABLESPACE        TMPGRP               Name of default temporary tablespace

3）要移除表空间组时，该组不能是缺省的临时表空间

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> alter tablespace temp tablespace group '';

Tablespace altered.

SQL> alter tablespace temp2 tablespace group '';

Tablespace altered.

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

SQL> select * from dba_tablespace_groups;

no rows selected

SQL> drop tablespace temp2 including contents and datafiles;

Tablespace dropped.

default temporary tablespace不可以offline然后drop，当某个tempfile坏了，而不能正常工作时，add新的tempfile，再drop坏掉的tempfile

六、如何调整表空间的尺寸（表空间大小等于所属表空间的数据文件大小之和）

当表空间不足时：

增加数据文件大小（resize）
增加数据文件（add datafile）
设置表空间自动增长（auto extend）

SQL> alter database datafile '/u01/app/oracle/oradata/SINGLE04/b01.dbf' resize 20m;

Database altered.

此时b01.dbf数据文件大小从10m增大为20m，表空间的大小为20m

SQL> alter tablespace b add datafile '/u01/app/oracle/oradata/SINGLE04/b02.dbf' size 10m;

Tablespace altered.

此时新添了大小为10m的属于b表空间的datafile b02.dbf,表空间b的大小为20m

实验详情如下

SQL> create table tim.test0811(id int) tablespace B;

Table created.

SQL> insert into tim.test0811 values(2);

1 row created.

SQL> insert into tim.test0811 select * from tim.test0811;

1 row created.

SQL> /

。。。多次/,指数级增长



当报错时，扩大表空间容量

SQL> alter database datafile '/u01/app/oracle/oradata/SINGLE04/b02.dbf' resize 40m;

Database altered.

SQL> insert into tim.test0811 select * from tim.test0811;

2097152 rows created.

SQL> /
insert into tim.test0811 select * from tim.test0811
*
ERROR at line 1:
ORA-01653: unable to extend table TIM.TEST0811 by 16 in tablespace B


当报错时，新增数据文件

SQL> alter tablespace b add datafile '/u01/app/oracle/oradata/SINGLE04/b03.dbf' size 80m;

Tablespace altered.

SQL> insert into tim.test0811 select * from tim.test0811;

4194304 rows created.

SQL> /
insert into tim.test0811 select * from tim.test0811
*
ERROR at line 1:
ORA-01653: unable to extend table TIM.TEST0811 by 16 in tablespace B


当报错时，使表空间自动增长

SQL> alter database datafile '/u01/app/oracle/oradata/SINGLE04/b02.dbf' autoextend on next 20m maxsize 200m;

Database altered.

SQL> insert into tim.test0811 select * from tim.test0811;                       
8388608 rows created.

SQL> /
insert into tim.test0811 select * from tim.test0811
*
ERROR at line 1:
ORA-01653: unable to extend table TIM.TEST0811 by 16 in tablespace B

七、可恢复空间分配 oracle的resumable（可恢复）功能

当一条insert语句插入大量数据时，由于表空间不足且没有开启自动扩展，该sql语句会中断，且报ora-01653的错误。导致数据库资源浪费的情况。为应对这种问题，oracle设计了resumable功能。在resumable开启时，若某条sql执行申请不到空间（包括数据表空间、undo表空间、temporary空间等）时，该事务的语句挂起（suspended），等空间扩展后，oracle会继续运行该条sql语句。

resumable有2个级别

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

因为resumable有资源消耗代价，所以session级别的resumable比较实际。

timeout单位为s，涉及到参数resumable_timeout.

resumable_timeout=0,enable session时需要指定timeout。否则使用缺省值7200s。
resumable_timeout<>0，enable session时可以省略timeout，此时若是指定timeout会覆盖掉参数resumable_timeout值

实验

begin
for i in 1..2000 loop
insert into tim.test0811 values('this is test');
end loop;
commit;
end;
/

SQL> create tablespace small datafile '/u01/app/oracle/oradata/SINGLE04/small01.dbf' size 2m;

Tablespace created.

SQL> create table tim.test0811(x char(1000)) tablespace small;

Table created.

SQL> begin
for i in 1..2000 loop
insert into tim.test0811 values('this is test');
end loop;
commit;
end;
/  2    3    4    5    6    7
begin
*
ERROR at line 1:
ORA-01653: unable to extend table TIM.TEST0811 by 128 in tablespace SMALL
ORA-06512: at line 3

查询tim.test0811，可以发现数据根本没有插入表中。

SQL> select count(*) from tim.test0811;

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

此时，启动resumable功能，并重新插入，会发现程序挂起

SQL> alter session enable resumable;

Session altered.

SQL> begin
for i in 1..2000 loop
insert into tim.test0811 values('this is test');
end loop;
commit;
end;
/  2    3    4    5    6    7
/

另开一个session查询是否是因为空间不够而导致挂起

SQL> select session_id,sql_text,error_number from dba_resumable;

SESSION_ID SQL_TEXT                                           ERROR_NUMBER
---------- -------------------------------------------------- ------------
       253 INSERT INTO TIM.TEST0811 VALUES('this is test')            1653

查询等待时间

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

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

对small表空间添加数据文件

SQL> alter tablespace small add datafile '/u01/app/oracle/oradata/SINGLE04/small02.dbf' size 40m;

Tablespace altered.

再回到第一个session，会发现程序已经完成，数据已经插入。

disable resumable并删除small测试表空间

SQL> alter session disable resumable ;

Session altered.

SQL> drop tablespace small including contents and datafiles;

Tablespace dropped.

总结

当处于生产环境中时，有时候我们需要插入大量数据，若是插入一半因为空间不够的原因而导致所有的数据都没有插入，是非常浪费时间和资源的，于是resumable功能还是非常重要的，可用性也比较高

考点

下面三种情况引起resumable：表空间超出上限，extents达最大值，quota超出
enable resumable可以再一个session中多次挂起执行的语句，直到disable resumable
dbms_resumable.set_session_timeout可以延长当前session的timeout，且立即生效

柠檬

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle存储架构

一、数据字典追踪数据库的执行操作SQL> SET AUTOTRACE ON;SQL> SELECT file# from v$datafile; FILE#---------- 1 2 3 7Execution Plan----------------------------------------------------------Plan hash value: 3398306107
复制链接

扫一扫