--ORACLE启动停止服务:
--启动监听 lsnrctl start 启动实例 net start oracleserviceORCL
--连接数据库 sqlplus /nolog, conn sys /assysdba,startup
--停止数据库 sqlplus /nolog,connect /as sysdba,shutdwon abort;
--关闭服务 停止实例 net stop oracleserviceoradb 停止监听 lsnrctl stop
--连接远程数据库 sqlplus sys/pwd@ip:1521/test as sysdba
-- sqlplusj1_dw/oracle@143.12.64.188:1521/ahjcbb
--插入etl_table_mapping 在核心征管中的库里跑
insert intoG3_1122_ZBQ.etl_table_mapping
select
'&INFTAB'TARGET_TAB, --目标表名称
F.COLUMN_NAMETARGET_TAB_COL, --目标表列名称
'G3' SJLYXT, --数据来源系统,C2 CTAIS2.0,ZR 中软上海,G3 中软金三
1MAP_ORDER, --映射次序
nvl(SOURCE_TAB,'CCTV'), --源表名称 别名,别名只能取一位大写字母
SOURCE_TAB_COL, --源表列名称
gz TRANS_EXPR, --转换表达式,在源表的列上加表达式,带表别名前缀,相等则为NULL
MULTI_TAB_JOIN, --多表连接,源表带表名前缀的等连接,没有则为NULL
null WHERE_EXPR, --WHERE条件,在源表的列前带表别名前缀,没有则为NULL
'Y' YXBZ, --有效标志,N 无效,Y 有效
nullTRANS_ADDEXPR
from (
selecta.COLUMN_NAME,b.comments, a.DATA_TYPE,
null,null,null,null,null,
case when A.COLUMN_NAME='XGRQ' then'NVL(to_char(A.XGRQ,''yyyymmdd hh24miss''),to_char(A.LRRQ,''yyyymmddhh24miss''))'
when A.COLUMN_NAME='SJJZRQ' then 'to_char(sysdate,''yyyymmddhh24miss'')'
when A.COLUMN_NAME='SJTJRQ' then 'to_char(sysdate,''yyyymmddhh24miss'')'
when A.COLUMN_NAME='SJBGBZ' then '''I'''
when A.COLUMN_NAME='NSRDAH' then 'nvl(B.NSRSBH,''N'')'
when A.COLUMN_NAME='NSRZHDAH' then'''G3''||A.DJXH'
when A.COLUMN_NAME='SJLYXT' then '''G3'''
when A.COLUMN_NAME='XGR_DM' then'NVL(A.XGR_DM,A.LRR_DM)'
else
DECODE(T.DATA_TYPE,'DATE','TO_CHAR(A.'||T.COLUMN_NAME||',''YYYYMMDD'')','')end GZ
,null, DECODE(A.COLUMN_NAME ,'NSRDAH','DJ_NSRXX B',''||''||'&G3TAB A' ) SOURCE_TAB ,
DECODE( A.COLUMN_NAME,'NSRDAH','A.DJXH=B.DJXH(+)',NULL ) MULTI_TAB_JOIN
,t.COLUMN_NAME SOURCE_TAB_COL ,T.comments
from dba_tab_columns a , dba_col_comments b ,
(selectt.TABLE_NAME,t.COLUMN_NAME,f.comments,T.DATA_TYPE FROM
user_tab_columns t,user_col_comments f
where t.TABLE_NAME=f.table_name
and t.COLUMN_NAME=f.column_name
and t.TABLE_NAME='&G3TAB' ) t
wherea.OWNER='G3_1122_ZBQ'
and a.OWNER=b.owner
anda.TABLE_NAME=b.table_name
anda.COLUMN_NAME=b.column_name
anda.TABLE_NAME='&INFTAB'
and (a.COLUMN_NAME=t.COLUMN_NAME(+)
-- or b.comments=t.comments(+)
)
order by a.COLUMN_ID
) f
--查询在准备区
select a.*,a.rowid
from etl_table_mapping a, all_tab_columns b
where a.target_tab ='INFT04_FP_FWYPHX_ZB'
and a.sjlyxt = 'G3'
and b.owner = 'G3_1122_ZBQ'
and a.target_tab = b.table_name
and a.target_tab_col = b.column_name
order by a.map_order, b.column_id;
--找出表中没映射mapping的字段
select a.table_name,a.column_name
from user_tab_columns a,
(select distinct target_tab,TARGET_TAB_COL
from etl_table_mapping
where SJLYXT = 'G3'
and target_tab like 'INFT04_FP_%') b
where a.table_name = b.target_tab(+)
and a.column_name = b.target_tab_col(+)
and b.target_tab_col is null
and a.table_name like 'INFT04_FP_%'
order by a.table_name;
--字段补全
SELECTF.TABLE_NAME TARGET_TAB, --目标表名称
F.COLUMN_NAME TARGET_TAB_COL, --目标表列名称
'G3' SJLYXT, --数据来源系统,C2 CTAIS2.0,ZR 中软上海,G3 中软金三
1 MAP_ORDER, --映射次序
nvl(SOURCE_TAB,'CCTV'), --源表名称 别名,别名只能取一位大写字母
SOURCE_TAB_COL, --源表列名称
TRANS_EXPR, --转换表达式,在源表的列上加表达式,带表别名前缀,相等则为NULL
MULTI_TAB_JOIN, --多表连接,源表带表名前缀的等连接,没有则为NULL
WHERE_EXPR, --WHERE条件,在源表的列前带表别名前缀,没有则为NULL
'Y' YXBZ, --有效标志,N 无效,Y 有效
TRANS_ADDEXPR
FROM (select T.*
from etl_table_mapping t
where t.sjlyxt = 'G3'
and t.map_order = 1
AND T.TARGET_TAB = 'INFT04_FP_YJMX')A,
USER_TAB_COLUMNS F
WHERE A.TARGET_TAB(+) = F.TABLE_NAME
AND A.TARGET_TAB_COL(+) = F.COLUMN_NAME
AND F.TABLE_NAME = 'INFT04_FP_YJMX'
AND F.COLUMN_NAME != 'SJSCRQ'
ORDER BY F.COLUMN_ID
--修改字段名
alter tableINFT04_FP_YJMX rename column CYJG to CYJG_DM;
--修改字段长度
alter tableINFT04_FP_YJMX modify KJQK_DM VARCHAR2(12 CHAR);
(SELECT JHBZUUID ,nvl(SUM(JHLGSL),0) JHLGSL FROM FP_FPJHBZ_PTFPMX GROUP BY JHBZUUID ) C
CASE C.SPZT WHEN '0'THEN '04' WHEN '2' THEN '03' ELSE 'G3' END
--有数据的用户 cq0807_hx_zgxt
select * fromuser_tab_comments where table_name like 'FP_%SFC%';
--查询重复数据
selecta.bdxh,count(*) from LDMT04_FP_NSRPZHDXX a group by a.bdxh having count(*)>1
--TRUNCATE和DELETE有以下几点区别
--1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销,而TRUNCATE则不会被撤销。
--2、TRUNCATE是一个DDL语言,向其他所有的DDL语言一样,他将被隐式提交,不能对TRUNCATE使用ROLLBACK命令。
--3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时,经过TRUNCATE操作后的表比DELETE操作后的表要快得多。
--4、TRUNCATE不能触发任何DELETE触发器。
--5、不能授予任何人清空他人的表的权限。
--6、当表被清空后表和表的索引讲重新设置成初始大小,而delete则不能。
--7、不能清空父表。
truncate tableINFT04_FP_FSXX;
--1.创建同义词:
--可能需要在user用户中给当前用户(user2)授权:
grantselect/delete/update on user2
create publicsynonym table_name for user.table_name;
--2.删除同义词:
drop public synonymtable_name;
--3.查看所有同义词:
select * fromdba_synonyms;
--创建序列
CREATE SEQUENCElsfp_sequence
INCREMENT BY 1-- 每次加几个
START WITH 1-- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加,不循环
NOCACHE -- 不建缓冲区
--创建触发器
create or replace trigger trig_lsfp
before insert on DM0_FP_LSFP
for each row
begin
select to_char(lsfp_sequence.nextval) into:new.xh from dual;
end trig_lsfp;
drop triggertrig_lsfp;
DROP SEQUENCElsfp_sequence;
--检查etl_table_mapping列和INF表列的异同,group map_order 个数相同
--表多列
select 'TABLE', a.*
from (select table_name, column_name
from user_tab_columns
where table_name in (select target_tabfrom etl_table_mapping)
and column_name <> 'SJSCRQ'
minus
select target_tab, target_tab_col frometl_table_mapping) a
order by table_name, column_name;
--MAPPING多列
select 'MAPPING',a.*
from (select target_tab, target_tab_col
from etl_table_mapping
minus
select table_name, column_name
from user_tab_columns
where table_name in (select target_tabfrom etl_table_mapping)) a
order by target_tab, target_tab_col;
--按sjlyxt,map_order分组,各表MAPPING个数不一致
select target_tab
from (select target_tab, sjlyxt, map_order,count(*) cn
from etl_table_mapping a
where target_tab in (select target_tab
frometl_table_mapping b
havingcount(distinct sjlyxt || map_order) > 1
group bytarget_tab)
group by target_tab, sjlyxt, map_order
order by target_tab, sjlyxt,map_order)
havingcount(distinct cn) > 1
group by target_tab
order by target_tab;
--按sjlyxt,map_order分组,各表MAPPING的个数
select target_tab,sjlyxt, map_order, count(*) cn
from etl_table_mapping a
where target_tab in
(select target_tab
from (select target_tab, sjlyxt,map_order, count(*) cn
from etl_table_mapping a
where target_tab in (selecttarget_tab
frometl_table_mapping b
havingcount(distinct sjlyxt || map_order) > 1
group bytarget_tab)
group by target_tab, sjlyxt,map_order
order by target_tab, sjlyxt,map_order)
having count(distinct cn) > 1
group by target_tab)
group by target_tab, sjlyxt, map_order
order by target_tab, sjlyxt, map_order;
--group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面
--在 SQL 中增加HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。
SELECT column_name,aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVINGaggregate_function(column_name) operator value;
--TOP 子句用于规定要返回的记录的数目。
--对于拥有数千条记录的大型表来说,TOP 子句是非常有用的。
SELECT * FROMPersons WHERE ROWNUM <= 5;
SELECT TOP 2 * FROMPersons;
--查找大量重复记录
select empno fromempa group by empno having count(*) >1;
Select * From empaWhere ROWID Not In(Select Min(ROWID) From empa Group By empno);
--查找少量重复记录
select * from empa awhere rowid<>(select max(rowid) from empa where empno=a.empno );
--删除重复记录的几种方法:
--(1).适用于有大量重复记录的情况(列上建有索引的时候,用以下语句效率会很高):
Delete empa
Where empno In (Select empno From empa GroupBy empno Having Count(*) > 1)
And ROWID Not In
(Select Min(ROWID) From empa Group Byempno Having Count(*) > 1);
Delete empa
Where ROWID Not In (Select Min(ROWID) Fromempa Group By empno);
--2).适用于有少量重复记录的情况(注意,对于有大量重复记录的情况,用以下语句效率会很低):
Delete empa a
where rowid <> (select max(rowid) fromempa where empno = a.empno);
--但如果就是想要用 rownum > 10 这种条件的话话就要用嵌套语句,把 rownum 先生成,然后对他进行查询。
select * from (seletrownum as rn, t1.* from a where.. .) where rn > 10;
select * from(selectrownum no ,id,name from student) where no>2;
--另外还要注意:rownum不能以任何基表的名称作为前缀。
--查询rownum在某区间的数据,必须使用子查询。例如要查询rownum在第二行到第三行之间的数据,包括第二行和第三行数据,那么我们只能写以下语句,先让它返回小于等于三的记录行,然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。
select * from(select rownum no,id,name from student where rownum<=3 ) where no >=2;
--可以看出,rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号,rowid也是顺序分配的。为了解决这个问题,必须使用子查询
select rownum,id,name from (select * from student order by name);
--笔者在工作中有一上百万条记录的表,在jsp页面中需对该表进行分页显示,便考虑用rownum来作,下面是具体方法(每页显示20条):
select * fromtabname where rownum<20 order by name;
--但却发现oracle却不能按自己的意愿来执行,而是先随便取20条记录,然后再order by,后经咨询oracle,说rownum确实就这样,想用的话,只能用子查询来实现先排序,后rownum,方法如下:
select * from(select * from tabname order by name) where rownum<20;
--但这样一来,效率会低很多。后经笔者试验,只需在order by 的字段上加主键或索引即可让oracle先按该字段排序,然后再rownum;方法不变
select * fromtabname where rownum<20 order by name;
--下面介绍的是导入导出的实例,向导入导出看实例基本上就可以完成,因为导入导出很简单。
--数据导出:
-- 1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
expsystem/manager@TEST file=d:\daochu.dmp full=y;
--2 将数据库中system用户与sys用户的表导出
expsystem/manager@TEST file=d:\daochu.dmp owner=(system,sys);
--3 将数据库中的表table1 、table2导出
expsystem/manager@TEST file=d:\daochu.dmp tables=(table1,table2) ;
--4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
expsystem/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" wherefiled1 like '00%'\";
--上面是常用的导出,对于压缩我不太在意,用winzip把dmp文件可以很好的压缩。不过在上面命令后面 加上compress=y 就可以了
--数据的导入
--1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
impsystem/manager@TEST file=d:\daochu.dmp;
--上面可能有点问题,因为有的表已经存在,然后它就报错,对该表就不进行导入。-在后面加上 ignore=y 就可以了。
--2 将d:\daochu.dmp中的表table1 导入
impsystem/manager@TEST file=d:\daochu.dmp tables=(table1) ;
--加 ignore=y 参数,改参数会忽略创建表时的错误并加以继续。
-- 如果是 impdp,加table_exists_action=append 参数。table_exists_action:action to take if imported object already exists.
-- valid keywords:(skip),append,replace andtruncate.
--基本上上面的导入导出够用了。不少情况我是将表彻底删除,然后导入。
--注意:-你要有足够的权限,权限不够它会提示你。-数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。
--数据导出:
exp hkb/hkb@boss_14full=y file=c:\orabackup\hkbfull.dmp log=c:\orabackup\hkbfull.log;
--导出注意事项:导出的是当前用户的的数据,当前用户如果有DBA的权限,则导出所有数据!
--同名用户之间的数据导入:
imp hkb/hkb@xe file=c:\orabackup\hkbfull.dmplog=c:\orabackup\hkbimp.log full=y;
--不同名之间的数据导入:
imp system/test@xefromuser=hkb touser=hkb_new file=c:\orabackup\hkbfull.dmplog=c:\orabackup\hkbimp.log;
(select * from#avc_schema#.FP_LGBFF where FPLGBUUID in (select max(FPLGBUUID) from#avc_schema#.FP_LGBFF group by LCSLID)) D;
SELECT * FROM etl_meta_unit;
SELECT * FROM etl_meta_log;
SELECT * FROM etl_meta_checkpoint;
SELECT * FROMetl_meta_org;
--创建同义词
SELECT 'create orreplace synonym ' || TABLE_NAME || ' for ' || OWNER || '.' ||
TABLE_NAME || '@G3_ZRSJ_0912 ;'
FROM ALL_TABLES
WHERE OWNER = 'G3_1101_SCK';
--获取表:
select table_name from user_tables; --//当前用户的表
select table_name from all_tables; --//所有用户的表
select table_name from dba_tables; --//包括系统表
select table_name from dba_tables where owner='用户名'
--获取表字段:
select * from user_tab_columns where Table_Name='用户表';
select * from all_tab_columns where Table_Name='用户表';
select * from dba_tab_columns where Table_Name='用户表';
--获取表注释:
select * fromuser_tab_comments;
--相应的还有dba_tab_comments,all_tab_comments,这两个比user_tab_comments多了ower列。
--获取字段注释:
select * fromuser_col_comments ;
--查询模块代码开发提交三个脚本
select * fromdm_cxmk a where a.cxmk_dm like 'FP%' for update;
SELECT * FROMcx_dmtomc a where a.table_name = 'DM_FP_FPGSLX';
select * from qx_gnmk a where a.systemname like '%系统管理%';
query&moduleid=dj0012&forsgy=n;
insert /*+ APPEND */into
LDMT04_FP_FSXX
(SELECT *
FROM poc1207_zbq.INFT04_FP_FSXX A
where a.sprq > '20110101'
AND A.SPRQ < '20110231');
alter table LDMT04_FP_YJ nologging;
--oracle创建临时表session与transaction
--session级临时表
Create GlobalTemporary Table Table_Name (Col1Type1,Col2 Type2...) On Commit Preserve Rows ;
--transaction级临时表
Create GlobalTemporary Table Table_Name (Col1Type1,Col2 Type2...) On Commit Delete Rows ;
--ORA-14452: attemptto create, alter or drop an index on temporary table already in use
--处理步骤:
--1、先从user_objects中查询到该表的object_id:
select object_idfrom user_objects where object_name=upper('TMP_FP_FPLXXX');
--2、根据查到的object_id知道使用该表的session:
select * from v$lockwhere id1=124815;
--3、在从v$session视图中查到该session的SID和SERIAL#:
select * fromv$session where sid=124;
--4、杀掉这些进程:
alter system killsession '124,9829';
--Oracletrunc()函数的用法
/**************日期********************/
select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.
selecttrunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
selecttrunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日
selecttrunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41
selecttrunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC(number,num_digits)
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
selecttrunc(123.458) from dual --123
selecttrunc(123.458,0) from dual --123
selecttrunc(123.458,1) from dual --123.4
selecttrunc(123.458,-1) from dual --120
selecttrunc(123.458,-4) from dual --0
selecttrunc(123.458,4) from dual --123.458
select trunc(123)from dual --123
select trunc(123,1)from dual --123
select trunc(123,-1)from dual --120
--如不小心删除了表 My_Table
drop table My_Table;
--这个时候再用SELECT语句查询此表时,将会提示表或视图不存在.
--但可以用如下语句查询到这个表还在Oracle回收站中:
SELECT * FROMuser_recyclebin WHERE original_name='My_Table';
--那么现在就可以用如下语句进行恢复:
FLASHBACK TABLEMy_Table TO BEFORE DROP;
--查看表字段类型
selectB.column_name, B.comments,
decode(a.data_type,
'NUMBER',
'NUMBER' || '(' ||a.DATA_PRECISION || ',' || a.DATA_SCALE || ')',
'VARCHAR2',
a.DATA_TYPE || '(' ||a.DATA_LENGTH / 4 || ' CHAR' || ')',
a.DATA_TYPE || '(' ||a.DATA_LENGTH || ')'),'','',
decode(a.NULLABLE, 'N', 'NOT_NULL','NULL')
from dba_tab_columns a, dba_col_comments b
where a.OWNER = b.owner
and a.TABLE_NAME = b.table_name
and a.COLUMN_NAME = b.column_name
and a.OWNER = 'G3_0328_DW'
AND B.owner = 'G3_0328_DW'
AND A.TABLE_NAME = 'DM2_FP_PTFPGLQKNDBG'
order by A.COLUMN_ID;
--查看索引字段类型
select A.index_name,B.COLUMN_NAME, A.index_type, ''
from dba_indexes a, dba_ind_columns b
where a.index_name = b.INDEX_NAME
and A.table_name = B.TABLE_NAME
AND A.owner = 'G3_0328_DW'
AND B.INDEX_OWNER = 'G3_0328_DW'
AND b.table_name = 'DM2_FP_PTFPGLQKNDBG';
to_char(to_date(A.HZRQ,'yyyy/MM/DD'),'yyyy"年"MM"月"DD"日"');
SUBSTR(A.SLRQ,0,4) YEAR,
SUBSTR(A.SLRQ,5,2) MONTH,
SUBSTR(A.SLRQ,7,2) DAY,
select *
FROM G3_1122_CXTJ.cs_bdxx a
where A.BDLXBM LIKE '%021007006%'
FOR UPDATE;
021007007
021007006
SELECT *
FROM G3_1122_CXTJ.module_map_bdlxbm A
WHERE A.MODULEID LIKE'%FP_0010_CXLLGFPGPTP_QC%'
FOR UPDATE;
select * fromG3_1122_CXTJ.module_map_bdlxbm_new a where a.moduleid like'%FP_0010_CXLLGFPGPTP_QC%' for update;
SELECT *
FROM G3_1122_CXTJ.module_map_bdlxbm A
WHERE A.MODULEID in (SELECT a.moduleid
FROM G3_1122_CXTJ.module_map_bdlxbm A
WHERE A.MODULEID LIKE '%FP%' group bya.moduleid having count(*)>1) order by A.MODULEID;
insert intoG3_1122_CXTJ.module_map_bdlxbm_new (BDLXBM, MODULEID, QCMKMZ, CDMZ, BDSBZD,BDSBZDZ, FNNAME, LINKPARAMVALUE, LINKLX, REMARK)
SELECT DISTINCTa.bdlxbm,a.moduleid,C.GNMK_HZMC QCMKMZ,B.BDLXMC CDMZ,'NO' BDSBZD,'NO'BDSBZDZ,A.FNNAME,A.LINKPARAMVALUE,'' LINKLX,'' REMARK
FROM G3_1122_CXTJ.module_map_bdlxbm A,G3_1122_CXTJ.cs_bdxx b,G3_1122_CXTJ.QX_GNMK C
WHERE a.bdlxbm=b.bdlxbm andA.MODULEID=C.GNMK_DM AND A.MODULEID in(SELECT a.moduleid
FROM G3_1122_CXTJ.module_map_bdlxbm A
WHERE A.MODULEID LIKE '%FP%' group bya.moduleid having count(*)>1) order by A.MODULEID;
--创建用户
create user BST_NWCS
identified by oracle
default tablespace SJFF
temporary tablespace AMIS_TMP
profile DEFAULT;
-- Grant/Revoke roleprivileges
grant connect toBST_NWCS;
grant dba toBST_NWCS;
-- Grant/Revokesystem privileges
grant execute anyprocedure to BST_NWCS;
grant select anytable to BST_NWCS;
grant unlimitedtablespace to BST_NWCS;
grant update anytable to BST_NWCS;
--创建DBLINK
create publicdatabase link DBL_CTAIS2
connect to CTAIS2
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS= (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA =(SID =amis)(SERVER = DEDICATED)))';
--更新多个字段SQL语句
UPDATE DJ_YHXX ASET(A.YHZL_DM,A.YH_DM) = (SELECT B.YHZL_DM,B,YH_DM FROM TRANS_DM_YH B WHEREA.YH_DM=B.YH_DM);
--强制加入索引
SELECT /*+INDEX(INDEX_NAME) */ * FROM TABLE_NAME;
--合并发票号码连号的记录
SELECT FP_DM, KPRQ,MIN(FPHM) START_FPHM, MAX(FPHM) END_FPHM
FROM (SELECT A.NSRDZDAH,
FP_DM,
FPHM,
A.KPRQ,
TO_NUMBER(A.FPHM - ROWNUM)GROUPCC
FROM (SELECT NSRDZDAH,
TO_CHAR(KPRQ, 'YYYYMM')KPRQ,
YSFP_DM FP_DM,
FPHM
FROM FP_JDFP_TMP_FPYJSCXX
WHERE ZFBZ = '0'
ORDER BY YSFP_DM, kprq, FPHM)A)
GROUP BY FP_DM, KPRQ, GROUPCC
ORDER BY FP_DM, KPRQ, GROUPCC;
(SELECT C.FPTPUUID,MAX(A.FPKF_DM) FPKF_DM FROM #avc_schema#.FP_FS A,#avc_schema#.FP_TP C WHEREA.DJXH = C.DJXH AND A.SPRQ_1 <= C.SLRQ AND A.SPRY_DM = C.SLR_DM GROUP BYC.FPTPUUID) D
select * fromyspz_xmlzjsyb t where t.yspz_xmlbh = 'TaxMLBw_gdzcjszjsp_V1.0';
select * fromhx_yspz.yspz_xmlsjb t where t.yspz_xmllsh = 'dddd6808f8d14e478f98862ffc29bc59';
--根据id多行显示到一行上
SELECTwm_concat(A.CZRY_DM),A.ZYWXH FROM LDMT04_JC_JCRY A GROUP BY A.ZYWXH;
--具找出低效SQL
select executions,
disk_reads,
buffer_gets,
round((buffer_gets - disk_reads) /buffer_gets, 2) hit_radio,
round(disk_reads / executions, 2)reads_per_run,
sql_text
from v$sqlarea
where executions > 0
and buffer_gets > 0
and (buffer_gets - disk_reads) / buffer_gets< 0.8
order by 4 desc;
--查询ALL_SOURCE中,(脚本代码)内容与0997500模糊匹配的类型为PROCEDURE(存储过程)的信息。
--该ALL_SOURCE中只有以下5种类型1 FUNCTION,2 JAVA SOURCE,3 PACKAGE,4 PACKAGE BODY,5 PROCEDURE
SELECT * FROMALL_SOURCE where TYPE='PROCEDURE' AND TEXT LIKE '%0997500%';
--insert 和update 都带有where字句
MERGE INTO productsp
USING newproducts np
ON (p.product_id =np.product_id)
WHEN MATCHED THEN
UPDATE
SET p.product_name = np.product_name,p.category = np.category
WHERE p.category = 'DVD'
WHEN NOT MATCHEDTHEN
INSERT
VALUES
(np.product_id, np.product_name,np.category) WHERE np.category != 'BOOKS';
--特别注意:通过把不等于操作符改成OR 条件,就可以使用索引避免全表扫描.
select cust_Id,namefrom customers where cust_rating <> 'aa';
select cust_Id,namefrom customers where cust_rating < 'aa' or > aa';
--位图索引的使用限制 :
--基于规则的优化器不会考虑位图索引。
--当执行 ALTER TABLE 语句并修改包含有位图索引的列时,会使位图索引失效。
--位图索引不包含任何列数据,并且能用于类型的完整性检查。
--位图索引不能被声明为唯一。 位图索引的最大长度为 30 。
--在线重定义DBMS_REDEFINITION步骤:在线重定义能保证数据的一致性,在大部分时间内表都可以正常进行DML操作。只在切换的瞬间锁表,具有很高可用性 。
--1、创建基础表A,如果存在,就不需要操作。
--2、创建临时的分区表B。
--3、开始重定义,将基表A的数据导入临时分区表B。
--4、结束重定义,此时在 MB 的 NameDirectory 里,已经将两个表进行了交换。即此时基表A已经成了分区表,B已经成了普通表。
create tableunpar_table(id number(10) primary key,
create_date date);
truncate tableunpar_table;
insert intounpar_table select rownum,created from dba_objects where rownum<1001;
commit;
create indexcreate_date_ind on unpar_table(create_date);
execdbms_stats.gather_table_stats('j1_dw','unpar_table',cascade=>true);
create tablepar_table (id number primary key,time date) partition by range (time)
(partition p1 valuesless than (to_date('20040701','yyyymmdd')),
partition p2 values less than(to_date('20120101','yyyymmdd')),
partition p3 values less than(to_date('20130701','yyyymmdd')),
partition p4 values less than (maxvalue));
execdbms_redefinition.can_redef_table('j1_dw','unpar_table');
--grant execute ondbms_redefinition to j1_dw;
execdbms_redefinition.start_redef_table('j1_dw','unpar_table','par_table','idid,create_date time',dbms_redefinition.cons_use_pk);
begin
dbms_redefinition.sync_interim_table(
uname=>'j1_dw',
orig_table=>'unpar_table',
int_table=>'par_table');
end;
/
create indexcreate_date_ind2 on par_table(time);
execdbms_stats.gather_table_stats('j1_dw','par_table',cascade=>true);
begin
dbms_redefinition.finish_redef_table(
uname=>'j1_dw',
orig_table=>'unpar_table',
int_table=>'par_table');
end;
/
--交换分区步骤:这种方法只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。适用于大数据量的表。
--1、创建分区表,假设有两个分区,P1,P2。
--2、创建表A存放P1规则的数据。
--3、创建表B存放P2规则的数据。
--4、用表A和P1分区交换。把表A的数据存放到P1分区。
--5、用表B和P2分区交换。把表B的数据存放到P2分区。
CREATE TABLE P_DBA(
ID NUMBER,TIME DATE)
PARTITION BYRANGE(TIME)
(PARTITION P1 VALUESLESS THAN (TO_DATE('20130901','YYYYMMDD')),
PARTITION P2 VALUES LESS THAN(TO_DATE('20131001','YYYYMMDD')),
PARTITION P3 VALUES LESS THAN (MAXVALUE));
CREATE TABLE DBA_P1AS
SELECTTO_NUMBER(A.JKPZZL_DM) ID, TO_DATE(A.LRRQ, 'YYYYMMDD') TIME
FROM LDMT02_YE_SFXX A WHEREA.LRRQ<='20130901' AND ROWNUM <100;
CREATE TABLE DBA_P2AS
SELECTTO_NUMBER(A.JKPZZL_DM) ID, TO_DATE(A.LRRQ, 'YYYYMMDD') TIME
FROM LDMT02_YE_SFXX A WHEREA.LRRQ>='20130901' AND A.LRRQ <= '20131001' AND ROWNUM <100;
ALTER TABLE P_DBAEXCHANGE PARTITION P1 WITH TABLE DBA_P1;
ALTER TABLE P_DBAEXCHANGE PARTITION P2 WITH TABLE DBA_P2;
--查看死锁对象、sql
SELECT 'ALTER SYSTEMKILL SESSION ''' || A.SID || ',' || A.SERIAL# || ''';' DEADLOCK
FROM V$SESSION A
WHERE A.SID IN (SELECT SID FROM V$LOCK B WHEREB.BLOCK = 1);
SELECT S.SID,Q.SQL_TEXT
FROM V$SQLTEXT Q, V$SESSION S
WHERE Q.ADDRESS =S.SQL_ADDRESS AND S.SID = &SID --这个&SID是第一步查出来的
ORDER BY Q.PIECE;
--查看谁锁了谁
SELECT s1.username|| '@' || s1.machine || ' ( SID=' || s1.sid ||
' ) is blocking ' || s2.username || '@'|| s2.machine || ' ( SID=' ||
s2.sid || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2,v$session s2
WHERE s1.sid = l1.sid AND s2.sid = l2.sid ANDl1.BLOCK = 1
AND l2.request > 0 AND l1.id1 = l2.id1AND l2.id2 = l2.id2;
SELECT LPAD(' ',DECODE(l.xidusn, 0, 3, 0)) || l.ORACLE_USERNAME User_name,
o.owner, o.object_name, o.object_type,s.sid, s.serial#
FROM v$locked_object l, dba_objects o,v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY o.object_id, xidusn DESC;
--查看表空间大小
SELECT F.TOTAL_BYTES,
F.MAX_BYTES,
A.TOT_GROOTE_MB,
(A.TOT_GROOTE_MB - F.TOTAL_BYTES),
F.TABLESPACE_NAME,
(ROUND((A.TOT_GROOTE_MB - F.TOTAL_BYTES)/ A.TOT_GROOTE_MB,4)*100) ,
(ROUND( F.TOTAL_BYTES /A.TOT_GROOTE_MB,4)*100)
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 *1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 *1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 *1024), 2) TOT_GROOTE_MB
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A
WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME;
--oracle 优化 SQL Tuning Advisor
grant advisor toj1_dw;
create table bigtabas select rownum as "id",a.* from sys.all_objects a;
create tablesmalltab as select rownum as "id",a.* from sys.all_tables a;
insert into bigtabselect rownum as "id",a.* from sys.all_objects a;
insert into smalltabselect rownum as "id",a.* from sys.all_tables a;
commit;
select count(*) frombigtab a,smalltab b where a.object_name=b.table_name;
declare
my_task_name varchar2(30);
my_sqltext clob;
begin
my_sqltext := 'select count(*) from bigtaba,smalltab b where a.object_name=b.table_name';
my_task_name :=dbms_sqltune.create_tuning_task(sql_text => my_sqltext,
user_name => 'J1_DW',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tuning_sql_test',
description => 'Task to tune a query on a specified table');
dbms_sqltune.execute_tuning_task(task_name=> 'tuning_sql_test');
END;
/
selecttask_name,status from user_advisor_tasks a where a.task_name='tuning_sql_test';
selectdbms_sqltune.report_tuning_task('tuning_sql_test') from dual;
--收集表的统计信息
executedbms_stats.gather_table_stats(ownname => 'J1_DW',tabname=>'SMALLTAB',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
executedbms_stats.gather_table_stats(ownname => 'J1_DW',tabname=>'BIGTAB',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
--删除任务
execdbms_sqltune.drop_tuning_task('tuning_sql_test');
将用户帐户添加到组,适用于 Windows 7,通过将用户帐户添加到组中,可以避免必须向多个不同的用户逐个授予相同的访问权限和权限。
组成员可以对设置进行相同类型的更改,并且对文件夹、打印机及其他网络服务具有相同的访问权限。
1、通过单击「开始」按钮 ,在搜索框中键入 mmc,然后按 Enter,打开“Microsoft 管理控制台”。
如果系统提示您输入管理员密码或进行确认,请键入该密码或提供确认。
2、在“Microsoft 管理控制台”的左窗格中,单击“本地用户和组”。
如果没有看到“本地用户和组”
如果没有看到“本地用户和组”,则可能是因为管理单元没有添加到“Microsoft 管理控制台”中。请按照以下步骤安装它:
a、在“Microsoft 管理控制台”中,单击“文件”菜单,然后单击“添加/删除管理单元”。
b、单击“本地用户和组”,然后单击“添加”。
c、单击“本地计算机”,再单击“完成”,然后单击“确定”。
3、双击“组”文件夹。
4、右键单击要将用户帐户添加到的组,然后单击“添加到组”。
5、单击“添加”,然后键入用户帐户的名称。
6、依次单击“检查名称”、“确定”,然后再次单击“确定”。
SELECT LEVEL "等级",
CONNECT_BY_ISLEAF"是否是叶子节点",
CONNECT_BY_ROOT a.fpkf_dm ENAME,
LPAD(' ', LEVEL * 2 - 1) ||SYS_CONNECT_BY_PATH(a.fpkf_dm, '=>'),
FROM fpglhx.dm_fpkf a
start with a.fpkf_dm = '234150000000000'
connect by priora.fpkf_dm = a.sj_fpkf_dm;
--MAPPING抽数脚本
DECLARE
an_rownum NUMBER;
begin
J1_G3_ZBQ.cp_trans_all_ah_init('J1_G3_ZBQ','INFT04_ZS_DJSKXX',an_rownum,'','','');
end;
/
TRUNCATE TABLEJ1_LDM.LDMT04_ZS_DJSKXX;
DECLARE
an_rownum NUMBER;
begin
J1_LDM.cp_loadldm_all('J1_G3_ZBQ','INFT04_ZS_DJSKXX',an_rownum);
end;
/
--过程抽数脚本
DECLARE
an_rownum NUMBER;
begin
j1_dw.cp_dw_loop('j1_ldm',4117.02,'','20140101','','20',an_rownum);
j1_dw.cp_dw_loop('j1_ldm',4117.02,'','20130101','20131201','20',an_rownum);
end;
/
--sql执行计划
explain plan forselect * from dual;
select * fromtable(dbms_xplan.display);
--oracle系统主键全球唯一
SELECT sys_guid()FROM dual;
--查询活动的session
select t.INST_ID,
t.SID,
t.SERIAL#,
t.USERNAME,
t.SCHEMANAME,
t.SQL_ID,
t.USERNAME,
t.MACHINE,
t.TERMINAL,
t.PROGRAM,
t.SQL_ID,
t.SQL_EXEC_START,
t.LOGON_TIME,
t.MODULE,
t.ACTION,
t.EVENT,
t.WAIT_CLASS
from gv$session t
where t.STATUS = 'ACTIVE'
and t.SQL_EXEC_START is not null
order by t.USERNAME, terminal;
select * from v$sqla where a.SQL_ID in ('3pkwrsn38pwwh','3pkwrsn38pwwh');