oracle表空間管理

                                           管理表空間

一： 設定表空間的隻讀狀态

1 read-only的前提條件：

表空間必須online

表空間不能是undo 表空間或者system表空間

表空間不能處于線上備份狀态

2 使表空間read-only 的語句

alter tablespace zx read only;

3 使表空間可讀可寫

alter tablespace zx read write;

4 eg：

 SQL> alter tablespace zx read only;

Tablespace altered.

SQL> alter tablespace zx read write;

二 表空間的維護

1 增加表空間的大小

 增加資料檔案

 alter tablespace zx add datafile '/oracle/CRM2/CRM/zx3.dbf' size 1m;

 調整資料檔案大小為自動增長

 alter  database datafile '/oracle/CRM2/CRM/zx2.dbf' autoextend on;

 eg: 

SQL> alter database datafile '/oracle/CRM2/CRM/zx2.dbf' autoextend on;

Database altered.

SQL> alter database datafile '/oracle/CRM2/CRM/zx2.dbf' autoextend off;

三大資料檔案表空間的建立和修改

1 大資料檔案表空間建立注意事項

指定bigfile關鍵字oracle将建立一個本地管理以及段自動管理的表空間。

建立時可以指定 extent management local和segment space management auto。

如果指定 extent management dictionary 或者 segment space managemnet manual 将報錯。

2 語句

create bigfile tablespace zxbigtbs 

            datafile '/oracle/CRM2/CRM/zxbig1.dbf' size 1G;

size 機關可以指定k/m/g/t/

3 查詢資料庫内部的大表空間

 可查詢視圖dba_tablespaces、user_tablespaces、v$tablespace 的bigfile字段

 eg：

SQL> select tablespace_name,bigfile from dba_tablespaces;

TABLESPACE_NAME                BIG

------------------------------ ---

SYSTEM                         NO

SYSAUX                         NO

TEMP                           NO

USERS                          NO

UNDOTBS2                       NO

ZX                             NO

ZXBIGTBS                       YES

4 修改大資料檔案的表空間

調整大小

alter tablespace zxbigtbs resize 2G;

調整資料檔案自動擴充

alter tablespace zxbigtbs autoextend on next 20G;

四 臨時表空間

 1 新安裝的oracle 預設會建立一個temp臨時表空間

   使用者可以建立額外的臨時表空間

   可以給資料庫每一個使用者指定一個臨時表空間

   使用者不能明确的在臨時表空間中建立對象

 2  預設的臨時表空間

 如果使用者沒有明确指定臨時表空間，則使用者預設臨時表空間為temp

 更改資料庫預設臨時表空間

 alter database default temporary tablespace 表空間名字；

 确認目前資料庫預設的臨時表空間

 select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

PROPERTY_NAME                  PROPERTY_VALUE

------------------------------ ------------------------------

DEFAULT_TEMP_TABLESPACE        TEMP

3 建立本地管理的臨時表空間

create temporary tablespace mytemp tempfile '/oracle/CRM2/CRM/mytemp.dbf' size 200M autoextend on next 10M maxsize 1G;

4 建立一個大資料檔案的臨時表空間以及對該大表空間的修改

create bigfile temporary tablespace mynewtemp tempfile '/oracle/CRM2/CRM/newtemp.dbf' size 2G;

SQL> alter tablespace mynewtemp resize 3G;

SQL> alter tablespace mynewtemp autoextend on next 20G maxsize 40G;

5 11g下查詢臨時表空間的使用率

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

------------------------------ --------------- --------------- ----------

TEMP                                 228589568       228589568  227540992

MYNEWTEMP                           3221225472         1048576 3220176896

5 臨時表空間組

臨時表空間組的特性：

a 至少包括一個表空間，對表空間個數無明确限制

b 可以把臨時表空間組名，作為資料庫預設的表空間，或者配置設定給使用者。

c 如果臨時表空間組被作為預設的臨時表空間，則不能直接丢棄組内任何成員，需要先從組内移除該表空間。

建立臨時表空間組

  臨時表空間組不能直接被建立，隻能間接建立

  語句：

  方式一： create temporary tablespace ltemp1 tempfile '/oracle/CRM2/CRM/ltemp01.dbf' size 50M tablespace group tempgroup1;

  方式二：  create temporary tablespace ltemp2 tempfile '/oracle/CRM2/CRM/ltemp02.dbf' size 50M;

         alter tablespace ltemp2 tablespace group tempgroup2;

eg:

方式一：SQL>  create temporary tablespace ltemp1 tempfile '/oracle/CRM2/CRM/ltemp01.dbf' size 50M tablespace group tempgroup1;

Tablespace created.

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME

TEMPGROUP1                     LTEMP1

方式二：SQL> create temporary tablespace ltemp2 tempfile '/oracle/CRM2/CRM/ltemp02.dbf' size 50M;

SQL> alter tablespace ltemp2 tablespace group tempgroup2;

TEMPGROUP2                     LTEMP2

對臨時表空間組的修改

移動組内的成員語句：alter tablespace ltemp2 tablespace group tempgroup1;

從組内删除某個臨時表空間語句： alter tablespace ltemp2 tablespace group ‘’；

eg: 移動組内成員（注意ltemp2屬于組tempgroup2也可以直接從組2移動到組1）

   SQL> select * from dba_tablespace_groups;

SQL> alter tablespace ltemp2 tablespace group tempgroup1;

TEMPGROUP1                     LTEMP2

eg：删除組内某個臨時表空間

SQL> alter tablespace ltemp2 tablespace group '';

配置設定一個臨時表空間組作為資料庫預設臨時表空間

語句：alter database default temporary tablespace 組名; 

eg 

SQL> alter database default temporary tablespace tempgroup1;

Database altered

select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

DEFAULT_TEMP_TABLESPACE        TEMPGROUP1

五：重命名表空間

注意事項

1 compatible 參數必須設定成10.0.0 或更高

2 不能重命名system、sysaux表空間

3 重命名時應先檢查表空間對應的資料位置是否離線，如果離線，重命名則報錯。

4 最好表空間read write 否則資料檔案頭部記錄的表空間名不更新。

5 當重命名時，将更新所有和該表空間有關的資料字典，控制檔案，和線上資料檔案頭部。

6 如果表空間是臨時表空間，更名後，視圖database_properties将自動更新并使用新的臨時表空間名。

語句 alter tablespace 表空間名 rename to  新表空間名

eg：

SQL> alter tablespace mytemp rename to testtemp;

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

TEMP

USERS

UNDOTBS2

ZX

ZXBIGTBS

TESTTEMP

LTEMP1

LTEMP2

六 drop 表空間

參數說明：

including contents 将丢棄表空間所有段 如果表空間是空的，不包括任何表，視圖，結構，則不需要指定該參數

cascade constraints 将丢棄所有引用和限制

including contents and datafiles 丢棄表空間的同時删除資料檔案

語句drop tablespace 表空間 including contents and datafiles;

eg

10 rows selected.

SQL> drop tablespace testtemp including contents and datafiles;

Tablespace dropped.

本文轉自 zhangxuwl 51CTO部落格，原文連結：http://blog.51cto.com/jiujian/1035174，如需轉載請自行聯系原作者