Oracle表空间和数据文件

三种表空间类型：永久表空间、UNDO表空间、临时表空间

系统表空间：system与sysaux，创建数据库时自动创建，包含数据字典

非系统表空间：存放数据表索引，将数据分散存放不同的表空间，便于空间管理与提升性能

创建永久表空间

create tablespace test20110228 datafile 'test20110228.dbf' size 100m;

特殊参数

create tablespace test100 datafile '/u01/test100.dbf' size 10m autoextend on next 1m maxsize unlimited;

autoextend on表示这个数据文件可以自动扩展

next 1m表示该数据文件每次扩展的大小

maxsize unlimited表示大小可以无限扩展

创建大文件表空间

大文件表空间只能有一个数据文件，组成大文件表空间的文件的最大尺寸可以是128TB（块大小是32KB）或者32TB（块大小是8KB）

create bigfile tablespace big100 datafile '/u01/big100.dbf' size 100m autoextend on

其中size表示文件的初始大小

autoextend on表示允许文件自动扩张

删除表空间

表空间信息从数据字典中删除，如dba_tablespaces中的信息
表空间包含的内容从数据字典删除，如user_tables中的信息

DROP TABLESPACE BIG100 INCLUDING CONTENTS AND DATAFILES;

其中“INCLUDING CONTENTS AND DATAFILES”命令的作用是：如果表空间不为空,没有指定INCLUDING CONTENTS这个选项，就会产生错误。AND DATAFILES表示连组成表空间的系统文件一同删除。

DROP TABLESPACE BIG100 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

注意还可以加CASCADE CONSTRAINTS选项，表示连参照完整性约束一起删除。如果其他表空间中的约束引起了要被删除的表空间的主键和唯一性约束，则必须指定这个选项。

表空间改名

alter tablespace test20110228 rename to xin1228;

修改对应的默认表空间

alter user zhangsan default tablespace test02_tbs;

表改名

alter table time_hz rename to shijianbiao;

指定表空间创建表，属于当前用户

create table time_hz tablespace xin1228 as select * from sh.times

创建undo空间，用于存储回滚段，（运行DML语句自动产生回滚段，用于rollback及其他操作）不能包含其他数据表以及索引等

create undo tablespace undo1 datafile '/db/undo100.dbf' size 40m;

创建临时表空间，用于排序

create temporary tablespace temp100 tempfile '/db/temp100.dbf' size 100m;

默认临时表空间设置，允许指定数据库默认的临时表空间，避免使用系统默认临时表空间进行数据的排序操作

alter database default temporary tablespace temp100;

默认临时表空间限制，新的默认临时表空间生效之前旧的不能被删除，当前默认临时表空间不能offline，默认临时表空间不能使用永久表空间

表空间脱机联机切换        

不能offline的表空间：system、临时表空间

sysaux可以被offline

alter tablespace test100 offline

alter tablespace test100 online

表空间读写切换

只读表空间只能提供读服务。注意：只读表空间上的对象可以被删除，例如删除表：

drop table 表名

alter tablespace test100 read only

alter tablespace test100 read write

修改表空间大小

修改数据文件大小（大文件表空间）

alter database datafile '/db/big100.dbf' resize 200m;

添加数据文件

alter tablespace test100 add datafile '/db/test101.dbf' size 100m;

在线移动数据文件

注意：offline情况下移动，避免业务故障

将数据文件从一个文件位置移动到另外一个位置

alter tablespace test100 offline;

移动数据文件到新的位置

$ mv test100.dbf /oracle/

$ cd /oracle/

admin/               lost+found/          oracle/             

flash_recovery_area/ oraInventory/        test100.dbf 

alter tablespace test100 rename datafile '/db/test100.dbf' to '/oracle/test100.dbf';

alter tablespace test100 online;

创建非标准块表空间

