常用sql

tns：

单节点

XJLT_YSC_ZCPT=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =

127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

  (SERVER = DEDICATED)

  (SERVICE_NAME = xjltzcpt)

)

)

双节点

XJLT_YSC_WSSB=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =

127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

  (SERVER = DEDICATED)

  (SERVICE_NAME = xjltwssb)

)

)

连接池：

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(LOAD_BALANCE
= OFF)(FAILOVER = ON))(CONNECT_DATA=(SERVICE_NAME=xjltwssb)))

常用sql

select * from hx_qz.qz_xtjrpz for update

select * from zj_mh.mh_xtcs版本1.6.23300.215.01161.07

select t.*,rowid from hx_zgxt.qx_dlzhxx t
where t.dlzh_dm=‘16100000000’–325a2cc052914ceeb8c19016c091d2ac

账号信息

select * from hx_zgxt.qz_dmb forupdate

select * from hx_zgxt.t_xt_hcbxx缓存表

select * from hx_zgxt.t_xt_hcbxx where table_name = 'CS_GY_QYXTSJYDZB’for update version+1 刷缓存

select t.*, t.rowid from HX_CX.CX_SJYXX t for update; 数据源信息表

alter user j2_hxcx identified by “J2_HXCX”;修改密码

create table name as select * from name ;

alter user aq_jcpt
account unlock;解锁aq_jcpt用户

conn hx_zgxt/mima 改完密码后可以这样测试

select instance_name from v$instance; 数据库内查看现在是哪个实例

echo $ORACLE_SID 数据库外查看现在是哪个实例

update aq_jcpt.reg_url set appurl=replace (appurl
,‘zjgs’,‘zjsw’) ;替换表中zjgs成zjsw

select * from hx_zgxt.sword_job_log order by
job_fire_date desc —看定时任务的日志

部署环境时需要的sql

select * from hx_qz.qz_jddz for update;

select * from hx_qz.qz_jyjd for update;

select * from aq_jcpt.reg_url for
update;

select * from hx_zgxt.cs_gy_xtcs t where
t.csbm=‘F0000001000000004’ for update;

select * from zj_mh.mh_xtcs ;

select * from hx_zgxt.cs_qx_zyydz for
update;

—grant select,insert,delete,update
on HX_CX.CX_SJYXX t to hx_zgxt

select t.*, t.rowid from HX_CX.CX_SJYXX
t for update;-- 通用查询数据源名称要修改

select t.*,t.rowid from hx_zgxt.qz_jyjd
t;

select t.*,t.rowid from hx_zgxt.qz_jddz
t;

select t.*,t.rowid from HX_CX.CX_SJYXX t
;

select t.*,t.rowid from
aq_jcpt.reg_url t where APPURL like
‘%scsw%’;

select t.*,t.rowid from
hx_zgxt.cs_qx_zyydz t;

select t.*,rowid from zj_mh.mh_xtcs t;

select t.*,t.rowid from
hx_zgxt.qz_xtjrpz t ;

–update hx_zgxt.qz_jyjd set
JYJDBM=replace(JYJDBM,‘BJSW’,‘SCSW’);

–update aq_jcpt.reg_url set
APPURL=replace(APPURL,‘bjsw’,‘scsw’);

–update
hx_zgxt.cs_qx_zyydz set YYFWQDZ=replace(YYFWQDZ,‘bjsw’,‘scsw’);

–update hx_zgxt.qz_xtjrpz set
XTUUID=replace(XTUUID,‘BJSW’,‘SCSW’),XTBM=replace(XTBM,‘BJSW’,‘SCSW’),FWYH=replace(FWYH,‘BJSW’,‘SCSW’) where xtuuid in(‘BJSW.SBF.QDWB.YSC’,‘BJSW.SBF.HDAP.YSC’);

–update hx_zgxt.qz_xtjrpz set
XTUUID=replace(XTUUID,’.YSC’,’’),XTBM=replace(XTBM,’.YSC’,’’),FWYH=replace(FWYH,’.YSC’,’’) where xtuuid
in(‘SCSW.SBF.QDWB.YSC’,‘SCSW.SBF.HDAP.YSC’);

