Oracle数据库新建表空间和对应用户

Oracle数据库新建表空间和对应用户

1. 创建表空间和临时表空间数据文件

   • 创建表空间数据文件

     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.
     

   • 创建临时表空间数据文件

     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. 创建用户与数据库文件关联

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

3. 添加权限

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

此时就可以以创建的用户身份重新进入Oracle数据库了。

以创建用户身份进入sqlplus，执行：

[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.

可以看到查询结果包含我们刚刚创建的两个TABLESPACE。

具体了解Oracle数据库中的数据库、表空间等概念，参见这篇文章。

---

问题记录：

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
   

   经查，/home/oracle/OracleDB/user/rec_news/目录的所有者为root:root，修改为oracle:oinstall后即可以oracle用户身份正常创建。