Oracle 表空间管理

只为了做的更好


　
表空间属性：

一个数据库可以包含多个表空间，一个表空间只能属于一个数据库

一个表空间包含多个数据文件，一个数据文件只能属于一个表空间

表这空间可以划分成更细的逻辑存储单元

表空间结构

   从逻辑的角度来看，一个数据库（database）下面可以分多个表空间（tablespace）；一个表空间下面又可以分多个段（segment）；一个数据表要占一个段（segment），一个索引也要占一个段（segment ）。 一个段（segment）由多个 区间（extent）组成，那么一个区间又由一组连续的数据块（data block）组成。这连续的数据块是在逻辑上是连续的，有可能在物理磁盘上是分散。

那么从物理的角度上看，一个表空间由多个数据文件组成，数据文件是实实在在存在的磁盘上的文件。这些文件是由oracle数据库操作系统的block 组成的。



表空间的分类

 
永久表空间                

临时表空间               

UNDO表空间             

 

Oracle 表空间的管理

 

表空间的管理方式：

字典管理：

不建议 在此不多加解释。

本地管理：

1. 本地化管理的表空间避免了递归的空间管理操作。而这种情况在数据字典管理的表空间是经常出现的，当表空间里的区的使用状况发生改变时，数据字典的表的信息发生改变，从而同时也使用了在系统表空间里的回滚段。

2. 本地化管理的表空间避免了在数据字典相应表里面写入空闲空间、已使用空间的信息，从而减少了数据字典表的竞争，提高了空间管理的并发性

3. 区的本地化管理自动跟踪表空间里的空闲块，减少了手工合并自由空间的需要。

4. 表空间里的区的大小可以选择由Oracle系统来决定，或者由数据库管理员指定一个统一的大小，避免了字典表空间一直头疼的碎片问题。

5. 从由数据字典来管理空闲块改为由数据文件的头部记录来管理空闲块，这样避免产生回滚信息，不再使用系统表空间里的回滚段。因为由数据字典来管理的话，它会把相关信息记在数据字典的表里，从而产生回滚信息。

由于这种表空间的以上特性，所以它支持在一个表空间里边进行更多的并发操作，并减少了对数据字典的依赖。

临时表空间管理
 --查看临时表空间数据文件
SQL> select * from v$tempfile;
 
     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
         1             3388 2015/3/21 21:          3          1 ONLINE  READ WRITE  134217728       8192     20971520      16384 F:\DATA\DATAFILE\MXQ\TEMP01.DBF
 

--创建临时表空间

SQL> create temporary tablespace  TEMP2 TEMPFILE 'E:\oracle_data\temp01.dbf' SIZE 50M REUSE AUTOEXTEND ON NEXT  128M MAXSIZE UNLIMITED;
 
Tablespace created
 

 
SQL> select * from v$tempfile;
 
     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
         1             3388 2015/3/21 21:          3          1 ONLINE  READ WRITE  134217728       8192     20971520      16384 F:\DATA\DATAFILE\MXQ\TEMP01.DBF
         2          1373980 2015/5/10 10:          7          1 ONLINE  READ WRITE   52428800       3200     52428800      16384 E:\ORACLE_DATA\TEMP01.DBF
 
 
SQL> select * from dba_temp_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS  RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- ------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
F:\DATA\DATAFILE\MXQ\TEMP01.DBF                                                           1 TEMP                            134217728       8192 ONLINE             1 YES            6871944396    4194302           40  133169152        8128
E:\ORACLE_DATA\TEMP01.DBF                                                                 2 TEMP2                            52428800       3200 ONLINE             1 YES            6871944396    4194302         8192   51380224        3136
 
--查询用户默认临时表空间
SQL> select TEMPORARY_TABLESPACE from user_users;
 
TEMPORARY_TABLESPACE
------------------------------
TEMP

--修改用户默认临时表空间
 
SQL> alter database default  temporary tablespace  temp2;
 
Database altered
 
SQL> select TEMPORARY_TABLESPACE from user_users;
 
TEMPORARY_TABLESPACE
------------------------------
TEMP2

--重新调整临时表空间大小
SQL> alter database TEMPFILE 'E:\oracle_data\temp01.dbf' RESIZE 60M;
 
Database altered
 
