在某些比较严格的环境中,不提供像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() ) ||','|| chr() ) ||','|| chr() ) ||',' || b.permis_order ||','|| ) ) end || ',' || b.permis_pid ||','|| ) ) end || ',' || b.creator ||','|| ))) ) || ')' || ',' || b.modifier||','|| ))) ) || ')' || ',' || chr() ) || ');' 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) ,,,,to_date(,to_date('); 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) ,,,,to_date(,to_date('); 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) ,,,,to_date(,to_date('); 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) ,,,,to_date(,to_date('); 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) ,,,,to_date(,to_date('); 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) ,,,,to_date(,to_date('); 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) ,,,,to_date(,to_date('); 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) ,,,,to_date(,to_date(');