语法: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
分享到:
相关推荐
Oracle多行记录合并/连接/聚合字符串的几种方法
Oracle数据库使用LOGMINER来分析日志。 一、安装LogMiner 要安装LogMiner工具,必须首先要运行下面这样两个脚本, $ORACLE_HOME/rdbms/admin/dbmslm.sql $ORACLE_HOME/rdbms/admin/dbmslmd.sql. 这两个...
查找访问orcale数据库用户名称或IP地址
本包用于记录存储过程里面的日志,将日志信息写入日志表(mylog)。可以记录日志时间、过程名称、过程里面记录日志的行数、参数值。如果在异常处理部分记录日志,还可以记录异常代码、异常...本人使用开发环境为oracle10g
自已写的可在ORACLE存储过程中记录运行日志工具包,调用方便简单。
Oracle返回记录集.docx
利用Oracle审计功能记录数据库操作.doc
oracle中记录用户登录信息的触发器的例子,有具体操作过程,包括错误信息及解释,这个例子是自己为某企业的oracle安全管理的具体例子
ORACLE 数据库中如何统计表记录数的过程说明
有做数据库巡检的同学,资料来啦,填空就可以喽
吉林大学,Oracle,课堂记录吉林大学,Oracle,课堂记录吉林大学,Oracle,课堂记录吉林大学,Oracle,课堂记录吉林大学,Oracle,课堂记录吉林大学,Oracle,课堂记录吉林大学,Oracle,课堂记录吉林大学,Oracle,...
oracle中 ,如何删除重复的记录信息
VC6下访问Oracle 数据库并获取记录总数;
利用Oracle审计功能记录数据库操作.docx
oracle删除重复记录性能分析平时收集,希望能给需要帮助的人提供便利
如何在Oracle数据表中随机抽取部分记录的方法
Oracle Linux 7 安装 Oracle 11.2.04 记录
Oracle左连接返回多条记录中一条记录的查询语句,更具指定条件分组排序,返回各组中第一条记录
Oracle RMAN增量备份恢复测试记录
oracle12c安装过程记录,是本人在甲骨文网站上下载的最新oracle 12c版本,并在自己电脑上成功安装的一个真实记录和截图