CONTAINER_DATA for Common Users

Hello Everyone (if anyone reading this 🙂 ),

it has been a long year and I am glad it is over. As always, we hope the for “a time of period” like new year, birthday etc etc but hope is the only thing that we can hold on. I hope whole world get rid of this illness called corona, racism, wars (and again etc etc, list goes on)… Wish everyone a healthy, peaceful year.

In my personal blog, I mentioned vary of things and I realized that I didn’t mention about container database structure at all. Main reason for this is I was lazy on multi-tenant architecture and didn’t work on it, yeah, pretty much this was the reason 🙂

I wanted to start with common user’s container_data attribute (or property I am not sure). As you know, in multi-tenant architecture there two types of users COMMON and LOCAL. Common users are defined in the CDB (not in a pluggable database) and they are known by every current and future pdb. Local users are defined in a pluggable database and can access to only pluggable database they are defined. By saying “defined”, I mean “created”. So, local users don’t aware of other pluggable databases or container database (cdb) but common users are aware that they are in a CDB and can connect to PDBs on that CDB if they have privileges.

By default, newly created common users doesn’t see whole data in container data views or tables. what is “container data views”? As you noticed (even if you use non-cdb database 12c and above) there are some new columns on data dictionary and v$ views like CON_ID. in a multi-tenant database when a privileged user like SYS queried V$SESSION, user can see whole sessions in all containers (cdb$root and pdbs). Let’s see:

I will be running those queries on a 21.3 version. I have 3 PDBs on my CDB:

these are just dummy PDBs that I use for testing purpose on VM. Now Lets connect as SYS and query some views:

I connected as SYS and I am in the cdb$root container. I queried V$SESSION and V$DATAFILE views by grouping con_id column. I am able to see sessions on different PDBs, in my example, con_id 3 and 5 has 1 session each. V$DATAFILE query shows count of all data files per PDBs.

Now, let’s create a common user and give necessary privileges and query with that user:

I created C##MY_ADMIN user and grant DBA for all containers. So, this user can connect to any pdb and query any data dictionary view. for example, c##my_admin can connect to pdb1, pdb2 etc and query v$session in all of them and is able to see whole session for current pdb that user in it but problem is when c##my_admin connected to root (cdb$root), he/she won’t be able to see whole sessions in CDB (including PDB sessions). just the ones in cdb$root:

as you see, only sessions in con_id 1 which is cdb$root are listed. By the way, con_id 0 is CDB itself and only background processes are in there and they are listed in v$session all the time.

again, v$datafile lists only files in current container which is cdb$root. Please consider that c##my_admin is a DBA in CDB so it can connect to any PDB and query their data. it has necessary privileges for that. It has just don’t have “privilege” to query PDBs’ data in the CDB$ROOT container.

That is what CONTAINER_DATA does. it allows a common user to query some or all pdbs data in cdb$root. you must add necessary pdbs to common user’s container_data property:

this alter must be done by a privileged user in cdb$root container. Here, I set container_data of c##my_admin user as PDB1, PDB2 (con_ids are 3 and 4) and CDB$ROOT.

there are few important keys here:

  1. this command must be run in cdb$root.
  2. CDB$ROOT must be in container_data list because it is the root and common user will query data in it so, it must be able to see it
  3. CONTAINER=CURRENT is mandatory because by default it is ALL and this command can be run only in cdb$root which is “current”

so, c##my_admin will be able to see whole sessions in v$session for cdb$root, pdb1 and pdb2 (not PROXY_PDB which its con_id is 5). Let’s see:

so, c##my_admin is able to query other pdbs data in cdb$root. There is no session in pdb2 by the way, that is why no session for con_id 4 in v$session.

instead of giving all container data view access you can specified necessary view as this:

by setting container_data to default it will reset everything. now, I just add container_data info just for V_$session view (which is based view for v$session, you can not grant on v$session) and query v$session and datafile again with c##my_admin:

here it is. we can see session rows for other pdbs in v$session but not for v$datafile.

if you want to set all pdbs at once:

you can use ALL clause for pdb list. remember when you use SET as “ALTER USER … SET CONTAINER_DATA” it will overwrite previous container_data property. if you want to add a new PDB into container_data list then you can use ADD instead of SET:

