只为了做的更好
表空间属性：
一个数据库可以包含多个表空间，一个表空间只能属于一个数据库
一个表空间包含多个数据文件，一个数据文件只能属于一个表空间
表这空间可以划分成更细的逻辑存储单元
表空间结构
从逻辑的角度来看，一个数据库（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
表空间属性：
一个数据库可以包含多个表空间，一个表空间只能属于一个数据库
一个表空间包含多个数据文件，一个数据文件只能属于一个表空间
表这空间可以划分成更细的逻辑存储单元
表空间结构
从逻辑的角度来看，一个数据库（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/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
