JOINs in ORACLE

WHAT IS JOIN: 
JOIN is to combine two tables to be one in relational database.

CONDITION CLAUSE:
1. using clause:
  using (department_ID)
  using ( department_ID, employee_ID)
the column name must be same in the parenthesis of using clause

2. on clause:
  on ( a.department_ID = b.dpartment_ID)
  on (a.dpartment_ID = b.department_ID and a.employee_ID = b.employee_ID)
  on (a.dpartment_ID = b.department_ID and a.employee_ID > b.employee_ID)
  on (a.dpartment_ID = b.department_ID and a.employee_ID in not NULL )
  on (a.dpartment_ID = b.department_ID and a.employee_ID = 12345 )
the column name is not required to be same in joined two tables. Other condition operation than equal can be used in on clause.

3. where clause
   where a.department_ID = b.dpartment_ID
   where a.dpartment_ID = b.department_ID and a.employee_ID = b.employee_ID

where clause is implicit condition clause for join operation. It is used for row restriction for the final joined table. However its condition can be put to interval join operation to restrict row early and save time and space.

JOIN TYPE in Oracle 
1. JOIN
this is general join operation. It must be used with using or on clause.
2. INNER JOIN
INNER join must be used with using or on clause.
3. NATURAL JOIN
this join don't need using clause and cann't have on clause. I must use common column name and default equal conidtion.
4. NATURAL LEFT JOIN
this join is the same as natural join execpt that it displays not matched row from left side table.

5. NATURAL RIGHT JOIN
this join is the same as natural join execpt that it displays not matched row from right side table.
6. NATURAL FULL JOIN
this join is the same as natural join execpt that it displays not matched row from both side table

7. OUTER JOIN
outer jion must use with using clause and acts like natural join.

yli@TEST.corvus> select * from  department outer join department_head using (department_ID);

DEPARTMENT_ID DEPARTMENT_NAM EMPLOYEE_ID FIRST_NAME      LAST_NAME
------------- -------------- ----------- --------------- ---------------
            1 SALES                    2 Peter           Bank
            2 PRODUCT                  3 Scott           Levy
            4 CORP SERVICE             1 James           Smith

 select * from  department d outer join department_head dh on (d.department_id =dh.department_id );

select * from  department d outer join department_head dh on (d.department_id =dh.department_id )
                            *
ERROR at line 1:
ORA-00933: SQL command not properly ended

8  FULL OUTER JOIN ( FULL JOIN)
this join like inner join expect that it displays not matched row from both side table

9. LEFT OUTER JOIN (LEFT JOIN)
this join like inner join expect that it displays not matched row from left side table

10. RIGHT OUTER JOIN (RIGHT JOIN)
this join like inner join expect that it displays not matched row from right side table
11.CROSS JOIN
this cartesian join and can not have using and on clause.


THOUGHTS. 
1. Write SQL statement with explicit join conditions
2. Avoid using join and outter jion because their results is depends on conditions and kind of unpredictable.


reference:
http://en.wikipedia.org/wiki/Join_%28SQL%29
http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm
http://psoug.org/definition/JOIN.htm
http://tedytirta.com/oracle/to-using-natural-join-in-oracle/

Merge Statement on Oracle database

ISSUE:

  Merge statement failed on "ORA-38104: Columns referenced in the ON Clause cannot be updated;" when update a common column of both target and source table.

CAUSE:
 The updated common column is included in ON clause of merge condition.

SOLUTION:
Remove ON clause condition including the updated column and put the condition as where clause appended to update statement

EXAMPLE:

Failed statement:

MERGE INTO TEST1 target USING
      (SELECT * FROM TEST2 WHERE created = inserted ) source
      ON (target.ID = source.Id AND target.secondId is null AND target.created = source.created)
    WHEN MATCHED THEN
       UPDATE SET target.secondId = 'secondid value';

ORA-38104: Columns referenced in the ON Clause cannot be updated: "TARGET"."SECONDID"

Succeeded statement:

MERGE INTO TEST1 target USING
      (SELECT * FROM TEST2 WHERE created = inserted ) source
      ON (target.ID = source.Id AND target.created = source.created)
    WHEN MATCHED THEN
       UPDATE SET target.secondId = 'secondid value' where target.secondId is null ;

MERGE STATEMENT SYNTEX:

SQL>      MERGE
                                INTO dbo.TargetTable tgt    -- Target Table
                                USING dbo.SourceTable src   -- Source Table
                                ON tgt.ID = src.ID          -- Main comparison criteria
                WHEN MATCHED                                 -- When ID's exist in both tables
                                THEN
                                -- DO SOMETHING
                WHEN NOT MATCHED                    -- When ID's from Source do not exist in Target
                                THEN
                                -- DO SOMETHING
                WHEN NOT MATCHED BY SOURCE          -- When ID's from Target do not exist in Source
                                THEN
                                -- DO SOMETHING

REFERENCE:

http://www.c-sharpcorner.com/UploadFile/0e9eba/oracle-merge-statement-%E2%80%93-quick-catch-up/

http://stackoverflow.com/questions/10600120/oracle-merge-statement-with-conditional-insert

http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_14.shtml

http://psoug.org/reference/merge.html

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

run command as another user in linux using "sudo"

#this will run the command with another user's environment and account.
sudo -i -u username command

Reference
http://linux.die.net/man/8/sudo

