当前位置：文档库 › ORACLE期末复习整理笔记(详细版)

ORACLE期末复习整理笔记(详细版)

Oracle性能优化

存储结构

SQL> select TABLESPACE_NAME,EXTENT_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN

------------------------------ ----------

SYSTEM LOCAL

UNDOTBS1 LOCAL

SYSAUX LOCAL

TEMP LOCAL

USERS LOCAL

EXAMPLE LOCAL

已选择6行。

减少了数据字典的冲突，因为区的分配不需要记录UET$,FET$

当执行事务时（如INSERT大量数据）一旦申请了区，即使执行回滚操作

也不需要把区释放回去，因为空间分配和释放没有UNDO生成

不需要SMON整理区碎片。

减少空间递归管理，这是本地管理最重要的出发点。

递归SQL 就是我们执行一条SQL,会使oracle在后台执行多条SQL,这个牵扯的多条SQL就是递归SQL

本地管理

uniform 就是区的大小都是一样的

CREATE TABLESPACE "MYTBS2" DA TAFILE

'/u01/oracle/oradata/ora10g/mytbs02.dbf' SIZE 104857600

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL uniform size 64K SEGMENT SPACE MANAGEMENT AUTO; autoallocate 是自动分配的

CREATE TABLESPACE "MYTBS" DATAFILE

'D:/test/mytbs01.dbf' SIZE 104857600

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

SQL> drop tablespace mytbs including contents and datafiles;

SQL> create tablespace mytbs datafile '/u01/oracle/oradata/ora10g/mytbs01.dbf' size 100M;

create table t2 tablespace mytbs2 as select * from scott.emp;

select segment_name,extent_id,file_id,block_id,blocks from dba_extents where owner='SYS' and SEGMENT_NAME='T2';

10G以前回收的方法

1. 在表空间中移动表

alter table TABLE_NAME move tablespace TABLESPACE_NAME;

2. 将数据导出删除表再将数据导入

10G中提供的新方法

alter table TABLE_NAME shrink space [compact|cascate]

alter table TABLE_NAME shrink space; 整理碎片并回收空间

alter table TABLE_NAME shrink space compact; 只整理碎片不回收空间

alter table TABLE_NAME shrink space cascate; 整理碎片回收空间

并连同表的级联对象一起整理(比如索引)

使用条件

自动段管理模式

打开行移动

使用步骤

1. alter table t1 enable ROW MOVEMENT;

2. shrink 操作

3. alter table t1 disable ROW MOVEMENT;

ASSM是自动段管理AUTO SEGMENT SPACE MANAGEMENT

MSSM是手工段管理

FREELIST(空闲列表)管理

FREE extent可以分配给不同的段

extent的使用和空闲由数据文件位图或者数据字典UET$和FET$来管理

在字典管理及本地管理非自动段管理时将由FREELIST和FREELIST组来管理

在本地管理表空间的自动段管理采用位图来管理

FREELIST作为一个Oracle存储管理的核心参数

其行为方式由Oracle内部控制

但对这种机制不了解时,会遇到很多问题

当插入一条记录，会插入到哪个块中?

是使用新块，还是插入有数据的老块?

段是什么时候扩展的，如何扩展的?

表中只有一条记录，但是做一次select时，代价为何却是上千个块?

带着这些问题来了解FREELIST

header中包含:

属性信息:

块的类型,块的格式,块地址,SCN,块的序列号,块的标志

事务信息:

事务相关的基本信息,和ITL(事务槽)

表目录:

当前表的信息

行目录:

记录块内存储行的地址信息

free space:

空闲空间

data space:

使用空间

在生成段的时候，会同时分配初始区(initial extents)，

初始区的第一个块就格式化为segment header，

并被用来记录free list描述信息、extents信息，HWM信息等。

段头就是初始区的第一个块

free list是一种单向链表用于定位可以接收数据的块，

在字典管理方式的表空间中或MSSM，Oracle使用free list来管理未分配的存储块。

Oracle记录了有空闲空间的块用于insert或Update。空闲空间来源于两种方式：

1.段中所有超过高水位（HWM）的块，这些块已经分配给段了，但是还未被使用。

2.段中所有在HWM下的且链入了free list的块，可以被重用。

HWM：HIGH W ATER MARK代表一个表使用的最大的(top limit)块。

简单点说HWM 就是一口井曾经达到的最高水位,也就是历史最高水位点

比如一个杯子能装100升水装到80,倒出20 . 高水位就是80;

空闲的块怎么来的？

1.段中所有超过HWM的块，这些块已经分配给段了，但是还未被使用。

注意限定词：这些块已经分配给段了

2.段中所有在HWM下的且链入了free list的块，可以被重用。

FREE LIST就是一个链入了空闲块的单向链表

高水位上的肯定是空闲的,想用高水位以上的空闲之前,必须将高水位延伸.

表空间数据字典视图:DBA_TABLESPACES

表空间动态性能视图:V$TABLESPACE

数据文件信息

数据字典视图:DBA_DATA_FILES

动态性能视图:V$DATAFILE

临时文件信息

数据字典视图:DBA_TEMP_FILES

动态性能视图:V$TEMPFILE

空间分配信息

被段分配的区:DBA_EXTENTS

没有被段分配的区: DBA_FREE_SPACE

系统默认

database_properties;

查询FREElist

dba_tables

引入了一种新的段空间管理方式，称为ASSM，自动段管理

自动段管理，采用位图方式管理段空间。

****** 回退表空间和数据文件的管理******

作用:

1.交易的回退:没有提交的交易可以rollback

2.交易的恢复:数据库崩溃时,将磁盘的不正确数据恢复到交易前

3.读一致性:被查询的记录有事务占用,转向回滚段找改前镜像

4.闪回数据:从回滚段中构造历史数据

回滚段创建的时候是空闲的，只有事务产生时候才会产生数据。

创建UNDO表空间：

SQL> create undo tablespace undo2 datafile '/u01/oracle/oradata/ora10g/undo02.dbf' size 10M; 管理风格：自动或手动

SQL> show parameter undo

NAME TYPE V ALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

undo_management=AUTO 回滚表空间段的段管理模式，

自动管理

管理员只需要备足够的表空间容量，oracle会自动管理扩展回滚段的数量。

只能使用一个UNDO表空间

undo_tablespace:只有在自动管理模式下才可以使用。指明使用哪个UNDO表空间

SQL> alter system set undo_tablespace=undo2;

System altered.

修改方法

SYS@beijing> show parameter rollback

NAME TYPE V ALUE

------------------------------------ ----------- ------------------------------

fast_start_parallel_rollback string LOW

rollback_segments string

transactions_per_rollback_segment integer 5

SYS@beijing>

默认一个回滚段可以维持5个事务不要放太多事务会产生回滚段头争用

因为段头中记录段中的块的位置找段里的数据就要访问段头

SQL> alter system set undo_management=manual scope=spfile;

***************临时表空间*****************

存放排序的中间结果或临时表

SQL> 改成手动

SQL> alter system set undo_management=manual scope=spfile;

追加新的临时文件:

SQL> alter tablespace temp add tempfile '/u01/oracle/oradata/ora10g/temp02.dbf' size 50M;