You can query who has access to what using CDB_CONTAINER_DATA data dictionary view.

I hope this will helpful, any comments is appreciated.

Thanks for reading, wish you healthy days.

How to Demonize Apex V Function!


Answer of the question in the subject is “Using too much V in SQL statements”. Let’s see why and what should you do.

First things first, I am NOT a APEX developer. I am a DBA and Database Developer but some of my clients are using APEX and I am dealing with many sql functions that uses V function in apex. Since the day 1, I did not like this V function because developers using it in very different ways and it returns VARCHAR2 but it uses for all kind of compares without any conversion functions (this is not on the V function of course).

So, what is the problem here? At my clients’ codes, V function is used heavily in sql statements. Some queries that I dealt with lead me to investigate V function because even if the query has no performance issue, still, it takes too much to complete. When I checked the V function, I find out that it is not DETERMINISTIC (which shouldn’t be because of the logic of DETERMINISTIC but I will talk about this later). Let’s do some tests but before I must explain a few things. I hacked the code of V a little bit and add some logging codes to demonstrate how it is worked and performance issues about it but I won’t explain how I do it (probably many of you can do it easily) and share any source code of it. whenever a V function is called my logging code will write the parameter name of v function into “my_log” table.

I added my log codes, set an apex session in my sql developer session and run these selects:

I delete log table first then call a select statement with four V function in select list run over DUAL table and then check the log table, as you see it is called 4 times. So same data is read 4 times and 4 plsql function call is occurred. this caused too much context switch between SQL and PLSQL and this can reduce your performance dramatically.  Especially, Where clause is extremely dangerous, why? Because V function might run for all rows in the table which will cause a disaster.

as you can see,  this query called  V function 79.647 times why? because there are 79.645 rows in the TMP table. Since I use V in Where clause, condition ( object_id = v(‘OBJECT_ID’) ) executed for every rows in the table (79.645 rows) and only 1 of the rows meets the condition so select list runs 1 times and there are 2 more V function (same with the one in where clause) so totally V called 79.647 times. I hope you see the problem here. in your application code if you use V function in your sql statements, it might be called too much!

How about an index? yep that will help:

so it run only 3 times at this execution because this time search operation (where condition) is run on an index not table so V function is called 1 times in where clause and 2 times in select list. this means you should have indexes at least for your condition columns.

is that enough? Let me show you a pseudo code on a database:

so many same V function call in select statement. creating and index will help you about where clause how about select list? since all items in select list cause a function call (even with same parameters) let’s test performance:

calling this query for 10.000 times (or you can thing this as calling sql 1 times for 10.000 rows) takes 3.2 seconds (20 same V function). if we change the code to this:

this query takes only 0.5 seconds. timings might significant but think about 1000 different queries that run on your database 1000 times per day and calculate spend of time for just calling V function. it is a lot! your significant amount of db time might be wasted.

Let’s improve the code a little bit more:

even better. if you increase number of call, you will see that first 2 plsql code timings will increase linearly but the last code (code just above) will give you almost same performance. Remember, V is a function not a variable. Use variables instead of using V if you can.

Even if these might help your system, main problem is still there: V is not DETERMINISTIC. you might change your codes as I suggested above or create more indexes but many developers also use V in VIEWS which is another and for me a bigger disaster. it is harder to modify the code you cannot use bind variables etc… What would happen if V was deterministic? I modified V and made it deterministic and let’s see the result with first test case without index:

pay attention that there is no index but V is just called 2 times (1 for where clause and 1 for select list, event if select list has 2 copy). it was 79.647 without DETERMINISTIC if you remember the first result.

So making V as deterministic will solve almost every kind of problem but only problem is what I looking for here is not deterministic. Why? Let me explain what deterministic is first. if a function always returns same value with same parameters then this function is a deterministic function like TO_CHAR or SUBSTR. this allow oracle to know that if calls with same parameters are repeated, it can be cached and used first calculation result for on going calls. This way, if same function with same parameters are called in a SQL statement, that function is called only 1 times but this is not V. Result of V is not deterministic because application might change the value of a V item and this means V call with same parameters can return different results because it is just return a context variable value.

