一、表空间简介

A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.

建表空间的目的就是对数据进行分类管理和存放。

最少有两个系统表空间：

system：存放数据字典，在建库时建立。

sysaux：辅助表空间，存放从system分离一部分信息如对数据库的监控（oem），运行状态等信息。

此外还最好有

temp：临时表空间，用于数据排序等，不存放永久对象。

undo：存放undo数据块（数据块旧的镜像）。

注意以下几点：

1）表空间同一时间只能存在于一个数据库中。

2）表空间有一个或多个数据文件。

3） system表空间，手工建库不能太小

4） sysaux以数据库的监控为主，statspack，awr等，太小oem都安不上。

5）如果没建undo表空间，就会在system表空间建立undo段，应建立单独的undo表空间。

6）如果没建临时表空间，或默认表空间，都占system，应建立专门的表空间。

二、表空间管理方式

1、字典管理方式（dictionary）：使用数据字典来管理存储空间的分配，当表空间分配新的区、或者回收已分配的区时，oracle会对数据字典对应的表（uet$与fet$）进行查询、更新。且使用单线程（并发性差），速度慢，并且会产生回退和重做信息。（注意：在字典管理方式下，如果对某个表进行更新，这是会产生存储操作，而该操作又会产生回滚和重做操作，导致对回滚段和重做日志文件进行读写，从而产生存储管理操作，因此形成递归现象）

2、本地管理方式（local）：oracle 9i默认方式，表空间中区分配和区回收的管理信息都被存储在表空间的数据文件中，而与数据字典无关。表空间为每个数据文件维护一个位图结构，用于记录表空间的区分配情况。当表空间分配新的区，或回收已分配的区时，oracle会对文件中的位图进行更新（并发性强），所以不会产生回滚和重做信息。

--查看表空间信息

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

TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN

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

SYSTEM PERMANENT ONLINE LOCAL

UNDOTBS UNDO ONLINE LOCAL

SYSAUX PERMANENT ONLINE LOCAL

TEMPTS TEMPORARY ONLINE LOCAL

USERS PERMANENT ONLINE LOCAL

TEMPTS2 TEMPORARY ONLINE LOCAL

以上查询结果可知，表空间管理方式为local，注意若system表空间管理方式为local，则其它表空间都只能为local方式管理，若system表空间管理方式为dictionary，则其它表空间管理方式可随意。

--若system表空间为dictionary，可执行下面语句进行转换为local方式

SQL> exec dbms_space_admin.tablespace_migrate_to_local('SYSTEM');

BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;

*

ERROR at line 1:

ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrate

ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227

ORA-06512: at line 1

因当前表空间管理方式就是local，所以执行上面语句时会报错。

--查看数据字典所管理表空间的信息

SQL> select a.ts#,a.name,b.file#,b.name "File_name" ,c.block#,d.extent_management from v$tablespace a,v$datafile b,fet$ c,dba_tablespaces d where a.ts#=b.ts# and a.ts#=c.ts# and a.name=d.tablespace_name;

no rows selected

下面创建三个表空间，设置不同的表空间管理方式

--查看当前数据文件及表空间信息

SQL> col name for a50;

SQL> select file#,name from v$datafile;

FILE# NAME

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

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf

SQL> select TABLESPACE_NAME,CONTENTS,STATUS,EXTENT_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN

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

SYSTEM PERMANENT ONLINE LOCAL

UNDOTBS UNDO ONLINE LOCAL

SYSAUX PERMANENT ONLINE LOCAL

TEMPTS TEMPORARY ONLINE LOCAL

USERS PERMANENT ONLINE LOCAL

TEMPTS2 TEMPORARY ONLINE LOCAL

6 rows selected.

--创建本地管理（自动增长）方式表空间

SQL> create tablespace lxtbs1 datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf'

2 size 50m extent management local;

Tablespace created.

注意：红色字体内容为默认格式，可省略不写。

--创建数据字典管理方式表空间

SQL> create tablespace lxtbs2 datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf'

