Oracle Create Tablespace
Oracle Tablespace空間查詢
col TABLESPACE_NAME format a20
col Size(MB) format a10
col Avail(MB) format a10
col Used(MB) format a10
select
a.TABLESPACE_NAME,
to_char(a.BYTES/(1024*1024),'999,999') "Size(MB)",
to_char(round((a.BYTES-b.BYTES)/(1024*1024),0),'999,999') "Used(MB)",
to_char(b.BYTES/(1024*1024),'999,999') "Avail(MB)"
from
(select TABLESPACE_NAME, sum(BYTES) "BYTES" from dba_data_files group by tablespace_name) a,
(select TABLESPACE_NAME,sum(BYTES) "BYTES" from dba_free_space group by tablespace_name) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
Create Tablespace
create tablespace <tablespace_name>
datafile <file_name> size <file_size>
default storage (<儲存格式>)
online | offline
<儲存格式>包含
initial 初始區段大小
next 下個區段大小
minextents 最小區段數(次數)
maxextents 最大區段數(次數)
pctincrease 第二區段後,每區段比前一區段增長百分比
EX
1.Linux
create tablespace my_space
datafile '/opt/oracle/oradata/my_space/myspace.ora' size 100M
default storage (initial 10k next 50k minextents 1 maxextents 9999
pctincrease 10)
2.windows
create tablespace mis_space
datafile 'C:\oracle\product\10.2.0\oradata\mis_space\mis_space.ora' size 100M
default storage (initial 10k next 50k minextents 1 maxextents 9999
pctincrease 10)
p.s 不用設定TEMPORARY TABLESPACE，因為8i以後就不用設
p.s 檔案副檔名.ORA 與.DBF功用待查.(一般都創建.DBF)
--------------------------------------------------------------------------
如何擴大已經建立的tablespace?
1.在原本的tablespace增加data file
操作實例:
SQL> alter tablespace frankts add datafile size 10M; (有使用OMF喔!)
Tablespace altered.
2.擴大data file size
操作實例:
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF' resize 90M;
Database altered.
在這邊要注意不可以把data file改的比目前已經使用的空間還要小 不然會有error喔!
操作實例:
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF' resize 80M;
alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF' resize 80M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
3.讓他自動長大
create tablespace demots datafile size 10M autoextend on NEXT 10M maxsize 100M
----------------------------------------------------------------------------------------------
1.创建tablespace test：表示创建一个名为test的tablespace，test的大小为8M，自动增长步长为5M，最大空间是10M。
create tablespace test datafile '/home/app/oracle/oradata/oracle8i/test01.dbf' size 8M autoextend on next 5M maxsize 10M;
2.创建tablespace sales：表示创建一个名为sales的tablespace，sales的大小为800M，自动增长步长为50M，没有最大空间限制。
create tablespace sales datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M autoextend on next 50M maxsize unlimited maxsize UNLIMITED; --是大小不受限制
3.创建tablespace sales：表示创建一个名为sales的tablespace，sales的大小为800M，自动增长步长为50M，最大空间为1000M，
采用local管理方式，unform表示区的大小相同，默认为1000M。
create tablespace sales datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M autoextend on next 50M maxsize 1000M extent management local uniform; --unform表示区的大小相同，默认为1000M
4.创建tablespace sales：表示创建一个名为sales的tablesapce，sales的大小为800M，自动增长步长为50M，最大空间为1000M，
采用local管理方式，uniform表示区的大小相同为500M。
create tablespace sales datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M autoextend on next 50M maxsize 1000M extent management local uniform size 500K; --unform size 500K;表示区的大小相同，为500K
5.创建tablespace sales：表示创建一个名为sales的tablespace，sales的大小为800M，自动增长步长为50M，最大空间为1000M，
采用local管理方式，automaticate表示区的大小由随表的大小自动动态改变，大表使用大的分区，小表使用小的分区。
create tablespace sales datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M autoextend on next 50M maxsize 1000M extent management local autoallocate; --autoallocate表示区的大小由随表的大小自动动态改变，大表使用大区小表使用小区
6.创建tablespace sales：表示创建一个名为sales的tablespace，sales的大小为800M，自动增长步长为50M，最大空间为1000M，
temporary表示创建字典管理临时表空间。
create tablespace sales datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M autoextend on next 50M maxsize 1000M temporary; --temporary创建字典管理临时表空间
7.创建临时tablespace sales：表示创建一个名为sales的temporary tablespace，并且datafile变成tempfile。
create temporary tablespace sales tempfile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M autoextend on next 50M maxsize 1000M --创建本地管理临时表空间，如果是临时表空间，所有语句中的datafile都换为tempfile 8i系统默认创建字典管理临时表空间，要创建本地管理临时表空间要加temporary tablespace关键字
8.创建本地管理临时表空间时，不得使用atuoallocate参数，系统默认创建uniform管理方式 为表空间增加数据文件：
alter tablespace sales add datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800M autoextend on next 50M maxsize 1000M;
9.为表空间增加数据文件
alter tablespace sales add datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800M autoextend on next 50M maxsize 1000M;
10.更改自动扩展属性
alter database datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf', '/home/app/oracle/oradata/oracle8i/sales02.dbf' '/home/app/oracle/oradata/oracle8i/sales01.dbf autoextend off;
11.删除表空间
Drop tablespace test INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
----------------------------------------------------------------------------------------------
TableSpace介紹 (轉貼)
簡介
TableSpace 是Oracle空間管理上的邏輯單位，實體上存放資料的是Tablespace裡面的檔案(Data File)；而我們所熟悉的Table就放在這 一個一個的檔案裡面。所以TableSpace可以看成是Data File的群組。通常在管理上會把使用者的資料與Oracle系統的物件以不同的Tablespace做切分 。如果一個Oracle上有多個不同的AP系統，以不同的TableSpace做切割，則可以達到管理與備份的目的。但是TableSpace的功用也不僅僅只是簡單的群組分類而已，Oracle 提供了許多功能上的參數來設定TableSpace來達到空間管理與效能增進的目的。
有必要提的是，TableSpace沒辦法跨資料庫，TableSpace中的Data File沒辦法跨TableSpace，Data File中的Table (Segement)可以跨 Data File，但不能跨TableSpace。簡單的來說，一個Table裡面的資料是有可能因為Oracle的空間分配而分布在同一個TableSpace的 不同的Data File中的；因此一個Data File創出來後，是不能隨便刪除的，這將會造成嚴重的資料損毀的問題。
SYSTEM與Non-SYSTEM TableSpace
當資料庫剛建立起來，系統會建立一個叫做SYSTEM的系統TableSpace，存放SYS、SYSTEM等User重要的系統資料(ex:資料字典與預儲程序等) 如果我們建立Oracle User時，不指定預設的TableSpace，則此User則會以SYSTEM TableSpace作為預設的TableSpace。 這將造成管理上的混亂與嚴重的效能問題，這是必須特別注意的。
TableSpace的類型：Permanent、Undo、Temporary
l Permanent Tablespace
一般我們創建給AP使用的都是Permanent Tablespace。裡面物件的生命週期不會隨著交易或者 使用者的session結束而消失。
l Ｕndo Tablespace
Undo Tablespace是系統用的特殊的Tablespace，用來取代過去的rollback segement的機制，主要的功用是提供使用者修改資料未commit之前的read consistency的功能以及rollback交易的功能。 也因為undo tablespace主要是取代過去的rollback segement的機制，所以不能存放其他種類的segement。 undo tablespace只能是local managed。
l Temporary Tablespace
Ｔemporary Tablespace也是系統用的特殊的Tablespace。當使用者需要做排序時， 有時就會使用Temporary Tablespace，因此裡面的Segement的生命週期都很短，可能交易結束或者Ｕser的Session結束就會消失。每個系統都必須要有一個預設的Temporary Tablespace（Default Temporary Tablespace）， 如果沒有的話，忘了指定使用哪個Temporary Tablespace的使用者會以SYSTEM tablespace來當作Temporary Tablespace，這樣就很糟糕。 以下列出幾個Temporary Tablespace的特性。
1. Temporary Tablespace是NOLOGGING模式，因此若資料庫損毀，做Recovery不需要恢復Temporary Tablespace。
2. Temporary最好是使用Local managed Tablespace
3. 若使用local managed模式，UNIFORM SIZE參數最好是Sort_Area_Size的參數，這樣效能比較好。
4. Uniform size預設1024K，而Sort_area_size預設是512K
5. Temporary Tablespace不能使用local managed的AUTOALLOCATE參數。
TableSpace的Extent空間管理：Local Managed 與Dictionary Managed
Local Managed 與Dictionary Managed最主要的分別，在於空間管理方式的不同。Local managed 的管理方式是讓 每個TableSpace自己利用bitmaps去管理他自己的空間，而Dictionary Managed則是利用SYSTEM TableSpace的資料字典來做空間管理。 這兩者最大的不同在於Local managed大大的改善了Ｏracle做空間管理（例如:產生新的Exten或釋放Extent...等）時，搶奪 SYSTEM TableSpace資源的問題。所以Ｏracle從8i以後已經朝Local managed的方向去走了，所以我們應該盡量使用Local managed的方式才對，所以Dictionary managed的方式不多做介紹了。
l Local managed tablespace
1. Local managed使用bitmaps做空間管理。
2. bitmaps中每個bit代表一個data block或者一堆相鄰的data block(extent)
3. 從10g開始，SYSTEM Tablespace預設使用local managed -->Oracle建議使用local managed的證據。
4. 假如SYSTEM TableSpace是local managed，那麼其他TableSpace必須是local managed。
5. 若沒指定使用local managed或者dictionary managed，則預設使用local managed。
6. 使用local managed可以增進效能，因為減少了SYSTEM TableSpace的效能競爭。
7. 使用local managed則不需要做空間縫合(loalescing)，因為相鄰的不同大小的extent，辨識extent使用狀態的bits也在一起 ，Oracle可以直接使用這些相鄰的extent。不需要先進行縫合才可以使用。這也可以增進部份效能。
l local managed的extent空間管理(Extent Management):AUTOALLOCATE與UNIFORM
AUTOALLOCATE與UNIFORM這兩個參數，是用來設定Local managed的extent大小的參數。AUTOALLOCATE是讓Oracle自己來決定extent的大小；而UNIFORM則是強制規定TableSpace中extent的為固定的大小。 通常若你明確的知道extent必須多大，才會使用UNIFORM，使用UNIFORM的好處是每個extent的大小都相同，不會產生空間破碎的問題。但是如果無法預知extent必須多大，使用AUTOALLOCATE會比較好，讓Oracle自己決定使用extent的大小，可以比較符合實際的需求 ，因此會比較節省空間，但是這可能會產生部分空間破碎的問題。使用AUTOEXTENT,Oracle會使用的extent大小為64k、1M、8M、64M。 根據我系統上使用的結果，99.95%的extent都是使用64k、只有少部分使用1M 的extent，所以其實破碎的情況不嚴重，使用AUTOALLOCATE 在我的系統上其實就夠用了。想知道你的TableSpace所使用的Extent有幾種，請用下列的語法：
Select bytes,count(*) from dba_extents where tablespace_name='your_tablespace_name' group by bytes
l Local managed中的Segment的空間管理(Segment Space Management ):AUTO與MANUAL
Tablespace中的Segment的空間管理上，可以設置的參數為AUTO與MANUAL。MANUAL是使用 我們熟悉的PCTUSED、FREELISTS、FREELIST GROUPS的方式來管理Segment中的data block；而AUTO則是 使用bitmaps來管理data block。使用AUTO來管理的話，以往create tablespace或create table時設定的 storage的參數設定都不需要再設定了，因為data block的管理已經是bitmaps了，不再是free list了。如果沒有特別的需求話 ，使用AUTO會比使用MANUAL有更好的空間利用率，與效能上的提升。
l Local managed中的Create TableSpace範例
Create Tablespace MYDATA
Datafile '/u1/oradata/mydb/mydata1.dbf' size 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED ,
'/u1/oradata/mydb/mydata2.dbf' size 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
Extent Management Local
Segment Space Management Auto;
l dictionary managed中的Create TableSpace範例
Create Tablespace MYDATA
Datafile '/u1/oradata/mydb/mydata1.dbf' size 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
Extent Management Dictionary
Default Storage (
Initial 64k
next 64k
minextents 2
maxextents unlimited
pctincrease 0 );