orale简介

所有文档:https://docs.oracle.com/cd/E11882_01/nav/portal_booklist.htm
下载地址:https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

Oracle数据库是相对于其他数据库来说比较难的一个。Oracle Database,又名Oracle RDBMS,简称Oracle。是甲骨文公司推出的一款关系数据库管理系统。Oracle数据库系统是目前世界上流行的关系数据库管理系统,拥有可移植性好、使用方便、功能强等优点,在各类大、中、小、微机环境中都适用。Oracle是一种高效率、可靠性好的、适应高吞吐量的数据库解决方案。下面我们来具体的学习一下:

首先我们来看什么是Oracle数据,他的组成都有哪些?

Oracle数据库服务器由一个数据库和至少一个数据库实例组成。 数据库是一组存储数据的文件,而数据库实例则是管理数据库文件 的内存结构。此外,数据库是由后台进程组成。数据库和实例是紧密相连的,所以我们一般说的Oracle数据库,通常指的就是实例和数据库。

Oracle 11G如何安装

https://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html

这是一个Oracle11g的下载链接,大家可以根据自己的电脑系统下载不同的安装包。大家这里需要注意,会下载一个文件一和文件二。

如图,下载好后,选中2个压缩包,解压到一个文件夹就可以了,这点非常重要。选择setup.exe 安装就行。

数据库的创建

安装完成后我们在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 (
,
,

);

====================================
示例如下:

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);

9、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(数据库管理员角色):包括无限制的空间限额和给其他用户授予各种权限的能力。

12、导入dmp数据库

imp test/111111@XE file=D:/zjyd.dmp full=y

格式说明——imp 用户名/秘钥@数据库版本名称 file=dmp文件路径 full=y

然后回车,即开始导入数据库。

13、oracle基本命令

sqlplus sys/sys as sysdba;

create user test identified by test;

授权

SQL> grant connect, resource dba to username;

grant create session to test;

这样test用户就能成功登录进去
赋予用户创建表的权限

grant create table to test;

赋予相应的权限

grant unlimited tablespace to test;

这个时候用户就拥有了创建表的权限 由于表是用户test的,相应的他就拥有了对创建的表的增删查改的权限了

撤销权限
revoke create table from test;

drop user 用户名;

若用户拥有对象,则不能直接删除,否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象,然后再删除用户。

drop user 用户名 cascade;

修改用户的密码.

alter user zzg identified by unis;

grant create session,create table,create view,create sequence,unlimited tablespace to zzg;

激活scott用户

alter database open;
alter user scott account unlock;
alter user scott identified by tiger;
select username,account_status from dba_users;

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 –更改表空间大小

通过命令直接建立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

su - oracle -c "sqlplus / as sysdba << EOF \
alter database open; \
alter user scott account unlock; \
alter user scott identified by tiger; \
select username,account_status from dba_users; \
EOF"

sqlplus -S '/ as sysdba' << "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';

启停数据库

// 停止数据库实例
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 123456@10.16.1.111"">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”

文档更新时间: 2020-11-24 11:09   作者:xlqywk