79-Oracle 19c PDB下-建表空间-用户-赋权-验证-基本操作

远方1609

于 2025-06-25 11:29:19 发布

阅读量752

点赞数 15

CC 4.0 BY-SA版权

文章标签： oracle 数据库 database sql 大数据

本文链接：https://blog.youkuaiyun.com/sharpwt/article/details/148895232

小伙伴们，这篇应该是日常最常做，且是每个DBA的必备技能，做个记录，防手抖，防迷路。

还记得上一篇OMF管理上（78-Oracle Oracle Managed Files(OMF)文件管理特性-优快云博客），PDB直接自动给了一大串GUID的文件夹路径，其实这一长串是PDB 的GUID在OMF管理的时候，自动当做保存路径来使用。

以下为现场BI的部署实测脚本，内容作为给现场部署的工程师使用适配Windows和Linux2种环境，欢迎借用可以保留出处。

1 部署数据库服务器

BI数据库的表结构，对应如下：

1.1 创建表空间(使用管理员权限的帐号)

BI需要四个额外的表空间：

BIDATA	用于存放FACT表
BIINDEX	用于存放FACT表索引
ODSDATA	用于存放ODS表
ODSINDEX	用于存放ODS表索引

先查询数据库服务器的表空间的文件路径（在PL/SQL或各种SQL工具的SQL窗口中执行）：

select * from dba_data_files

SYS@CDB$ROOT SQL> alter session set container=PDBHBI;
Session altered.
SYS@CDB$ROOT SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,TABLESPACE_NAME,TABLESPACE_NAME from dba_data_files;
                                                                                FILE_NAME    FILE_ID    TABLESPACE_NAME    TABLESPACE_NAME    TABLESPACE_NAME
_________________________________________________________________________________________ __________ __________________ __________________ __________________
/oradata/HBICDB/383C85C956F239E2E063BD00060A05F5/datafile/o1_mf_system_n5lgnhs6_.dbf               9 SYSTEM             SYSTEM             SYSTEM
/oradata/HBICDB/383C85C956F239E2E063BD00060A05F5/datafile/o1_mf_sysaux_n5lgnhsg_.dbf              10 SYSAUX             SYSAUX             SYSAUX
/oradata/HBICDB/383C85C956F239E2E063BD00060A05F5/datafile/o1_mf_undotbs1_n5lgnhsg_.dbf            11 UNDOTBS1           UNDOTBS1           UNDOTBS1
/oradata/HBICDB/383C85C956F239E2E063BD00060A05F5/datafile/o1_mf_users_n5lgnoj8_.dbf               12 USERS              USERS              USERS

举例：Linux版本Oracle，表空间的文件路径为 /u01/app/oracle/oradata/bi/;Windows版本Oracle，表空间的文件路径为 D:\app\Administrator\oradata\orcl\

注意：1）路径中的斜线，Windows是"\"，Linux是“/” 2）路径可能是相对或是绝对路径，现场实际情况替换下面各语句中的路径，OMF可自动也可以手动指定，但文件名x.dbf等不要修改。

1.1.1 BIDATA

--Windows：创建表空间：
create tablespace  bidata datafile  'D:\app\Administrator\oradata\orcl\bidata01.dbf'  size 30G autoextend on  next  30M;
--增加数据文件（在表空间不足时执行增加表空间）：
alter tablespace bidata add datafile  'D:\app\Administrator\oradata\orcl\bidata02.dbf'  size 10G autoextend on next 30M;
--Linux
--创建表空间：
create tablespace  bidata datafile  '/oradata/HBICDB/383C85C956F239E2E063BD00060A05F5/datafile/bidata01.dbf'  size 30G autoextend on  next  30M;

1.1.2 BIINDEX

--Windows
--创建表空间：
create tablespace  biindex datafile  'D:\app\Administrator\oradata\orcl\biindex01.dbf'  size 30G autoextend on  next  30M;
--增加数据文件（在表空间不足时执行增加表空间）：
alter tablespace biindex add datafile  'D:\app\Administrator\oradata\orcl\biindex02.dbf'  size 10G autoextend on next 30M;

--LINUX
create tablespace  biindex datafile  '/oradata/HBICDB/383C85C956F239E2E063BD00060A05F5/datafile/biindex01.dbf'  size 30G autoextend on  next  30M;

1.1.3 ODSDATA

