[ Home ]

Cover of SQL Cover of SQL Tricks Cover of SQL Short Course Cover of SQL Visual QuickStart Guide 3rd Edition

Creating the Sample Database in Oracle 11g Release 2

To create books in Oracle 11g Release 2:

  1. Start Database Configuration Assistant.

    This procedure varies by platform. In Windows, for example, choose Start > All Programs > Oracle - OraDb11g_home1 > Configuration and Migration Tools > Database Configuration Assistant.

  2. Click Next to skip the Welcome page.

  3. On the Operations page, select Create a Database, and then click Next.

    Database Configuration Assistant guides you through the steps needed to create a database.

    Operations page
  4. On the Database Templates page, select General Purpose or Transaction Processing, and then click Next.

    Database Templates page
  5. On the Database Identification page, type books in the Global Database Name and SID boxes, and then click Next.

    Database Identification page
  6. On the Management Options page, accept the default settings, and then click Next.

  7. On the Database Credentials page, set the user password(s), and then click Next.

  8. On the Database File Locations page, select “File System” for the Storage Type, select “Use Database File Locations from Template” for the Storage Locations, and then click Next.

  9. On the Recovery Configuration page, accept the default settings, and then click Next.

  10. On the Database Content page, accept the default settings, and then click Next.

  11. On the Initialization Parameters page, select the following options, and then click Next.

    Memory tab: Select Typical and accept the default percentage. (This setting is appropriate to create a database with minimal user input.)

    Initialization Parameters page - Memory tab

    Sizing tab: Accept the default settings.

    Character Sets tab: Accept the default settings.

    Connection Mode tab: Select Dedicated Server mode. (This setting is appropriate when only a small number of clients will use the database.)

    Initialization Parameters page - Connection Mode tab
  12. On the Database Storage page, accept the default settings, and then click Next.

    Database Storage page
  13. On the Creation Options page, select Create Database, and then click Finish.

    Create Options page
  14. In the Confirmation dialog box that opens, review the configuration options (and save them to an HTML file if you like), and then click OK.

    A progress meter appears while Oracle creates the database.

  15. Click Exit in the message box that opens when Oracle finishes creating the database.

  16. Start SQL*Plus (sqlplus) and connect to the books database.

    At a command prompt, type:

    sqlplus user/password@dbname

    user is your Oracle user name, password is your password, and dbname is the name of the database to connect to (books, in this case). For security, you can omit the password and instead type:

    sqlplus user@dbname

    SQL*Plus will prompt you for your password.

    If you’re running Oracle locally you can use the user name system and the password that you set in step 7:

    sqlplus system@books

    If you’re connecting to a remote Oracle database, ask your database administrator (DBA) for the connection parameters.

    Tip: To open a command prompt in Windows, choose Start > All Programs > Accessories > Command Prompt.

  17. At the SQL prompt, type:


    and then press Enter. You can include an absolute or relative pathname (see the “Paths” section in “Running SQL Programs” in Chapter 1 of SQL or SQL Tricks, or in Appendix B of SQL Short Course or SQL Crash Course for Beginners).

    sqlplus displays the results. Ignore the messages about nonexistent tables — they’re caused by the script’s DROP TABLE statements, which you’ll need to rerun books_oracle.sql later to restore the tables to their original states.

  18. To run SQL scripts and interactive statements against books, see “Oracle” in Chapter 1 of SQL or SQL Tricks, or in Appendix B of SQL Short Course or SQL Crash Course for Beginners.