Bug: In generating table script where subpartitions are involved

A

ashish_mistry

Member²
PlSQL developer Version 7.1.5.1398

Hi, Plsql developer does not display the partitions or subpartition information in the table edit window,the View sql also does not generate the exact table script where subpartitions are involved.

Hope this will be patched/fixed and wont have to wait for my org to purchase the newer version

Script generated by by plsql developer
-------------------------------------------
create table CP_USER_EMPLOYEES
(
EMPL_ID VARCHAR2(11) not null,
USER_NAME VARCHAR2(14) not null,
CLASS_TYPE NUMBER(2) not null
);

Actual table script (by another tool)
-------------------------------------------
CREATE TABLE CP_USER_EMPLOYEES
(
EMPL_ID VARCHAR2(11 BYTE) NOT NULL,
USER_NAME VARCHAR2(14 BYTE) NOT NULL,
CLASS_TYPE NUMBER(2) NOT NULL
)
TABLESPACE TBLSPC_A
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
LOGGING
PARTITION BY RANGE (CLASS_TYPE)
SUBPARTITION BY LIST (USER_NAME)
(
PARTITION P0 VALUES LESS THAN (1)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P0_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P0_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC,
SUBPARTITION P0_ABC40009928 VALUES ('ABC40009928') TABLESPACE ABC_SEC,
SUBPARTITION P0_ABC50009928 VALUES ('ABC50009928') TABLESPACE ABC_SEC,
SUBPARTITION P0_ABC30009928 VALUES ('ABC30009928') TABLESPACE ABC_SEC,
SUBPARTITION P0_ABC20009928 VALUES ('ABC20009928') TABLESPACE ABC_SEC,
SUBPARTITION P0_ABC60009928 VALUES ('ABC60009928') TABLESPACE ABC_SEC,
SUBPARTITION P0_ABC70009928 VALUES ('ABC70009928') TABLESPACE ABC_SEC,
SUBPARTITION P0_ABC80009928 VALUES ('ABC80009928') TABLESPACE ABC_SEC ),
PARTITION P1 VALUES LESS THAN (2)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P1_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P1_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P2 VALUES LESS THAN (3)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P2_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P2_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P4 VALUES LESS THAN (5)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P4_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P4_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P5 VALUES LESS THAN (6)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P5_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P5_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P6 VALUES LESS THAN (7)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P6_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P6_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P7 VALUES LESS THAN (8)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P7_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P7_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P8 VALUES LESS THAN (9)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P8_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P8_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P9 VALUES LESS THAN (10)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P9_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P9_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P11 VALUES LESS THAN (12)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P11_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P11_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P12 VALUES LESS THAN (13)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P12_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P12_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P13 VALUES LESS THAN (14)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P13_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P13_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P24 VALUES LESS THAN (25)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P24_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P24_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P28 VALUES LESS THAN (29)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P28_0 VALUES ('0') TABLESPACE ABC_SEC ),
PARTITION P32 VALUES LESS THAN (33)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P32_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P32_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC ),
PARTITION P42 VALUES LESS THAN (43)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P42_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P42_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC,
SUBPARTITION P42_ABC20009928 VALUES ('ABC20009928') TABLESPACE ABC_SEC,
SUBPARTITION P42_ABC89902134 VALUES ('ABC89902134') TABLESPACE ABC_SEC,
SUBPARTITION P42_ABC70009928 VALUES ('ABC70009928') TABLESPACE ABC_SEC ),
PARTITION P51 VALUES LESS THAN (52)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P51_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P51_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC,
SUBPARTITION P51_ABC70009928 VALUES ('ABC70009928') TABLESPACE ABC_SEC ),
PARTITION P52 VALUES LESS THAN (53)
NOLOGGING
NOCOMPRESS
TABLESPACE ABC_SEC
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P52_0 VALUES ('0') TABLESPACE ABC_SEC,
SUBPARTITION P52_ABC10009928 VALUES ('ABC10009928') TABLESPACE ABC_SEC )
)
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