标准块的大小由参数DB_BLOCK_SIZE指定。创建非标准块表空间就是创建块大小不等于DB_BLOCK_SIZE的表空间。如果要创建块大小是nKB的非标准块表空间，则创建这个表空间之前，必须用DB_nK_CACHE_SIZE创建一片数据库高速缓冲区。

SQL> show parameter db_block_size

创建高速缓冲区（如何确定高速缓冲区的大小）

alter system set db_16k_cache_size=8m scope=both;

在sql/plus中查看结果

SQL> show parameter cache;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_16k_cache_size                    big integer 8M

db_2k_cache_size                     big integer 0

……

创建非标准表空间

CREATE TABLESPACE TBS_TEST_16K DATAFILE '/DB/TBS16K.DBF' SIZE 50M BLOCKSIZE 16K AUTOEXTEND ON;

在此表空间中创建表

create table tab16k (id int) tablespace tbs16k;

针对全表扫描的表设置参数

db_file_multiblock_read_count此参数为oracle在全表扫描过程中一次IO所读取的数据块，对于存在大量全表扫描的数据仓库，此参数直接影响系统性能，此参数最大值为128，即oracle一次IO可读取128个块，可根据系统适当调整。

表空间，区、段的管理

区

主要介绍本地管理方式（LOCALLY MANAGED TABLESPACE），通过位图bitmap管理区

表空间中的每个数据文件有一个位图，位图存放在文件头，位图中的每个位（bit）与一个或者一组数据块（block）相对应。当分配一个区或者释放一个区的时候，Oracle将更新位图以反映这种改变。对于本地管理的表空间，Oracle不会更新数据字典，更不会产生回滚信息。

段

当用户进行插入、更新、删除的时候，段中数据块的空间会发生改变，块的状态也会发生改变。

段的自动管理，ASSM（auto Segment space management）

CREATE TABLESPACE TEST100 DATAFILE '/u01/test100.dbf' SIZE 10M SEGMENT SPACE MANAGEMENT AUTO;

管理UNDO表空间

UNDO段的作用：用于存储回滚段。在DML语句中，存储数据前映像，用于回滚（rollback）与恢复以及数据读的一致性。

切换UNDO表空间（可以使用oracle客户端的Enterprise Manager Console工具，进行效果查看）

如果目前正在使用的表空间为BUG或者应用问题不能及时释放UNDO表空间，那么必须进行切换，将旧的UNDO表空间删除

创建一个新的表空间

create undo tablespace undo02 datafile '/u01/undo02.dbf' size 100m;

切换

alter system set undo_tablespace=undo02 scope=both;

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDO02

undo_management指定undo手动或自动管理
undo_retention在undo表空间中保存undo数据时间

undo_tablespace指定oracle使用的undo表空间来自动管理undo数据

等待一段时间，可以删除旧的UNDO表空间

drop tablespace undotbs1 including contents datafiles;

UNDO表空间损坏恢复（案例）

数据库异常终止后，UNDO表空间损坏，启动失败

SQL> startup       

ORACLE instance started.

Total System Global Area  121634816 bytes

Fixed Size                  1218076 bytes

Variable Size              75500004 bytes

Database Buffers           41943040 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 9 - see DBWR trace file

ORA-01110: data file 9: '/db/undo02.dbf'

在pfile参数文件中使用_corrupted_rollback_segments=’_SYSSMU1$’, ’_SYSSMU2$’参数标记损坏的回滚段，利用pfile参数文件启动数据库时，oracle将跳过这些回滚段

startup pfile=/oracle/oracle/product/10.2.0/db_1/dbs/init”SID”.ora

对应的回滚段在日志文件alert和trace（*.trc）中查找

在数据库打开后

$ echo $ORACLE_HOME

/oracle/oracle/product/10.2.0/db_1

create spfile from pfile=$ORACLE_HOME/dbs/init”SID”.ora

导出用户数据并创建新的数据库，将导出数据导入新的数据库中。