Oracle – How to export & import only data in oracle 11g

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update: To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2: Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

From the documentation:

Format: Select the desired output format for the data to be unloaded. Depending on the selected format, other options may appear. For example, for xls (Microsoft Excel file), you can specify worksheet names for the data and the SELECT statement.

For CLOB data, exporting is supported only if the format is loader (SQL*Loader) or pdf (PDF). Some export types export only a subset of the string followed by an ellipsis (...).

It doesn't explicitly refer to BLOBs, but if CLOBs can only be exported as loader or pdf, it makes sense that BLOBs would also have that restriction. If you want to recreate this data in another schema or database, SQL*Loader format seems like a good choice.

What would you expect the insert statement to look like? You'd have to have a text literal containing the binary value, which is a problem in itself, but you'd also be limited to 4k - which many image files might exceed. For a CLOB it might give you the first 4903 chars followed by an ellipsis in the string literal, but I'm not sure; for a BLOB even that wouldn't make any sense.

If you want to transfer data between databases you should consider data pump export/import, or if you (or your client) are restricted by server access then you could fall back to legacy export/import. Both support LOBs. Data pump is superior and should be used in preference if at all possible. The only downside really is that the dump files are written to the database server and getting access to them (or permissions on a directory object to write to can be problematic in some organisations.