【oracle11g，14】表空间管理3：临时表空间，表空间的脱机和只读，数据文件迁移，更改表空间数据文件的大小，表空间数据文件的迁移，使用非标准块的表空间，bigfile 表空间

2022-10-08 15:21:21



一.临时表空间：
如果临时表空间不足会报ora-1652错误。


二.什么时候使用临时表空间： 排序和分组
索引create或rebuild
order by 或group by
distinct 操作
union或intersect或minus
sort-merge joins
analyze
用于排序、分组、索引等操作，在pga中的sort_area中排序，会将排序的中间结果存放到临时表空间中，如果想提高排序的效率可以提高sort_area_size参数值。
临时表空间不能存放持久化对象，推荐本地管理，并且uniform size。
没有临时表空间时，会占用system空间。


#排序区
SQL> show parameter  sort_area_size                       

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
sort_area_size                       integer     65536
三.建立临时表空间：

1.查看临时文件(两种)
SQL> select file#,name  from v$tempfile;

     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/prod/disk5/temp01.dbf

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

2.查看默认临时表空间：

SQL> select property_name , property_value from database_properties;


3.创建临时表空间
SQL> create  temporary tablespace tempts2 tempfile ‘/u01/app/oracle/oradata/prod/disk5/temp02.dbf‘ size 50m;

4.切换默认临时表空间
SQL> alter database default temporary tablespace tempts2;

四.临时表空间组：

1.临时表空间的好处
  避免当临时表空间不足时所引起的磁盘排序问题
  当一个用户同时有多个会话时，可以使得他们使用不同的临时表空间
  使得并行的服务器在单节点上，能使用多个临时表空间。

2.将临时表空间添加到临时表空间组：

SQL> alter tablespace tempts tablespace group temp_grp;

Tablespace altered.

SQL> alter tablespace tempts2 tablespace group temp_grp;

Tablespace altered.
#设置默认临时表空间为表空间组
SQL> alter database default temporary tablespace temp_grp;

Database altered.
3.指定用户用户使用指定的临时表空间
#查询用使用的默认表空间和临时表空间
SQL> select username,default_tablespace, temporary_tablespace  from dba_users;

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
OUTLN                          SYSTEM                         TEMP_GRP
SYS                            SYSTEM                         TEMP_GRP
SYSTEM                         SYSTEM                         TEMP_GRP
SCOTT                          USERS                          TEMP_GRP
APPQOSSYS                      SYSAUX                         TEMP_GRP
DBSNMP                         SYSAUX                         TEMP_GRP
DIP                            USERS                          TEMP_GRP
ORACLE_OCM                     USERS                          TEMP_GRP

8 rows selected.
#指定scott使用tempts
SQL> alter user scott temporary tablespace tempts;

User altered.

SQL> select username,default_tablespace, temporary_tablespace  from dba_users;

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
OUTLN                          SYSTEM                         TEMP_GRP
SYS                            SYSTEM                         TEMP_GRP
SYSTEM                         SYSTEM                         TEMP_GRP
SCOTT                          USERS                          TEMPTS
APPQOSSYS                      SYSAUX                         TEMP_GRP
DBSNMP                         SYSAUX                         TEMP_GRP
DIP                            USERS                          TEMP_GRP
ORACLE_OCM                     USERS                          TEMP_GRP

8 rows selected.

4.创建临时表空间并添加到指定的临时表空间组中：


五.表空间的只读和脱机：
1.查看表空间状态

SQL> select  tablespace_name,contents ,status from dba_tablespaces;

TABLESPACE_NAME CONTENTS  STATUS
--------------- --------- ---------
SYSTEM          PERMANENT ONLINE
SYSAUX          PERMANENT ONLINE
UNDOTBS         UNDO      ONLINE
USERS           PERMANENT ONLINE
TEMPTS2         TEMPORARY ONLINE
LXTBS1          PERMANENT ONLINE
LXTBS2          PERMANENT ONLINE
LXTBS3          PERMANENT ONLINE
TEMPTS          TEMPORARY ONLINE

9 rows selected.

2.只读
SQL> alter tablespace lxtbs1 read only;

Tablespace altered.

SQL>  select  tablespace_name,contents ,status from dba_tablespaces;

