Preparing Oracle Server

Oracle server is prepared by the DBMS administrator.

Scripts are executed using any available methods, for example, via the SQL Plus application included in the standard kit of the Oracle DBMS front and back end.

Creating a Database User (Schema)

The next stage is creating a database user (schema). This user (schema) is required to store platform metadata.

create user USER_NAME identified by USER_PASSWORD default tablespace TABLESPACE_NAME temporary tablespace TEMP_TABLESPACE_NAME profile default;

grant connect to USER_NAME;

grant resource to USER_NAME;

revoke unlimited tablespace from USER_NAME;

alter user USER_NAME quota unlimited on TABLESPACE_NAME;

Where:

USER_NAME. Name of the created user (schema).
USER_PASSWORD. Password for the created user (schema).
TABLESPACE_NAME. Name of user tablespace.
TEMP_TABLESPACE_NAME. Name of temporary tablespace for the user.

After executing all the steps, proceed to creating a repository.

Features of Working with Oracle 10

In Oracle 10 server or later it is required to expand the list of grants in executable script:

grant create table to USER_NAME;

grant create view to USER_NAME;

grant create synonym to USER_NAME;

grant create cluster to USER_NAME;

grant create database link to USER_NAME;

grant create sequence to USER_NAME;

grant alter session to USER_NAME;

Features of Working with Oracle 12c Release 1

In Oracle 12c Release 1 or later the "resource" role does not give the "unlimited" privilege anymore.

On setting up a server do not execute the command:

revoke unlimited tablespace from USER_NAME;

The attempt of executing the command will result in error.