oracle 10g 手動建立scott(tiger) schema

cnhtm發表於2010-02-24

oracle 9i/10g中,如果資料庫例項中沒有scott模式,可以手工建立,方法如下:

登入資料庫

ora_test@oracle[/home/oracle]> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 24 09:21:26 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[@more@]

建立scott使用者

sys@CNHTM> create user scott identified by tiger;

User created.

為scott使用者分配許可權

sys@CNHTM> grant connect,resource to scott;

Grant succeeded.

用scott使用者登入

sys@CNHTM> conn scott/tiger
Connected.

執行demobld.sql指令碼

  • 在oracle 9i中,demobld.sql指令碼位於 /sqlplus/demo 目錄中
  • 在oracle10g中,這個指令碼在單獨的光碟"Oracle Database 10g Companion CD Release 2"中,可以從otn下載安裝。或者手工建立這個檔案,本文最後有這個檔案的內容
    scott@CNHTM> @?/sqlplus/demo/demobld.sql
    Building demonstration tables. Please wait.
    Demonstration table build is complete.
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options

    附件:demobld.sql 指令碼內容

    ----------------------demobld.sql begin-----------------------
    --
    -- Copyright (c) Oracle Corporation 1988, 2000. All Rights Reserved.
    --
    -- NAME
    -- demobld.sql
    --
    -- DESCRIPTION
    -- This script. creates the SQL*Plus demonstration tables in the
    -- current schema. It should be STARTed by each user wishing to
    -- access the tables. To remove the tables use the demodrop.sql
    -- script.
    --
    -- USAGE
    -- From within SQL*Plus, enter:
    -- START demobld.sql

    SET TERMOUT ON
    PROMPT Building demonstration tables. Please wait.
    SET TERMOUT OFF

    DROP TABLE EMP;
    DROP TABLE DEPT;
    DROP TABLE BONUS;
    DROP TABLE SALGRADE;
    DROP TABLE DUMMY;

    CREATE TABLE EMP
    (EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7, 2),
    COMM NUMBER(7, 2),
    DEPTNO NUMBER(2));

    INSERT INTO EMP VALUES
    (7369, 'SMITH', 'CLERK', 7902,
    TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
    INSERT INTO EMP VALUES
    (7499, 'ALLEN', 'SALESMAN', 7698,
    TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
    INSERT INTO EMP VALUES
    (7521, 'WARD', 'SALESMAN', 7698,
    TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
    INSERT INTO EMP VALUES
    (7566, 'JONES', 'MANAGER', 7839,
    TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
    INSERT INTO EMP VALUES
    (7654, 'MARTIN', 'SALESMAN', 7698,
    TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
    INSERT INTO EMP VALUES
    (7698, 'BLAKE', 'MANAGER', 7839,
    TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
    INSERT INTO EMP VALUES
    (7782, 'CLARK', 'MANAGER', 7839,
    TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
    INSERT INTO EMP VALUES
    (7788, 'SCOTT', 'ANALYST', 7566,
    TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
    INSERT INTO EMP VALUES
    (7839, 'KING', 'PRESIDENT', NULL,
    TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
    INSERT INTO EMP VALUES
    (7844, 'TURNER', 'SALESMAN', 7698,
    TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
    INSERT INTO EMP VALUES
    (7876, 'ADAMS', 'CLERK', 7788,
    TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
    INSERT INTO EMP VALUES
    (7900, 'JAMES', 'CLERK', 7698,
    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
    INSERT INTO EMP VALUES
    (7902, 'FORD', 'ANALYST', 7566,
    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
    INSERT INTO EMP VALUES
    (7934, 'MILLER', 'CLERK', 7782,
    TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

    CREATE TABLE DEPT
    (DEPTNO NUMBER(2),
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13) );

    INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
    INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
    INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
    INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

    CREATE TABLE BONUS
    (ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    SAL NUMBER,
    COMM NUMBER);

    CREATE TABLE SALGRADE
    (GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER);

    INSERT INTO SALGRADE VALUES (1, 700, 1200);
    INSERT INTO SALGRADE VALUES (2, 1201, 1400);
    INSERT INTO SALGRADE VALUES (3, 1401, 2000);
    INSERT INTO SALGRADE VALUES (4, 2001, 3000);
    INSERT INTO SALGRADE VALUES (5, 3001, 9999);

    CREATE TABLE DUMMY
    (DUMMY NUMBER);

    INSERT INTO DUMMY VALUES (0);

    COMMIT;

    SET TERMOUT ON
    PROMPT Demonstration table build is complete.

    EXIT
    -------------------demobld.sql end-----------------------

    --end--

  • 來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22049049/viewspace-1031399/,如需轉載,請註明出處,否則將追究法律責任。

    相關文章