Missing microseconds with timestamp

[unrandom123]

In a oracle table I have a column with datatype "TIMESTAMP(6)" and when running ora2pg -t INSERT with these settings in ora2pg.conf

PG_VERSION      13
USER_GRANTS  0
EXPORT_SCHEMA   1
PG_NUMERIC_TYPE 0
DEFAULT_NUMERIC  numeric
SCHEMA xxx1
LOOK_FORWARD_FUNCTION  xxx2
KEEP_PKEY_NAMES 1
DROP_FKEY 1
DISABLE_TRIGGERS USER
NO_LOB_LOCATOR  0
LOB_CHUNK_SIZE  512000
BLOB_LIMIT     10
LONGREADLEN     10475520
FORCE_OWNER 1
DROP_INDEXES 1
EXPORT_INVALID 1
PLSQL_PGSQL 1
NULL_EQUAL_EMPTY 0
EMPTY_LOB_NULL 1
PACKAGE_AS_SCHEMA
DEBUG 1

in the resulting sql file I don't see the microseconds for that column. The postgresql table was given the type timestamp and not timestamp(6). I guess timestamp is fine, since it supports microseconds.

[darold]

Any Oracle column with data type DATE or TIMESTAMP will be exported as timestamp data type in PostgreSQL. As you said timestamp(6) is the default for PostgreSQL so normally there is no problem. Here an example:

In oracle:

SQL> CREATE TABLE TEST_DATE (ID NUMBER, START_DATE DATE, START_TIME TIMESTAMP(6));
Table created.
SQL> INSERT INTO TEST_DATE VALUES (1, SYSDATE, SYSTIMESTAMP);
1 row created.
SQL> INSERT INTO TEST_DATE VALUES (2, '01-JAN-01', SYSTIMESTAMP);
1 row created.
SQL> COMMIT;

Export by Ora2Pg:

CREATE TABLE test_date (
        id bigint,
        start_date timestamp,
        start_time timestamp
) ;

and the data exported:

COPY test_date (id,start_date,start_time) FROM STDIN;
1       2020-10-15 19:06:56     2020-10-15 19:06:56.542684
2       2001-01-01 00:00:00     2020-10-15 19:08:00.189854
\.

After data import:

gilles=# SELECT * FROM test_date;
 id |     start_date      |         start_time         
----+---------------------+----------------------------
  1 | 2020-10-15 19:06:56 | 2020-10-15 19:06:56.542684
  2 | 2001-01-01 00:00:00 | 2020-10-15 19:08:00.189854
(2 rows)

So I don't think that there is any regression here but you should check that Ora2Pg configuration directive ENABLE_MICROSECOND is enabled (this is the default). When this directive is enabled Ora2Pg sends to Oracle:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF6;

otherwise it sends:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF;

so without microseconds.

[unrandom123]

In the original config (that lead to this issue) I did not have the ENABLE_MICROSECOND directive, so it should have been enabled by default. But apparently wasn't since it lost the microseconds.

I was going to test this by adding

ENABLE_MICROSECOND 1

but before I did that I checked out the latest git code and used that, but I got an Oracle crash.

Exception signal: 11 (SIGSEGV), code: 2 (Invalid permissions for mapped object), addr: 0x2b92b8fff000, PC: [0x2b92b6f8fda8, _intel_fast_memcpy.A()+10]
*** 2020-10-16 08:17:49.248
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] [Invalid permissions for mapped object] [0x2B92B8FFF000] [] []
Current SQL statement for this session:
SELECT
        A.TABLE_NAME,
        A.PARTITION_POSITION,
        A.PARTITION_NAME,
        A.HIGH_VALUE,
        A.TABLESPACE_NAME,
        B.PARTITIONING_TYPE,
        C.NAME,
        C.COLUMN_NAME,
        C.COLUMN_POSITION,
        A.TABLE_OWNER
FROM DBA_TAB_PARTITIONS A, DBA_PART_TABLES B, DBA_PART_KEY_COLUMNS C
WHERE   < a long list with NOT REGEXP_LIKE > 

I think this is line 12122 or there about in Ora2Pg.pm

[darold]

Well if Oracle crash because of this exception I'm afraid I can't do anything. The query is a basic SELECT but probably we hit a limit not well handled in Oracle. I found this link about this problem http://www.dba-oracle.com/t_ora_07445_exception_encountered.htm

I don't think this is an Ora2Pg issue. Let us know your fix.

[unrandom123]

I'll see if I can fix it and will let you know.

[unrandom123]

Still having the Oracle error, but for your information, I changed the ora2pg.conf and removed the LOOK_FORWARD_FUNCTION and SCHEMA lines, and now it is exporting everything (currently halfway through). This makes me think the problem is with the SQL above, what does it acctually do? In my particular case it has a long list of

AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p105)

(it has over 100 condition like that). Is there a better way to do this?

[darold]

When you don't export a particular schema Ora2Pg has to exclude several objects that must not be exported as they are internal Oracle objects or part of some Oracle products. If you have set the EXCLUDE configuration directive the list of excluded object appears in these NOT REGEXP_LIKE clauses too. Do you have tables specified in ALLOW and EXCLUDE directive?

[unrandom123]

In my current config file I have not set any value for ALLOW or EXCLUDE. But I looked at the names of the tables and now I have successfully run with -t INSERT and a list of tables in the allow section. Thank you!

I'll close this since I now have a workaround

[unrandom123]

It works now with the

ENABLE_MICROSECOND 1

line in the config. Strange that it seems not to be used/enabled by default (bug?)

[darold]

Looks like ENABLE_MICROSECOND was enabled by default a long time ago:

commit ff0e5bc9f52aa8ecc967a5a573ee265b1abede40
Author: Darold Gilles <gilles@darold.net>
Date:   Fri Jun 29 18:42:17 2012 +0200

    Change ENABLE_MICROSECOND to be enabled by default in default ora2pg.conf

unless you were using a version < 18.2 that fix a regression in applying the Oracle setting. But if it was disabled in the configuration file I guess this is a human change.

[unrandom123]

Strange. I was using version 20 or 21. And didn’t have the microsecond directive at all in the config file.

22 okt. 2020 kl. 10:06 skrev Gilles Darold notifications@github.com:

 Looks like ENABLE_MICROSECOND was enabled by default a long time ago:

commit ff0e5bc9f52aa8ecc967a5a573ee265b1abede40 Author: Darold Gilles gilles@darold.net Date: Fri Jun 29 18:42:17 2012 +0200

Change ENABLE_MICROSECOND to be enabled by default in default ora2pg.conf

unless you were using a version < 18.2 that fix a regression in applying the Oracle setting. But if it was disabled in the configuration file I guess this is a human change.

— You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub, or unsubscribe.