Oracle用户与表空间的创建

最新推荐文章于 2024-03-17 11:07:50 发布

fhiguin

最新推荐文章于 2024-03-17 11:07:50 发布

阅读量105

点赞数

分类专栏：运维数据库 Oracle 文章标签：数据库 oracle centos 运维

本文链接：https://blog.csdn.net/fhiguin/article/details/111245395

版权

运维同时被 3 个专栏收录

17 篇文章 1 订阅

订阅专栏

数据库

3 篇文章 1 订阅

订阅专栏

Oracle

1 篇文章 0 订阅

订阅专栏

一、测试环境

-- 测试环境表空间创建
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";