Oracle 10 Gb uses utlsampl. SQL to create scott users and sample data

Oracle 10gExploitationUtlsampl. SQLCreateScott userAnd sample data are the main content we will introduce in this article. We know that many demo programs use the tables of scott users and their users as examples. Therefore, quick creation of this user and data in the initialization table are required. In the Oracle 10 Gb environment, this process is very simple. You only need to run the $ ORACLE_HOME/rdbms/admin/utlsampl. SQL script as a sys user.

The following describes the process. For your reference.

1. Check the database version and whether the scott user already exists in the system.

 
 

  
  
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. Create the script path $ ORACLE_HOME/rdbms/admin/utlsampl. SQL

 
 

  
  
sys@ora10g>@?/rdbms/admin/utlsampl.sql  
  
  
Table created.  
  
  
Table created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
Table created.  
  
  
Table created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
1 row created.  
  
  
Commit complete.  
  
  
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production  
  
  
With the Partitioning, Data Mining and Real Application Testing options  
  
  
ora10g@linux5 /home/oracle$ 
 
 

3. Verify that the user is successfully created

 
 

  
  
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. Appendix: utlsampl. SQL script content

 
 

  
  
$ cat $ORACLE_HOME/rdbms/admin/utlsampl.sql  
  
  
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; 
 
 

We will introduce the process of creating scott users and sample data using the system script utlsampl. SQL in Oracle 10 Gb database. We hope this introduction will be helpful to you!