1 客户端连接

1.1 安装和使用

oracle连接客户端有sqlplus和sqlDeveloper以及其他, 本篇主要就讲解sqlplus和sqlDeveloper使用过程中
碰到的一些问题.关于mac上如何安装Instant Client(轻量级绿色环保客户端程序包)请见:
官网安装.
按照官网说明下载安装完成之后即可使用sqlplus进行客户端连接了, 如果本地没有oracle服务器, 可以使用
docker快速安装一个oracle服务器, 按照官网的说明进行配置即可:
docker-oracle-enterprise

运行的docker情况如下:

1
b265cb8eda03   store/oracle/database-enterprise:12.2.0.1   "/bin/sh -c '/bin/ba…"   13 days ago   Up 4 minutes (healthy)   0.0.0.0:1521->1521/tcp, 5500/tcp   oraclebifeng

在安装完oracle服务器或者连接服务器的时候先要确认下

  • username
  • password
  • SERVICE_NAME: 连接远程服务器的时候非常重要
  • ORACLE_SID: 类似SERVICE_NAME

(这些术语在2.1节进行详细说明), 否则连接服务器的时候会出现如下的一些错误:

1
ORA-12514: TNS:listener does not currently know of service requested in connect

那么如何获取service_name, oracle_sid信息呢?
首先, 进入orcale容器中, 使用命令sqlplus sys as sysdba登录客户端; 之后就可以使用SQL语句进行环境
变量的查询操作:

1
2
3
4
-- 1. 获取service_name
show parameter service_name
-- 2. 获取SID
select sys_context('USERENV','DB_NAME') from dual;

后续就可以使用sqlplus和sqlDeveloper进行连接测试了.

1.2 sqlplus登录

10G以上版本本地登录不需要进行网络配置, 直接连接, 基本格式如下:

1
sqlplus 用户名/密码@ip地址[:端口]/service_name [as sysdba]

对于oracle9和以前的版本, 需要配置tnsnames.ora文件:

1
2
3
4
5
6
7
8
9
10
11
12
13
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip或主机名称)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = 数据库的服务名称)
)
)
# PROTOCOL:客户端与服务器端通讯的协议,一般为TCP,该内容一般不用改
# HOST:数据库侦听所在的机器的机器名或IP地址
# PORT:数据库侦听正在侦听的端口
# SERVICE_NAME:在服务器端名, 使用show parameter service_name查看

直接登录: sqlplus
使用用户名和密码: sqlplus [username]/[password]

操作系统权限认证的SYS 管理员登录:

1
2
3
4
5
6
7
8
-- 1. 指明, 此时需要输入密码
sqlplus sys as sysdba
show user
exit

-- 2. 默认
sqlplus / as sysdba
show user

不在终端暴露密码:

