Oracle专题

#########################前言########################

一、特殊符号ascii定义

制表符 chr(9)

换行符 chr(10)

回车符 chr(13)

###################

在我们遇到问题时,就应该不断深入研究,直至问题的核心本质,这样通过一个案例或实际问题的诊断学习和研究,我们就可以带动很多连带知识的学习,这样从一个点深入下去就形成一条线,再横向扩展就可以形成一个知识网,解决和研究的问题多了,就可以逐渐覆盖一个面,形成一个知识体系,这样慢慢的你就会觉得学习不再困难,而是一件得心应手的事情。

而且,认真思考和深入研究本身就是对DBA的一项基本素质要求

!!!!对于一个候选人来说,我希望他勤奋、严谨、具有钻研精神及独立思考能力。技术其实往往并不是我最关心的内容,因为具备了前面的素质之后,经过1~2年的锻炼,一个人绝对不会知道的太少。!!!!!!

DBA生存之四大守则:

1.备份重于一切

我们必需知道,系统总是要崩溃的,没有有效的备份只是等哪一天死!我经常开玩笑的说,唯一会使DBA在梦中惊醒的就是:没有有效的备份。

在进行重要的操作(如恢复尝试、升级操作等)之前,一定要做好备份,保留现场,以便必要时可以从头再来。

2.三思而后行

Think thrice before you act。

任何时候都要清楚你所做的一切,否则宁可不做!对于DBA来说,有时候一个回车,一条命令就会造成不可恢复的灾难,所以,你必需清楚确认你所做的一切,以及这些操作可能带来的后果,并且在必要时保护现场。

DBA切忌想当然。

3.rm是危险的

要知道在UNIX/Linux下,这个操作意味着你可能将永远失去后面的东西,所以,确认你的操作!!!

太多的人在 “rm -rf” 上悲痛欲绝,当年写下这条守则时,是一个凌晨被一个朋友吵醒,他说误操作rm -rf删除掉了200G的数据库,并且没有备份。

我当时能告诉他的只有一句话:要保持冷静.

4.你来制定规范

良好的规范是减少故障的基础。所以,作为一个DBA,你需要来制订规范,规范开发甚至系统人员,这样甚至可以规避有意或是无意的误操作,减少数据库的风险。

见过太多管理混乱的开发环境,经常出现程序员连错生产环境误操作的案例,所以规范实在是非常的重要

不以规矩,不成方圆。

这四个守则有的部分是相互关联,密不可分的,希望每个DBA都能谨慎认真,少犯错误

#######################################################

1.存储过程一:

DECLARE v_CreateSql VARCHAR2(4000);v_ExecuteSql VARCHAR2(4000);v_Pos NUMBER;

BEGIN  SELECT CREATE_SQL INTO v_CreateSql FROM DYN_TABLE

WHERE BASE_TABLE_NAME = ‘EVENT_USAGE’ ;

v_CreateSql := REPLACE(v_CreateSql, ‘&BILLING_CYCLE_ID’, 237 );

v_Pos := INSTR(v_CreateSql, ‘;’);  IF v_Pos = 0 THEN      v_Pos := length(v_CreateSql);

 END IF;  WHILE (v_Pos > 0) LOOP  v_ExecuteSql := SUBSTR(v_CreateSql, 1, v_Pos -1);

 EXECUTE IMMEDIATE(v_ExecuteSql);  v_CreateSql := SUBSTR(v_CreateSql, v_Pos+1);  

 v_Pos := INSTR(v_CreateSql, ‘;’);   END LOOP;   END;

##########################################################3

(1)declare定义变量;

(2)create_sql :

CREATE TABLE EVENT_CDR_&BILLING_CYCLE_ID

PARTITION BY HASH(SUBS_ID) PARTITIONS 64 STORE IN(TAB_RB)

AS

SELECT * FROM EVENT_CDR

WHERE ROWNUM<1;

CREATE INDEX IDX_EVENT_CDR_SUBS_ID_&BILLING_CYCLE_ID ON EVENT_CDR_&BILLING_CYCLE_ID (

  SUBS_ID ASC

)

LOCAL STORE IN(IDX_RB);

(3)replace函数:

replace(原字段,“原字段旧内容“,“原字段新内容“,)

语句:update sys_frmattachmentdb  set filefullname = replace(filefullname,’历城区,’北京区)

