Rupam Verma' Blog

get list of user accounts - either unlocked or password is expired.

2021-03-26T12:27:00.004-04:00

SELECT

'ALTER '||

SUBSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),

INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),500),'DEFAULT')-1) ,

INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),500),'USER')) USER_PASS , USERNAME, PROFILE

FROM DBA_USERS

WHERE USERNAME IN

(SELECT USERNAME

FROM DBA_USERS

WHERE (USERNAME in

(select username from dba_users where (account_status like '%EXPIRED%' or account_status like '%LOCK%'))));

search a pattern in file using Python

2021-02-24T15:28:00.006-05:00

import re

pattern = re.compile("ORA-")

for line in open("x2.txt"):

for match in re.finditer(pattern, line):

print(line)

count number of files generated per day

2021-02-24T15:26:00.003-05:00

# count number of files generated per day

find . -type f -printf '%TY-%Tm-%Td\n' | sort | uniq -c

# count number of files generated per hour in last 600 minutes

find . -cmin -600 -type f -printf '%TY-%Tm-%Td-%TH\n' | sort | uniq -c

find . -maxdepth 1 -type f -printf '%TY-%Tm-%Td\n' | awk '{array[$0]+=1}END{ for(val in array) print val" "array[val] }'|sort

what is causing account lock ?

2020-09-14T16:27:00.003-04:00

check profile

col PROFILE format a10

col LIMIT format a15

select * from dba_profiles where resource_type='PASSWORD' order by 1,2;

option 1

turn on trace : alter system set events '1017 trace name errorstack level 10';

check listener , alert.log

turn off trace : alter system set events '1017 trace name errorstack off';

option 2

col dbusername form a15

col userhost form a15

col event_timestamp form a30

select dbusername,userhost,event_timestamp

from unified_audit_trail

where return_code=1017 and

event_timestamp>sysdate-1/12 order by 3

option 3

AUDIT connect whenever NOT SUCCESSFUL;

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

-- oracle doc : 352389.1

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

select userid, userhost, terminal, clientid, TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE from aud$ where returncode=1017

and NTIMESTAMP# > sysdate - 1/12 ;

option 4

-- oracle doc : 221944.1

SQL>AUDIT CREATE SESSION BY ACCESS WHENEVER NOT SUCCESSFUL;

SQL>AUDIT CONNECT BY ACCESS WHENEVER NOT SUCCESSFUL;

col USERID format a10

col USERHOST format a50

col TERMINAL format a10

col userhost form a20

select returncode, action#, userid, userhost, terminal, TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF') from aud$ where returncode='1017' and action#=100

and NTIMESTAMP# > sysdate - 1/12 ;

SELECT TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE from aud$ where returncode=1017

and NTIMESTAMP# > sysdate - 1/12 ;

2020-08-26T13:33:00.004-04:00

Oracle 19c - CDB / PDB - useful sqls

sqlplus / as sysdba

set lines 200

COL name FORMAT A30

col CON_ID form 999999

col OPEN_TIME FORMAT a40

col pdb form a30

COLUMN pdb_name FORMAT A20

SELECT con_id, name, open_mode, restricted, open_time FROM gv$containers;

SELECT name, cdb, open_mode FROM v$database;

SELECT name, pdb FROM v$services ORDER BY name;

select PDB_ID,PDB_NAME,CON_ID,STATUS,LOGGING,FORCE_LOGGING from dba_pdbs ORDER BY pdb_name;

SELECT con_id, name, open_mode, restricted FROM v$pdbs ORDER BY name;

select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs

# the end

2020-08-26T13:29:00.004-04:00

oracle 19c - CDB/ PDB _ Save the state of PDB

-- save the state, so that pdb will open when cdb is restarted

sqlplus / as sysdba

alter pluggable database all open;

ALTER PLUGGABLE DATABASE demo SAVE STATE instances=all;

-- check

COLUMN con_name FORMAT A20

COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

# The end

2020-08-26T13:25:00.003-04:00

limit PDB's to 3 in CDB in Oracle 19c

sqlplus / as sysdba

show parameter max_pdbs

ALTER SYSTEM SET max_pdbs=3 scope=both sid='*';

show parameter max_pdbs

# the end

troubleshoot - scheduler job not running

2018-08-21T10:25:00.000-04:00

troubleshoot - scheduler job not running

sqlplus Scott/tiger

-- list scheduler job

