Data imported from Access database table goes into wrong partition

Toad for Oracle & DevOps Toolkit
1

Hello,

I have the following Oracle 11.2 table, partitioned by range. Every time I insert a new record, with a new IMPORT_ID, it goes to an existing or new partition that is automatically created (Oracle 11.2g feature).

create table
customers(
install_date DATE,
customer_name VARCHAR2(100),
import_id NUMBER
)
PARTITION BY RANGE (IMPORT_ID)
INTERVAL(1)
(
PARTITION p2 VALUES LESS THAN (2),
PARTITION p3 VALUES LESS THAN (3)
);

However, if I import from an Access database table having the same structure, using TOAD import wizard, all records go into the same partition (the second one) regardless IMPORT_ID range!

I was expecting a new partition to be created for every new IMPORT_ID is inserted into the Oracle table but this is not happening.

Please advise.

(Using latest TOAD for Oracle 12.1)

Regards,

M.R.

2

Placing data in some partition has perfectly nothing with toad.-period.
Seems that data are misunderstood.

You have number based partition interval, what is not unusual but if you define as PLS_INTEGER or INTEGER based partitions, where all will be clear at ones.
So please look for original values in Access database and then how they are represnted in Oracle (Toad). For proper handling excute:

alter session set NLS_NUMERIC_FORMAT=’,.’ ;
or opposite
alter session set NLS_NUMERIC_FORMAT=’.,’ ;

in one of the cases your import will succeed.

3

Sorry, it is not a NUMBER, it is an INTEGER.

I don’t have this field in the Access table, I set it in TOAD import wizard as an “expression”, i.e. [1], [2], [3], etc…

4

I set it in TOAD import wizard as an "expression", i.e. [1], [2], [3], etc.....

Now we are closer to the problem ... could you please specif more this-here is the problem or Toad bug. In both ways more details (Toad version for instance) i s needed

5

It looks to me like a TOAD bug. (TOAD version is 12.1, latest patch. Oracle version: 11.2.0.2)

I am posting again the revised table structure so that TOAD support can investigate

ORACLE table:

create table

customers (

install_date DATE,

customer_name VARCHAR2(100),

import_id INTEGER

)

PARTITION BY RANGE (IMPORT_ID)

INTERVAL(1)

(

PARTITION p2 VALUES LESS THAN (2)

);

MS Access table:

Customers

column1: install_date DATETIME

column2: customer_name STRING(100)

Steps to reproduce the issue:

  1. Right click on CUSTOMERS table in TOAD and select “Import Data”

  2. Choose Import from an Access database and provide the path to the Access database

  3. Select CUSTOMERS table in Access as a source.

  4. The wizard will automatically map the columns as they have the same name in both tables but will leave IMPORT_ID field blank.

  5. Click on drop-down beside IMPORT_ID field, choose and enter an integer value, i.e. 1

Repeat these steps several times and each time enter a different value for IMPORT_ID, i.e. 1, 2, 3, 4, 5…etc

You will notice that all records go into the same partition, even though you would expect a new one to be created for each distinct value of IMPORT_ID.

Thank you,

M.R.

6

I just tried this using 12.1.1.1 and 12.5 (beta) and it appears the values went into separate partitions correctly. Is this what your expecting?

pic.jpeg

7

Yes, this is exactly what I expected!

It does not seem to work however in TOAD 12.1. Anyways it is good to know the bug is not present anymore in the next version.

Thank you,

M.R.

8

Hmm…12.1.1.1 is a patch and only certain items were changed and this wasn’t one of them, so I installed 12.1.0.22 just to verify if I got the same results, and sure enough I did. So, I’m wondering if we are doing something a little differently in the import data settings. The attached word doc (hopefully it comes thru ok) shows the steps I’m using - maybe you can compare them to yours to see if there’s any differences.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/10/8080.Data-Import.docx:550:0]

9

btw…I just tried this on an 11.2 database and got the same correct results. By chance are you looking at the Partitions tab right after running the import table data. If so, then just a reminder you have to refresh the RHS data after data has been updated. I ran into this when I went to confirm my last test, and thought possibly this is why you might not be seeing it as well, so wanted to mention it.

10

Yes, I know I have to refresh the Partitions panel. I also ran “analyze table” to get an idea how many rows were in the partition.

I had to manually split it after I imported several Access databases by running the following statement for each IMPORT_ID:

alter table customers

split partition sys_p517

