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/.
```
sudo mv ojdbc<version_number>.jar /usr/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
SQL> @/usr/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;

/

Set up an Oracle database

We want your opinion

How can we improve this page?