Sample Schemas facility in Oracle Database 11G

Author: Bruno Reis da Silva (techdatabasket.com),
Revised by : Cleber Jose Campos Martins and Carlos Magno de Andrade Junior

Posted in June 2016

Original Version on OTN

During Oracle Corporation read the documentation, you can find some references and examples using so-called users Sample Schemas , which includes well – known users of the administrators of databases, such as the Scott users, RH, SH and many others. These users have a wide variety of objects that can be of great use in simulations of everyday situations in the database.

Thinking about it, the main focus will be installing the Sample Schemas. The installation environment used is an operating system Oracle Linux 6.3 with 32-bit CPU processing version along with an Oracle database version 11.2.0.3.0.

Therefore, the installation package will be used to “p10404530_112030_platform_6of7.zip”. However, if you are using another operating system platform, download the package related to your system is necessary in the “My Oracle Support (MetaLink).”

Thus, to make the installation of Samples Schemas you must follow the following steps:

  • Download installation software related to the operating system used:

As I mentioned earlier:

– Operating system: Linux 6.3
– Installation Package: p10404530_112030_LINUX_6of7.zip
– CPU processing Version: 32 bit

  • Unzipping the installation package:
 [Oracle @ tech_machine] $ unzip p10404530_112030_LINUX_6of7.zip 
 Archive: p10404530_112030_LINUX_6of7.zip 
 creating: examples / 
 creating: examples / doc / 
 creating: examples / doc / dcommon / 
 creating: examples / doc / dcommon / css / 
 inflating: examples / doc / dcommon / css / blafdoc.css 
 inflating: examples / doc / dcommon / css / bp_layout.css 
 Extracting: examples / doc / dcommon / css / darbbook.css 
 inflating: examples / doc / dcommon / css / doccd.css 
 creating: examples / doc / dcommon / dtds / 
 inflating: examples / doc / dcommon / dtds / targetdatabase.dtd 
 creating: examples / doc / dcommon / gifs /

 ... (List of all the unzipped files) 

 inflating: examples / stage / Components / oracle.sqlj.companion / 11.2.0.3.0 / 1 / DataFiles / filegroup1.jar 
 inflating: examples / stage / Components / oracle.sqlj.companion / 11.2.0.3.0 / 1 / DataFiles / filegroup3.jar 
 inflating: examples / stage / Components / oracle.sqlj.companion / 11.2.0.3.0 / 1 / DataFiles / filegroup2.jar 
 creating: examples / stage / sizes / 
 Extracting: examples / stage / sizes / oracle.companionCD.db.DB.sizes.properties 
 inflating: examples / stage / oracle.companionCD.db.11_2_0_3_0.xml 
 inflating: examples / stage / invDetails.properties 
 inflating: examples / stage / install1.jar 
 creating: examples / stage / GlobalVariables / 
 inflating: examples / stage / GlobalVariables / variable.properties 
 inflating: examples / stage / GlobalVariables / globalvar.xml 
 inflating: examples / stage / oracle.companionCD.db.11_2_0_3_0_1316395524086.bak 
 inflating: examples / readme.html
  • Installing Samples Schemas: After unzipping the file, a directory will be created with the name of “examples” and within it is contained the runInstaller which is the file used during installation:
  • Location of runInstaller startup script:
 [Oracle @ tech_machine] $ cd examples 
 [Oracle @ tech_machine] $ ls ltr 
 Total 60 
 -rwxr-xr-x.  1 oracle oinstall 3473 Aug 22 2011 welcome.html 
 -rwxr-xr-x.  1 oracle oinstall 10009 Sep 22 2011 runInstaller 
 drwxr-xr-x.  12 oracle oinstall 4096 Sep 22 2011 stage 
 drwxr-xr-x.  2 oracle oinstall 4096 Sep 22 2011 response 
 drwxr-xr-x.  4 oracle oinstall 4096 Sep 22 2011 install 
 drwxr-xr-x.  6 oracle oinstall 4096 Sep 22 2011 doc 
 -rwxr-xr-x.  1 oracle oinstall 28122 Sep 22 2011 readme.html
  • Starting the runInstaller script:
 [Oracle @ tech_machine] $ ./runInstaller 
 Starting Oracle Universal Installer ...
 Checking Temp space: must be greater than 120 MB.  Actual 1158 MB Passed 
 Checking swap space: must be greater than 150 MB.  Actual 3555 MB Passed 
 Checking monitor: must be configured to display at least 256 colors.  Current 16777216 Passed 
 Preparing to launch Oracle Universal Installer from / tmp / OraInstall2016-06-11_09-00-09PM. 
 Please wait ... [oracle @ tech_machine] $
  • Download Software Updates: In this option you can enter your credentials for access to Metalink, use pre-downloads or simply skip this step through the “Skip updates software”:

