How to use the Oracle 12c Row-limiting clauses

One of the most useful features of Oracle 12c is the ability to limit the number of rows from a query using top-N queries. Amongst others, they provide a great way to populate test tables.

It can be used in several ways, by specifying an offset of rows to skip, the number of rows to return or the percentage of rows to return.

A couple of simple examples:

Fetch the first 5 rows from the result set:

select * 
from scott.emp
fetch first 5 rows only;

Fetch the next 10 rows after the first five rows have been skipped:

select *  
from Scott.emp 
offset 5 rows fetch next 10 rows only;

Load the first few rows of data from a specific partition, into a new table:

insert into test_table
SELECT  *
FROM       original_table
subpartition (other_sp_default)
fetch first 10 rows only;

Sources:

http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABHFGAA

and

http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABEAACC