oracle学习实践

一、Oracle命令实践：

0、启动sqlplus：

Windows下，在命令行输入：sqlplus，系统会提示输入用户名和密码。

或者，开始——>程序——>oracle 10g——>运行sql命令行，直接进入。

 

连接数据库：

connect username/userpassword;

最好用下法：

connect username

输入口令：

 

退出sqlplus：

quit

 

1、查看Oracle版本信息

select * from v$version;

 

2、查看哪些用户从哪些机器登陆ORACLE

select username, machine, terminal from v$session;

 

3、查看表字段信息

desc tablename；

 

4、查看表空间信息

select * from dba_data_files;

 

5、oracle中任何一行都有一个隐藏字段：rowid

select rowid，columname from tablename;

 

6、使select语句的查询结果自动生成序号

select rownum, columname from tablename;

 

搜索一个表的前n条记录

select * from employees where rownum<11;

 

7、查看数据库中某个表所在的tablespace

select tablespace_name from user_tables where table_name='tablename';

 

8、做一个和原表一样的备份表

create table backup_employees as (select * from employees);

 

9、显示当前连接用户

show user；

 

10、查看数据库表

select owner, table_name form all_tables where owner='HR';

 

11、测试SQL语句执行所用的时间

set timing on;

SQL 语句；

 

12、把select的结果导出到一个文本文件中

spool e:\hr_tables.txt;

select table_name from all_tables where owner='HR';

spool off;

 

13、修改表名

alter table backup_employees rename to employees_backup;

 

14、返回当前月的最后一天

select last_day(sysdate) from dual;

 

返回当期年份

select to_char(sysdate, 'YYYY') from dual;

 

返回当前月份

select to_char(sysdate, 'mm') from dual;

 

返回当前日份

select to_char(sysdate, 'dd') from dual;

 

返回当前小时

select to_char(sysdate, 'hh24') from dual;

 

返回当前分钟

select to_char(sysdate, 'mi') from dual;

 

返回当前秒

select to_char(sysdate, 'SS') from dual;

 

返回当前日期

select to_char(sysdate, 'YYYYMMDD') from dual;

 

返回当前日期

select trunc(sysdate) from dual;

 

返回当前时间

select to_char(sysdate, 'HH24:MI:SS') from dual;

 

15、执行写好的sql脚本

@e:\oraclexe\script\oracle.sql

 

16、查找指定列的最大值

select max(salary) from employees;

 

查找指定列的最小值

select min(salary) from employees;

 

17、对指定列求平均值

Select avg(salary) from employees;

 

对指定列求和

Select sum(salary) from employees;

 

18、

 

 

二、oracle事务管理

在Oracle中一个事务是由一个可执行的SQL语句开始，一个可执行SQL语句产生对实例的调用。在事务开始时，被赋给一个可用回滚段，记录该事务的回滚项。

 

1、提交事务

一个事务以下列任何一个出现而结束：

（1）   当COMMIT或ROLLBACK(没有SAVEPOINT子句)语句发出。

（2）   一个DDL语句被执行（如create，drop，rename，alter等）。在DDL语句执行前、后都隐式地提交。

（3）   断开与Oracle的连接，事务将自动提交。

（4）   用户进程异常终止，当前事务回滚。

 

提交一事务，即将在事务中由SQL语句所执行的改变永久化。在提交前，Oracle已有下列情况：

（1）   在SGA的回滚段缓冲区已生成回滚段记录，回滚信息包含有所修改值的老值。

（2）   在SGA的日志缓冲区已生成日志项。这些改变在事务提交前可进入磁盘。

（3）   对SGA的数据库缓冲区已作修改，这些修改在事务真正提交之前可进入磁盘。

 

在事务提交之后，有下列情况：

（1）   对于与回滚段相关的内部事务表记录提交事务，并赋给一个相应的唯一系统修改号(SCN)，记录在表中。

（2）   在SGA的日志缓冲区中日志项由LGWR进程写入到在线日志文件， 这是构成提交事务的原子事务。

（3）   在行上和表上的封锁被释放。

（4）   该事务标志为完成 。