(4)instr()函数的格式  (俗称:字符查找函数)

格式一:instr( string1, string2 )    /   instr(源字符串, 目标字符串)

格式二:instr( string1, string2 [, start_position [, nth_appearance ] ] )   /   instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析:string2 的值要在string1中查找,是从start_position给出的数值(即:位置)开始在string1检索,检索第nth_appearance(几)次出现string2。

 注:在Oracle/PLSQL中,instr函数返回要截取的字符串在源字符串中的位置。只检索一次,也就是说从字符的开始到字符的结尾就结束。

<span style="font-family: Courier;"–<——实例————————————————

格式一

1 select instr(‘helloworld’,’l’) from dual; –返回结果:3    默认第一次出现“l”的位置2 select instr(‘helloworld’,’lo’) from dual; –返回结果:4    即:在“lo”中,“l”开始出现的位置3 select instr(‘helloworld’,’wo’) from dual; –返回结果:6    即“w”开始出现的位置

格式二

1 select instr(‘helloworld’,’l’,2,2) from dual;  –返回结果:4    也就是说:在”helloworld”的第2(e)号位置开始,查找第二次出现的“l”的位置

2 select instr(‘helloworld’,’l’,3,2) from dual;  –返回结果:4    也就是说:在”helloworld”的第3(l)号位置开始,查找第二次出现的“l”的位置

3 select instr(‘helloworld’,’l’,4,2) from dual;  –返回结果:9    也就是说:在”helloworld”的第4(l)号位置开始,查找第二次出现的“l”的位置

4 select instr(‘helloworld’,’l’,-1,1) from dual;  –返回结果:9    也就是说:在”helloworld”的倒数第1(d)号位置开始,往回查找第一次出现的“l”的位置

5 select instr(‘helloworld’,’l’,-2,2) from dual;  –返回结果:4    也就是说:在”helloworld”的倒数第1(d)号位置开始,往回查找第二次出现的“l”的位置

6 select instr(‘helloworld’,’l’,2,3) from dual;  –返回结果:9    也就是说:在”helloworld”的第2(e)号位置开始,查找第三次出现的“l”的位置

7 select instr(‘helloworld’,’l’,-2,3) from dual; –返回结果:3    也就是说:在”helloworld”的倒数第2(l)号位置开始,往回查找第三次出现的“l”的位置

注:MySQL中的模糊查询 like 和 oracle中的 instr() 函数有同样的查询效果; 如下所示:

select * from tableName a where name like ‘%helloworld%’;

select * from tableName a where instr(name,’helloworld’)>0;  –这两条语句的效果是一样的

(5) := 是Declare 与 Begin之间声明的变量、常量赋值符号,如 v_Total Number(14,2) := 100;

= 是Begin 与 End之间常用在SQL检索条件或者判断用的比较符号,如 …Where Rownum = 10; if v_Total = 100 then … end if;

(6)在oracle中,比较常见的可能是length、substr,至少我看到的大部分都是这两个,要不是昨天看代码发现了lengthb、substrb,估计我也遗忘了。length表示的是字符串的字符长度,lengthb表示的是字符串的字节长度;substr表示根据字符长度获取子串,substrb表示根据字节长度来获取字串

(7)substr(字符串,截取开始位置,截取长度) //返回截取的字

substr(‘Hello World’,0,1) //返回结果为 ‘H’  *从字符串第一个字符开始截取长度为1的字符串

substr(‘Hello World’,1,1) //返回结果为 ‘H’  *0和1都是表示截取的开始位置为第一个字符

substr(‘Hello World’,2,4) //返回结果为 ‘ello’

substr(‘Hello World’,-3,3)//返回结果为 ‘rld’ *负数(-i)表示截取的开始位置为字符串右端向左数第i个字符

测试:select substr(‘Hello World’,-3,3) value from dual;

(8)while loop 循环;

execute immediate:意思就是执行语句,在编写存储过程也是常用到的;

(9)power函数:

POWER(n2, n1)   返回n2的n1次方结果

2.oracle查看建表语句:

增加字段语法:alter table tablename add (column datatype [default value][null/not null],….);

说明:alter table 表名 add (字段名 字段类型 默认值 是否为空);

  例:alter table sf_users add (HeadPIC blob);

  例:alter table sf_users add (userName varchar2(30) default ‘空’ not null);

修改字段的语法:alter table tablename modify (column datatype [default value][null/not null],….);

