SQL Script / Table Partitioning

Asked by At

Is there an easy way to determine in a SQL Script if the ORACLE Table Partitioning feature is available?

I want to create some of my tables as partitoned tables if the feature is available, otherwise the tables should be created normally. I have a script with my DDL which I use to setup the database via sqlplus.

Thanks.

JeHo

2 Answers

6
Community On Best Solutions

The following query will tell you whether partitioning is enabled:

select value from v$option where parameter = 'Partitioning';
1
Tony Andrews On

If partitioning is not available then you should get this error:

ORA-00439: feature not enabled: Partitioning

So you could write PL/SQL in your script to create the table like this:

declare
  no_partioning exception;
  pragma exception_init (no_partioning, -439);
begin
  execute immediate 'CREATE TABLE mytable ...'; -- with partioning clauses
exception
  when no_partioning then
    execute immediate 'CREATE TABLE mytable ...'; -- without partioning clauses
end;
/