Oracle数据库新建表空间和对应用户
-
创建表空间和临时表空间数据文件
-
创建表空间数据文件
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.
-
-
创建用户与数据库文件关联
SQL> CREATE USER rec IDENTIFIED BY <password> DEFAULT TABLESPACE rec_news TEMPORARY TABLESPACE rec_news_temp; User created.
-
添加权限
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数据库中的数据库、表空间等概念,参见这篇文章。
问题记录:
-
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用户身份正常创建。