oracle 相关使用记录

语法：sqlplus usr/pwd@//host:port/sid
[oracle@mzl ~]$ sqlplus risenet/1@//192.168.130.99:1521/risenet

 

举例：exp lm/lm file=D:\lm.dmp log=D:\lm.log owner=lm -------备份

imp lm/lm file=E:/2012workspace/lm/lm/lm.dmp log=E:/2012workspace/lm/lm/lm.log fromuser=lm touser=lm ----还原

 

远程备份：exp USERID=bdcca/bdccds@101.109.2.01/bdcc
32 file=D:\bdcc.dmp log=D:\bdcc.log owner=bdcc

 

<!--StartFragment -->

用命令行登录进去：

sqlplus sys/pwd as sysdba
然后：
create user amass identified by amass;
grant connect, resource to amass;
grant create procedure, create view, create trigger to amass;
grant dba, sysdba to amass;
create tablespace amass_default_ts datafile 'd:\......dbf' size 10M autoextends on next 10M;
alter user amass default tablespace amass_default_ts;

 

 

select
(case
when recharge.terminalsn is not null then recharge.terminalsn
when rechargeterminal.terminalserialid is not null then rechargeterminal.terminalserialid
when tbconsume.terminalsn is not null then tbconsume.terminalsn
when consumeterminal.terminalserialid is not null then consumeterminal.terminalserialid
else '未知终端' end) as 终端序列号,
to_char(tbconsume.cdate, 'yyyy-mm-dd') as 扣费日期, to_char(tbconsume.cdate, 'hh24:mi:ss') as 扣费时间, tbconsume.cvalue as 扣费金额, tbconsume.businessref as 扣费参考号,
caccount as 银行账号,
nvl(consumeresultdefine.description, '未知标志') as 银联标志,

recharge.clientno as 用户手机号码, to_char(recharge.rechargedate, 'yyyy-mm-dd') 充值日期, to_char(recharge.rechargedate, 'hh24:mi:ss') 充值时间,
recharge.rechargeqty 充值金额, 'XXX' 连连充值流水号, 'YYYY' 连连代理充值流水号, nvl(rechargeresultdefine.description, '未知标志') 充值标志

from
(select * from tbrecharge where businessid = 7) recharge
left join tbconsume on recharge.consumeid = tbconsume.id
left join tbterminal consumeterminal on tbconsume.terminalid = consumeterminal.id
left join tbterminal rechargeterminal on recharge.terminalid = rechargeterminal.id
left join tbtransresultdefine rechargeresultdefine on recharge.resultflag = rechargeresultdefine.id
left join tbtransresultdefine consumeresultdefine on tbconsume.cflag = consumeresultdefine.id

 

 

select tm.TERMINALSERIALID as 终端充列号,np.NETPOINTERNO as 网点ID,CONCAT(np.NETPOINTERTYPE,'(未转换)') as 网点类型,'未知' as 网点代号,np.LOC as 安装位置,com.COMMNAME as 所属社区,str.STREETNAME as 所在街道,area.AREANAME as 所在区域,city.CITYNAME as 所在城市,TO_CHAR(cs.CURRENTDATE,'yyyy-mm-dd') as 扣费日期,to_char(cs.CURRENTDATE,'hh24:mi:ss') as 扣费时间,cs.CVALUE as 扣费金额,cs.CACCOUNT as 银行账号,cs.YLTERMINALNO as 银联终端号,cs.CBATCH as 扣费批次,cs.CNO as 扣费流水号,cs.BUSINESSREF as 扣费参考号,nvl(tsd.Description,'不明确') as 银联标志,re.CLIENTNO as 用户手机号码,to_char(re.RECHARGEDATE,'yyyy-mm-dd') as 充值日期,to_char(re.RECHARGEDATE,'hh24:mi:ss') as 充值时刻,RE.RECHARGEQTY as 充值金额,LL.SERIALNUMBER as 连连充值流水号,LL.BOSSSERIALNUMBER as 连连代理充值流水号,nvl(retsd.Description,'不明确') as 充值标志,cs.CORPNAME as 扣款金融机构,(case when cs.TESTCARD=0 then '是' else '否' end) as 是否测试卡 from TBRECHARGE re left join TBACNTCHECKREQLL ll on re.FLOWSN=LL.SERIALNUMBER left join TBTRANSRESULTDEFINE retsd on retsd.id=re.RESULTFLAG,TBCONSUME cs left join TBTRANSRESULTDEFINE tsd on tsd.id=cs.CFLAG left join TBTERMINAL tm on cs.TERMINALSN=tm.TERMINALSERIALID left join TBNETPOINTER np on tm.NETPOINTERID=np.id left join TBMERCHANT mc on np.MERCHANTID=mc.id left join TBCOMMUNITY com on mc.COMMUNITYID=com.id left join TBSTREET str on com.STREETID=str.id left join TBAREA area on str.AREAID=area.id left join TBCITY city on area.CITYID=city.id where RE.CONSUMEID=cs.id