Shulou(Shulou.com)06/01 Report--

Previously installed version 12c, I recently did some exercises to find out why there are no tables for scott users, 11g's own emp,dept, and so on. So I tried to unlock the user and found that the user did not exist.

SQL > alter user scott account unlock;alter user scott account unlock * ERROR at line 1:ORA-01918: user 'SCOTT' does not exist

Manual user creation, error, different from 11g

SQL > create user scott identified by oracle;create user scott identified by oracle * ERROR at line 1:ORA-65096: invalid common user or role name

The reason is related to pdb,cdb. Pdb and cdb are new features of 12c. I haven't learned it in detail. I don't know what it means, but you can refer to this article: http://www.cnblogs.com/tzetze/p/3665903.html.

View error ORA--65096

SQL >! oerr ora 6509665096, 00000, "invalid common user or role name" / / * Cause: An attempt was made to create a common user or role with a name// that wass not valid for common users or roles. In addition to / / the usual rules for user and role names, common user and role / / names must start with clients # or clients # and consist only of ASCII / / characters.// * Action: Specify a valid common user or role name.//

In CDB, users can start with clocked #.

Try @ / u01/app/oracle/product/12.1.0/db_1/rdbms/admin/utlsampl.sql in sql, but it won't work, because there is something wrong with the user name in it.

So I rewrote the script, and the steps are as follows:

Current path

[oracle@localhost ~] $pwd

/ home/oracle

Copy a copy to the current directory

[oracle@localhost] $cp / u01/app/oracle/product/12.1.0/db_1/rdbms/admin/utlsampl.sql.

Rename the file to scott.sql:

[oracle@localhost ~] $mv utlsampl.sql scott.sql

Modify scott.sql:

Change the statements related to scott users to c##scott, and delete the last EXIT. The modified scott.sql is as follows:

Scott.sql

SET TERMOUT OFFSET ECHO OFFDROP USER C##SCOTT CASCADE;create user c##scott identified by tiger;grant connect,resource,unlimited tablespace to c##scott container=all;alter user c##scott default tablespace users;alter user c##scott temporary tablespace temp;CONNECT C##SCOTT/tigerCREATE 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 CONSTRAINT FK_DEPTNO REFERENCES DEPT), COMM NUMBER (7 CONSTRAINT FK_DEPTNO REFERENCES DEPT 2), DEPTNO NUMBER (2) CONSTRAINT FK_DEPTNO REFERENCES DEPT); INSERT INTO DEPT VALUES (10 parcels); INSERT INTO DEPT VALUES (20 paraphernalia); INSERT INTO DEPT VALUES (30 reps) INSERT INTO DEPT VALUES (40 paramedics); INSERT INTO EMP VALUES (7369); INSERT INTO EMP VALUES ('20-2-1981); INSERT INTO EMP VALUES (7499) INSERT INTO EMP VALUES (7521), 766-8-81); INSERT INTO EMP VALUES (766-6), (2-4-1981); INSERT INTO EMP VALUES (7698-768); INSERT INTO EMP VALUES (7654-1981). INSERT INTO EMP VALUES (7698 MANAGERY); INSERT INTO EMP VALUES (7782)); INSERT INTO EMP VALUES (7782). INSERT INTO EMP VALUES (7788); INSERT INTO EMP VALUES (7839); INSERT INTO EMP VALUES ('17-11-1981). INSERT INTO EMP VALUES (7844); INSERT INTO EMP VALUES (7876)); INSERT INTO EMP VALUES (7876); ('13 dd-mm-rr','51) INSERT INTO EMP VALUES (7900 and INSERT INTO EMP VALUES (7902) and INSERT INTO EMP VALUES (7902) and 766 (3-12-1981), respectively. INSERT INTO EMP VALUES (7934 ENAME VARCHAR2 (10), JOB VARCHAR2 (9), SAL NUMBER, COMM NUMBER); CREATE TABLE SALGRADE (GRADE NUMBER, LOSAL NUMBER, HISAL NUMBER); INSERT INTO SALGRADE VALUES (1meme 700,1200); INSERT INTO SALGRADE VALUES (12010,1400); INSERT INTO SALGRADE VALUES INSERT INTO SALGRADE VALUES (4Jing 2001 Jor3000); INSERT INTO SALGRADE VALUES (5Jing 3001J.9999); COMMIT

Test:

Sqlplus / as sysdbaSQL > @ / home/oracle/scott.sql;SQL > conn c##scott/tigerConnected.SQL > select table_name from user_tables;TABLE_NAME----SALGRADEBONUSDEPTEMPSQL > select * from SALGRADE GRADE LOSAL HISAL- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999

Ok!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.