0

SQL*Loader: Oracle uses this functionality, through the ORACLE_LOADER access driver to move data from a flat file into the database;

Data Pump: It uses a Data Pump access driver to move data out of the database into a file in an proprietary Oracle format, and back into the database from files of that format.

When a data load can be done by either the SQL*Loader or Data Pump utilities, and data unload can also be done by the Data Pump utility:

  1. Are there any extra benefits that can be achieved by using external tables, that none of the previously mentioned utilities can do by themselves?

  2. The below Oracle table creation command creates a table which looks like an Oracle table.Why are then Oracle telling us to call it as an external table?

    create table export_empl_info organization external
  ( type oracle_datapump
    default directory xtern_data_dir
    location ('empl_info_rpt.dmp')
     ) as select * from empl_info;
CodeLover
  • 1,054
  • 6
  • 24
  • 40
  • Your last question doesn't really make sense. Could you rephrase it? Also, please don't stuff too many questions into one, and stick with practical, answerable questions. Question n°1 is pretty broad and answered by the docs. Question 2 and 3 are essentially the same. – Mat Jan 27 '13 at 10:33
  • @Mat i have modified my description, Any more improvement would you like to suggest? – CodeLover Jan 27 '13 at 11:01
  • 2
    Yes: stop using `code formatting` for `plain` English `words` or brands `or` product `names`. It makes stuff hard to read. There's no need to put your questions in italics either, the list items make them _stand out_ enough. Only use bold or italics to highlight **specific** terms or phrases that need to stand out. Lastly "achieve limitations" doesn't make sense. You never aim for a limitation. – Mat Jan 27 '13 at 11:03
  • And your second question doesn't male sense either. Oracle isn't suggesting this is an external table. You the user are telling Oracle to create an external table. – Mat Jan 27 '13 at 11:05
  • Your question is slightly confused as @Mat has identified. The title makes this not constructive as none of us can know _why_ Oracle did something... Are you asking what extra functionality you gain from external tables and when you should be using them? – Ben Jan 27 '13 at 11:16

3 Answers3

3

"Are there any extra benefits that can be achieved by using external tables, that none of the previously mentioned utilities can do by themselves?"

SQL*loader and Datapump both require us to load the data into tables before we can access it with the database. Whereas we only access external tables through SELECT statements. It's a much more flexible mechanism.

"Why are then Oracle telling us to call it as an external table?"

umm, because it is external. The data resides in an file (or files) which is controlled by the OS. We can change the data in an external table by running an OS command like

$>  cp wnatever.csv external_table_data.csv

There's no redo, rollback, flashback query or any of the other appurtenances of an internal database table.

Ben
  • 51,770
  • 36
  • 127
  • 149
APC
  • 144,005
  • 19
  • 170
  • 281
1

I think that the primary benefits of external tables for me have been:

i) Not having to execute a host command to import data, which supports a trend in Oracle to control the entire code bade from inside the database. Preprocessing in 11g allows access to remote files through ftp, use of compressed files, combining multiple files into one, etc

ii) More efficient loads, by means of applying complex data transformations during the load process. Aggregations, merges, multitable inserts ... etc

I've used it for data warehouse loads, but any scenario requiring loading of or access to standard data files is a candidate for use of external tables. SQL*Loader still has its place as a tool for loading to an Oracle database from a client or other host system. Data pump is for transfer of data between Oracle databases, so it's rather different.

One limitation of external tables is that they won't process stream data -- records have to be delimited. This was true in 10.2, not sure if it's been permitted since then.

Use the system catalog views ALL/DBA/USER_EXTERNAL_TABLES for information on them

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
0

RE: Why external table vs sqlldr for loading data? Mainly to have server managed parallelism vs client managed parallelism.

orcl_slave
  • 11
  • 1