In the situation that you have installed the Partitioning option and you don’t want your developers to use this feature, you could use the installer or “chopt” to remove the option.
However, with these utilities you need to shut down the database and that is not always desirable.
You could also use an INSTEAD OF CREATE on DATABASE to prevent your developers to create partitioned tables:
create trigger wi_prevent_partitioning instead of create on database when (ora_dict_obj_type = 'TABLE') declare l_sql varchar2(32767); l_sql_list ora_name_list_t; begin for r_i in 1 .. ora_sql_txt(l_sql_list) loop l_sql := l_sql||l_sql_list(r_i); end loop; -- if ( instr(lower(l_sql),'partition') > 0 ) then raise_application_error ( -20999 , 'Partitioning is not allowed' ); else l_sql := replace(l_sql,chr(10),' '); -- l_sql := replace( l_sql , 'CREATE TABLE ' , 'CREATE TABLE ' ||ora_login_user||'.' ); execute immediate l_sql; end if; end;
A word of caution: I have tested this with success in a 11GR2 database with APEX 4.2.
In 10.2.0.4 with APEX 4.1 I got an ORA-600 error when I went to the application section of the builder.
So of course test, test, test.