Release multiple Oracle scripts to Production Server using batch file

I am writing this blog for all DBA team who are doing this task manually for their Production Deployment repeatedly.

To solve this problem, I have created a batch file which will read all SQL files from folder excluding child folder and use SQLPLUS to execute scripts file to specified Data Source.

How to run batch file ?

1. Copy batch file “ReleaseOracleScripts.bat” to location from where you want to release SQL files.

2. Click on “ReleaseOracleScripts.bat” to execute batch file.

3. Batch file will ask Host Name, Port, Service Name, User Name and Password where you want to deploy your scripts.

4. Batch file will ask you to click any key before executing each script.

5. Once click any key, It will execute this script to Target Server and display output.

6. Once all scripts are deployed, It will ask to click any key to close window.

Below is the batch file to download.

1. ReleaseOracleScripts.bat

Release multiple SQL Server scripts to Production Server using batch file

I am writing this blog for one on my colleague Kallidile, Sujith who wants to do this task for his Production Deployment repeatedly. This is also dedicated to all DBA team who are also doing same.

To solve this problem, I have created a batch file which will read all SQL files from folder excluding child folder and use SQLCMD to execute scripts file to specified Data Source.

How to run batch file ?

1. Copy batch file “ReleaseSQLServerScripts.bat” to location from where you want to release SQL files.

2. Click on “ReleaseSQLServerScripts.bat” to execute batch file.

3. Batch file will ask Server Name where you want to deploy your scripts.

4. Batch file will ask you to click any key before executing each script.

5. Once click any key, It will execute this script to Target Server and display output.

6. Once all scripts are deployed, It will ask to click any key to close window.

Below is the batch file to download.

1. ReleaseSQLServerScripts.bat

Note:

1. We are using window authentication for deployment.

Linked Servers in SQL Server

I am always seeing that people are struggling while creating linked server in SQL Server based on their access on target server. I have tried to show some sample way to create linked server. Below are list of cases.
1) Linked server from SQL Server to another SQL Server using SQL Login
a) Go to SQL Server Management Studio –> Connect SQL Server –> Server Objects –> Linked Servers –> Right Click New Linked Server

b) Enter target server name in Linked Server. Select source type as SQL Server.

c) Go to Security and select “Be made using this security context”. Enter user name in Remote login and password in “With password”.

d) click Ok.

e) Execute below query to check if linked server is working.
Query: Select * From LinkedServerName.DatabaseName.sys.objects
2) Linked server from SQL Server to Self using SQL Login.
a) Go to SQL Server Management Studio –> Connect SQL Server –> Server Objects –> Linked Servers –> Right Click New Linked Server

b) Enter linked server name (e.g. SELF) in Linked Server. Select source type as Other data source. Select “Microsoft OLE DB Provider for SQL Server as Provider”. Enter target server name in Data Source. Enter database name in Catalog.

c) Go to Security and select “Be made using this security context”. Enter user name in Remote login and password in “With password”.

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

This is most well known error when we are trying to access excel from SQL Server. To fix this issue, we need to follow below steps.

1. Install Microsoft Access Database Engine 2010 Redistributable based on 32 bit or 64 bit version of Windows from below link.
   http://www.microsoft.com/en-in/download/details.aspx?id=13255
2. Registry changes. Navigate to <HKLM>\Software\Microsoft\Microsoft SQL Server\Providers\Microsoft.Jet.OLEDB.4.0 and Create a DWORD named DisallowAdhocAccess with the value 0. Just to check if you are in the right place there will be another DWORD entry under the name of AllowInProcess, which should be set to 1 already.
   
3. Restart agent and server services 
4. Enable OLEDB Driver in SQL Server 
    
   EXEC sp_configure 'show advanced options', 1; 
   GO 
   RECONFIGURE; 
   GO 
   EXEC sp_configure 'Ad Hoc Distributed Queries', 1; 
   GO 
   RECONFIGURE; 
   GO

Send Greetings to Multiple Mails from Excel in SQL Server

I am inspire to write this blog on the day of Ramzan when I want to send multiple greetings to my friends, family and others. I thought to create script which will read email IDs from excel and send Greeting to all of them.
We have to follow below steps to configure this process.

1. Create Error_Log Table (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts.html)
2. Create Greeting table
   1. Columns
      1. GreetingName
         1. This is unique name to identify greeting which we will send to all email addresses.
      2. FromName
         1. This will be written as from clause in email  body.
      3. FromMail
         1. This will be use your from email address.
      4. ToMailListFilePath
         1. This is path of excel which will contain list of name with their email ids.
      5. Subject
         1. This is subject of your greeting.
      6. Message
         1. This is message for your greeting.
      7. GreetingImgUrl
         1. This is greeting image url hosted on some server.
3. Insert Greetings to dbo.Greeting table.
4. Create procedure dbo.cp_Send_Greeting with parameter GreetingName.
5. Execute procedure dbo.cp_Send_Greeting. Below is greeting mail.

Below are the scripts to download.

1. Send Greetings to multiple emails.sql

Below is sample document to use it.

1. GreetingMailList.xlsx

Unzip all *.zip files from folder including child folders

I am writing this blog for one on my colleague Ashish Gupta who wants to do this task for his Production Deployment repeatedly.

We had requirement in our company to unzip all files from folder including child folders to specified location. It was not only single file but we had lots of zip files and each was having lots file in there.

To solve this problem, we have created batch file which will read all zip files from folder including child folder and use winzip32.exe command to extract all files to specified location.

We also found that winzip32.exe command will open folder where files are extracted once unzip operation is completed. We had also provided solution for this here.

How to disable “Show unzipped file” option ?

1. Open C:\Program Files (x86)\WinZip\WINZIP32.EXE.

2. Click right bottom error at “Unzip Options”.

3. Uncheck “Show unzipped file”.

How to run batch file ?

1. Copy batch file “ExtractZipFiles.bat” to location from where you want to extract all zip files.

2. Click on “ExtractZipFiles.bat” to execute batch file.

3. Batch file will ask location where you want to extract all files and click enter. I have used “C:\GhanchiAsif” to extract my all files.

4. Batch will extract all files and extract to “C:\GhanchiAsif”.

Below is the batch file to download.

1. ExtractZipFiles.bat

Auto Maintain Archive, Purge & Purge on Source Table in Oracle

We are developing many application where we are auditing, archiving and purging data also for many tables. Most of the application will use Water Flow SDLC for development. While modifying main table, we also need to maintain archival and purge objects.
There many places where we need to archive child table also if we are archiving parent table based on foreign key relationship with tables.
We are also using configuration table to maintain archival and purge period for different tables and also entering different key if we want to maintain archival or purge with different period.
Here, we tried to automate all above scenario. We will go through all above scenario one by one and their configuration. Before that we need to configure some objects. We need to create below objects to configure it. (Oracle Configure Archival Process.sql)
Tables

1. Error_Log (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts-in-oracle.html)
   
2. Config (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts-in-oracle.html)
   
3. Archive_Purge_Config
   
   1. Columns
      1. is_archive
         1. 1 = Archive is enable from ERROR_LOG to ERROR_LOG_ARCHIVE.
         2. 0 = Archive is disable.
      2. archive_column_name
         1. a. Archive is happening based on this datetime column.
      3. archive_day
         1. Archive is happening based on configured days in this column.
      4. archive_rule_sql
         1. Archive and Purge will happened on filtered records configured in this rule.
         2. NULL – It will happened to whole table.
      5. is_purge
         1. 1 = Purge is enable from ERROR_LOG_ARCHIVE.
         2. 0 = Purge is disable.
      6. purge_days
         1. Purge is happening based on configured days in this column.
      7. is_purge_on_source
         1. 1 = Purge is enable from ERROR_LOG. In this case, It will ignore values in is_archive and is_purge columns.
         2. 0 = Purge is disable.
      8. is_active
         1. 1 = Record is active.
         2. 0 = Record is inactive.

Procedures

