Oracle – IT点滴网

2020-07-21

CentOS7 安装oracle客户端

作者 chenj 在 Oracle

1、本机环境CentOS7 64　　

2、下载oracle客户端包

进入主页面：

32位:

https://www.oracle.com/database/technologies/instant-client/linux-x86-32-downloads.html

64位:

https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

　　下载当前的最新版本18.03，只下载下面两个文件即可

　　oracle-instantclient18.3-basic-18.3.0.0.0-1.i386.rpm
　　oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.i386.rpm

3、rpm方式安装包

　　将上述下载文件放到centos中某个目录下，我的放在/home/ftpuser/下

[root@localhost ftpuser]# pwd
/home/ftpuser

# ls -l 总用量 54076 -rw-r–r–. 1 ftpuser ftpuser 54650380 1月 3 10:09 oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm -rw-r–r–. 1 ftpuser ftpuser 717272 1月 3 10:09 oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64.rpm

进入目录，使用命令安装：
　　rpm -ivh oracle-instantclient18.3-basic-18.3.0.0.0-1.i386.rpm
　　rpm -ivh  oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.i386.rpm

rpm简单命令复习
　　1.安装软件：执行rpm -ivh rpm包名 
　　2.查询软件包的详细信息：执行rpm -qpi rpm包名

4、环境变量配置

　　打开用户专属配置文件(个人理解相当于windows系统环境变量的用户变量，只针对当前用户生效)：vi ~/.bashrc

　　在最后增加如下几行(注意路径是否正确)：　　

[root@localhost ~]# vi ~/.bashrc

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_VERSION=18.3
export ORACLE_HOME=/usr/lib/oracle/$ORACLE_VERSION/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
#export NLS_LANG="Simplified Chinese_china".ZHS16GBK
export PATH=$PATH:$ORACLE_HOME/bin

　　按下esc输入 :wq 保存退出

5、配置TNS

　　复制oracle服务端的tnsnames.ora文件，放到/usr/lib/oracle/18.3/client64/network/admin目录下。

6、测试

[root@localhost 18.3]# sqlplus test/ok123@PRODUCT_DB

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Apr 8 16:33:32 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Mon Apr 08 2019 13:33:20 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

2020-07-05

liunx Oralce 监听模板

作者 chenj 在 Oracle

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (SID_NAME = orcl)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

2020-06-22

记录下连接Docker中Oracle12C所遇到的问题

作者 chenj 在 Oracle

公司最近用上了Docker，所以大部分应用和数据库都正在往Docker迁移；今天晚上就遇到一个小问题，特此记录下；我像以前连接11g一样，写上用户名密码和ip/orcl开始连接，结果直接 ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

辗转反思，进去容器里面也捣鼓了半天，无果；附上命令

docker exec -it 容器ID /bin/bash

也没发现监听和tnsnames.ora文件，偶然在网上看到查询实例名，于是试一试，

sqlplus / as sysdba 输入用户名密码；

由于我用的镜像是sath89/oracle-12c，所以用户名密码是system/oracle

select name from v$database;查询实例名，结果是XE，汰。

于是重新试一试，成功！

附图

最后附上创建表空间和用户的命令

–创建表空间
create tablespace pd_psssyh

logging

datafile ‘/u01/app/oracle/pd_psssyh.dbf’ –oracle12c容器内的路径，挂载的路径在/data/database/oracle12/

size 50m

autoextend on –自动增长

next 50m maxsize 20480m

extent management local;

–添加用户
create user pd_psssyh identified by pd_psssyh

default tablespace pd_psssyh –默认表空间

temporary tablespace TEMP –临时表空间

–授权
grant connect,resource,dba to pd_psssyh;

2020-06-21

Oracle中row_number()、rank()、dense_rank() 的区别

作者 chenj 在 Oracle

row_number的用途非常广泛，排序最好用它，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

rank函数用于返回结果集的分区内每行的排名，行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。

dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。

借助实例能更直观地理解：

假设现在有一张学生表student，学生表中有姓名、分数、课程编号。

select * from student;

现在需要按照课程对学生的成绩进行排序：

--row_number() 顺序排序
select name,course,row_number() over(partition by course order by score desc) rank from student;

--rank() 跳跃排序，如果有两个第一级别时，接下来是第三级别
select name,course,rank() over(partition by course order by score desc) rank from student;

--dense_rank() 连续排序，如果有两个第一级别时，接下来是第二级别 
select name,course,dense_rank() over(partition by course order by score desc) rank from student;

取得每门课程的第一名：

