Differences

This shows you the differences between two versions of the page.

--- bugs_oracle [2019/04/09 12:14] – andonovj
+++ bugs_oracle [2021/04/27 11:32] (current) – andonovj
@@ Line 1: / Line 1: @@
-  * **Wrong function extract**
+======Overview======
+Oracle is full of bugs, I am sorry to say it, but like any software, even Oracle has A LOT of bugs. Here are few of them:
+  - [[oracle_bugs_wrong_hour_extract| Incorrect hour extract]]
+  - [[oracle_bugs_statistic_advisor| Statistic Advisor]]
+  - [[oracle_linux7.2_crash|Crash due to Linux 7.2 IPC Setting]]
+  - [[oracle_12c_expdp_hang_enqueue_blocker|Expdp hangs/slow on Enqueue blocked on low memory]]
-Very interesting "Feature" which I was shown in oracle is the function "extract hour from timestamp", it appears that Oracle will provide an hour which is wrong:
-<sxh bash>
-select (extract (hour from current_timestamp)),current_timestamp from dual;
-(EXTRACT(HOURFROMCURRENT_TIMESTAMP)) CURRENT_TIMESTAMP
------------------------------------- -------------------
-                                    2017-11-14 10:44:00
-</sxh>
-**Solution**
-This behavior is somehow normal however, since the function: extract (hour from current_timestamp) is supposed to return you the hour in UTC format, excluding the timezone, therefore if the hour is 18:00 and the timezone is +02:00 it will return you 16:00, instead of 18:00. There are 2 ways how to negate this:
-   * **Use the current_scn**:
-<sxh bash>
-SQL> select (extract (hour from (scn_to_timestamp(current_scn)))) from v$database;
-(EXTRACT(HOURFROM(SCN_TO_TIMESTAMP(CURRENT_SCN))))
---------------------------------------------------
-SQL>
-</sxh>
-  * **Use the sum of the UPC hour and the time zone**:
-<sxh bash>  * Unordered List Item
-SQL> Select (extract (HOUR from current_timestamp)), current_timestamp from dual; -------- We will extract the UTC value for hour
-SQL> Select (extract (HOUR from current_timestamp)) UTC_HOUR, (extract (TIMEZONE_HOUR from current_timestamp)) SESSION_TZ, (extract (HOUR from current_timestamp))+(extract (TIMEZONE_HOUR from current_timestamp)) SESSION_HOUR ,current_timestamp from dual;
-  UTC_HOUR SESSION_TZ SESSION_HOUR CURRENT_TIMESTAMP
----------- ---------- ------------ ---------------------------------------------------------------------------
-          1           17 16-NOV-17 17.26.55.236687 +01:00
-</sxh>
-  * **Object exists but cannot be replaced**
-There is a strange phenomena on Oracle, when you try to re-create a procedure which points to a synonym.
-For example, if you have a schema, which has a synonym pointing to a procedure of other schema and you try to create a new procedure under this user with the same name as the existing procedure you get:
-<sxh bash>
-EURAGIN > @PK_SINGLE_WALLET.sql
-:53:17 415  /
-CREATE OR REPLACE PACKAGE UAGVDEIO.PK_SINGLE_WALLET AS
-*
-ERROR at line 1:
-ORA-00955: name is already used by an existing object
-</sxh>
-Even though I specify the "OR REPLACE" clause, Oracle still fail. That was very mind itching problem. So let's see who has what:
-<sxh bash>
-OWNER                          STATUS  CREATED             OBJECT_TYPE
------------------------------- ------- ------------------- -------------------
-UAGPLZ2017                     VALID   2018-05-10 11:19:47 SYNONYM
-UAGUWB2017                     VALID   2018-05-10 11:20:00 SYNONYM
-UAGSWL2017                     VALID   2018-05-10 11:20:43 SYNONYM
-UAGNP17UCWB                    VALID   2018-05-10 11:21:35 SYNONYM
-UAGINDBM                       VALID   2018-05-10 11:24:32 PACKAGE BODY
-UAGINDBM                       VALID   2018-05-10 11:22:25 PACKAGE       <- 3rd Schema
-UAGVDEIO                       VALID   2018-05-10 11:24:36 SYNONYM
-AGVDEIO                        VALID   2019-03-14 13:42:08 SYNONYM
-</sxh>
-So the ACTUAL package is owned by 3RD SCHEMA :) and the others are synonyms.
-So we have to just add the it to the correct schema:
-<sxh bash>
-:07:59 SYS @ EURAGIN > @PK_SINGLE_WALLET.sql
-:08:05 415  /
-Package created.
-Elapsed: 00:00:00.56
-:08:06 SYS @ EURAGIN > @PK_SINGLE_WALLET_body.sql
-:08:10 1012  /
-Package body created.
-Elapsed: 00:00:00.40
-:08:11 SYS @ EURAGIN >
-</sxh>