注意：对于提交事务的数据修改不必由DBWR后台进程立即写入数据文件，可继续存储在SGA的数据库缓冲区中，在最有效时将其写入数据文件。

 

2、回滚事务

回滚事务的含义是撤消未提交事务中的SQL语句所作的对数据修改。Oracle允许撤消未提交的整个事务，也允许撤消部分。

在回滚整个事务(没有引用保留点)时，有下列情况：

（1）   在事务中所有SQL语句作的全部修改，利用相应的回滚段被撤消。

（2）   所有数据的事务封锁被释放。

（3）   事务结束。

 

当事务回滚到一保留点(具有SAVEPOINT)时，有下列情况：

（1）   仅在该保留点之后执行的语句被撤消。

（2）   该指定的保留点仍然被保留，该保留点之后所建立的保留点被删除。

（3）   该保留点之后所获取的全部表封锁和行封锁被释放，但指定的保留点以前所获取的全部数据封锁继续保持。

（4）   该事务仍可继续。

 

3、保留点

保留点(savepoint)是在一事务范围内的中间标志，经常用于将一个长的事务划分为小的部分。保留点可标志长事务中的任何点，允许可回滚该点之后的工作。在应用程序中经常使用保留点;例如一过程包含几个函数，在每个函数前可建立一个保留点，如果函数失败，很容易返回到每一个函数开始的情况。在回滚到一个保留点之后，该保持点之后所获得的数据封锁被释放。

 

4、实例

（1）

SQL> create table a ( i int);

表已创建。

SQL> insert into a values(1);

已创建 1 行。

SQL> insert into a values(2);

已创建 1 行。

SQL> rollback;

回退已完成。

SQL> select * from a;

未选定行。

 

（2）

SQL> create table a ( i int);

表已创建。

SQL> insert into a values(1);

已创建 1 行。

SQL> create table b ( i int);

表已创建。

SQL> rollback;

回退已完成。

SQL> select * from a;

         I                                                              

----------                                                                    

         1

 

（3）

