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
表空間屬性：
一個資料庫可以包含多個表空間，一個表空間只能屬於一個資料庫
一個表空間包含多個資料檔案，一個資料檔案只能屬於一個表空間
表這空間可以劃分成更細的邏輯儲存單元
表空間結構
從邏輯的角度來看，一個資料庫（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/30496894/viewspace-1806062/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle表空間管理Oracle
- Oracle undo 表空間管理Oracle
- Oracle 表空間的管理Oracle
- oracle undo表空間管理Oracle
- Oracle的表空間管理Oracle
- Oracle 本地表空間管理與字典表空間管理Oracle
- Oracle OCP(49)：表空間管理Oracle
- oracle表空間日常操作管理Oracle
- oracle本地管理的表空間Oracle
- oracle表空間管理維護Oracle
- oracle 表空間的管理方式Oracle
- Oracle的UNDO表空間管理總結Oracle
- oracle表空間管理（簡單記錄）Oracle
- oracle 資料檔案表空間管理Oracle
- 2 Day DBA-管理Oracle例項-修改表空間-刪除表空間Oracle
- Oracle表空間Oracle
- 將字典管理表空間轉換為本地管理表空間
- ASM表空間管理ASM
- 2 Day DBA-管理Oracle例項-修改表空間-使表空間離線Oracle
- 管理表空間（表空間的屬性）轉貼
- 表空間管理之bigfile表空間設定
- 遷移SYSTEM表空間為本地管理表空間
- 探索ORACLE_之表空間02_管理Oracle
- oracle temp 表空間Oracle
- 增加oracle表空間Oracle
- oracle undo 表空間Oracle
- oracle users 表空間Oracle
- oracle建立表空間Oracle
- oracle表空間操作Oracle
- ORACLE MOVE表空間Oracle
- ORACLE表空間概述Oracle
- Oracle表空間命令Oracle
- Oracle 表空間回收Oracle
- Oracle表移動表空間Oracle
- oracle 表移動表空間Oracle
- 監控和管理Oracle UNDO表空間的使用Oracle
- oracle表空間的整理Oracle
- Oracle 批量建表空間Oracle