New tablespaces and corresponding users of Oracle database based on database technology

Posted by SpasePeepole on Wed, 01 Apr 2020 03:05:27 +0200

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.

Topics: Oracle SQL Database sqlplus