Article KB-00012 - Create Oracle Database

Applies to: Databases - Oracle

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

-   Connect to database server (i.e. calchas.coherent-logic.com)
-   Log in as SYSDBA:

    $ sqlplus sys/<your_admin_pws>@localhost as sysdba

-   Create the database/admin user:

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

-   Log out of SQL*Plus
-   Log into the new database as a SYSDBA:

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

-   Create a tablespace:

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

-   Grant MYUSER unlimited space in the new tablespace:

    SQL> ALTER USER MYUSER QUOTA UNLIMITED ON TABLESPACENAME;

-   Grant MYUSER unlimited space in the SYSTEM tablespace:

    SQL> ALTER USER MYUSER QUOTA UNLIMITED ON SYSTEM;

-   Make TABLESPACENAME the default tablespace for MYUSER:

    SQL> ALTER USER MYUSER DEFAULT TABLESPACE TABLESPACENAME;

-   Grant MYUSER other permissions:

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

Note that RESOURCE is actually a role (not a permission) and will give
MYUSER the ability to create tables, views, indexes, procedures, etc.

Last modified Jun 26, 2024
2 revisions
Viewed 3260 times
Superseded by KB-00041

snw on Jun 26, 2024
snw on Jun 26, 2024