SQL Macro is finally here! Mustafa, 2020-10-282020-10-29 Hi, I was waiting for this for a long time. Finally we can use sql macro in Oracle database. Actually I was excited when I first read about this but after my tests, I stuck at some points. I was hoping to use this as “parameterized views” as in, send… Continue Reading
Direct Path Insert &/vs Conventional Insert Mustafa, 2020-10-27 Hi there, I couldn’t write anything for a while. Those days were busy. So I would talk to about direct path insert and conventional insert. Many people think that they are using “direct path insert” when used sqlldr utility but they are not. it is easy to confused batch insert… Continue Reading
Flashback Data Archive Problems! Mustafa, 2020-09-102020-09-10 Hi, if you don’t know about flashback data archive you can check this post first: https://mustafakalayci.me/2019/03/02/goodbye-log-triggers-welcome-flashback-data-archive/ I mentioned about flashback data archive (FDA) in that post and really flattered it. Well, maybe I shouldn’t! FDA is really fast dml tracking method but thanks to Peter Schlaeger, I noticed some potential… Continue Reading
Oracle Table Compression Part1 Mustafa, 2020-09-10 Hi, I have been dealing with table compression a lot during my development period and I still do. I wanted to share some information about table information but I believe this will be a long explanation so I will write them in parts and this is part1. Table compression is… Continue Reading
Timestamp Arithmetic and Well Known Mistakes Mustafa, 2020-07-28 Hi, Lately, I started to see many developer uses timestamp types (like timestamp or timestamp with time zone) wrong especially in time arithmetic. So as a short reminder, adding and subtracting numbers to/from a DATE value will add/subtract number of days: Oracle PL/SQL Select Sysdate "TODAY", Sysdate + 1 "TOMORROW", --add 1 day Sysdate - 1 "YESTERDAY", --subtract 1 day Sysdate + (1/24) "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour Sysdate - (1/24) "ONE HOUR BEFORE", -- remove 1 hour Sysdate + (1/(24*60)) "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently Sysdate + (1/(24*60*60)) "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently From Dual; TODAY TOMORROW YESTERDAY ONE HOUR LATER ONE HOUR BEFORE ONE MINUTE LATER ONE SECOND LATER ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- 2020-07-28 14:07:38 2020-07-29 14:07:38 2020-07-27 14:07:38 2020-07-28 15:07:38 2020-07-28 13:07:38 2020-07-28 14:08:38 2020-07-28 14:07:39 123456789101112 Select Sysdate "TODAY", Sysdate + 1 "TOMORROW", --add 1 day Sysdate - 1 "YESTERDAY", --subtract 1 day Sysdate + (1/24) "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour Sysdate - (1/24) "ONE HOUR BEFORE", -- remove 1 hour Sysdate + (1/(24*60)) "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently Sysdate + (1/(24*60*60)) "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequentlyFrom Dual; TODAY TOMORROW YESTERDAY ONE HOUR LATER ONE HOUR BEFORE ONE MINUTE LATER ONE SECOND LATER------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------2020-07-28 14:07:38 2020-07-29 14:07:38 2020-07-27 14:07:38 2020-07-28 15:07:38 2020-07-28 13:07:38 2020-07-28 14:08:38 2020-07-28 14:07:39 Basically that is all if you use add… Continue Reading
Frequent and Fast Refresh Materialized Views has a Big Problem! Mustafa, 2020-07-252020-07-25 Hi, While I was checking V$SQL_PLAN view for a whole different reason, I realized that some of the cursors has incredibly high child cursors. I started to dig and finally I find the problem. My customer have a few frequently fast refreshed materialized views. One of them is refreshed every… Continue Reading
CSV Data in Clob to Columns Using Select Mustafa, 2020-07-142020-07-14 Hi, Thanks to one of my customers, I am dealing with csv data in clob lately a lot. so they asked me if I can get a csv data in a clob column as separated columns using select statement. They wanted to show this data on their applicaitons. I suggested… Continue Reading
Is It Possible to Change SQL Statement in the Database? Mustafa, 2020-07-042020-07-04 YES! Hi, today I want to write about something which has been asked for many times to me. Let’s say you have an application and it is running very poorly designed sql statements in your database and you are really bored having fights with development teams to ask a change… Continue Reading
V$SqlStats vs V$SqlStats_Plan_Hash, different columns? Mustafa, 2020-07-012020-07-01 Hi there, V$SQLSTATSis a beautiful view for information about sql statements. you might say v$sql has that information too which is correct but V$SQLSTATSis much more faster than v$sql and keeps data about sql statements for a longer time than v$sql. https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLSTATS.html However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster,… Continue Reading