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

If you are tasked with installing and configuring the database, here is a list of steps to be taken:

  1. Install the ORACLE database software on your computer.
  2. Create and setup the Oracle instance and database. Consider creating a multitenant database when using Oracle 12c and higher, to create several pluggable databases (PDB) in the same Oracle instance. Specify the database character set when creating the database instance. If you plan to create a database a multi-byte character set like UTF-8, consider using character length semantics.
  3. Create a database context dedicated to your application. According the Oracle version, define a db user / schema to hold application tables, or create a pluggable database (starting with Oracle 12c).
    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;        
  4. If your programs create temporary tables, you must define the type of temporary table emulation to be used.
    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.

  5. Create the application tables by connecting to the database context as the application administrator:

    sqlplus appadmin/password@tnsname

    Convert Informix® data types to Oracle data types. See issue data type Conversion Tables for more details.
  6. If you plan to use SERIAL emulation, you must choose a serial emulation method. Select the best emulation technique that matches your needs. You need to prepare the database according to the emulation type. For more details, see SERIAL data types.