首先看下面的例一。如果触发器t_trigger被触发，但是insert语句执行失败。此时，执行结果会怎样呢？事实上，t2表中的cnt值将不会被更新。这是由于，在ORACLE中，客户端提交的语句（在这个例子中是INSERT语句）或者完全执行成功，或者完全失败。
??=====================================================
??例一：
??> create table t2 ( cnt int );
??Table created.
??> insert into t2 values ( 0 );
??1 row created.
??> create table t ( x int check ( x>0 ) );
??Table created.
??> create trigger t_trigger
??before insert or delete on t for each row
??begin
?? if ( inserting ) then
?? update t2 set cnt = cnt +1;
?? else
?? update t2 set cnt = cnt ‐1;
?? end if;
?? dbms_output.put_line( 'I fired and updated' ||sql%rowcount || 'rows' );
??end;
??/
??Trigger created
??=====================================================
??
??我们通过例二来验证。
??在例二中，1将被成功插入T表。第二个INSERT语句违反了完整性约束。通过获取DBMS_OUTPUT信息，我们看到触发器事实上也执行了，t2中的cnt值也被更新了。但最终t2中的cnt值为1。由此保证了插入操作的原子性。
??=====================================================
??例二：
??> set serveroutput on
??> insert into t values ( 1 );
??I fired and updated 1 rows
??1 row created.
??> insert into t values (‐1 );
??insert into t values (‐1 )
??*
??ERROR at line 1:
??ORA‐02290: check constraint (TKYTE.SYS_C001570) violated
??> exec null /* this is needed to retrieve the dbms_output */
??I fired and updated 1 rows
??PL/SQL procedure successfully completed.
??> select * from t2;
??CNT
??‐‐‐‐‐‐‐‐‐‐
??1
??=====================================================
??
??事实上，系统在两个INSERT语句前后分别作了这样的工作，见例三
??=====================================================
??例三：
??Savepoint statement1;
??Insert into t values ( 1 );
??If error then rollback to statement1;
??Savepoint statement2;
??Insert into t values ( ‐1 );
??If error then rollback to statement2;
??=====================================================
??
??比较有趣的是，ORACLE把PL/SQL匿名块整个作为一个语句。见例四。当过程块中有语句执行出错时，过程块中出错语句之前已成功执行的语句也将被回滚掉。
??=====================================================
??例四：
??> create or replace procedure p
??2 as
??3 begin
??4 insert into t values ( 1 );
??5 insert into t values (‐1 );
??6 end;
??7 /
??Procedure created.
??> select * from t;
??no rows selected
??> select * from t2;
??CNT
??‐‐‐‐‐‐‐‐‐‐
??0
??> begin
??2 p;
??3 end;
??4 /
??I fired and updated 1 rows
??I fired and updated 1 rows
??begin
??*
??ERROR at line 1:
??ORA‐02290: check constraint (TKYTE.SYS_C001570) violated
??ORA‐06512: at ?TKYTE.P?, line 5
??ORA‐06512: at line 2
??> select * from t;
??no rows selected
??> select * from t2;
??CNT
??‐‐‐‐‐‐‐‐‐‐
??0
??=====================================================
??
??但对这个存储过程稍作修改，我们将会得到一个完全不同的结果。见例五。ORACLE还是把整个存储过程P作为一个语句来执行，因为过程体中的执行错误被缓存从而忽略，因此整个存储过程得以成功执行，系统并没有回滚到SAVEPOINT。
??=====================================================
??例五：
??> begin
??2 p;
??3 exception
??4 when others then null;
??5 end;
??6 /
??I fired and updated 1 rows
??I fired and updated 1 rows
??PL/SQL procedure successfully completed.
??> select * from t;
??X
??‐‐‐‐‐‐‐‐‐‐
??1
??> select * from t2;
??CNT
??‐‐‐‐‐‐‐‐‐‐
??1
??=====================================================
??
??通过上面两个例子我们发现，仅仅在过程块中增加了异常处理，却导致执行结果发生了改变。那么我们在开发应用时应该如何处理呢？见例六：
??例六：
??=====================================================
??> begin
??2 savepoint sp;
??3 p;
??4 exception
??5 when others then
??6 rollback to sp;
??7 end;
??8 /
??I fired and updated 1 rows
??I fired and updated 1 rows
??PL/SQL procedure successfully completed.
??> select * from t;
??no rows selected
??> select * from t2;
??CNT
??‐‐‐‐‐‐‐‐‐‐
??0
??=====================================================

 

三、oracle备份恢复技术

1、oracle技术文档

Oracle? Database

Backup and Recovery Basics

10g Release 2 (10.2) 

B14192-03

 

四、oracle锁技术

 

1、下面来自网络的文章

Oracle数据库中的锁机制研究-开发技术

来源:中国网管联盟  作者:bitsCN

    本文通过对Oracle锁机制的研究，首先介绍了Oracle锁的种类，并描述了实际应用中遇到的与锁相关的异常情况，特别对经常遇到的由于等待锁而使事务被挂起的问题进行了定位及解决，并对死锁这一比较严重的现象，提出了相应的解决方法和具体的分析过程。

网管网bitsCN_com

    数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。 feedom.net

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。 54com.cn

    在实际应用中经常会遇到的与锁相关的异常情况，如由于等待锁事务被挂起、死锁等现象，如果不能及时地解决，将严重影响应用的正常执行，而目前对于该类问题的解决缺乏系统化研究和指导，本文在总结实际经验的基础上，提出了相应的解决方法和具体的分析过程。

54com.cn

    Oracle数据库的锁类型 网管网bitsCN.com

    根据保护的对象不同，Oracle数据库锁可以分为以下几大类：DML锁（data locks，数据锁），用于保护数据的完整性；DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；内部锁和闩（internal locks and latches），保护数据库的内部结构。

网管网bitsCN_com

    DML锁的目的在于保证并发情况下的数据完整性，本文主要讨论DML锁。在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

中国网管论坛bbs.bitsCN.com

当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。如表1所示： 中

