• 首页
  • 狐文
  • 狐图
  • About
狐窝
OvO
  1. 首页
  2. 未分类
  3. 正文

Oracle 12c中添加scott用户的方法

2017年08月06日 72点热度 0人点赞 0条评论
2016-10-08 13:08 from---http://blog.csdn.net/hpdlzu80100/article/details/52755680?hmsr=toutiao.io&utm_medium=toutiao.io&utm_source=toutiao.io
分类:

版权声明:本文为博主原创文章,未经博主允许不得转载。

新电脑上安装了Oracle 12c,没有自带scott用户。看了不少帖子,发现以下帖子最为靠谱。

补充事项:

1. 运行语句“select username from dba_users where username = 'SCOTT';”时,我遇到了以下错误:

第 1 行出现错误:
ORA-01219: 数据库或可插入数据库未打开: 仅允许在固定表或视图中查询

解决办法:

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL语句> alter database open;

2. 运行语句“conn scott/[email protected];”时,我遇到了以下错误:

ERROR:
ORA-28000: the account is locked

警告: 您不再连接到 oracle。

解决办法:

SQL> alter session set Container=pdborcl;

会话已更改。

SQL> alter user scott account unlock;

用户已更改。

SQL> conn scott/[email protected]
ERROR:
ORA-28001: the password has expired

更改 scott 的口令
新口令:
重新键入新口令:
口令已更改
已连接。

还是那句老话,看似简单的问题,实际解决起来并不简单啊。

原文标题:Oracle 12c 添加scott用户

原文链接:http://www.cnblogs.com/xqzt/p/5034375.html

原文内容:

对于熟悉Oracle或者接触过Oracle的人,scott这个用户大家一定相当的熟悉。12c推出了可插拔数据库,在一个容器cdb中以多租户的形式同时存在多个数据库pdb。pdb中默认不包含scott用户。

为了做数据泵的实验,需要一个测试用户,因此需要在pdb中添加scott用户。

oracle 12c自带了scott的脚本,$ORACLE_HOME/rdbms/admin/utlsampl.sql 内容如下:

复制代码
Rem Copyright (c) 1990, 2006, Oracle. All rights reserved.  
Rem NAME
REM    UTLSAMPL.SQL
Rem  FUNCTION
Rem  NOTES
Rem  MODIFIED
Rem     lburgess   04/02/06  - lowercase passwords 
Rem     menash     02/21/01 -  remove unnecessary users for security reasons
Rem     gwood      03/23/99 -  make all dates Y2K compliant
Rem     jbellemo   02/27/97 -  dont connect as system
Rem     akolk      08/06/96 -  bug 368261: Adding date formats
Rem     glumpkin   10/21/92 -  Renamed from SQLBLD.SQL 
Rem     blinden   07/27/92 -  Added primary and foreign keys to EMP and DEPT
Rem     rlim       04/29/91 -         change 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 independent
Rem
rem 
rem $Header: utlsampl.sql 02-apr-2006.21:13:01 lburgess Exp $ sqlbld.sql 
rem 
SET TERMOUT OFF
SET ECHO OFF

rem CONGDON    Invoked in RDBMS at build time.   29-DEC-1988
rem 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/tiger
CREATE 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,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
    (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
    (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
    (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','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(10)    ,
    JOB VARCHAR2(9)  ,
    SAL NUMBER,
    COMM NUMBER
    ) ;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT 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
复制代码

下面就利用这段脚本,在pdb中恢复scott

1、为pdb添加tnsnames (步骤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 = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xqzt)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =pdborcl)
    )
  )
复制代码

2、修改utlsampl.sql

将  connect scott/tiger 改为  connect  scott/[email protected]

3、使用sys登录plsql,切换到要导入的容器pdb ,执行 utlsampl.sql

切换到容器pdborcl

复制代码
SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2        PDB$SEED                READ ONLY    NO
    3         PDBORCL                READ WRITE   NO
SQL> alter session set container=pdborcl;  ##切换到pdb

Session altered.
复制代码

  确认使用sys连接到了pdborcl

复制代码
SQL> alter session set container=pdborcl;

Session altered.

SQL> show user
USER is "SYS"
SQL> show con_name;

CON_NAME
------------------------------
PDBORCL
复制代码

执行utlsampl.sql 脚本

SQL>  @$ORACLE_HOME/rdbms/admin/utlsampl.sql    
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

4、验证导入结果

查询是否存在scott用户

复制代码
SQL> alter session set container=pdborcl;

Session altered.

SQL> select username from dba_users where username = 'SCOTT';

USERNAME
--------------------------------------------------------------------------------
SCOTT
复制代码

以scott登录 查询数据

复制代码
SQL> conn scott/[email protected]                        
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE

SQL> select * from DEPT;

    DEPTNO DNAME      LOC
---------- -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH      DALLAS
    30 SALES         CHICAGO
    40 OPERATIONS      BOSTON
复制代码
顶
0
踩
0
标签: 暂无
最后更新:2017年08月06日

OvO

狐狸

点赞
< 上一篇
下一篇 >
最新 热点 随机
最新 热点 随机
钛备份或小米系统备份功能备份出来的APK无法正常还原 BBR安装启用 安装docker mitmproxy 调试方法 Android SDK 版本选择 Perdition Mail代理服务器配置 HAProxy SSL代理 Openssl 中间人SSL代理 Stunnel SSL to SSL代理 NGINX反代IMAP配置 各种设备User Agent Torrent下载工具推荐 用 rar2john+hashcat 破解 RAR 文件密码 2020最好的Linux发行版 关闭新版chrome工具栏上拼图图标 Office 下载、安装、激活,有它就够了! Android 非官方的发行版 openwrt添加autoconf-archive
NGINX反代IMAP配置Stunnel SSL to SSL代理Openssl 中间人SSL代理HAProxy SSL代理Perdition Mail代理服务器配置Android SDK 版本选择mitmproxy 调试方法安装dockerBBR安装启用钛备份或小米系统备份功能备份出来的APK无法正常还原
Centos7安装Oracle12c office2016激活备份方法 Windows DPAPI 数据加密保护接口详解 freenas 插件权限设置 如何下载各版本Chrome的离线安装包 使用OpenSsl自己CA根证书,二级根证书和颁发证书 Debian相关WIKI信息 Tesseract-OCR 训练中文 yum 和 epel 的详解 5 个用于 SOHO 的 Linux 服务器发行版 知识库 : 使用Tesseract识别弱验证码 cl编译C文件的环境变量修改 MyBatis Plugin 学习使用 Windows下的Console Emulator Android SDK 版本选择 【插件】再谈网络运维人员的火狐(firefox)扩展 Linux 网卡 mtu 1500 qdisc noop state DOWN group default qlen 1000 Ubuntu 系统 SSD固态硬盘检查4K对齐以及开启Trim
标签聚合
linux git chrome yum chromium ssl docker 安装 镜像 路由 blog 网卡 下载 https 密码 e

COPYRIGHT © 2020 狐窝. ALL RIGHTS RESERVED.

THEME KRATOS MADE BY VTROIS