So, it shouldn’t be deterministic but it should be something that acting like deterministic for an sql statement. I don’t think any apex developer wants to see a change of V values during a select statement. I think they assume values of V won’t change during the sql statement. So, there should be something like DETERMINISTIC_FOR_CALL in Oracle database. we should be able to define plsql objects as “deterministic” while they are used in a sql statement. Their result should be calculated once and used during the sql call. I think this is also necessary for any plsql object that we can call on SQL. because current structure of plsql is kind of violating the consistency. A select statement returns rows as they were when the sql call started. even if the rows are changed during the sql call, result of the statement won’t be affected from those changes but PLSQL breaks that. when a function calculated for every row, a select statement inside the plsql object can return different results because it will be affected by on going changes. This is a database option. Database should provide us this functionality, Apex team has nothing to do about it.

By the way, I am really frustrated about data type conversions about V. since V returns VARCHAR2, many developer does not care about type conversions. if you compare a V value with a number then you should use “TO_NUMBER(V(…))” for example or at least (as I search for it) NV function (number result of V). this is another issue to me.

For now, I suggest to avoid using V in a SQL statement as much as possible and use bind variable if you can.  Let’s say you have some SQL with so many V call with same parameter, what can you do?  you can “force” oracle to cache them:

there are better way to write this sql but I just want to demonstrate a way. as you see this query takes 3.26 seconds to complete because of too many V calls. you can rewrite your query and cache the V results first and then use them as this:

I am adding a NO_MERGE hint to prevent optimizer to merge queries before query run. as you see this version returns in 0.19 seconds 15 times faster becuase V functions are called only 2 times (2 different V item is selected OBJECT_ID and USER_NAME).


Another and maybe better solution for this problem is using your own V function and create it as deterministic:

this is faster than previous join method. only thing that you should change V calls with MY_V.

I hope, this helps you on your systems. Now after those tests, I must connect with my clients and explain them to change their coding to gain significant db time.

wish you all healthy days.

Hierarchical Blocking Session List


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) query but it will list blockers at the bottom so I just developed it a little bit. if I remember correctly there were a small script under rdbms/admin but it was creating some tables etc etc. I just want to get it using a simple sql statements.

here is the sql:

first, I get all blockers’ sids (blockers data source in the query) then I use them to generate the hierarchy (blocker_hierarcy data source in the query) then I remove some blockers from the hierarchy because they are also blocked by some other sessions and will be listed under them.

for example, let’s say sid 10 is blocking 2 different sessions (sid 20 and 30) and also sid 30 is blocking some other session (sid 40). so this query will produce a result like this:

you can see the hierarch in a visual way and the first 40 character of their currently running sql statements.

Hope this will be useful for you.

wish you healthy days.


Subquery Caching


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 always a package that contains some functions calculates staff or return some constant strings like “base url” or “environment name” etc. some of those functions has parameters too and we call them from SQL. Calling user defined plsql function from sql statement is a performance problem because it will cause context switch a lot. Let’s say you have function return a constant string. To see the performance difference I will add a sleep code into function so it will be easier to see performance problem.

this is our function. you need to execute grant on DBMS_LOCK package to create this procedure. Now, we will call this function from an sql statements. let’s create a table and then call this function:

when I call sf_base_url function in the first select statement (2 rows) it takes 2 seconds and on the second select statement (with 4 rows) it takes 4 seconds. this proves that our function called as many times as the number of rows from the query. Did you see the problem here? this function always returns the same result but it called more than 1 times. Actually calling it just 1 time is enough. Think about it on your databases. you have business function takes 10 seconds to complete and it is called on an sql statements which returns 1000 rows. you will lose 9990 seconds (we must call it at least 1 time so 10 seconds must be spend) calling your function unnecessarily.

This is where the subquery caching comes in. We can tell Oracle to cache this function and use that value from now on. How we do it is a little bit interesting to me 🙂 We just use a subquery to call this function.

as you see, I change sf_Base_url function to a subquery and select the function value from dual. Main query returns 4 rows (rownum <=4) but it only takes 1 second! our function is just called 1 times and result of it used consequent rows. That is so small change and very nice performance gain.

So far so good. how about a function with parameters? Let’s change the function a little bit and make it have 1 parameter, returning our parameter added at the end of base url .

let’s call this from the sql and give a column value as paramater:

it takes 4 seconds as expected. Now, subquery caching:

