2012-04-24 11:50 from—http://blog.csdn.net/wuweilong/article/details/7493056

                                                                                       添加SCOTT实例步骤

由于近期有培训需要,想起了SCOTT示例能够在后期的这次培训中带来不小的帮助,就果断的想起了添加SCOTT这个示例,添加示例步骤如下:

1、用sys身份登录数据库
[Oracle@wwl admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Apr 24 09:27:41 2012
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

SQL>

2、执行@ORACLE_HOME/rdbms/admin/scott.sql 脚本。
SQL> @ORACLE_HOME/rdbms/admin/scott.sql
SP2-0310: unable to open file “ORACLE_HOME/rdbms/admin/scott.sql”
SQL> @/oracle/orahome/10.2.0/db_1/rdbms/admin/scott.sql
SP2-0310: unable to open file “/oracle/orahome/10.2.0/db_1/rdbms/admin/scott.sql”
SQL>

[[email protected] admin]$ cd $ORACLE_HOME/rdbms/admin
[[email protected] admin]$ ll sco*
ls: sco*: No such file or directory
[[email protected] admin]$

居然没有这个文件,那要怎么添加SCOTT实例呢??????????一头的问号。。。。

后来进过询问朋友发现还有另外一个脚本utlsamp1.sql执行执行也可以达到添加scott的功能

3、执行utlsampl.sql 脚本:

SQL> @/oracle/orahome/10.2.0/db_1/rdbms/admin/utlsampl.sql
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
—————- ————
WWL              OPEN

也出问题了显示为Disconnected状态,但是我数据库是open的状态呀,为什么会这样,难道是这个文件有问题?????????

4、先看看吧,果然是,没有可执行权限

[[email protected] admin]$ ll utlsampl.sql
-rw-r—– 1 oracle oinstall 3628 Apr 27  2001 utlsampl.sql

5、赋予可执行权限
[[email protected] admin]$ chmod +x utlsampl.sql
[[email protected] admin]$ ll utlsampl.sql
-rwxr-x–x 1 oracle oinstall 3628 Apr 27  2001 utlsampl.sql

6、执行utlsampl.sql脚本后,结果也一样,自动退出了。

SQL> @/oracle/orahome/10.2.0/db_1/rdbms/admin/utlsampl.sql
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

7、但是通过查看dba_user视图,用户已经解锁了,也能正常登录了
SQL> select username,account_status from dba_users where username=’SCOTT’;

USERNAME             ACCOUNT_STATUS
——————– ——————————–
SCOTT                OPEN

SQL> conn scott/tiger;
Connected.

8、查询数据也没有问题
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE

SQL> select * from dept;

DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL>

说明,我的执行是成功的,具体什么远导致没有看到执行过程,可能是脚本返回的问题吧,不管了,已经成功就好。