compare strings in bash shell script

if [ $1 == $2 ] ; then
echo "Equal"
else
echo "not equal"
fi

if [ $1 != $2 ]; then
echo "not equal"
else
echo "equal"
fi


if [ $1 == "test" ] ; then
echo "Equal"
else
echo "not equal"
fi

reference:
http://www.tech-recipes.com/rx/209/bournebash-shell-scripts-string-comparison/

ORA-14048: by Alter index unusable usable : IMPORT

PROBLEM:
when importing schema to database (version 11.2.0.3 linux 64bit) using data pump, Below errors appeared in log.

ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-14048: a partition maintenance operation may not be combined with other operations
Failing sql is:
 ALTER INDEX "TEST"."SYS_C001906177"  UNUSABLE ENABLE

CAUSE:
This is unpublished bug 4919496.  When schema was exported, system generated index was put into unusable state. So export generate the "alter index .... unusable; enable" statement in dump file. this can be proved by generate SQLFILE using import tool. 

SOLUTION:
the error can be ignored during importing. To avoid this error, make system generated index usable when doing export.

reference:
Import Fails With Error ORA-14048 A Partition Maintenance Operation May Not Be Combined With Other Operations [ID 787407.1]

Password in Oracle 11G

ISSUE:

When we move schemas in one database or from it to another by datapump, we sometimes can not login to moved schemas with its password.

CAUSE:
Password has version (10G or 11G) in 11G database. Version usage is controlled by parameter.

HASH PROCESS:

a. in 10G
 passphrase + username are used to generate hash

b. in 11G
 passphrase + salt are userd to generate hash.

c. sec_case_sensitive_logon parameter is used to control password authentication method.
   sec_case_sensitive_logon -> TRUE   : 11G authentication
   sec_case_sensitive_logon -> FALSE  : 10G authentication

SYNOMINAL:

move schema TEST (11G) to TEST1 (10G) :  need reset pass
move schema TEST (11G) to TEST (10G) :  might need reset pass if 10G hash value is not correct
 move schema TEST (11G) to TEST1 (11G): no need to reset pass
move schema TEST (11G) to TEST (11G): no need to reset pass
move schema TEST (10G) to TEST (10G) : no need to reset pass
move schema TEST (10G) to TEST1 (10G) : need reset pass

move schema TEST (10G) to TEST (11G) : might need reset pass if 11G hash value is not null or correct
move schema TEST (10G) to TEST1 (11G) : need reset pass if 11G hash value is not correct

EXPLANATION:
Two hash values are stored in 11G database. One is for back-track to 10G pass authentication and the other one for new 11G authentication.

to check what hash was stored:
select username, password_versions from dba_users;
results can be:
10G
11G
10G 11G

to check the hash values
select name, password,spare4 from sys.user$;
password column is 10G hash value
spare4 column is 11G hash value(3-42) and its salt harsh value(43-63)


Reference:
http://www.notesbit.com/index.php/scripts-oracle/oracle-11g-new-password-algorithm-is-revealed-by-seclistsorg/
http://www.petefinnigan.com/sha1.sql
http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1101.htm#sthref3208
http://askdba.org/weblog/2008/11/how-to-changerestore-user-password-in-11g/

CHARACTER SET MIGRATION in ORACLE

ISSUE:

Database is using an invalid (non-UTF8) character set: (NLS_CHARACTERSET = WE8MSWIN1252) was given when tried to install space walk application.

CAUSE:

database was created with WE8MSWIN1252 because host os is windows. 

SOLUTION:

user csscan, csalter, datapump to migrate character set to AL32UTF8

STEPS:

1. csscan full=Y tochar=AL32UTF8

2. export user with application data need be converted. drop them from database

3. run csalter to change dictinary table character set

4. import back the dropped user.

POINTS:

it is good to user AL32UTF8 as character set at beginning. 

datapump and origical exp/imp tools can do character conversion on fly.

TABLE/VIEW:

nls_session_parameters

nls_instance_parameters

nls_database_parameters

v$nls_valid_values

v$nls_parameters

CHECK database character set:

select * from nls_database_parameters where parameter like '%CHAR%';

NLS paramters:

PARAMETER

------------------------------

NLS_LANGUAGE

NLS_TERRITORY

NLS_CURRENCY

NLS_ISO_CURRENCY

NLS_NUMERIC_CHARACTERS

NLS_CHARACTERSET

NLS_CALENDAR

NLS_DATE_FORMAT

NLS_DATE_LANGUAGE

NLS_SORT

NLS_TIME_FORMAT

NLS_TIMESTAMP_FORMAT

NLS_TIME_TZ_FORMAT

NLS_TIMESTAMP_TZ_FORMAT

NLS_DUAL_CURRENCY

NLS_COMP

NLS_LENGTH_SEMANTICS

NLS_NCHAR_CONV_EXCP

NLS_NCHAR_CHARACTERSET

NLS_RDBMS_VERSION

reference:

http://www.morganslibrary.org/reference/character_sets.html

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch12scanner.htm#g1035448

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch11charsetmig.htm#g1011430

https://forums.oracle.com/forums/thread.jspa?threadID=990007

http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch1overview.htm#i1005900

https://forums.oracle.com/forums/thread.jspa?messageID=3471873

https://forums.oracle.com/forums/thread.jspa?threadID=2206301