How to Prevent a User to Drop Own Objects

An Oracle user is always granted to drop their own objects. To prevent a user to drop their own objects, we can use DDL triggers.

Here’s sample trigger which will prevent HR user to drop EMPLOYEES table:

When HR user tries to drop employees table, he’ll see an ORA-20000 error.

For more information and samples about DDL triggers:

How to Limit the Number of Rows Returned in Oracle

I worked with MySQL for a long time, so I know how useful to limit the number of returning rows of a query. For example,

Unfortunately, Oracle does not have an operator like LIMIT. On the other hand, we can emulate it by using ROWNUM pseudo column. ROWNUM will tell the row number.

So we can limit the number of returning rows:

Unfortunately, if we add ORDER BY, GROUP BY operators to the query, this method will not work, because rownum will show the row number before “sorting”. So we use rownum in a subquery: