日积月累-oracle笔记
------------------------------------------------------------------------------------------
锁：行级排他锁TX，表级共享锁TM(主要防止其他事务对表进行DDL操作)
block：修改同一条记录的多个事务之间。一个事务修改未提交，另一个事务也修改该记录时，处于等待状态。行级锁。TX。如果修改的是不同的记录，不会产生阻塞。
DML锁:行级锁TX（排他锁）；表级锁TM（共享） X-exclude
DDL锁：一般是排他锁
查看自己的SID：select sid from v$mystat where rownum=1;
v$transaction --记录当前活动的事务，提交或者回滚后记录清除。
select addr,xidusn from V$transaction;
v$lock
select sid,ty,id1,id2,lmode,request from v$lock;
v$lock与v$transaction的关系：
select sid,type,trunc(id1/power(2,16)) usn,--提取高16位
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 req,lmode,requwst
from v$lock where sid=11;
select xidusn,xidslot,xidsqn req from v$transaction;
找到上锁的id1，再从all_objects中找到上锁对应的对象。
可以一步到位达到此目的：select * from v$locked_object;
lock table emp in share mode;--oracle一般是自动加锁，也可以手动加锁。commit后释放锁。
---------------------------------------------------------------------------------------
数据导入
1、直接导入：同一个数据库中不同表之间的数据迁移
insert /*+ append*/ into tb1 select * from tb2;
注意：未提交或者回滚前，查询该表会报错ORA-12838
2、并行导入：多个分区表（各个进程在不同分区上进行操作），一个分区表（多个进程在一个分区上进行操作），非分区表（各个进程在一张表上进行操作）
多个进行同时进行，在通过一个进程进行合并。
insert /*+ parallel(tb1,2)*/ into tb1 select * from tb2;
3、sqlldr导入：
方法一：
准备一个控制文件ld.ctl如下：
load data
infile *
into table tb1
fields termibated by ','
(col1,col2,col3)
begindata
a,aa,aaa
b,bb,bbb
c,cc,ccc
在系统中执行命令 sqlldr usserid=scott/tiger control=ld.ctl
方法二：
准备数据文件data.dat如下：
a,aa,aaa
b,bb,bbb
c,cc,ccc
准备一个控制文件ld.ctl如下：
load data
infile data.dat
into table tb1
fields termibated by ','
(col1,col2,col3)
在系统中执行命令 sqlldr usserid=scott/tiger control=ld.ctl
数据文件类型：固定长度记录，指定长度的变长记录，已特定符号为记录分割符。
a、数据文件data.dat如下,每条记录固定12个字节：
a,aaaa,aaaaa
b, bb, bb
c,cccc, ccc
准备一个控制文件ld.ctl如下：
load data
infile 'data.dat' "fix 12"
into table tb1
fields termibated by ','
(col1,col2,col3)
b、数据文件data.dat如下，前三位表示长度信息：
009aaaaa,aaaa,aaaaa
012bb,bb,bb
013c,cccc,ccc
准备一个控制文件ld.ctl如下：
load data
infile 'data.dat' "var 3"
into table tb1
fields termibated by ','
(col1,col2,col3)
c、数据文件data.dat如下，已|和换行符为记录分割符：
aaaaa,aaaa,aaaaa|
bb,bb,bb|
c,cccc,ccc
准备一个控制文件ld.ctl如下：
load data
infile 'data.dat' "str '|\n'"
into table tb1
fields termibated by ','
(col1,col2,col3)
d、数据文件中，一条记录中有换行（多行表示一条记录），控制文件怎么写。。。。
---压缩与压缩--------------------------------------------------------------------------------------------------
优点：压缩后，节省空间并在一定条件下提高查询性能。
缺点：压缩后，在更新操作时，性能有很大下降，MERGE/UPDATE操作应注意
--1）对表属性进行修改，影响以后新数据
要创建一个压缩的表，可在CREATE TABLE语句中使用COMPRESS关键字。COMPRESS关键字指示Oracle数据库尽可能以压缩的格式存储该表中的行。
或者，你可以用ALTER TABLE语句来修改已有表的压缩属性，如：ALTER TABLE TABLE_NAME COMPRESS
--2）对表中以存储数据进行压缩解压缩
如果你有一个已有的未压缩/压缩表，那么你可以利用ALTER table XX MOVE COMPRESS/NOCOMPRESS语句对其进行压缩/ 解压缩。
---解锁--------------------------------------------------------------------------------------------------
0.
通过 v$session，v$lock 查看每个用户下有什么锁
select * from v$session t1,v$lock t2
where t1.SID=t2.SID
and t1.STATUS='ACTIVE'
and t1.SCHEMANAME='AICS';
通过alter system kill session 'sid, serial#'把session kill掉
alter system kill session '10,34'
1.通过v$session,v$process得出后台进程ID，在用系统命令杀系统进程
select * from v$session s, v$process p
where s.PADDR=p.ADDR
and s.SCHEMANAME='AICS'
and s.STATUS='ACTIVE';
直接在os下杀进程：KILL -9 刚才查出的SPID
2.通过 v$session，v$locked_object 查看谁锁定了资源
select t1.sid, t1.serial#, t1.username, t1.logon_time from v$session t1 , v$locked_object t2
where t1.sid = t2.session_id order by t1.logon_time;
通过alter system kill session 'sid, serial#'把session kill掉
alter system kill session '10,34'
3.查询得到当前数据库中锁的等级，锁的用户，以及解锁：
查锁
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL;
解锁
$sqlplus /"as sysdba"
SQL>alter system kill session 'sid,serial';
如果解不了。直接倒os下kill进程kill －9 spid
--------------------------------------------------------------------------------------------------------
---闪回-------------------------------------------------------------------------------------------------------
闪回表：
flashback table test to before drop
闪回数据：
create table monthincall_syatem_agentnumold as
select * from monthincall_syatem_agentnum as of timestamp to_timestamp('2012-01-31 11:30','yyyy-mm-dd hh24:mi');
-----------------------------------------------------------------------
--闪回不可以在事务中使用，否则会报错；
--dbms_flashback包不能在sys中使用(sys不支持闪回)，可通过grant execute on dbms_flashback to 用户名,这样就可以在其他的用户使用；
--基于时间的闪回：dbms_flashback.enable_at_time(date);
--基于系统改变号来恢复：1.获取系统改变号：select dbms_flashback.get_system_change_number scn from dual;
2.execute dbms_flashback.enable_at_system_chage_number(scn);
--退出闪回状态：execute dbms_flashback.disable;
1.insert into tablename select * from tableame as of timestamp thetime/1440;
或者insert into tablename select * from tablename as of timestamp_to_scn scn;
2.declare
cursor my_value is select * from hr.my_test_table where age=20;
value_row my_test_table%rowtype;
begin
dbms_flashback.enable_at_time(sysdate-25/1440);
open my_value;
dbms_flashback.disable;
loop
fetch my_value into value_row;
exit when my_value%notfound;
insert into hr.my_test_table values(value_row.name,value_row.age,value_row.birth);
end loop;
close my_value;
commit;
end;
/
----------------------------------------------------------------------
oracle 并行执行
select /*+ full(e) parallel(e,10)*/ e.create_date from ecr_log e where e.description like '%Session%';
1、看看并行选件是否安装
Select * FROM V$OPTION where parameter like 'Parallel%';
看看Parallel execution是不是TRUE
2、如果是TRUE，执行语句后查看
select * from V$pq_sesstat where name like '%Parallelized';
如果Queries Parallelized>0就说明是执行了并行
3.当前时刻有那些并行查询在跑?
SQL> desc v$px_session
4.为什么本该并行执行的查询没有并行执行呢?
系统的并行度由parallel_max_servers 决定,如果它的并行度为5.如果有一个并行度为5的查询在跑,那么系统在这条查询运行完成前
是不能再跑并行查询的,该并行查询将会以非并行方式运行.
5.怎么才能让查询有并行执行?
如果建表时指定了并行度,例:
Create TABLE tt(a VARCHAR2 (5)) PARALLEL 5;
alter table tt parallel 7;
----------------------------------------------------------------------
删除分区：
alter table XXXXX truncate partition part_XXX;
-----------------------------------------------------------
Oracle自定义异常：
RAISE_APPLICATION_ERROR ( error_number_in IN NUMBER, error_msg_in IN VARCHAR2);
error_number: 自定义的错误编号。允许自定义的错误代码的范围为-20000 -- -20999
error_msg：自定义的错误内容。
如：RAISE_APPLICATION_ERROR (-20999, '开始时间未传入');
-----------------------------------------------------------
更新表中多个字段值
update tagentoprinfo_bak t
set t.mediatype=5,
t.agenttype=0,
t.calltype=0,
t.locationid=571
设置session日期显示格式
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
----------------------------------------------------------------------------------------------------------
oemapp dbastudio，Enterprise Management console，从https://localhost:1158/em/登入，实现配置资料库Enterprise Manager Configuration Assistant。
Oracle11g手工创建EM
1. 清除em相关配置
--删除db配置
emca -deconfig dbcontrol db
--清除repository
emca -repos drop
2. 配置em
--创建db control
emca -config dbcontrol db -repos create
3.启动em
emctl start dbconsole
em控制台地址：
https://hostname:1158/em/console
em中文问题解决：
打开IE， 工具-》Internet选项-》常规， 选择语言，添加中文和英文，并且吧英文放在上面
原文链接：http://www.cn-java.com/www1/?action-viewnews-itemid-105939
