OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQL Loader (SQL Loader 9.x, Windows XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader [message #410477]

Sat, 27 June 2009 04:18

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

Report message to a moderator

Re: SQL Loader [message #410478 is a reply to message #410477]

Sat, 27 June 2009 04:23

ThomasG
Messages: 3211
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.

Report message to a moderator

Re: SQL Loader [message #410479 is a reply to message #410477]

Sat, 27 June 2009 04:35

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

Report message to a moderator

Re: SQL Loader [message #410507 is a reply to message #410479]

Sat, 27 June 2009 14:12

Littlefoot
Messages: 21808
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

ThomasG
Messages: 3211
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?

Report message to a moderator

Previous Topic:	Unable to export users logical schema,,,!!!
Next Topic:	import issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 28 05:23:57 CDT 2024