All geek questions in one place

SQL Script / Table Splitting

Is there an easy way to determine in SQL Script if the ORACLE table partition function is available?

I want to create some of my tables as partitions if this function is available, otherwise the tables should be created in normal mode. I have a Script with my DDL, which I use to configure the database through sqlplus.

Thanks.

Jeho

+3
source share
2 answers

The following query will indicate if separation is enabled:

select value from v$option where parameter = 'Partitioning';
+6
source

If sharing is not available, you should get this error:

ORA-00439: feature not enabled: Partitioning

So you can write PL / SQL in your script to create the table as follows:

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;
/
+1
source

Source: https://habr.com/ru/post/1721399/

More articles:


All Articles