microsoft msconfigure

msconfigure: 

system configuration utility can be used to configure service and start up programs.

mstsc /console:

remote login command.

enq: TM - contention

PROBLEM:
One of user session is running for a long time without return.

CAUSE:
The running session is waiting on event: enq: TM - contention. The table is locked by another session.

SOLUTION:
1. Log out of blocking session if task is completed.
2. create a index for foreign key column if it is related to lock parent table.


reference:
http://www.confio.com/logicalread/solving-oracle-enq-tm-contention-wait-events/

Drop all objects of a schema

Purpose:
login as schema owner and cleanup all objects.

SQL:

set pagesize 0
spool ydropobj.sql
select 'drop table ' || table_name || ' cascade constraints purge;' from user_tables
;
spool off
--@ydropobj.sql
spool ydropobj.sql
select 'drop ' || object_type ||' ' || object_name || ' ;' from user_objects
;
spool off
--@ydropobj.sql
set pagesize 9999
select count(*) from user_objects;

ORA-12520: TNS:listener could not find available handler for requested type of server

ISSUE:
cannot connect to database through listener though tnsping is good.

SYMTUM:
lsnrclt services

Service "TEST" has 1 instance(s).
  Instance "TEST", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:704169 refused:0 state:blocked
         LOCAL SERVER

SOLUTION:
1.reloaded listener.
lsnrctl reload

2. register database instance

sqlplus / as sysdba

alter system register

reference:

http://www.orafaq.com/forum/t/60019/2/

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

ORA-39726: unsupported add/drop column operation on compressed tables

ISSUE:
ORA-39726 is hit when adding column with default value to basic compressed table.

CAUSE:
compressed table will not allow some operation, which are allowed in uncompressed table.

FAILED STATEMENT:
alter table yt add new_column number(3) default 0;

WORK AGROUND:
Case 1: old records not need have default vaule:
alter table yt add new_column number(3);
alter table yt modify new_column default 0;

Case 2: old record need have default value ;
this has exact same effect on compress table as on uncompress table.
alter table yt add new_column number(3) default 0 not null;
alter table yt modify new_column null;

DROP COLUMN FROM COMPRESS TABLE:
alter table yt_compress set unused column new_column;
if you want to get ride of the data from space, do a table move.  And
alter table yt_compress drop unused columns;



Example:

test@TEST.test> create table yt_compress compress as select owner, table_name from dba_tables where owner='SYS';

Table created.

test@TEST.test> create table yt_uncompress as select owner, table_name from dba_tables where owner='SYS';

Table created.

test@TEST.test> select count(*) from yt_compress;

  COUNT(*)
----------
       985

test@TEST.test> select count(*) from yt_uncompress;

  COUNT(*)
----------
       985

test@TEST.test> alter table yt_compress add new_column number(3) default 0;
alter table yt_compress add new_column number(3) default 0
                            *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


test@TEST.test> alter table yt_uncompress add new_column number(3) default 0;

Table altered.

test@TEST.test> select count(*) from yt_uncompress where new_column is null;

  COUNT(*)
----------
         0

test@TEST.test> select count(*) from yt_uncompress where new_column =0;

  COUNT(*)
----------
       985

test@TEST.test> alter table yt_compress add new_column number(3);

Table altered.

test@TEST.test> alter table yt_compress modify new_column default 0;

Table altered.

test@TEST.test> select count(*) from yt_compress where new_column =0;

  COUNT(*)
----------
         0

test@TEST.test> select count(*) from yt_compress where new_column is null;

  COUNT(*)
----------
       985

test@TEST.test> alter table yt_compress add new_column_1 number(3) default 0 not null;

Table altered.

test@TEST.test> alter table yt_compress modify new_column_1 null;

Table altered.

test@TEST.test> select count(*) from yt_compress where new_column is null;

  COUNT(*)
----------
       985

test@TEST.test> select count(*) from yt_compress where new_column_1 is null;

  COUNT(*)
----------
         0

test@TEST.test> select count(*) from yt_compress where new_column_1 =0;

  COUNT(*)
----------
       985


test@TEST.test> alter table yt_compress drop column new_column;
alter table yt_compress drop column new_column
                                    *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


test@TEST.test> alter table yt_compress set unused column new_column;

Table altered.

test@TEST.test> desc yt_compress
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(30)
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 NEW_COLUMN_1                                                   NUMBER(3)

test@TEST.test>

reference:

http://www.dba-oracle.com/oracle11g/sf_Oracle_11g_Data_Compression_Tips_for_the_DBA.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:69076630635645

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