SQL学习环境配置

 

安装oracle软件，创建数据库，执行以下sql脚本创建学习账户和表结构

 

[oracle@server2 labs]$ more creteach.sql
REM Script: CRETEACH.SQL
REM Purpose: To create a "teach" account for instructor
REM Created: By Nagavalli Pataballa on 16-MAR-2001
REM for the Introduction to Oracle9i:SQL course
REM This script is invoked by the CRESCHEM.SQL script

CONNECT / as sysdba

DROP USER teach CASCADE;
CREATE USER teach IDENTIFIED BY oracle;
ALTER USER teach DEFAULT TABLESPACE users
      QUOTA UNLIMITED ON users;
ALTER USER teach TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE
    , CREATE SEQUENCE, CREATE TRIGGER, CREATE VIEW
    , CREATE SYNONYM, ALTER SESSION, CREATE ANY INDEX
    , CREATE PUBLIC SYNONYM, CREATE USER, CREATE ROLE
TO teach;

REM connect to teach account and invoke the scripts that create schema objects.

CONNECT teach/oracle
@@hr_cre
@@hr_popul
@@hr_idx
@@hr_code
@@hr_comnt
@@del_data

CONNECT / as sysdba
GRANT CREATE ANY DIRECTORY, QUERY REWRITE, DROP PUBLIC SYNONYM TO teach;

REM verify the number of rows created for each table
select count(*) NUM_EMP from employees;
select count(*) NUM_DEP from departments;
select count(*) NUM_LOC from locations;
select count(*) NUM_REG from regions;
select count(*) NUM_CTR from countries;
select count(*) NUM_JOB from jobs;
select count(*) NUM_JH from job_history;

进入sqlplus执行以上脚本：

[oracle@server2 labs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 29 08:57:06 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/home/oracle/lanv/9isql/labs/creteach.sql