文章內(nèi)容

Oracle數(shù)據(jù)庫增加樣例數(shù)據(jù) scott用戶與相關(guān)的表

發(fā)布時間: 2012/8/26 15:36:53

-

如果想用scott用戶做試驗和練習(xí)，但，庫中沒有怎么辦？

1.運行 RDBMS\ADMIN\utlsampl.sql腳本

2.查看是否成功

示例：

1.SQL> @D:\Oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlsampl
2.從 Oracle Database 10g Release 10.2.0.1.0 - Production 斷開 3.
4.C:\Users\Administrator>sqlplus / as sysdba 5.
6.SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 7月 30 11:55:21 2011 7.
8.Copyright (c) 1982, 2005, Oracle. All rights reserved. 9.
10.
11.連接到:
12.Oracle Database 10g Release 10.2.0.1.0 - Production 13.
14.SQL> select * from dba_users where username='SCOTT'; PS:貼上創(chuàng)建scott相關(guān)表，數(shù)據(jù)，權(quán)限等信息

1.Rem Copyright (c) 1990, 1996, 1997, 1999, 2001 by Oracle Corporation
2.Rem NAME 3.REM    UTLSAMPL.SQL
4.Rem FUNCTION 5.Rem NOTES
6.Rem MODIFIED
7.Rem     menash     02/21/01 - remove unnecessary users for security reasons 8.Rem     gwood      03/23/99 - make all dates Y2K compliant 9.Rem     jbellemo   02/27/97 - dont connect as system 10.Rem     akolk      08/06/96 - bug 368261: Adding date formats 11.Rem     glumpkin   10/21/92 - Renamed from SQLBLD.SQL   12.Rem     blinden   07/27/92 - Added primary and foreign keys to EMP and DEPT 13.Rem     rlim       04/29/91 -         change char to varchar2   14.Rem     mmoore     04/08/91 -         use unlimited tablespace priv
15.Rem     pritto     04/04/91 -         change SYSDATE to 13-JUL-87   16.Rem   Mendels    12/07/90 - bug 30123;add to_date calls so language independent 17.Rem
18.rem
19.rem $Header: utlsampl.sql 21-feb-01.18:15:30 menash Exp $ sqlbld.sql
20.rem
21.SET TERMOUT OFF 22.SET ECHO OFF 23.
24.rem CONGDON    Invoked in RDBMS at build time.   29-DEC-1988 25.rem OATES:     Created: 16-Feb-83
26.
27.DROP USER SCOTT CASCADE; 28.DROP USER ADAMS CASCADE; 29.DROP USER JONES CASCADE; 30.DROP USER CLARK CASCADE; 31.DROP USER BLAKE CASCADE; 32.GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER; 33.DROP PUBLIC SYNONYM PARTS; 34.
35.CONNECT SCOTT/TIGER 36.CREATE TABLE DEPT 37.       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, 38.    DNAME VARCHAR2(14) ,
39.    LOC VARCHAR2(13) ) ;
40.CREATE TABLE EMP 41.       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, 42.    ENAME VARCHAR2(10),
43.    JOB VARCHAR2(9),
44.    MGR NUMBER(4),
45.    HIREDATE DATE, 46.    SAL NUMBER(7,2),
47.    COMM NUMBER(7,2),
48.    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT); 49.INSERT INTO DEPT VALUES 50.    (10,'ACCOUNTING','NEW YORK'); 51.INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); 52.INSERT INTO DEPT VALUES 53.    (30,'SALES','CHICAGO'); 54.INSERT INTO DEPT VALUES 55.    (40,'OPERATIONS','BOSTON'); 56.INSERT INTO EMP VALUES 57.(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); 58.INSERT INTO EMP VALUES 59.(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); 60.INSERT INTO EMP VALUES 61.(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); 62.INSERT INTO EMP VALUES 63.(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); 64.INSERT INTO EMP VALUES 65.(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); 66.INSERT INTO EMP VALUES 67.(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); 68.INSERT INTO EMP VALUES 69.(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); 70.INSERT INTO EMP VALUES 71.(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); 72.INSERT INTO EMP VALUES 73.(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); 74.INSERT INTO EMP VALUES 75.(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); 76.INSERT INTO EMP VALUES 77.(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); 78.INSERT INTO EMP VALUES 79.(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); 80.INSERT INTO EMP VALUES 81.(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); 82.INSERT INTO EMP VALUES 83.(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); 84.CREATE TABLE BONUS 85.    (
86.    ENAME VARCHAR2(10) ,
87.    JOB VARCHAR2(9) ,
88.    SAL NUMBER,
89.    COMM NUMBER
90.    ) ;
91.CREATE TABLE SALGRADE 92.      ( GRADE NUMBER,
93.    LOSAL NUMBER,
94.    HISAL NUMBER );
95.INSERT INTO SALGRADE VALUES (1,700,1200); 96.INSERT INTO SALGRADE VALUES (2,1201,1400); 97.INSERT INTO SALGRADE VALUES (3,1401,2000); 98.INSERT INTO SALGRADE VALUES (4,2001,3000); 99.INSERT INTO SALGRADE VALUES (5,3001,9999); 100.COMMIT; 101.EXIT