1. cp_archive_purge
2. cp_error_log_alert (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts-in-oracle.html)

Jobs

1. JOB_ARCHIVE_PURGE
   1. Execute procedure cp_archive_purge daily once at 1:00 AM.
2. JOB_ERROR_LOG_ALERT (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts-in-oracle.html)

Examples

To understand this scenario, I have taken Employee and Department example. Below is E-R Diagram for that. Below are the details of tables. (Oracle Configure Archival Process Employee & Department tables.sql)

1. Department
   
   1. There may be many departments in database.
2. Employee
   
   1. There are many employees for each departments
3. Contact
   
   1. For each employee, there may be many contact details.
4. Address
   
   1. For each employee, there may be many address details.
5. AddressDetails
   
   1. For each address, there may or may not be additional address details.

Now, our requirement is to archive and purge on Department & Error_Log tables which also should archive it’s child tables if exist based on relationship created between them. Below is configuration required in dbo.Archive_Purge_Config table to archive that. We also need to add config_key “Archive_Purge_Max_Queue_Size” in dbo.Config table. It will used to setup batch size while deleting records from table inside procedure. (Configure Archival Process Data.sql)

1. Department – It will archive 30 days in Department_Archive table and purge(permanently) on 30 days from Department_Archive table.
   
2. Error_Log - It will archive 30 days in Error_Log_Archive table and purge(permanently) on 30 days from Error_Log_Archive table.

Once job dbo.job_archive_purge will execute, it will create new _Archive tables for each tables configured in dbo.archive_purge_config if it’s not exists with their child relationship tables. Below are the list of archive tables will be created by dbo.cp_archive_purge procedure.

1. Department_archive
2. Contact_Archive
3. Employee_Archive
4. Address_Archive
5. AddressDetail_Archive
6. Error_Log_Archive

Now, we will talk about our daily changes on tables. We can have 3 types of changes in our tables. We will talk about each one by one.

1. New Column

I want to add one more column “DepartmentType varchar(5)” in dbo.Department table. Once job dbo.job_archive_purge will execute, it will also add column in _Archive table. Below is screenshot of Department_Archive table after job execution.

Query:
ALTER TABLE DEPARTMENT ADD DEPARTMENTTYPE VARCHAR2(5)

2. Rename Column

I want to rename column “AddressDetail” to “Address1” in dbo.AddressDetail table. Once job dbo.job_archive_purge will execute, it will drop old column and create new column in _Archive table. Below is screenshot of AddressDetail_Archive table after job execution.

Query:
ALTER TABLE ADDRESSDETAIL RENAME COLUMN ADDRESSDETAIL TO ADDRESS1;

3. Drop Column

If we extra column in archive table, it will not break archive process so we are not dropping extra columns for each run. Procedure will drop extra columns from _Archive table with any other fixes for that table like we did Rename Column as above.

4. Data Type Change of Column

After adding column “DepartmentType” in dbo.Department table, I realize that it should be varchar(10). While doing this conversion, we should make sure that _Archive column should be convertible with new data type. If it’s require data fixes then do that first before execution of job. To test this scenario we should have some data to archive. (Oracle Configure Archival Process Employee & Department tables Data.sql)

Query:
ALTER TABLE DEPARTMENT MODIFY DEPARTMENTTYPE VARCHAR2(10);

Summary

1. Archive Process can support below features.
   
   1. Auto create archive table.
   2. Auto create and delete additional columns on archive table.
   3. Auto modify data type of column.
2. Archive Process can create new column without any constraint.
   
3. Archive table will be created with one more column “archive_created_date” with default getdate().
   
4. Archive feature with their child tables.
   
5. Purge on archive table based on days setup in dbo.Archive_Purge_Config table.
   
6. Purge from Source directly based on days setup in dbo.Archive_Purge_Config table.

Below are the scripts to download.

1. Oracle Configure Archival Process.sql
   
2. Oracle Configure Archival Process Employee & Department tables.sql
   
3. Oracle Configure Archival Process Data.sql
   
