Datapump query for top 'N' rows

Datapump query is extremely powerful in letting you change what exactly is extracted into the dumpfile. I've done examples before where the data extracted can be based on data in other tables but what if you want to just retrieve the top 'N' rows from a table?

Again it's simple enough you just need to get the query syntax correct.

Lets do a simple example


SQL> conn rich/rich;
Connected.
SQL> create table demo (col1 number, col2 date);

Table created.

SQL> insert into demo values (1,sysdate);

1 row created.

SQL> insert into demo values (2,sysdate);

1 row created.

SQL> insert into demo values (3,sysdate);

1 row created.

SQL> insert into demo values (4,sysdate);

1 row created.

SQL> insert into demo values (5,sysdate);

1 row created.

SQL> insert into demo values (6,sysdate);

1 row created.

SQL> commit;

Commit complete.

So now we have a table with 6 rows and we want to retrieve the 'top 5' for example.

Here is how we would do that (and use a parfile for this rather than beating yourself over the head with escape characters on the command line).

[oracle@localhost ~]$ cat rich.par
include=TABLE:"='DEMO'"
query="where col1 in (select col1 from (select col1,row_number() over (order by col1 asc) as x from demo) where x <=5)"


Now lets run that and see what we get


[oracle@localhost ~]$ expdp rich/rich parfile=rich.par

Export: Release 11.2.0.2.0 - Production on Mon Feb 24 04:41:04 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RICH"."SYS_EXPORT_SCHEMA_01":  rich/******** parfile=rich.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "RICH"."DEMO"                               5.468 KB       5 rows
Master table "RICH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RICH.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/app/oracle/admin/orcl/dpdump/expdat.dmp
Job "RICH"."SYS_EXPORT_SCHEMA_01" successfully completed at 04:41:45

All - looks good only 5 rows extracted - but are they the correct ones?
lets drop the table and import back in


[oracle@localhost ~]$ sqlplus rich/rich

SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 24 04:43:55 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> drop table demo;

Table dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ impdp rich/rich

Import: Release 11.2.0.2.0 - Production on Mon Feb 24 04:44:14 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "RICH"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "RICH"."SYS_IMPORT_FULL_01":  rich/********
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "RICH"."DEMO"                               5.468 KB       5 rows
Job "RICH"."SYS_IMPORT_FULL_01" successfully completed at 04:44:20

[oracle@localhost ~]$ sqlplus rich/rich

SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 24 04:44:28 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pages 0 lines 1024

SQL> /
         1 24-FEB-14
         2 24-FEB-14
         3 24-FEB-14
         4 24-FEB-14
         5 24-FEB-14

And all looks good - only rows 1->5 are loaded (you make thing this was just coincidence somehow as it was just a limited dataset - but it is correct......)