at (13) into ( partition sys_p513, partition sys_p517 ); etc, etc…

So there is definitely a problem.

I looked at the document, I followed the approx. same steps. here are the differences:

  1. I imported from an Access 2003 database (customers.mdb). You imported from an Access 2007 or 2010 DB.

  2. I manually committed after each import. Can you please try to commit after each import using the “Commit” button in TOAD?

  3. Also, I truncated table data before starting the imports.

May I suggest another test?

Can you please truncate the table (the partitions will not be deleted though) and try again to import from Access?

(Note: I cannot remember if I deleted the empty partitions using TOAD prior to importing or after, maybe one of these two scenarios triggered the bug…).

Thank you,

M.R.

11

I truncated the table, but left the partitions there and everything still worked correctly. I even tried with straight insert statements and that also worked
fine. Have you tried dropping the partitions (except for P2, of course) before you do the load?

The only difference is I’m using Access 2007 - I wouldn’t think that would make a difference, but you never know. I can try to find a copy of 2003, but not
sure we have it anymore.

From: M.R. [mailto:bounce-mircea2013@toadworld.com]

Sent: Wednesday, March 19, 2014 11:48 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Data imported from Access database table goes into wrong partition

RE: Data imported from Access database table goes into wrong partition

Reply by M.R.

Yes, I know I have to refresh the Partitions panel. I also ran “analyze table” to get an idea how many rows were in the partition.

I had to manually split it after I imported several Access databases by running the following statement for each IMPORT_ID:

alter table customers

split partition sys_p517

at (13) into ( partition sys_p513, partition sys_p517 ); etc, etc…

So there is definitely a problem.

I looked at the document, I followed the approx. same steps. here are the differences:

  1. I imported from an Access 2003 database (customers.mdb)

  2. Also, I
    truncated table data before starting the imports.

May I suggest another test?

Can you please truncate the table (the partitions will not be deleted though) and try again to import from Access?

(Note: I cannot remember if I deleted the empty partitions using TOAD prior to importing or after, maybe one of these two scenarios triggered the bug…).

Thank you,

M.R.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

12

Yes, I may have dropped them before the import.

It is not necessary to find Access 2003.

I am on Access 2010 but save the databases in 2003 format (*.mdb).

Just go to File ->Save and Publish (on the left side) -> Save Database As (choose Access 2002-2003) format.

Regards,

M.R.

13

ah yes - forgot about that - thanks. I tried it and still works correctly for me. Hmmm…

From: M.R. [mailto:bounce-mircea2013@toadworld.com]

Sent: Wednesday, March 19, 2014 12:45 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Data imported from Access database table goes into wrong partition

RE: Data imported from Access database table goes into wrong partition

Reply by M.R.

Yes, I may have dropped them before the import.

It is not necessary to find Access 2003.

I am on Access 2010 but save the databases in 2003 format (*.mdb).

Just go to File ->Save and Publish (on the left side) -> Save Database As (choose Access 2002-2003) format.

Regards,

M.R.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

14

Weird. If I figure out other differences between out tests, I shall let you know.

Thank you for investigating this issue.

Regards,

M.R.

15

No problem - sorry, we couldn’t narrow it down. For reference tho, here is the table creation script I was using (straight from Script tab in Schema Browser), which is based on the script you sent above. If you come up with anything, please let me know. Thanks.

CREATE TABLE CUSTOMERS

