很多的演示程序都是以scott用户及其用户下的表做例子的,于是,快速的创建这个用户和初始化表中的数据是必须的。在Oracle
10g环境中这个过程很简便,只需要以sys用户执行一下$ORACLE_HOME/rdbms/admin/utlsampl.sql脚本就OK了。
1.确认一下数据库的版本,同时查看一下系统中是否已经存在scott用户
sys@ora10g> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
sys@ora10g>select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED from dba_users where USERNAME = 'SCOTT';
no rows selected
2.创建 脚本路径$ORACLE_HOME/rdbms/admin/utlsampl.sql
sys@ora10g>@?/rdbms/admin/utlsampl.sql
3.验证用户是否创建成功
sys@ora10g>select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED from dba_users where USERNAME = 'SCOTT';
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
-------- -------------- ------------------ -------------------- ---------
SCOTT OPEN USERS TEMP 05-MAR-09
sys@ora10g>conn scott/tiger
Connected.
scott@ora10g>
scott@ora10g>select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
4.创建脚本
Rem Copyright (c) 1990, 1996, 1997, 1999, 2001 by Oracle Corporation
Rem NAME
REM UTLSAMPL.SQL
Rem FUNCTION
Rem NOTES
Rem MODIFIED
Rem menash 02/21/01 - remove unnecessary users for security reasons
Rem gwood 03/23/99 - make all dates Y2K compliant
Rem jbellemo 02/27/97 - dont connect as system
Rem akolk 08/06/96 - bug 368261: Adding date formats
Rem glumpkin 10/21/92 - Renamed from SQLBLD.SQL
Rem blinden 07/27/92 - Added primary and foreign keys to EMP and DEPT
Rem rlim 04/29/91 - change char to varchar2
Rem mmoore 04/08/91 - use unlimited tablespace priv
Rem pritto 04/04/91 - change SYSDATE to 13-JUL-87
Rem Mendels 12/07/90 - bug 30123;add to_date calls so language independent
Rem
rem
rem $Header: utlsampl.sql 21-feb-01.18:15:30 menash Exp $ sqlbld.sql
rem
SET TERMOUT OFF
SET ECHO OFF
rem CONGDON Invoked in RDBMS at build time. 29-DEC-1988
rem OATES: Created: 16-Feb-83
DROP USER SCOTT CASCADE;
DROP USER ADAMS CASCADE;
DROP USER JONES CASCADE;
DROP USER CLARK CASCADE;
DROP USER BLAKE CASCADE;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
DROP PUBLIC SYNONYM PARTS;
CONNECT SCOTT/TIGER
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
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');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
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);
COMMIT;
EXIT
分享到:
相关推荐
oracle scott用户的表
在mysql中创建 oracle scott 用户的四个表及插入初始化数据
mysql中 oracle scott 用户的四个表
万年不变的Oracle Scott练习题,带答案;Oracle简单Scott用户表练习;使用scott/tiger用户下的emp表和dept表完成下列练习
Oracle数据库中的scott用户的表数据,Oracle数据库中的scott用户的表数据
在ORACLE中运行此脚本 如:@+此脚本全路径,就能生成相关scott用户和表,有助于学习oracle;
数据库范例表。用system用户导入。导入后可以登录使用。默认账户scott,密码tiger.
Oracle创建表空间、创建用户并指定表空间、给用户授予权限
以oracle 10g dbca建库的Scott用户为基础,用exp工具导出的DMP文件,需要的可以直接用IMP导入即可。
ORACLE创建用户、创建用户
SCOTT脚本
Oracle scott 用户下的4个表(emp bonus dept salgrade)的建表的SQL语句以及相关的测试数据,提供4个版本Access2007, Access2003, SQL Server2008,Oracle 另外还有一个Summit2是用在Oracle下的有关Sporting Goods...
解决Oracle 11g中scott用户默认被锁定的问题
基本的scott用户练习题,对初学者很有帮助
Oracle创建用户权限Oracle创建用户权限Oracle创建用户权限Oracle创建用户权限
Oracle创建用户语句,基础语句。
Oracle_View视图创建在oracle数据库中已建立两张表rt_issuesect和roadstate,表结构如下: 高架状态表结构rt_issuesect
一个oracle用户,创建多个实例图解。
郝斌老师 数据教程SCOTT资源,郝斌 scoot 资源 sql server oracle scott
Oracle密码文件的创建、使用和维护Oracle密码文件的创建、使用和维护Oracle密码文件的创建、使用和维护Oracle密码文件的创建、使用和维护Oracle密码文件的创建、使用和维护