TABLESPACE_NAME CONTENTS  STATUS
--------------- --------- ---------
SYSTEM          PERMANENT ONLINE
SYSAUX          PERMANENT ONLINE
UNDOTBS         UNDO      ONLINE
USERS           PERMANENT ONLINE
TEMPTS2         TEMPORARY ONLINE
LXTBS1          PERMANENT READ ONLY
LXTBS2          PERMANENT ONLINE
LXTBS3          PERMANENT ONLINE
TEMPTS          TEMPORARY ONLINE

9 rows selected.

SQL> alter system checkpoint;

System altered.

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             550181
         2             550181
         3             550181
         4             550181
         5             550151
         6             550181
         7             550181

7 rows selected.

SQL> alter tablespace lxtbs1 read write;

Tablespace altered.
3.脱机：

一般在表空间的数据文件迁移时，需要脱机。
脱机会写脏块， 如果使用offline immediate 不会写脏块，online时需要恢复。


SQL> alter tablespace lxtbs1 offline;

Tablespace altered.

SQL> select  tablespace_name,contents ,status from dba_tablespaces;

TABLESPACE_NAME CONTENTS  STATUS
--------------- --------- ---------
SYSTEM          PERMANENT ONLINE
SYSAUX          PERMANENT ONLINE
UNDOTBS         UNDO      ONLINE
USERS           PERMANENT ONLINE
TEMPTS2         TEMPORARY ONLINE
LXTBS1          PERMANENT OFFLINE
LXTBS2          PERMANENT ONLINE
LXTBS3          PERMANENT ONLINE
TEMPTS          TEMPORARY ONLINE

9 rows selected.

SQL>  alter tablespace lxtbs1 online;

Tablespace altered.

②.立即脱机的案例：
SQL> alter tablespace lxtbs1 offline immediate;

Tablespace altered.

SQL> alter tablespace lxtbs1 online;
alter tablespace lxtbs1 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/prod/disk3/lxtbs1.dbf‘


SQL> recover tablespace lxtbs1;
Media recovery complete.
SQL> alter tablespace lxtbs1 online;

Tablespace altered.


4.脱机数据文件 （相当于offline immediate）

SQL> alter database datafile 5 offline;

Database altered.

SQL>  alter database datafile 5 online;
 alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/prod/disk3/lxtbs1.dbf‘

SQL>  recover datafile 5 ;
Media recovery complete.
SQL>  alter database datafile 5 online;

Database altered.

六.更改表空间数据文件的大小：（三种方法）
1.是用resize改大小 ，一般往大的改。
SQL> alter database datafile 7 resize 60m;
或者
SQL> ALTER DATABASE DATAFILE ‘/remorse/sales.dbf‘ RESIZE 150M; 
2.自动扩展 :单个文件受操作系统支持文件的大小的限制。
SQL> alter database datafile 7 autoextend on next 10m maxsize unlimited;

3.给表空间添加数据文件（较好）
alter tablespace lxtbs3 add datafile ‘/u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf‘ size 50m;

例：给表空间添加数据文件
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME BYTES/1024/1024
---------- -------------------------------------------------- --------------- ---------------
         4 /u01/app/oracle/oradata/prod/disk3/users01.dbf     USERS              500
         3 /u01/app/oracle/oradata/prod/disk3/undotbs01.dbf   UNDOTBS            249
         2 /u01/app/oracle/oradata/prod/disk3/sysaux01.dbf    SYSAUX             325
         1 /u01/app/oracle/oradata/prod/disk3/system01.dbf    SYSTEM             325
         5 /u01/app/oracle/oradata/prod/disk3/lxtbs1.dbf      LXTBS1              50
         6 /u01/app/oracle/oradata/prod/disk3/lxtbs2.dbf      LXTBS2              50
         7 /u01/app/oracle/oradata/prod/disk3/lxtbs3.dbf      LXTBS3              50

7 rows selected.

#给lxtbs3 添加数据文件
SQL> alter tablespace lxtbs3 add datafile ‘/u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf‘ size 50m;

Tablespace altered.

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

   FILE_ID FILE_NAME                                          TABLESPACE_NAME BYTES/1024/1024