1. Download Software Updates

  • Specifying the installation location of Samples Schemas:

2. Installation Location

  • Verification of prerequisites: In this section will be performed to check the basic requirements for installing the Sample Schemas successfully happen.

3. Checking of the prerequisites

  • Installation Summary: this step will show a summary of the installation information.

4. Installation Summary

  • Installation Progress: Party where, in fact, the packages containing the information of the Sample Schemas, begin to be installed.

5. Installation Progress

  • End of installation: After the installation, a screen will be displayed informing the completion of the same. Finally, simply click the “Close” button.

6. Termination of the installation

After installing the package you can find several scripts in the $ ORACLE_HOME / demo / schema, which can be installed. Thus, following the example of a HR user installation, which is the smallest and the simplest user-called Sample Schemas, by running the hr_main.sql script, which in addition to ordering the information to the user creation inside the base data, will create the objects, popular tables and collect statistics for the HR user during its creation.

 [Oracle @ tech_machine] $ sqlplus "/ as sysdba"
 SQL * Plus: Release 11.2.0.3.0 Production on Mon Jun 13 10:51:16 2016
 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 Connected to: 
 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
 SQL> @ hr_main.sql
 specify password for the HR parameter 1: 
 Enter value for 1: *********
 specify default tablespeace HR is the parameter 2: 
 Enter value for 2: USERS
 specify temporary table space for the HR parameter 3: 
 Enter value for 3: TEMP
 specify password for SYS the parameter 4: 
 Enter value for 4: *********
 specify the log path parameter 5: 
 Enter value for 5: /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/human_resources

After creating the user, we can already perform the tests with the user created. As described above, the Samples Schemas are quite used in the documentation of Oracle Corporation, as the example below is contained in the section “8 Managing a Materialized View Replication Environment” manual “Database Advanced Replication Management API Reference” performing a refresh of a materialized view:

 [Oracle @ tech_machine] $ sqlplus "/ as sysdba"
 SQL * Plus: Release 11.2.0.3.0 Production on Mon Jun 13 11:33:18 2016
 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 SQL> BEGIN
 DBMS_MVIEW.REFRESH (
 list => 'hr.departments_mv' 
 method =>) '?';
 END;
 /

CONCLUSION:

Samples Schemas can assist the Database Administrator in the simulation of a real environment on a day-to-day of it. Another point to note is that during these simulations you can use your own testing of official documentation of Oracle Corporation, since it makes use of own Samples Schemas in presenting their examples.

REFERENCE:

[1] https://docs.oracle.com/cd/E11882_01/server.112/e10707/rarmanmv.htm#REPMA389
[2] https://docs.oracle.com/cd/E18283_01/server.112/e10831/installation.htm#I6806


Bruno Reis da Silva is Brazilian, Scientist Computer, Database Administrator Oracle / MySQL for over five years and professional certificate in database administration. Specialist administration and database infrastructure with experience in the implementation and management of database environments with Data Warehouse, Business Intelligence, database security, performance tuning , and high availability. He has managed the databases of major Brazilian and international companies. It also shares information on your blog techdatabasket.com.

Cleber Jose Campos Martins is Brazilian, Oracle Database Administrator for over 15 years and professionally OCP certificate for versions 10g, 11g and 12c and Oracle RAC 11g Release 2 and Grid Infrastructure Administration .With experience in versions 7 will 12c in companies like IBM, Marisa Lojas and DPaschoal.

Carlos Magno de Andrade Junior, now working as Database Architect eProseed in Europe, with over 15 years of experience in Oracle database, having worked in companies like Oracle Corporation, IBM, HSBC, among others. It also shares information on your blog ezdba.wordpress.com.

This article was reviewed by the Oracle product team and is in accordance with the standards and practices for the use of Oracle products.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s