试试oracle提供的脚本.
set pagesize 0
-- This script dumps a table to a comma delimited ASCII file and
-- also builds a control file and a parameter file for SQL*Loader.
set trimspool on
set serverout on
clear buffer
undef dumpfile
undef dumptable
undef dumpowner
var maxcol number
var linelen number
var dumpfile char(40)
col column_id noprint
set pages 0 feed off termout on echo off verify off
accept dumpowner char prompt 'Owner of table to dump: '
accept dumptable char prompt 'Table to dump: '
begin
select max (column_id)
into :maxcol
from all_tab_columns
wheretable_name = rtrim (upper ('&dumptable')) and
owner = rtrim (upper ('&dumpowner'));
select sum (data_length) + (:maxcol * 3)
into :linelen
from all_tab_columns
wheretable_name = rtrim (upper ('&dumptable')) and
owner = rtrim (upper ('&dumpowner'));
end;
/
print linelen
print maxcol
spool dump.sql
select 'set trimspool on' from dual;
select 'set termout off pages 0 heading off echo off' from dual;
select 'set line '||:linelen from dual;
select 'spool '||lower ('&dumptable')||'.txt' from dual;
select 'select'||chr (10) from dual;
select ' '||''''||'"'||''''||'||'||
'replace ('||column_name||', '||''''||'"'||''''||') '||
' ||'||''''||'", '||''''||' || ', column_id
from all_tab_columns
wheretable_name = upper ('&dumptable') and
owner = upper ('&dumpowner') and
column_id< :maxcol
union
select ' '||''''||'"'||''''||'||'||
'replace ('||column_name||', '||''''||'"'||''''||') '||
' ||'||''''||'"'||'''', column_id
from all_tab_columns
wheretable_name = upper ('&dumptable') and
owner = upper ('&dumpowner') and
column_id= :maxcol
orderby 2;
select 'from &dumpowner..&dumptable;' from dual;
select 'spool off' from dual;
spool off
-- Build a basic control file
set line 79
spool dtmp.sql
select 'spool '||lower ('&dumptable')||'.par' from dual;
spool off
select 'userid = /'||chr (10)||
'control = '||lower ('&dumptable')||'.ctl'||chr (10)||
'log = '||lower ('&dumptable')||'.log'||chr (10)||
'bad = '||lower ('&dumptable')||'.bad'||chr (10)
from dual;
spool off
spool dtmp.sql
select 'spool '||lower ('&dumptable')||'.ctl' from dual;
spool off
select 'load data'||chr(10)||
'infile '||''''||lower ('&dumptable')||'.txt'||''''||chr (10)||
'into table &dumptable'||chr (10)||
'fields terminated by '||''''||','||''''||
' optionally enclosed by '||''''||'"'||''''||chr (10)||'('
from dual;
select ' '||column_name||',', column_id
from all_tab_columns
wheretable_name = upper ('&dumptable') and
owner = upper ('&dumpowner') and
column_id< :maxcol
union
select ' '||column_name, column_id
from all_tab_columns
wheretable_name = upper ('&dumptable') and
owner = upper ('&dumpowner') and
column_id= :maxcol
orderby 2;
select ')' from dual;
spool off
创建数据账号只有个别表的权限_只有普通权限账号 如何把远程数据库中该用户的数据表导入到本地数据库？...
