Oracle® Database Sample Schemas 10g Release 2 (10.2) Part Number B14198-01 |
|
|
View PDF |
During a complete installation of Oracle Database, Sample Schemas can be installed automatically with the seed database. If the seed database is removed from your system, then you will need to reinstall Sample Schemas before you can duplicate the examples you find in Oracle documentation and training materials.
This chapter describes how to install Sample Schemas. It contains the following sections:
Caution: By installing any of the Oracle Database Sample Schemas, you will destroy any previously installed schemas that use any of the following user names:
Data contained in any of these schemas will be lost if you run any of the installation scripts described in this section. You should not use Oracle Database Sample Schemas for your personal or business data and applications. They are meant to be used for demonstration purposes only. |
When you install Oracle with the Oracle Universal Installer, the sample schemas are installed by default when you select the Basic Installation option. If you have installed your database with the Advanced Option and did not choose to install the sample schemas, you can install the Sample Schemas with the Database Configuration Assistant (DBCA). When you run DBCA, selecting the Sample Schemas option installs all five schemas (HR, OE, PM, IX, SH) in your database.
At the end of the installation process, a window displays the accounts that have been created and their lock status. All Sample Schemas are locked by default. You can unlock the accounts at this point in the installation process. Alternatively, after the installation is complete, you can unlock the schemas with an ALTER
USER
... ACCOUNT UNLOCK
statement.
The sample schemas available to you depend on the edition of Oracle that you have installed and its configuration. Refer to the following table:
Schema | Oracle Database Personal edition | Oracle Database Standard edition | Oracle Database Enterprise edition |
---|---|---|---|
HR | OK | OK | OK |
OE | OK | OK | OK |
PM | OK | OK | OK |
IX | OK | OK | OK |
SH | Not available | Not available | Needs Partitioning Option installed |
You can also create Sample Schemas manually by running SQL scripts, rather than using DBCA. The scripts are included in the companion directory on the installation medium.
Various dependencies have been established among the schemas. Therefore, when you create the schemas manually, you must create them in the following order: HR
, OE
, PM
, IX
, and SH
.
Use this sequence to create the schemas:
Create the HR
schema.
Create the OE
schema: The HR
schema is already present, and you must know the password for the HR
schema to grant HR
object privileges to OE
. Some HR
tables are visible to the OE
user by using private synonyms. In addition, some OE
tables have foreign key relationships to HR
tables.
Note: TheOE schema requires the database to be enabled for spatial data. You can accomplish this during installation or later using the Database Configuration Assistant. |
Create the PM
schema: Foreign key relationships require that the OE
schema already exist when the PM
schema is created. You need to know the password for OE
to grant to PM
the right to establish and use these foreign keys.
Note: ThePM schema requires the database to be enabled for the Java Virtual Machine (JVM) and interMedia. You can accomplish this during installation or later using the Database Configuration Assistant. |
Create the IX
schema: The information exchange schema IX
is based on order entry data in OE
. Again, foreign key relationships require that the OE
schema already be present when the IX
schema is created. You need to know the password for OE
to grant to IX
the right to establish and use the foreign keys.
Create the SH
schema. The SH
schema logically depends on the OE
schema, though you can create this schema without creating the other four schemas.
All scripts necessary to create Human Resource (HR) schema reside in $ORACLE_HOME
/demo/schema/human_resources
.
You need to call only one script, hr_main.sql
, to create all the objects and load the data. Running hr_main.sql
accomplishes the following tasks:
Prompts for passwords and tablespace names used within the scripts
Removes any previously installed HR
schema
Creates the user HR
and grants the necessary privileges
Connects as HR
Calls the scripts that create and populate the schema objects
For a complete listing of the scripts and their functions, refer to Table 4-1.
A pair of optional scripts, hr_dn_c.sql
and hr_dn_d.sql
, is provided as a schema extension. To prepare the HR schema for use with the directory capabilities of Oracle Internet Directory, run the hr_dn_c.sql
script. If you want to return to the initial setup of the HR
schema, then use the hr_dn_d.sql
script to undo the effects of the hr_dn_c.sql
script.
The hr_drop.sql
script is used to drop the HR
schema.
All scripts necessary to create the Order Entry (OE) schema and its Online Catalog (OC) subschema reside in $ORACLE_HOME
/demo/schema/order_entry
.
You need to call only one script, oe_main.sql
, to create all the objects and load the data. Running oe_main.sql
accomplishes the following tasks:
Prompts for passwords and tablespace names used within the scripts
Removes any previously installed OE
schema
Creates the user OE
and grants the necessary privileges
Connects as OE
Calls the scripts that create and populate the schema objects
For a complete listing of the scripts and their functions, refer to Table 4-2, respectively.
The oe_drop.sql
and oc_drop.sql
scripts are used to drop the OE
schema and OC
subschema.
All files necessary to create Product Media (PM) schema reside in $ORACLE_HOME
/demo/schema/product_media
.
You need to call only one script, pm_main.sql
, to create all the objects and load the data. Running pm_main.sql
accomplishes the following tasks:
Prompts for passwords and tablespace names used within the scripts, as well as datafile and log file directories
Removes any previously installed PM
schema
Creates the user PM
and grants the necessary privileges
Connects as PM
Calls the following scripts that create and populate the schema objects
For a complete listing of the scripts and their functions, refer to Table 4-3 .
The pm_drop.sql
script is used to drop the PM
schema.
Note: The SQL*Loader data filepm_p_lob.dat contains hard-coded absolute path names that have been set during installation. Before attempting to load the data in a different environment, you should first edit the path names in this file. |
To install the Information Exchange (IX) schema, you need to call only one script, ix_main.sql
, to create all the objects and load the data. Running ix_main.sql
accomplishes the following tasks:
Prompts for passwords and tablespace names used within the scripts
Removes any previously installed IX
schema
Creates the user IX
and grants the necessary privileges
Connects as IX
Calls the scripts that create and populate the schema objects
For a complete listing of the scripts and their functions, refer to Table 4-4.
The ix_drop.sql
script is used for dropping the IX
schema.
All files necessary to create the Sales History (SH) schema reside in $ORACLE_HOME
/demo/schema/sales_history
.
You need to call only one script, sh_main.sql
, to create all the objects and load the data. Running sh_main.sql
accomplishes the following tasks:
Prompts for passwords and tablespace names used within the scripts, as well as datafile and log file directories
Removes any previously installed SH
schema
Creates the user SH
and grants the necessary privileges
Connects as SH
Calls the scripts that create and populate the schema objects
For a complete listing of the scripts and their functions, refer to Table 4-5.
Note: The dimension tablesPROMOTIONS , CUSTOMERS , PRODUCTS and the fact table SALES are loaded by SQL*Loader, after which directory paths are created inside the database to point to the load and log file locations. This allows the loading of the COSTS table by using the external table sales_transactions_ext . |
A pair of optional scripts, sh_olp_c.sql
and sh_olp_d.sql
, is provided as a schema extension. To prepare the SH
schema for use with the advanced analytical capabilities of OLAP Services, run the sh_olp_c.sql
create script. If you want to return to the initial setup of the SH
schema, then use the script sh_olp_d.sql
to erase the effects of sh_olp_c.sql
and reinstate dimensions as they were before.
The file used to drop the SH
schema is sh_drop.sql
.
To reset Sample Schemas to their initial state, use the following syntax from the SQL*Plus command-line interface:
@?/demo/schema/mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd default_tablespace temp_tablespace log_file_directory/
The mksample
script expects 11 parameters. Provide the password for SYSTEM
and SYS
, and for the HR
, OE
, PM
, IX
, SH
, and BI
schemas. Specify a temporary and a default tablespace, and make sure to end the name of the log file directory with a trailing slash.
The mksample
script produces several log files:
mkverify.log
is the Sample Schema creation log file.
hr_main.log
is the HR
schema creation log file.
oe_oc_main.log
is the OE
schema creation log file.
pm_main.log
is the PM
schema creation log file.
pm_p_lob.log
is the SQL*Loader log file for PM.PRINT_MEDIA
.
ix_main.log
is the IX
schema creation log file.
sh_main.log
is the SH
schema creation log file.
cust.log
is the SQL*Loader log file for SH.CUSTOMERS
.
prod.log
is the SQL*Loader log file for SH.PRODUCTS
.
promo.log
is the SQL*Loader log file for SH.PROMOTIONS
.
sales.log
is the SQL*Loader log file for SH.SALES
.
sales_ext.log
is the external table log file for SH.COSTS
.
In most situations, there is no difference between installing a Sample Schema for the first time or reinstalling it over a previously installed version. The *_main.sql
scripts drop the schema users and all their objects.
In some cases, complex interobject relationships in the OE
or IX
schemas prevent the DROP
USER
... CASCADE
operations from completing normally. To correct these rare cases, use one of the following procedures:
For the OC
catalog subschema of the OE
schema:
Connect as the user OE
.
Run the oc_drop.sql
. script
Connect as SYSTEM
.
Ensure that no user is connected as OE
:
SELECT username FROM v$session;
Drop the user:
DROP USER oe CASCADE;
For the IX
schemas:
Connect as SYSTEM
.
Ensure that no user is connected as an IX
user:
SELECT username FROM v$session WHERE username like 'IX%';
Drop the schemas by running the dix.sql
. script. You will be prompted for passwords for the individual users.