I have a table which contains 1 million lines and need to export into different files which containing 100k lines each.

sample_01.sql ,sample_02.sql and so on

How can we take export like this in oracle?

  • To be honest I would just create 1 file for the whole table then split it to 10 smaller ones. Mar 30 '20 at 12:24
  • the usage of the word export is confusing here. In oracle this means extraction of structures and data with special tools (exp, expdp) into files that have a special format. The content of these kind of files can only me imported into Oracle databases. So I assume you want to write the output of one or more SQL statements into text files. Which tool do you use to query the database? Why do you want to extract this data and why do you want to split it??
    – miracle173
    Mar 30 '20 at 13:47

You should probably use utl_file and a for loop for this assignment. Then you can open and write one line per file. You should also let people know what a Lakh line is.


1 Lakh = 100,000.
10 Lakh = 1 million.

Question: Why do you want to do this?

A million row database table is not that significant these days.

A million row file won't give any modern file system much difficulty either.

OK, you won't find many editor programs that will allow you to usefully open such a file but - again - why would you want to?

Files, in this context, should be seen as a Transport media, not something that you'd want to actively work with except, perhaps, to load them into another database!

  • i want to insert that rows in MySQL .it is part of migration i'm wasting so much time there taking that table export and splitting into different into different files. Mar 30 '20 at 13:31
  • Use the MySQL version of SQL-Loader to import the data. A 10M row file isn't a problem if you use the right tools. Mar 30 '20 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.