Partition Option & Oracle Server SE One

Actually, the question arose: are there any DBA_TAB_PARTITIONS and other dictionary objects in SE One ? The reason for the question is simple: the code is based on Oracle EE and uses these views. After installation, it was found that these objects are not only present, but not empty. Since they are not empty, it follows from this that it is possible to create segmented tables, and if so, it would be nice to see how this happens.

declare
  v_cnt pls_integer;
begin
  for rec in (select distinct table_owner, table_name from dba_tab_partitions) loop
    execute immediate 'select count(1) from "'||rec.table_owner||'"."'||rec.table_name||'"' into v_cnt;
    if v_cnt > 0 then
      dbms_output.put_line('"'||rec.table_owner||'"."'||rec.table_name||'" - '||v_cnt);
    end if;
  end loop;
end;
/
"SYS"."WRH$_SEG_STAT" – 3138

As you can see, not only is there an object with sections, but it is also not empty ...

What happens during the usual creation of a segmented table? Let's try:

SQL> create table t (n number) partition by hash(n) partitions 16;
create table t (n number) partition by hash(n) partitions 16
*
ERROR at line 1:
ORA-00439: feature not enabled: Partitioning

Now take a look at the file where the WRH $ _SEG_STAT object is created .
Is this the file ? /Rdbms/admin/catawrtb.sql .

At the very beginning there is a line:

-- Turn ON the event to disable the partition check
alter session set events '14524 trace name context forever, level 1';

Well then, let's apply:

SQL> connect usr/password@database
Connected.
SQL> alter session set events '14524 trace name context forever, level 1';
Session altered.
SQL> create table t (n number) partition by hash(n) partitions 16;
Table created.
SQL>

Thus, we created a segmented table on Oracle SE One . There is only one BUT - this violates the Oracle license for this product.

Why is this useful?

Imagine that you have scripts for installing a schema on Oracle EE with creating segmented tables, i.e. requiring a Partition Option .
Those. you

1. turn off the test for Partition Option :

   alter system set events '14524 trace name context forever, level 1';
   

2. create your own scheme
3. Reorganize segmented tables into regular ones by dbms_redefinition
4. return event state back:

   alter system set events '14524 trace name context off';
   

And finally, I tried to fulfill the request:

select name ,version ,detected_usages
,total_samples ,currently_used ,first_usage_date
,last_usage_date ,aux_count ,last_sample_period
  from dba_feature_usage_statistics
 where last_usage_date is not null
   and name like '%Part%'
;
NAME VERSION DETECTED_USAGES TOTAL_SAMPLES CURRE FIRST_US LAST_USA
AUX_COUNT LAST_SAMPLE_PERIOD
--------------------- ----------- --------------- ------------- ----- -------- -------- -
--------- ------------------
Partitioning (system) 11.2.0.3.0 1 1 TRUE 28.11.12 28.11.12
2,74 0

There is no data on the use of the Partition Option for the user (i.e., the row with Partitioning ( user )) in the dba_feature_usage_statistics table , i.e. formally - there are no traces left.

As a result, we have the opportunity to use scripts oriented to Oracle EE to deploy database objects even if we use the Oracle Partition Option and then reorganize them to regular tables so as not to violate Oracle RDBMS licenses .