表空間和資料檔案管理

dawn009發表於2014-04-26
一、表空間簡介
A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further  divided into extents. Extents are a collection of contiguous blocks.
建表空間的目的就是對資料進行分類管理和存放。
最少有兩個系統表空間：
system：存放資料字典，在建庫時建立。
sysaux：輔助表空間，存放從system分離一部分資訊如對資料庫的監控（oem），執行狀態等資訊。
此外還最好有
temp：臨時表空間，用於資料排序等，不存放永久物件。
undo：存放undo資料塊（資料塊舊的映象）。
注意以下幾點：
1）表空間同一時間只能存在於一個資料庫中。
2） 表空間有一個或多個資料檔案。
3） system表空間，手工建庫不能太小
4） sysaux以資料庫的監控為主，statspack，awr等，太小oem都安不上。
5） 如果沒建undo表空間，就會在system表空間建立undo段，應建立單獨的undo表空間。
6） 如果沒建臨時表空間，或預設表空間，都佔system，應建立專門的表空間。
二、表空間管理方式
1、字典管理方式（dictionary）：使用資料字典來管理儲存空間的分配，當表空間分配新的區、或者回收已分配的區時，oracle會對資料字典對應的表（uet$與fet$）進行查詢、更新。且使用單執行緒（併發性差），速度慢，並且會產生回退和重做資訊。（注意：在字典管理方式下，如果對某個表進行更新，這是會產生儲存操作，而該操作又會產生回滾和重做操作，導致對回滾段和重做日誌檔案進行讀寫，從而產生儲存管理操作，因此形成遞迴現象）
2、本地管理方式（local）：oracle 9i預設方式，表空間中區分配和區回收的管理資訊都被儲存在表空間的資料檔案中，而與資料字典無關。表空間為每個資料檔案維護一個點陣圖結構，用於記錄表空間的區分配情況。當表空間分配新的區，或回收已分配的區時，oracle會對檔案中的點陣圖進行更新（併發性強），所以不會產生回滾和重做資訊。
--檢視錶空間資訊
SQL> select tablespace_name,contents,status,extent_management from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN
------------------------------ --------- --------- ----------
SYSTEM PERMANENT ONLINE LOCAL
UNDOTBS UNDO ONLINE LOCAL
SYSAUX PERMANENT ONLINE LOCAL
TEMPTS TEMPORARY ONLINE LOCAL
USERS PERMANENT ONLINE LOCAL
TEMPTS2 TEMPORARY ONLINE LOCAL
以上查詢結果可知，表空間管理方式為local，注意若system表空間管理方式為local，則其它表空間都只能為local方式管理，若system表空間管理方式為dictionary，則其它表空間管理方式可隨意。
--若system表空間為dictionary，可執行下面語句進行轉換為local方式
SQL> exec dbms_space_admin.tablespace_migrate_to_local('SYSTEM');
BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;
*
ERROR at line 1:
ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrate
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1
因當前表空間管理方式就是local，所以執行上面語句時會報錯。
--檢視資料字典所管理表空間的資訊
SQL> select a.ts#,a.name,b.file#,b.name "File_name" ,c.block#,d.extent_management from v$tablespace a,v$datafile b,fet$ c,dba_tablespaces d where a.ts#=b.ts# and a.ts#=c.ts# and a.name=d.tablespace_name;
no rows selected
下面建立三個表空間，設定不同的表空間管理方式
--檢視當前資料檔案及表空間資訊
SQL> col name for a50;
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
SQL> select TABLESPACE_NAME,CONTENTS,STATUS,EXTENT_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN
------------------------------ --------- --------- ----------
SYSTEM PERMANENT ONLINE LOCAL
UNDOTBS UNDO ONLINE LOCAL
SYSAUX PERMANENT ONLINE LOCAL
TEMPTS TEMPORARY ONLINE LOCAL
USERS PERMANENT ONLINE LOCAL
TEMPTS2 TEMPORARY ONLINE LOCAL
6 rows selected.
--建立本地管理（自動增長）方式表空間
SQL> create tablespace lxtbs1 datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf'
  2 size 50m extent management local;
Tablespace created.
注意：紅色字型內容為預設格式，可省略不寫。
--建立資料字典管理方式表空間
SQL> create tablespace lxtbs2 datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf'
  2 size 50m extent management dictionary;