set lines 120
col SCHEDULE_NAME format a25
col SCHEDULE_TYPE form a10
prompt from dba_scheduler_jobs
prompt =============
select job_name, schedule_name, schedule_type, enabled from dba_scheduler_jobs where owner=upper('&schema') ;

-NOTE in the enabled column - TRUE means working, FALSE means not working

-- identify scheduler running jobs
select
rj.job_name,
s.username,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.logon_time
from
user_scheduler_running_jobs rj,
v$session s,
v$process p
where
rj.session_id = s.sid
and
s.paddr = p.addr
order by
rj.job_name
;

SELECT max(log_date) FROM user_scheduler_job_run_details where job_name='SCH_JOB_NAME_HERE''

-- to kill by sid

select 'alter system kill session '||chr(39)||s.sid||','||s.serial#||chr(39)||';'
from user_scheduler_running_jobs rj,
v$session s,
v$process p
where rj.session_id = s.sid
and s.paddr = p.addr
order by rj.job_name;

-- to kill by by pid

select 'kill -9 '||p.spid
from
user_scheduler_running_jobs rj,
v$session s,
v$process p
where
rj.session_id = s.sid
and
s.paddr = p.addr
order by
rj.job_name
;

useful commands
exec dbms_scheduler.disable('SCH_JOB_NAME_HERE');
exec dbms_scheduler.enable('"SCH_JOB_NAME_HERE"');

exec dbms_scheduler.stop_job('SCH_JOB_NAME_HERE');

date time in python

2018-08-20T10:17:00.002-04:00

#!/usr/bin/env python

import io
import datetime
import traceback

AlertLogFile=r'test.dat'
EventDate=datetime.datetime(1, 1, 1, 0, 0)
OutputList=[]

try:
with io.open(AlertLogFile,mode='r') as f:
for line in f:
EventDate=datetime.datetime.strptime(line.rstrip('\n'), '%Y-%m-%d %H:%M:%S')
# EventDate=datetime.datetime.strptime(line.rstrip('\n'), '%a %b %d %H:%M:%S %Y')
# OutputList.append([EventDate,line.rstrip('\n')])
OutputList.append([EventDate,line.rstrip('\n')])
except:
print(traceback.format_exc())

for o in OutputList:
# use o[0].strftime('%a %b %d %H:%M:%S %Y') to get original Oracle style Format
print('[%s] %s' % (o[0],o[1]))
# print('[%s]' % (o[0]))

Data
oracle@lnx21426> cat test.dat
2016-11-03 12:01:13
2017-04-05 12:03:28
oracle@lnx21426>

Use ADRCI or Support Workbench to package the incident.

2018-08-19T17:58:00.004-04:00

step 1
adrci> show alert -p "message_text like '%incident%'"

ADR Home = /oramisc01/oracle/diag/rdbms/demoprim/demo1:
*************************************************************************
Output the results to file: /ora01/oracle/temp/alert_33812_140499732489984_demo_1.ado

step 2
adrci> show problem

ADR Home = /oramisc01/oracle/diag/rdbms/demoprim/demo1:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1 ORA 600 [ktbdchk1: bad dscn] 80396 2018-08-19 16:39:16.523000 -04:00
1 rows fetched

step 3
adrci> show incident

ADR Home = /oramisc01/oracle/diag/rdbms/demoprim/demo1:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
80393 ORA 600 [ktbdchk1: bad dscn] 2018-08-19 16:38:39.543000 -04:00
80394 ORA 600 [ktbdchk1: bad dscn] 2018-08-19 16:39:14.585000 -04:00
80395 ORA 600 [ktbdchk1: bad dscn] 2018-08-19 16:39:15.629000 -04:00
80396 ORA 600 [ktbdchk1: bad dscn] 2018-08-19 16:39:16.523000 -04:00
4 rows fetched

step 4
show incident -mode detail -p "incident_id=80393"

step 5
First, create the package using the problem ID displayed by the SHOW PROBLEM command.
adrci > ips create package problem 1 correlate all

step 6
Next, create a zip to send to Oracle Support by specifying the package number displayed by the above command.
adrci > ips generate package 1 in "/tmp"

step 6
send the zip file to oracle

Step by Step: How to troubleshoot a slow running query in Oracle

2018-08-18T16:18:00.002-04:00

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

# Step by Step: How to troubleshoot a slow running query in Oracle

- what are sessions doing