1
2
3
4
5
sqlplus /nolog
-- 1. 后续使用conn [user]/[passwd]进行登录(如果是sys用户需要指定sysdba
conn sys/syspwd as sysdba
-- 2. 使用sys登录
conn / as sysdba

这里有一个问题, 使用sqlplus连接的时候使用ORACLE_SID连接数据库实例的时候一直报错,
我看了一下sqlplus --help, 发现可能是命令不支持, 后续有时间再处理.

1.3 sqlDeveloper

一旦获取Username, Password, Service_name, ORACLE_SID之后, 使用sqlDeveloper连接就非常
简单了:

service_name

使用Service_name进行连接.

SID

使用SID进行连接.

2 数据库信息

2.1 术语

数据库: 物理数据,内存,操作系统进程的组合体.
获取数据库名: select name from v$database;

实例:是访问Oracle数据库所需的一部分计算机内存和辅助处理后台进程,

  • 进程和这些进程所使用的内存(SGA)所构成一个集合.
  • Oracle 用它们来管理数据库访问, 用户要基于实例来进行数据库访问服务
  • 任意时刻, 一个实例只能与一个数据库关联, 访问一个数据库
  • 同一个数据库可以由多个实例访问(RAC)

数据库实例名用于对外部连接, 在操作系统中要取得与数据库的联系,必须使用数据库实例.
注意, instance_name就是平常说的SID值. 获取实例名和service名操作:

1
2
3
4
5
6
--1. 实例, 输出: ORCLCDB
select instance_name from v$instance;
--2. service_name, 输出: ORCLCDB.localdomain
show parameter service_name
--3. 获取 SID(实际上就是实例), 输出: ORCLCDB
SELECT sys_context('userenv','instance_name') FROM dual;

关于上面这些环境变量, 其各自含义如下:

  1. ORACLE_SID: 操作系统环境变量, 基于它得到实例名, 其名字包含instance_name
  2. INSTANCE_NAME: 数据库实例名(SID), 在操作系统中要取得与数据库之间的交互必须使用数据库实例名
  3. DB_NAME: 数据库名
  4. DB_DOMAIN: 表示域名
  5. GLOBAL_NAME: 表示数据库名和域名的总和
  6. SERVICE_NAME: 数据库服务名, 用于监听器中, 一个对外的服务名, SID 只有一个, 但是服务名可以有多个

Oracle 数据库管理系统, 由Oracle 数据库和 Oracle 实例, 这是区别于mysql的.

2.2 获取信息

获取当前数据库版本信息: select * from v$version

USERENV函数用于获取当前用户信息, 例如:

1
2
3
4
5
6
7
--1. 判断当前用户是否为 DBA
select userenv('isdba' from dual;
--2. 获取session标志信息
select userenv('sessionid') from dual;
--3. 获取支持语言
select userenv('language') from dual;
select userenv('lang') from dual;

sys_context返回上下文namespace有关参数的值,其第一个参数是固定的userenv.
另外dual是一个虚拟表,用来构成select的语法规则,oracle保证dual里面永远只有一条记录.

1
2
3
4
5
6
7
8
--1. 用户认证类型
select sys_context('USERENV','AUTHENTICATION_TYPE') from dual;
--2. 当前登录用户
select sys_context('USERENV','CURRENT_USER') from dual
--3. 数据库实例名(SID)
select sys_context('USERENV','DB_NAME') from dual;
--4. 获取数据库类型
select sys_context('userenv', 'con_name') from dual;

3 用户

3.1 简单命令

首先, 让我们先看下sys和system用户区别:

  • system是数据库内置的一个普通管理员, 手动创建并赋予dba角色后用户和system类似
  • sys数据库的超级用户, sys用户必须以sysdba身份登录
  • 任何用户以sysdba身份登录时都会自动转为sys

查看当前用户: show user

查看数据库用户: select * from dba_users

修改当前登录用户密码:

1
2
3
4
--1. 当前用户
password;
--2. 指定用户
alter user username identified by password;

3.2 用户和表空间

在创建用户之前先了解一下表空间概念, 表空间: Oracle 的表都是存储在表空间里面的,
创建表之前需要创建一个表空间. 查看所有表空间:

1
2
3
4
5
6
7
--1. 直接查询
select * from dba_tablespaces;
--2. 使用视图"v$"
select name from v$tablespace;

--3. 查看表空间详细数据:
SELECT FILE_NAME,TABLESPACE_NAME from DBA_DATA_FILES;

创建表空间, 其格式:

1
2
3
4
create tablespace [表空间名称]
datafile [表空间数据文件路径 ]
size [表空间大小]
autoextend on;

实例:

1
2
3
4
create tablespace tbspace
datafile '/u02/app/oracle/oradata/ORCL/mytest.dbf'
size 50m
autoextend on;

正常情况下创建用户会报错invalid common user or role name, 原因是
oracle有两种概念CDB(数据库容器)PDB(可插拔数据库), 从oracle12开始
允许一个 CDB承载多个 PDB, 即数据库和实例有如下关系:

  • 1 - 1
  • 1 - N (单数据库多实例, RAC)
  • N- 1

如何查看当前数据库是否为CDB? 如何查看当前容器? 查看 PDBS?

1
2
3
4
5
6
--1. 查看是否 CDB
select name,cdb,open_mode,con_id from v$database;
--2. 查看当前容器
show con_name;
--3. 查看pdbs
show pdbs;

上面的创建用户问题出错就是数据库未切换到 PDB 上面.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--1. 如果pdb没有打开, 则需要先打开pdb
show pdbs;
alter pluggable database pdbname1 open;
select con_id, dbid, name, open_mode, con_id from v$pdbs;
--2. 关闭
alter pluggable database pdbname2 close;
--3. 在开启之后需要切换
alter session set container=ORCLPDB1;
select sys_context('userenv', 'con_name') from dual;
--4. 注意在切换之后要重新创建tablespace
create tablespace bifengspace
datafile '/u02/app/oracle/oradata/ORCL/bifeng.dbf'
size 50m
autoextend on;

在执行完上述命令之后就可以执行上面创建用户, 另外, 上面命令执行完毕进行
适用于当前打开的实例窗口, 在另外一个客户端上查询CON_NAME会发现未发生更改
创建用户命令格式:

1
2
3
4
CREATE USER [用户名]
IDENTIFIED BY [密码]
DEFAULT TABLESPACE [表空间] (默认USERS)
TEMPORARY TABLESPACE [临时表空间] (默认TEMP)

例子:

1
2
3
4
5
6
7
8
--1. 创建用户
create USER bifeng
identified by bifeng123
default tablespace bifengspace;
--2. 分配权限, 其中 DBA 为最高权限
GRANT CONNECT TO bifeng
GRANT RESOURCE to BIFENG;
GRANT DBA TO bifeng;

4 表

查看用户所有表: SELECT TABLE_NAME FROM USER_TABLES;

用户可存取的表: SELECT TABLE_NAME FROM ALL_TABLES;

数据库中所有表: SELECT TABLE_NAME FROM DBA_TABLES;

5 引用

参考: