Install Oracle and create a database - database configuration/design tasks

1. With Oracle version 11g and lower, group application tables in a schema by creating a dedicated database user.

   Connect as system user with:

   sqlplus / AS SYSDBA

   and execute the following SQL command to create the db user:

   CREATE USER appadmin IDENTIFIED BY password;

   Grant privileges to the application administrator user:

   GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO appadmin;

2. With Oracle version 12c and higher, group application tables in a pluggable database (PDB).

   Connect as system user with:

   sqlplus / AS SYSDBA

   and create a pluggable database and its PDB administrator user. This is a basic PDB creation example using Oracle Managed Files, consider planing the PDB creation with the person in charge of Oracle database administration:

   CREATE PLUGGABLE DATABASE mypdb
     ADMIN USER pdbadmin IDENTIFIED BY password ROLES = (DBA)
     DEFAULT TABLESPACE mypdb_01
        DATAFILE 'path_01' SIZE 250M AUTOEXTEND ON ;

   For now the PDB is only mounted, it must be opened for regular usage:

   ALTER PLUGGABLE DATABASE mypdb OPEN;

   PDBs must be identified as separate database services (i.e. different from the CDB service). By default Oracle creates a database service with the same name as the PDB. To access the PDB through TNS, create the mypdb record in TNSNAMES.ORA file in addition to the default database service (ORC*):

   tnsname = 
    (DESCRIPTION = 
       (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
       (CONNECT_DATA = 
          (SERVER = DEDICATED)
          (SERVICE_NAME = mypdb)
       )
    )

   By default when Oracle starts, the PDBs are mounted but are not open for regular usage. With Oracle 12c, you can create a database trigger to open all PDBs automatically:

   CREATE OR REPLACE TRIGGER open_pdbs
     AFTER STARTUP ON DATABASE
   BEGIN
       EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
   END open_pdbs;
   / 

   Re-connect as PDB administrator and create a user dedicated to application tables administration:

   connect pdbadmin/password@localhost/mypdb
   CREATE USER appadmin IDENTIFIED BY password;
   GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO appadmin;        

1. If you plan to use the default temporary table emulation, create the TEMPTABS tablespace:

   The TEMPTABS tablespace must be created as permanent tablespace, and when using a PDB, it must be created in the context of the PDB. When not using a PDB database, execute the CREATE TABLESPACE command with the appadmin user:

   CREATE TABLESPACE temptabs
     DATAFILE 'file-path' SIZE 1M AUTOEXTEND ON;
   -- Give privileges on temptabs tablespace to other users
   ALTER USER dbuser QUOTA UNLIMITED ON TEMPTABS;

2. If you plan to use the temporary table emulation based on Oracle global temporary tables, create the TEMPTABS schema/user:

   CREATE USER temptabs IDENTIFIED BY pswd;

For more details about temporay table emulations, see Temporary tables.

sqlplus appadmin/password@tnsname