这是我参与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;
今天的文章Oracle 创建用户分享到此就结束了，感谢您的阅读。
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/13564.html