---------- -------------------------------------------------- --------------- ---------------
         4 /u01/app/oracle/oradata/prod/disk3/users01.dbf     USERS              500
         3 /u01/app/oracle/oradata/prod/disk3/undotbs01.dbf   UNDOTBS            249
         2 /u01/app/oracle/oradata/prod/disk3/sysaux01.dbf    SYSAUX             325
         1 /u01/app/oracle/oradata/prod/disk3/system01.dbf    SYSTEM             325
         5 /u01/app/oracle/oradata/prod/disk3/lxtbs1.dbf      LXTBS1              50
         6 /u01/app/oracle/oradata/prod/disk3/lxtbs2.dbf      LXTBS2              50
         7 /u01/app/oracle/oradata/prod/disk3/lxtbs3.dbf      LXTBS3              50
         8 /u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf     LXTBS3              50

8 rows selected.

七.表空间数据文件的迁移：(两种方法：)
1.方法一：使用脱机：无需关库
#脱机
SQL> alter tablespace lxtbs3 offline;
#修改数据文件位置
cp /u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf /u01/app/oracle/oradata/prod/disk4/lxtbs33.dbf

#修改控制文件里的数据文件的位置.
SQL> alter tablespace lxtbs3 rename datafile ‘/u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf‘ to ‘/u01/app/oracle/oradata/prod/disk4/lxtbs33.dbf‘;

#online
SQL> alter tablespace lxtbs3 online;

Tablespace altered.

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

   FILE_ID FILE_NAME                                          TABLESPACE_NAME BYTES/1024/1024
---------- -------------------------------------------------- --------------- ---------------
         4 /u01/app/oracle/oradata/prod/disk3/users01.dbf     USERS              500
         3 /u01/app/oracle/oradata/prod/disk3/undotbs01.dbf   UNDOTBS            249
         2 /u01/app/oracle/oradata/prod/disk3/sysaux01.dbf    SYSAUX             325
         1 /u01/app/oracle/oradata/prod/disk3/system01.dbf    SYSTEM             325
         5 /u01/app/oracle/oradata/prod/disk3/lxtbs1.dbf      LXTBS1              50
         6 /u01/app/oracle/oradata/prod/disk3/lxtbs2.dbf      LXTBS2              50
         7 /u01/app/oracle/oradata/prod/disk3/lxtbs3.dbf      LXTBS3              50
         8 /u01/app/oracle/oradata/prod/disk4/lxtbs33.dbf     LXTBS3              50

8 rows selected.
#删除原始的数据文件
[oracle@master ~]$ rm  /u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf
2.方法二：关库copy，然后改名：


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@master ~]$ cp /u01/app/oracle/oradata/prod/disk4/lxtbs33.dbf /u01/app/oracle/oradata/prod/disk4/lxtbs3.dbf


SQL> startup mount;
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             281020828 bytes
Database Buffers          130023424 bytes
Redo Buffers                6103040 bytes
Database mounted.

SQL> alter database rename file ‘/u01/app/oracle/oradata/prod/disk4/lxtbs33.dbf‘ to ‘/u01/app/oracle/oradata/prod/disk4/lxtbs3.dbf‘;

Database altered.

SQL> alter database open ;
Database altered.

八.删除表空间

#删除表空间，包含数据文件
SQL> drop tablespace lxtbs3 including contents and datafiles;
 


九.OMF
详见：【dba,25】OMF（oracle managed files）
omf 一般在rac中使用到，单实例数据库不推荐使用


十.使用非标准块的表空间：

oracle支持五种块：2k,4k,8k,16k,32k
oltp 8k 足矣。


1.如果使用非标准块的表空间， 由于缓冲区(buffer cache)不能通用，所以要设置表空间缓冲区，下面是16k的表空间的缓冲区,
SQL> alter system set db_16k_cache_size=40m;
如果数据库启动时报空间不足的错误，就是因为没有设置非标准块的缓冲区。


2. 案例：
SQL> select tablespace_Name, contents,status, block_size from dba_tablespaces;

