The UNION [ALL], INTERSECT, MINUS Operators

UNION Example The following statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table:

SELECT location_id, department_name "Department", 
   TO_CHAR(NULL) "Warehouse"  FROM departments
   UNION
   SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name 
   FROM warehouses;

UNION ALL Example The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:
SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;

SELECT location_id  FROM locations 
UNION ALL 
SELECT location_id  FROM departments;



A location_id value that appears multiple times in either or both queries (such as '1700') is returned only once by the UNION operator, but multiple times by the UNION ALL operator.

INTERSECT Example The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:
SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;


MINUS Example The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second:
SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;

Extract DDL of scheduler job in oracle

set long 10000

select dbms_metadata.get_ddl('PROCOBJ','SCOTT_JOB','SCOTT') from dual;

Note : The job in sys cannot be extracted, so, copy it to another user and then extract

exec dbms_scheduler.copy_job('SYS.MY_JOB','SCOTT.MY_JOB');

Checking Swap Space Size and Usage

step 1
grep SwapTotal /proc/meminfo

step 2
vmstat 3 100
  The fields si and so show the amount of memory paged in from disk and paged out to disk, respectively

step 3
ls -al /var/log/sa | grep "Oct 12"
sar -W -f /var/log/sa/sa12
The fields pswpin and pswpout show the total number of pages brought in and out per second, respectively.

alternative example for yesterday
sar -f /var/log/sa/sa$(date +%d -d yesterday)

solutions
Adding more RAM.
Reducing the size of the SGA.
Increasing the size of the swap space

Oracle 12c agent won't start due to large amount of databases on the server

Oracle 12c agent won't start due to large amount of databases on the server

cd /ora01/oracle/product/agent12c/agent_inst/sysman/config

Change Xmx1024M    to Xmx2024M

< agentJavaDefines=-Xmx1024M -XX:MaxPermSize=96M
---
> agentJavaDefines=-Xmx2024M -XX:MaxPermSize=96M

Useful command to Check performance on linux server

network
   netstat -ptc

IO
  iostat

 ps commands in order to check for performance probelms:

1) Displaying top CPU_consuming processes:

 # ps aux|head -1; ps aux|sort -rn -k2|head -10
2) Displaying top 10 memory-consuming processes:

# ps aux|head -1; ps aux|sort -rn -k3|head
3) Displaying process in order of being penalized:

# ps -eakl|head -1; ps -eakl|sort -rn +5
4) Displaying process in order of priority:

# ps -eakl|sort -n +6|head
5) Displaying process in order of nice value

# ps -eakl|sort -n +7
6) Displaying the process in order of time

# ps vx|head -1;ps vx|grep -v PID|sort -rn +3|head -10
7) Displaying the process in order of real memory use

# ps vx|head -1; ps vx|grep -v PID|sort -rn +6|head -10
8) Displaying the process in order of I/O

# ps vx|head -1; ps vx|grep -v PID|sort -rn +4|head -10
9) Displaying WLM classes

# ps -a -o pid, user, class, pcpu, pmem, args
10) Determining process ID of wait processes:

# ps vg|head -1; ps vg|grep -w wait
11) Wait process bound to CPU

 # ps -mo THREAD -p
12) CPU usage with priority levels

 # topas -P


# for x in `seq 1 1 10`; do ps -eo state,pid,cmd | grep "^D"; echo "----"; sleep 5; done

# ps auxww --sort=lstart | sort -r -k3,4 | head -20

# ps aux --sort -pcpu

# ps -ef --sort=start_time|head -20

# watch -n 1 "(ps aux | awk '\$8 ~ /D/  { print \$0 }')"

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