dbi Blog

In the last post we looked at how to install the “Dell DVD Store Database Test Suite” into a PostgreSQL 9.5.2 database. In this post we’ll do the same with an Oracle database.

The starting point is exactly the same. Download the generic “ds21.tar.gz” and the vendor specific “ds21_oracle.tar.gz” files and transfer both to the node the hosts the Oracle database:

oracle@oel12102:/var/tmp/ [PROD] ls
ds21_oracle.tar.gz  ds21.tar.gz

Once extracted we have almost the same structure as in the last post for PostgreSQL:

oracle@oel12102:/var/tmp/ds2/ [PROD] ls -l
total 132
-rw-r--r--. 1 oracle oinstall  5308 Aug 12  2010 CreateConfigFile.pl
drwxr-xr-x. 5 oracle oinstall    73 May 31  2011 data_files
drwxr-xr-x. 2 oracle oinstall  4096 Dec  2  2011 drivers
-rw-r--r--. 1 oracle oinstall 30343 May 13  2011 ds2.1_Documentation.txt
-rw-r--r--. 1 oracle oinstall 10103 Nov  9  2011 ds2_change_log.txt
-rw-r--r--. 1 oracle oinstall  1608 Jul  1  2005 ds2_faq.txt
-rw-r--r--. 1 oracle oinstall  2363 May  5  2011 ds2_readme.txt
-rw-r--r--. 1 oracle oinstall  5857 Apr 21  2011 ds2_schema.txt
-rw-r--r--. 1 oracle oinstall 18013 May 12  2005 gpl.txt
-rw-r--r--. 1 oracle oinstall 32827 Nov  9  2011 Install_DVDStore.pl
drwxr-xr-x. 5 oracle oinstall  4096 May 31  2011 oracleds2

The only difference is the “oracleds2” directory. In contrast to the PostgreSQL version you do not need to create user as the scripts will connect as sysdba:

oracle@oel12102:/var/tmp/ds2/oracleds2/ [PROD] pwd
/var/tmp/ds2/oracleds2
oracle@oel12102:/var/tmp/ds2/oracleds2/ [PROD] head oracleds2_create_all.sh
# oracleds2_create_all.sh
# start in ./ds2/oracleds2
cd ./build
sqlplus "/ as sysdba" @oracleds2_create_tablespaces_small.sql
sqlplus "/ as sysdba" @oracleds2_create_db_small.sql
cd ../load/cust
sh oracleds2_cust_sqlldr.sh
cd ../orders
sh oracleds2_orders_sqlldr.sh
sh oracleds2_orderlines_sqlldr.sh

Lets go:

oracle@oel12102:/var/tmp/ds2/ [PROD] pwd
/var/tmp/ds2
oracle@oel12102:/var/tmp/ds2/ [PROD] chmod +x Install_DVDStore.pl
oracle@oel12102:/var/tmp/ds2/ [PROD] ./Install_DVDStore.pl
Please enter following parameters:
***********************************
Please enter database size (integer expected) : 100
Please enter whether above database size is in (MB / GB) : MB
Please enter database type (MSSQL / MYSQL / PGSQL / ORACLE) : ORACLE
Please enter system type on which DB Server is installed (WIN / LINUX) : LINUX
***********************************

For Oracle database scripts, total 4 paths needed to specify where cust, index, ds_misc and order dbfiles are stored.

If only one path is specified, it will be assumed same for all dbfiles.

For specifying multiple paths use ; character as seperator to specify multiple paths

Please enter path(s) (; seperated if more than one path) where Database Files will be stored (ensure that path exists) : /u02/oradata/PROD/
***********************************
Initializing parameters...
***********************************
Database Size: 100
Database size is in MB
Database Type is ORACLE
System Type for DB Server is LINUX
File Paths : /u02/oradata/PROD/
***********************************

Calculating Rows in tables!!
Small size database (less than 1 GB)
Ratio calculated : 10
Customer Rows: 200000
Order Rows / month: 10000
Product Rows: 100000

Creating CSV files....
Starting to create CSV data files....
For larger database sizes, it will take time.
Do not kill the script till execution is complete.

Creating Customer CSV files!!!
1 100000 US S 0
100001 200000 ROW S 0

Customer CSV Files created!!

Creating Orders, Orderlines and Cust_Hist csv files!!!

Creating Order CSV file for Month jan !!!
1 10000 jan S 1 0 100000 200000

Creating Order CSV file for Month feb !!!
10001 20000 feb S 2 0 100000 200000

Creating Order CSV file for Month mar !!!
20001 30000 mar S 3 0 100000 200000

