Set up an Oracle database

You install an Oracle database to serve as the backend database for the Hive metastore. You can use the free Express edition. You also install the Oracle driver on your cluster, and then configure the database.

Install Oracle on a node in your cluster.
Download the Oracle JDBC driver from the Oracle web site, and put the JDBC JAR into /usr/lib/hive/lib/ if you are using a Package install method or place the JAR in /opt/cloudera/parcels/CDH/lib/hive/lib/ if you are using a Parcel install method.
```
-- For Package install method:
sudo mv ojdbc<version_number>.jar /usr/lib/hive/lib/
-- For Parcel install method:
sudo mv ojdbc<version_number>.jar /opt/cloudera/parcels/CDH/lib/hive/lib/
```

Connect to the Oracle database as administrator, and create a user account to access the metastore.

sqlplus "sys as sysdba"
SQL> create user hiveuser identified by mypassword;
SQL> grant connect to hiveuser;
SQL> grant all privileges to hiveuser;

Connect as the hiveuser, and load the initial schema, using the script for your release n.n.n.

sqlplus hiveuser
-- For Package install method:
SQL> @/usr/lib/hive/scripts/metastore/upgrade/oracle/hive-schema-n.n.n.oracle.sql
-- For Parcel install method:
SQL> @/opt/cloudera/parcels/CDH/lib/hive/scripts/metastore/upgrade/oracle/hive-schema-n.n.n.oracle.sql

Connect to the Oracle database as administrator and remove the power privileges from user hiveuser.
```
sqlplus "sys as sysdba"
SQL> revoke all privileges from hiveuser;
```

Grant limited access to all the tables.

SQL> BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='HIVEUSER') LOOP
  EXECUTE IMMEDIATE 'grant  SELECT,INSERT,UPDATE,DELETE on '||R.owner||'.'||R.table_name||' to hiveuser';
  END LOOP;
END;

/