Oracle12c uses scott and other common user's method examples to explain, oracle12cscott

Oracle12c uses scott and other common user's method examples to explain, oracle12cscott

Directory:

I. Preface

Ii. Common users

3. Automatically Start PDB

I. Preface

Recently, the oracle 12c database has been installed on the computer, and you want to experience new features. After the installation is complete, perform the following operations in the dos window like 11 GB:

SQL * Plus: Release12.1.0.2.0Production on Sunday August9 13:06:33 2015Copyright (c)1982,2014, Oracle. All rights reserved. Connect to: Oracle Database 12c Enterprise Edition Release12.1.0.2.0-64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> alter user scott account unlock identified by tiger; alter user scott account unlock identified by tiger *1Line Error: ORA-01918: User 'Scott 'does not exist

Shenma situation! No SCOTT user? I checked it with SQL, but I did not find it (not none ):

1 2 3

SQL> select username from dba_users where username like 'Scott '; row not selected

Oh, let me create one by myself (I thought more about o. o at the time ):

1 2 3 4 5

SQL> create user scott identified by tiger; create user scott identified by tiger * row 1st error: ORA-65096: Invalid public user name or role name

Well, it is estimated that new users who are new to 12c will make such a mistake. In fact, oracle 12c has made structural adjustments and introduced the concepts of CDB and PDB. For details, please refer to the article by Xiaoxiang:

In fact, the operation I just performed is in CDB. By default, sys is used to log on to CDB. However, if you want to create a user (which can be understood as a public user) in CDB, you must add "c #" before the user name ##":

1 2 3

SQL> create user c # joker identified by joker; the user has been created.

Oracle aims to differentiate CDB users (users with "c #" in front) and PDB users. So the question is, how can we use normal users? I have read a lot of information online, but I cannot make it clear. Let's summarize it today.

Ii. Common users

In fact, common users of oracle 12c must use it in PDB. PDB can be created by itself or by using the built-in 12c. First, check the built-in PDB in 12c:

SQL>  select con_id, dbid, guid, name , open_mode from v$pdbs;    CON_ID       DBID GUID                             NAME                           OPEN_MODE---------- ---------- -------------------------------- ------------------------------ ----------4071321146 E89E8DA2866E3157E043DE07A8C09238 PDB$SEED                       READ ONLY1930201447 E89E9418B882350CE043DE07A8C092B6 PDBORCL                        MOUNTEDSQL>

To link to PDBORCL, you must add the following content 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 to PDBORCL.

First, start PDBORCL and log on to CDB with sys to perform the following operations:

SQL> alter session set container = PDBORCL; the session has been changed. SQL> STARTUP

In this way, we can use sys to log on to PDBORCL:

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

We can check whether there are SCOTT users:

SQL> select username from dba_users where username like '%SCOTT%';USERNAME--------------------------------------------------------------------SCOTT

It turns out that SCOTT is under PDBORCL.

SQL> alter user scott account unlock identified by tiger; the user has changed. SQL> conn scott @ pdborcl/tiger is connected. SQL> select ename, empno, job from emp offset5Rows fetch next5Rows only; ename empno job ---------- --------- BLAKE7698MANAGERCLARK7782MANAGERSCOTT7788ANALYSTKING7839PRESIDENTTURNER7844SALESMAN

In this way, we can use normal users like scott like 11 GB!

3. Automatically Start PDB

We can automatically start PDB by creating a trigger. Instead of using sys to log on to CDB every time, we need to start PDB. The trigger code is as follows:

CREATE OR REPLACE TRIGGER open_all_pdbs   AFTER STARTUP   ON DATABASEBEGIN   EXECUTE IMMEDIATE 'alter pluggable database all open';END open_all_pdbs;

Experiment:

SQL> CREATE OR REPLACE TRIGGER open_all_pdbs2AFTER STARTUP3ON DATABASE4BEGIN5Execute immediate 'alter pluggable database all open ';6END open_all_pdbs;7/The SQL> shutdown immediate database has been created for the trigger. The database has been detached. The ORACLE routine has been disabled. The SQL> startupORACLE routine has been started. Total System Global Area1660944384BytesFixed Size3046320BytesVariable Size989856848BytesDatabase Buffers654311424BytesRedo Buffers13729792The bytes database has been loaded. The database has been opened. SQL> conn scott @ pdborcl/tiger is connected.

Summary:

To use a common user like 11 GB, you must use it under PDB.