Site icon Radu Pârvu

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

Radu

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

Procedure

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

  1. In Oracle SQL Developer client, navigate to Tools -> Database Export… :
  2. 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‘.
  3. In the next screen, choose the object types: Click ‘Next‘.
  4. In the next window, choose More — fill-in the schema ‘SSB’ — select type ‘ALL OBJECTS‘ — Lookup:
  5. Select all the listed objects on the left pane and move them to the right by clicking the double right arrow: Click Next.
  6. On the next screen, click ‘Finish’ and the object create script will be ready soon:
  7. As an example I am sharing here the object create script I did using this method.

Note 1: The method above is not SSB specific, it can be used to export the DDL specific to any schema or object/s.

Note 2: I link here the create user script alongside the relevant role (Oracle Clous specific).

Note 3: For on-prem, this script can be used to create the user.

References

Exit mobile version