Min/Max on same column mustafakalayci, 2020-05-22 Hi, Let’s do a simple trick. You want to get min and max value of a column which is already indexed. As you know if you have an btree index on a column and try to get a Min or Max value then you will see an “Index Full Scan(Min/Max)”… Continue Reading
How to convert a LONG column to CLOB directly in a query mustafakalayci, 2020-04-06 Hi, This is a big problem since LONG is a very painful data type. You cannot search in it, use it in CTAS (create table as), pass it to a plsql object as parameter etc. Oracle strongly suggest not to use a LONG column anymore but unfortunately there are many… Continue Reading
Using “User” function in a scheduled Job mustafakalayci, 2020-03-17 Hi there, I wanted to talk about a problem that I faced recently. Also I thought about the subject because this post is also related with “Session user vs Schema”. Whenever I use the term of “Job” I will be talking about Dbms_Scheduler Jobs not Dbms_Job! When you created a… Continue Reading
Sequential ID column without gap! mustafakalayci, 2020-02-06 Hello, A customer is asking to you generate order numbers one by one and without gap! What an unpleasant request isn’t it? if you are not familiar to databases then you might think that this request is logical but it is not. So your customer asked for this and you… Continue Reading
How to store Yes No questions on table and BITAND function mustafakalayci, 2020-01-20 Hi everyone, you might see BITAND function is frequently used by Oracle on source codes. Did you wonder why? What does BITAND function do? as you can understand from the name, it does a bitwise AND operation on parameters. probably you know what a bitwise and but as a small… Continue Reading
Commit Time – Part 2 (On Commit Trigger) mustafakalayci, 2019-12-19 Hello again, Not long ago, I mentioned about how to get approximate commit time on a table: http://10.157.89.12/2019/05/25/commit-time-for-an-inserted-row-in-oracle/ While I was reading some blogs and jumping from one to another, I came to Saibabu Devabhaktuni’n blog post: http://sai-oracle.blogspot.com/2013/09/how-to-setup-oracle-on-commit-trigger.html and I was literally shocked because I never heard something about this…. Continue Reading
Can not Drop Materialized View on 12c and above? mustafakalayci, 2019-12-06 Hello Everyone, I couldn’t write for a long time because of a crazy workload. During this heavy hardworking, I faced with a problem on one of my customers. They have a on commit refresh Materialized View (MV) and started to complain about they couldn’t insert into main table of MV…. Continue Reading
CLOB size matters! mustafakalayci, 2019-07-31 Hi, Many developer and DBA has an idea about the storing clobs. Basic rule is “if length of clob is higher tan 4000 then it will be stored in lob segment not in table segment” which is correct but sometimes misinterpreted! if you have a clob column in your table,… Continue Reading
ORA-00942: table or view does not exist and sequences as default value mustafakalayci, 2019-07-23 Hello, after 12c or above we are able to set sequence’s nextval as the default value of an ID column. One of my customer started to complain about the ORA 00942 (table or view does not exist) error even if they have all necessary privileges on the table. After a… Continue Reading
Insert multi line text data using sqlldr mustafakalayci, 2019-07-05 Hello Everyone, I just answered an OTN question about inserting multi line data using sqlldr and simply wanted to share it here (https://community.oracle.com/thread/4278952) so if have a data like this in your text file: Oracle PL/SQL 1,"this is two line data", 10 2,"this is three line data", 11 3,"this is one line data", 12 4,this, 13 1234567 1,"this istwo line data", 102,"this isthree linedata", 113,"this is one line data", 124,this, 13 you need to do some extra work to accomplish this problem. In my example I… Continue Reading