Oracle 12c Add Scott Users

Reprint: http://www.cnblogs.com/xqzt/p/5034375.html

For those who are familiar with Oracle or who have contacted Oracle, Scott, the user, must be quite familiar with it. 12c has launched a pluggable database with multiple database PDB in the form of multiple tenants in a single container cdb. The PDB does not include the Scott user by default.

To do a data pump experiment, you need a test user, so you need to add a Scott user to the PDB.

Oracle 12c comes with Scott's script, $ORACLE _home/rdbms/admin/utlsampl.sql content as follows:

Rem Copyright (c) 1990, 2006, Oracle.  All rights reserved. Rem Namerem Utlsampl. Sqlrem functionrem notesrem modifiedrem lburgess 04/02/06-lowercase passwords Rem Menash 02/21/01-r   Emove unnecessary users for security Reasonsrem Gwood 03/23/99-make all dates y2k Compliantrem Jbellemo 02/27/97-dont Connect as Systemrem akolk 08/06/96-bug 368261:adding date Formatsrem Glumpkin 10/21/9 2-renamed from Sqlbld. SQL Rem Blinden 07/27/92-added Primary and foreign keys to EMP and Deptrem Rlim 04/29/91-chan         GE 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 Independentremrem Rem  $Header: Utlsampl.sql 02-apr-2006.21:13:01 lburgess Exp $ sqlbld.sql rem SET termout OFFSET ECHO offrem Congdon invoked   In RDBMS at build time. 29-dec-1988rem 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/tigercreate TABLE DEPT (DEPTNO number (2) CONSTRAINT pk_dept PRIMARY KEY, dname VARCHAR2 (+), LO C VARCHAR2 (13)); CREATE TABLE EMP (EMPNO number (4) CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2 (Ten), JOB VARCHAR2 (9), MGR N Umber (4), HireDate DATE, SAL number (7,2), COMM number (7,2), DEPTNO number (2) CONSTRAINT Fk_deptno REFERENCES D EPT); insert into DEPT values (' ACCOUNTING ', ' NEW YORK '), insert into DEPT values ("" "," DALLAS "); INSERT into DEPT values (' SALES ', ' CHICAGO '), insert into DEPT values (+, ' OPERATIONS ', ' BOSTON '), insert into EMP values (7369, ' S MITH ', ' 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 (Ten), 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; EXIT

Use this script to recover Scott in the PDB

1. Add TNSNames for PDB (to be used later in step 2)

[Email protected] admin]$ Pwd/data/app/oracle/product/12.1.0/dbhome_1/network/admin[[email protected] admin]$ cat Tnsnames.ora # Tnsnames.ora Network Configuration File:/data/app/oracle/product/12.1.0/dbhome_1/network/admin/ tnsnames.ora# Generated by Oracle configuration tools. ORCL =  (DESCRIPTION = (    ADDRESS = (PROTOCOL = TCP) (HOST = xqzt) (PORT = 1521))    (Connect_data =      (SERVER = DED icated)      (service_name = ORCL)    )  ) PDBORCL =  (DESCRIPTION = (    ADDRESS = (PROTOCOL = TCP) (HOST = xqzt) (PORT = 1521))    (Connect_data =      (SERVER = dedicated)      (service_name =PDBORCL)    )  )

2, modify the Utlsampl.sql

Change connect Scott/tiger to connect Scott/[email protected]

3, use sys login plsql, switch to the container PDB to be imported , execute utlsampl.sql

Switch to Container PDBORCL

Sql> show PDBs    con_id con_name              OPEN MODE  RESTRICTED------------------------------------------------ ------------     2        pdb$seed                Read Only    NO    3         pdborcl                read WRITE   nosql> alter Session set CONTAINER=PDBORCL;  # #切换到pdbSession altered.

Verify that you are connected to the PDBORCL using SYS

Sql> alter session set CONTAINER=PDBORCL; Session altered. Sql> show Useruser is "SYS" sql> show Con_name; Con_name------------------------------PDBORCL

Execute Utlsampl.sql Script

sql>  @ $ORACLE _home/rdbms/admin/utlsampl.sql    disconnected from ORACLE Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Productionwith The partitioning, OLAP, Advanced Analytics and Real application testing options

4. Validate Import Results

Query whether a Scott user exists

Sql> alter session set CONTAINER=PDBORCL; Session altered. Sql> Select username from dba_users where username = ' SCOTT '; USERNAME--------------------------------------------------------------------------------SCOTT

Log in with Scott to query data:

The problem is that the Oracle SQL Developer tool is connected to Scott and similar errors occur:

Error 1

Error 2

Generally the above two kinds of errors, solutions such as:

Sql> conn Scott/[email protected]                        connected.sql> Select table_name from User_tables; Table_ NAME--------------------------------------------------------------------------------Deptempbonussalgradesql > select * from DEPT;    DEPTNO dname      LOC-------------------------------------    ten ACCOUNTING     NEW YORK      DALLAS    SALES         CHICAGO    OPERATIONS      BOSTON

Oracle 12c Add Scott Users