Oracle用户与表空间的创建

一、测试环境

-- 测试环境表空间创建
CREATE TABLESPACE "PROJECT_DATA_DF" DATAFILE 
'D:/app/PROJECT_DATA_DF_01.dbf' SIZE 100M REUSE autoextend on next 100M  maxsize 1000M,
'D:/app/PROJECT_DATA_DF_02.dbf' SIZE 100M REUSE autoextend on next 100M  maxsize 1000M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
extent management local autoallocate segment space management auto; 

-- 临时表空间
CREATE TEMPORARY TABLESPACE "PROJECT_TEMP_DF" TEMPFILE 
'D:/app/PROJECT_TEMP_DF.dbf' SIZE 100M REUSE autoextend on next 100M  maxsize 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M ;

-- project用户创建
CREATE USER "PROJECT" IDENTIFIED BY "project" DEFAULT TABLESPACE "PROJECT_DATA_DF" TEMPORARY TABLESPACE "PROJECT_TEMP_DF";
GRANT "CONNECT", "DBA", "RESOURCE" TO "PROJECT";
ALTER USER "PROJECT" DEFAULT ROLE "CONNECT", "DBA", "RESOURCE";
GRANT UNLIMITED TABLESPACE TO "PROJECT";

-- project_sys用户创建
CREATE USER "PROJECT_SYS" IDENTIFIED BY "project_sys" DEFAULT TABLESPACE "PROJECT_DATA_DF" TEMPORARY TABLESPACE "PROJECT_TEMP_DF";
GRANT "CONNECT", "DBA", "RESOURCE" TO "PROJECT_SYS";
ALTER USER "PROJECT_SYS" DEFAULT ROLE "CONNECT", "DBA", "RESOURCE";
GRANT UNLIMITED TABLESPACE TO "PROJECT_SYS";

二、生产环境

-- 生产环境表空间创建
CREATE TABLESPACE "PROJECT_DATA_DF" DATAFILE 
'/home/oracle/PROJECT_DATA_DF_01.dbf' SIZE 1024M REUSE autoextend on next 100M  maxsize 10000M,
'/home/oracle/PROJECT_DATA_DF_02.dbf' SIZE 1024M REUSE autoextend on next 100M  maxsize 10000M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
extent management local autoallocate segment space management auto; 

-- 临时表空间
CREATE TEMPORARY TABLESPACE "PROJECT_TEMP_DF" TEMPFILE 
'/home/oracle/PROJECT_TEMP_DF.dbf' SIZE 1024M REUSE autoextend on next 100M  maxsize 10000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M ;

-- project用户创建
CREATE USER "PROJECT" IDENTIFIED BY "project" DEFAULT TABLESPACE "PROJECT_DATA_DF" TEMPORARY TABLESPACE "PROJECT_TEMP_DF";
GRANT "CONNECT", "DBA", "RESOURCE" TO "PROJECT";
ALTER USER "PROJECT" DEFAULT ROLE "CONNECT", "DBA", "RESOURCE";
GRANT UNLIMITED TABLESPACE TO "PROJECT";

-- project_sys用户创建
CREATE USER "PROJECT_SYS" IDENTIFIED BY "project_sys" DEFAULT TABLESPACE "PROJECT_DATA_DF" TEMPORARY TABLESPACE "PROJECT_TEMP_DF";
GRANT "CONNECT", "DBA", "RESOURCE" TO "PROJECT_SYS";
ALTER USER "PROJECT_SYS" DEFAULT ROLE "CONNECT", "DBA", "RESOURCE";
GRANT UNLIMITED TABLESPACE TO "PROJECT_SYS";