--每门课程第一名只取一个： 
select * from (select name,course,row_number() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有： 
select * from (select name,course,dense_rank() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有：
select * from (select name,course,rank() over(partition by course order by score desc) rank from student) where rank=1;

附：每门课程第一名取所有的其他方法（使用group by 而不是partition by）：

select s.* from student s
　　inner join(select course,max(score) as score from student group by course) c
　　on s.course=c.course and s.score=c.score;　
--或者使用using关键字简化连接
select * from student s
　　inner join(select course,max(score) as score from student group by course) c
　　using(course,score);

关于Parttion by：

Parttion by关键字是Oracle中分析性函数的一部分，用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列，能够返回一个分组中的多条记录（记录数不变），而Group by是对原始数据进行聚合统计，一般只有一条反映统计值的结果（每组返回一条）。

TIPS：

使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。

可以这样： rank over(partition by course order by score desc nulls last)

总结：

在使用排名函数的时候需要注意以下三点：

1、排名函数必须有 OVER 子句。

2、排名函数必须有包含 ORDER BY 的 OVER 子句。

3、分组内从1开始排序。

2020-06-21

Oracle连接数过多释放机制

作者 chenj 在 Oracle

Oracle连接数过多释放机制

sqlplus /nolog
打开sqlplus


connect /as sysdba
使用具有dba权限得用户登陆oracle


show parameter resource_limit
显示资源限定是否开启,value为true是开启,为false是关闭

alter system set resource_limit=true
如果未开启,则使用此命令开启资源限定功能


create profile profileName limit connect_time 60 idle_time 30
创建profile文件,profileName任意起,connect_time设置连接超过多少分钟后强制释放,idle_time设置连续不活动的会话超过多少分钟后强制释放

alter user oracleUser profile profileName
将profile文件作用于指定用户

从上周起，服务器Oracle数据库出现问题，用不到半天，就会报maxsession（150）的问题，肯定是数据库的会话超过最大数了。

由于服务器跑的是文件传输应用，占用的请求和会话肯定很大，因此用户数不大就已经让oracle的会话数达到最大值。

处理方式不外乎两种：扩大oracle最大session数以及清除inactive会话，当然还有，就是从数据库连接池和程序bug上面下手。

从各处收集了一些查看当前会话的语句，记录一下：

1.select count(*) from v$session；

select count(*) from v$process；

查看当前总会话数和进程数，这两个视图就是跟会话及进程有关的重要视图啦，信息都是从这里面取的。

2.查询那些应用的连接数此时是多少

select b.MACHINE, b.PROGRAM , count(*) from v$process a, v$session b where a.ADDR = b.PADDR and b.USERNAME is not null group by b.MACHINE , b.PROGRAM order by count(*) desc;

3.查询是否有死锁

select * from v$locked_object;

如果查询结果为no rows selected，说明数据库中没有死锁。否则说明数据库中存在死锁。

接下来说明一下会话的状态：

1.active 处于此状态的会话，表示正在执行，处于活动状态。

2.killed 处于此状态的会话，表示出现了错误，正在回滚，当然，也是占用系统资源的。还有一点就是，killed的状态一般会持续较长时间，而且用windows下的工具pl/sql developer来kill掉，是不管用的，要用命令：alter system kill session ‘sid,serial#’ ;

3.inactive 处于此状态的会话表示不是正在执行的，比如select语句已经完成。我一开始以为，只要是inactive状态的会话，就是该杀，为什么不释放呢。其实，inactive对数据库本身没有什么影响，但是如果程序没有及时commit，那么就会造成占用过多会话。解决inactive的方法最好的就是在 oracle中直接设置超时时间，也是有两种方法，区别暂时还不清楚：

1.修改sqlnet.ora文件，新增expire_time=x(单位是分钟)

我的sqlnet.ora位置在D:/oracle/ora92/network/admin

2.通过ALTER PROFILE DEFAULT LIMIT IDLE_TIME 10; 命令修改,记得重启下oracle。

修改ORACLE 中的SESSION和PROCESS

会话sessions和进程pocesses的关系
一个process可以有0个、1个或者多个session，一个session也可以存在若干个process中，并行同样是一个session对应一个process，主session是coordinator session，每个parallel process同样会对应数据库里一个单独的session。可以从v$px_session和v$session中验证这点。
连接connects,会话sessions和进程pocesses的关系

每个sql login称为一个连接（connection),而每个连接，可以产生一个或多个会话，如果数据库运行在专用服务器方式，一个会话对应一个服务器进程（process),如果数据库运行在共享服务器方式，一个服务器进程可以为多个会话服务。

Oracle的sessions和processes的数量关系是：sessions=1.1 * processes + 5

下面我们用两种方法修改PROCESS的最大值
一、通过Oracle Enterprise Manager Console在图形化管理器中修改
以系统管理员的身份登入，进入界面数据库的例程 – 配置 – 一般信息 – 所有初始化参数，修改processes的值

二、在SQLPLUS中修改
以DBA权限登录，修改PROCESS的值（SESSION的值会跟着改）；创建pfile；重新启动数据库。输入的SQL命令如下，回显信息省略了
SQL> connect sys/sys as sysdba
SQL> alter system set processes=400 scope = spfile;
SQL> create pfile from spfile;
SQL> shutdown immediate;
SQL> startup

分类目录Oracle

Oracle连接数过多释放机制