CODEWITHSUNDEEP
databaseoracle-databasedatabase-partitioning
Konrad
Konrad

Reputation: 18585

Understanding system naming convention in Oracle

As stated in documentation names starting with SYS_ and ORA_ are used by the Oracle system:

Oracle uses system-generated names beginning with SYS_ for implicitly generated schema objects and subobjects, and names beginning with ORA_ for some Oracle-supplied objects. Oracle discourages you from using these prefixes in the names you explicitly provide to your schema objects and subobjects to avoid possible conflict in name resolution.

I'm currently working with an interval-partitioned table with partition names:

SYS_P2672
SYS_P1787
SYS_P654

Is there a convention according to which the integer in partition name SYS_2627 is being generated? In particular, I would like to predict future partition names assuming that my partitions are generated by monthly or weekly intervals.

Upvotes: 1

Views: 931

Answers (1)

Jon Heller
Jon Heller

Reputation: 36862

Use partition extended names and the data dictionary instead of relying on system names.

The partition extended name syntax allows SQL statements to reference partitions based on a value inside the partition instead of the actual partition name.

Syntax:

enter image description here

(There is also a similar SUBPARTITION version.)

Example:

--Create and populate sample schema.
--drop table test1;

create table test1(a date)
partition by range(a)
interval (numToDSInterval(1, 'DAY'))
(
    partition p1 values less than (date '2000-01-01')
);

insert into test1
select date '2000-01-01' + level
from dual
connect by level <= 100;

--Select the partition using a value.
select * from test1 partition for (date '2000-01-05');

If that doesn't work the partition information can be retrieved through the data dictionary. This gets tricky, especially because the HIGH_VALUE is stored as text in a LONG column. But it does allow complete control and automation when used in PL/SQL.

--Must create as a table to convert LONG to CLOB in SQL.
--(This would be easier in PL/SQL, you can just "TO_CHAR" it.)
create table temp_convert_long_to_lob as
select table_name, partition_name, to_lob(high_value) high_value
from user_tab_partitions
where table_name = 'TEST1';

--Show some data about the partitions.
select table_name, partition_name, to_char(high_value) high_value
from temp_convert_long_to_lob
order by partition_name;

TABLE_NAME   PARTITION_NAME   HIGH_VALUE
----------   --------------   ----------
TEST1        P1               TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST1        SYS_P3094        TO_DATE(' 2000-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST1        SYS_P3095        TO_DATE(' 2000-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST1        SYS_P3096        TO_DATE(' 2000-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Upvotes: 1

Related Questions