Oracle 创建用户

395 阅读3分钟

这是我参与8月更文挑战的第4天,活动详情查看:8月更文挑战

Oracle 的数据存在用户下的表空间里,创建用户时若不指定用户使用到的表空间,则数据默认会写入系统表空间 users 表空间中,临时数据会写入系统临时表空间 temp 临时表空间中,所以生产环境中,为了做到业务隔离及方便后期维护,通常会为创建单独的表空间给用户使用。

需求:

业务要新上线 OA 系统,需要在 oracle 数据库中新建 OA 账户。

操作步骤

以下操作均用数据库管理员用户操作。

1.新建 OA 表空间

--为了方便后期运维,建议表空间的数据文件统一存放,查看之前数据文件存放路径
Set pagesize 300
Set linesize 300
col file_name format a60
select file_id,tablespace_name,file_name,bytes/1024/1024,status,autoextensible,maxbytes/1024/1024 from dba_data_files;

   FILE_ID TABLESPACE_NAME                FILE_NAME                                                    BYTES/1024/1024 STATUS    AUT MAXBYTES/1024/1024
---------- ------------------------------ ------------------------------------------------------------ --------------- --------- --- ------------------
         4 USERS                          /msun/data/oradata/orcl/users01.dbf                                        5 AVAILABLE YES         32767.9844
         3 UNDOTBS1                       /msun/data/oradata/orcl/undotbs01.dbf                                    210 AVAILABLE YES         32767.9844
         2 SYSAUX                         /msun/data/oradata/orcl/sysaux01.dbf                                     610 AVAILABLE YES         32767.9844
         1 SYSTEM                         /msun/data/oradata/orcl/system01.dbf                                     770 AVAILABLE YES         32767.9844
         5 SYD                            /msun/data/oradata/orcl/syd01.dbf                                    9586.25 AVAILABLE YES         32767.9844
         6 O2O                            /msun/data/oradata/orcl/o2o01.dbf                                 12384.5625 AVAILABLE YES         32767.9844
         7 SYD                            /msun/data/oradata/orcl/syd01.dbf                                            AVAILABLE
         8 O2O                            /msun/data/oradata/orcl/o2o01.dbf                                            AVAILABLE
         9 O2O                            /msun/data/oradata/orcl/o2o02.dbf                                            AVAILABLE
        10 O2O                            /msun/data/oradata/orcl/o2o03.dbf                                         10 AVAILABLE YES         32767.9844
        11 O2O                            /msun/data/oradata/orcl/o2o04.dbf                                         10 AVAILABLE NO                   0

11 rows selected.

--新建 OA 表空间
create tablespace oa datafile '/msun/data/oradata/orcl/oa01.dbf' size 100m autoextend on;

2.新建 OA 临时表空间

--查看临时数据文件路径
Set pagesize 100
Set linesize 200
col file_name format a55
select tablespace_name,file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_temp_files;


TABLESPACE_NAME                FILE_NAME                                               BYTES/1024/1024 AUT MAXBYTES/1024/1024
------------------------------ ------------------------------------------------------- --------------- --- ------------------
TEMP                           /msun/data/oradata/orcl/temp01.dbf                                 1528 YES         32767.9844
TEST_TEMP                      /msun/data/oradata/orcl/test_temp01.dbf                             100 YES         32767.9844

--新建临时 OA 表空间
create temporary tablespace oa_temp tempfile '/msun/data/oradata/orcl/oa_temp01.dbf' size 100m autoextend on;

3.创建 OA 用户指定使用上述表空间

--建用户
create user oa identified by woshimima default tablespace oa temporary tablespace oa_temp;

--查询用户
set line 200 pagesize 500
select username,account_status,default_tablespace,temporary_tablespace,created from dba_users;

USERNAME                       ACCOUNT_STATUS                   DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ -------------------------------- ------------------------------ ------------------------------ ------------------
OA                             OPEN                             OA                             OA_TEMP                        04-AUG-21
TEST                           OPEN                             SYD                            TEST_TEMP                      13-JUL-21
SYSTEM                         OPEN                             SYSTEM                         TEMP                           24-AUG-13
SYS                            OPEN                             SYSTEM                         TEMP                           24-AUG-13
SCOTT                          OPEN                             USERS                          TEMP                           24-AUG-13
SYD                            OPEN                             SYD                            TEMP                           30-DEC-20
ce_syd                         OPEN                             SYD                            TEMP                           08-JUN-21
O2O                            OPEN                             O2O                            TEMP                           26-JAN-21
MGMT_VIEW                      EXPIRED & LOCKED                 SYSTEM                         TEMP                           24-AUG-13
OUTLN                          EXPIRED & LOCKED                 SYSTEM                         TEMP                           24-AUG-13
DBSNMP                         EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
OLAPSYS                        EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
OWBSYS                         EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
ORDPLUGINS                     EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
XDB                            EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
SYSMAN                         EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
ANONYMOUS                      EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
CTXSYS                         EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
ORDDATA                        EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
OWBSYS_AUDIT                   EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
APEX_030200                    EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
APPQOSSYS                      EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
WMSYS                          EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
EXFSYS                         EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
ORDSYS                         EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
MDSYS                          EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
FLOWS_FILES                    EXPIRED & LOCKED                 SYSAUX                         TEMP                           24-AUG-13
SPATIAL_WFS_ADMIN_USR          EXPIRED & LOCKED                 USERS                          TEMP                           24-AUG-13
SPATIAL_CSW_ADMIN_USR          EXPIRED & LOCKED                 USERS                          TEMP                           24-AUG-13
APEX_PUBLIC_USER               EXPIRED & LOCKED                 USERS                          TEMP                           24-AUG-13
DIP                            EXPIRED & LOCKED                 USERS                          TEMP                           24-AUG-13
MDDATA                         EXPIRED & LOCKED                 USERS                          TEMP                           24-AUG-13
XS$NULL                        EXPIRED & LOCKED                 USERS                          TEMP                           24-AUG-13
ORACLE_OCM                     EXPIRED & LOCKED                 USERS                          TEMP                           24-AUG-13

35 rows selected.

4.用户授权

新建的用户没有任何权限,需要为其授予连接数据库和管理自己用户下对象的权限,如需访问其他用户下的对象需再另外授权,为安全考虑,生产环境不建议授予普通业务用户 DBA 权限。

grant connect,resource to oa;