常用 sql

--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&amp;moduleid=dj0012&amp;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');