Oracle11g创建表空间及用户

• 第1步：创建临时表空间  

create temporary tablespace pgenius_temp
tempfile '/data/oracle/oradata/orcl/pgenius_temp.dbf'
size 50m  
autoextend on  
next 50m maxsize 20480m
extent management local;

删除临时表空间：

drop tablespace pgenius_temp including contents and datafiles;

 

• 第2步：创建数据表空间

create tablespace pgenius  
logging
datafile '/data/oracle/oradata/orcl/pgenius_0.dbf' size  1g autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_1.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_2.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_3.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_4.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_5.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_6.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_7.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_8.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_9.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_10.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_11.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_12.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_13.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_14.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_15.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_16.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_17.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_18.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_19.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
,'/data/oracle/oradata/orcl/pgenius_20.dbf' size  1m autoextend on next 1g maxsize UNLIMITED
extent management local;

删除表空间：

drop tablespace pgenius;//仅删除表空间
drop tablespace pgenius including contents and datafiles; //同时删除数据文件

 

• 第3步：创建用户并指定表空间

create user pgenius identified by "xxxxxx"
default tablespace pgenius
temporary tablespace pgenius_temp;

 

• 第4步：给用户授予权限

grant connect,resource,dba to pgenius;