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')
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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s