Oracle 12c methods for using ordinary users such as Scott

Source: Internet
Author: User

Directory:

First, preface

Second, the use of ordinary users

Third, automatically start the PDB

First, preface

A recent Oracle 12c database has been installed on your computer to experience the new features. After installation, like the 11g in the DOS window to do the following:

Sql*Plus:release12.1.0.2.0Production onSunday August9  -: .: -  -Copyright (c)1982, the, Oracle. Allrights reserved. Connect to: OracleDatabase12c Enterprise Edition Release12.1.0.2.0 -64bitProduction withThe partitioning, OLAP, Advanced Analytics and RealApplication Testing Optionssql> Alter UserScott account unlock identified byTiger;Alter UserScott account unlock identified byTiger*Section1line error: ORA-01918: User'SCOTT'does not exist

God horse situation! Isn't there a Scott user? The SQL looked up, did not find (not NO):

Sql> Select username from dba_users where username like ' SCOTT '; rows not selected

Oh, then I'll build it myself (I thought more o.o):

Sql> create user Scott identified by Tiger;create user Scott identified by Tiger            * 1th error: ORA-65096: Invalid public user name or role name

。。。。。

Oh, just touch 12c novice estimates will make such a mistake. In fact, the Oracle 12c has been structurally adjusted to introduce the CDB and PDB concepts. Specific can look at the article of Xiaoxiang hermit:

Http://www.cnblogs.com/kerrycode/p/3386917.html

In fact, I just the operation is in the CDB operation, with sys default login is CDB, but if you want to create a user in the CDB (can be understood as a public user), then you must precede the user name with "c##":

sql> Create User C # #joker identified by Joker;

Oracle does this to differentiate between CDB users (users with "c##" in front of them) and PDB users. So the question is, how do you use a regular user? Online access to a lot of information, but are not clear, today summed up it.

Second, the use of ordinary users

In fact, the normal user of Oracle 12c must be used under the PDB, the PDB can be created by themselves, or can be used with 12c. First, check out the PDB that comes with the 12c:

SQL>  Select from V$pdbs;    con_id       DBID GUID                             NAME                           open_mode------------------------------------------------- -------------------------------------------4071321146 e89e8da2866e3157e043de07a8c09238 pdb$seed                       READonly1930201447  e89e9418b882350ce043de07a8c092b6 pdborcl                        Mountedsql>

To link to PDBORCL, you must add the following to the TNS file:

PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = PDBORCL)
)
)

In this way, we can link the PDBORCL.

First, to start PDBORCL, first log in to CDB with SYS, do the following:

SQL>alterset=  PDBORCL; The session has changed. SQL> STARTUP

So we can log in to PDBORCL with SYS:

SQL> conn sys@pdborcl/admin as    sysdba connected. 

Let's check it out. With no Scott users:

SQL>Selectfromwhereto like'%scott%' ; USERNAME--------------------------------------------------------------------  SCOTT

It turns out that Scott is under the PDBORCL.

Sql> Alter UserScott account unlock identified byTiger; The user has changed. SQL>Conn Scott@pdborcl/Tiger is connected. SQL> SelectEname,empno,job fromEMP Offset5RowsFetch Next 5Rows only; ename EMPNO JOB---------- ---------- ---------BLAKE7698Managerclark7782Managerscott7788analystking7839Presidentturner7844Salesman

This way, we can use a regular user like Scott like 11g!

Third, automatically start the PDB

We can start the PDB automatically by creating a trigger, without having to log in to CDB with SYS each time, and then start the PDB. The trigger code is as follows:

CREATE OR REPLACE TRIGGER Open_all_pdbs   After STARTUP   onDATABASEBEGIN   EXECUTE'alter Pluggable database all open'; END Open_all_pdbs;

Let's experiment:

Sql> CREATE OR REPLACE TRIGGEROpen_all_pdbs2After STARTUP3      on DATABASE  4  BEGIN  5     EXECUTEIMMEDIATE'Alter pluggable database all open'; 6  ENDOpen_all_pdbs; 7  /Trigger has created SQL> shutdownThe immediate database is closed. The database has been uninstalled. The ORACLE routine has been closed. SQL>The startuporacle routine has been started. Total System Global Area1660944384bytesfixed Size3046320bytesvariable Size989856848bytesDatabaseBuffers654311424Bytesredo Buffers13729792The bytes database is loaded. The database is already open. SQL>Conn Scott@pdborcl/Tiger is connected. 

Summary:

To imagine 11g as a normal user, you have to use it under the PDB.

Reference documents:

Http://www.cnblogs.com/kerrycode/p/3386917.html

http://blog.csdn.net/yuguanquan1990/article/details/17495331

Http://www.askmaclean.com/archives/autostart-pdb-pluggable-database.html

http://blog.csdn.net/liou825/article/details/10054589

Oracle 12c methods for using ordinary users such as Scott

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.