4. Oracle Configure Archival Process Employee & Department tables Data.sql

Error Logging and Alerts in Oracle

Error Logging is main part of our database application. I am trying to share some sample code which I am using in my daily life.
We need to follow below steps.
1) Create Error Log Table.
Table: Error_Log
Columns:

1. Log_ID
   1. Identity column.
2. HOST_NAME
   1. Host Name.
3. SERVICE_NAME
   1. Service Name.
4. USER_NAME
   1. User Name.
5. PROC_NAME
   1. Procedure Name.
6. SQL_CODE
   1. Error Code from Procedure.
7. SQL_ERRM
   1. Error Message from Procedure.
8. ERROR_BACKTRACE
   1. Error Line Number from Procedure.
9. Created_Date
   1. Default current date from database.

2) Create Sequence for Error_Log Table.
3) Create trigger on Error Log table to set sequence to Log_ID on insert.
4) Create Error Log Procedure
5) Create Procedure which used Error Log Procedure in 3rd Step.
6) Execute Parent Procedure. Below is error output from Parent Procedure.

7) Check logs in Error Log table.

8) Create Config Table.
Table: Error_Log
Columns:

1. CONFIG_ID
   1. Identity column
2. CONFIG_KEY
   1. Config Key
3. CONFIG_VALUE
   1. Config Value
4. IS_ACTIVE
   1. 1 = Config Key is active
   2. 0 = Config Key is inactive
5. CREATED_DATE
   1. Default current date from database.
6. MODIFIED_DATE

9) Create Sequence for Config Table.
10) Create trigger on Config table to set sequence to Config_ID on insert.
11) Insert mail configuration data into Config Table.

1. Mail_Smtp_Host
2. Mail_From
3. Mail_To
4. Mail_Subject_Error_Log_Alert

12) Create string array type.
13) Create function split
14) Create Procedure to send mails.
15) Create Procedure to send alert mails.
16) Create job to send error log alert daily once at 7:00 A.M.
17) Execute job to send alert mail. Below is alert mail.
Below are the scripts to download.

1. Oracle Error Logging and Alerts.sql

Note

