Oracle各种表空间

system表空间：含数据字典信息

sysaux表空间：存储各种oracle应用的元数据（如AWR的操作数据）

创建表空间后，不能改变区尺寸

区尺寸管理：
自动分配（AUTOALLOCATE）：小段64K,中段1M,大段64M
统一分配（UNIFORM):需跟size，如果认为表空间的所有段的尺寸大致相同，并且认为它们会以一种相似方式增长，可以选择uniform区尺寸管理。

手动段空间管理，须使用可用列表（Free list）,pctfree,pctused

通过pctfree参数可以在每个数据块中保留一定比例的空间。

自动段空间管理，用位图跟踪一个段的可用空间的可用性。

创建表空间
create tablespace datafile 创建永久表空间
create temporary tablespace tempfile
create undo tablespace

SQL>r
1 create tablespace test1
2 datafile ‘/u02/app/oracle/test1.dbf‘
3* size 1M
Tablespace created.

SQL>r
1* select tablespace_name,allocation_type,contents,extent_management,segment_space_management from dba_tablespaces

TABLESPACE_NAME ALLOCATION_type CONTENTS EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT
----------------------------- --------- --------- -------------------- -------------------------
SYSTEM SYSTEM permanent LOCAL MANUAL
SYSAUX SYSTEM PERMANENT LOCAL AUTO
UNDOTBS1 SYSTEM UNDO LOCAL MANUAL
TEMP uniform TEMPORARY LOCAL MANUAL
USERS SYSTEM PERMANENT LOCAL AUTO
TEST1 SYSTEM PERMANENT LOCAL AUTO

create tablespace test3
datafile ‘/u02/app/oracle/test3.dbf‘ size 8m
uniform size 4m;

如果区分配使用uniform未指定size，默认创建1M统一区

SQL>select initial_extent,next_extent,extent_management,allocation_type,segment_space_management,tablespace_name from dba_tablespaces;

INITIAL_EXTENT NEXT_EXTENT EXTENT_MANAGEMENT ALLOCATIO SEGMENT_SPACE_MANAGEMENT TABLESPACE_NAME
-------------- ----------- -------------------- --------- ------------------------- ------------------------------
65536 LOCAL SYSTEM MANUAL SYSTEM
65536 LOCAL SYSTEM AUTO SYSAUX
65536 LOCAL SYSTEM MANUAL UNDOTBS1
1048576 1048576 LOCAL UNIFORM MANUAL TEMP
65536 LOCAL SYSTEM AUTO USERS
65536 LOCAL SYSTEM AUTO TEST1
4194304 4194304 LOCAL UNIFORM AUTO TEST3

增加表空间大小：
alter tablespace test1 add datafile ‘/u02/app/oracle/test12.dbf‘ size 1m ;
alter database datafile ‘/u02/app/oracle/test1.dbf‘ resize 20m ;

请求表空间时，自动给表空间增加10M，MAXSIZE限定表空间为100M
alter tablespace test3 add datafile ‘/u02/app/oracle/test31.dbf‘ size 10m
autoextend on
next 10m
maxsize 100m

删除表空间
drop tablespace test4;
drop tablespace test4 INCLUDING CONTENTS AND DATAFILES;
drop tablespace test3 cascade constraints;删除表的引用完整性约束

SQL>desc v$sysaux_occupants
Name Null? Type
----------------------------------------- -------- ----------------------------
OCCUPANT_NAME VARCHAR2(64)
OCCUPANT_DESC VARCHAR2(64)
SCHEMA_NAME VARCHAR2(64)
MOVE_PROCEDURE VARCHAR2(64)
MOVE_PROCEDURE_DESC VARCHAR2(64)
SPACE_USAGE_KBYTES NUMBER

select occupant_name,schema_name,move_procedure from v$sysaux_occupants;

OCCUPANT_NAME SCHEMA_NAM MOVE_PROCEDURE
-------------------- ---------- ----------------------------------------------------------------