10G的新语法临时文件的删除

删除临时表空间

SQL> drop tablespace temp2 including contents and datafiles;

删除临时表空间的文件

SQL> alter tablespace temp2 add tempfile '/u01/oracle/oradata/ora10g/temp02a.db' size 10M; SQL> alter tablespace temp2 drop tempfile '/u01/oracle/oradata/ora10g/temp02a.db';

扩容手段和永久的一致

SQL> alter database tempfile '/u01/oracle/oradata/ora10g/temp03.dbf' resize 10M;

清除临时表空间组

alter tablespace temp01 tablespace group ''; 为空即可

******************永久表空间管理*********************** 永久表空间管理

又分为:

小文件表空间(一个表空间可以存放1023个文件每个文件4M*block_size)

基本应用都是这类默认创建也是它

大文件表空间(一个表空间可以存放1个文件文件管理4G*block_size)

很少使用,不利于IO分散,容易产生争用,容易被FS限制

小文件表空间

查看

SQL> select tablespace_name,file_id,file_name,ceil(bytes/1048567)||'M' MB

from dba_data_files;

TABLESPACE_NAME FILE_ID FILE_NAME MB

--------------- ---------- --------------------------------------------- --------------------

USERS 4 /u01/oracle/oradata/ora10g/users01.dbf 26M

SYSAUX 3 /u01/oracle/oradata/ora10g/sysaux01.dbf 241M

UNDOTBS1 2 /u01/oracle/oradata/ora10g/undotbs01.dbf 26M

SYSTEM 1 /u01/oracle/oradata/ora10g/system01.dbf 481M

创建大文件表空间

SYS@beijing> create bigfile tablespace bigtbs datafile 'D:/test /bigtbs01.dbf' size 5M;

select tablespace_name,bigfile from dba_tablespaces;

TABLESPACE_NAME BIG

--------------- ---

SYSTEM NO

UNDOTBS1 NO

SYSAUX NO

TEMP NO

USERS NO

MYTBS NO

BIGTBS YES

修改表空间状态(online readonly offline)

只读表空间只能select查询和drop删除对象不能修改(INTSERT update delete truncate)

system undotbs temp 包含活动事务的表空间都不能只读

TABLESPACE_NAME STATUS

--------------- ----------

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

MYTBS ONLINE

alter tablespace mytbs offline;

命令

前提条件

a.表空间要offline状态alter tablespace users offline

b.目标文件要存在就是先做mv操作

移动文件

2. 使用alter database rename file 'OLD_file' to 'NEW_file';命令

前提条件

a.数据库mount或表空间离线shut immediate ==> startup mount

b.目标文件存在mv src_file dst_file

移动文件

练习

将表空间设置只读然后备份控制文件创建脚本

一致停库启动到nomount重新创建控制文件

启动数据库根据控制文件备份脚本中的提示将只读表空间恢复正常

(直到可以在这个表空间内创建表)

表空间扩容

使文件自动增长

增加新文件

修改现有文件的大小

SQL> select tablespace_name,sum(bytes/1048576) curr_MB,sum(MAXBYTES/1048576) MAX_MB from dba_data_files group by TABLESPACE_NAME;

TABLESPACE_NAME CURR_MB MAX_MB

--------------- ---------- ----------

SYSAUX 240 32767.9844

UNDOTBS1 25 32767.9844

USERS 25 32767.9844

SYSTEM 480 32767.9844

MYTBS 10 10

SQL>

最大大小为空说明不能增长

SQL> select TABLESPACE_NAME,sum(bytes/1048576) free_mb from dba_free_space group by TABLESPACE_NAME;

TABLESPACE_NAME FREE_MB

--------------- ----------

UNDOTBS1 4.0625

SYSAUX .875

USERS 24.5625

SYSTEM 6.875

MYTBS 10.875

1.使文件自动增长

alter database datafile '' autoextend on next 10M maxsize 4G;

alter database datafile '' autoextend off;

2.增加新文件

alter tablespace uses add datafile '' size 5M;

alter tablespace uses drop datafile ''; /*10G才能删有数据不能删第一个文件不能删*/

3.修改现有文件的大小

alter database datafile '' resize 15M;

alter database datafile '' resize 10M;

删除表空间及其内容一起删除

删除表空间及其文件一起删除

create table t1 tablespace mytbs as select * from scott.emp;

drop tablespace mytbs INCLUDING CONTENTS;

create tablespace mytbs datafile '/u01/oracle/oradata/db10/mytbs01.dbf' size 10M;

create table t1 tablespace mytbs as select * from scott.emp;

drop tablespace mytbs INCLUDING CONTENTS and datafiles;

************* 表空间******************************

表空间是数据文件的容器

表空间是数据文件在数据库内逻辑的组织形式

数据文件存在于操作系统之上,也可以是裸设备

常见表空间

system系统表空间

数据库创建时就创建必须包含

存放数据库基本组件信息比如基表

sysaux辅助表空间

10G引入system太大了.就将部分功能组件取出放到sysaux里

属于SYSTEM表空间的辅助表空间

独立出来的好处

SYSTEM表空间的负荷得以减轻.

反复创建一些相关对象及组件引起SYSTEM表空间的碎片问题得以避免查看sysaux表空间内的组件信息和大小

select OCCUPANT_NAME,SPACE_USAGE_KBYTES from

V$SYSAUX_OCCUPANTS;

undotbs回退表空间

用于事务回退,保存改前镜像块

temp临时表空间

存放临时数据磁盘排序或临时表

users和example

oracle提供的样本数据表空间

用户数据表空间

用户自建

表空间分类

永久表空间管理

临时表空间管理

回退表空间管理

查看表空间的类型

idle> select TABLESPACE_NAME,CONTENTS from dba_tablespaces;

TABLESPACE_NAME CONTENTS

--------------- ---------

SYSTEM PERMANENT

UNDOTBS1 UNDO

SYSAUX PERMANENT

TEMP TEMPORARY

USERS PERMANENT

MYTBS PERMANENT

********* 联机日志文件***************************

联机日志redofile

联机日志存在的目的是为了数据完整性通过时间向量和日志条目来维持完整性

Oracle服务器维护联机重做日志文件以使数据库中的数据丢失减到最小。

重做日志文件记录了对数据库缓冲区高速缓存内数据所做的所有更改,

存放着SQL条目(记录)就是改变向量

重做日志

按group来存放的.叫日志组

一个数据库至少有2个日志组

每个日志组至少有一个成员,组内成员与成员之间是镜像关系

顺序切循环写

业务交易会不断生成REDO，使得REDO LOG文件越来越大，

显然不利于ORACLE管理,所以设计切换模式

当前组日志写满,就会触发日志切换.手动也可以触发

手动触发日志切换

重做日志文件是写多读少，要存放在写性能最好的磁盘上.

而且要多元化管理(物理隔离).

重做日志文件的读写特征

写→重做日志主要是写操作，由LGWR完成。

读→重做在实例恢复或者介质恢复的时候才会去读,日志挖掘也能用到。

idle> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------

1 1 0 52428800 1 YES UNUSED 0

