[Oracle] Account Expiry

Question: How can we auto lock a schema or user in oracle.
Ans: You can not directly lock a schema but you can lock user trough profile. First create a profile with the method defined below and then while creating user give that profile name instead of default.

CREATE PROFILE prof LIMIT
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 30
PASSWORD_LIFE_TIME 90;

ALTER USER mabbas PROFILE prof;

User mabbas can use his password for 90 days before the user (or DBA) is forced to change the password.

[T-SQL] Change SQL Server 2005 SA Password

Changing the password in SQL Server was much easier. You just type the following thing and password for the account is changed:

sp_password 'Old','New', Account

But this dont work for Sql Server 2005. Initially I thought whats worng with the SQL Server, but later I realized that it may be different for the two versions. At last I found it and here it is:

ALTER LOGIN Account_Name WITH
PASSWORD = 'New_Password'
OLD_PASSWORD = 'Old_Password';
GO

[Informatica] Tutorial Part 4

Here is the third part of Informatica Tutorial Part 4.


http://www.youtube.com/watch?v=AuCVmPpRvy4

[T-SQL] Concurrency Issues and Isolation Level

There are four types of concurrency problems visible in the normal programming.

1) Lost Update – This problem occurs when there are two transactions involved and both are unaware of each other. The transaction which occurs later overwrites the transactions created by the earlier update.

2) Dirty Reads – This problem occurs when a transactions selects data that isn’t committed by another transaction leading to read the data which may not exists when transactions are over.

Example: Transaction 1 changes the row. Transaction 2 changes the row. Transaction 1 rolls back the changes. Transaction 2 has selected the row which does not exist.

3) Nonrepeatable Reads – This problem occurs when two SELECT statements of the same data results in different values because another transactions has updated the data between the two SELECT statements.

Example: Transaction 1 selects a row, which is later on updated by Transaction 2. When Transaction A later on selects the row it gets different value.

4) Phantom Reads – This problem occurs when UPDATE/DELETE is happening on one set of data and INSERT/UPDATE is happening on the same set of data leading inconsistent data in earlier transaction when both the transactions are over.

Example: Transaction 1 is deleting 10 rows which are marked as deleting rows, during the same time Transaction 2 inserts row marked as deleted. When Transaction 1 is done deleting rows, there will be still rows marked to be deleted.

When two or more transactions are updating the data, concurrency is the biggest issue. I commonly see people toying around with isolation level or locking hints (e.g. NOLOCK) etc, which can very well compromise your data integrity leading to much larger issue in future.

Here is the quick mapping of the isolation level with concurrency problems:

Isolation Dirty Reads Lost Update Nonrepeatable Reads Phantom Reads
Read Uncommitted Yes Yes Yes Yes
Read Committed No Yes Yes Yes
Repeatable Read No No No Yes
Snapshot No No No No
Serializable No No No No

Source: sqlauthority.com

[Informatica] Tutorial Part 3

Here is the third part of Informatica Tutorial Part 3.

http://www.youtube.com/watch?v=upwtGPn4pDI

[Informatica] Tutorial Part 2

Informatica Tutorial Part 2 for two beginners:

http://www.youtube.com/watch?v=oM2d-IHfRUw