TABLESPACE_NAME                CONTENTS  STATUS    BLOCK_SIZE
------------------------------ --------- --------- ----------
SYSTEM                         PERMANENT ONLINE          8192
SYSAUX                         PERMANENT ONLINE          8192
UNDOTBS                        UNDO      ONLINE          8192
USERS                          PERMANENT ONLINE          8192
TEMPTS2                        TEMPORARY ONLINE          8192
LXTBS1                         PERMANENT ONLINE          8192
LXTBS2                         PERMANENT ONLINE          8192
LXTBS3                         PERMANENT ONLINE          8192
TEMPTS                         TEMPORARY ONLINE          8192

9 rows selected.


SQL> alter system set db_16k_cache_size=40m;

System altered.

SQL> create tablespace lxtbs4 datafile ‘/u01/app/oracle/oradata/prod/disk3/lxtbs04.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
SYSAUX                         PERMANENT ONLINE          8192
UNDOTBS                        UNDO      ONLINE          8192
USERS                          PERMANENT ONLINE          8192
TEMPTS2                        TEMPORARY ONLINE          8192
LXTBS1                         PERMANENT ONLINE          8192
LXTBS2                         PERMANENT ONLINE          8192
LXTBS3                         PERMANENT ONLINE          8192
TEMPTS                         TEMPORARY ONLINE          8192
LXTBS4                         PERMANENT ONLINE         16384

10 rows selected.
十一. bigfile 表空间：
1.
bigfile 表空间最多支持4g个数据库，如果8k，可达32T;
bigfile:在一个表空间只能建立一个数据文件，可以简化对数据文件管理，适合于海量数据。数据库默认是smallfile表空间。

2.创建bigfile表空间
SQL> create bigfile tablespace bigtbs datafile ‘/u01/app/oracle/oradata/prod/disk3/lxtbs11.dbf‘ size 50m autoextend on next 10m maxsize 4t;

SQL> alter tablespace bigtbs add datafile ‘/u01/app/oracle/oradata/prod/disk3/bigtbs02.dbf‘ size 50m;
alter tablespace bigtbs add datafile ‘/u01/app/oracle/oradata/prod/disk3/bigtbs02.dbf‘ size 50m
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace

3. bigfile有缺点： 一般生产库不建议适应bigfile 表空间。

优点：简化管理 
缺点：备份恢复不好，























一.临时表空间：
如果临时表空间不足会报ora-1652错误。


二.什么时候使用临时表空间： 排序和分组
索引create或rebuild
order by 或group by
distinct 操作
union或intersect或minus
sort-merge joins
analyze
用于排序、分组、索引等操作，在pga中的sort_area中排序，会将排序的中间结果存放到临时表空间中，如果想提高排序的效率可以提高sort_area_size参数值。
临时表空间不能存放持久化对象，推荐本地管理，并且uniform size。
没有临时表空间时，会占用system空间。


#排序区
SQL> show parameter  sort_area_size                       

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
sort_area_size                       integer     65536
三.建立临时表空间：

1.查看临时文件(两种)
SQL> select file#,name  from v$tempfile;

     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/prod/disk5/temp01.dbf

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

2.查看默认临时表空间：

SQL> select property_name , property_value from database_properties;


3.创建临时表空间
SQL> create  temporary tablespace tempts2 tempfile ‘/u01/app/oracle/oradata/prod/disk5/temp02.dbf‘ size 50m;

4.切换默认临时表空间
SQL> alter database default temporary tablespace tempts2;

四.临时表空间组：

1.临时表空间的好处
  避免当临时表空间不足时所引起的磁盘排序问题
  当一个用户同时有多个会话时，可以使得他们使用不同的临时表空间
  使得并行的服务器在单节点上，能使用多个临时表空间。

2.将临时表空间添加到临时表空间组：

SQL> alter tablespace tempts tablespace group temp_grp;

Tablespace altered.

SQL> alter tablespace tempts2 tablespace group temp_grp;

Tablespace altered.
#设置默认临时表空间为表空间组
SQL> alter database default temporary tablespace temp_grp;

Database altered.
3.指定用户用户使用指定的临时表空间
#查询用使用的默认表空间和临时表空间
SQL> select username,default_tablespace, temporary_tablespace  from dba_users;

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
OUTLN                          SYSTEM                         TEMP_GRP
SYS                            SYSTEM                         TEMP_GRP
SYSTEM                         SYSTEM                         TEMP_GRP
SCOTT                          USERS                          TEMP_GRP
APPQOSSYS                      SYSAUX                         TEMP_GRP
DBSNMP                         SYSAUX                         TEMP_GRP
DIP                            USERS                          TEMP_GRP
ORACLE_OCM                     USERS                          TEMP_GRP