it also takes 4 seconds. Subquery caching didn’t work? of course not it is used but this time our function is cached with it’s parameters. since every parameter is different, our function is called for each different parameter and every row has different my_string column value. we would see the performance gain if we have same values on our table. lets do it:

Now our table has 3 A, B and C rows. let’s run subquery caching again.

Now it returns 12 rows (3 of each letter) but it takes 4 seconds because our function called one time only for each parameter and repeated parameters are read from the cache. Work like a charm.

Is there a limit for that? how many different parameter can be cached and how it is work? Let’s test.

Now, our table has 256 distinct rows and then I double it by inserting all rows again. Finally, we have 512 rows on the table. Run our subquery cached select statement again.

interesting result. This table has 256 distinct value and 512 total number of rows. every row is doubled but both subquery caching and normal call takes almost same time. Did not subquery caching work? Actually, It worked but it can only cache limited number of rows. if there are more than number of rows that can be cached, new parameter values of function will overwrite an old cached values. So, our table rows are like this: 1,2,3,4…256,1,2,3,4…256 it cached some rows but new arriving rows eventually overwrite the cache and we couldn’t take advantage of subquery caching. lets order those rows:

t2 has the same table with table t but rows are ordered in the first place. When we run subquery caching example on t2 it takes 270 seconds almost half of the previous version which was on table t. now it was able to use cached values before they are overwrite.


I wrote that my tests indicates that only 8 different values has been cached not 255 but my further tests show that this is not true. I assume Oracle uses a hash function to store data and while hashing our function and variable, some collusions occur. that is why some of the function results are overwritten frequently. this is not something we can change. of course consequent same calls are cached and take advantage of subquery caching.

thank you very much for reading. any comment will be welcomed.

wish you all healthy days.

Why should you use PLSQL_OPTIMIZE_LEVEL as 3


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:

Very much thanks to Stephan Borsodi who contacted me over linked in and told me that Java developers are using database objects to interact with database. I actually remember that one of the insurance company (I had trained their developer teams years before) that uses Java, has objects in database. So, Stephan added a object type test script for testing performance gain of NOCOPY parameters. On his tests there were no performance gain for object type plsql parameters. When I do the test, I saw more than 20 times performance gain for nocopy parameters, then we discussed the situation about this and so for I realized that plsql optimize level is really doing a good stuff on this.

