Sample Schemas
3 min read
Photo by Skylar Kang on Pexels.com
In Oracle Database, a database schema is a collection of logical data structures, or schema objects. A database user owns a database schema, which has the same name as the user name.
Schema objects are user-created structures that directly refer to the data in the database. The database supports many types of schema objects, the most important of which are tables and indexes.
A schema object is one type of database object. Some database objects, such as profiles and roles, do not reside in schemas.
The sample database schemas provide a common platform for examples in each release of the Oracle Database. The sample schemas are a set of interlinked database schemas. This set provides approach to complexity:
• Schema Human Resources (HR) is useful for introducing basic topics.
• Schema Order Entry (OE) is useful for dealing with matters of intermediate complexity. Many data types are available in this schema, including nonscalar data types.
For many years, Oracle used the simple database schema SCOTT, with its two prominent tables EMP and DEPT, for various examples in documentation and training.
Benefits of Sample Schemas
• Continuity of context. When encountering the same set of tables everywhere, users, students, and developers can spend less time becoming familiar with the schema and more time understanding or explaining the technical concepts.
• Usability. Customers can use these schemas in the seed database to run examples that are shown in Oracle Database documentation and training materials. This first-hand access to examples facilitates both conceptual understanding and application development.
Installing HR Schema Using Database Configuration Assistant
Select the sample schemas option to install HR schema in the database.
At the end of the installation process, a dialog box displays the accounts that have been created and their lock status. By default, sample schemas are locked and their passwords are expired. Before you can use a locked account, you must unlock it and reset its password.
You can unlock the accounts at this point in the installation process. Alternatively, after the installation completes, you can unlock the schemas and reset their passwords by using the ALTER USER … ACCOUNT UNLOCK statement.
For example:
ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY Password;
Manually Installing the HR Schema
All scripts necessary to create the 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.
1. Log on to SQL*Plus as SYS and connect using the AS SYSDBA privilege.
sqlplus connect sys as sysdba
Enter password: password
2. To run the hr_main.sql script, use the following command:
SQL> @?/demo/schema/human_resources/hr_main.sql
3. Enter a secure password for HR
specify password for HR as parameter 1:
Enter value for 1:
Enter an appropriate tablespace, for example, users as the default tablespace for
HR
specify default tablespace for HR as parameter 2:
Enter value for 2:
4. Enter temp as the temporary tablespace for HR
specify temporary tablespace for HR as parameter 3:
Enter value for 3:
5. Enter your SYS password
specify password for SYS as parameter 4:
Enter value for 4:
6. Enter the directory path, for example, $ORACLE_HOME/demo/schema/log/, for your
log directory
specify log path as parameter 5:
Enter value for 5:
After script hr_main.sql runs successfully and schema HR is installed, you are
connected as user HR. To verify that the schema was created, use the following
command:
SQL> SELECT table_name FROM user_tables;
Uninstalling HR Schema
sqlplus system/systempw@connect_string
@drop_hr.sql
HR Sample Schema Scripts and Objects
- hr_analz.sql Collects statistics on the tables in the schema
- hr_code.sql Creates procedural objects in the schema
- hr_comnt.sql Creates comments for each object in the schema
- hr_cre.sql Creates the HR objects
- hr_dn_c.sql Adds the distinguished name column used by Oracle Internet Directory to the employees and departments tables
- hr_dn_d.sql Drops the Oracle Internet Directory distinguished name column from employees and departments
- hr_drop.sql Drops schema HR and all its objects
- hr_idx.sql Creates indexes on the HR tables
- hr_main.sql Main script for schema HR ; calls other scripts
- hr_popul.sql Populates the objects
