Home » RDBMS Server » Server Utilities » SQL Loader (SQL Loader 9.x, Windows XP)
SQL Loader [message #410477] Sat, 27 June 2009 04:18 Go to next message
Sivaperumalr
Messages: 9
Registered: June 2009
Location: Chennai
Junior Member
Hi,

While Importing datafile ( apprx.15Lakh records) into my Oracle9i Database thru SQL Loader , it removes the index of the table. Only one index available for that table. Which is basically Customer information Master table. Import is a regular process ( Daily). But this problem we are facing occasionaly.

Can anyone tell me the reason?

Regards
Siva.R
Re: SQL Loader [message #410478 is a reply to message #410477] Sat, 27 June 2009 04:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There is no way that an SQLLoader import would "remove" an index.

Unless you specifically remove that index in some script that is called during the import process.

So have a look at those scripts for any DROP statements.
Re: SQL Loader [message #410479 is a reply to message #410477] Sat, 27 June 2009 04:35 Go to previous messageGo to next message
Sivaperumalr
Messages: 9
Registered: June 2009
Location: Chennai
Junior Member
The process is like this,

We do import in Temp table ( SQL Loader ). Then we delete duplicate records from temp table. Then We truncate the main table. Finally Select and Insert to main table.

No script is running!

Thanks
Re: SQL Loader [message #410507 is a reply to message #410479] Sat, 27 June 2009 14:12 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, none of these actions drops index.

Create tables and index on main_table:
SQL> create table temp_table (col number);

Table created.

SQL> create table main_table (col number);

Table created.

SQL> create index i1_main on main_table (col);

Index created.

SQL>

Here's a sample control file with some data:
load data
  infile *
  replace
into table temp_table
(col)

begindata
1
4
1
So let's load it!
SQL> $sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sub Lip 27 21:06:16 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * from temp_table;

       COL
----------
         1
         4
         1

SQL>

Delete duplicates:
SQL> delete from temp_table t1
  2  where t1.rowid <> (select min(t2.rowid)
  3                     from temp_table t2
  4                     where t2.col = t1.col
  5                    );

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from temp_table;

       COL
----------
         1
         4

SQL>
Truncate the main table:
SQL> truncate table main_table;

Table truncated.

SQL>

Finally, insert into the main table:
SQL> insert into main_table select * from temp_table;

2 rows created.

SQL> select * from main_table;

       COL
----------
         1
         4

SQL> 

Moment of the truth: is index still here?
SQL> select index_name from user_indexes where table_name = 'MAIN_TABLE';

INDEX_NAME
------------------------------
I1_MAIN

SQL>

Hm, it is still here.

Is there something else you'd want to share with us?

[EDIT] Forgot to mention (but you have seen it already): my database is not 9.x but 10.2.0.1

[Updated on: Sat, 27 June 2009 14:14]

Report message to a moderator

Re: SQL Loader [message #410515 is a reply to message #410507] Sat, 27 June 2009 17:32 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What?

There are multiple actions happening, but there is no script running?

What does make them happen? Magic?
Previous Topic: Unable to export users logical schema,,,!!!
Next Topic: import issue
Goto Forum:
  


Current Time: Tue Dec 06 12:26:26 CST 2016

Total time taken to generate the page: 0.08395 seconds