Creating Order CSV file for Month apr !!!
30001 40000 apr S 4 0 100000 200000

Creating Order CSV file for Month may !!!
40001 50000 may S 5 0 100000 200000

Creating Order CSV file for Month jun !!!
50001 60000 jun S 6 0 100000 200000

Creating Order CSV file for Month jul !!!
60001 70000 jul S 7 0 100000 200000

Creating Order CSV file for Month aug !!!
70001 80000 aug S 8 0 100000 200000

Creating Order CSV file for Month sep !!!
80001 90000 sep S 9 0 100000 200000

Creating Order CSV file for Month oct !!!
90001 100000 oct S 10 0 100000 200000

Creating Order CSV file for Month nov !!!
100001 110000 nov S 11 0 100000 200000

Creating Order CSV file for Month dec !!!
110001 120000 dec S 12 0 100000 200000

All Order, Orderlines, Cust_Hist CSV files created !!!

Creating Inventory CSV file!!!!

Inventory CSV file created!!!!

Creating product CSV file!!!!

Product CSV file created!!!!

Started creating and writing build scripts for Oracle database...

Completed creating and writing build scripts for Oracle database!!

All database build scripts(shell and sql) are dumped into their respective folders.

These scripts are created from template files in same folders with '_generic_template' in their name.

Scripts that are created from template files have '_' 100 MB in their name.

User can edit the sql script generated for customizing sql script for more DBFiles per table and change the paths of DBFiles.

Now Run CreateConfigFile.pl perl script in ds2 folder which will generate configuration file used as input to the driver program.

Looks fine so lets try to load (for Oracle a separate load script was generated):

oracle@oel12102:/var/tmp/ds2/oracleds2/ [PROD] pwd
/var/tmp/ds2/oracleds2
oracle@oel12102:/var/tmp/ds2/oracleds2/ [PROD] chmod +x oracleds2_create_all_100MB.sh
oracle@oel12102:/var/tmp/ds2/oracleds2/ [PROD] ./oracleds2_create_all_100MB.sh
...
  INSERT INTO "DS2"."CATEGORIES" (CATEGORY, CATEGORYNAME) VALUES (1,'Action')
                    *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'DS_MISC'


  INSERT INTO "DS2"."CATEGORIES" (CATEGORY, CATEGORYNAME) VALUES (2,'Animation')
                    *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'DS_MISC'
...

Hm. Quotas seem to be missing. The script that creates the user is this one:

/var/tmp/ds2/oracleds2build/oracleds2_create_db_100MB.sql

Lets add the quotas for the tablespaces there:

oracle@oel12102:/var/tmp/ds2/oracleds2/ [PROD] head -21 build/oracleds2_create_db_100MB.sql

-- DS2 Database Build Scripts
-- Dave Jaffe  Todd Muirhead 8/31/05
-- Copyright Dell Inc. 2005

-- User

SET TERMOUT OFF
DROP USER DS2 CASCADE;
SET TERMOUT ON

CREATE USER DS2
  IDENTIFIED BY ds2
  TEMPORARY TABLESPACE "TEMP"
  DEFAULT TABLESPACE "DS_MISC"
  ;
ALTER USER DS2 QUOTA UNLIMITED ON CUSTTBS;
ALTER USER DS2 QUOTA UNLIMITED ON INDXTBS;
ALTER USER DS2 QUOTA UNLIMITED ON DS_MISC;
ALTER USER DS2 QUOTA UNLIMITED ON ORDERTBS;

… and try again (sorry for the long output, just want to be complete here):

oracle@oel12102:/var/tmp/ds2/oracleds2/ [PROD] pwd
/var/tmp/ds2/oracleds2
oracle@oel12102:/var/tmp/ds2/oracleds2/ [PROD] ./oracleds2_create_all_100MB.sh
oracle@oel12102:/var/tmp/ds2/oracleds2/ [PROD] ./oracleds2_create_all_100MB.sh

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 12 13:29:06 2016

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Connected.
SQL> spool CreateDS2_Tablespaces.log
SQL>
SQL> --Currently this template assumes need for only single datafile per table
SQL> --This might impact performance for larger database sizes, so either user needs to edit the generated script from this template or change logic in perl script to generate required build table space script
SQL> --Paramters that need to be changed acc to database size are - number of datafiles per table, initial size of data file and size of increments for data file in case of overflow
SQL>
SQL> --Paths for windows should be like this : c:oracledbfiles
SQL> --paths for linux should be like this : /oracledbfiles/
SQL>
SQL> CREATE TABLESPACE "CUSTTBS" LOGGING DATAFILE '/u02/oradata/PROD/cust_1.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE "CUSTTBS" LOGGING DATAFILE '/u02/oradata/PROD/cust_1.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
*
ERROR at line 1:
ORA-01543: tablespace 'CUSTTBS' already exists

