ORACLE11GR2 the creation of Scott users and tables

Directory

• ORACLE11GR2 the creation of Scott users and tables
  • Find the system-brought SQL file (Utlsample.sql)
  • Operation based on SQL content
    • Create a new user and authorize
    • Scott Login
    • Table Operations
    • query table (using PL/SQL)
• Questions

ORACLE11GR2 the creation of Scott users and tables

After the Oralce database installation is complete, how do you build users and system-supplied tables without Scott users and practice sheets?

Operation Steps :

Find the system-brought SQL file (Utlsample.sql)

[[email protected] admin]# pwd/oracle/app/oracle/product/11.2.0/rdbms/admin[[email protected] admin]# ll utlsampl.sql -rw-r--r--. 1 oracle oinstall 3676 6月   2 2006 utlsampl.sql

Create a new user and authorize based on SQL content operations

-- 删除系统带的用户DROP USER SCOTT CASCADE;DROP USER ADAMS CASCADE;DROP USER JONES CASCADE;DROP USER CLARK CASCADE;DROP USER BLAKE CASCADE;<!-- more -->-- 授权scott相应的权限GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;DROP PUBLIC SYNONYM PARTS;

Scott Login

[[email protected] admin]$ sqlplus ‘/as sysdba‘SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 15 11:31:17 2018Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> conn scott/tiger;Connected.

Table Operations

CREATE TABLE DEPT (DEPTNO number (2) CONSTRAINT pk_dept PRIMARY KEY, dname VARCHAR2 (+), LOC VARCHAR2 (); C reate TABLE EMP (EMPNO number (4) CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2 (Ten), JOB VARCHAR2 (9), MGR NU Mber (4), HireDate DATE, SAL number (7,2), COMM number (7,2), DEPTNO number (2) CONSTRAINT Fk_deptno REFERENCES DE PT); insert into DEPT values (' ACCOUNTING ', ' NEW YORK '), insert into DEPT values ([], ' ' ", ' DALLAS '); INSERT INTO D EPT values (' SALES ', ' CHICAGO '), insert into DEPT values (+, ' OPERATIONS ', ' BOSTON '), insert into EMP values (7369, ' SM ITH ', ' 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); Nsert 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); INS ERT 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;

query table (using PL/SQL)

Questions

The above method may not be the optimal method, equivalent to the Utlsample.sql manual execution.
Most of the information on the web is carried out in this way:

[[email protected] admin]$ sqlplus ‘/as sysdba‘SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 15 11:46:27 2018Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> @/oracle/app/oracle/product/11.2.0/rdbms/admin/utlsample.sqlSP2-0310: unable to open file "/oracle/app/oracle/product/11.2.0/rdbms/admin/utlsample.sql"

In fact, the command operation error, the correct operation is as follows:

[[email protected] admin]$ sqlplus '/as sysdba ' sql*plus:release 11.2.0.4.0 Production on Fri June 11:46:27 2018Cop  Yright (c) 1982, Oracle. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith the partitioning, Automatic Storage Management, OLAP, Data miningand Real application Testing optionssql>start/oracle/app/oracle/product/11.2.0 /rdbms/admin/utlsampl.sqltable 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 Crea Ted.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 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith the partitioning, Autom Atic Storage Management, OLAP, Data MininganD Real Application Testing options 

Positive Solution!

ORACLE11GR2 the creation of Scott users and tables