1
//
把下面显示文本保存到TablespaceStudy.txt文件中
2
3 SQL > spool ' F:\app\Administrator\oradata\TablespaceStudy.txt ' ;
4
5 // 创建临时表空间
6
7 SQL > create temporary tablespace user_temp
8
9 1 tempfile ' F:\app\Administrator\oradata\user_temp.dbf '
10
11 2 size 50m
12
13 3 autoextend on
14
15 4 next 50m maxsize 20480m
16
17 5 extent management local;
18
19 // 创建数据表空间
20
21 SQL > create tablespace user_data
22
23 2 logging
24
25 3 datafile ' F:\app\Administrator\oradata\user_data.dbf '
26
27 4 size 50m
28
29 5 autoextend on
30
31 6 next 50m maxsize 20480m
32
33 7 extent management local;
34
35
36
37 // 创建用户并指定表空间
38
39 SQL > create user username identified by password
40
41 2 default tablespace user_data
42
43 3 temporary tablespace user_temp;
44
45
46
47 // 给用户授予权限
48
49
50 SQL > grant connect,resource to username;
51
52
53 SQL > connect system / as sysdba;
54 已连接。
55 SQL > select tablespace_name, file_id , file_name , round (bytes / ( 1024 * 1024 ), 0 ) total_sapce from dba_data_files order by tablespace_name;
56
57 TABLESPACE FILE_ID FILE_NAME TOTAL_SAPCE
58 -- -------- ---------- ------------------------------ -----------
59 EXAMPLE ########## F:\APP\ADMINISTRATOR\ORADATA\O ##########
60 RCL\EXAMPLE01.DBF
61
62 SYSAUX ########## F:\APP\ADMINISTRATOR\ORADATA\O ##########
63 RCL\SYSAUX01.DBF
64
65 SYSTEM ########## F:\APP\ADMINISTRATOR\ORADATA\O ##########
66 RCL\SYSTEM01.DBF
67
68 UNDOTBS1 ########## F:\APP\ADMINISTRATOR\ORADATA\O ##########
69 RCL\UNDOTBS01.DBF
70
71 USERS ########## F:\APP\ADMINISTRATOR\ORADATA\O ##########
72 RCL\USERS01.DBF
73
74 USER_DATA ########## F:\APP\ADMINISTRATOR\ORADATA\U ##########
75 SER_DATA.DBF
76
77
78
79 SQL > alter tablespace user_data
80 2 add datafile ' F:\app\Administrator\oradata\user_data2.dbf '
81 3 size 10m
82 4 autoextend on
83 5 maxsize 100m
84 6 ;
85
86 表空间已更改。
87
88
89 SQL > select file_name , file_id ,tablespace_name from dba_data_files;
90
91 FILE_NAME FILE_ID TABLESPACE
92 -- ---------------------------- ---------- ----------
93 F:\APP\ADMINISTRATOR\ORADATA\O ########## USERS
94 RCL\USERS01.DBF
95
96 F:\APP\ADMINISTRATOR\ORADATA\O ########## UNDOTBS1
97 RCL\UNDOTBS01.DBF
98
99 F:\APP\ADMINISTRATOR\ORADATA\O ########## SYSAUX
100 RCL\SYSAUX01.DBF
101
102 F:\APP\ADMINISTRATOR\ORADATA\O ########## SYSTEM
103 RCL\SYSTEM01.DBF
104
105 F:\APP\ADMINISTRATOR\ORADATA\O ########## EXAMPLE
106 RCL\EXAMPLE01.DBF
107
108 F:\APP\ADMINISTRATOR\ORADATA\U ########## USER_DATA
109 SER_DATA.DBF
110
111 F:\APP\ADMINISTRATOR\ORADATA\U ########## USER_DATA
112 SER_DATA2.DBF
113
114
115 已选择7行。
116
117 SQL > select file_name , file_id ,tablespace_name from dba_data_files where tablespace_name = ' USER_DATA ' ;
118
119 FILE_NAME FILE_ID TABLESPACE
120 -- ---------------------------- ---------- ----------
121 F:\APP\ADMINISTRATOR\ORADATA\U ########## USER_DATA
122 SER_DATA.DBF
123
124 F:\APP\ADMINISTRATOR\ORADATA\U ########## USER_DATA
125 SER_DATA2.DBF
126
127 删除表空间数据文件
128 SQL > alter tablespace user_data
129 2 drop datafile ' F:\app\Administrator\oradata\USER_DATA2.DBF ' ;
130
131 表空间已更改。
132
133 SQL > select file_name , file_id ,tablespace_name from dba_data_files where tablespace_name = ' USER_DATA ' ;
134
135 FILE_NAME FILE_ID TABLESPACE
136 -- ---------------------------- ---------- ----------
137 F:\APP\ADMINISTRATOR\ORADATA\U ########## USER_DATA
138 SER_DATA.DBF
139
140
141 SQL > connect username / password;
142 已连接。
143 SQL > create table Customer(ID int , Name varchar ( 30 )) tablespace user_data;
144 成功创建表
145
146 查看当前用户下的表
147 SQL > select * from tab;
148 TNAME TABTYPE CLUSTERID
149 -- ---------------------------- ------- ---------
150 Customer TABLE
151
152 SQL > desc customer;
153 名称 类型
154 ID UMBER( 38 )
155 NAME Varchar2
156
157 -- 切换到system账户
158 SQL > connect system / as sysdba;
159
160 SQL > select * from v$instance;
161
162 INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS
163
164 1 orcl WWW - 81A02E68C5B 11.1 . 0.7 . 0 04 - 3月 - 11 OPEN
165
166 -- ----------查看表空间信息
167
168 SQL > select t.tablespace_name, round ( sum (bytes / ( 1024 * 1024 )), 0 ) ts_size
169 2 from dba_tablespaces t, dba_data_files d
170 3 where t.tablespace_name = d.tablespace_name
171 4 group by t.tablespace_name;
172
173 TABLESPACE TS_SIZE
174 -- -------- -------
175 UNDOTBS1 75
176 SYSAUX 609
177 USERS 5
178 SYSTEM 720
179 EXAMPLE 100
180 USER_DATA 50
181
182 已选择6行。
183
184 SQL > grant select on dba_tablespaces to username;
185
186 授权成功。
187
188 SQL > select a.tablespace_name,a.bytes / 1024 / 1024 " Sum MB",(a.bytes - b.bytes) / 1024 / 1024 "used MB",
189 2 b.bytes / 1024 / 1024 "free MB", round (((a.bytes - b.bytes) / a.bytes) * 100 , 2 ) "percent_used" from
190 3 ( select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name) a,
191 4 ( select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) b
192 5 where a.tablespace_name = b.tablespace_name
193 6 order by ((a.bytes - b.bytes) / a.bytes) desc
194 7 ;
195
196 TABLESPACE Sum MB used MB free MB percent_used
197 -- -------- ------ ------- ------- ------------
198 SYSTEM 720 714 6 99.17
199 SYSAUX 608.5 578 30.5 94.99
200 USERS 5 4.063 . 9375 81.25
201 EXAMPLE 100 78.63 21.38 78.63
202 UNDOTBS1 75 18.06 56.94 24.08
203 USER_DATA 50 1.188 48.81 2.38
204
205 SQL > spool off ;
2
3 SQL > spool ' F:\app\Administrator\oradata\TablespaceStudy.txt ' ;
4
5 // 创建临时表空间
6
7 SQL > create temporary tablespace user_temp
8
9 1 tempfile ' F:\app\Administrator\oradata\user_temp.dbf '
10
11 2 size 50m
12
13 3 autoextend on
14
15 4 next 50m maxsize 20480m
16
17 5 extent management local;
18
19 // 创建数据表空间
20
21 SQL > create tablespace user_data
22
23 2 logging
24
25 3 datafile ' F:\app\Administrator\oradata\user_data.dbf '
26
27 4 size 50m
28
29 5 autoextend on
30
31 6 next 50m maxsize 20480m
32
33 7 extent management local;
34
35
36
37 // 创建用户并指定表空间
38
39 SQL > create user username identified by password
40
41 2 default tablespace user_data
42
43 3 temporary tablespace user_temp;
44
45
46
47 // 给用户授予权限
48
49
50 SQL > grant connect,resource to username;
51
52
53 SQL > connect system / as sysdba;
54 已连接。
55 SQL > select tablespace_name, file_id , file_name , round (bytes / ( 1024 * 1024 ), 0 ) total_sapce from dba_data_files order by tablespace_name;
56
57 TABLESPACE FILE_ID FILE_NAME TOTAL_SAPCE
58 -- -------- ---------- ------------------------------ -----------
59 EXAMPLE ########## F:\APP\ADMINISTRATOR\ORADATA\O ##########
60 RCL\EXAMPLE01.DBF
61
62 SYSAUX ########## F:\APP\ADMINISTRATOR\ORADATA\O ##########
63 RCL\SYSAUX01.DBF
64
65 SYSTEM ########## F:\APP\ADMINISTRATOR\ORADATA\O ##########
66 RCL\SYSTEM01.DBF
67
68 UNDOTBS1 ########## F:\APP\ADMINISTRATOR\ORADATA\O ##########
69 RCL\UNDOTBS01.DBF
70
71 USERS ########## F:\APP\ADMINISTRATOR\ORADATA\O ##########
72 RCL\USERS01.DBF
73
74 USER_DATA ########## F:\APP\ADMINISTRATOR\ORADATA\U ##########
75 SER_DATA.DBF
76
77
78
79 SQL > alter tablespace user_data
80 2 add datafile ' F:\app\Administrator\oradata\user_data2.dbf '
81 3 size 10m
82 4 autoextend on
83 5 maxsize 100m
84 6 ;
85
86 表空间已更改。
87
88
89 SQL > select file_name , file_id ,tablespace_name from dba_data_files;
90
91 FILE_NAME FILE_ID TABLESPACE
92 -- ---------------------------- ---------- ----------
93 F:\APP\ADMINISTRATOR\ORADATA\O ########## USERS
94 RCL\USERS01.DBF
95
96 F:\APP\ADMINISTRATOR\ORADATA\O ########## UNDOTBS1
97 RCL\UNDOTBS01.DBF
98
99 F:\APP\ADMINISTRATOR\ORADATA\O ########## SYSAUX
100 RCL\SYSAUX01.DBF
101
102 F:\APP\ADMINISTRATOR\ORADATA\O ########## SYSTEM
103 RCL\SYSTEM01.DBF
104
105 F:\APP\ADMINISTRATOR\ORADATA\O ########## EXAMPLE
106 RCL\EXAMPLE01.DBF
107
108 F:\APP\ADMINISTRATOR\ORADATA\U ########## USER_DATA
109 SER_DATA.DBF
110
111 F:\APP\ADMINISTRATOR\ORADATA\U ########## USER_DATA
112 SER_DATA2.DBF
113
114
115 已选择7行。
116
117 SQL > select file_name , file_id ,tablespace_name from dba_data_files where tablespace_name = ' USER_DATA ' ;
118
119 FILE_NAME FILE_ID TABLESPACE
120 -- ---------------------------- ---------- ----------
121 F:\APP\ADMINISTRATOR\ORADATA\U ########## USER_DATA
122 SER_DATA.DBF
123
124 F:\APP\ADMINISTRATOR\ORADATA\U ########## USER_DATA
125 SER_DATA2.DBF
126
127 删除表空间数据文件
128 SQL > alter tablespace user_data
129 2 drop datafile ' F:\app\Administrator\oradata\USER_DATA2.DBF ' ;
130
131 表空间已更改。
132
133 SQL > select file_name , file_id ,tablespace_name from dba_data_files where tablespace_name = ' USER_DATA ' ;
134
135 FILE_NAME FILE_ID TABLESPACE
136 -- ---------------------------- ---------- ----------
137 F:\APP\ADMINISTRATOR\ORADATA\U ########## USER_DATA
138 SER_DATA.DBF
139
140
141 SQL > connect username / password;
142 已连接。
143 SQL > create table Customer(ID int , Name varchar ( 30 )) tablespace user_data;
144 成功创建表
145
146 查看当前用户下的表
147 SQL > select * from tab;
148 TNAME TABTYPE CLUSTERID
149 -- ---------------------------- ------- ---------
150 Customer TABLE
151
152 SQL > desc customer;
153 名称 类型
154 ID UMBER( 38 )
155 NAME Varchar2
156
157 -- 切换到system账户
158 SQL > connect system / as sysdba;
159
160 SQL > select * from v$instance;
161
162 INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS
163
164 1 orcl WWW - 81A02E68C5B 11.1 . 0.7 . 0 04 - 3月 - 11 OPEN
165
166 -- ----------查看表空间信息
167
168 SQL > select t.tablespace_name, round ( sum (bytes / ( 1024 * 1024 )), 0 ) ts_size
169 2 from dba_tablespaces t, dba_data_files d
170 3 where t.tablespace_name = d.tablespace_name
171 4 group by t.tablespace_name;
172
173 TABLESPACE TS_SIZE
174 -- -------- -------
175 UNDOTBS1 75
176 SYSAUX 609
177 USERS 5
178 SYSTEM 720
179 EXAMPLE 100
180 USER_DATA 50
181
182 已选择6行。
183
184 SQL > grant select on dba_tablespaces to username;
185
186 授权成功。
187
188 SQL > select a.tablespace_name,a.bytes / 1024 / 1024 " Sum MB",(a.bytes - b.bytes) / 1024 / 1024 "used MB",
189 2 b.bytes / 1024 / 1024 "free MB", round (((a.bytes - b.bytes) / a.bytes) * 100 , 2 ) "percent_used" from
190 3 ( select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name) a,
191 4 ( select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) b
192 5 where a.tablespace_name = b.tablespace_name
193 6 order by ((a.bytes - b.bytes) / a.bytes) desc
194 7 ;
195
196 TABLESPACE Sum MB used MB free MB percent_used
197 -- -------- ------ ------- ------- ------------
198 SYSTEM 720 714 6 99.17
199 SYSAUX 608.5 578 30.5 94.99
200 USERS 5 4.063 . 9375 81.25
201 EXAMPLE 100 78.63 21.38 78.63
202 UNDOTBS1 75 18.06 56.94 24.08
203 USER_DATA 50 1.188 48.81 2.38
204
205 SQL > spool off ;