1. Before executing attached script replace your email id.
2. You should have ACL created on your schema. (Check http://www.sqlscientist.com/2013/07/how-to-create-aclaccess-control-list-in.html)

Auto Maintain Archive, Purge & Purge on Source Table in SQL Server

We are developing many application where we are auditing, archiving and purging data also for many tables. Most of the application will use Water Flow SDLC for development. While modifying main table, we also need to maintain archival and purge objects.

There many places where we need to archive child table also if we are archiving parent table based on foreign key relationship with tables.

We are also using configuration table to maintain archival and purge period for different tables and also entering different key if we want to maintain archival or purge with different period.

Here, we tried to automate all above scenario. We will go through all above scenario one by one and their configuration. Before that we need to configure some objects. We need to create below objects to configure it. (Configure Archival Process.sql)

Tables

1. Error_Log (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts.html)
2. Config (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts.html)
   
3. Archive_Purge_Config
   
   1. Columns
      1. is_archive
         1. 1 = Archive is enable from ERROR_LOG to ERROR_LOG_ARCHIVE.
         2. 0 = Archive is disable.
      2. archive_column_name
         1. a. Archive is happening based on this datetime column.
      3. archive_day
         1. Archive is happening based on configured days in this column.
      4. archive_rule_sql
         1. Archive and Purge will happened on filtered records configured in this rule.
         2. NULL – It will happened to whole table.
      5. is_purge
         1. 1 = Purge is enable from ERROR_LOG_ARCHIVE.
         2. 0 = Purge is disable.
      6. purge_days
         1. Purge is happening based on configured days in this column.
      7. is_purge_on_source
         1. 1 = Purge is enable from ERROR_LOG. In this case, It will ignore values in is_archive and is_purge columns.
         2. 0 = Purge is disable.
      8. is_active
         1. 1 = Record is active.
         2. 0 = Record is inactive.

Procedures

1. cp_archive_purge
2. cp_error_log_alert (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts.html)

Jobs

1. DatabaseName.dbo.job_archive_purge
   1. Name : DatabaseName.dbo.job_archive_purge
   2. Description: This job is used to archive, purge and purge on source table as per configuration in dbo.ARCHIVE_PURGE_CONFIG table.
   3. Steps
      1. Name: Step 1
      2. Type: Transact-SQL script(T-SQL)
      3. Command: exec dbo.cp_archive_purge
   4. Schedules
      1. Name: Occurs every day at 1:00:00 AM.
      2. Schedule type: Recurring
      3. Occurs: Daily
      4. Occurs once at: 1:00:00 AM
      5. Start Date: Today’s Date
2. DatabaseName.dbo.job_error_log_alert (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts.html)

Examples

To understand this scenario, I have taken Employee and Department example. Below is E-R Diagram for that. Below are the details of tables. (Configure Archival Process Employee & Department tables.sql)

1. Department
   1. There may be many departments in database.
2. Employee
   1. There are many employees for each departments
3. Contact
   1. For each employee, there may be many contact details.
4. Address
   1. For each employee, there may be many address details.
5. AddressDetails
   1. For each address, there may or may not be additional address details.

Now, our requirement is to archive and purge on Department & Error_Log tables which also should archive it’s child tables if exist based on relationship created between them. Below is configuration required in dbo.Archive_Purge_Config table to archive that. We also need to add config_key “Archive_Purge_Max_Queue_Size” in dbo.Config table. It will used to setup batch size while deleting records from table inside procedure. (Configure Archival Process Data.sql)

1. Department – It will archive 30 days in Department_Archive table and purge(permanently) on 30 days from Department_Archive table.
2. Error_Log - It will archive 30 days in Error_Log_Archive table and purge(permanently) on 30 days from Error_Log_Archive table.

Once job dbo.job_archive_purge will execute, it will create new _Archive tables for each tables configured in dbo.archive_purge_config if it’s not exists with their child relationship tables. Below are the list of archive tables will be created by dbo.cp_archive_purge procedure.

1. Department_archive
2. Contact_Archive
3. Employee_Archive
4. Address_Archive
5. AddressDetail_Archive
6. Error_Log_Archive

Now, we will talk about our daily changes on tables. We can have 3 types of changes in our tables. We will talk about each one by one.

1. New Column

I want to add one more column “DepartmentType varchar(5)” in dbo.Department table. Once job dbo.job_archive_purge will execute, it will also add column in _Archive table. Below is screenshot of Department_Archive table after job execution.

Query:

alter table dbo.department add DepartmentType varchar(5)

2. Rename Column

I want to rename column “AddressDetail” to “Address1” in dbo.AddressDetail table. Once job dbo.job_archive_purge will execute, it will drop old column and create new column in _Archive table. Below is screenshot of AddressDetail_Archive table after job execution.

Query:

sp_rename 'AddressDetail.AddressDetail' , 'Address1', 'column'

3. Drop Column

If we extra column in archive table, it will not break archive process so we are not dropping extra columns for each run. Procedure will drop extra columns from _Archive table with any other fixes for that table like we did Rename Column as above.

4. Data Type Change of Column

After adding column “DepartmentType” in dbo.Department table, I realize that it should be varchar(10). While doing this conversion, we should make sure that _Archive column should be convertible with new data type. If it’s require data fixes then do that first before execution of job. To test this scenario we should have some data to archive. (

Query:

alter table dbo.department alter column DepartmentType varchar(10)

Summary

1. Archive Process can support below features.
   1. Auto create archive table.
   2. Auto create and delete additional columns on archive table.
   3. Auto modify data type of column.
2. Archive Process can create new column without any constraint.
   
3. Archive table will be created with one more column “archive_created_date” with default getdate().
   
4. Archive feature with their child tables.
   
5. Purge on archive table based on days setup in dbo.Archive_Purge_Config table.
   
6. Purge from Source directly based on days setup in dbo.Archive_Purge_Config table.

Below are the scripts to download.

1. Configure Archival Process.sql
2. Configure Archival Process Employee & Department tables.sql
3. Configure Archival Process Data.sql
4. Configure Archival Process Employee & Department tables Data.sql

How to create ACL(Access Control List) in Oracle

Below are some steps to create ACL in Oracle. Please change UserName in script with your own UserName for database.

Script:

begin

        dbms_network_acl_admin.create_acl (

                acl             => 'utlpkg3.xml',

                description     => 'Normal Access',

                principal       => 'UserName',

                is_grant        => TRUE,

                privilege       => 'connect',

                start_date      => null,

                end_date        => null

        );

end;

/

begin

  dbms_network_acl_admin.add_privilege (

  acl                         => 'utlpkg3.xml',

  principal             => 'UserName',

  is_grant              => TRUE,

  privilege             => 'connect',

  start_date         => null,

  end_date          => null);

end;

/

begin

  dbms_network_acl_admin.assign_acl (

  acl => 'utlpkg3.xml',

  host => 'localhost',

  lower_port => 22,

  upper_port => 55);

end;

/

commit;

/

Sample Script:

Add your email address before executing script.

declare

  l_mail_conn   UTL_SMTP.connection;

begin

  l_mail_conn := UTL_SMTP.open_connection('localhost', 25);

  UTL_SMTP.helo(l_mail_conn, 'localhost');

  UTL_SMTP.mail(l_mail_conn, ' Your email address');

  UTL_SMTP.rcpt(l_mail_conn, ' Your email address ');

  UTL_SMTP.data(l_mail_conn, 'This is a test message.' || UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.quit(l_mail_conn);

end;

/

Below is error if ACL is missing.

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 17
ORA-06512: at “SYS.UTL_TCP”, line 246
ORA-06512: at “SYS.UTL_SMTP”, line 115
ORA-06512: at “SYS.UTL_SMTP”, line 138
ORA-06512: at “XXX”, line 36
ORA-06512: at line 1

Compare collation from SQL Server 2000(source) table to 2008(target) view and create script to cast data type in View

We are always upgrading from lower version to highest version and also changing approaches for new implementation. There may be chances that data type mismatched from our old version(2000) to new version(2008).
Here, we had requirement to compare collation from SQL Server 2000(source) table to 2008(target) view and create script to cast data type in View. I have tried to create script which will use of OPENROWSET command to read system tables from SOURCE database and compare it with TARGET database. This script will return difference from SOURCE to TARGET tables which are exists in TARGET database. We are doing that because it’s possible that we don’t want some table from older version. This script also provides cast prefix and suffix for TARGET database to sync views with SOURCE database.
Below is script to download.
Compare Collation from Source To Target.sql

Error Logging and Alerts in SQL Server

Error Logging is main part of our database application. I am trying to share some sample code which I am using in my daily life.
We need to follow below steps.
1) Create database.
2) Create Error Log Table.
Table: Error_Log
Columns:

1. Log_ID
   1. Identity column.
2. Database_Name
   1. Database Name.
3. Proc_Name
   1. Procedure Name.
4. Err_Number
   1. Error Number from Procedure.
5. Err_State
   1. Error Stage from Procedure.
6. Err_Message
   1. Error Message from Procedure.
7. Err_Line
   1. Error Line from Procedure.
8. Created_Date
   1. Default current date from database.

3) Create Error Log Procedure.
4) Create Procedure with error which used Error Log Procedure in 3rd Step.
5) Execute Parent Procedure. Below is error output from Parent Procedure.
66) Check logs in Error Log table.

