Category: 12c

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…

Redo Log Switch Order

Mustafa, 2020-09-032020-09-04

Hi, I always have the impression that redo log switch occurs in group number order but a small test showed me, I was wrong. As you know redo log is one of the most important part of a Oracle database. it almost protect everything! your data in memory, data file,…

Control File Content and the Limits

Mustafa, 2020-08-11

Hi, I would like to talk about Control File (CF). One of the most important component of the Oracle Database. It stores many critical data and limits for the database. Last SCN (System Change Number). Last Checkpoint Information. Data file locations. Redo log file locations. RMAN Repository (where the backup…

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:

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

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

Basically that is all if you use add…

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…

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…

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…

Performance Gain of NOCOPY Parameters

Mustafa, 2020-06-262022-08-31

Hi, Almost every Oracle database uses PLSQL codes like packages/procedures/functions etc. Complex systems with complex codes pass many arguments (parameters) many times between plsql objects. Passing parameters might be the most executed operations in a database. So how well do you pass your parameters? I assume you know about types…

Oracle Join Methods

Mustafa, 2020-06-262020-06-26

Hi, Actually I won’t explain anything today. I will just share a video by Chris Saxon who is also from askTom team. Great guy, you should also follow him. So he made a great, so simple and explanatory video that I have ever seen. Many thanks to Chris. Please watch.