2 1 0 52428800 1 YES UNUSED 0

3 1 1 52428800 1 NO CURRENT 706870 22-FEB-11 status:

CURRENT 当前当前日志非常重要若当前日志组丢失系统崩溃恢复时

将会丢失数据

UNUSED 未使用

ACTIVE 活动的说明这组redo记录的条目里关联的脏块没写盘

或者说这组日志在实例恢复时需要

INACTIVE 表明日志条目里关联的脏块已经全部写盘

或者说这组日志实例恢复已经不需要了

idle> select * from v$logfile;

idle> alter system switch logfile;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------

1 1

2 52428800 1 NO CURRENT 708058 22-FEB-11

2 1 0 52428800 1 YES UNUSED 0

3 1 1 52428800 1 NO ACTIVE 706870 22-FEB-11

idle> select to_char(FIRST_TIME,'YYYY-MM-DD HH24:MI:SS')

FIRST_TIME FROM V$LOG_history;

FIRST_TIME

-------------------

2011-02-22 01:11:08

2011-02-22 01:52:55

idle>

idle> alter system switch logfile;

idle> select to_char(FIRST_TIME,'YYYY-MM-DD HH24:MI:SS')

FROM V$LOG_history;

TO_CHAR(FIRST_TIME,

-------------------

2011-02-22 01:11:08

2011-02-22 01:52:55

2011-02-22 01:54:11

2011-02-22 02:02:02

2011-02-22 02:02:10

2011-02-22 02:02:20

2011-02-22 02:02:30

日志切换原理: 把当前组冻结下一组初始化

如果太快就会导致下一组初始化未完成在这个切换的间隙就会导致redo log

buffer不能写越积越多可能积满

积满时再执行update语句无法产生redo条目时挂起

(触发了日志等待事件等待事件后期会学)

解决方法: 增加日志组的数量

增大日志文件

使切换的周长时间增加

alter system checkpoint;

idle> alter database add logfile group 4

'/home/oracle/oradata/ora10g/redo04.log' size 50M;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------

1 1 8 52428800 1 NO CURRENT 708333 22-FEB-11

2 1 6 52428800 1 NO INACTIVE 708329 22-FEB-11

3 1 7 52428800 1 NO INACTIVE 708331 22-FEB-11

alter system switch logfile;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------

1 1 8 52428800 1 NO ACTIVE 708333 22-FEB-11

2 1 6 52428800 1 NO INACTIVE 708329 22-FEB-11

3 1 7 52428800 1 NO INACTIVE 708331 22-FEB-11

4 1 9 52428800 1 NO CURRENT 708576 22-FEB-11

group 1: 708333 ==> 708328

group 2: 708329 ==> 708330

group 3: 708331 ==> 708575

group 4: 708576 ==>

一组日志中一个成员这个成员损坏就会导致日志断档将来恢复时不是顺序

的日志恢复无法进行

为了增加日志的安全性故要多元化管理日志成员

alter database add logfile member

'/home/oracle/oradata/ora10g/redo01a.log' to group 1;

alter database add logfile member '/home/oracle/oradata/ora10g/redo02a.log' to group 2;

alter database add logfile member '/home/oracle/oradata/ora10g/redo03a.log' to group 3;

alter database add logfile member '/home/oracle/oradata/ora10g/redo04a.log' to group 4;

删除日志组有如下限制:

一个数据库至少2个日志组，不能删除至少于2个日志组

活动或当前日志组不允许删除

如果数据库运行在ARCHIVELOG（归档）模式下，

并且未将日志文件组归档，那么无法丢弃该组

当删除日志组时，文件没有正真的删除，需要人工删除（OS下去删）

alter system checkpoint;

select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------

1 1 1

2 52428800 2 NO CURRENT 51409

3 28-MAR-11

2 1 11 52428800 2 NO INACTIVE 514091 28-MAR-11

3 1 10 52428800 2 NO INACTIVE 514088 28-MAR-11

alter database drop logfile group 1;

ERROR at line 1:

ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ora10g/redo01.log'

ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ora10g/redo01a.log'

alter system switch logfile;

alter database drop logfile group 1;

ERROR at line 1:

ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ora10g/redo01.log'

ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ora10g/redo01a.log'

Database altered.

删除日志组成员有如下限制:

current不能删,active可以删

最后一个有效成员不能删

在删除联机重做日志成员时并未删除操作系统文件

GROUP# STATUS TYPE MEMBER IS_

---------- ---------- ------- --------------------------------------------- ---

3 ONLINE /u01/oracle/oradata/ora10g/redo03.log NO

2 ONLINE /u01/oracle/oradata/ora10g/redo02.log NO

3 ONLINE /u01/oracle/oradata/ora10g/redo03a.log NO

2 ONLINE /u01/oracle/oradata/ora10g/redo02a.log NO

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------

2 1 14 52428800 2 NO CURRENT 514130 28-MAR-11

3 1 13 52428800 2 NO ACTIVE 514105 28-MAR-11

alter database drop logfile member

'/u01/oracle/oradata/ora10g/redo02a.log';

alter database drop logfile member '/u01/oracle/oradata/ora10g/redo02a.log'

*ERROR at line 1:

ORA-01609: log 2 is the current log for thread 1 - cannot drop members

ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/ora10g/redo02.log'

ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/ora10g/redo02a.log'

alter system switch logfile;

Database altered.

SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------

2 1 14 52428800 1 NO ACTIVE 514130 28-MAR-11

3 1 15 52428800 2 NO CURRENT 514153 28-MAR-11 SQL> select * from v$Logfile;

GROUP# STATUS TYPE MEMBER IS_

---------- ---------- ------- --------------------------------------------- ---

3 ONLINE /u01/oracle/oradata/ora10g/redo03.log NO

2 ONLINE /u01/oracle/oradata/ora10g/redo02.log NO

3 ONLINE /u01/oracle/oradata/ora10g/redo03a.log NO

SQL> alter database drop logfile member '/u01/oracle/oradata/ora10g/redo02.log';

alter database drop logfile member '/u01/oracle/oradata/ora10g/redo02.log'

ERROR at line 1:

可以不停库在open模式做,但要保证被移动的文件不是活跃的.

1.停库shut immediate

2. 2.启动到mount startup mount

3.移动磁盘文件

4.移动控制文件中的指针指向新文件alter database rename file

'/home/oracle/oradata/ora10g/redo01.log' to '/home/oracle/oradata/ora10g/redo01abc.log';

5.正常启库alter database open;

select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------

1 1 4 52428800 1 NO INACTIVE 515008 28-MAR-11

2 1

3 52428800 1 NO INACTIVE 514969 28-MAR-11

3 1 5 52428800 1 NO CURRENT 515011 28-MAR-11 select * from v$Logfile;

GROUP# STATUS TYPE MEMBER IS_

---------- ---------- ------- --------------------------------------------- ---

3 ONLINE /u01/oracle/oradata/ora10g/redo03.log NO

2 ONLINE /u01/oracle/oradata/ora10g/redo02.log NO

1 ONLINE /u01/oracle/oradata/ora10g/redo01.log NO

***************************日志文件笔记***************************************************** startup mount

alter database archivelog;

SQL> archive log list;

数据库日志模式存档模式

自动存档启用

存档终点USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列 4

下一个存档日志序列 6

当前日志序列 6

alter database open;

alter system switch logfile;

show parameter log_archive_dest;

SQL> alter system set log_archive_dest_1='location=E:/oracle';

系统已更改。

SQL> alter system switch logfile;

系统已更改。

select name from v$archived_log;

SQL> select log_mode from v$database;

LOG_MODE

------------

ARCHIVELOG

alter system set log_archive_dest_1='location=E:/oracle1';

alter system set log_archive_dest_2='location=E:/oracle2';

1.修改追加日志数据模式

SYS@beijing> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

SYS@beijing> alter database add SUPPLEMENTAL log data;

Database altered.

SYS@beijing> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

YES

conn /as sysdba

exec dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'); alter user scott account unlock identified by seker;

update scott.emp set sal=100 ;

commit;

exec dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'); select sql_redo from v$logmnr_contents where lower(sql_redo) like '%update%' and seg_name='EMP'; select sql_undo from v$logmnr_contents where lower(sql_redo) like '%update%' and seg_name='EMP';

热备要求数据库处于归档状态,在业务不是很繁忙时用热备

shut immediate

startup mount

alter database archivelog;

alter database open;启动归档模式

archive log list

热备:

alter tablespace users begin backup;

copy D:\oracle\product\10.2.0\oradata\orcl \users01.dbf e:\test\users01.dbf

alter tablespace users end backup

假设users表空间存在热备份

该空间有一个T2表（scott.emp）

在该表产生交易

update t2 set sal=sal+1;

commit;

alter system switch logfile;

然后将USERS表空间的数据文件给它破坏掉

比如用一个txt文件覆盖掉

copy d:\1.txt d:\users01.bdf(用txt覆盖掉了users01.bdf)

alter system checkpoint;

select file_name,online_status from dba_data_files;

产看当前数据库有哪些数据文件需要恢复

取回备份文件；

recover datafile 4:

alter database datafile 4 online;

alter database archivelog

alter database open

create table t2 tablespace users as select * from scott.emp;

alter tablespace users begin backup;

alter tablespace users end backup;

update t2 set sal=sal+1;

commit;

alter system switch logfile;

alter system checkpoint;

select file_name,online_status from dba_data_files;

select * from v$recovery_log;

recover datafile 4;

select file_name,online_status from dba_data_files;

alter database datafile 4 online;

undo表空间

startup mount

alter database archivelog;

alter database open;

archive log list

alter tablespace UNDOTBS1 begin backup;

host copy d:\test\1.txt D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF

已复制 1 个文件。

alter tablespace UNDOTBS1 end backup;

alter system checkpoint;

conn / as sysdba

startup mount

recover datafile 3;

alter database open;

alter database datafile 3 online

select status from v$instance;

说明数据库名不仅仅在参数文件里控制文件也包含所以要重建控制文件因为是二进制的不能编辑

3.正常关闭数据库重建控制文件创建前先把原来的控制文件删除已存在不能创建

idle> create controlfile reuse database ora10g set database beijing resetlogs

maxinstances 8

maxloghistory 1

maxlogfiles 16

maxlogmembers 3

datafile

'/u01/oracle/oradata/ora10g/system01.dbf',

'/u01/oracle/oradata/ora10g/undotbs01.dbf',

'/u01/oracle/oradata/ora10g/sysaux01.dbf'

logfile

group 1 '/u01/oracle/oradata/ora10g/redo01.log' size 50M,

group 2 '/u01/oracle/oradata/ora10g/redo02.log' size 50M,

group 3 '/u01/oracle/oradata/ora10g/redo03.log' size 50M

character set AL32UTF8

Control file created.

5.启动数据库需要使用resetlogs模式重置联机日志文件

alter database open resetlogs;

1.先正常停掉原来的库使数据库是一致的

idle> shut immediate

2.修改操作系统和实例关联的信息ORACLE_SID

以及根据ORACLE_SID参数去寻找的参数文件名和口令文件名

说到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：

1、语法检查(syntax check)

检查此sql的拼写是否语法。

2、语义检查(semantic check)

诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。

3、对sql语句进行解析(prase)

利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。

4、执行sql，返回结果(execute and return)

其中，软、硬解析就发生在第三个过程里。

Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash 值;

假设存在，则将此sql与cache中的进行比较;

假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。

诚然，如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。

创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。

这就是在很多项目中，倡导开发设计人员对功能相同的代码要努力保持代码的一致性，以及要在程序中多使用绑定变量的原因。

/****************************************************/

问题二、大家都在说在Sql中使用了Bind Var(绑定变量)会提高不少性能，那他到底是如何提高性能的呢？

使用了Bind Var能提高性能主要是因为这样做可以尽量避免不必要的硬分析(Hard Parse)而节约了时间，同时节约了大量的CPU资源。

当一个Client提交一条Sql给Oracle后，Oracle 首先会对其进行解析(Parse)，然后将解析结果提交给优化器(Optimiser)来进行优化而取得Oracle认为的最优的Query Plan，然后再按照这个最优的Plan来执行这个Sql语句(当然在这之中如果只需要软解析的话会少部分步骤)。

但是，当Oracle接到Client提交的Sql后会首先在共享池(Shared Pool)里面去查找是否有之前已经解析好的与刚接到的这一个Sql完全相同的Sql(注意这里说的是完全相同，既要求语句上的字符级别的完全相同，又要求涉及的对象也必须完全相同)。当发现有相同的以后解析器就不再对新的Sql在此解析而直接用之前解析好的结果了。这里就节约了解析时间以及解析时候消耗的CPU资源。尤其是在OLTP中运行着的大量的短小Sql，效果就会比较明显了。因为一条两条Sql的时间可能不会有多少感觉，但是当量大了以后就会有比较明显的感觉了。

上面说到了硬解析(Hard Parse)，那这个Hard Parse到底是个啥呢？

Parse主要分为三种：

1、Hard Parse (硬解析)

2、Soft Parse (软解析)

3、Soft Soft Parse(好像有些资料中并没有将这个算在其中)

shared pool

共享池，存放SQL和PLSQL解析树和字典对象定义的共享区域

他又分为两部分:

1.data dict cache 也叫rowcache 加速SQL的解析

使用过的字典就会缓存在这里再次使用这个字典就直接从内存获取

这些统计的信息在v$rowcache中记录

DC_USERS 用户列表

DC_PRIVS 权限列表

DC_TABLES 表的列表

DC_COLUMNS 某表的字段列表

Hard Parse就是上面提到的对提交的Sql完全重新从头进行解析(当在Shared Pool中找不到时候将会进行此操作)，总共有一下5个执行步骤：

1：语法分析

2：权限与对象检查

3：在共享池中检查是否有完全相同的之前完全解析好的—如果存在，直接跳过4和5，运行Sql(此时算soft parse)

4：选择执行计划

5：产生执行计划

Soft Parse就如果是在Shared Pool中找到了与之完全相同的Sql解析好的结果后会跳过Hard Parse 中的后面的两个步骤。

Soft Soft Parse实际上是当设置了session_cursor_cache这个参数之后，Cursor被直接Cache在当前Session的PGA中的，在解析的时候只需要对其语法分析、权限对象分析之后就可以转到PGA中查找了，如果发现完全相同的Cursor，就可以直接去取结果了，也就就是实现了Soft Soft Parse.

不过在计算解析次数的时候是只计算Hard Parse和Soft Parse的(其实Soft Soft Parse好像也并不能算是做了Parse )：Soft Parse百分比计算：Round(100*(1-：hprs/：prse)，2) [hprs：硬解析次数;prse：解析次数] Parse比率计算：Round(100*(1-prse/exec) ，2) [exec：执行次数]

2.library cache: 存储最近频繁执行的SQL或PLSQL语句信息加速解析减少语句重解析

主要包括两部分共享SQL AREA和共享PL/SQL AREA

共享SQL AREA ：

LIBRARY CACHE的核心部分是SQL AREA，它存放了执行计划和解析树，SQL必须文本匹配才可以共享，它包括：

SQL 语句文本

分析树语句的已编译版本

执行计划执行时要采取的步骤

共享PL/SQL AREA：

存放编译代码，调试代码，解析树等信息

oracle常用命令及格式

oracle常用命令及格式一：关于日志管理的 1.切换日志：sql> alter system switch logfile; 2.切换checkpoints：sql> alter system checkpoint; 3.增加日志组：sql> alter database add logfile [group 4] sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo') size1m; 4.增加日志成员 sql> alter database add logfile member sql> '/disk3/log1b.rdo' to group 1, sql> '/disk4/log2b.rdo' to group 2; 5.改变日志文件名字或路径 sql> alter database rename file 'c:/oracle/oradata/oradb/re do01.log' sql> to 'c:/oracle/oradata/redo01.log'; （此处注意，那个文件路径的输入格式） 6.删除日志文件组：sql> alter database drop logfile group 3;

7.删除日志文件成员 sql> alter database drop logfile member 'c:/oracle/oradata/ redo01.log'; 8.清除日志文件内容 sql> alter database clear [unarchived] logfile 'c:/oracle/l og2a.rdo'; 二、关于表空间管理的 1.创建表空间 sql> create tablespace tablespace_name datafile 'c:\oracle\ oradata\file1.dbf' size100m, sql> 'c:\oracle\oradata\file2.dbf' size100mminimum extent 5 50k [logging/nologging] sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0) sql> [online/offline] [permanent/temporary] [extent_managem ent_clause]

