OK. Well, I don’t think Toad is the culprit. Toad just sends insert statements to Oracle, and Oracle decides what to do after that. Have you tried an insert statement from SQLPlus? I bet you will get the same result.
I understand, then the problem must be within our Oracle database…
Please do not speak about Oracle bugs in partitioning. They just do not exist on level you mentioned. here is mine whole example that prove new records create new partitions!
create table demo_part (
install_date DATE,
customer_name VARCHAR2(100),
import_id INTEGER
)
PARTITION BY RANGE (IMPORT_ID)
INTERVAL(1)
(
PARTITION p2 VALUES LESS THAN (2)
);
CREATE SEQUENCE DVADAS.demo_part_seq
START WITH 10
INCREMENT BY 1
MINVALUE 10
NOCACHE
NOCYCLE
ORDER;
select count(*) from demo_part;
Generate 10 records (with Toad Data Generator)
DECLARE
BEGIN
– Generate Random Data
INSERT INTO DVADAS.DEMO_PART (
INSTALL_DATE, CUSTOMER_NAME, IMPORT_ID
) VALUES (
TO_DATE(‘14-06-2013’, ‘DD-MM-YYYY’), ‘XW Transmission’, DVADAS.DEMO_PART_SEQ.NextVal
);
INSERT INTO DVADAS.DEMO_PART (
INSTALL_DATE, CUSTOMER_NAME, IMPORT_ID
) VALUES (
TO_DATE(‘14-01-2003’, ‘DD-MM-YYYY’), ‘NM Roofers’, DVADAS.DEMO_PART_SEQ.NextVal
);
INSERT INTO DVADAS.DEMO_PART (
INSTALL_DATE, CUSTOMER_NAME, IMPORT_ID
) VALUES (
TO_DATE(‘08-06-2007’, ‘DD-MM-YYYY’), ‘Larsen Windows’, DVADAS.DEMO_PART_SEQ.NextVal
);
INSERT INTO DVADAS.DEMO_PART (
INSTALL_DATE, CUSTOMER_NAME, IMPORT_ID
) VALUES (
TO_DATE(‘03-11-2008’, ‘DD-MM-YYYY’), ‘Pennington Lens and Glasses’, DVADAS.DEMO_PART_SEQ.NextVal
);
INSERT INTO DVADAS.DEMO_PART (
INSTALL_DATE, CUSTOMER_NAME, IMPORT_ID
) VALUES (
TO_DATE(‘19-11-2010’, ‘DD-MM-YYYY’), ‘Espinoza Party Supplies’, DVADAS.DEMO_PART_SEQ.NextVal
);
INSERT INTO DVADAS.DEMO_PART (
INSTALL_DATE, CUSTOMER_NAME, IMPORT_ID
) VALUES (
TO_DATE(‘20-02-2004’, ‘DD-MM-YYYY’), ‘NY Auto Services’, DVADAS.DEMO_PART_SEQ.NextVal
);
INSERT INTO DVADAS.DEMO_PART (
INSTALL_DATE, CUSTOMER_NAME, IMPORT_ID
) VALUES (
TO_DATE(‘23-06-2000’, ‘DD-MM-YYYY’), ‘KHIS Funeral Services’, DVADAS.DEMO_PART_SEQ.NextVal
);
INSERT INTO DVADAS.DEMO_PART (
INSTALL_DATE, CUSTOMER_NAME, IMPORT_ID
) VALUES (
TO_DATE(‘27-12-2003’, ‘DD-MM-YYYY’), ‘Nichols Elementary School’, DVADAS.DEMO_PART_SEQ.NextVal
);
INSERT INTO DVADAS.DEMO_PART (
INSTALL_DATE, CUSTOMER_NAME, IMPORT_ID
) VALUES (
TO_DATE(‘25-10-2008’, ‘DD-MM-YYYY’), ‘LQH Network Services’, DVADAS.DEMO_PART_SEQ.NextVal
);
INSERT INTO DVADAS.DEMO_PART (
INSTALL_DATE, CUSTOMER_NAME, IMPORT_ID
) VALUES (
TO_DATE(‘13-06-2013’, ‘DD-MM-YYYY’), ‘CK Computers’, DVADAS.DEMO_PART_SEQ.NextVal
);
END;
/
COMMIT;
/
DVADAS@mom2d> create table demo_part (
2 install_date DATE,
3 customer_name VARCHAR2(100),
4 import_id INTEGER
5 )
6 PARTITION BY RANGE (IMPORT_ID)
7 INTERVAL(1)
8 (
9 PARTITION p2 VALUES LESS THAN (2)
10 );
Table created.
DVADAS@mom2d> select count(*) from demo_part;
COUNT(*)
0
DVADAS@mom2d> CREATE SEQUENCE DVADAS.demo_part_seq
2 START WITH 10
3 INCREMENT BY 1
4 MINVALUE 10
5 NOCACHE
6 NOCYCLE
7 ORDER;
Sequence created.
DVADAS@mom2d> – insert 10 generated records
DVADAS@mom2d> select count(*) from demo_part;
COUNT(*)
10
DVADAS@mom2d>
Look at table script (through Toad)
CREATE TABLE DVADAS.DEMO_PART
(
INSTALL_DATE DATE,
CUSTOMER_NAME VARCHAR2(100 BYTE),
IMPORT_ID INTEGER
)
NOCOMPRESS
TABLESPACE USERS
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
PARTITION BY RANGE (IMPORT_ID)
INTERVAL( 1)
(
PARTITION P2 VALUES LESS THAN (2)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION VALUES LESS THAN (11)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 8M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION VALUES LESS THAN (12)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 8M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION VALUES LESS THAN (13)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 8M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION VALUES LESS THAN (14)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 8M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION VALUES LESS THAN (15)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 8M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION VALUES LESS THAN (16)
LOGGING
NOCOMPRESS
TABLESPACE USERS
Damir, doesn’t this just prove that you aren’t experiencing a bug? It doesn’t prove that he didn’t experience one.
Damir, doesn't this just prove that you aren't experiencing a bug
I do not understand ... what bug? I insert 10 records with each new partition value and 10 partitions are created. He was mentioning Oracle bug ... and I say that this is far from true.
It doesn't prove that he didn't experience one.
Frankly I didn't say that he saw something wrong. Once again, I show that Oracle is functioning correctly in this area. Nothing more or less.
And if this is Toad "play", I have no Access db to recreate the problem. If I get one I'll do that no problem
Well, if he has data going into the wrong partition, then there is a bug somewhere, right?
You proved that it works correctly in your Oracle installation. Sometimes bugs exist only in a particular version or patch, or only on a particular platform, or only in certain circumstances.
John,
please forgive me but working with partitions for 5-6 years and never, never heard for that kind of bug. This will be not a bug but a failure in some bigger systems.
If you can reproduce it please tell me Oracle version and I’ll check and raise SR to see the respond from Oracle. This is far from game for such a case.
Brg
Damir
@MR,
could you please send me MS Access file with example data. Beside that, could you show details about Oracle database (version/patch set) and Oracle client version where Toad is installed.
Then I could look in something similar to see what is going on here.
mail is:
damir dot vadas monkey gmail.com
Brg
Damir