Oracle创建实例SQL脚本

一、创建oracle实例

1.1 第一种：自定义“临时表空间”

-- 1)	创建永久表空间
create tablespace AVFAR_DATA 
datafile'E:\app\XIAO\oradata\orcl\AVFAR_DATA.ora' size 1800m
autoextend on next 10m maxsize 20000m
permanent
default storage(initial 64k minextents 1 maxextents 2147483645)
minimum extent 64k
logging
online
/

-- 2)	创建临时表空间
create temporary tablespace AVFAR_TEMP
tempfile'E:\app\XIAO\oradata\orcl\AVFAR_TEMP.ora' size 40m
autoextend on next 10m maxsize unlimited

-- 3)	创建用户（账户：avfar，密码：avfar）
create user avfar identified by avfar
default tablespace AVFAR_DATA
temporary tablespace AVFAR_TEMP
profile default;

-- 4)	授权（给avfar用户分配DBA权限，使用system用户登录授权）
grant connect to avfar;
grant dba to avfar with admin option;
grant resource to avfar;
grant unlimited tablespace to avfar with admin option;

---

1.2 第二种：使用默认“临时表空间”

--创建表空间
create tablespace AVFAR datafile 'E:\app\XIAO\oradata\orcl\AVFAR.ora' size 200m autoextend on next 10m;

--创建用户
create user avfar6y identified by avfar6y default tablespace AVFAR temporary tablespace TEMP profile DEFAULT;

--授权
grant connect to avfar6y;
grant dba to avfar6y;
grant resource to avfar6y;
grant unlimited tablespace to avfar6y;
grant create materialized view to avfar6y;

参考：

--创建表空间
create tablespace XIAO_TABLESPACE datafile 'F:\app\XIAO\oradata\orcl\XIAO_TEST.ora' size 200m autoextend on next 10m;

--创建用户
create user xiao identified by 1234 default tablespace XIAO_TABLESPACE temporary tablespace TEMP profile DEFAULT;

--授权
grant connect to xiao;
grant dba to xiao;
grant resource to xiao;
grant unlimited tablespace to xiao;
grant create materialized view to xiao;