set lines 999
col osuser form a10
col username form a10
col spid form a10
select a.sid, a.serial#,
spid ,
sql_id,
a.inst_id,
a.osuser,
a.username, status,
substr(to_char(logon_time,'dd-mon-yy hh24:mi'),1,18) logon_time,
substr(to_char(sql_exec_start,'dd-mon-yy hh24:mi'),1,18) sql_exec_start,
substr(a.module,1,18) module ,
substr(machine,1,17) machine,
event
-- substr(a.program,1,16) program
from gv$session a, gv$process b
where a.username not in (' ','PATROL','SYS','DBSNMP')
and b.addr=a.paddr
and b.inst_id=a.inst_id
and a.status='ACTIVE'
-- and event='inactive transaction branch'
order by status desc, logon_time
/

-- blockers in standalone

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ') is blocking'
|| s2.username || '@' || s2.machine || '( SID=' || s2.sid || ')' from
v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.block=1 and l2.request > 0
and l1.id1=l2.id1
and l2.id2=l2.id2;

-- blockers in rac
set pages 1000
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' Serial#= '||s1.serial# ||' Inst=' ||s1.INST_ID ||' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

-- top
get pid

-- get sid using pid
select sid
from v$session s, v$process p
where p.spid = &pid
and s.paddr = p.addr;

-- get sqlId using sid
select sql_id
from v$session
where sid = &sid;

- get sql using sid
select sql_fulltext
from v$sql l, v$session s
where s.sid = &sid
and l.sql_id = s.sql_id;

# Check for the wait events:

-- Check for the particular user and session.
col "Description" format a50
select sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session
where username = 'GOS_USER';

select SID, osuser, machine, terminal, service_name,
logon_time, last_call_et
from v$session
where username = 'GOS_USER';

-- Session waits for a specific machine
col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
decode(state, 'WAITING', 'Waiting',
'Working') state,
last_call_et, seconds_in_wait, event
from v$session
where sid = &sid
/

History of wait events in a specific session

set lines 120 trimspool on
col event head "Waited for" format a30
col total_waits head "Total|Waits" format 999,999
col tw_ms head "Waited|for (ms)" format 999,999.99
col aw_ms head "Average|Wait (ms)" format 999,999.99
col mw_ms head "Max|Wait (ms)" format 999,999.99
select event, total_waits, time_waited*10 tw_ms,
average_wait*10 aw_ms, max_wait*10 mw_ms
from v$session_event
where sid = &sid
/

-- check data access isue

select event
from v$session
where sid = &sid;

-- check data access waits
select SID, state, event, p1, p2
from v$session
where sid = &sid;

-- run sql advisor

1. Tuning task created for specific Sql id:

SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'bxa13by3718uw',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'bxa13by3718uw_tuning_task',
description => 'Tuning task for statement bxa13by3718uw.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

2. Executing the tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'bxa13by3718uw_tuning_task');

3. Displaying the recommendations:

Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function.

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('bxa13by3718uw_tuning_task') AS recommendations FROM dual;

SET PAGESIZE 24

Based on the tuning advisor recommendation we have to take corrective actions. These recommendation could be:

1) Gather Statistics
2) Create Index
3) Drop Index
4) Join orders
5) Create sql profiles and many more

After corrective action from tuning advisor run the SQL again and see the improvement.

python program to check ocssd.log for error/issue/node eviction etc in last 48 hours

2018-08-18T16:14:00.001-04:00

# python program to check ocssd.log for error/issue/node eviction etc

#!/usr/bin/env python

import io
import datetime
import traceback

DayList=['Sun','Mon','Tue','Wed','Thu','Fri','Sat']
KeyWordList=['ORA-','Error','CRS-1013','Polling','Diskpingout']
# KeyWordList=['ORA-','Error','Starting ORACLE instance','Shutting down instance']
OutputList=[]

# customize to check last 48 hours in this case
SkipOldEventHours=48

# name and location of ocssd file . it has changed in 12c
AlertLogFile=r'/ora01/grid/11.2.0.4/grid/log/`hostname`/cssd/ocssd.log'

SkipOldEventDateTimeDelta=datetime.timedelta(hours=SkipOldEventHours)
EventDate=datetime.datetime(1, 1, 1, 0, 0)

try:
with io.open(AlertLogFile,mode='r') as f:
for line in f:
for w in KeyWordList:
if w in line:
OutputList.append([EventDate,line.rstrip('\n')])
except:
print(traceback.format_exc())

for o in OutputList:
# use o[0].strftime('%a %b %d %H:%M:%S %Y') to get original Oracle style Format
print('[%s] %s' % (o[0],o[1]))

# the end

python program to check ocssd.log in oracle 11g for node eviction / issues

2018-08-18T16:06:00.003-04:00

# python program to check ocssd.log in oracle 11g for node eviction / issues

# customize name/location of ocssd file: it has changed in 12g
infile = r'/ora01/grid/11.2.0.4/grid/log/`hostname`/cssd/ocssd.log'

important = []
keep_phrases = ["Diskpingout",
"CRS-1013",
"Polling"]

with open(infile) as f:
f = f.readlines()

for line in f:
for phrase in keep_phrases:
if phrase in line:
# important.append(line.rstrip('\n'))
important.append([line.rstrip('\n')])
# OutputList.append([EventDate,line.rstrip('\n')])
break

# print(important)
print infile
for o in important:
# use o[0].strftime('%a %b %d %H:%M:%S %Y') to get original Oracle style Format
# print('[%s] %s' % (o[0],o[1]))
print('[%s]' % (o[0]))

python program to check oracle alert log file for error in past 48 hours

2018-08-18T16:01:00.004-04:00

# python program to check oracle alert log file for error in past 48 hours

#!/usr/bin/env python

import io
import datetime
import traceback

DayList=['Sun','Mon','Tue','Wed','Thu','Fri','Sat']
KeyWordList=['ORA-','Error','alter']
# KeyWordList=['ORA-','Error','Starting ORACLE instance','Shutting down instance']
OutputList=[]

# customize hours to look for
SkipOldEventHours=48

# customize name of alert.log
AlertLogFile=r'alert_cisdbc.log'

SkipOldEventDateTimeDelta=datetime.timedelta(hours=SkipOldEventHours)
EventDate=datetime.datetime(1, 1, 1, 0, 0)

try:
with io.open(AlertLogFile,mode='r') as f:
for line in f:
if len(line)>3 and line[0:3] in DayList:
# OutputList.append(line)
EventDate=datetime.datetime.strptime(line.rstrip('\n'), '%a %b %d %H:%M:%S %Y')
if EventDate < datetime.datetime.now()-SkipOldEventDateTimeDelta :
continue
elif len(line)>3:
if EventDate < datetime.datetime.now()-SkipOldEventDateTimeDelta :
continue
for w in KeyWordList:
if w in line:
OutputList.append([EventDate,line.rstrip('\n')])
else:
continue
except:
print(traceback.format_exc())

for o in OutputList:
# use o[0].strftime('%a %b %d %H:%M:%S %Y') to get original Oracle style Format
print('[%s] %s' % (o[0],o[1]))

# the end

List TOP 10 by size directories

2018-08-18T15:52:00.003-04:00

# List TOP 10 by size directories
du -m --max-depth 1 | sort -rn | head -11

Get max value in partition

2018-05-23T11:37:00.002-04:00

Set serverout on size 1000000
declare
cursor tab_part_cur IS
select table_name,partition_name from user_tab_partitions where table_name='MESSAGE_LOG' order by 1,2;
v_date date;
v_pass integer := 0;
begin
dbms_output.put_line('These partitions can be dropped.');
for tab_part_rec in tab_part_cur
loop
exit when tab_part_cur%notfound;
v_date := GET_MAX_LOG_TS(tab_part_rec.table_name,tab_part_rec.partition_name);
if v_date <= (sysdate - 10) then
dbms_output.put_line(tab_part_rec.table_name||' '||tab_part_rec.partition_name|| ' '||v_date );
end if;
end loop;
end;
/

CREATE or replace FUNCTION GET_MAX_LOG_TS (
p_TableName IN VARCHAR2,
p_PatitionName IN VARCHAR2
) RETURN DATE
IS
v_DateVal Date;
v_string varchar2(400);
BEGIN
v_string := 'SELECT max(log_ts) v_DateVal FROM MESSAGE_LOG PARTITION('||p_PatitionName||')';

dbms_output.put_line(v_string);
dbms_output.put_line(chr(10));
execute immediate v_string into into v_DateVal;;

RETURN v_DateVal;
END GET_max_log_ts;
/

list max value in partition

2018-05-23T11:20:00.001-04:00

function

CREATE OR REPLACE FUNCTION GET_HIGH_VALUE_AS_DATE (
p_TableName IN VARCHAR2,
p_PatitionName IN VARCHAR2
) RETURN DATE
IS
v_LongVal LONG;
BEGIN
SELECT HIGH_VALUE INTO v_LongVal
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = p_TableName
AND PARTITION_NAME = p_PatitionName;