(

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 (

INITIAL 8M

NEXT 1M

MAXSIZE UNLIMITED

MINEXTENTS 1

MAXEXTENTS UNLIMITED

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

)

NOCACHE

NOPARALLEL

MONITORING;

16

This is my table, you are right, it may be relevant to use the original one:

ALTER TABLE RBS_USER.CUSTOMERS

DROP PRIMARY KEY CASCADE;

DROP TABLE RBS_USER.CUSTOMERS CASCADE CONSTRAINTS;

CREATE TABLE RBS_USER.CUSTOMERS

(

SIGNING_ACCOUNT VARCHAR2(30 CHAR) DEFAULT ‘N/A’,

SUB_ACCOUNT VARCHAR2(30 CHAR) DEFAULT ‘N/A’ NOT NULL,

SOURCE_ID INTEGER DEFAULT 1,

COMPANY_NAME VARCHAR2(255 CHAR) DEFAULT ‘N/A’,

STATUS VARCHAR2(3 CHAR) DEFAULT ‘N/A’,

TYPE_CODE VARCHAR2(20 CHAR) DEFAULT ‘N/A’,

PRODUCT VARCHAR2(5 CHAR) DEFAULT ‘N/A’,

ACCESS_CODE VARCHAR2(5 CHAR) DEFAULT ‘N/A’,

CANCEL_CODE VARCHAR2(3 CHAR) DEFAULT ‘N/A’,

BILLING_POOL_ID VARCHAR2(3 CHAR) DEFAULT ‘N/A’,

AM_ID VARCHAR2(6 CHAR) DEFAULT ‘N/A’,

AC_ID VARCHAR2(6 CHAR) DEFAULT ‘N/A’,

INSTALL_DATE DATE,

CANCEL_DATE DATE,

STATUS_DATE DATE,

LANGUAGE_CODE VARCHAR2(3 CHAR) DEFAULT ‘N/A’,

CONTACT_NAME VARCHAR2(120 BYTE) DEFAULT ‘N/A’,

ADDRESS1 VARCHAR2(150 CHAR) DEFAULT ‘N/A’,

ADDRESS2 VARCHAR2(150 CHAR) DEFAULT ‘N/A’,

CITY VARCHAR2(100 CHAR) DEFAULT ‘N/A’,

PROVINCE VARCHAR2(3 CHAR) DEFAULT ‘N/A’,

POSTAL_CODE VARCHAR2(20 CHAR) DEFAULT ‘N/A’,

COUNTRY VARCHAR2(3 CHAR) DEFAULT ‘N/A’,

TELEPHONE_NO VARCHAR2(20 CHAR) DEFAULT ‘N/A’,

TELEPHONE_EXT VARCHAR2(20 CHAR) DEFAULT ‘N/A’,

FAX_NO VARCHAR2(15 CHAR) DEFAULT ‘N/A’,

FAX_EXT VARCHAR2(15 CHAR) DEFAULT ‘N/A’,

SIGNED_DATE DATE,

FLAG_SHELL_ACCOUNT VARCHAR2(3 CHAR) DEFAULT ‘N/A’,

HOUSE_ACCOUNT_FLAG VARCHAR2(3 CHAR) DEFAULT ‘N/A’,

COMPANY_CODE VARCHAR2(3 CHAR) DEFAULT ‘N/A’,

SERVICE_LEVEL_CODE VARCHAR2(3 CHAR) DEFAULT ‘N/A’,

CUSTOMER_GROUP VARCHAR2(8 CHAR) DEFAULT ‘N/A’,

CURRENCY_ID VARCHAR2(3 CHAR) DEFAULT ‘N/A’,

SIC_CODE VARCHAR2(5 CHAR) DEFAULT ‘N/A’,

REPORT_MM DATE,

REPORT_TYPE VARCHAR2(5 CHAR),

ADDRESS_TYPE VARCHAR2(3 CHAR),

NO_OF_EMPLOYEES INTEGER DEFAULT 0,

IMPORT_ID INTEGER DEFAULT -1 NOT NULL,

ID NUMBER(38),

REPORT_CYCLE VARCHAR2(3 CHAR) NOT NULL,

DISTRICT_MAPPING_ID VARCHAR2(8 CHAR) NOT NULL,

UNIQUE_CUSTOMER_MAPPING_ID VARCHAR2(8 CHAR) NOT NULL,

REFERENCE_SUB_ACCOUNT VARCHAR2(30 CHAR) DEFAULT ‘N/A’

)

COMPRESS BASIC

TABLESPACE RBS_DATA1

RESULT_CACHE (MODE DEFAULT)

PCTUSED 0

PCTFREE 0

INITRANS 1

MAXTRANS 255

STORAGE (

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

PARTITION BY RANGE (IMPORT_ID)

INTERVAL( 1)

(

PARTITION P_FIRST VALUES LESS THAN (2)

LOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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 (3)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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 (4)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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 (5)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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 (6)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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 (7)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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 (8)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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 (9)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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 (10)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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 (11)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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 (92)

NOLOGGING

COMPRESS BASIC

TABLESPACE RBS_DATA1

PCTFREE 0

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

)

)

NOCACHE

PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )

MONITORING

ENABLE ROW MOVEMENT;

CREATE INDEX RBS_USER.CUSTOMERS_IX4 ON RBS_USER.CUSTOMERS

(AM_ID, IMPORT_ID)

NOLOGGING

