Exploring the example database

In this recipe, we will prepare a database to use for our examples.

Getting ready

We need an Oracle Database 11gR2 system up and running to create our database. The host system could be a UNIX/Linux or Windows physical or virtual machine. If you want to use a virtual machine, be sure to follow the minimum CPU and memory requirements for the Oracle installation.

If you have installed the database software along with the Create Database option, then you have already set up a database with the necessary schema installed.

How to do it...

We will use the default demo database installed by the default OLTP template of Oracle Database Configuration Assistant (DBCA) for all our examples.

Note

You can find the official Oracle Database Installation Guide 11gR2 for Linux at http://download.oracle.com/docs/cd/E11882_01/install.112/e16763/toc.htm.

1. Log on to the Operating System as a member of the administrative group, authorized to install Oracle software and to create and run database instances.

2. Launch DBCA (for Windows users: Start | Programs | Oracle – home_name | Configuration and Migration Tools | Database Configuration Assistant) for *nix systems enter the following command at system prompt:

   $ dbca
   

   Please note that the dbca executable is by default in the $ORACLE_HOME/bin directory.

3. A welcome screen is shown. Click Next.

4. You are presented with some options. Select the first, namely Create a database, and click Next.

5. You are presented a list of database templates. Choose the first, namely General purpose / OLTP, and click Next.

6. You are asked for the global database name and SID; enter TESTDB in the global database name (the SID should be set accordingly) and click Next.

7. In the next screen—shown in the following screenshot—leave the default options selected (OEM configuration). If you wish, you can enable e-mail notifications, checking the corresponding flag and entering the SMTP server to use (something like smtp.yourdomain.com or smtp.yourISP.com) and the e-mail address where the alerts will be delivered. Click Next to go to the next screen.

   

8. Choose to use the same password for all administrative accounts, enter the password you want to use twice, and click Next. If you are advised that the password you entered is weak (not responding to the minimum complexity requirements) you can ignore the message and go on. Please note that for a production database these are very bad choices, but we are installing a demo database for testing purposes only and don't want to bother with security issues.

9. In the next screen, leave the default option for the files position (Use Database File Locations from Template) and click Next.

10. Leave the default options for the flash recovery area and click Next.

11. In the next screen, check the Sample Schemas flag and click Finish.

12. You are presented with the operations summary. Click OK and wait until the database creation process is finished.

13. At the end of the creation process, we have to unlock the accounts created. In the summary form, there is a Password Manager button; click on it, and you will be presented with the list of accounts created.

14. Find the following accounts: BI, HR, IX, OC, OE, PM, SH and uncheck the second column (unlocking them). Insert the password for the accounts in the last two columns, setting them the same as the account name.

    Tip

    You can click on the username column to sort accordingly.

    Don't use sample schemas or passwords the same as the username in production databases!

Now our TESTDB database is ready for experimenting.

How it works...

Oracle DBCA lets us create a database using predefined templates. For our examples, we will use the default example schemas provided by Oracle (which are installed in the EXAMPLE tablespace).

The sample schemas are HR (Human Resources), OE (Order Entry), OC (Order Catalog), PM (Product Media), IX (Information eXchange), SH (Sales History), and BI (Business Intelligence). We will use mostly HR and SH schemas.

There's more...

If we want to reset the sample schemas to the initial state, we can use the script mksample.sql located in the $ORACLE_HOME/demo/schema/ directory. This script requires eleven parameters, with the following syntax:

SQL>@?/demo/schema/mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd default_tablespace temp_tablespace log_file_directory/

Note

Please note that the log_file_directory is an already existing folder and also the path must be terminated by a slash.

Our database—assuming test as the system and system password—will be reset with the following statement:

SQL>@?/demo/schema/mksample test test hr oe pm ix sh bi EXAMPLE TEMP testlog/

Note

Please note that in the default installation of Oracle Database 11gR2 Enterprise Edition the mksample.sql script is not present.

You can find it in the Companion CD.