oracle知识点整理,oracle12c知识点学习与整理

********************监听********************

lsnrctl

start/stop/status

********************CDB/PDB********************

sqlplus / as

sysdba

startup

shutdown

immediate

show

con_name

show pdbs

alter pluggable

database all open;

alter pluggable

database pdbjcdb close immediate;

alter session set

container=pdbjcdb;

********************导入导出********************

alter session set

container=pdbjcdb;

CREATE DIRECTORY

DataPump_Dir AS '/Backup/jcdb/DataPump/pdbjcdb';

col OWNER for

a8;

col DIRECTORY_NAME for

a15;

col DIRECTORY_PATH for

a40;

col ORIGIN_CON_ID for

9999;

SELECT * FROM

dba_directories;

grant read, write on

directory datapump_dir to system;

expdp

system/123456@pdbjcdb full=y logtime=all DIRECTORY=DATAPUMP_DIR

JOB_NAME=expdp_pdbjcdb_p2_job DUMPFILE=parallel_pdbjcdb_%U.dmp

LOGFILE=parallel_pdbjcdb_expdp.log PARALLEL=2

impdp

system/123456@pdbjcdb SCHEMAS=zhouzzz DIRECTORY=DataPump_Dir

JOB_NAME=impdp_pdbjcdb_p2_job DUMPFILE=parallel_pdbjcdb_%U.dmp

logtime=all LOGFILE=parallel_pdbjcdb_impdp.log

PARALLEL=2

********************SQL********************

show parameter

parameter_name

select username from

all_users/dba_users;

1.启动、关闭数据库

$ lsnrctl

start

sqlplus /

as sysdba

SYS@jcdb>

startup

SYS@jcdb>

shutdown immediate

2.启动PDB

SYS@jcdb>

alter pluggable database all

open;

alter

pluggable database pdbjcdb open;

alter

pluggable database pdbjcdb close immediate;

查看

show con_name

CON_NAME

------------------------------

CDB$ROOT

alter session

set container=pdbjcdb;

show pdbs;

CON_ID

CON_NAME  OPEN MODE RESTRICTED

---------- ------------------------------

---------- ----------

2

PDB$SEED  READ ONLY NO

3

PDBJCDB  READ WRITE NO

3.从模板创建PDB

SYS@jcdb> create pluggable database "PDB123" admin user

"PDBADMIN" identified by 123456;

SYS@jcdb> alter pluggable database PDB123 open read

write;

SYS@jcdb> alter session set container=pdb123;

SYS@jcdb> show pdbs

CON_ID CON_NAME  OPEN

MODE RESTRICTED

----------

------------------------------ ---------- ----------

2 PDB$SEED  READ ONLY NO

3 PDBJCDB  READ WRITE NO

4 PDB123  READ WRITE NO

删除PDB(从CDB$ROOT session下删除其他pdb)

SYS@jcdb> alter pluggable database pdb123 close

immediate;

SYS@jcdb> drop pluggable database pdb123 including

datafiles;

SYS@jcdb> show

pdbs;

CON_ID CON_NAME  OPEN

MODE RESTRICTED

----------

------------------------------ ---------- ----------

2 PDB$SEED  READ ONLY NO

3 PDBJCDB  READ WRITE NO

4.以PDB登录EM报错弹窗

解决方法

查看em

SYS@jcdb> WITH

2 FUNCTION b2vc (in_bool_expr

VARCHAR2)

3 RETURN VARCHAR2

4 IS

5 l_bool BOOLEAN;

6 l_plsql VARCHAR2(32767);

7 l_ret VARCHAR2(5);

8 BEGIN

9 l_plsql :=

'BEGIN :l_bool := ' || in_bool_expr || '; END;';

10 EXECUTE IMMEDIATE l_plsql

USING OUT l_bool;

11 IF l_bool IS NOT NULL

THEN

12 IF l_bool

THEN

13 l_ret := 'TRUE';

14 ELSE

15 l_ret := 'FALSE';

16 END

IF;

17 END IF;

18 RETURN l_ret;

19 END

b2vc;

20 SELECT

b2vc('DBMS_XDB_CONFIG.ISGLOBALPORTENABLED') FROM dual;

21 /

B2VC('DBMS_XDB_CONFIG.ISGLOBALPORTENABLED')

-------------------------------------------------------------------------------------------

FALSE

open pdb再次登录查看变为TRUE，就可以登录了。

5.创建用户报错

创建新用户，CDB容器中创建一个通用用户，用户名必须以C##或者c##开头，因为CDB中默认创建的是common

user。如果想要创建本地用户，则要在PDB容器中创建，切换进PDB

创建用户

··········································································

SYS@jcdb> show

con_name;

CON_NAME

CDB$ROOT

SYS@jcdb> create user

zhouzzz identified by 123456;

create user zhouzzz identified

by 123456

*

ERROR at line 1:

ORA-65096: invalid common user

or role name

SYS@jcdb> create user c##zhouzzz identified by

123456;

User created

···········································································

SYS@jcdb> alter

session set container=pdbjcdb;

Session

altered.

SYS@jcdb> show

con_name;

CON_NAME

PDBJCDB

SYS@jcdb>

create user zhowwww identified by

123456;

User

created.

授权

grant connect,resource to c##zhouzzz

container=all;

[oracle@ora12db ~]$ sqlplus

c##zhouzzz/123456;

C##ZHOUZZZ@jcdb>

select

GRANTEE,con_id from cdb_ROLE_PRIVS where GRANTED_ROLE='CONNECT' AND

GRANTEE='C##ZHOUZZZ';

GRANTEE CON_ID

C##ZHOUZZZ 1

删除用户

drop user c##zhouzzz cascade;