首页 > 代码库 > Oracle SQL*Plus 数据备份为 sql 文件

Oracle SQL*Plus 数据备份为 sql 文件

 

在某些比较严格的环境中,不提供像PL/SQL Developer 这样的工具供我们备份表数据时,使用SQL*Plus运行如下脚本内容导出数据.

 

1,  执行时登录SQL*Plus,  命令   @D:/backup.sql

 

2, backup.sql 文件内容:

-- 备份权限表数据--
set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 500
set verify off
set pagesize 0
set term off
set trims on
set linesize 600
set heading off
set timing off
set verify off
set numwidth 38
SPOOL c:\xxx.sql   -- 指定的导出文件路径

 

---------------------------具体要导出的sql---------可以写任意多条sql语句----------------------

-------------back base_permission----------------start---

select insert into BASE_PERMISSION (permis_id, permis_code, permis_name, permis_url, permis_order, remark, permis_pid, belong, creator, gmt_create, modifier, gmt_modified, permis_type) 
values ( || b.permis_id ||, || 
chr(39) || b.permis_code || chr(39) ||,|| 
chr(39) || b.permis_name || chr(39) ||,|| 
chr(39) || b.permis_url || chr(39) ||, || 
b.permis_order ||,|| 
case when b.remark is null then null else chr(39) || b.remark || chr(39) end || , || 
b.permis_pid ||,|| 
case when b.belong is null then null else chr(39) || b.belong || chr(39) end || , || 
b.creator ||,|| 
to_date( || chr(39)|| to_char(b.gmt_create,dd-mm-yyyy hh24:mi:ss)||chr(39)||,|| chr(39) || dd-mm-yyyy hh24:mi:ss ||chr(39) || ) || , || 
b.modifier||,|| 
to_date( || chr(39)|| to_char(b.gmt_modified,dd-mm-yyyy hh24:mi:ss)||chr(39)||,|| chr(39) || dd-mm-yyyy hh24:mi:ss ||chr(39) || ) || , || 
chr(39) || b.permis_type|| chr(39) || 
); 
from BASE_PERMISSION b; 
-------------back base_permission----------------end---

------------------继续导出其它表-----------------

 

SPOOL OFF

 

上面脚本仅仅导出了权限表数据,导出的最终内容如下:

insert into BASE_PERMISSION (permis_id, permis_code, permis_name, permis_url, permis_order, remark, permis_pid, belong, creator, gmt_create, modifier, gmt_modified, permis_type)
values (221,TRAN_DATA_MONITOR,交易信息实时监控,trandatamonitor,3,null,67,1,1,to_date(26-08-2016 16:13:45,dd-mm-yyyy hh24:mi:ss),1,to_date(26-08-2016 16:13:45,dd-mm-yyyy hh24:mi:ss),1);

insert into BASE_PERMISSION (permis_id, permis_code, permis_name, permis_url, permis_order, remark, permis_pid, belong, creator, gmt_create, modifier, gmt_modified, permis_type)
values (144,SYSTEM_PARAMETER,系统参数,systemParameter,5,null,1,null,1,to_date(05-08-2016 17:03:36,dd-mm-yyyy hh24:mi:ss),1,to_date(05-08-2016 17:03:36,dd-mm-yyyy hh24:mi:ss),1);

insert into BASE_PERMISSION (permis_id, permis_code, permis_name, permis_url, permis_order, remark, permis_pid, belong, creator, gmt_create, modifier, gmt_modified, permis_type)
values (181,APPLICATIONS_MODULE,业务申请单,‘‘,9,null,0,null,1,to_date(17-08-2016 10:39:23,dd-mm-yyyy hh24:mi:ss),1,to_date(02-09-2016 16:15:20,dd-mm-yyyy hh24:mi:ss),0);

insert into BASE_PERMISSION (permis_id, permis_code, permis_name, permis_url, permis_order, remark, permis_pid, belong, creator, gmt_create, modifier, gmt_modified, permis_type)
values (161,SIGN_INFO_COUNT,签约统计报表,signInfoCount,4,null,8,null,1,to_date(11-08-2016 16:46:23,dd-mm-yyyy hh24:mi:ss),1,to_date(15-08-2016 09:58:36,dd-mm-yyyy hh24:mi:ss),1);

insert into BASE_PERMISSION (permis_id, permis_code, permis_name, permis_url, permis_order, remark, permis_pid, belong, creator, gmt_create, modifier, gmt_modified, permis_type)
values (182,APPLICATIONS,业务申请单打印,applications,0,null,181,null,1,to_date(17-08-2016 10:43:38,dd-mm-yyyy hh24:mi:ss),1,to_date(02-09-2016 16:15:32,dd-mm-yyyy hh24:mi:ss),1);

insert into BASE_PERMISSION (permis_id, permis_code, permis_name, permis_url, permis_order, remark, permis_pid, belong, creator, gmt_create, modifier, gmt_modified, permis_type)
values (41,SYS_FAULT,故障管理,‘‘,3,null,0,null,1,to_date(27-05-2016 13:45:16,dd-mm-yyyy hh24:mi:ss),1,to_date(27-05-2016 13:46:06,dd-mm-yyyy hh24:mi:ss),0);

insert into BASE_PERMISSION (permis_id, permis_code, permis_name, permis_url, permis_order, remark, permis_pid, belong, creator, gmt_create, modifier, gmt_modified, permis_type)
values (19,PARAMETER,全行参数,parameter,3,null,1,null,1,to_date(24-12-2015 10:30:05,dd-mm-yyyy hh24:mi:ss),1,to_date(31-05-2016 16:47:35,dd-mm-yyyy hh24:mi:ss),1);

insert into BASE_PERMISSION (permis_id, permis_code, permis_name, permis_url, permis_order, remark, permis_pid, belong, creator, gmt_create, modifier, gmt_modified, permis_type)
values (13,SERVER_MODULE,服务管理,‘‘,6,null,0,null,1,to_date(21-12-2015 11:20:19,dd-mm-yyyy hh24:mi:ss),1,to_date(30-03-2016 15:25:06,dd-mm-yyyy hh24:mi:ss),0);

 

Oracle SQL*Plus 数据备份为 sql 文件