7) Create Config Table. Add below entry for Mail Configuration.

1. Mail_From
2. Mail_Subject_Error_Log_Alert
3. Mail_To

8) Create Procedure to send alert mails.
9) Create Job to execute Procedure to send alert mails. Below is alert mail.

Below are the scripts to download.
Error Logging and Alerts.sql

Compare indexes of tables from SQL Server 2000(source) to 2008(target) and create script to create indexes at target

We are always upgrading from lower version to highest version. There may be chances that indexes are mismatched from our old version(2000) to new version(2008). I have tried to create script which will use of OPENROWSET command to read system tables from SOURCE database and compare it with TARGET database. This script will return difference from SOURCE to TARGET tables which are exists in TARGET database. We are doing that because it’s possible that we don’t want some table from older version. This script also provides create script for TARGET database to sync indexes with SOURCE database.

Below is script to download.

Compare Index from Source To Target.sql

Compare collation of tables from SQL Server 2000(source) to 2008(target) and create script to alter collation at target

We are always upgrading from lower version to highest version. There may be chances that collation is mismatched from our old version(2000) to new version(2008). I have tried to create script which will use of OPENROWSET command to read system tables from SOURCE database and compare it with TARGET database. This script will return difference from SOURCE to TARGET tables which are exists in TARGET database. We are doing that because it’s possible that we don’t want some table from older version. This script also provides alter script for TARGET database to sync collation with SOURCE database.

