Oracle学习系列笔记------数据库登陆、表空间管理
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;