create tablespace lxtbs2 datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf'
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
注意：在此無法建立dictionary管理方式的表空間，因為system表空間為local管理方式，前面提到了，若system為local管理方式，其它表空間都必須是local管理方式
--建立本地管理（uniform即區大小不變）方式表空間
SQL> create tablespace lxtbs2 datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf'
  2 size 50m uniform size 1m;
Tablespace created.
 --建立本地管理（自定義區大小）方式表空間
SQL> create tablespace lxtbs3 datafile '/u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf'
  2 size 50m default storage(initial 32k next 64k pctincrease 50);
Tablespace created.

--給以上三個表空間各建立一個表，並插入相同多的資料
SQL> create table lxtb1 tablespace lxtbs1 as select * from scott.emp;
Table created.
SQL> create table lxtb2 tablespace lxtbs2 as select * from scott.emp;
Table created.
SQL> create table lxtb3 tablespace lxtbs3 as select * from scott.emp;
Table created.
SQL> insert into lxtb1 select * from lxtb1;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> /
112 rows created.
SQL> /
224 rows created.
SQL> /
448 rows created.
SQL> /
896 rows created.
SQL> /
1792 rows created.
SQL> commit;
Commit complete.
SQL> insert into lxtb2 select * from lxtb2;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> /
112 rows created.
SQL> /
224 rows created.
SQL> /
/
448 rows created.
SQL>
896 rows created.
SQL> /
1792 rows created.
SQL> commit;
Commit complete.
SQL> insert into lxtb3 select * from lxtb3;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> /
112 rows created.
SQL> /
224 rows created.
SQL> /
448 rows created.
SQL> /
896 rows created.
SQL> /
1792 rows created.
SQL> commit;
Commit complete.
--檢視剛建立的三張表，相應的extent資訊
SQL> col segment_name for a10
SQL> select segment_name,bytes/1024 k,extents,blocks,initial_extent/1024 init,next_extent/1024 next,pct_increase from dba_segments where segment_name like 'LXTB%';
SEGMENT_NA K EXTENTS BLOCKS INIT NEXT PCT_INCREASE
---------- ---------- ---------- ---------- ---------- ---------- ------------
LXTB1 256 4 32 64
LXTB2 1024 1 128 1024 1024 0
LXTB3 256 4 32 64
分析上面的查詢結果，lxtb1為自動增長方式，初始化大小為64，下一個大小未知，增長幅度也未知。lxtb3為自定義方式，前面自定義init為32，next為64，pctincrease 50，但實際查詢結果與lxtbs的自動增長方式一樣，可知想自定義表空間增長方式，以區的管理形式是無法改變的（可控制段的管理方式來實現自定義增長方式）。lxtb2在lxtbs2表空間上，為uniform方式，此方式是oracle推薦方式，更利於回收空間，不會有太多的碎片（索引表空間建議為uniform方式）。自動增長方式雖然效率更高，但碎片較多。
三、undo表空間與臨時表空間
3.1、Undo Tablespaces
3.1.1 undo表空間簡介
1）undo表空間用來存放undo資訊，一般是本地管理，且不能在undo表空間建立任何物件（表或索引）。
2）可以建立多個undo表空間，但處於active狀態的只有一個。
3）system表空間也含有undo段，如果沒建undo表空間就佔system的，不推薦此做法。
注意：修改資料後，舊的映象就放在undo段中了。而update時，server process會在databuffer中找到該記錄的記錄塊，沒有就從datafile中找，在修改之前，先放到undo段（active）狀態，並在資料塊頭記錄undo段中該資料塊的位置，然後進行update。因此，當某session做update操作時，若未commit，則其它session檢視這些資料時，還是未修改之前的資料，下面例項驗證。
SQL> create table emp2 as select * from scott.emp;
Table created.
SQL> select count(1) from emp2;
  COUNT(1)
----------
        14
SQL> delete from emp2;
14 rows deleted.
SQL> select count(1) from emp2;
  COUNT(1)
----------
         0
