Commit Time – Part 2 (On Commit Trigger) Mustafa, 2019-12-192019-12-19 Hello again, Not long ago, I mentioned about how to get approximate commit time on a table: https://mustafakalayci.me/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? Mustafa, 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! Mustafa, 2019-07-312020-07-05 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 Mustafa, 2019-07-232020-07-05 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 Mustafa, 2019-07-052020-01-09 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
Using Temp instead of Undo for GTTs Mustafa, 2019-06-262019-07-03 Hello everyone, with 12c we have a very nice new option; Temp Undo for GTTs (Global Temporary Tables). as you know temporary tables are used for storing temporary data. those tables data are stored in TEMP tablespace so they are not vital. Temporary tables can generates very little redo data… Continue Reading
Commit Time for an Inserted Row in Oracle Mustafa, 2019-05-25 Hello everyone, This week, one of my customer have experienced a problem. They provide some Web Service to their suppliers and one of those web services creates some rows and then returns the response as successful operation but just after that, supplier queries the newly created those rows but they… Continue Reading
How to Get Your SID? USERENV vs V$MYSTAT! Mustafa, 2019-05-232020-07-05 Hello everyone, This is an easy question. How to find your own session SID and when you ask Google for it, you will find many answers. While reading some documents, I realized that some Gurus are using V$MYSTAT to get current SID: Oracle PL/SQL select sid from v$mystat where rownum=1; 1 select sid from v$mystat where rownum=1; I always prefer USERENV(‘SID’) context to get… Continue Reading
Goodbye Log Triggers Welcome Flashback Data Archive Mustafa, 2019-03-022020-10-25 Hello, I would like to talk about Flashback Data Archive (or Flashback Archive – FBA) in 12c. FBA was introduced in 11g. It is not new but it has very important new features that allow us to use FBA very efficiently and for free. I would like to talk about… Continue Reading