Loading Siebel Sample Data into Oracle Server

Oracle XE is a a bit of beast, a pain in the proverbial to install and overkill for the purpose of a local Siebel database. However, it is at least an Oracle database and not a Sybase database, as it was in days gone by.

What this means is that it is possible to use Oracle tools to access and manipulate the data. Gone are the days of disql and it’s more than limited functionality. We can now access the local and sample databases using SQL Developer and other Oracle tools, such as import / export and data pump. I used this fact to my advantage, recently, to populate a fresh Siebel 16 server installation with demo data. This lets me use the full gamut of thin client apps, with useful data and users.

Terry Smythe's Accounts!

Terry Smythe’s Accounts!

Install the Sample DB

First up, we need to install the XE sample using the Tools or Client installers. All straight forward (as of 16.2, anyway) and you’ll be left with an XE Service that’s running and listening on localhost, port 1521.

Reset the System Password

We now need to get access to the system account, so that we can mess around. Bring up a command prompt and enter:

This will reset the “system” password. Note that SADMIN and SIEBEL use the equivalent value for logging in.

Export the XE Sample Data

You can use Oracle Data Pump (impdp / expdp) to do this, but I found it to be a bit of a hassle – my target is an RDS instance on Amazon, so I’d have to push a load of files up in order for data pump to suck them in. As it is, I used the old fashion import / export (imp / exp) commands in XE to do the job.

First up, we don’t want all the data from sample – we really only want “S_” tables and we want to exclude the Repository tables. As such, I selected out the tables I wanted directly from S_TABLE:

I then saved this to a text file (tables.par) and modified this to work as a parameter file input into the “exp” command:

I then initiated an export from my XE database. Note that the exp command can be found in the Tools installation folder, <TOOLS>\oraclexe\app\oracle\product\11.2.0\server\bin:

This took roughly 15 minutes to complete and I’m left with a “.dmp” file of around 4GB in size.

Import the Sample Data into Oracle Server

You can probably guess the next step – using the “imp” command to import the data into my Server Oracle instance. The process is much the same as for export – the main point of note here is that we want to explicitly ignore errors. Even though we’re pushing sample data into a “clean” Siebel Database, there’s still a load of seed data in there that we don’t want duplicated. By ignoring errors, the indexes will keep duplicates out while allowing the whole process to complete without terminating:

Create our test users

Now that we have all the sample data in the server DB, we must create some users to match the thousand or so S_USER records that have been created for our use across the sample system. Again, I put Oracle’s own tools to good use and created a SQL script in SQL Developer:

Execute this script and users will be created. Be careful to exclude any users you don’t want to affect (SIEBEL and SADMIN being the main ones) as the script will reset their passwords. You can find a list of relevant test users for each Siebel vertical in this Bookshelf Guide.

Login as Casey Cheng!

It’s what you’ve always wanted, right? Fire up Siebel Call Center and login as CCHENG. Ah, memories!

Now sit back and enjoy perusing the sample data from the comfort of your full Siebel installation.