Oralce11g example schema 安装

Oracle中有些示例的schema，在部分资料中以此为示例做练习。在11g中，这些schema不会默认安装，需要单独下载安装介质6/7进行安装，再运行$ORACLE_HOME/demo下的脚本才行。

具体过程可参考《 Sample Schemas》

7个安装介质不同的作用可参考：http://blog.youkuaiyun.com/tanqingru/article/details/8056149

1.将介质6解压后进行安装。

【其他图省略】

 

2.安装schema。

Installing the HR Schema

[oracle@rhel63db schema]$ sqlplus "/as sysdba"

SQL> select username from dba_users order by username;

 

USERNAME

------------------------------

ANONYMOUS

APEX_030200

APEX_PUBLIC_USER

APPQOSSYS

CTXSYS

DBSNMP

DIP

EXFSYS

FLOWS_FILES

MDDATA

MDSYS

MGMT_VIEW

OLAPSYS

ORACLE_OCM

ORDDATA

ORDPLUGINS

ORDSYS

OUTLN

OWBSYS

OWBSYS_AUDIT

SCOTT

SI_INFORMTN_SCHEMA

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

SYS

SYSMAN

SYSTEM

TAN

WMSYS

XDB

XS$NULL

 

31 rows selected.

 

SQL>

 

SQL> @?/demo/schema/human_resources/hr_main.sql

输入提示的相关参数

specify password for HR as parameter 1:

Enter value for 1: hr

 

specify default tablespeace for HR as parameter 2:

Enter value for 2: users

 

specify temporary tablespace for HR as parameter 3:

Enter value for 3: temp

 

specify password for SYS as parameter 4:

Enter value for 4: system

 

specify log path as parameter 5:

Enter value for 5:  $ORACLE_HOME/demo/schema/log/

[oracle@rhel63db ~]$ sqlplus hr/hr

 

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 1 13:49:19 2013

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

COUNTRIES                      TABLE

DEPARTMENTS                    TABLE

EMPLOYEES                      TABLE

EMP_DETAILS_VIEW               VIEW

JOBS                           TABLE

JOB_HISTORY                    TABLE

LOCATIONS                      TABLE

REGIONS                        TABLE

 

8 rows selected.

 

Installing the SH Schema

SH schema的内容：

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10771/scripts007.htm

 

安装：

 

SQL> @?/demo/schema/sales_history/sh_main.sql;

 

中间需要输入密码，表空间，目录等，版本我输入了V3（这个版本不是很明白，看到别人是V3）

SQL> conn sh/sh

Connected.

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

CAL_MONTH_SALES_MV             TABLE

CHANNELS                       TABLE

COSTS                          TABLE

COUNTRIES                      TABLE

CUSTOMERS                      TABLE

DIMENSION_EXCEPTIONS           TABLE

DR$SUP_TEXT_IDX$I              TABLE

DR$SUP_TEXT_IDX$K              TABLE

DR$SUP_TEXT_IDX$N              TABLE

DR$SUP_TEXT_IDX$R              TABLE

FWEEK_PSCAT_SALES_MV           TABLE

 

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

PRODUCTS                       TABLE

PROFITS                        VIEW

PROMOTIONS                     TABLE

SALES                          TABLE

SALES_TRANSACTIONS_EXT         TABLE

SUPPLEMENTARY_DEMOGRAPHICS     TABLE

TIMES                          TABLE

 

18 rows selected.

Resetting Sample Schemas

重置sample schema的初始状态。

@?/demo/schema/mksample

 

Uninstalling Sample Schemas

@?/demo/schema/drop_sch.sql

 

 

2.用data pump备份。

用data pump做个简单的备份，在下一个环境使用时，可以避免这么多费时的过程，只需简单导入。

关于data pump可以参考《Oracle® Database Utilities》

 

SQL> create or replace directory dpump_dir as '/data01/apps/oracle/dpdir';

如果想用hr schema操作时

SQL> grant read,write on directory dpump_dir to hr;

 

导出

$ expdp system/system schemas=hr,sh dumpfile=hr_sh.dmp directory=dpump_dir logfile=dp.20130401.log;

 

 

导入：

$ impdp system/system directory=dpump_dir dumpfile=hr_sh.dmp logfile=dp.imp.log