2 size 50m extent management dictionary;

create tablespace lxtbs2 datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf'

*

ERROR at line 1:

ORA-12913: Cannot create dictionary managed tablespace

注意：在此无法创建dictionary管理方式的表空间，因为system表空间为local管理方式，前面提到了，若system为local管理方式，其它表空间都必须是local管理方式

--创建本地管理（uniform即区大小不变）方式表空间

SQL> create tablespace lxtbs2 datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf'

2 size 50m uniform size 1m;

Tablespace created.

--创建本地管理（自定义区大小）方式表空间

SQL> create tablespace lxtbs3 datafile '/u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf'

2 size 50m default storage(initial 32k next 64k pctincrease 50);

Tablespace created.

--给以上三个表空间各创建一个表，并插入相同多的数据

SQL> create table lxtb1 tablespace lxtbs1 as select * from scott.emp;

Table created.

SQL> create table lxtb2 tablespace lxtbs2 as select * from scott.emp;

Table created.

SQL> create table lxtb3 tablespace lxtbs3 as select * from scott.emp;

Table created.

SQL> insert into lxtb1 select * from lxtb1;

14 rows created.

SQL> /

28 rows created.

SQL> /

56 rows created.

SQL> /

112 rows created.

SQL> /

224 rows created.

SQL> /

448 rows created.

SQL> /

896 rows created.

SQL> /

1792 rows created.

SQL> commit;

Commit complete.

SQL> insert into lxtb2 select * from lxtb2;

14 rows created.

SQL> /

28 rows created.

SQL> /

56 rows created.

SQL> /

112 rows created.

SQL> /

224 rows created.

SQL> /

/

448 rows created.

SQL>

896 rows created.

SQL> /

1792 rows created.

SQL> commit;

Commit complete.

SQL> insert into lxtb3 select * from lxtb3;

14 rows created.

SQL> /

28 rows created.

SQL> /

56 rows created.

SQL> /

112 rows created.

SQL> /

224 rows created.

SQL> /

448 rows created.

SQL> /

896 rows created.

SQL> /

1792 rows created.

SQL> commit;

Commit complete.

--查看刚创建的三张表，相应的extent信息

SQL> col segment_name for a10

SQL> select segment_name,bytes/1024 k,extents,blocks,initial_extent/1024 init,next_extent/1024 next,pct_increase from dba_segments where segment_name like 'LXTB%';

SEGMENT_NA K EXTENTS BLOCKS INIT NEXT PCT_INCREASE

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

LXTB1 256 4 32 64

LXTB2 1024 1 128 1024 1024 0

LXTB3 256 4 32 64

分析上面的查询结果，lxtb1为自动增长方式，初始化大小为64，下一个大小未知，增长幅度也未知。lxtb3为自定义方式，前面自定义init为32，next为64，pctincrease 50，但实际查询结果与lxtbs的自动增长方式一样，可知想自定义表空间增长方式，以区的管理形式是无法改变的（可控制段的管理方式来实现自定义增长方式）。lxtb2在lxtbs2表空间上，为uniform方式，此方式是oracle推荐方式，更利于回收空间，不会有太多的碎片（索引表空间建议为uniform方式）。自动增长方式虽然效率更高，但碎片较多。

三、undo表空间与临时表空间

3.1、Undo Tablespaces

3.1.1 undo表空间简介

1）undo表空间用来存放undo信息，一般是本地管理，且不能在undo表空间创建任何对象（表或索引）。

2）可以创建多个undo表空间，但处于active状态的只有一个。

3）system表空间也含有undo段，如果没建undo表空间就占system的，不推荐此做法。

注意：修改数据后，旧的镜像就放在undo段中了。而update时，server process会在databuffer中找到该记录的记录块，没有就从datafile中找，在修改之前，先放到undo段（active）状态，并在数据块头记录undo段中该数据块的位置，然后进行update。因此，当某session做update操作时，若未commit，则其它session查看这些数据时，还是未修改之前的数据，下面实例验证。