8 rows selected.
#指定scott使用tempts
SQL> alter user scott temporary tablespace tempts;

User altered.

SQL> select username,default_tablespace, temporary_tablespace  from dba_users;

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
OUTLN                          SYSTEM                         TEMP_GRP
SYS                            SYSTEM                         TEMP_GRP
SYSTEM                         SYSTEM                         TEMP_GRP
SCOTT                          USERS                          TEMPTS
APPQOSSYS                      SYSAUX                         TEMP_GRP
DBSNMP                         SYSAUX                         TEMP_GRP
DIP                            USERS                          TEMP_GRP
ORACLE_OCM                     USERS                          TEMP_GRP

8 rows selected.

4.创建临时表空间并添加到指定的临时表空间组中：


五.表空间的只读和脱机：
1.查看表空间状态

SQL> select  tablespace_name,contents ,status from dba_tablespaces;

TABLESPACE_NAME CONTENTS  STATUS
--------------- --------- ---------
SYSTEM          PERMANENT ONLINE
SYSAUX          PERMANENT ONLINE
UNDOTBS         UNDO      ONLINE
USERS           PERMANENT ONLINE
TEMPTS2         TEMPORARY ONLINE
LXTBS1          PERMANENT ONLINE
LXTBS2          PERMANENT ONLINE
LXTBS3          PERMANENT ONLINE
TEMPTS          TEMPORARY ONLINE

9 rows selected.

2.只读
SQL> alter tablespace lxtbs1 read only;

Tablespace altered.

SQL>  select  tablespace_name,contents ,status from dba_tablespaces;

TABLESPACE_NAME CONTENTS  STATUS
--------------- --------- ---------
SYSTEM          PERMANENT ONLINE
SYSAUX          PERMANENT ONLINE
UNDOTBS         UNDO      ONLINE
USERS           PERMANENT ONLINE
TEMPTS2         TEMPORARY ONLINE
LXTBS1          PERMANENT READ ONLY
LXTBS2          PERMANENT ONLINE
LXTBS3          PERMANENT ONLINE
TEMPTS          TEMPORARY ONLINE

9 rows selected.

SQL> alter system checkpoint;

System altered.

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             550181
         2             550181
         3             550181
         4             550181
         5             550151
         6             550181
         7             550181

7 rows selected.

SQL> alter tablespace lxtbs1 read write;

Tablespace altered.
3.脱机：

一般在表空间的数据文件迁移时，需要脱机。
脱机会写脏块， 如果使用offline immediate 不会写脏块，online时需要恢复。


SQL> alter tablespace lxtbs1 offline;

Tablespace altered.

SQL> select  tablespace_name,contents ,status from dba_tablespaces;

TABLESPACE_NAME CONTENTS  STATUS
--------------- --------- ---------
SYSTEM          PERMANENT ONLINE
SYSAUX          PERMANENT ONLINE
UNDOTBS         UNDO      ONLINE
USERS           PERMANENT ONLINE
TEMPTS2         TEMPORARY ONLINE
LXTBS1          PERMANENT OFFLINE
LXTBS2          PERMANENT ONLINE
LXTBS3          PERMANENT ONLINE
TEMPTS          TEMPORARY ONLINE

9 rows selected.

SQL>  alter tablespace lxtbs1 online;

Tablespace altered.

②.立即脱机的案例：
SQL> alter tablespace lxtbs1 offline immediate;

Tablespace altered.

SQL> alter tablespace lxtbs1 online;
alter tablespace lxtbs1 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/prod/disk3/lxtbs1.dbf‘


SQL> recover tablespace lxtbs1;
Media recovery complete.
SQL> alter tablespace lxtbs1 online;

Tablespace altered.


4.脱机数据文件 （相当于offline immediate）

SQL> alter database datafile 5 offline;

Database altered.

SQL>  alter database datafile 5 online;
 alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/prod/disk3/lxtbs1.dbf‘

SQL>  recover datafile 5 ;
Media recovery complete.
SQL>  alter database datafile 5 online;

Database altered.