SQL> ALTER TABLESPACE "CUSTTBS" ADD DATAFILE '/u02/oradata/PROD/cust_2.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED ;
ALTER TABLESPACE "CUSTTBS" ADD DATAFILE '/u02/oradata/PROD/cust_2.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
*
ERROR at line 1:
ORA-01537: cannot add file '/u02/oradata/PROD/cust_2.dbf' - file already partof database

SQL> CREATE TABLESPACE "INDXTBS" LOGGING DATAFILE '/u02/oradata/PROD/indx_1.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "INDXTBS" LOGGING DATAFILE '/u02/oradata/PROD/indx_1.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
*
ERROR at line 1:
ORA-01543: tablespace 'INDXTBS' already exists

SQL> ALTER TABLESPACE "INDXTBS" ADD DATAFILE '/u02/oradata/PROD/indx_2.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED ;
ALTER TABLESPACE "INDXTBS" ADD DATAFILE '/u02/oradata/PROD/indx_2.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
*
ERROR at line 1:
ORA-01537: cannot add file '/u02/oradata/PROD/indx_2.dbf' - file already partof database

SQL> CREATE TABLESPACE "DS_MISC" LOGGING DATAFILE '/u02/oradata/PROD/ds_misc.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "DS_MISC" LOGGING DATAFILE '/u02/oradata/PROD/ds_misc.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
*
ERROR at line 1:
ORA-01543: tablespace 'DS_MISC' already exists

SQL> CREATE TABLESPACE "ORDERTBS" LOGGING DATAFILE '/u02/oradata/PROD/order_1.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "ORDERTBS" LOGGING DATAFILE '/u02/oradata/PROD/order_1.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
*
ERROR at line 1:
ORA-01543: tablespace 'ORDERTBS' already exists

SQL> ALTER TABLESPACE "ORDERTBS" ADD DATAFILE '/u02/oradata/PROD/order_2.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED ;
ALTER TABLESPACE "ORDERTBS" ADD DATAFILE '/u02/oradata/PROD/order_2.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
*
ERROR at line 1:
ORA-01537: cannot add file '/u02/oradata/PROD/order_2.dbf' - file already partof database

SQL> spool off
SQL> exit;
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 12 13:29:06 2016

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

User created.

User altered.

Grant succeeded.

Table created.

1 row created.

Table created.

Sequence created.

Package created.

Commit complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:10 2016

Path used: Direct
Path used: Direct

Load completed - logical record count 100000.

Table DS2.CUSTOMERS, partition US_PART:
100000 Rows successfully loaded.

Check the log file:
us.log
for more information about the load.

Load completed - logical record count 100000.

Table DS2.CUSTOMERS, partition ROW_PART:
100000 Rows successfully loaded.

Check the log file:
row.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:12 2016

Path used: Direct
Path used: Direct
Path used: Direct
Path used: Direct
Path used: Direct
Path used: Direct
Path used: Direct
Path used: Direct
Path used: Direct
Path used: Direct
Path used: Direct
Path used: Direct

Load completed - logical record count 10000.

Table DS2.ORDERS, partition FEB2009:
10000 Rows successfully loaded.

Check the log file:
feb_orders.log
for more information about the load.

Load completed - logical record count 10000.

Table DS2.ORDERS, partition JUL2009:
10000 Rows successfully loaded.

Check the log file:
jul_orders.log
for more information about the load.

Load completed - logical record count 10000.

Table DS2.ORDERS, partition SEP2009:
10000 Rows successfully loaded.

Check the log file:
sep_orders.log
for more information about the load.

Load completed - logical record count 10000.

Table DS2.ORDERS, partition APR2009:
10000 Rows successfully loaded.

Check the log file:
apr_orders.log
for more information about the load.

Load completed - logical record count 10000.

Table DS2.ORDERS, partition JAN2009:
10000 Rows successfully loaded.

Check the log file:
jan_orders.log
for more information about the load.

Load completed - logical record count 10000.

Table DS2.ORDERS, partition DEC2009:
10000 Rows successfully loaded.

Check the log file:
dec_orders.log
for more information about the load.

Table DS2.ORDERS, partition OCT2009:
10000 Rows successfully loaded.

Check the log file:
oct_orders.log
for more information about the load.

Table DS2.ORDERS, partition MAY2009:
10000 Rows successfully loaded.

Check the log file:
may_orders.log
for more information about the load.