SQL> create table emp2 as select * from scott.emp;

Table created.

SQL> select count(1) from emp2;

COUNT(1)

----------

14

SQL> delete from emp2;

14 rows deleted.

SQL> select count(1) from emp2;

COUNT(1)

----------

0

--另开窗口，统计emp2表记录数

[oracle@gc1 ~]$ export ORACLE_SID=PROD

[oracle@gc1 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 11 19:15:53 2014

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select count(1) from emp2;

COUNT(1)

----------

14

由此可见，上面session1用户执行delete操作，未提交之前，session2统计的结果还是未删除之前的记录数。下面session1做提交操作，再看二边emp2表统计记录数结果。

session1用户统计结果

SQL> commit;

Commit complete.

SQL> select count(1) from emp2;

COUNT(1)

----------

0

session2用户再统计

SQL> select count(1) from emp2;

COUNT(1)

----------

0

修改操作commit之后，二边结果一致。

3.1.2 修改系统默认undo表空间

--查看系统当前undo参数信息

SQL> show parameter undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 5400

undo_tablespace string undotbs

--offline当前undo表空间（无法offline当前undo表空间）

SQL> alter tablespace undotbs offline;

alter tablespace undotbs offline

*

ERROR at line 1:

ORA-30042: Cannot offline the undo tablespace

--查看undo表空间及对应数据文件信息

SQL> col name for a50;

SQL> select file#,name from v$datafile;

FILE# NAME

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

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf

7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf

SQL> select TABLESPACE_NAME,CONTENTS,STATUS,EXTENT_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN

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

SYSTEM PERMANENT ONLINE LOCAL

UNDOTBS UNDO ONLINE LOCAL

SYSAUX PERMANENT ONLINE LOCAL

TEMPTS TEMPORARY ONLINE LOCAL

USERS PERMANENT ONLINE LOCAL

TEMPTS2 TEMPORARY ONLINE LOCAL

LXTBS1 PERMANENT ONLINE LOCAL

LXTBS2 PERMANENT ONLINE LOCAL

LXTBS3 PERMANENT ONLINE LOCAL

--新建一个undo表空间，为替换当前undo表空间所用

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf' size 50m;

SQL> select file#,name from v$datafile;

FILE# NAME

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

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf

7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf

8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf

8 rows selected.

SQL> select TABLESPACE_NAME,CONTENTS,STATUS,EXTENT_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN

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

SYSTEM PERMANENT ONLINE LOCAL

UNDOTBS UNDO ONLINE LOCAL

SYSAUX PERMANENT ONLINE LOCAL

TEMPTS TEMPORARY ONLINE LOCAL

USERS PERMANENT ONLINE LOCAL

TEMPTS2 TEMPORARY ONLINE LOCAL

LXTBS1 PERMANENT ONLINE LOCAL

LXTBS2 PERMANENT ONLINE LOCAL

LXTBS3 PERMANENT ONLINE LOCAL

UNDOTBS2 UNDO ONLINE LOCAL

注意：上面二个sql，红色部分为新增的undo表空间及对应数据文件

--修改系统默认undo表空间（spfile文件也做相应修改）

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> show parameter undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 5400

undo_tablespace string UNDOTBS2

修改spfile文件中默认undo表空间

[oracle@gc1 ~]$ cd $ORACLE_HOME/dbs

[oracle@gc1 dbs]$ ls

??7?.txt hc_BOB.dat hc_yj.dat initBOB.ora init.ora lkBOB lkYJ orapwyj spfilePROD.ora.bak

ha hc_PROD.dat hc_ZHEN.dat initdw.ora initPROD.ora lkPROD orapwPROD shutdown spfileyj.ora

[oracle@gc1 dbs]$ vi initPROD.ora

红圈部分内容，将原来的undo_tablespace=undotbs改为 undotbs2。

到此为止，修改系统默认undo表空间结束。

3.2 Temporary Tablespace

3.2.1 临时表空间简介

临时表空间主要用于排序、分组、索引等操作，在pga中的sort_area中排序，如以下一些操作：

1）索引create或rebuild

2）order by或group by

3）distinct操作

4）union或intersect或minus

5）sort-merge joins

6）analyze

临时表空间不能放持久化对象，推荐本地管理，并且uniform size。没临时表空间就占system表空间，临时表空间不能删除。

--查看排序参数信息

SQL> show parameter sort;

NAME TYPE VALUE

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

nls_sort string

sort_area_retained_size integer 0

sort_area_size integer 65536

3.2.2 修改系统默认临时表空间

--查看系统当前temp表空间信息

SQL> select TABLESPACE_NAME,CONTENTS,STATUS,EXTENT_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN

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

SYSTEM PERMANENT ONLINE LOCAL

UNDOTBS UNDO ONLINE LOCAL

SYSAUX PERMANENT ONLINE LOCAL

TEMPTS TEMPORARY ONLINE LOCAL

USERS PERMANENT ONLINE LOCAL

TEMPTS2 TEMPORARY ONLINE LOCAL

LXTBS1 PERMANENT ONLINE LOCAL

LXTBS2 PERMANENT ONLINE LOCAL

LXTBS3 PERMANENT ONLINE LOCAL

UNDOTBS2 UNDO ONLINE LOCA

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

FILE# NAME

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

1 /u01/app/oracle/oradata/PROD/disk5/temp02.dbf

SQL> col file_name for a50;

SQL> col tablespace_name for a20;

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_temp_files;

FILE_ID FILE_NAME TABLESPACE_NAME M

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

1 /u01/app/oracle/oradata/PROD/disk5/temp02.dbf TEMPTS2 50

--新建一个临时表空间，为切换默认临地表空间所用

SQL> create temporary tablespace tempts3 tempfile '/u01/app/oracle/oradata/PROD/disk5/temp03.dbf' size 50m;

Tablespace created.

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

TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN

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

SYSTEM PERMANENT ONLINE LOCAL

UNDOTBS UNDO ONLINE LOCAL

SYSAUX PERMANENT ONLINE LOCAL

TEMPTS TEMPORARY ONLINE LOCAL

USERS PERMANENT ONLINE LOCAL

TEMPTS2 TEMPORARY ONLINE LOCAL

LXTBS1 PERMANENT ONLINE LOCAL

LXTBS2 PERMANENT ONLINE LOCAL

LXTBS3 PERMANENT ONLINE LOCAL

UNDOTBS2 UNDO ONLINE LOCAL

TEMPTS3 TEMPORARY ONLINE LOCAL

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

FILE# NAME

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

1 /u01/app/oracle/oradata/PROD/disk5/temp02.dbf

2 /u01/app/oracle/oradata/PROD/disk5/temp03.dbf

--查看系统默认临时表空间（与查看字符集方式相同）

SQL> col PROPERTY_NAME for a30;

SQL> col PROPERTY_VALUE for a50;

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties;

PROPERTY_NAME PROPERTY_VALUE

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

DICT.BASE 2

DEFAULT_TEMP_TABLESPACE TEMPTS2

DEFAULT_PERMANENT_TABLESPACE USERS

DBTIMEZONE +08:00

DEFAULT_TBS_TYPE SMALLFILE

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET ZHS16GBK

PROPERTY_NAME PROPERTY_VALUE

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

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

PROPERTY_NAME PROPERTY_VALUE

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

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_RDBMS_VERSION 10.2.0.1.0

GLOBAL_DB_NAME PROD

EXPORT_VIEWS_VERSION 8

27 rows selected.

--修改系统默认临时表空间

SQL> alter database default temporary tablespace tempts3;

Database altered.

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties;

PROPERTY_NAME PROPERTY_VALUE

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

DICT.BASE 2

DEFAULT_TEMP_TABLESPACE TEMPTS3

DEFAULT_PERMANENT_TABLESPACE USERS

DBTIMEZONE +08:00

DEFAULT_TBS_TYPE SMALLFILE

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET ZHS16GBK

PROPERTY_NAME PROPERTY_VALUE

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

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

PROPERTY_NAME PROPERTY_VALUE

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

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_RDBMS_VERSION 10.2.0.1.0

GLOBAL_DB_NAME PROD

EXPORT_VIEWS_VERSION 8

27 rows selected.

3.2.3 创建临时表空间组

SQL> alter tablespace tempts2 tablespace group temp_grp;

Tablespace altered.

SQL> alter tablespace tempts3 tablespace group temp_grp;

Tablespace altered.

SQL> alter tablespace tempts tablespace group temp_grp;

Tablespace altered.

--设默认临时表空间为temp_grp表空间组

SQL> alter database default temporary tablespace temp_grp;

Database altered.

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties;

PROPERTY_NAME PROPERTY_VALUE

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

DICT.BASE 2

DEFAULT_TEMP_TABLESPACE TEMP_GRP

DEFAULT_PERMANENT_TABLESPACE USERS

DBTIMEZONE +08:00

DEFAULT_TBS_TYPE SMALLFILE

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET ZHS16GBK

PROPERTY_NAME PROPERTY_VALUE

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

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

PROPERTY_NAME PROPERTY_VALUE

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

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_RDBMS_VERSION 10.2.0.1.0

GLOBAL_DB_NAME PROD

EXPORT_VIEWS_VERSION 8

--查看临时表空间组信息

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

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

TEMP_GRP TEMPTS

TEMP_GRP TEMPTS2

TEMP_GRP TEMPTS3

--临时表空间退组

SQL> alter tablespace tempts tablespace group '';

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

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

TEMP_GRP TEMPTS2

TEMP_GRP TEMPTS3

总结：使用临时表空间组，有如下优点：

1）避免当临时表空间不足时所引起的磁盘排序问题；

2）当一个用户同时有多个会话时，可以使它们使用不同的临时表空间；

3）使得并行的服务器在单节点上，能使用多个临时表空间。

--指定用户临时表空间

SQL> select username,default_tablespace,temporary_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

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

SCOTT USERS TEMP_GRP

TOM USERS TEMP_GRP

TSMSYS USERS TEMP_GRP

DIP USERS TEMP_GRP

DBSNMP SYSAUX TEMP_GRP

OUTLN SYSTEM TEMP_GRP

SYS SYSTEM TEMP_GRP

SYSTEM SYSTEM TEMP_GRP

8 rows selected.

SQL> alter user scott temporary tablespace tempts2;

User altered.

SQL> select username,default_tablespace,temporary_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

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

SCOTT USERS TEMPTS2

TOM USERS TEMP_GRP

TSMSYS USERS TEMP_GRP

DIP USERS TEMP_GRP

DBSNMP SYSAUX TEMP_GRP

OUTLN SYSTEM TEMP_GRP

SYS SYSTEM TEMP_GRP

SYSTEM SYSTEM TEMP_GRP

8 rows selected.

四、表空间的只读与脱机

--查看系统当前表空间信息及数据文件信息

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME M

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

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50

7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 50

8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50

8 rows selected.

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

TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN

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

SYSTEM PERMANENT ONLINE LOCAL

UNDOTBS UNDO ONLINE LOCAL

SYSAUX PERMANENT ONLINE LOCAL

TEMPTS TEMPORARY ONLINE LOCAL

USERS PERMANENT ONLINE LOCAL

TEMPTS2 TEMPORARY ONLINE LOCAL

LXTBS1 PERMANENT ONLINE LOCAL

LXTBS2 PERMANENT ONLINE LOCAL

LXTBS3 PERMANENT ONLINE LOCAL

UNDOTBS2 UNDO ONLINE LOCAL

TEMPTS3 TEMPORARY ONLINE LOCAL

11 rows selected.

--查看数据文件的scn号

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

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

1 540731

2 540731

3 540731

4 540731

5 571548

6 571701

7 571856

8 574895

8 rows selected.

--生成系统全局检查点，使数据文件scn一致

SQL> alter system checkpoint;

System altered.

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

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

1 579785

2 579785

3 579785

4 579785

5 579785

6 579785

7 579785

8 579785

--设置lxtbs3表空间为只读（对应数据文件为7）

SQL> alter tablespace lxtbs3 read only;

Tablespace altered.

SQL> alter system checkpoint;

System altered.

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

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

1 580049

2 580049

3 580049

4 580049

5 580049

6 580049

7 580038

8 580049

8 rows selected.

SQL> select * from lxtb3 where rownum<=3;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

--设置表空间为读写

SQL> alter tablespace lxtbs3 read write;

Tablespace altered.

SQL> alter system checkpoint;

System altered.

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

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

1 580080

2 580080

3 580080

4 580080

5 580080

6 580080

7 580080

8 580080

8 rows selected.

--设置表空间脱机（offline）

SQL> alter tablespace lxtbs3 offline;

Tablespace altered.

SQL> alter system checkpoint;

System altered.

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

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

1 580196

2 580196

3 580196

4 580196

5 580196

6 580196

7 580185

8 580196

8 rows selected.

SQL> select * from lxtb3 where rownum<=3;

select * from lxtb3 where rownum<=3

*

ERROR at line 1:

ORA-00376: file 7 cannot be read at this time

ORA-01110: data file 7: '/u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf'

SQL> alter tablespace lxtbs3 online;

Tablespace altered.

SQL> alter system checkpoint;

System altered.

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

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

1 580225

2 580225

3 580225

4 580225

5 580225

6 580225

7 580225

8 580225

8 rows selected.

--offline immediate（online之前要先recover tablespace，因为正常来说，offline会生产全局检查点，如果immediate立急脱机的话，就不会产生全局检查点）

SQL> alter tablespace lxtbs3 offline immediate;

Tablespace altered.

SQL> alter tablespace lxtbs3 online;

alter tablespace lxtbs3 online

*

ERROR at line 1:

ORA-01113: file 7 needs media recovery

ORA-01110: data file 7: '/u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf'

SQL> recover tablespace lxtbs3;

Media recovery complete.

SQL> alter tablespace lxtbs3 online;

Tablespace altered.

--datafile offline类似于tablespace offline immediate，online之前需先recover

SQL> alter database datafile 7 offline;

Database altered.

SQL> alter database datafile 7 online;

alter database datafile 7 online

*

ERROR at line 1:

ORA-01113: file 7 needs media recovery

ORA-01110: data file 7: '/u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf'

SQL> recover datafile 7;

Media recovery complete.

SQL> alter database datafile 7 online;

Database altered.

五、修改表空间大小

三种方式可修改表空间大小：

1）resize重置数据文件大小（一般是往大扩，往小缩，要实际数据存储大于设置大小会有问题）；

2）设置数据文件自动增长，且最大值为无限大（此设置无法满足所有要求，因为文件大小会受操作系统限制，所以会有下面的第三种情况）；

3）添加数据文件，从而实现扩大表空间目的。

--查看当前表空间大小

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME M

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

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50

7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 50

8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50

--方式一：重置数据文件大小

SQL> alter database datafile 7 resize 60M;

Database altered.

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME M

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

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50

7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60

8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50

8 rows selected.

--方式二：设置数据文件自动增长，且最大值为无限大

SQL> alter database datafile 7 autoextend on next 10M maxsize unlimited;

Database altered.

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME M

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

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50

7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60

8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50

8 rows selected.

--方式三：添加数据文件

SQL> alter tablespace lxtbs3 add datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf' size 50m;

Tablespace altered.

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME M

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

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50

7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60

8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50

9 /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf LXTBS3 50

9 rows selected.

六、表空间迁移

方式一：offline表空间，操作系统cp数据文件，rename数据文件

例：将 /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf 数据文件迁移至disk5下

--查看表空间对应数据文件信息

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME M

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

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50

7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60

8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50

9 /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf LXTBS3 50

9 rows selected.

--执行表空间脱机

SQL> alter tablespace lxtbs3 offline;

Tablespace altered.

--执行数据文件迁移（迁移失败，因为操作系统要先复制数据文件至新存放地址）

SQL> alter tablespace lxtbs3 rename datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf';

alter tablespace lxtbs3 rename datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf'

*

ERROR at line 1:

ORA-01525: error in renaming data files

ORA-01141: error renaming data file 9 - new file '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf'

not found

ORA-01110: data file 9: '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

--操作系统复制数据文件至新存放地址

[oracle@gc1 ~]$ cp /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf /u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf

[oracle@gc1 ~]$ cd /u01/app/oracle/oradata/PROD/disk5/

[oracle@gc1 disk5]$ ls

lstbs03.dbf temp01.dbf temp02.dbf temp03.dbf undotbs02.dbf

--再次执行数据文件迁移

SQL> alter tablespace lxtbs3 rename datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf';

Tablespace altered.

--执行表空间online

SQL> alter tablespace lxtbs3 online;

Tablespace altered.

--迁移成功后，删除原数据文件，并查看最新的表空间对应数据文件信息

[oracle@gc1 disk5]$ rm /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME M

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

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50

7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60

8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50

9 /u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf LXTBS3 50

方式二：关库并启至mount状态，操作系统cp数据文件，rename 数据文件

例：将上面迁移的数据文件迁移回原位置

--关库并启库至mount状态

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 419430400 bytes

Fixed Size 1219760 bytes

Variable Size 121635664 bytes

Database Buffers 293601280 bytes

Redo Buffers 2973696 bytes

Database mounted.

--操作系统复制要迁移的数据文件至新存放位置

[oracle@gc1 disk3]$ cp /u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf

--执行数据文件迁移（即重命名数据文件）

SQL> alter tablespace lxtbs3 rename datafile '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf';

alter tablespace lxtbs3 rename datafile '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf'

*

ERROR at line 1:

ORA-01109: database not open

SQL> alter database rename file '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf';

Database altered.

注意：最开始尝试用第一种方式的alter tablespace rename datafile的形式重命名数据文件失败，因为此语句必须在open时才可执行，mount状态只能用alter database rename file的方式

--开库并删除原数据文件

SQL> alter database open;

Database altered.

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME M

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

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50

7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60

8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50

9 /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf LXTBS3 50

9 rows selected.

[oracle@gc1 disk3]$ rm /u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf

七、删除表空间

SQL> drop tablespace lxtbs3 including contents and datafiles;

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME M

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

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50

8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50

7 rows selected.

八、OMF管理表空间方式

OMF管理方式，就是设置db_create_file_dest参数（系统默认数据文件存放位置），然后创建表空间时，不用指定数据文件及大小，系统默认会加上这些信息，此方式不推荐使用。

--查看create参数信息

SQL> show parameter create;

NAME TYPE VALUE

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

create_bitmap_area_size integer 8388608

create_stored_outlines string

db_create_file_dest string

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

--设置db_create_file_dest参数

SQL> alter system set db_create_file_dest ='/u01/app/oracle/oradata/PROD/disk3';

System altered.

SQL> create tablespace lxtbs3;

Tablespace created.

--查看lxtbs3表空间对应数据文件信息

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME M

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

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325

5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50

6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50

7 /u01/app/oracle/oradata/PROD/disk3/PROD/datafile/o LXTBS3 100

1_mf_lxtbs3_9ntlsdv8_.dbf

8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50

8 rows selected.

--删除lxtbs3表空间

SQL> drop tablespace lxtbs3 including contents and datafiles;

Tablespace dropped.

九、使用非标准块的表空间

oracle支持五种块，2k,4k,8k,16k,32k

oltp 8k足矣，linux和windows不支持 32K块，unix支持32k块。

--查看参数缓存信息

SQL> show parameter cache;

NAME TYPE VALUE

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

db_16k_cache_size big integer 0

db_2k_cache_size big integer 0

db_32k_cache_size big integer 0

db_4k_cache_size big integer 0

db_8k_cache_size big integer 0

db_cache_advice string ON

db_cache_size big integer 0

db_keep_cache_size big integer 0

db_recycle_cache_size big integer 0

object_cache_max_size_percent integer 10

object_cache_optimal_size integer 102400

NAME TYPE VALUE

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

session_cached_cursors integer 20

--创建16K块表空间（失败，因为不能与之前的8k共享缓存，需设置16K的缓存区大小）

SQL> create tablespace lxtbs3 datafile '/u01/app/oracle/oradata/PROD/disk3/lxtbs03.dbf' size 50m blocksize 16k;

create tablespace lxtbs3 datafile '/u01/app/oracle/oradata/PROD/disk3/lxtbs03.dbf' size 50m blocksize 16k

*

ERROR at line 1:

ORA-29339: tablespace block size 16384 does not match configured block sizes

--设置16k块缓存区大小

SQL> alter system set db_16k_cache_size=40m;

System altered.

SQL> show parameter cache;

NAME TYPE VALUE

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

db_16k_cache_size big integer 40M

db_2k_cache_size big integer 0

db_32k_cache_size big integer 0

db_4k_cache_size big integer 0

db_8k_cache_size big integer 0

db_cache_advice string ON

db_cache_size big integer 0

db_keep_cache_size big integer 0

db_recycle_cache_size big integer 0

object_cache_max_size_percent integer 10

object_cache_optimal_size integer 102400

NAME TYPE VALUE

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

session_cached_cursors integer 20

--再次创建16K块表空间

SQL> create tablespace lxtbs3 datafile '/u01/app/oracle/oradata/PROD/disk3/lxtbs03.dbf' size 50m blocksize 16k;

Tablespace created.

--查看表空间块大小信息

SQL> select tablespace_name,contents,status,block_size from dba_tablespaces;

TABLESPACE_NAME CONTENTS STATUS BLOCK_SIZE

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

SYSTEM PERMANENT ONLINE 8192

UNDOTBS UNDO ONLINE 8192

SYSAUX PERMANENT ONLINE 8192

TEMPTS TEMPORARY ONLINE 8192

USERS PERMANENT ONLINE 8192

TEMPTS2 TEMPORARY ONLINE 8192

LXTBS1 PERMANENT ONLINE 8192

LXTBS2 PERMANENT ONLINE 8192

LXTBS3 PERMANENT ONLINE 16384

UNDOTBS2 UNDO ONLINE 8192

TEMPTS3 TEMPORARY ONLINE 8192

11 rows selected.

注意，以上操作之后，若是pfile文件启动，还需修改pfile中的db_16k_cache_size参数信息，因为前面设置的 alter system set db_16k_cache_size=40m;要永久生效必须改初始化参数文件，否则重启后，因创建了16K块的表空间，但未设置其对应的缓存区，未报空间不足错。

--修改pfile初始化参数文件

[oracle@gc1 dbs]$ vi initPROD.ora

十、bigfile表空间

bigfile表空间最多支持4g个数据块，如果是8k，可达32T。

bigfile在一个表空间只能建立一个数据文件（8k的block，datafile maxsize可以32T），可以简化对数据文件管理，适合于海量数据，但复制备份复杂，不推荐使用（如果尽是因为便于数据文件管理，可采用asm方式管理文件）。操作系统默认是smallfile表空间。

SQL> create bigfile tablespace bigtbs datafile '/u01/app/oracle/oradata/PROD/disk3/bigtbs01.dbf' size 50m autoextend

2 on next 10m maxsize 4t;

Tablespace created.

SQL> alter tablespace bigtbs add datafile '/u01/app/oracle/oradata/PROD/disk3/bigtbs11.dbf' size 50m;

alter tablespace bigtbs add datafile '/u01/app/oracle/oradata/PROD/disk3/bigtbs11.dbf' size 50m

*

ERROR at line 1:

ORA-32771: cannot add file to bigfile tablespace

ITPub博客

表空间和数据文件管理