Below is script to download.

Compare Collation from Source To Target.sql

Some good things to learn for "for update” in oracle.

Here, we have tried cover a scenario where one procedure is called by 2 process on same time and they want unique record from table. To understand this scenario, we have created tables Departments, Employee and Contact (Some good things to learn for for update in oracle 0.sql).

We are going to learn 3 different cases which comes in our actual life.

1) Simple way of use “update of column” on table. (Some good things to learn for for update in oracle 2.sql)

I have tried to execute same script in 3 windows which is using for update of column. I see below timeframes. Here, 2nd window’s open cursor is waiting till 1^st window is not committed and 3^rd window’s open cursor is waiting till 2^nd window is not completed. (Query1)

In below output. 1^st window is committing at 04:10:41, On same time2^nd window open cursor is opening. Same for 3^rd window also.

It means we will get unique records from different process if it’s called on same time.

1^st window output.                   2^nd window output.                   3^rd window output.

2) Use of order by clause with “update of column” (Some good things to learn for for update in oracle 2.sql)

This will also give unique records to different process. 

3) Use of order by clause with “update of column” and also fetch together column/ columns from different table using inner join (Some good things to learn for for update in oracle 3.sql)

In this case, where current of will not work to update column. We need to write explicit query with where clause to update column. But it will also give unique records. 

4) Use of distinct and order by clause with “update of column” and also fetch together column/ columns from different table using inner and left join (Some good things to learn for for update in oracle 4.sql)

This scenario is always confusing to developer while reading oracle documentation or some other link that “distinct is not supporting for “for_update_clause”. Below is comments from Oracle Document.

It doesn’t mean that we can’t use distinct in our query with “for_update_clause”. It means that we can’t use it in outer query. There are always 2 sides of any statement. We all need to understand it properly before assuming anything.

I have taken example of Department to show this scenario. I have 5 departments and each department having more than 2 employees. I tried to create script which will return me employees of top 2 distinct departments from employee table in descending order of department. I tried to execute this script in 3 separate window on same time and it was giving distinct results. It means we can use distinct with “for_update_clause”. Below is screen shot of same.

1^st window output.                   2^nd window output.                   3^rd window output.

5) Simple way of use “update skipped locked” on table. (Some good things to learn for for update in oracle 5.sql)

I tried to test 1^st case for “for update skip locked” by executing same script in 3 separate window on same time. I noticed that 1^st window is returning only 1 record and skipped 2^nd record, 2^nd window is returning one record which was skipped by 1^st window, 3^rd query is not returning any records. It means all windows are trying to lock on same rows and skipping which is locked by other session and continue.

Hope this will help all. Gimme some comments if you like it. I will try to make some scenarios which will satisfy our requirements and also can use “for update skip locked”.

Below scripts to download.

Some good things to learn for for update in oracle 0.sql

Some good things to learn for for update in oracle 1.sql

Some good things to learn for for update in oracle 2.sql

Some good things to learn for for update in oracle 3.sql

Some good things to learn for for update in oracle 4.sql

Some good things to learn for for update in oracle 5.sql