RETURN TO_DATE(substr(v_LongVal, 11, 19), 'YYYY-MM-DD HH24:MI:SS');
END GET_HIGH_VALUE_AS_DATE;
/

calls function
Set serverout on size 1000000
declare
cursor tab_part_cur IS
select table_name,partition_name from user_tab_partitions where table_name='BOMR_MESSAGE_LOG' order by 1,2;
v_date date;
v_pass integer := 0;
begin
dbms_output.put_line('These partitions can be dropped.');
for tab_part_rec in tab_part_cur
loop
exit when tab_part_cur%notfound;
v_date := GET_HIGH_VALUE_AS_DATE(tab_part_rec.table_name,tab_part_rec.partition_name);
if v_date <= (sysdate - 10) then
dbms_output.put_line(tab_part_rec.table_name||' '||tab_part_rec.partition_name|| ' '||v_date );
end if;
end loop;
end;
/

rebuild unusable indexes

2018-05-23T11:16:00.003-04:00

Set serverout on size 1000000
declare
cursor idx_cur IS
select owner, index_name from dba_indexes where status = 'UNUSABLE';
v_string varchar2(400);

begin
dbms_output.put_line('These indexes are in unusable.');
for idx_rec in idx_cur
loop
exit when idx_cur%notfound;
dbms_output.put_line(idx_rec.owner||' '||idx_rec.index_name);
v_string := 'alter index '|| idx_rec.owner ||'.'|| idx_rec.index_name||' rebuild;';
dbms_output.put_line(v_string);
dbms_output.put_line(chr(10));
execute immediate v_string ;
end loop;
end;
/

stream_pool_size

2018-05-23T10:53:00.000-04:00

check the spilling status and pool size recommendation of your Streams pool with following query.

select inst_id,STREAMS_POOL_SIZE_FOR_ESTIMATE,estd_spill_time, estd_unspill_time, streams_pool_size_factor

from gv$streams_pool_advice order by 1,5;

PERFORMANCE BOTTLENECK CHECK USING OS TOOLS

2018-04-06T14:14:00.004-04:00

A.REAL TIME PERFORMANCE TUNING USING OS TOOLS

TOOL1:-TOP UTILITY

check in TOP with column S where it shows ‘R’ means running and ‘S’ means sleeping.

TOOL 2:-SAR UTILITY WITH -U OPTION TO CHECK CPU AND IO BOTTLENECK
sar -u 10 8

TOOL 3:VMSTAT REPORT
vmstat 1 10
r column is runnable processes

TOOL 4:TO IDENTIFY DISK BOTTLENECK
sar -d 5 2

TOOL 5:-SAR -B TO REPORT DISK USAGE

TOOL 6:-SAR -Q TO FIND PROCESSES UNDER QUEUE.WE NEED TO LOOK BLOCKED SECTION

TOOL 7:-TO IDENTIFY MEMORY USAGE USING SAR -R

TOOL 8. REAL TIME PERFORMANCE TUNING USING ORATOP REPORT
./oratop -f -d -i 10 / as sysdba

How to get all the alert.log file entry for a date

2018-01-23T14:47:00.001-05:00

grep -B1 -A1 'Jan 19' alert.log

awk '/Jan 19/{c=6}c&&c --' alert.log

Change Remote Window Password VPN via Mac

2017-12-19T10:51:00.002-05:00

To Change Password on Window

Press fn-control-option-del

Extract range of lines from a text file in Unix

2017-12-19T10:48:00.002-05:00

Input File : orig-data-file
OutPut File : new-file

Line range 100 to 110

sed -n '100,110 p' orig-data-file > new-file

awk 'NR>=100&&NR<=110' orig-data-file > new-file

perl -ne 'print if 110..110' orin-data-file > new_file

-- get alert.log file for particular day
awk '/Jun 26/{c=6}c&&c --' alert_demo.log

grep -B1 -A1 'Jun 26' alert_demo.log

oratop

2017-11-29T13:22:00.001-05:00

Pre- requisite


download oratop
set  oracle environment

helpful commands


oratop -i 5 / as sysdba



oratop -bdfi5 "/ as sysdba"



to remote database

oratop -f -i 5 sys/pass@db as sysdba

FOR HELP press h and get the interactive key and enter in the main screen for example t is for table space

The UNION [ALL], INTERSECT, MINUS Operators

2017-11-29T13:02:00.000-05:00

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;