oracle函数

许多软件公司都理解开发不依赖于特定数据库类型(例如Oracle、SQL Server、DB2)的应用程序的重要性，它可以让客户们选择自己习惯的平台。一般来说，软件开发人员都能够识别出他们的负责数据库维护的客户，和必须使用现有平台和个性化的客户。关于Oracle和SQL Server之间的区别，已经有很多篇文章从企业的角度和数据库管理员的角度出发描写了两者之间的一般差别。在这篇文章里面，我将会从应用程序的角度向你描述SQL erver 和oracle平台之间的区别，并且将会讨论开发不依赖于数据库环境的应用程序的几种可能的方法。与此同时，我不会再论讨对于应用程序来说，两个平台之间显而易见的区别，例如表的分割和索引。定义通用接口和语言有很少的几种通用语言和接口可以让应用程序不依赖于数据库，想来也可以以同样的方式应用在关系型数据库上面: ANSI是美国国家标准局定义的，这是一家志愿者成员的组织(用私人基金运转)，他们在有关设备和程序等广泛领域内开发了国家承认的标准。在数据库领域， ANSI定义了编写SQL命令的标准，假设命令可以运行在任何的数据库上，而不需要更改命令的语法。 ODBC是开放数据库连接(ODBC)接口，微软定义的，它可以让应用程序访问数据库管理系统(DBMS)中的数据，使用SQL作为访问数据的标准。 ODBC允许最大的互联性，这意味着一个单个的应用程序可以访问不同的数据库管理系统。然后，应用程序终端用户可以添加开放数据库连接(ODBC)数据库驱动来将应用程序与他们所选择的数据库管理系统链接上。 OLE DB 是ODBC的继承者，是一组允许例如基于VB、C++、Access等任何可以连接到类似SQL Server、Oracle、DB2、MySQL等后台的“前台”的软件组件。在许多种情况下，OLE DB组件都比原有的ODBC提供了好得多的性能。 JDBC(Java数据库连接)应用程序接口是Java编程语言和广泛范围的数据库，SQL数据库和其他表列数据源(例如电子表格或者普通文本文件)之间，进行不依赖于数据库的连接的行业标准。JDBD应用程序接口为基于SQL的数据库访问提供了调用级别的应用程序接口。真实世界中的通用接口不幸的是，并不是所有数据库级别的命令都是ANSI，每个数据库平台都有自己的扩展功能。对于ANSI或者通用接口，一般来说都代表着几本功能，因此也可能意味着丧失了性能方面的竞争力。对于小型数据库和小型应用程序来说，要维护对数据库的通用访问是简单的，但是当数据库和/或应用程序变得越来越大，越来越复杂，你就不得不向代码中添加功能。 SQL Server和Oracle的常用函数对比 ---------数学函数 1.绝对值 S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual

(Oracle数据库管理)玩转实战教程学习笔记最全版

（O管理）玩转实战教程(韩顺平)学习笔记

Oracle SQLPlus 常用命令及解释

Oracle SQLPlus 常用命令及解释 1.@ 执行位于指定脚本中的SQLPlus语句。可以从本地文件系统或Web服务器中调用脚本。可以为脚本中的变量传递值。在iSQL*Plus中只能从Web服务器中调用脚本。 2.@@ 执行位于指定脚本中的SQL*Plus语句。这个命令和@（“at”符号）命令功能差不多。在执行嵌套的命令文件时它很有用，因为它会在与调用它的命令文件相同的路径或url中查找指定的命令文件。在iSQL*Plus中只支持url形式。 3./ 执行保存在SQL缓冲区中的最近执行的SQL命令或PL/SQL块。在SQL*Plus命令行中，可在命令提示符或行号提示符使用斜线（/）。也可在iSQL*Plus的输入区中使用斜线（/）。斜线不会列出要执行的命令。 4.ACCEPT 可以修改既有变量，也可定义一个新变量并等待用户输入初始值,读取一行输入并保存到给出的用户变量中。ACCEPT在iSQL*Plus中不可用。 5.APPEND 把指定文本添加到SQL缓冲区中当前行的后面。如果text的最前面包含一个空格可在APPEND和text间输入两个空格。如果text的最后是一个分号，可在命令结尾输入两个分号（SQL*Plus会把单个的分号解释为一个命令结束符）。APPEND 在iSQL*Plus中不可用。 6.ARCHIVE LOG 查看和管理归档信息。启动或停止自动归档联机重做日志，手工（显示地）归档指定的重做日志，或者显示重做日志文件的信息。 7.ATTRIBUTE 为对象类型列的给定属性指定其显示特性，或者列出单个属性或所有属性的当前显示特性。 8.BREAK 分开重复列。指定报表中格式发生更改的位置和要执行的格式化动作（例如，在列值每次发生变化时跳过一行）。只输入BREAK而不包含任何子句可列出当前的BREAK定义。 9.BTITLE 在每个报表页的底部放置一个标题并对其格式化，或者列出当前BTITLE定义。

Oracle 常见函数(一)——数值函数

Oracle常见数值函数 ----***特别说明***： x 可以是纯的数值，也可以是数值型表达式/* ABS(x)返回x绝对值 eg. */ selectabs(100),abs(-100) from dual; /* sign(x)判断x的正负，正数返回1，负数返回-1,0返回0； eg. */ selectsign(100),sign(-100),sign(0) from dual;

/* round(x[,n])对x进行四舍五入，保留n位小数，其中n采用其整数部分；没有n时默认四舍五入到整数位，n为负数时，四舍五入保留小数点左边n位(补零)， eg. */ selectround(5555.6666, 2.1), round(5555.6666, -2.6), round(5555.6666) from dual; /* trunc(x)对x进行直接截取，保留n位小数，其中n采用其整数部分；没有n时默认截取到整数位，n为负数时，截取保留小数点左边n位(补零)， eg. */ selecttrunc(5555.66666,2.1), trunc(5555.66666,-2.6), trunc(5555.033333) from dual; /* ceil(x)对x进行向上取整，返回不小于x的最小整数(可以是整数x本身)。

eg. */ selectceil(3.1), ceil(2.8+1.3), ceil(0) from dual; /* floor(x)对x进行向下取整，返回不大于x的最大整数(可以是整数x本身)。eg. */ selectfloor(3.1), floor(2.8+1.3), floor(0) from dual; /* mod(x,y)求x除以y的余数，x，y为数字型表达式。 eg. */ selectmod(23,8),mod(24,8) from dual;

oracle笔记(3)

1.用insert语句进行行数据的迁移 Insert into 表名(列，列) select 列，列from emp where 条件 2.用update更新数据(使用子查询)：希望员工scott的岗位，工资，补助与smith员工一样？ update emp set (job,sal,comm)=(select job,sal,comm. From emp where ename=?SMITH?) where ename=?SCOTT?; 3.事务 (1)锁 (2)提交事务 (3)回退事务（一开始就做保存点(savepoint)） (4)事务的几个重要操作 1)设置保存点:savepoint a 2)取消部分事务:rollback to a 3)取消全部事务:rollback (5)只读事务:set transaction read only（当前用户能看到之前的操作，若其他用户进行更新，该用户之后的操作都不能看到更新） (6)字符函数 (1)lower(char):将字符串转化为小写的格式 (2)upper(char): 将字符串转化为大写的格式 (3)length(char):返回字符串的长度 (4)substr(char,m,n):取字符串的子串，从m开始取，取n个 (5)例：以首字符大写的方式显示所有的员工的姓名: (1)完成首字母大写：select upper(substr(ename,1,1)) from emp; (2)完成后面字母小写：select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并:select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp; (6)replace(char1,search_string,replace_string) (7)instr(char1,char2,[,n[,m]])—取子串在字符串的位置 (8)例：显示所有员工的姓名，用”我是A”替换所有”A”:select replace(ename,?A?,? 我是A?) from emp; (7)数学函数 (1)round(n,[m]):用于四舍五入 (2)trunc(n,[m]):用于截取数字 (3)mod(m,n)：取模 (4)floor(n)：返回<=n的最大整数 (5)ceil(n):返回>=n的最小整数 (6)例：显示在一个月为30天的情况所有员工的日薪金，忽略余数:select floor(sal/30),ename from emp; (8)日期函数 (1)sysdate (2)add_months(d,n) (3)last_day(d):返回指定日期所在月份的最后一天例：返回在八个月前雇佣的员工(已经入职8个月多的员工):select * from emp where sysdate>add_months(hiredate,300); 例：对于每个员工，显示其加入公司的天数:select trunk(sysdate-hiredate) “入职天

ORACLE笔记

1.Oracle 工具:sqlplus Sqlplus / as sysdba Shutdown immediate(关闭数据库) Startup(启动数据库) 注意：数据库开启才可以进行操作 Select username,account_status from dba_users;(查询数据库中所有的用户名称与用户状态) Alter user scott identified by tiger account unlock;(scott用户解锁) Sqlplus scott/tiger(使用SCOTT 密码为tiger登陆ORACLE数据库) Connect scott/tiger (在SQL>中直接使用SCOTT用户连接数据库) Show all;(看所有变量) Set sqlprompt “_user>”(设置sqlplus环境下面的提示符用自身用户显示) 注意：如要变量下次重启生效必须把变量写入 /u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sql中 Host(回到系统) Help index（查看所有命令） Help+命令(类似于LINUX中的MAN,查看命令的格式与用法) Disconnect(关闭SQLPLUS工具但是不离开数据库) Describe emp(查看EMP表) Define(定义变量,常用与写脚本用) 如：DEFINE _EDITOR =”vi” (CHAR) (定义VI编辑器用于保存最后一条执行的SQL语句) Save /u01/app/oracle/aa.sql (保存SQL语句) Get /u01/app/oracle/aa.sql (调用保存的SQL语句) 2.select（select 查询语句是ORACLE中最常用的语句） DML语句包括（insert 写入，update改变，delect删除，merge两张表同步）注意：merge常用在ETL（数据仓库）底下 DDL数据定义语句包括（create创建对象，alter修改，drop删除对象，rename改名，truncate 删除整个表） TCL事务处理语句（commit提交立刻生效，rollback回滚，savepoint保存点，savepoint(保存点) DCL权限语句（grant赋予权限，revoke(收回权限) ORACLE的表称之为堆表（keep table） SELECT (1) writing basic SQL select statemanes 1. selecting all columns SCOTT>select * from dept; SCOTT>select dname,deptno,loc from dept; Basic SELECT Statement SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;

Oracle基本语法

Oracle的历史 ?Oracle 公司( 甲骨文) 创始人: Larry Ellison 32岁,公司提供数据库服务. ?公司成立于1977 年, 83 年公司更名为Oracle ,原名为”软件开发实验室”. ?Oracle 数据库适用于大型企业 ?竞争对手 –微软的SQLServer –IBM 的DB2 ?目前的版本 – 2.0~7.0 , 8.0 , 8i , 9i , 10g Oracle的服务：我的电脑右键选择管理--服务和应用程序—服务 -----是数据库或例程的系统标识符 ------是Oracle主目录名称这几个服务之间的关系：启动顺序：1、OracleTNSListener必须启动 2、OracleServer必须启动 3、OracleDBConsole启动依赖于OracleServer SqlPlus SqlPlus是Oracle任何版本都自带的数据库操作工具，使用它可以完成大部分的数据库操作。 SqlPlus可以“开始→程序→Oracle”启动，也可以命令行启动（互动） 1.命令行启动sqlPlus sqlplus 用户名/密码@orcl

