Article KB-00041 - Creating an Oracle Database

Applies to: Databases - Oracle

Created by Serena (she/her) on Jun 30, 2024

Steps

1. Connect to database server (such as calchas or ennomus)
2. Log in as SYSDBA:

   $ sqlplus sys/<your_admin_pws>@localhost as sysdba

3. Create the database/admin user:

   SQL> CREATE PLUGGABLE DATABASE MYDATABASE ADMIN USER MYUSER IDENTIFIED BY MYPASSWORD;
   SQL> ALTER PLUGGABLE DATABASE MYDATABASE OPEN;

4. Log out of SQL*Plus
5. Log into the new database in a SYSDBA role:

   $ sqlplus sys/<your_admin_pws>@localhost/MYDATABASE as sysdba

6. Create a tablespace:

   SQL> CREATE TABLESPACE TABLESPACENAME DATAFILE 'tablespace_datafile.dat' 
        SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

7. Grant MYUSER unlimited space in the new tablespace:

   SQL> ALTER USER MYUSER QUOTA UNLIMITED ON TABLESPACENAME;

8. Grant MYUSER unlimited space in the SYSTEM tablespace:

   SQL> ALTER USER MYUSER QUOTA UNLIMITED ON SYSTEM;

9. Make TABLESPACENAME the default tablespace for MYUSER:

   SQL> ALTER USER MYUSER DEFAULT TABLESPACE TABLESPACENAME;

10. Grant MYUSER other permissions:

    SQL> GRANT CREATE SESSION TO MYUSER;
    SQL> GRANT CONNECT TO MYUSER;
    SQL> GRANT RESOURCE TO MYUSER;

Last modified Jul 3, 2024
5 revisions
Viewed 4128 times
Supersedes KB-00012

snw on Jul 3, 2024
snw on Jul 3, 2024
snw on Jun 30, 2024
snw on Jun 30, 2024
snw on Jun 30, 2024