IMCS bug

View All Public Scripts Login to Run Script

Script Name IMCS bug
Visibility Unlisted - anyone with the share link can access
Description Demo BUG in IMCS and composite-partitioned tables
Area Oracle 19c
Contributor Dava
Created Monday September 06, 2021

Statement 1

create table RE 
(  
cobdate date, 
entity  varchar(20), 
label   varchar2(20), 
value   float(126) 
) 
PARTITION BY RANGE (COBDATE) INTERVAL (NUMTODSINTERVAL(1, 'DAY'))  
  SUBPARTITION BY LIST (ENTITY)  
  SUBPARTITION TEMPLATE (  
    SUBPARTITION "PART_A" VALUES ( 'A' ),  
    SUBPARTITION "PART_B" VALUES ( 'B' ),  
    SUBPARTITION "PART_C" VALUES ( 'C' ),  
    SUBPARTITION "PART_OTHERS" VALUES ( DEFAULT ) )     
(PARTITION "P_20210830"  VALUES LESS THAN (TO_DATE(' 2021-08-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
) 
  PARALLEL 8

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 2

insert into RE values ( DATE '2021-08-30', 'A', 'AAA', 11.11 )

1 row(s) inserted.

Statement 3

insert into RE values ( DATE '2021-08-30', 'B', 'BBB', 22.11 )

1 row(s) inserted.

Statement 4

insert into RE values ( DATE '2021-08-30', 'XXX', 'XXX', 33.11 )

1 row(s) inserted.

Statement 5

insert into RE values ( DATE '2021-09-01', 'A', 'AAA', 11.22 )

1 row(s) inserted.

Statement 6

insert into RE values ( DATE '2021-09-01', 'B', 'BBB', 22.22 )

1 row(s) inserted.

Statement 7

Need to get lastest partition name ( as it is system generated ) from this query and modify next SQL statement

Find out what partitoin name to use in next statement

select * from user_Tab_partitions where table_name='RE'


TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT MAX_SIZE PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION INTERVAL SEGMENT_CREATED INDEXING READ_ONLY INMEMORY INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION INMEMORY_DUPLICATE CELLMEMORY INMEMORY_SERVICE INMEMORY_SERVICE_NAME MEMOPTIMIZE_READ MEMOPTIMIZE_WRITE
RE YES P_20210830 4 TO_DATE(' 2021-08-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 83 1 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - NONE NONE  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO N/A N/A NO NONE NONE NONE NONE  -  -  -  -  -  -  - N/A N/A
RE YES SYS_P308259 4 TO_DATE(' 2021-09-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 83 2 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - NONE NONE  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO N/A N/A YES NONE NONE NONE NONE  -  -  -  -  -  -  - N/A N/A

2 rows selected.

Statement 8
Change the partition name (as its dynamic) in this ALTER TABLE statement to use the correct partition-name from above
Modify this ALTER TABLE statement to use the correct partition-name from previous query
```
alter table RE modify partition SYS_P308259 inmemory memcompress for capacity low priority medium
```
```
Table altered.
```

Statement 9

select round(i.inmemory_size/1024/1024,1) MB_mem, round(i.bytes/1024/1204,1) MB_disk, i.* from v$im_segments i order by segment_name,partition_name

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 10

insert into RE values ( DATE '2021-09-01', 'C', 'CCC', 99.99 )

1 row(s) inserted.

Statement 11

select * from re where cobdate= '01-SEP-21' and entity ='C'


COBDATE ENTITY LABEL VALUE
01-SEP-21 C CCC 99.99

COBDATE	ENTITY	LABEL	VALUE
01-SEP-21	C	CCC	99.99

Statement 12

select * from re where cobdate= DATE '2021-09-01' and entity ='C'


COBDATE ENTITY LABEL VALUE
01-SEP-21 C CCC 99.99

COBDATE	ENTITY	LABEL	VALUE
01-SEP-21	C	CCC	99.99

Statement 13

select round(i.inmemory_size/1024/1024,1) MB_mem, round(i.bytes/1024/1204,1) MB_disk, i.* from v$im_segments i order by segment_name,partition_name

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

TABLE_NAME	COMPOSITE	PARTITION_NAME	SUBPARTITION_COUNT	HIGH_VALUE	HIGH_VALUE_LENGTH	PARTITION_POSITION	TABLESPACE_NAME	PCT_FREE	PCT_USED	INI_TRANS	MAX_TRANS	INITIAL_EXTENT	NEXT_EXTENT	MIN_EXTENT	MAX_EXTENT	MAX_SIZE	PCT_INCREASE	FREELISTS	FREELIST_GROUPS	LOGGING	COMPRESSION	COMPRESS_FOR	NUM_ROWS	BLOCKS	EMPTY_BLOCKS	AVG_SPACE	CHAIN_CNT	AVG_ROW_LEN	SAMPLE_SIZE	LAST_ANALYZED	BUFFER_POOL	FLASH_CACHE	CELL_FLASH_CACHE	GLOBAL_STATS	USER_STATS	IS_NESTED	PARENT_TABLE_PARTITION	INTERVAL	SEGMENT_CREATED	INDEXING	READ_ONLY	INMEMORY	INMEMORY_PRIORITY	INMEMORY_DISTRIBUTE	INMEMORY_COMPRESSION	INMEMORY_DUPLICATE	CELLMEMORY	INMEMORY_SERVICE	INMEMORY_SERVICE_NAME	MEMOPTIMIZE_READ	MEMOPTIMIZE_WRITE
RE	YES	P_20210830	4	TO_DATE(' 2021-08-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	83	1	LIVESQL_USERS	10	-	1	255	-	-	-	-	-	-	-	-	NONE	NONE	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	N/A	N/A	NO	NONE	NONE	NONE	NONE	-	-	-	-	-	-	-	N/A	N/A
RE	YES	SYS_P308259	4	TO_DATE(' 2021-09-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	83	2	LIVESQL_USERS	10	-	1	255	-	-	-	-	-	-	-	-	NONE	NONE	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	N/A	N/A	YES	NONE	NONE	NONE	NONE	-	-	-	-	-	-	-	N/A	N/A