So what was the difference, I did my tests after setting the plsql_optimize_level to 1. when I do that nocopy parameters for object types are 20 times faster. I won’t rewrite whole code for my testing purposes, you can find it on my previous post ( but of course he added object type SP_MAIN and SP_PROCESS procedures into my test package here are their codes:

I must say that, his codding is much tidier than mine 🙂

here is the object type and the array type of that object which Stephan used:

compile the test package before starting the test and I use 1 as plsql_optimize_level

then run the test:

so, it is an amazing result, more than 30 times faster in NOCOPY parameters but why Stephan’s results shows no performance difference? I decided to run this code on a different database than my local db and I made a mistake! I forgot to compile package with plsql_optimiz_level as 1. it was 2 which is default value for plsql_optimize_level and there were no performance gain :

result was almost same and it was so fast that I run the first code for 1.000 times but on this second one I run it for 1.000.000 times and it was extremely faster than previous one. 1 million parameter call completed in 0.22 second. then I got curious about plsql_optimize_level 3 and tested of course:

and the result was even much more better. 5 times faster than plsql_optimize_level 2! of course this is the purpose of plsql_optimize_level but this is really a big performance gain with a very very small change.

Should we stop here? of course not 🙂 Let’s also add PLSQL_CODE_TYPE parameter. it is INTERPRETED by default, let’s make it NATIVE (plsql_optimize_level 3 too)  and see the difference one more time.

Yes, now it is even 2 times more faster than just plsql_optimize_level 2 version. So, if you are a Java developer or a database developer which runs Java application and use database objects (or collection of objects, table collections) then you must consider compiling your plsql objects with plsql_optimize_level 3 and plsql_code_type as NATIVE. Of course all calls will be faster with plsql_optimize_level 3 but non of the data types has this much (10 times in our example) performance gain. for example when I run the same code for VARCHAR2 data types, it worked approximately 2 times faster.

This is the type of performance tuning that I like most. with a very low effort, gain a lot 🙂 I would like to thank to Stephan one more time for improving my code and find out something beautiful about Java applications and db objects.

wish you all healthy days.

Enable Unified Audit on Linux and Windows


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:

if result is FALSE then you are not using unified auditing, you are using either mixed mode or standard auditing. Unfortunately, enabling unified auditing is not as easy as changing a parameter. On Linux OS:

shutdown all oracle components (database, listener etc) then

then start database and listener.

On Windows OS:

shutdown all oracle components (database, listener etc) BUT on Windows you must shutdown and start Oracle Services. shutting down and starting database using sqlplus will not work. Open services.msc and find Oracle database and listener services and stop them.

after stopping services, rename the %ORACLE_HOME%/bin/orauniaud19.dll.dbl file to %ORACLE_HOME%/bin/orauniaud19.dll. 19 in the name of dll is your version if you are using 12c then you must rename orauniaud12.dll.dbl (or orauniaud12.dll.option) file.

and then start windows services. you can check v$option view as I wrote above to see if unified auditing is enabled.

if you are not using unified auditing policies by now, you should start using them. probably they will be the only audit feature in the future. Also they are much more easier than standard auditing. You have much more option to create audit rows. for example if you are using an APEX web application (or any other) you application will logon to database with same user. if you want to audit only few of your users you can use SYS_CONTEXT values like client identifier and you can audit only those users which has specific client identifier info. Also you can create conditions to audit specific database users too. One of the most important thing is new unified audit table is tamper proof, you can not run DML on it so your audit data will be safe (unfortunately you can modify standard auditing base table AUD$). So, don’t be late.

wish you healthy days, don’t forget to get your vaccination.

Create a Clone DB from Backup on the Same Server (or remote)


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 etc…

What I am about to demonstrate is creating a TEST db from PROD db on the same server with “no ASM” only OS file system using PROD DB backups (not rman duplicate database command). of course, you can use same method to create a TEST db on “remote server”  just by copying backups and archivelogs (maybe a copy of controlfile too) to same location on a remote server. So, Lets get started:

source database (let’s say this is your PROD db which is source db) SID                : orcl
duplicate/target database (let’s say this is the TEST db which you want to create ) SID : dup
method : using backups only (no duplicate database command)
server    : same or remote (you can copy your backups to a remote server under same location with prod server and follow the same steps)
file naming : OMF (Oracle Managed Files)
OS : Linux / Windows (Windows steps are also very similar. I will add some notes for windows).
Database version: 19c (19.3)

while doing this, I will try to explain why we do that step and important files of database.

1- Parameter File

Parameter file is the first file to read while starting a database instance. it defines vital and mandatory parameters to create an instance, like database name. So, to create a new database, first we need parameter file. it could be either SPFILE (binary version) or PFILE (text version). Since we can not create a binary file easily, I will create text parameter file and then create binary version of it. there thousands of parameters in Oracle but just 3 of them is enough to create an instance. under ORACLE_HOME/dbs directory, create an empty file with name initdup.ora and put those into it:

Windows OS Note: for Windows OS, directory is ORACLE_HOME/database and file name is INITDUP.ora by default. Also you need to create a Windows Service for Oracle Database instance using “oradim” tool first: “oradim -NEW -SID dup -STARTMODE auto” is enough to achieve this.

what are these 3 parameters?

  1. db_name: name of the database which is “orcl” for now. wait what? don’t we create a new db called “dup” why is this orcl ? because we will use orcl database backups to create our database and I don’t want to show all backup files to rman. so We will act as if we are creating an db called “orcl” but we will change it while doing this. we will use orcl is just for finding backups and restore/recover datafiles.
  2. db_unique_name: this parameter is set to null by default and if it is null, it’s value is db_name. so you can think it as if Oracle uses it “NVL(db_unique_name, db_name)”. as you see this parameter is set to “dup” which is name of new database. while doing restore/recover operations, if rman does not know that this is a different database then it will restore /recover source database (orcl). with this parameter we are identifying our unique database.
  3. db_create_file_dest: this is the parameter that makes your database as OMF (oracle managed files) which means you don’t need to give any file name. just say “add a datafile to this tablespace”; Oracle will add new datafile and give a name to it. we don’t need to know what is the name of a datafile. this parameter also, of course, tells that where the files will be created. by saying files not just datafiles. also redo logs and control files too. this is the “main directory”  for a database. so path pattern is like this:
    /u01/app/oracle/oradata/<instance_name>/datafile => for datafiles
    /u01/app/oracle/oradata/<instance_name>/controlfile => for controlfiles
    /u01/app/oracle/oradata/<instance_name>/onlinelog => for redo logs.
    in our example, instance name will be “dup”.

Now, we have a parameter file so we can start our database instance. Do not forget that our source database is also on the same server so we must be careful not to mess with source db. So, I will set ORACLE_SID environment variable first:

Windows OS Note: in Windows you can set your environment variable ORALCE_HOME by using “set” command not export. “set ORACLE_SID=dup”.

as you can see we started an instance already. of course we don’t even have a datafile yet but still this is an instance. we didn’t specify any memory parameter for example in pfile but they have all default values so it will work but of course if you have a defined configuration you can add them into pfile (initdup.ora) so db instance will be using them.

I created SPFILE before starting the instance so spfiledup.ora is created under ORACLE_HOME/dbs (ORACLE_HOME/database for windows os ) directory and instance is started by this spfile.

so we have a basic parameter file and our instance is up. let’s continue with step 2.

2- Control File

control file is the next step while starting a database. it stores very critical information like where are datafiles, backups, redo logs. Also many database limits are defined in controlfile like maximum number of data files which can be created in db (MAXDATAFFILES). to restore control file, first I will backup as copy control file from source db (which is orcl, our prod database). so, from “orcl”, first we create control file copy.

so I created controlORCL.ora file under /tmp which is copy of current control file of orcl db. now let’s turn to dup db and restore it.

command is “restore controlfile from ‘/tmp/controlORCL.ora’;” and control file restored under: /u01/app/oracle/oradata/DUP/controlfile/ directory. as you see it is under DUP directory because of db_unique_name parameter.

We restored our control file but this is not entirely correct because this control file is belong to orcl db and it knows this database as “orcl” but we will deal with that later. Now, this control file knows where backups are, so we can restore datafiles but first we must take our database to mount mode.

Now, restore database BUT as I mentioned, this control file belongs to “orcl” db so if I run “restore database” it will restore datafiles to original “orcl” db datafile locations.  You must be careful here, you don’t want to shutdown your source db. to restore those datafiles to a different location (dup db datafile location)  we will use “set newname” command:

since I use OMF, ” set newname for database to new;” means, restore datafiles to wherever db_file_create_dest parameter points. by default, control file knows where those datafiles are so if I restore them, it will be under ORCL db location. “set newname for database to new” cause re-read parameter file and datafile location information and restore datafiles to there. as you see in the output, datafiles are restore under DUP location (ex: channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/DUP/datafile/o1_mf_system_%u_.dbf) .

perfect. now we have datafiles too but we can not open database right away. first, even if we are able to open database, control file is still old and will look for datafiles under ORCL directory not DUP directory. we must tell controlfile to use those recently restored datafiles as primary datafiles. so we must “switch database to copy”.

let’s recover database now:

this will apply all changes which are made after the backup. it will read archivelog files and apply them into datafiles.

Good!, can we open database now? well, no 🙂 why? because as I said earlier, this controlfile is still a stranger! it knows this database as “orcl” but it is not anymore! Since we get everything we want (controlfile, datafiles, recover operation) now we can change our controlfile. to achieve that we must re-create control file and tell “this is dup db anymore”.  of course before do that, we must change “db_name” parameter anymore. it is still “orcl”. we can set it as “dup” now but this parameter can not be changed without restart. Problem about that, you can not change db_name with “alter system” if you are using spfile. so stesp are these:

  1. I create pfile
  2. shutdown database
  3. modify pfile and change db_name parameter to “dup”, remove db_unique_name parameter
  4. create spfile again. ===> This is very important. if you forgot this step, when you start db, it will be using old values.
  5. start db in mount mode.
  6. create controlfile create script using “alter database backup controlfile to trace” command.

finally we need to re create our controlfile to make it completely a dup db controlfile. I created controlfile script under the tmp directory. you can open it any text editor. you will find 2 “create controlfile ” command. one with NORESETLOGS and the other RESETLOGS. on a new database, to create redo logs, we must use RESETLOGS clause. so I will just write here RESETLOGS version in control_file.txt file.

as you can see, controlfile thinks that this is ORCL database and all redo logs are also under ORCL directory (‘/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_j6xpd19n_.log’,). This is also an important step. if you don’t change those ORCLs to DUP, you might cause damage to your source database which is orcl in my example. Since I use OMF, and I always use OMF just for these reasons, all I need to do is remove those path.

Also, since we want to change database name, we must add SET clause after “create controlfile reuse” part. Please note that, just changing ORCLs to DUP in this script won’t work because datafiles also has database name information in their header file. so we must use SET which means, “set database name”. this will also update datafile headers. to create controlfile again, you must be in nomount mode (in mount mode, cotnrolfiles are already in use, you can not change them.)

finally, we have a real controlfile for dup db. after now, we can  open it with resetlogs option. creating controlfile will also take our database into “mount” mode so no need another “alter database mount” command.

viola! we have a new db with a new name which is also created from another database backup. you can use this on the same or remote servers. as said before, only difference is copying backups to remote server.

I hope this helps. I will try to write for other methods too like duplicate database etc.

please make a comment, so I can believe that not daily 30 visitors on my blog are bots 🙂

wish you healthy days.

Important Edit: you might ask why we don’t create controlfile before all those operations. it has 2 reasons. creating control file will cause loosing the location of backups and more importantly, create controlfile command validates the datafiles if they are in the correct locations. so we can not create controlfile without having datafiles.

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 so far and how to generate acl records. What I am about to write is for 19c databases.

  1. First of all, of course, you can get this error by setting an invalid port like -123 or 999999999. check them first.
  2. secondly, port range you use might intersect with another definition. for example, there is an acl entry for for 3000 and 3010 ports. you can not add a new port range for same host, same principal and same privileges with port range 3010-3015. since 3010 is already defined in first entry you can not use it again, second entry must start with 3011 at least.
  3. thirdly, you might be trying to insert an entry for RESOLVE privilege with port definition! this is not also allowed. for resolve privilege, lower and upper ports must be null.

these are my basic steps to check. even expdp utility with “include=NETWORK_ACL” option might failed while importing them because of these 3 steps. Lately I was copying acls from a 12c db to 19c and after getting too much error I decided to generate a simple select statements to generate acls append command. you can either copy, paste and run the result of statement or run it in a loop and run the command with dynamic sql.

Please don’t judge for not using match_recognize. I just wrote it fastest way I can think of. script is first dividing port ranges to row by row single ports and them regroup them (some old entries had intersected port ranges). also uses NULL for resolve privileges. I didn’t add all options of course. I hope this script helps you.


wish you healthy days.

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 your source database:

this will export all stored ACL privileges on your database and to import those ACLs to a new db:

so easy but during the import you can get ORA-24244:  invalid host or port for access control list (ACL) assignment error.

I encountered this error while creating a new db for a client. In my case this has happened because RESOLVE privileges shouldn’t be used with port definition!

so, somehow previous db has port definitions for RESOLVE privileges and those ones wasn’t imported. to add them into new db, I simply used this sql:

run this select on the source db and execute the output on the target db.

wish you all happy and healthy days.

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 course this is a big subject but I want to talk about the basics and how to use sqlldr to demonstrate it.

first of all, let’s discuss the basics.

  • High Water Mark : This is a pointer which points end of the table! this is the boundary of used space. when you create a table (without a segment) and start to insert into it, Oracle will allocate new blocks and put your data into them. Oracle already knows the starting block because it is a fixed point when you created the segment but end of the table (last block) is not fixed. it can be change when new data arrived. So, Oracle uses a pointer which points end of this table called High Water Mark (HWM). as a simple example, “full table scan” means searching all blocks from starting block to HWM. if you truncate a table this means moving HWM to starting block. that is why truncate is really fast. it won’t delete rows, it will move HWM to starting point and don’t forget this is end of the table so you remove all rows automatically. of course rows are still there but they are not belong to this table anymore and data is obsolete, can be overwritten.
  • Insert & Delete operations: when you insert a new row using “insert into values” command, Oracle will search for an “empty place” in table blocks. if it is found then this row will be inserted into that block. Oracle always try to use space efficiently. motto is; if there is empty space, use it. This is valid for “insert into … values (…)” command. sqlldr for example can act differently and I will discuss about it. Delete command will mark a rows as “deleted”. that means that place is considered as “empty” anymore and if a new row arrives (insert into values) this row can be written to “empty” place. if you delete all rows from a billion rows table and then select it, your select will take huge amount of time. Why? deleting will not remove blocks from table just mark rows as deleted. so if your table has 1million blocks, after deleting all rows, you will still have 1 million blocks. if you run “select * from table” at that point, as said above, a full table scan will be done and all blocks from starting block to HWM will search, as a result your select will search 1million blocks but couldn’t find any “not deleted” row. that is why it will take so long. if you truncate the table on the other hand, since it will move HWM to starting point, first truncate will complete really fast and after then when you run “select * from table”, it will complete in milliseconds because there is no million blocks, there is only starting point. In conclusion, delete cause empty spaces and when an Insert statement issued Oracle wants to use that empty space to eliminate unnecessary growth.

At this point you can say that, insert statements could take more time because Oracle searches for an “empty space”. you are right! oracle takes some time but not much of course, this is controlled by a bit map so it is easy to find an empty space but still there is a search.

What defined here is “conventional insert”. search for an empty space and insert it. At this point, “direct path insert” does what you think. don’t check empty spaces just add new data to the end of the table. “add data to the end of the table” means, new data won’t interact with table directly! Oracle will use “new blocks” to insert this data. those new blocks are free blocks which means they are not related to any other segment (table, index, cluster etc). data will be inserted directly to those new free blocks so Oracle won’t look for empty spaces in the table and those completely new blocks will be added to “end of the table”. this also means, HWM will be moved to end of those newly created blocks. this operation is of course much more faster especially if you are loading huge amount of data. you can “tell” oracle to do that via some hints like APPEND: insert /*+ append */ into table select * from ….; this date will be inserted using direct path insert and data will be added directly to end of this table (won’t use any empty space in table”. even if whole table is empty (all rows are deleted by delete command), Oracle will add new blocks for this table and table size will increase.

I wanted to show you how sqlldr tool does that and difference of performance. this is for my setup:

this is add 582.616 rows into tmp table on my local db. After inserting those rows, I just exported this data as csv (using sql developer) and save it as object.csv file.

As you see, table has 12.288 blocks which contains 582.616 rows. Now I am deleting all rows:

of course table still has 12.288 blocks because we just marked rows as deleted!

Let’s insert our previously exported object.csv file into this table using sqlldr. here is sqlldr.ctl file:

my sqlldr command is like this (I am using windows 10 for this demonstration):

all rows are inserted. first, let’s check table size:

there is no change on table size! because this is a “conventional” insert. please check first lines of sqlldr output just before “commit point reached” lines, it says: “Path used: Conventional”. this means look for empty spaces and insert new data into those empty spaces as we discussed earlier. so since I have deleted all rows before the insert, all table was empty and has enough space to get same rows again.

when I check my log file (conventional.log) sqlldr takes 11.20 seconds to insert all rows.

Let’s do it again with direct path insert this time. delete table and check size:

all rows are deleted and still have same size. here is the direct path insert:

as you see, now “Path used” says Direct. I did this by using “direct=true” parameter of sqlldr tool and same amount of rows are inserted. Let’s check table size now:

it almost doubled the size! why? because new data is added to end of the table, no empty space is not checked! we still have same number of rows in the table but also have a huge empty space in it. so what is the benefit here? Let’s check log file (direct.log) and see how long does it take to complete:

Elapsed time was: 00:00:02.83

2.83 seconds it is almost 4 times faster than conventional insert! here is the benefit.

sqlldr is a great tool if you use it correctly. of course it has a lot more options like parallelism, array size etc (I hope I will write about them too) but if you have a huge data to load into a table and empty spaces are not an issue then you can use direct path insert. only problem is “logging” option is important. if object is in “no logging” mode then it will even run faster but no redo log will be generated. this means you can not get your data from backups! if you have a failure after no logging direct path insert operation, you can not use your backups. you must backup immediately. of course your database administrator might put your database into “Force Logging” mode, then it is not important whether your object is logging or nologging mode. database will log it anyway and your data will be safe in backups.

so use it carefully. I hope this helps.

wish you healthy, corona free days.