六.更改表空间数据文件的大小：（三种方法）
1.是用resize改大小 ，一般往大的改。
SQL> alter database datafile 7 resize 60m;
或者
SQL> ALTER DATABASE DATAFILE ‘/remorse/sales.dbf‘ RESIZE 150M; 
2.自动扩展 :单个文件受操作系统支持文件的大小的限制。
SQL> alter database datafile 7 autoextend on next 10m maxsize unlimited;

3.给表空间添加数据文件（较好）
alter tablespace lxtbs3 add datafile ‘/u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf‘ size 50m;

例：给表空间添加数据文件
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME BYTES/1024/1024
---------- -------------------------------------------------- --------------- ---------------
         4 /u01/app/oracle/oradata/prod/disk3/users01.dbf     USERS              500
         3 /u01/app/oracle/oradata/prod/disk3/undotbs01.dbf   UNDOTBS            249
         2 /u01/app/oracle/oradata/prod/disk3/sysaux01.dbf    SYSAUX             325
         1 /u01/app/oracle/oradata/prod/disk3/system01.dbf    SYSTEM             325
         5 /u01/app/oracle/oradata/prod/disk3/lxtbs1.dbf      LXTBS1              50
         6 /u01/app/oracle/oradata/prod/disk3/lxtbs2.dbf      LXTBS2              50
         7 /u01/app/oracle/oradata/prod/disk3/lxtbs3.dbf      LXTBS3              50

7 rows selected.

#给lxtbs3 添加数据文件
SQL> alter tablespace lxtbs3 add datafile ‘/u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf‘ size 50m;

Tablespace altered.

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

   FILE_ID FILE_NAME                                          TABLESPACE_NAME BYTES/1024/1024
---------- -------------------------------------------------- --------------- ---------------
         4 /u01/app/oracle/oradata/prod/disk3/users01.dbf     USERS              500
         3 /u01/app/oracle/oradata/prod/disk3/undotbs01.dbf   UNDOTBS            249
         2 /u01/app/oracle/oradata/prod/disk3/sysaux01.dbf    SYSAUX             325
         1 /u01/app/oracle/oradata/prod/disk3/system01.dbf    SYSTEM             325
         5 /u01/app/oracle/oradata/prod/disk3/lxtbs1.dbf      LXTBS1              50
         6 /u01/app/oracle/oradata/prod/disk3/lxtbs2.dbf      LXTBS2              50
         7 /u01/app/oracle/oradata/prod/disk3/lxtbs3.dbf      LXTBS3              50
         8 /u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf     LXTBS3              50

8 rows selected.

七.表空间数据文件的迁移：(两种方法：)
1.方法一：使用脱机：无需关库
#脱机
SQL> alter tablespace lxtbs3 offline;
#修改数据文件位置
cp /u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf /u01/app/oracle/oradata/prod/disk4/lxtbs33.dbf

#修改控制文件里的数据文件的位置.
SQL> alter tablespace lxtbs3 rename datafile ‘/u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf‘ to ‘/u01/app/oracle/oradata/prod/disk4/lxtbs33.dbf‘;

#online
SQL> alter tablespace lxtbs3 online;

Tablespace altered.

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

   FILE_ID FILE_NAME                                          TABLESPACE_NAME BYTES/1024/1024
---------- -------------------------------------------------- --------------- ---------------
         4 /u01/app/oracle/oradata/prod/disk3/users01.dbf     USERS              500
         3 /u01/app/oracle/oradata/prod/disk3/undotbs01.dbf   UNDOTBS            249
         2 /u01/app/oracle/oradata/prod/disk3/sysaux01.dbf    SYSAUX             325
         1 /u01/app/oracle/oradata/prod/disk3/system01.dbf    SYSTEM             325
         5 /u01/app/oracle/oradata/prod/disk3/lxtbs1.dbf      LXTBS1              50
         6 /u01/app/oracle/oradata/prod/disk3/lxtbs2.dbf      LXTBS2              50
         7 /u01/app/oracle/oradata/prod/disk3/lxtbs3.dbf      LXTBS3              50
         8 /u01/app/oracle/oradata/prod/disk4/lxtbs33.dbf     LXTBS3              50