–update hx_zgxt.qz_jyjd set
JYJDBM=replace(JYJDBM,’.YSC’,’’) where jyjdbm like ‘%SW.SBF%’;

–update aq_jcpt.reg_url set
APPURL=replace(APPURL,‘ysc’,’’) where APPURL like ‘%sw.tax%’;

–update hx_zgxt.cs_qx_zyydz set
YYFWQDZ=replace(YYFWQDZ,‘ysc’,’’) where YYFWQDZ like ‘%sw.tax%’;

–update hx_qz.qz_dmb set VALUE=VALUE+1
where CODE=‘RouteVersion’;

top sql

begin

dbms_workload_repository.create_snapshot();

end;

/

–查询 dbid:

select dbid from v$database;

–查询实例名：

select instance_number,instance_name
from v$instance;

–查询snap_id:

SELECT SNAP_ID,
TO_CHAR(BEGIN_INTERVAL_TIME,‘yyyy-mm-dd hh24:mi:ss’) BEGIN_INTERVAL_TIME,
TO_CHAR(STARTUP_TIME,‘yyyy-mm-dd hh24:mi:ss’) STARTUP_TIME FROM DBA_HIST_SNAPSHOT order by snap_id desc;

SQL ordered by Elapsed Time：

select *

from (select sqt.sql_id,

          nvl((sqt.elap / 1000000),

to_number(null))/sqt.exec as 单位执行时间，

          nvl((sqt.elap / 1000000),

to_number(null)) as 总执行时间,

           sqt.exec as 执行次数,

           nvl(st.sql_text, to_clob(' **

SQL Text Not Available ** ')) as SQL_TEXT

from (select sql_id,

                   max(module) module,

                   sum(elapsed_time_delta)

elap,

                   sum(cpu_time_delta)

cput,

                   sum(executions_delta)

exec

              from dba_hist_sqlstat

             where dbid = &dbid

               and instance_number = &inst_num

               and &beg_snap <

snap_id

               and snap_id <=

&end_snap

               and module='JDBC Thin

Client’

             group by sql_id) sqt,

           dba_hist_sqltext st

where st.sql_id(+) = sqt.sql_id

       and st.dbid(+) = &dbid

order by nvl(sqt.elap, -1) desc, sqt.sql_id)

where rownum < 65

and (rownum <=50);

SQL ordered by CPU Time：

select *

from (select nvl((sqt.cput / 1000000), to_number(null)),

                nvl((sqt.elap / 1000000),

to_number(null)),

                sqt.exec,

                decode(sqt.exec,

                       0,

                       to_number(null),

                       (sqt.cput / sqt.exec

/ 1000000)),

                (100 * (sqt.elap /

                (SELECT sum(e.VALUE) -

sum(b.value)

                           FROM

DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e

                          WHERE B.SNAP_ID =

&beg_snap

                            AND E.SNAP_ID =

&end_snap

                            AND B.DBID =

&DBID

                            AND E.DBID =

&DBID

                            AND

B.INSTANCE_NUMBER = &INST_NUM

                            AND

E.INSTANCE_NUMBER = &INST_NUM

                            and e.STAT_NAME

= ‘DB time’

                            and b.stat_name

= ‘DB time’))) norm_val,

                sqt.sql_id,

    
       to_clob(decode(sqt.module,

                               null,

                               null,

                               'Module: '

|| sqt.module)),

                nvl(st.sql_text,

to_clob(’** SQL Text Not Available **’))

           from (select sql_id,

                        max(module) module,

                        sum(cpu_time_delta)

cput,

sum(elapsed_time_delta) elap,

sum(executions_delta) exec

                   from dba_hist_sqlstat

                  where dbid = &dbid

                    and instance_number =

&inst_num

                    and &beg_snap <

snap_id

                    and snap_id <=

&end_snap

                  group by sql_id) sqt,

                dba_hist_sqltext st

          where st.sql_id(+) = sqt.sql_id

            and st.dbid(+) = &dbid

          order by nvl(sqt.cput, -1) desc,

sqt.sql_id)

where rownum < 65

and (rownum <= 10 or norm_val > 1)