Conditional OR joins in SQL query returns duplicate rows

Scenario: Whenever I have a particular OR condition join in my query, I get a duplicated record. The case is when the two join conditions before and after OR are satisfied.

SELECT A.Column1,A.Column2, Name, Address FROM A, B
WHERE (
(A.COLUMN1 = B.COLUMN1) OR (A.COLUMN2  = B.COLUMN2)
)

Cause: Imagine there are rows in A and B that satisfy the two join conditions used in OR, In such a scenario the database will return two rows with same value.

Here as per the query, for row 'Athul' in Table A, both the join conditions match with B and hence will return below output resultset,

As you can see in the result above, 'Athul' is resulted twice.  However for the second recrod

Solution: This can be avoided by using a LEFT join instead of an OR join condition. Do not consider using DISTINCT as it is a fairly cost consuming alternative.

[Solved] sqlplus / as sysdba not working

Earlier in one of our previous posts we had discussed how to login to Oracle 11g Database as the SYS user without credentials. However this doesn't apply everywhere, chances are that you might have come across this error as shown below,

Login as SYSDBA

Error

Cause: This is because the Windows user you have used to login is not a part of the ORA_DBA group. 

Resolution: The issue can be resolved by adding the user to the ORA_DBA group as described in the steps below,

1. Go to Administrative Tools > Server Manager 
2. Inside the Server Manager > Configuration > Local Users and Groups > Groups locate the ORA_DBA group 
   
3. Open the ORA_DBA group and add the windows user to the group as seen below,
   
4. Check again with the sqlplus / as sysdba command again after you have added the user to the group and the issue will be fixed as below,

   

   SQLPlus logged successfully as SYSDBA user

[Solved] Forgot SYSDBA credentials for Oracle 11g

This is a really helpful tip incase you forgot the password to the SYSDBA user in your Oracle 11g Database. You can use the following syntax to login to the server as SYSDBA from RUN,

sqlplus / as sysdba

Login to SQLPlus without SYS credentials

This would open SQLPlus as shown below and you can test the same by running the command SHOW USER

Logged into SQLPlus and test the name of the current user.

Note: This will only work if the Windows User is a part of the ORA_DBA user group. 

[Oracle 11g] Run a SQL Script from SQLPlus

Scenario: So there was a set of sql scripts to be executed into my database in order to work on a training session. Instead of copy pasting each script to SQL Developer and then running it. We thought of saving time by executing it from SQL Plus.

Resolution: Place your .sql extension file with the script at a particular folder in your system.

Now go to run and open sqlplus. Connect as a user with SYSDBA or DBA privileges since it is highly likely that this scripts will contain DDL statements like create, alter, or drop.

After connecting to SQLPlus as SYSDBA user, Enter the following in the command line

SQL > @PATH/FILENAME.sql

where PATH specifies the path where the sql file resides and FILENAME the name of the sql file script to be executed. We created a sql script named test.sql with content as shown below.

Sample SQL Script

 Save the file and copy the folderpath/filename.sql and go to cmd and paste it as shown below

SQL Script output after execution