SSB Schema creation on Oracle DB by using Autonomous and SQL Developer

The Star Schema benchmark (SSB) is designed to be used to measure performance of database products in support of classical data warehouses. It is based on the TPC-H benchmark. The main change from TPC-H to SSB is the merge of the tables LINEITEM and ORDERS into table LINEORDER.

One of the problems that typically arises when setting up the SSB on Oracle is creating the empty database objects that can be populated afterwards. Other options to achieve this are outlined in some of the resources mentioned at the end of the article.

Prerequisites

Access to an existing ADB service
Oracle SQL Developer installed locally on the Client workstation
The current method will use the Oracle Autonomous Database service accessed via Oracle SQL Developer client. How to connect the Oracle SQL Developer client to the autonomous service (either TP or DW) is a prerequisite and not in the scope of this article

Procedure

The method is based on exporting the DDL from the ADB via SQL Developer.

In Oracle SQL Developer client, navigate to Tools -> Database Export… :
In the next pop-up
- Fill in the connection info (sufficient rights are required, in this case I used ADMIN)
- Ensure you check ‘Export DDL’ and the other desired options
- Uncheck ‘Export Data’.
- Save as single file and specify the path
- Click ‘Next‘.
In the next screen, choose the object types: Click ‘Next‘.
In the next window, choose More — fill-in the schema ‘SSB’ — select type ‘ALL OBJECTS‘ — Lookup:
Select all the listed objects on the left pane and move them to the right by clicking the double right arrow: Click Next.
On the next screen, click ‘Finish’ and the object create script will be ready soon:
As an example I am sharing here the object create script I did using this method.