How to realize the pagination in Oracle

Let say you have 10 records in your OWNED_MEDIA table. In the query you specify the page_number 2 (page_number starts from 1) and page_size 5, your instinct will tell you the query should look like this

SELECT * FROM OWNED_MEDIA
WHERE ROWNUM <= 10 and ROWNUM > 5
order by id

However, this doesn't work.

The correct pagination query will look like this:

SELECT *
FROM (SELECT fullset.*, ROWNUM low_value
FROM (SELECT * FROM OWNED_MEDIA order by id) fullset
WHERE ROWNUM <= 10) WHERE low_value > 5