SQL> select * from dba_temp_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS  RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- ------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
F:\DATA\DATAFILE\MXQ\TEMP01.DBF                                                           1 TEMP                            134217728       8192 ONLINE             1 YES            6871944396    4194302           40  133169152        8128
E:\ORACLE_DATA\TEMP01.DBF                                                                 2 TEMP2                            62914560       3840 ONLINE             1 YES            6871944396    4194302         8192   61865984        3776
 

--删除临时表空间以及数据数据文件
SQL> drop tablespace temp2 including contents and datafiles;
 
Tablespace dropped
 
SQL> select * from v$tempfile;
 
     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
         1             3388 2015/3/21 21:          3          1 ONLINE  READ WRITE  134217728       8192     20971520      16384 F:\DATA\DATAFILE\MXQ\TEMP01.DBF
 
SQL> select * from v$tablespace;
 
       TS# NAME                           INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------- ------------------------------ --------------------------- ------- ------------ -----------------
         0 SYSTEM                         YES                         NO      YES          
         1 SYSAUX                         YES                         NO      YES          
         2 UNDOTBS1                       YES                         NO      YES          
         3 TEMP                           NO                          NO      YES          
         4 USERS                          YES                         NO      YES          
         5 MXQ_DATA                       YES                         NO      YES          
         6 MU                             YES                         NO      YES          
 
7 rows selected
 
UNDO表空间管理

--创建UNDO表空间
SQL> CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE 'E:\oracle_data\UNDOTBS2.dbf ' SIZE 5M EXTENT MANAGEMENT LOCAL;
 
Tablespace created
 
SQL> select * from v$tablespace;
 
       TS# NAME                           INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------- ------------------------------ --------------------------- ------- ------------ -----------------
         0 SYSTEM                         YES                         NO      YES          
         1 SYSAUX                         YES                         NO      YES          
         2 UNDOTBS1                       YES                         NO      YES          
         3 TEMP                           NO                          NO      YES          
         4 USERS                          YES                         NO      YES          
         5 MXQ_DATA                       YES                         NO      YES          
         6 MU                             YES                         NO      YES          
         8 UNDOTBS2                       YES                         NO      YES          
 
8 rows selected
 
SQL> select * from dba_data_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
F:\DATA\DATAFILE\MXQ\SYSTEM01.DBF                                                         1 SYSTEM                          723517440      44160 AVAILABLE            1 YES            6871944396    4194302          640  722468864       44096 SYSTEM
F:\DATA\DATAFILE\MXQ\SYSAUX01.DBF                                                         2 SYSAUX                          880803840      53760 AVAILABLE            2 YES            6871944396    4194302          640  879755264       53696 ONLINE
F:\DATA\DATAFILE\MXQ\UNDOTBS01.DBF                                                        3 UNDOTBS1                        639631360      39040 AVAILABLE            3 YES            6871944396    4194302          320  638582784       38976 ONLINE
F:\DATA\DATAFILE\MXQ\USERS01.DBF                                                          4 USERS                             5242880        320 AVAILABLE            4 YES            6871944396    4194302           80    4194304         256 ONLINE
F:\DATA\DATAFILE\MXQ\MXQ_DATA.DBF                                                         5 MXQ_DATA                         52428800       3200 AVAILABLE            5 YES             104857600       6400         3200   51380224        3136 ONLINE
E:\ORACLE_DATA\MU.DBF                                                                     6 MU                             1073741824      65536 AVAILABLE            6 NO                      0          0            0 1072693248       65472 ONLINE
E:\ORACLE_DATA\UNDOTBS2.DBF                                                               7 UNDOTBS2                          5242880        320 AVAILABLE            7 NO                      0          0            0    4194304         256 ONLINE
 
7 rows selected
 
SQL> select * from dba_tablespaces;
 
TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION   BIGFILE PREDICATE_EVALUATION ENCRYPTED COMPRESS_FOR
------------------------------ ---------- -------------- ----------- ----------- ----------- ---------- ------------ ---------- --------- --------- --------- ------------- ----------------- --------------- ---------- ------------------------ ------------------- ----------- ------- -------------------- --------- ------------
SYSTEM                              16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         MANUAL                   DISABLED            NOT APPLY   NO      HOST                 NO        
SYSAUX                              16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
UNDOTBS1                            16384          65536                       1  2147483645 2147483645                   65536 ONLINE    UNDO      LOGGING   NO            LOCAL             SYSTEM          NO         MANUAL                   DISABLED            NOGUARANTEE NO      HOST                 NO        
TEMP                                16384        1048576     1048576           1             2147483645            0    1048576 ONLINE    TEMPORARY NOLOGGING NO            LOCAL             UNIFORM         NO         MANUAL                   DISABLED            NOT APPLY   NO      HOST                 NO        
USERS                               16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
MXQ_DATA                            16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
MU                                  16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
UNDOTBS2                            16384          65536                       1  2147483645 2147483645                   65536 ONLINE    UNDO      LOGGING   NO            LOCAL             SYSTEM          NO         MANUAL                   DISABLED            NOGUARANTEE NO      HOST                 NO        
 
8 rows selected
 
 --修改用户默认UNDO表空间
SQL> alter system set undo_tablespace=UNDOTBS2;
 
System altered
 
 
SQL> show parameter undo;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
 
--删除UNDO表空间以及数据文件
 
SQL> drop tablespace UNDOTBS2 including contents;
 
Tablespace dropped

永久表空管理

--创建表空间
SQL> CREATE TABLESPACE mxq DATAFILE  'E:\oracle_data\mxq01.dbf ' SIZE  10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
 
Tablespace created
 
SQL>
SQL> select * from v$tablespace;
 
       TS# NAME                           INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------- ------------------------------ --------------------------- ------- ------------ -----------------
         0 SYSTEM                         YES                         NO      YES          
         1 SYSAUX                         YES                         NO      YES          
         2 UNDOTBS1                       YES                         NO      YES          
         3 TEMP                           NO                          NO      YES          
         4 USERS                          YES                         NO      YES          
         5 MXQ_DATA                       YES                         NO      YES          
         6 MU                             YES                         NO      YES          
         9 MXQ                            YES                         NO      YES          
 
8 rows selected
 

 
--重新调整表空间大小
SQL> ALTER DATABASE DATAFILE 'E:\oracle_data\mxq01.dbf ' RESIZE 100M;
 
Database altered
 
SQL> select * from dba_data_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
F:\DATA\DATAFILE\MXQ\SYSTEM01.DBF                                                         1 SYSTEM                          723517440      44160 AVAILABLE            1 YES            6871944396    4194302          640  722468864       44096 SYSTEM
F:\DATA\DATAFILE\MXQ\SYSAUX01.DBF                                                         2 SYSAUX                          880803840      53760 AVAILABLE            2 YES            6871944396    4194302          640  879755264       53696 ONLINE
F:\DATA\DATAFILE\MXQ\UNDOTBS01.DBF                                                        3 UNDOTBS1                        639631360      39040 AVAILABLE            3 YES            6871944396    4194302          320  638582784       38976 ONLINE
F:\DATA\DATAFILE\MXQ\USERS01.DBF                                                          4 USERS                             5242880        320 AVAILABLE            4 YES            6871944396    4194302           80    4194304         256 ONLINE
F:\DATA\DATAFILE\MXQ\MXQ_DATA.DBF                                                         5 MXQ_DATA                         52428800       3200 AVAILABLE            5 YES             104857600       6400         3200   51380224        3136 ONLINE
E:\ORACLE_DATA\MU.DBF                                                                     6 MU                             1073741824      65536 AVAILABLE            6 NO                      0          0            0 1072693248       65472 ONLINE
E:\ORACLE_DATA\MXQ01.DBF                                                                  7 MXQ                             104857600       6400 AVAILABLE            7 NO                      0          0            0  103809024        6336 ONLINE
 
7 rows selected
 
SQL>


--表空间添加数据文件
SQL> ALTER TABLESPACE mxq ADD DATAFILE 'E:\oracle_data\mxq02.dbf ' SIZE 10M;
 
Tablespace altered
 
SQL> select * from dba_data_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
F:\DATA\DATAFILE\MXQ\SYSTEM01.DBF                                                         1 SYSTEM                          723517440      44160 AVAILABLE            1 YES            6871944396    4194302          640  722468864       44096 SYSTEM
F:\DATA\DATAFILE\MXQ\SYSAUX01.DBF                                                         2 SYSAUX                          880803840      53760 AVAILABLE            2 YES            6871944396    4194302          640  879755264       53696 ONLINE
F:\DATA\DATAFILE\MXQ\UNDOTBS01.DBF                                                        3 UNDOTBS1                        639631360      39040 AVAILABLE            3 YES            6871944396    4194302          320  638582784       38976 ONLINE
F:\DATA\DATAFILE\MXQ\USERS01.DBF                                                          4 USERS                             5242880        320 AVAILABLE            4 YES            6871944396    4194302           80    4194304         256 ONLINE
F:\DATA\DATAFILE\MXQ\MXQ_DATA.DBF                                                         5 MXQ_DATA                         52428800       3200 AVAILABLE            5 YES             104857600       6400         3200   51380224        3136 ONLINE
E:\ORACLE_DATA\MU.DBF                                                                     6 MU                             1073741824      65536 AVAILABLE            6 NO                      0          0            0 1072693248       65472 ONLINE
E:\ORACLE_DATA\MXQ01.DBF                                                                  7 MXQ                             104857600       6400 AVAILABLE            7 NO                      0          0            0  103809024        6336 ONLINE
E:\ORACLE_DATA\MXQ02.DBF                                                                  8 MXQ                              10485760        640 AVAILABLE            8 NO                      0          0            0    9437184         576 ONLINE
 
8 rows selected
 

--表空间设置脱机
SQL> ALTER TABLESPACE mxq offline;
 
Tablespace altered
 
 --把物理文件拷贝到要修改地方然后在执行下面语句修改路径
SQL> alter database rename file 'E:\oracle_data\mxq02.dbf ' to 'E:\mxq02.dbf ';
 
Database altered
 
 --表空间设置在线

SQL> ALTER TABLESPACE mxq online;
 
Tablespace altered
 
SQL> select * from dba_data_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
F:\DATA\DATAFILE\MXQ\SYSTEM01.DBF                                                         1 SYSTEM                          723517440      44160 AVAILABLE            1 YES            6871944396    4194302          640  722468864       44096 SYSTEM
F:\DATA\DATAFILE\MXQ\SYSAUX01.DBF                                                         2 SYSAUX                          880803840      53760 AVAILABLE            2 YES            6871944396    4194302          640  879755264       53696 ONLINE
F:\DATA\DATAFILE\MXQ\UNDOTBS01.DBF                                                        3 UNDOTBS1                        639631360      39040 AVAILABLE            3 YES            6871944396    4194302          320  638582784       38976 ONLINE
F:\DATA\DATAFILE\MXQ\USERS01.DBF                                                          4 USERS                             5242880        320 AVAILABLE            4 YES            6871944396    4194302           80    4194304         256 ONLINE
F:\DATA\DATAFILE\MXQ\MXQ_DATA.DBF                                                         5 MXQ_DATA                         52428800       3200 AVAILABLE            5 YES             104857600       6400         3200   51380224        3136 ONLINE
E:\ORACLE_DATA\MU.DBF                                                                     6 MU                             1073741824      65536 AVAILABLE            6 NO                      0          0            0 1072693248       65472 ONLINE
E:\ORACLE_DATA\MXQ01.DBF                                                                  7 MXQ                             104857600       6400 AVAILABLE            7 NO                      0          0            0  103809024        6336 ONLINE
E:\MXQ02.DBF                                                                              8 MXQ                              10485760        640 AVAILABLE            8 NO                      0          0            0    9437184         576 ONLINE
 
8 rows selected
 

 
SQL> select * from v$tablespace;
 
       TS# NAME                           INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------- ------------------------------ --------------------------- ------- ------------ -----------------
         0 SYSTEM                         YES                         NO      YES          
         1 SYSAUX                         YES                         NO      YES          
         2 UNDOTBS1                       YES                         NO      YES          
         3 TEMP                           NO                          NO      YES          
         4 USERS                          YES                         NO      YES          
         5 MXQ_DATA                       YES                         NO      YES          
         6 MU                             YES                         NO      YES          
         9 MXQ                            YES                         NO      YES          
 
8 rows selected
 
SQL> select * from dba_tablespaces;
 
TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION   BIGFILE PREDICATE_EVALUATION ENCRYPTED COMPRESS_FOR
------------------------------ ---------- -------------- ----------- ----------- ----------- ---------- ------------ ---------- --------- --------- --------- ------------- ----------------- --------------- ---------- ------------------------ ------------------- ----------- ------- -------------------- --------- ------------
SYSTEM                              16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         MANUAL                   DISABLED            NOT APPLY   NO      HOST                 NO        
SYSAUX                              16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
UNDOTBS1                            16384          65536                       1  2147483645 2147483645                   65536 ONLINE    UNDO      LOGGING   NO            LOCAL             SYSTEM          NO         MANUAL                   DISABLED            NOGUARANTEE NO      HOST                 NO        
TEMP                                16384        1048576     1048576           1             2147483645            0    1048576 ONLINE    TEMPORARY NOLOGGING NO            LOCAL             UNIFORM         NO         MANUAL                   DISABLED            NOT APPLY   NO      HOST                 NO        
USERS                               16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
MXQ_DATA                            16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
MU                                  16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
MXQ                                 16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
 
8 rows selected
 
--设置只读表空间
 
SQL> alter tablespace mxq read only;
 
Tablespace altered

--设置可读可写表空间
SQL> alter tablespace mxq read write;
 
Tablespace altered
 
--删除表空间里面的某一个数据文件物理是不会自动删除，需要手动删除
SQL> ALTER TABLESPACE mxq drop DATAFILE 'E:\MXQ02.DBF';
 
Tablespace altered
 

 
SQL> select * from dba_data_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
F:\DATA\DATAFILE\MXQ\SYSTEM01.DBF                                                         1 SYSTEM                          723517440      44160 AVAILABLE            1 YES            6871944396    4194302          640  722468864       44096 SYSTEM
F:\DATA\DATAFILE\MXQ\SYSAUX01.DBF                                                         2 SYSAUX                          880803840      53760 AVAILABLE            2 YES            6871944396    4194302          640  879755264       53696 ONLINE
F:\DATA\DATAFILE\MXQ\UNDOTBS01.DBF                                                        3 UNDOTBS1                        639631360      39040 AVAILABLE            3 YES            6871944396    4194302          320  638582784       38976 ONLINE
F:\DATA\DATAFILE\MXQ\USERS01.DBF                                                          4 USERS                             5242880        320 AVAILABLE            4 YES            6871944396    4194302           80    4194304         256 ONLINE
F:\DATA\DATAFILE\MXQ\MXQ_DATA.DBF                                                         5 MXQ_DATA                         52428800       3200 AVAILABLE            5 YES             104857600       6400         3200   51380224        3136 ONLINE
E:\ORACLE_DATA\MU.DBF                                                                     6 MU                             1073741824      65536 AVAILABLE            6 NO                      0          0            0 1072693248       65472 ONLINE
E:\ORACLE_DATA\MXQ01.DBF                                                                  7 MXQ                             104857600       6400 AVAILABLE            7 NO                      0          0            0  103809024        6336 ONLINE
 
7 rows selected
 
 --删除表空间以及数据文件
SQL> drop tablespace mxq including contents and datafiles;
 
Tablespace dropped
 
SQL> select * from dba_tablespaces;
 
TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION   BIGFILE PREDICATE_EVALUATION ENCRYPTED COMPRESS_FOR
------------------------------ ---------- -------------- ----------- ----------- ----------- ---------- ------------ ---------- --------- --------- --------- ------------- ----------------- --------------- ---------- ------------------------ ------------------- ----------- ------- -------------------- --------- ------------
SYSTEM                              16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         MANUAL                   DISABLED            NOT APPLY   NO      HOST                 NO        
SYSAUX                              16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
UNDOTBS1                            16384          65536                       1  2147483645 2147483645                   65536 ONLINE    UNDO      LOGGING   NO            LOCAL             SYSTEM          NO         MANUAL                   DISABLED            NOGUARANTEE NO      HOST                 NO        
TEMP                                16384        1048576     1048576           1             2147483645            0    1048576 ONLINE    TEMPORARY NOLOGGING NO            LOCAL             UNIFORM         NO         MANUAL                   DISABLED            NOT APPLY   NO      HOST                 NO        
USERS                               16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
MXQ_DATA                            16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
MU                                  16384          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        
 
7 rows selected
 
COMPLETE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29065182/viewspace-1633111/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29065182/viewspace-1633111/