This is the 4th day of my participation in the August More Text Challenge

Oracle data is stored in the users tablespace. If the users tablespace is not specified when a user is created, data is written to the Users tablespace by default, and temporary data is written to the temp tablespace. Therefore, in production environments, to isolate services and facilitate maintenance, Separate table Spaces are typically created for users to use.

Requirements:

An OA account needs to be created in the Oracle database for services to be connected to the OA system.

steps

The following operations are performed by the database administrator.

1. Create an OA tablespace

-- To facilitate later operation and maintenance, it is recommended that the data files of the tablespace be stored in a unified manner. 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/former/users01. DBF 5 AVAILABLE YES 32767.9844 3 UNDOTBS1 / msun/data/oradata/former/undotbs01. DBF 210 AVAILABLE YES 32767.9844 2 SYSAUX/msun/data/oradata/former/sysaux01. DBF, 610 The AVAILABLE SYSTEM/YES 32767.9844 1 msun/data/oradata/former/system01. DBF 770 AVAILABLE YES 32767.9844 5 SYD / msun/data/oradata/former/syd01. DBF 9586.25 AVAILABLE YES 32767.9844 6 O2O/msun/data/oradata/former/o2o01. DBF 12384.5625 The AVAILABLE YES 32767.9844 7 SYD/msun/data/oradata/former/syd01. DBF AVAILABLE 8 O2O/msun/data/oradata/former/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/former/o2o04. DBF 10 AVAILABLE NO 0 11 rows selected. - create new OA table space in tablespace OA  datafile '/msun/data/oradata/orcl/oa01.dbf' size 100m autoextend on;Copy the code

2. Create a temporary OA tablespace

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/former/temp01. DBF 1528 YES TEST_TEMP 32767.9844 / msun/data/oradata/former/test_temp01. DBF 100 YES 32767.9844 - the new temporary OA table space create temporary in tablespace oa_temp tempfile as expected '/ msun/data/oradata/former/oa_temp01. DBF' 100 m size autoextend on;Copy the code

3. Create an OA user to specify the tablespace

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.Copy the code

4. Authorize users

The newly created user does not have any permission. You need to grant the user the permission to connect to the database and manage objects of your own user. If you want to access objects of other users, you need to grant another permission.

grant connect,resource to oa;
Copy the code