oracle笔记
# 1. oracle安装
# 1.1 安装Oracle前准备
# 1.1.1 创建运行oracle数据库的系统用户和用户组
[humf@localhost ~]$ su root #切换到root
Password:
[root@localhost sonny]# groupadd oinstall #创建用户组oinstall
[root@localhost sonny]# groupadd dba #创建用户组dba
[root@localhost sonny]# useradd -g oinstall -g dba -m oracle #创建oracle用户,并加入到oinstall和dba用户组
[root@localhost sonny]# passwd oracle #设置用户oracle的登陆密码,不设置密码,在CentOS的图形登陆界面没法登陆
Changing password for user oracle.
New password: # 密码
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: # 确认密码
passwd: all authentication tokens updated successfully.
[root@localhost sonny]# id oracle # 查看新建的oracle用户
uid=1001(oracle) gid=1002(dba) groups=1002(dba)
[root@localhost sonny]#
理论上单例按照需要3种用户组,实际只建两个oinstall和dba,后面再安装oracle数据库的时候把OSOPER组也设置是dba组。
a.oracle 清单组(一般为oinstall):
OINSTALL 组的成员被视为 Oracle 软件的“所有者”,拥有对 Oracle 中央清单 (oraInventory) 的写入权限。在一个 Linux 系统上首次安装 Oracle 软件时,
OUI 会创建 /etc/oraInst.loc 文件。该文件指定 Oracle 清单组的名称(默认为 oinstall)以及 Oracle 中央清单目录的路径。
b.数据库管理员(OSDBA,一般为 dba):
OSDBA 组的成员可通过操作系统身份验证使用 SQL 以 SYSDBA 身份连接到一个 Oracle 实例。该组的成员可执行关键的数据库管理任务,如创建数据库、启动和关
闭实例。该组的默认名称为dba。SYSDBA 系统权限甚至在数据库未打开时也允许访问数据库实例。对此权限的控制完全超出了数据库本身的范围。不要混淆 SYSDBA
系统权限与数据库角色 DBA。DBA 角色不包括 SYSDBA 或 SYSOPER 系统权限。
c.数据库操作员组(OSOPER,一般为 oper):
OSOPER 组的成员可通过操作系统身份验证使用 SQL 以 SYSOPER 身份连接到一个 Oracle 实例。这个可选组的成员拥有一组有限的数据库管理权限,如管理和运行备份。
该组的默认名称为oper。SYSOPER 系统权限甚至在数据库未打开时也允许访问数据库实例。对此权限的控制完全超出了数据库本身的范围。要使用该组,选择 Advanced 安装类型来安装 Oracle 数据库软件。
# 1.2 修改操作系统核心参数
在Root用户下执行以下步骤:
1)修改用户的SHELL的限制,修改/etc/security/limits.conf文件
输入命令:vi /etc/security/limits.conf,按i键进入编辑模式,将下列内容加入该文件。
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
编辑完成后按Esc键,输入“:wq”存盘退出
2)修改/etc/pam.d/login 文件,输入命令:vi /etc/pam.d/login,按i键进入编辑模式,将下列内容加入该文件。
session required /lib/security/pam_limits.so
session required pam_limits.so
编辑完成后按Esc键,输入“:wq”存盘退出
3)修改linux内核,修改/etc/sysctl.conf文件,输入命令: vi /etc/sysctl.conf ,按i键进入编辑模式,将下列内容加入该文件
fs.file-max = 6815744
fs.aio-max-nr = 1048576
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
编辑完成后按Esc键,输入“:wq”存盘退出
4)要使 /etc/sysctl.conf 更改立即生效,执行以下命令。 输入:sysctl -p 显示如下:
linux:~ # sysctl –p
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.conf.all.rp_filter = 1
fs.file-max = 6815744
fs.aio-max-nr = 1048576
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
5)编辑 /etc/profile ,输入命令:vi /etc/profile,按i键进入编辑模式,将下列内容加入该文件。
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
编辑完成后按Esc键,输入“:wq”存盘退出
# 1.3 创建数据库软件目录和数据文件存放目录
目录的位置,根据自己的情况来定,注意磁盘空间即可,这里我把其放到oracle用户下,例如:
输入命令:
mkdir /home/oracle/app
mkdir /home/oracle/app/oracle
mkdir /home/oracle/app/oradata
mkdir /home/oracle/app/oracle/product
# 1.4 更改目录属主为Oracle用户所有,输入命令:
chown -R oracle:oinstall /home/oracle/app
# 1.5 配置oracle用户的环境变量
输入:su – oracle ,然后直接在输入 : vi .bash_profile
按i编辑 .bash_profile,进入编辑模式,增加以下内容:
export ORACLE_BASE=/home/oracle/app
export ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
编辑完成后按Esc键,输入“:wq”存盘退出
ps:我的云服务器没有防火墙,如果你们有,可以关闭
--关闭防火墙 CentOS 7.2默认使用的是firewall作为防火墙
[humf@localhost /]$ su root
Password:
[root@localhost /]# systemctl status firewalld.service #查看防火墙状态,运行中
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2016-04-07 18:54:29 PDT; 2h 20min ago
Main PID: 802 (firewalld)
CGroup: /system.slice/firewalld.service
└─802 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid
Apr 07 18:54:25 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon...
Apr 07 18:54:29 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon.
[root@localhost /]# systemctl stop firewalld.service #关闭防火墙
[root@localhost /]# systemctl status firewalld.service #再次查看防火墙状态,发现已关闭
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Thu 2016-04-07 21:15:34 PDT; 9s ago
Main PID: 802 (code=exited, status=0/SUCCESS)
Apr 07 18:54:25 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon...
Apr 07 18:54:29 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon.
Apr 07 21:15:33 localhost systemd[1]: Stopping firewalld - dynamic firewall daemon...
Apr 07 21:15:34 localhost systemd[1]: Stopped firewalld - dynamic firewall daemon.
[root@localhost /]# systemctl disable firewalld.service #禁止使用防火墙(重启也是禁止的)
Removed symlink /etc/systemd/system/dbus-org.Fedoraproject.FirewallD1.service.
Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service.
[root@localhost /]#
防火墙先禁用,搞好之后再配置,个人虚拟机,要毛线防火墙~~
# 1.6 关闭selinux(需重启生效)
[root@localhost /]# vi /etc/selinux/config
[root@localhost /]# cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled #此处修改为disabled
# SELINUXTYPE= can take one of three two values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
[root@localhost /]#
为啥要关闭selinux?因为selinux太高深,非专业人士搞不懂~~
# 1.7 正式Oracle的安装过程
1)当上述系统要求操作全部完成后,注销系统,在图形界面以Oracle用户登陆。
PS:对于不会用图形界面的这边教你怎么用Windows远程连接Oracle图形界面
前提:
2.1.1 CentOS已经安装桌面,如果无桌面,请执行 (yum不会的可以百度,这边不赘述了)
# yum -y groups install "GNOME Desktop"
# startx
2.1.2 配置源
# yum install epel* -y
2.1.3 安装xrdp
# yum --enablerepo=epel -y install xrdp
2.1.4 启动xrdp并设置开机启动
# systemctl start xrdp
# systemctl enable xrdp
2.1.5 打开Windows的远程工具输入服务器IP用Oracle账户登陆就行
首先将下载的Oracle安装包复制到linux中,用SSH其他ftp工具拷贝。
打开一个终端,运行unzip命令解压oracle安装文件,如:
输入命令:
unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip
解压完成后 cd 进入其解压后的目录database
输入命令:
cd database
使用ls命令可以查看解压后database所包含的文件,如下图:
2)执行安装,输入命令:./runInstaller
装到这一步,可以看到,可以查看到有很多的rpm包没有,这里可以通过yum安装
我这边的是操作系统:Oracle Linux 7 centos
The following packages (or later versions) must be installed:
binutils-2.23.52.0.1-12.el7.x86_64
compat-libcap1-1.10-3.el7.x86_64
gcc-4.8.2-3.el7.x86_64
gcc-c++-4.8.2-3.el7.x86_64
glibc-2.17-36.el7.i686
glibc-2.17-36.el7.x86_64
glibc-devel-2.17-36.el7.i686
glibc-devel-2.17-36.el7.x86_64
ksh
libaio-0.3.109-9.el7.i686
libaio-0.3.109-9.el7.x86_64
libaio-devel-0.3.109-9.el7.i686
libaio-devel-0.3.109-9.el7.x86_64
libgcc-4.8.2-3.el7.i686
libgcc-4.8.2-3.el7.x86_64
libstdc++-4.8.2-3.el7.i686
libstdc++-4.8.2-3.el7.x86_64
libstdc++-devel-4.8.2-3.el7.i686
libstdc++-devel-4.8.2-3.el7.x86_64
libXi-1.7.2-1.el7.i686
libXi-1.7.2-1.el7.x86_64
libXtst-1.2.2-1.el7.i686
libXtst-1.2.2-1.el7.x86_64
make-3.82-19.el7.x86_64
sysstat-10.1.5-1.el7.x86_64
一个一个的安装,命令也很简单,反正文档要求高版本也可以:
[humf@localhost data]$ su root
Password:
[root@localhost data]# yum install binutils
省略...
一个一个检查package,在准备阶段中漏掉的,此处再安装,有些系统报错是因为现有的包的版本比检测要高,最后右上角忽略即可
3)安装过程是一个漫长的过程,中间有几次卡住,没有出现任何画面,屏幕中间有条小线,尝试多次,发现光标在该线上,右键点击Closed,不知道关闭了啥,又能继续安装了。先装吧,到时有问题看日志
# 1.8 配置监听listener
1)执行netca 报错
[Oracle@localhost ~]$ netca
Oracle Net Services Configuration:
#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
# SIGSEGV (0xb) at pc=0x00007f69a69fcb9d, pid=8033, tid=140092892297024
#
# Java VM: Java HotSpot(TM) 64-Bit Server VM (1.5.0_17-b03 mixed mode)
# Problematic frame:
# C [libclntsh.so.11.1+0x62ab9d] snlinGetAddrInfo+0x1b1
#
# An error report file with more information is saved as hs_err_pid8033.log
#
# If you would like to submit a bug report, please visit:
# http://java.sun.com/webapps/bugreport/crash.jsp
#
/data/oracle/product/11.2.0/db_1/bin/netca: line 178: 8033 Aborted (core dumped) $JRE $JRE_OPTIONS -classpath $CLASSPATH oracle.net.ca.NetCA $*
[oracle@localhost ~]$
错误原因:安装操作系统是默认主机名localhost造成错误
解决办法:
racle]# cat /etc/sysconfig/network
# Created by anaconda
[root@localhost oracle]# vi /etc/sysconfig/network #增加HOSTNAME
[root@localhost oracle]# cat /etc/sysconfig/network
# Created by anaconda
HOSTNAME=odb-sonny
[root@localhost oracle]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
[root@localhost oracle]# vi /etc/hosts #增加HOSTNAME
[root@localhost oracle]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 odb-sonny
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
[root@localhost oracle]# hostname odb-sonny #执行
[root@localhost oracle]#
最后注销当前oracle用户,重新登陆即可!!这次发现打开配置界面正常
# 1.9 创建Oracle数据实例Orcl
执行dbca命令,启动oracle实例安装界面,剩下的与Windows上安装一样,不废话了:
注意:必须先创建监听,并且监听是启动中,否则报错。
经过漫长的等待,当看到此界面,说明oracle建库完成
这样oracle服务器安装配置基本就完成了
注意:
1、Linux oracle数据库listener.ora存放路径
cd /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
2、Linux oracle数据库tnsnames.ora存放路径
cd /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
3、ORACLE启动监听器
(1)切换至oracle安装用户(一般为oracle)
su - oracle
(2)启动监听器
lsnrctl start
(3)停止监听器
lsnrctl stop
4、启动测试oracle:
[oracle@oracle ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 27 02:12:12 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> quit
测试的另一种方法:找一台windows平台电脑,telnet oracle主机IP地址:1521,通的话,会出现一个黑屏,光标一闪一闪。
5、Linux开放1521端口允许网络连接Oracle Listener
症状:
(1)TCP/IP连接是通的。可以用ping 命令测试。
(2)服务器上Oracle Listener已经启动。
lsnrctl status 查看listener状态
lsnrctl start 启动Oracle listener
(3)客户端得到的错误信息通常是:ORA-12170: TNS:连接超时
这时,我们基本可以肯定是服务器没有开放1521端口(假设你用默认设置)
解决方法:
(1)假如你是在一个局域网环境,配置了防火墙。那么可以关闭Linux的防火墙。
sudo service iptables stop
(2)编辑iptables, 开放1521端口:
sudo vi /etc/sysconfig/iptables
-A INPUT -p tcp -m state --state NEW -m tcp --dport 1521 -j ACCEPT
:wq
重启防火墙
sudo service iptables restart
保存配置,以便linux重启后依然有效
sudo service iptables save
查看防火墙规则:
sudo iptables -L –n
PS:如果你是云服务器,请看看自己的安全策略组有没有给1521添加
# 1.10 linux下创建oracle用户表空间
就是在已有的数据库实例上创建一个新的帐号,访问一些新的表
操作步骤如下:
(1)登录linux,以oracle用户登录(如果是root用户登录的,登录后用 su - oracle命令切换成oracle用户)
(2)以sysdba方式来打开sqlplus,命令如下: sqlplus "/as sysdba"
(3)查看我们常规将用户表空间放置位置:执行如下sql:
select name from v$datafile
(4)创建用户表空间:
CREATE TABLESPACE NOTIFYDB DATAFILE '/home/oracle/app/oradata/orcl/scsdb.dbf' SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
(5)创建用户,指定密码和上边创建的用户表空间
CREATE USER scs IDENTIFIED BY scs DEFAULT TABLESPACE NOTIFYDB;
(6)赋予权限
grant connect,resource to scs;
grant unlimited tablespace to scs;
grant create database link to scs;
grant select any sequence,create materialized view to scs;
--经过以上操作,我们就可以使用scs/scs登录指定的实例,创建我们自己的表了
# 1.11 安装oracle数据库注意事项
1、按照官方文档安装数据库
检查操作系统环境。如CPU位数、内存数量、交换分区大小
操作系统内核参数设置
关闭selinux
检查磁盘空间大小
检查/etc/hosts域名配置文件
2、安装之前没有检查操作系统错误
/var/log/messages
3、安装过程中网络异常终止
导致前台ssh连接中断,安装终止
1、数据连接类故障
检查是否由网络故障引起
检查是否由主机资源不足引起
检查是否由监听故障引起
CPU使用率,观察sqlnet.log日志
检查是否由数据库故障引起
select * from v$session_wait;
2、数据库无响应(hang类)故障
主机资源达到瓶颈
进程之间的死锁
所有在线日志均为ACTIVE状态,或者归档空间不足导致无法归档
3、数据库性能类故障
性能类故障的处理思路
了解主机的硬件情况、业务系统的拓扑架构
登录到主机,查看主机的资源消耗情况(主要使用top\vmstat\iostat)
登录到数据库,查看活动状态的会话数
下面是获取LOCK相关的资源持有者信息的SQL
select * from gv$lock where block=1;
select * from dba_waiters;
下面是获取LATCH相关的持有者信息的SQL
select * from v$latchholder;
数据误操作类故障的处理思路
如果有备份,则评估一下恢复时间和数据丢失率
如果表被误删(DROP),首先查看回收站中是否存在误删除表,如果不存在,则使用DUL等挖掘工具恢复数据
如果数据被误删除(DELETE),则使用表闪回版本查询特性查看是否能挽救误删除
4、数据库坏块类故障
一般出现ORA-01578,ORA-10632,ORA-00600错误
产生坏块的原因
硬件问题。如内存混乱,CPU计算错误
操作系统BUG
非oracle进程异常修改oracle SGA内存区域
主机或者存储异常掉电
处理思路
使用dbv,rman,exp,expdp检验数据库的坏块数量
如果索引出现坏块,则考虑删除并重建索引
如果表出现坏块,全表扫描时可以跳过坏块
全用bbed工具尝试修复坏块,较为复杂且风险较大
tnsping命令检查连接串是否配置,配置语法是否正确
tnsping oracledb:1521/orcl
tnsping并不检查实例服务和句柄
ssh端口转发
客户端通过ssh免密连接到服务端的oracle服务
ssh -CNfg -L 1555:118.126.93.180:1521 root@118.126.93.180
跟踪监听的工作过程
select * from v$process;
监听的优化思路
1、在listener.ora和tnsnames.ora配置文件中增加关键字(SDU=4096)来增加缓冲大小。如果是在共享服务器模式下,则可以在数据库参数DISPATCHERS增加关键字(SDU=8192)来增加缓冲区大小。而对于动态注册环境,只需要在客户端和服务端的配置文件sqlnet.ora中增加DEFAULT_SDU_SIZE=8192即可
2、调整操作系统的TCP的发送和接受缓冲区大小
3、调整oracle的TCP的发送和接受缓冲区大小。调整SEND/RECVBUF_SIZE大小来达到这一目的。从经验上看,这两个参数在广域网传输中能提高一定的性能
4、通过设置监听参数QUEUESIZE来提高监听处理的最大并发数
5、增加监听来处理更多的并发数。比如增加多个端口
6、将客户端连接方式从短连接修改为长连接或都使用连接池。大量的短连接无疑会消耗更多的CPU和内存
# 2. oracle授权
Oracle提供了三种权限:
connect role(连接角色):connect是使用oracle简单权限,这种权限只对其他用户的表有访问权限,包括select/insert/update和delete等。拥有connect role 的用户还能够创建表、视图、序列(sequence)、簇(cluster)、同义词(synonym)、回话(session)和其他 数据的链(link)。
resource role(资源角色):resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。
dba role(数据库管理员角色):包括无限制的空间限额和给其他用户授予各种权限的能力。
sqlplus sys/sys as sysdba;
create user test identified by test;
# 2.1 用户授权
SQL> grant connect, resource dba to username;
grant create session to test;
这样test用户就能成功登录进去
# 2.2 赋予用户创建表的权限
grant create table to test;
# 2.3 赋予相应的权限
grant unlimited tablespace to test;
这个时候用户就拥有了创建表的权限 由于表是用户test的,相应的他就拥有了对创建的表的增删查改的权限了
# 2.4 撤销权限
revoke create table from test;
# 3. oracle用户管理
drop user 用户名;
若用户拥有对象,则不能直接删除,否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象,然后再删除用户。
drop user 用户名 cascade;
# 3.1 系统默认用户
默认用户名和密码有:
1、用户名: internal
密码:oracle
2、用户名:system
密码:manager
3、用户名:sys
密码:change_on_install
修改用户的密码.
在系统运行中输入:sqlplus /nolog
在命令窗口分别执行:conn /as sysdba
alter user scott identified by tiger;
alter user scott account unlock;
然后“scott”用户密码就修改为“tiger”了,用户可根据自己需求,重置密码。
alter user zzg identified by unis;
grant create session,create table,create view,create sequence,unlimited tablespace to zzg;
# 3.2 激活scott用户
alter database open;
alter user scott account unlock;
alter user scott identified by tiger;
select username,account_status from dba_users;
# 3.3 新建表空间和用户,授权
CREATE TABLESPACE YTH_DATA_TBS
DATAFILE 'D:\oracledata\orcl\YTH_DATA_TBS_01.DBF'
SIZE 500M
AUTOEXTEND ON NEXT 500M
MAXSIZE UNLIMITED
create user jiangxi identified by jiangxi default tablespace YTH_DATA_TBS;
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATE SESSION TO jiangxi;
grant create table to jiangxi;
grant connect to jiangxi;
grant resource to jiangxi;
grant dba to jiangxi;
imp jiangxi/jiangxi@localhost/orcl file=G:/jiangxi.dmp full=y
alter database datafile 'E:\oracle\product\10.2.0\oradata\test.dbf' resize 2000m;
test.dbf --更改表空间大小
# 4. 通过命令直接建立oracle实例
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname orcl -sid orcl -sysPassword syc -systemPassword syc -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u01/app/oracle/fast_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE
shell执行sql语句
su - oracle -c "sqlplus sys/oracle as sysdba<<EOF
alter user scott account unlock;
alter user scott identified by tiger;
quit
EOF"
"sqlplus -S '/ as sysdba' << "select username,account_status from dba_users;""
The Oracle system identifier(SID) "orcl" already exists. Specify another SID
注释/etc/oratab
sqlplus -S sys/sys@localhost select username,account_status from dba_users;
runInstaller -silent -responseFile /home/oracle/db_install.rsp -ignorePrereq
# -silent 指定使用静默模式安装,并使用应答响应文件
# -responseFile 指定响应文件路径
# -ignorePrereq 忽略运行先决条件检查
configure channel 2 device type disk format '/home/oracle/backup/bakup_%U';
configure controlfile autobackup format for device type disk to '/home/oracle/backup/ctl_%F';
检查确认数据库实例是否启动。
SQL> select status from v$instance;
启停数据库
// 停止数据库实例
sqlplus / as sysdba
// 进入sqlplus后,关闭数据库
shutdown immediate
// 启动数据库命令
startup
// 退出
exit
监听服务
// 查看状态
lsnrctl status
// 停止监听服务
lsnrctl stop
// 启动监听服务
lsnrctl start
// 查看服务
lsnrctl service
sqlplus相关配置
set linesize 200;
set pagesize 30;
set heading off;
//登录
sqlplus test/123456@10.16.1.111:1521/orcl
sqlplus脚本相关
sqlplus中引用shell变量
表空间相关创建表空间
//单个文件不能超过32G,总文件数不能超过db_files参数
create tablespace sxdata2 logging datafile '/mnt/data2/oradata/sxdata.dbf' size 10000m autoextend on next 200m maxsize 3500000m extent management local;
// 增加表空间文件
add tablespace sxdata2 datafile '/mnt/data2/oradata/sxdata1.dbf' size 10000m autoextend on next 200m maxsize 3500000m;
// bigfile创建的表空间不受单个文件32G的限制,但不能增加datafile
create bigfile tablespace sxdata2 logging datafile '/mnt/data2/oradata/sxdata.dbf' size 10000m autoextend on next 200m maxsize 3500000m extent management local;
查看表空间
select username, default_tablespace from dba_users;
// 查看用户表空间
select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME='';
查看表数据量
select bytes/1024/1024 as "MB",segment_name from user_segments group by segment_name;
创建DBLINK
create public database link qhfklink connect to myusername identified by 123456
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.10)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = orcl)))';
用户相关创建用户、授权
CREATE USER dbtest IDENTIFIED BY 123456 ;
grant resource,connect,dba to dbtest;
impdp expdp创建数据目录、授权
CREATE DIRECTORY dumptest AS ‘/home/oracle/dbtest’;
GRANT READ,WRITE ON DIRECTORY dumptest TO dbtest;
创建测试数据
create table t1 (id int);
insert into t1 values (123);
创建物理数据目录,导出
mkdir /home/oracle/dbtest
按照表导出
expdp dbtest/123456@orcl directory=dumptest dumpfile=test.dmp tables=t1
其他示例(待整理)
1)按用户导
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;
2)并行进程parallel
expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
3)按表名导
expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;
4)按查询条件导
expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';
5)按表空间导
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
6)导整个数据库
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;
导入数据
导入前,删除dbtest用户下的t1表,测试导入;
impdp dbtest/123456@orcl directory=dumptest dumpfile=test.dmp tables=t1
参考
http://www.cnblogs.com/hellojesson/p/9070597.html
https://www.cnblogs.com/peterpanzsy/p/3435018.html
字符集相关查看服务端字符集
select * from nls_database_parameters;
设置客户端字符集
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
# 5. oracle开机自启
cat >/usr/lib/systemd/system/oracle.service <<EOF
# /etc/systemd/system/oracle-rdbms.service
# Invoking Oracle scripts to start/shutdown Instances defined in /etc/oratab
# and starts Listener
[Unit]
Description=Oracle Database(s) and Listener
Requires=network.target
[Service]
Type=forking
Restart=no
ExecStart=$ORACLE_HOME/bin/dbstart $ORACLE_HOME
ExecStop=$ORACLE_HOME/bin/dbshut $ORACLE_HOME
User=oracle
[Install]
WantedBy=multi-user.target
EOF
# 将服务器设为开机自启
systemctl enable oracle && systemctl restart oracle
# 6. oracle导入导出
# 6.1 exp/imp导出和导入
导入dmp数据库
imp test/111111@XE file=D:/zjyd.dmp full=y
格式说明——imp 用户名/秘钥@数据库版本名称 file=dmp文件路径 full=y
然后回车,即开始导入数据库。
exp jiangxi/jiangxi file=/tmp/cjpt.dmp
imp jiangxi/jiangxi file=/opt/cjpt.dmp full=y
导出工具exp非交互式命令行方式的例子
$ exp scott/tiger tables=(emp,dept) file=/directory/scott.dmp grants=y
说明:把scott用户里两个表emp,dept导出到文件/directory/scott.dmp
$ exp scott/tiger tables=emp query="where job='salesman' and sal<1600" file=/directory/scott2.dmp
说明:在exp里面加上导出emp的查询条件job='salesman' and sal<1600
(但我个人很少这样用,还是把满足条件的记录生成临时表后,再exp会方便一些)
$ exp parfile=username.par file=/directory1/username_1.dmp,/directory1/username_2.dmp filesize=2000M log=/directory2/username_exp.log
导入工具imp非交互式命令行方式的例子
$ imp system/manager fromuser=jones tables=(accts)
$ imp system/manager fromuser=scott tables=(emp,dept)
$ imp system/manager fromuser=scott touser=joe tables=emp
$ imp scott/tiger file = expdat.dmp full=y
$ imp scott/tiger file = /mnt1/t1.dmp show=n buffer=2048000 ignore=n commit=y grants=y full=y log=/oracle_backup/log/imp_scott.log
$ imp system/manager parfile=params.dat
params.dat 内容
file=dba.dmp show=n ignore=n grants=y fromuser=scott tables=(dept,emp)
不同用户之前导出导入(表空间需要一样)
imp fujian/fujian fromuser=personinfo touser=fujian file=/tmp/cjpt.dmp
# 6.2 expdp/impdp导出和导入
1、expdp/impdp和exp/imp的区别
1.exp和imp是客户端工具程序,它们既可以在客户端使用,也可以在服务端使用。
2.expdp和impdp是服务端的工具程序,他们只能在oracle服务端使用,不能在客户端使用。
3.imp只适用于exp导出的文件,不适用于expdp导出文件;impdp只适用于expdp导出的文件,而不适用于exp导出文件。
4.对于10g以上的服务器,使用exp通常不能导出0行数据的空表,而此时必须使用expdp导出。
本节主要讲解的是expdp/impdp的用法,不涉及到exp和imp
2、导出数据
在准备要备份的数据库服务器上创建备份目录(在后面使用sql命令创建的逻辑目录并不是在OS上创建目录,所以我们先要在服务器上创建一个目录)
# su oracle
$ mkdir /home/oracle/oracle_bak
用管理员身份登录到sqlplus
$ sqlplys /nolog
SQL> conn sys/oracle as sysdba
创建逻辑目录
SQL> create directory data_dir as '/home/oracle/oracle_bak';
查看管理员目录是否存在
SQL> select * from dba_direcories;
使用管理员用户给指定的用户赋予在该目录的操作权限(比如该用户需要备份自己的数据)
SQL> grant read,write on directory data_dir to C##BAK_TEST_USER;
导出可有五种方式
1、“full=y”,全量导出数据库
$ expdp sys/oracle@orcl dumpfile=expdp.dmp directory=data_dir full=y logfile=expdp.log
2、schemas按用户导出
$ expdp user/passwd@orcl schemas=user dumpfile=expdp.dmp directory=data_dir logfile=expdp.log
3、按表空间导出
$ expdp sys/passwd@orcl tablespace=tbs1,tbs2 dumpfile=expdp.dmp directory=data_dir logfile=expdp.log
4、导出表
$ expdp user/passwd@orcl tables=table1,table2 dumpfile=expdp.dmp directory=data_dir logfile=expdp.log
5、按查询条件导出
$ expdp user/passwd@orcl tables=table1='where number=1234' dumpfile=expdp.dmp directory=data_dir logfile=expdp.log
2、导入数据
首先将需要导入的数据文件存放导需要导入的数据库服务器上
参照导出的时候的建立目录方式建立物理目录和逻辑目录(只是建目录即可,如果需要给用户权限则加上给用户权限的那步)
使用命令导入,同时,导入方式也可以分为五种,分别对应着导出的五种方式
1、“full=y”,全量导入数据库;
impdp user/passwd directory=data_dir dumpfile=expdp.dmp full=y
2、同名用户导入,从用户A导入到用户A;
impdp A/passwd schemas=A directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;
3、
①从A用户中把表table1和table2导入到B用户中;
impdp B/passwdtables=A.table1,A.table2 remap_schema=A:B directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;
②将表空间TBS01、TBS02、TBS03导入到表空间A_TBS,将用户B的数据导入到A,并生成新的oid防止冲突;
impdp A/passwd remap_tablespace=TBS01:A_TBS,TBS02:A_TBS,TBS03:A_TBS remap_schema=B:A FULL=Y transform=oid:n
directory=data_dir dumpfile=expdp.dmp logfile=impdp.log
4、导入表空间;
impdp sys/passwd tablespaces=tbs1 directory=data_dir dumpfile=expdp.dmp logfile=impdp.log
5、追加数据;
impdp sys/passwd directory=data_dir dumpfile=expdp.dmp schemas=system table_exists_action=replace logfile=impdp.log; --table_exists_action:导入对象已存在时执行的操作。有效关键字:SKIP,APPEND,REPLACE和TRUNCATE
# 7. oracle使用方向键和回退
oracle中sqlplus必备的rlwrap。安装后可以正常使用方向键和回退等。
https://gitee.com/mirrors/rlwrap
在安装rlwrap之前需要安装readline
yum install readline
yum install rlwrap
接着安装rlwrap
可以使用rlwrap的rpm包安装
# 8. oracle报错
emctl start dbconsole
报错信息:
OC4J Configuration issue. /u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_orcl-db-01_orcl not found.
解决办法:
$emca -config dbcontrol db -repos recreate;
注意:(重新创建DBCONSOLE:(如果是在windows下,要先到注册表删除DBCONSOLE(HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/OracleDBconsole)的服务,重启电脑))
根据提示,
先输入SID,
输入监听程序端口端口1521,
输入SYS密码,
输入DBSNMP密码,
输入SYSMAN 密码,
输入Y继续
完成。
输入startup,
#startup的输出提示:
SQL> statup
SP2-0042: unknown command "statup" - rest of line ignored.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/data/app/oracle/product/11.2.0/dbs/initORCL.ora'
根据提示,将ORACLEBASE/admin/数据库名称/pfile目录下的init.ora.xxx形式的文件copy到 ORACLE_BASE/admin/数据库名称/pfile目录下的init.ora.xxx形式的文件copy到ORACLE
B
ASE/admin/数据库名称/pfile目录下的init.ora.xxx形式的文件copy到ORACLE_HOME/dbs目录下initOracle.ora(根据startup提示)即可
cp /data/app/oracle/admin/orcl/pfile/init.ora.022019183329 initORCL.ora
expdp备份报错
ORA-39006: internal error
ORA-39213: Metadata processing is not available
sqlplus / as sqlplus
execute dbms_metadata_util.load_stylesheets;
报错解决
ORA-12547的故障解决思路
当出现上述错误时,通常需要检查以下内容
1、检查操作系统内核参数是否无误,检查当前会话的资源限制
2、确认$ORACLE_HOME/bin/oracle文件权限是否有问题
3、检查以下环境变量设置是否存在问题,注意环境变量LD_LIBRARY_PATH
4、检查$ORACLE_HOME/bin/oracle 和$ORACLE_HOME/rdbms/lib/config.o的文件大小
# 9.增删改查
# 9.1 查询
查看所有用户:
select * from dba_users;
select * from all_users;
select * from user_users;
查看用户或角色系统权限(直接赋值给用户或角色的系统权限):
select * from dba_sys_privs;
select * from user_sys_privs; (查看当前用户所拥有的权限)
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;
4.查看用户对象权限:
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
5.查看所有角色: select * from dba_roles;
6.查看用户或角色所拥有的角色:
select * from dba_role_privs;
select * from user_role_privs;
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
修改用户密码
alter user 用户名 identified by 新密码;
数据库的创建
安装完成后我们在CMD命令窗口中执行sqlplus命令,来打开终端,此时他会提示我们输入用户名和密码,这个时候用户可以输入sys,密码则是你在安装的时候设置的密码。
接下来我们创建一个用户:
CREATE USER user1 IDENTIFIED BY 1234;
我们给user1用户来授权:
GRANT CONNECT,RESOURCE,DBA TO user1;
上面我们创建了一个用户,并且授予了登录和DBA的权限,下面我们用user1来进行登录看看:
CONNECT user1@orcl;
会提示你输入密码,登录成功。注意,user1用户仅存在于orcl数据库中,因此,必须在CONNECT命令中明确指定用户名为user1@orcl。
Oracle创建数据库有三种方式:
用oracle dbca来创建
手工创建数据库(这个是比较复杂的)
使用oracle managed Field来创建
带着问题去学习
常用的查询方面的有:
Select 演示如何查询单个表中的数据。
排序方面有:
Order By 按升序或降序对查询的结果集进行排序。
过滤方面有:
Distinct 介绍如何消除查询输出中的重复行。
Where 演示如何为查询返回结果集中的行记录指定过滤条件。
And 组合两个或两个以上的布尔表达式,如果所有表达式都为true,则返回true。
Or 组合两个或两个以上的布尔表达式,如果其中一个表达式为true,则返回true。
Fetch 演示如何使用行限制子句限制查询返回的行数。
in 演示如何使用行限制子句限制查询返回的行数。
Between 基于一系列值(区间值)过滤数据。
Like 根据特定模式执行匹配。
链接表方面有:
Inner join 演示如何从表中查询具有与其他表匹配的行记录。
Left join 介绍左连接概念,并学习如何使用它选择左表中具有,但右表中不具有的行记录。
Right join 解释右连接概念,并演示如何从右表查询具有,但左表中不具有的行记录。
Cross join –介绍如何从多个表中构建笛卡尔乘积。
Self join 演示如何将表连接到自身以查询分层数据或比较同一个表中的行记录。
分组方面有:
Group By 演示如何将行分组为子组,并为每个分组应用聚合函数。
Having 演示如何过滤分组中的行记录。
子查询方面有:
子查询 - 介绍子查询的概念以及如何使用子查询来执行高级数据选择技术。
相关子查询 - 了解相关的子查询,它是一个依赖于外部查询返回的值的子查询。
EXISTS和NOT EXISTS - 检查子查询返回的行是否存在。
ANY,SOME和ALL - 将值与列表或子查询进行比较。
设置操作符
UNION - 演示如何将两个查询的结果合并为一个结果。
INTERSECT - 演示如何实现两个独立查询的结果的交集。
MINUS - 学习如何从一个结果集中减去另一个结果(也就是求差集)。
修改数据
INSERT - 学习如何在表中插入一行。
INSERT INTO SELECT - 从查询结果中将数据插入到表中。
INSERT ALL - 讨论多重插入语句,将多行插入到一个或多个表中。
UPDATE - 演示如何更新表的存在的数据值。
DELETE - 演示如何从表中删除一行或多行。
MERGE - 使用单个语句逐步完成插入,更新和删除操作。
数据定义:
CREATE TABLE - 演示如何在数据库中创建新表。
IDENTITY列 - 了解如何使用IDENTITY子句来定义表的标识列。
ALTER TABLE - 演示如何改变表的结构。
ALTER TABLE ADD列 - 显示如何将一个或多个列添加到现有表
ALTER TABLE MODIFY列 - 演示如何更改表中现有列的定义。
DROP COLUMN - 了解如何使用各种语句从表中删除一列或多列。
DROP TABLE - 演示如何从数据库中删除表。
TRUNCATE TABLE - 更快,更有效地删除表中的所有数据。
RENAME TABLE - 学习如何重命名表和处理其依赖对象的过程。
数据类型:
Oracle数据类型 - 内置Oracle数据类型的概述。
NUMBER - 介绍数字数据类型,并展示如何使用它为表定义数字列。
FLOAT - 通过实例来解释Oracle中的浮点数据类型。
CHAR - 了解固定长度的字符串类型。
NCHAR - 演示如何存储固定长度的Unicode字符数据,并解释CHAR和NCHAR数据类型之间的区别
VARCHAR2 - 向您介绍可变长度字符,并向您展示如何在表中定义可变长度字符列。
NVARCHAR2 - 了解如何在数据库中存储可变长度的Unicode字符。
DATE - 讨论日期和时间数据类型,并说明如何有效地处理日期时间数据。
TIMESTAMP - 介绍如何以小数秒精度存储日期和时间。
INTERVAL - 介绍区间数据类型,主要用来存储时间段。
TIMESTAMP WITH TIME ZONE - 了解如何使用时区数据存储日期时间。
约束:
主键 - 解释主键概念,并演示如何使用主键约束来管理表的主键。
外键 - 解释介绍外键概念,并演示如何使用外键约束来强制表之间的关系。
NOT NULL约束 - 演示如何确保列不接受NULL值。
UNIQUE约束 - 讨论如何确保存储在一列或一组列中的数据在整个表内的行之间是唯一的。
CHECK约束 - 在将数据存储到表中之前添加用于检查数据的逻辑的过程。
创建表空间
创建临时表空间
CREATE TEMPORARY TABLESPACE ttf_temp
TEMPFILE 'F:\oracledata\ttf_temp.dbf'
SIZE 50m
AUTOEXTEND on
NEXT 50m MAXSIZE 40960m
EXTENT MANAGEMENT LOCAL;
创建数据表空间
CREATE TEMPORARY TABLESPACE ttf_data LOGGING
DATAFILE 'F:\oracledata\ttf_data.dbf'
SIZE 50m
AUTOEXTEND on
NEXT 50m MAXSIZE 40960m
EXTENT MANAGEMENT LOCAL;
创建用户并指定表空间
CREATE USER USER1 IDENTIFIED BY 1234
DEFAULT TABLESPACE ttf_data
TEMPORARY TABLESPACE ttf_temp;
//给用户授予权限
GRANT CONNECT,RESOURCE,DBA to user1
创建表
创建表的一般语法格式如下:
CREATE TABLE <table_name> (
<column_name_1> <data_type_1>,
<column_name_2> <data_type_2>,
<column_name_N> <data_type_N>
);
====================================
示例如下:
CREATE TABLE authors (
id number(38),
name varchar2(100),
birth_date date,
gender varchar2(30)
);
创建表并且指定主键等约束:
//创建一个学生表
CREATE TABLE STU(
STUID NUMBER(10) PRIMARY KEY, //申明为主键
STUNAME VARCHAR2(20) NOT NULL , //不为null
STUSEX VARCHAR2(2) DEFAULT '男' CHECK(STUSEX IN('男','女'))
);
//创建一个课程表
CREATE TABLE COURSE(
COURSEID NUMBER(10) PRIMARY KEY,
COURSENAME VARCHAR2(20) NOT NULL,
COURSETYPE VARCHAR2(4)
);
//创建一个学生和课程的关联表
CREATE TABLE STU_COURSE(
ID NUMBER(10) PRIMARY KEY,
STUID NUMBER(10) REFERENCES STU(STUID), //外键
COURSEID NUMBER(10),
CONSTRAINT FF_COURSEid FOREIGN KEY(COURSEID) REFERENCES COURSE(COURSEID)
ON DELETE CASCADE //级联删除
)
添加数据--Insert
//插入的格式一般为
INSERT INTO table [(column1,column2,...)] VALUE (value1,value2,...)
INSERT INTO STU(id,name) VALUES(1,'张三');
//多表多行插入
INSERT [ALL] [condition_insert_clause]
[insert_into_clause values_clause] (subquery)
INSERT ALL
INTO stu(sid,sname) VALUES(ssid,ssname)
INTO tea(tid,tname) VALUES(ttid,ttname)
SELECT ssid,ssname,ttid,ttname,state FROM stu_tea WHERE state != 0
//有条件的INSERT
INSERT [ALL | FIRST]
WHEN condition THEN insert_into_clause values_clause
[WHEN condition THEN] [insert_into_clause values_clause]
......
[ELSE] [insert_into_clause values_clause]
Subquery;
INSERT ALL
WHEN id > 5 THEN INTO stu(sid,sname) VALUES(ssid,ssname)
WHEN id < 5 THEN INTO tea(tid,tname) VALUES(ttid,ttname)
ELSE INTO tt(sid,tid) VALUES(ssid,ttid)
SELECT ssid,ssname,ttid,ttname FROM stu_tea;
//旋转Insert(pivoting Insert)
create table sales_source_data (
employee_id number(6),
week_id number(2),
sales_mon number(8,2),
sales_tue number(8,2),
sales_wed number(8,2),
sales_thur number(8,2),
sales_fri number(8,2)
);
insert into sales_source_data values (176,6,2000,3000,4000,5000,6000);
create table sales_info (
employee_id number(6),
week number(2),
sales number(8,2)
);
看上面的表结构,现在将要sales_source_data表中的数据转换到sales_info表中,这种情况就需要使用旋转Insert
示例如下:
insert all
into sales_info values(employee_id,week_id,sales_mon)
into sales_info values(employee_id,week_id,sales_tue)
into sales_info values(employee_id,week_id,sales_wed)
into sales_info values(employee_id,week_id,sales_thur)
into sales_info values(employee_id,week_id,sales_fri)
select employee_id,week_id,sales_mon,sales_tue,
sales_wed,sales_thur,sales_fri
from sales_source_data;
从该例子可以看出,所谓旋转Insert是无条件 insert all 的一种特殊应用,但这种应用被oracle官方,赋予了一个pivoting insert的名称,即旋转insert
更新数据 -- Update
UPDATE 表名称 SET 列名称 = 新值 <WHERE 条件>
UPDATE stu SET sid = 1,sname = '张三' WHERE state = 0
删除数据 -- Delete
//语法如下
DELETE FROM <table/view> [WHERE
//注意事项:
//如果有外键关联,则删除数据之前,需先删除外键关联数据
DELETE FROM stu WHERE sid = 1;
//DELETE 与 TRUNCATE 应用区别:
1、对于删除整个表的所有数据时,delete并不会释放表所占用的空间
2、如果用户确定是 删除 整表的所有数据,那么使用 truncate table 速度更快
//删除所有学生信息,使用DELETE
DELETE FROM stu
//删除所有部门信息使用 TRUNCATE
TRUNCATE TABLE stu
查询语句 -- Select
//语法如下
SELECT column_1,column_2 FROM table_name;
//查询单个列的数据
SELECT sid FROM stu
//查询多个列
SELECT sid,sname FROM stu
//查询所有列的数据
SELECT * FROM stu
//分组查询
SELECT id,name,age FROM stu GROUP BY age
//排序查询,按照id降序排序
SELECT id,name,age FROM stu ORDER BY id DESC
//唯一查询 语法格式如下
SELECT DISTINCT column_1,column_2 FROM table_name
//多个条件查询 AND
SELECT id,name,age,state FROM stu WHERE age = 1 AND state = 0
//多个条件查询 OR
SELECT id,name,age,state FROM stu WHERE age = 1 OR age = 2
链接查询
//内链接 inner join
SELECT * FROM stu INNER JOIN course ON cid = ccid ORDER BY ccid DESC
//使用USING
//1.查询必须是等值连接。
//2.等值连接中的列必须具有相同的名称和数据类型。
SELECT * FROM stu INNER JOIN course USING(cid) ORDER BY cid DESC
----------------------------------------------------------------------------
//左链接
SELECT * FROM stu LEFT JOIN course ON cid == ccid ORDER BY cid DESC
//使用USING ,这里的c1和c2,在stu表和course表中都必须要相同类型的相同字段
SELECT * FROM stu LEFT JOIN course USING(c1,c2) ORDER BY cid
----------------------------------------------------------------------------
//右链接
SELECT * FROM stu RIGHT JOIN course ON cid = ccid ORDER BY cid DESC
//使用USING
SELECT * FROM stu RIGHT JOIN course USING(c1,c2) ORDER BY cid DESC
----------------------------------------------------------------------------
//笛卡尔积 CROSS JOIN
SELECT * FROM stu CROSS JOIN course
----------------------------------------------------------------------------
//自身链接
SELECT m.id ,c.name FROM stu AS m,LEFT JOIN stu AS c ON c.cid = m.pid
Oracle 常用分页
//1、通过MINUS分页
SELECT * FROM STU WHERE ROWNUM < 3 MINUS SELECT * FROM STU WHERE ROWNUM < 2
//2、通过ROWNUM分页
SELECT * FROM STU WHERE ROWNUM < 10
//查询前10条
SELECT * FROM (SELECT * FROM STU) WHERE ROWNUM <= 10
//3、通过BETWEEN分页 (查询1到10)
SELECT * FROM STU WHERE ROWNUM BETWEEN 1 AND 10
SELECT * FROM (SELECT a.*,ROWNUM RN FROM STU) WHERE RN <=10
Oracle 创建视图
视图的优点有如下:
对数据库的访问,因为视图可以有选择性的选取数据库里的一部分。
用户通过简单的查询可以从复杂查询中得到结果。
维护数据的独立性,试图可从多个表检索数据。
对于相同的数据可产生不同的视图。
视图分为简单视图和复杂视图:
简单视图只从单表里获取数据,复杂视图从多表;
简单视图不包含函数和数据组,复杂视图包含;
简单视图可以实现DML操作,复杂视图不可以。
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
//创建视图
CREATE OR REPLACE VIEW SV AS SELECT sid,sname FROM STU WITH READ ONLY
//查询视图
SELECT * FROM SV;
//查询视图定义
SELECT SV,text FROM SV;
语法解析:
OR REPLACE :若所创建的试图已经存在,则替换旧视图;
FORCE:不管基表是否存在ORACLE都会自动创建该视图(即使基表不存在,也可以创建该视图,但是该视图不能正常使用,当基表创建成功后,视图才能正常使用);
NOFORCE :如果基表不存在,无法创建视图,该项是默认选项(只有基表都存在ORACLE才会创建该视图)。
alias:为视图产生的列定义的别名;
subquery :一条完整的SELECT语句,可以在该语句中定义别名;
WITH CHECK OPTION :插入或修改的数据行必须满足视图定义的约束;
WITH READ ONLY :默认可以通过视图对基表执行增删改操作,但是有很多在基表上的限制(比如:基表中某列不能为空,但是该列没有出现在视图中,则不能通过视图执行insert操作),WITH READ ONLY说明视图是只读视图,不能通过该视图进行增删改操作。现实开发中,基本上不通过视图对表中的数据进行增删改操作。
删除视图
DROP VIEW 视图名称;
Oracle 中EXISTS 和 NOT EXISTS
EXISTS (sql 返回结果集为真)
NOT EXISTS (sql 不返回结果集为真)
//如果有值就返回 EXISTS
SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.ID)
//如果有值就返回 NOT EXISTS
SELECT * FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE A.ID = B.ID)
EXISTS 和 NOT EXISTS 用的也是比较多的,效率相对来收也比较优。
IN、NOT IN的用法
// IN的基本语法如下
SELECT columns
FROM tables
WHERE column1 in (value1, value2, .... value_n);
SELECT * FROM STU WHERE ID IN / NOT IN (SELECT ID FROM B WHERE state = 0)
//举个例子来说明 “exists” 和 “in” 的效率问题
SELECT * FROM B1 WHERE EXISTS(SELECT * FROM B2 WHERE B1.a = B2.a)
//B1数据量小而B2数据量非常大时, B1 << B2 时,查询效率高
SELECT * FROM B1 WHERE B1.a in (SELECT a FROM B2)
//B1的数据量非常大而B2数据量小时, B1 >> B2 时 查询效率高
自定义函数
//创建语法
create [or replace] function function_name
[(parameter_list)]
return datatype
{is/as}
[local_declarations]
begin
executable_statements;
[exception
exception_handlers;]
end;
说明:
function_name:函数名称。
parameter_list:函数列表,可选。
return 自居:指定函数的返回类型,不能指定大小。
local_declarations:局部变量声明,可选。
executable_statements:要执行的PL-SQL语句。
exception_handlers:异常处理,可选。
or repalce:是否覆盖,可选。
参数的模式有3种:(如果没有注明, 参数默认的类型为 in.)
in: 为只读模式, 在函数中, 参数的值只能被引用, 不能被改变;
out: 为只写模式, 只能被赋值, 不能被引用;
in out: 可读可写.
//注意
1.在Oracle自定义函数中, else if 的正确写法是 elsif 而不是 else if
2.使用 if 需要加 then "if 条件 then 操作"
/////////////////////////////////////////////////////////////
例如,读入两个值,返回比较大的值
create or replace function get_max(para1 in number, para2 in number)
return number
as
begin
if para1 > para2 then
return para1;
else
return para2;
end if;
end get_max;
//使用
select get_max(666, 333) from dual;
///////////////////////////////////////////////////////////////
CREATE or REPLACE FUNCTION useEasy(a1 in number,a2 in number) RETURN NUMBER IS
fres NUMBER;
BEGIN
fres := a1 + a2;
RETURN fres;
END useEasy;
//调用
select useEasy(1,30) from dual;
///////////////////////////////////////////////////////////////
CREATE OR REPLACE FUNCTION get_emp_id(usernameq varchar2) RETURN NUMBER
AS
sid emp.id%TYPE;
BEGIN
SELECT id INTO sid FROM emp WHERE name = usernameq;
RETURN sid;
END get_emp_id;
//调用
select get_emp_id('张三') from dual;
//注意
在Oracle的存储过程和函数中,其实IS和AS是同义词,没有什么区别。
还有在自定义类型(TPYE)和包(PACKAGE)时,使用IS和AS也并没有什么区别。
但是在创建视图(VIEW)时,只能使用AS而不能使用IS。
在声明游标(CURSOR)时,只能使用IS而不能使用AS。
**1.**前言
有一定Linux/Unix操作系统、Oracle数据库基础的工程人员和维护人员,证券公司信息技术人员等。本手册可作为工具,Oralce运维提供帮助。
**2.**简单命令使用
2.1进入SQL*Plus
$sqlplus 用户名/密码
2.2退出SQL*Plus
SQL>exit
2.3在sqlplus下得到帮助信息
列出全部SQL命令和SQL*Plus命令
SQL>help
列出某个特定的命令的信息
SQL>help 命令名
2.4显示表结构命令DESCRIBE
SQL>DESC 表名
2.5SQL*Plus中的编辑命令
显示SQL缓冲区命令
SQL>L
修改SQL命令
首先要将待改正行变为当前行
SQL>n
用CHANGE命令修改内容
SQL>c/旧/新
重新确认是否已正确
SQL>L
使用INPUT命令可以在SQL缓冲区中增加一行或多行
SQL>i
SQL>输入内容
2.6调用外部系统编辑器
SQL>edit 文件名
可以使用DEFINE命令设置系统变量EDITOR来改变文本编辑器的类型,在login.sql文件中定义如下一行
DEFINE_EDITOR=vi
2.7运行命令文件
SQL>START test
SQL>@test
2.8关于侦听
1、新建/修改/删除侦听
以oracle用户登录,运行netca,会跳出图形配置界面。
2、打开侦听
lsnrctl start
3、查看侦听
Lsnrctl status
4、关闭侦听
lsnrctl stop
3.ORACLE****的启动和关闭
3.1在单机环境下
要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下
su - oracle
启动oracle 数据库命令:
$sqlplus/nolog
SQL*Plus: Release 10.2.0.1.0- Production on 星期一 7月 16 16:09:40 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>conn / as sysdba
已连接到空闲例程。
SQL>startup
ORACLE 例程已经启动。
Total System Global Area 369098752 bytes
Fixed Size 1249080 bytes
Variable Size 201326792 bytes
Database Buffers 159383552 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
数据库已经打开。
关闭 oracle 数据库命令:
$ sqlplus/nolog
SQL*Plus: Release 10.2.0.1.0- Production on 星期一 7月 16 16:08:10 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>conn / as sysdba
已连接。
SQL>shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>
3.2在双机环境下
要想启动或关闭crs服务必须首先切换到root用户,如下
su - root
启动crs 服务:
启动CRS
#$CRS_HOME/crs/bin/crsctlstart crs
查看CRS状态
#$CRS_HOME/crs/bin/crsctlcheck crs
关闭CRS
#$CRS_HOME/crs/bin/crsctlstop crs
查看CRS内部各资源状态
#$CRS_HOME/crs/bin/crs_stat–t
启动数据库服务
# srvctlstart database -d tdb #tdb为数据库名
**4.**数据库管理员日常工作
4.1检查alterSID.log
这个日志文件位于参数BACKGROUND_DUMP_DEST指定的目录,可能通过以下命令来查看。
SQL> SHOW PARAMETER background_dump_dest
在出现大故障前,数据库有可能会报一些警告或错误信息,应该充分重视这些信息,未雨绸缪,避免更大错误的发生。
检查alterSID.log 的什么内容。
检查数据库是否出现过宕机(可能在晚间重启而维护人员不知道)
Oracle 出错信息,通过$grep ORA- alterSID.log查找
产品有关的问题:ORA-00600/ORA-07445等错误
相应的TRACE文件
4.2环境确认
数据库实例是否正常工作
SQL >select status from v$instance;
数据库监听器是否正常工作
-$ lsnrctl status
是否存在故障表空间
-SQL> select tablespace_name,status fromdba_tablespace;
控制文件、日志文件是否正常
SQL>select * from v$controlfile;
SQL>select * from v$log;
SQL>select * from v$logfile;
性能监测
每天按业务峰值情况,对数据库性能数据进行定时采集
每天检查数据库的主要性能指标
每天检查最消耗资源的SQL语句变化情况。
每天检查是否有足够的资源
检查所有表空间的剩余情况
识别出一些异常的增长
检查CPU、内存、网络等是否异常
**5.**数据库日常操作SQL
5.1查看表空间物理文件的名称及大小
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) filesize
from dba_data_files
orderby tablespace_name;
5.2查询表空间使用情况
select a.tablespace_name "表空间名称",
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc)* 100, 2) "占用率(%)",
round(a.bytes_alloc / 1024 / 1024, 2) "容量(M)",
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) "空闲(M)",
round((a.bytes_alloc -nvl(b.bytes_free, 0)) / 1024 / 1024, 2) "使用(M)",
to_char(sysdate, 'yyyy-mm-ddhh24:mi:ss')"采样时间"
from (selectf.tablespace_name,
sum(f.bytes)bytes_alloc,
sum(decode(f.autoextensible,'YES', f.maxbytes, 'NO', f.bytes))maxbytes
from dba_data_files f
groupbytablespace_name) a,
(select f.tablespace_name, sum(f.bytes)bytes_free
from dba_free_space f
groupbytablespace_name) b
where a.tablespace_name =b.tablespace_name
orderby2desc;
5.3查询表空间的碎片程度
select tablespace_name, count(tablespace_name)
from dba_free_space
groupby tablespace_name
havingcount(tablespace_name) > 10;
altertablespace HS_USER_DATA coalesce;
altertablenamedeallocateunused;
5.4碎片程度
select tablespace_name, count(tablespace_name)
from dba_free_space
groupby tablespace_name
havingcount(tablespace_name) > 10;
altertablespacenamecoalesce;
altertablenamedeallocateunused;
createorreplaceview ts_blocks_v as
select tablespace_name,block_id, bytes, blocks, segment_name
from dba_free_space
unionall
select tablespace_name,block_id, bytes, blocks, segment_name
from dba_extents;
select * from ts_blocks_v;
select tablespace_name, sum(bytes), max(bytes), count(block_id)
from dba_free_space
groupby tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name, COUNT(*) extents
FROM dba_segments
WHERE owner NOTIN('SYS', 'SYSTEM')
GROUPBY segment_name
HAVINGCOUNT() = (SELECTMAX(COUNT())
FROMdba_segments
GROUPBY segment_name);
5.5查看回滚段名称及大小
select segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) InitialExtent,
(next_extent / 1024) NextExtent,
max_extents,
v.curext CurExtent
From dba_rollback_segs r,v$rollstat v
Where r.segment_id = v.usn(+)
orderby segment_name;
5.6查看控制文件
selectnamefromv$controlfile;
5.7查看日志文件
selectmemberfromv$logfile;
5.8查看表空间的使用情况
selectsum(bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
groupby tablespace_name;
SELECT A.TABLESPACE_NAME,
A.BYTES TOTAL,
B.BYTES USED,
C.BYTES FREE,
(B.BYTES * 100) / A.BYTES "% USED",
(C.BYTES * 100) / A.BYTES "% FREE"
FROMSYS.SM$TS_AVAIL A, SYS.SM$TS_USEDB, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME =B.TABLESPACE_NAME
AND A.TABLESPACE_NAME =C.TABLESPACE_NAME;
5.9查看数据库对象
select owner, object_type, status, count(*)count#
from all_objects
groupby owner,object_type, status;
5.10查看数据库的版本
Select version
FROM Product_component_version
Where SUBSTR(PRODUCT, 1, 6) = 'Oracle';
5.11查看Oracle字符集
select * fromsys.props$ wherename= 'NLS_CHARACTERSET';
5.12在某个用户下找所有的索引
select user_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name
from user_ind_columns,user_indexes
where user_ind_columns.index_name= user_indexes.index_name
and user_ind_columns.table_name= user_indexes.table_name
orderbyuser_indexes.table_type,
user_indexes.table_name,
user_indexes.index_name,
column_position;
5.13表、索引的存储情况检查
select segment_name, sum(bytes), count(*)ext_quan
from dba_extents
where tablespace_name = '&tablespace_name'
and segment_type = 'TABLE'
groupby tablespace_name,segment_name;
select segment_name, count(*)
from dba_extents
where segment_type = 'INDEX'
and owner = '&owner'
groupby segment_name;
5.14查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode FromV$Database;
5.15显示所有数据库对象的类别和大小
selecttype,
count(name)num_instances,
sum(source_size)source_size,
sum(parsed_size)parsed_size,
sum(code_size) code_size,
sum(error_size) error_size,
sum(source_size) + sum(parsed_size)+ sum(code_size) +
sum(error_size)size_required
from dba_object_size
groupbytype
orderby1;
5.16设置RAC为归档模式?
步骤:
1.以SYSDBA身份登陆2个节点,执行
alter system set cluster_database=false scope =spfile sid=’*’;
设置归档路径
alter system set log_archive_start=true scope=spfile;
2.2个节点
shutdown immediate
3.在一个节点上执行
startup mount
alter database archivelog;
shutdown immediate;
alter database open;
alter system set cluster_database=true scope =spfile sid=’*’;
shutdown immediate
4、分别启动2个节点,修改完毕
6.AWR****报告
与9i 中的statspack相似,awr报告也需要两个快照,才能生成这两个时间点之间的性能报告。
$sqlplus/ as sysdba
生成快照一(10g中自动会每个整点都会生成一个快照)
SQL>exec dbms_workload_repository.create_snapshot();
(间隔一段时间)生成快照二
SQL>exec dbms_workload_repository.create_snapshot();
生成报告
SQL>@?/rdbms/admin/awrrpt.sql
7.Troubleshooting
常用性能相关SQL,监控数据库性能的SQL语句。
7.1监控事务的等待
select event,
sum(decode(wait_Time, 0, 0, 1)) "Prev",
sum(decode(wait_Time, 0, 1, 0)) "Curr",
count(*) "Totol"
from v$session_Wait
groupby event
orderby4;
7.2查看一些等待信息:
select sid,event
from v$session_wait
where event notlike'SQL%'
and event notlike'%ipc%';
查看是否存在下面等常见的等待事件:
buffer busy waits,
free buffer waits,
db file sequentialread,
db file scatteredread,
enqueue,latch free,
log file parallelwrite,
log file sync
7.3查看等待(wait)情况
SELECT v$waitstat.class,
v$waitstat.countcount,
SUM(v$sysstat.value)sum_value
FROM v$waitstat, v$sysstat
WHERE v$sysstat.nameIN('dbblock gets', 'consistent gets')
groupby v$waitstat.class,v$waitstat.count;
7.4回滚段查看
selectrownum,
sys.dba_rollback_segs.segment_nameName,
v$rollstat.extentsExtents,
v$rollstat.rssize Size_in_Bytes,
v$rollstat.xacts XActs,
v$rollstat.gets Gets,
v$rollstat.waits Waits,
v$rollstat.writes Writes,
sys.dba_rollback_segs.statusstatus
from v$rollstat, sys.dba_rollback_segs,v$rollname
where v$rollname.name(+) =sys.dba_rollback_segs.segment_name
and v$rollstat.usn(+) =v$rollname.usn
orderbyrownum;
7.5回滚段的争用情况
selectname, waits, gets, waits / gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
7.6监控表空间的I/O 比例
select df.tablespace_name name,
df.file_name "file",
f.phyrds pyr,
f.phyblkrd pbr,
f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f,dba_data_files df
where f.file# = df.file_id
orderbydf.tablespace_name;
7.7监控文件系统的I/O 比例
select substr(a.file#, 1, 2) "#",
substr(a.name, 1, 30) "Name",
a.status,
a.bytes,
b.phyrds,
b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
7.8监控 SGA 的命中率
select a.value + b.value"logical_reads",
c.value"phys_reads",
round(100 * ((a.value + b.value) - c.value) /(a.value + b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b,v$sysstat c
where a.statistic# = 38 --physical read total multi block requests
and b.statistic# = 39 --physical read total bytes
and c.statistic# = 40; --physical write total IO requests
7.9监控 SGA 中字典缓冲区的命中率
select parameter,
gets,
Getmisses,
getmisses / (gets + getmisses) * 100 "miss ratio",
(1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) *100 "Hit ratio"
from v$rowcache
where gets + getmisses <> 0
groupby parameter, gets,getmisses;
7.10监控 SGA 中共享缓存区的命中率,应该小于1%
selectsum(pins) "Total Pins",
sum(reloads) "TotalReloads",
sum(reloads) / sum(pins)libcache
from v$librarycache;
selectsum(pinhits - reloads) / sum(pins) * 100 "hitradio",
sum(reloads) / sum(pins)"reload percent"
from v$librarycache;
7.11临控 SGA 中重做日志缓存区的命中率,应该小于1%
SELECTname,
gets,
misses,
immediate_gets,
immediate_misses,
Decode(gets, 0, 0, misses / gets * 100) ratio1,
Decode(immediate_gets +immediate_misses,
0,
0,
immediate_misses /(immediate_gets + immediate_misses) * 100) ratio2
FROM v$latch
WHEREnameIN ('redo allocation', 'redo copy');
7.12监控内存和硬盘的排序比率,最好使它小于 0.10,增加 sort_area_size
SELECTname, value
FROM v$sysstat
WHEREnameIN ('sorts (memory)', 'sorts (disk)');
7.13监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address = b.address
orderby address, piece;
7.14监控字典缓冲区
SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSESWHILE EXECUTING",
(SUM(PINS - RELOADS)) / SUM(PINS)"LIB CACHE"
FROM V$LIBRARYCACHE;
SELECT SUM(GETS) "DICTIONARY GETS",
SUM(GETMISSES) "DICTIONARYCACHE GET MISSES",
(SUM(GETS - GETMISSES - USAGE -FIXED)) / SUM(GETS) "ROW CACHE"
FROM V$ROWCACHE;
“LIB CACHE“与“ROW CACHE”越接近1.00超好,不要低于0.90。否则需要调大SGA的空间。
7.15查看Lock
select s.osuser,
l.sid,
s.serial#,
s.username,
s.terminal,
decode(l.type,
'TM',
'TM - DML Enqueue',
'TX',
'TX - Trans Enqueue',
'UL',
'UL - User',
l.type || ' - Other Type')LOCKTYPE,
substr(t.name, 1, 10) OBJECT,
u.name owner,
l.id1,
l.id2,
decode(l.lmode,
1,
'No Lock',
2,
'Row Share',
3,
'Row Exclusive',
4,
'Share',
5,
'Shr Row Excl',
6,
'Exclusive',
null) lmode,
decode(l.request,
1,
'No Lock',
2,
'Row Share',
3,
'Row Excl',
4,
'Share',
5,
'Shr Row Excl',
6,
'Exclusive',
null) request
from v$lock l, v$session s, sys.user$u, sys.obj$ t
where l.sid = s.sid
and s.type != 'BACKGROUND'
and t.obj# = l.id1
and u.user# = t.owner#;
7.16捕捉运行很久的SQL
select username,
sid,
opname,
round(sofar * 100 / totalwork, 0) || '%'asprogress,
time_remaining,
sql_text
from v$session_longops, v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value =hash_value;
7.17查看数据表的参数信息
SELECT partition_name,
table_name,
high_value,
high_value_length,
tablespace_name,
pct_free,
pct_used,
ini_trans,
max_trans,
initial_extent,
next_extent,
min_extent,
max_extent,
pct_increase,
FREELISTS,
freelist_groups,
LOGGING,
BUFFER_POOL,
num_rows,
blocks,
empty_blocks,
avg_space,
chain_cnt,
avg_row_len,
sample_size,
last_analyzed
FROMsys.dba_tab_partitions
WHERE table_owner = 'HS_HIS'ANDtable_name = 'HISBANKTRADE'
--WHEREtable_name = :tname AND table_owner = :towner
ORDERBYpartition_position,partition_name;
7.18查看还没提交的事务
select * from v$locked_object;
select * from v$transaction;
7.19查找object为哪些进程所用
select p.spid,
s.sid,
s.serial# serial_num,
s.username user_name,
a.type object_type,
s.osuser os_user_name,
a.owner,
a.object object_name,
decode(sign(48 - command),
1,
to_char(command),
'Action Code #' ||to_char(command)) action,
p.program oracle_process,
s.terminal terminal,
s.program program,
s.status session_status
from v$session s, v$access a,v$process p
where s.paddr = p.addr
and s.type = 'USER'
and a.sid = s.sid
and a.object = 'FUNDREAL'
orderby s.username,s.osuser;
7.20查看catched object
SELECT owner,
name,
db_link,
namespace,
type,
sharable_mem,
loads,
executions,
locks,
pins,
kept
FROM v$db_object_cache whereowner LIKE'HS_%';
7.21查看V$SQLAREA
SELECT SQL_TEXT,
SHARABLE_MEM,
PERSISTENT_MEM,
RUNTIME_MEM,
SORTS,
VERSION_COUNT,
LOADED_VERSIONS,
OPEN_VERSIONS,
USERS_OPENING,
EXECUTIONS,
USERS_EXECUTING,
LOADS,
FIRST_LOAD_TIME,
INVALIDATIONS,
PARSE_CALLS,
DISK_READS,
BUFFER_GETS,
ROWS_PROCESSED
FROM V$SQLAREA;
7.22有关connection的相关信息
l查看有哪些用户连接
select s.sid,
s.serial# serial_num,
s.osuser os_user_name,
decode(sign(48 - command),
1,
to_char(command),
'Action Code #' ||to_char(command)) action,
p.program oracle_process,
status session_status,
s.terminal terminal,
s.program program,
s.username user_name,
s.fixed_table_sequenceactivity_meter
from v$session s, v$process p
where s.paddr = p.addr
and s.type = 'USER'orderbys.username, s.osuser;
l2)根据v.sid查看对应连接的资源占用等情况
select n.name, v.value, n.class,n.statistic#
from v$statname n, v$sesstat v
where v.sid = &sid
and v.statistic# = n.statistic#
orderby n.class,n.statistic#;
l3)根据sid查看对应连接正在运行的sql
select/*+ PUSH_SUBQ */
command_type,
sql_text,
sharable_mem,
persistent_mem,
runtime_mem,
sorts,
version_count,
loaded_versions,
open_versions,
users_opening,
executions,
users_executing,
loads,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed,
sysdate start_time,
sysdate finish_time,
'>' || address sql_address,
'N' status
from v$sqlarea
where address = (selectsql_address from v$session where sid = &sid);
# 10.参数优化
# 10.1 设置内存相关参数
OLTP 比较注重读
OLAP 读写都比较集中
OLTP数据库:SGA=系统内存*70%80%, PGA=SGA(10%~20%)
OLAP数据库:SGA=系统内存*60%80%, PGA=SGA(45%~65%)
OLTP SGA的大小设置为不超过物理内存的一半,PGA不超过3GB较为合适
设置进程相关参数
alter system set processes=500;
设置DB_FILES参数
默认值为200个,该参数为静态参数,需要重启数据库才能生效,设置过大的db_files参数也容易导致消耗过多的共享池和PGA内存
设置BLOCK_SIZE和DB_FILE_MULTIBLOCK_READ_COUNT参数
参数DB_BLOCK_SIZE表示Oracle默认的数据块大小,Oracle 11G中默认值为8KB
一个典型的数据库参数文件
*.processes=500
*.job_queue_processes=10
*.sga_target=16384M
*.db_block_size=8192
*.db_files=500
*.open_cursors=500
*.session_cached_cursors=100
*.undo_management='AUTO'
*.undo_tablespace= 'undotbs'
1、生产数据库一般都有自动循环备份机制,所以,你首先应该问一下他们的备份策略 2、SHOW PARAMETERS SGA; 查看一下sga_max_size,sga_target的大小,一般就是要调这个参数的。 3、如果是windows服务器,可以直接调,如果是linux下,还要调节内核参数,这个你google一下 4、楼上的兄弟说的非常好,一定要备份控制文件和参数文件,否则一旦你调了参数,可能就起不来了,这时候参数文件就是救命的灵丹妙药了。 5、show parameters sga; 建议12G(服务器内存32G下) show parameters session; 4000 show parameters process; 2000 show parameters open_cursors; 2600 show undo_retention; 7200 6、修改参数alter system set parameter="values' 7、要搞清楚spfile和pfile的区别,如果是裸设备,嘿嘿,一旦参数调节后,数据库起不来,而spfile是在裸设备上,你就惨了。准备restore,recover数据库吧。 所以,一定要搞到pfile再动手,即使改参数失败了,也可以startup pfile='path/你的pfile'来启动数据库再改回来。 解决前,一定要备份数据库, 或者冷备份,(关机后拷贝 所有的/oradata下文件) 或者热备份(exp/rman) 建议你冷备份加上exp备份比较保险。 修改sga参数的例子如下 把oracle 的线程数增大。 命令: SQL> alter system set processes=1000 scope=spfile; 调整SGA SQL> alter system set sga_max_size=10240M scope=spfile; SQL>alter system set job_queue_processes=15 scope=spfile; SQL>alter system set cursor_sharing=force; 修改归档空间大小 SQL>alter system set db_recovery_file_dest_size =200G scope=both; Oracle 服务器参数文件spfile_files 在什么路径下 一般在$ORACLE_HOME/dbs下 可以登录数据库查看: sqlplus '/as sysdba' SQL>show parameter spfile /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora 无备份的情况 有PFILE,可以直接恢复 如果当前数据库是以SPFILE启动的,重建SPFILE需要改名: SQL > Create spfile='/opt/oracle/product/10.2.0/dbs/spfile1.ora' from pfile=’/ opt/oracle/admin/order/scripts/init.ora’; $ Mv spfile1.ora spfileORCL.ora 否则可以直接新建同名SPFILE: SQL > Create spfile='/opt/oracle/product/10.2.0/dbs/spfileORCL.ora' from pfile='/ opt/oracle/admin/order/scripts/init.ora';
11g的处理方式:
SQL > Create spfile = '
数据文件管理
1、单个数据文件不宜过大。对于自动扩展的数据文件最好设置MAXBYTES属性,一般建议单个数据文件不超过16GB
2、对于自动扩展的数据文件,如果BLOCK SIZE为8KB,则数据文件最大不能超过32GB
3、不要在业务高峰期添加数据文件。如果添加数据文件的会话挂起,不要随意KILL、Ctrl+C或硬性重启数据库。递归操作不能很好地保持事务原子性
# 11.oracle性能优化方法论
# 11.1 数据库性能优化的目标
1、sql语句大规模优化,应用架构优化(难度最大,耗时最长)
2、数据库并发加大,数据量增多引导起的,增加硬件投入来解决性能问题,优化难度一般
3、突发性的数据库性能故障优化。难度最低,但需要快速解决
4、事前优化,指的是系统设计、开发、产品优化、平台优化的全过程都很重要。实践经验表明,事前优化对系统的平衡运行有着根本性的影响,而且优化代价也最低。
5、事后优化,指的是业务系统上线后的优化。由于事后优化受到宕机时间、代码 版本控制、代码内核不容易更改等因素影响,所以事后优化往往只能缓解系统性能下降的症状,治标不治本。如果爆发出大规模的性能问题,通常都会是灾难性的。
一个性能优化项目的目标如下:
增大数据库吞吐量。如通过数据库优化增加每秒执行的事务数。
高效利用服务器硬件资源。如开启并行充分 利用CPU和存储I/O资源 加快执行速度
# 11.2 建立数据库性能标准
各项指标,包括主机资源和数据库资源的消耗情况。
1、技术基线,主要指的是数字指标。通常由(CPU,内存,I/O,网络等)、并发性资源(系统解析数、事务数、归档量等)、TOP SESSION、TOP EVENT、TOP SQL组成
2、感官基线,主要指的是人为感觉。比如在前端 单击业务按钮正常的情况下应该在多少时间内返回结果
数据库优化的成果可以用两种方式表示:一种是技术成果,通常反映数据库性能统计值 来直观地表示数据库性能的状态;另外一种是用户体验,通常以用户的使用感觉来表示数据库性能状态
# 11.3 寻找关键变化
系统性能是什么时候开始变坏的
所有业务模块都变坏了,还是局部模块变坏
在变坏的时间点之前是否做过特殊的操作
业务部门是否开展过什么特殊活动
以前是否发生过同类型的性能故障
硬件是否出现了故障
# 11.4 检查操作系统资源
1、查看CPU资源
2、查看内存资源
3、查看I/O资源
Oracle性能优化基本方法包括一下几个步骤,包括:
1)设立合理的Oracle性能优化目标。
2)测量并记录当前的Oracle性能。
3)确定当前Oracle性能瓶颈(Oracle等待什么、哪些SQL语句是该等待事件的成分)。
4)把等待事件记入跟踪文件。
5)确定当前的OS瓶颈。
6)优化所需的成分(应用程序、数据库、I/O、争用、OS等)。
7)跟踪并实施更改控制过程。
8)测量并记录当前性能
9)重复步骤3到7,直到满足优化目标
下面来一一详述。
1.设立合理的Oracle性能优化目标
重点:关于设立目标的最重要的一点是它们必须是可量化和可达到的。
方法:目标必须是当前性能和所需性能的的陈述形式的语句。
2.测量并记录当前Oracle性能重点:
1)需要在峰值活动时间获得当前系统性能快照
2)关键是要在出现性能问题的时间段内采集信息
3)必须在合理的时间段上采集,一般在峰值期间照几个为期15分钟的快照
3.确定当前Oracle性能瓶颈重点:从Oracle 等待接口v$system_event、v$session_event和v$session_wait中获得等待事件,进而找出影响性能的对象和sql语句。方法如下:
1)首先,利用v$system_event视图执行下面的查询查看数据库中某些常见的等待事件:
select * from v$system_event
where event in ('buffer busy waits',
'db file sequential read',
'db file scattered read',
'enqueue',
'free buffer waits',
'latch free',
'log file parallel write',
'log file sync');
2)接着,利用下面对v$session_event和v$session视图进行的查询,研究具有对上面显示的内容有贡献的等待事件的会话:
select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
from v$session s,v$session_event se
where s.sid = se.sid
and se.event not like 'SQL*Net%'
and s.status = 'ACTIVE'
and s.username is not null;
3)使用下面查询找到与所连接的会话有关的当前等待事件。这些信息是动态的,为了查看一个会话的等待最多的事件是什么,需要多次执行此查询。
select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait SEC_IN_WAIT
from v$session s,v$session_wait sw
where s.sid = sw.sid
and sw.event not like 'SQL*Net%'
and s.username is not null
order by sw.wait_time desc;
4)查询会话等待事件的详细信息
select sid,event,p1text,p1,p2text,p2,p3text,p3
from v$session_wait
where sid between &1 and &2
and event not like '%SQL%'
and event not like '%rdbms%';
5)利用P1、P2的信息,找出等待事件的相关的段
select owner,segment_name,segment_type,tablespace_name
from dba_extents
where file_id = &fileid_in
and &blockid_in between block_id and block_id + blocks - 1;
6)获得操作该段的sql语句:
select sid, getsqltxt(sql_hash_value,sql_address)
from v$session
where sid = &sid_in;
7)getsqltxt函数
8)至此已经找到影响性能的对象和sql语句,可以有针对性地优化
4.把等待事件记入跟踪文件
重点:如果在跟踪系统上的等待事件时,由于某种原因遇到了麻烦,则可以将这些等待事件记入一个跟踪文件。方法如下:
1)对于当前会话:
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 12';
2)执行应用程序,然后在USER_DUMP_DEST指出的目录中找到跟踪文件。
3)查看文件中以词WAIT开始的所有行。
4)对于其它的会话
5)确定会话的进程ID(SPID)。下面的查询识别出名称以A开始的所有用户的会话进程ID:
select S.Username, P.Spid from V$SESSION S, V$PROCESS P
where S.PADDR = P.ADDR and S.Username like 'A%';
6)以sysdba进入sqlplus执行
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
oradebug setospid
oradebug unlimit
oradebug event 10046 trace name context forever, level X /* Where X = (1,4,8,12) */
7)跟踪某个时间间隔得会话应用程序。
8)在USER_DUMP_DEST 的值指出的目录中利用SPID查看跟踪文件
9)查看文件中以词WAIT开始的所有行。
5.确定当前OS瓶颈1)Windows NT上的监控
使用控制面板-〉管理工具-〉性能即可
2)UNIX上的监控
使用通用性的工具,包括sar、iostat、cpustat、mpstat、netstat、top、osview等。
6.Oracle性能优化所需的成分(应用程序、数据库、I/O、争用、OS等)。
7.跟踪并实施更改控制过程。
8.测量并记录当前Oracle性能
9.重复步骤3到7,直到满足优化目标
【编辑推荐】
简单概述Oracle性能测试 (opens new window)
Oracle性能在调整中的要点介绍 (opens new window)
使用资源管理器优化Oracle性能 (opens new window)
Oracle性能优化借助分区技术实现 (opens new window)
浅谈Oracle性能优化可能出现的问题 (opens new window)
# 12.表空间配置
查询表空间
select * from dba_tablespaces;
查询数据文件
select * from dba_data_files
oracle查询所有数据文件位置
select name from v$datafile;
可以查看到控制文件存放位置
select name from v$controlfile;
查询表空间是否为自增
select tablespace_name,file_name,autoextensible from dba_data_files where autoextensible='YES';
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;
1、查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
3、查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
oracle表空间设置自动扩展
开启自动扩展功能语法:
alter database datafile '对应的数据文件路径信息' autoextend on;
关闭自动扩展功能语法:
alter database datafile '对应的数据文件路径信息' autoextend off;
数据文件设置
--创建表空间,数据文件为'F:\app\zang\oradata\orcl\charge_zang.dbf',初始大小50M,递增10M,最大递增到2G
create tablespace charge_zang datafile 'F:\app\zang\oradata\orcl\charge_zang.dbf'
size 50M
autoextend on next 10M
maxsize 2048M;
修改表空间已经存在的数据文件大小
ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ex_data' AUTOEXTEND ON NEXT 50M;
注:该方法是不修改原来表空间的前提下进行的。
放开表空间大小限制
ALTER USER YTH_DATA_TBS QUOTA UNLIMITED ON YTH_DATA_TBS;
--为表空间增加数据文件
alter tablespace JC_DATA
add datafile 'F:\app\oracle\oradata\orcl\JC_DATA02.dbf' size 50m
autoextend on next 50m maxsize 32767m;
注:该命令修改原来的表空间。
1. 2. 扩大****db_block_size
根据oracle的算法,我们很容易想到这个解决方法。数目衡定,但是db_block_size可以更改(db_block_size的最大大小为32KB)。如果把db_block_size扩大到32KB,那么我们的系统就可以支持单个数据文件最大128GB。
这个方案听起来好像很迷人,但是实际上并不是那么回事。因为要修改db_block_size并不是很容易的事。因为这个db_block_size在创建实例的时候就要指定。而且不能通过简单修改参数来指定db_block_size。db_block_size的默认值为8192 bytes,是不能被用户修改的。因为db_block_size对应于一个实例,所以意味着在数据库创建(建库)以后是不能修改的,如需修改,可行的方式是重新建库并把原库的数据export到新库。当然最好的方式是在建数据库之前就规划好,一般如果是OLTP系统,可以保持默认值;OLAP环境可以考虑适当调大。
不幸的是, 不同操作系统不同Oracle版本对其物理文件的最大值限制是不一样的. 如果物理文件超过了某 个限制大小, ORACLE数据库将会出现内核错误并崩溃.
2. 3. 创建bigfile表空间
在oracle11g中引进了bigfile表空间,他充分利用了64位CPU的寻址能力,使oracle可以管理的数据文件总量达到8EB。单个数据文件的大小达到128TB,即使默认8K的db_block_size也达到了32TB。
创建bigfile的表空间使用的sql语句和创建表空间的语句使用基本相同。
create bigfile tablespace···
需要注意的是使用bigfile表空间,它只能支持一个数据文件。也就是说这个文件的最大大小就是表空间最大大小,你不可能通过增加数据文件来扩大该表空间的大小。
# 13.数据库的物理备份
拒绝备份的理由:数据库太大,没有空间备份等
充足的空间是数据库备份的重要前提,但是即使系统空间紧张,至少还是要备份重要的表、业务表结构或者是SYSTEM表空间下的数据文件
如果数据库处于关闭状态,那么物理备份数据库和备份普通的文件没有任何区别,即只需要将数据文件、在线日志文件、控制文件物理复制到其他地方即可。
正在运行的数据库备份需要考虑一致性的问题,一种是使用操作系统命令备份,另一种是通过RMAN命令备份。使用操作系统命令主要用于数据库的冷备份和手动热备份。