表1 Oracle的TM锁类型
锁模式	锁描述	解释	SQL操作
0	none
1	NULL	空	Select
2	SS(Row-S)	行级共享锁，其他对象只能查询这些数据行	Select for update、Lock for update、Lock row share 中国网管联盟www、bitsCN、com
3	SX(Row-X)	行级排它锁，在提交前不允许做DML操作	Insert、Update、Delete、Lock row share 网管联盟
4	S(Share)	共享锁	Create index、Lock share
5	SSX(S/Row-X)	共享行级排它锁	Lock share row exclusive
6	X(Exclusive)	排它锁	Alter table、Drop able、Drop index、Truncate table 、Lock exclusive 网管网bitsCN_com

在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。 中国网管论坛bbs.bitsCN.com

    当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-60的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应，大量事务失败等。 中国网管联盟www、bitsCN、com

    TX锁等待的分析 中国网管论坛bbs.bitsCN.com

    在介绍了有关地Oracle数据库锁的种类后，下面讨论如何有效地监控和解决锁等待现象，及在产生死锁时如何定位死锁的原因。

中国网管联盟

监控锁的相关视图 数据字典是Oracle数据库的重要组成部分，用户可以通过查询数据字典视图来获得数据库的信息。和锁相关的数据字典视图如表2所示：

中国网管联盟表2 数据字典视图说明
视图名	描述	主要字段说明
v$session	查询会话的信息和锁的信息。	sid,serial#：表示会话信息。 中国网管论坛bbs.bitsCN.com program：表示会话的应用程序信息。 网管网bitsCN.com row_wait_obj#：表示等待的对象。 网管网bitsCN.com 和dba_objects中的object_id相对应。 中国网管联盟
v$session_wait	查询等待的会话信息。	sid：表示持有锁的会话信息。 网管联盟 Seconds_in_wait：表示等待持续的时间信息 中国网管联盟www_bitscn_com Event：表示会话等待的事件。 网管网bitsCN_com
v$lock	列出系统中的所有的锁。	Sid：表示持有锁的会话信息。 网管网bitsCN_com Type：表示锁的类型。值包括TM和TX等。 网管联盟 ID1：表示锁的对象标识。 中国网管联盟www、bitsCN、com lmode,request：表示会话等待的锁模式的信 54com.cn 息。用数字0－6表示，和表1相对应。 网管网bitsCN_com
dba_locks	对v$lock的格式化视图。	Session_id：和v$lock中的Sid对应。 网管网bitsCN.com Lock_type：和v$lock中的type对应。 54com.cn Lock_ID1： 和v$lock中的ID1对应。 网管联盟 Mode_held,mode_requested：和v$lock中 网管联盟 的lmode,request相对应。 中国网管论坛bbs.bitsCN.com
v$locked_object	只包含DML的锁信息，包括回滚段和会话信息。	Xidusn,xidslot,xidsqn：表示回滚段信息。和 网管联盟 v$transaction相关联。 feedom.net Object_id：表示被锁对象标识。 54com.cn Session_id：表示持有锁的会话信息。 中国网管联盟 Locked_mode：表示会话等待的锁模式的信 54ne.com 息，和v$lock中的lmode一致。 网管网bitsCN.com

 

TX锁等待的监控和解决在日常工作中，如果发现在执行某条SQL时数据库长时间没有响应，很可能是产生了TX锁等待的现象。为解决这个问题，首先应该找出持锁的事务，然后再进行相关的处理，如提交事务或强行中断事务。 中国网管论坛bbs.bitsCN.co

死锁的监控和解决在数据库中，当两个或多个会话请求同一个资源时会产生死锁的现象。死锁的常见类型是行级锁死锁和页级锁死锁，Oracle数据库中一般使用行级锁。下面主要讨论行级锁的死锁现象。

中国网管联盟

    当Oracle检测到死锁产生时，中断并回滚死锁相关语句的执行，报ORA-00060的错误并记录在数据库的日志文件alertSID.log中。同时在user_dump_dest下产生了一个跟踪文件，详细描述死锁的相关信息。

54ne.com

    在日常工作中，如果发现在日志文件中记录了ora-00060的错误信息，则表明产生了死锁。这时需要找到对应的跟踪文件，根据跟踪文件的信息定位产生的原因。

中国网管联盟

如果查询结果表明，死锁是由于bitmap索引引起的，将IND_T_PRODUCT_HIS_STATE索引改为normal索引后，即可解决死锁的问题。