Oracle10g利用utlsampl.sql創(chuàng)建scott用戶及樣本數(shù)據(jù)

1、我們知道，很多的演示程序都是以scott用戶及其用戶下的表做例子的，于是，快速的創(chuàng)建這個(gè)用戶和初始化表中的數(shù)據(jù)是必須的。在Oracle 10g環(huán)境中這個(gè)過(guò)程很簡(jiǎn)便，只需要sys 用戶執(zhí)行一下 $ORACLE_HOME/rdbms/admin/utlsampl.sql 腳本就 OK了。F面來(lái)演示一下這個(gè)過(guò)程。以便大家參考。scott 用戶。1.1.確認(rèn)一下數(shù)據(jù)庫(kù)的版本，同時(shí)查看一下系統(tǒng)中是否已經(jīng)存在sysora10g > select * from v$version;2.3.4.BANNEROracle Database 10g Enterprise Edition Release 10

2、. - 64bi5.6.P L/SQL Release .0 - P roductionCORE .0P roduction0.TNS for Linux: Version .0 - P roductionNLSRTL Version .0 - P roductionsysora10g >select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLES PACE,TE MP ORARY_TABLES PACE,CREATED from dba_users whereno

3、rows selected2.創(chuàng)建腳本路徑 $ORACLE_HOME/rdbms/admin/utlsampl.sqlUSERNAME= 'SCOTT'1.sysora10g > ?/rdbms/admin/utlsa mp l.sql2.Table created.3.Table created.4.1 row created.5.1 row created.6.1 row created.7.1 row created.8.1 row created.9.1 row created.10.1 row created.11.1 row created.12.1 row

4、created.13.1 row created.14.1 row created.15.1 row created.16.1 row created.17.1 row created.18.1 row created.19.1 row created.20.1 row created.21.1 row created.22.Table created.23.Table created.24.1 row created.25.1 row created.26.1 row created.27.1 row created.28.1 row created.29.Commit compi ete.

5、30.Disconnected from Oracle Database 10g Enter prise Edition Release .0 - P roduction31.With the P artitioning, Data Mining and Real App lication Testing op tions32.ora10glinux5 /home/oracle$3.驗(yàn)證用戶是否創(chuàng)建成功1.sysora10g >select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLES PACE,TE MP ORARY_TABLES PAC

6、E,CREATED from dba_users whereUSERNAME= 'SCOTT'2.USERNAME ACCOUNT STATUS DEFAULT TABLES PACE TEMP ORARY TABLES PACE CREATED3.4.SCOTT OPENUSERSTEMP05-MAR-095.sysora10g >conn scott/tiger6.Connected.7.scottora10g >8.scottora10g >select * from cat;9.TABLE NAMETABLE TYPE10.11.DEPTTABLE12

7、.EMPTABLE13.BONUSTABLE14.SALGRADETABLE4.【附錄】utlsam pl.sql腳本內(nèi)容1.$ cat $ORACLE_HOME/rdbms/admin/utlsa mp l.sql2.Rem Cop yright (c) 1990, 1996, 1997, 1999, 2001 by Oracle Corporation3.Rem NAME4.REMUTLSAM PL.SQL5.RemFUNCTION6.RemNOTES7.RemMODIFIED8.Remmenash02/21/01 - remove unnecessary users for securi

8、ty reasons9.Remgwood03/23/99 - make all dates Y2K comp liant10.Remjbellemo02/27/97 - dont connect as system11.Remakolk 08/06/96 - bug 368261: Adding date formats12.Remglum pkin 10/21/92 - Renamed from SQLBLD.SQL13.Remblinden 07/27/92 - Added primary and foreign keys to EMP and DEPT14.Remrlim 04/29/9

9、1 -change char to varchar215.Remmmoore 04/08/91 -use unlimited tables pace priv16.Rempritto 04/04/91 -change SYSDATE to 13-JUL-8717.RemMendels 12/07/90 - bug 30123;add to_date calls so language inde pendent18.Rem19.rem20.21.22.SET TERMOUT OFF23.SET ECHO OFFrem $Header: utlsa mp l.sql 21-feb-01.18:15

10、:30 menash Exp $ sqlbld.sql rem7.DROP USER SCOTT CASCADE;DROP USER ADAMS CASCADE;28.DROP USER JONES CASCADE;29.DROP USER CLARK CASCADE;30.DROP USER BLAKE CASCADE;rem CONGDON Invoked in RDBMS at build time. 29-DEC-1988 rem OATES: Created: 16-Feb-8331.32.33.CONNECT SCOTT/TIGER34.CREATE TABLE

11、 DEPTGRANT CONNECT,RESOURCE,UN LIMITED TABLES PACE TO SCOTT IDENTIFIED BY TIGER;DROP P UBLIC SYNONYM P ARTS;35.36.37.DNAME VARCHAR2(14),LOC VARCHAR2(13);38.CREATE TABLE EMP(DEP TNO NUMBER(2) CONSTRAINT P K_DE PT P RIMARY KEY,39.40.ENAME VARCHAR2(10),41.JOB VARCHAR2(9),42.MGR NUMBER(4),43.HIREDATE DA

12、TE,44.SAL NUMBER(7,2),45.COMM NUMBER(7,2),(EMPNO NUMBERS) CONSTRAINT PK_EMP P RIMARY KEY,46.47.INSERT INTO DEPT VALUESDEP TNO NUMBER(2) CONSTRAINT FK_DE PTNO REFERENCES DEP T);48.49.50.INSERT INTO DEPT VALUES51.(30,'SALES','CHICAGO');52.INSERT INTO DEPT VALUES53.54.(40,' OP ERATI

13、ONS','BOSTON');INSERT INTO EMP VALUES(10,'ACCOUNTING','NEW YORK');INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');55.56.INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);57.58.

14、INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);59.60.INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);61.62.INSERT INTO EMP VALUES(7566

15、,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);63.(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);7.68.69.INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',783

16、9,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

17、-rr')-85,3000,NULL,20);70.71.72.INSERT INTO EMP VALUES(7839,'KING',' PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);INSERT INTO EMP VALUES9.(7844,'TURNER','SALESMAN',7698,to_date(' 8-9-1981','dd-mm-y

18、yyy'),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-1