没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
Skip Headers
Oracle9i Sample Schemas
Release 2 (9.2)
Part Number A96539-01
Home Book
List
Master
Index
FeedbackContents
4
i Sample Schema Scripts
This chapter contains the scripts used to generate the Oracle9i Sample Schemas.
Each section corresponds to a separate schema. This chapter contains these
sections:
About the Scripts
Master Script
Human Resources (HR) Schema Scripts
Order Entry (OE) Schema Scripts
Product Media (PM) Schema Scripts
Queued Shipping (QS) Schema Scripts
Sales History (SH) Schema Scripts
About the Scripts
There are two sets of scripts for each schema:
One script that resets and creates all objects and data for a particular
schema. This script is named xx_main.sql, where xx is the schema abbreviation.
This main script calls all other scripts necessary to build and load the
schema.
One script that erases all objects from a particular schema, called
xx_drop.sql, where xx is the schema abbreviation.
The Oracle9i Sample Schemas script directories are located in
$ORACLE_HOME/demo/schema.
Note:
This chapter does not include the scripts that populate the
schemas, because they are very lengthy.
Master Script
The master script sets up the overall Sample Schema environment and creates all
five schemas.
Note:
In the master script (mksample.sql) that follows, you will notice
variables including %s_pmPath%, %s_logPath%, and %s_shPath%. These
variables are instantiated upon installation.
mksample.sql
Rem
Rem $Header: mksample.sql 05-dec-2001.16:41:15 ahunold Exp $
Rem
Rem mksample.sql
Rem
Rem Copyright (c) 2001, Oracle Corporation. All rights reserved.
Rem
Rem NAME
Rem mksample.sql - creates all 5 Sample Schemas
Rem
Rem DESCRIPTION
Rem This script rees and creates all Schemas belonging
Rem to the Oracle9i Sample Schemas.
Rem If you are unsure about the prerequisites for the Sample Schemas,
Rem please use the Database Configuration Assistant DBCA to
Rem configure the Sample Schemas.
Rem
Rem NOTES
Rem - This script is edited during installation to match
Rem the directory structur on you system
Rem - CAUTION: This script will erase the following schemas:
Rem - HR
Rem - OE
Rem - PM
Rem - SH
Rem - QS, QS_ADM, QS_CB, QS_CBADM, QS_CS, QS_ES, QS_OS, QS_WS
Rem - CAUTION: Never use the above mentioned Sample Schemas for
Rem anything other than demos and examples
Rem - USAGE: To return the Sample Schemas to their initial
Rem state, you can call this script and pass the passwords
Rem for SYS, SYSTEM and the schemas as parameters.
Rem Example: @?/demo/schema/mksample mgr secure h1 o2 p3 q4 s5
Rem (please choose your own passwords for security purposes)
Rem - LOG FILES: The SQL*Plus and SQL*Loader log files are written
Rem to the equivalent of $ORACLE_HOME/demo/schema/log
Rem If you edit the log file location further down in this
Rem script, use absolute pathnames
Rem
Rem MODIFIED (MM/DD/YY)
Rem ahunold 12/05/01 - added parameters
Rem ahunold 05/03/01 - dupl lines
Rem ahunold 04/23/01 - Verification, parameters for pm_main.
Rem ahunold 04/13/01 - aaditional parameter (HR,OE,QS)
Rem ahunold 04/04/01 - Installer variables
Rem ahunold 04/03/01 - Merged ahunold_mkdir_log
Rem ahunold 03/28/01 - Created
Rem
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 999
SET ECHO OFF
SET CONCAT '.'
PROMPT
PROMPT specify password for SYSTEM as parameter 1:
DEFINE password_system = &1
PROMPT
PROMPT specify password for SYS as parameter 2:
DEFINE password_sys = &2
PROMPT
PROMPT specify password for HR as parameter 3:
DEFINE password_hr = &3
PROMPT
PROMPT specify password for OE as parameter 4:
DEFINE password_oe = &4
PROMPT
PROMPT specify password for PM as parameter 5:
DEFINE password_pm = &5
PROMPT
PROMPT specify password for all QS schemas as parameter 6:
DEFINE password_qs = &6
PROMPT
PROMPT specify password for SH as parameter 7:
DEFINE password_sh = &7
PROMPT
PROMPT Sample Schema creating will take about 40 minutes to complete...
PROMPT
CONNECT system/&&password_system
@?/demo/schema/human_resources/hr_main.sql &&password_hr example temp
&&password_sys ?/demo/schema/log/
CONNECT system/&&password_system
@?/demo/schema/order_entry/oe_main.sql &&password_oe example temp &&password_hr
&&password_sys ?/demo/schema/log/
CONNECT system/&&password_system
@?/demo/schema/product_media/pm_main.sql &&password_pm example temp &&password_
oe &&password_sys %s_pmPath% %s_logPath% %s_pmPath%
CONNECT system/&&password_system
@?/demo/schema/shipping/qs_main.sql &&password_qs example temp &&password_system
&&password_oe &&password_sys ?/demo/schema/log/
CONNECT system/&&password_system
@?/demo/schema/sales_history/sh_main &&password_sh example temp &&password_sys
%s_shPath% %s_logPath%
CONNECT system/&&password_system
SPOOL OFF
SPOOL ?/demo/schema/log/mkverify.log
SELECT owner, object_type, object_name, subobject_name, status
FROM dba_objects
WHERE ( owner in ('HR','OE','SH','PM') OR owner like 'QS%' )
AND object_name NOT LIKE 'SYS%'
ORDER BY 1,2,3,4;
SELECT owner, object_type, status, count(*)
FROM dba_objects
WHERE ( owner in ('HR','OE','SH','PM') OR owner like 'QS%' )
AND object_name LIKE 'SYS%'
GROUP BY owner, object_type, status;
SELECT owner, table_name, num_rows
FROM dba_tables
WHERE ( owner in ('HR','OE','SH','PM')
OR owner like 'QS%' )
ORDER BY 1,2,3;
SPOOL OFF
Human Resources (HR) Schema Scripts
This section shows the HR schema scripts in alphabetical order.
hr_analz.sql
Rem
Rem $Header: hr_analz.sql 12-mar-2001.15:08:47 ahunold Exp $
Rem
Rem hr_analz.sql
Rem
Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved.
Rem
Rem NAME
Rem hr_analz.sql - Gathering statistics for HR schema
Rem
Rem DESCRIPTION
Rem Staistics are used by the cost based optimizer to
Rem choose the best physical access strategy
Rem
Rem NOTES
Rem Results can be viewed in columns of DBA_TABLES,
Rem DBA_TAB_COLUMNS and such
Rem
Rem MODIFIED (MM/DD/YY)
Rem ahunold 03/12/01 - cleanup b3
Rem ahunold 03/07/01 - Merged ahunold_hr_analz
Rem ahunold 03/07/01 - Created
Rem
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET ECHO OFF
EXECUTE dbms_stats.gather_table_stats ('HR','COUNTRIES');
EXECUTE dbms_stats.gather_table_stats ('HR','DEPARTMENTS');
EXECUTE dbms_stats.gather_table_stats ('HR','EMPLOYEES');
EXECUTE dbms_stats.gather_table_stats ('HR','JOBS');
EXECUTE dbms_stats.gather_table_stats ('HR','JOB_HISTORY');
EXECUTE dbms_stats.gather_table_stats ('HR','LOCATIONS');
EXECUTE dbms_stats.gather_table_stats ('HR','REGIONS');
hr_code.sql
Rem
Rem $Header: hr_code.sql 11-may-2001.09:49:06 ahunold Exp $
Rem
Rem hr_code.sql
Rem
Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved.
Rem
Rem NAME
Rem hr_code.sql - Create procedural objects for HR schema
Rem
Rem DESCRIPTION
Rem Create a statement level trigger on EMPLOYEES
Rem to allow DML during business hours.
Rem Create a row level trigger on the EMPLOYEES table,
Rem after UPDATES on the department_id or job_id columns.
Rem Create a stored procedure to insert a row into the
Rem JOB_HISTORY table. Have the above row level trigger
Rem row level trigger call this stored procedure.
Rem
Rem NOTES
Rem
Rem CREATED by Nancy Greenberg - 06/01/00
Rem
Rem MODIFIED (MM/DD/YY)
Rem ahunold 05/11/01 - disable
Rem ahunold 03/03/01 - HR simplification, REGIONS table
Rem ahunold 02/20/01 - Created
Rem
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET ECHO OFF
REM **************************************************************************
REM procedure and statement trigger to allow dmls during business hours:
CREATE OR REPLACE PROCEDURE secure_dml
IS
BEGIN
IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205,
'You may only make changes during normal office hours');
END IF;
END secure_dml;
/
剩余137页未读,继续阅读
yiranking
- 粉丝: 1
- 资源: 2
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
- 1
- 2
前往页