EM SYSMAN emd_maintenance.move_em_tblspc
TEXT CTXSYS DRI_MOVE_CTXSYS
ULTRASEARCH WKSYS MOVE_WK
ULTRASEARCH_DEMO_USE WK_TEST MOVE_WK

如果希望将sysaux的占有物ULTRASEARCH移动到一个新的表空间ULTRA1，可以使用WKSYS模式拥有的MOVE_WK过程来完成
exect WKSYS.MOVE_WK(‘ULTRA1‘);

预警极限值
预警极限值类型：百分比满，字节剩余数
设置预警极限值：OEM--administration--related links--manage metrics--edit thresholds
OEM--相关链接--度量和策略设置--编辑

SQL>BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id =>DBMS_SERVER_ALTER.TABLESPACE_BYT_FREE,
warning_operator =>DBMS_SERVER_ALTER.OPERATOR_LE,
warning_value =>‘10240‘,
critical_operator =>DBMS_SERVER_ALTER.OPERATOR_LE,
critical_value =>‘2048‘,
observation_period =>1,
consecutive_occurrences =>1,
instance_name =>NULL,
object_type =>DBMS_SERVER_ALTER.OBJECT_TYPE_TABLESPACE,
object_name =>‘users‘);
END;

以上警告极限值warning_value为10M，严重极限值critical_value为2M

SQL>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

TABLESPACE_N TOTAL USED FREE % USED % FREE
------------ ---------- ---------- ---------- ---------- ----------
SYSAUX 629145600 545652736 82444288 86.72 13.28
UNDOTBS1 550502400 44302336 505151488 8.04 91.96
USERS 5242880 327680 3866624 6.25 73.75
SYSTEM 734003200 684457984 48496640 93.25 6.65

表空间脱机
alter tablespace test1 offline;

重命名表空间
alter tablespace test1 rename to test2;

select file#, name from v$datafile

FILE# NAME
---------- --------------------------------------------------
1 +DATA/ora11g/datafile/system.260.823205117
2 +DATA/ora11g/datafile/sysaux.261.823205185
3 +DATA/ora11g/datafile/undotbs1.262.823205249
4 +DATA/ora11g/datafile/users.264.823205299
5 /u02/app/oracle/test1.dbf
6 /u02/app/oracle/test3.dbf
8 /u02/app/oracle/test12.dbf
9 /u02/app/oracle/test31.dbf

select tablespace_name, file_name,file_id,autoextensible from dba_data_files;

TABLESPACE_N FILE_NAME FILE_ID AUTOEXTENSIBLE
------------ -------------------------------------------------- ---------- --------------------
SYSTEM +DATA/ora11g/datafile/system.260.823205117 1 YES
SYSAUX +DATA/ora11g/datafile/sysaux.261.823205185 2 YES
UNDOTBS1 +DATA/ora11g/datafile/undotbs1.262.823205249 3 YES
USERS +DATA/ora11g/datafile/users.264.823205299 4 YES
TEST1 /u02/app/oracle/test1.dbf 5 NO
TEST3 /u02/app/oracle/test3.dbf 6 NO
TEST1 /u02/app/oracle/test12.dbf 8 NO
TEST3 /u02/app/oracle/test31.dbf 9 YES

重命名数据文件，须使表空间脱机，以致数据文件脱机
alter tablespace test1 offline
在os下：cp或mv
在db下：alter tablespace test1 rename datafile ‘/u01/app/oracle/test1.dbf‘ to ‘/u02/app/oracle/test1.dbf‘;

只读表空间
alter tablespace test1 read only;
alter tablespace test1 read write;

在临时表空间内排序

默认时，所有临时表空间都是用统一尺寸的本地管理的区来创建。

在给一个表空间组分配第一个临时表空间时，就自动创建了该临时表空间组。
alter tablespace temp1 tablespace group group1;如果group1不存在，改语句将会自动创建group1