Table DS2.ORDERS, partition JUN2009:
10000 Rows successfully loaded.

Check the log file:
jun_orders.log
for more information about the load.

Load completed - logical record count 10000.

Table DS2.ORDERS, partition NOV2009:
10000 Rows successfully loaded.

Check the log file:
nov_orders.log
for more information about the load.

Table DS2.ORDERS, partition MAR2009:
10000 Rows successfully loaded.

Check the log file:
mar_orders.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:14 2016

Load completed - logical record count 10000.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:14 2016

Table DS2.ORDERS, partition AUG2009:
10000 Rows successfully loaded.

Check the log file:
aug_orders.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:14 2016

Load completed - logical record count 49487.

Table DS2.ORDERLINES, partition NOV2009:
49487 Rows successfully loaded.

Check the log file:
nov_orderlines.log
for more information about the load.

Load completed - logical record count 50081.

Table DS2.ORDERLINES, partition MAY2009:
50081 Rows successfully loaded.

Check the log file:
may_orderlines.log
for more information about the load.

Load completed - logical record count 49791.

Load completed - logical record count 49763.

Table DS2.ORDERLINES, partition FEB2009:
49791 Rows successfully loaded.

Check the log file:
feb_orderlines.log
for more information about the load.

Table DS2.ORDERLINES, partition MAR2009:
49763 Rows successfully loaded.

Check the log file:
mar_orderlines.log
for more information about the load.

Load completed - logical record count 49784.

Table DS2.ORDERLINES, partition OCT2009:
49784 Rows successfully loaded.

Check the log file:
oct_orderlines.log
for more information about the load.

Load completed - logical record count 50251.

Table DS2.ORDERLINES, partition DEC2009:
50251 Rows successfully loaded.

Check the log file:
dec_orderlines.log
for more information about the load.

Load completed - logical record count 50234.

Table DS2.ORDERLINES, partition SEP2009:
50234 Rows successfully loaded.

Check the log file:
sep_orderlines.log
for more information about the load.

Load completed - logical record count 49918.

Table DS2.ORDERLINES, partition JUN2009:
49918 Rows successfully loaded.

Check the log file:
jun_orderlines.log
for more information about the load.

Load completed - logical record count 50159.

Load completed - logical record count 50206.

Load completed - logical record count 50718.

Table DS2.ORDERLINES, partition AUG2009:
50159 Rows successfully loaded.

Check the log file:
aug_orderlines.log
for more information about the load.

Table DS2.ORDERLINES, partition APR2009:
50206 Rows successfully loaded.

Check the log file:
apr_orderlines.log
for more information about the load.

Table DS2.ORDERLINES, partition JUL2009:
50718 Rows successfully loaded.

Check the log file:
jul_orderlines.log
for more information about the load.

Load completed - logical record count 49687.

Table DS2.CUST_HIST:
49687 Rows successfully loaded.

Check the log file:
jan_cust_hist.log
for more information about the load.

Load completed - logical record count 49687.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:15 2016

Table DS2.ORDERLINES, partition JAN2009:
49687 Rows successfully loaded.

Check the log file:
jan_orderlines.log
for more information about the load.
Path used: Direct

Load completed - logical record count 49791.

Table DS2.CUST_HIST:
49791 Rows successfully loaded.

Check the log file:
feb_cust_hist.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:15 2016

Path used: Direct

Load completed - logical record count 49763.

Table DS2.CUST_HIST:
49763 Rows successfully loaded.

Check the log file:
mar_cust_hist.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:15 2016

Path used: Direct

Load completed - logical record count 50206.

Table DS2.CUST_HIST:
50206 Rows successfully loaded.

Check the log file:
apr_cust_hist.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:15 2016

Path used: Direct

Load completed - logical record count 50081.

Table DS2.CUST_HIST:
50081 Rows successfully loaded.

Check the log file:
may_cust_hist.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:15 2016

Path used: Direct

Load completed - logical record count 49918.

Table DS2.CUST_HIST:
49918 Rows successfully loaded.

Check the log file:
jun_cust_hist.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:15 2016

Path used: Direct

Load completed - logical record count 50718.

Table DS2.CUST_HIST:
50718 Rows successfully loaded.

Check the log file:
jul_cust_hist.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:15 2016

Path used: Direct

Load completed - logical record count 50159.

Table DS2.CUST_HIST:
50159 Rows successfully loaded.

Check the log file:
aug_cust_hist.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:15 2016

Path used: Direct

Load completed - logical record count 50234.

Table DS2.CUST_HIST:
50234 Rows successfully loaded.

Check the log file:
sep_cust_hist.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:16 2016

Path used: Direct

