Hi, Today, I needed to list all blocking sessions hierarchically. As you know, all information about sessions (and blocking sessions) are in gv$session (or just v$session if you are using single instance) view. I wanted to see who is blocking who and blocker at the top. you can write simple connect by (or with clause)
18c
Subquery Caching
Hi, I want to talk about very sweet performance “trick”. I love when I do something small and gain a lot 🙂 Today, I will demonstrate subquery caching in Oracle and try to explain how it works and it’s limitations. Probably, you are using some plsql functions in your select (or dml) statements. There is
Why should you use PLSQL_OPTIMIZE_LEVEL as 3
Hi, As you know PLSQL_OPTIMIZE_LEVEL parameter is used to optimize the codes while compiling them and it is really important. By default it’s value is 2 but you can increase it to 3 and you should, here is why. before begin, this is an extension for this post: http://mustafakalayci.me/2020/06/26/performance-gain-of-nocopy-parameters/ Very much thanks to Stephan Borsodi who
Enable Unified Audit on Linux and Windows
Hi, As you know, since 12c Oracle introduce Unified Auditing which has much more capabilities than standard auditing. Creating policies, audit conditions, top level auditing etc. By default Oracle uses “mixed” mode which allows you to run unified auditing commands and standard auditing. you can check if you are using unified auditing by default:
Create a Clone DB from Backup on the Same Server (or remote)
Hi, I wanted to create a post series about cloning/duplicating database on the same or remote servers. There are so many options and combinations about this like rman duplicate database with/without target/source db connection, rman restore using backups, ASM to OS or OS to ASM, same server or remote server, OMF or regular file naming
ORA-24244: invalid host or port for access control list (ACL) assignment
Hello everyone, So you wanted to migrate your ACLs to another database or you are trying to add a new acl entity but you got ORA-24244. There are many reasons for that error unfortunately. I wish Oracle provides us more detail about what is the error but I wanted to share what I have seen
export import ACL & ORA-24244 error during import
Hello everyone, it’s been a while since my last post. So, I wanted to write about export and import ACL privileges. They always become a pain (at least for me). after 12c, Oracle introduce us a new and easy way of ACL copying from db to db. export&import. so here is the basic command on
Direct Path Insert &/vs Conventional Insert
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 and direct path insert. of
Flashback Data Archive Problems!
Hi, if you don’t know about flashback data archive you can check this post first: http://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 problems and “some” solutions. Few
Oracle Table Compression Part1
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 a very useful feature of