--Windows
--创建表空间：
create tablespace  odsdata datafile  'D:\app\Administrator\oradata\orcl\odsdata01.dbf'  size 30G autoextend on  next  30M;
--增加数据文件（在表空间不足时执行增加表空间）：
alter tablespace odsdata add datafile  'D:\app\Administrator\oradata\orcl\odsdata02.dbf'  size 10G autoextend on next 30M;

--Linux
create tablespace  odsdata datafile  '/oradata/HBICDB/383C85C956F239E2E063BD00060A05F5/datafile/odsdata01.dbf'  size 30G autoextend on  next  30M;

1.1.4 ODSINDEX

--Windows
--创建表空间：
create tablespace  odsindex datafile  'D:\app\Administrator\oradata\orcl\odsindex01.dbf'  size 30G autoextend on  next  30M;
--增加数据文件（在表空间不足时执行增加表空间）：
alter tablespace odsindex add datafile  'D:\app\Administrator\oradata\orcl\odsindex02.dbf'  size 10G autoextend on next  30M;

--Linux
create tablespace  odsindex datafile  '/oradata/HBICDB/383C85C956F239E2E063BD00060A05F5/datafile/odsindex01.dbf'  size 30G autoextend on  next  30M;

1.2 创建用户同时赋权(使用管理员权限的帐号)

需要创建三个用户并赋予相应的权限(注：创建用户时，密码要遵循当前现场的密码设置要求)

etl_mgr	ETL支持库
newbi	FACT层
newods	ODS层

1.2.1 elt_mgr

create user etl_mgr identified by etl_mgr default tablespace BIDATA;
--
grant connect, resource, select_catalog_role, execute_catalog_role, create any table, 
create any view, create database link, debug connect session to etl_mgr;
--
grant execute on DBMS_AQ to etl_mgr;
grant execute on DBMS_AQADM to etl_mgr;
grant unlimited tablespace to ETL_MGR;
grant create any table to etl_mgr; 
grant alter any table to etl_mgr; 
grant insert any table to etl_mgr; 
grant update any table to etl_mgr;
grant comment any table to etl_mgr;
grant create any index to etl_mgr;

1.2.1 newbi

create user newbi identified by softnewbi default tablespace BIDATA;

grant connect, resource, select_catalog_role, execute_catalog_role, create any table,
 create any view, create database link, debug connect session to newbi;
grant execute any procedure to newbi;
grant select any table to newbi; 
grant unlimited tablespace to newbi;

1.2.2 newods

create user newods identified by softnewods default tablespace ODSDATA;

grant connect, resource, select_catalog_role, execute_catalog_role, create any table,
 create any view, create database link, debug connect session to newods;
grant unlimited tablespace to newods;

1.2.3 验证建立（建立用户在管理员下，角色和权限在各自账户下查询）

在用户名/密码登录上面的用户，并在用户下执行

select * from user_tab_privs;-- 查看用户的系统权限是否赋予成功

--
SYS@CDB$ROOT SQL> conn etl_mgr/etl_mgr@localhost:1521/PDBHBI
Connected.
ETL_MGR@localhost:1521/PDBHBI SQL> select * from user_tab_privs;
GRANTEE    OWNER    TABLE_NAME    GRANTOR             PRIVILEGE    GRANTABLE    HIERARCHY    COMMON       TYPE    INHERITED
ETL_MGR    SYS      DBMS_AQ       SYS        EXECUTE               NO           NO           NO        PACKAGE    NO
ETL_MGR    SYS      DBMS_AQADM    SYS        EXECUTE               NO           NO           NO        PACKAGE    NO
PUBLIC     SYS      ETL_MGR       ETL_MGR    INHERIT PRIVILEGES    NO           NO           NO        USER       NO

select * from user_role_privs; --查看用户的对象权限是否赋予成功

ETL_MGR@localhost:1521/PDBHBI SQL> select * from user_role_privs;

   USERNAME            GRANTED_ROLE    ADMIN_OPTION    DELEGATE_OPTION    DEFAULT_ROLE    OS_GRANTED    COMMON    INHERITED
___________ _______________________ _______________ __________________ _______________ _____________ _________ ____________
ETL_MGR     CONNECT                 NO              NO                 YES             NO            NO        NO   
ETL_MGR     EXECUTE_CATALOG_ROLE    NO              NO                 YES             NO            NO        NO   
ETL_MGR     RESOURCE                NO              NO                 YES             NO            NO        NO   
ETL_MGR     SELECT_CATALOG_ROLE     NO              NO                 YES             NO            NO        NO

这一篇是最最最基础的操作，留个痕迹，下期从11g的dmp进行数据泵导入。