oracle表空间操作详解
1
oracle表空间操作详解
2![](/Images/OutliningIndicators/None.gif)
3
作者: 来源: 更新日期:2006-01-04
4
5
6
7
建立表空间
8![](/Images/OutliningIndicators/None.gif)
9
CREATE TABLESPACE data01
10
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M
11
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定,区尺寸默认为64k
12![](/Images/OutliningIndicators/None.gif)
13
删除表空间
14![](/Images/OutliningIndicators/None.gif)
15
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
16![](/Images/OutliningIndicators/None.gif)
17
修改表空间大小
alter database datafile '/path/NADDate05.dbf' resize 100M
移动表至另一表空间
alter table move tablespace room1;
18
一、建立表空间
19![](/Images/OutliningIndicators/None.gif)
20
CREATE TABLESPACE data01
21
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M
22
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定,区尺寸默认为64k
23![](/Images/OutliningIndicators/None.gif)
24
二、建立UNDO表空间
25![](/Images/OutliningIndicators/None.gif)
26
CREATE UNDO TABLESPACE UNDOTBS02
27
DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M
28![](/Images/OutliningIndicators/None.gif)
29
#注意:在OPEN状态下某些时刻只能用一个UNDO表空间,如果要用新建的表空间,必须切换到该表空间:
30![](/Images/OutliningIndicators/None.gif)
31
ALTER SYSTEM SET undo_tablespace=UNDOTBS02;
32![](/Images/OutliningIndicators/None.gif)
33
三、建立临时表空间
34![](/Images/OutliningIndicators/None.gif)
35
CREATE TEMPORARY TABLESPACE temp_data
36
TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M
37![](/Images/OutliningIndicators/None.gif)
38
四、改变表空间状态
39![](/Images/OutliningIndicators/None.gif)
40
1.使表空间脱机
41![](/Images/OutliningIndicators/None.gif)
42
ALTER TABLESPACE game OFFLINE;
43![](/Images/OutliningIndicators/None.gif)
44
如果是意外删除了数据文件,则必须带有RECOVER选项
45![](/Images/OutliningIndicators/None.gif)
46
ALTER TABLESPACE game OFFLINE FOR RECOVER;
47![](/Images/OutliningIndicators/None.gif)
48
2.使表空间联机
49![](/Images/OutliningIndicators/None.gif)
50
ALTER TABLESPACE game ONLINE;
51![](/Images/OutliningIndicators/None.gif)
52![](/Images/OutliningIndicators/None.gif)
53
3.使数据文件脱机
54![](/Images/OutliningIndicators/None.gif)
55
ALTER DATABASE DATAFILE 3 OFFLINE;
56![](/Images/OutliningIndicators/None.gif)
57
4.使数据文件联机
58![](/Images/OutliningIndicators/None.gif)
59
ALTER DATABASE DATAFILE 3 ONLINE;
60![](/Images/OutliningIndicators/None.gif)
61
5.使表空间只读
62![](/Images/OutliningIndicators/None.gif)
63
ALTER TABLESPACE game READ ONLY;
64![](/Images/OutliningIndicators/None.gif)
65
6.使表空间可读写
66![](/Images/OutliningIndicators/None.gif)
67
ALTER TABLESPACE game READ WRITE;
68![](/Images/OutliningIndicators/None.gif)
69
五、删除表空间
70![](/Images/OutliningIndicators/None.gif)
71
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
72![](/Images/OutliningIndicators/None.gif)
73
六、扩展表空间
74![](/Images/OutliningIndicators/None.gif)
75
首先查看表空间的名字和所属文件
76![](/Images/OutliningIndicators/None.gif)
77
select tablespace_name, file_id, file_name,
78
round(bytes/(1024*1024),0) total_space
79
from dba_data_files
80
order by tablespace_name;
81![](/Images/OutliningIndicators/None.gif)
82
1.增加数据文件
83
ALTER TABLESPACE game
84
ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;
85![](/Images/OutliningIndicators/None.gif)
86
2.手动增加数据文件尺寸
87
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'
88
RESIZE 4000M;
89![](/Images/OutliningIndicators/None.gif)
90
3.设定数据文件自动扩展
91
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf
92
AUTOEXTEND ON NEXT 100M
93
MAXSIZE 10000M;
94![](/Images/OutliningIndicators/None.gif)
95
设定后查看表空间信息
96![](/Images/OutliningIndicators/None.gif)
97
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
98
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
99
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
100
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
101
102
103![](/Images/OutliningIndicators/None.gif)
104![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
alter database datafile '/path/NADDate05.dbf' resize 100M
移动表至另一表空间
alter table move tablespace room1;
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
53
![](/Images/OutliningIndicators/None.gif)
54
![](/Images/OutliningIndicators/None.gif)
55
![](/Images/OutliningIndicators/None.gif)
56
![](/Images/OutliningIndicators/None.gif)
57
![](/Images/OutliningIndicators/None.gif)
58
![](/Images/OutliningIndicators/None.gif)
59
![](/Images/OutliningIndicators/None.gif)
60
![](/Images/OutliningIndicators/None.gif)
61
![](/Images/OutliningIndicators/None.gif)
62
![](/Images/OutliningIndicators/None.gif)
63
![](/Images/OutliningIndicators/None.gif)
64
![](/Images/OutliningIndicators/None.gif)
65
![](/Images/OutliningIndicators/None.gif)
66
![](/Images/OutliningIndicators/None.gif)
67
![](/Images/OutliningIndicators/None.gif)
68
![](/Images/OutliningIndicators/None.gif)
69
![](/Images/OutliningIndicators/None.gif)
70
![](/Images/OutliningIndicators/None.gif)
71
![](/Images/OutliningIndicators/None.gif)
72
![](/Images/OutliningIndicators/None.gif)
73
![](/Images/OutliningIndicators/None.gif)
74
![](/Images/OutliningIndicators/None.gif)
75
![](/Images/OutliningIndicators/None.gif)
76
![](/Images/OutliningIndicators/None.gif)
77
![](/Images/OutliningIndicators/None.gif)
78
![](/Images/OutliningIndicators/None.gif)
79
![](/Images/OutliningIndicators/None.gif)
80
![](/Images/OutliningIndicators/None.gif)
81
![](/Images/OutliningIndicators/None.gif)
82
![](/Images/OutliningIndicators/None.gif)
83
![](/Images/OutliningIndicators/None.gif)
84
![](/Images/OutliningIndicators/None.gif)
85
![](/Images/OutliningIndicators/None.gif)
86
![](/Images/OutliningIndicators/None.gif)
87
![](/Images/OutliningIndicators/None.gif)
88
![](/Images/OutliningIndicators/None.gif)
89
![](/Images/OutliningIndicators/None.gif)
90
![](/Images/OutliningIndicators/None.gif)
91
![](/Images/OutliningIndicators/None.gif)
92
![](/Images/OutliningIndicators/None.gif)
93
![](/Images/OutliningIndicators/None.gif)
94
![](/Images/OutliningIndicators/None.gif)
95
![](/Images/OutliningIndicators/None.gif)
96
![](/Images/OutliningIndicators/None.gif)
97
![](/Images/OutliningIndicators/None.gif)
98
![](/Images/OutliningIndicators/None.gif)
99
![](/Images/OutliningIndicators/None.gif)
100
![](/Images/OutliningIndicators/None.gif)
101
![](/Images/OutliningIndicators/None.gif)
102
![](/Images/OutliningIndicators/None.gif)
103
![](/Images/OutliningIndicators/None.gif)
104
![](/Images/OutliningIndicators/None.gif)
转载自 http://www.initpower.com/print.php?id=103