或 sqlplus 用户名@orcl 如果用户是管理员要在sqlplus 用户名/密码@主机字符串as sysdba “/”是用户名和密码分隔符号 “@”是密码和数据库的分隔符号 “orcl”是数据库的名称，在安装时指定常用命令（互动） connect 切换用户 show user 显示当前用户 set linesize 1000 设置行显示长度 set pagesize 1000 设置分页长度 desc dept 查看表结构 select table_name from user_tables 查询当前用户的表 / 运行上一条SQL语句 clear screen 清除屏幕 edit 编辑 spool d:/a 保存输出结果到某个位置 spool off 保存结束 quit 退出 list 查看最后一条语句 @ 文件名.sql 运行外部文件中的SQL语句

Oracle函数详解(经典)

Oracle常用函数/过程说明主要介绍Oracle的系统函数、过程和包。 SQL常用函数：数值函数： ABS Purpose 返回绝对值 Returns the absolute value of n. Example SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15 CEIL Purpose 取最小整数 Returns smallest integer greater than or equal to n. Example SELECT CEIL(15.7) "Ceiling" FROM DUAL;

Ceiling ---------- 16 * MOD Syntax MOD(m,n) Purpose 取余 Returns remainder of m divided by n. Returns m if n is 0. Example SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3 * ROUND Syntax ROUND(n[,m]) Purpose 取四舍五入信息 Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.

学习oracle数据库的总结(图文整理)

1、在开发环境中连接到数据库，进行基本的select查询操作； 2、熟悉plsql的使用； 3、熟悉sqlplus相关命令（登录、查询、导入导出等）登录sqlplus：第一：使用dos窗口登录sqlplus Sqlplus 用户名/密码@数据库实例名 Sqlplus system/密码@数据库实例名as sysdba 第二：使用oracle自带的一个sqlplus登录，提供界面，显得更简单一些。显示当前用户名：show user; 创建一个用户：create user 用户名identified by 密码；给用户赋予登录的权限：grant connect to 用户名；（此时才可以使用这个用户来登录这个数据库。）给其赋予dba的权限。修改用户的密码：alter user 用户名identified by 新密码；查询： Select * from t_user; Select id from t_user; Select name from t_user; Select birthday from t_user; Select id,name from t_user; 导入导出：导出表: （注意，导出表的exp命令不是在sqlplus下使用的，是在dos窗口下使用的命令。） exp userid=test/sa@test tables=(qx) file=d:\e1223.dmp exp userid=test/sa@test tables=(t_user,qx) file=f:\test.dmp 导出方案： Exp userid=test/sa@test owner=test file=f:\test2.dmp 导出数据库： Exp userid=test/sa@test full=y inctype=complete file=f:\all.dmp 导入表：下面以一个例子来说明：看下面的图，我的用户名test，密码sa，数据库实例名test，所有的表都在这里现在我执行导出表JD的操作：exp userid=test/sa@test tables=(jd) file=f:\jd.dmp 在我的f盘下就出现了这么一个.dmp文件

oracle中常用函数大全

oracle中常用函数大全 1、数值型常用函数函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual;

ORACLE常用函数和一些项目中常用的SQL语句

Oracle笔记实现中英文转换 Alter session set nls_language=‘ADMIN’ 从已知表中复制数据和结构 Create table test as select * from dept；从已知表中复制数据和结构，但是不包括数据 Create table test as select * from dept where 1=2 插入数据 Insert into test select * from dept 二、运算符 +——*/可以在select 语句中使用 ||连接两个字段，select deptno ||dname from dept 比较运算付：> >= = !=< <= 逻辑运算符：not ，or 集合运算符：intersect，union，union all，minus，使用ord er by 的时候必须使用位置序号，不能使用列名 Select *from emp intersect select * from emp where deptno=10； Select *from emp minus select * from emp where deptno=10； ---不包括重复行 Select *from emp where deptno=10 union select * from emp where deptno in（10，20）；----包括重复行 Select *from emp where deptno=10 union all select * from emp where deptno in（10，20）；三、常用ORACLE函数

oracle学习笔记

●PLSQL控制台输出语句 SET serveroutput ON; --打开控制台输出服务 dbms_output.put_line('values2='||var_val); --输出语句 ●PLSQL动态变量 var_str := '&input'; ●创建表空间和用户 --创建表空间 CREATE TABLESPACE "BCPBS" LOGGING DATAFILE 'D:\app\E430\oradata\orcl\BCPBS_01.ora' SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED, 'D:\app\E430\oradata\orcl\BCPBS_02.ora' SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; --建立用户 CREATE USER "BCPBS" PROFILE "DEFAULT" IDENTIFIED BY "bcpbs123" DEFAULT TABLESPACE "BCPBS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK; GRANT "CONNECT" TO "BCPBS"; GRANT "DBA" TO "BCPBS"; GRANT "RESOURCE" TO "BCPBS"; ●删除表空间和用户 drop user bcpbs cascade ; drop tablespace BCPBS including contents and datafiles cascade constraints ; ●自定义函数 CREATE OR REPLACE FUNCTION fun_level_value(level_value number) RETURN number IS return_value number:=null; BEGIN CASE level_value WHEN 0 THEN return_value:='0'; WHEN 1 THEN return_value:='1'; WHEN 2 THEN return_value:='2'; WHEN 3 THEN return_value:='3'; WHEN 6 THEN return_value:='8'; ELSE

oracle学习心得体会

oracle学习心得一、sqlserver的理解 sqlserver服务器就像一栋大楼，大楼里的机房就像服务器的数据库，机房里的电脑如同数据库里的表 1、登录用户可以登录服务器——可以进大楼 2、登录用户成为数据库用户才能进指定的数据库——进入大楼的人给了某个机房的钥匙才能进入机房 3、登录用户有权限使用表——进入机房的人有电脑的密码才能使用电脑二、oracle的理解 oracle服务器（全局数据库）就像一个商场，商场的每一家公司是表空间，公司的业务是表 1、数据库由多个表空间组成——商场里有多家公司组成 2、表空间由段组成——公司要有自己的经营业务，可以只有一个业务，就是一个表空间中只有一个段，可以有多个业务，就是一个表空间有多个段 3、段由区组成——单个业务的细分类别。例如有家公司经营三个业务，卖书，卖家电，

卖衣服，则每个业务就是一个段。而每个业务又有细分，比如卖书的话要进行分类了。计算机区，人文区，小说区等，每一区都要放上书架存放书籍，则书架就是oracle块，存放数据的三、数据库，表空间，用户（指定默认表空间），表统统由管理员管理四、在oem中管理数据库的步骤 1、创建 1)存储——表空间——创建表空间(tomspace)（类似于在sql中创建数据库，通常可以省略，使用默认表空间为users,,临时表空间为temp） 2)首先展开安全性——创建新用户（tom，指定表空间）（类似于在sql中指定数据库用户） 3)创建表——指定方案（用户）和表空间（列名不要带<>） 4)设置约束 5)输入信息：方案——用户名——表——右击——查看/编辑目录… 2、修改 1）方案——用户名——表 2）修改表结构，添加约束

Oracle学习笔记