CREATE INDEX I_CP_USER_EMPL_ID ON CP_USER_EMPL_ID
(EMPL_ID)
INITRANS 2
MAXTRANS 255
LOGGING
LOCAL (
PARTITION P0
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P0_0 TABLESPACE ABC_SEC,
SUBPARTITION P0_ABC70009928 TABLESPACE TBLSPC_A,
SUBPARTITION P0_ABC30009928 TABLESPACE TBLSPC_A,
SUBPARTITION P0_ABC50009928 TABLESPACE TBLSPC_A,
SUBPARTITION P0_ABC60009928 TABLESPACE TBLSPC_A,
SUBPARTITION P0_ABC10009928 TABLESPACE TBLSPC_A,
SUBPARTITION P0_ABC20009928 TABLESPACE TBLSPC_A,
SUBPARTITION P0_ABC80009928 TABLESPACE TBLSPC_A,
SUBPARTITION P0_ABC40009928 TABLESPACE TBLSPC_A ),
PARTITION P1
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P1_0 TABLESPACE ABC_SEC,
SUBPARTITION P1_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P2
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P2_0 TABLESPACE ABC_SEC,
SUBPARTITION P2_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P4
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P4_0 TABLESPACE ABC_SEC,
SUBPARTITION P4_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P5
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P5_0 TABLESPACE ABC_SEC,
SUBPARTITION P5_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P6
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P6_0 TABLESPACE ABC_SEC,
SUBPARTITION P6_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P7
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P7_0 TABLESPACE ABC_SEC,
SUBPARTITION P7_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P8
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P8_0 TABLESPACE ABC_SEC,
SUBPARTITION P8_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P9
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P9_0 TABLESPACE ABC_SEC,
SUBPARTITION P9_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P11
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P11_0 TABLESPACE ABC_SEC,
SUBPARTITION P11_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P12
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P12_0 TABLESPACE ABC_SEC,
SUBPARTITION P12_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P13
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P13_0 TABLESPACE ABC_SEC,
SUBPARTITION P13_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P24
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P24_0 TABLESPACE ABC_SEC,
SUBPARTITION P24_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P28
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P28_0 TABLESPACE ABC_SEC ),
PARTITION P32
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P32_0 TABLESPACE ABC_SEC,
SUBPARTITION P32_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P42
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P42_0 TABLESPACE ABC_SEC,
SUBPARTITION P42_ABC70009928 TABLESPACE TBLSPC_A,
SUBPARTITION P42_ABC10009928 TABLESPACE TBLSPC_A,
SUBPARTITION P42_ABC89902134 TABLESPACE TBLSPC_A,
SUBPARTITION P42_ABC20009928 TABLESPACE TBLSPC_A ),
PARTITION P51
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P51_0 TABLESPACE ABC_SEC,
SUBPARTITION P51_ABC70009928 TABLESPACE TBLSPC_A,
SUBPARTITION P51_ABC10009928 TABLESPACE TBLSPC_A ),
PARTITION P52
NOLOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P52_0 TABLESPACE ABC_SEC,
SUBPARTITION P52_ABC10009928 TABLESPACE TBLSPC_A )
)
NOPARALLEL;
 
Hi Marco,

Is it not going to be considered as a bug and should the license not cover it? Partitions and Subpartitions have been in oracle for ages

Plsql developer does not even list the partitions tab in this case, giving no indication that the table is partitioned even, altleast a message that says/ indicates the limitations of the generated script

Regards,
Ashish

 
Last edited:
You must log in or register to reply here.

Similar threads

E
  • Discussion Discussion
Blockchain and inmutable tables
Replies
1
Views
690
Marco Kalter
Marco Kalter
C
  • Discussion Discussion
Global Partitioned Indexes in Table Window
Replies
1
Views
517
Marco Kalter
Marco Kalter
mezonix
  • Discussion Discussion
Subpartitions not showing
Replies
0
Views
359
mezonix
mezonix
E
  • Discussion Discussion
Generate table script doesn't generate "row archival" if activated
Replies
1
Views
1K
Marco Kalter
Marco Kalter
B
  • Discussion Discussion
Export User Objects: Enhancement request
Replies
1
Views
534
Marco Kalter
Marco Kalter
Back
Top