oracle数据库表空间及用户初始化
–1、建立表空间
create tablespace “DEMO” logging datafile ‘D:\DATABASES\ORACLE\ORADATA\ORCL\DEMO.DBF’ size 500M autoextend on next 100M;
–2、添加表空间物理物件(可选)
–介于项目的特殊性:系统数据量会比较庞大,加上oracle数据库文件最大只支持30G,所以新建库的时候必须建立多个数据文件,具体数视需求而定,可不加
ALTER TABLESPACE “DEMO” ADD DATAFILE ‘
p
a
t
h
D
E
M
O
−
2.
D
B
F
′
S
I
Z
E
10000
M
a
u
t
o
e
x
t
e
n
d
o
n
n
e
x
t
100
M
;
A
L
T
E
R
T
A
B
L
E
S
P
A
C
E
"
D
E
M
O
"
A
D
D
D
A
T
A
F
I
L
E
′
{path}DEMO-2.DBF' SIZE 10000M autoextend on next 100M; ALTER TABLESPACE "DEMO" ADD DATAFILE '
pathDEMO−2.DBF′SIZE10000Mautoextendonnext100M;ALTERTABLESPACE"DEMO"ADDDATAFILE′{path}DEMO-3.DBF’ SIZE 10000M autoextend on next 100M;
ALTER TABLESPACE “DEMO” ADD DATAFILE ‘
p
a
t
h
D
E
M
O
−
4.
D
B
F
′
S
I
Z
E
10000
M
a
u
t
o
e
x
t
e
n
d
o
n
n
e
x
t
100
M
;
A
L
T
E
R
T
A
B
L
E
S
P
A
C
E
"
D
E
M
O
"
A
D
D
D
A
T
A
F
I
L
E
′
{path}DEMO-4.DBF' SIZE 10000M autoextend on next 100M; ALTER TABLESPACE "DEMO" ADD DATAFILE '
pathDEMO−4.DBF′SIZE10000Mautoextendonnext100M;ALTERTABLESPACE"DEMO"ADDDATAFILE′{path}DEMO-5.DBF’ SIZE 10000M autoextend on next 100M;
ALTER TABLESPACE “DEMO” ADD DATAFILE ‘
p
a
t
h
D
E
M
O
−
6.
D
B
F
′
S
I
Z
E
10000
M
a
u
t
o
e
x
t
e
n
d
o
n
n
e
x
t
100
M
;
A
L
T
E
R
T
A
B
L
E
S
P
A
C
E
"
D
E
M
O
"
A
D
D
D
A
T
A
F
I
L
E
′
{path}DEMO-6.DBF' SIZE 10000M autoextend on next 100M; ALTER TABLESPACE "DEMO" ADD DATAFILE '
pathDEMO−6.DBF′SIZE10000Mautoextendonnext100M;ALTERTABLESPACE"DEMO"ADDDATAFILE′{path}DEMO-7.DBF’ SIZE 10000M autoextend on next 100M;
ALTER TABLESPACE “DEMO” ADD DATAFILE ‘
p
a
t
h
D
E
M
O
−
8.
D
B
F
′
S
I
Z
E
10000
M
a
u
t
o
e
x
t
e
n
d
o
n
n
e
x
t
100
M
;
A
L
T
E
R
T
A
B
L
E
S
P
A
C
E
"
D
E
M
O
"
A
D
D
D
A
T
A
F
I
L
E
′
{path}DEMO-8.DBF' SIZE 10000M autoextend on next 100M; ALTER TABLESPACE "DEMO" ADD DATAFILE '
pathDEMO−8.DBF′SIZE10000Mautoextendonnext100M;ALTERTABLESPACE"DEMO"ADDDATAFILE′{path}DEMO-9.DBF’ SIZE 10000M autoextend on next 100M;
ALTER TABLESPACE “DEMO” ADD DATAFILE ‘${path}DEMO-10.DBF’ SIZE 10000M autoextend on next 100M;
–举例先建立总数10个物理文件
–3、建立用户并默认相应表空间,默认 用户名C##DEMO 密码DEMO1234
create user C##DEMO identified by DEMO1234 default tablespace DEMO temporary tablespace TEMP;
–4、修改oracle密码有效期无限,这以后会成为一个坑,所以改了好
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
–5、赋予用户权限
GRANT dba TO C##DEMO with admin option;
GRANT UNLIMITED TABLESPACE TO C##DEMO;
GRANT SELECT ANY TABLE TO C##DEMO;
GRANT SELECT ANY DICTIONARY TO C##DEMO;