8 rows selected.
#删除原始的数据文件
[oracle@master ~]$ rm  /u01/app/oracle/oradata/prod/disk3/lxtbs33.dbf
2.方法二：关库copy，然后改名：


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@master ~]$ cp /u01/app/oracle/oradata/prod/disk4/lxtbs33.dbf /u01/app/oracle/oradata/prod/disk4/lxtbs3.dbf


SQL> startup mount;
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             281020828 bytes
Database Buffers          130023424 bytes
Redo Buffers                6103040 bytes
Database mounted.

SQL> alter database rename file ‘/u01/app/oracle/oradata/prod/disk4/lxtbs33.dbf‘ to ‘/u01/app/oracle/oradata/prod/disk4/lxtbs3.dbf‘;

Database altered.

SQL> alter database open ;
Database altered.

八.删除表空间

#删除表空间，包含数据文件
SQL> drop tablespace lxtbs3 including contents and datafiles;
 


九.OMF
详见：【dba,25】OMF（oracle managed files）
omf 一般在rac中使用到，单实例数据库不推荐使用


十.使用非标准块的表空间：

oracle支持五种块：2k,4k,8k,16k,32k
oltp 8k 足矣。


1.如果使用非标准块的表空间， 由于缓冲区(buffer cache)不能通用，所以要设置表空间缓冲区，下面是16k的表空间的缓冲区,
SQL> alter system set db_16k_cache_size=40m;
如果数据库启动时报空间不足的错误，就是因为没有设置非标准块的缓冲区。


2. 案例：
SQL> select tablespace_Name, contents,status, block_size from dba_tablespaces;

TABLESPACE_NAME                CONTENTS  STATUS    BLOCK_SIZE
------------------------------ --------- --------- ----------
SYSTEM                         PERMANENT ONLINE          8192
SYSAUX                         PERMANENT ONLINE          8192
UNDOTBS                        UNDO      ONLINE          8192
USERS                          PERMANENT ONLINE          8192
TEMPTS2                        TEMPORARY ONLINE          8192
LXTBS1                         PERMANENT ONLINE          8192
LXTBS2                         PERMANENT ONLINE          8192
LXTBS3                         PERMANENT ONLINE          8192
TEMPTS                         TEMPORARY ONLINE          8192

9 rows selected.


SQL> alter system set db_16k_cache_size=40m;

System altered.

SQL> create tablespace lxtbs4 datafile ‘/u01/app/oracle/oradata/prod/disk3/lxtbs04.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
SYSAUX                         PERMANENT ONLINE          8192
UNDOTBS                        UNDO      ONLINE          8192
USERS                          PERMANENT ONLINE          8192
TEMPTS2                        TEMPORARY ONLINE          8192
LXTBS1                         PERMANENT ONLINE          8192
LXTBS2                         PERMANENT ONLINE          8192
LXTBS3                         PERMANENT ONLINE          8192
TEMPTS                         TEMPORARY ONLINE          8192
LXTBS4                         PERMANENT ONLINE         16384

10 rows selected.
十一. bigfile 表空间：
1.
bigfile 表空间最多支持4g个数据库，如果8k，可达32T;
bigfile:在一个表空间只能建立一个数据文件，可以简化对数据文件管理，适合于海量数据。数据库默认是smallfile表空间。

2.创建bigfile表空间
SQL> create bigfile tablespace bigtbs datafile ‘/u01/app/oracle/oradata/prod/disk3/lxtbs11.dbf‘ size 50m autoextend on next 10m maxsize 4t;

SQL> alter tablespace bigtbs add datafile ‘/u01/app/oracle/oradata/prod/disk3/bigtbs02.dbf‘ size 50m;
alter tablespace bigtbs add datafile ‘/u01/app/oracle/oradata/prod/disk3/bigtbs02.dbf‘ size 50m
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace

3. bigfile有缺点： 一般生产库不建议适应bigfile 表空间。

优点：简化管理 
缺点：备份恢复不好，























【oracle11g，14】表空间管理3：临时表空间，表空间的脱机和只读，数据文件迁移，更改表空间数据文件的大小，表空间数据文件的迁移，使用非标准块的表空间，bigfile 表空间

上一篇：sql复制表数据的方法


下一篇：Oracle 存储过程 返回结果集