oracle 10g 手动创建scott(tiger) schema

转自：http://cnhtm.itpub.net/post/39970/496967

为了学习数据库，今天装上了Oracle 10g,装了以后发现没有scott账户，并不像别的文章里说的scott账户已锁定，而是完全没有，搜了很多才明白自己在安装的时候没有选择安装演示程序，所以没有scott账户。可以按下面操作手动创建SCOTT。

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

创建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脚本位于 <ORACLE_HOME>/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--

提示：

上面操作比较麻烦，在执行上面操作前，你可以使用database configuration assistant创建一个一般用途的数据库，可能里头包含了scott账户和相关数据，创建定制数据库可能有取消创建scott和相关数据的选项。我没亲测，如果大家测试如此，请留言告知。

neworacle