Oracle的四个主要用户 1.超级管理员sys/change_on_install 2.普通管理员system/manager 3.普通用户scott/tiger 4.大数据用户sh/sh 简单查询 1.查询表结构 DESC 表名 2.简单查询SELECT [DISTINCT] *|[字段 [别名]] [,字段 [别名]] … FROM 表名 [别名] （1）查询时也可以使用四则运算，如：SELECT (字段*x)+y FROM 表名（2）可以使用“||”连接字段与字符串，如： select '工作与工资：' || job || sal 工作工资 from emp 执行结果： 3.限定查询SELECT [DISTINCT] *|[字段 [别名]] [,字段 [别名]] … FROM 表名 [别名] [WHERE 条件(s)] （1）Where字句后可以增加多个条件，最常见的条件就是最基本关系运算：>、<、>=、<=、!=(<>)、BE TWEEN…AND、LIKE、IN(指定范围)、IS NULL(为空)、AND(且)、OR(或)、NOT(非)。（2）多个条件可以使用AND与OR连接。例如：select * from emp where sal>1300 and sal<4000 （3）范围判断BE TWEEN…AND：BETWEEN 最小值 AND 最大值（大于等于最小值，小于等于最大值），BE TWEEN…AND 对数字、日期都可以使用！！！例如：select * from emp where sal between 1300 and 4000 例如：select * from emp where hiredate between '01-1月-1981' and '31-12月-1981' （4）判断为空IS (NOT) NULL 例如：select * from emp where comm is null 例如：select * from emp where comm is not null 例如：select * from emp where not comm is null （5）指定范围的判断(NOT) IN 例如：select * from emp where empno in(7369,7566,7799)

Oracle常用函数及使用案例(珍藏版)

Oracle常用函数及使用案例（珍藏版）一：sql函数： lower（char）:将字符串转化为小写的格式。 upper（char）:将字符串转化为大写的格式。 length（char）：返回字符串的长度。 substr（char，m，n）：取字符串的字串。案例1.将所有员工的名字按小写的方式显示 select lower(ename),sal from emp; 案例2.将所有员工的名字按大写的方式显示。 select upper(ename),sal from emp; 案例3.显示正好为五个字符的的员工的姓名。 select * from emp where length(ename)=5; 案例4.显示所有员工姓名的前三个字符。 select substr(ename,1,3) from emp;//从名字的第一个字符开始取，向后取三个字符。案例5.以首字母为大写的方式显示所有员工的姓名。（1）首字母大写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母小写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp; 案例6.以首字母为小写的方式显示所有员工的姓名。（需要有较高的灵活度，细心分析和清晰思路）（1）首字母小写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母大写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp; 案例7.函数（替换）：replace(char1,search_string,replace_string); 显示所有员工的姓名，用“我要替换A”替代所有“A”。 select replace(ename,'A','我是老鼠')from emp; 案例8.以首字母为小写的方式显示所有员工的姓名。 select replace(ename,substr(ename,1,1),lower(substr(ename,1,1)))from emp; 案例9.以首字母为大写的方式显示所有员工的姓名。 Select replace(ename,substr(ename,2,length(ename)-1),lower(substr(ename,2,length(ename) -1)))from emp; 二：数学函数：(在财务中用的比较多) ronud（sal）用于四舍五默认取整； ronud（sal，1）用于四舍五留一位小数。 trunc（sal）取整，忽略小数。截去小数部分。 trunc（sal，1）截取；小数点留一位，之后的右边的省去。 trunc（sal，-1）截取；只留整数，个位数取零。 floor（sal）向下最接近取整；比如1.1值为1.

Oracle数据库期末复习知识点整理

基础知识表3.2 Oracle数据类型

表3.3 XSB的表结构

操作表创建表 CREATE TABLE [schema.] table_name ( column_namedatatype [DEFAULT expression] [column_constraint][,…n] [,…n] ) [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace_name] [STORGE storage_clause] [CLUSTER cluster_name(cluster_column,…n)] [ENABLE | DISABLE ] [AS subquery] 【例】使用CRETE TABLE命令为XSCJ数据库建立表XSB，表结构参照表3.3。打开SQL*Plus工具，以system方案连接数据库，输入以下语句： CREATE TABLE XSB ( XH char(6) NOT NULL PRIMARY KEY, XM char(8) NOT NULL, XB char(2) DEFAULT '1' NOT NULL, CSSJ date NOT NULL, ZY char(12) NULL, ZXF number(2) NULL, BZ varchar2(200) NULL ); 修改表 ALTER TABLE [schema.] table_name [ ADD(column_namedatatype [DEFAULT expression][column_constraint],…n) ] /*增加新列*/ [ MODIFY([ datatype ] [ DEFAULT expression ] [column_constraint],…n) ] /*修改已有列的属性*/ [ STORAGE storage_clause ] *修改存储特征*/ [ DROP drop_clause ] /*删除列或约束条件*/ 【例】使用ALTER TABLE语句修改XSCJ数据库中的表。

PL_SQL命令的使用大全

SQL*PLUS命令的使用大全[zt] Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中，可以运行sql*plus命令与sql*plus语句。我们通常所说的DML、DDL、DCL语句都是sql*plus语句，它们执行完后，都可以保存在一个被称为sql buffer的内存区域中，并且只能保存一条最近执行的sql语句，我们可以对保存在sql buffer中的sql 语句进行修改，然后再次执行，sql*plus一般都与数据库打交道。除了sql*plus语句，在sql*plus中执行的其它语句我们称之为sql*plus命令。它们执行完后，不保存在sql buffer的内存区域中，它们一般用来对输出的结果进行格式化显示，以便于制作报表。下面就介绍一下一些常用的sql*plus命令： 1. 执行一个SQL脚本文件 SQL>start file_name SQL>@ file_name 我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。 2. 对当前的输入进行编辑 SQL>edit 3. 重新运行上一次运行的sql语句 SQL>/ 4. 将显示的内容输出到指定文件 SQL> SPOOL file_name 在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。 5. 关闭spool输出 SQL> SPOOL OFF 只有关闭spool输出，才会在输出文件中看到输出的内容。 6．显示一个表的结构 SQL> desc table_name 7. COL命令：主要格式化列的显示形式。该命令有许多选项，具体如下： COL[UMN] [{ column|expr} [ option ...]] Option选项可以是如下的子句: ALI[AS] alias CLE[AR] FOLD_A[FTER] FOLD_B[EFORE] FOR[MA T] format HEA[DING] text JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]} LIKE { expr|alias} NEWL[INE] NEW_V[ALUE] variable NOPRI[NT]|PRI[NT] NUL[L] text OLD_V[ALUE] variable ON|OFF WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED] 1). 改变缺省的列标题

oracle常用函数列表速查 (1)

Oracle常用函数列表速查 PL/SQL单行函数和组函数详解函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类：单行函数组函数本文将讨论如何利用单行函数以及使用规则。 SQL中的单行函数 SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY 等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。 SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename) 单行函数也可以在其他语句中使用，如update的SET子句，INSERT的V ALUES子句，DELET 的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。 NULL和单行函数在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿 column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2 不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子： update empset salary=(salary+bonus)*1.1 这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。所以正确的语句是： update empset salary=(salary+nvl(bonus,0)*1.1