The trigger below will only allow user A, B or C create up to 10 objects within the database. Once the 11th object is created then the database will throw up the raise application error.

create or replace trigger no_more_than_10
before create on database
l_cnt number;
if (user in (‘A’,’B’,’C’))
select count(*) into l_cnt
from dba_objects
where owner = USER
and object_type = ‘TABLE’;

if (l_cnt >= 10)
raise_application_Error (-20001, ‘You can only create up to 10 objects’);
end if;

end if;

How to limit the number of objects created by an Oracle user

Leave a Reply

Your email address will not be published. Required fields are marked *

sixty one − sixty =