Rebuild SCOTT users and SCOTT stories

Oracle was first learned from the dept and emp tables under scott, and is still used to testing under scott. Sometimes sco needs to be rebuilt to facilitate testing

Oracle was first learned from the dept and emp tables under scott, and is still used to testing under scott. Sometimes sco needs to be rebuilt to facilitate testing

Oracle was first learned from the dept and emp tables under scott, and is still used to testing under scott. Sometimes to facilitate testing, scott users need to be rebuilt to provide a completely new test environment, where the record of the process of rebuilding scott users is recorded.

1. scott user profile
SCOTT is the name of an example user in the Oracle database. Its function is to provide some simple application examples for beginners, but it is locked by default. SCOTT's default password is tiger. The following table is emp and dept. The relationship between these tables demonstrates some basic principles of relational databases. Oracle generally uses this user as an example, this user is generally used in Oracle books and textbooks. It is not necessary for Oracle itself. If you do not want to use it, you can delete it (if you do not create other objects under it ).

2. Prepare the environment
We tested Oracle 10 Gb to delete scott users.

Click (here) to fold or open

C: \ Users \ Administrator> sqlplus sys/hoegh as sysdba

SQL * Plus: Release 10.2.0.4.0-Production on Wednesday May 13 10:24:10 2015

Copyright (c) 1982,200 7, Oracle. All Rights Reserved.

Connect:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL>

SQL> select * from v $ version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bi

PL/SQL Release 10.2.0.4.0-Production

CORE 10.2.0.4.0 Production

TNS for 64-bit Windows: Version 10.2.0.4.0-Production

NLSRTL Version 10.2.0.4.0-Production

SQL>

SQL> drop user scott cascade;

The user has been deleted.

SQL>

3. Rebuilding scott users
Oracle provides the scott user's reconstruction script, which is located in the ORACLE_HOME \ RDBMS \ ADMIN directory and named utlsampl. SQL. Run the script in sys as follows:

SQL>

SQL> show user

The USER is \ "SYS \"

SQL>

SQL> @ C: \ oracle \ product \ 10.2.0 \ db_1 \ RDBMS \ ADMIN \ utlsampl. SQL

From Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options disconnected

Open

C: \ Users \ Administrator>
After the script is executed, the system automatically releases SQL * plus. Next, we connect to the scott user and check whether the script is successfully executed.

C: \ Users \ Administrator> sqlplus scott/tiger

SQL * Plus: Release 10.2.0.4.0-Production on Wednesday May 13 10:34:29 2015

Copyright (c) 1982,200 7, Oracle. All Rights Reserved.

Connect:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from cat;

TABLE_NAME TABLE_TYPE

-----------------------------------------

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>

SQL>

4. utlsampl. SQL script content
There are many scripts in the ORACLE_HOME \ RDBMS \ ADMIN directory. utlsampl. SQL is only one of them. If you are interested in these scripts, you will also find some benefits. Here, let's take a look at the utlsampl. SQL script content.

Rem Copyright (c) 1990,199 6, 1997,199 9, 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 CILS 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, 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, 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 \'), 0, 30 );
INSERT INTO EMP VALUES
(7876, \ 'adams \ ', \ 'clerk \', 7788, to_date (\ '13-JUL-87 \ ', \ 'dd-mm-rr, 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,120 0 );
Insert into salgrade values (1400 );
Insert into salgrade values (2000 );
Insert into salgrade values (4,2001, 3000 );
Insert into salgrade values (5, 3001, 9999 );
COMMIT;
EXIT
The script is mainly divided into several parts,
The first part is the remarks Section, with the Rem keyword at the beginning of the line, describing the evolution process of utlsampl. SQL script;
The second part is the drop object. Let's take a look at the yellow-marked statement. That is to say, the drop user statement prepared in the environment is purely superfluous, and Oracle has already thought of it for us;
The third part is to create a table and insert data.

5. Who is scott?

From the first commercial version of Oracle to the present, oracle's default database has all the users whose names are scott and whose passwords are tiger. What exactly does scott come from? How can we keep it rare in our oracle version.