TABLESPACE RBS_DATA1

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MAXSIZE UNLIMITED

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOPARALLEL;

CREATE INDEX RBS_USER.CUSTOMERS_IX5 ON RBS_USER.CUSTOMERS

(SOURCE_ID, IMPORT_ID)

NOLOGGING

TABLESPACE RBS_DATA1

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MAXSIZE UNLIMITED

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOPARALLEL;

CREATE INDEX RBS_USER.CUSTOMERS_IX6 ON RBS_USER.CUSTOMERS

(TYPE_CODE, IMPORT_ID)

NOLOGGING

TABLESPACE RBS_DATA1

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MAXSIZE UNLIMITED

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOPARALLEL;

CREATE UNIQUE INDEX RBS_USER.CUSTOMERS_PK ON RBS_USER.CUSTOMERS

(ID)

NOLOGGING

TABLESPACE RBS_DATA1

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MAXSIZE UNLIMITED

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOPARALLEL;

CREATE INDEX RBS_USER.CUSTOMERS_IX2 ON RBS_USER.CUSTOMERS

(STATUS, IMPORT_ID)

NOLOGGING

TABLESPACE RBS_DATA1

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MAXSIZE UNLIMITED

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOPARALLEL;

CREATE UNIQUE INDEX RBS_USER.CUSTOMERS_IX1 ON RBS_USER.CUSTOMERS

(SUB_ACCOUNT, IMPORT_ID)

NOLOGGING

TABLESPACE RBS_DATA1

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MAXSIZE UNLIMITED

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOPARALLEL;

CREATE INDEX RBS_USER.CUSTOMERS_IX3 ON RBS_USER.CUSTOMERS

(AC_ID, IMPORT_ID)

NOLOGGING

TABLESPACE RBS_DATA1

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MAXSIZE UNLIMITED

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOPARALLEL;

CREATE OR REPLACE TRIGGER RBS_USER.CUSTOMERS_TRG

BEFORE INSERT

ON RBS_USER.CUSTOMERS

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

BEGIN

– For Toad: Highlight column ID

:new.ID := CUSTOMERS_SEQ.nextval;

END CUSTOMERS_TRG;

/

BEGIN

SYS.DBMS_RLS.DROP_POLICY (

object_schema => ‘RBS_USER’

,object_name => ‘CUSTOMERS’

,policy_name => ‘USER_POLICY’);

END;

/

BEGIN

SYS.DBMS_RLS.ADD_POLICY (

object_schema => ‘RBS_USER’

,object_name => ‘CUSTOMERS’

,policy_name => ‘USER_POLICY’

,function_schema => ‘RBS_USER’

,policy_function => ‘RBS_SECURITY.CHECK_DATE’

,statement_types => ‘UPDATE,DELETE’

,policy_type => dbms_rls.dynamic

,long_predicate => FALSE

,update_check => FALSE

,static_policy => FALSE

,enable => TRUE );

END;

/

ALTER TABLE RBS_USER.CUSTOMERS ADD (

CONSTRAINT CUSTOMERS_PK

PRIMARY KEY

(ID)

USING INDEX RBS_USER.CUSTOMERS_PK

ENABLE VALIDATE);

17

I just tried this using the table that you most recently added to the thread (with triggers, policy, and everything), imported some data from access mdb format, using [4] as an expression for ‘import id’ and for me, the data went into the expected partition. If you turn on Spool SQL in Toad, you will see that we are just running insert statements with bind variables.

To find which partition the rows went into, I was going into the filter/sort dialog and choosing the partition that I expected the rows to be in, then clicking OK, and looking in the SB RHS Data tab to verify. The “High value” as shown above in some screen shots is related more to the table DDL rather than the data it contains.

18

Just an idea: did you close the wizard between imports?

I know you can leave it open and keep importing but I close it and re-opened it every time I did a new import.

Maybe this will trigger the issue?

19

I wasn’t doing that before but I just now tried it and the data still went into the correct partition.

Are you 100% certain that rows are going into the wrong partition? You can verify with this query (You may need to change user_objects to dba_objects or all_objects if the table is not in your login schema)

select CUSTOMERS.IMPORT_ID, uo.subobject_name partition_name

from customers, user_objects uo

where dbms_rowid.rowid_object(customers.rowid) = uo.object_id

20

I did 12 imports and everything went into one single partition. I had to split it 12 times manually using

ALTER TABLE Customers SPLIT PARTITION etc,