Preventing the usage of partitioning in Oracle

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')
  l_sql      varchar2(32767);
  l_sql_list ora_name_list_t;
  for r_i in 1 .. ora_sql_txt(l_sql_list)
    l_sql := l_sql||l_sql_list(r_i);
  end loop;
  if ( instr(lower(l_sql),'partition') > 0 )
      ( -20999
      , 'Partitioning is not allowed'
    l_sql := replace(l_sql,chr(10),' '); 
    l_sql := replace( l_sql
                    , 'CREATE TABLE '
                    , 'CREATE TABLE '
    execute immediate l_sql;
  end if;

A word of caution: I have tested this with success in a 11GR2 database with APEX 4.2.
In 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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s