--另開視窗，統計emp2表記錄數
[oracle@gc1 ~]$ export ORACLE_SID=PROD
[oracle@gc1 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 11 19:15:53 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select count(1) from emp2;
  COUNT(1)
----------
        14
由此可見，上面session1使用者執行delete操作，未提交之前，session2統計的結果還是未刪除之前的記錄數。下面session1做提交操作，再看二邊emp2表統計記錄數結果。
session1使用者統計結果
SQL> commit;
Commit complete.
SQL> select count(1) from emp2;
  COUNT(1)
----------
         0
session2使用者再統計
SQL> select count(1) from emp2;
  COUNT(1)
----------
         0
修改操作commit之後，二邊結果一致。
3.1.2 修改系統預設undo表空間
--檢視系統當前undo引數資訊
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 5400
undo_tablespace string undotbs
--offline當前undo表空間（無法offline當前undo表空間）
SQL> alter tablespace undotbs offline;
alter tablespace undotbs offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
--檢視undo表空間及對應資料檔案資訊
SQL> col name for a50;
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf
         7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf
SQL> select TABLESPACE_NAME,CONTENTS,STATUS,EXTENT_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN
------------------------------ --------- --------- ----------
SYSTEM PERMANENT ONLINE LOCAL
UNDOTBS UNDO ONLINE LOCAL
SYSAUX PERMANENT ONLINE LOCAL
TEMPTS TEMPORARY ONLINE LOCAL
USERS PERMANENT ONLINE LOCAL
TEMPTS2 TEMPORARY ONLINE LOCAL
LXTBS1 PERMANENT ONLINE LOCAL
LXTBS2 PERMANENT ONLINE LOCAL
LXTBS3 PERMANENT ONLINE LOCAL
--新建一個undo表空間，為替換當前undo表空間所用
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf' size 50m;
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf
         7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf
8 rows selected.
SQL> select TABLESPACE_NAME,CONTENTS,STATUS,EXTENT_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN
------------------------------ --------- --------- ----------
SYSTEM PERMANENT ONLINE LOCAL
UNDOTBS UNDO ONLINE LOCAL
SYSAUX PERMANENT ONLINE LOCAL
TEMPTS TEMPORARY ONLINE LOCAL
USERS PERMANENT ONLINE LOCAL
TEMPTS2 TEMPORARY ONLINE LOCAL
LXTBS1 PERMANENT ONLINE LOCAL
LXTBS2 PERMANENT ONLINE LOCAL
LXTBS3 PERMANENT ONLINE LOCAL
UNDOTBS2 UNDO ONLINE LOCAL
注意：上面二個sql，紅色部分為新增的undo表空間及對應資料檔案
--修改系統預設undo表空間（spfile檔案也做相應修改）
SQL> alter system set undo_tablespace=undotbs2;
System altered.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 5400
undo_tablespace string UNDOTBS2
修改spfile檔案中預設undo表空間
[oracle@gc1 ~]$ cd $ORACLE_HOME/dbs
[oracle@gc1 dbs]$ ls
??7?.txt hc_BOB.dat hc_yj.dat initBOB.ora init.ora lkBOB lkYJ orapwyj spfilePROD.ora.bak
ha hc_PROD.dat hc_ZHEN.dat initdw.ora initPROD.ora lkPROD orapwPROD shutdown spfileyj.ora
[oracle@gc1 dbs]$ vi initPROD.ora
 表空間和資料檔案管理
紅圈部分內容，將原來的undo_tablespace=undotbs改為 undotbs2。
到此為止，修改系統預設undo表空間結束。
3.2 Temporary Tablespace
3.2.1 臨時表空間簡介
臨時表空間主要用於排序、分組、索引等操作，在pga中的sort_area中排序，如以下一些操作：
1）索引create或rebuild
2）order by或group by
3）distinct操作
4）union或intersect或minus
5）sort-merge joins
6）analyze
臨時表空間不能放持久化物件，推薦本地管理，並且uniform size。沒臨時表空間就佔system表空間，臨時表空間不能刪除。
--檢視排序引數資訊
SQL> show parameter sort;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string
sort_area_retained_size integer 0
sort_area_size integer 65536

3.2.2 修改系統預設臨時表空間
--檢視系統當前temp表空間資訊
SQL> select TABLESPACE_NAME,CONTENTS,STATUS,EXTENT_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN
------------------------------ --------- --------- ----------
SYSTEM PERMANENT ONLINE LOCAL
UNDOTBS UNDO ONLINE LOCAL
SYSAUX PERMANENT ONLINE LOCAL
TEMPTS TEMPORARY ONLINE LOCAL
USERS PERMANENT ONLINE LOCAL
TEMPTS2 TEMPORARY ONLINE LOCAL
LXTBS1 PERMANENT ONLINE LOCAL
LXTBS2 PERMANENT ONLINE LOCAL
LXTBS3 PERMANENT ONLINE LOCAL
UNDOTBS2 UNDO ONLINE LOCA
SQL> select file#,name from v$tempfile;
     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/PROD/disk5/temp02.dbf
SQL> col file_name for a50;
SQL> col tablespace_name for a20;
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_temp_files;
   FILE_ID FILE_NAME TABLESPACE_NAME M
---------- -------------------------------------------------- -------------------- ----------
         1 /u01/app/oracle/oradata/PROD/disk5/temp02.dbf TEMPTS2 50
--新建一個臨時表空間，為切換預設臨地表空間所用
SQL> create temporary tablespace tempts3 tempfile '/u01/app/oracle/oradata/PROD/disk5/temp03.dbf' size 50m;
Tablespace created.
SQL> select tablespace_name,contents,status,extent_management from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN
-------------------- --------- --------- ----------
SYSTEM PERMANENT ONLINE LOCAL
UNDOTBS UNDO ONLINE LOCAL
SYSAUX PERMANENT ONLINE LOCAL
TEMPTS TEMPORARY ONLINE LOCAL
USERS PERMANENT ONLINE LOCAL
TEMPTS2 TEMPORARY ONLINE LOCAL
LXTBS1 PERMANENT ONLINE LOCAL
LXTBS2 PERMANENT ONLINE LOCAL
LXTBS3 PERMANENT ONLINE LOCAL
UNDOTBS2 UNDO ONLINE LOCAL
TEMPTS3 TEMPORARY ONLINE LOCAL

SQL> select file#,name from v$tempfile;
     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/PROD/disk5/temp02.dbf
         2 /u01/app/oracle/oradata/PROD/disk5/temp03.dbf
--檢視系統預設臨時表空間（與檢視字符集方式相同）
SQL> col PROPERTY_NAME for a30;
SQL> col PROPERTY_VALUE for a50;
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties;
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
DICT.BASE 2
DEFAULT_TEMP_TABLESPACE TEMPTS2
DEFAULT_PERMANENT_TABLESPACE USERS
DBTIMEZONE +08:00
DEFAULT_TBS_TYPE SMALLFILE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0
GLOBAL_DB_NAME PROD
EXPORT_VIEWS_VERSION 8
27 rows selected.
--修改系統預設臨時表空間
SQL> alter database default temporary tablespace tempts3;
Database altered.
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties;
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
DICT.BASE 2
DEFAULT_TEMP_TABLESPACE TEMPTS3
DEFAULT_PERMANENT_TABLESPACE USERS
DBTIMEZONE +08:00
DEFAULT_TBS_TYPE SMALLFILE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0
GLOBAL_DB_NAME PROD
EXPORT_VIEWS_VERSION 8
27 rows selected.
3.2.3 建立臨時表空間組
SQL> alter tablespace tempts2 tablespace group temp_grp;
Tablespace altered.
SQL> alter tablespace tempts3 tablespace group temp_grp;
Tablespace altered.
SQL> alter tablespace tempts tablespace group temp_grp;
Tablespace altered.
--設預設臨時表空間為temp_grp表空間組
SQL> alter database default temporary tablespace temp_grp;
Database altered.
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties;
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
DICT.BASE 2
DEFAULT_TEMP_TABLESPACE TEMP_GRP
DEFAULT_PERMANENT_TABLESPACE USERS
DBTIMEZONE +08:00
DEFAULT_TBS_TYPE SMALLFILE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0
GLOBAL_DB_NAME PROD
EXPORT_VIEWS_VERSION 8
--檢視臨時表空間組資訊
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ --------------------
TEMP_GRP TEMPTS
TEMP_GRP TEMPTS2
TEMP_GRP TEMPTS3
--臨時表空間退組
SQL> alter tablespace tempts tablespace group '';
Tablespace altered.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ --------------------
TEMP_GRP TEMPTS2
TEMP_GRP TEMPTS3
總結：使用臨時表空間組，有如下優點：
1）避免當臨時表空間不足時所引起的磁碟排序問題；
2）當一個使用者同時有多個會話時，可以使它們使用不同的臨時表空間；
3）使得並行的伺服器在單節點上，能使用多個臨時表空間。
--指定使用者臨時表空間
SQL> select username,default_tablespace,temporary_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT USERS TEMP_GRP
TOM USERS TEMP_GRP
TSMSYS USERS TEMP_GRP
DIP USERS TEMP_GRP
DBSNMP SYSAUX TEMP_GRP
OUTLN SYSTEM TEMP_GRP
SYS SYSTEM TEMP_GRP
SYSTEM SYSTEM TEMP_GRP
8 rows selected.
SQL> alter user scott temporary tablespace tempts2;
User altered.
SQL> select username,default_tablespace,temporary_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT USERS TEMPTS2
TOM USERS TEMP_GRP
TSMSYS USERS TEMP_GRP
DIP USERS TEMP_GRP
DBSNMP SYSAUX TEMP_GRP
OUTLN SYSTEM TEMP_GRP
SYS SYSTEM TEMP_GRP
SYSTEM SYSTEM TEMP_GRP
8 rows selected.
四、表空間的只讀與離線
--檢視系統當前表空間資訊及資料檔案資訊
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;
   FILE_ID FILE_NAME TABLESPACE_NAME M
---------- -------------------------------------------------- -------------------- ----------
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50
         7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 50
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50
8 rows selected.
SQL> select tablespace_name,contents,status,extent_management from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN
-------------------- --------- --------- ----------
SYSTEM PERMANENT ONLINE LOCAL
UNDOTBS UNDO ONLINE LOCAL
SYSAUX PERMANENT ONLINE LOCAL
TEMPTS TEMPORARY ONLINE LOCAL
USERS PERMANENT ONLINE LOCAL
TEMPTS2 TEMPORARY ONLINE LOCAL
LXTBS1 PERMANENT ONLINE LOCAL
LXTBS2 PERMANENT ONLINE LOCAL
LXTBS3 PERMANENT ONLINE LOCAL
UNDOTBS2 UNDO ONLINE LOCAL
TEMPTS3 TEMPORARY ONLINE LOCAL
11 rows selected.
--檢視資料檔案的scn號
SQL> select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 540731
         2 540731
         3 540731
         4 540731
         5 571548
         6 571701
         7 571856
         8 574895
8 rows selected.
--生成系統全域性檢查點，使資料檔案scn一致
SQL> alter system checkpoint;
System altered.
SQL> select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 579785
         2 579785
         3 579785
         4 579785
         5 579785
         6 579785
         7 579785
         8 579785
--設定lxtbs3表空間為只讀（對應資料檔案為7）
SQL> alter tablespace lxtbs3 read only;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 580049
         2 580049
         3 580049
         4 580049
         5 580049
         6 580049
         7 580038
         8 580049
8 rows selected.
SQL> select * from lxtb3 where rownum<=3;
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH CLERK 7902 17-DEC-80 800 20
      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
      7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
--設定表空間為讀寫
SQL> alter tablespace lxtbs3 read write;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 580080
         2 580080
         3 580080
         4 580080
         5 580080
         6 580080
         7 580080
         8 580080
8 rows selected.
--設定表空間離線（offline）
SQL> alter tablespace lxtbs3 offline;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 580196
         2 580196
         3 580196
         4 580196
         5 580196
         6 580196
         7 580185
         8 580196
8 rows selected.
SQL> select * from lxtb3 where rownum<=3;
 select * from lxtb3 where rownum<=3
               *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf'
SQL> alter tablespace lxtbs3 online;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 580225
         2 580225
         3 580225
         4 580225
         5 580225
         6 580225
         7 580225
         8 580225
8 rows selected.
--offline immediate（online之前要先recover tablespace，因為正常來說，offline會生產全域性檢查點，如果immediate立急離線的話，就不會產生全域性檢查點）
SQL> alter tablespace lxtbs3 offline immediate;
Tablespace altered.
SQL> alter tablespace lxtbs3 online;
alter tablespace lxtbs3 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf'
SQL> recover tablespace lxtbs3;
Media recovery complete.
SQL> alter tablespace lxtbs3 online;
Tablespace altered.
--datafile offline類似於tablespace offline immediate，online之前需先recover
SQL> alter database datafile 7 offline;
Database altered.
SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf'
SQL> recover datafile 7;
Media recovery complete.
SQL> alter database datafile 7 online;
Database altered.
五、修改表空間大小
三種方式可修改表空間大小：
1）resize重置資料檔案大小（一般是往大擴，往小縮，要實際資料儲存大於設定大小會有問題）；
2）設定資料檔案自動增長，且最大值為無限大（此設定無法滿足所有要求，因為檔案大小會受作業系統限制，所以會有下面的第三種情況）；
3）新增資料檔案，從而實現擴大表空間目的。
--檢視當前表空間大小
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;
   FILE_ID FILE_NAME TABLESPACE_NAME M
---------- -------------------------------------------------- -------------------- ----------
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50
         7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 50
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50

--方式一：重置資料檔案大小
SQL> alter database datafile 7 resize 60M;
Database altered.
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;
   FILE_ID FILE_NAME TABLESPACE_NAME M
---------- -------------------------------------------------- -------------------- ----------
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50
         7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50
8 rows selected.
--方式二：設定資料檔案自動增長，且最大值為無限大
SQL> alter database datafile 7 autoextend on next 10M maxsize unlimited;
Database altered.
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;
   FILE_ID FILE_NAME TABLESPACE_NAME M
---------- -------------------------------------------------- -------------------- ----------
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50
         7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50
8 rows selected.
--方式三：新增資料檔案
SQL> alter tablespace lxtbs3 add datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf' size 50m;
Tablespace altered.
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;
   FILE_ID FILE_NAME TABLESPACE_NAME M
---------- -------------------------------------------------- -------------------- ----------
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50
         7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50
         9 /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf LXTBS3 50
9 rows selected.
六、表空間遷移
方式一：offline表空間，作業系統cp資料檔案，rename資料檔案
例：將 /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf 資料檔案遷移至disk5下
--檢視錶空間對應資料檔案資訊
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;
   FILE_ID FILE_NAME TABLESPACE_NAME M
---------- -------------------------------------------------- -------------------- ----------
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50
         7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50
         9 /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf LXTBS3 50
9 rows selected.
--執行表空間離線
SQL> alter tablespace lxtbs3 offline;
Tablespace altered.
--執行資料檔案遷移（遷移失敗，因為作業系統要先複製資料檔案至新存放地址）
SQL> alter tablespace lxtbs3 rename datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf';
alter tablespace lxtbs3 rename datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf'
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01141: error renaming data file 9 - new file '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf'
not found
ORA-01110: data file 9: '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
--作業系統複製資料檔案至新存放地址
[oracle@gc1 ~]$ cp /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf /u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf
[oracle@gc1 ~]$ cd /u01/app/oracle/oradata/PROD/disk5/
[oracle@gc1 disk5]$ ls
lstbs03.dbf temp01.dbf temp02.dbf temp03.dbf undotbs02.dbf

--再次執行資料檔案遷移
SQL> alter tablespace lxtbs3 rename datafile '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf';
Tablespace altered.
--執行表空間online

SQL> alter tablespace lxtbs3 online;
Tablespace altered.
--遷移成功後，刪除原資料檔案，並檢視最新的表空間對應資料檔案資訊
[oracle@gc1 disk5]$ rm /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;
   FILE_ID FILE_NAME TABLESPACE_NAME M
---------- -------------------------------------------------- -------------------- ----------
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50
         7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50
         9 /u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf LXTBS3 50
方式二：關庫並啟至mount狀態，作業系統cp資料檔案，rename 資料檔案
例：將上面遷移的資料檔案遷移回原位置
--關庫並啟庫至mount狀態
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 121635664 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
--作業系統複製要遷移的資料檔案至新存放位置
[oracle@gc1 disk3]$ cp /u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf
--執行資料檔案遷移（即重新命名資料檔案）
SQL> alter tablespace lxtbs3 rename datafile '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf';
alter tablespace lxtbs3 rename datafile '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf'
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter database rename file '/u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf' to '/u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf';
Database altered.
注意：最開始嘗試用第一種方式的alter tablespace rename datafile的形式重新命名資料檔案失敗，因為此語句必須在open時才可執行，mount狀態只能用alter database rename file的方式
--開庫並刪除原資料檔案
SQL> alter database open;
Database altered.
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;
   FILE_ID FILE_NAME TABLESPACE_NAME M
---------- -------------------------------------------------- -------------------- ----------
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50
         7 /u01/app/oracle/oradata/PROD/disk4/lstbs03.dbf LXTBS3 60
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50
         9 /u01/app/oracle/oradata/PROD/disk3/lstbs03.dbf LXTBS3 50
9 rows selected.
[oracle@gc1 disk3]$ rm /u01/app/oracle/oradata/PROD/disk5/lstbs03.dbf
七、刪除表空間
SQL> drop tablespace lxtbs3 including contents and datafiles;
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;
   FILE_ID FILE_NAME TABLESPACE_NAME M
---------- -------------------------------------------------- -------------------- ----------
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50
7 rows selected.
八、OMF管理表空間方式
OMF管理方式，就是設定db_create_file_dest引數（系統預設資料檔案存放位置），然後建立表空間時，不用指定資料檔案及大小，系統預設會加上這些資訊，此方式不推薦使用。
--檢視create引數資訊
SQL> show parameter create;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
--設定db_create_file_dest引數
SQL> alter system set db_create_file_dest ='/u01/app/oracle/oradata/PROD/disk3';
System altered.
SQL> create tablespace lxtbs3;
Tablespace created.
--檢視lxtbs3表空間對應資料檔案資訊
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;
   FILE_ID FILE_NAME TABLESPACE_NAME M
---------- -------------------------------------------------- -------------------- ----------
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf LXTBS1 50
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf LXTBS2 50
         7 /u01/app/oracle/oradata/PROD/disk3/PROD/datafile/o LXTBS3 100
           1_mf_lxtbs3_9ntlsdv8_.dbf
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf UNDOTBS2 50
8 rows selected.
--刪除lxtbs3表空間
SQL> drop tablespace lxtbs3 including contents and datafiles;
Tablespace dropped.

九、使用非標準塊的表空間
oracle支援五種塊，2k,4k,8k,16k,32k
oltp 8k足矣，linux和windows不支援 32K塊，unix支援32k塊。
--檢視引數快取資訊
SQL> show parameter cache;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 20
--建立16K塊表空間（失敗，因為不能與之前的8k共享快取，需設定16K的快取區大小）
SQL> create tablespace lxtbs3 datafile '/u01/app/oracle/oradata/PROD/disk3/lxtbs03.dbf' size 50m blocksize 16k;
create tablespace lxtbs3 datafile '/u01/app/oracle/oradata/PROD/disk3/lxtbs03.dbf' size 50m blocksize 16k
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
--設定16k塊快取區大小
SQL> alter system set db_16k_cache_size=40m;
System altered.
SQL> show parameter cache;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 40M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 20
--再次建立16K塊表空間
SQL> create tablespace lxtbs3 datafile '/u01/app/oracle/oradata/PROD/disk3/lxtbs03.dbf' size 50m blocksize 16k;
Tablespace created.
--檢視錶空間塊大小資訊
SQL> select tablespace_name,contents,status,block_size from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS BLOCK_SIZE
-------------------- --------- --------- ----------
SYSTEM PERMANENT ONLINE 8192
UNDOTBS UNDO ONLINE 8192
SYSAUX PERMANENT ONLINE 8192
TEMPTS TEMPORARY ONLINE 8192
USERS PERMANENT ONLINE 8192
TEMPTS2 TEMPORARY ONLINE 8192
LXTBS1 PERMANENT ONLINE 8192
LXTBS2 PERMANENT ONLINE 8192
LXTBS3 PERMANENT ONLINE 16384
UNDOTBS2 UNDO ONLINE 8192
TEMPTS3 TEMPORARY ONLINE 8192
11 rows selected.
注意，以上操作之後，若是pfile檔案啟動，還需修改pfile中的db_16k_cache_size引數資訊，因為前面設定的 alter system set db_16k_cache_size=40m;要永久生效必須改初始化引數檔案，否則重啟後，因建立了16K塊的表空間，但未設定其對應的快取區，未報空間不足錯。
--修改pfile初始化引數檔案
[oracle@gc1 dbs]$ vi initPROD.ora
表空間和資料檔案管理
十、bigfile表空間
bigfile表空間最多支援4g個資料塊，如果是8k，可達32T。
bigfile在一個表空間只能建立一個資料檔案（8k的block，datafile maxsize可以32T），可以簡化對資料檔案管理，適合於海量資料，但複製備份複雜，不推薦使用（如果盡是因為便於資料檔案管理，可採用asm方式管理檔案）。作業系統預設是smallfile表空間。

SQL> create bigfile tablespace bigtbs datafile '/u01/app/oracle/oradata/PROD/disk3/bigtbs01.dbf' size 50m autoextend
  2 on next 10m maxsize 4t;
Tablespace created.
SQL> alter tablespace bigtbs add datafile '/u01/app/oracle/oradata/PROD/disk3/bigtbs11.dbf' size 50m;
alter tablespace bigtbs add datafile '/u01/app/oracle/oradata/PROD/disk3/bigtbs11.dbf' size 50m
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1148927/，如需轉載，請註明出處，否則將追究法律責任。

相關文章