Hi Dilan,I am trying to migrate a database from Oracle to Postgresql. I am using the PgAdmin database migration wizard (1.5.60-Dev) to do this. Using ODBC I am able to migrate small tables but I have a few tables that have over 10 million rows that are failing. I know the reason as I am getting an "Snapshot too old" error from Oracle but am unable to come up with a workaround. I prefer using the wizard as it takes care of nulls etc. which I might have to handle if I went the dump and load route. Can someone give me an idea on getting around this (other than preventing updates to Oracle).
Also, is there a way to turn off WAL while loading bulk data.
thanks dilan
this is an well known problem in Oracle.
(I know its off topic, but it might interest other people)
You got several options around it:
- Increase the size of your rollback segments (the message means, that Oralce cannot give you a consistent view)
See
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c23cnsis.htm#2599
- Stop all other transactions
- Do the work in serveral steps
- Use a script like "unload.sql" ( in the Attachement, from http://www.evergreen-database.com/)
( it doesn not prevent the problem, but is quite performant)
I hope I could help you with that. Cheers, Dani
------------------------------------------------------------------------- -- SQL Script Name: unload_fixed.sql -- -- Function: generates a sql*plus script to unload a table to a -- file and a SQL*Loader script to reload the same -- data. Intent is to create a faster alternative -- to export/import. -- -- Initial testing indicates that the unload takes about -- 20% longer than EXP, but that the reload takes only -- 45% as long as IMP. Under Oracle7 r7.1, the capability -- of parallelizing direct loads (but not IMP) should -- provide much faster load times, reasonably 10% of the -- time for IMP. -- -- WORD OF WARNING Re: PERFORMANCE: -- Performance is very specific to the data distribution -- of the table data. Much poorer performance has been -- seen in the following cases: -- - many long varchar columns which actually contain -- short data values; -- - many number columns without precision or scale -- which are defaulted to large numbers; -- - lots of null values; -- All of these cases lead to inefficient use of the -- fixed record format of the unload file. Padding the -- unload file with leading/trailing zeros or trailing -- blanks yields files 4X the size of an export dmp file -- and unload times 4X those of export. (Even in these -- extreme test situations, the load time was still -- between 80% and 90% of IMP.) -- -- -- This unload/reload utility has some other advantages -- besides speed. The unload can easily select a subset -- of the original table (for statistical sampling or -- retrieving rows for a particular department or busines -- date for instance) whereas EXP/IMP deals with entire -- tables. Additionally, if desired, unload can sort -- the output in order to speed index builds and/or -- optimize cache hits on the reloaded data based on -- loading frequently used rows contiguously. This may -- provide an additional benefit in some reorg efforts. -- Finally, the unload might have a GROUP BY appended -- which would facilitate development of summary tables. -- -- By editing the generated unload2.sql and SQL*Loader -- .CTL scripts, one could additionally remove columns -- or modify them with SQL (or PL/SQL functions in r7.1) -- during the extract. Just be sure to adjust the length -- of the COLUMN and PAGESIZE in unload2.sql and the -- input field in .CTL to reflect whatever changes. -- -- This utility can also unload data from a view which -- is not possible via EXP. This facility may be used -- to do subsets--selection (specific rows), projection -- (specific columns), joins, GROUP BY summaries or -- function application without having to edit this -- script or its generated output scripts. -- -- Arguments IN: prompts for table_owner, table_name as well as -- default_precision and default_scale for columns -- defined as NUMBER or FLOAT (without precision or -- scale defined). -- -- Arguments OUT: none -- -- Calls: none -- -- Called by: none -- -- Change History: 05/25/94 gcdodge original development -- -- Limitations: Doesn't handle long, raw, long raw, rowid, mlslabel -- datatypes. -- -- This utility has been tested in a Solaris 2.3 -- environment, but is expected to be fully portable -- to any ASCII platform. Unlike EXP/IMP, however, -- it will not automatically make translations when -- the unload file is moved from ASCII to EBCDIC or -- vice versa. Since all data is written in external -- formats, one should expect that file transfer -- utilities that do such conversions should work. -- As an alternative, one could edit the SQL*Loader -- .CTL script to specify the alternative encoding -- scheme. -- -- If a numeric column is encountered which has no -- defined precision or scale, then this script will -- use default values (prompted for); this poses three -- risks: 1) that you may overspecify the precision -- and thereby waste space in the unload file; 2) -- you may underspecify the precision and thereby -- get overflow indicators in the unloaded data which -- may not be caught prior to loading; 3) you may -- underspecify the scale and introduce truncation -- which will not be found by either the unload or -- load processes. For this reason, it is strongly -- recommended that numeric table columns be defined -- with appropriate precision and scale values. -- -- The generated SQL*Loader script assumes that fields -- of blanks should be loaded as NULLS...if the table -- has columns for which SPACES are valid values, then -- it will be necessary to edit the generated unload2.sql -- script to concatenate double quotes before and after -- the affected column(s) along with changing the length -- (pagesize in unload2.sql and the individual field's -- length in the generated .CTL file) by two bytes. -- ------------------------------------------------------------------------- set tab off set heading off heading off feedback off echo off verify off space 1 pagesize 0 linesize 120 accept owner prompt 'What schema owns the table to be unloaded? ' accept table_name prompt 'What table is to be unloaded? ' accept default_precision prompt 'What TOTAL number of digits should be reserved for numbers without defined precision? ' accept default_scale prompt 'What number of DECIMAL digits should be reserved for numbers without defined scale? ' --------------------------------------------------- -- Generate the unload script --------------------------------------------------- spool unload_fixed2.sql select 'SET HEADING OFF FEEDBACK OFF ECHO OFF VERIFY OFF SPACE 0 PAGESIZE 0 TERMOUT OFF' from dual /
-- Calculate the sum of all output field lengths and set the output record size
select 'SET LINESIZE '
|| (sum(decode(data_type,
'CHAR',data_length,
'VARCHAR',data_length,
'VARCHAR2',data_length,
'DATE',14,
'NUMBER',decode(data_precision,
'',&default_precision+2,
greatest(data_precision-data_scale,1)+decode(data_scale,0,0,1)+data_scale)+1,
'FLOAT',&default_precision+2,
data_length)))
from dba_tab_columns
where owner=upper('&&owner')
and table_name=upper('&&table_name')
/
-- Generate an appropriate SQL*Plus COLUMN command to control formatting of each
output field
select 'COLUMN ' || rpad('"'||column_name||'"',32)
|| ' FORMAT '
|| rpad(decode(data_type,
'CHAR','A'||data_length,
'VARCHAR2','A'||data_length,
'VARCHAR','A'||data_length,
'DATE','A14',
'NUMBER',decode(data_precision,
'',
rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&default_scale,'9'),
rpad('0',greatest(data_precision-data_scale,1),'9')
|| decode(data_scale,0,'','.')
||
decode(data_scale,0,'',rpad('9',data_scale,'9'))),
'FLOAT',rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&default_scale,'9'),
'ERROR'),40)
|| ' HEADING ''X'''
from dba_tab_columns
where owner=upper('&&owner')
and table_name=upper('&&table_name')
order by column_id
/
-- Generate the actual SELECT statement to unload table data
select 'SPOOL /tmp/&&owner..&&table_name..DAT'
from dual
/
column var1 noprint
column var2 noprint
select 'a' var1, 0 var2, 'SELECT '
from dual
union
select 'b', column_id, decode(column_id, 1, ' ', ' , ')
||
decode(data_type,'DATE','to_char('||'"'||column_name||'"'||',''YYYYMMDDHH24MISS'')
'||'"'||column_name||'"' ,
'"'||column_name||'"')
from dba_tab_columns
where owner=upper('&&owner')
and table_name=upper('&&table_name')
union
select 'c', 0, 'FROM &&owner..&&table_name'
from dual
union
select 'd', 0, ';'
from dual
order by 1,2
/
select 'SPOOL OFF'
from dual
/
select 'SET TERMOUT ON'
from dual
/
spool off
-----------------------------------------------------------------------------
-- Generate the SQL*Loader control file
-----------------------------------------------------------------------------
set lines 120 pages 0
spool &&owner..&&table_name..CTL
select 'a' var1, 0 var2, 'OPTIONS(DIRECT=TRUE)'
from dual
union
select 'b', 0, 'LOAD DATA'
from dual
union
select 'c', 0, 'INFILE ''/tmp/&&owner..&&table_name..DAT'''
from dual
union
select 'd', 0, 'BADFILE &&owner..&&table_name..BAD'
from dual
union
select 'e', 0, 'DISCARDFILE &&owner..&&table_name..DSC'
from dual
union
select 'f', 0, 'DISCARDMAX 999'
from dual
union
select 'm', 0, 'INTO TABLE &&owner..&&table_name'
from dual
union
select 'n', column_id, rpad(decode(column_id,1,'(',',')||'"'||column_name||'"',31)
|| decode(data_type,
'CHAR','CHAR('||data_length||')',
'VARCHAR','CHAR('||data_length||')',
'VARCHAR2','CHAR('||data_length||')',
'DATE','DATE(14) "YYYYMMDDHH24MISS"',
'NUMBER','DECIMAL EXTERNAL('||decode(data_precision,
'',&default_precision+2,
greatest(data_precision-data_scale,1)+decode(data_scale,0,0,1)+data_scale+1)
||')',
'FLOAT','DECIMAL
EXTERNAL('||to_char(&default_precision+2)||')',
'ERROR--'||data_type)
|| ' NULLIF ("' ||column_name||'" = BLANKS)'
from dba_tab_columns
where owner = upper('&&owner')
and table_name = upper('&&table_name')
union
select 'z', 0, ')'
from dual
order by 1, 2
/
spool off
-----------------------------------------------------------------------------
-- Cleanup
-----------------------------------------------------------------------------
clear column
clear break
clear compute
undef owner
undef table_name
undef default_precision
undef default_scale
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