说明:alter table 表名 modify (字段名 字段类型 默认值 是否为空);

  例:alter table sf_InvoiceApply modify (BILLCODE number(4));

删除字段的语法:alter table tablename drop (column);

说明:alter table 表名 drop column 字段名;

  例:alter table sf_users drop column HeadPIC;

字段的重命名:

说明:alter table 表名 rename  column  列名 to 新列名   (其中:column是关键字)

  例:alter table sf_InvoiceApply rename column PIC to NEWPIC;

表的重命名:

说明:alter table 表名 rename to  新表名

  例:alter table sf_InvoiceApply rename to  sf_New_InvoiceApply;

3.删表,drop,或者直接plsql tables中删除

一次插入一条数据

insert into 表名 (字段名1,字段名2) values (字段值1,字段值2);

例如:

insert into customer (id,name) values (1,‘xiaozhang’);

注意:字符类型的数据需要添加

一次插入多条条数据

例如:

insert all

into seller values(1,‘A服装店’,‘1300000000’,‘中国北京朝阳区’,www.a.com,‘5’,‘经营各式服装’)

into seller values(2,‘B数码店’,‘1580000000’,‘中国浙江省杭州市拱墅区’,www.b.com,‘4’,‘经营各类电子’)

into seller values(3,‘c电器店’,‘1370000000’,‘中国广东深圳莲花港’,www.c.com,‘4’,‘经营各类家电’)

into seller values(4,‘D书店’,‘1590000000’,‘中国山东崂山’,www.d.com,‘5’,‘经营各类实体书与电子书’)

select * from dual;

注意:字符类型的数据需要添加

4.Oracle报错:“ORA-02292:违反完整约束条件(XXX.FKXXX)- 已找到子记录

1、找到以”FKXXX“为外键的表A的子表,直接运行

select a.constraint_name, a.table_name, b.constraint_name

from user_constraints a, user_constraints b

where a.constraint_type = ‘R’

and b.constraint_type = ‘P’

and a.r_constraint_name = b.constraint_name

and a.constraint_name = ‘FKXXX’

2、删除相应的子表记录

3、删除主表记录

##############rownum##############

1.作用:查询返回结果的编号,用于限制返回的条数;只能查询rownum<某个数的结果,针对>默认为false,但是可以用子查询进行;

select * from(select rownum no ,id,name from student) where no>2;

2.默认排序是按照插入顺序排序的,用子查询;

select rownum ,id,name from (select * from student order by name);

#################trunc函数##################

/*

TRUNC(number,num_digits) 

Number 需要截尾取整的数字。 

Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。

TRUNC()函数截取时不进行四舍五入

*/

##################表空间和数据文件#######################

首先,你需要明白的一点是:数据库的物理结构是由数据库的操作系统文件所决定,每一个Oracle数据库是由三种类型的文件组成:数据文件、日志文件和控制文件。数据库的文件为数据库信息提供真正的物理存储。 

每一个Oracle数据库有一个或多个物理的数据文件(data file)。一个数据库的数据文件包含全部数据库数据。逻辑数据库结构(如表、索引等)的数据物理地存储在数据库的数据文件中。数据文件通常为*.dbf格式,例如:userCIMS.dbf。数据文件有下列特征:①、一个数据文件仅与一个数据库联系;②、一旦建立,数据文件只增不减;③、一个表空间(数据库存储的逻辑单位)由一个或多个数据文件组成。 

其次,我们再来叙述一下Oracle的逻辑结构:Oracle的逻辑结构包括表空间(tablespace),段(segment),数据块(data block)以及模式对象(schema object)。 

Oracle数据库在逻辑上是由多个表空间组成的,表空间在物理上包含一个或多个数据文件。而数据文件大小是块大小的整数倍;表空间中存储的对象叫段,比如数据段,索引段和回退段。段由区组成,区是磁盘分配的最小单位。段的增大是通过增加区的个数来实现的。每个区的大小是数据块大小的整数倍,区的大小可以不相同;数据块是数据库中的最小的I/O单位,同时也是内存数据缓冲区的单位,及数据文件存储空间单位。块的大小由参数DB_BLOCK_SIZE设置,其值应设置为操作系统块大小的整数倍。 

⑴、表空间(tablespace) 