Load completed - logical record count 49784.

Table DS2.CUST_HIST:
49784 Rows successfully loaded.

Check the log file:
oct_cust_hist.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:16 2016

Path used: Direct

Load completed - logical record count 49487.

Table DS2.CUST_HIST:
49487 Rows successfully loaded.

Check the log file:
nov_cust_hist.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:16 2016

Path used: Direct

Load completed - logical record count 50251.

Table DS2.CUST_HIST:
50251 Rows successfully loaded.

Check the log file:
dec_cust_hist.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:16 2016

Path used: Direct

Load completed - logical record count 100000.

Table DS2.PRODUCTS:
100000 Rows successfully loaded.

Check the log file:
prod.log
for more information about the load.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 12 13:29:16 2016

Path used: Direct

Load completed - logical record count 100000.

Table DS2.INVENTORY:
100000 Rows successfully loaded.

Check the log file:
inv.log
for more information about the load.

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 12 13:29:16 2016

Last Successful login time: Tue Apr 12 2016 13:29:16 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Index created.

Table altered.

Index created.

Table altered.

Index created.

Table altered.

Index created.

Table altered.

Index created.

Table altered.

Index created.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 12 13:29:19 2016

Last Successful login time: Tue Apr 12 2016 13:29:16 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Connected.
"DS2"."PRODUCTS"(actor) INDEXTYPE IS CTXSYS.CONTEXT
*
ERROR at line 2:
ORA-29833: indextype does not exist

"DS2"."PRODUCTS"(title) INDEXTYPE IS CTXSYS.CONTEXT
*
ERROR at line 2:
ORA-29833: indextype does not exist

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 12 13:29:19 2016

Last Successful login time: Tue Apr 12 2016 13:29:19 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Table created.

Procedure created.

Warning: Procedure created with compilation errors.

Procedure created.

Trigger created.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsCTXSYS.CONTEXT
oracle@oel12102:/var/tmp/ds2/oracleds2/ [PROD]

Ok, now it failed to create the tablespaces but this is fine. As I do not have Oracle Text installed the creation of CTXSYS.CONTEXT indexes failed. In general it looks fine. Did we get the same what we did get in the PostgreSQL instance?:

SQL> select table_name from dba_tables where owner = 'DS2' order by 1;

TABLE_NAME
--------------------------------------------------------------------------------
CATEGORIES
CUSTOMERS
CUST_HIST
DERIVEDTABLE1
INVENTORY
ORDERLINES
ORDERS
PRODUCTS
REORDER

9 rows selected.

SQL> select sequence_name from dba_sequences where sequence_owner = 'DS2' order by 1;

SEQUENCE_NAME
--------------------------------------------------------------------------------
CUSTOMERID_SEQ
ORDERID_SEQ

SQL> select index_name from dba_indexes where owner = 'DS2' order by 1;

INDEX_NAME
--------------------------------------------------------------------------------
IX_CUST_USERNAME
IX_INV_PROD_ID
IX_PROD_CATEGORY
IX_PROD_SPECIAL
PK_CATEGORIES
PK_CUSTOMERS
PK_CUST_HIST
PK_ORDERLINES
PK_ORDERS
PK_PROD_ID

10 rows selected.

SQL> select distinct name from dba_source where owner = 'DS2' order by 1;

NAME
--------------------------------------------------------------------------------
BROWSE_BY_ACTOR
BROWSE_BY_CATEGORY
BROWSE_BY_TITLE
DS2_TYPES
LOGIN
NEW_CUSTOMER
PURCHASE
RESTOCK

SQL> select constraint_name from dba_constraints where owner = 'DS2' and constraint_name not like 'SYS%' order by 1;

CONSTRAINT_NAME
--------------------------------------------------------------------------------
FK_CUSTOMERID
FK_CUST_HIST_CUSTOMERID
FK_ORDERID
PK_CATEGORIES
PK_CUSTOMERS
PK_ORDERLINES
PK_ORDERS
PK_PROD_ID

8 rows selected.

It is not exactly the same but this might be because parts of the sample application are implemented in different ways. We at at least have the same tables, almost, except for the “DERIVEDTABLE1” 🙂 I am only interested in the data anyway.

Post Views: 299

The (almost) same sample schema for all major relational databases (2) – Oracle

Daniel Westermann

Leave a Reply:

Related blog articles

Daniel Westermann

Leave a Reply:

Related blog articles

Another file system for Linux: bcachefs (2) – multi device file systems

Another file system for Linux: bcachefs (1) – basics

PostgreSQL 17: pg_buffercache_evict()

PostgreSQL 17: Split and Merge partitions