New tablespaces and corresponding users in Oracle Database

1. Create tablespace and temporary tablespace data files

   • Create a tablespace data file

     SQL> CREATE TABLESPACE rec_news LOGGING DATAFILE '/home/oracle/OracleDB/user/rec_news/rec_news.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 5120M EXTENT MANAGEMENT LOCAL;
     
     Tablespace created.

   • Create a temporary tablespace data file

     SQL> CREATE TEMPORARY TABLESPACE rec_news_temp tempfile '/home/oracle/OracleDB/user/rec_news/rec_news_tmp.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE 1024M EXTENT MANAGEMENT LOCAL;
     
     Tablespace created.

2. Create user and database file association

   SQL> CREATE USER rec IDENTIFIED BY <password> DEFAULT TABLESPACE rec_news TEMPORARY TABLESPACE rec_news_temp;
   
   User created.

3. add permission

   SQL> grant connect, resource, dba to rec;
   
   Grant succeeded.
   
   SQL> grant create session to rec;
   
   Grant succeeded.
   
   SQL> commit;
   
   Commit complete.

At this point, you can re-enter the Oracle database as the user you created.

Enter sqlplus as the creation user and execute:

[oracle@datanode1 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 6 17:56:53 2018

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: rec
Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select tablespace_name from user_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
REC_NEWS
REC_NEWS_TEMP

8 rows selected.

You can see that the query results contain the two tablespaces we just created.

For more information about the concepts of database and tablespace in Oracle database, see This article.

Problem record:

1. ORA-27040: file create error, unable to create file Linux-x86_64 Error: 13: Permission denied

   SQL> CREATE TABLESPACE rec_news LOGGING DATAFILE '/home/oracle/OracleDB/user/rec_news/rec_news.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 5120M EXTENT MANAGEMENT LOCAL;
   CREATE TABLESPACE rec_news LOGGING DATAFILE '/home/oracle/OracleDB/user/rec_news/rec_news.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 5120M EXTENT MANAGEMENT LOCAL
   *
   ERROR at line 1:
   ORA-01119: error in creating database file
   '/home/oracle/OracleDB/user/rec_news/rec_news.dbf'
   ORA-27040: file create error, unable to create file
   Linux-x86_64 Error: 13: Permission denied

   It is found that the owner of / home/oracle/OracleDB/user/rec_news / directory is root:root, and the user identity of oracle can be created normally after the directory is modified to oracle:oinstall.