表空间是数据库中最大的逻辑单位,每一个表空间由一个或多个数据文件组成,一个数据文件只能与一个表空间相联系。每一个数据库都有一个SYSTEM表空间,该表空间是在数据库创建或数据库安装时自动创建的,用于存储系统的数据字典表,程序系统单元,过程函数,包和触发器等,也可用于存储用户数据表,索引对象。表空间具有在线(online)和离线(offline)属性,可以将除SYSTME以外的其他任何表空间置为离线。 

⑵、段(segment) 

数据库的段可以分为四类:数据段、索引段、回退段和临时段。 

⑶、区 

区是磁盘空间分配的最小单位。磁盘按区划分,每次至少分配一个区。区存储与段中,它由连续的数据块组成。 

⑷、数据块 

数据块是数据库中最小的数据组织单位与管理单位,是数据文件磁盘存储空间单位,也是数据库I/O的最小单位,数据块大小由DB_BLOCK_SIZE参数决定,不同的Oracle版本DB_BLOCK_SIZE的默认值是不同的。 

⑸、模式对象 

模式对象是一种应用,包括:表、聚簇、视图、索引序列生成器、同义词、哈希、程序单元、数据库链等。 

最后,在来说一下Oracle的用户、表空间和数据文件的关系: 

一个用户可以使用一个或多个表空间,一个表空间也可以供多个用户使用。用户和表空间没有隶属关系,表空间是一个用来管理数据存储的逻辑概念,表空间只是和数据文件发生关系,数据文件是物理的,一个表空间可以包含多个数据文件,而一个数据文件只能隶属一个表空间。 

总结一下:解释数据库、表空间、数据文件、表、数据的最好办法就是想象一个装满东西的柜子。数据库其实就是柜子,柜中的抽屉是表空间,抽屉中的文件夹是数据文件,文件夹中的纸是表,写在纸上的信息就是数据。

#######################索引#############################

<span style="font-size: 13px; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(245, 245, 245); color: rgb(0, 0, 0); font-family: "Helvetica Neue", Helvetica, Arial, sans-serif; font-variant-caps: normal; font-variant-ligatures: normal;"–<   从物理上说,索引通常可以分为:分区和非分区索引、常规B树索引、位图(bitmap)索引、翻转(reverse)索引等。其中,B树索引属于最常见的索引。

-=- 提高查询速度的方法还有在表上建立主键,主键与唯一索引的差别在于唯一索引可以空,主键为非空,

—rowid, 存储了row在数据文件中的具体位置

<span style="font-size: 14px; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; color: rgb(0, 0, 0); font-family: "Microsoft YaHei", Verdana, sans-serif, SimSun; font-variant-caps: normal; font-variant-ligatures: normal;"–<基本概念:

1、 类似书的目录结构

2、 Oracle 的“索引”对象,与表关联的可选对象,提高SQL查询语句的速度

3、 索引直接指向包含所查询值的行的位置,减少磁盘I/O

4、 与所索引的表是相互独立的物理结构

5、 Oracle 自动使用并维护索引,插入、删除、更新表后,自动更新索引

6、 语法:CREATE INDEX index ON table (column[, column]…);

7、 B-tree结构(非bitmap):

########################################union and union all##########################

如果我们需要将两个select语句的结果作为一个整体显示出来,我们就需要用到union或者union all关键字。

union(或称为联合)的作用是将多个结果合并在一起显示出来。

union和union all的区别是,union会自动压缩多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复。

Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;

Union All:对两个结果集进行并集操作,包括重复行,不进行排序;

Intersect:对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序;

Minus:对两个结果集进行差操作,不包括重复行,同时进行默认规则的排序。

可以在最后一个结果集中指定Order by子句改变排序方式。

############################################################

#########################那些年ora报的那些数字#################

(1)ORA-14402:更新分区关键字列将导致分区更改(分区表注意)

建立完分区表后一定要和开发确认一点,就是是否会修改分区字段。因为update分区字段到其他分区时候,会报错。

解决办法:开启表的行转移功能,这样在update以后,会在老分区删除数据,新分区插入数据。

1
alter table XX enable row movement

(2) ORA-01348:值大于为此列指定的允许精度

(3) ora-00054;oracle之报错:ORA-00054: 资源正忙,要求指定 NOWAIT. https://www.cnblogs.com/Ronger/archive/2011/12/19/2293509.html

 select session_id from v$locked_object;
 SELECT sid, serial#, username, osuser FROM v$session where sid = 31;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据